====== pears.NetCandidateBankDetails ======
<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"."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';
*/
}
</code>