pears.NETITSPayslips

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;
*/
}