2 * This program is free software; you can redistribute it and/or modify
3 * it under the terms of the GNU General Public License as published by
4 * the Free Software Foundation; either version 2 of the License, or
5 * (at your option) any later version.
7 * This program is distributed in the hope that it will be useful,
8 * but WITHOUT ANY WARRANTY; without even the implied warranty of
9 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10 * GNU General Public License for more details.
12 * You should have received a copy of the GNU General Public License
13 * along with this program; if not, write to the Free Software
14 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18 * Author : Maxim Mamontov <faust@stargazer.dp.ua>
22 * Tariffs manipulation methods
25 * $Date: 2009/06/09 12:32:40 $
35 #include "postgresql_store.h"
36 #include "stg/locker.h"
38 //-----------------------------------------------------------------------------
39 int POSTGRESQL_STORE::GetTariffsList(vector<string> * tariffsList) const
41 STG_LOCKER lock(&mutex, __FILE__, __LINE__);
43 if (PQstatus(connection) != CONNECTION_OK)
45 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
48 strError = "Connection lost";
49 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): '%s'\n", strError.c_str());
56 if (StartTransaction())
58 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Failed to start transaction'\n");
62 result = PQexec(connection, "SELECT name FROM tb_tariffs");
64 if (PQresultStatus(result) != PGRES_TUPLES_OK)
66 strError = PQresultErrorMessage(result);
68 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): '%s'\n", strError.c_str());
69 if (RollbackTransaction())
71 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Failed to rollback transaction'\n");
76 int tuples = PQntuples(result);
78 for (int i = 0; i < tuples; ++i)
80 tariffsList->push_back(PQgetvalue(result, i, 0));
85 if (CommitTransaction())
87 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Failed to commit transaction'\n");
94 //-----------------------------------------------------------------------------
95 int POSTGRESQL_STORE::AddTariff(const string & name) const
97 STG_LOCKER lock(&mutex, __FILE__, __LINE__);
99 if (PQstatus(connection) != CONNECTION_OK)
101 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
104 strError = "Connection lost";
105 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): '%s'\n", strError.c_str());
112 if (StartTransaction())
114 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to start transaction'\n");
118 std::string ename = name;
120 if (EscapeString(ename))
122 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to escape name'\n");
123 if (RollbackTransaction())
125 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to rollback transaction'\n");
130 std::stringstream query;
131 query << "SELECT sp_add_tariff('" << ename << "', " << DIR_NUM << ")";
133 result = PQexec(connection, query.str().c_str());
135 if (PQresultStatus(result) != PGRES_TUPLES_OK)
137 strError = PQresultErrorMessage(result);
139 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): '%s'\n", strError.c_str());
140 if (RollbackTransaction())
142 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to rollback transaction'\n");
149 if (CommitTransaction())
151 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to commit transaction'\n");
157 //-----------------------------------------------------------------------------
158 int POSTGRESQL_STORE::DelTariff(const string & name) const
160 STG_LOCKER lock(&mutex, __FILE__, __LINE__);
162 if (PQstatus(connection) != CONNECTION_OK)
164 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
167 strError = "Connection lost";
168 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): '%s'\n", strError.c_str());
175 if (StartTransaction())
177 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Failed to start transaction'\n");
181 std::string ename = name;
183 if (EscapeString(ename))
185 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to escape name'\n");
186 if (RollbackTransaction())
188 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to rollback transaction'\n");
193 std::stringstream query;
194 query << "DELETE FROM tb_tariffs WHERE name = '" << ename << "'";
196 result = PQexec(connection, query.str().c_str());
198 if (PQresultStatus(result) != PGRES_COMMAND_OK)
200 strError = PQresultErrorMessage(result);
202 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): '%s'\n", strError.c_str());
203 if (RollbackTransaction())
205 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Failed to rollback transaction'\n");
212 if (CommitTransaction())
214 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Failed to commit transaction'\n");
220 //-----------------------------------------------------------------------------
221 int POSTGRESQL_STORE::SaveTariff(const TARIFF_DATA & td,
222 const string & tariffName) const
224 STG_LOCKER lock(&mutex, __FILE__, __LINE__);
226 if (PQstatus(connection) != CONNECTION_OK)
228 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
231 strError = "Connection lost";
232 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
239 if (StartTransaction())
241 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to start transaction'\n");
245 std::string ename = tariffName;
247 if (EscapeString(ename))
249 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to escape name'\n");
250 if (RollbackTransaction())
252 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
258 double pda, pdb, pna, pnb;
261 std::stringstream query;
262 query << "SELECT pk_tariff FROM tb_tariffs WHERE name = '" << ename << "'";
264 result = PQexec(connection, query.str().c_str());
266 if (PQresultStatus(result) != PGRES_TUPLES_OK)
268 strError = PQresultErrorMessage(result);
270 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
271 if (RollbackTransaction())
273 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
278 int tuples = PQntuples(result);
282 strError = "Failed to fetch tariff ID";
283 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
285 if (RollbackTransaction())
287 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
292 std::stringstream tuple;
293 tuple << PQgetvalue(result, 0, 0);
300 query << "UPDATE tb_tariffs SET \
301 fee = " << td.tariffConf.fee << ", \
302 free = " << td.tariffConf.free << ", \
303 passive_cost = " << td.tariffConf.passiveCost << ", \
304 traff_type = " << td.tariffConf.traffType;
307 query << ", period = '" << TARIFF::PeriodToString(td.tariffConf.period) << "'";
309 query << " WHERE pk_tariff = " << id;
311 result = PQexec(connection, query.str().c_str());
313 if (PQresultStatus(result) != PGRES_COMMAND_OK)
315 strError = PQresultErrorMessage(result);
317 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
318 if (RollbackTransaction())
320 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
327 for(i = 0; i < DIR_NUM; i++)
330 pda = td.dirPrice[i].priceDayA * 1024 * 1024;
331 pdb = td.dirPrice[i].priceDayB * 1024 * 1024;
333 if (td.dirPrice[i].singlePrice)
340 pna = td.dirPrice[i].priceNightA * 1024 * 1024;
341 pnb = td.dirPrice[i].priceNightB * 1024 * 1024;
344 if (td.dirPrice[i].noDiscount)
346 threshold = 0xffFFffFF;
350 threshold = td.dirPrice[i].threshold;
353 std::stringstream query;
354 query << "UPDATE tb_tariffs_params SET \
355 price_day_a = " << pda << ", \
356 price_day_b = " << pdb << ", \
357 price_night_a = " << pna << ", \
358 price_night_b = " << pnb << ", \
359 threshold = " << threshold << ", \
360 time_day_begins = CAST('" << td.dirPrice[i].hDay
362 << td.dirPrice[i].mDay << "' AS TIME), \
363 time_day_ends = CAST('" << td.dirPrice[i].hNight
365 << td.dirPrice[i].mNight << "' AS TIME) \
366 WHERE fk_tariff = " << id << " AND dir_num = " << i;
368 result = PQexec(connection, query.str().c_str());
370 if (PQresultStatus(result) != PGRES_COMMAND_OK)
372 strError = PQresultErrorMessage(result);
374 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
375 if (RollbackTransaction())
377 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
385 if (CommitTransaction())
387 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to commit transaction'\n");
393 //-----------------------------------------------------------------------------
394 int POSTGRESQL_STORE::RestoreTariff(TARIFF_DATA * td,
395 const string & tariffName) const
397 STG_LOCKER lock(&mutex, __FILE__, __LINE__);
399 if (PQstatus(connection) != CONNECTION_OK)
401 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
404 strError = "Connection lost";
405 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): '%s'\n", strError.c_str());
412 if (StartTransaction())
414 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to start transaction'\n");
418 std::string ename = tariffName;
420 if (EscapeString(ename))
422 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to escape name'\n");
423 if (RollbackTransaction())
425 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
430 td->tariffConf.name = tariffName;
432 std::stringstream query;
433 query << "SELECT pk_tariff, \
442 query << " FROM tb_tariffs WHERE name = '" << ename << "'";
444 result = PQexec(connection, query.str().c_str());
446 if (PQresultStatus(result) != PGRES_TUPLES_OK)
448 strError = PQresultErrorMessage(result);
450 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): '%s'\n", strError.c_str());
451 if (RollbackTransaction())
453 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
458 int tuples = PQntuples(result);
462 strError = "Failed to fetch tariff data";
463 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
465 if (RollbackTransaction())
467 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
472 std::stringstream tuple;
473 tuple << PQgetvalue(result, 0, 0) << " ";
474 tuple << PQgetvalue(result, 0, 1) << " ";
475 tuple << PQgetvalue(result, 0, 2) << " ";
476 tuple << PQgetvalue(result, 0, 3) << " ";
477 tuple << PQgetvalue(result, 0, 4) << " ";
481 tuple >> td->tariffConf.fee;
482 tuple >> td->tariffConf.free;
483 tuple >> td->tariffConf.passiveCost;
484 tuple >> td->tariffConf.traffType;
487 td->tariffConf.period = TARIFF::StringToPeriod(PQgetvalue(result, 0, 5));
492 query << "SELECT dir_num, \
498 EXTRACT(hour FROM time_day_begins), \
499 EXTRACT(minute FROM time_day_begins), \
500 EXTRACT(hour FROM time_day_ends), \
501 EXTRACT(minute FROM time_day_ends) \
502 FROM tb_tariffs_params \
503 WHERE fk_tariff = " << id;
505 result = PQexec(connection, query.str().c_str());
507 if (PQresultStatus(result) != PGRES_TUPLES_OK)
509 strError = PQresultErrorMessage(result);
511 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): '%s'\n", strError.c_str());
512 if (RollbackTransaction())
514 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
519 tuples = PQntuples(result);
521 if (tuples != DIR_NUM)
523 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Tariff params count and DIR_NUM does not feet (wanted %d, actually %d)'\n", DIR_NUM, tuples);
526 for (int i = 0; i < std::min(tuples, DIR_NUM); ++i)
528 std::stringstream tuple;
529 tuple << PQgetvalue(result, i, 0) << " ";
530 tuple << PQgetvalue(result, i, 1) << " ";
531 tuple << PQgetvalue(result, i, 2) << " ";
532 tuple << PQgetvalue(result, i, 3) << " ";
533 tuple << PQgetvalue(result, i, 4) << " ";
534 tuple << PQgetvalue(result, i, 5) << " ";
535 tuple << PQgetvalue(result, i, 6) << " ";
536 tuple << PQgetvalue(result, i, 7) << " ";
537 tuple << PQgetvalue(result, i, 8) << " ";
538 tuple << PQgetvalue(result, i, 9) << " ";
543 tuple >> td->dirPrice[dir].priceDayA;
544 td->dirPrice[dir].priceDayA /= 1024 * 1024;
545 tuple >> td->dirPrice[dir].priceDayB;
546 td->dirPrice[dir].priceDayB /= 1024 * 1024;
547 tuple >> td->dirPrice[dir].priceNightA;
548 td->dirPrice[dir].priceNightA /= 1024 * 1024;
549 tuple >> td->dirPrice[dir].priceNightB;
550 td->dirPrice[dir].priceNightB /= 1024 * 1024;
551 tuple >> td->dirPrice[dir].threshold;
552 tuple >> td->dirPrice[dir].hDay;
553 tuple >> td->dirPrice[dir].mDay;
554 tuple >> td->dirPrice[dir].hNight;
555 tuple >> td->dirPrice[dir].mNight;
557 if (td->dirPrice[dir].priceDayA == td->dirPrice[dir].priceNightA &&
558 td->dirPrice[dir].priceDayB == td->dirPrice[dir].priceNightB)
560 td->dirPrice[dir].singlePrice = true;
564 td->dirPrice[dir].singlePrice = false;
566 if (td->dirPrice[dir].threshold == (int)0xffFFffFF)
568 td->dirPrice[dir].noDiscount = true;
573 td->dirPrice[dir].noDiscount = false;
580 if (CommitTransaction())
582 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to commit transaction'\n");
588 //-----------------------------------------------------------------------------