
IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_Auskunftsblatt')
  DROP PROCEDURE dbo.crp_Auskunftsblatt
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'V' and [name] = 'crv_BelegListe')
  DROP VIEW dbo.crv_BelegListe
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_ZArbeitnehmer')
  DROP PROCEDURE dbo.crp_ZArbeitnehmer
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_ZBaustellen')
  DROP PROCEDURE dbo.crp_ZBaustellen
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_ZProjekte')
  DROP PROCEDURE dbo.crp_ZProjekte
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_ZDatum')
  DROP PROCEDURE dbo.crp_ZDatum
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_SollIstAlleArbeitnehmer')
  DROP PROCEDURE dbo.crp_SollIstAlleArbeitnehmer
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_SollIstArbeitnehmer')
  DROP PROCEDURE dbo.crp_SollIstArbeitnehmer
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'V' and [name] = 'crv_Zahlungen')
  DROP VIEW dbo.crv_Zahlungen
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'V' and [name] = 'crv_OPListe')
  DROP VIEW dbo.crv_OPListe
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'V' and [name] = 'crv_OPKreditoren')
  DROP VIEW dbo.crv_OPKreditoren
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_BelegStatistik')
  DROP PROCEDURE dbo.crp_BelegStatistik
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_MesseBeleg')
  DROP PROCEDURE dbo.crp_MesseBeleg
GO
IF 0 < (select count(*) from sysobjects where [xtype] = 'P' and [name] = 'crp_MesseBelegText')
  DROP PROCEDURE dbo.crp_MesseBelegText
GO




CREATE PROCEDURE dbo.crp_Auskunftsblatt
			    @FltKey char(18) = 'NULL'
AS
DECLARE @PrjNr    char(8)
DECLARE @BelegNr  char(8)
DECLARE @BelegArt char(2)

set @PrjNr    = LEFT ( @FltKey, 8)
set @BelegNr  = RIGHT( @FltKey, 8)
set @BelegArt = SUBSTRING( @FltKey, 9, 2)

DECLARE @projekt  char(8), @nummer   char(8), @belnr    char(8), @belart   char(2)
DECLARE @ekmat    float,   @eklohn   float,   @ekfremd  float,   @ekzeit   float
DECLARE @vkmat    float,   @vklohn   float,   @vkfremd  float,   @vkzeit   float
DECLARE @eurokurs float,   @zeit     float,   @aufwand  float
set @projekt  = ''  set @nummer   = ''  set @belnr    = ''  set @belart   = ''
set @ekmat    = 0   set @eklohn   = 0   set @ekfremd  = 0   set @ekzeit   = 0
set @vkmat    = 0   set @vklohn   = 0   set @vkfremd  = 0   set @vkzeit   = 0
set @eurokurs = 0   set @zeit     = 0   set @aufwand  = 0

DECLARE @ekmatsoll   float, @eklohnsoll  float, @ekfremdsoll float, @ekzeitsoll  float
DECLARE @vkmatsoll   float, @vklohnsoll  float, @vkfremdsoll float, @vkzeitsoll  float
DECLARE @ekmatist    float, @eklohnist   float, @ekfremdist  float, @ekzeitist   float
DECLARE @vkmatist    float, @vklohnist   float, @vkfremdist  float, @vkzeitist   float
set @ekmatsoll   = 0  set @eklohnsoll  = 0  set @ekfremdsoll = 0  set @ekzeitsoll  = 0
set @vkmatsoll   = 0  set @vklohnsoll  = 0  set @vkfremdsoll = 0  set @vkzeitsoll  = 0
set @ekmatist    = 0  set @eklohnist   = 0  set @ekfremdist  = 0  set @ekzeitist   = 0
set @vkmatist    = 0  set @vklohnist   = 0  set @vkfremdist  = 0  set @vkzeitist   = 0

DECLARE @stammkurs float
set @stammkurs = ISNULL( ( select ISNULL( W.Eurokurs ,1 ) from dbo.Waehrungen W inner join Optionen O 
                                                          on O.OptId = ' 7008'  where W.Kuerzel = O.OptDataC
                       ), 1 )

