====== pears.NetCandidateBankDetailsSet ======
<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"."NetCandidateBankDetailsSet"( in "pWebUserID" char(20),in "pBankName" char(30) default null,in "pBankSortCode" char(15) default null,in "pBankAcName" char(50) default null,in "pBankAcNo" char(15) default null,in "pBankRef" char(20) default null,in "pni" char(20) default null,in "pTaxMethod" integer default null,in "pHideDetails" integer default null,in "pPersonID" char(20) default null ) 
result( "pResult" char(250) ) 
// IQXWeb
// Some parameters are only there because we are saving clean fields
begin
  declare "ssql" char(255);
  declare "dBankName" char(50);
  declare "dBankSortCode" char(20);
  declare "dBankAcName" char(60);
  declare "dBankAcNo" char(20);
  declare "dBankRef" char(20);
  declare "dni" char(20);
  declare "DefaultTaxMethod" char(1);
  set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  if "pPersonID" is null then
    select '97:~Unique candidate link required';
    return
  end if;
  if "IQXNetHasPermission"("pWebUserID",'CANDBANKDETAILSEDIT') = 0 then
    select '1:~Permission denied';
    return
  end if;
  if not exists(select * from "Pay_Employee" where "Personid" = "pPersonID") then
    set "DefaultTaxMethod" = "WPKMaintainGetSwitchValue"('DEFTAXMETHOD','Global','L');
    if "trim"("isnull"("DefaultTaxMethod",'')) = '' then
      set "DefaultTaxMethod" = '1'
    end if;
    insert into "pay_employee"( "personid","taxmethod" ) values( "ppersonid","DefaultTaxMethod" ) 
  end if;
  select "BankName","BankSortCode","BankAcName","BankAcNo","BankRef" into "dBankName","dBankSortCode","dBankAcName","dBankAcNo","dBankRef" from "Pay_Employee" where "Personid" = "pPersonID";
  select "ni" into "dni" from "person" where "Personid" = "pPersonID";
  set "ssql" = '';
  if "isnull"("pBankSortCode",'') <> '' then
    if "left"("pBankAcNo",1) <> '*' then
      if "isnull"("pBankSortCode",'') <> "isnull"("dBankSortCode",'') then
        set "ssql" = "ssql"+',BankSortCode=pBankSortCode'
      end if;
      if "isnull"("pBankAcNo",'') <> "isnull"("dBankAcNo",'') then
        set "ssql" = "ssql"+',BankAcNo=pBankAcNo'
      end if end if;
    if "isnull"("pBankName",'') <> "isnull"("dBankName",'') then
      set "ssql" = "ssql"+',BankName=pBankName'
    end if;
    if "isnull"("pBankAcName",'') <> "isnull"("dBankAcName",'') then
      set "ssql" = "ssql"+',BankAcName=pBankAcName'
    end if;
    if "isnull"("pBankRef",'') <> "isnull"("dBankRef",'') then
      set "ssql" = "ssql"+',BankRef=pBankRef'
    end if;
    set "ssql" = "ssql"+',PayMethod=1' -- if bank details present then set pay method to bacs
  end if;
  if "ssql" <> '' then
    execute immediate 'update Pay_Employee set '+"stuff"("ssql",1,1,'')+' where personid=pPersonID'
  end if;
  if "isnull"("pni",'') <> "isnull"("dni",'') then
    select "replace"("pni",' ','') into "dni";
    execute immediate 'update person set ni=dni where personid=pPersonID'
  end if;
  call "personrecordupdated"("pPersonID");
  select '0:~Success'
