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

----------------------------------------------------------------------------------------------
-- DROP INDEX [Ik_KEY0] ON [dbo].[ut_Belege] 
IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_Belege' 
   and so.[id] = si.[id] and si.[name] = 'Ik_KEY0')
CREATE INDEX [Ik_KEY0] ON [dbo].[ut_Belege] 
(
   [PRJNR],[BELEGART],[BELEGNR]
)
GO

----------------------------------------------------------------------------------------------
-- DROP INDEX [Ik_KEY2] ON [dbo].[ut_Belege] 
IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_Belege' 
   and so.[id] = si.[id] and si.[name] = 'Ik_KEY2')
CREATE INDEX [Ik_KEY2] ON [dbo].[ut_Belege] 
(
   [BELEGART],[BELEGNR]
)
GO

----------------------------------------------------------------------------------------------
--
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'itcview_BelegeAuswahl')
  DROP VIEW [dbo].[itcview_BelegeAuswahl]
GO
CREATE VIEW [dbo].[itcview_BelegeAuswahl] AS
  SELECT b.RecId, b.PRJNR, b.BELEGART, b.BELEGNR, b.BELARTTEXT, b.BETREFF1, b.KDNR, b.NAME1, b.STRASSE, b.ORT, 
         b.NETTO, b.BRUTTO, b.JAHR + '-' + b.MONAT + '-' + b.TAG as BelDatum,
		 s.StWertI as showWeb
  FROM ut_Belege b
	-- LEFT JOIN ut_Adressen a ON a.DESTDEBNR = b.KRNR
  LEFT JOIN ut_Statusfelder s ON s.PrjNr = b.PRJNR AND s.BelArt = b.BELEGART AND s.BelNr = b.BELEGNR AND s.StatId = 'SHOW-WEB'
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'itcview_BelegeAuswahl_Web')
  DROP VIEW [dbo].[itcview_BelegeAuswahl_Web]
GO
CREATE VIEW [dbo].[itcview_BelegeAuswahl_Web] AS
  SELECT * FROM [dbo].[itcview_BelegeAuswahl] WHERE showWeb = 1
GO

--------------------------------------------------------------------------------------------------------
-- BelegeAuswahl abrufen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_BelegeAuswahl_get')
  DROP PROCEDURE [dbo].[itcproc_BelegeAuswahl_get]
GO
CREATE PROCEDURE [dbo].[itcproc_BelegeAuswahl_get]
  @Parm_PrjNr   varchar(8) = ''
 ,@Parm_BelArt  varchar(2) = ''
 ,@Parm_KdNr    varchar(15) = ''
 ,@Parm_showWeb varchar(10) = ''
AS
  DECLARE @m_showWeb integer = dbo.itcfunc_integer(@Parm_showWeb)

  ------------------------------------------------------------------- alle Belege
  IF @m_showWeb = 0 BEGIN
	IF @Parm_PrjNr <> '' AND @Parm_KdNr <> ''
	  SELECT * FROM [dbo].[itcview_BelegeAuswahl] WHERE PRJNR = @Parm_PrjNr AND KDNR = @Parm_KdNr AND (BELEGART = @Parm_BelArt OR @Parm_BelArt = '') ORDER BY BelDatum DESC
	ELSE IF @Parm_PrjNr <> ''
	  SELECT * FROM [dbo].[itcview_BelegeAuswahl] WHERE PRJNR = @Parm_PrjNr AND (BELEGART = @Parm_BelArt OR @Parm_BelArt = '') ORDER BY BelDatum DESC
	ELSE IF @Parm_KdNr <> ''
	  SELECT * FROM [dbo].[itcview_BelegeAuswahl] WHERE KDNR = @Parm_KdNr AND (BELEGART = @Parm_BelArt OR @Parm_BelArt = '') ORDER BY BelDatum DESC
	ELSE
	  SELECT * FROM [dbo].[itcview_BelegeAuswahl] WHERE (BELEGART = @Parm_BelArt OR @Parm_BelArt = '') ORDER BY BelDatum DESC
	RETURN
  END

  ------------------------------------------------------------------- nur public Belege
  IF @Parm_PrjNr <> '' AND @Parm_KdNr <> ''
    SELECT * FROM [dbo].[itcview_BelegeAuswahl_Web] WHERE PRJNR = @Parm_PrjNr AND KDNR = @Parm_KdNr AND (BELEGART = @Parm_BelArt OR @Parm_BelArt = '') ORDER BY BelDatum DESC
  ELSE IF @Parm_PrjNr <> ''
    SELECT * FROM [dbo].[itcview_BelegeAuswahl_Web] WHERE PRJNR = @Parm_PrjNr AND (BELEGART = @Parm_BelArt OR @Parm_BelArt = '') ORDER BY BelDatum DESC
  ELSE IF @Parm_KdNr <> ''
    SELECT * FROM [dbo].[itcview_BelegeAuswahl_Web] WHERE KDNR = @Parm_KdNr AND (BELEGART = @Parm_BelArt OR @Parm_BelArt = '') ORDER BY BelDatum DESC
  ELSE
    SELECT * FROM [dbo].[itcview_BelegeAuswahl_Web] WHERE (BELEGART = @Parm_BelArt OR @Parm_BelArt = '') ORDER BY BelDatum DESC
GO

--EXECUTE [dbo].[itcproc_BelegeAuswahl_get] '','','','1'
