
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WOMessDaten')
	DROP PROCEDURE dbo.crp_WOMessDaten
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WWTermine')
	DROP PROCEDURE dbo.crp_WWTermine
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WMTermine')
	DROP PROCEDURE dbo.crp_WMTermine
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WMeldungen')
	DROP PROCEDURE dbo.crp_WMeldungen
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WMeldungsZeilen')
	DROP PROCEDURE dbo.crp_WMeldungsZeilen
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WStammBlatt')
	DROP PROCEDURE dbo.crp_WStammBlatt
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WSTodo')
	DROP PROCEDURE dbo.crp_WSTodo
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAPosten')
	DROP PROCEDURE dbo.crp_WAPosten
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAPostenEx')
	DROP PROCEDURE dbo.crp_WAPostenEx
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WADaten')
	DROP PROCEDURE dbo.crp_WADaten
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WSDaten')
	DROP PROCEDURE dbo.crp_WSDaten
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAMessMuster')
	DROP PROCEDURE dbo.crp_WAMessMuster
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WSMessMuster')
	DROP PROCEDURE dbo.crp_WSMessMuster
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAMeldungsZeilen')
	DROP PROCEDURE dbo.crp_WAMeldungsZeilen
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WSText')
	DROP PROCEDURE dbo.crp_WSText
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAuftragsText')
	DROP PROCEDURE dbo.crp_WAuftragsText
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WStapelAuftrag')
	DROP PROCEDURE dbo.crp_WStapelAuftrag
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WFAnlagen')
	DROP PROCEDURE dbo.crp_WFAnlagen
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAuftrag')
	DROP PROCEDURE dbo.crp_WAuftrag
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAuftragEx')
	DROP PROCEDURE dbo.crp_WAuftragEx
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAuftragExEx')
	DROP PROCEDURE dbo.crp_WAuftragExEx
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WBestand')
	DROP PROCEDURE dbo.crp_WBestand
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WBestandEx')
	DROP PROCEDURE dbo.crp_WBestandEx
GO



CREATE PROCEDURE dbo.crp_WOMessDaten
				@pIdent varchar(8)
AS
SET @pIdent = REPLACE(@pIdent,'.','')

DECLARE @AnlageIdent      int
DECLARE @Inbetriebnahme   varchar(30)
DECLARE @LetzteWartung    varchar(30)
DECLARE @VorletzteWartung varchar(30)
DECLARE @Datum1           varchar(30)
DECLARE @Datum2           varchar(30)
DECLARE @Datum3           varchar(30)

DECLARE @Opt9911          integer
SET @Opt9911 = ISNULL((SELECT TOP 1 O.OptDataI FROM dbo.Optionen O WHERE O.OptId = ' 9911'),0)

CREATE TABLE dbo.#table_MessDaten( AnlIdent int, LfdNr int, Bez varchar(40), 
                                   Datum1 varchar(30), Datum2 varchar(30), Datum3 varchar(30) )
IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
BEGIN
  EXECUTE('
            ALTER TABLE dbo.#table_MessDaten ALTER COLUMN Bez      varchar(40) COLLATE database_default 
            ALTER TABLE dbo.#table_MessDaten ALTER COLUMN Datum1   varchar(30) COLLATE database_default 
            ALTER TABLE dbo.#table_MessDaten ALTER COLUMN Datum2   varchar(30) COLLATE database_default 
            ALTER TABLE dbo.#table_MessDaten ALTER COLUMN Datum3   varchar(30) COLLATE database_default 
         ')
END

IF @Opt9911 = 1 BEGIN

DECLARE WAC CURSOR FOR 
  SELECT WA.Ident 
  FROM  dbo.WartungsObjekte WO
  INNER JOIN dbo.WartungsAnlagen WA
          ON WO.Ident = WA.ObjektId
  WHERE WO.Ident = @pIdent 
  ORDER BY WA.Ident
                  
OPEN WAC
FETCH NEXT FROM WAC
INTO @AnlageIdent
WHILE @@FETCH_STATUS = 0
BEGIN    
  SET @Inbetriebnahme   = ISNULL((SELECT TOP 1 CONVERT(varchar(30),M.Datum,104) 
                                FROM dbo.WartungsMessDaten M 
                                WHERE M.AnlagenId = @AnlageIdent AND M.Inbetriebnahme = 1 
                                ORDER BY M.Datum DESC), '')
  SET @LetzteWartung    = ISNULL((SELECT TOP 1 CONVERT(varchar(30),M.Datum,104) 
                                FROM dbo.WartungsMessDaten M 
                                WHERE M.AnlagenId = @AnlageIdent AND M.Inbetriebnahme <> 1 
                                ORDER BY M.Datum DESC), '')
  SET @VorletzteWartung = ISNULL((SELECT TOP 1 CONVERT(varchar(30),M.Datum,104) 
                                FROM dbo.WartungsMessDaten M 
                                WHERE M.AnlagenId = @AnlageIdent AND M.Inbetriebnahme <> 1 AND 
                                      CONVERT(varchar(30),M.Datum,104) <> @LetzteWartung 
                                ORDER BY M.Datum DESC), '')
  SET @Datum1 = @Inbetriebnahme
  SET @Datum2 = @VorletzteWartung
  SET @Datum3 = @LetzteWartung
  IF @VorletzteWartung = ''
  BEGIN
      SET @Datum2 = @LetzteWartung
      SET @Datum3 = ''
  END

  INSERT INTO dbo.#table_MessDaten( AnlIdent, LfdNr, Bez, Datum1, Datum2, Datum3 )
    SELECT DISTINCT
       @AnlageIdent ,WM.LfdNr ,WM.Bez ,@Datum1 ,@Datum2 ,@Datum3
    FROM dbo.WartungsAnlagen WA
    LEFT JOIN dbo.WartungsTypenDaten WM
           ON WA.AnlTyp = WM.Ident
    WHERE WA.ObjektId = @pIdent AND WA.Ident = @AnlageIdent
    ORDER BY WM.LfdNr

  FETCH NEXT FROM WAC
  INTO @AnlageIdent
END
CLOSE WAC
DEALLOCATE WAC

SELECT
  WO.Ident   AS WO_Ident   ,WO.Nr       AS WO_Nr       ,WO.VertragId  AS WO_VertragId
 ,WO.Bez1    AS WO_Bez1    ,WO.Bez2     AS WO_Bez2     ,WO.KdNr       AS WO_KdNr
 ,WO.KdNrRng AS WO_KdNrRng ,WO.Standort AS WO_Standort ,WO.HausMstr   AS WO_HausMstr  
 ,WO.Mieter  AS WO_Mieter  ,WO.GeoDaten AS WO_GeoDaten ,WO.Entfernung AS WO_Entfernung

 ,WA.Ident      AS WA_Ident      ,WA.Nr        AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2      AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte    AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit   AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter    AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng
 ,WA.LRechDatum AS WA_LRechDatum ,WA.RechDatum AS WA_RechDatum

 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Klein = 1) ,0)    AS Anlage_Pauschale_Klein
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Gross = 1) ,0)    AS Anlage_Pauschale_Gross
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Pauschal = 1) ,0) AS Anlage_Pauschale_Rechnung

 ,MD.LfdNr AS MD_LfdNr 
 ,MD.Bez   AS MD_Bez 

 ,ISNULL((SELECT TOP 1 M.Sollwert
          FROM dbo.WartungsMessDaten M 
          WHERE M.AnlagenId = WA.Ident AND M.Bez = MD.Bez), '')  AS MD_Sollwert
 ,ISNULL((SELECT TOP 1 M.Zusatz
          FROM dbo.WartungsMessDaten M 
          WHERE M.AnlagenId = WA.Ident AND M.Bez = MD.Bez), '')  AS MD_Zusatz

 ,MD.Datum1 AS MD_Datum1 ,MD.Datum2 AS MD_Datum2 ,MD.Datum3 AS MD_Datum3 

 ,ISNULL((SELECT TOP 1 M.Messwert
          FROM dbo.WartungsMessDaten M 
          WHERE M.AnlagenId = WA.Ident AND CONVERT(varchar(30), M.Datum, 104) = MD.Datum1 AND M.Bez = MD.Bez) 
         , '')  AS MD_Wert1
 ,ISNULL((SELECT TOP 1 M.Messwert
          FROM dbo.WartungsMessDaten M 
          WHERE M.AnlagenId = WA.Ident AND CONVERT(varchar(30), M.Datum, 104) = MD.Datum2 AND M.Bez = MD.Bez) 
         , '')  AS MD_Wert2
 ,ISNULL((SELECT TOP 1 M.Messwert
          FROM dbo.WartungsMessDaten M 
          WHERE M.AnlagenId = WA.Ident AND CONVERT(varchar(30), M.Datum, 104) = MD.Datum3 AND M.Bez = MD.Bez) 
         , '')  AS MD_Wert3

 ,ISNULL(A.Anrede    ,'') AS A_Anrede
 ,ISNULL(A.VorName   ,'') AS A_Vorname 
 ,ISNULL(A.Name1     ,'') AS A_Name1  
 ,ISNULL(A.Name2     ,'') AS A_Name2 
 ,ISNULL(A.Strasse   ,'') AS A_Strasse 
 ,ISNULL(A.Ort       ,'') AS A_Ort    
 ,ISNULL(A.Land      ,'') AS A_Land 
 ,ISNULL(A.Plz       ,'') AS A_Plz     
 ,ISNULL(A.Telefon1  ,'') AS A_Telefon     
 ,ISNULL(A.Telefon2  ,'') AS A_Telefon2     
 ,ISNULL(A.Telefon3  ,'') AS A_Telefon3    
 ,ISNULL(A.FaxNummer ,'') AS A_Fax     
 ,ISNULL(A.EMail     ,'') AS A_EMail     

 ,ISNULL(S.VorName   ,'') AS S_Vorname 
 ,ISNULL(S.Name1     ,'') AS S_Name1  
 ,ISNULL(S.Name2     ,'') AS S_Name2 
 ,ISNULL(S.Strasse   ,'') AS S_Strasse 
 ,ISNULL(S.Ort       ,'') AS S_Ort    
 ,ISNULL(S.Land      ,'') AS S_Land 
 ,ISNULL(S.Plz       ,'') AS S_Plz     
 ,ISNULL(S.Telefon1  ,'') AS S_Telefon     
 ,ISNULL(S.Telefon2  ,'') AS S_Telefon2     
 ,ISNULL(S.Telefon3  ,'') AS S_Telefon3    
 ,ISNULL(S.FaxNummer ,'') AS S_Fax     
 ,ISNULL(S.EMail     ,'') AS S_EMail     

 ,AN.Name1       AS F_Name1       ,AN.Name2      AS F_Name2      ,AN.Name3     AS F_Name3
 ,AN.Strasse     AS F_Strasse     ,AN.PlzOrt     AS F_PlzOrt     ,AN.Telefon   AS F_Telefon
 ,AN.FaxNr       AS F_Fax         ,AN.Bank1_Name AS F_Bank1_Name ,AN.Bank1_BLZ AS F_Bank1_Blz
 ,AN.Bank1_Konto AS F_Bank1_Konto ,AN.Bank2_Name AS F_Bank2_Name ,AN.Bank2_BLZ AS F_Bank2_Blz
 ,AN.Bank2_Konto AS F_Bank2_Konto

FROM dbo.WartungsObjekte WO
LEFT JOIN dbo.WartungsAnlagen WA
       ON WO.Ident = WA.ObjektId
LEFT JOIN dbo.#table_MessDaten MD
       ON WA.Ident = MD.AnlIdent
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE WO.Ident = @pIdent
ORDER BY WA.Ident, MD.LfdNr

END
ELSE BEGIN

DECLARE WAC CURSOR FOR 
  SELECT WA.Ident 
  FROM  dbo.WartungsObjekte WO
  INNER JOIN dbo.WartungsAnlagen WA
          ON WO.Ident = WA.ObjektId
  WHERE WO.Ident = @pIdent 
  ORDER BY WA.Ident
                  
OPEN WAC
FETCH NEXT FROM WAC
INTO @AnlageIdent
WHILE @@FETCH_STATUS = 0
BEGIN    
  SET @Inbetriebnahme   = ISNULL((SELECT TOP 1 CONVERT(varchar(30),M.Datum,104) 
                                FROM dbo.WartungsMessDaten M 
                                WHERE M.AnlagenId = @AnlageIdent AND M.Inbetriebnahme = 1 
                                ORDER BY M.Datum DESC), '')
  SET @LetzteWartung    = ISNULL((SELECT TOP 1 CONVERT(varchar(30),M.Datum,104) 
                                FROM dbo.WartungsMessDaten M 
                                WHERE M.AnlagenId = @AnlageIdent AND M.Inbetriebnahme <> 1 
                                ORDER BY M.Datum DESC), '')
  SET @VorletzteWartung = ISNULL((SELECT TOP 1 CONVERT(varchar(30),M.Datum,104) 
                                FROM dbo.WartungsMessDaten M 
                                WHERE M.AnlagenId = @AnlageIdent AND M.Inbetriebnahme <> 1 AND 
                                      CONVERT(varchar(30),M.Datum,104) <> @LetzteWartung 
                                ORDER BY M.Datum DESC), '')
  SET @Datum1 = @Inbetriebnahme
  SET @Datum2 = @VorletzteWartung
  SET @Datum3 = @LetzteWartung

  INSERT INTO dbo.#table_MessDaten( AnlIdent, LfdNr, Bez, Datum1, Datum2, Datum3 )
    SELECT DISTINCT
       @AnlageIdent ,WM.LfdNr ,WM.Bez ,@Datum1 ,@Datum2 ,@Datum3
    FROM dbo.WartungsAnlagen WA
    LEFT JOIN dbo.WartungsMessDaten WM
           ON WA.Ident = WM.AnlagenId AND CONVERT(varchar(30),WM.Datum,104) = @Datum3
    WHERE WA.ObjektId = @pIdent AND WA.Ident = @AnlageIdent
    ORDER BY WM.LfdNr

  FETCH NEXT FROM WAC
  INTO @AnlageIdent
END
CLOSE WAC
DEALLOCATE WAC

SELECT
  WO.Ident   AS WO_Ident   ,WO.Nr       AS WO_Nr       ,WO.VertragId  AS WO_VertragId
 ,WO.Bez1    AS WO_Bez1    ,WO.Bez2     AS WO_Bez2     ,WO.KdNr       AS WO_KdNr
 ,WO.KdNrRng AS WO_KdNrRng ,WO.Standort AS WO_Standort ,WO.HausMstr   AS WO_HausMstr  
 ,WO.Mieter  AS WO_Mieter  ,WO.GeoDaten AS WO_GeoDaten ,WO.Entfernung AS WO_Entfernung

 ,WA.Ident      AS WA_Ident      ,WA.Nr        AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2      AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte    AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit   AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter    AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng
 ,WA.LRechDatum AS WA_LRechDatum ,WA.RechDatum AS WA_RechDatum

 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Klein = 1) ,0)    AS Anlage_Pauschale_Klein
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Gross = 1) ,0)    AS Anlage_Pauschale_Gross
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Pauschal = 1) ,0) AS Anlage_Pauschale_Rechnung

 ,MD.LfdNr AS MD_LfdNr 
 ,MD.Bez   AS MD_Bez 

 ,ISNULL((SELECT TOP 1 M.Sollwert
          FROM dbo.WartungsMessDaten M 
          WHERE M.AnlagenId = WA.Ident AND CONVERT(varchar(30),M.Datum,104) = MD.Datum3 AND M.LfdNr = MD.LfdNr), '')  AS MD_Sollwert
 ,ISNULL((SELECT TOP 1 M.Zusatz
          FROM dbo.WartungsMessDaten M 
          WHERE M.AnlagenId = WA.Ident AND CONVERT(varchar(30),M.Datum,104) = MD.Datum3 AND M.LfdNr = MD.LfdNr), '')  AS MD_Zusatz

 ,MD.Datum1 AS MD_Datum1 ,MD.Datum2 AS MD_Datum2 ,MD.Datum3 AS MD_Datum3 

 ,ISNULL((SELECT TOP 1 M.Messwert
          FROM dbo.WartungsMessDaten M 
          WHERE M.AnlagenId = WA.Ident AND CONVERT(varchar(30),M.Datum,104) = MD.Datum1 AND M.LfdNr = MD.LfdNr) 
         , '')  AS MD_Wert1
 ,ISNULL((SELECT TOP 1 M.Messwert
          FROM dbo.WartungsMessDaten M 
          WHERE M.AnlagenId = WA.Ident AND CONVERT(varchar(30),M.Datum,104) = MD.Datum2 AND M.LfdNr = MD.LfdNr) 
         , '')  AS MD_Wert2
 ,ISNULL((SELECT TOP 1 M.Messwert
          FROM dbo.WartungsMessDaten M 
          WHERE M.AnlagenId = WA.Ident AND CONVERT(varchar(30),M.Datum,104) = MD.Datum3 AND M.LfdNr = MD.LfdNr) 
         , '')  AS MD_Wert3

 ,ISNULL(A.Anrede    ,'') AS A_Anrede
 ,ISNULL(A.VorName   ,'') AS A_Vorname 
 ,ISNULL(A.Name1     ,'') AS A_Name1  
 ,ISNULL(A.Name2     ,'') AS A_Name2 
 ,ISNULL(A.Strasse   ,'') AS A_Strasse 
 ,ISNULL(A.Ort       ,'') AS A_Ort    
 ,ISNULL(A.Land      ,'') AS A_Land 
 ,ISNULL(A.Plz       ,'') AS A_Plz     
 ,ISNULL(A.Telefon1  ,'') AS A_Telefon     
 ,ISNULL(A.Telefon2  ,'') AS A_Telefon2     
 ,ISNULL(A.Telefon3  ,'') AS A_Telefon3    
 ,ISNULL(A.FaxNummer ,'') AS A_Fax     
 ,ISNULL(A.EMail     ,'') AS A_EMail     

 ,ISNULL(S.VorName   ,'') AS S_Vorname 
 ,ISNULL(S.Name1     ,'') AS S_Name1  
 ,ISNULL(S.Name2     ,'') AS S_Name2 
 ,ISNULL(S.Strasse   ,'') AS S_Strasse 
 ,ISNULL(S.Ort       ,'') AS S_Ort    
 ,ISNULL(S.Land      ,'') AS S_Land 
 ,ISNULL(S.Plz       ,'') AS S_Plz     
 ,ISNULL(S.Telefon1  ,'') AS S_Telefon     
 ,ISNULL(S.Telefon2  ,'') AS S_Telefon2     
 ,ISNULL(S.Telefon3  ,'') AS S_Telefon3    
 ,ISNULL(S.FaxNummer ,'') AS S_Fax     
 ,ISNULL(S.EMail     ,'') AS S_EMail     

 ,AN.Name1       AS F_Name1       ,AN.Name2      AS F_Name2      ,AN.Name3     AS F_Name3
 ,AN.Strasse     AS F_Strasse     ,AN.PlzOrt     AS F_PlzOrt     ,AN.Telefon   AS F_Telefon
 ,AN.FaxNr       AS F_Fax         ,AN.Bank1_Name AS F_Bank1_Name ,AN.Bank1_BLZ AS F_Bank1_Blz
 ,AN.Bank1_Konto AS F_Bank1_Konto ,AN.Bank2_Name AS F_Bank2_Name ,AN.Bank2_BLZ AS F_Bank2_Blz
 ,AN.Bank2_Konto AS F_Bank2_Konto

