pears.NetProvTimesheetComplete

CREATE PROCEDURE "pears"."NetProvTimesheetComplete"( IN "pWebUserID" CHAR(20),IN "ptempprovtimesheetid" CHAR(20),IN "pInstruction" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "oldstat" SMALLINT;
  DECLARE "newstat" SMALLINT;
  DECLARE "statdesc" CHAR(30);
  DECLARE "userClass" CHAR(20);
  DECLARE "divid" CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "userClass" = 'OWNER' THEN
    INSERT INTO "IDs"( "ID" ) 
      SELECT DISTINCT "s"."divisionid" FROM "iqxnetuser" AS "i" KEY JOIN "staff" AS "s" WHERE "i"."iqxnetuserid" = "pwebuserid";
    IF NOT "ptempprovtimesheetid" = any(SELECT "t"."tempprovtimesheetid" FROM "tempprovtimesheet" AS "t" KEY JOIN "person" AS "p" JOIN "IDs" ON "p"."divisionid" = "IDs"."ID") THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF
  ELSE IF NOT "ptempprovtimesheetid" = any(SELECT "t"."tempprovtimesheetid"
        FROM "tempprovtimesheet" AS "t" KEY JOIN "person" KEY JOIN "pay_employee" KEY JOIN "company" AS "agcomp" KEY JOIN "employment" AS "agemp" KEY JOIN "iqxnetuserlink"
        WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" UNION
      SELECT "t"."tempprovtimesheetid"
        FROM "tempprovtimesheet" AS "t" KEY JOIN "person" KEY JOIN "iqxnetuserlink"
        WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" UNION
      SELECT "t"."tempprovtimesheetid" FROM "tempprovtimesheet" AS "t" KEY JOIN "vacancy" KEY JOIN "employment" AS "vacemp" KEY JOIN "company" KEY JOIN "employment" KEY JOIN "iqxnetuserlink"
        WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid") THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF END IF;
  IF "ucase"("isnull"("pInstruction",'')) = 'REVERSE' THEN
    SET "newstat" = 1;
    SET "oldstat" = 2;
    SET "statdesc" = 'Timesheet Un-filled'
  ELSE
    SET "newstat" = 2;
    SET "oldstat" = 1;
    SET "statdesc" = 'Timesheet Filled'
  END IF;
  UPDATE "tempprovtimesheet" SET "extnumber" = "newstat" WHERE "tempprovtimesheetid" = "ptempprovtimesheetid";
  IF @@rowcount = 1 THEN
    INSERT INTO "tempprovtimesheethistory"( "tempprovtimesheethistoryid","tempprovtimesheetid","serialnumber","description","oldstatus","newstatus","staffid","whenentered","externaluserid" ) 
      SELECT "uniquekey"("ptempprovtimesheetid"),"ptempprovtimesheetid","serialnumber","statdesc","oldstat","newstat","userstaffid",CURRENT TIMESTAMP,"pwebuserid" FROM "tempprovtimesheet" WHERE "tempprovtimesheetid" = "ptempprovtimesheetid";
    SELECT '0:~Success'
  ELSE
    SELECT '98:~Timesheet already finalised or deleted'
  END IF
END /* DOC
2016-08-31 PC test and doc
2017-11-14 LM inserted select extnumber from temprovtime where tempprovtimesheetid = 'test'; 
expect extnumber=1.
2017-11-14 LM changed the order by to "order by" desc to get the latest timesheet.
2018-07-25 PC include improved error handler
 
inputs:
pWebUserID
ptempprovtimesheetid
pInstruction
 
outputs:
pResult
 
tests:
invalid timesheet for an owner
timesheet does not belong to pWebUserID
null instruction
reverse timesheet
completed timesheet
*/
/* TEST
call NetTestSetup('');
delete from tempprovtimesheetline where tempprovtimesheetid = 'test';
delete from tempprovtimesheet where tempprovtimesheetid = 'test';
delete from tempprovtimesheethistory where tempprovtimesheetid = 'test';
insert into tempprovtimesheet(tempprovtimesheetid, period, periodlength, personid) on existing update defaults off values('test', string(dateformat(today(*),'yyyy'),'01'), 'W', 'test' );
insert into tempprovtimesheetline(tempprovtimesheetlineid, tempprovtimesheetid, TempPayBandID,) on existing update defaults off values('test', 'test', 'test1');
insert into tempprovtimesheethistory(tempprovtimesheethistoryid, tempprovtimesheetid, oldstatus, newstatus, description) on existing update defaults off values('test', 'test', 1, 2, 'Timesheet Filled');
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0:~Success;
update staff set divisionid='test' where staffid='TEST';
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0:~Success;
update person set divisionid='TEST' where personid='test';
select count (*) as rc from tempprovtimesheethistory where tempprovtimesheetid='test2';
expect rc=0;
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0*;
select oldstatus,newstatus,replace(description,' ','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test';
expect oldstatus=1,newstatus=2,Descrip=Timesheet!Filled;
select oldstatus,newstatus,replace(description,' ','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test2';
expect EOF;
select * from netprovtimesheetcomplete('test.owner','test','REVERSE');
expect pResult=0*;
select extnumber from tempprovtimesheet where tempprovtimesheetid = 'test';
expect extnumber=1;
select oldstatus from tempprovtimesheethistory where tempprovtimesheetid = 'test' order by whenentered desc;
expect oldstatus=2;
select top 1 oldstatus,newstatus,replace(replace(description,' ','!'),'-','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test' order by whenentered desc;
expect oldstatus=2,newstatus=1,Descrip=Timesheet!Un!filled;
expect EOF;
delete from tempprovtimesheetline where tempprovtimesheetid = 'test';
delete from tempprovtimesheet where tempprovtimesheetid = 'test';
delete from tempprovtimesheethistory where tempprovtimesheetid = 'test';
 
 
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetComplete" IS 
{CREATE PROCEDURE pears."NetProvTimesheetComplete"(IN pWebUserID CHAR(20),IN ptempprovtimesheetid CHAR(20),IN pInstruction CHAR(20) DEFAULT NULL)
RESULT(pResult CHAR(250))
// IQXWeb
BEGIN
  DECLARE oldstat SMALLINT;
  DECLARE newstat SMALLINT;
  DECLARE statdesc CHAR(30);
  DECLARE userClass CHAR(20);
  DECLARE divid CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NULL,
    ) NOT transactional;
  SET userClass=(SELECT FIRST iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid = pWebUserID);
  IF userClass = 'OWNER' THEN
    INSERT INTO IDs( ID) 
      SELECT DISTINCT s.divisionid FROM iqxnetuser AS i KEY JOIN staff AS s WHERE i.iqxnetuserid = pwebuserid;
    IF NOT ptempprovtimesheetid = any(SELECT t.tempprovtimesheetid FROM tempprovtimesheet AS t KEY JOIN person AS p JOIN IDs ON p.divisionid = IDs.ID) THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF
  ELSE
    IF NOT ptempprovtimesheetid = any(SELECT t.tempprovtimesheetid
        FROM tempprovtimesheet AS t KEY JOIN person KEY JOIN pay_employee KEY JOIN company AS agcomp KEY JOIN employment AS agemp KEY JOIN iqxnetuserlink
        WHERE iqxnetuserlink.iqxnetuserid = pwebuserid UNION
      SELECT t.tempprovtimesheetid
        FROM tempprovtimesheet AS t KEY JOIN person KEY JOIN iqxnetuserlink
        WHERE iqxnetuserlink.iqxnetuserid = pwebuserid UNION
      SELECT t.tempprovtimesheetid FROM tempprovtimesheet AS t KEY JOIN vacancy KEY JOIN employment AS vacemp KEY JOIN company KEY JOIN employment KEY JOIN iqxnetuserlink
        WHERE iqxnetuserlink.iqxnetuserid = pwebuserid) THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF
  END IF;
 
  IF ucase(isnull(pInstruction,'')) = 'REVERSE' THEN
    SET newstat=1;
    SET oldstat=2;
    SET statdesc='Timesheet Un-filled'
  ELSE
    SET newstat=2;
    SET oldstat=1;
    SET statdesc='Timesheet Filled'
  END IF;
  UPDATE tempprovtimesheet SET extnumber = newstat WHERE tempprovtimesheetid = ptempprovtimesheetid;
  IF @@rowcount = 1 THEN
    INSERT INTO tempprovtimesheethistory( tempprovtimesheethistoryid,tempprovtimesheetid,serialnumber,description,oldstatus,newstatus,staffid,whenentered,externaluserid) 
      SELECT uniquekey(ptempprovtimesheetid),ptempprovtimesheetid,serialnumber,statdesc,oldstat,newstat,userstaffid,CURRENT TIMESTAMP,pwebuserid FROM tempprovtimesheet WHERE tempprovtimesheetid = ptempprovtimesheetid;
    SELECT '0:~Success'
  ELSE
    SELECT '98:~Timesheet already finalised or deleted'
  END IF;
END
 
/* DOC
2016-08-31 PC test and doc
2017-11-14 LM inserted select extnumber from temprovtime where tempprovtimesheetid = 'test'; 
expect extnumber=1.
2017-11-14 LM changed the order by to "order by" desc to get the latest timesheet.
2018-07-25 PC include improved error handler
 
inputs:
    pWebUserID
    ptempprovtimesheetid
    pInstruction
 
outputs:
    pResult
 
tests:
    invalid timesheet for an owner
    timesheet does not belong to pWebUserID
    null instruction
    reverse timesheet
    completed timesheet
*/
 
/* TEST
call NetTestSetup('');
delete from tempprovtimesheetline where tempprovtimesheetid = 'test';
delete from tempprovtimesheet where tempprovtimesheetid = 'test';
delete from tempprovtimesheethistory where tempprovtimesheetid = 'test';
insert into tempprovtimesheet(tempprovtimesheetid, period, periodlength, personid) on existing update defaults off values('test', string(dateformat(today(*),'yyyy'),'01'), 'W', 'test' );
insert into tempprovtimesheetline(tempprovtimesheetlineid, tempprovtimesheetid, TempPayBandID,) on existing update defaults off values('test', 'test', 'test1');
insert into tempprovtimesheethistory(tempprovtimesheethistoryid, tempprovtimesheetid, oldstatus, newstatus, description) on existing update defaults off values('test', 'test', 1, 2, 'Timesheet Filled');
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0:~Success;
update staff set divisionid='test' where staffid='TEST';
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0:~Success;
update person set divisionid='TEST' where personid='test';
select count (*) as rc from tempprovtimesheethistory where tempprovtimesheetid='test2';
expect rc=0;
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0*;
select oldstatus,newstatus,replace(description,' ','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test';
expect oldstatus=1,newstatus=2,Descrip=Timesheet!Filled;
select oldstatus,newstatus,replace(description,' ','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test2';
expect EOF;
select * from netprovtimesheetcomplete('test.owner','test','REVERSE');
expect pResult=0*;
select extnumber from tempprovtimesheet where tempprovtimesheetid = 'test';
expect extnumber=1;
select oldstatus from tempprovtimesheethistory where tempprovtimesheetid = 'test' order by whenentered desc;
expect oldstatus=2;
select top 1 oldstatus,newstatus,replace(replace(description,' ','!'),'-','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test' order by whenentered desc;
expect oldstatus=2,newstatus=1,Descrip=Timesheet!Un!filled;
expect EOF;
delete from tempprovtimesheetline where tempprovtimesheetid = 'test';
delete from tempprovtimesheet where tempprovtimesheetid = 'test';
delete from tempprovtimesheethistory where tempprovtimesheetid = 'test';
 
 
 
*/
}