pears.NetProvTimesheetTimeSet

CREATE PROCEDURE "pears"."NetProvTimesheetTimeSet"( IN "pWebUserID" CHAR(20),IN "pplacementid_date" CHAR(250),IN "phours" CHAR(250) DEFAULT NULL,IN "ptimefrom" CHAR(250) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "i" SMALLINT;
  DECLARE "pplacementid" CHAR(20);
  DECLARE "pdate" DATE;
  DECLARE "pptime" TIME;
  DECLARE "pphours" DOUBLE;
  DECLARE "pcancelled" tinyint;
  DECLARE "userClass" CHAR(20);
  DECLARE "divid" CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  SET "i" = "locate"("pplacementid_date",'_',-1);
  IF "i" > 0 THEN
    SET "pdate" = "iqxnetstringtodate"("right"("pplacementid_date","length"("pplacementid_date")-"i"));
    SET "pplacementid" = "left"("pplacementid_date","i"-1)
  ELSE
    SELECT '101:~Invalid data';
    RETURN
  END IF;
  SET "pphours" = "nullif"("iqxnetstringtodouble"("phours"),0.0);
  IF "pphours" IS NULL THEN
    SET "pptime" = NULL;
    SET "pcancelled" = 1
  ELSE
    SET "pptime" = "iqxnetstringtotime"("ptimefrom");
    SET "pcancelled" = 0
  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 "pplacementid" = any(SELECT FIRST "pl"."placementid" FROM "placement" AS "pl" KEY JOIN "employment" KEY JOIN "person" AS "pe" JOIN "IDs" ON "pe"."divisionid" = "IDs"."ID") THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF
  ELSE IF NOT "pplacementid" = any(
      SELECT "p"."placementid" FROM "placement" AS "p" KEY JOIN "employment" KEY JOIN "person" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid" UNION
      SELECT "p"."placementid" FROM "placement" AS "p" KEY JOIN "employment" 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 "p"."placementid" FROM "placement" AS "p" 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;
  DELETE FROM "placementdayvariation" WHERE "placementid" = "pplacementid" AND "variationdate" = "pdate";
  INSERT INTO "placementdayvariation"( "placementid","variationdate","workstarttime","workhours","workcancelled" ) VALUES( "pplacementid","pdate","pptime","pphours","pcancelled" ) ;
  SELECT '0:~Success'
END /* DOC
2017-06-21 PC Test and doc
2018-07-26 PC include improved error handler
 
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
delete from placementdayvariation where placementid='TEST';
select * from NetProvTimesheetTimeSet('test.candidate','test',12,'06:00');
expect 101*;
select * from NetProvTimesheetTimeSet('test.owner','test_'+dateformat(today(*),'dd/mm/yyyy'),12,'06:00');
expect 99*;
select * from NetProvTimesheetTimeSet('test.candidate','test99_'+dateformat(today(*),'dd/mm/yyyy'),12,'06:00');
expect 99*;
select * from NetProvTimesheetTimeSet('test.candidate','test_'+dateformat(today(*),'dd/mm/yyyy'),12,'06:00');
select (if VariationDate=dateformat(today(*),'yyyy-mm-dd') then 1 else 0 endif) as A,WorkStartTime,WorkHours from placementdayvariation where placementid='test';
expect A=1,WorkStartTime=06:00*,WorkHours=12;
delete from placementdayvariation where placementid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetTimeSet" IS 
{CREATE PROCEDURE pears."NetProvTimesheetTimeSet"(IN pWebUserID CHAR(20),IN pplacementid_date CHAR(250),IN phours CHAR(250) DEFAULT NULL,IN ptimefrom CHAR(250) DEFAULT NULL)
RESULT(pResult CHAR(250))
// IQXWeb
BEGIN
  DECLARE i SMALLINT;
  DECLARE pplacementid CHAR(20);
  DECLARE pdate DATE;
  DECLARE pptime TIME;
  DECLARE pphours DOUBLE;
  DECLARE pcancelled tinyint;
  DECLARE userClass CHAR(20);
  DECLARE divid CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NULL,
    ) NOT transactional;
  SET i=locate(pplacementid_date,'_',-1);
  IF i > 0 THEN
    SET pdate=iqxnetstringtodate("right"(pplacementid_date,LENGTH(pplacementid_date)-i));
    SET pplacementid="left"(pplacementid_date,i-1)
  ELSE
    SELECT '101:~Invalid data';
    RETURN
  END IF;
  SET pphours=NULLIF(iqxnetstringtodouble(phours),0.0);
  IF pphours IS NULL THEN
    SET pptime=NULL;
    SET pcancelled=1
  ELSE
    SET pptime=iqxnetstringtotime(ptimefrom);
    SET pcancelled=0
  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 pplacementid = any(SELECT FIRST pl.placementid FROM placement AS pl KEY JOIN employment KEY JOIN person AS pe JOIN IDs ON pe.divisionid = IDs.ID) THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF
  ELSE
    IF NOT pplacementid = any(
      SELECT p.placementid FROM placement AS p KEY JOIN employment KEY JOIN person KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pwebuserid UNION
      SELECT p.placementid FROM placement AS p KEY JOIN employment 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 p.placementid FROM placement AS p 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;
  DELETE FROM placementdayvariation WHERE placementid = pplacementid AND variationdate = pdate;
  INSERT INTO placementdayvariation( placementid,variationdate,workstarttime,workhours,workcancelled) VALUES( pplacementid,pdate,pptime,pphours,pcancelled) ;
  SELECT '0:~Success'
END
 
/* DOC
2017-06-21 PC Test and doc
2018-07-26 PC include improved error handler
 
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
delete from placementdayvariation where placementid='TEST';
select * from NetProvTimesheetTimeSet('test.candidate','test',12,'06:00');
expect 101*;
select * from NetProvTimesheetTimeSet('test.owner','test_'+dateformat(today(*),'dd/mm/yyyy'),12,'06:00');
expect 99*;
select * from NetProvTimesheetTimeSet('test.candidate','test99_'+dateformat(today(*),'dd/mm/yyyy'),12,'06:00');
expect 99*;
select * from NetProvTimesheetTimeSet('test.candidate','test_'+dateformat(today(*),'dd/mm/yyyy'),12,'06:00');
select (if VariationDate=dateformat(today(*),'yyyy-mm-dd') then 1 else 0 endif) as A,WorkStartTime,WorkHours from placementdayvariation where placementid='test';
expect A=1,WorkStartTime=06:00*,WorkHours=12;
delete from placementdayvariation where placementid='TEST';
*/
}