Loading...
Searching...
No Matches
transport9.cpp
Go to the documentation of this file.
1/*
2 *
3 * GAMS - General Algebraic Modeling System C++ API
4 *
5 * Copyright (c) 2017-2023 GAMS Software GmbH <support@gams.com>
6 * Copyright (c) 2017-2023 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
31using namespace std;
32using namespace gams;
33
34#if defined(__unix__) || defined(__linux__) || defined(__APPLE__)
35
40int 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
53string 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
100void 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
127void 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
154GAMSDatabase 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
185void 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
224void 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
252int 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::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:224
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
writeVariable
void writeVariable(QSqlDatabase sqlDb, GAMSDatabase db, string varName, vector< string > domains)
Write GAMSVariable to MS Access.
Definition: transport9.cpp:185
getModelText
string getModelText()
Get model as string.
Definition: transport9.cpp:53
readFromAccess
GAMSDatabase readFromAccess(GAMSWorkspace ws)
Read data from MS Access into a GAMSDatabase.
Definition: transport9.cpp:154