pears.NetOwnerCandidateSetBank

CREATE PROCEDURE "pears"."NetOwnerCandidateSetBank"( IN "pWebUserID" CHAR(20),IN "ppersonid" CHAR(20),IN "pni" CHAR(10) DEFAULT 'NOT-SET',IN "pBankName" CHAR(30) DEFAULT 'NOT-SET',IN "pBankSortCode" CHAR(8) DEFAULT 'NOT-SET',IN "pBankAcName" CHAR(50) DEFAULT 'NOT-SET',IN "pBankAcNo" CHAR(8) DEFAULT 'NOT-SET',IN "pUniqueTaxReference" CHAR(20) DEFAULT 'NOT-SET',IN "poldni" CHAR(10) DEFAULT 'NOT-SET',IN "poldBankName" CHAR(30) DEFAULT 'NOT-SET',IN "poldBankSortCode" CHAR(8) DEFAULT 'NOT-SET',IN "poldBankAcName" CHAR(50) DEFAULT 'NOT-SET',IN "poldBankAcNo" CHAR(8) DEFAULT 'NOT-SET',IN "poldUniqueTaxReference" CHAR(20) DEFAULT 'NOT-SET' ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "ssql" CHAR(255); -- sql string
  DECLARE "errmsg" CHAR(150);
  DECLARE "DivID" CHAR(20);
  DECLARE "CurrentStates" CHAR(30);
  SET "ssql" = '';
  -- check that the record is owned by the logged in user
  SET "DivID" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  SET "CurrentStates" = "string"('[',(SELECT FIRST "PersonCurrentStates" FROM "params"),']');
  IF NOT "ppersonid" = any(SELECT "person"."personid" FROM "person" WHERE "person"."divisionid" = "DivID" AND "person"."status" LIKE "CurrentStates") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "left"("pBankAcNo",1) = '*' THEN
    SELECT '2:~Check your bank details are correct';
    RETURN
  END IF;
  IF "isnull"("pni",'') <> "isnull"("poldni",'') THEN -- National insurance number goes into person table. others go into pay_Employee table
    SET "pni" = "ucase"("pni");
    IF "trim"("isnull"("pni",'')) <> '' THEN
      SET "errmsg" = "validateninumber"("pni");
      IF "trim"("isnull"("errmsg",'')) <> '' THEN
        SELECT '121:~'+"errmsg";
        RETURN
      END IF END IF;
    SET "ssql" = "ssql"+'ni=pni'
  END IF;
  IF "ssql" <> '' THEN
    EXECUTE IMMEDIATE 'update person set '+"ssql"+' where personid=ppersonid'
  END IF;
  -- check if record already present in Pay_Employee. If no, insert stub record first
  IF NOT EXISTS(SELECT * FROM "Pay_Employee" WHERE "Personid" = "ppersonid") THEN
    -- insert stub
    INSERT INTO "pay_employee"( "personid" ) VALUES( "ppersonid" ) 
  END IF;
  SET "ssql" = ''; -- clear for second update to Pay_Employee table
  -- update
  IF "isnull"("pBankName",'') <> "isnull"("poldBankName",'') THEN
    SET "ssql" = "ssql"+',BankName=pBankName'
  END IF;
  IF "isnull"("pBankSortCode",'') <> "isnull"("poldBankSortCode",'') THEN
    SET "ssql" = "ssql"+',BankSortCode=pBankSortCode'
  END IF;
  IF "isnull"("pBankAcName",'') <> "isnull"("poldBankAcName",'') THEN
    SET "ssql" = "ssql"+',BankAcName=pBankAcName'
  END IF;
  IF "isnull"("pBankAcNo",'') <> "isnull"("poldBankAcNo",'') THEN
    SET "ssql" = "ssql"+',BankAcNo=pBankAcNo'
  END IF;
  IF "isnull"("pUniqueTaxReference",'') <> "isnull"("poldUniqueTaxReference",'') THEN
    SET "ssql" = "ssql"+',UniqueTaxReference=pUniqueTaxReference'
  END IF;
  IF "ssql" <> '' THEN
    EXECUTE IMMEDIATE 'update Pay_Employee set '+"stuff"("ssql",1,1,'')+' where personid=ppersonid'
  END IF;
  CALL "personrecordupdated"("ppersonid");
  SELECT '0:~Success'
END /* DOC
2018-05-07 PC IW-503 hide bank account number except for last two digits
2018-08-27 PC improve error handling
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerCandidateSetBank" IS 
{CREATE PROCEDURE pears."NetOwnerCandidateSetBank"( IN "pWebUserID" CHAR(20),IN "ppersonid" CHAR(20),IN "pni" CHAR(10) DEFAULT 'NOT-SET',IN "pBankName" CHAR(30) DEFAULT 'NOT-SET',IN "pBankSortCode" CHAR(8) DEFAULT 'NOT-SET',IN "pBankAcName" CHAR(50) DEFAULT 'NOT-SET',IN "pBankAcNo" CHAR(8) DEFAULT 'NOT-SET',IN "pUniqueTaxReference" CHAR(20) DEFAULT 'NOT-SET',IN "poldni" CHAR(10) DEFAULT 'NOT-SET',IN "poldBankName" CHAR(30) DEFAULT 'NOT-SET',IN "poldBankSortCode" CHAR(8) DEFAULT 'NOT-SET',IN "poldBankAcName" CHAR(50) DEFAULT 'NOT-SET',IN "poldBankAcNo" CHAR(8) DEFAULT 'NOT-SET',IN "poldUniqueTaxReference" CHAR(20) DEFAULT 'NOT-SET' ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "ssql" CHAR(255); -- sql string
  DECLARE "errmsg" CHAR(150);
  DECLARE "DivID" CHAR(20);
  DECLARE "CurrentStates" CHAR(30);
  SET "ssql" = '';
  -- check that the record is owned by the logged in user
  SET "DivID" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  SET "CurrentStates" = "string"('[',(SELECT FIRST "PersonCurrentStates" FROM "params"),']');
  IF NOT "ppersonid" = any(SELECT "person"."personid" FROM "person" WHERE "person"."divisionid" = "DivID" AND "person"."status" LIKE "CurrentStates") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF LEFT(pBankAcNo,1)='*' THEN
    SELECT '2:~Check your bank details are correct';
    RETURN; 
  END IF;
  IF "isnull"("pni",'') <> "isnull"("poldni",'') THEN -- National insurance number goes into person table. others go into pay_Employee table
    SET "pni" = "ucase"("pni");
    IF "trim"("isnull"("pni",'')) <> '' THEN
      SET "errmsg" = "validateninumber"("pni");
      IF "trim"("isnull"("errmsg",'')) <> '' THEN
        SELECT '121:~'+"errmsg";
        RETURN
      END IF END IF;
    SET "ssql" = "ssql"+'ni=pni'
  END IF;
  IF "ssql" <> '' THEN
    EXECUTE IMMEDIATE 'update person set '+"ssql"+' where personid=ppersonid'
  END IF;
  -- check if record already present in Pay_Employee. If no, insert stub record first
  IF NOT EXISTS(SELECT * FROM "Pay_Employee" WHERE "Personid" = "ppersonid") THEN
    -- insert stub
    INSERT INTO "pay_employee"( "personid" ) VALUES( "ppersonid" ) 
  END IF;
  SET "ssql" = ''; -- clear for second update to Pay_Employee table
  -- update
  IF "isnull"("pBankName",'') <> "isnull"("poldBankName",'') THEN
    SET "ssql" = "ssql"+',BankName=pBankName'
  END IF;
  IF "isnull"("pBankSortCode",'') <> "isnull"("poldBankSortCode",'') THEN
    SET "ssql" = "ssql"+',BankSortCode=pBankSortCode'
  END IF;
  IF "isnull"("pBankAcName",'') <> "isnull"("poldBankAcName",'') THEN
    SET "ssql" = "ssql"+',BankAcName=pBankAcName'
  END IF;
  IF "isnull"("pBankAcNo",'') <> "isnull"("poldBankAcNo",'') THEN
    SET "ssql" = "ssql"+',BankAcNo=pBankAcNo'
  END IF;
  IF "isnull"("pUniqueTaxReference",'') <> "isnull"("poldUniqueTaxReference",'') THEN
    SET "ssql" = "ssql"+',UniqueTaxReference=pUniqueTaxReference'
  END IF;
  IF "ssql" <> '' THEN
    EXECUTE IMMEDIATE 'update Pay_Employee set '+"stuff"("ssql",1,1,'')+' where personid=ppersonid'
  END IF;
  CALL "personrecordupdated"("ppersonid");
  SELECT '0:~Success'
END
 
/* DOC
2018-05-07 PC IW-503 hide bank account number except for last two digits
2018-08-27 PC improve error handling
 
*/
}