CREATE TABLE dbo.#Tmp( MandId int ,Projekt char(8) ,BelNr char(8) ,BelArt char(2) ,
                    Ek_Mat_SOLL float, Ek_Lohn_SOLL float, Ek_Fremd_SOLL float, Ek_Zeit_SOLL float, 
	            Vk_Mat_SOLL float, Vk_Lohn_SOLL float, Vk_Fremd_SOLL float, Vk_Zeit_SOLL float,
		    Ek_Mat_IST  float, Ek_Lohn_IST  float, Ek_Fremd_IST  float, Ek_Zeit_IST  float, 
	            Vk_Mat_IST  float, Vk_Lohn_IST  float, Vk_Fremd_IST  float, Vk_Zeit_IST  float
		  )
  IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
  BEGIN
   execute('
            ALTER TABLE dbo.#Tmp ALTER COLUMN Projekt char(8) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN BelNr   char(8) COLLATE database_default
            ALTER TABLE dbo.#Tmp ALTER COLUMN BelArt  char(2) COLLATE database_default
          ')
  END

  DECLARE beleg_cursor CURSOR FOR 
	SELECT P.Projekt,P.Nummer,P.Belnr,P.Belart,BP.Zeit,BP.Aufwand,
               BP.EkMat,BP.EkLohn,BP.EkFremd,BP.VkMat,BP.VkLohn,BP.VkFremd,
               ISNULL(NULLIF(B.KURSZUEURO, 0) ,1) 
	FROM dbo.Projektueberwachung P
	INNER JOIN dbo.Belege B
	        ON P.Projekt = B.PRJNR And P.Belnr = B.BELEGNR And P.Belart = B.BELEGART
	INNER JOIN dbo.BelPos BP
	        ON BP.PrjNr = P.Projekt And P.Belnr = BP.BelNr And P.Belart = BP.BelArt And 
                   BP.Ebene = 0 And BP.SatzArt = 1
	WHERE P.Nummer = @BelegNr
  OPEN beleg_cursor

  FETCH NEXT FROM beleg_cursor
  INTO @projekt,@nummer,@belnr,@belart,@zeit,@aufwand,
       @ekmat,@eklohn,@ekfremd,@vkmat,@vklohn,@vkfremd,
       @eurokurs
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @belart = ' 0' Or @belart = ' 8' Or @belart = ' 7'
    BEGIN
      set @ekmatsoll   = @ekmatsoll   + ISNULL( @ekmat   * @stammkurs / @eurokurs , 0)
      set @eklohnsoll  = @eklohnsoll  + ISNULL( @eklohn  * @stammkurs / @eurokurs , 0)
      set @ekfremdsoll = @ekfremdsoll + ISNULL( @ekfremd * @stammkurs / @eurokurs , 0)
      set @ekzeitsoll  = @ekzeitsoll  + ISNULL( @zeit    * @aufwand   / 60        , 0)
      set @vkmatsoll   = @vkmatsoll   + ISNULL( @vkmat   * @stammkurs / @eurokurs , 0)
      set @vklohnsoll  = @vklohnsoll  + ISNULL( @vklohn  * @stammkurs / @eurokurs , 0)
      set @vkfremdsoll = @vkfremdsoll + ISNULL( @vkfremd * @stammkurs / @eurokurs , 0)
      set @vkzeitsoll  = @vkzeitsoll  + ISNULL( @zeit    * @aufwand   / 60        , 0)
    END
    IF @belart = ' 5' Or @belart = ' 6' Or @belart = ' 9'
    BEGIN
      set @ekmatist   = @ekmatist   + ISNULL( @ekmat   * @stammkurs / @eurokurs , 0)
      set @ekfremdist = @ekfremdist + ISNULL( @ekfremd * @stammkurs / @eurokurs , 0)
    END
    IF @belart = ' 1' Or @belart = ' 3' Or @belart = ' 4'
    BEGIN
      set @vkmatist   = @vkmatist   + ISNULL( @vkmat    * @stammkurs / @eurokurs , 0)
      set @vklohnist  = @vklohnist  + ISNULL( @vklohn   * @stammkurs / @eurokurs , 0)
      set @vkfremdist = @vkfremdist + ISNULL( @vkfremd  * @stammkurs / @eurokurs , 0)
      set @vkzeitist  = @vkzeitist  + ISNULL( @zeit     * @aufwand   / 60        , 0)
    END
    IF @belart = ' 2'
    BEGIN
      set @vkmatist   = @vkmatist   - ISNULL( @vkmat    * @stammkurs / @eurokurs , 0)
      set @vklohnist  = @vklohnist  - ISNULL( @vklohn   * @stammkurs / @eurokurs , 0)
      set @vkfremdist = @vkfremdist - ISNULL( @vkfremd  * @stammkurs / @eurokurs , 0)
      set @vkzeitist  = @vkzeitist  - ISNULL( @zeit     * @aufwand   / 60        , 0)
    END
    FETCH NEXT FROM beleg_cursor
    INTO @projekt,@nummer,@belnr,@belart,@zeit,@aufwand,
         @ekmat,@eklohn,@ekfremd,@vkmat,@vklohn,@vkfremd,
         @eurokurs
  END
  CLOSE beleg_cursor
  DEALLOCATE beleg_cursor

  set @eklohnist  = ISNULL( (SELECT SUM( (Z.stunden * Z.eklohn * ISNULL(L.ekfaktor,1) + Z.betrag) / ISNULL(NULLIF(Z.eurokurs,0),1) )
	                     FROM dbo.Projektueberwachung P
	                     INNER JOIN dbo.Zeiterfassung Z
	                             ON Z.art = P.Belart And Z.belegnr = P.Belnr 
	                     INNER JOIN dbo.Lohnarten L
	                             ON Z.lohnart = L.lohnart
	                     WHERE @BelegNr = P.Nummer
	                  ),0)
  set @ekzeitist  = ISNULL( (select SUM( Z.stunden )
                             from dbo.Projektueberwachung P
	                     INNER JOIN dbo.Zeiterfassung Z
	                             ON Z.art = P.Belart And Z.belegnr = P.Belnr 
	                     INNER JOIN dbo.Lohnarten L
	                             ON Z.lohnart = L.lohnart And L.bsoll = 1
	                     WHERE @BelegNr = P.Nummer
	                  ) ,0)

  set @ekmatsoll = @ekmatsoll - @ekfremdsoll
  set @vkmatsoll = @vkmatsoll - @vkfremdsoll
  set @ekmatist  = @ekmatist  - @ekfremdist
  set @vkmatist  = @vkmatist  - @vkfremdist

  INSERT INTO dbo.#Tmp( MandId       ,Projekt       ,BelNr          ,BelArt, 
                    Ek_Mat_SOLL  ,Ek_Lohn_SOLL  ,Ek_Fremd_SOLL  ,Ek_Zeit_SOLL, 
	            Vk_Mat_SOLL  ,Vk_Lohn_SOLL  ,Vk_Fremd_SOLL  ,Vk_Zeit_SOLL,
		    Ek_Mat_IST   ,Ek_Lohn_IST   ,Ek_Fremd_IST   ,Ek_Zeit_IST,
	            Vk_Mat_IST   ,Vk_Lohn_IST   ,Vk_Fremd_IST   ,Vk_Zeit_IST
                   )
         VALUES( 1           ,@projekt     ,@BelegNr      ,@BelegArt,  
                 @ekmatsoll  ,@eklohnsoll  ,@ekfremdsoll  ,@ekzeitsoll, 
                 @vkmatsoll  ,@vklohnsoll  ,@vkfremdsoll  ,@vkzeitsoll, 
                 @ekmatist   ,@eklohnist   ,@ekfremdist   ,@ekzeitist,
                 @vkmatist   ,@vklohnist   ,@vkfremdist   ,@vkzeitist
               )
SELECT 	
	 ROUND(ISNULL( T.Ek_Mat_SOLL   ,0), 2)                                  AS Ek_Mat_SOLL
        ,ROUND(ISNULL( T.Ek_Lohn_SOLL  ,0), 2)                                  AS Ek_Lohn_SOLL
        ,ROUND(ISNULL( T.Ek_Fremd_SOLL ,0), 2)                                  AS Ek_Fremd_SOLL
        ,ROUND(ISNULL( T.Ek_Zeit_SOLL  ,0), 2)                                  AS Ek_Zeit_SOLL
        ,ROUND(ISNULL( T.Ek_Mat_SOLL + T.Ek_Lohn_SOLL + T.Ek_Fremd_SOLL, 0), 2) AS Ek_SOLL_Summe
        ,ROUND(ISNULL( T.Vk_Mat_SOLL   ,0), 2)                                  AS Vk_Mat_SOLL
        ,ROUND(ISNULL( T.Vk_Lohn_SOLL  ,0), 2)                                  AS Vk_Lohn_SOLL
        ,ROUND(ISNULL( T.Vk_Fremd_SOLL ,0), 2)                                  AS Vk_Fremd_SOLL 
        ,ROUND(ISNULL( T.Vk_Zeit_SOLL  ,0), 2)                                  AS Vk_Zeit_SOLL
        ,ROUND(ISNULL( T.Vk_Mat_SOLL + T.Vk_Lohn_SOLL + T.Vk_Fremd_SOLL, 0), 2) AS Vk_SOLL_Summe
	,ROUND(ISNULL( T.Ek_Mat_IST    ,0), 2)                                  AS Ek_Mat_IST
	,ROUND(ISNULL( T.Ek_Lohn_IST   ,0), 2)                                  AS Ek_Lohn_IST  
        ,ROUND(ISNULL( T.Ek_Fremd_IST  ,0), 2)                                  AS Ek_Fremd_IST
        ,ROUND(ISNULL( T.Ek_Zeit_IST   ,0), 2)                                  AS Ek_Zeit_IST
        ,ROUND(ISNULL( T.Ek_Mat_IST + T.Ek_Lohn_IST + T.Ek_Fremd_IST , 0), 2)   AS Ek_IST_Summe
	,ROUND(ISNULL( T.Vk_Mat_IST    ,0), 2)                                  AS Vk_Mat_IST  
        ,ROUND(ISNULL( T.Vk_Lohn_IST   ,0), 2)                                  AS Vk_Lohn_IST  
        ,ROUND(ISNULL( T.Vk_Fremd_IST  ,0), 2)                                  AS Vk_Fremd_IST  
        ,ROUND(ISNULL( T.Vk_Zeit_IST   ,0), 2)                                  AS Vk_Zeit_IST  
        ,ROUND(ISNULL( T.Vk_Mat_IST + T.Vk_Lohn_IST + T.Vk_Fremd_IST , 0), 2)                                 AS Vk_IST_Summe
	,ROUND(ISNULL( 100 * T.Vk_Mat_IST / ISNULL(NULLIF(T.Ek_Mat_IST,0),1) - 100, 0), 2)                    AS Durch_EkMat
	,ROUND(ISNULL( 100 * T.Vk_Lohn_IST / ISNULL(NULLIF(T.Ek_Lohn_IST,0),1) - 100, 0), 2)                  AS Durch_EkLohn
	,ROUND(ISNULL( NULLIF( 100 * T.Vk_Fremd_IST / ISNULL(NULLIF(T.Ek_Fremd_IST,0),1) - 100, -100),0), 2)  AS Durch_EkFremd
	,ROUND(ISNULL( 100 * (T.Vk_Mat_IST + T.Vk_Lohn_IST + T.Vk_Fremd_IST) / 
		 ISNULL(NULLIF(T.Ek_Mat_IST+T.Ek_Lohn_IST+T.Ek_Fremd_IST,0),1) - 100, 0), 2)                  AS Durch_EkSumme
        ,B.PRJNR, B.KDNR, B.NAME1, B.VORHABEN, B.BETREFF1
	,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.#Tmp T
