Loading...
Searching...
No Matches
transport9.cpp
Go to the documentation of this file.
1
26#include <vector>
27#include "gams.h"
28#include <iostream>
29
30using namespace std;
31using namespace gams;
32
33#if defined(__unix__) || defined(__linux__) || defined(__APPLE__)
34
39int main()
40{
41 cout << "---------- Transport 9 --------------" << endl;
42 cout << "Transport 9 is a Microsoft Windows only example." << endl;
43 return 0;
44}
45
46#else
47
48#include <QCoreApplication>
49#include <QtSql>
50
52string getModelText()
53{
54 return " Sets \n"
55 " i canning plants \n"
56 " j markets \n"
57 " \n"
58 " Parameters \n"
59 " a(i) capacity of plant i in cases \n"
60 " b(j) demand at market j in cases \n"
61 " d(i,j) distance in thousands of miles \n"
62 " Scalar f freight in dollars per case per thousand miles /90/; \n"
63 " \n"
64 "$if not set gdxincname $abort 'no include file name for data file provided' \n"
65 "$gdxin %gdxincname% \n"
66 "$load i j a b d \n"
67 "$gdxin \n"
68 " \n"
69 " Parameter c(i,j) transport cost in thousands of dollars per case ; \n"
70 " \n"
71 " c(i,j) = f * d(i,j) / 1000 ; \n"
72 " \n"
73 " Variables \n"
74 " x(i,j) shipment quantities in cases \n"
75 " z total transportation costs in thousands of dollars ; \n"
76 " \n"
77 " Positive Variable x ; \n"
78 " \n"
79 " Equations \n"
80 " cost define objective function \n"
81 " supply(i) observe supply limit at plant i \n"
82 " demand(j) satisfy demand at market j ; \n"
83 " \n"
84 " cost .. z =e= sum((i,j), c(i,j)*x(i,j)) ; \n"
85 " \n"
86 " supply(i) .. sum(j, x(i,j)) =l= a(i) ; \n"
87 " \n"
88 " demand(j) .. sum(i, x(i,j)) =g= b(j) ; \n"
89 " \n"
90 " Model transport /all/ ; \n"
91 " \n"
92 " Solve transport using lp minimizing z ; \n"
93 " \n"
94 " Display x.l, x.m ; \n"
95 " \n";
96}
97
99void readSet(QSqlDatabase sqlDb, GAMSDatabase db, string strAccessSelect, string setName, int setDim, string setExp = "")
100{
101 QSqlQuery query(sqlDb);
102 if (!query.exec(strAccessSelect.c_str()))
103 {
104 cout << "Error executing query on set '" << setName << "'" << endl;
105 cout << query.lastError().text().toStdString() << endl;
106 exit(1);
107 }
108 if (query.size() && (query.record().count() != setDim))
109 {
110 cout << "Number of fields in select statement does not match setDim" << endl;
111 exit(1);
112 }
113
114 GAMSSet i = db.addSet(setName, setDim, setExp);
115 vector<string> keys = vector<string>(setDim);
116
117 while (query.next())
118 {
119 for (int idx = 0; idx < setDim; idx++)
120 keys[idx] = query.value(idx).toString().toStdString();
121 i.addRecord(keys);
122 }
123}
124
126void readParameter(QSqlDatabase sqlDb, GAMSDatabase db, string strAccessSelect, string parName, int parDim, string parExp = "")
127{
128 QSqlQuery query(sqlDb);
129 if (!query.exec(strAccessSelect.c_str()))
130 {
131 cout << "Error executing query on parameter '" << parName << "'" << endl;
132 cout << query.lastError().text().toStdString() << endl;
133 exit(1);
134 }
135 if (query.size() && (query.record().count() != parDim+1))
136 {
137 cout << "Number of fields in select statement does not match parDim" << endl;
138 exit(1);
139 }
140
141 GAMSParameter a = db.addParameter(parName, parDim, parExp);
142 vector<string> keys = vector<string>(parDim);
143
144 while (query.next())
145 {
146 for (int idx = 0; idx < parDim; idx++)
147 keys[idx] = query.value(idx).toString().toStdString();
148 a.addRecord(keys).setValue(query.value(parDim).toDouble());
149 }
150}
151
153GAMSDatabase readFromAccess(GAMSWorkspace ws)
154{
155 GAMSDatabase db = ws.addDatabase();
156
157 QSqlDatabase sqlDb = QSqlDatabase::addDatabase("QODBC", "readConnection");
158
159 QString strAccessConn = ("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=" + ws.systemDirectory() \
160 + cPathSep + "apifiles" + cPathSep + "Data" + cPathSep + "transport.accdb").c_str();
161 sqlDb.setDatabaseName(strAccessConn);
162
163 if(sqlDb.open())
164 {
165 // read GAMS sets
166 readSet(sqlDb, db, "SELECT Plant FROM Plant", "i", 1, "canning plants");
167 readSet(sqlDb, db, "SELECT Market FROM Market", "j", 1, "markets");
168
169 // read GAMS parameters
170 readParameter(sqlDb, db, "SELECT Plant,Capacity FROM Plant", "a", 1, "capacity of plant i in cases");
171 readParameter(sqlDb, db, "SELECT Market,Demand FROM Market", "b", 1, "demand at market j in cases");
172 readParameter(sqlDb, db, "SELECT Plant,Market,Distance FROM Distance", "d", 2, "distance in thousands of miles");
173 sqlDb.close();
174 }
175 else
176 {
177 cout << "Error: Failed to create a database connection. " << sqlDb.lastError().text().toStdString() << endl;
178 exit(1);
179 }
180 return db;
181}
182
184void writeVariable(QSqlDatabase sqlDb, GAMSDatabase db, string varName, vector<string> domains)
185{
186 GAMSVariable var = db.getVariable(varName);
187 if(domains.size() != static_cast<size_t>(var.dim()))
188 {
189 cout << "Number of column names does not match the dimension of the variable." << endl;
190 exit(1);
191 }
192
193 // delete table varName if it exists already
194 QSqlQuery query(sqlDb);
195 query.exec(("drop table " + varName).c_str());
196
197 string queryStr = "create table " + varName + "(";
198 for (string dom : domains)
199 queryStr += dom + " varchar(64), ";
200 queryStr += "lvl double)";
201
202 query.exec(queryStr.c_str());
203
204 for (GAMSVariableRecord rec : var)
205 {
206 queryStr = "insert into " + varName + "(";
207 for (string dom : domains)
208 queryStr += dom + ", ";
209 queryStr += "lvl) values (";
210 for (string key : rec.keys())
211 queryStr += "'" + key + "', ";
212 queryStr += std::to_string(rec.level()) + ")";
213 if(!query.exec(queryStr.c_str()))
214 {
215 cout << "Error: Failed to write variable to the database" << endl;
216 cout << sqlDb.lastError().text().toStdString() << endl;
217 exit(1);
218 }
219 }
220}
221
223void writeToAccess(GAMSWorkspace ws, GAMSDatabase db)
224{
225 // connect to database
226 QSqlDatabase sqlDb = QSqlDatabase::addDatabase("QODBC", "writeConnection");
227
228 QString strAccessConn = ("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=" + ws.systemDirectory() \
229 + cPathSep + "apifiles" + cPathSep + "Data" + cPathSep + "transport.accdb").c_str();
230 sqlDb.setDatabaseName(strAccessConn);
231
232 if(sqlDb.open())
233 {
234 // write levels of variable x
235 vector<string> domains{"i", "j"};
236 writeVariable(sqlDb, db, "x", domains);
237 sqlDb.close();
238 }
239 else
240 {
241 cout << "Error: Failed to create a database connection. " << sqlDb.lastError().text().toStdString() << endl;
242 exit(1);
243 }
244}
245
251int main(int argc, char* argv[])
252{
253 cout << "---------- Transport 9 --------------" << endl;
254
255
256 try {
257 QCoreApplication app(argc, argv);
258 GAMSWorkspaceInfo wsInfo;
259 if (argc > 1)
260 wsInfo.setSystemDirectory(argv[1]);
261 GAMSWorkspace ws(wsInfo);
262
263 // fill GAMSDatabase by reading from Access
264 GAMSDatabase db = readFromAccess(ws);
265
266 // run job
267 GAMSOptions opt = ws.addOptions();
268 GAMSJob t9 = ws.addJobFromString(getModelText());
269 opt.setDefine("gdxincname", db.name());
270 opt.setAllModelTypes("xpress");
271 t9.run(opt, db);
272 for (GAMSVariableRecord rec : t9.outDB().getVariable("x"))
273 cout << "x(" << rec.key(0) << "," << rec.key(1) << "):" << " level=" << rec.level() << " marginal="
274 << rec.marginal() << endl;
275 // write results into Access file
276 writeToAccess(ws, t9.outDB());
277
278 } catch (GAMSException &ex) {
279 cout << "GAMSException occured: " << ex.what() << endl;
280 } catch (exception &ex) {
281 cout << ex.what() << endl;
282 }
283
284 return 0;
285}
286#endif
gams::GAMSDatabase::addSet
GAMSSet addSet(const std::string &name, const int dimension, const std::string &explanatoryText="", GAMSEnum::SetType setType=GAMSEnum::SetType::Multi)
gams::GAMSDatabase::addParameter
GAMSParameter addParameter(const std::string &name, const int dimension, const std::string &explanatoryText="")
gams::GAMSDatabase::name
std::string name()
gams::GAMSDatabase::getVariable
GAMSVariable getVariable(const std::string &name)
gams::GAMSJob::outDB
GAMSDatabase outDB()
gams::GAMSOptions::setAllModelTypes
void setAllModelTypes(const std::string &solver)
gams::GAMSOptions::setDefine
void setDefine(const std::string &key, const std::string &value)
gams::GAMSParameterRecord::setValue
void setValue(const double val)
gams::GAMSParameter::addRecord
GAMSParameterRecord addRecord(const std::vector< std::string > &keys)
gams::GAMSSet::addRecord
GAMSSetRecord addRecord(const std::vector< std::string > &keys)
gams::GAMSSymbol::dim
int dim() const
gams::GAMSWorkspaceInfo::setSystemDirectory
void setSystemDirectory(std::string systemDir)
gams::GAMSWorkspace::systemDirectory
std::string systemDirectory() const
gams::GAMSWorkspace::addDatabase
GAMSDatabase addDatabase(const std::string &databaseName="", const std::string &inModelName="")
writeToAccess
void writeToAccess(GAMSWorkspace ws, GAMSDatabase db)
Write GAMSDatabase to MS Access.
Definition: transport9.cpp:223
readSet
void readSet(QSqlDatabase sqlDb, GAMSDatabase db, string strAccessSelect, string setName, int setDim, string setExp="")
Read GAMSSet from MS Access.
Definition: transport9.cpp:99
readParameter
void readParameter(QSqlDatabase sqlDb, GAMSDatabase db, string strAccessSelect, string parName, int parDim, string parExp="")
Read GAMSParameter from MS Access.
Definition: transport9.cpp:126
writeVariable
void writeVariable(QSqlDatabase sqlDb, GAMSDatabase db, string varName, vector< string > domains)
Write GAMSVariable to MS Access.
Definition: transport9.cpp:184
getModelText
string getModelText()
Get model as string.
Definition: transport9.cpp:52
readFromAccess
GAMSDatabase readFromAccess(GAMSWorkspace ws)
Read data from MS Access into a GAMSDatabase.
Definition: transport9.cpp:153