pears.NetCandidateAddEventsOneDay

CREATE PROCEDURE "pears"."NetCandidateAddEventsOneDay"( IN "pWebUserID" CHAR(20),IN "pvacancyid" CHAR(20),IN "pshifttemplateid" CHAR(20),IN "pDateFrom" DATE,
  IN "pTimeFrom" CHAR(25) DEFAULT NULL,IN "pDateTo" DATE,IN "pTimeTo" CHAR(25) DEFAULT NULL,IN "pBreak" CHAR(20) DEFAULT NULL,IN "pTheirRef" CHAR(50) DEFAULT NULL,IN "pNote" CHAR(50) DEFAULT NULL,IN "pQty" CHAR(20) DEFAULT NULL,IN "pShiftType" CHAR(50) DEFAULT NULL,
  IN "pSkills" CHAR(100) DEFAULT NULL,IN "pGrade" CHAR(50) DEFAULT NULL,IN "pWebUserName" CHAR(50) DEFAULT NULL,IN "pCanAuthorise" SMALLINT DEFAULT 0,INOUT "pLog" long VARCHAR ) 
BEGIN
  DECLARE "sid" CHAR(20);
  DECLARE "cnt" INTEGER;
  DECLARE "i" INTEGER;
  DECLARE "tfrom" TIME;
  DECLARE "tto" TIME;
  DECLARE "ibreak" INTEGER;
  DECLARE "slog" CHAR(200);
  DECLARE "pOrderReason" CHAR(100);
  IF "pdateFrom" IS NULL THEN
    RETURN
  END IF;
  SET "i" = "charindex"('|',"pshifttemplateid");
  IF "i" > 0 THEN -- Get rid of any extra info tacked onto the code
    SET "pshifttemplateid" = "left"("pshifttemplateid","i"-1)
  END IF;
  IF "trim"("isnull"("pshifttemplateid",'')) = '' THEN
    RETURN
  END IF;
  SET "cnt" = "isnull"("iqxnetstringtointeger"("pqty"),0);
  IF "cnt" <= 0 THEN
    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
  while "cnt" > 0 loop
    SET "sid" = "uniquekey"("cnt");
    INSERT INTO "tempshiftplan"( "TempShiftPlanID","VacancyID","TimeFrom","TimeTo","BreakMinutes","ShiftDate","Moveable","Description","ClientNote","EssentialSkill","EssentialSkillChoiceList","EssentialSkillGradeID","ReferenceCode","ReferenceRequired","ClientMustConfirm","TempMustConfirm","OrderedBy","TempShiftTypeID","AnalysisCode","RecoveryHours","TempShiftOrderReasonID" ) 
      SELECT "sid","pvacancyid","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),"pdateFrom","Moveable","Description","pNote","string"("taglocation",';',"tagid",';',"tagchoiceid",';',"gradetagid"),"replace"("pskills","char"(9),';'),"pgrade","ucase"("pTheirRef"),"ReferenceRequired","ClientMustConfirm","TempMustConfirm","pWebUserName","TempShiftTypeID","AnalysisCode","RecoveryHours","nullif"("pOrderReason",'')
        FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid";
    CALL "NetSetShiftPlanCascade"("sid");
    IF "pCanAuthorise" = 0 THEN
      INSERT INTO "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","breakminutes","state","tempshiftplanid","cancelreason" ) 
        SELECT "sid","pvacancyid","pdateFrom","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),'C',"sid",'~'
          FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid"
    END IF;
    SET "slog" = (SELECT "string"("dateformat"("pdateFrom",'dd/mm/yyyy'),' ',"dateformat"("isnull"("tfrom","timefrom"),'hh:nn'),'-',"dateformat"("isnull"("tto","timeto"),'hh:nn'),' ',"description")
        FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid");
    SET "pLog" = "string"("pLog","slog","char"(13),"char"(10));
    SET "cnt" = "cnt"-1
  END loop
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateAddEventsOneDay" IS 
{CREATE PROCEDURE pears."NetCandidateAddEventsOneDay"( IN "pWebUserID" CHAR(20),IN "pvacancyid" CHAR(20),IN "pshifttemplateid" CHAR(20),IN "pDateFrom" DATE,
  IN "pTimeFrom" CHAR(25) DEFAULT NULL,IN "pDateTo" DATE,IN "pTimeTo" CHAR(25) DEFAULT NULL,IN "pBreak" CHAR(20) DEFAULT NULL,IN "pTheirRef" CHAR(50) DEFAULT NULL,IN "pNote" CHAR(50) DEFAULT NULL,IN "pQty" CHAR(20) DEFAULT NULL,IN "pShiftType" CHAR(50) DEFAULT NULL,
  IN "pSkills" CHAR(100) DEFAULT NULL,IN "pGrade" CHAR(50) DEFAULT NULL,IN "pWebUserName" CHAR(50) DEFAULT NULL,IN "pCanAuthorise" SMALLINT DEFAULT 0,INOUT "pLog" long VARCHAR ) 
BEGIN
  DECLARE "sid" CHAR(20);
  DECLARE "cnt" INTEGER;
  DECLARE "i" INTEGER;
  DECLARE "tfrom" TIME;
  DECLARE "tto" TIME;
  DECLARE "ibreak" INTEGER;
  DECLARE "slog" CHAR(200);
  DECLARE "pOrderReason" CHAR(100);
  IF "pdateFrom" IS NULL THEN
    RETURN
  END IF;
  SET "i" = "charindex"('|',"pshifttemplateid");
  IF "i" > 0 THEN -- Get rid of any extra info tacked onto the code
    SET "pshifttemplateid" = "left"("pshifttemplateid","i"-1)
  END IF;
  IF "trim"("isnull"("pshifttemplateid",'')) = '' THEN
    RETURN
  END IF;
  SET "cnt" = "isnull"("iqxnetstringtointeger"("pqty"),0);
  IF "cnt" <= 0 THEN
    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
  while "cnt" > 0 loop
    SET "sid" = "uniquekey"("cnt");
    INSERT INTO "tempshiftplan"( "TempShiftPlanID","VacancyID","TimeFrom","TimeTo","BreakMinutes","ShiftDate","Moveable","Description","ClientNote","EssentialSkill","EssentialSkillChoiceList","EssentialSkillGradeID","ReferenceCode","ReferenceRequired","ClientMustConfirm","TempMustConfirm","OrderedBy","TempShiftTypeID","AnalysisCode","RecoveryHours","TempShiftOrderReasonID" ) 
      SELECT "sid","pvacancyid","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),"pdateFrom","Moveable","Description","pNote","string"("taglocation",';',"tagid",';',"tagchoiceid",';',"gradetagid"),"replace"("pskills","char"(9),';'),"pgrade","ucase"("pTheirRef"),"ReferenceRequired","ClientMustConfirm","TempMustConfirm","pWebUserName","TempShiftTypeID","AnalysisCode","RecoveryHours","nullif"("pOrderReason",'')
        FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid";
    CALL "NetSetShiftPlanCascade"("sid");
    IF "pCanAuthorise" = 0 THEN
      INSERT INTO "tempshift"( "tempshiftid","vacancyid","shiftdate","timefrom","timeto","breakminutes","state","tempshiftplanid","cancelreason" ) 
        SELECT "sid","pvacancyid","pdateFrom","isnull"("tfrom","timefrom"),"isnull"("tto","timeto"),"isnull"("ibreak","breakminutes"),'C',"sid",'~'
          FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid"
    END IF;
    SET "slog" = (SELECT "string"("dateformat"("pdateFrom",'dd/mm/yyyy'),' ',"dateformat"("isnull"("tfrom","timefrom"),'hh:nn'),'-',"dateformat"("isnull"("tto","timeto"),'hh:nn'),' ',"description")
        FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "pshifttemplateid");
    SET "pLog" = "string"("pLog","slog","char"(13),"char"(10));
    SET "cnt" = "cnt"-1
  END loop
END
}