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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_ProjektAuftraege')
  DROP PROCEDURE [dbo].[up_Sel_ProjektAuftraege]
GO
CREATE PROCEDURE [dbo].[up_Sel_ProjektAuftraege]
  @RI integer
AS
  DECLARE @mPrjNr   char(8)
  SET     @mPrjNr = (SELECT PrjNr FROM ut_Statusfelder WHERE RecId = @RI)

  select
      @mPrjNr             as PrjNr
     ,ISNULL(SUM(Bel.BRUTTO),0.00)     as Brutto
     ,ISNULL(SUM(Bel.NETTO),0.00)      as Netto
     ,ISNULL(SUM(Bel.SUMMELOHN),0.00)  as VKLohn
     ,ISNULL(SUM(Bel.SUMMEMAT),0.00)   as VKMat
     ,ISNULL(SUM(Bel.SUMMEFREMD),0.00) as VKFremd
     ,ISNULL(SUM(Bel.SUMMEZEIT / 60.00),0.00)        as Stunden
     ,ISNULL(SUM(Bel.MITTLOHN * Bel.SUMMEZEIT),0.00) as EkLohn
     ,ISNULL(SUM(Bel.SUMMEEKM),0.00)   as EKMat
     ,ISNULL(SUM(Bel.SUMMEEKFR),0.00)  as EKFremd
     ,ISNULL(SUM(Bel.GEMEINK),0.00)    as EKGemein
  from  Belege Bel
  Where Bel.PRJNR = @mPrjNr AND BELEGART = ' 7' 
GO

/*------------------------------------------------------------*/
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Del_ProjektAuftraege')
  DROP PROCEDURE [dbo].[up_Del_ProjektAuftraege]
GO
CREATE PROCEDURE [dbo].[up_Del_ProjektAuftraege]
  @RI integer
AS
  DELETE FROM [dbo].[ut_Statusfelder] WHERE RecId = @RI
GO

/*------------------------------------------------------------*/
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_ProjektAuftraege_KEY00')
  DROP PROCEDURE [dbo].[up_First_ProjektAuftraege_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_ProjektAuftraege_KEY00')
  DROP PROCEDURE [dbo].[up_Last_ProjektAuftraege_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_ProjektAuftraege_KEY00')
  DROP PROCEDURE [dbo].[up_Prior_ProjektAuftraege_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_ProjektAuftraege_KEY00')
  DROP PROCEDURE [dbo].[up_Next_ProjektAuftraege_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_ProjektAuftraege_KEY00')
  DROP PROCEDURE [dbo].[up_Prior50_ProjektAuftraege_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_ProjektAuftraege_KEY00')
  DROP PROCEDURE [dbo].[up_Next50_ProjektAuftraege_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_ProjektAuftraege_KEY00')
  DROP PROCEDURE [dbo].[up_FindL_ProjektAuftraege_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_ProjektAuftraege_KEY00')
  DROP PROCEDURE [dbo].[up_FindF_ProjektAuftraege_KEY00]
GO

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

/*------------------------------------------------------------*/
/* letzten Datensatz aus ProjektAuftraege in Folge KEY00 ermittln */
CREATE PROCEDURE [dbo].[up_Last_ProjektAuftraege_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_ProjektAuftraege_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_ProjektAuftraege_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_ProjektAuftraege_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_ProjektAuftraege_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

