====== pears.TempShift ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Description =====
Individual shift details - once booked / worked / billed / paid etc. Also availability.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **TempShiftID** | char(20) | NOT NULL |  |  |
| VacancyID | char(20) | NULL |  |  |
| PersonID | char(20) | NULL |  |  |
| PlacementID | char(20) | NULL |  |  |
| TempTimeSheetID | char(20) | NULL |  |  |
| ShiftDate | date | NOT NULL |  |  |
| TimeFrom | time | NULL |  |  |
| TimeTo | time | NULL |  |  |
| State | char(1) | NOT NULL |  | Temp: Available,Unavailable,Holiday; Vacancy: Unfilled,Cancelled; Placement: Provisional,Booked,Worked,Cancelled |
| Note | char(100) | NULL |  |  |
| TempShiftPlanID | char(20) | NULL |  |  |
| EssentialSkillGradeID | char(4) | NULL |  |  |
| BreakMinutes | smallint | NULL |  |  |
| ReferenceCode | char(20) | NULL |  |  |
| CancelReason | char(1) | NULL |  |  |
| WhenCancelled | timestamp | NULL |  |  |
| CBill | tinyint | NULL | 0 | Cancelled shift must still be billed. Clear when billed |
| CPay | tinyint | NULL | 0 | Cancelled shift must still be paid. Clear when paid |
| CRefill | tinyint | NULL | 0 | Cancelled shift still needs to be filled |
| ClientConfirmed | tinyint | NULL | 0 |  |
| TempConfirmed | tinyint | NULL | 0 |  |
| WhoCancelled | char(20) | NULL |  |  |
| StaffID | char(20) | NULL |  |  |
| WhenEntered | timestamp | NULL | current timestamp |  |
| ConfirmedWith | char(20) | NULL |  |  |
| TempShiftTypeID | char(2) | NULL |  |  |
| AnalysisCode | char(20) | NULL |  |  |
| RecoveryHours | smallint | NULL | 0 |  |
| EffectiveTimeTo | time | NULL |  |  |
| UnavailableReason | char(1) | NULL |  |  |
| CandidateNotified | timestamp | NULL |  |  |
| ClientNotified | timestamp | NULL |  |  |
| AvailTemplateID | char(2) | NULL |  |  |
| AWRNotes | long varchar | NULL |  |  |
| ColourOverride | smallint | NULL | 0 |  |
| TempShiftTemplateID | char(20) | NULL |  |  |
| WhenClientConfirmed | timestamp | NULL |  |  |
| WhenTempConfirmed | timestamp | NULL |  |  |
| WhoTempConfirmed | char(20) | NULL |  |  |
| WhoClientConfirmed | char(20) | NULL |  |  |
| CancelAt | timestamp | NULL |  |  |

===== Primary Key =====
  * TempShiftID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| TempShiftCancelReason | CancelReason | [[database:tables:pears_tempshiftcancelreason|pears.TempShiftCancelReason (TempShiftCancelReasonID)]] |  |
