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


IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_ChefInfo')
	DROP PROCEDURE dbo.crp_ChefInfo
GO
/*
CREATE PROCEDURE dbo.crp_ChefInfo
                      @pJahr  varchar(4) = 'NULL'
                     ,@pPrj   varchar(8) = 'NULL'
                     ,@pKst   varchar(8) = 'NULL'
                     ,@pKtr   varchar(8) = 'NULL'
                     ,@pUlt   varchar(8) = 'NULL'
AS
BEGIN
DECLARE @m_pPrj INT
DECLARE @m_pKst INT
DECLARE @m_pKtr INT
DECLARE @m_pUlt INT
SET @m_pPrj = 0
SET @m_pKst = 0
SET @m_pKtr = 0
SET @m_pUlt = 0

IF @pJahr IS NULL OR @pJahr = 'NULL' OR ISNUMERIC(@pJahr) = 0 OR LEN(@pJahr) = 0
BEGIN
     SET @pJahr = CONVERT( varchar(4), YEAR(GETDATE()) )
END
ELSE
BEGIN
   SET @pJahr = LTRIM(RTRIM(@pJahr))
   IF LEN(@pJahr) <> 4
   BEGIN
     SET @pJahr = RIGHT('0'  + @pJahr, 2)
     SET @pJahr = RIGHT('20' + @pJahr, 4)
   END
END

IF @pPrj IS NOT NULL AND @pPrj <> 'NULL' AND LEN(@pPrj) > 0 AND @pPrj <> ''
    SET @m_pPrj = 1
IF @pKst IS NOT NULL AND @pKst <> 'NULL' AND LEN(@pKst) > 0 AND @pKst <> ''
    SET @m_pKst = 1
IF @pKtr IS NOT NULL AND @pKtr <> 'NULL' AND LEN(@pKtr) > 0 AND @pKtr <> ''
    SET @m_pKtr = 1
IF @pUlt = '1' OR UPPER(@pUlt) = 'JA'
    SET @m_pUlt = 1

CREATE TABLE dbo.#table_KontoSaldo( Konto char(8), Typ char(1), 
                                    Saldo_Alt float, Saldo_01 float, Saldo_02 float, Saldo_03 float, Saldo_04 float, 
                                    Saldo_05  float, Saldo_06 float, Saldo_07 float, Saldo_08 float,
                                    Saldo_09  float, Saldo_10 float, Saldo_11 float, Saldo_12 float,
                                    Saldo_00  float )
IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
BEGIN
  EXECUTE('
            ALTER TABLE dbo.#table_KontoSaldo ALTER COLUMN Konto  char(8) COLLATE database_default 
            ALTER TABLE dbo.#table_KontoSaldo ALTER COLUMN Typ    char(1) COLLATE database_default 
         ')
END

INSERT INTO dbo.#table_KontoSaldo( Konto, Typ, 
                                   Saldo_Alt, Saldo_01, Saldo_02, Saldo_03, Saldo_04, Saldo_05, Saldo_06,
                                   Saldo_07,  Saldo_08, Saldo_09, Saldo_10, Saldo_11, Saldo_12, Saldo_00 )
       SELECT DISTINCT
              K.Konto
             ,K.Typ
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr < @pJahr )                        AND 
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '01')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '02')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '03')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '04')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '05')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '06')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '07')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '08')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '09')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '10')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '11')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr ) AND (BU.Monat = '12')  AND
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
             ,ISNULL(( SELECT SUM( ISNULL(BU.Haben,0)-ISNULL(BU.Soll,0) )  FROM dbo.Buchungszeilen BU
                       WHERE ( BU.Konto = K.Konto ) AND (BU.Typ = K.Typ) AND 
                             ( BU.Jahr = @pJahr )                        AND 
                             ( @m_pUlt = 1 OR (BU.Tag <> '00' AND BU.Tag <> '32')) AND
                             --( BU.Tag <> '00') AND ( BU.Tag <> '32')     AND
                             ( @m_pPrj = 0 OR BU.PrjNr = @pPrj )         AND 
                             ( @m_pKst = 0 OR BU.Kst   = @pKst )         AND 
                             ( @m_pKtr = 0 OR BU.Ktr   = @pKtr ) ),0.00 )
       FROM dbo.Kontenplan K
       WHERE K.Konto <> '' AND ( K.Typ = 'S' OR K.Typ = 'P' )


SELECT
   B.RecId  ,B.MandId  ,B.LstNr   ,B.LfdNr  ,B.PosNr ,B.UPosNr
  ,B.PosTyp ,B.KtoTyp  ,B.KtoVon  ,B.KtoBis ,B.[Text]
  ,B.Tiefe  ,B.VzDruck ,B.VzSumme ,B.Still  ,B.Fett

  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_Alt, 0.00) ) FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_Vo
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_01, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_01
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_02, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_02
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_03, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_03
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_04, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_04
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_05, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_05
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_06, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_06
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_07, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_07
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_08, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_08
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_09, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_09
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_10, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_10
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_11, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_11
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_12, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_12
  ,ROUND(ISNULL( ( SELECT SUM( ISNULL(T.Saldo_00, 0.00) )  FROM dbo.#table_KontoSaldo T
                   WHERE ( B.KtoVon <= T.Konto ) AND ( B.KtoBis >= T.Konto ) AND ( B.KtoTyp = T.Typ ) ), 0.00),2) AS Saldo_Ge

FROM dbo.Bilanzzeilen B
WHERE ( B.LstNr = '0004' )
ORDER BY B.LfdNr

END
*/
--GO

