IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPSearchAttendanceAbsence')
	DROP PROCEDURE LD0SPSearchAttendanceAbsence
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE LD0SPSearchAttendanceAbsence (@searchCriteria_ xml)
AS
BEGIN
IF(@searchCriteria_ is null) 
BEGIN
       raiserror('xml-string can not be null', 11, 1);
END;
-- U10
DECLARE @EnrollmentStartDate_ VARCHAR(10);
DECLARE @EnrollmentStopDate_ VARCHAR(10);
DECLARE @EnrollmentPeriodVerification_  VARCHAR(10);

-- Item from V1U80
DECLARE @Introarb_ VARCHAR(10);
DECLARE @DateFrom_ VARCHAR(10);
DECLARE @DateTo_ VARCHAR(10);

DECLARE @Activity_  VARCHAR(10);
DECLARE @ActivityType_  VARCHAR(10);
DECLARE @CaseworkerId_ VARCHAR(10);

-- Item from V0A
DECLARE @YearOfBirthFrom_  VARCHAR(100);
DECLARE @YearOfBirthTo_  VARCHAR(100);
DECLARE @AgeVerification_  VARCHAR(100);
DECLARE @GenderVerification_ VARCHAR(100);
--
-- U11
DECLARE @DirectiveOrganizationInternal_ VARCHAR(100); 
DECLARE @DirectiveOrganizationExtern_ VARCHAR(100); 

-- U10
DECLARE @OrganizationUnitId_ VARCHAR(100); 
DECLARE @OrganizationId_ VARCHAR(100);

--U34
DECLARE @SimpleTaskId_ VARCHAR(100);

DECLARE @MaxResult_ INT;

--Extract data from the XML structure
SELECT
          @Introarb_ = ref.value('Introarb[1]/@Value', 'varchar(10)'),
          @DateFrom_ = ref.value('DateFrom[1]/@Value', 'varchar(10)'),
          @DateTo_ = ref.value('DateTo[1]/@Value', 'varchar(10)'),
          @EnrollmentStartDate_ = ref.value('EnrollmentStartDate[1]/@Value', 'varchar(20)'),
          @EnrollmentStopDate_ = ref.value('EnrollmentStopDate[1]/@Value', 'varchar(20)'),
          @EnrollmentPeriodVerification_ = ref.value('EnrollmentPeriodVerification[1]/@Value', 'varchar(10)'),
          @YearOfBirthFrom_ = ref.value('YearOfBirthFrom[1]/@Value', 'varchar(100)'),
          @YearOfBirthTo_ = ref.value('YearOfBirthTo[1]/@Value', 'varchar(100)'),
          @AgeVerification_ = ref.value('AgeVerification[1]/@Value', 'varchar(100)'),
          @GenderVerification_ = ref.value('GenderVerification[1]/@Value', 'varchar(100)'),
          @Activity_ = ref.value('Activity[1]/@Value', 'varchar(10)'),
          @ActivityType_ = ref.value('ActivityType[1]/@Value', 'varchar(10)'),
          @CaseworkerId_ = ref.value('CaseworkerId[1]/@Value', 'varchar(10)'),
          @DirectiveOrganizationInternal_ = ref.value('DirectiveOrganizationInternal[1]/@Value', 'varchar(100)'),
          @DirectiveOrganizationExtern_ = ref.value('DirectiveOrganizationExtern[1]/@Value', 'varchar(100)'),
          @OrganizationUnitId_ = ref.value('OrganizationUnitId[1]/@Value', 'varchar(100)'),
          @OrganizationId_ = ref.value('OrganizationId[1]/@Value', 'varchar(100)'),
          @SimpleTaskId_ = ref.value('SimpleTaskId[1]/@Value', 'varchar(100)'),

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

IF(@DateFrom_ is null)
BEGIN
       raiserror('DateFrom can not be null', 11, 1);
END;

IF (@maxResult_ is Null)
BEGIN
       SET @maxResult_=100
END
                                                                                                                                            
IF OBJECT_ID('tempdb.. #TEMPAttendanceAbsenceRESULT') IS NOT NULL
    DROP TABLE #TEMPAttendanceAbsenceRESULT																					


