pears.NetClientAddMultipleShifts

CREATE PROCEDURE "pears"."NetClientAddMultipleShifts"( IN "pWebUserID" CHAR(20),IN "pParamList" long VARCHAR ) 
RESULT( "pResult" CHAR(255) ) 
// IQXWeb
BEGIN
  DECLARE "RecordCount" INTEGER;
  DECLARE "LoopCounter" INTEGER;
  DECLARE "FieldCount" INTEGER;
  DECLARE "ResidualRecord" long VARCHAR;
  DECLARE "CurrentRecord" CHAR(2048);
  DECLARE "ShiftDetailList" long VARCHAR;
  DECLARE "ErrorResponse" CHAR(100);
  DECLARE "CombinedErrorResponse" CHAR(1000);
  DECLARE "vacid" CHAR(20);
  DECLARE "shifttemplateid" CHAR(20);
  DECLARE "ShiftDate" CHAR(20);
  DECLARE "timefrom" CHAR(25);
  DECLARE "timeto" CHAR(25);
  DECLARE "BreakMinutes" CHAR(20);
  DECLARE "TheirRef" CHAR(50);
  DECLARE "Location" CHAR(50);
  DECLARE "PONumber" CHAR(50);
  DECLARE "Note" CHAR(50);
  DECLARE "Qty" CHAR(20);
  DECLARE "OrderReason" CHAR(50);
  DECLARE "Skills" CHAR(100);
  DECLARE "Grade" CHAR(50);
  DECLARE "CanAuthorise" SMALLINT;
  DECLARE "compid" CHAR(20);
  SET "ResidualRecord" = "pParamList"+'*';
  while "length"("ResidualRecord") > 0 loop
    SET "vacid" = '';
    SET "shifttemplateid" = '';
    SET "ShiftDate" = NULL;
    SET "timefrom" = '';
    SET "timeto" = '';
    SET "BreakMinutes" = '';
    SET "TheirRef" = '';
    SET "Location" = '';
    SET "PONumber" = '';
    SET "Note" = '';
    SET "Qty" = '';
    SET "OrderReason" = '';
    SET "Skills" = '';
    SET "Grade" = '';
    SET "CanAuthorise" = 0;
    SET "CurrentRecord" = "left"("ResidualRecord","charindex"('*',"ResidualRecord"));
    SET "vacid" = "replace"("left"("CurrentRecord","charindex"('|',"CurrentRecord")),'|','');
    SET "CurrentRecord" = "right"("CurrentRecord","length"("CurrentRecord")-"charindex"('|',"CurrentRecord"));
    SET "shifttemplateid" = "replace"("left"("CurrentRecord","charindex"('|',"CurrentRecord")),'|','');
    SET "CurrentRecord" = "right"("CurrentRecord","length"("CurrentRecord")-"charindex"('|',"CurrentRecord"));
    SET "shiftdate" = "replace"("left"("CurrentRecord","charindex"('|',"CurrentRecord")),'|','');
    SET "CurrentRecord" = "right"("CurrentRecord","length"("CurrentRecord")-"charindex"('|',"CurrentRecord"));
    SET "timefrom" = "replace"("replace"("left"("CurrentRecord","charindex"('|',"CurrentRecord")),'|',''),'%3A',':');
    SET "CurrentRecord" = "right"("CurrentRecord","length"("CurrentRecord")-"charindex"('|',"CurrentRecord"));
    SET "timeto" = "replace"("replace"("left"("CurrentRecord","charindex"('|',"CurrentRecord")),'|',''),'%3A',':');
    SET "CurrentRecord" = "right"("CurrentRecord","length"("CurrentRecord")-"charindex"('|',"CurrentRecord"));
    SET "Qty" = "replace"("left"("CurrentRecord","charindex"('|',"CurrentRecord")),'|','');
    SET "CurrentRecord" = "right"("CurrentRecord","length"("CurrentRecord")-"charindex"('|',"CurrentRecord"));
    SET "OrderReason" = "replace"("left"("CurrentRecord","charindex"('|',"CurrentRecord")),'|','');
    SET "CurrentRecord" = "right"("CurrentRecord","length"("CurrentRecord")-"charindex"('|',"CurrentRecord"));
    SET "TheirRef" = "replace"("left"("CurrentRecord","charindex"('|',"CurrentRecord")),'|','');
    SET "CurrentRecord" = "right"("CurrentRecord","length"("CurrentRecord")-"charindex"('|',"CurrentRecord"));
    SET "Note" = "replace"("left"("CurrentRecord","charindex"('|',"CurrentRecord")),'|','');
    SET "CurrentRecord" = "right"("CurrentRecord","length"("CurrentRecord")-"charindex"('|',"CurrentRecord"));
    SET "BreakMinutes" = "replace"("left"("CurrentRecord","charindex"('*',"CurrentRecord")),'*','');
    SET "ResidualRecord" = "right"("ResidualRecord","length"("ResidualRecord")-"charindex"('*',"ResidualRecord"));
    SET "compid" = (SELECT "e"."companyid" FROM "vacancy" AS "v" KEY JOIN "employment" AS "e" WHERE "v"."vacancyid" = "vacid");
    IF NOT "pwebuserid" = any(SELECT "l"."iqxnetuserid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "e"."companyid" = "compid") THEN
      SELECT '100:~Permission denied';
      RETURN
    END IF;
    IF "isnull"("shiftdate",'') = '' THEN
      SELECT '101:~Invalid start date';
      RETURN
    END IF;
    IF "isnull"("timefrom",'') = '' THEN
      SELECT '102:~Invalid start time';
      RETURN
    END IF;
    IF "isnull"("timeto",'') = '' THEN
      SELECT '103:~Invalid end time';
      RETURN
    END IF;
    IF "isnull"("Qty",'') = '' THEN
      SELECT '104:~Invalid quantity';
      RETURN
    END IF;
    IF "isnull"("OrderReason",'') = '' THEN
      SELECT '105:~Order reason required';
      RETURN
    END IF;
    SET "ErrorResponse" = (SELECT "pResult" FROM "NetClientAddSingleShift"("pWebUserID","vacid","shifttemplateid","shiftdate","timefrom","timeto","BreakMinutes","TheirRef",NULL,NULL,"Note","Qty","OrderReason",NULL,NULL,0));
    IF "ErrorResponse" = '0:~Success' THEN
      SET "ShiftDetailList" = "string"("ShiftDetailList","shiftdate",' ',"timefrom",'-',"timeto",' (',"Qty",')',"char"(13))
    ELSE
      SET "CombinedErrorResponse" = "string"("CombinedErrorResponse","char"(13),"shiftdate",' ',"timefrom",'-',"timeto",': ',"ErrorResponse")
    END IF
  END loop;
  IF "CombinedErrorResponse" IS NULL THEN
    CALL "IQXNetPopup"("pWebUserID",NULL,NULL,"vacid",NULL,NULL,'ADDSHIFTS','New Shift requirement(s) added',"ShiftDetailList");
    SELECT '0:~Success'
  ELSE
    SELECT "CombinedErrorResponse"
  END IF
END /* DOC
20161025 PC wrapper for NetClientAddShiftsOneDay
use in conjunction with NetClientAddOneDay and IQXNetPopup
20170609 PC add Order reason IW-69
2018-08-01 PC improved error handling
2019-01-10 IW-784 GJ/PC Removed permission check and updated tests.
 
inputs:
pWebUserID
pParamList - aggregated parameters. 
| between fields
* between records
 
outputs:
pResult
 
tests:
invalid pWebUserID
valid pWebUserID but no value string
valid pWebUserID and value string but no date
valid pWebUserID and value string but no start time
valid pWebUserID and value string but no end time
valid pWebUserID and value string but quantity
valid pWebUserID and value string
*/
/* TEST
call NetTestSetup('');
select pResult from NetClientAddMultipleShifts(null,null);  
expect pResult=100*;    
select pResult from NetClientAddMultipleShifts('test.client',null); 
expect pResult=100*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST||||||||30*TEST|TEST||||||||60');
expect pResult=101*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST|2016-05-24|||||||30*TEST|TEST|2016-05-25|||||||60');
expect pResult=102*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST|2016-05-24|08%3A00||||||30*TEST|TEST|2016-05-25|06%3A00||||||60');
expect pResult=103*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST|2016-05-24|08%3A00|16%3A30|||||30*TEST|TEST|2016-05-25|06%3A00|16%3A00|||||60');
expect pResult=104*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST|2016-05-24|08%3A00|16%3A30|1||||30*TEST|TEST|2016-05-25|06%3A00|16%3A00|1||||60');
expect pResult=105*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST|2016-05-24|08%3A00|16%3A30|1|TEST|||30*TEST|TEST|2016-05-25|06%3A00|16%3A00|1|TEST|||60');
expect pResult=0:~Success;
select top 2 * from tempshiftplan where VacancyID='TEST' and TempShiftPlanID!='TEST' order by ShiftDate asc;
expect VacancyID=TEST,description=test,TimeFrom=08:00*,TimeTo=16:30*,ShiftDate=24/05/2016,BreakMinutes=30;
expect VacancyID=TEST,description=test,TimeFrom=06:00*,TimeTo=16:00*,ShiftDate=25/05/2016,BreakMinutes=60;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientAddMultipleShifts" IS 
{CREATE PROCEDURE pears."NetClientAddMultipleShifts"( IN "pWebUserID" CHAR(20),IN pParamList long VARCHAR) 
RESULT (pResult CHAR(255))
// IQXWeb
BEGIN
    DECLARE RecordCount INTEGER;
    DECLARE LoopCounter INTEGER;
    DECLARE FieldCount INTEGER;
    DECLARE ResidualRecord long VARCHAR;
    DECLARE CurrentRecord CHAR(2048);
    DECLARE ShiftDetailList long VARCHAR;
    DECLARE ErrorResponse CHAR(100);
    DECLARE CombinedErrorResponse CHAR(1000);
    DECLARE vacid CHAR(20);
    DECLARE shifttemplateid CHAR(20);
    DECLARE ShiftDate CHAR(20);
    DECLARE timefrom CHAR(25);
    DECLARE timeto CHAR(25);
    DECLARE BreakMinutes CHAR(20);
    DECLARE TheirRef CHAR(50);
    DECLARE Location CHAR(50);
    DECLARE PONumber CHAR(50);
    DECLARE Note CHAR(50);
    DECLARE Qty CHAR(20);
    DECLARE OrderReason CHAR(50);
    DECLARE Skills CHAR(100);
    DECLARE Grade CHAR(50);
    DECLARE CanAuthorise SMALLINT; 
    DECLARE compid CHAR(20);
    SET ResidualRecord=pParamList+'*';
 
    while LENGTH(ResidualRecord)>0 loop
        SET vacid='';
        SET shifttemplateid='';
        SET ShiftDate=NULL;
        SET timefrom='';
        SET timeto ='';
        SET BreakMinutes='';
        SET TheirRef='';
        SET Location='';
        SET PONumber='';
        SET Note='';
        SET Qty='';
        SET OrderReason='';
        SET Skills='';
        SET Grade='';
        SET CanAuthorise =0;
 
        SET CurrentRecord=LEFT(ResidualRecord, charindex('*',ResidualRecord));
        SET vacid=REPLACE(LEFT(CurrentRecord,charindex('|',CurrentRecord)),'|','');
        SET CurrentRecord=RIGHT(CurrentRecord, LENGTH(CurrentRecord)-charindex('|',CurrentRecord));
        SET shifttemplateid=REPLACE(LEFT(CurrentRecord,charindex('|',CurrentRecord)),'|','');
        SET CurrentRecord=RIGHT(CurrentRecord, LENGTH(CurrentRecord)-charindex('|',CurrentRecord));
        SET shiftdate=REPLACE(LEFT(CurrentRecord,charindex('|',CurrentRecord)),'|','');
        SET CurrentRecord=RIGHT(CurrentRecord, LENGTH(CurrentRecord)-charindex('|',CurrentRecord));
        SET timefrom=REPLACE(REPLACE(LEFT(CurrentRecord,charindex('|',CurrentRecord)),'|',''),'%3A',':');
        SET CurrentRecord=RIGHT(CurrentRecord, LENGTH(CurrentRecord)-charindex('|',CurrentRecord));
        SET timeto=REPLACE(REPLACE(LEFT(CurrentRecord,charindex('|',CurrentRecord)),'|',''),'%3A',':');
        SET CurrentRecord=RIGHT(CurrentRecord, LENGTH(CurrentRecord)-charindex('|',CurrentRecord));
        SET Qty=REPLACE(LEFT(CurrentRecord,charindex('|',CurrentRecord)),'|','');
        SET CurrentRecord=RIGHT(CurrentRecord, LENGTH(CurrentRecord)-charindex('|',CurrentRecord));
        SET OrderReason=REPLACE(LEFT(CurrentRecord,charindex('|',CurrentRecord)),'|','');
        SET CurrentRecord=RIGHT(CurrentRecord, LENGTH(CurrentRecord)-charindex('|',CurrentRecord));
        SET TheirRef=REPLACE(LEFT(CurrentRecord,charindex('|',CurrentRecord)),'|','');
        SET CurrentRecord=RIGHT(CurrentRecord, LENGTH(CurrentRecord)-charindex('|',CurrentRecord));
        SET Note=REPLACE(LEFT(CurrentRecord,charindex('|',CurrentRecord)),'|','');
        SET CurrentRecord=RIGHT(CurrentRecord, LENGTH(CurrentRecord)-charindex('|',CurrentRecord));
        SET BreakMinutes=REPLACE(LEFT(CurrentRecord,charindex('*',CurrentRecord)),'*','');
        SET ResidualRecord=RIGHT(ResidualRecord, LENGTH(ResidualRecord)-charindex('*',ResidualRecord));
 
        SET compid=(SELECT e.companyid FROM vacancy v KEY JOIN employment e WHERE v.vacancyid=vacid);
        IF NOT pwebuserid IN (SELECT l.iqxnetuserid FROM iqxnetuserlink l KEY JOIN employment e WHERE e.companyid=compid) THEN
            SELECT '100:~Permission denied';
            RETURN;
        END IF;
 
        IF isnull(shiftdate,'') = '' THEN
            SELECT '101:~Invalid start date';
            RETURN;
        END IF;
        IF isnull(timefrom,'') = '' THEN
            SELECT '102:~Invalid start time';
            RETURN;
        END IF;
        IF isnull(timeto,'') = '' THEN
            SELECT '103:~Invalid end time';
            RETURN;
        END IF;
        IF isnull(Qty,'') = '' THEN
            SELECT '104:~Invalid quantity';
            RETURN;
        END IF;
        IF isnull(OrderReason,'') = '' THEN
            SELECT '105:~Order reason required';
            RETURN;
        END IF;
        SET ErrorResponse=(SELECT pResult FROM NetClientAddSingleShift(pWebUserID,vacid,shifttemplateid,shiftdate,timefrom,timeto,BreakMinutes,TheirRef,NULL,NULL,Note,Qty,OrderReason,NULL,NULL,0)); 
        IF ErrorResponse ='0:~Success' THEN
            SET ShiftDetailList=string(ShiftDetailList,shiftdate,' ',timefrom,'-',timeto,' (',Qty,')',CHAR(13));
        ELSE
            SET CombinedErrorResponse=string(CombinedErrorResponse,CHAR(13),shiftdate,' ',timefrom,'-',timeto,': ',ErrorResponse);
        END IF
    END loop;
    IF CombinedErrorResponse IS NULL THEN
        CALL "IQXNetPopup"("pWebUserID",NULL,NULL,vacid,NULL,NULL,'ADDSHIFTS','New Shift requirement(s) added',ShiftDetailList);
        SELECT '0:~Success';
    ELSE
        SELECT CombinedErrorResponse;
    END IF;
END
 
/* DOC
20161025 PC wrapper for NetClientAddShiftsOneDay
use in conjunction with NetClientAddOneDay and IQXNetPopup
20170609 PC add Order reason IW-69
2018-08-01 PC improved error handling
2019-01-10 IW-784 GJ/PC Removed permission check and updated tests.
 
inputs:
    pWebUserID
    pParamList - aggregated parameters. 
        | between fields
        * between records
 
outputs:
    pResult
 
tests:
    invalid pWebUserID
    valid pWebUserID but no value string
    valid pWebUserID and value string but no date
    valid pWebUserID and value string but no start time
    valid pWebUserID and value string but no end time
    valid pWebUserID and value string but quantity
    valid pWebUserID and value string
*/
 
/* TEST
call NetTestSetup('');
select pResult from NetClientAddMultipleShifts(null,null);  
expect pResult=100*;    
select pResult from NetClientAddMultipleShifts('test.client',null); 
expect pResult=100*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST||||||||30*TEST|TEST||||||||60');
expect pResult=101*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST|2016-05-24|||||||30*TEST|TEST|2016-05-25|||||||60');
expect pResult=102*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST|2016-05-24|08%3A00||||||30*TEST|TEST|2016-05-25|06%3A00||||||60');
expect pResult=103*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST|2016-05-24|08%3A00|16%3A30|||||30*TEST|TEST|2016-05-25|06%3A00|16%3A00|||||60');
expect pResult=104*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST|2016-05-24|08%3A00|16%3A30|1||||30*TEST|TEST|2016-05-25|06%3A00|16%3A00|1||||60');
expect pResult=105*;
select pResult from NetClientAddMultipleShifts('test.client','TEST|TEST|2016-05-24|08%3A00|16%3A30|1|TEST|||30*TEST|TEST|2016-05-25|06%3A00|16%3A00|1|TEST|||60');
expect pResult=0:~Success;
select top 2 * from tempshiftplan where VacancyID='TEST' and TempShiftPlanID!='TEST' order by ShiftDate asc;
expect VacancyID=TEST,description=test,TimeFrom=08:00*,TimeTo=16:30*,ShiftDate=24/05/2016,BreakMinutes=30;
expect VacancyID=TEST,description=test,TimeFrom=06:00*,TimeTo=16:00*,ShiftDate=25/05/2016,BreakMinutes=60;
*/
}