FROM dbo.WartungsObjekte WO
LEFT JOIN dbo.WartungsAnlagen WA
       ON WO.Ident = WA.ObjektId
LEFT JOIN dbo.#table_MessDaten MD
       ON WA.Ident = MD.AnlIdent
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE WO.Ident = @pIdent
ORDER BY WA.Ident, MD.LfdNr

END


GO






CREATE PROCEDURE dbo.crp_WWTermine
				 @VonObj   varchar(20)
				,@BisObj   varchar(20)
				,@VonKnd   varchar(15)
				,@BisKnd   varchar(15)
				,@VonMont  varchar(4)
				,@BisMont  varchar(4)
				,@VonDatum varchar(10)
				,@BisDatum varchar(10)
				,@VonAbr   varchar(10)
				,@BisAbr   varchar(10)
AS
DECLARE @m_VonObj   int
DECLARE @m_BisObj   int
DECLARE @m_VonKnd   int
DECLARE @m_BisKnd   int
DECLARE @m_VonMont  int
DECLARE @m_BisMont  int
DECLARE @m_VonDatum varchar(10)
DECLARE @m_BisDatum varchar(10)
DECLARE @m_VonAbr   varchar(10)
DECLARE @m_BisAbr   varchar(10)

SET @m_VonObj   = 0
SET @m_BisObj   = 0
SET @m_VonKnd   = 0
SET @m_BisKnd   = 0
SET @m_VonMont  = 0
SET @m_BisMont  = 0
SET @m_VonDatum = 'NULL'
SET @m_BisDatum = 'NULL'
SET @m_VonAbr   = 'NULL'
SET @m_BisAbr   = 'NULL'

IF @VonObj IS NOT NULL AND LEN(@VonObj) > 0 AND @VonObj <> 'NULL'
    SET @m_VonObj = 1
IF @BisObj IS NOT NULL AND LEN(@BisObj) > 0 AND @BisObj <> 'NULL'
    SET @m_BisObj = 1

IF @VonKnd IS NOT NULL AND LEN(@VonKnd) > 0 AND @VonKnd <> 'NULL'
    SET @m_VonKnd = 1
IF @BisKnd IS NOT NULL AND LEN(@BisKnd) > 0 AND @BisKnd <> 'NULL'
    SET @m_BisKnd = 1

IF @VonMont IS NOT NULL AND LEN(@VonMont) > 0 AND @VonMont <> 'NULL'
    SET @m_VonMont = 1
IF @BisMont IS NOT NULL AND LEN(@BisMont) > 0 AND @BisMont <> 'NULL'
    SET @m_BisMont = 1

IF @VonDatum IS NOT NULL AND LEN(@VonDatum) > 0 AND ISDATE(@VonDatum) = 1
    SET @m_VonDatum = SUBSTRING(@VonDatum,7,4)+'.'+SUBSTRING(@VonDatum,4,2)+'.'+SUBSTRING(@VonDatum,1,2) 
IF @BisDatum IS NOT NULL AND LEN(@BisDatum) > 0 AND ISDATE(@BisDatum) = 1
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4)+'.'+SUBSTRING(@BisDatum,4,2)+'.'+SUBSTRING(@BisDatum,1,2) 

IF @VonAbr IS NOT NULL AND LEN(@VonAbr) > 0 AND ISDATE(@VonAbr) = 1
    SET @m_VonAbr = SUBSTRING(@VonAbr,7,4)+'.'+SUBSTRING(@VonAbr,4,2)+'.'+SUBSTRING(@VonAbr,1,2) 
IF @BisAbr IS NOT NULL AND LEN(@BisAbr) > 0 AND ISDATE(@BisAbr) = 1
    SET @m_BisAbr = SUBSTRING(@BisAbr,7,4)+'.'+SUBSTRING(@BisAbr,4,2)+'.'+SUBSTRING(@BisAbr,1,2) 

SELECT

  WO.Ident   AS WO_Ident   ,WO.Nr       AS WO_Nr       ,WO.VertragId  AS WO_VertragId
 ,WO.Bez1    AS WO_Bez1    ,WO.Bez2     AS WO_Bez2     ,WO.KdNr       AS WO_KdNr
 ,WO.KdNrRng AS WO_KdNrRng ,WO.Standort AS WO_Standort ,WO.HausMstr   AS WO_HausMstr  
 ,WO.Mieter  AS WO_Mieter  ,WO.GeoDaten AS WO_GeoDaten ,WO.Entfernung AS WO_Entfernung

 ,WA.Ident      AS WA_Ident      ,WA.Nr        AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2      AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte    AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit   AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter    AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng
 ,WA.LRechDatum AS WA_LRechDatum ,WA.RechDatum AS WA_RechDatum
 
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Klein = 1) ,0)    AS Anlage_Pauschale_Klein
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Gross = 1) ,0)    AS Anlage_Pauschale_Gross
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Pauschal = 1) ,0) AS Anlage_Pauschale_Rechnung

 ,ISNULL(A.Anrede    ,'') AS A_Anrede
 ,ISNULL(A.VorName   ,'') AS A_Vorname 
 ,ISNULL(A.Name1     ,'') AS A_Name1  
 ,ISNULL(A.Name2     ,'') AS A_Name2 
 ,ISNULL(A.Strasse   ,'') AS A_Strasse 
 ,ISNULL(A.Ort       ,'') AS A_Ort    
 ,ISNULL(A.Land      ,'') AS A_Land 
 ,ISNULL(A.Plz       ,'') AS A_Plz     
 ,ISNULL(A.Telefon1  ,'') AS A_Telefon     
 ,ISNULL(A.Telefon2  ,'') AS A_Telefon2     
 ,ISNULL(A.Telefon3  ,'') AS A_Telefon3    
 ,ISNULL(A.FaxNummer ,'') AS A_Fax     
 ,ISNULL(A.EMail     ,'') AS A_EMail     

 ,ISNULL(S.VorName   ,'') AS S_Vorname 
 ,ISNULL(S.Name1     ,'') AS S_Name1  
 ,ISNULL(S.Name2     ,'') AS S_Name2 
 ,ISNULL(S.Strasse   ,'') AS S_Strasse 
 ,ISNULL(S.Ort       ,'') AS S_Ort    
 ,ISNULL(S.Land      ,'') AS S_Land 
 ,ISNULL(S.Plz       ,'') AS S_Plz     
 ,ISNULL(S.Telefon1  ,'') AS S_Telefon     
 ,ISNULL(S.Telefon2  ,'') AS S_Telefon2     
 ,ISNULL(S.Telefon3  ,'') AS S_Telefon3    
 ,ISNULL(S.FaxNummer ,'') AS S_Fax     
 ,ISNULL(S.EMail     ,'') AS S_EMail     

 ,ISNULL((SELECT TOP 1 M.Name1 FROM dbo.Adressen M WHERE M.DESTTYP = ' 4' AND M.Annr = WA.Monteur),'') AS M_Name1

 ,AN.Name1       AS F_Name1       ,AN.Name2      AS F_Name2      ,AN.Name3     AS F_Name3
 ,AN.Strasse     AS F_Strasse     ,AN.PlzOrt     AS F_PlzOrt     ,AN.Telefon   AS F_Telefon
 ,AN.FaxNr       AS F_Fax         ,AN.Bank1_Name AS F_Bank1_Name ,AN.Bank1_BLZ AS F_Bank1_Blz
 ,AN.Bank1_Konto AS F_Bank1_Konto ,AN.Bank2_Name AS F_Bank2_Name ,AN.Bank2_BLZ AS F_Bank2_Blz
 ,AN.Bank2_Konto AS F_Bank2_Konto

FROM dbo.WartungsObjekte WO
LEFT JOIN dbo.WartungsAnlagen WA
       ON WO.Ident = WA.ObjektId
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (@m_VonObj = 0 OR WO.Nr >= @VonObj) AND
      (@m_BisObj = 0 OR WO.Nr <= @BisObj) AND
      (@m_VonKnd = 0 OR WO.KdNr >= @VonKnd) AND
      (@m_BisKnd = 0 OR WO.KdNr <= @BisKnd) AND 
      (@m_VonMont = 0 OR WA.Monteur >= @VonMont) AND
      (@m_BisMont = 0 OR WA.Monteur <= @BisMont) AND
      (@m_VonDatum = 'NULL' OR CONVERT(varchar(10),WA.Naechste,102)  >= @m_VonDatum) AND
      (@m_BisDatum = 'NULL' OR CONVERT(varchar(10),WA.Naechste,102)  <= @m_BisDatum) AND
      (@m_VonAbr   = 'NULL' OR CONVERT(varchar(10),WA.RechDatum,102) >= @m_VonAbr)   AND
      (@m_BisAbr   = 'NULL' OR CONVERT(varchar(10),WA.RechDatum,102) <= @m_BisAbr) 
GO





CREATE PROCEDURE dbo.crp_WMTermine
				 @VonMont  varchar(4)
				,@BisMont  varchar(4)
				,@VonObj   varchar(20)
				,@BisObj   varchar(20)
				,@VonDatum varchar(10)
				,@BisDatum varchar(10)
AS
DECLARE @m_VonMont  int
DECLARE @m_BisMont  int
DECLARE @m_VonObj   int
DECLARE @m_BisObj   int
DECLARE @m_VonDatum varchar(10)
DECLARE @m_BisDatum varchar(10)

SET @m_VonMont  = 0
SET @m_BisMont  = 0
SET @m_VonObj   = 0
SET @m_BisObj   = 0
SET @m_VonDatum = 'NULL'
SET @m_BisDatum = 'NULL'

IF @VonMont IS NOT NULL AND LEN(@VonMont) > 0 AND @VonMont <> 'NULL'
    SET @m_VonMont = 1
IF @BisMont IS NOT NULL AND LEN(@BisMont) > 0 AND @BisMont <> 'NULL'
    SET @m_BisMont = 1

IF @VonObj IS NOT NULL AND LEN(@VonObj) > 0 AND @VonObj <> 'NULL'
    SET @m_VonObj = 1
IF @BisObj IS NOT NULL AND LEN(@BisObj) > 0 AND @BisObj <> 'NULL'
    SET @m_BisObj = 1

IF @VonDatum IS NOT NULL AND LEN(@VonDatum) > 0 AND ISDATE(@VonDatum) = 1
    SET @m_VonDatum = SUBSTRING(@VonDatum,7,4)+'.'+SUBSTRING(@VonDatum,4,2)+'.'+SUBSTRING(@VonDatum,1,2) 
IF @BisDatum IS NOT NULL AND LEN(@BisDatum) > 0 AND ISDATE(@BisDatum) = 1
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4)+'.'+SUBSTRING(@BisDatum,4,2)+'.'+SUBSTRING(@BisDatum,1,2) 

SELECT

  WO.Ident   AS WO_Ident   ,WO.Nr       AS WO_Nr       ,WO.VertragId  AS WO_VertragId
 ,WO.Bez1    AS WO_Bez1    ,WO.Bez2     AS WO_Bez2     ,WO.KdNr       AS WO_KdNr
 ,WO.KdNrRng AS WO_KdNrRng ,WO.Standort AS WO_Standort ,WO.HausMstr   AS WO_HausMstr  
 ,WO.Mieter  AS WO_Mieter  ,WO.GeoDaten AS WO_GeoDaten ,WO.Entfernung AS WO_Entfernung

 ,WA.Ident      AS WA_Ident      ,WA.Nr        AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2      AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte    AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit   AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter    AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng
 ,WA.LRechDatum AS WA_LRechDatum ,WA.RechDatum AS WA_RechDatum

 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Klein = 1) ,0)    AS Anlage_Pauschale_Klein
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Gross = 1) ,0)    AS Anlage_Pauschale_Gross
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Pauschal = 1) ,0) AS Anlage_Pauschale_Rechnung

 ,'' AS WM_Datum  ,'' AS WM_Monteur  ,'' AS WM_Bearbeiter
 ,0  AS WM_Status ,0  AS WM_Dringend ,'' AS WM_Wiedervorl
 ,'' AS WM_Termin ,'' AS WM_Uhrzeit

 ,ISNULL(A.Anrede    ,'') AS A_Anrede
 ,ISNULL(A.VorName   ,'') AS A_Vorname 
 ,ISNULL(A.Name1     ,'') AS A_Name1  
 ,ISNULL(A.Name2     ,'') AS A_Name2 
 ,ISNULL(A.Strasse   ,'') AS A_Strasse 
 ,ISNULL(A.Ort       ,'') AS A_Ort    
 ,ISNULL(A.Land      ,'') AS A_Land 
 ,ISNULL(A.Plz       ,'') AS A_Plz     
 ,ISNULL(A.Telefon1  ,'') AS A_Telefon     
 ,ISNULL(A.Telefon2  ,'') AS A_Telefon2     
 ,ISNULL(A.Telefon3  ,'') AS A_Telefon3    
 ,ISNULL(A.FaxNummer ,'') AS A_Fax     
 ,ISNULL(A.EMail     ,'') AS A_EMail     

 ,ISNULL(S.VorName   ,'') AS S_Vorname 
 ,ISNULL(S.Name1     ,'') AS S_Name1  
 ,ISNULL(S.Name2     ,'') AS S_Name2 
 ,ISNULL(S.Strasse   ,'') AS S_Strasse 
 ,ISNULL(S.Ort       ,'') AS S_Ort    
 ,ISNULL(S.Land      ,'') AS S_Land 
 ,ISNULL(S.Plz       ,'') AS S_Plz     
 ,ISNULL(S.Telefon1  ,'') AS S_Telefon     
 ,ISNULL(S.Telefon2  ,'') AS S_Telefon2     
 ,ISNULL(S.Telefon3  ,'') AS S_Telefon3    
 ,ISNULL(S.FaxNummer ,'') AS S_Fax     
 ,ISNULL(S.EMail     ,'') AS S_EMail     

 ,WB.Termin AS Auftrag_Termin ,WB.Uhrzeit AS Auftrag_Uhrzeit ,WB.Monteur AS Auftrag_Monteur   
 ,ISNULL((SELECT TOP 1 M.Name1 FROM dbo.Adressen M WHERE M.DESTTYP = ' 4' AND M.Annr = WA.Monteur),'') AS Monteur_Name1
 ,WB.Art AS Auftrag_Typ ,WB.Nr AS Auftrag_Nr ,'NULL' AS _NULL

 ,AN.Name1       AS F_Name1       ,AN.Name2      AS F_Name2      ,AN.Name3     AS F_Name3
 ,AN.Strasse     AS F_Strasse     ,AN.PlzOrt     AS F_PlzOrt     ,AN.Telefon   AS F_Telefon
 ,AN.FaxNr       AS F_Fax         ,AN.Bank1_Name AS F_Bank1_Name ,AN.Bank1_BLZ AS F_Bank1_Blz
 ,AN.Bank1_Konto AS F_Bank1_Konto ,AN.Bank2_Name AS F_Bank2_Name ,AN.Bank2_BLZ AS F_Bank2_Blz
 ,AN.Bank2_Konto AS F_Bank2_Konto

FROM dbo.WartungsObjekte WO
LEFT JOIN dbo.WartungsAnlagen WA
       ON WO.Ident = WA.ObjektId
LEFT JOIN dbo.WartungsAuftraege WB
       ON WA.Ident = WB.AnlagenId
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE (WB.Status = 1) AND
      (@m_VonObj  = 0 OR WO.Nr >= @VonObj)       AND
      (@m_BisObj  = 0 OR WO.Nr <= @BisObj)       AND
      (@m_VonMont = 0 OR WB.Monteur >= @VonMont) AND
      (@m_BisMont = 0 OR WB.Monteur <= @BisMont) AND
      (@m_VonDatum = 'NULL' OR CONVERT(varchar(10),WB.Termin,102) >= @m_VonDatum) AND
      (@m_BisDatum = 'NULL' OR CONVERT(varchar(10),WB.Termin,102) <= @m_BisDatum) 
GO


CREATE PROCEDURE dbo.crp_WMeldungen
				 @Status    varchar(1)
				,@Vorgem    varchar(1)
				,@VonDatum  varchar(10)
				,@BisDatum  varchar(10)
AS
DECLARE @m_Status    int
DECLARE @m_Vorgem    int
DECLARE @m_VonDatum  varchar(10)
DECLARE @m_BisDatum  varchar(10)

SET @m_Status   = 0
SET @m_Vorgem   = 0
SET @m_VonDatum = 'NULL'
SET @m_BisDatum = 'NULL'

IF @VonDatum IS NOT NULL AND LEN(@VonDatum) > 0 AND ISDATE(@VonDatum) = 1
    SET @m_VonDatum = SUBSTRING(@VonDatum,7,4)+'.'+SUBSTRING(@VonDatum,4,2)+'.'+SUBSTRING(@VonDatum,1,2) 
IF @BisDatum IS NOT NULL AND LEN(@BisDatum) > 0 AND ISDATE(@BisDatum) = 1
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4)+'.'+SUBSTRING(@BisDatum,4,2)+'.'+SUBSTRING(@BisDatum,1,2) 

SET @m_Status = CASE 
                  WHEN @Status = '1' THEN 1
                  WHEN @Status = '2' THEN 2
                  WHEN @Status = '3' THEN 3
                  WHEN @Status = '4' THEN 4
                  WHEN @Status = '5' THEN 5
                  ELSE 0
                END
SET @m_Vorgem = CASE 
                  WHEN @Vorgem = '1' THEN 1
                  ELSE 0
                END

