====== pears.NETITSPayslips ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NETITSPayslips"( in "pWebUserID" char(20),in "pPersonID" char(20) ) 
--RESULT( "id" char(20),"title" char(100),"className" char(50),"allDay" char(5),"shiftStart" char(20),"shiftEnd" char(20),"description" char(250),"editable" char(5),"deletable" char(5),"confirmable" char(5),"unconfirmable" char(5) ) 
// IQXWeb
// ITSPaySlip Proc
begin
  set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  select(select "list"("h"."CompanyName") from "itspaysliphours" as "h" where "h"."itspayslipimportid" = "PI") as "companyName",
    "I"."BenefitType",
    "I"."BenefitAmount",
    "I"."DeductionType",
    "I"."DeductionAmount",
    "I"."PayTime",
    "I"."PayRate",
    "I"."PayAmount",
    "I"."BasicPayTotal",
    "I"."GrossPayTotal",
    "I"."DeductionsTotal",
    "I"."NetPayTotal",
    "I"."EmpCode",
    "I"."PayWeek",
    "I"."NINumber",
    "I"."PayDate",
    "string"("p"."name","char"(10),"p"."Addr1",(if "isnull"("p"."addr2",'') <> '' then "char"(10) endif),"p"."Addr2",(if "isnull"("p"."addr3",'') <> '' then "char"(10) endif),"p"."Addr3",(if "isnull"("p"."town",'') <> '' then "char"(10) endif),"town",(if "isnull"("p"."county",'') <> '' then "char"(10) endif),"county",(if "isnull"("p"."country",'') <> '' then "char"(10) endif),"country","char"(10),"postcode") as "Address",
    "I"."NICode",
    "I"."PayMethod",
    "I"."TaxCode",
    "I"."PageNumber",
    "PageCount",
    (select top 1 "string"("name","char"(10),"addressetc4") from "agencydetails" where "replace"("replace"("trim"("left"("name","charindex"(' ltd',"name")+3)),'(',''),')','') = "replace"("replace"("trim"("left"("clientaddress","charindex"(' ltd',"clientaddress")+3)),'(',''),')','') order by "clientaddress" desc) as "ClientAddress",
    "I"."InsuranceAmount",
    "I"."EmployerNI",
    "I"."EmployeeNI",
    "I"."EmployeePension",
    "I"."EmployerPension",
    "I"."GrossPayYTD",
    "I"."TaxYTD",
    "I"."TaxablePayYTD",
    "I"."Tax",
    "I"."ITSPayslipImportID",
    "I"."ITSPayslipImportID" as "PI"
    --,*
    from "ITSPayslipImport" as "I" join "person" as "p" on "replace"("p"."ni",' ','') = "replace"("I"."ninumber",' ','')
    where "p"."personid" = "pPersonID"
    order by "string"("substring"("PayDate",7,4),"substring"("PayDate",4,2),"substring"("PayDate",1,2)) desc
end /* DOC
2017-10-06 PC modified to look up on NI number, so that multiple profiles can still access payslips of different divisions
2018-06-08 PC modify so that ClientAddress search does not rely on branch names having brackets in spreadsheet but will work with either with or without
2019-07-08 GJ Updated to work with all the changes in iqxWEB 1.4.0
*/
/* TEST
SELECT * FROM NETITSPayslips('test.candidate', 'XXXXXXXXXXXXXXXXXXXX');
EXPECT EOF;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NETITSPayslips" IS 
{create PROCEDURE pears."NETITSPayslips"( in "pWebUserID" char(20), in "pPersonID" char(20) )
--RESULT( "id" char(20),"title" char(100),"className" char(50),"allDay" char(5),"shiftStart" char(20),"shiftEnd" char(20),"description" char(250),"editable" char(5),"deletable" char(5),"confirmable" char(5),"unconfirmable" char(5) ) 
// IQXWeb
// ITSPaySlip Proc
BEGIN
    SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);

    SELECT
        (select list(h.CompanyName) from itspaysliphours h where h.itspayslipimportid=PI) as companyName,
        I.BenefitType,
        I.BenefitAmount,
        I.DeductionType,
        I.DeductionAmount,
        I.PayTime,
        I.PayRate,
        I.PayAmount,
        I.BasicPayTotal,
        I.GrossPayTotal,
        I.DeductionsTotal,
        I.NetPayTotal,
        I.EmpCode,
        I.PayWeek,
        I.NINumber,
        I.PayDate,
        string(p.name,char(10),p.Addr1,(if isnull(p.addr2,'') !='' then char(10) endif),p.Addr2,(if isnull(p.addr3,'') !='' then char(10) endif),p.Addr3,(if isnull(p.town,'') !='' then char(10) endif),town,(if isnull(p.county,'') !='' then char(10) endif),county,(if isnull(p.country,'') !='' then char(10) endif),country,char(10),postcode) as Address,
        I.NICode,
        I.PayMethod,
        I.TaxCode,
        I.PageNumber,
        PageCount,
        (select top 1 string(name,char(10),addressetc4) from agencydetails where replace(replace(trim(left(name,charindex(' ltd',name)+3)),'(',''),')','')= replace(replace(trim(left(clientaddress,charindex(' ltd',clientaddress)+3)),'(',''),')','') order by clientaddress desc) as ClientAddress,
        I.InsuranceAmount,
        I.EmployerNI,
        I.EmployeeNI,
        I.EmployeePension,
        I.EmployerPension,
        I.GrossPayYTD,
        I.TaxYTD,
        I.TaxablePayYTD,
        I.Tax,
        I.ITSPayslipImportID,
        I.ITSPayslipImportID as PI
        --,*
    FROM
        ITSPayslipImport I inner join person p on replace(p.ni,' ','')=replace(I.ninumber,' ','')
    WHERE
        p.personid = pPersonID
    ORDER BY 
        string(substring(PayDate,7,4),substring(PayDate,4,2),substring(PayDate,1,2)) Desc
END

/* DOC
    2017-10-06 PC modified to look up on NI number, so that multiple profiles can still access payslips of different divisions
    2018-06-08 PC modify so that ClientAddress search does not rely on branch names having brackets in spreadsheet but will work with either with or without
    2019-07-08 GJ Updated to work with all the changes in iqxWEB 1.4.0
*/
/* TEST
SELECT * FROM NETITSPayslips('test.candidate', 'XXXXXXXXXXXXXXXXXXXX');
EXPECT EOF;
*/
}
</code>