transport9.cpp
Go to the documentation of this file.
1/*
2  *
3  * GAMS - General Algebraic Modeling System C++ API
4  *
5  * Copyright (c) 2017-2022 GAMS Software GmbH <support@gams.com>
6  * Copyright (c) 2017-2022 GAMS Development Corp. <support@gams.com>
7  *
8  * Permission is hereby granted, free of charge, to any person obtaining a copy
9  * of this software and associated documentation files (the "Software"), to deal
10  * in the Software without restriction, including without limitation the rights
11  * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
12  * copies of the Software, and to permit persons to whom the Software is
13  * furnished to do so, subject to the following conditions:
14  *
15  * The above copyright notice and this permission notice shall be included in all
16  * copies or substantial portions of the Software.
17  *
18  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
19  * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
20  * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
21  * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
22  * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
23  * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
24  * SOFTWARE.
25  */
26 
27 #include <vector>
28 #include "gams.h"
29 #include <iostream>
30 
31 using namespace std;
32 using namespace gams;
33 
34 #if defined(__unix__) || defined(__linux__) || defined(__APPLE__)
35 
40 int main()
41 {
42  cout << "---------- Transport 9 --------------" << endl;
43  cout << "Transport 9 is a Microsoft Windows only example." << endl;
44  return 0;
45 }
46 
47 #else
48 
49 #include <QCoreApplication>
50 #include <QtSql>
51 
53 string getModelText()
54 {
55  return " Sets \n"
56  " i canning plants \n"
57  " j markets \n"
58  " \n"
59  " Parameters \n"
60  " a(i) capacity of plant i in cases \n"
61  " b(j) demand at market j in cases \n"
62  " d(i,j) distance in thousands of miles \n"
63  " Scalar f freight in dollars per case per thousand miles /90/; \n"
64  " \n"
65  "$if not set gdxincname $abort 'no include file name for data file provided' \n"
66  "$gdxin %gdxincname% \n"
67  "$load i j a b d \n"
68  "$gdxin \n"
69  " \n"
70  " Parameter c(i,j) transport cost in thousands of dollars per case ; \n"
71  " \n"
72  " c(i,j) = f * d(i,j) / 1000 ; \n"
73  " \n"
74  " Variables \n"
75  " x(i,j) shipment quantities in cases \n"
76  " z total transportation costs in thousands of dollars ; \n"
77  " \n"
78  " Positive Variable x ; \n"
79  " \n"
80  " Equations \n"
81  " cost define objective function \n"
82  " supply(i) observe supply limit at plant i \n"
83  " demand(j) satisfy demand at market j ; \n"
84  " \n"
85  " cost .. z =e= sum((i,j), c(i,j)*x(i,j)) ; \n"
86  " \n"
87  " supply(i) .. sum(j, x(i,j)) =l= a(i) ; \n"
88  " \n"
89  " demand(j) .. sum(i, x(i,j)) =g= b(j) ; \n"
90  " \n"
91  " Model transport /all/ ; \n"
92  " \n"
93  " Solve transport using lp minimizing z ; \n"
94  " \n"
95  " Display x.l, x.m ; \n"
96  " \n";
97 }
98 
100 void readSet(QSqlDatabase sqlDb, GAMSDatabase db, string strAccessSelect, string setName, int setDim, string setExp = "")
101 {
102  QSqlQuery query(sqlDb);
103  if (!query.exec(strAccessSelect.c_str()))
104  {
105  cout << "Error executing query on set '" << setName << "'" << endl;
106  cout << query.lastError().text().toStdString() << endl;
107  exit(1);
108  }
109  if (query.size() && (query.record().count() != setDim))
110  {
111  cout << "Number of fields in select statement does not match setDim" << endl;
112  exit(1);
113  }
114 
115  GAMSSet i = db.addSet(setName, setDim, setExp);
116  vector<string> keys = vector<string>(setDim);
117 
118  while (query.next())
119  {
120  for (int idx = 0; idx < setDim; idx++)
121  keys[idx] = query.value(idx).toString().toStdString();
122  i.addRecord(keys);
123  }
124 }
125 
127 void readParameter(QSqlDatabase sqlDb, GAMSDatabase db, string strAccessSelect, string parName, int parDim, string parExp = "")
128 {
129  QSqlQuery query(sqlDb);
130  if (!query.exec(strAccessSelect.c_str()))
131  {
132  cout << "Error executing query on parameter '" << parName << "'" << endl;
133  cout << query.lastError().text().toStdString() << endl;
134  exit(1);
135  }
136  if (query.size() && (query.record().count() != parDim+1))
137  {
138  cout << "Number of fields in select statement does not match parDim" << endl;
139  exit(1);
140  }
141 
142  GAMSParameter a = db.addParameter(parName, parDim, parExp);
143  vector<string> keys = vector<string>(parDim);
144 
145  while (query.next())
146  {
147  for (int idx = 0; idx < parDim; idx++)
148  keys[idx] = query.value(idx).toString().toStdString();
149  a.addRecord(keys).setValue(query.value(parDim).toDouble());
150  }
151 }
152 
154 GAMSDatabase readFromAccess(GAMSWorkspace ws)
155 {
156  GAMSDatabase db = ws.addDatabase();
157 
158  QSqlDatabase sqlDb = QSqlDatabase::addDatabase("QODBC", "readConnection");
159 
160  QString strAccessConn = ("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=" + ws.systemDirectory() \
161  + cPathSep + "apifiles" + cPathSep + "Data" + cPathSep + "transport.accdb").c_str();
162  sqlDb.setDatabaseName(strAccessConn);
163 
164  if(sqlDb.open())
165  {
166  // read GAMS sets
167  readSet(sqlDb, db, "SELECT Plant FROM Plant", "i", 1, "canning plants");
168  readSet(sqlDb, db, "SELECT Market FROM Market", "j", 1, "markets");
169 
170  // read GAMS parameters
171  readParameter(sqlDb, db, "SELECT Plant,Capacity FROM Plant", "a", 1, "capacity of plant i in cases");
172  readParameter(sqlDb, db, "SELECT Market,Demand FROM Market", "b", 1, "demand at market j in cases");
173  readParameter(sqlDb, db, "SELECT Plant,Market,Distance FROM Distance", "d", 2, "distance in thousands of miles");
174  sqlDb.close();
175  }
176  else
177  {
178  cout << "Error: Failed to create a database connection. " << sqlDb.lastError().text().toStdString() << endl;
179  exit(1);
180  }
181  return db;
182 }
183 
185 void writeVariable(QSqlDatabase sqlDb, GAMSDatabase db, string varName, vector<string> domains)
186 {
187  GAMSVariable var = db.getVariable(varName);
188  if(domains.size() != static_cast<size_t>(var.dim()))
189  {
190  cout << "Number of column names does not match the dimension of the variable." << endl;
191  exit(1);
192  }
193 
194  // delete table varName if it exists already
195  QSqlQuery query(sqlDb);
196  query.exec(("drop table " + varName).c_str());
197 
198  string queryStr = "create table " + varName + "(";
199  for (string dom : domains)
200  queryStr += dom + " varchar(64), ";
201  queryStr += "lvl double)";
202 
203  query.exec(queryStr.c_str());
204 
205  for (GAMSVariableRecord rec : var)
206  {
207  queryStr = "insert into " + varName + "(";
208  for (string dom : domains)
209  queryStr += dom + ", ";
210  queryStr += "lvl) values (";
211  for (string key : rec.keys())
212  queryStr += "'" + key + "', ";
213  queryStr += std::to_string(rec.level()) + ")";
214  if(!query.exec(queryStr.c_str()))
215  {
216  cout << "Error: Failed to write variable to the database" << endl;
217  cout << sqlDb.lastError().text().toStdString() << endl;
218  exit(1);
219  }
220  }
221 }
222 
224 void writeToAccess(GAMSWorkspace ws, GAMSDatabase db)
225 {
226  // connect to database
227  QSqlDatabase sqlDb = QSqlDatabase::addDatabase("QODBC", "writeConnection");
228 
229  QString strAccessConn = ("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=" + ws.systemDirectory() \
230  + cPathSep + "apifiles" + cPathSep + "Data" + cPathSep + "transport.accdb").c_str();
231  sqlDb.setDatabaseName(strAccessConn);
232 
233  if(sqlDb.open())
234  {
235  // write levels of variable x
236  vector<string> domains{"i", "j"};
237  writeVariable(sqlDb, db, "x", domains);
238  sqlDb.close();
239  }
240  else
241  {
242  cout << "Error: Failed to create a database connection. " << sqlDb.lastError().text().toStdString() << endl;
243  exit(1);
244  }
245 }
246 
252 int main(int argc, char* argv[])
253 {
254  cout << "---------- Transport 9 --------------" << endl;
255 
256 
257  try {
258  QCoreApplication app(argc, argv);
259  GAMSWorkspaceInfo wsInfo;
260  if (argc > 1)
261  wsInfo.setSystemDirectory(argv[1]);
262  GAMSWorkspace ws(wsInfo);
263 
264  // fill GAMSDatabase by reading from Access
265  GAMSDatabase db = readFromAccess(ws);
266 
267  // run job
268  GAMSOptions opt = ws.addOptions();
269  GAMSJob t9 = ws.addJobFromString(getModelText());
270  opt.setDefine("gdxincname", db.name());
271  opt.setAllModelTypes("xpress");
272  t9.run(opt, db);
273  for (GAMSVariableRecord rec : t9.outDB().getVariable("x"))
274  cout << "x(" << rec.key(0) << "," << rec.key(1) << "):" << " level=" << rec.level() << " marginal="
275  << rec.marginal() << endl;
276  // write results into Access file
277  writeToAccess(ws, t9.outDB());
278 
279  } catch (GAMSException &ex) {
280  cout << "GAMSException occured: " << ex.what() << endl;
281  } catch (exception &ex) {
282  cout << ex.what() << endl;
283  }
284 
285  return 0;
286 }
287 #endif
gams::GAMSWorkspaceInfo::setSystemDirectory
void setSystemDirectory(std::string systemDir)
gams::GAMSSymbol::dim
int dim() const
gams::GAMSDatabase::getVariable
GAMSVariable getVariable(const std::string &name)
readFromAccess
GAMSDatabase readFromAccess(GAMSWorkspace ws)
Read data from MS Access into a GAMSDatabase.
Definition: transport9.cpp:154
gams::GAMSOptions::setAllModelTypes
void setAllModelTypes(const std::string &solver)
gams::GAMSDatabase::addSet
GAMSSet addSet(const std::string &name, const int dimension, const std::string &explanatoryText="", GAMSEnum::SetType setType=GAMSEnum::SetType::Multi)
gams::GAMSParameter::addRecord
GAMSParameterRecord addRecord(const std::vector< std::string > &keys)
gams::GAMSJob::outDB
GAMSDatabase outDB()
gams::GAMSWorkspace::addDatabase
GAMSDatabase addDatabase(const std::string &databaseName="", const std::string &inModelName="")
gams::GAMSSet::addRecord
GAMSSetRecord addRecord(const std::vector< std::string > &keys)
gams::GAMSDatabase::addParameter
GAMSParameter addParameter(const std::string &name, const int dimension, const std::string &explanatoryText="")
writeVariable
void writeVariable(QSqlDatabase sqlDb, GAMSDatabase db, string varName, vector< string > domains)
Write GAMSVariable to MS Access.
Definition: transport9.cpp:185
gams::GAMSParameterRecord::setValue
void setValue(const double val)
gams::GAMSWorkspace::systemDirectory
std::string systemDirectory() const
getModelText
string getModelText()
Get model as string.
Definition: transport9.cpp:53
gams::GAMSOptions::setDefine
void setDefine(const std::string &key, const std::string &value)
readSet
void readSet(QSqlDatabase sqlDb, GAMSDatabase db, string strAccessSelect, string setName, int setDim, string setExp="")
Read GAMSSet from MS Access.
Definition: transport9.cpp:100
readParameter
void readParameter(QSqlDatabase sqlDb, GAMSDatabase db, string strAccessSelect, string parName, int parDim, string parExp="")
Read GAMSParameter from MS Access.
Definition: transport9.cpp:127
gams::GAMSDatabase::name
std::string name()
writeToAccess
void writeToAccess(GAMSWorkspace ws, GAMSDatabase db)
Write GAMSDatabase to MS Access.
Definition: transport9.cpp:224