end /* DOC

2016-10-12 PC create, edit or delete candidate bank details
2017-01-17 PC dummy pHideDetails parameter added to match NetCandidateBankDetails
2017-09-26 PC modify to handle pay details
2017-11-13 PC modified test routine to avoid duplicate insert
2018-01-18 PC look up default pay method and default to bacs when bank details are entered. 
2018-05-07 PC IW-503 hide bank account number showing only last two digits, if bank account unchanged then don't edit account details
2018-06-18 PC modify test routine
2018-07-24 PC include improved error handler
2018-10-10 MHS IW-456 multi-cand fix and tests
2019-02-20 PC IW-786 modify payment method so that if bank details are present use bacs, otherwise use default
2019-03-05 PC IW-873 add top 1 and order by on subqueries
2019-03-05 PC IW-873 revised
2019-4-9 MHS strip out rogue tax code rubbish

*/
/* TEST
call NetTestSetup('');
delete from iqxnetrightassigned where iqxnetuserid = 'test.candidate';
delete from pay_employee where personid='TEST';
select * from NetCandidateBankDetailsSet(null,null,null,null,null,null);
Expect 97*;
update person set status='C' where personid='TEST';
insert into iqxnetrightassigned  (iqxnetuserid,iqxnetrightid,assigned)on existing update defaults off values('test.candidate','CANDBANKDETAILSEDIT',1);
select * from NetCandidateBankDetailsSet('test.candidate',null,null,null,null,null);
Expect 0*;
insert into iqxnetrightassigned  (iqxnetuserid,iqxnetrightid,assigned) on existing update defaults off values('test.candidate','CANDBANKDETAILSEDIT',1);
select * from NetCandidateBankDetailsSet('test.candidate',null,null,null,null,null);
Expect 0*;
select count(*) as LineCount from pay_employee where personid='TEST';
Expect LineCount=1;
select * from NetCandidateBankDetailsSet('test.candidate','SmithsBank','99-99-99',null,null,null);
Expect 0*;
select BankName from pay_employee where personid='TEST';
expect BankName=SmithsBank;
select * from NetCandidateBankDetailsSet('test.candidate','SmithsBank','99-99-99','TestName','12345678','RefNo');
Expect 0*;
select * from pay_employee where personid='TEST';
expect BankName=SmithsBank,BankSortCode=99-99-99,BankAcName=TestName,BankAcNo=12345678,BankRef=RefNo;
select * from NetCandidateBankDetailsSet('test.candidate',null,'99-99-99','TestName','12345678','RefNo');
Expect 0*;
select isnull(BankName,'!') as A,isnull(BankSortCode,'!') as B,isnull(BankAcName,'!') as C,isnull(BankAcNo,'!') as D,isnull(BankRef,'!') as E from pay_employee where personid='TEST';
expect A=!,B=99-99-99,C=TestName,D=12345678,E=RefNo;
select * from NetCandidateBankDetailsSet('test.candidate',null,null,null,null,null);
Expect 0*;
select isnull(BankName,'!') as A,isnull(BankSortCode,'!') as B,isnull(BankAcName,'!') as C,isnull(BankAcNo,'!') as D,isnull(BankRef,'!') as E from pay_employee where personid='TEST';
expect A=!,B=99-99-99,C=TestName,D=12345678,E=RefNo;
select (if dateformat(changedate,'dd/mm/yyyy!hh:nn:ss') = dateformat(now(*),'dd/mm/yyyy!hh:nn:ss') then 1 else 0 endif) as IsNow from person where personid='TEST';
expect IsNow=1;
delete from iqxnetrightassigned where iqxnetuserid = 'test.candidate';


*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateBankDetailsSet" IS 
{create PROCEDURE pears."NetCandidateBankDetailsSet"( in "pWebUserID" char(20),in "pBankName" char(30) default null,in "pBankSortCode" char(15) default null,in "pBankAcName" char(50) default null,in "pBankAcNo" char(15) default null,in "pBankRef" char(20) default null, in pni char(20) default null, in pTaxMethod integer default null, in pHideDetails integer default null, in pPersonID char(20) default null)
result (pResult char(250))
// IQXWeb
// Some parameters are only there because we are saving clean fields
BEGIN
    declare "ssql" char(255);
    declare dBankName char(50);
    declare dBankSortCode char(20);
    declare dBankAcName char(60);
    declare dBankAcNo char(20);
    declare dBankRef char(20);
    declare dni char(20);
    declare DefaultTaxMethod char(1);

    set pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
    if pPersonID is null then
      select '97:~Unique candidate link required';
      return;
    end if;
    
    if "IQXNetHasPermission"("pWebUserID",'CANDBANKDETAILSEDIT') = 0 then
        select '1:~Permission denied';
        return;
    end if;

  	if not exists(select * from "Pay_Employee" where "Personid" = "pPersonID") then
      set DefaultTaxMethod=WPKMaintainGetSwitchValue('DEFTAXMETHOD','Global','L');
      if trim(isnull(DefaultTaxMethod,''))='' then
        set DefaultTaxMethod='1';
      end if;
      insert into pay_employee(personid, taxmethod ) values (ppersonid, DefaultTaxMethod); 
    end if;

    select BankName,BankSortCode,BankAcName,BankAcNo,BankRef into dBankName,dBankSortCode,dBankAcName,dBankAcNo,dBankRef from "Pay_Employee" where "Personid" = "pPersonID";
    select ni into dni from person where "Personid" = "pPersonID";

    set "ssql" = '';
    if "isnull"("pBankSortCode",'') != '' then
        if left(pBankAcNo,1) !='*' then
            if "isnull"("pBankSortCode",'') <> "isnull"("dBankSortCode",'') then
                set "ssql" = "ssql"+',BankSortCode=pBankSortCode'
            end if;
            if "isnull"("pBankAcNo",'') <> "isnull"("dBankAcNo",'') then
                set "ssql" = "ssql"+',BankAcNo=pBankAcNo'
            end if;
        end if;
        if "isnull"("pBankName",'') <> "isnull"("dBankName",'') then
            set "ssql" = "ssql"+',BankName=pBankName'
        end if;
        if "isnull"("pBankAcName",'') <> "isnull"("dBankAcName",'') then
            set "ssql" = "ssql"+',BankAcName=pBankAcName'
        end if;
        if "isnull"("pBankRef",'') <> "isnull"("dBankRef",'') then
            set "ssql" = "ssql"+',BankRef=pBankRef'
        end if;
        set "ssql" = "ssql"+',PayMethod=1' -- if bank details present then set pay method to bacs
    end if;
      
    if "ssql" <> '' then
        execute immediate 'update Pay_Employee set '+"stuff"("ssql",1,1,'')+' where personid=pPersonID'
    end if;
    if "isnull"("pni",'') <> "isnull"("dni",'') then
        select replace (pni,' ','') into dni;
        execute immediate 'update person set ni=dni where personid=pPersonID'
    end if;
    call "personrecordupdated"("pPersonID");
    select '0:~Success'
END

/* DOC

2016-10-12 PC create, edit or delete candidate bank details
2017-01-17 PC dummy pHideDetails parameter added to match NetCandidateBankDetails
2017-09-26 PC modify to handle pay details
2017-11-13 PC modified test routine to avoid duplicate insert
2018-01-18 PC look up default pay method and default to bacs when bank details are entered. 
2018-05-07 PC IW-503 hide bank account number showing only last two digits, if bank account unchanged then don't edit account details
2018-06-18 PC modify test routine
2018-07-24 PC include improved error handler
2018-10-10 MHS IW-456 multi-cand fix and tests
2019-02-20 PC IW-786 modify payment method so that if bank details are present use bacs, otherwise use default
2019-03-05 PC IW-873 add top 1 and order by on subqueries
2019-03-05 PC IW-873 revised
2019-4-9 MHS strip out rogue tax code rubbish

*/

