====== pears.NetCandidateDiaryActionSave ======
<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"."NetCandidateDiaryActionSave"( in "pWebUserID" char(20),in "pActions" long varchar ) 
result( "pResult" char(250) ) 
begin
  declare "i" smallint;
  declare "s" char(100);
  declare "sAction" char(20);
  set "pActions" = "trim"("isnull"("pActions",''));
  "looplabel": loop
    if "pActions" = '' then
      leave "looplabel"
    end if;
    set "i" = "charindex"(',',"pActions");
    if "i" = 0 then
      set "s" = "pActions";
      set "pActions" = ''
    else
      set "s" = "trim"("left"("pActions","i"-1));
      set "pActions" = "trim"("right"("pActions","length"("pActions")-"i"))
    end if;
    if "s" like 'Shift%' then
      set "s" = "right"("s","length"("s")-6);
      set "i" = "charindex"('=',"s");
      set "sAction" = "right"("s","length"("s")-"i");
      set "s" = "left"("s","i"-1);
      if "saction" = 'Delete' then
        delete from "tempshift" where "tempshiftid" = "s" and "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID") and "state" in( 'A','U','H' ) 
      else if "saction" = 'Confirm' then
          update "tempshift" set "tempconfirmed" = 1 where "tempshiftid" = "s" and "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID") and "state" in( 'P','C' ) ;
          call "IQXNetRequestAction"("pWebUserID",'CandidateConfirm','SHIFT',"s")
        else if "saction" = 'Reject' then
            update "tempshift" set "state" = 'C',"whencancelled" = current timestamp,"whocancelled" = "userstaffid","crefill" = 1,"clientconfirmed" = 0,"tempconfirmed" = 1,"cancelreason" = 'T'
              where "tempshiftid" = "s" and "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID") and "state" = 'P';
            call "IQXNetRequestAction"("pWebUserID",'CandidateReject','SHIFT',"s")
          else if "saction" = 'Acknowledge' then
              update "tempshift" set "tempconfirmed" = 1
                where "tempshiftid" = "s" and "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID") and "state" = 'C'
            end if
          end if
        end if
      end if
    end if
  end loop "looplabel";
  select '0:~Success'
end /* DOC
2018-10-10 MHS IW-456 multi-cand fix. Appears not currently in use so won't do tests
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDiaryActionSave" IS 
{create procedure pears."NetCandidateDiaryActionSave"( in "pWebUserID" char(20),in "pActions" long varchar ) 
result( "pResult" char(250) ) 
begin
  declare "i" smallint;
  declare "s" char(100);
  declare "sAction" char(20);
  set "pActions" = "trim"("isnull"("pActions",''));
  "looplabel": loop
    if "pActions" = '' then
      leave "looplabel"
    end if;
    set "i" = "charindex"(',',"pActions");
    if "i" = 0 then
      set "s" = "pActions";
      set "pActions" = ''
    else
      set "s" = "trim"("left"("pActions","i"-1));
      set "pActions" = "trim"("right"("pActions","length"("pActions")-"i"))
    end if;
    if "s" like 'Shift%' then
      set "s" = "right"("s","length"("s")-6);
      set "i" = "charindex"('=',"s");
      set "sAction" = "right"("s","length"("s")-"i");
      set "s" = "left"("s","i"-1);
      if "saction" = 'Delete' then
        delete from "tempshift" where "tempshiftid" = "s" and "personid" in (select personid from iqxnetuserlink where iqxnetuserid=pWebUserID) and "state" in( 'A','U','H' ) 
      else if "saction" = 'Confirm' then
          update "tempshift" set "tempconfirmed" = 1 where "tempshiftid" = "s" and "personid" in (select personid from iqxnetuserlink where iqxnetuserid=pWebUserID) and "state" in( 'P','C' ) ;
          call "IQXNetRequestAction"("pWebUserID",'CandidateConfirm','SHIFT',"s")
        else if "saction" = 'Reject' then
            update "tempshift" set "state" = 'C',"whencancelled" = current timestamp,"whocancelled" = "userstaffid","crefill" = 1,"clientconfirmed" = 0,"tempconfirmed" = 1,"cancelreason" = 'T'
              where "tempshiftid" = "s" and "personid" in (select personid from iqxnetuserlink where iqxnetuserid=pWebUserID) and "state" = 'P';
            call "IQXNetRequestAction"("pWebUserID",'CandidateReject','SHIFT',"s")
          else if "saction" = 'Acknowledge' then
              update "tempshift" set "tempconfirmed" = 1
                where "tempshiftid" = "s" and "personid" in (select personid from iqxnetuserlink where iqxnetuserid=pWebUserID) and "state" = 'C'
            end if
          end if
        end if
      end if
    end if
  end loop "looplabel";
  select '0:~Success'
end

 /* DOC
2018-10-10 MHS IW-456 multi-cand fix. Appears not currently in use so won't do tests
*/
}
</code>