-- 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.
--

-- Diesen Code nicht automatisch generieren
-- nur select/delete und indexsuche implementiert
-- eigentliche Sicht ist Tabelle Statusfelder      
-- Stand 03.05.2004, 2022/06, 2023/11
-- Autor: Steffen Anders

/* KzPrj - Werte
  Case 1
     mText = "Zeit"
  Case 2
     mText = "EK Lohn"
  Case 3
     mText = "EK Mat."
  Case 4
     mText = "EK Fremd"
  Case 5
     mText = "EK Sonst."
  Case 12
     mText = "VK Lohn"
  Case 13
     mText = "VK Mat."
  Case 15
     mText = "VK Sonst."
  Case 19
     mText = "VK Ust."
*/

--------------------------------------------------------------------
-- Abfragen durch Indexaufbau optimieren
IF 0 = (select count(*)
   from       sysindexes si
   inner join sysobjects so on
   so.[xtype]    = 'U'
   and so.[name] = 'ut_Kontenplan'
   and so.[id]   = si.[id]
   and si.[name] = 'Ik_KzPrj_Typ_Konto')
CREATE INDEX [Ik_KzPrj_Typ_Konto] ON [dbo].[ut_Kontenplan]
(KzPrj,Typ,Konto,RecId)
GO

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_ProjektGebucht')
  DROP PROCEDURE [dbo].[up_Sel_ProjektGebucht]
GO
CREATE PROCEDURE [dbo].[up_Sel_ProjektGebucht]
  @RI integer
AS
  DECLARE @mPrjNr   char(8)
  DECLARE @mDebNr   char(8)
  SET     @mPrjNr = (SELECT PrjNr FROM ut_Statusfelder WHERE RecId = @RI)
  SET     @mDebNr = (SELECT TOP 1 SUBSTRING(DEBKONTO,1,8) FROM ut_Belege WHERE PRJNR = @mPrjNr AND BELEGART = ' 7')

  select
     @mPrjNr                  as PrjNr
    ,ISNULL((select SUM(Soll - Haben) from Buchungszeilen BZ inner join Kontenplan KP
          on BZ.PrjNr = @mPrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto 
         AND KP.KzPrj =  1),0.00)   as Stunden
    ,ISNULL((select SUM(Soll - Haben) from Buchungszeilen BZ inner join Kontenplan KP
          on BZ.PrjNr = @mPrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto 
         AND KP.KzPrj =  2),0.00)   as EkLohn
    ,ISNULL((select SUM(Soll - Haben) from Buchungszeilen BZ inner join Kontenplan KP
          on BZ.PrjNr = @mPrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto 
         AND KP.KzPrj =  3),0.00)   as EkMat
    ,ISNULL((select SUM(Soll - Haben) from Buchungszeilen BZ inner join Kontenplan KP
          on BZ.PrjNr = @mPrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto 
         AND KP.KzPrj =  4),0.00)   as EkFremd
    ,ISNULL((select SUM(Soll - Haben) from Buchungszeilen BZ inner join Kontenplan KP
          on BZ.PrjNr = @mPrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto 
         AND KP.KzPrj =  5),0.00)   as EkSonst
    ,ISNULL((select SUM(Haben - Soll) from Buchungszeilen BZ inner join Kontenplan KP
          on BZ.PrjNr = @mPrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto 
         AND KP.KzPrj = 12),0.00)   as VkLohn
    ,ISNULL((select SUM(Haben - Soll) from Buchungszeilen BZ inner join Kontenplan KP
          on BZ.PrjNr = @mPrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto 
         AND KP.KzPrj = 13),0.00)   as VkMat
    ,ISNULL((select SUM(Haben - Soll) from Buchungszeilen BZ inner join Kontenplan KP
          on BZ.PrjNr = @mPrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto 
         AND KP.KzPrj = 15),0.00)   as VkSonst
    ,ISNULL((select SUM(Haben - Soll) from Buchungszeilen BZ inner join Kontenplan KP
          on BZ.PrjNr = @mPrjNr AND BZ.Typ = KP.Typ AND BZ.Konto = KP.Konto 
         AND KP.KzPrj = 19),0.00)   as VkUst
    ,ISNULL((select SUM(Haben) from Buchungszeilen BZ left join ut_BelTransaction BT 
		  on BZ.TransNo = BT.TransNo
       WHERE BZ.PrjNr = @mPrjNr AND BZ.Typ = 'P' AND BZ.Konto = @mDebNr AND ISNULL(BT.[Status],1) = 1),0.00)
                                    as Bezahlt
GO

