pears.NetTimesheetApprove

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*;
 
*/
}