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 $
36 #include "postgresql_store.h"
37 #include "stg/locker.h"
39 //-----------------------------------------------------------------------------
40 int POSTGRESQL_STORE::GetTariffsList(std::vector<std::string> * tariffsList) const
42 STG_LOCKER lock(&mutex, __FILE__, __LINE__);
44 if (PQstatus(connection) != CONNECTION_OK)
46 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
49 strError = "Connection lost";
50 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): '%s'\n", strError.c_str());
57 if (StartTransaction())
59 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Failed to start transaction'\n");
63 result = PQexec(connection, "SELECT name FROM tb_tariffs");
65 if (PQresultStatus(result) != PGRES_TUPLES_OK)
67 strError = PQresultErrorMessage(result);
69 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): '%s'\n", strError.c_str());
70 if (RollbackTransaction())
72 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Failed to rollback transaction'\n");
77 int tuples = PQntuples(result);
79 for (int i = 0; i < tuples; ++i)
81 tariffsList->push_back(PQgetvalue(result, i, 0));
86 if (CommitTransaction())
88 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Failed to commit transaction'\n");
95 //-----------------------------------------------------------------------------
96 int POSTGRESQL_STORE::AddTariff(const std::string & name) const
98 STG_LOCKER lock(&mutex, __FILE__, __LINE__);
100 if (PQstatus(connection) != CONNECTION_OK)
102 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
105 strError = "Connection lost";
106 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): '%s'\n", strError.c_str());
113 if (StartTransaction())
115 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to start transaction'\n");
119 std::string ename = name;
121 if (EscapeString(ename))
123 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to escape name'\n");
124 if (RollbackTransaction())
126 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to rollback transaction'\n");
131 std::ostringstream query;
132 query << "SELECT sp_add_tariff('" << ename << "', " << DIR_NUM << ")";
134 result = PQexec(connection, query.str().c_str());
136 if (PQresultStatus(result) != PGRES_TUPLES_OK)
138 strError = PQresultErrorMessage(result);
140 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): '%s'\n", strError.c_str());
141 if (RollbackTransaction())
143 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to rollback transaction'\n");
150 if (CommitTransaction())
152 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to commit transaction'\n");
158 //-----------------------------------------------------------------------------
159 int POSTGRESQL_STORE::DelTariff(const std::string & name) const
161 STG_LOCKER lock(&mutex, __FILE__, __LINE__);
163 if (PQstatus(connection) != CONNECTION_OK)
165 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
168 strError = "Connection lost";
169 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): '%s'\n", strError.c_str());
176 if (StartTransaction())
178 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Failed to start transaction'\n");
182 std::string ename = name;
184 if (EscapeString(ename))
186 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to escape name'\n");
187 if (RollbackTransaction())
189 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to rollback transaction'\n");
194 std::ostringstream query;
195 query << "DELETE FROM tb_tariffs WHERE name = '" << ename << "'";
197 result = PQexec(connection, query.str().c_str());
199 if (PQresultStatus(result) != PGRES_COMMAND_OK)
201 strError = PQresultErrorMessage(result);
203 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): '%s'\n", strError.c_str());
204 if (RollbackTransaction())
206 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Failed to rollback transaction'\n");
213 if (CommitTransaction())
215 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Failed to commit transaction'\n");
221 //-----------------------------------------------------------------------------
222 int POSTGRESQL_STORE::SaveTariff(const TARIFF_DATA & td,
223 const std::string & tariffName) const
225 STG_LOCKER lock(&mutex, __FILE__, __LINE__);
227 if (PQstatus(connection) != CONNECTION_OK)
229 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
232 strError = "Connection lost";
233 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
240 if (StartTransaction())
242 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to start transaction'\n");
246 std::string ename = tariffName;
248 if (EscapeString(ename))
250 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to escape name'\n");
251 if (RollbackTransaction())
253 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
259 std::ostringstream query;
260 query << "SELECT pk_tariff FROM tb_tariffs WHERE name = '" << ename << "'";
262 result = PQexec(connection, query.str().c_str());
265 if (PQresultStatus(result) != PGRES_TUPLES_OK)
267 strError = PQresultErrorMessage(result);
269 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
270 if (RollbackTransaction())
272 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
277 int tuples = PQntuples(result);
281 strError = "Failed to fetch tariff ID";
282 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
284 if (RollbackTransaction())
286 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
292 std::stringstream tuple;
293 tuple << PQgetvalue(result, 0, 0);
302 std::ostringstream query;
303 query << "UPDATE tb_tariffs SET \
304 fee = " << td.tariffConf.fee << ", \
305 free = " << td.tariffConf.free << ", \
306 passive_cost = " << td.tariffConf.passiveCost << ", \
307 traff_type = " << td.tariffConf.traffType;
310 query << ", period = '" << TARIFF::PeriodToString(td.tariffConf.period) << "'";
312 query << " WHERE pk_tariff = " << id;
314 result = PQexec(connection, query.str().c_str());
317 if (PQresultStatus(result) != PGRES_COMMAND_OK)
319 strError = PQresultErrorMessage(result);
321 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
322 if (RollbackTransaction())
324 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
331 for(int i = 0; i < DIR_NUM; i++)
333 double pda = td.dirPrice[i].priceDayA * 1024 * 1024;
334 double pdb = td.dirPrice[i].priceDayB * 1024 * 1024;
338 if (td.dirPrice[i].singlePrice)
345 pna = td.dirPrice[i].priceNightA * 1024 * 1024;
346 pnb = td.dirPrice[i].priceNightB * 1024 * 1024;
350 if (td.dirPrice[i].noDiscount)
352 threshold = 0xffFFffFF;
356 threshold = td.dirPrice[i].threshold;
360 std::ostringstream query;
361 query << "UPDATE tb_tariffs_params SET \
362 price_day_a = " << pda << ", \
363 price_day_b = " << pdb << ", \
364 price_night_a = " << pna << ", \
365 price_night_b = " << pnb << ", \
366 threshold = " << threshold << ", \
367 time_day_begins = CAST('" << td.dirPrice[i].hDay
369 << td.dirPrice[i].mDay << "' AS TIME), \
370 time_day_ends = CAST('" << td.dirPrice[i].hNight
372 << td.dirPrice[i].mNight << "' AS TIME) \
373 WHERE fk_tariff = " << id << " AND dir_num = " << i;
375 result = PQexec(connection, query.str().c_str());
378 if (PQresultStatus(result) != PGRES_COMMAND_OK)
380 strError = PQresultErrorMessage(result);
382 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
383 if (RollbackTransaction())
385 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
393 if (CommitTransaction())
395 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to commit transaction'\n");
401 //-----------------------------------------------------------------------------
402 int POSTGRESQL_STORE::RestoreTariff(TARIFF_DATA * td,
403 const std::string & tariffName) const
405 STG_LOCKER lock(&mutex, __FILE__, __LINE__);
407 if (PQstatus(connection) != CONNECTION_OK)
409 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
412 strError = "Connection lost";
413 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): '%s'\n", strError.c_str());
420 if (StartTransaction())
422 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to start transaction'\n");
426 std::string ename = tariffName;
428 if (EscapeString(ename))
430 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to escape name'\n");
431 if (RollbackTransaction())
433 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
438 td->tariffConf.name = tariffName;
440 std::ostringstream query;
441 query << "SELECT pk_tariff, \
450 query << " FROM tb_tariffs WHERE name = '" << ename << "'";
452 result = PQexec(connection, query.str().c_str());
454 if (PQresultStatus(result) != PGRES_TUPLES_OK)
456 strError = PQresultErrorMessage(result);
458 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): '%s'\n", strError.c_str());
459 if (RollbackTransaction())
461 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
466 int tuples = PQntuples(result);
470 strError = "Failed to fetch tariff data";
471 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
473 if (RollbackTransaction())
475 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
483 std::stringstream tuple;
484 tuple << PQgetvalue(result, 0, 0) << " ";
485 tuple << PQgetvalue(result, 0, 1) << " ";
486 tuple << PQgetvalue(result, 0, 2) << " ";
487 tuple << PQgetvalue(result, 0, 3) << " ";
488 tuple << PQgetvalue(result, 0, 4) << " ";
491 tuple >> td->tariffConf.fee;
492 tuple >> td->tariffConf.free;
493 tuple >> td->tariffConf.passiveCost;
494 tuple >> td->tariffConf.traffType;
498 td->tariffConf.period = TARIFF::StringToPeriod(PQgetvalue(result, 0, 5));
503 query << "SELECT dir_num, \
509 EXTRACT(hour FROM time_day_begins), \
510 EXTRACT(minute FROM time_day_begins), \
511 EXTRACT(hour FROM time_day_ends), \
512 EXTRACT(minute FROM time_day_ends) \
513 FROM tb_tariffs_params \
514 WHERE fk_tariff = " << id;
516 result = PQexec(connection, query.str().c_str());
518 if (PQresultStatus(result) != PGRES_TUPLES_OK)
520 strError = PQresultErrorMessage(result);
522 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): '%s'\n", strError.c_str());
523 if (RollbackTransaction())
525 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
530 tuples = PQntuples(result);
532 if (tuples != DIR_NUM)
534 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Tariff params count and DIR_NUM does not feet (wanted %d, actually %d)'\n", DIR_NUM, tuples);
537 for (int i = 0; i < std::min(tuples, DIR_NUM); ++i)
542 std::stringstream tuple;
543 tuple << PQgetvalue(result, i, 0) << " ";
544 tuple << PQgetvalue(result, i, 1) << " ";
545 tuple << PQgetvalue(result, i, 2) << " ";
546 tuple << PQgetvalue(result, i, 3) << " ";
547 tuple << PQgetvalue(result, i, 4) << " ";
548 tuple << PQgetvalue(result, i, 5) << " ";
549 tuple << PQgetvalue(result, i, 6) << " ";
550 tuple << PQgetvalue(result, i, 7) << " ";
551 tuple << PQgetvalue(result, i, 8) << " ";
552 tuple << PQgetvalue(result, i, 9) << " ";
555 tuple >> td->dirPrice[dir].priceDayA;
556 td->dirPrice[dir].priceDayA /= 1024 * 1024;
557 tuple >> td->dirPrice[dir].priceDayB;
558 td->dirPrice[dir].priceDayB /= 1024 * 1024;
559 tuple >> td->dirPrice[dir].priceNightA;
560 td->dirPrice[dir].priceNightA /= 1024 * 1024;
561 tuple >> td->dirPrice[dir].priceNightB;
562 td->dirPrice[dir].priceNightB /= 1024 * 1024;
563 tuple >> td->dirPrice[dir].threshold;
564 tuple >> td->dirPrice[dir].hDay;
565 tuple >> td->dirPrice[dir].mDay;
566 tuple >> td->dirPrice[dir].hNight;
567 tuple >> td->dirPrice[dir].mNight;
570 if (std::fabs(td->dirPrice[dir].priceDayA - td->dirPrice[dir].priceNightA) > 1.0e-3 &&
571 std::fabs(td->dirPrice[dir].priceDayB - td->dirPrice[dir].priceNightB) > 1.0e-3)
573 td->dirPrice[dir].singlePrice = true;
577 td->dirPrice[dir].singlePrice = false;
579 if (td->dirPrice[dir].threshold == (int)0xffFFffFF)
581 td->dirPrice[dir].noDiscount = true;
586 td->dirPrice[dir].noDiscount = false;
593 if (CommitTransaction())
595 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to commit transaction'\n");
601 //-----------------------------------------------------------------------------