database:procedures:pears_netclientaddsingleshift



pears.NetClientAddSingleShift

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE PROCEDURE "pears"."NetClientAddSingleShift"( IN "pWebUserID" CHAR(20),IN "pvacancyid" CHAR(20),IN "pshifttemplateid" CHAR(20),IN "pdate" DATE,
  IN "ptimefrom" CHAR(25) DEFAULT NULL,IN "ptimeto" CHAR(25) DEFAULT NULL,IN "pBreak" CHAR(20) DEFAULT NULL,IN "pTheirRef" CHAR(50) DEFAULT NULL,
  IN "pLocation" CHAR(50) DEFAULT NULL,IN "pPONumber" CHAR(50) DEFAULT NULL,IN "pNote" CHAR(50) DEFAULT NULL,IN "pQty" CHAR(20) DEFAULT NULL,
  IN "pOrderReason" CHAR(50) DEFAULT NULL,IN "pSkills" CHAR(100) DEFAULT NULL,IN "pGrade" CHAR(50) DEFAULT NULL,IN "pCanAuthorise" SMALLINT DEFAULT 0 ) 
RESULT( "pResult" CHAR(255) ) 
// IQXWeb
BEGIN
  DECLARE "sid" CHAR(20);
  DECLARE "cnt" INTEGER;
  DECLARE "tfrom" TIME;
  DECLARE "tto" TIME;
  DECLARE "ibreak" INTEGER;
  DECLARE "isReasonCompulsory" INTEGER;
  DECLARE "RefNote" CHAR(50);
  DECLARE "WebUser" CHAR(60);
  DECLARE "TempShiftTemplateID" CHAR(20);
  DECLARE "NewState" CHAR(5);
  IF "pTheirRef" <> '' THEN
    SET "RefNote" = "ucase"("pTheirRef")
  ELSE
    SET "RefNote" = "pPONumber"
  END IF;
  IF "isnull"("pNote",'') = '' THEN SET "pNote" = "pLocation"
  END IF;
  IF "pdate" IS NULL THEN
    SELECT '101:~pDate is null';
    RETURN
  END IF;
  SET "cnt" = "isnull"("iqxnetstringtointeger"("pqty"),0);
  IF "cnt" <= 0 THEN
    SELECT '102:~Quantity is 0';
    RETURN
  END IF;
  SET "tfrom" = "iqxnetstringtotime"("ptimefrom");
  SET "tto" = "iqxnetstringtotime"("ptimeto");
  SET "ibreak" = "iqxnetstringtointeger"("pbreak"); -- If times and break not supplied they will come from the template
  SET "WebUser" = (SELECT top 1 "name" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  SET "NewState" = 'C';
  while "cnt" > 0 loop
    SET "sid" = "uniquekey"("cnt"*"rand"()*10);
    SET "isReasonCompulsory" = (SELECT FIRST "ShiftOrderReasonRequired" FROM "TempShiftTemplate" WHERE "TempShiftTemplateID" = "pshifttemplateid");
    IF "pOrderReason" = '' AND "isReasonCompulsory" = 1 THEN
      SELECT '105~Reason is compulsory';
      RETURN
    END IF;
    INSERT INTO "tempshiftplan"( "TempShiftPlanID","VacancyID","TimeFrom","TimeTo","BreakMinutes","ShiftDate","Moveable","Description","ClientNote",
      "EssentialSkill","EssentialSkillChoiceList","EssentialSkillGradeID",
      "ReferenceCode","ReferenceRequired",
      "ClientMustConfirm","TempMustConfirm","OrderedBy","TempShiftTypeID","AnalysisCode","RecoveryHours","TempShiftOrderReasonID","TempShiftTemplateID" ) 
      SELECT top 1 "sid","pvacancyid","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),"pdate","Moveable","Description","pNote",
        "string"("taglocation",';',"tagid",';',"tagchoiceid",';',"gradetagid"),"replace"("pskills","char"(9),';'),"isnull"("pgrade","EssentialSkillGradeID"),
        "RefNote","ReferenceRequired",
        "ClientMustConfirm","TempMustConfirm","WebUser","TempShiftTypeID","AnalysisCode","RecoveryHours","nullif"("pOrderReason",''),"pShiftTemplateID"
        FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid";
    CALL "NetSetShiftPlanCascade"("sid");
    -- retain the section below until definitely not required
    -- if "pCanAuthorise" = 0 then
    --   insert into "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","breakminutes","state","tempshiftplanid","cancelreason",ConfirmedWith,"TempshiftTemplateID" )
    --     select "sid","pvacancyid","pdate","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),NewState,"sid",'~',WebUser,"pShiftTemplateID"
    --       from "tempshifttemplate" where "tempshifttemplateid" = "pshifttemplateid";
    -- end if;
    SET "cnt" = "cnt"-1
  END loop;
  SELECT '0:~Success'
