-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2022..2023, All rights reserved by Steffen Anders, Elsteraue - Germany
-- Visit also https://www.it-crafts.de.
--
-- minimales Funktionsset fuer virtuelle Lookup-Tabelle

-- ---------------------------------------------------------------------------------------------------------------
-- select with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_WartungsAuftraege_Lookup')
  DROP PROCEDURE [dbo].[up_Sel_WartungsAuftraege_Lookup]
GO
CREATE PROCEDURE [dbo].[up_Sel_WartungsAuftraege_Lookup]  @mRI integer
AS
  DECLARE @m_Ident integer
  -- MT_* Monteurdaten
  DECLARE @m_MT_Annr varchar(4)
  DECLARE @m_MT_Name1 varchar(40) = ''
  -- KD_* Kundendaten
  DECLARE @m_KD_Nr varchar(15)
  DECLARE @m_KD_Name1 varchar(40) = ''
  -- SO_* Standortdaten
  DECLARE @m_SO_Strasse varchar(40) = ''
  DECLARE @m_SO_Plz varchar(8) = ''
  DECLARE @m_SO_Ort varchar(40) = ''
  -- AN_* Anlagendaten
  DECLARE @m_AN_Ident integer = 0
  DECLARE @m_AN_Nr  varchar(10) = ''
  DECLARE @m_AN_Bez1 varchar(40) = ''
  DECLARE @m_AN_Bez2 varchar(40) = ''
  -- WM_* Meldungsdaten
  DECLARE @m_WM_Bearb  varchar(10) = ''

  -- Lookup WartungsObjekte
  DECLARE @m_WOKdNr varchar(15)
  DECLARE @m_WOStandort varchar(15)

  select  @m_Ident = WA.Ident, @m_KD_Nr = WA.KdNr, @m_MT_Annr = WA.Monteur, @m_AN_Ident = WA.AnlagenId,
          @m_WOKdNr = ISNULL(WO.KdNr,''), @m_WOStandort = ISNULL(WO.Standort,'') 
    from ut_WartungsAuftraege WA left join ut_WartungsObjekte WO on WO.Ident = WA.ObjektId where WA.RecId = @mRI

  -- Kundennamen ermitteln
  If @m_KD_Nr = '' 
    SET @m_KD_Nr = @m_WOKdNr
  IF @m_KD_Nr <> '' 
    select @m_KD_Name1 = Name1 from ut_Adressen where DESTDEBNR = @m_KD_Nr 

  -- Monteur ermitteln
  IF @m_MT_Annr <> ''
    select @m_MT_Name1 = Name1 from ut_Adressen where DESTTYP = ' 4' AND Annr = @m_MT_Annr

  -- Standortdaten ermitteln
  IF @m_WOStandort = ''
    SET @m_WOStandort = @m_KD_Nr
  IF @m_WOStandort = ''
    SET @m_WOStandort = @m_WOKdNr
  IF @m_WOStandort <> ''
    select @m_SO_Strasse = Strasse, @m_SO_Plz = RTRIM(SUBSTRING(Plz,1,8)), @m_SO_Ort = Ort from ut_Adressen where DESTDEBNR = @m_WOStandort

  -- Anlagendaten
  IF @m_AN_Ident <> 0
    select @m_AN_Nr = Nr, @m_AN_Bez1 = Bez1, @m_AN_Bez2 = Bez2 from ut_WartungsAnlagen WHERE Ident = @m_AN_Ident

  -- Sachbearbeiter, der Meldung angenommen hat
  select @m_WM_Bearb = ISNULL(Bearbeiter,'') from ut_WartungsMeldungen WHERE AuftragId = @m_Ident

  select 
    @m_Ident AS Ident,
    @m_MT_Name1 AS MT_Name1,
    @m_KD_Nr AS KD_Nr,
    @m_KD_Name1 AS KD_Name1,
    @m_SO_Strasse AS SO_Strasse,
    @m_SO_Plz AS SO_Plz,
    @m_SO_Ort AS SO_Ort,
    @m_AN_Nr AS AN_Nr,
    @m_AN_Bez1 AS AN_Bez1,
    @m_AN_Bez2 AS AN_Bez2,
    @m_WM_Bearb AS WM_Bearb
GO

-- ---------------------------------------------------------------------------------------------------------------

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_WartungsAuftraege_Lookup_Key00')
  DROP PROCEDURE [dbo].[up_First_WartungsAuftraege_Lookup_Key00]
GO
CREATE PROCEDURE [dbo].[up_First_WartungsAuftraege_Lookup_Key00] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_WartungsAuftraege] WITH(INDEX = Ik_Key00) ORDER BY Ident,RecId
GO

-- ---------------------------------------------------------------------------------------------------------------

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_WartungsAuftraege_Lookup_Key00')
  DROP PROCEDURE [dbo].[up_FindF_WartungsAuftraege_Lookup_Key00]
GO
CREATE PROCEDURE [dbo].[up_FindF_WartungsAuftraege_Lookup_Key00] @xKey char(250), @xLen integer AS
  DECLARE @mIdent integer = dbo.itcfunc_KeyToken_integer(@xKey,@xLen,1,8)

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_WartungsAuftraege] WITH(INDEX = Ik_Key00) WHERE 
      Ident = @mIdent
      ORDER BY Ident,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