/* TEST
call NetTestSetup('');
delete from iqxnetrightassigned where iqxnetuserid = 'test.candidate';
delete from pay_employee where personid='TEST';
select * from NetCandidateBankDetailsSet(null,null,null,null,null,null);
Expect 97*;
update person set status='C' where personid='TEST';
insert into iqxnetrightassigned  (iqxnetuserid,iqxnetrightid,assigned)on existing update defaults off values('test.candidate','CANDBANKDETAILSEDIT',1);
select * from NetCandidateBankDetailsSet('test.candidate',null,null,null,null,null);
Expect 0*;
insert into iqxnetrightassigned  (iqxnetuserid,iqxnetrightid,assigned) on existing update defaults off values('test.candidate','CANDBANKDETAILSEDIT',1);
select * from NetCandidateBankDetailsSet('test.candidate',null,null,null,null,null);
Expect 0*;
select count(*) as LineCount from pay_employee where personid='TEST';
Expect LineCount=1;
select * from NetCandidateBankDetailsSet('test.candidate','SmithsBank','99-99-99',null,null,null);
Expect 0*;
select BankName from pay_employee where personid='TEST';
expect BankName=SmithsBank;
select * from NetCandidateBankDetailsSet('test.candidate','SmithsBank','99-99-99','TestName','12345678','RefNo');
Expect 0*;
select * from pay_employee where personid='TEST';
expect BankName=SmithsBank,BankSortCode=99-99-99,BankAcName=TestName,BankAcNo=12345678,BankRef=RefNo;
select * from NetCandidateBankDetailsSet('test.candidate',null,'99-99-99','TestName','12345678','RefNo');
Expect 0*;
select isnull(BankName,'!') as A,isnull(BankSortCode,'!') as B,isnull(BankAcName,'!') as C,isnull(BankAcNo,'!') as D,isnull(BankRef,'!') as E from pay_employee where personid='TEST';
expect A=!,B=99-99-99,C=TestName,D=12345678,E=RefNo;
select * from NetCandidateBankDetailsSet('test.candidate',null,null,null,null,null);
Expect 0*;
select isnull(BankName,'!') as A,isnull(BankSortCode,'!') as B,isnull(BankAcName,'!') as C,isnull(BankAcNo,'!') as D,isnull(BankRef,'!') as E from pay_employee where personid='TEST';
expect A=!,B=99-99-99,C=TestName,D=12345678,E=RefNo;
select (if dateformat(changedate,'dd/mm/yyyy!hh:nn:ss') = dateformat(now(*),'dd/mm/yyyy!hh:nn:ss') then 1 else 0 endif) as IsNow from person where personid='TEST';
expect IsNow=1;
delete from iqxnetrightassigned where iqxnetuserid = 'test.candidate';


*/
}
</code>