END /* DOC
20161025 IW-69 PC modified NetClientAddShiftsOneDay, but removing pLog and other things, which was causing a return error. use in conjunction with NetClientAddMultipleShifts
2018-08-22 PC improve error handling
 
tests:
invalid pwebuserid
valid pwebuserid but invalid  pvacancyid
valid pwebuserid, valid pvacancyid but invalid ptempshifttemplateid
valid pwebuserid, pvacancyid, ptempshifttemplateid but invalid date
valid pwebuserid, pvacancyid, ptempshifttemplateid, date but invalid timefrom
valid pwebuserid, pvacancyid, ptempshifttemplateid, date, timefrom but invalid quantity
valid pwebuserid, pvacancyid, ptempshifttemplateid, date, timefrom, quantity
 
*/
/* TEST
call NetTestSetup('');
delete from tempshiftplan where vacancyid='TEST';
select replace(pResult,' ','!') as A from NetClientAddSingleShift(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0 );
expect A=101*;
select replace(pResult,' ','!') as A  from NetClientAddSingleShift('test.client',null,null,null,null,null,null,null,null,null,null,null,null,null,null,0 );
expect A=101*;
select replace(pResult,' ','!') as A  from NetClientAddSingleShift('test.client','TEST',null,null,null,null,null,null,null,null,null,null,null,null,null,0 );
expect A=101*;
select replace(pResult,' ','!') as A  from NetClientAddSingleShift('test.client','TEST','TEST',null,null,null,null,null,null,null,null,null,null,null,null,0 );
expect A=101*;
select replace(pResult,' ','!') as A  from NetClientAddSingleShift('test.client','TEST','TEST',today(),null,null,null,null,null,null,null,0,null,null,null,0 );
expect A=102*;
select replace(pResult,' ','!') as A  from NetClientAddSingleShift('test.client','TEST','TEST',today(),null,null,null,null,null,null,null,1,null,null,null,0 );
expect A=0:~Success;
select top 1 * from tempshiftplan order by whenentered desc;
expect VacancyID=TEST,Description=test,TimeFrom=08:00*,TimeTo=16:00*;
delete from tempshiftplan where vacancyid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientAddSingleShift" IS 
{CREATE PROCEDURE pears."NetClientAddSingleShift"( IN "pWebUserID" CHAR(20),IN "pvacancyid" CHAR(20),IN "pshifttemplateid" CHAR(20),IN "pdate" DATE,
  IN "ptimefrom" CHAR(25) DEFAULT NULL,IN "ptimeto" CHAR(25) DEFAULT NULL,IN "pBreak" CHAR(20) DEFAULT NULL,IN "pTheirRef" CHAR(50) DEFAULT NULL,
  IN "pLocation" CHAR(50) DEFAULT NULL,IN "pPONumber" CHAR(50) DEFAULT NULL,IN "pNote" CHAR(50) DEFAULT NULL,IN "pQty" CHAR(20) DEFAULT NULL,
  IN "pOrderReason" CHAR(50) DEFAULT NULL,IN "pSkills" CHAR(100) DEFAULT NULL,IN "pGrade" CHAR(50) DEFAULT NULL,IN "pCanAuthorise" SMALLINT DEFAULT 0 ) 
RESULT (pResult CHAR(255))
// IQXWeb
BEGIN
  DECLARE "sid" CHAR(20);
  DECLARE "cnt" INTEGER;
  DECLARE "tfrom" TIME;
  DECLARE "tto" TIME;
  DECLARE "ibreak" INTEGER;
  DECLARE "isReasonCompulsory" INTEGER;
  DECLARE "RefNote" CHAR(50);
  DECLARE WebUser CHAR(60);
  DECLARE TempShiftTemplateID CHAR(20);
  DECLARE NewState CHAR(5);
  IF "pTheirRef" <> '' THEN 
    SET "RefNote" = "ucase"("pTheirRef")
  ELSE 
    SET "RefNote" = "pPONumber"
  END IF;
  IF "isnull"("pNote",'') = '' THEN SET "pNote" = "pLocation"
  END IF;
  IF "pdate" IS NULL THEN
    SELECT '101:~pDate is null';
    RETURN
  END IF;
  SET "cnt" = "isnull"("iqxnetstringtointeger"("pqty"),0);
  IF "cnt" <= 0 THEN
    SELECT '102:~Quantity is 0';
    RETURN
  END IF;
  SET "tfrom" = "iqxnetstringtotime"("ptimefrom");
  SET "tto" = "iqxnetstringtotime"("ptimeto");
  SET "ibreak" = "iqxnetstringtointeger"("pbreak"); -- If times and break not supplied they will come from the template
  SET WebUser=(SELECT top 1 name FROM iqxnetuser WHERE iqxnetuserid=pWebUserID);
  SET NewState='C';
  while "cnt" > 0 loop
    SET "sid" = "uniquekey"("cnt" * rand()*10);
    SET "isReasonCompulsory" = (SELECT FIRST "ShiftOrderReasonRequired" FROM "TempShiftTemplate" WHERE "TempShiftTemplateID" = "pshifttemplateid");
    IF "pOrderReason" = '' AND "isReasonCompulsory" = 1 THEN
      SELECT '105~Reason is compulsory';
      RETURN
    END IF;
 
    INSERT INTO "tempshiftplan"( "TempShiftPlanID","VacancyID","TimeFrom","TimeTo","BreakMinutes","ShiftDate","Moveable","Description","ClientNote",
      "EssentialSkill","EssentialSkillChoiceList","EssentialSkillGradeID",
      "ReferenceCode","ReferenceRequired",
      "ClientMustConfirm","TempMustConfirm","OrderedBy","TempShiftTypeID","AnalysisCode","RecoveryHours","TempShiftOrderReasonID","TempShiftTemplateID" ) 
     SELECT top 1 "sid","pvacancyid","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),"pdate","Moveable","Description","pNote",
       "string"("taglocation",';',"tagid",';',"tagchoiceid",';',"gradetagid"),"replace"("pskills","char"(9),';'),isnull("pgrade",EssentialSkillGradeID),
       "RefNote","ReferenceRequired",
       "ClientMustConfirm","TempMustConfirm","WebUser","TempShiftTypeID","AnalysisCode","RecoveryHours","nullif"("pOrderReason",''),"pShiftTemplateID"
          FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid";
      CALL "NetSetShiftPlanCascade"("sid");
    -- retain the section below until definitely not required
     -- if "pCanAuthorise" = 0 then
     --   insert into "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","breakminutes","state","tempshiftplanid","cancelreason",ConfirmedWith,"TempshiftTemplateID" )
     --     select "sid","pvacancyid","pdate","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),NewState,"sid",'~',WebUser,"pShiftTemplateID"
     --       from "tempshifttemplate" where "tempshifttemplateid" = "pshifttemplateid";
     -- end if;
    SET "cnt" = "cnt"-1;
 
  END loop;
  SELECT '0:~Success';
END
 
/* DOC
20161025 IW-69 PC modified NetClientAddShiftsOneDay, but removing pLog and other things, which was causing a return error. use in conjunction with NetClientAddMultipleShifts
2018-08-22 PC improve error handling
 
tests:
    invalid pwebuserid
    valid pwebuserid but invalid  pvacancyid
    valid pwebuserid, valid pvacancyid but invalid ptempshifttemplateid
    valid pwebuserid, pvacancyid, ptempshifttemplateid but invalid date
    valid pwebuserid, pvacancyid, ptempshifttemplateid, date but invalid timefrom
    valid pwebuserid, pvacancyid, ptempshifttemplateid, date, timefrom but invalid quantity
    valid pwebuserid, pvacancyid, ptempshifttemplateid, date, timefrom, quantity
 
*/
 
