IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPSearchInquiry')
	DROP PROCEDURE LD0SPSearchInquiry
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE LD0SPSearchInquiry (@searchCriteria_ xml)
AS

BEGIN
IF(@searchCriteria_ is null) 
BEGIN
       raiserror('xml-string can not be null', 11, 1);
END;
DECLARE @FromDate_ VARCHAR(12);
DECLARE @ToDate_ VARCHAR(12);
DECLARE @IsServiceAdded_ VARCHAR(20); 
DECLARE @GenderVerification_  VARCHAR(20);
DECLARE @AgeFrom_  VARCHAR(10);
DECLARE @AgeTo_  VARCHAR(10);
DECLARE @InquiryByWhoTypeId_  VARCHAR(10);
DECLARE @InquiryTypeId_  VARCHAR(10);
DECLARE @InquiryReasonTypeId_  VARCHAR(10);
DECLARE @InquiryServiceTypeId_ VARCHAR(10);
DECLARE @CaseworkerId_  VARCHAR(10);
DECLARE @OrganizationId_  VARCHAR(10);
DECLARE @MaxResult_ INT

--Extract data from the XML structure
SELECT
          @FromDate_= ref.value('FromDate[1]/@Value', 'varchar(12)'),
          @ToDate_ = ref.value('ToDate[1]/@Value', 'varchar(12)'),
          @IsServiceAdded_ = ref.value('IsServiceAdded[1]/@Value', 'varchar(20)'),
          @GenderVerification_ = ref.value('GenderVerification[1]/@Value', 'varchar(20)'),
          @AgeFrom_ = ref.value('AgeFrom[1]/@Value', 'varchar(10)'),
          @AgeTo_ = ref.value('AgeTo[1]/@Value', 'varchar(10)'),
          @InquiryByWhoTypeId_ = ref.value('InquiryByWhoTypeId[1]/@Value', 'varchar(10)'),
          @InquiryTypeId_ = ref.value('InquiryTypeId[1]/@Value', 'varchar(10)'),
          @InquiryReasonTypeId_ = ref.value('InquiryReasonTypeId[1]/@Value', 'varchar(10)'),
          @InquiryServiceTypeId_ = ref.value('InquiryServiceTypeId[1]/@Value', 'varchar(10)'),
          @CaseworkerId_ = ref.value('CaseworkerId[1]/@Value', 'varchar(10)'),
          @OrganizationId_ = ref.value('OrganizationId[1]/@Value', 'varchar(10)'),
		  @MaxResult_ = ref.value('MaxResult[1]/@Value', 'int')
FROM @searchCriteria_.nodes( '/SearchInquirySPCriteria' ) as node(ref);

--Set default result size
IF (@maxResult_ is Null)
BEGIN
       SET @maxResult_=100
END

--START ->Code to convert comma separated value to table
IF OBJECT_ID('tempdb.. #GENDERVERIFICATION') IS NOT NULL
    DROP TABLE #GENDERVERIFICATION
BEGIN
CREATE TABLE #GENDERVERIFICATION(GENDERTYPE INT NOT NULL)
IF(@GenderVerification_ is NOT NULL)
BEGIN
   DECLARE @POSITION        int,
           @NEXTPOSITION    int,
           @VALUELENGTH   int
   SELECT @POSITION = 0, @NEXTPOSITION = 1

   WHILE @NEXTPOSITION > 0
   BEGIN
      SELECT @NEXTPOSITION = charindex(',', @GenderVerification_, @POSITION + 1)
      SELECT @VALUELENGTH = CASE WHEN @NEXTPOSITION > 0
                              THEN @NEXTPOSITION
                              ELSE len(@GenderVerification_) + 1
                         END - @POSITION - 1
       INSERT INTO #GENDERVERIFICATION (GENDERTYPE)
         VALUES (convert(int, substring(@GenderVerification_, @POSITION + 1, @VALUELENGTH)))
      SELECT @POSITION = @NEXTPOSITION
   END
END        
END
--END
    
IF OBJECT_ID('tempdb.. #TEMPRESULT') IS NOT NULL
    DROP TABLE #TEMPRESULT																					

CREATE TABLE #TEMPRESULT(
 [FORFRNR] [decimal](10,0)