SELECT

  WM.Datum  AS WM_Datum  ,WM.Monteur   AS WM_Monteur   ,WM.Bearbeiter AS WM_Bearbeiter
 ,WM.Status AS WM_Status ,WM.Dringend  AS WM_Dringend  ,WM.Wiedervorl AS WM_Wiedervorl
 ,WM.Termin AS WM_Termin ,WM.Uhrzeit   AS WM_Uhrzeit   ,WM.KzStoerung AS WM_KzStoerung
 ,WM.Dauer  AS WM_Dauer  ,WM.AnlagenId AS WM_AnlagenId ,WM.AuftragId  AS WM_AuftragId
 ,WM.LfdNr  AS WM_LfdNr

 ,WAU.Nr     AS WAU_Nr     ,WAU.PrjNr   AS WAU_PrjNr   ,WAU.Datum   AS WAU_Datum
 ,WAU.BelNr  AS WAU_BelNr  ,WAU.BelArt  AS WAU_BelArt  ,WAU.Status  AS WAU_Status
 ,WAU.Termin AS WAU_Termin ,WAU.Uhrzeit AS WAU_Uhrzeit ,WAU.Monteur AS WAU_Monteur
 ,WAU.Art    AS WAU_Art    ,WAU.Ident   AS WAU_Ident

 ,WO.Ident   AS WO_Ident   ,WO.Nr       AS WO_Nr       ,WO.VertragId  AS WO_VertragId
 ,WO.Bez1    AS WO_Bez1    ,WO.Bez2     AS WO_Bez2     ,WO.KdNr       AS WO_KdNr
 ,WO.KdNrRng AS WO_KdNrRng ,WO.Standort AS WO_Standort ,WO.HausMstr   AS WO_HausMstr  
 ,WO.Mieter  AS WO_Mieter  ,WO.GeoDaten AS WO_GeoDaten ,WO.Entfernung AS WO_Entfernung

 ,WA.Ident      AS WA_Ident      ,WA.Nr         AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2       AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte     AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit    AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter     AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng
 ,WA.RechDatum  AS WA_Abrechnung ,WA.LRechDatum AS WA_LAbrechnung
 
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WM.AnlagenId AND T.Klein = 1) ,0)    AS Anlage_Pauschale_Klein
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WM.AnlagenId AND T.Gross = 1) ,0)    AS Anlage_Pauschale_Gross
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WM.AnlagenId AND T.Pauschal = 1) ,0) AS Anlage_Pauschale_Rechnung

 ,ISNULL(A.Anrede    ,'') AS K_Anrede
 ,ISNULL(A.VorName   ,'') AS K_Vorname 
 ,ISNULL(A.Name1     ,'') AS K_Name1  
 ,ISNULL(A.Name2     ,'') AS K_Name2 
 ,ISNULL(A.Strasse   ,'') AS K_Strasse 
 ,ISNULL(A.Ort       ,'') AS K_Ort    
 ,ISNULL(A.Land      ,'') AS K_Land 
 ,LTRIM(RTRIM(SUBSTRING(ISNULL(A.Plz,'')+ SPACE(8),1,8))) AS K_Plz     
 ,ISNULL(A.Telefon1  ,'') AS K_Telefon1     
 ,ISNULL(A.Telefon2  ,'') AS K_Telefon2     
 ,ISNULL(A.Telefon3  ,'') AS K_Telefon3    
 ,ISNULL(A.FaxNummer ,'') AS K_Fax     
 ,ISNULL(A.EMail     ,'') AS K_EMail     
 ,ISNULL(A.DESTDEBNR ,'') AS K_Nr     

 ,ISNULL(S.VorName   ,'') AS S_Vorname 
 ,ISNULL(S.Name1     ,'') AS S_Name1  
 ,ISNULL(S.Name2     ,'') AS S_Name2 
 ,ISNULL(S.Strasse   ,'') AS S_Strasse 
 ,ISNULL(S.Ort       ,'') AS S_Ort    
 ,ISNULL(S.Land      ,'') AS S_Land 
 ,LTRIM(RTRIM(SUBSTRING(ISNULL(S.Plz,'')+ SPACE(8),1,8))) AS S_Plz     
 ,ISNULL(S.Telefon1  ,'') AS S_Telefon1     
 ,ISNULL(S.Telefon2  ,'') AS S_Telefon2     
 ,ISNULL(S.Telefon3  ,'') AS S_Telefon3    
 ,ISNULL(S.FaxNummer ,'') AS S_Fax     
 ,ISNULL(S.EMail     ,'') AS S_EMail     

 ,ISNULL((SELECT TOP 1 M.Name1 FROM dbo.Adressen M WHERE M.DESTTYP = ' 4' AND M.Annr = WA.Monteur),'') AS M_Name1

 ,AN.Name1       AS F_Name1       ,AN.Name2      AS F_Name2      ,AN.Name3     AS F_Name3
 ,AN.Strasse     AS F_Strasse     ,AN.PlzOrt     AS F_PlzOrt     ,AN.Telefon   AS F_Telefon
 ,AN.FaxNr       AS F_Fax         ,AN.Bank1_Name AS F_Bank1_Name ,AN.Bank1_BLZ AS F_Bank1_Blz
 ,AN.Bank1_Konto AS F_Bank1_Konto ,AN.Bank2_Name AS F_Bank2_Name ,AN.Bank2_BLZ AS F_Bank2_Blz
 ,AN.Bank2_Konto AS F_Bank2_Konto

FROM dbo.WartungsMeldungen WM
LEFT JOIN dbo.WartungsAnlagen WA
       ON WA.Ident = WM.AnlagenId
LEFT JOIN dbo.WartungsObjekte WO
       ON WO.Ident = WA.ObjektId
LEFT JOIN dbo.WartungsAuftraege WAU
       ON WAU.Ident = WM.AuftragId
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (@m_Status = 0  OR  WM.Status     = @m_Status)  AND
      (@m_Vorgem = 0  OR  WM.Vorgemerkt = @m_Vorgem)  AND
      (@m_VonDatum = 'NULL' OR CONVERT(varchar(10),WM.Termin,102) >= @m_VonDatum) AND
      (@m_BisDatum = 'NULL' OR CONVERT(varchar(10),WM.Termin,102) <= @m_BisDatum) 
GO



CREATE PROCEDURE dbo.crp_WMeldungsZeilen
			            @pIdent  varchar(8)
                                   ,@pDatum  varchar(10)
			           ,@pLfd    varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  SET @pLfd   = REPLACE(@pLfd,'.','')
  DECLARE @m_Datum datetime
  SET @m_Datum = GETDATE()
  IF ISDATE(@pDatum) = 1
      SET @m_Datum = CONVERT(datetime, @pDatum)

  SELECT
    WZ.AnlagenId AS WZ_Ident ,WZ.Datum AS WZ_Datum ,WZ.LfdNr AS WZ_LfdNr ,WZ.Bemerkung AS WZ_Bemerkung
  FROM dbo.WartungsMeldungen WM
  LEFT JOIN dbo.WartungsMeldungsZeilen WZ
         ON WM.AnlagenId = WZ.AnlagenId AND WM.Datum = WZ.Datum AND WM.LfdNr = WZ.MeldLfdNr
  WHERE WM.AnlagenId = @pIdent AND WM.Datum = @m_Datum AND WM.LfdNr = @pLfd
GO



CREATE PROCEDURE dbo.crp_WStammBlatt
			    @pIdent  varchar(8)
AS

SET @pIdent = REPLACE(@pIdent,'.','')
SELECT
  WO.Ident   AS WO_Ident   ,WO.Nr       AS WO_Nr       ,WO.VertragId  AS WO_VertragId
 ,WO.Bez1    AS WO_Bez1    ,WO.Bez2     AS WO_Bez2     ,WO.KdNr       AS WO_KdNr
 ,WO.KdNrRng AS WO_KdNrRng ,WO.Standort AS WO_Standort ,WO.HausMstr   AS WO_HausMstr  
 ,WO.Mieter  AS WO_Mieter  ,WO.GeoDaten AS WO_GeoDaten ,WO.Entfernung AS WO_Entfernung

 ,WA.Ident      AS WA_Ident      ,WA.Nr         AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2       AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte     AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit    AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.AnzBT      AS WA_AnzBT   ,WA.AnzMD     AS WA_AnzMD
 ,WA.KzCheck    AS WA_KzCheck    ,WA.Mieter     AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng
 ,WA.RechDatum  AS WA_Abrechnung ,WA.LRechDatum AS WA_LAbrechnung

 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Klein = 1) ,0)    AS Anlage_Pauschale_Klein
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Gross = 1) ,0)    AS Anlage_Pauschale_Gross
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Pauschal = 1) ,0) AS Anlage_Pauschale_Rechnung

 ,ISNULL((SELECT TOP 1 T.Nr FROM dbo.WartungsTypen T WHERE T.Ident = WA.AnlTyp)  ,'')  AS WA_Typ

 ,ISNULL(A.Anrede    ,'') AS A_Anrede
 ,ISNULL(A.VorName   ,'') AS A_Vorname 
 ,ISNULL(A.Name1     ,'') AS A_Name1  
 ,ISNULL(A.Name2     ,'') AS A_Name2 
 ,ISNULL(A.Strasse   ,'') AS A_Strasse 
 ,ISNULL(A.Ort       ,'') AS A_Ort    
 ,ISNULL(A.Land      ,'') AS A_Land 
 ,ISNULL(A.Plz       ,'') AS A_Plz     
 ,ISNULL(A.Telefon1  ,'') AS A_Telefon     
 ,ISNULL(A.Telefon2  ,'') AS A_Telefon2     
 ,ISNULL(A.Telefon3  ,'') AS A_Telefon3    
 ,ISNULL(A.FaxNummer ,'') AS A_Fax     
 ,ISNULL(A.EMail     ,'') AS A_EMail     

 ,ISNULL(S.VorName   ,'') AS S_Vorname 
 ,ISNULL(S.Name1     ,'') AS S_Name1  
 ,ISNULL(S.Name2     ,'') AS S_Name2 
 ,ISNULL(S.Strasse   ,'') AS S_Strasse 
 ,ISNULL(S.Ort       ,'') AS S_Ort    
 ,ISNULL(S.Land      ,'') AS S_Land 
 ,ISNULL(S.Plz       ,'') AS S_Plz     
 ,ISNULL(S.Telefon1  ,'') AS S_Telefon     
 ,ISNULL(S.Telefon2  ,'') AS S_Telefon2     
 ,ISNULL(S.Telefon3  ,'') AS S_Telefon3    
 ,ISNULL(S.FaxNummer ,'') AS S_Fax     
 ,ISNULL(S.EMail     ,'') AS S_EMail     

 ,ISNULL(H.VorName   ,'') AS H_Vorname 
 ,ISNULL(H.Name1     ,'') AS H_Name1  
 ,ISNULL(H.Name2     ,'') AS H_Name2 
 ,ISNULL(H.Strasse   ,'') AS H_Strasse 
 ,ISNULL(H.Ort       ,'') AS H_Ort    
 ,ISNULL(H.Land      ,'') AS H_Land 
 ,ISNULL(H.Plz       ,'') AS H_Plz     
 ,ISNULL(H.Telefon1  ,'') AS H_Telefon     
 ,ISNULL(H.Telefon2  ,'') AS H_Telefon2     
 ,ISNULL(H.Telefon3  ,'') AS H_Telefon3    
 ,ISNULL(H.FaxNummer ,'') AS H_Fax     

 ,ISNULL(M.VorName   ,'') AS M_Vorname 
 ,ISNULL(M.Name1     ,'') AS M_Name1  
 ,ISNULL(M.Name2     ,'') AS M_Name2 
 ,ISNULL(M.Strasse   ,'') AS M_Strasse 
 ,ISNULL(M.Ort       ,'') AS M_Ort    
 ,ISNULL(M.Land      ,'') AS M_Land 
 ,ISNULL(M.Plz       ,'') AS M_Plz     
 ,ISNULL(M.Telefon1  ,'') AS M_Telefon     
 ,ISNULL(M.Telefon2  ,'') AS M_Telefon2     
 ,ISNULL(M.Telefon3  ,'') AS M_Telefon3    
 ,ISNULL(M.FaxNummer ,'') AS M_Fax     

 ,ISNULL(R.Anrede    ,'') AS R_Anrede
 ,ISNULL(R.VorName   ,'') AS R_Vorname 
 ,ISNULL(R.Name1     ,'') AS R_Name1  
 ,ISNULL(R.Name2     ,'') AS R_Name2 
 ,ISNULL(R.Strasse   ,'') AS R_Strasse 
 ,ISNULL(R.Ort       ,'') AS R_Ort    
 ,ISNULL(R.Land      ,'') AS R_Land 
 ,ISNULL(R.Plz       ,'') AS R_Plz     
 ,ISNULL(R.Telefon1  ,'') AS R_Telefon     
 ,ISNULL(R.Telefon2  ,'') AS R_Telefon2     
 ,ISNULL(R.Telefon3  ,'') AS R_Telefon3    
 ,ISNULL(R.FaxNummer ,'') AS R_Fax     

 ,ISNULL((SELECT TOP 1 M.Name1 FROM dbo.Adressen M WHERE M.DESTTYP = ' 4' AND M.Annr = WA.Monteur),'') AS MO_Name1

 ,ISNULL((SELECT TOP 1 O.OptDataI FROM dbo.Optionen O WHERE O.OptId = ' 9901'),'') AS OPT_9901

 ,ISNULL((SELECT TOP 1 V.Nr FROM dbo.WartungsVertraege V WHERE V.Ident = WO.VertragId),'') AS Vertrag_Nr

 ,AN.Name1       AS F_Name1       ,AN.Name2      AS F_Name2      ,AN.Name3     AS F_Name3
 ,AN.Strasse     AS F_Strasse     ,AN.PlzOrt     AS F_PlzOrt     ,AN.Telefon   AS F_Telefon
 ,AN.FaxNr       AS F_Fax         ,AN.Bank1_Name AS F_Bank1_Name ,AN.Bank1_BLZ AS F_Bank1_Blz
 ,AN.Bank1_Konto AS F_Bank1_Konto ,AN.Bank2_Name AS F_Bank2_Name ,AN.Bank2_BLZ AS F_Bank2_Blz
 ,AN.Bank2_Konto AS F_Bank2_Konto

FROM dbo.WartungsAnlagen WA
LEFT JOIN dbo.WartungsObjekte WO
       ON WA.ObjektId = WO.Ident
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT  JOIN dbo.Adressen H
        ON WO.HausMstr = H.DESTDEBNR AND WO.HausMstr <> ''
LEFT  JOIN dbo.Adressen M
        ON WA.Mieter = M.DESTDEBNR AND WA.Mieter <> ''
LEFT  JOIN dbo.Adressen R
        ON WA.KdNrRng = R.DESTDEBNR AND WA.KdNrRng <> ''
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE WA.Ident = @pIdent
GO




CREATE PROCEDURE dbo.crp_WSTodo
			@pIdent  varchar(8)
AS
SET @pIdent = REPLACE(@pIdent,'.','')
SELECT
  WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter  AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng

 ,WT.PosNr  AS WT_PosNr  ,WT.Gross   AS WT_Gross  ,WT.Klein   AS WT_Klein
 ,WT.Bedarf AS WT_Bedarf ,WT.Menge   AS WT_Menge  ,WT.Einheit AS WT_Einheit
 ,WT.Lkz    AS WT_Lkz    ,WT.ArtNr   AS WT_ArtNr  ,WT.Bez1    AS WT_Bez1
 ,WT.Bez2   AS WT_Bez2   ,WT.Merkmal AS WT_Merkmal

FROM dbo.WartungsAnlagen WA
LEFT JOIN dbo.WartungsTodo WT
       ON WA.Ident = WT.AnlagenId
WHERE WA.Ident = @pIdent
GO



CREATE PROCEDURE dbo.crp_WAPosten
			@pIdent  varchar(8)
AS
SET @pIdent = REPLACE(@pIdent,'.','')
SELECT
  WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.KzCheck AS WA_KzCheck
 ,WA.Mieter     AS WA_Mieter     ,WA.KdNrRng AS WA_KdNrRng

 ,WT.LfdNr   AS WT_LfdNr   ,WT.Grund   AS WT_Grund  ,WT.Menge   AS WT_Menge  
 ,WT.Einheit AS WT_Einheit ,WT.Lkz     AS WT_Lkz    ,WT.ArtNr   AS WT_ArtNr  
 ,WT.Bez1    AS WT_Bez1    ,WT.Bez2    AS WT_Bez2   ,WT.Merkmal AS WT_Merkmal
 ,WT.Minuten AS WT_Minuten ,WT.VkLohn  AS WT_VkLohn ,WT.VkMat   AS WT_VkMat
 ,WT.[Check] AS WT_Check

FROM dbo.WartungsAuftraege W
LEFT JOIN dbo.WartungsPosten WT
       ON W.Ident = WT.AuftragId
LEFT JOIN dbo.WartungsAnlagen WA
       ON W.AnlagenId = WA.Ident
WHERE W.Ident = @pIdent
GO




CREATE PROCEDURE dbo.crp_WAPostenEx
			@pIdent  varchar(8)
AS
SET @pIdent = REPLACE(@pIdent,'.','')
SELECT
  WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.KzCheck AS WA_KzCheck
 ,WA.Mieter     AS WA_Mieter     ,WA.KdNrRng AS WA_KdNrRng

 ,WT.LfdNr    AS WT_LfdNr   ,WT.Grund     AS WT_Grund  ,WT.Menge    AS WT_Menge  
 ,WT.Einheit  AS WT_Einheit ,WT.Lkz       AS WT_Lkz    ,WT.ArtNr    AS WT_ArtNr  
 ,WT.Bez1     AS WT_Bez1    ,WT.Bez2      AS WT_Bez2   ,WT.Merkmal  AS WT_Merkmal
 ,WT.Minuten  AS WT_Minuten ,WT.VkLohn    AS WT_VkLohn ,WT.VkMat    AS WT_VkMat
 ,WT.[Check]  AS WT_Check   ,WT.LohnJN    AS WT_LohnJN ,WT.MatJN    AS WT_MatJN
 ,WT.RabattVH AS WT_RabVH   ,WT.RabattEUR AS WT_RabEUR ,WT.Pauschal AS WT_Pau

 ,ISNULL(S.Kzmwst,0) AS S_KzMwst

 ,ISNULL((SELECT TOP 1 O.OptDataD FROM Optionen O WHERE OptId = ' 7003'), 0) AS O_Mwst1
 ,ISNULL((SELECT TOP 1 O.OptDataD FROM Optionen O WHERE OptId = ' 7004'), 0) AS O_Mwst2
 ,ISNULL((SELECT TOP 1 O.OptDataD FROM Optionen O WHERE OptId = ' 7005'), 0) AS O_Mwst3
 ,ISNULL((SELECT TOP 1 O.OptDataC FROM Optionen O WHERE OptId = ' 7008'), 0) AS O_Wz

FROM dbo.WartungsAuftraege W
LEFT JOIN dbo.WartungsPosten WT
       ON W.Ident = WT.AuftragId
LEFT JOIN dbo.WartungsAnlagen WA
       ON W.AnlagenId = WA.Ident
LEFT JOIN dbo.Stammpositionen S
       ON S.Lkz = WT.Lkz AND S.Artnr = WT.ArtNr
WHERE W.Ident = @pIdent
GO




CREATE PROCEDURE dbo.crp_WADaten
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  SELECT
    WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
   ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
   ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
   ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
   ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter  AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng

   ,WP.PosNr AS WP_PosNr ,WP.Menge   AS WP_Menge  ,WP.Einheit AS WP_Einheit
   ,WP.Lkz   AS WP_Lkz   ,WP.ArtNr   AS WP_ArtNr  ,WP.Bez1    AS WP_Bez1
   ,WP.Bez2  AS WP_Bez2  ,WP.Merkmal AS WP_Merkmal

   ,WA.Dimension1  AS WD_Bez1  ,WA.Dimension2  AS WD_Bez2   ,WA.Dimension3  AS WD_Bez3
   ,WA.Dimension4  AS WD_Bez4  ,WA.Dimension5  AS WD_Bez5

   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.Bez = WA.Dimension1),  '') AS WD_Wert1
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.Bez = WA.Dimension2),  '') AS WD_Wert2
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.Bez = WA.Dimension3),  '') AS WD_Wert3
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.Bez = WA.Dimension4),  '') AS WD_Wert4
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.Bez = WA.Dimension5),  '') AS WD_Wert5

  FROM dbo.WartungsAuftraege W
  LEFT JOIN dbo.WartungsAnlagen WA
         ON W.AnlagenId = WA.Ident
  LEFT JOIN dbo.WartungsPositionen WP
         ON WA.Ident = WP.AnlagenId
  WHERE W.Ident = @pIdent
  ORDER BY WP.PosNr
