I am going to start off with that I am not well versed in Minizinc nor constraint programming, I have watched excel "solver" tutorials on youtube which I can understand, but I do not see how I can translate my issue into a excel solver-able solution, nor Minizinc for that matter.
To explain the issue, I have what I believe is a multi-level knapsack problem, but could be wrong. Here are what I think are the constraints
There are 25 "admin" who supervise over 200 "staff".
Each admin has a unique workload allocation.
Each admin also has to moderate staff
that is both equal to or greater than their supervisorial allocation
and has the ability to rate their moderation preference
Admin cannot supervise and moderate the same staff member.
Every staff member has to have both a supervisor and a moderator.
To wrap my head around the problem I have represented it as a table
Taking the attached example we can see
admin1 is the supervisor for staff1, 13, and 17 They have volunteered to moderate staff2, 20, 10, and 23 in that order (preference).
Ignoring all of the above which is my breakdown of the problem you can simplify the issue as follows
I hope I have tried to explain the problem well enough and my analysis is not too convoluted, any pointers on how I can solve this so it can be scaled to a much larger data set would be appreciated.
Thanks.
You could try the following MIP-model in MiniZinc with the solver set to OsiCbc.
int: n = 10;
int: m = 24;
set of int: ADMIN = 1..n;
set of int: STAFF = 1..m;
array[ADMIN,STAFF] of var 0..1: supervise;
array[ADMIN,STAFF] of var 0..1: moderate;
array[ADMIN, STAFF] of int: moderateValue = [|
0,4,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,3,0,0,1,0|
3,2,0,0,0,0,0,0,4,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0|
0,0,1,0,0,0,0,0,0,0,0,0,0,4,0,0,0,0,0,3,0,0,0,2|
0,0,0,1,0,0,0,0,0,0,3,2,0,0,0,0,0,4,0,0,0,0,0,0|
0,0,0,0,0,0,0,4,0,0,0,0,0,0,0,0,3,0,0,0,0,2,0,1|
0,0,0,0,2,4,0,0,0,0,0,0,0,0,3,1,0,0,0,0,0,0,0,0|
0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,2,3,0,0,0,0,0,0|
0,2,0,0,0,0,0,4,0,0,0,0,3,1,0,0,0,0,0,0,0,0,0,0|
0,0,0,0,0,0,3,0,0,0,0,4,0,0,2,1,0,0,0,0,0,0,0,0|
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,3,2,1|];
% admin member cannot both supervise and moderate staff member
constraint forall(a in ADMIN, s in STAFF)
(supervise[a,s] + moderate[a,s] <= 1);
% each staff member is supervised by exactly one admin member
constraint forall(s in STAFF)
(sum(col(supervise, s)) = 1);
% each staff member is moderated by exactly one admin member
constraint forall(s in STAFF)
(sum(col(moderate, s)) = 1);
% each admin member cannot supervise more staff members than moderated
constraint forall(a in ADMIN)
(sum(row(supervise, a)) <= sum(row(moderate, a)));
var int: obj = sum(a in ADMIN, s in STAFF)(moderateValue[a,s]*moderate[a,s]);
solve maximize obj;
output ["obj = \(obj)\n"] ++ ["assignment = \n"] ++ [show2d(array2d(ADMIN, STAFF, [if supervise[a,s] = 1 then 1 elseif moderate[a,s] = 1 then 2 else 0 endif | a in ADMIN, s in STAFF]))];
I'm not sure if I understood all of your requirements but hopefully the model can serve as a base. Each admin member can here put moderation values of 4, 3, 2 and 1 to staff members. The objective is then to maximize the sum of the assigned moderations. In the output 1 means admin member supervises staff member, 2 means admin member moderates staff member otherwise 0 is displayed. The data in the model is based on the provided example.
Edit: To make supervise pre-defined change the following:
%array[ADMIN,STAFF] of var 0..1: supervise;
array[ADMIN, STAFF] of int: supervise = [|
1,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,0|
0,0,1,0,1,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0|
0,0,0,0,0,0,1,1,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0|
0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0|
0,0,0,0,0,0,0,0,0,1,0,1,0,1,0,0,0,0,0,0,0,0,0,0|
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,1,1,0,0,0,0|
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,1,1,0,0|
0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0|
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1|
0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0|];