pears.NetTimesheetApprove [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetTimesheetApprove ======
<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"."NetTimesheetApprove"( in "pWebUserID" char(20),in "pTempTimesheetList" long varchar ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "userClass" char(20);
  declare "s" char(20);
  declare "i" integer;
  declare "sn" char(20);
  set "userClass" = (select first "iqxnetuserclassid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
  if "userClass" <> 'CLIENT' then -- Currently clients only. Will need OWNER at some point.
    select '99:~Permission denied';
    return
  end if;
  if "IQXNetHasPermission"("pWebUserID",'APPROVETIMESHEETS') = 0 then
    select '99:~Permission denied';
    return
  end if;
  "looplabel": loop
    set "i" = "charindex"(',',"pTempTimesheetList");
    if "i" = 0 then
      set "s" = "pTempTimesheetList"
    else
      set "s" = "left"("pTempTimesheetList","i"-1);
      set "pTempTimesheetList" = "right"("pTempTimesheetList","length"("pTempTimesheetList")-"i")
    end if;
    set "s" = "trim"("s");
    if "s" <> '' then
      update "temptimesheet" as "t" key join "placement" key join "vacancy" key join "employment" as "vacemp" key join "company" key join "employment" key join "iqxnetuserlink"
        set "t"."transferbatch" = 0,"t"."billed" = 0,"t"."paid" = 0,"t"."estatus" = 1 where "t"."temptimesheetid" = "s" and "t"."estatus" = 3
        and "iqxnetuserlink"."iqxnetuserid" = "pwebuserid";
      if @@rowcount = 1 then
        select "serialnumber" into "sn" from "temptimesheet" where "temptimesheetid" = "s";
        insert into "IQXNetAuditLog"( "IQXNetUserID","ActionType","Description","RecordID" ) values
          ( "pWebUserID",'TIMESHEETAPPROVE',"string"('Number ',"sn"),"sn" ) 
      end if end if;
    if "i" = 0 then
      leave "looplabel"
    end if
  end loop "looplabel";
  select '0:~Success'
end /* DOC
2017-06-22 PC Test and doc
2018-07-30 PC IW-513 Improve error handling
2019-7-25 MHS IW-783 put serialnumber in IQXNetAuditLog.RecordID to improve error tracking and fix tests
*/
/* TEST
call NetTestSetup('');
insert into temptimesheet (temptimesheetid,placementid,period,periodlength,serialnumber,tempdeskid,personid,estatus) on existing update defaults off values ('TEST','TEST','200101','W',99,'test','TEST',3);
select * from NetTimesheetApprove('test.client','test');
expect pResult=0*;

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetTimesheetApprove" IS 
{create PROCEDURE pears."NetTimesheetApprove"(in pWebUserID char(20),in pTempTimesheetList long varchar)
result(pResult char(250))
// IQXWeb
begin
  declare userClass char(20);
  declare s char(20);
  declare i integer;
  declare sn char(20);
  
  set userClass=(select first iqxnetuserclassid from iqxnetuser where iqxnetuserid = pWebUserID);
  if userClass <> 'CLIENT' then -- Currently clients only. Will need OWNER at some point.
    select '99:~Permission denied';
    return
  end if;
  if IQXNetHasPermission(pWebUserID,'APPROVETIMESHEETS') = 0 then
    select '99:~Permission denied';
    return
  end if;

  looplabel: loop
    set i=charindex(',',pTempTimesheetList);
    if i = 0 then
      set s=pTempTimesheetList
    else
      set s="left"(pTempTimesheetList,i-1);
      set pTempTimesheetList="right"(pTempTimesheetList,length(pTempTimesheetList)-i)
    end if;
    set s=trim(s);
    if s <> '' then
      update temptimesheet t key join placement key join vacancy key join employment as vacemp key join company key join employment key join iqxnetuserlink
      set t.transferbatch=0,t.billed=0,t.paid=0,t.estatus=1 where t.temptimesheetid=s and t.estatus=3
      and iqxnetuserlink.iqxnetuserid = pwebuserid;
      if @@rowcount = 1 then
        select serialnumber into sn from temptimesheet where temptimesheetid=s;
        insert into IQXNetAuditLog (IQXNetUserID, ActionType, Description, RecordID) 
          values (pWebUserID, 'TIMESHEETAPPROVE', string('Number ',sn), sn);
      end if;
    end if;
    if i = 0 then
      leave looplabel
    end if
  end loop looplabel;

  select '0:~Success'
end

/* DOC
2017-06-22 PC Test and doc
2018-07-30 PC IW-513 Improve error handling
2019-7-25 MHS IW-783 put serialnumber in IQXNetAuditLog.RecordID to improve error tracking and fix tests
*/

/* TEST
call NetTestSetup('');
insert into temptimesheet (temptimesheetid,placementid,period,periodlength,serialnumber,tempdeskid,personid,estatus) on existing update defaults off values ('TEST','TEST','200101','W',99,'test','TEST',3);
select * from NetTimesheetApprove('test.client','test');
expect pResult=0*;

*/
}
</code>