/* TEST
call NetTestSetup('');
delete from tempshiftplan where vacancyid='TEST';
select replace(pResult,' ','!') as A from NetClientAddSingleShift(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0 );
expect A=101*;
select replace(pResult,' ','!') as A  from NetClientAddSingleShift('test.client',null,null,null,null,null,null,null,null,null,null,null,null,null,null,0 );
expect A=101*;
select replace(pResult,' ','!') as A  from NetClientAddSingleShift('test.client','TEST',null,null,null,null,null,null,null,null,null,null,null,null,null,0 );
expect A=101*;
select replace(pResult,' ','!') as A  from NetClientAddSingleShift('test.client','TEST','TEST',null,null,null,null,null,null,null,null,null,null,null,null,0 );
expect A=101*;
select replace(pResult,' ','!') as A  from NetClientAddSingleShift('test.client','TEST','TEST',today(),null,null,null,null,null,null,null,0,null,null,null,0 );
expect A=102*;
select replace(pResult,' ','!') as A  from NetClientAddSingleShift('test.client','TEST','TEST',today(),null,null,null,null,null,null,null,1,null,null,null,0 );
expect A=0:~Success;
select top 1 * from tempshiftplan order by whenentered desc;
expect VacancyID=TEST,Description=test,TimeFrom=08:00*,TimeTo=16:00*;
delete from tempshiftplan where vacancyid='TEST';
*/
}
  • database/procedures/pears_netclientaddsingleshift.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1