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

===== Original SQL =====
<code sql>
create procedure "pears"."AutoMatchProcessShift"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in @TempShiftPlanID char(20) ) 
begin
  declare @Explic smallint;
  declare @TempDeskID char(20);
  declare @VacancyID char(20);
  declare @CompanyID char(20);
  declare @ShiftDate date;
  declare @OldWorkers integer;
  declare @PercentNew integer;
  declare @NewWorkers integer;
  declare @LeadTime integer;
  declare @DivID char(20);
  declare @Urgency smallint;
  declare @Email smallint;
  declare @SMS smallint;
  declare @Push smallint;
  set @Explic = 1;
  select "v"."TempDeskID","v"."VacancyID","e"."CompanyID","c"."DivisionID","p"."ShiftDate","datediff"("hour",current timestamp,"p"."ShiftDate"+"p"."TimeFrom"),"c"."AutoMatchUrgency"
    into @TempDeskID,@VacancyID,@CompanyID,@DivID,@ShiftDate,@LeadTime,@Urgency
    from "TempShiftPlan" as "p" key join "Vacancy" as "v" key join "Employment" as "e" key join "Company" as "c" where "TempshiftPlanID" = @TempShiftPlanID;
  select first "QtyWorkers","PercentNew","Email","SMS","Push" into @OldWorkers,@PercentNew,@Email,@SMS,@Push from "AutoMatchDivisionConfig" where("divisionid" = @DivID or "divisionid" is null)
    and("urgency" = @Urgency or "urgency" is null) and(select "min"("Leadtime") from "AutoMatchDivisionConfig" as "a" where("urgency" = @Urgency or "urgency" is null)
      and("divisionid" = @DivID or "divisionid" is null) and "leadtime" > @LeadTime) = "leadtime" order by "divisionid" desc;
  set @NewWorkers = "round"(@OldWorkers*@PercentNew/100,0);
  set @OldWorkers = (@OldWorkers-@NewWorkers);
  -- put shift plan in temp table
  insert into "tempmatchplan"( "tempmatchplanid","vacancyid","tempshiftplanid","shiftdate" ) (
    select "uniquekey"("p"."tempshiftplanid"),@VacancyID,"p"."tempshiftplanid","p"."shiftdate" as "thedate"
      from "tempshiftplan" as "p" where "p"."TempShiftPlanID" = @TempShiftPlanID);
  -- put possible temps in temp table
  insert into "tempmatchcandidate"( "tempmatchcandidateid","personid","matchscore","vacancyid","highlight" ) 
    select "uniquekey"("t"."personid"),
      "t"."personid",0,@VacancyID,
      ((select first 1 from "employment" where "companyid" = @CompanyID and "personid" = "person"."personid")
      +"isnull"((select first 1 from "placement" key join "employment" where "employment"."companyid" = @CompanyID
        and "employment"."personid" = "person"."personid" and "placement"."vacancyid" = @VacancyID),0)) -- use custom function (look for existing ??)
      from "temppoolmember" as "t" key join "person"
      where "t"."tempdeskid" = @TempDeskID
      and "employeeacceptable"("person"."personid",@CompanyID,@Explic,@VacancyID) is null
      and "locate"((select "personcurrentstates" from "params"),"person"."status") > 0
      and("isnull"("person"."OnlyMatchIfKnownAvailable",0) = 0
      or "person"."personid" = any(select "personid" from "tempshift" where "state" = 'A' and "shiftdate" = @ShiftDate));
  call "MatchShifts"(@VacancyID,@Explic);
  -- add relevant candidates to the notify list (no attempt to eliminate duplicates of records already in the queue)
  -- add contactstart and end to these and use in job when populating final queue
  -- add contactstart and end to these and use in job when populating final queue
  if @OldWorkers > 0 then
    insert into "AutoMatchNotificationQueue"( "PersonID","TempShiftPlanID","NotNewRating","LeadTime","NewWorkers","QtyWorkers","Email","SMS","Push",
      "MaxEmails","MaxSMS","MaxPush","MinDelayBetweenEmails","MinDelayBetweenSMS","MinDelayBetweenPush" ) 
      select "t"."PersonID",@TempShiftPlanID,"AutoMatchShiftMatchOrder"("t"."PersonID",@TempShiftPlanID,"MatchScore",null,'Old') as "Rating",
        @LeadTime,@NewWorkers,@OldWorkers,@Email,@SMS,@Push,
        "isnull"("MaxEmails","GlobalMaxEmails"),
        "isnull"("MaxSMS","GlobalMaxSMS"),
        "isnull"("MaxPush","GlobalMaxPush"),
        "isnull"("MinDelayBetweenEmails","GlobalMinEmailGap"),
        "isnull"("MinDelayBetweenSMS","GlobalMinSMSGap"),
        "isnull"("MinDelayBetweenPush","GlobalMinPushGap")
        from "TempMatchCandidate" as "t" left outer join "AutoMatchPersonConfig" as "c" on "t"."personid" = "c"."personid" where "VacancyID" = @VacancyID and "Rating" > 0
        and "left"("tempshiftallowed"("t"."PersonID",@TempShiftPlanID,"VacancyID"),1) in( '^','' ) 
  end if;
  if @NewWorkers > 0 then
    insert into "AutoMatchNotificationQueue"( "PersonID","TempShiftPlanID","NewRating","LeadTime","NewWorkers","QtyWorkers","Email","SMS","Push",
      "MaxEmails","MaxSMS","MaxPush","MinDelayBetweenEmails","MinDelayBetweenSMS","MinDelayBetweenPush" ) 
      select "t"."PersonID",@TempShiftPlanID,"AutoMatchShiftMatchOrder"("t"."PersonID",@TempShiftPlanID,"MatchScore",null,'New') as "Rating",
        @LeadTime,@NewWorkers,@OldWorkers,@Email,@SMS,@Push,
        "isnull"("MaxEmails","GlobalMaxEmails"),
        "isnull"("MaxSMS","GlobalMaxSMS"),
        "isnull"("MaxPush","GlobalMaxPush"),
        "isnull"("MinDelayBetweenEmails","GlobalMinEmailGap"),
        "isnull"("MinDelayBetweenSMS","GlobalMinSMSGap"),
        "isnull"("MinDelayBetweenPush","GlobalMinPushGap")
        from "TempMatchCandidate" as "t" left outer join "AutoMatchPersonConfig" as "c" on "t"."personid" = "c"."personid" where "VacancyID" = @VacancyID and "Rating" > 0
        and "left"("tempshiftallowed"("t"."PersonID",@TempShiftPlanID,"VacancyID"),1) in( '^','' ) 
  end if;
  -- clear out temp tables
  truncate table "pears"."tempmatchcandidate";
  truncate table "pears"."tempmatchplan"
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."AutoMatchProcessShift" IS 
{create procedure AutoMatchProcessShift 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(in @TempShiftPlanID char(20))
begin
  declare @Explic smallint;
  declare @TempDeskID char(20);
  declare @VacancyID char(20);
  declare @CompanyID char(20);
  declare @ShiftDate date;
  declare @OldWorkers integer;
  declare @PercentNew integer;
  declare @NewWorkers integer;
  declare @LeadTime integer;
  declare @DivID char(20);
  declare @Urgency smallint;
  declare @Email smallint;
  declare @SMS smallint;
  declare @Push smallint;
  set @Explic = 1;
  select v.TempDeskID, v.VacancyID, e.CompanyID, c.DivisionID, p.ShiftDate, datediff(hour, current timestamp, p.ShiftDate+p.TimeFrom), c.AutoMatchUrgency 
 into @TempDeskID, @VacancyID, @CompanyID, @DivID, @ShiftDate, @LeadTime, @Urgency 
    from TempShiftPlan p key join Vacancy v key join Employment e key join Company c where TempshiftPlanID = @TempShiftPlanID;
  select first QtyWorkers, PercentNew, Email, SMS, Push into @OldWorkers, @PercentNew, @Email, @SMS, @Push from AutoMatchDivisionConfig where (divisionid = @DivID or divisionid is null) 
 and ("urgency" = @Urgency or "urgency" is null) and (select min(Leadtime) from AutoMatchDivisionConfig a where ("urgency" = @Urgency or "urgency" is null) 
 and (divisionid = @DivID or divisionid is null) and leadtime > @LeadTime) = leadtime  order by divisionid desc; 
  set @NewWorkers = round(@OldWorkers*@PercentNew/100,0);
  set @OldWorkers = (@OldWorkers - @NewWorkers);
-- put shift plan in temp table
  insert into tempmatchplan (tempmatchplanid,vacancyid,tempshiftplanid,shiftdate)
    (select uniquekey(p.tempshiftplanid),@VacancyID,p.tempshiftplanid,p.shiftdate as thedate
    from tempshiftplan p where p.TempShiftPlanID = @TempShiftPlanID);
-- put possible temps in temp table
  insert into tempmatchcandidate (tempmatchcandidateid,personid,matchscore,vacancyid,highlight)
    select uniquekey(t.personid),
		 t.personid,0,@VacancyID,
		 ((select first 1 from employment where companyid= @CompanyID and personid=person.personid)
		 + isnull((select first 1 from placement key join employment where employment.companyid = @CompanyID 
                  and employment.personid=person.personid and placement.vacancyid=@VacancyID),0)) -- use custom function (look for existing ??)
   from temppoolmember t key join person
   where t.tempdeskid=@TempDeskID and
		  employeeacceptable(person.personid,@CompanyID,@Explic,@VacancyID) is null and
		  locate((select personcurrentstates from params),person.status) > 0 and
		  (	isnull(person.OnlyMatchIfKnownAvailable,0)=0 or
			person.personid in (select personid from tempshift where state='A' and shiftdate = @ShiftDate));
  call MatchShifts(@VacancyID,@Explic);
-- add relevant candidates to the notify list (no attempt to eliminate duplicates of records already in the queue)
-- add contactstart and end to these and use in job when populating final queue
-- add contactstart and end to these and use in job when populating final queue
  if @OldWorkers > 0 then
    insert into AutoMatchNotificationQueue (PersonID,TempShiftPlanID,NotNewRating,LeadTime,NewWorkers,QtyWorkers,Email,SMS,Push, 
    MaxEmails, MaxSMS, MaxPush, MinDelayBetweenEmails, MinDelayBetweenSMS, MinDelayBetweenPush )
      select  t.PersonID, @TempShiftPlanID, AutoMatchShiftMatchOrder(t.PersonID ,@TempShiftPlanID,MatchScore,null,'Old') as Rating, 
      @LeadTime, @NewWorkers, @OldWorkers, @Email, @SMS, @Push, 
      isnull(MaxEmails,GlobalMaxEmails), 
      isnull(MaxSMS,GlobalMaxSMS), 
      isnull(MaxPush,GlobalMaxPush), 
      isnull(MinDelayBetweenEmails,GlobalMinEmailGap), 
      isnull(MinDelayBetweenSMS,GlobalMinSMSGap), 
      isnull(MinDelayBetweenPush,GlobalMinPushGap) 
       from TempMatchCandidate t left outer join AutoMatchPersonConfig c on t.personid = c.personid where VacancyID = @VacancyID and Rating > 0
      and left(tempshiftallowed(t.PersonID,@TempShiftPlanID,VacancyID),1) in ('^','');
  end if;
  if @NewWorkers > 0 then
    insert into AutoMatchNotificationQueue (PersonID,TempShiftPlanID,NewRating,LeadTime,NewWorkers,QtyWorkers,Email,SMS,Push, 
    MaxEmails, MaxSMS, MaxPush, MinDelayBetweenEmails, MinDelayBetweenSMS, MinDelayBetweenPush )
      select  t.PersonID, @TempShiftPlanID, AutoMatchShiftMatchOrder(t.PersonID ,@TempShiftPlanID,MatchScore,null,'New') as Rating, 
      @LeadTime, @NewWorkers, @OldWorkers, @Email, @SMS, @Push, 
      isnull(MaxEmails,GlobalMaxEmails), 
      isnull(MaxSMS,GlobalMaxSMS), 
      isnull(MaxPush,GlobalMaxPush), 
      isnull(MinDelayBetweenEmails,GlobalMinEmailGap), 
      isnull(MinDelayBetweenSMS,GlobalMinSMSGap), 
      isnull(MinDelayBetweenPush,GlobalMinPushGap) 
       from TempMatchCandidate t left outer join AutoMatchPersonConfig c on t.personid = c.personid where VacancyID = @VacancyID and Rating > 0
      and left(tempshiftallowed(t.PersonID,@TempShiftPlanID,VacancyID),1) in ('^','');
  end if;
-- clear out temp tables
  truncate table tempmatchcandidate;
  truncate table tempmatchplan;
end
}
</code>