CREATE TABLE #TEMPAttendanceAbsenceRESULT(
DATUM varchar(12),
NAMN varchar(180),
PERSONNR  varchar(12),
KUNDNUMMER decimal(10,0),
INTROARB decimal(10,0),
AKTIVITET decimal(10,0),
AKTIVTYP decimal(10,0),
U34HAND varchar(100),
FROMTID varchar(100),
TOMTID varchar(100),
INTID varchar(100),
UTTID varchar(100),
FRANVARO decimal(10,0),
NARVARO decimal(10,0),
FRANVAROTID varchar(100),
NARVAROTID varchar(100),
FRANVAROORSAK decimal(10,0),
FODDAT varchar(100),
KON decimal(10,0),
FRANVARONARVARONR decimal(10,0),
ORSAKSTEXT varchar(250),
PLANAKTIVITETNR decimal(10,0),
INSKRIVNINGSNR decimal(10,0),
INCHECKNINGNR decimal(10,0),
UTCHECKNINGNR decimal(10,0))     

INSERT INTO #TEMPAttendanceAbsenceRESULT(FRANVARONARVARONR, PLANAKTIVITETNR, DATUM, FROMTID, TOMTID, INTID, UTTID, INCHECKNINGNR, UTCHECKNINGNR, NARVARO, FRANVARO, FRANVAROTID, NARVAROTID, FRANVAROORSAK, ORSAKSTEXT, INSKRIVNINGSNR, INTROARB, AKTIVTYP, AKTIVITET, U34HAND, PERSONNR, NAMN, FODDAT, KON, KUNDNUMMER)
SELECT  A.FRANVARONARVARONR, A.PLANAKTIVITETNR, A.DATUM, A.FROMTID, A.TOMTID, A.INTID, A.UTTID, A.INCHECKNINGNR, A.UTCHECKNINGNR, A.NARVARO, A.FRANVARO, A.FRANVAROTID, A.NARVAROTID, A.FRANVAROORSAK, A.ORSAKSTEXT, A.INSKRIVNINGSNR, A.INTROARB, A.AKTIVTYP, A.AKTIVITET, A.U34HAND, A.PERSONNR, A.NAMN, A.FODDAT, A.KON, A.KUNDNUMMER
FROM LD0V1U80 A 
WHERE A.DATUM >= CONVERT(VARCHAR, (SELECT CONVERT(datetime, @DateFrom_)), 112)  
AND (@DateTo_ IS NULL OR DATUM <= CONVERT(VARCHAR, (SELECT CONVERT(datetime, @DateTo_)), 112)  )
AND (@Introarb_  IS NULL OR @Introarb_ = A.INTROARB)
AND (@GenderVerification_ IS NULL OR A.KON = (SELECT CAST(@GenderVerification_ AS DECIMAL(2,0))) ) 
AND ((@YearOfBirthFrom_ IS NULL AND @YearOfBirthTo_ IS NULL) OR (A.FODDAT BETWEEN @YearOfBirthFrom_ AND @YearOfBirthTo_ ))
AND (@OrganizationUnitId_ is NULL or EXISTS (SELECT 'X' FROM LD0VYU10 U10 WHERE A.INSKRIVNINGSNR = U10.INSKRIVNINGSNR AND U10.ORGENHNR = @OrganizationUnitId_ and U10.ORGID = @OrganizationId_))
AND (@DirectiveOrganizationExtern_ IS NULL OR EXISTS(SELECT'X' FROM LD0V0U11 U11 WHERE A.INSKRIVNINGSNR = U11.INSKRIVNINGSNR AND U11.ORGEXTERN = @DirectiveOrganizationExtern_ ))
AND (@DirectiveOrganizationInternal_ IS NULL  OR EXISTS(SELECT'X' FROM LD0V0U11 U11 WHERE A.INSKRIVNINGSNR = U11.INSKRIVNINGSNR AND U11.ANVISORGINTERN = @DirectiveOrganizationInternal_ ))
AND (@CaseworkerId_ IS NULL OR @CaseworkerId_ = A.U34HAND)
AND (@Activity_ IS NULL OR @Activity_ = A.AKTIVITET)
AND (@ActivityType_ IS NULL OR @ActivityType_ = A.AKTIVTYP)
AND (@SimpleTaskId_ IS NULL OR EXISTS (SELECT 'X' FROM LD0VYU34 U34 WHERE U34.PLANAKTIVITETNR = A.PLANAKTIVITETNR AND U34.ENKELPLATSNR = @SimpleTaskId_))

