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

----------------------------------------------------------------------------------------------
-- Suchfunktion Table-View
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'hwv_Adressen_Trech')
  DROP VIEW [dbo].[hwv_Adressen_Trech]
GO
CREATE VIEW [dbo].[hwv_Adressen_Trech]
AS
  SELECT   RecId, DESTTYP, DESTDEBNR,
      UPPER(REPLACE([DESTDEBNR] + [SUCHEN] + [Anrede] + [VorName] + [Name1] + [Name2] + [Branche] + [Strasse] + [Ort] + [Land] + [Plz]
        + [Dokument] + [Info1] + [Info2] + [Info3] + [Info4] + [UstId1] + [Telefon1] + [EMail] + [zuHaenden] + [zuHaeAnre]
        + [Tel1Kurz] + [Telefon2] + [Tel2Kurz] + [Telefon3] + [Tel3Kurz] + [FaxNummer] + [FaxKurz]
	    + [Selection1] + [Selection2] + [FibuKonto] + [HomePage] + [LandName] + [PsfPlz] + [Psf] + [PlzGrossEm]
	  ,' ', ''))
    AS [TextData]
  FROM     [dbo].[ut_Adressen]
GO

----------------------------------------------------------------------------------------------
-- Adressuebersicht Table-View
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'itcview_Adressen_list')
  DROP VIEW [dbo].[itcview_Adressen_list]
GO
CREATE VIEW [dbo].[itcview_Adressen_list]
AS
  SELECT RecId,DESTTYP, DESTDEBNR, SpKzJn, SUCHEN,Anrede,VorName,Name1,Name2,Strasse,RTRIM(SUBSTRING(Plz,1,8)) AS Plz,Ort
        ,zuHaenden,BrAnrede,Telefon1,Telefon2,EMail,Selection1,Selection2,Info1,Info2,Info3,Info4,HomePage
  FROM dbo.ut_Adressen
GO

----------------------------------------------------------------------------------------------
-- Suchfunktion
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Adressen_TextSearch')
  DROP PROCEDURE [dbo].[itcproc_Adressen_TextSearch]
GO
CREATE PROCEDURE [dbo].[itcproc_Adressen_TextSearch]
    @Param_QueryId varchar(36)
  , @Param_DestTyp  varchar(2)
  , @Param_ToSearch varchar(128)
AS
	SET NOCOUNT ON

	IF RTRIM(@Param_QueryId) = '' OR RTRIM(@Param_ToSearch) = ''
	  RETURN

	DECLARE @mToSearch varchar(128) = dbo.itcfunc_SearchTokenString(@Param_ToSearch)
	DECLARE @mQueryTime datetime = CURRENT_TIMESTAMP

	-- einzelnes Token
	DECLARE @mDelimiterPos integer = CHARINDEX(' ', @mToSearch)
	IF @mDelimiterPos = 0 BEGIN
	  INSERT dbo.hwt_SearchQueryResult (QueryId, QueryTime, DataRecId)
	    SELECT @Param_QueryId, @mQueryTime, RecId FROM hwv_Adressen_Trech 
	    WHERE [TextData] like '%' + @mToSearch + '%' 
		  AND (DESTTYP = @Param_DestTyp OR @Param_DestTyp = '')
	  RETURN
	END
  
	-- 2 Token
	DECLARE @mToken1 varchar(128) = '%' + SUBSTRING(@mToSearch, 1, @mDelimiterPos - 1) + '%'
	SET @mToSearch = LTRIM(SUBSTRING(@mToSearch, @mDelimiterPos, LEN(@mToSearch) + 1 - @mDelimiterPos))
	SET @mDelimiterPos = CHARINDEX(' ', @mToSearch)
	IF @mDelimiterPos = 0 BEGIN
	  INSERT dbo.hwt_SearchQueryResult (QueryId, QueryTime, DataRecId)
	    SELECT @Param_QueryId, @mQueryTime, RecId FROM hwv_Adressen_Trech 
	    WHERE ([TextData] like @mToken1) AND ([TextData] like '%' + @mToSearch + '%') 
		  AND (DESTTYP = @Param_DestTyp OR @Param_DestTyp = '')
	  RETURN
	END

	-- 3 Token
	DECLARE @mToken2 varchar(128) = '%' + SUBSTRING(@mToSearch, 1, @mDelimiterPos - 1) + '%'
	SET @mToSearch = LTRIM(SUBSTRING(@mToSearch, @mDelimiterPos, LEN(@mToSearch) + 1 - @mDelimiterPos))
	SET @mDelimiterPos = CHARINDEX(' ', @mToSearch)
	IF @mDelimiterPos = 0 BEGIN
	  INSERT dbo.hwt_SearchQueryResult (QueryId, QueryTime, DataRecId)
	    SELECT @Param_QueryId, @mQueryTime, RecId FROM hwv_Adressen_Trech 
		WHERE ([TextData] like @mToken1) AND ([TextData] like @mToken2) AND ([TextData] like '%' + @mToSearch + '%') 
		  AND (DESTTYP = @Param_DestTyp OR @Param_DestTyp = '')
	  RETURN
	END

	-- 4 Token
	DECLARE @mToken3 varchar(128) = '%' + SUBSTRING(@mToSearch, 1, @mDelimiterPos - 1) + '%'
	SET @mToSearch = LTRIM(SUBSTRING(@mToSearch, @mDelimiterPos, LEN(@mToSearch) + 1 - @mDelimiterPos))
	SET @mDelimiterPos = CHARINDEX(' ', @mToSearch)
	IF @mDelimiterPos = 0 BEGIN
	  INSERT dbo.hwt_SearchQueryResult (QueryId, QueryTime, DataRecId)
	    SELECT @Param_QueryId, @mQueryTime, RecId FROM hwv_Adressen_Trech 
		WHERE ([TextData] like @mToken1) AND ([TextData] like @mToken2) AND ([TextData] like @mToken3) AND ([TextData] like '%' + @mToSearch + '%')
		  AND (DESTTYP = @Param_DestTyp OR @Param_DestTyp = '')
	  RETURN
	END

	-- 5 Token
	DECLARE @mToken4 varchar(128) = '%' + SUBSTRING(@mToSearch, 1, @mDelimiterPos - 1) + '%'
	SET @mToSearch = LTRIM(SUBSTRING(@mToSearch, @mDelimiterPos, LEN(@mToSearch) + 1 - @mDelimiterPos))
	SET @mDelimiterPos = CHARINDEX(' ', @mToSearch)
	----------- 5 Token sollten vorerst ausreichen
	IF @mDelimiterPos > 0
	  SET @mToSearch = SUBSTRING(@mToSearch, 1, @mDelimiterPos - 1)
    INSERT dbo.hwt_SearchQueryResult (QueryId, QueryTime, DataRecId)
	  SELECT @Param_QueryId, @mQueryTime, RecId FROM hwv_Adressen_Trech 
	  WHERE ([TextData] like @mToken1) AND ([TextData] like @mToken2) AND ([TextData] like @mToken3) AND ([TextData] like @mToken4) AND ([TextData] like '%' + @mToSearch + '%')
	    AND (DESTTYP = @Param_DestTyp OR @Param_DestTyp = '')
    RETURN
