Here’s the script; unfortunately, I have no way to simplify it.
Lines 180-190 contain the ones that change the algorithm’s behavior.
Summary
using XLSX, Dates , CSV, Tables, DataFrames, JuMP, HiGHS
function sol_jump(mese,y)
function getdata(path,sh,rngData)
xf = XLSX.readxlsx(path)
#sn=XLSX.sheetnames(xf)
data=xf[sh][rngData]
replace(data, missing=>".")
end
function weekends(m,y)
d=Date(Dates.Month(m), Dates.Year(y))
#.(filter(d->dayofweek(d)==6||dayofweek(d)==7, d:lastdayofmonth(d)))
day.(filter(d->Dates.issaturday(d)||Dates.issunday(d), d:lastdayofmonth(d)))
end
function saturdays(m,y)
d=Date(Dates.Month(m), Dates.Year(y))
day.(filter(d->dayofweek(d)==6, d:lastdayofmonth(d)))
end
function mondays(m,y)
d=Date(Dates.Month(m), Dates.Year(y))
day.(filter(d->dayofweek(d)==1, d:lastdayofmonth(d)))
end
function MNDS(m,y)
d=Date(Dates.Month(m), Dates.Year(y))
starts=max(d,firstdayofweek(d))
mondays=filter(d->dayofweek(d)==1, d:lastdayofmonth(d))
ends=tonext.(d->dayofweek(d) == Dates.Friday, mondays)
ends[end]=min(ends[end], lastdayofmonth(d))
(:).(day.(mondays),day.(ends))
end
wks=MNDS(mese,y)
#controllare se funziona per i diversi mesi.
# se nel mese c'è prima un venerdì di un lunedì?
# se lunedi+ 4 supera 31?
# ecc
ops=["BAT",
"CAVA",
"CEL",
"DE_V",
"EMO",
"GAT",
"LAN",
"LUO",
"MAL",
"MAN",
"PAG",
"RIZ",
"SOL"
]
BAT = findfirst(==( "BAT"), ops,) # 1
CEL = findfirst(==( "CEL"), ops,) # 3
DE_V = findfirst(==( "DE_V"), ops,) # 4
EMO = findfirst(==( "EMO"), ops,) # 5
GAT = findfirst(==( "GAT"), ops,) # 6
LUO = findfirst(==( "LUO"), ops,) # 8
MAN = findfirst(==( "MAN"), ops,) # 10
PAG = findfirst(==( "PAG"), ops,) # 11
nr = length(ops)
ops_lim=setdiff(1:nr,[1,3,4,5,8,11])
no_E=setdiff(1:nr,EMO)
ops_no_CUMs_CLMs=setdiff(1:nr,[4,8,11]) # tutti meno DE_V, LUO, PAG
ng = daysinmonth(Date(y,mese))
col='A'*('A'+ng-26)*"$(nr+2)" # colonna AE10 o AGF0
H = (1,"B3:$col") ##### foglio excel,celle indispon. e ranges per persona
res= getdata(path,H[1],H[2])
g_festivi = weekends(mese,y)
g_feriali = setdiff(1:ng,g_festivi)
turni = ["M1","M2", "P", "N", "GF","NF","CUM1","CUM2","CLM","CUMs","CLMs"]
t_feriali = turni[1:4]
t_festivi = turni[5:6]
t_card = turni[7:9]
t_Cs = turni[10:11]
t_mattina = ["M1","M2","CUM1","CUM2","CLM","GF","CUMs","CLMs"]
model = Model(HiGHS.Optimizer)
#set_silent(model)
@variable(model, x[1:nr, 1:ng, turni ], Bin)
@variable(model, z[1:nr, 1:ng], Bin)
@constraints(model, begin
# each day must have exactly one t: (t in turni)
fer1[k in union(t_feriali,t_card), g in g_feriali], sum(x[:,g,k]) == 1
fer2[k in t_festivi, g in g_feriali], sum(x[:,g,k]) == 0
fest1[k in t_festivi, g in g_festivi], sum(x[:,g,k]) == 1
fest2[k in union(t_feriali, t_card), g in g_festivi], sum(x[:,g,k]) == 0
sat[k in t_Cs, g in saturdays(mese,y)], sum(x[:,g,k]) == 1
no_sat[k in ["CUMs","CLMs"], g in setdiff(1:ng,saturdays(mese,y))], sum(x[:,g,k]) == 0
# range of N and NF for row for month
[r in ops_lim], sum(x[r,j,"N"]+x[r,j,"NF"] for j in 1:ng) <= 4 #4
[r in ops_lim], sum(x[r,j,"N"]+x[r,j,"NF"] for j in 1:ng) >= 3 #3
[r in ops_lim], sum(x[r,j,"N"] for j in 1:ng) <= 3
[r in ops_lim], sum(x[r,j,"N"] for j in 1:ng) >= 1
# max of NF for row for month
[ r in 1:nr], sum(x[r,j,"NF"] for j in g_festivi) <= 2 # 2
# NF==2 --> N<=1
[r in ops_lim], sum(x[r,:,"NF"]) == sum(i * z[r, i] for i in 1:ng)
[r in ops_lim], z[r,2] --> {sum(x[r, :, "N"]) <= 1}
[r in ops_lim], sum(z[r,:]) <= 1
[r in 1:nr, w in wks], x[r,w[1],"CUM1"] --> {sum(x[r,Base.rest(w),"CUM1"])>=length(Base.rest(w))}
[r in 1:nr, w in wks], x[r,w[1],"CUM2"] --> {sum(x[r,Base.rest(w),"CUM2"])>=length(Base.rest(w))}
[r in 1:nr, w in wks], x[r,w[1],"CLM" ] --> {sum(x[r,Base.rest(w),"CLM" ])>=length(Base.rest(w))}
# max turni nello stesso giorno per operatore
# non notte insieme a mattina o pomeriggio, ecc.
max_turni_fer_N_P[op in 1:nr, g in g_feriali], sum(x[op,g,k] for k in ["N","P"]) <= 1
max_turni_fer_N_M[op in 1:nr, g in g_feriali], sum(x[op,g,k] for k in ["N";t_mattina]) <= 1
max_turni_fer_P_M[op in 1:nr, g in g_feriali], sum(x[op,g,k] for k in ["P";t_mattina]) <= 1
max_turni_fer[op in 1:nr, g in g_feriali], sum(x[op,g,k] for k in t_feriali) <= 2
max_turni_CU[op in 1:nr, g in g_feriali], sum(x[op,g,k] for k in t_card) <= 1
max_turni_fest[op in 1:nr, g in g_festivi], sum(x[op,g,k] for k in [t_festivi;t_Cs]) <= 1
# max num of GF and NF for row for weekends
wk_lim[r in 1:nr], sum(x[r,j,"GF"]+x[r,j,"NF"] for j in weekends(mese,y)) <= 2 #2
# # range for month # sostituiti dalla funzione obiettivo
# [r in no_E], sum(sum(x[r,j,k] for k in turni ) for j in 1:ng) <= 16 #16
# [r in no_E], sum(sum(x[r,j,k] for k in turni ) for j in 1:ng) >= 14 #14
# vincoli sui turni specifici per operatore
# es. BAT 3 turni di notte a mese
# PAG, LUO, CEL MAN NON COPRONI I TURNI UTIC DI MATTINA
# BAT SEMPRE IN UTIC LA MATTINA TRANNE SE FA LA NOTTE
sum(x[BAT,g,"N"] + x[BAT,g,"NF"] for g in 1:ng) == 3 # 3 notti
sum(x[BAT,g,"P"] for g in 1:ng)== 0 # no pomeriggi
sum(x[DE_V,g,k] for k in ["N", "NF"], g in 1:ng) == 0 # no notti
sum(x[LUO,g, k] for k in ["N", "NF","M1","M2"], g in 1:ng) == 0 # no notti, no UTIC mattina
4 <= sum(x[LUO,g,"P"] for g in 1:ng) <= 5 # 4 o 5 pomeriggi
sum(x[CEL,g,k] for k in ["GF", "NF","M1","M2"], g in 1:ng) == 0 # no festivi, no UTIC mattina
3 <= sum(x[CEL,g,"P"] for g in 1:ng) <= 4 # 3 o 4 pomeriggi
sum(x[CEL,g,"N"] for g in 1:ng) ==2 # 2 notti
sum(x[CEL,g,k] for k in ["M1","M2"], g in 1:ng) == 0 # no UTIC mattina
sum(x[MAN,g,k] for k in ["M1","M2"], g in 1:ng) == 0 # no UTIC mattina
4 <= sum(x[PAG,g,"P"] for g in 1:ng) <= 5 # 4 o 5 pomeriggi
sum(x[PAG,g,k] for k in ["N","GF","NF","M1","M2"] , g in 1:ng) == 0 # no notti, no festivi, no UTIC mattina
sum(x[GAT,g,t] for g in 1:ng, t in t_card) == 0 # no cardio urgenza
# prevalentemente DE_V, LUO, PAG dovrebbero fare CU di sabato
sum(x[r,g,"CUMs"]+x[r,g,"CLMs"] for g in saturdays(mese,y),r in ops_no_CUMs_CLMs) <= 1
# no more than 5 turni consecutive
[r in 1:nr, g in 6:ng], sum(sum(x[r,i,k] for k in turni ) for i in g-5:g) <= 5
# nothing can follow N or NF
[r in 1:nr, g in 2:ng, k in turni], x[r,g,k] <= 1 - x[r,g-1,"NF"]
[r in 1:nr, g in 2:ng, k in turni], x[r,g,k] <= 1 - x[r,g-1,"N"]
#distance between N and N or NF >=4
[r in 1:nr, g in 5:ng], sum(x[r,i,"N"]+x[r,i,"NF"] for i in g-4:g) <= 1
end)
peso = Dict{String, Int}() # THIS IS CONVERGENT
for g in turni
if g in [t_festivi; "N"]
peso[g] = 2
else
peso[g] = 1
end
end
# peso = Dict{String, Float64}() THIS DOES NOT CONVERGE
# for g in turni
# if g in [t_festivi; "N"]
# peso[g] = 1
# else
# peso[g] = 0.5
# end
# end
# Carico di lavoro
@expression(model, workload[r=no_E], sum(x[r, g,k]*peso[k] for g in 1:ng, k in turni))
# Variabili per max/min carico
@variable(model, M) # massimo carico
@variable(model, m) # minimo carico
@constraint(model, [r in no_E], workload[r] <= M)
@constraint(model, [r in no_E], workload[r] >= m)
# Obiettivo: minimizzare squilibrio massimo
@objective(model, Min, M - m)
function init_model(res)
fixm(i,j, k,b)= begin fix(x[i, j, k], b; force = true); res[i,j]=replace(res[i,j], r"[M|P|N|E]"=>".") end
for i in 1:nr, j in 1:ng
res[i, j] == "X" ? fixm.(i, j, turni, 0) :
res[i, j] == "M" ? fixm.(i, j, t_mattina, 0) :
# chiarire se res[i, j] == "P" esclude anche "GF" #
res[i, j] == "P" ? fixm.(i, j, ["P"], 0) :
res[i, j] == "N" ? fixm.(i, j, ["N","NF"], 0) :
res[i, j] == "MP" ? fixm.(i, j, ["P";t_mattina], 0) :
res[i, j] == "PN" ? fixm.(i, j, ["P","N","NF"], 0) :
res[i, j] == "MN" ? fixm.(i, j, ["N";t_mattina], 0) :
res[i, j] == "E" ? fixm.(i, j, turni, [0,0,0,1,0,0,0,0,0,0,0]) :
nothing
end
for i in 1:nr, j in 1:ng
if (res[i,j]==".."||res[i,j]=="...")
res[i,j]="."
end
end
end
init_model(res)
optimize!(model)
assert_is_solved_and_feasible(model)
x,nr,ng,turni,ops,res
end # soljump
function report_to_excel(path, x,nr,ng,turni,ops,res,shn="1")
for i in 1:nr, j in 1:ng, k in turni
if value(x[i, j, k]) >= 0.5
if res[i,j]=="."
res[i, j] = k
else
res[i, j] *= "+"*k
end
end
end
vv=["" for i in 1:ng, j in 1:length(turni)]
resp=DataFrame(vv,turni)
for j in 1:ng, k in turni, i in 1:nr
if value(x[i, j, k]) >= 0.5
resp[j, k] *= ops[i]
end
end
giormi_it=Dict(
"Wed" => "Mer",
"Tue" => "Mar",
"Thu" => "Gio",
"Sun" => "Dom",
"Mon" => "Lun",
"Fri" => "Ven",
"Sat" => "Sab")
git=[giormi_it[d] for d in Dates.format.(Date.(y, mese, 1:ng),"e")]
gn = Dates.format.(Date.(y, mese, 1:ng),"dd")
insertcols!(resp,1, :giorno => gn .* "-" .* git)
# output mode vista operatore
XLSX.openxlsx(path, mode="rw") do xf
sheet = XLSX.addsheet!(xf, "sol_jump_m_"*shn*"p")
XLSX.writetable!(sheet, resp, anchor_cell=XLSX.CellRef("A1"))
end
# output mode vista servizio
XLSX.openxlsx(path, mode="rw") do xf
sheet = XLSX.addsheet!(xf, "sol_jump_"*shn)
# dnames = Dates.format.(Date.(y, mese, 1:ng),"dd") .* "-" .* (giormi_it[d] for d in Dates.format.(Date.(y, mese, 1:ng),"e"))
dnames = gn .* "-" .* git
sol=DataFrame(res,dnames)
insertcols!(sol,1, "giorno\\operatore" => ops)
XLSX.writetable!(sheet, sol, anchor_cell=XLSX.CellRef("A1"))
end
# output mode vista sintesi/sommario
XLSX.openxlsx(path, mode="rw") do xf
sheet = XLSX.addsheet!(xf, "summary_jump_"*shn)
vx=round.(Int,value.(x))
df=DataFrame([ t=>[sum(vx[j, :, t]) for j in 1:nr] for t in turni])
transform!(df,Cols(:) =>ByRow((x...)->sum(x)) => "tot")
transform!(df,["N","NF"]=>ByRow((x...)->sum(x)) => "tot_N")
insertcols!(df,1, :operatore => ops)
XLSX.writetable!(sheet, df, anchor_cell=XLSX.CellRef("A1"))
end
end
y, mese = 2025,10
path="C:\\Users\\sprmn\\OneDrive\\Desktop\\Turni San Giovanni\\test_ott_2025.xlsx"
x,nr,ng,turni,ops,res=sol_jump(mese,y)
report_to_excel(path, x,nr,ng,turni,ops,res,"peso_turni_1x2")
In the same Excel folder the script loads the processing results, in 3 separate sheets