SELECT DISTINCT TOP(@MaxResult_)
  U80.FRANVARONARVARONR, U80.PLANAKTIVITETNR, U80.INSKRIVNINGSNR, 
  U80.DATUM, U80.NAMN, U80.PERSONNR, U80.KUNDNUMMER, U80.FROMTID, U80.TOMTID, U80.INTID, U80.UTTID, U80.FRANVARO, U80.NARVARO, U80.FRANVAROTID, U80.NARVAROTID, 
  U80.ORSAKSTEXT, U80.FODDAT, U80.KON, U80.INTROARB, U80.AKTIVTYP, U80.AKTIVITET,
  U34.ENKELPLATSNR, U35.PRAKPROJNR, 
  Q01A.KLARTEXT AS INTROARB_T,
  Q01B.KLARTEXT AS AKTIVTYP_T,
  Q01C.KLARTEXT AS AKTIVITET_T,
  Q01D.KLARTEXT AS FRANVAROORSAK_T,
  Q01E.KLARTEXT AS KON_T,
  I.NAMN AS U34HAND_T,
  case when U35.PRAKPROJNR > 0 THEN U60.BENAMNING ELSE  U70.BENAMNING END AS PLATSBENAMNING
FROM #TEMPAttendanceAbsenceRESULT as U80 
JOIN LD0V1U10 AS U10 ON U80.INSKRIVNINGSNR = U10.INSKRIVNINGSNR 
JOIN LD0VYU34 U34 ON U34.PLANAKTIVITETNR = U80.PLANAKTIVITETNR
LEFT OUTER JOIN LD0V0U35 U35 ON U35.PLANAKTIVITETNR = U80.PLANAKTIVITETNR AND U34.INTROARB = 2
LEFT OUTER JOIN LD0V0Q01 AS Q01A ON Q01A.KOD = U80.INTROARB and Q01A.KODTYP = 'LNINTROARB'
LEFT OUTER JOIN LD0V0Q01 AS Q01B ON Q01B.KOD = U80.AKTIVTYP and Q01B.KODTYP = 'LNAKTIVTYP'
LEFT OUTER JOIN LD0V0Q01 AS Q01C ON Q01C.KOD = U80.AKTIVITET and Q01C.KODTYP = 'LNAKTIV'
LEFT OUTER JOIN LD0V0Q01 AS Q01D ON Q01D.KOD = U80.FRANVAROORSAK and Q01D.KODTYP = 'LNFRANVORS'
LEFT OUTER JOIN LD0V0Q01 AS Q01E ON Q01E.KOD = U80.KON and Q01E.KODTYP = 'LNKOEN'
LEFT OUTER JOIN LD0V0I AS I ON I.HAND = U80.U34HAND
LEFT OUTER JOIN LD0V0U70 AS U70 ON U70.ENKELPLATSNR = U34.ENKELPLATSNR AND U34.ENKELPLATSNR > 0
LEFT OUTER JOIN LD0V0U60 AS U60 ON U60.PRAKPROJNR = U35.PRAKPROJNR 

WHERE  
--1 = Aktuella inom perioden
--2 = Aktuella inom perioden pågår idag
--3 = Inskrivning öppnad under perioden
--4 = Avslutade under perioden
(@EnrollmentPeriodVerification_ IS NULL OR (@EnrollmentStartDate_ IS NULL AND @EnrollmentStopDate_ IS NULL) OR (@EnrollmentPeriodVerification_ = 4
                                              OR U10.[INLEDDAT] <= CASE WHEN @EnrollmentPeriodVerification_ IN (1,2,3) 
                                                                         THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @EnrollmentStopDate_)), 112)  
                                                                         ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                               END)  

AND (@EnrollmentPeriodVerification_ IN (3) 
                                              OR U10.[AVSLUTDAT] >= CASE WHEN @EnrollmentPeriodVerification_ IN (1,2,4) 
                                                                          THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @EnrollmentStartDate_)), 112) 
                                                                           ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END)

AND (@EnrollmentPeriodVerification_ IN (1,2,4) 
                                              OR U10.[INLEDDAT] >= CASE WHEN @EnrollmentPeriodVerification_ = 3 
                                              THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @EnrollmentStartDate_)), 112)  
                                              ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END)

AND (@EnrollmentPeriodVerification_ IN (1,2,3) 
                                              OR U10.[AVSLUTDAT] <= CASE WHEN @EnrollmentPeriodVerification_ = 4 
                                              THEN CONVERT(VARCHAR, (SELECT CONVERT(datetime, @EnrollmentStopDate_)), 112)  
                                              ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)
                                                                                                                                                  END)

AND (@EnrollmentPeriodVerification_ IN (1,3,4)
											  OR U10.[AVSLUTDAT] = CASE WHEN @EnrollmentPeriodVerification_ IN (2) 
                                              THEN '99999999'  
                                              ELSE CONVERT(VARCHAR, (SELECT CONVERT(datetime, GETDATE())), 112)

                                                                                                                                                  END))  

DROP TABLE #TEMPAttendanceAbsenceRESULT

END


GO


GRANT ALL ON LD0SPSearchAttendanceAbsence TO LD0ROANV

GO

