Hi,
I am using the current version of GLPK and have been referencing the
sodoku_odbc.mod example for my own project. I recently changed my setup
of reading each set of data from a different excel workbook to one
workbook where each set has a sheet. The sheets are populated from a
template sheet. I wrote some macros to transfer the data from the
template to the appropriate sheet. I now have a problem with the ODBC
handler. It will not accept the *.xlsm extension. Does anyone have any
suggestions for fixing this? I get an error from the first table
statement.
Thank you for your help.
Christina Cignarale
Industrial & Systems Engineer
Rochester Institute of Technology
set P;
#set of all patients
set PA within P;
#subset of patients admitted until the day before
set PN within P;
#subset of incoming patients requiring admission in the unit
set ISOLATION;
#set of isolation needs
set GENDER;
#set of genders
set R;
#set of available rooms
param filepath symbolic := 'C:\glpk445\examples\RGHPatient'; #
text-parameter used to represent the path where the table files are
stored
param B {j in R};
# number of beds available in each room j in R
param G {i in P};# in GENDER ;
# gender of each patient i in P
param I {i in P};# in ISOLATION;
# isolation requirement of patient i in P
param c {i in P};
# current relative criticality of patient i in P compared with all other
# patients in the unit
param y {i in P, j in R} binary, default 0;
# binary parameter that is 1 if patient i was in room j the day before,
and 0 o.w.
param flag {i in P} binary;
# binary parameter that is 1 if patient i cannot be moved from its room,
and 0 o.w.
table patient IN "ODBC"
'DRIVER={Microsoft Excel Driver (*.xlsm)};
dbq=RGHtemplate.xlsm'
'SELECT * FROM [patient$]':
P <- [P], G, I, c, flag;
table pa IN "ODBC"
'DRIVER={Microsoft Excel Driver (*.xlsm)};
dbq=RGHtemplate.xlsm'
'SELECT * FROM [patest$]':
PA <- [PA];
table pn IN "ODBC"
'DRIVER={Microsoft Excel Driver (*.xlsm)};
dbq=RGHtemplate.xlsm'
'SELECT * FROM [pntest$]':
PN <- [PN];
table iso IN "ODBC"
'DRIVER={Microsoft Excel Driver (*.xlsm)};
dbq=RGHtemplate.xlsm'
'SELECT * FROM [iso$]':
ISOLATION <- [iso];
table gender IN "ODBC"
'DRIVER={Microsoft Excel Driver (*.xlsm)};
dbq=RGHtemplate.xlsm'
'SELECT * FROM [gender$]':
GENDER <- [gender];
table rooms IN "ODBC"
'DRIVER={Microsoft Excel Driver (*.xlsm)};
dbq=RGHtemplate.xlsm'
'SELECT * FROM [rooms$]':
R <-[R],B;
table yparam IN "ODBC"
'DRIVER={Microsoft Excel Driver (*.xlsm)};
dbq=RGHtemplate.xlsm'
'SELECT * FROM [y$]':
[P, R], y;
var x {i in P, j in R} binary;
# binary variable: 1 if patient i is moved to room j, and 0 o.w.
var delta {g in GENDER, j in R: B[j] <> 1} binary;
# binary variable : 1 if there is at least one patient with gender g in
room j, and 0 o.w.
var gamma {i in ISOLATION, j in R: B[j] <> 1} binary;
# binary variable: 1 if there is at least one patient with isolation i
in room j, and 0 o.w.
var splus{ i in P, j in R} >=0;
var sminus{i in P, j in R} >=0;
#minimize PatientMoves1: sum {i in PA, j in R} c[i]*(x[i,j]-y[i,j]) -
sum { i in PN,j in R:B[j] <> 0 and B[j] <> 99 and B[j] <>1 }
c[i]*x[i,j];
minimize PatientMoves2: sum {i in P, j in R}
c[i]*(splus[i,j]+sminus[i,j]);
subject to const0 {i in P, j in R}:x[i,j]-y[i,j] =
splus[i,j]-sminus[i,j];
subject to const1{i in P}: sum {j in R} x[i,j] =1;
subject to const2 {i in P, j in R: B[j] <> 0 and B[j] <> 99 and B[j]
<>1}: x[i,j] <= delta[G[i],j];
subject to const3 {i in P, j in R: B[j] <> 0 and B[j] <> 99 and B[j]
<>1}: x[i,j] <= gamma[I[i],j];
subject to const4{j in R: B[j] <> 0 and B[j] <> 99 and B[j] <>1}: sum {g
in GENDER} delta[g,j] <=1;
subject to const5 {j in R: B[j] <> 0 and B[j] <> 99 and B[j] <>1}: sum
{i in ISOLATION} gamma[i,j] <=1;
subject to const6 {j in R:B[j] <> 0 and B[j] <> 99 }: sum {i in P}
x[i,j] <= B[j];
subject to const7 {i in PA,j in R: B[j]=0}: x[i,j] = 0;
subject to const8 {i in PN,j in R: B[j]= 99 }: x[i,j] =0;
subject to const9 {i in P, j1 in R: B[j1]=1 and y[i, j1]=1}:sum {j2 in
R: B[j2] = 1 and j1 <>j2} x[i,j2]=0;
subject to const10 {i in P}: sum{j in R: B[j] <> 0 and B[j] <> 99}
x[i,j] <= 1 - flag[i];
solve;
display x, y;
table results {i in P, j in R: x[i,j]=1} OUT "ODBC"
'DRIVER={Microsoft Excel Driver
(*.xlsm)};READONLY=FALSE; dbq=RGHtemplate.xlsm'
'UPDATE [result$] set P = '''';'
'UPDATE [result$] set R = '''';'
'UPDATE [result$] set y = '''';'
'UPDATE [result$] set x = '''';'
'INSERT INTO [result$]'
'(P, R, y,x)'
'VALUES(?,?,?,?);':
i ~ P, j ~ R, y[i,j]~Y, x[i,j]~X ; # outputting
results to table
printf "%16s%16s%16s%16s\n", "PATIENT","STATUS", "INITIAL_ROOM",
"NEW_ROOM" > "RGHPatient_results.txt"; # printing
results to a text file
for {i in PA}{
printf "%16i%16s", i, "PA">>"RGHPatient_results.txt";
printf{r in R: y[i,r]=1} "%16s", r >> filepath
&"RGHPatient_results.txt";
printf{r in R:x[i,r]=1}"%16s\n", r >> filepath
&"RGHPatient_results.txt";
}
for {i in PN}{
printf "%16i%16s", i, "PN">>"RGHPatient_results.txt";
printf{r in R: y[i,r]=1} "%16s", r >>filepath
&"RGHPatient_results.txt";
printf{r in R: x[i,r]=1}"%16s\n", r >> filepath
&"RGHPatient_results.txt";
}
end;
_______________________________________________
Help-glpk mailing list
[email protected]
http://lists.gnu.org/mailman/listinfo/help-glpk