| Vacancy | VacancyID | [[database:tables:pears_vacancy|pears.vacancy (vacancyid)]] |  |
| Person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] |  |
| Placement | PlacementID | [[database:tables:pears_placement|pears.Placement (placementid)]] |  |
| TempTimesheet | TempTimeSheetID | [[database:tables:pears_temptimesheet|pears.TempTimeSheet (TempTimeSheetID)]] |  |
| TempShiftPlan | TempShiftPlanID | [[database:tables:pears_tempshiftplan|pears.TempShiftPlan (TempShiftPlanID)]] | ON DELETE SET NULL |
| Staff | StaffID | [[database:tables:pears_staff|pears.staff (staffid)]] | ON DELETE SET NULL |
| TempShiftType | TempShiftTypeID | [[database:tables:pears_tempshifttype|pears.TempShiftType (TempShiftTypeID)]] |  |
| WhoCancelled | WhoCancelled | [[database:tables:pears_staff|pears.staff (staffid)]] | ON DELETE SET NULL |
| TempShiftUnavailableReason | UnavailableReason | [[database:tables:pears_tempshiftunavailablereason|pears.TempShiftUnavailableReason (TempShiftUnavailableReasonID)]] |  |
| AvailabilityTemplate | AvailTemplateID | [[database:tables:pears_availabilitytemplate|pears.AvailabilityTemplate (AvailTemplateID)]] | ON DELETE SET NULL |
| TempShiftTemplate | TempShiftTemplateID | [[database:tables:pears_tempshifttemplate|pears.TempShiftTemplate (TempShiftTemplateID)]] |  |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_awrjobmaster|pears.AWRJobMaster]] | TempShift | TempShiftID | TempShiftID |
| [[database:tables:pears_cardreaderlog|pears.CardReaderLog]] | tempshift | MatchedToShiftID | TempShiftID |
| [[database:tables:pears_cardreadershift|pears.CardReaderShift]] | tempshift | MatchedToShiftID | TempShiftID |
| [[database:tables:pears_expensebenefitshiftitem|pears.ExpenseBenefitShiftItem]] | TempShift | TempShiftID | TempShiftID |
| [[database:tables:pears_iqacshift|pears.IQAcShift]] | TempShift | TempShiftID | TempShiftID |
| [[database:tables:pears_tempprovtimesheetline|pears.TempProvTimeSheetLine]] | TempShift | TempShiftID | TempShiftID |
| [[database:tables:pears_tempprovtimesheetshift|pears.TempProvTimeSheetShift]] | TempShift | TempShiftID | TempShiftID |
| [[database:tables:pears_tempshiftinvoice|pears.TempShiftInvoice]] | TempShift | TempShiftID | TempShiftID |
| [[database:tables:pears_temptimesheetline|pears.TempTimeSheetLine]] | TempShift | TempShiftID | TempShiftID |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| TempShift_PDate | Index | PersonID, ShiftDate |  |
| TempShift_VDate | Index | VacancyID, ShiftDate |  |
| TempShift_PStateDate | Index | PersonID, State, ShiftDate |  |
| TempShift_WhenEntered | Index | WhenEntered |  |
| TempShift_ReferenceCode | Index | ReferenceCode |  |
| TempShift_CancelAt | Index | CancelAt |  |
| TempShift_AnalysisCode | Index | AnalysisCode |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| Shift_Confirm | before | update of "ClientConfirmed", "TempConfirmed" order 1 |
| Shift_Insert | before | insert order 1 |
| TempShiftCancelled_ModifyPlan | after | update of "State" order 10 |
| TempShiftAudit | after | update of "VacancyID", "TimeFrom","TimeTo","ClientConfirmed","TempConfirmed", "BreakMinutes","RecoveryHours" order 2 |
| TempShiftCancelled_AWR | after | update of "State" order 11 |
| psHealthTempShiftInsert | after | insert order 900 |
| psHealthTempShiftUpdate | after | update of "shiftdate", "timefrom","timeto","state" order 900 |
| Shift_TrimUpdate | before | update order 13 |
| AutoMatchShiftInsertUpdate | after | insert,update order 12 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."TempShift"
-- Table comment: Individual shift details - once booked / worked / billed / paid etc. Also availability.
-- Statement count: 41

CREATE TABLE "pears"."TempShift" (
    "TempShiftID"                    char(20) NOT NULL
   ,"VacancyID"                      char(20) NULL
   ,"PersonID"                       char(20) NULL
   ,"PlacementID"                    char(20) NULL
   ,"TempTimeSheetID"                char(20) NULL
   ,"ShiftDate"                      date NOT NULL
   ,"TimeFrom"                       time NULL
   ,"TimeTo"                         time NULL
   ,"State"                          char(1) NOT NULL
   ,"Note"                           char(100) NULL
   ,"TempShiftPlanID"                char(20) NULL
   ,"EssentialSkillGradeID"          char(4) NULL
   ,"BreakMinutes"                   smallint NULL
   ,"ReferenceCode"                  char(20) NULL
   ,"CancelReason"                   char(1) NULL
   ,"WhenCancelled"                  timestamp NULL
   ,"CBill"                          tinyint NULL DEFAULT 0
   ,"CPay"                           tinyint NULL DEFAULT 0
   ,"CRefill"                        tinyint NULL DEFAULT 0
   ,"ClientConfirmed"                tinyint NULL DEFAULT 0
   ,"TempConfirmed"                  tinyint NULL DEFAULT 0
   ,"WhoCancelled"                   char(20) NULL
   ,"StaffID"                        char(20) NULL
   ,"WhenEntered"                    timestamp NULL DEFAULT current timestamp
   ,"ConfirmedWith"                  char(20) NULL
   ,"TempShiftTypeID"                char(2) NULL
   ,"AnalysisCode"                   char(20) NULL
   ,"RecoveryHours"                  smallint NULL DEFAULT 0
   ,"EffectiveTimeTo"                time NULL COMPUTE ("pears"."AddRecoveryHours"("timefrom","timeto","recoveryhours"))
   ,"UnavailableReason"              char(1) NULL
   ,"CandidateNotified"              timestamp NULL
   ,"ClientNotified"                 timestamp NULL
   ,"AvailTemplateID"                char(2) NULL
   ,"AWRNotes"                       long varchar NULL
   ,"ColourOverride"                 smallint NULL DEFAULT 0
   ,"TempShiftTemplateID"            char(20) NULL
   ,"WhenClientConfirmed"            timestamp NULL
   ,"WhenTempConfirmed"              timestamp NULL
   ,"WhoTempConfirmed"               char(20) NULL
   ,"WhoClientConfirmed"             char(20) NULL
   ,"CancelAt"                       timestamp NULL
   ,PRIMARY KEY ("TempShiftID" ASC) 
)
go