GO

----------------------------------------------------------------------------------------------
-- Adressuebersicht Textrecherche-Result
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Adressen_list_SearchResult')
  DROP PROCEDURE [dbo].[itcproc_Adressen_list_SearchResult]
GO
CREATE PROCEDURE [dbo].[itcproc_Adressen_list_SearchResult]
   @Param_DestTyp  varchar(2)
 , @Param_ToSearch varchar(128)
AS
	SET NOCOUNT ON
	DECLARE @mQueryId varchar(36) = NEWID()
	EXECUTE [dbo].[itcproc_Adressen_TextSearch] @mQueryId, @Param_DestTyp, @Param_ToSearch
	SELECT TOP 100 a.* from [dbo].[hwt_SearchQueryResult] q inner join [dbo].[itcview_Adressen_list] a on a.RecId = q.DataRecId	
	  WHERE QueryId = @mQueryId ORDER BY DESTDEBNR
	DELETE from [dbo].[hwt_SearchQueryResult] WHERE QueryId = @mQueryId
GO

----------------------------------------------------------------------------------------------
-- Adressuebersicht seitenweise auflisten
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Adressen_list')
  DROP PROCEDURE [dbo].[itcproc_Adressen_list]
GO
CREATE PROCEDURE [dbo].[itcproc_Adressen_list]
   @Param_DestTyp  varchar(2)    = ' 0'
 , @Param_ToSearch varchar(128)  = ''
 , @Param_Method   varchar(1)    = '0'
-- @Param_Method:
-- 0 Naechste  ab DESTDEBNR >= @Param_ToSearch
-- 1 Vorherige ab DESTDEBNR <= @Param_ToSearch
-- 2 Ab Anfang
-- 3 Ab Ende
-- 9 Suchergebnis
AS
	SET NOCOUNT ON

	IF @Param_Method = '0'
	  SELECT TOP 20 * from [dbo].[itcview_Adressen_list] WHERE DESTTYP = @Param_DestTyp AND DESTDEBNR >= @Param_ToSearch ORDER BY DESTDEBNR 
	ELSE IF @Param_Method = '1'
	  SELECT * FROM (SELECT TOP 20 * from [dbo].[itcview_Adressen_list] WHERE DESTTYP = @Param_DestTyp AND DESTDEBNR <= @Param_ToSearch ORDER BY DESTDEBNR DESC) AS t ORDER BY DESTDEBNR
	ELSE IF @Param_Method = '2'
	  SELECT TOP 20 * from [dbo].[itcview_Adressen_list] WHERE DESTTYP = @Param_DestTyp ORDER BY DESTDEBNR 
	ELSE IF @Param_Method = '3'
	  SELECT * FROM (SELECT TOP 20 * from [dbo].[itcview_Adressen_list] WHERE DESTTYP = @Param_DestTyp ORDER BY DESTDEBNR DESC) AS t ORDER BY DESTDEBNR
	ELSE IF @Param_Method = '9'
	  EXECUTE [dbo].[itcproc_Adressen_list_SearchResult] @Param_DestTyp, @Param_ToSearch
	ELSE
	  SELECT * from [dbo].[itcview_Adressen_list] WHERE 0 = 1
GO

----------------------------------------------------------------------------------------------
-- Adressuebersicht Kunden
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_Kunden_list')
  DROP PROCEDURE [dbo].[itcproc_Kunden_list]
GO
CREATE PROCEDURE [dbo].[itcproc_Kunden_list]
   @Param_ToSearch varchar(128) = ''
 , @Param_Method   varchar(1)   = '0'
-- @Param_Method:
-- 0 Naechste  ab DESTDEBNR >= @Param_ToSearch
-- 1 Vorherige ab DESTDEBNR <= @Param_ToSearch
-- 2 Ab Anfang
-- 3 Ab Ende
-- 9 Suchergebnis
AS
	EXECUTE [dbo].[itcproc_Adressen_list] ' 0', @Param_ToSearch, @Param_Method
GO

-- EXECUTE [dbo].[itcproc_Kunden_list] 'keil','9'
-- EXECUTE [dbo].[itcproc_Kunden_list] '','0'
-- EXECUTE [dbo].[itcproc_Kunden_list] '10132','0'
-- EXECUTE dbo.itcproc_GetMyPermissions2 '59998', 'User', ''