GO



CREATE PROCEDURE dbo.crp_WSDaten
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  SELECT
    WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
   ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
   ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
   ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
   ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter  AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng

   ,WP.PosNr AS WP_PosNr ,WP.Menge   AS WP_Menge  ,WP.Einheit AS WP_Einheit
   ,WP.Lkz   AS WP_Lkz   ,WP.ArtNr   AS WP_ArtNr  ,WP.Bez1    AS WP_Bez1
   ,WP.Bez2  AS WP_Bez2  ,WP.Merkmal AS WP_Merkmal

   ,WA.Dimension1  AS WD_Bez1  ,WA.Dimension2  AS WD_Bez2   ,WA.Dimension3  AS WD_Bez3
   ,WA.Dimension4  AS WD_Bez4  ,WA.Dimension5  AS WD_Bez5

   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.Bez = WA.Dimension1),  '') AS WD_Wert1
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.Bez = WA.Dimension2),  '') AS WD_Wert2
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.Bez = WA.Dimension3),  '') AS WD_Wert3
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.Bez = WA.Dimension4),  '') AS WD_Wert4
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.Bez = WA.Dimension5),  '') AS WD_Wert5

  FROM dbo.WartungsAnlagen WA
  LEFT JOIN dbo.WartungsPositionen WP
         ON WA.Ident = WP.AnlagenId
  WHERE WA.Ident = @pIdent
  ORDER BY WP.PosNr
GO




