Storage Process of duty management

The requirements are as follows:

A group of n members has four roles: Leaders, men, aunts, and drivers. N people lined up in order according to their own roles

2 duty requirements: one manager is on duty from Monday to Sunday, one driver is on duty, one man is on duty from Monday to Sunday, and two aunts are on duty on Saturday and Sunday morning; every day during the holidays, one manager, one driver, one man, two aunts in the morning and afternoon.

3. the queue can be added, deleted, and queried. The personnel sequence can be adjusted. When the queue changes, the duty schedule is automatically updated.

4. the queue personnel are required to be able to shift the personnel in the column at any time not to participate in this round of sorting (business trip or leave). The next round will continue to be sorted by queue order. After the personnel is called, the queue will automatically replace the former.

5 shift change...

Create two tables

1 watching

[Datetime] date [weekday] week [leaderid] lead id [maleid] MAN id [female1] Aunt 1id [female2] Aunt 2id [driverid] driver id [mark] remarks

2 watching_person

[Ordercode] personnel no. [personid] Personnel id [part] Personnel role [leave] whether to leave [mark] remarks

Part is the personnel role 1 leads 2 man 3 aunt 4 Driver

When a new queue is generated, the duty schedule from tomorrow to the next day (30 days here) is required ), then, pass the starting position in line with the role to the storage process (that is, the number of the drivers starting from the number of the men's aunts starting from the number ...)

Create proc Proc_WatchingSetup
-- The parameter is the starting position of the four roles.
@ Leader int,
@ Male int,
@ Female int,
@ Driver int
As

Declare @ I int -- Counter
Declare @ j int
Declare @ PersonID int
Declare @ weekday int
Declare @ InsertPoint datetime
Declare @ msg char (20)

Set @ I = 1
Set @ j = 1

-- Start transaction
Begin tran ReChange
-- Delete records after tomorrow (the queue has changed and deleted the previous records)
Delete from Watching where [Datetime]> GetDate ()

If (@ error <> 0)
Begin
Rollback tran
Set @ msg = 'error1'
Return
End

-- The date and week of the last 30 days after the insertion.
While @ I <= 30
Begin
Insert Watching (Datetime, WeekDay) values (dateadd (day, @ I, {fn curdate ()}), datepart (weekday, dateadd (day, @ I, {fn curdate ()})))
Set @ I = @ I + 1
End

If (@ error! = 0)
Begin
Rollback tran
Set @ msg = 'error2'
Return
End

-- Start to use cursor

Set @ j = 1

-- /// First, read the queue of the leader in the queue order.

Declare cur_watchingPerson scroll cursor
Select Personid from watching_person where part = 1 order by orderCode asc

Open cur_watchingPerson

-- Move to the starting position
Fetch absolute @ leader from cur_watchingPerson into @ PersonID
If @ fetch_status =-1
Fetch first from cur_watchingperson into @ PersonID

Set @ I = 1

While @ I <= 30
Begin

While @ j <= 7 -- the maximum value may be 1 person inserted for 7 days
Begin
Update watching set LeaderId = @ PersonID where [datetime] = (dateadd (day, @ I, {fn curdate ()}))
If (@ error! = 0)
Begin
Rollback tran
Set @ msg = 'error3'
Return
End
-- If it is less than 7 days, it will end at the weekend.
Select @ weekday = datepart (weekday, dateadd (day, @ I, {fn curdate ()}))
Set @ I = @ I + 1
If (@ weekday = 1)
Break

End

Set @ j = 1

Fetch next from cur_watchingperson into @ PersonID
-- If the number of backend queues exceeds the boundary
If @ fetch_status =-1
Fetch first from cur_watchingperson into @ PersonID
End

Close cur_watchingPerson
Deallocate cur_watchingPerson

-- // The driver is very superior and identical
Declare cur_watchingPerson4 scroll cursor
Select Personid from watching_person where part = 4 order by orderCode asc

Open cur_watchingPerson4

-- Move to the starting position
Fetch absolute @ driver from cur_watchingPerson4 into @ PersonID
If @ fetch_status =-1
Fetch first from cur_watchingperson4 into @ PersonID

Set @ I = 1

While @ I <= 30
Begin
While @ j <= 7 -- the maximum value may be 1 person inserted for 7 days
Begin
Update watching set driverId = @ PersonID where [datetime] = (dateadd (day, @ I, {fn curdate ()}))
If (@ error! = 0)
Begin
-- Rollback tran
Set @ msg = 'error3'
Return
End

Select @ weekday = datepart (weekday, dateadd (day, @ I, {fn curdate ()}))
Set @ I = @ I + 1
If (@ weekday = 1)
Break

End

Set @ j = 1
Fetch next from cur_watchingperson4 into @ PersonID
-- If the number of backend queues exceeds the boundary
If @ fetch_status =-1
Fetch first from cur_watchingperson4 into @ PersonID
End

Close cur_watchingPerson4
Deallocate cur_watchingPerson4

--///////////

-- It is relatively easy for a man to work for one night shift every day
Declare cur_watchingPerson2 scroll cursor
Select Personid from watching_person where part = 2 order by orderCode asc

Open cur_watchingPerson2

-- Move to the starting position
Fetch absolute @ male from cur_watchingPerson2 into @ PersonID
If @ fetch_status =-1
Fetch first from cur_watchingperson2 into @ PersonID

Set @ I = 1

While @ I <= 30
Begin
 
Update watching set MaleId = @ PersonID where [datetime] = (dateadd (day, @ I, {fn curdate ()}))
If (@ error! = 0)
Begin
Rollback tran
Set @ msg = 'error3'
Return
End

Set @ I = @ I + 1

Fetch next from cur_watchingperson2 into @ PersonID
-- If the number of backend queues exceeds the boundary
If @ fetch_status =-1
Fetch first from cur_watchingperson2 into @ PersonID
 
End

Close cur_watchingPerson2
Deallocate cur_watchingPerson2

-- Two Dayu employees are on duty every Saturday and Sunday.
Declare cur_watchingPerson3 scroll cursor
Select Personid from watching_person where part = 3 order by orderCode asc

Open cur_watchingPerson3

Fetch absolute @ female from cur_watchingPerson3 into @ PersonID
If @ fetch_status =-1
Fetch first from cur_watchingperson3 into @ PersonID

Set @ I = 1

While @ I <= 30
Begin

Select @ weekday = [weekday] from watching where [datetime] = (dateadd (day, @ I, {fn curdate ()}))

-- Determine that only two persons are on duty within half a day of the weekend
If @ weekday = 7 or @ weekday = 1
Begin

-- Insert first
Update watching set Female1 = @ PersonID where [datetime] = (dateadd (day, @ I, {fn curdate ()}))

If (@ error! = 0)
Begin
Rollback tran
Set @ msg = 'error3'
Return
End

Fetch next from cur_watchingperson3 into @ PersonID
-- If the number of backend queues exceeds the boundary
If @ fetch_status =-1
Fetch first from cur_watchingperson3 into @ PersonID
-- Insert the second digit
Update watching set Female2 = @ PersonID where [datetime] = (dateadd (day, @ I, {fn curdate ()}))

If (@ error! = 0)
Begin
-- Rollback tran
Set @ msg = 'error3'
Return
End

End

Set @ I = @ I + 1
Fetch next from cur_watchingperson3 into @ PersonID
-- If the number of backend queues exceeds the boundary
If @ fetch_status =-1
Fetch first from cur_watchingperson3 into @ PersonID
 
End

Close cur_watchingPerson3
Deallocate cur_watchingPerson3

Commit tran

The above is the stored procedure for the new duty schedule generated when the queue changes

Others, such as holiday adjustment staff, are too similar to those who welcome criticism and correction.