,[INQUIRYTYP] [decimal](10, 0)
,[FDATUM] [varchar](12)
,[TOMDAT] [varchar](12)
,[FROMWHO] [decimal](10, 0) 
,[REASON] [decimal](10, 0)  
,[AGE] [decimal](10, 0)    
,[GENDER] [decimal](1, 0)   
,[HAND][varchar](10) 
,[ORGID] [decimal](10, 0) 
,[ORGENHNR] [varchar](12)
,[UPDDAT][varchar](12)
,[UPDSIGN] [varchar](10)
,[FTEXT] [varchar](1000) 
,[MEDHAND] [varchar](10)
,[ORGENHNR2][varchar](10)
,[NUMBEROFCHILDREN] [varchar](10)--[decimal](10, 0) -- 
,[INQUIRYTYPTEXT] [varchar](100) 
,[FROMWHOTEXT] [varchar](100) 
,[REASONTEXT] [varchar](100)
,[CASEWORKERNAME][varchar](50)
,[ORGNAME] [varchar](50)
,[SERVICETYPTEXT] [varchar](50)
,[SERVICETYPID] [decimal] (10,0)
,[LAWSECTIONTEXT] [varchar](50)
,[SERVICEDATE] [decimal] (10,0)
)

INSERT INTO #TEMPRESULT(
 [FORFRNR],[INQUIRYTYP] ,[FDATUM],[TOMDAT],[FROMWHO],[REASON],[AGE],[GENDER],[HAND],[ORGID] ,[ORGENHNR] ,[UPDDAT],[UPDSIGN] ,[FTEXT]
,[MEDHAND] ,[ORGENHNR2],[NUMBEROFCHILDREN] ,[INQUIRYTYPTEXT] ,[FROMWHOTEXT] ,[REASONTEXT] ,[CASEWORKERNAME],[ORGNAME],[SERVICETYPTEXT],[SERVICETYPID]
,[LAWSECTIONTEXT],[SERVICEDATE])

SELECT H30.[FORFRNR]
      ,H30.[FORFR] AS [INQUIRYTYP]
      ,H30.[FDATUM]
	  ,H30.[TOMDAT]
      ,H30.[FVEM] AS [FROMWHO]
      ,H30.[FORSAK] AS [REASON]
      ,H30.[FALDER] AS [AGE]
      ,H30.[FKOEN] AS [GENDER]
      ,H30.[HAND]
      ,H30.[ORGID]
      ,H30.[ORGENHNR]
      ,H30.[UPDDAT]
      ,H30.[UPDSIGN]
      ,CONCAT( H30.[FTEXT1], H30.[FTEXT2], H30.[FTEXT3], H30.[FTEXT4])
      ,H30.[MEDHAND]
      ,H30.[ORGENHNR2]
      ,H30.[ANTALBARN] AS [NUMBEROFCHILDREN]
	  ,F01.[KLARTEXT] AS [INQUIRYTYPTEXT]
	  ,F02.[KLARTEXT] AS [FROMWHOTEXT]
	  ,F03.[KLARTEXT] AS [REASONTEXT]
	  ,I01.[NAMN] AS [CASEWORKERNAME]
	  ,J01.[NAMN] AS [ORGNAME]
	  ,F04.[KLARTEXT] as [SERVICETYPTEXT]
	  ,F04.KOD as [SERVICETYPID]
	  ,Q01.KLARTEXT as [LAWSECTIONTEXT]
	  ,H31.IDATUM as [SERVICEDATE]
FROM LD0V0H30 H30
LEFT JOIN LD0V0Q01 F01 ON F01.KODTYP = 'FORFRAGAN' AND F01.KOD = H30.FORFR
LEFT JOIN LD0V0Q01 F02 ON F02.KODTYP = 'FORFVEM' AND F02.KOD = H30.FVEM
LEFT JOIN LD0V0Q01 F03 ON F03.KODTYP = 'FORFORSAK' AND F03.KOD = H30.FORSAK
LEFT JOIN LD0V0I I01 ON I01.HAND = H30.HAND
LEFT JOIN LD0V0J01 J01 ON J01.ORGENHNR = H30.ORGENHNR
LEFT JOIN LD0V0H31 H31 ON H30.FORFRNR = H31.FORFRNR
LEFT join LD0V0Q01 F04 ON F04.KODTYP = 'FORFINSATS' and F04.KOD =H31.INSATS
LEFT JOIN LD0V0Q01 Q01 ON Q01.KODTYP = 'LAGRUM' AND Q01.KOD = H31.ILAGRUM
WHERE
 (@FromDate_ IS NULL OR @FromDate_ <= H30.[FDATUM])