INNER JOIN dbo.Belege B
        ON T.BelNr  = B.BELEGNR AND T.BelArt = B.BELEGART  
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'




GO
CREATE VIEW dbo.crv_BelegListe
AS
SELECT
         B.RecId ,1 AS MandId ,B.PRJNR ,B.BELEGART ,B.BELEGNR ,B.VERSION
        ,B.LFDNR ,B.NRKREIS ,B.BELARTTEXT ,B.ANZNACHTR ,B.ANZRAEUME 
        ,B.ANZBAUPHA ,B.BAUPHAAKT ,B.KDNR ,B.BLNR ,B.TAG ,B.TAGDUMMY 
        ,B.MONAT ,B.MONDUMMY ,B.JAHR ,B.SUBMISSION ,B.BAUBEGINN 
        ,B.BAUFERTIG ,B.FERTIGJN ,B.EIGANSCH ,B.PHASE ,B.SKONTOTAGE
        ,B.SKONTOSATZ ,B.DRKFFTITEL ,B.DRKINCMWST ,B.DRKAUFMASS ,B.DRKLM
        ,B.DRKZUBEHOE ,B.DRKDMRAB ,B.DRKRABATT ,B.DRKNULLWER ,B.AUFLOHN
        ,B.AUFMAT ,B.AUFFREMD ,B.MITTLOHN ,B.EXACT ,B.KGRPS ,B.KZZUBEHOER
        ,B.LOHNANTEIL ,B.KZVK2 ,B.TITEL ,B.MANUELL ,B.OZMASKE ,B.ANREDE
        ,B.VORNAME ,B.NAME1 ,B.NAME2 ,B.ZUHAENDEN ,B.STRASSE ,B.LAND
        ,B.PLZ ,B.ORT ,B.BRANREDE ,B.VORHABEN ,B.BETREFF1 ,B.BETREFF2
        ,B.BETREFF3 ,B.EDMENGEN ,B.EDARTNR ,B.EDLPOSNR ,B.DRKNETTO
        ,B.UEBERTRAG ,B.TITELZUS ,B.DRKTEXT ,B.DRKUPOS ,B.DRKUPOSPR 
        ,B.EDFAKTOR ,B.FAELLTAGE ,B.DRKARTNR ,B.DRKBRANRE ,B.UNSERZEI
        ,B.SICHBEHALT ,B.DRKVORTEXT ,B.DRKNACTEXT ,B.DRKTITSUMM ,B.JOURNAL
        ,B.ZUVERBUCH ,B.VERBUCHT ,B.NETTO ,B.MWST ,B.MWSTSATZ ,B.BRUTTO
        ,B.VERBUCHTDM ,B.SUMMEMAT ,B.SUMMELOHN ,B.SUMMEFREMD ,B.SUMMEZEIT
        ,B.SUMMEEKFR ,B.SUMMEEKM ,B.GEMEINK ,B.GEMEINKVH ,B.MAHNENJN ,B.MAHNSTUFE
        ,B.BEZAHLT ,B.ZULETZTBEZ ,B.ZAHLDATUM ,B.WAEHRUNG ,B.KURSZUEURO
        ,B.DRKBANK ,B.EGRUMAT ,B.EGRULOHN ,B.EGRUFREMD ,B.EGRUGEMEIN ,B.KST
        ,B.KTR ,B.DEBKONTO ,B.NERECHNEN ,B.NEAUSCHLAG ,B.NETRENNEN ,B.FREMDPROJ
        ,B.GAEBJN ,B.GAEBNAME ,B.GAEBPHASE ,B.NETTO1 ,B.NETTO2 ,B.UST1 ,B.UST2
        ,B.USTVH2 ,B.RES4NG2 ,B.NUMRECS
        ,P.Nummer
	,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.Projektueberwachung P
INNER JOIN dbo.Belege B
        ON B.PRJNR = P.Projekt And B.BELEGART = P.Belart And B.BELEGNR = P.Belnr
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'





GO
CREATE PROCEDURE dbo.crp_ZArbeitnehmer
                                   @VonNum     varchar(4)
                                  ,@BisNum     varchar(4)
                                  ,@VonDatum   varchar(10)
                                  ,@BisDatum   varchar(10)
AS
DECLARE @m_VonNum int
DECLARE @m_BisNum int
DECLARE @m_VonDatum varchar(10)
DECLARE @m_BisDatum varchar(10)
SET @m_VonNum   = 0
SET @m_BisNum   = 0
SET @m_VonDatum = '' 
SET @m_BisDatum = ''

IF @VonNum IS NOT NULL AND @VonNum <> '' AND @VonNum <> 'NULL'
    SET @m_VonNum = 1 
IF @BisNum IS NOT NULL AND @BisNum <> '' AND @BisNum <> 'NULL'
    SET @m_BisNum = 1 

IF @VonDatum IS NOT NULL AND LEN(@VonDatum) = 10
    SET @m_VonDatum = SUBSTRING(@VonDatum,7,4)+'.'+SUBSTRING(@VonDatum,4,2)+'.'+SUBSTRING(@VonDatum,1,2) 
ELSE
    SET @m_VonDatum = SUBSTRING(CONVERT(varchar(10),GETDATE(),104),7,4) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),4,2) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),1,2) 

IF @BisDatum IS NOT NULL AND LEN(@BisDatum) = 10
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4)+'.'+SUBSTRING(@BisDatum,4,2)+'.'+SUBSTRING(@BisDatum,1,2) 
ELSE
    SET @m_BisDatum = SUBSTRING(CONVERT(varchar(10),GETDATE(),104),7,4) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),4,2) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),1,2) 

