pears.psHealthInsertUpdateShift

CREATE PROCEDURE "pears"."psHealthInsertUpdateShift"( 
  IN @TempShiftID CHAR(20),IN @TempShiftPlanID CHAR(20) ) 
BEGIN
  DECLARE "recipid" CHAR(20);
  DECLARE "tempid" CHAR(20);
  DECLARE "vacid" CHAR(20);
  DECLARE "sdate" TIMESTAMP;
  DECLARE "edate" TIMESTAMP;
  DECLARE "sstate" CHAR(1);
  -- shift and shiftplan
  -- do the insert/update into psHealthShift here
  -- if inserting check sending recipient, temp, vacancy
  -- no recipient do nothing
  -- if updating check just flag resending
  IF @TempShiftID IS NOT NULL THEN
    SELECT "recipientid" INTO "recipid" FROM "vacancy" KEY JOIN "tempshift" WHERE "tempshiftid" = @TempShiftID;
    SELECT "personid","vacancyid" INTO "tempid","vacid" FROM "tempshift" WHERE "tempshiftid" = @TempShiftID
  ELSE
    SELECT "recipientid","vacancy"."vacancyid" INTO "recipid","vacid" FROM "vacancy" KEY JOIN "tempshiftplan" WHERE "tempshiftplanid" = @TempShiftPlanID
  END IF;
  IF(NOT "psHealthCanSendVacancy"("vacid") = 1) OR "recipid" IS NULL THEN
    RETURN
  END IF;
  IF "tempid" IS NOT NULL THEN
    IF "psHealthCanSendPerson"("tempid",'C') = 1 THEN
      CALL "psHealthInsertUpdatePerson"("tempid",'C')
    ELSE RETURN
    END IF END IF;
  IF "psHealthCanSendPerson"("recipid",'R') = 1 THEN
    CALL "psHealthInsertUpdatePerson"("recipid",'R');
    IF @TempShiftID IS NOT NULL THEN
      SELECT "shiftdate"+"timefrom","shiftdate"+"timeto"+(IF "timefrom" > "timeto" THEN 1 ELSE 0 endif),
        "state" INTO "sdate","edate","sstate" FROM "tempshift" WHERE "tempshiftid" = @TempShiftID;
      IF EXISTS(SELECT * FROM "psHealthShift" WHERE "tempshiftid" = @TempShiftID) THEN
        UPDATE "psHealthShift" SET "transferbatch" = 0,"startdatetime" = "sdate","enddatetime" = "edate","status" = (IF "sstate" = 'C' THEN 'Cancelled'
          ELSE 'Outstanding'
          endif),"updatedDateTime" = CURRENT TIMESTAMP WHERE "tempshiftid" = @TempShiftID
      ELSE
        INSERT INTO "psHealthShift"( "endDateTime",
          "isDeleted","name","psHealthShiftID","startDateTime","status","taskTypeId","tempshiftid","tenantBranchId","tenantCarerId","tenantCaseId","tenantCustomerId",
          "tenantTaskId","transferbatch" ) SELECT "edate",0,(SELECT "shiftserialnumber" FROM "tempshiftplan" WHERE "tempshiftplanid" = "tempshift"."tempshiftplanid"),"uniquekey"('x'),"sdate",'Outstanding',
            "pshealthtasktype"(@TempShiftID,NULL),@TempShiftID,"pshealthbranch"(@TempShiftID,NULL,NULL,NULL),
            "appnumber",(SELECT "refcode" FROM "vacancy" WHERE "vacancyid" = "tempshift"."vacancyid"),(SELECT "appnumber" FROM "person" WHERE "personid" = (SELECT "recipientid" FROM "vacancy" WHERE "vacancyid" = "tempshift"."vacancyid")),
            (SELECT "shiftserialnumber" FROM "tempshiftplan" WHERE "tempshiftplanid" = "tempshift"."tempshiftplanid"),0 FROM "tempshift" KEY JOIN "person" WHERE "tempshiftid" = @TempShiftID;
        IF NOT EXISTS(SELECT * FROM "psHealthVacancy" WHERE "vacancyid" = (SELECT "vacancyid" FROM "tempshift" WHERE "tempshiftid" = @TempShiftID)) THEN
          INSERT INTO "psHealthVacancy"( "psHealthVacancyID","startDateTime","Status","tenantBranchId","tenantCaseId","tenantCustomerId","transferbatch","vacancyid" ) 
            SELECT "uniquekey"('x'),"startdate",'Referred',
              "pshealthbranch"(@TempShiftID,NULL,NULL,NULL),
              "refcode",(SELECT "appnumber" FROM "person" WHERE "personid" = "recipientid"),0,"Vacancy"."vacancyid" FROM "tempshift" KEY JOIN "vacancy" WHERE "tempshiftid" = @TempShiftID
        END IF
      END IF
    ELSE IF EXISTS(SELECT * FROM "psHealthShift" WHERE "tempshiftplanid" = @TempShiftPlanID) THEN
        UPDATE "psHealthShift" SET "transferbatch" = 0 WHERE "tempshiftplanid" = @TempShiftPlanID
      ELSE
        --            this will need completing if using shiftplan
        --            this will need completing if using shiftplan
      END IF
    END IF
  END IF
END