CREATE PROCEDURE dbo.crp_WAMessMuster
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')

  DECLARE @Opt9911          integer
  SET @Opt9911 = ISNULL((SELECT TOP 1 O.OptDataI FROM dbo.Optionen O WHERE O.OptId = ' 9911'),0)

  IF @Opt9911 = 1 BEGIN

  SELECT
    WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
   ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
   ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
   ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
   ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter  AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng

   ,WTD.LfdNr  AS WTD_LfdNr    
   ,WTD.Bez    AS WTD_Bez

   ,ISNULL((SELECT TOP 1 WMD.Sollwert FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WTD_Sollwert

   ,ISNULL((SELECT TOP 1 WMD.Zusatz FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WTD_Zusatz

   ,ISNULL((SELECT TOP 1 WMD.Datum FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident ORDER BY WMD.Datum DESC)
          , '')  AS WMD_Datum

   ,ISNULL((SELECT TOP 1 WMD.Messwert FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WMD_Messwert

  FROM dbo.WartungsAuftraege W
  LEFT JOIN dbo.WartungsAnlagen WA
         ON W.AnlagenId = WA.Ident
  LEFT JOIN (SELECT DISTINCT LfdNr,Bez,Ident FROM dbo.WartungsTypenDaten) WTD
         ON WTD.Ident = WA.AnlTyp
  WHERE W.Ident = @pIdent

  END
  ELSE BEGIN

  DECLARE @mAnlIdent integer
  SET @mAnlIdent = ISNULL((SELECT TOP 1 W.AnlagenId FROM dbo.WartungsAuftraege W WHERE W.Ident = @pIdent), 0)

  DECLARE @mDatum varchar(30)
  SET @mDatum = ISNULL((SELECT TOP 1 CONVERT(varchar(30),M.Datum,104) 
                        FROM dbo.WartungsMessDaten M 
                        WHERE M.AnlagenId = @mAnlIdent 
                        ORDER BY M.Datum DESC), '')

  SELECT
    WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
   ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
   ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
   ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
   ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter  AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng

   ,WTD.LfdNr  AS WTD_LfdNr
   ,WTD.Bez    AS WTD_Bez

   ,ISNULL((SELECT TOP 1 WMD.Sollwert FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WTD_Sollwert

   ,ISNULL((SELECT TOP 1 WMD.Zusatz FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WTD_Zusatz

   ,ISNULL((SELECT TOP 1 WMD.Datum FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident ORDER BY WMD.Datum DESC)
          , '')  AS WMD_Datum

   ,ISNULL((SELECT TOP 1 WMD.Messwert FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WMD_Messwert

  FROM dbo.WartungsAuftraege W
  LEFT JOIN dbo.WartungsAnlagen WA
         ON W.AnlagenId = WA.Ident
  LEFT JOIN (SELECT DISTINCT LfdNr,Bez,AnlagenId FROM dbo.WartungsMessDaten
             WHERE CONVERT(varchar(30),Datum,104) = @mDatum) WTD
         ON WTD.AnlagenId = WA.Ident
  WHERE W.Ident = @pIdent

  END
GO





CREATE PROCEDURE dbo.crp_WSMessMuster
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')

  DECLARE @Opt9911          integer
  SET @Opt9911 = ISNULL((SELECT TOP 1 O.OptDataI FROM dbo.Optionen O WHERE O.OptId = ' 9911'),0)

  IF @Opt9911 = 1 BEGIN

  SELECT
    WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
   ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
   ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
   ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
   ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter  AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng

   ,ISNULL((SELECT TOP 1 WMD.LfdNr FROM dbo.WartungsMessDaten WMD 
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , 0)   AS WTD_LfdNr    

   ,WTD.Bez    AS WTD_Bez

   ,ISNULL((SELECT TOP 1 WMD.Sollwert FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WTD_Sollwert

   ,ISNULL((SELECT TOP 1 WMD.Zusatz FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WTD_Zusatz

   ,ISNULL((SELECT TOP 1 WMD.Datum FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident ORDER BY WMD.Datum DESC)
          , '')  AS WMD_Datum

   ,ISNULL((SELECT TOP 1 WMD.Messwert FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WMD_Messwert

  FROM dbo.WartungsAnlagen WA
  LEFT JOIN (SELECT DISTINCT Bez, Ident FROM dbo.WartungsTypenDaten) WTD
         ON WTD.Ident = WA.AnlTyp
  WHERE WA.Ident = @pIdent
  
  END
  ELSE BEGIN

  DECLARE @mDatum varchar(30)
  SET @mDatum = ISNULL((SELECT TOP 1 CONVERT(varchar(30),M.Datum,104) 
                        FROM dbo.WartungsMessDaten M 
                        WHERE M.AnlagenId = @pIdent 
                        ORDER BY M.Datum DESC), '')

  SELECT
    WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
   ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
   ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
   ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
   ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter  AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng

   ,ISNULL((SELECT TOP 1 WMD.LfdNr FROM dbo.WartungsMessDaten WMD 
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , 0)   AS WTD_LfdNr    

   ,WTD.Bez    AS WTD_Bez

   ,ISNULL((SELECT TOP 1 WMD.Sollwert FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WTD_Sollwert

   ,ISNULL((SELECT TOP 1 WMD.Zusatz FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WTD_Zusatz

   ,ISNULL((SELECT TOP 1 WMD.Datum FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident ORDER BY WMD.Datum DESC)
          , '')  AS WMD_Datum

   ,ISNULL((SELECT TOP 1 WMD.Messwert FROM dbo.WartungsMessDaten WMD
            WHERE WMD.AnlagenId = WA.Ident AND WMD.Bez = WTD.Bez ORDER BY WMD.Datum DESC)
          , '')  AS WMD_Messwert

  FROM dbo.WartungsAnlagen WA
  LEFT JOIN (SELECT DISTINCT Bez, AnlagenId FROM dbo.WartungsMessDaten
             WHERE CONVERT(varchar(30),Datum,104) = @mDatum) WTD
         ON WTD.AnlagenId = WA.Ident
  WHERE WA.Ident = @pIdent

  END
GO




CREATE PROCEDURE dbo.crp_WAMeldungsZeilen
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  DECLARE @Letzte datetime
  SET @Letzte = (SELECT TOP 1 WM.Datum FROM dbo.WartungsMeldungen WM 
                 WHERE WM.AuftragId = @pIdent ORDER BY WM.Datum DESC)

  SELECT
    WZ.AnlagenId AS WZ_Ident ,WZ.Datum AS WZ_Datum ,WZ.LfdNr AS WZ_LfdNr ,WZ.Bemerkung AS WZ_Bemerkung
  FROM dbo.WartungsAuftraege W
  LEFT JOIN dbo.WartungsMeldungen WM
         ON W.Ident = WM.AuftragId
  LEFT JOIN dbo.WartungsMeldungsZeilen WZ
         ON WM.AnlagenId = WZ.AnlagenId AND WM.Datum = WZ.Datum
  WHERE W.Ident = @pIdent AND WM.Datum = @Letzte
GO



CREATE PROCEDURE dbo.crp_WSText
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  SELECT L.Daten
  FROM dbo.Langtextzeilen L
  WHERE L.Art = '25' AND L.Lkz = 'AT' AND L.Nr = 'ANL' + STR(@pIdent, 15)
GO



CREATE PROCEDURE dbo.crp_WAuftragsText
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  SELECT L.LfdNr, L.Daten
  FROM dbo.Langtextzeilen L
  WHERE L.Art = '25' AND L.Lkz = 'AU' AND L.Nr = 'AUF' + STR(@pIdent, 15)
GO




CREATE PROCEDURE dbo.crp_WStapelAuftrag
			    @pIdent  varchar(8)
AS
SET @pIdent = REPLACE(@pIdent,'.','')

CREATE TABLE dbo.#tmp_WStapelAuftrag (Ident int)

IF @pIdent = '0' OR @pIdent = ''
BEGIN
    INSERT INTO dbo.#tmp_WStapelAuftrag
    SELECT W.Ident 
    FROM dbo.WartungsMeldungen WM
    INNER JOIN dbo.WartungsAuftraege W
            ON WM.AuftragId = W.Ident
    WHERE WM.KzMark = 1 AND WM.AuftragId <> 0
END
ELSE
BEGIN
    INSERT INTO dbo.#tmp_WStapelAuftrag
    SELECT W.Ident
    FROM dbo.WartungsAuftraege W
    WHERE W.Ident = @pIdent
END


SELECT
  W.Ident AS W_Ident

 ,WO.Ident   AS WO_Ident   ,WO.Nr       AS WO_Nr       ,WO.VertragId  AS WO_VertragId
 ,WO.Bez1    AS WO_Bez1    ,WO.Bez2     AS WO_Bez2     ,WO.KdNr       AS WO_KdNr
 ,WO.KdNrRng AS WO_KdNrRng ,WO.Standort AS WO_Standort ,WO.HausMstr   AS WO_HausMstr  
 ,WO.Mieter  AS WO_Mieter  ,WO.GeoDaten AS WO_GeoDaten ,WO.Entfernung AS WO_Entfernung

 ,WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.AnzBT   AS WA_AnzBT   ,WA.AnzMD     AS WA_AnzMD
 ,WA.KzCheck    AS WA_KzCheck    ,WA.Mieter  AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng

 ,ISNULL((SELECT TOP 1 T.Nr FROM dbo.WartungsTypen T WHERE T.Ident = WA.AnlTyp)  ,'')  AS WA_Typ

 ,ISNULL((SELECT TOP 1 A.Anrede    FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Anrede
 ,ISNULL((SELECT TOP 1 A.VorName   FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Vorname 
 ,ISNULL((SELECT TOP 1 A.Name1     FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Name1  
 ,ISNULL((SELECT TOP 1 A.Name2     FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Name2 
 ,ISNULL((SELECT TOP 1 A.Strasse   FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Strasse 
 ,ISNULL((SELECT TOP 1 A.Ort       FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Ort    
 ,ISNULL((SELECT TOP 1 A.Land      FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Land 
 ,ISNULL((SELECT TOP 1 A.Plz       FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Plz     
 ,ISNULL((SELECT TOP 1 A.Telefon1  FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Telefon     
 ,ISNULL((SELECT TOP 1 A.Telefon2  FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Telefon2     
 ,ISNULL((SELECT TOP 1 A.Telefon3  FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Telefon3    
 ,ISNULL((SELECT TOP 1 A.FaxNummer FROM dbo.Adressen A WHERE WO.KdNr = A.DESTDEBNR),'') AS A_Fax     

 ,ISNULL((SELECT TOP 1 S.VorName   FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Vorname 
 ,ISNULL((SELECT TOP 1 S.Name1     FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Name1  
 ,ISNULL((SELECT TOP 1 S.Name2     FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Name2 
 ,ISNULL((SELECT TOP 1 S.Strasse   FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Strasse 
 ,ISNULL((SELECT TOP 1 S.Ort       FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Ort    
 ,ISNULL((SELECT TOP 1 S.Land      FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Land 
 ,ISNULL((SELECT TOP 1 S.Plz       FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Plz     
 ,ISNULL((SELECT TOP 1 S.Telefon1  FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Telefon     
 ,ISNULL((SELECT TOP 1 S.Telefon2  FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Telefon2     
 ,ISNULL((SELECT TOP 1 S.Telefon3  FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Telefon3    
 ,ISNULL((SELECT TOP 1 S.FaxNummer FROM dbo.Adressen S WHERE WO.Standort = S.DESTDEBNR),'') AS S_Fax     

 ,ISNULL((SELECT TOP 1 H.VorName   FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Vorname 
 ,ISNULL((SELECT TOP 1 H.Name1     FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Name1  
 ,ISNULL((SELECT TOP 1 H.Name2     FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Name2 
 ,ISNULL((SELECT TOP 1 H.Strasse   FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Strasse 
 ,ISNULL((SELECT TOP 1 H.Ort       FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Ort    
 ,ISNULL((SELECT TOP 1 H.Land      FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Land 
 ,ISNULL((SELECT TOP 1 H.Plz       FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Plz     
 ,ISNULL((SELECT TOP 1 H.Telefon1  FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Telefon     
 ,ISNULL((SELECT TOP 1 H.Telefon2  FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Telefon2     
 ,ISNULL((SELECT TOP 1 H.Telefon3  FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Telefon3    
 ,ISNULL((SELECT TOP 1 H.FaxNummer FROM dbo.Adressen H WHERE WO.HausMstr = H.DESTDEBNR),'') AS H_Fax     

 ,ISNULL((SELECT TOP 1 M.VorName   FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Vorname 
 ,ISNULL((SELECT TOP 1 M.Name1     FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Name1  
 ,ISNULL((SELECT TOP 1 M.Name2     FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Name2 
 ,ISNULL((SELECT TOP 1 M.Strasse   FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Strasse 
 ,ISNULL((SELECT TOP 1 M.Ort       FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Ort    
 ,ISNULL((SELECT TOP 1 M.Land      FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Land 
 ,ISNULL((SELECT TOP 1 M.Plz       FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Plz     
 ,ISNULL((SELECT TOP 1 M.Telefon1  FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Telefon     
 ,ISNULL((SELECT TOP 1 M.Telefon2  FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Telefon2     
 ,ISNULL((SELECT TOP 1 M.Telefon3  FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Telefon3    
 ,ISNULL((SELECT TOP 1 M.FaxNummer FROM dbo.Adressen M WHERE WA.Mieter = M.DESTDEBNR),'') AS M_Fax     

 ,ISNULL((SELECT TOP 1 R.Anrede    FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Anrede
 ,ISNULL((SELECT TOP 1 R.VorName   FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Vorname 
 ,ISNULL((SELECT TOP 1 R.Name1     FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Name1  
 ,ISNULL((SELECT TOP 1 R.Name2     FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Name2 
 ,ISNULL((SELECT TOP 1 R.Strasse   FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Strasse 
 ,ISNULL((SELECT TOP 1 R.Ort       FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Ort    
 ,ISNULL((SELECT TOP 1 R.Land      FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Land 
 ,ISNULL((SELECT TOP 1 R.Plz       FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Plz     
 ,ISNULL((SELECT TOP 1 R.Telefon1  FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Telefon     
 ,ISNULL((SELECT TOP 1 R.Telefon2  FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Telefon2     
 ,ISNULL((SELECT TOP 1 R.Telefon3  FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Telefon3    
 ,ISNULL((SELECT TOP 1 R.FaxNummer FROM dbo.Adressen R WHERE WA.KdNrRng = R.DESTDEBNR),'') AS R_Fax     

 ,ISNULL((SELECT TOP 1 M.Name1 FROM dbo.Adressen M WHERE M.DESTTYP = ' 4' AND M.Annr = W.Monteur),'') AS MO_Name1

 ,ISNULL((SELECT TOP 1 O.OptDataI FROM dbo.Optionen O WHERE O.OptId = ' 9901'),'') AS OPT_9901

 ,W.Nr      AS W_Nr      ,W.Datum   AS W_Datum   ,W.Termin AS W_Termin
 ,W.Uhrzeit AS W_Uhrzeit ,W.Monteur AS W_Monteur ,W.Art    AS W_Art

 ,ISNULL((SELECT TOP 1 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR 
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR 
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END
          FROM dbo.WartungsPosten P 
          WHERE P.AuftragId = W.Ident AND P.Pauschal = 1 ORDER BY P.LfdNr),0) AS W_Pauschale
 ,ISNULL((SELECT TOP 1 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR 
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR 
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END
          FROM dbo.WartungsPosten P 
          WHERE P.AuftragId = W.Ident AND P.Pauschal = 1 ORDER BY P.LfdNr),0) +
  ISNULL((SELECT SUM( 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR 
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR 
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END)
          FROM dbo.WartungsPosten P WHERE P.AuftragId = W.Ident AND P.Pauschal = 0),0) AS W_VkGesamt
 ,ISNULL((SELECT SUM( P.Minuten )
          FROM dbo.WartungsPosten P WHERE P.AuftragId = W.Ident AND P.Pauschal = 0),0) AS W_Zeit

 ,AN.Name1       AS F_Name1       ,AN.Name2      AS F_Name2      ,AN.Name3     AS F_Name3
 ,AN.Strasse     AS F_Strasse     ,AN.PlzOrt     AS F_PlzOrt     ,AN.Telefon   AS F_Telefon
 ,AN.FaxNr       AS F_Fax         ,AN.Bank1_Name AS F_Bank1_Name ,AN.Bank1_BLZ AS F_Bank1_Blz
 ,AN.Bank1_Konto AS F_Bank1_Konto ,AN.Bank2_Name AS F_Bank2_Name ,AN.Bank2_BLZ AS F_Bank2_Blz
 ,AN.Bank2_Konto AS F_Bank2_Konto


FROM dbo.#tmp_WStapelAuftrag T
INNER JOIN dbo.WartungsAuftraege W
        ON W.Ident = T.Ident
LEFT  JOIN dbo.WartungsAnlagen WA
        ON W.AnlagenId = WA.Ident
LEFT  JOIN dbo.WartungsObjekte WO
        ON W.ObjektId = WO.Ident
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE W.Ident = @pIdent
GO




CREATE PROCEDURE dbo.crp_WFAnlagen
                           @pMark varchar(1)
AS

SELECT
  WO.Ident   AS WO_Ident   ,WO.Nr       AS WO_Nr       ,WO.VertragId  AS WO_VertragId
 ,WO.Bez1    AS WO_Bez1    ,WO.Bez2     AS WO_Bez2     ,WO.KdNr       AS WO_KdNr
 ,WO.KdNrRng AS WO_KdNrRng ,WO.Standort AS WO_Standort ,WO.HausMstr   AS WO_HausMstr  
 ,WO.Mieter  AS WO_Mieter  ,WO.GeoDaten AS WO_GeoDaten ,WO.Entfernung AS WO_Entfernung

 ,WA.Ident      AS WA_Ident      ,WA.Nr        AS WA_Nr        ,WA.AnlTyp    AS WA_AnlTyp
 ,WA.Bez1       AS WA_Bez1       ,WA.Bez2      AS WA_Bez2      ,WA.Inbetrieb AS WA_Inbetrieb 
 ,WA.Garantie   AS WA_Garantie   ,WA.Letzte    AS WA_Letzte    ,WA.Naechste  AS WA_Naechste  
 ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit   AS WA_Uhrzeit   ,WA.Monteur   AS WA_Monteur   
 ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.AnzBT     AS WA_AnzBT     ,WA.AnzMD     AS WA_AnzMD
 ,WA.KzCheck    AS WA_KzCheck    ,WA.Mieter    AS WA_Mieter    ,WA.KdNrRng   AS WA_KdNrRng
 ,WA.LRechDatum AS WA_LRechDatum ,WA.RechDatum AS WA_RechDatum

 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Klein = 1) ,0)    AS Anlage_Pauschale_Klein
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Gross = 1) ,0)    AS Anlage_Pauschale_Gross
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = WA.Ident AND T.Pauschal = 1) ,0) AS Anlage_Pauschale_Rechnung

 ,ISNULL((SELECT TOP 1 T.Nr FROM dbo.WartungsTypen T WHERE T.Ident = WA.AnlTyp)  ,'')  AS WA_Typ

 ,ISNULL(A.Anrede    ,'') AS A_Anrede
 ,ISNULL(A.VorName   ,'') AS A_Vorname 
 ,ISNULL(A.Name1     ,'') AS A_Name1  
 ,ISNULL(A.Name2     ,'') AS A_Name2 
 ,ISNULL(A.Strasse   ,'') AS A_Strasse 
 ,ISNULL(A.Ort       ,'') AS A_Ort    
 ,ISNULL(A.Land      ,'') AS A_Land 
 ,ISNULL(A.Plz       ,'') AS A_Plz     
 ,ISNULL(A.Telefon1  ,'') AS A_Telefon     
 ,ISNULL(A.Telefon2  ,'') AS A_Telefon2     
 ,ISNULL(A.Telefon3  ,'') AS A_Telefon3    
 ,ISNULL(A.FaxNummer ,'') AS A_Fax     
 ,ISNULL(A.EMail     ,'') AS A_EMail     

 ,ISNULL(S.VorName   ,'') AS S_Vorname 
 ,ISNULL(S.Name1     ,'') AS S_Name1  
 ,ISNULL(S.Name2     ,'') AS S_Name2 
 ,ISNULL(S.Strasse   ,'') AS S_Strasse 
 ,ISNULL(S.Ort       ,'') AS S_Ort    
 ,ISNULL(S.Land      ,'') AS S_Land 
 ,ISNULL(S.Plz       ,'') AS S_Plz     
 ,ISNULL(S.Telefon1  ,'') AS S_Telefon     
 ,ISNULL(S.Telefon2  ,'') AS S_Telefon2     
 ,ISNULL(S.Telefon3  ,'') AS S_Telefon3    
 ,ISNULL(S.FaxNummer ,'') AS S_Fax     
 ,ISNULL(S.EMail     ,'') AS S_EMail     

 ,ISNULL(H.VorName   ,'') AS H_Vorname 
 ,ISNULL(H.Name1     ,'') AS H_Name1  
 ,ISNULL(H.Name2     ,'') AS H_Name2 
 ,ISNULL(H.Strasse   ,'') AS H_Strasse 
 ,ISNULL(H.Ort       ,'') AS H_Ort    
 ,ISNULL(H.Land      ,'') AS H_Land 
 ,ISNULL(H.Plz       ,'') AS H_Plz     
 ,ISNULL(H.Telefon1  ,'') AS H_Telefon     
 ,ISNULL(H.Telefon2  ,'') AS H_Telefon2     
 ,ISNULL(H.Telefon3  ,'') AS H_Telefon3    
 ,ISNULL(H.FaxNummer ,'') AS H_Fax     

 ,ISNULL(M.VorName   ,'') AS M_Vorname 
 ,ISNULL(M.Name1     ,'') AS M_Name1  
 ,ISNULL(M.Name2     ,'') AS M_Name2 
 ,ISNULL(M.Strasse   ,'') AS M_Strasse 
 ,ISNULL(M.Ort       ,'') AS M_Ort    
 ,ISNULL(M.Land      ,'') AS M_Land 
 ,ISNULL(M.Plz       ,'') AS M_Plz     
 ,ISNULL(M.Telefon1  ,'') AS M_Telefon     
 ,ISNULL(M.Telefon2  ,'') AS M_Telefon2     
 ,ISNULL(M.Telefon3  ,'') AS M_Telefon3    
 ,ISNULL(M.FaxNummer ,'') AS M_Fax     

 ,ISNULL(R.Anrede    ,'') AS R_Anrede
 ,ISNULL(R.VorName   ,'') AS R_Vorname 
 ,ISNULL(R.Name1     ,'') AS R_Name1  
 ,ISNULL(R.Name2     ,'') AS R_Name2 
 ,ISNULL(R.Strasse   ,'') AS R_Strasse 
 ,ISNULL(R.Ort       ,'') AS R_Ort    
 ,ISNULL(R.Land      ,'') AS R_Land 
 ,ISNULL(R.Plz       ,'') AS R_Plz     
 ,ISNULL(R.Telefon1  ,'') AS R_Telefon     
 ,ISNULL(R.Telefon2  ,'') AS R_Telefon2     
 ,ISNULL(R.Telefon3  ,'') AS R_Telefon3    
 ,ISNULL(R.FaxNummer ,'') AS R_Fax     

 ,ISNULL((SELECT TOP 1 M.Name1 FROM dbo.Adressen M WHERE M.DESTTYP = ' 4' AND M.Annr = WA.Monteur),'') AS MO_Name1

 ,ISNULL((SELECT TOP 1 O.OptDataI FROM dbo.Optionen O WHERE O.OptId = ' 9901'),'') AS OPT_9901

 ,ISNULL((SELECT TOP 1 V.Nr FROM dbo.WartungsVertraege V WHERE V.Ident = WO.VertragId),'') AS Vertrag_Nr

 ,AN.Name1       AS F_Name1       ,AN.Name2      AS F_Name2      ,AN.Name3     AS F_Name3
 ,AN.Strasse     AS F_Strasse     ,AN.PlzOrt     AS F_PlzOrt     ,AN.Telefon   AS F_Telefon
 ,AN.FaxNr       AS F_Fax         ,AN.Bank1_Name AS F_Bank1_Name ,AN.Bank1_BLZ AS F_Bank1_Blz
 ,AN.Bank1_Konto AS F_Bank1_Konto ,AN.Bank2_Name AS F_Bank2_Name ,AN.Bank2_BLZ AS F_Bank2_Blz
 ,AN.Bank2_Konto AS F_Bank2_Konto

FROM dbo.WartungsAnlagen WA
LEFT JOIN dbo.WartungsObjekte WO
       ON WA.ObjektId = WO.Ident
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT  JOIN dbo.Adressen H
        ON WO.HausMstr = H.DESTDEBNR AND WO.HausMstr <> ''
LEFT  JOIN dbo.Adressen M
        ON WA.Mieter = M.DESTDEBNR AND WA.Mieter <> ''
LEFT  JOIN dbo.Adressen R
        ON WA.KdNrRng = R.DESTDEBNR AND WA.KdNrRng <> ''
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE @pMark <> '1' Or WA.KzMark = 1
GO






CREATE PROCEDURE dbo.crp_WAuftrag
			    @pIdent  varchar(8)
AS

SET @pIdent = REPLACE(@pIdent,'.','')

CREATE TABLE dbo.#tmp_WAuftrag (Ident int)

IF @pIdent = '0' OR @pIdent = ''
BEGIN
    INSERT INTO dbo.#tmp_WAuftrag
    SELECT W.Ident
    FROM dbo.WartungsMeldungen WM
    INNER JOIN dbo.WartungsAuftraege W
            ON WM.AuftragId = W.Ident
    WHERE WM.KzMark = 1 AND WM.AuftragId <> 0
END
ELSE
BEGIN
    INSERT INTO dbo.#tmp_WAuftrag
    SELECT W.Ident
    FROM dbo.WartungsAuftraege W
    WHERE W.Ident = @pIdent
END


SELECT
  W.Ident AS Auftrag_Ident

 ,WO.Ident   AS Objekt_Ident   ,WO.Nr       AS Objekt_Nr       ,WO.VertragId  AS Objekt_VertragId
 ,WO.Bez1    AS Objekt_Bez1    ,WO.Bez2     AS Objekt_Bez2     ,WO.KdNr       AS Objekt_KdNr
 ,WO.KdNrRng AS Objekt_KdNrRng ,WO.Standort AS Objekt_Standort ,WO.HausMstr   AS Objekt_HausMstr  
 ,WO.Mieter  AS Objekt_Mieter  

 ,(SELECT TOP 1 WM.Bearbeiter FROM WartungsMeldungen WM WHERE WM.AuftragId = W.Ident) AS Objekt_GeoDaten 
 ,WO.Entfernung AS Objekt_Entfernung

 ,WA.Ident      AS Anlage_Ident      ,WA.Nr         AS Anlage_Nr      ,WA.AnlTyp    AS Anlage_AnlTyp
 ,WA.Bez1       AS Anlage_Bez1       ,WA.Bez2       AS Anlage_Bez2    ,WA.Inbetrieb AS Anlage_Inbetrieb 
 ,WA.Garantie   AS Anlage_Garantie   ,WA.Letzte     AS Anlage_Letzte  ,WA.Naechste  AS Anlage_Naechste  
 ,WA.Termin     AS Anlage_Termin     ,WA.Uhrzeit    AS Anlage_Uhrzeit ,WA.Monteur   AS Anlage_Monteur   
 ,WA.Vorgemerkt AS Anlage_Vorgemerkt ,WA.AnzBT      AS Anlage_AnzBT   ,WA.AnzMD     AS Anlage_AnzMD
 ,WA.KzCheck    AS Anlage_KzCheck    ,WA.Mieter     AS Anlage_Mieter  ,WA.KdNrRng   AS Anlage_KdNrRng
 ,WA.RechDatum  AS Anlage_Abrechnung ,WA.LRechDatum AS Anlage_LAbrechnung

 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = W.AnlagenId AND T.Klein = 1) ,0)    AS Anlage_Pauschale_Klein
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = W.AnlagenId AND T.Gross = 1) ,0)    AS Anlage_Pauschale_Gross
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = W.AnlagenId AND T.Pauschal = 1) ,0) AS Anlage_Pauschale_Rechnung

 ,ISNULL((SELECT TOP 1 T.Nr FROM dbo.WartungsTypen T WHERE T.Ident = WA.AnlTyp)  ,'')  AS Anlage_Typ

 ,ISNULL(A.Anrede    ,'') AS Kunde_Anrede
 ,ISNULL(A.VorName   ,'') AS Kunde_Vorname 
 ,ISNULL(A.Name1     ,'') AS Kunde_Name1  
 ,ISNULL(A.Name2     ,'') AS Kunde_Name2 
 ,ISNULL(A.Strasse   ,'') AS Kunde_Strasse 
 ,ISNULL(A.Ort       ,'') AS Kunde_Ort    
 ,ISNULL(A.Land      ,'') AS Kunde_Land 
 ,ISNULL(A.Plz       ,'') AS Kunde_Plz     
 ,ISNULL(A.Telefon1  ,'') AS Kunde_Telefon     
 ,ISNULL(A.Telefon2  ,'') AS Kunde_Telefon2     
 ,ISNULL(A.Telefon3  ,'') AS Kunde_Telefon3    
 ,ISNULL(A.FaxNummer ,'') AS Kunde_Fax     
 ,ISNULL(A.EMail     ,'') AS Kunde_EMail     

 ,ISNULL(S.VorName   ,'') AS Standort_Vorname 
 ,ISNULL(S.Name1     ,'') AS Standort_Name1  
 ,ISNULL(S.Name2     ,'') AS Standort_Name2 
 ,ISNULL(S.Strasse   ,'') AS Standort_Strasse 
 ,ISNULL(S.Ort       ,'') AS Standort_Ort    
 ,ISNULL(S.Land      ,'') AS Standort_Land 
 ,ISNULL(S.Plz       ,'') AS Standort_Plz     
 ,ISNULL(S.Telefon1  ,'') AS Standort_Telefon     
 ,ISNULL(S.Telefon2  ,'') AS Standort_Telefon2     
 ,ISNULL(S.Telefon3  ,'') AS Standort_Telefon3    
 ,ISNULL(S.FaxNummer ,'') AS Standort_Fax     
 ,ISNULL(S.EMail     ,'') AS Standort_EMail     

 ,ISNULL(H.VorName   ,'') AS Hausmeister_Vorname 
 ,ISNULL(H.Name1     ,'') AS Hausmeister_Name1  
 ,ISNULL(H.Name2     ,'') AS Hausmeister_Name2 
 ,ISNULL(H.Strasse   ,'') AS Hausmeister_Strasse 
 ,ISNULL(H.Ort       ,'') AS Hausmeister_Ort    
 ,ISNULL(H.Land      ,'') AS Hausmeister_Land 
 ,ISNULL(H.Plz       ,'') AS Hausmeister_Plz     
 ,ISNULL(H.Telefon1  ,'') AS Hausmeister_Telefon     
 ,ISNULL(H.Telefon2  ,'') AS Hausmeister_Telefon2     
 ,ISNULL(H.Telefon3  ,'') AS Hausmeister_Telefon3    
 ,ISNULL(H.FaxNummer ,'') AS Hausmeister_Fax     

 ,ISNULL(M.VorName   ,'') AS Mieter_Vorname 
 ,ISNULL(M.Name1     ,'') AS Mieter_Name1  
 ,ISNULL(M.Name2     ,'') AS Mieter_Name2 
 ,ISNULL(M.Strasse   ,'') AS Mieter_Strasse 
 ,ISNULL(M.Ort       ,'') AS Mieter_Ort    
 ,ISNULL(M.Land      ,'') AS Mieter_Land 
 ,ISNULL(M.Plz       ,'') AS Mieter_Plz     
 ,ISNULL(M.Telefon1  ,'') AS Mieter_Telefon     
 ,ISNULL(M.Telefon2  ,'') AS Mieter_Telefon2     
 ,ISNULL(M.Telefon3  ,'') AS Mieter_Telefon3    
 ,ISNULL(M.FaxNummer ,'') AS Mieter_Fax     

 ,ISNULL(R.Anrede    ,'') AS Empfaenger_Anrede
 ,ISNULL(R.VorName   ,'') AS Empfaenger_Vorname 
 ,ISNULL(R.Name1     ,'') AS Empfaenger_Name1  
 ,ISNULL(R.Name2     ,'') AS Empfaenger_Name2 
 ,ISNULL(R.Strasse   ,'') AS Empfaenger_Strasse 
 ,ISNULL(R.Ort       ,'') AS Empfaenger_Ort    
 ,ISNULL(R.Land      ,'') AS Empfaenger_Land 
 ,ISNULL(R.Plz       ,'') AS Empfaenger_Plz     
 ,ISNULL(R.Telefon1  ,'') AS Empfaenger_Telefon     
 ,ISNULL(R.Telefon2  ,'') AS Empfaenger_Telefon2     
 ,ISNULL(R.Telefon3  ,'') AS Empfaenger_Telefon3    
 ,ISNULL(R.FaxNummer ,'') AS Empfaenger_Fax     

 ,ISNULL((SELECT TOP 1 LTRIM(RTRIM(RTRIM(M.VorName) +' '+ LTRIM(M.Name1))) FROM dbo.Adressen M 
          WHERE M.DESTTYP = ' 4' AND M.Annr = W.Monteur),'') AS Monteur_Name1

 ,ISNULL((SELECT TOP 1 O.OptDataI FROM dbo.Optionen O WHERE O.OptId = ' 9901'),'') AS OPT_9901

 ,W.Nr      AS Auftrag_Nr      ,W.Datum   AS Auftrag_Datum   ,W.Termin AS Auftrag_Termin
 ,W.Uhrzeit AS Auftrag_Uhrzeit ,W.Monteur AS Auftrag_Monteur ,W.Art    AS Auftrag_Art

 ,ISNULL((SELECT TOP 1 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR 
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR 
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END
          FROM dbo.WartungsPosten P 
          WHERE P.AuftragId = W.Ident AND P.Pauschal = 1 ORDER BY P.LfdNr),0) AS Auftrag_Pauschale
 ,ISNULL((SELECT TOP 1 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END
          FROM dbo.WartungsPosten P 
          WHERE P.AuftragId = W.Ident AND P.Pauschal = 1 ORDER BY P.LfdNr),0) +
  ISNULL((SELECT SUM( 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR 
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR 
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END)
          FROM dbo.WartungsPosten P WHERE P.AuftragId = W.Ident AND P.Pauschal = 0),0) AS Auftrag_VkGesamt
 ,ISNULL((SELECT SUM( P.Minuten )
          FROM dbo.WartungsPosten P WHERE P.AuftragId = W.Ident AND P.Pauschal = 0),0) AS Auftrag_Zeit

 ,AN.Name1       AS EigeneAdresse_Name1       ,AN.Name2      AS EigeneAdresse_Name2      ,AN.Name3     AS EigeneAdresse_Name3
 ,AN.Strasse     AS EigeneAdresse_Strasse     ,AN.PlzOrt     AS EigeneAdresse_PlzOrt     ,AN.Telefon   AS EigeneAdresse_Telefon
 ,AN.FaxNr       AS EigeneAdresse_Fax         ,AN.Bank1_Name AS EigeneAdresse_Bank1_Name ,AN.Bank1_BLZ AS EigeneAdresse_Bank1_Blz
 ,AN.Bank1_Konto AS EigeneAdresse_Bank1_Konto ,AN.Bank2_Name AS EigeneAdresse_Bank2_Name ,AN.Bank2_BLZ AS EigeneAdresse_Bank2_Blz
 ,AN.Bank2_Konto AS EigeneAdresse_Bank2_Konto

FROM dbo.#tmp_WAuftrag T
INNER JOIN dbo.WartungsAuftraege W
        ON W.Ident = T.Ident
LEFT  JOIN dbo.WartungsAnlagen WA
        ON W.AnlagenId = WA.Ident
LEFT  JOIN dbo.WartungsObjekte WO
        ON W.ObjektId = WO.Ident
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT  JOIN dbo.Adressen H
        ON WO.HausMstr = H.DESTDEBNR AND WO.HausMstr <> ''
LEFT  JOIN dbo.Adressen M
        ON WA.Mieter = M.DESTDEBNR AND WA.Mieter <> ''
LEFT  JOIN dbo.Adressen R
        ON WA.KdNrRng = R.DESTDEBNR AND WA.KdNrRng <> ''
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'

GO





CREATE PROCEDURE dbo.crp_WAuftragEx
			    @pIdent  varchar(8)
AS

SET @pIdent = REPLACE(@pIdent,'.','')

CREATE TABLE dbo.#tmp_WAuftrag (Ident int)

IF @pIdent = '0' OR @pIdent = ''
BEGIN
    INSERT INTO dbo.#tmp_WAuftrag
    SELECT W.Ident
    FROM dbo.WartungsMeldungen WM
    INNER JOIN dbo.WartungsAuftraege W
            ON WM.AuftragId = W.Ident
    WHERE WM.KzMark = 1 AND WM.AuftragId <> 0
END
ELSE
BEGIN
    INSERT INTO dbo.#tmp_WAuftrag
    SELECT W.Ident
    FROM dbo.WartungsAuftraege W
    WHERE W.Ident = @pIdent
END


SELECT
  W.Ident AS Auftrag_Ident

 ,WO.Ident   AS Objekt_Ident   ,WO.Nr       AS Objekt_Nr       ,WO.VertragId  AS Objekt_VertragId
 ,WO.Bez1    AS Objekt_Bez1    ,WO.Bez2     AS Objekt_Bez2     ,WO.KdNr       AS Objekt_KdNr
 ,WO.KdNrRng AS Objekt_KdNrRng ,WO.Standort AS Objekt_Standort ,WO.HausMstr   AS Objekt_HausMstr  
 ,WO.Mieter  AS Objekt_Mieter  

 ,(SELECT TOP 1 WM.Bearbeiter FROM WartungsMeldungen WM WHERE WM.AuftragId = W.Ident) AS Objekt_GeoDaten 
 ,WO.Entfernung AS Objekt_Entfernung

 ,WA.Ident      AS Anlage_Ident      ,WA.Nr         AS Anlage_Nr      ,WA.AnlTyp    AS Anlage_AnlTyp
 ,WA.Bez1       AS Anlage_Bez1       ,WA.Bez2       AS Anlage_Bez2    ,WA.Inbetrieb AS Anlage_Inbetrieb 
 ,WA.Garantie   AS Anlage_Garantie   ,WA.Letzte     AS Anlage_Letzte  ,WA.Naechste  AS Anlage_Naechste  
 ,WA.Termin     AS Anlage_Termin     ,WA.Uhrzeit    AS Anlage_Uhrzeit ,WA.Monteur   AS Anlage_Monteur   
 ,WA.Vorgemerkt AS Anlage_Vorgemerkt ,WA.AnzBT      AS Anlage_AnzBT   ,WA.AnzMD     AS Anlage_AnzMD

 ,CASE
    WHEN (0 <> (SELECT COUNT(WP.LfdNr) FROM dbo.WartungsPosten WP WHERE WP.AuftragId=W.Ident AND WP.[Check]=1)) AND
         (0 <> WA.KzCheck)
    THEN 1
    ELSE 0
  END  AS Anlage_KzCheck


 ,WA.Mieter     AS Anlage_Mieter  ,WA.KdNrRng   AS Anlage_KdNrRng
 ,WA.RechDatum  AS Anlage_Abrechnung ,WA.LRechDatum AS Anlage_LAbrechnung

 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = W.AnlagenId AND T.Klein = 1) ,0)    AS Anlage_Pauschale_Klein
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = W.AnlagenId AND T.Gross = 1) ,0)    AS Anlage_Pauschale_Gross
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = W.AnlagenId AND T.Pauschal = 1) ,0) AS Anlage_Pauschale_Rechnung

 ,ISNULL((SELECT TOP 1 T.Nr FROM dbo.WartungsTypen T WHERE T.Ident = WA.AnlTyp)  ,'')  AS Anlage_Typ

 ,ISNULL(A.Anrede    ,'') AS Kunde_Anrede
 ,ISNULL(A.VorName   ,'') AS Kunde_Vorname 
 ,ISNULL(A.Name1     ,'') AS Kunde_Name1  
 ,ISNULL(A.Name2     ,'') AS Kunde_Name2 
 ,ISNULL(A.Strasse   ,'') AS Kunde_Strasse 
 ,ISNULL(A.Ort       ,'') AS Kunde_Ort    
 ,ISNULL(A.Land      ,'') AS Kunde_Land 
 ,ISNULL(A.Plz       ,'') AS Kunde_Plz     
 ,ISNULL(A.Telefon1  ,'') AS Kunde_Telefon     
 ,ISNULL(A.Telefon2  ,'') AS Kunde_Telefon2     
 ,ISNULL(A.Telefon3  ,'') AS Kunde_Telefon3    
 ,ISNULL(A.FaxNummer ,'') AS Kunde_Fax     
 ,ISNULL(A.EMail     ,'') AS Kunde_EMail     

 ,ISNULL(S.VorName   ,'') AS Standort_Vorname 
 ,ISNULL(S.Name1     ,'') AS Standort_Name1  
 ,ISNULL(S.Name2     ,'') AS Standort_Name2 
 ,ISNULL(S.Strasse   ,'') AS Standort_Strasse 
 ,ISNULL(S.Ort       ,'') AS Standort_Ort    
 ,ISNULL(S.Land      ,'') AS Standort_Land 
 ,ISNULL(S.Plz       ,'') AS Standort_Plz     
 ,ISNULL(S.Telefon1  ,'') AS Standort_Telefon     
 ,ISNULL(S.Telefon2  ,'') AS Standort_Telefon2     
 ,ISNULL(S.Telefon3  ,'') AS Standort_Telefon3    
 ,ISNULL(S.FaxNummer ,'') AS Standort_Fax     
 ,ISNULL(S.EMail     ,'') AS Standort_EMail     

 ,ISNULL(H.VorName   ,'') AS Hausmeister_Vorname 
 ,ISNULL(H.Name1     ,'') AS Hausmeister_Name1  
 ,ISNULL(H.Name2     ,'') AS Hausmeister_Name2 
 ,ISNULL(H.Strasse   ,'') AS Hausmeister_Strasse 
 ,ISNULL(H.Ort       ,'') AS Hausmeister_Ort    
 ,ISNULL(H.Land      ,'') AS Hausmeister_Land 
 ,ISNULL(H.Plz       ,'') AS Hausmeister_Plz     
 ,ISNULL(H.Telefon1  ,'') AS Hausmeister_Telefon     
 ,ISNULL(H.Telefon2  ,'') AS Hausmeister_Telefon2     
 ,ISNULL(H.Telefon3  ,'') AS Hausmeister_Telefon3    
 ,ISNULL(H.FaxNummer ,'') AS Hausmeister_Fax     

 ,ISNULL(M.VorName   ,'') AS Mieter_Vorname 
 ,ISNULL(M.Name1     ,'') AS Mieter_Name1  
 ,ISNULL(M.Name2     ,'') AS Mieter_Name2 
 ,ISNULL(M.Strasse   ,'') AS Mieter_Strasse 
 ,ISNULL(M.Ort       ,'') AS Mieter_Ort    
 ,ISNULL(M.Land      ,'') AS Mieter_Land 
 ,ISNULL(M.Plz       ,'') AS Mieter_Plz     
 ,ISNULL(M.Telefon1  ,'') AS Mieter_Telefon     
 ,ISNULL(M.Telefon2  ,'') AS Mieter_Telefon2     
 ,ISNULL(M.Telefon3  ,'') AS Mieter_Telefon3    
 ,ISNULL(M.FaxNummer ,'') AS Mieter_Fax     

 ,ISNULL(R.Anrede    ,'') AS Empfaenger_Anrede
 ,ISNULL(R.VorName   ,'') AS Empfaenger_Vorname 
 ,ISNULL(R.Name1     ,'') AS Empfaenger_Name1  
 ,ISNULL(R.Name2     ,'') AS Empfaenger_Name2 
 ,ISNULL(R.Strasse   ,'') AS Empfaenger_Strasse 
 ,ISNULL(R.Ort       ,'') AS Empfaenger_Ort    
 ,ISNULL(R.Land      ,'') AS Empfaenger_Land 
 ,ISNULL(R.Plz       ,'') AS Empfaenger_Plz     
 ,ISNULL(R.Telefon1  ,'') AS Empfaenger_Telefon     
 ,ISNULL(R.Telefon2  ,'') AS Empfaenger_Telefon2     
 ,ISNULL(R.Telefon3  ,'') AS Empfaenger_Telefon3    
 ,ISNULL(R.FaxNummer ,'') AS Empfaenger_Fax     

 ,ISNULL((SELECT TOP 1 LTRIM(RTRIM(RTRIM(M.VorName) +' '+ LTRIM(M.Name1))) FROM dbo.Adressen M 
          WHERE M.DESTTYP = ' 4' AND M.Annr = W.Monteur),'') AS Monteur_Name1

 ,ISNULL((SELECT TOP 1 O.OptDataI FROM dbo.Optionen O WHERE O.OptId = ' 9901'),'') AS OPT_9901

 ,W.Nr      AS Auftrag_Nr      ,W.Datum   AS Auftrag_Datum   ,W.Termin AS Auftrag_Termin
 ,W.Uhrzeit AS Auftrag_Uhrzeit ,W.Monteur AS Auftrag_Monteur ,W.Art    AS Auftrag_Art
 ,W.Betreff AS Auftrag_Betreff

 ,ISNULL((SELECT TOP 1 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR 
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR 
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END
          FROM dbo.WartungsPosten P 
          WHERE P.AuftragId = W.Ident AND P.Pauschal = 1 ORDER BY P.LfdNr),0) AS Auftrag_Pauschale
 ,ISNULL((SELECT TOP 1 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END
          FROM dbo.WartungsPosten P 
          WHERE P.AuftragId = W.Ident AND P.Pauschal = 1 ORDER BY P.LfdNr),0) +
  ISNULL((SELECT SUM( 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR 
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR 
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END)
          FROM dbo.WartungsPosten P WHERE P.AuftragId = W.Ident AND P.Pauschal = 0),0) AS Auftrag_VkGesamt
 ,ISNULL((SELECT SUM( P.Minuten )
          FROM dbo.WartungsPosten P WHERE P.AuftragId = W.Ident AND P.Pauschal = 0),0) AS Auftrag_Zeit

 ,AN.Name1       AS EigeneAdresse_Name1       ,AN.Name2      AS EigeneAdresse_Name2      ,AN.Name3     AS EigeneAdresse_Name3
 ,AN.Strasse     AS EigeneAdresse_Strasse     ,AN.PlzOrt     AS EigeneAdresse_PlzOrt     ,AN.Telefon   AS EigeneAdresse_Telefon
 ,AN.FaxNr       AS EigeneAdresse_Fax         ,AN.Bank1_Name AS EigeneAdresse_Bank1_Name ,AN.Bank1_BLZ AS EigeneAdresse_Bank1_Blz
 ,AN.Bank1_Konto AS EigeneAdresse_Bank1_Konto ,AN.Bank2_Name AS EigeneAdresse_Bank2_Name ,AN.Bank2_BLZ AS EigeneAdresse_Bank2_Blz
 ,AN.Bank2_Konto AS EigeneAdresse_Bank2_Konto

FROM dbo.#tmp_WAuftrag T
INNER JOIN dbo.WartungsAuftraege W
        ON W.Ident = T.Ident
LEFT  JOIN dbo.WartungsAnlagen WA
        ON W.AnlagenId = WA.Ident
LEFT  JOIN dbo.WartungsObjekte WO
        ON W.ObjektId = WO.Ident
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT  JOIN dbo.Adressen H
        ON WO.HausMstr = H.DESTDEBNR AND WO.HausMstr <> ''
LEFT  JOIN dbo.Adressen M
        ON WA.Mieter = M.DESTDEBNR AND WA.Mieter <> ''
LEFT  JOIN dbo.Adressen R
        ON WA.KdNrRng = R.DESTDEBNR AND WA.KdNrRng <> ''
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'

GO






CREATE PROCEDURE dbo.crp_WAuftragExEx
			    @pIdent  varchar(8)
AS

SET @pIdent = REPLACE(@pIdent,'.','')

CREATE TABLE dbo.#tmp_WAuftrag (Ident int)

IF @pIdent = '0' OR @pIdent = ''
BEGIN
    INSERT INTO dbo.#tmp_WAuftrag
    SELECT W.Ident
    FROM dbo.WartungsMeldungen WM
    INNER JOIN dbo.WartungsAuftraege W
            ON WM.AuftragId = W.Ident
    WHERE WM.KzMark = 1 AND WM.AuftragId <> 0
END
ELSE
BEGIN
    INSERT INTO dbo.#tmp_WAuftrag
    SELECT W.Ident
    FROM dbo.WartungsAuftraege W
    WHERE W.Ident = @pIdent
END


SELECT
  W.Ident AS Auftrag_Ident

 ,WO.Ident   AS Objekt_Ident   ,WO.Nr       AS Objekt_Nr       ,WO.VertragId  AS Objekt_VertragId
 ,WO.Bez1    AS Objekt_Bez1    ,WO.Bez2     AS Objekt_Bez2     ,WO.KdNr       AS Objekt_KdNr
 ,WO.KdNrRng AS Objekt_KdNrRng ,WO.Standort AS Objekt_Standort ,WO.HausMstr   AS Objekt_HausMstr  
 ,WO.Mieter  AS Objekt_Mieter  

 ,(SELECT TOP 1 WM.Bearbeiter FROM WartungsMeldungen WM WHERE WM.AuftragId = W.Ident) AS Objekt_GeoDaten 
 ,WO.Entfernung AS Objekt_Entfernung

 ,WA.Ident      AS Anlage_Ident      ,WA.Nr         AS Anlage_Nr      ,WA.AnlTyp    AS Anlage_AnlTyp
 ,WA.Bez1       AS Anlage_Bez1       ,WA.Bez2       AS Anlage_Bez2    ,WA.Inbetrieb AS Anlage_Inbetrieb 
 ,WA.Garantie   AS Anlage_Garantie   ,WA.Letzte     AS Anlage_Letzte  ,WA.Naechste  AS Anlage_Naechste  
 ,WA.Termin     AS Anlage_Termin     ,WA.Uhrzeit    AS Anlage_Uhrzeit ,WA.Monteur   AS Anlage_Monteur   
 ,WA.Vorgemerkt AS Anlage_Vorgemerkt ,WA.AnzBT      AS Anlage_AnzBT   ,WA.AnzMD     AS Anlage_AnzMD

 ,CASE
    WHEN (0 <> (SELECT COUNT(WP.LfdNr) FROM dbo.WartungsPosten WP WHERE WP.AuftragId=W.Ident AND WP.[Check]=1)) AND
         (0 <> WA.KzCheck)
    THEN 1
    ELSE 0
  END  AS Anlage_KzCheck


 ,WA.Mieter     AS Anlage_Mieter  ,WA.KdNrRng   AS Anlage_KdNrRng
 ,WA.RechDatum  AS Anlage_Abrechnung ,WA.LRechDatum AS Anlage_LAbrechnung

 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = W.AnlagenId AND T.Klein = 1) ,0)    AS Anlage_Pauschale_Klein
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = W.AnlagenId AND T.Gross = 1) ,0)    AS Anlage_Pauschale_Gross
 ,ISNULL((SELECT TOP 1 T.Pauschale FROM dbo.WartungsTurnus T 
          WHERE T.AnlagenId = W.AnlagenId AND T.Pauschal = 1) ,0) AS Anlage_Pauschale_Rechnung

 ,ISNULL((SELECT TOP 1 T.Nr FROM dbo.WartungsTypen T WHERE T.Ident = WA.AnlTyp)  ,'')  AS Anlage_Typ

 ,ISNULL(A.Anrede    ,'') AS Kunde_Anrede
 ,ISNULL(A.VorName   ,'') AS Kunde_Vorname 
 ,ISNULL(A.Name1     ,'') AS Kunde_Name1  
 ,ISNULL(A.Name2     ,'') AS Kunde_Name2 
 ,ISNULL(A.Strasse   ,'') AS Kunde_Strasse 
 ,ISNULL(A.Ort       ,'') AS Kunde_Ort    
 ,ISNULL(A.Land      ,'') AS Kunde_Land 
 ,ISNULL(A.Plz       ,'') AS Kunde_Plz     
 ,ISNULL(A.Telefon1  ,'') AS Kunde_Telefon     
 ,ISNULL(A.Telefon2  ,'') AS Kunde_Telefon2     
 ,ISNULL(A.Telefon3  ,'') AS Kunde_Telefon3    
 ,ISNULL(A.FaxNummer ,'') AS Kunde_Fax     
 ,ISNULL(A.EMail     ,'') AS Kunde_EMail     

 ,ISNULL(S.VorName   ,'') AS Standort_Vorname 
 ,ISNULL(S.Name1     ,'') AS Standort_Name1  
 ,ISNULL(S.Name2     ,'') AS Standort_Name2 
 ,ISNULL(S.Strasse   ,'') AS Standort_Strasse 
 ,ISNULL(S.Ort       ,'') AS Standort_Ort    
 ,ISNULL(S.Land      ,'') AS Standort_Land 
 ,ISNULL(S.Plz       ,'') AS Standort_Plz     
 ,ISNULL(S.Telefon1  ,'') AS Standort_Telefon     
 ,ISNULL(S.Telefon2  ,'') AS Standort_Telefon2     
 ,ISNULL(S.Telefon3  ,'') AS Standort_Telefon3    
 ,ISNULL(S.FaxNummer ,'') AS Standort_Fax     
 ,ISNULL(S.EMail     ,'') AS Standort_EMail     

 ,ISNULL(H.VorName   ,'') AS Hausmeister_Vorname 
 ,ISNULL(H.Name1     ,'') AS Hausmeister_Name1  
 ,ISNULL(H.Name2     ,'') AS Hausmeister_Name2 
 ,ISNULL(H.Strasse   ,'') AS Hausmeister_Strasse 
 ,ISNULL(H.Ort       ,'') AS Hausmeister_Ort    
 ,ISNULL(H.Land      ,'') AS Hausmeister_Land 
 ,ISNULL(H.Plz       ,'') AS Hausmeister_Plz     
 ,ISNULL(H.Telefon1  ,'') AS Hausmeister_Telefon     
 ,ISNULL(H.Telefon2  ,'') AS Hausmeister_Telefon2     
 ,ISNULL(H.Telefon3  ,'') AS Hausmeister_Telefon3    
 ,ISNULL(H.FaxNummer ,'') AS Hausmeister_Fax     

 ,ISNULL(M.VorName   ,'') AS Mieter_Vorname 
 ,ISNULL(M.Name1     ,'') AS Mieter_Name1  
 ,ISNULL(M.Name2     ,'') AS Mieter_Name2 
 ,ISNULL(M.Strasse   ,'') AS Mieter_Strasse 
 ,ISNULL(M.Ort       ,'') AS Mieter_Ort    
 ,ISNULL(M.Land      ,'') AS Mieter_Land 
 ,ISNULL(M.Plz       ,'') AS Mieter_Plz     
 ,ISNULL(M.Telefon1  ,'') AS Mieter_Telefon     
 ,ISNULL(M.Telefon2  ,'') AS Mieter_Telefon2     
 ,ISNULL(M.Telefon3  ,'') AS Mieter_Telefon3    
 ,ISNULL(M.FaxNummer ,'') AS Mieter_Fax     

 ,ISNULL(R.Anrede    ,'') AS Empfaenger_Anrede
 ,ISNULL(R.VorName   ,'') AS Empfaenger_Vorname 
 ,ISNULL(R.Name1     ,'') AS Empfaenger_Name1  
 ,ISNULL(R.Name2     ,'') AS Empfaenger_Name2 
 ,ISNULL(R.Strasse   ,'') AS Empfaenger_Strasse 
 ,ISNULL(R.Ort       ,'') AS Empfaenger_Ort    
 ,ISNULL(R.Land      ,'') AS Empfaenger_Land 
 ,ISNULL(R.Plz       ,'') AS Empfaenger_Plz     
 ,ISNULL(R.Telefon1  ,'') AS Empfaenger_Telefon     
 ,ISNULL(R.Telefon2  ,'') AS Empfaenger_Telefon2     
 ,ISNULL(R.Telefon3  ,'') AS Empfaenger_Telefon3    
 ,ISNULL(R.FaxNummer ,'') AS Empfaenger_Fax     

 ,ISNULL((SELECT TOP 1 LTRIM(RTRIM(RTRIM(M.VorName) +' '+ LTRIM(M.Name1))) FROM dbo.Adressen M 
          WHERE M.DESTTYP = ' 4' AND M.Annr = W.Monteur),'') AS Monteur_Name1

 ,ISNULL((SELECT TOP 1 O.OptDataI FROM dbo.Optionen O WHERE O.OptId = ' 9901'),'') AS OPT_9901

 ,W.Nr      AS Auftrag_Nr      ,W.Datum   AS Auftrag_Datum   ,W.Termin AS Auftrag_Termin
 ,W.Uhrzeit AS Auftrag_Uhrzeit ,W.Monteur AS Auftrag_Monteur ,W.Art    AS Auftrag_Art
 ,W.Betreff AS Auftrag_Betreff ,W.PrjNr   AS Auftrag_Projekt

 ,ISNULL((SELECT TOP 1 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR 
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR 
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END
          FROM dbo.WartungsPosten P 
          WHERE P.AuftragId = W.Ident AND P.Pauschal = 1 ORDER BY P.LfdNr),0) AS Auftrag_Pauschale
 ,ISNULL((SELECT TOP 1 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END
          FROM dbo.WartungsPosten P 
          WHERE P.AuftragId = W.Ident AND P.Pauschal = 1 ORDER BY P.LfdNr),0) +
  ISNULL((SELECT SUM( 
          CASE 
            WHEN P.LohnJN = 1 AND P.MatJN = 1 THEN P.Menge * (P.VkLohn + P.VkMat) - P.RabattEUR 
            WHEN P.LohnJN = 1 AND P.MatJN = 0 THEN P.Menge * P.VkLohn - P.RabattEUR 
            WHEN P.LohnJN = 0 AND P.MatJN = 1 THEN P.Menge * P.VkMat - P.RabattEUR 
            ELSE 0
          END)
          FROM dbo.WartungsPosten P WHERE P.AuftragId = W.Ident AND P.Pauschal = 0),0) AS Auftrag_VkGesamt
 ,ISNULL((SELECT SUM( P.Minuten )
          FROM dbo.WartungsPosten P WHERE P.AuftragId = W.Ident AND P.Pauschal = 0),0) AS Auftrag_Zeit

 ,AN.Name1       AS EigeneAdresse_Name1       ,AN.Name2      AS EigeneAdresse_Name2      ,AN.Name3     AS EigeneAdresse_Name3
 ,AN.Strasse     AS EigeneAdresse_Strasse     ,AN.PlzOrt     AS EigeneAdresse_PlzOrt     ,AN.Telefon   AS EigeneAdresse_Telefon
 ,AN.FaxNr       AS EigeneAdresse_Fax         ,AN.Bank1_Name AS EigeneAdresse_Bank1_Name ,AN.Bank1_BLZ AS EigeneAdresse_Bank1_Blz
 ,AN.Bank1_Konto AS EigeneAdresse_Bank1_Konto ,AN.Bank2_Name AS EigeneAdresse_Bank2_Name ,AN.Bank2_BLZ AS EigeneAdresse_Bank2_Blz
 ,AN.Bank2_Konto AS EigeneAdresse_Bank2_Konto

FROM dbo.#tmp_WAuftrag T
INNER JOIN dbo.WartungsAuftraege W
        ON W.Ident = T.Ident
LEFT  JOIN dbo.WartungsAnlagen WA
        ON W.AnlagenId = WA.Ident
LEFT  JOIN dbo.WartungsObjekte WO
        ON W.ObjektId = WO.Ident
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT  JOIN dbo.Adressen H
        ON WO.HausMstr = H.DESTDEBNR AND WO.HausMstr <> ''
LEFT  JOIN dbo.Adressen M
        ON WA.Mieter = M.DESTDEBNR AND WA.Mieter <> ''
LEFT  JOIN dbo.Adressen R
        ON WA.KdNrRng = R.DESTDEBNR AND WA.KdNrRng <> ''
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'

GO






CREATE PROCEDURE dbo.crp_WBestand
			     @Teil   varchar(40)
			    ,@Dim1   varchar(40)
			    ,@Wert1  varchar(40)
			    ,@Dim2   varchar(40)
			    ,@Wert2  varchar(40)
			    ,@Dim3   varchar(40)
			    ,@Wert3  varchar(40)
AS

IF @Teil  = '' SET @Teil  = 'NULL'
IF @Wert1 = '' SET @Wert1 = 'NULL'
IF @Wert2 = '' SET @Wert2 = 'NULL'
IF @Wert3 = '' SET @Wert3 = 'NULL'
IF @Dim1  = '' SET @Dim1  = 'NULL'
IF @Dim2  = '' SET @Dim2  = 'NULL'
IF @Dim3  = '' SET @Dim3  = 'NULL'


SELECT
  W.WA_Ident      ,W.WA_Nr      ,W.WA_AnlTyp
 ,W.WA_Bez1       ,W.WA_Bez2    ,W.WA_Inbetrieb 
 ,W.WA_Garantie   ,W.WA_Letzte  ,W.WA_Naechste  
 ,W.WA_Termin     ,W.WA_Uhrzeit ,W.WA_Monteur   
 ,W.WA_Vorgemerkt ,W.WA_Mieter  ,W.WA_KdNrRng

 ,W.WP_PosNr ,W.WP_Menge   ,W.WP_Einheit 
 ,W.WP_Lkz   ,W.WP_ArtNr   ,W.WP_Bez1 
 ,W.WP_Bez2  ,W.WP_Merkmal ,W.WP_Teil

 ,W.WT_Nr    ,W.WT_Bez1  ,W.WT_Bez2
 ,W.WD_Bez1  ,W.WD_Bez2  ,W.WD_Bez3  ,W.WD_Bez4  ,W.WD_Bez5
 ,W.WD_Wert1 ,W.WD_Wert2 ,W.WD_Wert3 ,W.WD_Wert4 ,W.WD_Wert5

 ,W.F_Name1       ,W.F_Name2      ,W.F_Name3
 ,W.F_Strasse     ,W.F_PlzOrt     ,W.F_Telefon
 ,W.F_Fax         ,W.F_Bank1_Name ,W.F_Bank1_Blz
 ,W.F_Bank1_Konto ,W.F_Bank2_Name ,W.F_Bank2_Blz ,W.F_Bank2_Konto

  FROM (SELECT
    WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
   ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
   ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
   ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
   ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter  AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng

   ,WP.PosNr AS WP_PosNr ,WP.Menge   AS WP_Menge  ,WP.Einheit AS WP_Einheit
   ,WP.Lkz   AS WP_Lkz   ,WP.ArtNr   AS WP_ArtNr  ,WP.Bez1    AS WP_Bez1
   ,WP.Bez2  AS WP_Bez2  ,WP.Merkmal AS WP_Merkmal

   ,WP.Bez1 + WP.Bez2 + WP.Merkmal AS WP_Teil 

   ,WT.Nr  AS WT_Nr ,WT.Bez1 AS WT_Bez1 ,WT.Bez2 AS WT_Bez2

   ,ISNULL( (SELECT TOP 1 D.Bez FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 1),  '') AS WD_Bez1
   ,ISNULL( (SELECT TOP 1 D.Bez FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 2),  '') AS WD_Bez2
   ,ISNULL( (SELECT TOP 1 D.Bez FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 3),  '') AS WD_Bez3
   ,ISNULL( (SELECT TOP 1 D.Bez FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 4),  '') AS WD_Bez4
   ,ISNULL( (SELECT TOP 1 D.Bez FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 5),  '') AS WD_Bez5

   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 1),  '') AS WD_Wert1
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 2),  '') AS WD_Wert2
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 3),  '') AS WD_Wert3
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 4),  '') AS WD_Wert4
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 5),  '') AS WD_Wert5

   ,AN.Name1       AS F_Name1       ,AN.Name2      AS F_Name2      ,AN.Name3     AS F_Name3
   ,AN.Strasse     AS F_Strasse     ,AN.PlzOrt     AS F_PlzOrt     ,AN.Telefon   AS F_Telefon
   ,AN.FaxNr       AS F_Fax         ,AN.Bank1_Name AS F_Bank1_Name ,AN.Bank1_BLZ AS F_Bank1_Blz
   ,AN.Bank1_Konto AS F_Bank1_Konto ,AN.Bank2_Name AS F_Bank2_Name ,AN.Bank2_BLZ AS F_Bank2_Blz
   ,AN.Bank2_Konto AS F_Bank2_Konto

  FROM dbo.WartungsAnlagen WA
  LEFT JOIN dbo.WartungsTypen WT
         ON WA.AnlTyp = WT.Ident
  LEFT JOIN dbo.WartungsPositionen WP
         ON WA.Ident = WP.AnlagenId
  LEFT JOIN dbo.Anschriften AN
         ON AN.Nr = '01' ) AS W

  WHERE (@Teil  = 'NULL' OR 0 <> CHARINDEX(@Teil,W.WP_Teil)) AND
        (@Wert1 = 'NULL' OR 0 <> CHARINDEX(@Wert1,W.WD_Wert1) OR 0 <> CHARINDEX(@Wert1,W.WD_Wert2) OR
                            0 <> CHARINDEX(@Wert1,W.WD_Wert3) OR 0 <> CHARINDEX(@Wert1,W.WD_Wert4) OR
                            0 <> CHARINDEX(@Wert1,W.WD_Wert5)) AND
        (@Dim1  = 'NULL' OR 0 <> CHARINDEX(@Dim1,W.WD_Bez1) OR 0 <> CHARINDEX(@Dim1,W.WD_Bez2) OR
                            0 <> CHARINDEX(@Dim1,W.WD_Bez3) OR 0 <> CHARINDEX(@Dim1,W.WD_Bez4) OR
                            0 <> CHARINDEX(@Dim1,W.WD_Bez5)) AND
        (@Wert2 = 'NULL' OR 0 <> CHARINDEX(@Wert2,W.WD_Wert1) OR 0 <> CHARINDEX(@Wert2,W.WD_Wert2) OR
                            0 <> CHARINDEX(@Wert2,W.WD_Wert3) OR 0 <> CHARINDEX(@Wert2,W.WD_Wert4) OR
                            0 <> CHARINDEX(@Wert2,W.WD_Wert5)) AND
        (@Dim2  = 'NULL' OR 0 <> CHARINDEX(@Dim2,W.WD_Bez1) OR 0 <> CHARINDEX(@Dim2,W.WD_Bez2) OR
                            0 <> CHARINDEX(@Dim2,W.WD_Bez3) OR 0 <> CHARINDEX(@Dim2,W.WD_Bez4) OR
                            0 <> CHARINDEX(@Dim2,W.WD_Bez5)) AND
        (@Wert3 = 'NULL' OR 0 <> CHARINDEX(@Wert3,W.WD_Wert1) OR 0 <> CHARINDEX(@Wert3,W.WD_Wert2) OR
                            0 <> CHARINDEX(@Wert3,W.WD_Wert3) OR 0 <> CHARINDEX(@Wert3,W.WD_Wert4) OR
                            0 <> CHARINDEX(@Wert3,W.WD_Wert5)) AND
        (@Dim3  = 'NULL' OR 0 <> CHARINDEX(@Dim3,W.WD_Bez1) OR 0 <> CHARINDEX(@Dim3,W.WD_Bez2) OR
                            0 <> CHARINDEX(@Dim3,W.WD_Bez3) OR 0 <> CHARINDEX(@Dim3,W.WD_Bez4) OR
                            0 <> CHARINDEX(@Dim3,W.WD_Bez5))

GO




CREATE PROCEDURE dbo.crp_WBestandEx
			     @Teil   varchar(40)
			    ,@Dim1   varchar(40)
			    ,@Wert1  varchar(40)
			    ,@Dim2   varchar(40)
			    ,@Wert2  varchar(40)
			    ,@Dim3   varchar(40)
			    ,@Wert3  varchar(40)
			    ,@Dim4   varchar(40)
			    ,@Wert4  varchar(40)
			    ,@Dim5   varchar(40)
			    ,@Wert5  varchar(40)
AS

IF @Teil  = '' SET @Teil  = 'NNNN'
IF @Wert1 = '' SET @Wert1 = 'NNNN'
IF @Wert2 = '' SET @Wert2 = 'NNNN'
IF @Wert3 = '' SET @Wert3 = 'NNNN'
IF @Wert4 = '' SET @Wert4 = 'NNNN'
IF @Wert5 = '' SET @Wert5 = 'NNNN'
IF @Dim1  = '' SET @Dim1  = 'NNNN'
IF @Dim2  = '' SET @Dim2  = 'NNNN'
IF @Dim3  = '' SET @Dim3  = 'NNNN'
IF @Dim4  = '' SET @Dim4  = 'NNNN'
IF @Dim5  = '' SET @Dim5  = 'NNNN'

IF @Teil  = 'NULL' SET @Teil  = 'NNNN'
IF @Wert1 = 'NULL' SET @Wert1 = 'NNNN'
IF @Wert2 = 'NULL' SET @Wert2 = 'NNNN'
IF @Wert3 = 'NULL' SET @Wert3 = 'NNNN'
IF @Wert4 = 'NULL' SET @Wert4 = 'NNNN'
IF @Wert5 = 'NULL' SET @Wert5 = 'NNNN'
IF @Dim1  = 'NULL' SET @Dim1  = 'NNNN'
IF @Dim2  = 'NULL' SET @Dim2  = 'NNNN'
IF @Dim3  = 'NULL' SET @Dim3  = 'NNNN'
IF @Dim4  = 'NULL' SET @Dim4  = 'NNNN'
IF @Dim5  = 'NULL' SET @Dim5  = 'NNNN'

SET @Teil  = UPPER(@Teil)
SET @Dim1  = UPPER(@Dim1)
SET @Dim2  = UPPER(@Dim2)
SET @Dim3  = UPPER(@Dim3)
SET @Dim4  = UPPER(@Dim4)
SET @Dim5  = UPPER(@Dim5)
SET @Wert1 = UPPER(@Wert1)
SET @Wert2 = UPPER(@Wert2)
SET @Wert3 = UPPER(@Wert3)
SET @Wert4 = UPPER(@Wert4)
SET @Wert5 = UPPER(@Wert5)

DECLARE @mTeil AS Varchar(10)
DECLARE @mDim  AS Varchar(10)
DECLARE @mWert AS Varchar(10)
SET @mTeil = 'NULL'
SET @mDim  = 'NULL'
SET @mWert = 'NULL'

IF @Teil <> 'NNNN'
  SET @mTeil = ''

IF @Dim1 <> 'NNNN' OR @Dim2 <> 'NNNN' OR @Dim3 <> 'NNNN' OR @Dim4 <> 'NNNN' OR @Dim5 <> 'NNNN'
  SET @mDim = ''

IF @Wert1 <> 'NNNN' OR @Wert2 <> 'NNNN' OR @Wert3 <> 'NNNN' OR @Wert4 <> 'NNNN' OR @Wert5 <> 'NNNN'
  SET @mWert = ''

SELECT WP.AnlagenId   
      ,WP.PosNr 
INTO #crt_WBestand
FROM (SELECT  AnlagenId
             ,PosNr
             ,UPPER(Bez1 + Bez2 + Merkmal) AS Teil
      FROM dbo.WartungsPositionen) WP
LEFT JOIN (SELECT  AnlagenId
                  ,PosNr
                  ,UPPER(Bez)  AS Bez
                  ,UPPER(Wert) AS Wert
           FROM dbo.WartungsDimensionen) WD
       ON WP.AnlagenId = WD.AnlagenId AND WP.PosNr = WD.PosNr
WHERE  ((@mTeil  = 'NULL') OR (WP.Teil LIKE '%'+@Teil +'%'))
   AND ((@mDim   = 'NULL') OR (WD.Bez  LIKE '%'+@Dim1 +'%') 
                           OR (WD.Bez  LIKE '%'+@Dim2 +'%') 
                           OR (WD.Bez  LIKE '%'+@Dim3 +'%') 
                           OR (WD.Bez  LIKE '%'+@Dim4 +'%') 
                           OR (WD.Bez  LIKE '%'+@Dim5 +'%'))
   AND ((@mWert  = 'NULL') OR (WD.Wert LIKE '%'+@Wert1+'%') 
                           OR (WD.Wert LIKE '%'+@Wert2+'%') 
                           OR (WD.Wert LIKE '%'+@Wert3+'%') 
                           OR (WD.Wert LIKE '%'+@Wert4+'%') 
                           OR (WD.Wert LIKE '%'+@Wert5+'%'))
GROUP BY WP.AnlagenId, WP.PosNr

CREATE UNIQUE INDEX INDX_Anlage_Pos ON #crt_WBestand(AnlagenId,PosNr)

SELECT
    WA.Ident      AS WA_Ident      ,WA.Nr      AS WA_Nr      ,WA.AnlTyp    AS WA_AnlTyp
   ,WA.Bez1       AS WA_Bez1       ,WA.Bez2    AS WA_Bez2    ,WA.Inbetrieb AS WA_Inbetrieb 
   ,WA.Garantie   AS WA_Garantie   ,WA.Letzte  AS WA_Letzte  ,WA.Naechste  AS WA_Naechste  
   ,WA.Termin     AS WA_Termin     ,WA.Uhrzeit AS WA_Uhrzeit ,WA.Monteur   AS WA_Monteur   
   ,WA.Vorgemerkt AS WA_Vorgemerkt ,WA.Mieter  AS WA_Mieter  ,WA.KdNrRng   AS WA_KdNrRng

   ,WO.Ident   AS WO_Ident   ,WO.Nr       AS WO_Nr       ,WO.VertragId  AS WO_VertragId
   ,WO.Bez1    AS WO_Bez1    ,WO.Bez2     AS WO_Bez2     ,WO.KdNr       AS WO_KdNr
   ,WO.KdNrRng AS WO_KdNrRng ,WO.Standort AS WO_Standort ,WO.HausMstr   AS WO_HausMstr  
   ,WO.Mieter  AS WO_Mieter  ,WO.GeoDaten AS WO_GeoDaten ,WO.Entfernung AS WO_Entfernung

   ,ISNULL(A.Anrede    ,'') AS KD_Anrede
   ,ISNULL(A.VorName   ,'') AS KD_Vorname 
   ,ISNULL(A.Name1     ,'') AS KD_Name1  
   ,ISNULL(A.Name2     ,'') AS KD_Name2 
   ,ISNULL(A.Strasse   ,'') AS KD_Strasse 
   ,ISNULL(A.Ort       ,'') AS KD_Ort    
   ,ISNULL(A.Land      ,'') AS KD_Land 
   ,ISNULL(A.Plz       ,'') AS KD_Plz     
   ,ISNULL(A.Telefon1  ,'') AS KD_Telefon     
   ,ISNULL(A.Telefon2  ,'') AS KD_Telefon2     
   ,ISNULL(A.Telefon3  ,'') AS KD_Telefon3    
   ,ISNULL(A.FaxNummer ,'') AS KD_Fax     
   ,ISNULL(A.EMail     ,'') AS KD_EMail     

   ,ISNULL(S.VorName   ,'') AS SO_Vorname 
   ,ISNULL(S.Name1     ,'') AS SO_Name1  
   ,ISNULL(S.Name2     ,'') AS SO_Name2 
   ,ISNULL(S.Strasse   ,'') AS SO_Strasse 
   ,ISNULL(S.Ort       ,'') AS SO_Ort    
   ,ISNULL(S.Land      ,'') AS SO_Land 
   ,ISNULL(S.Plz       ,'') AS SO_Plz     
   ,ISNULL(S.Telefon1  ,'') AS SO_Telefon     
   ,ISNULL(S.Telefon2  ,'') AS SO_Telefon2     
   ,ISNULL(S.Telefon3  ,'') AS SO_Telefon3    
   ,ISNULL(S.FaxNummer ,'') AS SO_Fax     
   ,ISNULL(S.EMail     ,'') AS SO_EMail     

   ,WP.PosNr AS WP_PosNr ,WP.Menge   AS WP_Menge  ,WP.Einheit AS WP_Einheit
   ,WP.Lkz   AS WP_Lkz   ,WP.ArtNr   AS WP_ArtNr  ,WP.Bez1    AS WP_Bez1
   ,WP.Bez2  AS WP_Bez2  ,WP.Merkmal AS WP_Merkmal

   ,WP.Bez1 + WP.Bez2 + WP.Merkmal AS WP_Teil 

   ,ISNULL(WT.Nr,'')   AS WT_Nr 
   ,ISNULL(WT.Bez1,'') AS WT_Bez1 
   ,ISNULL(WT.Bez2,'') AS WT_Bez2

   ,ISNULL( (SELECT TOP 1 D.Bez FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 1),  '') AS WD_Bez1
   ,ISNULL( (SELECT TOP 1 D.Bez FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 2),  '') AS WD_Bez2
   ,ISNULL( (SELECT TOP 1 D.Bez FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 3),  '') AS WD_Bez3
   ,ISNULL( (SELECT TOP 1 D.Bez FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 4),  '') AS WD_Bez4
   ,ISNULL( (SELECT TOP 1 D.Bez FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 5),  '') AS WD_Bez5

   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 1),  '') AS WD_Wert1
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 2),  '') AS WD_Wert2
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 3),  '') AS WD_Wert3
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 4),  '') AS WD_Wert4
   ,ISNULL( (SELECT TOP 1 D.Wert FROM dbo.WartungsDimensionen D
             WHERE D.AnlagenId = WA.Ident AND WP.PosNr = D.PosNr AND D.LfdNr = 5),  '') AS WD_Wert5

   ,AN.Name1       AS F_Name1       ,AN.Name2      AS F_Name2      ,AN.Name3     AS F_Name3
   ,AN.Strasse     AS F_Strasse     ,AN.PlzOrt     AS F_PlzOrt     ,AN.Telefon   AS F_Telefon
   ,AN.FaxNr       AS F_Fax         ,AN.Bank1_Name AS F_Bank1_Name ,AN.Bank1_BLZ AS F_Bank1_Blz
   ,AN.Bank1_Konto AS F_Bank1_Konto ,AN.Bank2_Name AS F_Bank2_Name ,AN.Bank2_BLZ AS F_Bank2_Blz
   ,AN.Bank2_Konto AS F_Bank2_Konto

FROM #crt_WBestand T
INNER JOIN dbo.WartungsPositionen WP
        ON T.AnlagenId = WP.AnlagenId AND T.PosNr = WP.PosNr
INNER JOIN dbo.WartungsAnlagen WA
        ON T.AnlagenId = WA.Ident
LEFT  JOIN dbo.WartungsObjekte WO
        ON WA.ObjektId = WO.Ident
LEFT  JOIN dbo.Adressen A 
        ON WO.KdNr = A.DESTDEBNR AND WO.KdNr <> ''
LEFT  JOIN dbo.Adressen S
        ON WO.Standort = S.DESTDEBNR AND WO.Standort <> ''
LEFT  JOIN dbo.WartungsTypen WT
        ON WA.AnlTyp = WT.Ident
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
GO






--*****************************************************************************************************************
-----------------------  Langtexte für Wartung --------------------------------------------------------------------
--*****************************************************************************************************************



IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'U' and [name] = 'crt_WAuftragLangTexte')
	DROP TABLE dbo.crt_WAuftragLangTexte
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'U' and [name] = 'crt_WAnlageLangTexte')
	DROP TABLE dbo.crt_WAnlageLangTexte
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'U' and [name] = 'crt_WAuftragAnnahme')
	DROP TABLE dbo.crt_WAuftragAnnahme
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'U' and [name] = 'crt_WAuftragAbgabe')
	DROP TABLE dbo.crt_WAuftragAbgabe
GO

CREATE TABLE dbo.crt_WAuftragLangTexte(  Ident     varchar(8)  
                                        ,LfdNr     varchar(6)   
	                                ,LineData  varchar(200) )
GO
CREATE INDEX IkPrimary ON dbo.crt_WAuftragLangTexte( Ident, LfdNr )
GO


CREATE TABLE dbo.crt_WAnlageLangTexte(  Ident     varchar(8)  
                                       ,LfdNr     varchar(6)   
	                               ,LineData  varchar(200) )
GO
CREATE INDEX IkPrimary ON dbo.crt_WAnlageLangTexte( Ident, LfdNr )
GO

CREATE TABLE dbo.crt_WAuftragAnnahme(  Ident     varchar(8)  
                                      ,LfdNr     varchar(6)   
	                              ,LineData  varchar(200) )
GO
CREATE INDEX IkPrimary ON dbo.crt_WAuftragAnnahme( Ident, LfdNr )
GO

CREATE TABLE dbo.crt_WAuftragAbgabe(  Ident     varchar(8)  
                                     ,LfdNr     varchar(6)   
	                             ,LineData  varchar(200) )
GO
CREATE INDEX IkPrimary ON dbo.crt_WAuftragAbgabe( Ident, LfdNr )
GO




IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_MakeWAuftragLangText')
	DROP PROCEDURE dbo.crp_MakeWAuftragLangText
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_MakeWAnlageLangText')
	DROP PROCEDURE dbo.crp_MakeWAnlageLangText
GO

CREATE PROCEDURE dbo.crp_MakeWAuftragLangText
                                        @mIdent   varchar(8)
AS
  SET @mIdent = REPLACE(@mIdent,'.','')
  DECLARE @mLfdNr integer
  SET @mLfdNr = 1

  DECLARE   CWA CURSOR FOR 
    SELECT   Daten, CntChar, SoftBreak+LineFeeds, Abschluss
    FROM     dbo.Langtextzeilen 
    WHERE    Art='25' AND Lkz='AU' AND Nr='AUF'+STR(@mIdent,15) 
    ORDER BY Lkz,Nr,LfdNr
  OPEN CWA
  
  DECLARE @mDaten  char(55)
  DECLARE @mCnt    integer
  DECLARE @mBreak  integer
  DECLARE @mEnde   integer
  DECLARE @mText   varchar(160)

  SET @mText   = ''
  FETCH NEXT FROM CWA INTO @mDaten, @mCnt, @mBreak, @mEnde
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF (LEN(@mText)+@mCnt) > 200
    BEGIN
      INSERT INTO dbo.crt_WAuftragLangTexte (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
    END

    IF (@mCnt > 0)
    BEGIN
      SET @mText = @mText + SUBSTRING(@mDaten, 1, @mCnt)
    END	

    WHILE (@mBreak > 0)
    BEGIN
      INSERT INTO dbo.crt_WAuftragLangTexte (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
      SET @mBreak = @mBreak - 1
    END

    IF @mEnde > 0 BREAK
    FETCH NEXT FROM CWA INTO @mDaten, @mCnt, @mBreak, @mEnde
  END

  IF LEN(@mText) > 0
  BEGIN
      INSERT INTO dbo.crt_WAuftragLangTexte (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
  END

  CLOSE CWA
  DEALLOCATE CWA
GO


CREATE PROCEDURE dbo.crp_MakeWAnlageLangText
                                        @mIdent   varchar(8)
AS
  SET @mIdent = REPLACE(@mIdent,'.','')
  DECLARE @mLfdNr integer
  SET @mLfdNr = 1

  DECLARE   CWA CURSOR FOR 
    SELECT   Daten, CntChar, SoftBreak+LineFeeds, Abschluss
    FROM     dbo.Langtextzeilen 
    WHERE    Art='25' AND Lkz='AT' AND Nr='ANL'+STR(@mIdent,15) 
    ORDER BY Lkz,Nr,LfdNr
  OPEN CWA
  
  DECLARE @mDaten  char(55)
  DECLARE @mCnt    integer
  DECLARE @mBreak  integer
  DECLARE @mEnde   integer
  DECLARE @mText   varchar(160)

  SET @mText   = ''
  FETCH NEXT FROM CWA INTO @mDaten, @mCnt, @mBreak, @mEnde
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF (LEN(@mText)+@mCnt) > 200
    BEGIN
      INSERT INTO dbo.crt_WAnlageLangTexte (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
    END

    IF (@mCnt > 0)
    BEGIN
      SET @mText = @mText + SUBSTRING(@mDaten, 1, @mCnt)
    END	

    WHILE (@mBreak > 0)
    BEGIN
      INSERT INTO dbo.crt_WAnlageLangTexte (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
      SET @mBreak = @mBreak - 1
    END

    IF @mEnde > 0 BREAK
    FETCH NEXT FROM CWA INTO @mDaten, @mCnt, @mBreak, @mEnde
  END

  IF LEN(@mText) > 0
  BEGIN
      INSERT INTO dbo.crt_WAnlageLangTexte (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
  END

  CLOSE CWA
  DEALLOCATE CWA
GO




IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_MakeWAuftragAnnahme')
	DROP PROCEDURE dbo.crp_MakeWAuftragAnnahme
GO

CREATE PROCEDURE dbo.crp_MakeWAuftragAnnahme
                                        @mIdent   varchar(8)
AS
  SET @mIdent = REPLACE(@mIdent,'.','')
  DECLARE @mLfdNr integer
  SET @mLfdNr = 1

  DECLARE   CWA CURSOR FOR 
    SELECT   Daten, CntChar, SoftBreak+LineFeeds, Abschluss
    FROM     dbo.Langtextzeilen 
    WHERE    Art = '31' AND Nr = (SELECT TOP 1 O.OptDataC FROM dbo.Optionen O WHERE O.OptId = ' 9924') 
    ORDER BY Lkz,Nr,LfdNr
  OPEN CWA
  
  DECLARE @mDaten  char(55)
  DECLARE @mCnt    integer
  DECLARE @mBreak  integer
  DECLARE @mEnde   integer
  DECLARE @mText   varchar(160)

  SET @mText   = ''
  FETCH NEXT FROM CWA INTO @mDaten, @mCnt, @mBreak, @mEnde
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF (LEN(@mText)+@mCnt) > 200
    BEGIN
      INSERT INTO dbo.crt_WAuftragAnnahme (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
    END

    IF (@mCnt > 0)
    BEGIN
      SET @mText = @mText + SUBSTRING(@mDaten, 1, @mCnt)
    END	

    WHILE (@mBreak > 0)
    BEGIN
      INSERT INTO dbo.crt_WAuftragAnnahme (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
      SET @mBreak = @mBreak - 1
    END

    IF @mEnde > 0 BREAK
    FETCH NEXT FROM CWA INTO @mDaten, @mCnt, @mBreak, @mEnde
  END

  IF LEN(@mText) > 0
  BEGIN
      INSERT INTO dbo.crt_WAuftragAnnahme (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
  END

  CLOSE CWA
  DEALLOCATE CWA
GO




IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_MakeWAuftragAbgabe')
	DROP PROCEDURE dbo.crp_MakeWAuftragAbgabe
GO

CREATE PROCEDURE dbo.crp_MakeWAuftragAbgabe
                                        @mIdent   varchar(8)
AS
  SET @mIdent = REPLACE(@mIdent,'.','')
  DECLARE @mLfdNr integer
  SET @mLfdNr = 1

  DECLARE   CWA CURSOR FOR 
    SELECT   Daten, CntChar, SoftBreak+LineFeeds, Abschluss
    FROM     dbo.Langtextzeilen 
    WHERE    Art = '31' AND Nr = (SELECT TOP 1 O.OptDataC FROM dbo.Optionen O WHERE O.OptId = ' 9925') 
    ORDER BY Lkz,Nr,LfdNr
  OPEN CWA
  
  DECLARE @mDaten  char(55)
  DECLARE @mCnt    integer
  DECLARE @mBreak  integer
  DECLARE @mEnde   integer
  DECLARE @mText   varchar(160)

  SET @mText   = ''
  FETCH NEXT FROM CWA INTO @mDaten, @mCnt, @mBreak, @mEnde
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF (LEN(@mText)+@mCnt) > 200
    BEGIN
      INSERT INTO dbo.crt_WAuftragAbgabe (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
    END

    IF (@mCnt > 0)
    BEGIN
      SET @mText = @mText + SUBSTRING(@mDaten, 1, @mCnt)
    END	

    WHILE (@mBreak > 0)
    BEGIN
      INSERT INTO dbo.crt_WAuftragAbgabe (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
      SET @mBreak = @mBreak - 1
    END

    IF @mEnde > 0 BREAK
    FETCH NEXT FROM CWA INTO @mDaten, @mCnt, @mBreak, @mEnde
  END

  IF LEN(@mText) > 0
  BEGIN
      INSERT INTO dbo.crt_WAuftragAbgabe (Ident, LfdNr, LineData) VALUES (@mIdent, STR(@mLfdNr,6), @mText)
      SET @mLfdNr = @mLfdNr + 1
      SET @mText  = ''
  END

  CLOSE CWA
  DEALLOCATE CWA
GO



IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAuftragsText')
	DROP PROCEDURE dbo.crp_WAuftragsText
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAnlageText')
	DROP PROCEDURE dbo.crp_WAnlageText
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAuftragAnnahme')
	DROP PROCEDURE dbo.crp_WAuftragAnnahme
GO
IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_WAuftragAbgabe')
	DROP PROCEDURE dbo.crp_WAuftragAbgabe
GO


CREATE PROCEDURE dbo.crp_WAuftragsText
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  DELETE FROM dbo.crt_WAuftragLangTexte WHERE Ident = @pIdent
  EXECUTE dbo.crp_MakeWAuftragLangText @pIdent
  SELECT LfdNr, LineData AS Daten FROM dbo.crt_WAuftragLangTexte WHERE Ident = @pIdent ORDER BY LfdNr
  DELETE FROM dbo.crt_WAuftragLangTexte WHERE Ident = @pIdent
GO

CREATE PROCEDURE dbo.crp_WAnlageText
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  DELETE FROM dbo.crt_WAnlageLangTexte WHERE Ident = @pIdent
  EXECUTE dbo.crp_MakeWAnlageLangText @pIdent
  SELECT LfdNr, LineData AS Daten FROM dbo.crt_WAnlageLangTexte WHERE Ident = @pIdent ORDER BY LfdNr
  DELETE FROM dbo.crt_WAnlageLangTexte WHERE Ident = @pIdent
GO

CREATE PROCEDURE dbo.crp_WAuftragAnnahme
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  DELETE FROM dbo.crt_WAuftragAnnahme WHERE Ident = @pIdent
  EXECUTE dbo.crp_MakeWAuftragAnnahme @pIdent
  SELECT LfdNr, LineData AS Daten FROM dbo.crt_WAuftragAnnahme WHERE Ident = @pIdent ORDER BY LfdNr
  DELETE FROM dbo.crt_WAuftragAnnahme WHERE Ident = @pIdent
GO

CREATE PROCEDURE dbo.crp_WAuftragAbgabe
			    @pIdent  varchar(8)
AS
  SET @pIdent = REPLACE(@pIdent,'.','')
  DELETE FROM dbo.crt_WAuftragAbgabe WHERE Ident = @pIdent
  EXECUTE dbo.crp_MakeWAuftragAbgabe @pIdent
  SELECT LfdNr, LineData AS Daten FROM dbo.crt_WAuftragAbgabe WHERE Ident = @pIdent ORDER BY LfdNr
  DELETE FROM dbo.crt_WAuftragAbgabe WHERE Ident = @pIdent
GO






--*****************************************************************************************************************
-----------------------  Index für Wartung --------------------------------------------------------------------
--*****************************************************************************************************************

IF 0 = (SELECT COUNT(*) FROM sysindexes si INNER JOIN sysobjects so 
        ON so.[xtype] = 'U' AND so.[name] = 'ut_WartungsTurnus' AND so.[id] = si.[id] AND si.[name] = 'WK_WTurnus1')
  CREATE INDEX WK_WTurnus1 ON dbo.ut_WartungsTurnus (AnlagenId,Monat)
GO

IF 0 = (SELECT COUNT(*) FROM sysindexes si INNER JOIN sysobjects so 
        ON so.[xtype] = 'U' AND so.[name] = 'ut_WartungsTypen' AND so.[id] = si.[id] AND si.[name] = 'WK_WTypen1')
  CREATE INDEX WK_WTypen1 ON dbo.ut_WartungsTypen (Ident)
GO

IF 0 = (SELECT COUNT(*) FROM sysindexes si INNER JOIN sysobjects so 
        ON so.[xtype] = 'U' AND so.[name] = 'ut_WartungsMessDaten' AND so.[id] = si.[id] AND si.[name] = 'WK_WMessDaten1')
  CREATE INDEX WK_WMessDaten1 ON dbo.ut_WartungsMessDaten (AnlagenId,Datum,LfdNr)
GO

IF 0 = (SELECT COUNT(*) FROM sysindexes si INNER JOIN sysobjects so 
        ON so.[xtype] = 'U' AND so.[name] = 'ut_WartungsTodo' AND so.[id] = si.[id] AND si.[name] = 'WK_WTodo1')
  CREATE INDEX WK_WTodo1 ON dbo.ut_WartungsTodo (AnlagenId,PosNr)
GO


