pears.pay_usercheckemployment

CREATE PROCEDURE "pears"."pay_usercheckemployment"( 
  IN "pid" CHAR(20),IN "pstartdate" DATE ) 
BEGIN
  DECLARE "pempid" CHAR(20);
  DECLARE "pempstartdate" DATE;
  DECLARE "oldtax" INTEGER;
  DECLARE "oldregno" CHAR(20);
  DECLARE "newtax" INTEGER;
  DECLARE "newregno" CHAR(20);
  SELECT FIRST "pay_employmentid","startdate","companyregno","taxmethod" INTO "pempid","pempstartdate","oldregno","oldtax"
    FROM "pay_employment" WHERE "personid" = "pid" AND "enddate" IS NULL ORDER BY "startdate" DESC;
  UPDATE "pay_employment" SET "enddate" = "pstartdate" WHERE "personid" = "pid" AND "pay_employmentid" <> "pempid" AND "enddate" IS NULL;
  IF "pempid" IS NULL THEN
    INSERT INTO "pay_employment"( "pay_employmentid","personid","startdate",
      "CompanyName","CompanyRegNo","HMRCEngagementDetail","PayrollAddr1","PayrollAddr2","PayrollAddr3","PayrollAddr4",
      "PayrollFullAddr","payrollnumber","PayrollPostcode","TaxMethod","UTR","VATNumber",
      "VATRegistered" ) SELECT "uniquekey"("pid"),"pid","pstartdate",
        "e"."CompanyName","e"."RegNo","e"."HMRCEngagementDetail","e"."PayrollAddr1","e"."PayrollAddr2","e"."PayrollAddr3","e"."PayrollAddr4",
        "p"."extranotes","p"."payrollnumber","e"."PayrollPostcode","e"."TaxMethod","e"."UniqueTaxReference","e"."VATNumber","e"."VATRegistered"
        FROM "person" AS "p" LEFT OUTER JOIN "pay_employee" AS "e" WHERE "p"."personid" = "pid";
    UPDATE "pay_employee" SET "p45requested" = 0 WHERE "personid" = "pid"
  ELSE
    SELECT "regno","taxmethod" INTO "newregno","newtax" FROM "pay_employee" WHERE "personid" = "pid";
    IF "isnull"("oldtax",'') = '' THEN
      UPDATE "pay_employment" SET "payrollnumber" = "p"."payrollnumber","taxmethod" = "e"."taxmethod","PayrollFullAddr" = "p"."extranotes",
        "CompanyName" = "e"."CompanyName","CompanyRegNo" = "e"."RegNo","HMRCEngagementDetail" = "e"."HMRCEngagementDetail",
        "PayrollAddr1" = "e"."PayrollAddr1","PayrollAddr2" = "e"."PayrollAddr2","PayrollAddr3" = "e"."PayrollAddr3","PayrollAddr4" = "e"."PayrollAddr4",
        "PayrollPostcode" = "e"."PayrollPostcode","UTR" = "e"."UniqueTaxReference","VATNumber" = "e"."VATNumber","VATRegistered" = "e"."VATRegistered" FROM
        "pay_employee" AS "e" KEY JOIN "person" AS "p" KEY JOIN "pay_employment" WHERE "pay_employmentid" = "pempid"
    ELSE
      IF(("isnull"("oldtax",'') <> "isnull"("newtax",''))
        OR(("isnull"("oldregno",'') <> "isnull"("newregno",'')) AND "isnull"("newtax",'') IN( 2,3 ) )) THEN
        UPDATE "pay_employment" SET "enddate" = "dateadd"("day",-1,"pstartdate") WHERE "pay_employmentid" = "pempid";
        INSERT INTO "pay_employment"( "pay_employmentid","personid","startdate",
          "CompanyName","CompanyRegNo","HMRCEngagementDetail","PayrollAddr1","PayrollAddr2","PayrollAddr3","PayrollAddr4",
          "PayrollFullAddr","payrollnumber","PayrollPostcode","TaxMethod","UTR","VATNumber",
          "VATRegistered" ) SELECT "uniquekey"("pid"),"pid","pstartdate",
            "e"."CompanyName","e"."RegNo","e"."HMRCEngagementDetail","e"."PayrollAddr1","e"."PayrollAddr2","e"."PayrollAddr3","e"."PayrollAddr4",
            "p"."extranotes","p"."payrollnumber","e"."PayrollPostcode","e"."TaxMethod","e"."UniqueTaxReference","e"."VATNumber","e"."VATRegistered"
            FROM "person" AS "p" LEFT OUTER JOIN "pay_employee" AS "e" WHERE "p"."personid" = "pid"
      ELSE
        IF "pempstartdate" > "pstartdate" THEN
          UPDATE "pay_employment" SET "startdate" = "pstartdate" WHERE "pay_employmentid" = "pempid"
        END IF
      END IF
    END IF
  END IF
END