------------------------------------------------------------
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_ProjektGebucht_KEY00')
  DROP PROCEDURE [dbo].[up_First_ProjektGebucht_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_ProjektGebucht_KEY00')
  DROP PROCEDURE [dbo].[up_Last_ProjektGebucht_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_ProjektGebucht_KEY00')
  DROP PROCEDURE [dbo].[up_Prior_ProjektGebucht_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_ProjektGebucht_KEY00')
  DROP PROCEDURE [dbo].[up_Next_ProjektGebucht_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_ProjektGebucht_KEY00')
  DROP PROCEDURE [dbo].[up_Prior50_ProjektGebucht_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_ProjektGebucht_KEY00')
  DROP PROCEDURE [dbo].[up_Next50_ProjektGebucht_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_ProjektGebucht_KEY00')
  DROP PROCEDURE [dbo].[up_FindL_ProjektGebucht_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_ProjektGebucht_KEY00')
  DROP PROCEDURE [dbo].[up_FindF_ProjektGebucht_KEY00]
GO

/*------------------------------------------------------------*/
/* ersten Datensatz aus ProjektGebucht in Folge KEY00 ermittln */
CREATE PROCEDURE [dbo].[up_First_ProjektGebucht_KEY00] AS
  SELECT TOP 1 RecId FROM ut_Statusfelder WHERE StatId = 'PRJ STAT B' ORDER BY PrjNr,RecId
GO

/*------------------------------------------------------------*/
/* letzten Datensatz aus ProjektGebucht in Folge KEY00 ermittln */
CREATE PROCEDURE [dbo].[up_Last_ProjektGebucht_KEY00] AS
  SELECT TOP 1 RecId FROM ut_Statusfelder WHERE StatId = 'PRJ STAT B' ORDER BY PrjNr DESC,RecId DESC
GO

/*------------------------------------------------------------*/
/* naechsten Datensatz in Folge ORDER BY PrjNr,RecId ermitteln */
CREATE PROCEDURE [dbo].[up_Next_ProjektGebucht_KEY00] @oldRecId integer AS
  SELECT TOP 1 RecId FROM ut_Statusfelder
     WHERE StatId = 'PRJ STAT B' 
       AND PrjNr  > (SELECT PrjNr FROM  ut_Statusfelder WHERE RecId = @oldRecId)
     ORDER BY StatId,PrjNr,RecId
GO

--------------------------------------------------------------
/* vorherigen Datensatz in Folge ORDER BY PrjNr DESC,RecId DESC ermitteln */
CREATE PROCEDURE [dbo].[up_Prior_ProjektGebucht_KEY00] @oldRecId integer AS
  SELECT TOP 1 RecId FROM ut_Statusfelder
     WHERE StatId = 'PRJ STAT B' 
       AND PrjNr  < (SELECT PrjNr FROM  ut_Statusfelder WHERE RecId = @oldRecId)
     ORDER BY StatId DESC,PrjNr DESC,RecId DESC
GO

/*------------------------------------------------------------*/
/* ersten Datensatz in Folge ORDER BY PrjNr,RecId suchen */
CREATE PROCEDURE [dbo].[up_FindF_ProjektGebucht_KEY00] @xKey char(128),@xLen integer AS

  IF @xLen < 8
  BEGIN
    SELECT TOP 1 RecId FROM ut_Statusfelder
      WHERE StatId = 'PRJ STAT B' AND PrjNr like SUBSTRING(@xKey,1,@xLen) + '%'
      ORDER BY StatId,PrjNr,RecId
    RETURN
  END

  IF @xLen = 8
  BEGIN
    SELECT TOP 1 RecId FROM ut_Statusfelder
      WHERE StatId = 'PRJ STAT B' AND PrjNr = SUBSTRING(@xKey,1,8)
      ORDER BY StatId,PrjNr,RecId
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

/*------------------------------------------------------------*/
/* letzten Datensatz in Folge ORDER BY PrjNr DESC,RecId DESC suchen */
CREATE PROCEDURE [dbo].[up_FindL_ProjektGebucht_KEY00] @xKey char(128),@xLen integer AS

  IF @xLen < 8
  BEGIN
    SELECT TOP 1 RecId FROM ut_Statusfelder
      WHERE StatId = 'PRJ STAT B' AND PrjNr like SUBSTRING(@xKey,1,@xLen) + '%'
      ORDER BY StatId DESC,PrjNr DESC,RecId DESC
    RETURN
  END

  IF @xLen = 8
  BEGIN
    SELECT TOP 1 RecId FROM ut_Statusfelder
      WHERE StatId = 'PRJ STAT B' AND PrjNr = SUBSTRING(@xKey,1,8)
      ORDER BY StatId DESC,PrjNr DESC,RecId DESC
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

