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

----------------------------------------------------------------------------------------------------------
-- Saldo am Ende eines Tages
IF OBJECT_ID (N'itcfunc_SaldoAm', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_SaldoAm
GO
CREATE FUNCTION dbo.itcfunc_SaldoAm(@Parm_Typ varchar(1), @Parm_Konto varchar(8), @Parm_Jahr varchar(4), @Parm_Monat varchar(2), @Parm_Tag varchar(2)) RETURNS decimal(14,2)
AS
BEGIN
  DECLARE @m_Saldo decimal(14,2)
  select TOP 1 @m_Saldo = SaldoNeu from dbo.ut_Buchungszeilen 
    where Typ = @Parm_Typ AND Konto = @Parm_Konto AND
      (    (Jahr = @Parm_Jahr AND Monat = @Parm_Monat AND Tag <= @Parm_Tag)
	    OR (Jahr = @Parm_Jahr AND Monat < @Parm_Monat)
	    OR (Jahr < @Parm_Jahr)
      )
	order by Jahr desc, Monat desc, Tag desc, TransNo desc, RecId desc

  RETURN ISNULL(@m_Saldo, 0)
END
GO

----------------------------------------------------------------------------------------------------------
-- Saldo vor einem Tag
IF OBJECT_ID (N'itcfunc_SaldoVor', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_SaldoVor
GO
CREATE FUNCTION dbo.itcfunc_SaldoVor(@Parm_Typ varchar(1), @Parm_Konto varchar(8), @Parm_Jahr varchar(4), @Parm_Monat varchar(2), @Parm_Tag varchar(2)) RETURNS decimal(14,2)
AS
BEGIN
  DECLARE @m_Saldo decimal(14,2)
  select TOP 1 @m_Saldo = SaldoNeu from dbo.ut_Buchungszeilen 
    where Typ = @Parm_Typ AND Konto = @Parm_Konto AND
      (    (Jahr = @Parm_Jahr AND Monat = @Parm_Monat AND Tag < @Parm_Tag)
	    OR (Jahr = @Parm_Jahr AND Monat < @Parm_Monat)
	    OR (Jahr < @Parm_Jahr)
      )
	order by Jahr desc, Monat desc, Tag desc, TransNo desc, RecId desc

  RETURN ISNULL(@m_Saldo, 0)
END
GO

----------------------------------------------------------------------------------------------------------
-- Umsatz am Ende eines Tages
IF OBJECT_ID (N'itcfunc_UmsatzAm', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_UmsatzAm
GO
CREATE FUNCTION dbo.itcfunc_UmsatzAm(@Parm_Typ varchar(1), @Parm_Konto varchar(8), @Parm_Jahr varchar(4), @Parm_Monat varchar(2), @Parm_Tag varchar(2)) RETURNS decimal(14,2)
AS
BEGIN
  DECLARE @m_Umsatz decimal(14,2)
  select TOP 1 @m_Umsatz = SaldoUms from dbo.ut_Buchungszeilen 
    where Typ = @Parm_Typ AND Konto = @Parm_Konto AND
      (    (Jahr = @Parm_Jahr AND Monat = @Parm_Monat AND Tag <= @Parm_Tag)
	    OR (Jahr = @Parm_Jahr AND Monat < @Parm_Monat)
	    OR (Jahr < @Parm_Jahr)
      )
	order by Jahr desc, Monat desc, Tag desc, TransNo desc, RecId desc

  RETURN ISNULL(@m_Umsatz, 0)
END
GO

----------------------------------------------------------------------------------------------------------
-- Umsatz vor einem Tag
IF OBJECT_ID (N'itcfunc_UmsatzVor', N'FN') IS NOT NULL
    DROP FUNCTION dbo.itcfunc_UmsatzVor
GO
CREATE FUNCTION dbo.itcfunc_UmsatzVor(@Parm_Typ varchar(1), @Parm_Konto varchar(8), @Parm_Jahr varchar(4), @Parm_Monat varchar(2), @Parm_Tag varchar(2)) RETURNS decimal(14,2)
AS
BEGIN
  DECLARE @m_Umsatz decimal(14,2)
  select TOP 1 @m_Umsatz = SaldoUms from dbo.ut_Buchungszeilen 
    where Typ = @Parm_Typ AND Konto = @Parm_Konto AND
      (    (Jahr = @Parm_Jahr AND Monat = @Parm_Monat AND Tag < @Parm_Tag)
	    OR (Jahr = @Parm_Jahr AND Monat < @Parm_Monat)
	    OR (Jahr < @Parm_Jahr)
      )
	order by Jahr desc, Monat desc, Tag desc, TransNo desc, RecId desc

  RETURN ISNULL(@m_Umsatz, 0)
END
GO

----------------------------------------------------------------------------------------------------------
-- Saldenabfrage
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_SaldenAm')
  DROP PROCEDURE [dbo].[itcproc_SaldenAm]
GO
CREATE PROCEDURE [dbo].[itcproc_SaldenAm] 
  @Parm_Typ varchar(1), 
  @Parm_Konto varchar(8), 
  @Parm_Jahr varchar(4), 
  @Parm_Monat varchar(2), 
  @Parm_Tag varchar(2)
AS
  IF @Parm_Typ <> '' AND @Parm_Konto <> ''
    SELECT Typ, Konto, @Parm_Jahr AS Jahr, @Parm_Monat AS Monat, @Parm_Tag AS Tag
		,dbo.itcfunc_SaldoAm(Typ, Konto, @Parm_Jahr, @Parm_Monat, @Parm_Tag) AS Saldo
		,dbo.itcfunc_UmsatzAm(Typ, Konto, @Parm_Jahr, @Parm_Monat, @Parm_Tag) AS Umsatz
    FROM dbo.ut_Kontenplan WHERE Typ = @Parm_Typ AND Konto = @Parm_Konto

  ELSE IF @Parm_Typ <> ''  
    SELECT Typ, Konto, @Parm_Jahr AS Jahr, @Parm_Monat AS Monat, @Parm_Tag AS Tag
		,dbo.itcfunc_SaldoAm(Typ, Konto, @Parm_Jahr, @Parm_Monat, @Parm_Tag) AS Saldo
		,dbo.itcfunc_UmsatzAm(Typ, Konto, @Parm_Jahr, @Parm_Monat, @Parm_Tag) AS Umsatz
    FROM dbo.ut_Kontenplan WHERE Typ = @Parm_Typ ORDER BY Konto

  ELSE
    SELECT Typ, Konto, @Parm_Jahr AS Jahr, @Parm_Monat AS Monat, @Parm_Tag AS Tag
		,dbo.itcfunc_SaldoAm(Typ, Konto, @Parm_Jahr, @Parm_Monat, @Parm_Tag) AS Saldo
		,dbo.itcfunc_UmsatzAm(Typ, Konto, @Parm_Jahr, @Parm_Monat, @Parm_Tag) AS Umsatz
    FROM dbo.ut_Kontenplan WHERE Typ = 'S' OR Typ = 'P' ORDER BY Typ, Konto
GO
-- EXECUTE [dbo].[itcproc_SaldenAm] 'S','','','',''

----------------------------------------------------------------------------------------------------------
-- Konten mit Anfangs und Endesaldo
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V'   and [name] = 'hwv_PeriodenSaldi')
  DROP VIEW [dbo].[hwv_PeriodenSaldi]
GO
CREATE VIEW [dbo].[hwv_PeriodenSaldi]
AS
  SELECT
     K.[RecId]
    ,K.[Typ]
    ,K.[Konto]
    ,K.[Passiv]
    ,K.[Name]
    ,K.[UstSeite]
    ,K.[UstSpalte]
    ,K.[BilZeile]
    ,SUBSTRING(O1.OptDataC,1,8) AS [Anfang]
	,dbo.itcfunc_SaldoVor(Typ, Konto, SUBSTRING(O1.OptDataC,1,4), SUBSTRING(O1.OptDataC,5,2), SUBSTRING(O1.OptDataC,7,2)) AS SaldoAlt
    ,SUBSTRING(O2.OptDataC,1,8) AS [Ende]
	,dbo.itcfunc_SaldoVor(Typ, Konto, SUBSTRING(O2.OptDataC,1,4), SUBSTRING(O2.OptDataC,5,2), SUBSTRING(O2.OptDataC,7,2)) AS SaldoNeu
  FROM [dbo].[ut_Kontenplan] K
  LEFT JOIN Optionen O1 ON O1.OptId = ' 6220'
  LEFT JOIN Optionen O2 ON O2.OptId = ' 6221'
GO

----------------------------------------------------------------------------------------------------
-- Prozeduren fuer den klassischen Klienten (nur lesende)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_PeriodenSaldi')
  DROP PROCEDURE [dbo].[up_Sel_PeriodenSaldi]
GO
CREATE PROCEDURE [dbo].[up_Sel_PeriodenSaldi]
  @RI integer
AS
SELECT
     [Typ]
    ,[Konto]
    ,[Passiv]
    ,[Name]
    ,[UstSeite]
    ,[UstSpalte]
    ,[BilZeile]
    ,[Anfang]
    ,[SaldoAlt]
    ,[Ende]
    ,[SaldoNeu]
  FROM [dbo].[hwv_PeriodenSaldi]
  WHERE RecId = @RI
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_PeriodenSaldi_Key00')
  DROP PROCEDURE [dbo].[up_First_PeriodenSaldi_Key00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_PeriodenSaldi_Key00')
  DROP PROCEDURE [dbo].[up_Last_PeriodenSaldi_Key00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_PeriodenSaldi_Key00')
  DROP PROCEDURE [dbo].[up_Prior_PeriodenSaldi_Key00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_PeriodenSaldi_Key00')
  DROP PROCEDURE [dbo].[up_Next_PeriodenSaldi_Key00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_PeriodenSaldi_Key00')
  DROP PROCEDURE [dbo].[up_Prior50_PeriodenSaldi_Key00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_PeriodenSaldi_Key00')
  DROP PROCEDURE [dbo].[up_Next50_PeriodenSaldi_Key00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_PeriodenSaldi_Key00')
  DROP PROCEDURE [dbo].[up_FindL_PeriodenSaldi_Key00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_PeriodenSaldi_Key00')
  DROP PROCEDURE [dbo].[up_FindF_PeriodenSaldi_Key00]
GO

CREATE PROCEDURE [dbo].[up_First_PeriodenSaldi_Key00]
AS
  EXECUTE [dbo].[up_First_Kontenplan_Key00]
GO

CREATE PROCEDURE [dbo].[up_Last_PeriodenSaldi_Key00]
AS
  EXECUTE [dbo].[up_Last_Kontenplan_Key00]
GO

CREATE PROCEDURE [dbo].[up_Next_PeriodenSaldi_Key00] @mRecNo integer
AS
  EXECUTE [dbo].[up_Next_Kontenplan_Key00] @mRecno
GO

CREATE PROCEDURE [dbo].[up_Prior_PeriodenSaldi_Key00] @mRecNo integer
AS
  EXECUTE [dbo].[up_Prior_Kontenplan_Key00] @mRecno
GO

CREATE PROCEDURE [dbo].[up_FindF_PeriodenSaldi_Key00] @xKey char(128),@xLen integer
AS
  EXECUTE [dbo].[up_FindF_Kontenplan_Key00] @xKey, @xLen
GO

CREATE PROCEDURE [dbo].[up_FindL_PeriodenSaldi_Key00] @xKey char(128),@xLen integer
AS
  EXECUTE [dbo].[up_FindL_Kontenplan_Key00] @xKey, @xLen
GO
