排班思路与存储过程--排班（一）
/*
由于系统系统调取排班信息，所以安排系统进行排班处理
00网排班需求
1.共三个个班次，正常班，中班、中长班，中班随机穿插在正常班中，中长班随机穿插在中
班中。
2.CD两个分组,每个大组分四个小组，如C1,C2,C3,C4,D1,D2,D3,D4。每日需3个中班，C组2
个中班时，D组1个中班；中班人数分配一周轮换一次。中长班班所需人数共2人，从CD组各
安排一个。
3.一个月总休息天数和当月周末数相同，当周周末有上班，只能安排在相邻的两个周休息。
同一人周末两天不可连续上班。每人连续上班天数不得超过5天。
4.中长班次日必须为休息或中班。仅在附表一中人员中安排中长班(尽量均等)，附表二中人
员不可安排中长班。
5.CD组同一天休息总人数不可大于3人，周末除外。
6.周末值班人数正常班4人、中班4人、中长班2人。
7.遇节假日3天休息，值班3天班次不受周六日影响，3天所上班次相同。
主站排班表需求
如果A1、A2组今天是呼入，那A3、A4组就是呼出，呼入呼出是每天轮流的，
红人只会出现在呼出组 ，每天3人，一个月大家红人天数均等
早班班次安排1个晚班，0个长班。中班班次安排2个晚班，3个长班。晚班及长班包括主站客
服及旺旺客服总和。
1.共四个班次，早班，中班，长中班，晚班，早中班按周轮，周日到下周六为一轮，晚班随
机穿插在早中班。
2.AB两个分组,每个大组分四个小组，如A1,A2,A3,A4,B1,B2,B3,B4。A组上早班时，B组上中
班，晚班所需人数共N人（N可自行设定），从AB组各安排一半，非双数时中班安排多一个。
3.一个月总休息天数和当月周末数相同，当周周末有上班，只能安排在相邻的两个周休息。
同一人周末两天不可连续上班。
4.晚班次日必须为休息，长晚班次日必须为晚班或休息。仅在附表一中人员中安排长晚班(
尽量均等)，附表二中人员不可安排长晚班，非附表一中其他人员晚班数要求尽量均等。
5.1，2小组同一天休息人数不可大于大组人数的1/4，周末除外。3，4小组同样要求。
6.周末值班人数可自行设定。
7.遇节假日3天休息，A组3天班次不受周六日影响，3天所上班次相同。
a.根据周进行AB组早中班交替，之前有排班记录的，要接上之前最后周的来交替
b.计算每个人早中晚班上班数量，红人等属性（属性可以后续加）
*/
declare @yearmonth int
set @yearmonth=11
delete tbCustomerScheduled where yearmonth=@yearmonth
declare @maxleave int
set @maxleave=4--设置每天最大休息人数为总人数的 1/@maxleave
declare @from date
declare @end date
declare @date date
declare @i int
declare @count int
declare @worktype int
delete from tbCustomerScheduledTemp
set @from=(select fromdate from tbCustomerScheduledRecord where id=@yearmonth)
set @end=(select enddate from tbCustomerScheduledRecord where id=@yearmonth)
--begin 顺序排早中班开始，默认AB组循环上早中班，手动排班不变
set @from=(select fromdate from tbCustomerScheduledRecord where id=@yearmonth)
set @end=(select enddate from tbCustomerScheduledRecord where id=@yearmonth)
--插入手工排班班表
begin
insert into tbCustomerScheduled(customer,worktype,workday,memo,yearmonth,adddate,handinsert)
select customer,worktype,workday,memo,yearmonth,adddate,1 from tbCustomerScheduledHand sdh
where yearmonth=@yearmonth and not exists(select top 1 1 from tbCustomerScheduled sd1
where sdh.customer=sd1.customer and sd1.workday=sdh.workday and sd1.yearmonth=sdh.yearmonth)
declare @morning varchar
declare @noon varchar
set @morning=(select ISNULL((select 'A' from tbCustomerScheduledRecord where id=@yearmonth and ismorning=0),'B'))
set @noon = (select case when @morning='A' then 'B' else 'A' end)--;//B组分中班
declare @datetemp date
set @datetemp = @from;
while (@datetemp <= @end)
begin
if (select
case when datename(weekday,@datetemp)='星期日' then 0
when datename(weekday,@datetemp)='星期一' then 1
when datename(weekday,@datetemp)='星期二' then 2
when datename(weekday,@datetemp)='星期三' then 3
when datename(weekday,@datetemp)='星期四' then 4
when datename(weekday,@datetemp)='星期五' then 5
when datename(weekday,@datetemp)='星期六' then 6
else 0 end)
<
(select
case when datename(weekday,dateadd(d,-1,@datetemp))='星期日' then 0
when datename(weekday,dateadd(d,-1,@datetemp))='星期一' then 1
when datename(weekday,dateadd(d,-1,@datetemp))='星期二' then 2
when datename(weekday,dateadd(d,-1,@datetemp))='星期三' then 3
when datename(weekday,dateadd(d,-1,@datetemp))='星期四' then 4
when datename(weekday,dateadd(d,-1,@datetemp))='星期五' then 5
when datename(weekday,dateadd(d,-1,@datetemp))='星期六' then 6
else 0 end)
begin
set @morning = @noon;
if @morning='A'
set @noon='B'
else
set @noon='A'
end
select @morning
insert into tbCustomerScheduled(customer,worktype,workday,yearmonth)
select wd.customer,(select case when p.CustomerGroupBig=@morning then 1 when p.CustomerGroupBig=@noon then 2 else 1 end ),@datetemp,@yearmonth
from P_User p inner join tbCustomerScheduledWorkDay wd on p.userName=wd.customer
where wd.yearmonth=@yearmonth
and not exists(select top 1 1 from tbCustomerScheduled sd1
where wd.customer=sd1.customer and sd1.workday=@datetemp and sd1.yearmonth=@yearmonth)
set @datetemp = dateadd(d,1,@datetemp)
end
end
--end 顺序排早中班结束
--排班中班早班旺旺和中民客服分开，分两步排班
set @i=0
while @i<=DATEDIFF(d,@from,@end)
begin
set @date=DATEADD(d,@i,@from)
--begin 排中班开始
set @worktype=2
--主站客服 begin
--算出主站客服还剩多少天中班未上
set @count=(select nooncount-(select COUNT(1) from tbCustomerScheduled d where d.worktype=@worktype and d.yearmonth=@yearmonth and d.workday=tbCustomerScheduledDutyDay.workdate and handinsert=1 and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay where workdate=@date and yearmonth=@yearmonth)
delete tbCustomerScheduledTemp
if @count>0
begin
--查询出适合上中班的主站客服，且是主站客服
--中班的情况一般是周末，周末不让连着上班
--当天前一天跟当天上班类型一样或者前一天是休息才排班
--前6天不是一直上班的才排班
--前后9天没手动排过早中班的才排班（最大化防止周末连着上班）
--假期之前安排过最多的排序上班，防止假期不够排
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduled sd
where sd.worktype=@worktype and handinsert=0
and @date=sd.workday and sd.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,-1,sd.workday) and (sd1.worktype=@worktype or sd1.worktype=0))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer)
and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd.yearmonth=@yearmonth and sd1.workday between dateadd(d,-6,@date) and @date and sd1.worktype<>0)<=6
and not exists(select top 1 1 from tbCustomerScheduled sd2
where handinsert=1 and sd2.yearmonth=@yearmonth and sd.customer=sd2.customer and sd2.worktype in(1,2)
and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date))
order by (select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and sd.customer=sd1.customer) desc,NEWID()
set @count = @count-@@rowcount
--如果上面条件没找到客服或者客服不够上中班
--排查9天上班限制（周末连着上班）
if @count>0
begin
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduled sd
where sd.worktype=@worktype and handinsert=0
and @date=sd.workday and sd.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,-1,sd.workday) and (sd1.worktype=@worktype or sd1.worktype=0))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer)
and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-6,@date) and @date and sd1.worktype<>0)<=6
--and not exists(select top 1 1 from tbCustomerScheduled sd2
--where handinsert=1 and sd.customer=sd2.customer and sd2.worktype in(1,2)
--and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer)
order by (select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and sd.customer=sd1.customer) desc,NEWID()
end
update tbCustomerScheduled set worktype=@worktype,handinsert=1
where workday=@date and yearmonth=@yearmonth
and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduled set worktype=0
where workday=@date and handinsert=0 and worktype=@worktype and yearmonth=@yearmonth
and customer not in(select customer from tbCustomerScheduledTemp)
end
--主站客服 end
--end 排中班结束
--begin 排早班开始
set @worktype=1
--主站客服begin
set @count=(select morningcount-(select COUNT(1) from tbCustomerScheduled d where d.worktype=@worktype and d.workday=dd.workdate and handinsert=1 and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay dd where workdate=@date and yearmonth=@yearmonth)
delete tbCustomerScheduledTemp
if @count>0
begin
--跟中班规则一样
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduled sd
where sd.worktype=@worktype and handinsert=0
and @date=sd.workday and sd.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer
and sd1.yearmonth=@yearmonth and sd1.workday=dateadd(d,-1,sd.workday) and (sd1.worktype=@worktype or sd1.worktype=0))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer)
and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-6,@date) and @date and sd1.worktype<>0)<=6
and not exists(select top 1 1 from tbCustomerScheduled sd2
where handinsert=1 and sd2.yearmonth=@yearmonth and sd.customer=sd2.customer and sd2.worktype in(1,2,3)
and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date))
order by (select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and sd1.yearmonth=@yearmonth and sd.customer=sd1.customer) desc,NEWID()
set @count = @count-@@rowcount
--跟中班规则一样
if @count>0
begin
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduled sd
where sd.worktype=@worktype and handinsert=0
and @date=sd.workday and sd.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,-1,sd.workday) and (sd1.worktype=@worktype or sd1.worktype=0))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer)
and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-6,@date) and @date and sd1.worktype<>0)<=6
--and not exists(select top 1 1 from tbCustomerScheduled sd2
--where handinsert=1 and sd.customer=sd2.customer and sd2.worktype in(1,2,3)
--and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=sd.customer)
order by (select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and sd.customer=sd1.customer) desc,NEWID()
end
update tbCustomerScheduled set worktype=@worktype,handinsert=1
where workday=@date and yearmonth=@yearmonth
and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduled set worktype=0
where workday=@date and handinsert=0 and worktype=@worktype and yearmonth=@yearmonth
and customer not in(select customer from tbCustomerScheduledTemp)
end
--主站客服 end
--早班结束
set @i=@i+1
end
--设置每个客服该上晚班的天数
update tbCustomerScheduledWorkDay set nightdaytemp=nightday-(select COUNT(1) from tbCustomerScheduled d where d.worktype=3 and d.yearmonth=@yearmonth and d.customer=tbCustomerScheduledWorkDay.customer and yearmonth=@yearmonth) where yearmonth=@yearmonth
--begin 排晚班开始
set @i=0
while @i<=DATEDIFF(d,@from,@end)
begin
set @date=DATEADD(d,@i,@from)
set @worktype=3
set @count=(select nightcount-(select COUNT(1) from tbCustomerScheduled d where d.worktype=@worktype and d.yearmonth=@yearmonth and d.workday=tbCustomerScheduledDutyDay.workdate and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay where workdate=@date and yearmonth=@yearmonth)
delete tbCustomerScheduledTemp
if @count>0
begin
--晚班优先排在6天班之内，且6天班前后不排晚班（最大的减少6天班的存在）
--在非手工排班内，安排系统排班的早中班选择晚班
--晚班后一天且不为手工排班
--9天内上过晚班的不排晚班
--晚班还剩最多的排序，假期最少的排序
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduledWorkDay wk where
wk.nightdaytemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2)
and handinsert=0 and sd.customer=wk.customer and @date=sd.workday and sd.yearmonth=@yearmonth
and not exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,1,sd.workday) and sd1.handinsert=1))
and (((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,1,@date)
and sd1.worktype<>0)=2
and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-4,@date) and @date
and sd1.worktype<>0)=5)
or ((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,2,@date)
and sd1.worktype<>0)=3
and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-3,@date) and @date
and sd1.worktype<>0)=4)
or ((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,3,@date)
and sd1.worktype<>0)=4
and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-2,@date) and @date
and sd1.worktype<>0)=3)
or ((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between @date and dateadd(d,4,@date)
and sd1.worktype<>0)=5
and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth
and sd1.workday between dateadd(d,-1,@date) and @date
and sd1.worktype<>0)=2)
)
and not exists(select top 1 1 from tbCustomerScheduled sd2 where sd2.customer=wk.customer and sd2.yearmonth=@yearmonth
and sd2.worktype=3 and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=wk.customer)
order by nightdaytemp desc,(select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and wk.customer=sd1.customer) asc,NEWID()
set @count = @count-@@rowcount
--如果当天的还没有安排完，则往下走
--在非手工排班内，安排系统排班的早中班选择晚班
--晚班后一天且不为手工排班
--
if @count>0
begin
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduledWorkDay wk where wk.nightdaytemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and handinsert=0 and sd.customer=wk.customer and @date=sd.workday and sd.yearmonth=@yearmonth
and not exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd.workday=dateadd(d,-1,sd1.workday) and sd1.handinsert=1))
and not exists(select top 1 1 from tbCustomerScheduled sd2 where sd2.customer=wk.customer and sd2.yearmonth=@yearmonth
and sd2.worktype=3 and sd2.workday between dateadd(d,-9,@date) and dateadd(d,9,@date))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=wk.customer)
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.workday=DATEADD(d,1,@date) and sd1.yearmonth=@yearmonth and sd1.worktype=0 and sd1.handinsert<>1)
--and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.workday=dateadd(d,-5,@date) and sd1.worktype=0)
--and (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.workday between dateadd(d,-4,@date) and @date and sd1.worktype<>0)=5
order by nightdaytemp desc,(select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and wk.customer=sd1.customer) asc,NEWID()
set @count = @count-@@rowcount
if @count>0--如果当天的还没有安排完，则随机安排还剩夜班最多的客服
--if 1=2
begin
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduledWorkDay wk
where wk.nightdaytemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd
where sd.worktype in (0,1,2) and handinsert=0 and sd.yearmonth=@yearmonth
and sd.customer=wk.customer and @date=sd.workday
and not exists(select top 1 1 from tbCustomerScheduled sd1
where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd.workday=dateadd(d,-1,sd1.workday) and sd1.handinsert=1 and sd1.worktype not in(0,3)))
and not exists(select top 1 1 from tbCustomerScheduled sd2
where sd2.customer=wk.customer and sd2.yearmonth=@yearmonth
and sd2.worktype=3 and sd2.workday
between dateadd(d,-9,@date) and dateadd(d,9,@date))
and not exists(select top 1 1 from tbCustomerScheduledTemp tp1 where tp1.customer=wk.customer)
order by (select COUNT(1) from tbCustomerScheduled sd1 where sd1.worktype=0 and sd1.yearmonth=@yearmonth and wk.customer=sd1.customer) asc,nightdaytemp desc,NEWID()
end
end
update tbCustomerScheduled set worktype=@worktype,handinsert=1
where workday=@date and yearmonth=@yearmonth
and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set nightdaytemp=nightdaytemp-1
where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonth
end
set @i=@i+1
end
--end 排晚班结束
--begin 晚班之后接着休息日
update sd set worktype=0 from (select customer,dateadd(d,1,workday) workday from tbCustomerScheduled sd where worktype=3 and yearmonth=@yearmonth and dateadd(d,1,workday) between (select fromdate from tbCustomerScheduledRecord where id=@yearmonth) and (select enddate from tbCustomerScheduledRecord where id=@yearmonth))sd1 inner join tbCustomerScheduled sd on sd1.customer=sd.customer and sd1.workday=sd.workday and sd.yearmonth=@yearmonth and sd.handinsert=0
--然后计算客服还剩多少假日
update tbCustomerScheduledWorkDay set leavedaytemp=leaveday-(select COUNT(1) from tbCustomerScheduled d where d.worktype=0 and d.yearmonth=@yearmonth and d.customer=tbCustomerScheduledWorkDay.customer and d.yearmonth=@yearmonth) where yearmonth=@yearmonth
--这里处理旺旺客服的休息天数
declare @leavecustomer int
declare @sexworkcount int
--循环查找上班6天的客服，3次应该够了，如果有6天班的情况这里就要多循环几次
--或者去安排6天班的第二天休息，这里只安排第三、第四天休息
set @sexworkcount=3
while @sexworkcount>0 and exists(select top 1 1 from tbCustomerScheduled sd
where (select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-5,sd.workday) and sd.workday and sd1.worktype<>0)>5 and sd.yearmonth=@yearmonth)
begin
--排假日日期全部没安排休假的客服全部安排休假
set @i=0
while @i<=DATEDIFF(d,@from,@end)
begin
set @date=DATEADD(d,@i,@from)
set @worktype=0
--主站客服 begin
delete tbCustomerScheduledTemp
--1，2小组同一天休息人数不可大于大组人数的1/@maxleave，周末除外。3，4小组同样要求。
--查询出今天能安排的休息人数，对于周末已经安排了
--主站客服休息人数
set @leavecustomer=(select COUNT(1) from tbCustomerScheduled sd where worktype=0 and sd.workday=@date and sd.yearmonth=@yearmonth )--休息人数
--主站客服，最大可休息人数
set @count=(select COUNT(1) from tbCustomerScheduledWorkDay where yearmonth=@yearmonth)/@maxleave-@leavecustomer
--有没排班主站客服的且最大休息人数大于0就去寻找最优休息客服
if @count>0 and (select COUNT(1) from tbCustomerScheduled sd where handinsert=0 and sd.yearmonth=@yearmonth )>0
begin
--前后有连续上班6天的，如果当天不是手动排班，设置休息日。如果客服有休息日的时候
insert into tbCustomerScheduledTemp(customer)
select * from (select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
and(((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-2,@date) and @date and sd1.worktype<>0)=3
and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between @date and dateadd(d,3,@date) and sd1.worktype<>0)=4)
or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-3,@date) and @date and sd1.worktype<>0)=4
and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between @date and dateadd(d,2,@date) and sd1.worktype<>0)=3)
or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-1,@date) and @date and sd1.worktype<>0)=2
and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between @date and dateadd(d,4,@date) and sd1.worktype<>0)=5)
or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-4,@date) and @date and sd1.worktype<>0)=5
and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between @date and dateadd(d,1,@date) and sd1.worktype<>0)=2)
)
and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='A')
union
select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
and(((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-2,@date) and @date and sd1.worktype<>0)=3
and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between @date and dateadd(d,3,@date) and sd1.worktype<>0)=4)
or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-3,@date) and @date and sd1.worktype<>0)=4
and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between @date and dateadd(d,2,@date) and sd1.worktype<>0)=3)
or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-1,@date) and @date and sd1.worktype<>0)=2
and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between @date and dateadd(d,4,@date) and sd1.worktype<>0)=5)
or((select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between dateadd(d,-4,@date) and @date and sd1.worktype<>0)=5
and(select COUNT(1) from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and sd1.workday between @date and dateadd(d,1,@date) and sd1.worktype<>0)=2)
)
and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='B'))a
order by NEWID()
delete tbCustomerScheduledTemp where
customer not in(select top (@count) customer from tbCustomerScheduledTemp order by NEWID())
update tbCustomerScheduled set worktype=@worktype
where workday=@date and yearmonth=@yearmonth
and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set leavedaytemp=leavedaytemp-1
where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonth
end
--主站客服end
set @i=@i+1
end
set @sexworkcount=@sexworkcount-1
end
--end 排假日结束
--排假日日期全部没安排休假的客服全部安排休假
set @i=0
while @i<=DATEDIFF(d,@from,@end)
begin
set @date=DATEADD(d,@i,@from)
set @worktype=0
--主站客服 begin
delete tbCustomerScheduledTemp
--1，2小组同一天休息人数不可大于大组人数的1/@maxleave，周末除外。3，4小组同样要求。
--查询出今天能安排的休息人数，对于周末已经安排了
--declare @workcustomer int
set @leavecustomer=(select COUNT(1) from tbCustomerScheduled sd where worktype=0 and sd.workday=@date and sd.yearmonth=@yearmonth )--休息人数
--最大可休息人数
set @count=(select COUNT(1) from tbCustomerScheduledWorkDay where yearmonth=@yearmonth )/@maxleave-@leavecustomer
--有没排班的且最大休息人数大于0就去寻找最优休息客服
if @count>0 and (select COUNT(1) from tbCustomerScheduled where handinsert=0 and yearmonth=@yearmonth )>0
begin
--这里优先安排休息日之后再休息
insert into tbCustomerScheduledTemp(customer)
select * from (select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and (sd1.workday =DATEADD(d,-1, @date) or sd1.workday =DATEADD(d,1, @date)) and sd1.worktype=0)
and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='A')
union
select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and (sd1.workday =DATEADD(d,-1, @date) or sd1.workday =DATEADD(d,1, @date)) and sd1.worktype=0)
and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='B'))a
order by NEWID()
delete tbCustomerScheduledTemp where
customer not in(select top (@count) customer from tbCustomerScheduledTemp order by NEWID())
update tbCustomerScheduled set worktype=@worktype
where workday=@date and yearmonth=@yearmonth
and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set leavedaytemp=leavedaytemp-1
where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonth
end
--主站客服 end
set @i=@i+1
end
--end 排假日结束
--排假日日期全部没安排休假的客服全部安排休假--防止还有人没安排休假的情况，随机安排
set @i=0
while @i<=DATEDIFF(d,@from,@end)
begin
set @date=DATEADD(d,@i,@from)
set @worktype=0
--主站客服 begin
delete tbCustomerScheduledTemp
--1，2小组同一天休息人数不可大于大组人数的1/@maxleave，周末除外。3，4小组同样要求。
--查询出今天能安排的休息人数，对于周末已经安排了
--declare @workcustomer int
set @leavecustomer=(select COUNT(1) from tbCustomerScheduled sd where worktype=0 and sd.workday=@date and sd.yearmonth=@yearmonth )--休息人数
--最大可休息人数
set @count=(select COUNT(1) from tbCustomerScheduledWorkDay where yearmonth=@yearmonth )/@maxleave-@leavecustomer
--有没排班的且最大休息人数大于0就去寻找最优休息客服
if @count>0 and (select COUNT(1) from tbCustomerScheduled where handinsert=0 and yearmonth=@yearmonth )>0
begin
--这里优先安排休息日之后再休息
insert into tbCustomerScheduledTemp(customer)
select * from (select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=wk.customer and sd1.yearmonth=@yearmonth and (sd1.workday =DATEADD(d,-1, @date) or sd1.workday =DATEADD(d,1, @date)) and sd1.worktype=0)
and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='A')
union
select top (cast((round(@count/2.0,0)) as int)) customer
from tbCustomerScheduledWorkDay wk where leavedaytemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype in (1,2) and sd.yearmonth=@yearmonth and handinsert=0 and wk.customer=sd.customer and sd.workday=@date)
--and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=wk.customer and (sd1.workday =DATEADD(d,-1, @date) or sd1.workday =DATEADD(d,1, @date)) and sd1.worktype=0)
and exists(select top 1 1 from P_User u1 where u1.userName=wk.customer and u1.CustomerGroupBig='B'))a
order by NEWID()
delete tbCustomerScheduledTemp where
customer not in(select top (@count) customer from tbCustomerScheduledTemp order by NEWID())
update tbCustomerScheduled set worktype=@worktype
where workday=@date and yearmonth=@yearmonth
and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set leavedaytemp=leavedaytemp-1
where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonth
end
--主站客服 end
set @i=@i+1
end
--end 排假日结束
--中长班
begin
--设置每个客服该上中长班的天数,使用longnoontemp做临时变量使用
update tbCustomerScheduledWorkDay set longnoontemp=longnoon-(select COUNT(1) from tbCustomerScheduled d where d.longnoon<>0 and d.customer=tbCustomerScheduledWorkDay.customer and yearmonth=@yearmonth) where yearmonth=@yearmonth
set @i=0
while @i<=DATEDIFF(d,@from,@end)
begin
set @date=DATEADD(d,@i,@from)
set @count=(select longnooncount-(select COUNT(1) from tbCustomerScheduled d where d.longnoon<>0 and d.workday=tbCustomerScheduledDutyDay.workdate and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay where workdate=@date and yearmonth=@yearmonth)
delete tbCustomerScheduledTemp
if @count>0
begin
--选择当天是中班的客服
--优先排班后一天是休息的客服
--中长班还剩最多的排序
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduledWorkDay wk where
wk.longnoontemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype=2
and sd.customer=wk.customer and @date=sd.workday and sd.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd1 where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,1,sd.workday) and sd1.worktype in(0,3)))
order by longnoontemp desc,NEWID()
set @count = @count-@@rowcount
--如果当天的还没有安排完，则往下走
--在非手工排班内选择当天是中班的客服
--次之，排班，后一天是中中班的客服
--中长班还剩最多的排序
if @count>0
begin
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduledWorkDay wk where
wk.longnoontemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype=2
and sd.customer=wk.customer and @date=sd.workday and sd.yearmonth=@yearmonth
and not exists(select top 1 1 from tbCustomerScheduled sd1
where sd1.customer=sd.customer and sd1.yearmonth=@yearmonth
and sd1.workday=dateadd(d,1,sd.workday) and sd1.worktype in(0,1,3)))
order by longnoontemp desc,NEWID()
end
update tbCustomerScheduled set longnoon=1
where workday=@date and yearmonth=@yearmonth
and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set longnoontemp=longnoontemp-1
where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonth
end
set @i=@i+1
end
end
--中长班
/*
--红人 早班红人，中班红人
begin
--设置每个客服该上红人的天数,使用isredtemp做临时变量使用
update tbCustomerScheduledWorkDay set isredtemp=isred-(select COUNT(1) from tbCustomerScheduled d where d.isred<>0 and d.customer=tbCustomerScheduledWorkDay.customer and yearmonth=@yearmonth) where yearmonth=@yearmonth
set @i=0
while @i<=DATEDIFF(d,@from,@end)
begin
set @date=DATEADD(d,@i,@from)
--早班红人开始
begin
set @count=(select isredcount-(select COUNT(1) from tbCustomerScheduled d where d.isred<>0 and d.workday=tbCustomerScheduledDutyDay.workdate and d.worktype=1 and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay where workdate=@date and yearmonth=@yearmonth)
delete tbCustomerScheduledTemp
if @count>0
begin
--选择当天是早班的客服
--红人还剩最多的排序
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduledWorkDay wk where
wk.isredtemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype=1
and sd.customer=wk.customer and @date=sd.workday)
order by isredtemp desc,NEWID()
update tbCustomerScheduled set isred=1
where workday=@date and yearmonth=@yearmonth
and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set isredtemp=isredtemp-1
where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonth
end
end --早班结束
--中班红人开始
begin
set @count=(select isrednooncount-(select COUNT(1) from tbCustomerScheduled d where d.isred<>0 and d.workday=tbCustomerScheduledDutyDay.workdate and d.worktype=2 and yearmonth=@yearmonth) from tbCustomerScheduledDutyDay where workdate=@date and yearmonth=@yearmonth)
delete tbCustomerScheduledTemp
if @count>0
begin
--选择当天是中班的客服
--红人还剩最多的排序
insert into tbCustomerScheduledTemp(customer)
select top (@count) customer
from tbCustomerScheduledWorkDay wk where
wk.isredtemp>0 and wk.yearmonth=@yearmonth
and exists(select top 1 1 from tbCustomerScheduled sd where sd.worktype=2
and sd.customer=wk.customer and @date=sd.workday)
order by isredtemp desc,NEWID()
update tbCustomerScheduled set isred=1
where workday=@date and yearmonth=@yearmonth
and customer in(select customer from tbCustomerScheduledTemp)
update tbCustomerScheduledWorkDay set isredtemp=isredtemp-1
where customer in(select customer from tbCustomerScheduledTemp)
and yearmonth=@yearmonth
end
end --中班红人结束
set @i=@i+1
end
end
--红人 */
GO
支持原创：如有问题或者不同见解请联系 cheerfulhuang@yeah.net
