IF exists(SELECT * FROM sysobjects WHERE name = 'LMSearchInternshipStatus')
DROP PROCEDURE LMSearchInternshipStatus
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE LMSearchInternshipStatus (@searchCriteria_ xml)
AS
BEGIN
IF(@searchCriteria_ is null) 
BEGIN
       raiserror('xml-string can not be null', 11, 1);
END
DECLARE @PrakProjNr_ VARCHAR(100);
DECLARE @PlaceDateFrom_ VARCHAR(100);
DECLARE @PlaceDateTom_ VARCHAR(100);
DECLARE @PeriodVerification_ VARCHAR(100);
DECLARE @StatusDateFrom_  VARCHAR(100);
DECLARE @StatusDateTom_  VARCHAR(100);
DECLARE @Status_ BIT;
DECLARE @Direction_ VARCHAR(100);

DECLARE @MaxResult_ INT

--Extract data from the XML structure

SELECT		
		@PrakProjNr_= ref.value('PrakProjNr[1]/@Value', 'varchar(100)'),
       @PlaceDateFrom_= ref.value('PlaceDateFrom[1]/@Value', 'varchar(100)'),
          @PlaceDateTom_ = ref.value('PlaceDateTom[1]/@Value', 'varchar(100)'),
          @PeriodVerification_ = ref.value('PeriodVerification[1]/@Value', 'varchar(100)'),
          @StatusDateFrom_ = ref.value('StatusDateFrom[1]/@Value', 'varchar(100)'),
          @StatusDateTom_ = ref.value('StatusDateTom[1]/@Value', 'varchar(100)'),
          @Status_ = ref.value('Status[1]/@Value','varchar(10)'),
          @Direction_ = ref.value('Direction[1]/@Value', 'varchar(100)'),

		  @MaxResult_ = ref.value('MaxResult[1]/@Value', 'varchar(100)')
		  
FROM @searchCriteria_.nodes( '/SearchInternshipStatusSPCriteria' ) as node(ref);


IF (@MaxResult_ is Null)
BEGIN
       SET @MaxResult_= 100
END

CREATE TABLE #SearchInternshipStatusTEMPRESULT(
U64_PRAKPROJNR DECIMAL(9),
U64_PLACEDATEFROM VARCHAR(100),
U64_PLACEDATETOM VARCHAR(100),
U64_PLATSNR DECIMAL(9),
U64_UPDDAT VARCHAR(100),
U64_UPDSIGN VARCHAR(100),
U60_INRIKTNING VARCHAR(100),
U60_BENAMNING VARCHAR(100),
Q01_KLARTEXT VARCHAR(100)
)

INSERT INTO #SearchInternshipStatusTEMPRESULT(U64_PRAKPROJNR, U64_PLACEDATEFROM, U64_PLACEDATETOM, U64_PLATSNR,
U64_UPDDAT, U64_UPDSIGN, U60_INRIKTNING, U60_BENAMNING, Q01_KLARTEXT)

SELECT
U64.PRAKPROJNR, U64.FROMDAT, U64.TOMDAT,U64.PLATSNR, U64.UPDDAT,U64.UPDSIGN, U60.INRIKTNING, U60.Benamning, Q01.KlarText
FROM LD0V0U64 U64
left join LD0V0U60 U60 on U64.PRAKPROJNR = U60.PRAKPROJNR
LEFT JOIN LD0V0Q01 Q01 ON Q01.KOD = U60.INRIKTNING and Q01.KODTYP = 'LNINRIKTN'

SELECT DISTINCT TOP(@MaxResult_) 
U64_PRAKPROJNR,
U64_PLACEDATEFROM, U64_PLACEDATETOM, 
U64_PLATSNR,U64_UPDDAT,U64_UPDSIGN,U60_INRIKTNING ,U60_BENAMNING,Q01_KLARTEXT
FROM #SearchInternshipStatusTEMPRESULT TEMP
WHERE	
((@Status_ = '0' AND NOT EXISTS (SELECT 'X' FROM LD0V0U35 U35 WHERE U35.PLATSNR = U64_PLATSNR AND U35.PLATSFROM <= @StatusDateTom_ AND U35.PLATSTOM >= @StatusDateFrom_))
            OR   (@Status_ = '1' AND EXISTS (SELECT 'X' FROM LD0V0U35 U35 WHERE U35.PLATSNR = U64_PLATSNR AND U35.PLATSFROM <= @StatusDateTom_ AND U35.PLATSTOM >= @StatusDateFrom_)))
            AND (@Direction_ IS NULL OR U60_INRIKTNING = @Direction_)  
            AND ((@PlaceDateFrom_ IS NULL AND @PlaceDateTom_ IS NULL)
                OR  (@PeriodVerification_ = '1' AND TEMP.U64_PLACEDATETOM >= @PlaceDateFrom_ AND TEMP.U64_PLACEDATEFROM <= @PlaceDateTom_)
                OR (@PeriodVerification_ = '2' AND TEMP.U64_PLACEDATETOM >= @PlaceDateFrom_)
                OR (@PeriodVerification_ = '3' AND TEMP.U64_PLACEDATEFROM >= @PlaceDateFrom_ AND TEMP.U64_PLACEDATEFROM <= @PlaceDateTom_)
                OR (@PeriodVerification_ = '4' AND TEMP.U64_PLACEDATETOM <= @PlaceDateFrom_ AND TEMP.U64_PLACEDATETOM <= @PlaceDateTom_))

DROP TABLE #SearchInternshipStatusTEMPRESULT
END	
	
GO


GRANT ALL ON LMSearchInternshipStatus TO LD0ROANV

GO