COMMENT ON COLUMN "pears"."TempShift"."State" IS 
	'Temp: Available,Unavailable,Holiday; Vacancy: Unfilled,Cancelled; Placement: Provisional,Booked,Worked,Cancelled'
go


COMMENT ON COLUMN "pears"."TempShift"."CBill" IS 
	'Cancelled shift must still be billed. Clear when billed'
go


COMMENT ON COLUMN "pears"."TempShift"."CPay" IS 
	'Cancelled shift must still be paid. Clear when paid'
go


COMMENT ON COLUMN "pears"."TempShift"."CRefill" IS 
	'Cancelled shift still needs to be filled'
go


COMMENT ON TABLE "pears"."TempShift" IS 
	'Individual shift details - once booked / worked / billed / paid etc. Also availability.'
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "TempShiftCancelReason" ("CancelReason" ASC)
    REFERENCES "pears"."TempShiftCancelReason" ("TempShiftCancelReasonID")
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "Vacancy" ("VacancyID" ASC)
    REFERENCES "pears"."vacancy" ("vacancyid")
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "Person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "Placement" ("PlacementID" ASC)
    REFERENCES "pears"."Placement" ("placementid")
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "TempTimesheet" ("TempTimeSheetID" ASC)
    REFERENCES "pears"."TempTimeSheet" ("TempTimeSheetID")
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "TempShiftPlan" ("TempShiftPlanID" ASC)
    REFERENCES "pears"."TempShiftPlan" ("TempShiftPlanID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "Staff" ("StaffID" ASC)
    REFERENCES "pears"."staff" ("staffid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "TempShiftType" ("TempShiftTypeID" ASC)
    REFERENCES "pears"."TempShiftType" ("TempShiftTypeID")
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "WhoCancelled" ("WhoCancelled" ASC)
    REFERENCES "pears"."staff" ("staffid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "TempShiftUnavailableReason" ("UnavailableReason" ASC)
    REFERENCES "pears"."TempShiftUnavailableReason" ("TempShiftUnavailableReasonID")
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "AvailabilityTemplate" ("AvailTemplateID" ASC)
    REFERENCES "pears"."AvailabilityTemplate" ("AvailTemplateID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."TempShift"
    ADD FOREIGN KEY "TempShiftTemplate" ("TempShiftTemplateID" ASC)
    REFERENCES "pears"."TempShiftTemplate" ("TempShiftTemplateID")
go


CREATE INDEX "TempShift_PDate" ON "pears"."TempShift"
    ( "PersonID","ShiftDate" )
go


CREATE INDEX "TempShift_VDate" ON "pears"."TempShift"
    ( "VacancyID","ShiftDate" )
go


CREATE INDEX "TempShift_PStateDate" ON "pears"."TempShift"
    ( "PersonID","State","ShiftDate" )
go


CREATE INDEX "TempShift_WhenEntered" ON "pears"."TempShift"
    ( "WhenEntered" )
go


CREATE INDEX "TempShift_ReferenceCode" ON "pears"."TempShift"
    ( "ReferenceCode" )
go


CREATE INDEX "TempShift_CancelAt" ON "pears"."TempShift"
    ( "CancelAt" )
go


CREATE INDEX "TempShift_AnalysisCode" ON "pears"."TempShift"
    ( "AnalysisCode" )
go


create trigger "Shift_Confirm" before update of "ClientConfirmed",
"TempConfirmed" order 1 on "pears"."TempShift"
referencing new as "new_shift"
for each row
begin
  call "TempShiftConfirmationChange"("new_shift"."tempshiftid","new_shift"."vacancyid","new_shift"."personid","new_shift"."shiftdate","new_shift"."clientconfirmed","new_shift"."tempconfirmed");
  if "new_shift"."state" = 'P' and "new_shift"."clientconfirmed" = 1 and "new_shift"."tempconfirmed" = 1 then
    set "new_shift"."state" = 'B'
  else
    if "new_shift"."state" = 'B' and("isnull"("new_shift"."clientconfirmed",0) = 0 or "isnull"("new_shift"."tempconfirmed",0) = 0) then
      set "new_shift"."state" = 'P'
    end if end if;
  if update("ClientConfirmed") then
    if "varexists"('userstaffid') <> 0 then
      set "new_shift"."whoclientconfirmed" = "userstaffid"
    end if;
    set "new_shift"."whenclientconfirmed" = current timestamp
  end if;
  if update("TempConfirmed") then
    if "varexists"('userstaffid') <> 0 then
      set "new_shift"."whotempconfirmed" = "userstaffid"
    end if;
    set "new_shift"."whentempconfirmed" = current timestamp
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempShift"."Shift_Confirm" IS 
{create trigger Shift_Confirm 
 before update of ClientConfirmed,
TempConfirmed order 1 on pears.TempShift
referencing new as new_shift
for each row
begin
  call TempShiftConfirmationChange(new_shift.tempshiftid,new_shift.vacancyid,new_shift.personid,new_shift.shiftdate,new_shift.clientconfirmed,new_shift.tempconfirmed);
  if new_shift.state = 'P' and new_shift.clientconfirmed = 1 and new_shift.tempconfirmed = 1 then
    set new_shift.state='B'
  else
    if new_shift.state = 'B' and(isnull(new_shift.clientconfirmed,0) = 0 or isnull(new_shift.tempconfirmed,0) = 0) then
      set new_shift.state='P'
    end if
  end if;
      if update(ClientConfirmed) then
        if varexists('userstaffid') <> 0 then
           set new_shift.whoclientconfirmed=userstaffid
        end if;
        set new_shift.whenclientconfirmed=current timestamp
      end if;
      if update(TempConfirmed) then
        if varexists('userstaffid') <> 0 then
           set new_shift.whotempconfirmed=userstaffid
        end if;
        set new_shift.whentempconfirmed=current timestamp
      end if;
end
}
go


create trigger "Shift_Insert" before insert order 1 on
"pears"."TempShift"
referencing new as "new_shift"
for each row
begin
  set "new_shift"."referencecode" = "trim"("new_shift"."referencecode");
  call "TempShiftConfirmationChange"("new_shift"."tempshiftid","new_shift"."vacancyid","new_shift"."personid","new_shift"."shiftdate","new_shift"."clientconfirmed","new_shift"."tempconfirmed");
  if "new_shift"."staffid" is null then
    set "new_shift"."staffid" = "userstaffid"
  end if;
  if("wpkmaintaingetswitchvalue"('AWRVISIBLE','','L') = 'Y') and(("new_shift"."state" = 'U') or("new_shift"."state" = 'H')) then
    if "new_shift"."state" = 'H' then
      call "AWRInsertWeekly"("new_shift"."shiftdate","new_shift"."shiftdate",'H','H',"new_shift"."personid",null,"new_shift"."AWRnotes")
    end if;
    if("new_shift"."state" = 'U') and exists(select "tempshiftunavailablereasonid" from "tempshiftunavailablereason" where "awraction" <> 0 and "tempshiftunavailablereasonid" = "new_shift"."unavailablereason") then
      call "AWRInsertWeekly"("new_shift"."shiftdate","new_shift"."shiftdate","new_shift"."unavailablereason",'P',"new_shift"."personid",null,"new_shift"."AWRnotes")
    end if
  end if
exception
  when others then set "new_shift"."staffid" = null
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempShift"."Shift_Insert" IS 
{create trigger Shift_Insert 
 before insert order 1 on
pears.TempShift
referencing new as new_shift
for each row
begin
  set new_shift.referencecode = trim(new_shift.referencecode);
  call TempShiftConfirmationChange(new_shift.tempshiftid,new_shift.vacancyid,new_shift.personid,new_shift.shiftdate,new_shift.clientconfirmed,new_shift.tempconfirmed);
  if new_shift.staffid is null then
    set new_shift.staffid=userstaffid
  end if;
  if (wpkmaintaingetswitchvalue('AWRVISIBLE','','L')= 'Y') and((new_shift.state = 'U') or(new_shift.state = 'H')) then
    if new_shift.state = 'H' then
      call AWRInsertWeekly(new_shift.shiftdate,new_shift.shiftdate,'H','H',new_shift.personid,null,new_shift.AWRnotes)
    end if;
    if(new_shift.state = 'U') and exists(select tempshiftunavailablereasonid from tempshiftunavailablereason where awraction <> 0 and tempshiftunavailablereasonid = new_shift.unavailablereason) then
      call AWRInsertWeekly(new_shift.shiftdate,new_shift.shiftdate,new_shift.unavailablereason,'P',new_shift.personid,null,new_shift.AWRnotes)
    end if
  end if
exception
  when others then
    set new_shift.staffid=null
end
}
go


create trigger "TempShiftCancelled_ModifyPlan" after update of "State"
order 10 on "pears"."TempShift"
referencing old as "OldShift" new as "NewShift"
for each row
when("NewShift"."State" = 'C' and "OldShift"."State" in( 'B','P' ) and "NewShift"."CReFill" = 1)
begin
  declare @AdjustPlan smallint;
  declare @PlanTimeFrom time;
  declare @PlanTimeTo time;
  declare @PlanBreakMinutes smallint;
  declare @PlanRecoveryHours smallint;
  set @AdjustPlan = (select "AdjustPlanOfCancelledShifts" from "TempDesk" key join "Vacancy" where "Vacancy"."VacancyID" = "NewShift"."VacancyID");
  if @AdjustPlan = 1 then
    select "TimeFrom","TimeTo","BreakMinutes","RecoveryHours" into @PlanTimeFrom,@PlanTimeTo,@PlanBreakMinutes,@PlanRecoveryHours from "TempShiftPlan" where "NewShift"."TempShiftPlanID" = "TempShiftPlan"."TempShiftPlanID";
    if "NewShift"."TimeFrom" <> @PlanTimeFrom or "NewShift"."TimeTo" <> @PlanTimeTo or "isnull"("NewShift"."BreakMinutes",0) <> "isnull"(@PlanBreakMinutes,0) or "isnull"("NewShift"."RecoveryHours",0) <> "isnull"(@PlanRecoveryHours,0) then
      update "TempShiftPlan"
        set "TimeFrom" = "NewShift"."TimeFrom","TimeTo" = "NewShift"."TimeTo","BreakMinutes" = "NewShift"."BreakMinutes","RecoveryHours" = "NewShift"."RecoveryHours"
        where "TempShiftPlan"."TempShiftPlanID" = "NewShift"."TempShiftPlanID";
      call "AuditLog"('SHIFTPLAN',"NewShift"."TempShiftPlanID",'Shift Plan modified to match cancelled shift',
      "string"('Time From: ',@PlanTimeFrom,', Time To: ',@PlanTimeTo,', Break: ',@PlanBreakMinutes,', Recovery: ',@PlanRecoveryHours),
      "string"('Time From: ',"NewShift"."TimeFrom",', Time To: ',"NewShift"."TimeTo",', Break: ',"NewShift"."BreakMinutes",', Recovery: ',"NewShift"."RecoveryHours"))
    end if
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempShift"."TempShiftCancelled_ModifyPlan" IS 
{create trigger TempShiftCancelled_ModifyPlan 
after update of State
order 10 on pears.TempShift
referencing old as OldShift new as NewShift
for each row
when(NewShift.State = 'C' and OldShift.State in( 'B','P') and NewShift.CReFill = 1)
begin
  declare @AdjustPlan smallint;
  declare @PlanTimeFrom time;
  declare @PlanTimeTo time;
  declare @PlanBreakMinutes smallint;
  declare @PlanRecoveryHours smallint;
  set @AdjustPlan=(select AdjustPlanOfCancelledShifts from TempDesk key join Vacancy where Vacancy.VacancyID = NewShift.VacancyID);
  if @AdjustPlan = 1 then
    select TimeFrom,TimeTo,BreakMinutes,RecoveryHours into @PlanTimeFrom,@PlanTimeTo,@PlanBreakMinutes,@PlanRecoveryHours from TempShiftPlan where NewShift.TempShiftPlanID = TempShiftPlan.TempShiftPlanID;
    if NewShift.TimeFrom <> @PlanTimeFrom or NewShift.TimeTo <> @PlanTimeTo or isnull(NewShift.BreakMinutes,0) <> isnull(@PlanBreakMinutes,0) or isnull(NewShift.RecoveryHours,0) <> isnull(@PlanRecoveryHours,0) then
      update TempShiftPlan set
        TimeFrom = NewShift.TimeFrom,TimeTo = NewShift.TimeTo,BreakMinutes = NewShift.BreakMinutes,RecoveryHours = NewShift.RecoveryHours where
        TempShiftPlan.TempShiftPlanID = NewShift.TempShiftPlanID;
      call AuditLog('SHIFTPLAN',NewShift.TempShiftPlanID,'Shift Plan modified to match cancelled shift',
      string('Time From: ',@PlanTimeFrom,', Time To: ',@PlanTimeTo,', Break: ',@PlanBreakMinutes,', Recovery: ',@PlanRecoveryHours),
      string('Time From: ',NewShift.TimeFrom,', Time To: ',NewShift.TimeTo,', Break: ',NewShift.BreakMinutes,', Recovery: ',NewShift.RecoveryHours))
    end if
  end if
end
}
go


create trigger "TempShiftAudit" after update of "VacancyID",
"TimeFrom","TimeTo","ClientConfirmed","TempConfirmed",
"BreakMinutes","RecoveryHours" order 2 on "pears"."TempShift"
referencing old as "OldShift" new as "NewShift"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Shift' and "AuditFlag" = 1))
begin
  declare "AuditList" long varchar;
  declare "NewRefCode" char(20);
  declare "OldRefCode" char(20);
  declare "SerialNo" char(20);
  declare "NewConfirm" char(60);
  declare "OldConfirm" char(60);
  select "string"(',',"list"("ItemName"),',') into "AuditList" from "AuditItems" where "AreaName" = 'SHIFT' and "AuditFlag" = 1;
  select "ShiftSerialNumber" into "SerialNo" from "tempshiftplan" where "tempshiftplanid" = "NewShift"."TempShiftPlanID";
  select "RefCode" into "NewRefCode" from "Vacancy" where "Vacancy"."VacancyID" = "NewShift"."VacancyID";
  if "locate"("AuditList",',Temp Confirmed,') > 0 then
    if update("TempConfirmed") then
      select "Name" into "OldConfirm" from "staff" where "staffID" = "OldShift"."WhoTempConfirmed";
      select "Name" into "NewConfirm" from "staff" where "staffID" = "NewShift"."WhoTempConfirmed";
      call "AuditLog"('SHIFT',"NewShift"."TempShiftID",'Shift ' || "SerialNo" || ' Temp Confirmed edited OurRef: ' || "NewRefCode",
      "string"("OldShift"."TempConfirmed",', ',"Dateformat"("OldShift"."WhenTempConfirmed",'dd/mm/yy hh:nn:ss'),', ',"OldConfirm"),
      "string"("NewShift"."TempConfirmed",', ',"Dateformat"("NewShift"."WhenTempConfirmed",'dd/mm/yy hh:nn:ss'),', ',"NewConfirm"))
    end if
  end if;
  if "locate"("AuditList",',Client Confirmed,') > 0 then
    if update("ClientConfirmed") then
      select "Name" into "OldConfirm" from "staff" where "staffID" = "OldShift"."WhoClientConfirmed";
      select "Name" into "NewConfirm" from "staff" where "staffID" = "NewShift"."WhoClientConfirmed";
      call "AuditLog"('SHIFT',"NewShift"."TempShiftID",'Shift ' || "SerialNo" || ' Client Confirmed edited OurRef: ' || "NewRefCode",
      "string"("OldShift"."ClientConfirmed",', ',"Dateformat"("OldShift"."WhenClientConfirmed",'dd/mm/yy hh:nn:ss'),', ',"OldConfirm"),
      "string"("NewShift"."ClientConfirmed",', ',"Dateformat"("NewShift"."WhenClientConfirmed",'dd/mm/yy hh:nn:ss'),', ',"NewConfirm"))
    end if
  end if;
  if "locate"("AuditList",',Moving,') > 0 and update("VacancyID") then
    select "RefCode" into "OldRefCode" from "Vacancy" where "Vacancy"."VacancyID" = "OldShift"."VacancyID";
    call "AuditLog"('SHIFT',"NewShift"."TempShiftID",'Shift ' || "SerialNo" || ' moved between vacancies',
    "string"('Original OurRef: ',"OldRefCode",', Original VacancyID: ',"OldShift"."VacancyID"),
    "string"('New OurRef: ',"NewRefCode",', New VacancyID: ',"NewShift"."VacancyID"))
  else
    if "locate"("AuditList",',Times,') > 0 then
      if update("RecoveryHours") then
        call "AuditLog"('SHIFT',"NewShift"."TempShiftID",'Shift ' || "SerialNo" || ' Recovery Hours edited OurRef: ' || "NewRefCode",
        "OldShift"."RecoveryHours",
        "NewShift"."RecoveryHours")
      end if;
      if update("BreakMinutes") then
        call "AuditLog"('SHIFT',"NewShift"."TempShiftID",'Shift ' || "SerialNo" || ' Break Minutes edited OurRef: ' || "NewRefCode",
        "OldShift"."BreakMinutes",
        "NewShift"."BreakMinutes")
      end if;
      if update("TimeFrom") then
        call "AuditLog"('SHIFT',"NewShift"."TempShiftID",'Shift ' || "SerialNo" || ' From edited OurRef: ' || "NewRefCode",
        "OldShift"."TimeFrom",
        "NewShift"."TimeFrom")
      end if;
      if update("TimeTo") then
        call "AuditLog"('SHIFT',"NewShift"."TempShiftID",'Shift ' || "SerialNo" || ' To edited OurRef: ' || "NewRefCode",
        "OldShift"."TimeTo",
        "NewShift"."TimeTo")
      end if
    end if
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempShift"."TempShiftAudit" IS 
{create trigger TempShiftAudit 
 after update of VacancyID,
TimeFrom,TimeTo,ClientConfirmed,TempConfirmed,
BreakMinutes,RecoveryHours order 2 on pears.TempShift
referencing old as OldShift new as NewShift
for each row
when(exists(select * from AuditItems where AreaName = 'Shift' and AuditFlag = 1))
begin
  declare AuditList long varchar;
  declare NewRefCode char(20);
  declare OldRefCode char(20);
  declare SerialNo char(20);
  declare NewConfirm char(60);
  declare OldConfirm char(60);
  select string(',',list(ItemName),',') into AuditList from AuditItems where AreaName = 'SHIFT' and AuditFlag = 1;
  select ShiftSerialNumber into SerialNo from tempshiftplan where tempshiftplanid = NewShift.TempShiftPlanID; 
  select RefCode into NewRefCode from Vacancy where Vacancy.VacancyID = NewShift.VacancyID;
    if locate(AuditList,',Temp Confirmed,') > 0 then
      if update(TempConfirmed) then
        select Name into OldConfirm from staff where staffID = OldShift.WhoTempConfirmed;
        select Name into NewConfirm from staff where staffID = NewShift.WhoTempConfirmed;
        call AuditLog('SHIFT',NewShift.TempShiftID,'Shift '||SerialNo||' Temp Confirmed edited OurRef: ' || NewRefCode,
        string(OldShift.TempConfirmed,', ',Dateformat(OldShift.WhenTempConfirmed,'dd/mm/yy hh:nn:ss'),', ',OldConfirm),
        string(NewShift.TempConfirmed,', ',Dateformat(NewShift.WhenTempConfirmed,'dd/mm/yy hh:nn:ss'),', ',NewConfirm))
      end if;
    end if;
    if locate(AuditList,',Client Confirmed,') > 0 then
      if update(ClientConfirmed) then
        select Name into OldConfirm from staff where staffID = OldShift.WhoClientConfirmed;
        select Name into NewConfirm from staff where staffID = NewShift.WhoClientConfirmed;
        call AuditLog('SHIFT',NewShift.TempShiftID,'Shift '||SerialNo||' Client Confirmed edited OurRef: ' || NewRefCode,
        string(OldShift.ClientConfirmed,', ',Dateformat(OldShift.WhenClientConfirmed,'dd/mm/yy hh:nn:ss'),', ',OldConfirm),
        string(NewShift.ClientConfirmed,', ',Dateformat(NewShift.WhenClientConfirmed,'dd/mm/yy hh:nn:ss'),', ',NewConfirm))
      end if;
    end if;
  if locate(AuditList,',Moving,') > 0 and update(VacancyID) then
    select RefCode into OldRefCode from Vacancy where Vacancy.VacancyID = OldShift.VacancyID;
    call AuditLog('SHIFT',NewShift.TempShiftID,'Shift '||SerialNo||' moved between vacancies',
    string('Original OurRef: ',OldRefCode,', Original VacancyID: ',OldShift.VacancyID),
    string('New OurRef: ',NewRefCode,', New VacancyID: ',NewShift.VacancyID))
  else
    if locate(AuditList,',Times,') > 0 then
      if update(RecoveryHours) then
        call AuditLog('SHIFT',NewShift.TempShiftID,'Shift '||SerialNo||' Recovery Hours edited OurRef: ' || NewRefCode,
        OldShift.RecoveryHours,
        NewShift.RecoveryHours)
      end if;
      if update(BreakMinutes) then
        call AuditLog('SHIFT',NewShift.TempShiftID,'Shift '||SerialNo||' Break Minutes edited OurRef: ' || NewRefCode,
        OldShift.BreakMinutes,
        NewShift.BreakMinutes)
      end if;
      if update(TimeFrom) then
        call AuditLog('SHIFT',NewShift.TempShiftID,'Shift '||SerialNo||' From edited OurRef: ' || NewRefCode,
        OldShift.TimeFrom,
        NewShift.TimeFrom)
      end if;
      if update(TimeTo) then
        call AuditLog('SHIFT',NewShift.TempShiftID,'Shift '||SerialNo||' To edited OurRef: ' || NewRefCode,
        OldShift.TimeTo,
        NewShift.TimeTo)
      end if
    end if
  end if
end
}
go


create trigger "TempShiftCancelled_AWR" after update of "State"
order 11 on "pears"."TempShift"
referencing old as "OldShift" new as "NewShift"
for each row
when("NewShift"."State" = 'C')
begin
  call "AWRShiftCancel"("NewShift"."TempShiftID")
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempShift"."TempShiftCancelled_AWR" IS 
{create trigger TempShiftCancelled_AWR 
after update of State
order 11 on pears.TempShift
referencing old as OldShift new as NewShift
for each row
when (NewShift.State = 'C') 
begin
  call AWRShiftCancel(NewShift.TempShiftID)
end
}
go


create trigger "psHealthTempShiftInsert" after insert order 900 on
"pears"."tempshift"
referencing new as "new_ts"
for each row
begin
  if "psHealthCanSendShift"("new_ts"."tempshiftid",null) = 1 then
    call "psHealthInsertUpdateShift"("new_ts"."tempshiftid",null)
  end if
end
go


create trigger "psHealthTempShiftUpdate" after update of "shiftdate",
"timefrom","timeto","state" order 900 on "pears"."tempshift"
referencing new as "new_ts"
for each row
begin
  if "psHealthCanSendShift"("new_ts"."tempshiftid",null) = 1 then
    call "psHealthInsertUpdateShift"("new_ts"."tempshiftid",null)
  end if
end
go


create trigger "Shift_TrimUpdate" before update order 13 on
"pears"."TempShift"
referencing new as "new_shift"
for each row
begin
  set "new_shift"."referencecode" = "trim"("new_shift"."referencecode")
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempShift"."Shift_TrimUpdate" IS 
{create trigger Shift_TrimUpdate 
 before update order 13 on
pears.TempShift
referencing  new as new_shift
for each row
begin
  set new_shift.referencecode = trim(new_shift.referencecode)
end
}
go


create trigger "AutoMatchShiftInsertUpdate" after insert,update order 12 on
"pears"."TempShift"
referencing new as "New_Shift"
for each row
when("AutoMatchingEnabled" = 'Y')
begin
  -- This trigger deals with filling and cancelling
  if(select "TempDesk"."AutoMatchOn" from "TempDesk" key join "Vacancy" as "v" where "v"."VacancyID" = "New_Shift"."VacancyID") = 1 then
    call "AutoMatchRemoveShiftFromQueue"("New_Shift"."TempShiftPlanID","New_Shift"."state","New_Shift"."CRefill")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempShift"."AutoMatchShiftInsertUpdate" IS 
{create trigger AutoMatchShiftInsertUpdate 
 AFTER INSERT, UPDATE
ORDER 12 ON "pears"."TempShift"
REFERENCING NEW as New_Shift
FOR EACH ROW
WHEN (AutoMatchingEnabled = 'Y')
BEGIN
-- This trigger deals with filling and cancelling
if (select TempDesk.AutoMatchOn from TempDesk key join Vacancy v where v.VacancyID = New_Shift.VacancyID) = 1
 then call AutoMatchRemoveShiftFromQueue(New_Shift.TempShiftPlanID,New_Shift.state, New_Shift.CRefill);
end if;
END
}
go
</code>