SELECT
   Z.RecId ,1 AS MandId ,Z.arbtnr ,Z.art ,Z.belegnr ,Z.posnr
  ,Z.lohnart ,Z.tag ,Z.monat ,Z.jahr ,Z.kstnr ,Z.stunden
  ,Z.betrag ,Z.bsgru ,Z.prsatz ,Z.satzstrzei ,Z.wkz
  ,Z.eurokurs ,Z.eklohn ,Z.bruttolohn ,Z.bemerkung
  ,Z.ExpFibu

  ,Z.eklohn     / ISNULL(NULLIF(Z.eurokurs,0),1) AS eklohn_eur
  ,Z.bruttolohn / ISNULL(NULLIF(Z.eurokurs,0),1) AS bruttolohn_eur
  ,Z.betrag     / ISNULL(NULLIF(Z.eurokurs,0),1) AS betrag_eur

  ,ISNULL(L.bsoll      ,0 ) AS bsoll 
  ,ISNULL(L.lohnarttxt ,'') AS LohnArtText
  ,ISNULL(L.ekfaktor   ,1 ) AS Ekfaktor

  ,ISNULL( CASE WHEN Z.stunden = 0 OR Z.stunden IS NULL
                THEN ( Z.betrag / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)
                ELSE Z.stunden * ( Z.eklohn / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)  
           END
         ,0) AS EkLohn_Betrag
  ,ISNULL( CASE WHEN Z.stunden = 0 OR Z.stunden IS NULL
                THEN ( Z.betrag / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)
                ELSE Z.stunden * ( Z.bruttolohn / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)  
           END
         ,0) AS BruttoLohn_Betrag

  ,ISNULL((SELECT TOP 1 A.VorName FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Vorname 
  ,ISNULL((SELECT TOP 1 A.Name1   FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Name1
  ,ISNULL((SELECT TOP 1 A.Name2   FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Name2   
  ,ISNULL((SELECT TOP 1 A.Strasse FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Strasse
  ,ISNULL((SELECT TOP 1 A.Ort     FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Ort     
  ,ISNULL((SELECT TOP 1 A.Plz     FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Plz

  ,ISNULL((SELECT TOP 1 P.VORHABEN FROM dbo.Belege B 
           INNER JOIN dbo.Belege P ON P.PRJNR = B.PRJNR AND P.BELEGART = ' 0'
           WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr), '') AS PRJ_VORHABEN    
  ,ISNULL((SELECT TOP 1 P.BETREFF1 FROM dbo.Belege B 
           INNER JOIN dbo.Belege P ON P.PRJNR = B.PRJNR AND P.BELEGART = ' 0'
           WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr), '') AS PRJ_BETREFF1    
  ,ISNULL((SELECT TOP 1 P.BETREFF2 FROM dbo.Belege B 
           INNER JOIN dbo.Belege P ON P.PRJNR = B.PRJNR AND P.BELEGART = ' 0'
           WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr), '') AS PRJ_BETREFF2    

  ,ISNULL((SELECT TOP 1 B.PRJNR    FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS PrjNr    
  ,ISNULL((SELECT TOP 1 B.VORHABEN FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS VORHABEN    
  ,ISNULL((SELECT TOP 1 B.BETREFF1 FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BETREFF1    
  ,ISNULL((SELECT TOP 1 B.BETREFF2 FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BETREFF2    
  ,ISNULL((SELECT TOP 1 B.BELEGART FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BELEGART    
  ,ISNULL((SELECT TOP 1 B.BELEGNR  FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BELEGNR_B    

  ,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.Zeiterfassung Z
LEFT  JOIN dbo.Lohnarten L
        ON Z.lohnart = L.lohnart 
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( @m_VonNum   = 0  OR  Z.arbtnr >= @VonNum ) AND
      ( @m_BisNum   = 0  OR  Z.arbtnr <= @BisNum ) AND
      ( Z.jahr +'.'+ Z.monat +'.'+ Z.tag >= @m_VonDatum ) AND
      ( Z.jahr +'.'+ Z.monat +'.'+ Z.tag <= @m_BisDatum )





GO
CREATE PROCEDURE dbo.crp_ZBaustellen
                                   @FltKey     varchar(18)
                                  ,@VonDatum   varchar(10)
                                  ,@BisDatum   varchar(10)
AS
DECLARE @m_Projekt  varchar(8)
DECLARE @m_Art      varchar(2)
DECLARE @m_Beleg    varchar(8)
DECLARE @m_VonDatum varchar(10)
DECLARE @m_BisDatum varchar(10)
SET @m_Projekt  = '00000000'
SET @m_Art      = '00'
SET @m_Beleg    = '00000000'
SET @m_VonDatum = '' 
SET @m_BisDatum = ''

IF @FltKey IS NOT NULL AND @FltKey <> ''
BEGIN
    SET @m_Projekt = SUBSTRING(@FltKey, 1,  8) 
    SET @m_Art     = SUBSTRING(@FltKey, 9,  2) 
    SET @m_Beleg   = SUBSTRING(@FltKey, 11, 8) 
END

IF @VonDatum IS NOT NULL AND LEN(@VonDatum) = 10
    SET @m_VonDatum = SUBSTRING(@VonDatum,7,4)+'.'+SUBSTRING(@VonDatum,4,2)+'.'+SUBSTRING(@VonDatum,1,2) 
ELSE
    SET @m_VonDatum = SUBSTRING(CONVERT(varchar(10),GETDATE(),104),7,4) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),4,2) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),1,2) 

IF @BisDatum IS NOT NULL AND LEN(@BisDatum) = 10
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4)+'.'+SUBSTRING(@BisDatum,4,2)+'.'+SUBSTRING(@BisDatum,1,2) 
ELSE
    SET @m_BisDatum = SUBSTRING(CONVERT(varchar(10),GETDATE(),104),7,4) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),4,2) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),1,2) 

DECLARE @Prj_Vorhaben varchar(10)
DECLARE @Prj_Betreff1 varchar(42)
DECLARE @Prj_Betreff2 varchar(42)

SET @Prj_Vorhaben = ISNULL((SELECT TOP 1 P.VORHABEN FROM dbo.Belege P 
                            WHERE P.PRJNR = @m_Projekt AND P.BELEGART = ' 0'),'')
SET @Prj_Betreff1 = ISNULL((SELECT TOP 1 P.BETREFF1 FROM dbo.Belege P 
                            WHERE P.PRJNR = @m_Projekt AND P.BELEGART = ' 0'),'')
SET @Prj_Betreff2 = ISNULL((SELECT TOP 1 P.BETREFF2 FROM dbo.Belege P 
                            WHERE P.PRJNR = @m_Projekt AND P.BELEGART = ' 0'),'')
    
DECLARE @Bel_Vorhaben varchar(10)
DECLARE @Bel_Betreff1 varchar(42)
DECLARE @Bel_Betreff2 varchar(42)

SET @Bel_Vorhaben = ISNULL((SELECT TOP 1 B.VORHABEN FROM dbo.Belege B 
                            WHERE B.PRJNR = @m_Projekt AND B.BELEGART = @m_Art AND B.BELEGNR = @m_Beleg),'')    
SET @Bel_Betreff1 = ISNULL((SELECT TOP 1 B.BETREFF1 FROM dbo.Belege B 
                            WHERE B.PRJNR = @m_Projekt AND B.BELEGART = @m_Art AND B.BELEGNR = @m_Beleg),'')    
SET @Bel_Betreff2 = ISNULL((SELECT TOP 1 B.BETREFF2 FROM dbo.Belege B 
                            WHERE B.PRJNR = @m_Projekt AND B.BELEGART = @m_Art AND B.BELEGNR = @m_Beleg),'')    

SELECT
   Z.RecId ,1 AS MandId ,Z.arbtnr ,Z.art ,Z.belegnr ,Z.posnr
  ,Z.lohnart ,Z.tag ,Z.monat ,Z.jahr ,Z.kstnr ,Z.stunden
  ,Z.betrag ,Z.bsgru ,Z.prsatz ,Z.satzstrzei ,Z.wkz
  ,Z.eurokurs ,Z.eklohn ,Z.bruttolohn ,Z.bemerkung
  ,Z.ExpFibu

  ,Z.eklohn     / ISNULL(NULLIF(Z.eurokurs,0),1) AS eklohn_eur
  ,Z.bruttolohn / ISNULL(NULLIF(Z.eurokurs,0),1) AS bruttolohn_eur
  ,Z.betrag     / ISNULL(NULLIF(Z.eurokurs,0),1) AS betrag_eur

  ,ISNULL(L.bsoll      ,0 ) AS bsoll 
  ,ISNULL(L.lohnarttxt ,'') AS LohnArtText
  ,ISNULL(L.ekfaktor   ,1 ) AS Ekfaktor

  ,ISNULL( CASE WHEN Z.stunden = 0 OR Z.stunden IS NULL
                THEN ( Z.betrag / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)
                ELSE Z.stunden * ( Z.eklohn / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)  
           END
         ,0) AS EkLohn_Betrag
  ,ISNULL( CASE WHEN Z.stunden = 0 OR Z.stunden IS NULL
                THEN ( Z.betrag / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)
                ELSE Z.stunden * ( Z.bruttolohn / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)  
           END
         ,0) AS BruttoLohn_Betrag

  ,ISNULL((SELECT TOP 1 A.VorName FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Vorname 
  ,ISNULL((SELECT TOP 1 A.Name1   FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Name1
  ,ISNULL((SELECT TOP 1 A.Name2   FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Name2   
  ,ISNULL((SELECT TOP 1 A.Strasse FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Strasse
  ,ISNULL((SELECT TOP 1 A.Ort     FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Ort     
  ,ISNULL((SELECT TOP 1 A.Plz     FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Plz

  ,@Prj_Vorhaben AS PRJ_VORHABEN    
  ,@Prj_Betreff1 AS PRJ_BETREFF1    
  ,@Prj_Betreff2 AS PRJ_BETREFF2    

  ,@m_Projekt    AS PrjNr    
  ,@Bel_Vorhaben AS VORHABEN    
  ,@Bel_Betreff1 AS BETREFF1    
  ,@Bel_Betreff2 AS BETREFF2    
  ,@m_Art        AS BELEGART    
  ,@m_Beleg      AS BELEGNR_B    

  ,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.Zeiterfassung Z
LEFT  JOIN dbo.Lohnarten L
        ON Z.lohnart = L.lohnart 
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( Z.art = @m_Art AND Z.belegnr = @m_Beleg ) AND
      ( Z.jahr +'.'+ Z.monat +'.'+ Z.tag >= @m_VonDatum ) AND
      ( Z.jahr +'.'+ Z.monat +'.'+ Z.tag <= @m_BisDatum )
GO



CREATE PROCEDURE dbo.crp_ZProjekte
                                   @VonDatum   varchar(10)
                                  ,@BisDatum   varchar(10)
AS
DECLARE @m_VonDatum varchar(10)
DECLARE @m_BisDatum varchar(10)
SET @m_VonDatum = '' 
SET @m_BisDatum = ''

IF @VonDatum IS NOT NULL AND LEN(@VonDatum) = 10
    SET @m_VonDatum = SUBSTRING(@VonDatum,7,4)+'.'+SUBSTRING(@VonDatum,4,2)+'.'+SUBSTRING(@VonDatum,1,2) 
ELSE
    SET @m_VonDatum = SUBSTRING(CONVERT(varchar(10),GETDATE(),104),7,4) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),4,2) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),1,2) 

IF @BisDatum IS NOT NULL AND LEN(@BisDatum) = 10
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4)+'.'+SUBSTRING(@BisDatum,4,2)+'.'+SUBSTRING(@BisDatum,1,2) 
ELSE
    SET @m_BisDatum = SUBSTRING(CONVERT(varchar(10),GETDATE(),104),7,4) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),4,2) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),1,2) 

SELECT
   Z.art ,Z.belegnr 
  ,ISNULL( SUM(Z.stunden)   , 0 ) AS stunden
  ,ISNULL( SUM(Z.betrag)    , 0 ) AS betrag 
  ,ISNULL( SUM(Z.eklohn)    , 0 ) AS eklohn 
  ,ISNULL( SUM(Z.bruttolohn), 0 ) AS bruttolohn

  ,ISNULL( SUM(Z.eklohn     / ISNULL(NULLIF(Z.eurokurs,0),1)), 0) AS eklohn_eur
  ,ISNULL( SUM(Z.bruttolohn / ISNULL(NULLIF(Z.eurokurs,0),1)), 0) AS bruttolohn_eur
  ,ISNULL( SUM(Z.betrag     / ISNULL(NULLIF(Z.eurokurs,0),1)), 0) AS betrag_eur

  ,ISNULL( SUM(CASE WHEN Z.stunden = 0 OR Z.stunden IS NULL
                    THEN ( Z.betrag / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)
                    ELSE Z.stunden * ( Z.eklohn / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)  
               END)
         ,0) AS EkLohn_Betrag
  ,ISNULL( SUM(CASE WHEN Z.stunden = 0 OR Z.stunden IS NULL
                    THEN ( Z.betrag / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)
                    ELSE Z.stunden * ( Z.bruttolohn / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)  
               END)
         ,0) AS BruttoLohn_Betrag

  ,ISNULL((SELECT TOP 1 P.VORHABEN FROM dbo.Belege B 
           INNER JOIN dbo.Belege P ON P.PRJNR = B.PRJNR AND P.BELEGART = ' 0'
           WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr), '') AS PRJ_VORHABEN    
  ,ISNULL((SELECT TOP 1 P.BETREFF1 FROM dbo.Belege B 
           INNER JOIN dbo.Belege P ON P.PRJNR = B.PRJNR AND P.BELEGART = ' 0'
           WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr), '') AS PRJ_BETREFF1    
  ,ISNULL((SELECT TOP 1 P.BETREFF2 FROM dbo.Belege B 
           INNER JOIN dbo.Belege P ON P.PRJNR = B.PRJNR AND P.BELEGART = ' 0'
           WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr), '') AS PRJ_BETREFF2    

  ,ISNULL((SELECT TOP 1 B.PRJNR    FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS PrjNr    
  ,ISNULL((SELECT TOP 1 B.VORHABEN FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS VORHABEN    
  ,ISNULL((SELECT TOP 1 B.BETREFF1 FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BETREFF1    
  ,ISNULL((SELECT TOP 1 B.BETREFF2 FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BETREFF2    
  ,ISNULL((SELECT TOP 1 B.BELEGART FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BELEGART    
  ,ISNULL((SELECT TOP 1 B.BELEGNR  FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BELEGNR_B    

  ,ISNULL((SELECT AN.Name1       FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Name1       
  ,ISNULL((SELECT AN.Name2       FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Name2      
  ,ISNULL((SELECT AN.Name3       FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Name3
  ,ISNULL((SELECT AN.Strasse     FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Strasse     
  ,ISNULL((SELECT AN.PlzOrt      FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_PlzOrt     
  ,ISNULL((SELECT AN.Telefon     FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Telefon
  ,ISNULL((SELECT AN.FaxNr       FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Fax         
  ,ISNULL((SELECT AN.Bank1_Name  FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Bank1_Name 
  ,ISNULL((SELECT AN.Bank1_BLZ   FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Bank1_Blz
  ,ISNULL((SELECT AN.Bank1_Konto FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Bank1_Konto 
  ,ISNULL((SELECT AN.Bank2_Name  FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Bank2_Name 
  ,ISNULL((SELECT AN.Bank2_BLZ   FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Bank2_Blz
  ,ISNULL((SELECT AN.Bank2_Konto FROM dbo.Anschriften AN WHERE AN.Nr = '01'),'') AS F_Bank2_Konto

FROM dbo.Zeiterfassung Z
LEFT  JOIN dbo.Lohnarten L
        ON Z.lohnart = L.lohnart 
WHERE ( Z.jahr +'.'+ Z.monat +'.'+ Z.tag >= @m_VonDatum ) AND
      ( Z.jahr +'.'+ Z.monat +'.'+ Z.tag <= @m_BisDatum )
GROUP BY Z.art, Z.belegnr
GO




CREATE PROCEDURE dbo.crp_ZDatum
                               @VonDatum   varchar(10)
                              ,@BisDatum   varchar(10)
AS
DECLARE @m_VonDatum varchar(10)
DECLARE @m_BisDatum varchar(10)
SET @m_VonDatum = '' 
SET @m_BisDatum = ''

IF @VonDatum IS NOT NULL AND LEN(@VonDatum) = 10
    SET @m_VonDatum = SUBSTRING(@VonDatum,7,4)+'.'+SUBSTRING(@VonDatum,4,2)+'.'+SUBSTRING(@VonDatum,1,2) 
ELSE
    SET @m_VonDatum = SUBSTRING(CONVERT(varchar(10),GETDATE(),104),7,4) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),4,2) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),1,2) 

IF @BisDatum IS NOT NULL AND LEN(@BisDatum) = 10
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4)+'.'+SUBSTRING(@BisDatum,4,2)+'.'+SUBSTRING(@BisDatum,1,2) 
ELSE
    SET @m_BisDatum = SUBSTRING(CONVERT(varchar(10),GETDATE(),104),7,4) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),4,2) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),1,2) 

SELECT
   Z.RecId ,1 AS MandId ,Z.arbtnr ,Z.art ,Z.belegnr ,Z.posnr
  ,Z.lohnart ,Z.tag ,Z.monat ,Z.jahr ,Z.kstnr ,Z.stunden
  ,Z.betrag ,Z.bsgru ,Z.prsatz ,Z.satzstrzei ,Z.wkz
  ,Z.eurokurs ,Z.eklohn ,Z.bruttolohn ,Z.bemerkung

  ,Z.eklohn     / ISNULL(NULLIF(Z.eurokurs,0),1) AS eklohn_eur
  ,Z.bruttolohn / ISNULL(NULLIF(Z.eurokurs,0),1) AS bruttolohn_eur
  ,Z.betrag     / ISNULL(NULLIF(Z.eurokurs,0),1) AS betrag_eur

  ,ISNULL(L.bsoll      ,0 ) AS bsoll 
  ,ISNULL(L.lohnarttxt ,'') AS LohnArtText
  ,ISNULL(L.ekfaktor   ,1 ) AS Ekfaktor

  ,ISNULL( CASE WHEN Z.stunden = 0 OR Z.stunden IS NULL
                THEN ( Z.betrag / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)
                ELSE Z.stunden * ( Z.eklohn / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)  
           END
         ,0) AS EkLohn_Betrag
  ,ISNULL( CASE WHEN Z.stunden = 0 OR Z.stunden IS NULL
                THEN ( Z.betrag / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)
                ELSE Z.stunden * ( Z.bruttolohn / ISNULL(NULLIF(Z.eurokurs,0),1) ) * ISNULL(L.ekfaktor,1)  
           END
         ,0) AS BruttoLohn_Betrag

  ,ISNULL((SELECT TOP 1 A.VorName FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Vorname 
  ,ISNULL((SELECT TOP 1 A.Name1   FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Name1
  ,ISNULL((SELECT TOP 1 A.Name2   FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Name2   
  ,ISNULL((SELECT TOP 1 A.Strasse FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Strasse
  ,ISNULL((SELECT TOP 1 A.Ort     FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Ort     
  ,ISNULL((SELECT TOP 1 A.Plz     FROM dbo.Adressen A WHERE A.Annr = Z.arbtnr And A.DESTTYP = ' 4'), '') AS Plz

  ,ISNULL((SELECT TOP 1 P.VORHABEN FROM dbo.Belege B 
           INNER JOIN dbo.Belege P 
                   ON P.PRJNR = B.PRJNR AND P.BELEGART = ' 0'
           WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr), '') AS PRJ_VORHABEN    
  ,ISNULL((SELECT TOP 1 P.BETREFF1 FROM dbo.Belege B 
           INNER JOIN dbo.Belege P
                   ON P.PRJNR = B.PRJNR AND P.BELEGART = ' 0'
           WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr), '') AS PRJ_BETREFF1    
  ,ISNULL((SELECT TOP 1 P.BETREFF2 FROM dbo.Belege B 
           INNER JOIN dbo.Belege P
                   ON P.PRJNR = B.PRJNR AND P.BELEGART = ' 0'
           WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr), '') AS PRJ_BETREFF2    

  ,ISNULL((SELECT TOP 1 B.PRJNR    FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS PrjNr    
  ,ISNULL((SELECT TOP 1 B.VORHABEN FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS VORHABEN    
  ,ISNULL((SELECT TOP 1 B.BETREFF1 FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BETREFF1    
  ,ISNULL((SELECT TOP 1 B.BETREFF2 FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BETREFF2    
  ,ISNULL((SELECT TOP 1 B.BELEGART FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BELEGART    
  ,ISNULL((SELECT TOP 1 B.BELEGNR  FROM dbo.Belege B WHERE B.BELEGART = Z.art AND B.BELEGNR = Z.belegnr),'') AS BELEGNR_B    

  ,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.Zeiterfassung Z
LEFT  JOIN dbo.Lohnarten L
        ON Z.lohnart = L.lohnart 
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE ( Z.jahr +'.'+ Z.monat +'.'+ Z.tag >= @m_VonDatum ) AND
      ( Z.jahr +'.'+ Z.monat +'.'+ Z.tag <= @m_BisDatum )




GO
CREATE PROCEDURE dbo.crp_SollIstAlleArbeitnehmer
				@VonDatum   varchar(10),
				@BisDatum   varchar(10)
AS
DECLARE @m_VonDatum varchar(10)
DECLARE @m_BisDatum varchar(10)
SET @m_VonDatum = '' 
SET @m_BisDatum = ''

IF @VonDatum IS NOT NULL AND LEN(@VonDatum) = 10
    SET @m_VonDatum = SUBSTRING(@VonDatum,7,4)+'.'+SUBSTRING(@VonDatum,4,2)+'.'+SUBSTRING(@VonDatum,1,2) 
ELSE
    SET @m_VonDatum = SUBSTRING(CONVERT(varchar(10),GETDATE(),104),7,4) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),4,2) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),1,2) 

IF @BisDatum IS NOT NULL AND LEN(@BisDatum) = 10
    SET @m_BisDatum = SUBSTRING(@BisDatum,7,4)+'.'+SUBSTRING(@BisDatum,4,2)+'.'+SUBSTRING(@BisDatum,1,2) 
ELSE
    SET @m_BisDatum = SUBSTRING(CONVERT(varchar(10),GETDATE(),104),7,4) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),4,2) +'.'+
                      SUBSTRING(CONVERT(varchar(10),GETDATE(),104),1,2) 

SELECT 
   ISNULL((SELECT SUM(ISNULL(Z.stunden, 0)) FROM dbo.Zeiterfassung Z
	   INNER JOIN dbo.Lohnarten L
	           ON Z.lohnart = L.lohnart AND L.bsoll = 1
	   LEFT  JOIN dbo.Adressen K
	           ON K.DESTTYP = ' 4' AND K.Annr = Z.arbtnr
	   WHERE A.Annr = Z.arbtnr AND ISDATE(Z.tag +'.'+ Z.monat +'.'+ Z.jahr) = 1 AND
                 Z.jahr+'.'+Z.monat+'.'+Z.tag >= @m_VonDatum AND Z.jahr+'.'+Z.monat+'.'+Z.tag <= @m_BisDatum)
         ,0) AS stunden

  ,ISNULL((SELECT SUM(ISNULL(S.sstunden, 0 )) FROM dbo.Sollstundenschema S
	   LEFT JOIN dbo.Adressen K 
	          ON K.DESTTYP = ' 4' AND K.BaGruppe = S.bsgruppe AND K.DESTDEBNR = A.DESTDEBNR
	   WHERE A.BaGruppe = S.bsgruppe AND ISDATE(S.tag +'.'+ S.monat +'.'+ S.jahr) = 1 AND
                 S.jahr+'.'+S.monat+'.'+S.tag >= @m_VonDatum AND S.jahr+'.'+S.monat+'.'+S.tag <= @m_BisDatum)
         ,0) AS sollstunden

  ,A.Name1
  ,A.Annr

  ,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.Adressen A
LEFT JOIN dbo.Anschriften AN
       ON AN.Nr = '01'
WHERE A.DESTTYP = ' 4'


GO
CREATE PROCEDURE dbo.crp_SollIstArbeitnehmer
				 @VonNum    char(4)
				,@BisNum    char(4)   
				,@VonDatum  char(10)
				,@BisDatum  char(10)
AS

DECLARE @tag      int
DECLARE @vontag   int
DECLARE @bistag   int
DECLARE @monat    int
DECLARE @vonmonat int
DECLARE @bismonat int
DECLARE @jahr     int
DECLARE @vonjahr  int
DECLARE @bisjahr  int

DECLARE @arbnr     char(4)
DECLARE @gruppe    char(2)

DECLARE @m_VonNum int
DECLARE @m_BisNum int
SET @m_VonNum   = 0
SET @m_BisNum   = 0
IF @VonNum IS NOT NULL AND @VonNum <> '' AND @VonNum <> 'NULL'
    SET @m_VonNum = 1 
IF @BisNum IS NOT NULL AND @BisNum <> '' AND @BisNum <> 'NULL'
    SET @m_BisNum = 1 

SET @vontag   = DAY  ( GETDATE() )
SET @vonmonat = MONTH( GETDATE() )
SET @vonjahr  = YEAR ( GETDATE() )
SET @bistag   = DAY  ( GETDATE() )
SET @bismonat = MONTH( GETDATE() )
SET @bisjahr  = YEAR ( GETDATE() )

IF (ISDATE(@VonDatum) = 1)
BEGIN
  SET @vontag   = DAY(   @VonDatum )
  SET @vonmonat = MONTH( @VonDatum )
  SET @vonjahr  = YEAR(  @VonDatum )
END
IF (ISDATE(@BisDatum) = 1)
BEGIN
  SET @bistag   = DAY(   @BisDatum )
  SET @bismonat = MONTH( @BisDatum )
  SET @bisjahr  = YEAR(  @BisDatum )
END

IF (@vonjahr > @bisjahr)
RETURN
IF (@vonjahr = @bisjahr) AND (@vonmonat > @bismonat)
RETURN
IF (@vonjahr = @bisjahr) AND (@vonmonat = @bismonat) AND (@vontag > @bistag)
RETURN

CREATE TABLE dbo.#crv_Temp(MandId int, annr char(4), gruppe char(2), tag char(2), monat char(2), jahr char(4), 
                           soll float, ist float, diff float)

IF SUBSTRING(@@VERSION,22,1) = '2' OR SUBSTRING(@@VERSION,23,1) = '2'
BEGIN
  execute('
            ALTER TABLE dbo.#crv_Temp ALTER COLUMN annr    char(4) COLLATE database_default
            ALTER TABLE dbo.#crv_Temp ALTER COLUMN gruppe  char(2) COLLATE database_default
            ALTER TABLE dbo.#crv_Temp ALTER COLUMN tag     char(2) COLLATE database_default 
            ALTER TABLE dbo.#crv_Temp ALTER COLUMN monat   char(2) COLLATE database_default
            ALTER TABLE dbo.#crv_Temp ALTER COLUMN jahr    char(4) COLLATE database_default 
          ')
END         
                         
DECLARE @count int 
DECLARE @soll  float
DECLARE @ist   float
DECLARE @diff  float
SET @count = 0

DECLARE adressen_cursor CURSOR FOR 
SELECT A.Annr,A.BaGruppe FROM dbo.Adressen A
WHERE  A.DESTTYP = ' 4' AND (@m_VonNum = 0 OR A.Annr >= @VonNum) AND (@m_BisNum = 0 OR A.Annr <= @BisNum)
OPEN adressen_cursor

FETCH NEXT FROM adressen_cursor
INTO @arbnr,@gruppe

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @soll  = 0
    SET @ist   = 0
    SET @diff  = 0
    SET @tag   = @vontag
    SET @monat = @vonmonat
    SET @jahr  = @vonjahr    
    SET @count = 0

    WHILE (@count <= 365)
    BEGIN
      SET @count = @count + 1
      SET @soll = ISNULL( (SELECT TOP 1 sstunden FROM  dbo.Sollstundenschema S
                           WHERE S.bsgruppe = @gruppe AND 
                                 RIGHT('00'+LTRIM(STR(@tag)),2)   = S.tag   AND
                                 RIGHT('00'+LTRIM(STR(@monat)),2) = S.monat AND
	                                    LTRIM(STR(@jahr))     = S.jahr)
                        , 0 )
      SET @ist  = ISNULL( (SELECT SUM(ISNULL(Z.stunden, 0)) FROM dbo.Zeiterfassung Z
	                   INNER JOIN dbo.Lohnarten L
	                           ON Z.lohnart = L.lohnart AND L.bsoll = 1                  
	                   WHERE Z.arbtnr  = @arbnr AND
	                         RIGHT('00'+LTRIM(STR(@tag)),2)   = Z.tag   AND
                                 RIGHT('00'+LTRIM(STR(@monat)),2) = Z.monat AND
	                                    LTRIM(STR(@jahr))     = Z.jahr)
                        , 0 ) 

      INSERT INTO dbo.#crv_Temp(MandId, annr, gruppe, tag, monat, jahr, soll, ist, diff)
      VALUES( 1, @arbnr, @gruppe, 
              RIGHT('00'+LTRIM(STR(@tag)),2), RIGHT('00'+LTRIM(STR(@monat)),2), LTRIM(STR(@jahr)), 
              ISNULL(@soll,0), ISNULL(@ist,0), ISNULL(@ist,0) - ISNULL(@soll,0) )	
	    
      IF ((@monat = @bismonat) AND (@tag = @bistag) AND (@jahr = @bisjahr))  
      BREAK
      IF ((@tag=28 AND  @monat=2 AND @jahr<>2004 AND @jahr<>2008 AND @jahr<>2012 AND @jahr<>2016 AND @jahr<>2020) OR
	  (@tag=29 AND  @monat=2 AND (@jahr=2004 OR @jahr=2008 OR @jahr=2012 OR @jahr=2016 OR @jahr=2020)) OR
	  (@tag=30 AND (@monat=4 OR @monat=6 OR @monat=9 OR @monat=11)) OR
	  (@tag=31 AND (@monat=1 OR @monat=3 OR @monat=5 OR @monat=7 OR @monat=8 OR @monat=10 OR @monat=12)))
      BEGIN
	 SET @tag   = 1
	 SET @monat = @monat + 1
      END
      ELSE
      BEGIN 
	 SET @tag = @tag + 1
      END

      IF(@monat = 13)
      BEGIN
	 SET @jahr  = @jahr + 1
	 SET @monat = 1
      END
    END

    FETCH NEXT FROM adressen_cursor
    INTO @arbnr,@gruppe
END

CLOSE adressen_cursor
DEALLOCATE adressen_cursor

SELECT 
   T.MandId ,T.annr ,T.gruppe ,T.tag  ,T.monat, T.jahr
  ,T.soll AS sollstunden ,T.ist  AS stunden ,T.diff AS differenz

  ,A.Name1   AS Name1   ,A.Name2 AS Name2 ,A.VorName AS VorName
  ,A.Strasse AS Strasse ,A.Plz   AS Plz   ,A.Ort     AS Ort

  ,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.#crv_Temp T
INNER JOIN dbo.Adressen A
        ON T.annr = A.Annr AND A.DESTTYP = ' 4'
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'






GO
CREATE VIEW dbo.crv_Zahlungen
AS
SELECT 
         Z.RecId ,Z.MandId ,Z.BelArt ,Z.BelNr ,Z.BuchNr ,Z.Datum ,Z.Betrag
        ,Z.Waehrung ,Z.Kurs ,Z.Ausgl ,Z.BuchText ,Z.FibuKtoVon ,Z.FibuKtoAuf
        ,Z.BankKtoVon ,Z.BankKtoAuf ,Z.BLZVon ,Z.BLZAuf	
        ,Z.Waehrung                                                    AS Firmenwaehrung
	,ISNULL(Z.Betrag / ISNULL(NULLIF(Z.Kurs,0),1), 0)              AS EuroBetrag

	,(SELECT TOP 1 B.PRJNR FROM dbo.Belege B 
          WHERE Z.BelArt = B.BELEGART AND Z.BelNr = B.BELEGNR)         AS PRJNR
	,(SELECT TOP 1 B.BETREFF1 +' '+ B.BETREFF2 +' '+ B.BETREFF3 FROM dbo.Belege B 
          WHERE Z.BelArt = B.BELEGART AND Z.BelNr = B.BELEGNR)         AS PRJTEXT
	,(SELECT TOP 1 B.BELARTTEXT FROM dbo.Belege B   
          WHERE Z.BelArt = B.BELEGART AND Z.BelNr = B.BELEGNR)         AS BELARTTEXT
	,(SELECT TOP 1 B.VORHABEN FROM dbo.Belege B
          WHERE Z.BelArt = B.BELEGART AND Z.BelNr = B.BELEGNR)         AS VORHABEN
	,(SELECT TOP 1 B.BETREFF1 FROM dbo.Belege B
          WHERE Z.BelArt = B.BELEGART AND Z.BelNr = B.BELEGNR)         AS BETREFF1
	,(SELECT TOP 1 B.BETREFF2 FROM dbo.Belege B
          WHERE Z.BelArt = B.BELEGART AND Z.BelNr = B.BELEGNR)         AS BETREFF2
	,(SELECT TOP 1 B.BETREFF3 FROM dbo.Belege B
          WHERE Z.BelArt = B.BELEGART AND Z.BelNr = B.BELEGNR)         AS BETREFF3

	,ISNULL(A.DESTDEBNR ,'')                                       AS DESTDEBNR
	,ISNULL(A.DESTDEBNR ,'')                                       AS Annr
	,ISNULL(A.VorName   ,'')                                       AS VorName
	,ISNULL(A.Name1     ,'')                                       AS Name1
	,ISNULL(A.Name2     ,'')                                       AS Name2
	,ISNULL(A.Strasse   ,'')                                       AS Strasse
	,ISNULL(A.Ort       ,'')                                       AS Ort
	,ISNULL(A.Plz       ,'')                                       AS Plz
	,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.ZahlOp Z
LEFT  JOIN dbo.Adressen A
        ON A.DESTDEBNR = (SELECT TOP 1 BEL.KDNR FROM dbo.Belege BEL 
                          WHERE BEL.BELEGART = Z.BelArt AND BEL.BELEGNR = Z.BelNr)
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'



GO
CREATE VIEW dbo.crv_OPListe
AS
SELECT
         Z.RecId ,1 AS 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(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.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 Z.Status = ' ' 
GO



CREATE VIEW dbo.crv_OPKreditoren
AS
SELECT
         BZ.RecId ,1 AS MandId ,BZ.Typ ,BZ.Konto ,BZ.Jahr ,BZ.Monat ,BZ.Tag
        ,BZ.TransNo ,BZ.Soll ,BZ.Haben ,BZ.SaldoAlt ,BZ.SaldoNeu ,BZ.BtrOrg
        ,BZ.BtrWae ,BZ.Saldiert ,BZ.Abgeholt ,BZ.[Text] ,BZ.SaldoUms
        ,BZ.Kst ,BZ.Ktr ,BZ.PrjNr ,BZ.BelNr ,BZ.FJahr ,BZ.FMonat ,BZ.FTag
        ,BZ.AusglTRN ,BZ.AusglBtr ,BZ.SkoFrist ,BZ.SkoSatz ,BZ.Status1 ,BZ.Status2
        ,BZ.BelDatum ,BZ.BelNrExt
	,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.Buchungszeilen BZ
INNER JOIN dbo.Kontenplan K
        ON K.Typ = BZ.Typ AND K.Konto = BZ.Konto AND K.KzKasse = 'L'
LEFT  JOIN dbo.Adressen A 
        ON A.FibuKonto = BZ.Konto
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE BZ.AusglTRN = '' AND BZ.Typ = 'P' AND BZ.Tag <> '00' AND BZ.Tag <> '32' AND ( BZ.Soll <> 0 OR BZ.Haben <> 0 )
GO



CREATE PROCEDURE dbo.crp_BelegStatistik
                                 @BelArt   varchar(2)
				,@VonPrj   varchar(8)
				,@BisPrj   varchar(8)   
				,@VonBel   varchar(8)
				,@BisBel   varchar(8)   
                                ,@VonKnd   varchar(15)
                                ,@BisKnd   varchar(15)
                                ,@VonBlt   varchar(15)
                                ,@BisBlt   varchar(15)
				,@VonDatum varchar(10)
				,@BisDatum varchar(10)
AS
DECLARE @p_VonDatum varchar(10) 
DECLARE @p_BisDatum varchar(10) 
SET @p_VonDatum = 'NULL'
SET @p_BisDatum = 'NULL'

IF ISDATE( @VonDatum ) = 1
    SET @p_VonDatum = RIGHT(@VonDatum, 4) + SUBSTRING(@VonDatum,4,2) + LEFT(@VonDatum,2)

IF ISDATE( @BisDatum ) = 1 
    SET @p_BisDatum = RIGHT(@BisDatum, 4) + SUBSTRING(@BisDatum,4,2) + LEFT(@BisDatum,2)

SELECT
    B.PRJNR, B.BELEGART, B.BELEGNR, B.KDNR
   ,B.BLNR,  B.TAG, B.MONAT, B.JAHR
   ,B.LOHNANTEIL * B.SUMMEZEIT   	       AS Zeit
   ,B.MITTLOHN		                       AS Mittellohn 
   ,B.GEMEINK		                       AS Gemeink
   ,B.SUMMEEKFR		                       AS EkFremd
   ,B.LOHNANTEIL * B.SUMMEZEIT * B.MITTLOHN    AS EkLohn
   ,B.SUMMEEKM - B.SUMMEEKFR                   AS EkMat
   ,B.SUMMEFREMD		               AS VkFremd
   ,B.LOHNANTEIL * B.SUMMELOHN                 AS VkLohn
   ,B.SUMMEMAT - B.SUMMEFREMD                  AS VkMat
   ,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.Belege B 
LEFT  JOIN dbo.Anschriften AN
        ON AN.Nr = '01'
WHERE (B.BELEGART = ' 1' OR B.BELEGART = ' 3' OR B.BELEGART = ' 4')    AND
      ((@BelArt = '00')   OR (@BelArt = '') OR (B.BELEGART = @BelArt)) AND 
      ((@VonPrj = 'NULL') OR (@VonPrj = '') OR (B.PRJNR   >= @VonPrj)) AND
      ((@BisPrj = 'NULL') OR (@BisPrj = '') OR (B.PRJNR   <= @BisPrj)) AND
      ((@VonBel = 'NULL') OR (@VonBel = '') OR (B.BELEGNR >= @VonBel)) AND
      ((@BisBel = 'NULL') OR (@BisBel = '') OR (B.BELEGNR <= @BisBel)) AND
      ((@VonKnd = 'NULL') OR (@VonKnd = '') OR (B.KDNR    >= @VonKnd)) AND
      ((@BisKnd = 'NULL') OR (@BisKnd = '') OR (B.KDNR    <= @BisKnd)) AND
      ((@VonBlt = 'NULL') OR (@VonBlt = '') OR (B.BLNR    >= @VonBlt)) AND
      ((@BisBlt = 'NULL') OR (@BisBlt = '') OR (B.BLNR    <= @BisBlt)) AND
      ((@p_VonDatum = 'NULL') OR (B.JAHR + B.MONAT + B.TAG >= @p_VonDatum)) AND
      ((@p_BisDatum = 'NULL') OR (B.JAHR + B.MONAT + B.TAG <= @p_BisDatum)) 
GO