-- =====================================================================================================================================
-- Baustellenspiegel

IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_BauSpiegel')
	DROP PROCEDURE dbo.crp_BauSpiegel
GO
CREATE PROCEDURE dbo.crp_BauSpiegel
AS
-- DECLARE @mProjekt varchar(8)
-- DECLARE @mKonto   varchar(8)

  CREATE TABLE dbo.#table_BauSpiegel(  Projekt char(8)  
                                      ,SollBrutto float ,SollNetto float ,SollStunden float 
                                      ,SollEkLohn float ,SollEkMat float ,SollEkFremd float ,SollEkGemein float  
                                      ,SollVkLohn float ,SollVkMat float ,SollVkFremd float
                                      ,IstStunden float ,IstEkLohn float ,IstEkMat    float ,IstEkFremd   float ,IstEkSonst float  
                                      ,IstVkLohn  float ,IstVkMat  float ,IstVkSonst  float ,IstVkUst     float ,IstBezahlt float  
                                    )
  IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
  BEGIN
    EXECUTE('
              ALTER TABLE dbo.#table_BauSpiegel ALTER COLUMN Projekt char(8) COLLATE database_default 
           ')
  END

  INSERT INTO dbo.#table_BauSpiegel(  Projekt 
                                     ,SollBrutto  ,SollNetto  ,SollStunden  
                                     ,SollEkLohn  ,SollEkMat  ,SollEkFremd ,SollEkGemein   
                                     ,SollVkLohn  ,SollVkMat  ,SollVkFremd 
                                     ,IstStunden  ,IstEkLohn  ,IstEkMat    ,IstEkFremd   ,IstEkSonst   
                                     ,IstVkLohn   ,IstVkMat   ,IstVkSonst  ,IstVkUst     ,IstBezahlt 
                                   )
       SELECT
          S.PrjNr
         ,ISNULL( (SELECT SUM(B.BRUTTO)                   FROM dbo.Belege B
                   WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7'), 0.00 ) -- SollBrutto
         ,ISNULL( (SELECT SUM(B.NETTO)                    FROM dbo.Belege B
                   WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7'), 0.00)  -- SollNetto
         ,ISNULL( (SELECT SUM(B.SUMMEZEIT)                FROM dbo.Belege B
                   WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7'), 0.00)  -- SollStunden
         ,ISNULL( (SELECT SUM(B.MITTLOHN * B.SUMMEZEIT)   FROM dbo.Belege B
                   WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7'), 0.00)  -- SollEkLohn
         ,ISNULL( (SELECT SUM(B.SUMMEEKM)                 FROM dbo.Belege B
                   WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7'), 0.00)  -- SollEKMat
         ,ISNULL( (SELECT SUM(B.SUMMEEKFR)                FROM dbo.Belege B
                   WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7'), 0.00)  -- SollEKFremd
         ,ISNULL( (SELECT SUM(B.GEMEINK)                  FROM dbo.Belege B
                   WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7'), 0.00)  -- SollEKGemein
         ,ISNULL( (SELECT SUM(B.SUMMELOHN)                FROM dbo.Belege B
                   WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7'), 0.00)  -- SollVKLohn
         ,ISNULL( (SELECT SUM(B.SUMMEMAT)                 FROM dbo.Belege B
                   WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7'), 0.00)  -- SollVKMat
         ,ISNULL( (SELECT SUM(B.SUMMEFREMD)               FROM dbo.Belege B
                   WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7'), 0.00)  -- SollVKFremd

         ,ISNULL( (SELECT SUM(BZ.Soll - BZ.Haben) FROM dbo.Buchungszeilen BZ INNER JOIN dbo.Kontenplan KP
                   ON BZ.PrjNr = S.PrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto AND KP.KzPrj = 1),  0.00)  --IstStunden 
         ,ISNULL( (SELECT SUM(BZ.Soll - BZ.Haben) FROM dbo.Buchungszeilen BZ INNER JOIN dbo.Kontenplan KP
                   ON BZ.PrjNr = S.PrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto AND KP.KzPrj = 2),  0.00)  --IstEkLohn 
         ,ISNULL( (SELECT SUM(BZ.Soll - BZ.Haben) FROM dbo.Buchungszeilen BZ INNER JOIN dbo.Kontenplan KP
                   ON BZ.PrjNr = S.PrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto AND KP.KzPrj = 3),  0.00)  --IstEkMat 
         ,ISNULL( (SELECT SUM(BZ.Soll - BZ.Haben) FROM dbo.Buchungszeilen BZ INNER JOIN dbo.Kontenplan KP
                   ON BZ.PrjNr = S.PrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto AND KP.KzPrj = 4),  0.00)  --IstEkFremd 
         ,ISNULL( (SELECT SUM(BZ.Soll - BZ.Haben) FROM dbo.Buchungszeilen BZ INNER JOIN dbo.Kontenplan KP
                   ON BZ.PrjNr = S.PrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto AND KP.KzPrj = 5),  0.00)  --IstEkSonst 
         ,ISNULL( (SELECT SUM(BZ.Soll - BZ.Haben) FROM dbo.Buchungszeilen BZ INNER JOIN dbo.Kontenplan KP
                   ON BZ.PrjNr = S.PrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto AND KP.KzPrj = 12), 0.00)  --IstVkLohn 
         ,ISNULL( (SELECT SUM(BZ.Soll - BZ.Haben) FROM dbo.Buchungszeilen BZ INNER JOIN dbo.Kontenplan KP
                   ON BZ.PrjNr = S.PrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto AND KP.KzPrj = 13), 0.00)  --IstVkMat 
         ,ISNULL( (SELECT SUM(BZ.Soll - BZ.Haben) FROM dbo.Buchungszeilen BZ INNER JOIN dbo.Kontenplan KP
                   ON BZ.PrjNr = S.PrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto AND KP.KzPrj = 15), 0.00)  --IstVkSonst 
         ,ISNULL( (SELECT SUM(BZ.Soll - BZ.Haben) FROM dbo.Buchungszeilen BZ INNER JOIN dbo.Kontenplan KP
                   ON BZ.PrjNr = S.PrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto AND KP.KzPrj = 19), 0.00)  --IstVkUst 

         --,ISNULL( (SELECT SUM(Haben) FROM dbo.Buchungszeilen BZ left join ut_BelTransaction BT on BZ.TransNo = BT.TransNo
		 --		WHERE BZ.PrjNr = S.PrjNr AND BZ.Typ = 'P' AND ISNULL(BT.[Status],1) = 1
		 --		  AND BZ.Konto = (SELECT TOP 1 SUBSTRING(B.DEBKONTO,1,8) FROM dbo.Belege B WHERE B.PRJNR = S.PrjNr AND B.BELEGART = ' 7')
		 --		), 0.00)   -- IstBezahlt

         ,ISNULL( (SELECT SUM(BZ.Soll - BZ.Haben) FROM dbo.Buchungszeilen BZ INNER JOIN dbo.Kontenplan KP
                   ON BZ.PrjNr = S.PrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto AND KP.KzPrj = 28
				   inner join ut_BelTransaction BT on BZ.TransNo = BT.TransNo and BT.[Status] = 1
		    ), 0.00)  --IstBezahlt

       FROM dbo.Statusfelder S
       WHERE S.StatId = 'PRJ STAT B'

  SELECT 
     T.Projekt 
    ,T.SollBrutto  ,T.SollNetto  ,T.SollStunden  
    ,T.SollEkLohn  ,T.SollEkMat  ,T.SollEkFremd ,T.SollEkGemein   
    ,T.SollVkLohn  ,T.SollVkMat  ,T.SollVkFremd 
    ,T.IstStunden  ,T.IstEkLohn  ,T.IstEkMat    ,T.IstEkFremd   ,T.IstEkSonst   
    ,T.IstVkLohn   ,T.IstVkMat   ,T.IstVkSonst  ,T.IstVkUst     ,T.IstBezahlt 

    ,ISNULL((SELECT TOP 1 B.VORHABEN FROM dbo.Belege B 
             WHERE B.PRJNR = T.Projekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '') AS VORHABEN
    ,ISNULL((SELECT TOP 1 B.BETREFF1 FROM dbo.Belege B 
             WHERE B.PRJNR = T.Projekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '') AS BETREFF1
    ,ISNULL((SELECT TOP 1 B.BETREFF2 FROM dbo.Belege B 
             WHERE B.PRJNR = T.Projekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '') AS BETREFF2
    ,ISNULL((SELECT TOP 1 B.BETREFF3 FROM dbo.Belege B 
             WHERE B.PRJNR = T.Projekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '') AS BETREFF3

    ,ISNULL((SELECT TOP 1 B.KDNR  FROM dbo.Belege B 
             WHERE B.PRJNR = T.Projekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '') AS KDNR
    ,ISNULL((SELECT TOP 1 B.NAME1 FROM dbo.Belege B 
             WHERE B.PRJNR = T.Projekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '') AS 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.#table_BauSpiegel T
  LEFT  JOIN dbo.Anschriften AN
          ON AN.Nr = '01'
GO


-- =====================================================================================================================================
-- Baustellenspiegel Detailauskunft

IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_DetailBauSpiegel')
	DROP PROCEDURE dbo.crp_DetailBauSpiegel
GO
CREATE PROCEDURE dbo.crp_DetailBauSpiegel
                                    @pProjekt varchar(8)
AS
DECLARE @mVorhaben AS varchar(10)
DECLARE @mBetreff1 AS varchar(42)
DECLARE @mBetreff2 AS varchar(42)
DECLARE @mBetreff3 AS varchar(42)
DECLARE @mKunde    AS varchar(15)
DECLARE @mName1    AS varchar(40)

SET @mVorhaben = ISNULL((SELECT TOP 1 B.VORHABEN FROM dbo.Belege B 
                         WHERE B.PRJNR = @pProjekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '')
SET @mBetreff1 = ISNULL((SELECT TOP 1 B.BETREFF1 FROM dbo.Belege B 
                         WHERE B.PRJNR = @pProjekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '')
SET @mBetreff2 = ISNULL((SELECT TOP 1 B.BETREFF2 FROM dbo.Belege B 
                         WHERE B.PRJNR = @pProjekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '')
SET @mBetreff3 = ISNULL((SELECT TOP 1 B.BETREFF3 FROM dbo.Belege B 
                         WHERE B.PRJNR = @pProjekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '')

SET @mKunde = ISNULL((SELECT TOP 1 B.KDNR  FROM dbo.Belege B 
                      WHERE B.PRJNR = @pProjekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '')
SET @mName1 = ISNULL((SELECT TOP 1 B.NAME1 FROM dbo.Belege B 
                      WHERE B.PRJNR = @pProjekt AND B.BELEGART = ' 7' ORDER BY B.BELEGNR), '')

SELECT

  @pProjekt   AS Projekt
 ,@mVorhaben  AS Vorhaben
 ,@mBetreff1  AS Betreff1
 ,@mBetreff2  AS Betreff2
 ,@mBetreff3  AS Betreff3
 ,@mKunde     AS Kunde
 ,@mName1     AS Name1

 ,BZ.Typ ,BZ.Konto ,BZ.Jahr ,BZ.Monat ,BZ.Tag ,BZ.TransNo ,BZ.Soll ,BZ.Haben
 ,BZ.[Text] ,BZ.Kst ,BZ.Ktr ,BZ.PrjNr ,BZ.BelNr ,BZ.AusglTRN ,BZ.BelDatum ,BZ.BelNrExt 

 ,KP.Schl ,KP.Passiv ,KP.[Name] ,KP.KzKasse ,KP.KzUst ,KP.KzPrj

FROM dbo.Buchungszeilen BZ
LEFT JOIN dbo.Kontenplan KP
       ON BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto
WHERE (BZ.PrjNr = @pProjekt) AND
      ((KP.KzPrj = 1)  OR (KP.KzPrj = 2)  OR (KP.KzPrj = 3)  OR (KP.KzPrj = 4) OR (KP.KzPrj = 5) OR
       (KP.KzPrj = 12) OR (KP.KzPrj = 13) OR (KP.KzPrj = 15) OR (KP.KzPrj = 19))
ORDER BY KP.KzPrj, KP.Typ, KP.Konto
GO


-- =====================================================================================================================================

IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_Mahnwesen')
	DROP PROCEDURE dbo.crp_Mahnwesen
GO
CREATE PROCEDURE dbo.crp_Mahnwesen
                                     @BelArt    varchar(2)
                                    ,@KdNr      varchar(15)
                                    ,@PrjNr     varchar(8)
                                    ,@MahnStufe varchar(2)
                                    ,@Status    varchar(1)
AS
IF ISNUMERIC(@MahnStufe) = 0 SET @MahnStufe = '0'
IF @Status = '1' SET @Status = ' '
IF @Status = '2' SET @Status = 'G'
IF @Status <> ' ' AND @Status <> 'G' SET @Status = '0'

SELECT
         Z.RecId ,Z.MandId ,Z.BELART ,Z.BELNR ,Z.ADRESSNR, Z.BELDATUM
        ,Z.PRJNR ,Z.Brutto ,Z.Netto ,Z.Mwst ,Z.MwstSatz ,Z.SkontoTage 
        ,Z.SkontoSatz ,Z.MahnPlan ,Z.MahnStufe ,Z.Vorhaben ,Z.Betreff1 
        ,Z.Name1 ,Z.Waehrung ,Z.Eurokurs ,Z.BezahltSum ,Z.Faelligkeit
        ,Z.FDatum ,Z.SDatum ,Z.Status ,Z.DebKonto

        ,ISNULL(B.BELARTTEXT,'')      AS Beleg_BELARTTEXT
        ,ISNULL(B.TAG,'')             AS Beleg_TAG
        ,ISNULL(B.MONAT,'')           AS Beleg_MONAT
        ,ISNULL(B.JAHR,'')            AS Beleg_JAHR
        ,ISNULL(B.PHASE,'')           AS Beleg_PHASE
        ,ISNULL(B.VORHABEN,'')        AS Beleg_VORHABEN
        ,ISNULL(B.BETREFF1,'')        AS Beleg_BETREFF1
        ,ISNULL(B.BETREFF2,'')        AS Beleg_BETREFF2
        ,ISNULL(B.BETREFF3,'')        AS Beleg_BETREFF3
        ,ISNULL(B.UNSERZEI,'')        AS Beleg_UZ
        ,ISNULL(B.WAEHRUNG,'')        AS Beleg_WZ
        ,ISNULL(B.KST,'')             AS Beleg_KST
        ,ISNULL(B.KTR,'')             AS Beleg_KTR
        ,ISNULL(B.GAEBNAME,'')        AS Beleg_GAEBNAME
        ,ISNULL(B.GAEBPHASE,'')       AS Beleg_GAEBPHASE

        ,ISNULL(BA0.DestDebNr,'')     AS KD_DestDebNr
        ,ISNULL(BA0.Suchen,'')        AS KD_Suchen
        ,ISNULL(BA0.Anrede,'')        AS KD_Anrede
        ,ISNULL(BA0.Vorname,'')       AS KD_Vorname
        ,ISNULL(BA0.Name1,'')         AS KD_Name1
        ,ISNULL(BA0.Name2,'')         AS KD_Name2
        ,ISNULL(BA0.Strasse,'')       AS KD_Strasse
        ,ISNULL(BA0.Ort,'')           AS KD_Ort
        ,ISNULL(BA0.Plz,'')           AS KD_Plz
        ,ISNULL(BA0.Telefon1,'')      AS KD_Telefon1
        ,ISNULL(BA0.Telefon2,'')      AS KD_Telefon2
        ,ISNULL(BA0.FaxNummer,'')     AS KD_FaxNummer
        ,ISNULL(BA0.EMail,'')         AS KD_EMail
        ,ISNULL(BA0.FibuKonto,'')     AS KD_FibuKonto

        ,ISNULL(BA1.DestDebNr,'')     AS BL_DestDebNr
        ,ISNULL(BA1.Suchen,'')        AS BL_Suchen
        ,ISNULL(BA1.Anrede,'')        AS BL_Anrede
        ,ISNULL(BA1.Vorname,'')       AS BL_Vorname
        ,ISNULL(BA1.Name1,'')         AS BL_Name1
        ,ISNULL(BA1.Name2,'')         AS BL_Name2
        ,ISNULL(BA1.Strasse,'')       AS BL_Strasse
        ,ISNULL(BA1.Ort,'')           AS BL_Ort
        ,ISNULL(BA1.Plz,'')           AS BL_Plz
        ,ISNULL(BA1.Telefon1,'')      AS BL_Telefon1
        ,ISNULL(BA1.Telefon2,'')      AS BL_Telefon2
        ,ISNULL(BA1.FaxNummer,'')     AS BL_FaxNummer
        ,ISNULL(BA1.EMail,'')         AS BL_EMail

	,ISNULL(Z.Waehrung  ,'')                                             AS FirmenWaehrung
	,ISNULL(M.WarteTage ,0 )                                             AS WarteTage
	,ROUND(ISNULL(Z.Brutto     / ISNULL(NULLIF(Z.Eurokurs,0),1), 0) ,2)  AS EuroBrutto
	,ROUND(ISNULL(Z.Netto      / ISNULL(NULLIF(Z.Eurokurs,0),1), 0) ,2)  AS EuroNetto
	,ROUND(ISNULL(Z.BezahltSum / ISNULL(NULLIF(Z.Eurokurs,0),1), 0) ,2)  AS EuroBezahltSum

	,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.Plz      ,'')                                              AS K_Plz
	,ISNULL(A.Telefon1 ,'')                                              AS K_Telefon

	,AN.Nr                                                               AS 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.ZahlBelege Z
LEFT  JOIN dbo.Belege B
        ON B.PRJNR = Z.PRJNR AND B.BELEGART = Z.BELART AND B.BELEGNR = Z.BELNR
LEFT  JOIN dbo.BelAdresse BA0
        ON BA0.PrjNr = Z.PRJNR AND BA0.BelArt = Z.BELART AND BA0.BelNr = Z.BELNR AND BA0.LfdNr = 0
LEFT  JOIN dbo.BelAdresse BA1
        ON BA1.PrjNr = Z.PRJNR AND BA1.BelArt = Z.BELART AND BA1.BelNr = Z.BELNR AND BA1.LfdNr = 1
LEFT  JOIN dbo.Adressen A
        ON A.DESTDEBNR = Z.ADRESSNR
LEFT  JOIN dbo.Mahnplan M
        ON M.Stufe = Z.MahnStufe 
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (@BelArt    = ''  OR @BelArt    = '0' OR @BelArt    = 'N'    OR @BelArt    = Z.BELART)    AND
      (@PrjNr     = ''  OR @PrjNr     = '0' OR @PrjNr     = 'NULL' OR @PrjNr     = Z.PRJNR)     AND
      (@KdNr      = ''  OR @KdNr      = '0' OR @KdNr      = 'NULL' OR @KdNr      = Z.ADRESSNR)  AND
      (@MahnStufe = ''  OR @MahnStufe = '0' OR @MahnStufe = '00'   OR @MahnStufe = Z.MahnStufe) AND
      (@Status    = '0' OR @Status    = '0' OR @Status    = 'N'    OR @Status    = Z.Status)
GO


-- =====================================================================================================================================

IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'P' and [name] = 'crp_MahnwesenEx')
	DROP PROCEDURE dbo.crp_MahnwesenEx
GO
CREATE PROCEDURE dbo.crp_MahnwesenEx
                                     @BelArt    varchar(2)
                                    ,@KdNr      varchar(15)
                                    ,@PrjNr     varchar(8)
                                    ,@MahnStufe varchar(2)
                                    ,@Status    varchar(1)
AS
IF ISNUMERIC(@MahnStufe) = 0 SET @MahnStufe = '0'
IF @Status = '1' SET @Status = ' '
IF @Status = '2' SET @Status = 'G'
IF @Status <> ' ' AND @Status <> 'G' SET @Status = '0'

SELECT
         Z.RecId ,Z.MandId ,Z.BELART ,Z.BELNR ,Z.ADRESSNR, Z.BELDATUM
        ,Z.PRJNR ,Z.Brutto ,Z.Netto ,Z.Mwst ,Z.MwstSatz ,Z.SkontoTage 
        ,Z.SkontoSatz ,Z.MahnPlan ,Z.MahnStufe ,Z.Vorhaben ,Z.Betreff1 
        ,Z.Name1 ,Z.Waehrung ,Z.Eurokurs ,Z.BezahltSum ,Z.Faelligkeit
        ,Z.FDatum ,Z.SDatum ,Z.Status ,Z.DebKonto

        ,ISNULL(B.BELARTTEXT,'')      AS Beleg_BELARTTEXT
        ,ISNULL(B.TAG,'')             AS Beleg_TAG
        ,ISNULL(B.MONAT,'')           AS Beleg_MONAT
        ,ISNULL(B.JAHR,'')            AS Beleg_JAHR
        ,ISNULL(B.PHASE,'')           AS Beleg_PHASE
        ,ISNULL(B.VORHABEN,'')        AS Beleg_VORHABEN
        ,ISNULL(B.BETREFF1,'')        AS Beleg_BETREFF1
        ,ISNULL(B.BETREFF2,'')        AS Beleg_BETREFF2
        ,ISNULL(B.BETREFF3,'')        AS Beleg_BETREFF3
        ,ISNULL(B.UNSERZEI,'')        AS Beleg_UZ
        ,ISNULL(B.WAEHRUNG,'')        AS Beleg_WZ
        ,ISNULL(B.KST,'')             AS Beleg_KST
        ,ISNULL(B.KTR,'')             AS Beleg_KTR
        ,ISNULL(B.GAEBNAME,'')        AS Beleg_GAEBNAME
        ,ISNULL(B.GAEBPHASE,'')       AS Beleg_GAEBPHASE

        ,ISNULL(BA0.DestDebNr,'')     AS KD_DestDebNr
        ,ISNULL(BA0.Suchen,'')        AS KD_Suchen
        ,ISNULL(BA0.Anrede,'')        AS KD_Anrede
        ,ISNULL(BA0.Vorname,'')       AS KD_Vorname
        ,ISNULL(BA0.Name1,'')         AS KD_Name1
        ,ISNULL(BA0.Name2,'')         AS KD_Name2
        ,ISNULL(BA0.Strasse,'')       AS KD_Strasse
        ,ISNULL(BA0.Ort,'')           AS KD_Ort
        ,ISNULL(BA0.Plz,'')           AS KD_Plz
        ,ISNULL(BA0.Telefon1,'')      AS KD_Telefon1
        ,ISNULL(BA0.Telefon2,'')      AS KD_Telefon2
        ,ISNULL(BA0.FaxNummer,'')     AS KD_FaxNummer
        ,ISNULL(BA0.EMail,'')         AS KD_EMail
        ,ISNULL(BA0.FibuKonto,'')     AS KD_FibuKonto

        ,ISNULL(BA1.DestDebNr,'')     AS BL_DestDebNr
        ,ISNULL(BA1.Suchen,'')        AS BL_Suchen
        ,ISNULL(BA1.Anrede,'')        AS BL_Anrede
        ,ISNULL(BA1.Vorname,'')       AS BL_Vorname
        ,ISNULL(BA1.Name1,'')         AS BL_Name1
        ,ISNULL(BA1.Name2,'')         AS BL_Name2
        ,ISNULL(BA1.Strasse,'')       AS BL_Strasse
        ,ISNULL(BA1.Ort,'')           AS BL_Ort
        ,ISNULL(BA1.Plz,'')           AS BL_Plz
        ,ISNULL(BA1.Telefon1,'')      AS BL_Telefon1
        ,ISNULL(BA1.Telefon2,'')      AS BL_Telefon2
        ,ISNULL(BA1.FaxNummer,'')     AS BL_FaxNummer
        ,ISNULL(BA1.EMail,'')         AS BL_EMail

	,ISNULL(Z.Waehrung  ,'')                                             AS FirmenWaehrung
	,ISNULL(M.WarteTage ,0 )                                             AS WarteTage
	,ROUND(ISNULL(Z.Brutto     / ISNULL(NULLIF(Z.Eurokurs,0),1), 0) ,2)  AS EuroBrutto
	,ROUND(ISNULL(Z.Netto      / ISNULL(NULLIF(Z.Eurokurs,0),1), 0) ,2)  AS EuroNetto
	,ROUND(ISNULL(Z.BezahltSum / ISNULL(NULLIF(Z.Eurokurs,0),1), 0) ,2)  AS EuroBezahltSum

	,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.Plz      ,'')                                              AS K_Plz
	,ISNULL(A.Telefon1 ,'')                                              AS K_Telefon

	,ISNULL(ZO.Datum  ,'')                                               AS ZO_Datum
	,ISNULL(ZO.Betrag ,0)                                                AS ZO_Betrag
	,ISNULL(ZO.Waehrung  ,'')                                            AS ZO_Waehrung
	,ISNULL(ZO.Kurs ,0)                                                  AS ZO_Kurs
	,ISNULL(ZO.Ausgl ,0)                                                 AS ZO_Ausgl
	,ISNULL(ZO.BuchText  ,'')                                            AS ZO_Text
	,ISNULL(ZO.FibuKtoVon  ,'')                                          AS ZO_FibuKtoVon
	,ISNULL(ZO.FibuKtoAuf  ,'')                                          AS ZO_FibuKtoAuf
	,ISNULL(ZO.BankKtoVon  ,'')                                          AS ZO_BankKtoVon
	,ISNULL(ZO.BankKtoAuf  ,'')                                          AS ZO_BankKtoAuf
	,ISNULL(ZO.BLZVon  ,'')                                              AS ZO_BLZVon
	,ISNULL(ZO.BLZAuf  ,'')                                              AS ZO_BLZAuf

	,AN.Nr                                                               AS 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.ZahlBelege Z
LEFT  JOIN dbo.Belege B
        ON B.PRJNR = Z.PRJNR AND B.BELEGART = Z.BELART AND B.BELEGNR = Z.BELNR
LEFT  JOIN dbo.BelAdresse BA0
        ON BA0.PrjNr = Z.PRJNR AND BA0.BelArt = Z.BELART AND BA0.BelNr = Z.BELNR AND BA0.LfdNr = 0
LEFT  JOIN dbo.BelAdresse BA1
        ON BA1.PrjNr = Z.PRJNR AND BA1.BelArt = Z.BELART AND BA1.BelNr = Z.BELNR AND BA1.LfdNr = 1
LEFT  JOIN dbo.Adressen A
        ON A.DESTDEBNR = Z.ADRESSNR
LEFT  JOIN dbo.Mahnplan M
        ON M.Stufe = Z.MahnStufe 
LEFT  JOIN dbo.ZahlOp ZO
        ON ZO.PrjNr = Z.PRJNR AND ZO.BelArt = Z.BELART AND ZO.BelNr = Z.BELNR
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (@BelArt    = ''  OR @BelArt    = '0' OR @BelArt    = 'N'    OR @BelArt    = Z.BELART)    AND
      (@PrjNr     = ''  OR @PrjNr     = '0' OR @PrjNr     = 'NULL' OR @PrjNr     = Z.PRJNR)     AND
      (@KdNr      = ''  OR @KdNr      = '0' OR @KdNr      = 'NULL' OR @KdNr      = Z.ADRESSNR)  AND
      (@MahnStufe = ''  OR @MahnStufe = '0' OR @MahnStufe = '00'   OR @MahnStufe = Z.MahnStufe) AND
      (@Status    = '0' OR @Status    = '0' OR @Status    = 'N'    OR @Status    = Z.Status)
GO

