-- Sichten und Prozeduren zur Fibu

-------------------------------------------------------------------------------------------------
-- Anzeige aller bebuchten Sachkonten mit ihrer Bilanzzuordnung

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


CREATE PROCEDURE [dbo].[hwp_BilanzDebug01] 
AS
 select  
   K.Typ
  ,K.Konto
  ,K.BilZeile
  ,K.[Name]
  ,ISNULL(B1.PosNr,'')   AS PosNr1
  ,ISNULL(B1.[Text],'')  AS Text1
  ,ISNULL(B2.PosNr,'')   AS PosNr2
  ,ISNULL(B2.[Text],'')  AS Text2
  ,ISNULL(B3.PosNr,'')   AS PosNr3
  ,ISNULL(B3.[Text],'')  AS Text3
  ,ISNULL(B1.LfdNr,0)   AS LfdNr1
  ,ISNULL(B2.LfdNr,0)   AS LfdNr2
  ,ISNULL(B3.LfdNr,0)   AS LfdNr3
  ,ISNULL(B4.PosNr,'')   AS PosNr4
  ,ISNULL(B4.[Text],'')  AS Text4
  ,ISNULL(B4.LfdNr,0)   AS LfdNr4
   from Kontenplan K
    left join Bilanzzeilen B1
    on  B1.LstNr = '0001' and B1.PosTyp = 2 and B1.KtoTyp = K.Typ 
    and B1.KtoVon <= K.Konto and B1.KtoBis >= K.Konto
    left join Bilanzzeilen B2
    on  B2.LstNr = '0002' and B2.PosTyp = 2 and B2.KtoTyp = K.Typ 
    and B2.KtoVon <= K.Konto and B2.KtoBis >= K.Konto
    left join Bilanzzeilen B3
    on  B3.LstNr = '0003' and B3.PosTyp = 2 and B3.KtoTyp = K.Typ 
    and B3.KtoVon <= K.Konto and B3.KtoBis >= K.Konto
    left join Bilanzzeilen B4
    on  B4.LstNr = '0003' and B4.PosTyp = 2 and B4.KtoTyp = K.Typ 
    and B4.KtoVon <= K.Konto and B4.KtoBis >= K.Konto
 where K.Typ = 'S' AND 0 < (select count(*) from Buchungszeilen Z where Z.Typ = K.Typ AND Z.Konto = K.Konto)
 order by K.Konto
GO

------------------------------------------------------------------------------------------------------
-- Anzeige aller gebuchten Zeilen für die kein Konto existiert

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


CREATE PROCEDURE [dbo].[hwp_BuchzeilenDebug01] 
AS
 select * from (select distinct Typ,Konto from Buchungszeilen) Z
  where 0 = (select count(*) from Kontenplan K where K.Typ = Z.Typ and K.Konto = Z.Konto)
  order by Typ,Konto
GO

-------------------------------------------------------------------------------------------------
-- Semaphore - Handling

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

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

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

CREATE PROCEDURE [dbo].[hwp_FibuLock] 
AS
  DECLARE @mUseSmp integer
  DECLARE @mSemaphoreName varchar(128)
  DECLARE @mRetCode integer

  SET @mUseSmp  = (SELECT OptDataI FROM Optionen WHERE OptId = ' 8011')
  SET @mRetCode = 0

  IF NOT (@mUseSmp IS NULL)
  BEGIN
    IF @mUseSmp = 1
    BEGIN
      SET @mSemaphoreName = RTRIM(DB_NAME())+'_FibuTRN'
      SET @mRetCode = 1
      WHILE @mRetCode <> 0
      BEGIN
        EXECUTE @mRetCode = master.dbo.xp_BshSmp 0,60,@mSemaphoreName
      END
    END
  END

  RETURN  @mRetCode
GO