AND( @ToDate_ IS NULL OR  @ToDate_ >= H30.[FDATUM])
AND( @GenderVerification_ IS NULL OR  H30.[FKOEN] IN ( SELECT GENDERTYPE FROM #GENDERVERIFICATION))
AND (@AgeFrom_ IS NULL OR @AgeTo_ IS NULL OR H30.[FALDER] BETWEEN @AgeFrom_ AND @AgeTo_)
AND (@InquiryByWhoTypeId_ IS NULL OR @InquiryByWhoTypeId_ = H30.[FVEM] )
AND (@InquiryTypeId_  IS NULL OR @InquiryTypeId_= H30.[FORFR])
AND (@InquiryReasonTypeId_  IS NULL OR @InquiryReasonTypeId_= H30.[FORSAK])
AND (@CaseworkerId_  IS NULL OR @CaseworkerId_= H30.[HAND])
AND (@OrganizationId_  IS NULL OR @OrganizationId_= H30.[ORGENHNR])

IF( @IsServiceAdded_='0')
BEGIN
	SELECT distinct  TOP(@MaxResult_) A.[FORFRNR], A.[INQUIRYTYP], A.[FDATUM],A.[TOMDAT], A.[FROMWHO], A.[REASON], A.[AGE], A.[GENDER], A.[HAND], A.[ORGID], A.[ORGENHNR], 
	A.[UPDDAT],A.[UPDSIGN], A.[FTEXT], A.[MEDHAND], A.[ORGENHNR2], A.[NUMBEROFCHILDREN], A.[INQUIRYTYPTEXT], A.[FROMWHOTEXT],
	A.[REASONTEXT], A.[CASEWORKERNAME], A.[ORGNAME] , A.SERVICETYPTEXT,A.LAWSECTIONTEXT,A.SERVICEDATE
	FROM #TEMPRESULT A
	JOIN LD0V0H31 H31 ON A.FORFRNR = H31.FORFRNR AND (@InquiryServiceTypeId_ IS NULL OR H31.INSATS = @InquiryServiceTypeId_ ) 
	where (@InquiryServiceTypeId_ IS NULL OR @InquiryServiceTypeId_ =SERVICETYPID) order by A.[FORFRNR] desc
END 

ELSE IF(@IsServiceAdded_='1')
BEGIN
	SELECT TOP(@MaxResult_) A.[FORFRNR], A.[INQUIRYTYP], A.[FDATUM],A.[TOMDAT], A.[FROMWHO], A.[REASON], A.[AGE], A.[GENDER], A.[HAND], A.[ORGID], A.[ORGENHNR], 
	A.[UPDDAT],A.[UPDSIGN], A.[FTEXT], A.[MEDHAND], A.[ORGENHNR2], A.[NUMBEROFCHILDREN], A.[INQUIRYTYPTEXT], A.[FROMWHOTEXT],
	A.[REASONTEXT], A.[CASEWORKERNAME], A.[ORGNAME] , A.SERVICETYPTEXT,A.LAWSECTIONTEXT,A.SERVICEDATE
	FROM #TEMPRESULT A 
	where SERVICETYPID IS NULL order by A.[FORFRNR] desc
END 

ELSE IF( @IsServiceAdded_='2')
BEGIN
IF(@InquiryServiceTypeId_ IS NULL)
  SELECT TOP(@MaxResult_) A.[FORFRNR], A.[INQUIRYTYP], A.[FDATUM],A.[TOMDAT], A.[FROMWHO], A.[REASON], A.[AGE], A.[GENDER], A.[HAND], A.[ORGID], A.[ORGENHNR], 
	A.[UPDDAT],A.[UPDSIGN], A.[FTEXT], A.[MEDHAND], A.[ORGENHNR2], A.[NUMBEROFCHILDREN], A.[INQUIRYTYPTEXT], A.[FROMWHOTEXT],
	A.[REASONTEXT], A.[CASEWORKERNAME], A.[ORGNAME]  , A.SERVICETYPTEXT,A.LAWSECTIONTEXT,A.SERVICEDATE
  FROM #TEMPRESULT A  order by A.[FORFRNR] desc
END 

DROP TABLE #TEMPRESULT
DROP TABLE #GENDERVERIFICATION
END
GO



GRANT ALL ON LD0SPSearchInquiry TO LD0ROANV
GO




