pears.NetProvTimesheetRateSet

CREATE PROCEDURE "pears"."NetProvTimesheetRateSet"( IN "pWebUserID" CHAR(20),IN "pTempProvTimesheetLineID" CHAR(250),IN "pUnitDescription" CHAR(250) DEFAULT NULL,IN "pUnits" CHAR(250) DEFAULT NULL,IN "pRate" CHAR(250) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "i" SMALLINT;
  DECLARE "isExp" SMALLINT;
  DECLARE "isnewline" SMALLINT;
  DECLARE "newlineid" CHAR(20);
  DECLARE "ptempprovtimesheetid" CHAR(20);
  DECLARE "userClass" CHAR(20);
  DECLARE "divid" CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  IF "pTempProvTimesheetLineID" LIKE 'Copy%' THEN
    SET "isnewline" = 1
  ELSE
    SET "isnewline" = 0
  END IF;
  SET "i" = "charindex"('_',"pTempProvTimesheetLineID");
  IF "i" > 0 THEN
    SET "pTempProvTimesheetLineID" = "right"("pTempProvTimesheetLineID","length"("pTempProvTimesheetLineID")-"i")
  END IF;
  SET "ptempprovtimesheetid" = (SELECT FIRST "tempprovtimesheetid" FROM "tempprovtimesheetline" WHERE "tempprovtimesheetlineid" = "pTempProvTimesheetLineID");
  SELECT(IF "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' OR "isnull"("temppayband"."isexpenses",0) = 1 THEN 1 ELSE 0 endif)
    INTO "isExp" FROM "tempprovtimesheetline" KEY JOIN "temppayband"
    WHERE "tempprovtimesheetline"."tempprovtimesheetlineid" = "ptempprovtimesheetlineid";
  IF "isExp" = 0 THEN -- Can only edit rate if expenses
    SET "pRate" = NULL
  END IF;
  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 "isnewline" = 1 THEN
    SET "newlineid" = "uniquekey"("pTempProvTimesheetLineID");
    INSERT INTO "tempprovtimesheetline"( "tempprovtimesheetlineid","tempprovtimesheetid","temppaybandid","linenumber","unitspaid","unitscharged","payrate","chargerate",
      "description" ) SELECT "newlineid","ptempprovtimesheetid","temppaybandid",
        "isnull"((SELECT 1+"max"("linenumber") FROM "tempprovtimesheetline" WHERE "tempprovtimesheetid" = "ptempprovtimesheetid"),1),
        "isnull"("punits","unitspaid"),"isnull"("punits","unitscharged"),"isnull"("prate","payrate"),"isnull"("prate","chargerate"),
        "isnull"("punitdescription","description") FROM "tempprovtimesheetline" WHERE "tempprovtimesheetlineid" = "ptempprovtimesheetlineid"
  ELSE
    UPDATE "tempprovtimesheetline" SET "unitspaid" = "isnull"("punits","unitspaid"),"unitscharged" = "isnull"("punits","unitscharged"),"payrate" = "isnull"("prate","payrate"),"chargerate" = "isnull"("prate","chargerate"),
      "description" = "isnull"("punitdescription","description") WHERE "tempprovtimesheetlineid" = "pTempProvTimesheetLineID"
  END IF;
  SELECT '0:~Success'
END /* DOC
2016-09-01 PC doc and test
 
inputs:
pWebUserID
pTempProvTimesheetLineID
pUnitDescription
pUnits
pRate
 
outputs:
pResult
 
test:
edit a timesheet that owner does not own
edit an existing non-expense line that an owner owns
edit an existing expense line
edit an existing line that an client owns
duplicate a line
 
*/
/* TEST
call NetTestSetup('');
update staff set divisionid='TEST' where staffid='TEST';
update person set divisionid='TEST' where personid='TEST';
select * from NetProvTimesheetRateSet('test.owner','test1','zz',8,23);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test1';
expect description=zz,unitspaid=8,unitscharged=8,payrate=10,chargerate=11;
select * from NetProvTimesheetRateSet('test.owner','test2','zz2',9,23);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test2';
expect description=zz2,unitspaid=9,unitscharged=9,payrate=23,chargerate=23;
select * from NetProvTimesheetRateSet('test.client','test1','zz3',7,17);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test1';
expect description=zz3,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
select * from NetProvTimesheetRateSet('test.client','copy_test1','zz4',null,null);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where description like 'zz%' order by linenumber;
expect description=zz3,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
expect description=zz2,unitspaid=9,unitscharged=9,payrate=23,chargerate=23;
expect description=zz4,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
expect EOF;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetRateSet" IS 
{CREATE PROCEDURE pears."NetProvTimesheetRateSet"(IN pWebUserID CHAR(20),IN pTempProvTimesheetLineID CHAR(250),IN pUnitDescription CHAR(250) DEFAULT NULL,IN pUnits CHAR(250) DEFAULT NULL,IN pRate CHAR(250) DEFAULT NULL)
RESULT(pResult CHAR(250))
// IQXWeb
BEGIN
  DECLARE i SMALLINT;
  DECLARE isExp SMALLINT;
  DECLARE isnewline SMALLINT;
  DECLARE newlineid CHAR(20);
  DECLARE ptempprovtimesheetid CHAR(20);
  DECLARE userClass CHAR(20);
  DECLARE divid CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NULL,
    ) NOT transactional;
  IF pTempProvTimesheetLineID LIKE 'Copy%' THEN
    SET isnewline=1
  ELSE
    SET isnewline=0
  END IF;
  SET i=charindex('_',pTempProvTimesheetLineID);
  IF i > 0 THEN
    SET pTempProvTimesheetLineID="right"(pTempProvTimesheetLineID,LENGTH(pTempProvTimesheetLineID)-i)
  END IF;
  SET ptempprovtimesheetid=(SELECT FIRST tempprovtimesheetid FROM tempprovtimesheetline WHERE tempprovtimesheetlineid = pTempProvTimesheetLineID);
 
  SELECT (IF isnull(temppayband.payrollflag,'') = 'EXPENSES' OR isnull(temppayband.isexpenses,0) = 1 THEN 1 ELSE 0 endif) 
  INTO isExp FROM tempprovtimesheetline KEY JOIN temppayband 
  WHERE tempprovtimesheetline.tempprovtimesheetlineid = ptempprovtimesheetlineid;  
  IF isExp=0 THEN  -- Can only edit rate if expenses
    SET pRate=NULL;
  END IF;
 
  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 isnewline = 1 THEN
    SET newlineid=uniquekey(pTempProvTimesheetLineID);
    INSERT INTO tempprovtimesheetline( tempprovtimesheetlineid,tempprovtimesheetid,temppaybandid,linenumber,unitspaid,unitscharged,payrate,chargerate,
      description) SELECT newlineid,ptempprovtimesheetid,temppaybandid,
        isnull((SELECT 1+MAX(linenumber) FROM tempprovtimesheetline WHERE tempprovtimesheetid = ptempprovtimesheetid),1),
        isnull(punits,unitspaid),isnull(punits,unitscharged),isnull(prate,payrate),isnull(prate,chargerate),
        isnull(punitdescription,description) FROM tempprovtimesheetline WHERE tempprovtimesheetlineid = ptempprovtimesheetlineid
  ELSE
    UPDATE tempprovtimesheetline SET unitspaid = isnull(punits,unitspaid),unitscharged = isnull(punits,unitscharged),payrate = isnull(prate,payrate),chargerate = isnull(prate,chargerate),
      description = isnull(punitdescription,description) WHERE tempprovtimesheetlineid = pTempProvTimesheetLineID
  END IF;
  SELECT '0:~Success'
END
 
/* DOC
2016-09-01 PC doc and test
 
inputs:
    pWebUserID
    pTempProvTimesheetLineID
    pUnitDescription
    pUnits
    pRate
 
outputs:
    pResult
 
test:
    edit a timesheet that owner does not own
    edit an existing non-expense line that an owner owns
    edit an existing expense line
    edit an existing line that an client owns
    duplicate a line
 
*/
 
/* TEST
call NetTestSetup('');
update staff set divisionid='TEST' where staffid='TEST';
update person set divisionid='TEST' where personid='TEST';
select * from NetProvTimesheetRateSet('test.owner','test1','zz',8,23);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test1';
expect description=zz,unitspaid=8,unitscharged=8,payrate=10,chargerate=11;
select * from NetProvTimesheetRateSet('test.owner','test2','zz2',9,23);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test2';
expect description=zz2,unitspaid=9,unitscharged=9,payrate=23,chargerate=23;
select * from NetProvTimesheetRateSet('test.client','test1','zz3',7,17);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test1';
expect description=zz3,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
select * from NetProvTimesheetRateSet('test.client','copy_test1','zz4',null,null);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where description like 'zz%' order by linenumber;
expect description=zz3,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
expect description=zz2,unitspaid=9,unitscharged=9,payrate=23,chargerate=23;
expect description=zz4,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
expect EOF;
*/
}