pears.NetCandidateBankDetails

CREATE PROCEDURE "pears"."NetCandidateBankDetails"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "BankName" CHAR(30),"BankSortCode" CHAR(8),"BankAcName" CHAR(50),"BankAcNo" CHAR(8),"BankRef" CHAR(20),"ni" CHAR(20),"TaxMethod" INTEGER,"HideDetails" INTEGER ) 
// IQXWeb
BEGIN
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "IQXNetHasPermission"("pWebUserID",'CANDBANKDETAILSVIEW') = 0 THEN
    RETURN
  END IF;
  SELECT "e"."BankName",
    "e"."BankSortCode",
    "e"."BankAcName",
    "string"("replicate"('*',"length"("e"."bankacno")-2),"right"("e"."bankacno",2)),
    "e"."BankRef",
    "ni",
    "e"."taxmethod",
    (IF "e"."TaxMethod" = 1 THEN 0 ELSE 1 endif) AS "HideDetails"
    FROM "person" KEY LEFT OUTER JOIN "Pay_Employee" AS "e"
    WHERE "person"."personid" = "pPersonID"
END /* DOC
 
2016-10-12 PC procedure to return bank details for candidate
2017-01-17 PC provide flag to hide all details other than paye, to avoid candidates being able to edit payment agencies details.
2017-09-26 PC include tax details
2017-10-24 PC alter size of tax parameters
2018-04-24 PC add column to hide tax type tab
2018-05-06 PC IW-503 only display last two digits of bank code
2018-10-10 MHS IW-456 multi-cand fix and tests
2019-4-9 MHS strip out rogue tax code rubbish
 
Tests:
Valid pWebUserID without read permission
Valid pWebUserID with read permission
 
Flag names:
CandBankDetailsView
 
*/
/* TEST
NetTestSetup('');
insert into pay_employee (Personid,bankname,banksortcode,BankAcName,BankAcNo,BankRef,secondaryagencyid) on existing update defaults off values ('TEST','TestBank','99-99-99','TestTest','09988776','TestRef','TEST');
insert into iqxnetright (IQXNetRightID,Description,sortorder) on existing update defaults off values ('CANDBANKDETAILSVIEW','View candidate bank details',998);
insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) on existing update defaults off values ('CANDBANKDETAILSVIEW','CANDIDATE','*',1);
select * from NetCandidateBankDetails(null);
expect EOF;
select * from NetCandidateBankDetails(null);
expect EOF;
insert into iqxnetrightassigned (iqxnetuserid,iqxnetrightid,assigned) on existing update defaults off values ('test.candidate','candbankdetailsview',1);
select * from "NetCandidateBankDetails"("pWebUserID" = 'test.candidate');
expect BankName=TestBank,BankSortCode=99-99-99,BankAcName=TestTest,BankAcNo=******76,BankRef=TestRef;
delete from iqxnetrightassigned where iqxnetuserid='test.candidate' and iqxnetrightid='candbankdetailsview';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateBankDetails" IS 
{CREATE PROCEDURE pears."NetCandidateBankDetails"( IN "pWebUserID" CHAR(20), IN pPersonID CHAR(20) DEFAULT NULL ) 
RESULT("BankName" CHAR(30),"BankSortCode" CHAR(8),"BankAcName" CHAR(50),"BankAcNo" CHAR(8),"BankRef" CHAR(20), ni CHAR(20),TaxMethod INTEGER, "HideDetails" INTEGER) 
// IQXWeb
BEGIN
    SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
    IF "IQXNetHasPermission"("pWebUserID",'CANDBANKDETAILSVIEW') = 0 THEN
        RETURN;
    END IF;
    SELECT 
        e."BankName",
        e."BankSortCode",
        e."BankAcName",
        string(replicate('*',LENGTH(e.bankacno)-2),RIGHT(e.bankacno,2)),
        e.BankRef,
        ni,
        e.taxmethod,
        (IF e.TaxMethod = 1 THEN 0 ELSE 1 endif) AS HideDetails
    FROM 
        person KEY LEFT OUTER JOIN "Pay_Employee" AS e
    WHERE 
        person.personid=pPersonID
END
 
/* DOC
 
2016-10-12 PC procedure to return bank details for candidate
2017-01-17 PC provide flag to hide all details other than paye, to avoid candidates being able to edit payment agencies details.
2017-09-26 PC include tax details
2017-10-24 PC alter size of tax parameters
2018-04-24 PC add column to hide tax type tab
2018-05-06 PC IW-503 only display last two digits of bank code
2018-10-10 MHS IW-456 multi-cand fix and tests
2019-4-9 MHS strip out rogue tax code rubbish
 
Tests:
	Valid pWebUserID without read permission
	Valid pWebUserID with read permission
 
Flag names:
	CandBankDetailsView
 
*/
 
/* TEST
NetTestSetup('');
insert into pay_employee (Personid,bankname,banksortcode,BankAcName,BankAcNo,BankRef,secondaryagencyid) on existing update defaults off values ('TEST','TestBank','99-99-99','TestTest','09988776','TestRef','TEST');
insert into iqxnetright (IQXNetRightID,Description,sortorder) on existing update defaults off values ('CANDBANKDETAILSVIEW','View candidate bank details',998);
insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) on existing update defaults off values ('CANDBANKDETAILSVIEW','CANDIDATE','*',1);
select * from NetCandidateBankDetails(null);
expect EOF;
select * from NetCandidateBankDetails(null);
expect EOF;
insert into iqxnetrightassigned (iqxnetuserid,iqxnetrightid,assigned) on existing update defaults off values ('test.candidate','candbankdetailsview',1);
select * from "NetCandidateBankDetails"("pWebUserID" = 'test.candidate');
expect BankName=TestBank,BankSortCode=99-99-99,BankAcName=TestTest,BankAcNo=******76,BankRef=TestRef;
delete from iqxnetrightassigned where iqxnetuserid='test.candidate' and iqxnetrightid='candbankdetailsview';
*/
}