CREATE PROCEDURE [dbo].[hwp_FibuUnlock] 
AS
  DECLARE @mUseSmp integer
  DECLARE @mSemaphoreName varchar(128)
  DECLARE @mRetCode       integer

  SET @mUseSmp  = (SELECT OptDataI FROM Optionen WHERE OptId = ' 8011')

  IF NOT (@mUseSmp IS NULL)
  BEGIN
    IF @mUseSmp = 1
    BEGIN
      SET     @mSemaphoreName = RTRIM(DB_NAME())+'_FibuTRN'
      -- EXECUTE @mRetCode = master.dbo.xp_BshSmp 2,0,@mSemaphoreName
    END
  END
GO


CREATE PROCEDURE [dbo].[hwp_FibuContinue] 
AS
  DECLARE @mUseSmp integer
  DECLARE @mSemaphoreName varchar(128)
  DECLARE @mRetCode       integer

  SET @mUseSmp  = (SELECT OptDataI FROM Optionen WHERE OptId = ' 8011')

  IF NOT (@mUseSmp IS NULL)
  BEGIN
    IF @mUseSmp = 1
    BEGIN
      SET     @mSemaphoreName = RTRIM(DB_NAME())+'_FibuTRN'
      -- EXECUTE @mRetCode = master.dbo.xp_BshSmp 1,0,@mSemaphoreName
    END
  END
GO

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

IF 0 < (SELECT COUNT(*) FROM sysobjects WHERE [xtype] = 'V' and [name] = 'hwv_ProjektFibuBuckets')
	DROP VIEW [dbo].[hwv_ProjektFibuBuckets]
GO

CREATE VIEW [dbo].[hwv_ProjektFibuBuckets]
AS
  SELECT Buckets.*
        ,(SELECT SUM(Round(Soll,2)) FROM ut_Buchungszeilen Z1 
           WHERE Z1.PrjNr = Buckets.PrjNr AND Z1.Typ = Buckets.Typ AND Z1.Konto = Buckets.Konto
             AND Z1.Jahr  = Buckets.Jahr AND Z1.Monat = Buckets.Monat
         ) AS Soll
        ,(SELECT SUM(Round(Haben,2)) FROM ut_Buchungszeilen Z2 
           WHERE Z2.PrjNr = Buckets.PrjNr AND Z2.Typ = Buckets.Typ AND Z2.Konto = Buckets.Konto
             AND Z2.Jahr  = Buckets.Jahr AND Z2.Monat = Buckets.Monat
         ) AS Haben
        ,(SELECT SUM(Round(Soll,2)) FROM ut_Buchungszeilen Z1 
           WHERE Z1.PrjNr = Buckets.PrjNr AND Z1.Typ = Buckets.Typ AND Z1.Konto = Buckets.Konto
         ) AS GesSoll
        ,(SELECT SUM(Round(Haben,2)) FROM ut_Buchungszeilen Z2 
           WHERE Z2.PrjNr = Buckets.PrjNr AND Z2.Typ = Buckets.Typ AND Z2.Konto = Buckets.Konto
         ) AS GesHaben
    FROM (SELECT DISTINCT PrjNr, Typ, Konto, Jahr, Monat FROM ut_Buchungszeilen WHERE (Soll <> 0 OR Haben <> 0)) AS Buckets
GO


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

CREATE PROCEDURE [dbo].[hwp_ProjektFibuBucket]   @mPrjNr char(8)
AS
  SELECT B.*,K.[Name],K.KzPrj,K.UstSpalte,
    (SELECT count(*) FROM [dbo].[hwv_ProjektFibuBuckets] B2 WHERE B2.PrjNr = B.PrjNr AND B2.Typ = B.Typ AND B2.Konto = B.Konto) AS Anzahl 
    FROM [dbo].[hwv_ProjektFibuBuckets] B
    LEFT Join [dbo].[ut_Kontenplan] K ON K.Typ = B.Typ AND K.Konto = B.Konto
   WHERE B.PrjNr = @mPrjNr
   ORDER BY B.Typ,B.Konto,B.Jahr,B.Monat
GO

-- EXECUTE dbo.hwp_ProjektFibuBucket '00000002'
-- select * from CodeUnits
-- update CodeUnits set Version = '03.03N/05', LastEdit = convert(datetime,'2005-04-30',120)

