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 "stg/common.h"
37 #include "postgresql_store.h"
38 #include "stg/locker.h"
43 const int pt_mega = 1024 * 1024;
47 //-----------------------------------------------------------------------------
48 int POSTGRESQL_STORE::GetTariffsList(std::vector<std::string> * tariffsList) const
50 STG_LOCKER lock(&mutex);
52 if (PQstatus(connection) != CONNECTION_OK)
54 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
57 strError = "Connection lost";
58 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): '%s'\n", strError.c_str());
65 if (StartTransaction())
67 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Failed to start transaction'\n");
71 result = PQexec(connection, "SELECT name FROM tb_tariffs");
73 if (PQresultStatus(result) != PGRES_TUPLES_OK)
75 strError = PQresultErrorMessage(result);
77 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): '%s'\n", strError.c_str());
78 if (RollbackTransaction())
80 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Failed to rollback transaction'\n");
85 int tuples = PQntuples(result);
87 for (int i = 0; i < tuples; ++i)
89 tariffsList->push_back(PQgetvalue(result, i, 0));
94 if (CommitTransaction())
96 printfd(__FILE__, "POSTGRESQL_STORE::GetTariffsList(): 'Failed to commit transaction'\n");
103 //-----------------------------------------------------------------------------
104 int POSTGRESQL_STORE::AddTariff(const std::string & name) const
106 STG_LOCKER lock(&mutex);
108 if (PQstatus(connection) != CONNECTION_OK)
110 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
113 strError = "Connection lost";
114 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): '%s'\n", strError.c_str());
121 if (StartTransaction())
123 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to start transaction'\n");
127 std::string ename = name;
129 if (EscapeString(ename))
131 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to escape name'\n");
132 if (RollbackTransaction())
134 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to rollback transaction'\n");
139 std::ostringstream query;
140 query << "SELECT sp_add_tariff('" << ename << "', " << DIR_NUM << ")";
142 result = PQexec(connection, query.str().c_str());
144 if (PQresultStatus(result) != PGRES_TUPLES_OK)
146 strError = PQresultErrorMessage(result);
148 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): '%s'\n", strError.c_str());
149 if (RollbackTransaction())
151 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to rollback transaction'\n");
158 if (CommitTransaction())
160 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to commit transaction'\n");
166 //-----------------------------------------------------------------------------
167 int POSTGRESQL_STORE::DelTariff(const std::string & name) const
169 STG_LOCKER lock(&mutex);
171 if (PQstatus(connection) != CONNECTION_OK)
173 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
176 strError = "Connection lost";
177 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): '%s'\n", strError.c_str());
184 if (StartTransaction())
186 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Failed to start transaction'\n");
190 std::string ename = name;
192 if (EscapeString(ename))
194 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to escape name'\n");
195 if (RollbackTransaction())
197 printfd(__FILE__, "POSTGRESQL_STORE::AddTariff(): 'Failed to rollback transaction'\n");
202 std::ostringstream query;
203 query << "DELETE FROM tb_tariffs WHERE name = '" << ename << "'";
205 result = PQexec(connection, query.str().c_str());
207 if (PQresultStatus(result) != PGRES_COMMAND_OK)
209 strError = PQresultErrorMessage(result);
211 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): '%s'\n", strError.c_str());
212 if (RollbackTransaction())
214 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Failed to rollback transaction'\n");
221 if (CommitTransaction())
223 printfd(__FILE__, "POSTGRESQL_STORE::DelTariff(): 'Failed to commit transaction'\n");
229 //-----------------------------------------------------------------------------
230 int POSTGRESQL_STORE::SaveTariff(const TARIFF_DATA & td,
231 const std::string & tariffName) const
233 STG_LOCKER lock(&mutex);
235 if (PQstatus(connection) != CONNECTION_OK)
237 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
240 strError = "Connection lost";
241 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
248 if (StartTransaction())
250 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to start transaction'\n");
254 std::string ename = tariffName;
256 if (EscapeString(ename))
258 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to escape name'\n");
259 if (RollbackTransaction())
261 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
267 std::ostringstream query;
268 query << "SELECT pk_tariff FROM tb_tariffs WHERE name = '" << ename << "'";
270 result = PQexec(connection, query.str().c_str());
273 if (PQresultStatus(result) != PGRES_TUPLES_OK)
275 strError = PQresultErrorMessage(result);
277 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
278 if (RollbackTransaction())
280 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
285 int tuples = PQntuples(result);
289 strError = "Failed to fetch tariff ID";
290 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
292 if (RollbackTransaction())
294 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
302 std::stringstream tuple;
303 tuple << PQgetvalue(result, 0, 0);
311 std::ostringstream query;
312 query << "UPDATE tb_tariffs SET \
313 fee = " << td.tariffConf.fee << ", \
314 free = " << td.tariffConf.free << ", \
315 passive_cost = " << td.tariffConf.passiveCost << ", \
316 traff_type = " << td.tariffConf.traffType;
319 query << ", period = '" << TARIFF::PeriodToString(td.tariffConf.period) << "'";
322 query << ", change_policy = '" << TARIFF::ChangePolicyToString(td.tariffConf.changePolicy) << "', \
323 change_policy_timeout = CAST('" << formatTime(td.tariffConf.changePolicyTimeout) << "' AS TIMESTAMP)";
325 query << " WHERE pk_tariff = " << id;
327 result = PQexec(connection, query.str().c_str());
330 if (PQresultStatus(result) != PGRES_COMMAND_OK)
332 strError = PQresultErrorMessage(result);
334 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
335 if (RollbackTransaction())
337 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
344 for(int i = 0; i < DIR_NUM; i++)
346 double pda = td.dirPrice[i].priceDayA * 1024 * 1024;
347 double pdb = td.dirPrice[i].priceDayB * 1024 * 1024;
351 if (td.dirPrice[i].singlePrice)
358 pna = td.dirPrice[i].priceNightA * 1024 * 1024;
359 pnb = td.dirPrice[i].priceNightB * 1024 * 1024;
363 if (td.dirPrice[i].noDiscount)
365 threshold = 0xffFFffFF;
369 threshold = td.dirPrice[i].threshold;
373 std::ostringstream query;
374 query << "UPDATE tb_tariffs_params SET \
375 price_day_a = " << pda << ", \
376 price_day_b = " << pdb << ", \
377 price_night_a = " << pna << ", \
378 price_night_b = " << pnb << ", \
379 threshold = " << threshold << ", \
380 time_day_begins = CAST('" << td.dirPrice[i].hDay
382 << td.dirPrice[i].mDay << "' AS TIME), \
383 time_day_ends = CAST('" << td.dirPrice[i].hNight
385 << td.dirPrice[i].mNight << "' AS TIME) \
386 WHERE fk_tariff = " << id << " AND dir_num = " << i;
388 result = PQexec(connection, query.str().c_str());
391 if (PQresultStatus(result) != PGRES_COMMAND_OK)
393 strError = PQresultErrorMessage(result);
395 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): '%s'\n", strError.c_str());
396 if (RollbackTransaction())
398 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to rollback transaction'\n");
406 if (CommitTransaction())
408 printfd(__FILE__, "POSTGRESQL_STORE::SaveTariff(): 'Failed to commit transaction'\n");
414 //-----------------------------------------------------------------------------
415 int POSTGRESQL_STORE::RestoreTariff(TARIFF_DATA * td,
416 const std::string & tariffName) const
418 STG_LOCKER lock(&mutex);
420 if (PQstatus(connection) != CONNECTION_OK)
422 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Connection lost. Trying to reconnect...'\n", strError.c_str());
425 strError = "Connection lost";
426 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): '%s'\n", strError.c_str());
433 if (StartTransaction())
435 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to start transaction'\n");
439 std::string ename = tariffName;
441 if (EscapeString(ename))
443 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to escape name'\n");
444 if (RollbackTransaction())
446 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
451 td->tariffConf.name = tariffName;
453 std::ostringstream query;
454 query << "SELECT pk_tariff, \
464 query << ", change_policy \
465 , change_policy_timeout";
467 query << " FROM tb_tariffs WHERE name = '" << ename << "'";
469 result = PQexec(connection, query.str().c_str());
471 if (PQresultStatus(result) != PGRES_TUPLES_OK)
473 strError = PQresultErrorMessage(result);
475 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): '%s'\n", strError.c_str());
476 if (RollbackTransaction())
478 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
483 int tuples = PQntuples(result);
487 strError = "Failed to fetch tariff data";
488 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Invalid number of tuples. Wanted 1, actulally %d'\n", tuples);
490 if (RollbackTransaction())
492 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
500 std::stringstream tuple;
501 tuple << PQgetvalue(result, 0, 0) << " ";
502 tuple << PQgetvalue(result, 0, 1) << " ";
503 tuple << PQgetvalue(result, 0, 2) << " ";
504 tuple << PQgetvalue(result, 0, 3) << " ";
505 tuple << PQgetvalue(result, 0, 4) << " ";
508 tuple >> td->tariffConf.fee;
509 tuple >> td->tariffConf.free;
510 tuple >> td->tariffConf.passiveCost;
511 tuple >> td->tariffConf.traffType;
515 td->tariffConf.period = TARIFF::StringToPeriod(PQgetvalue(result, 0, 5));
519 td->tariffConf.changePolicy = TARIFF::StringToChangePolicy(PQgetvalue(result, 0, 6));
520 td->tariffConf.changePolicyTimeout = readTime(PQgetvalue(result, 0, 7));
526 query << "SELECT dir_num, \
532 EXTRACT(hour FROM time_day_begins), \
533 EXTRACT(minute FROM time_day_begins), \
534 EXTRACT(hour FROM time_day_ends), \
535 EXTRACT(minute FROM time_day_ends) \
536 FROM tb_tariffs_params \
537 WHERE fk_tariff = " << id;
539 result = PQexec(connection, query.str().c_str());
541 if (PQresultStatus(result) != PGRES_TUPLES_OK)
543 strError = PQresultErrorMessage(result);
545 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): '%s'\n", strError.c_str());
546 if (RollbackTransaction())
548 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to rollback transaction'\n");
553 tuples = PQntuples(result);
555 if (tuples != DIR_NUM)
557 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Tariff params count and DIR_NUM does not feet (wanted %d, actually %d)'\n", DIR_NUM, tuples);
560 for (int i = 0; i < std::min(tuples, DIR_NUM); ++i)
565 std::stringstream tuple;
566 tuple << PQgetvalue(result, i, 0) << " ";
567 tuple << PQgetvalue(result, i, 1) << " ";
568 tuple << PQgetvalue(result, i, 2) << " ";
569 tuple << PQgetvalue(result, i, 3) << " ";
570 tuple << PQgetvalue(result, i, 4) << " ";
571 tuple << PQgetvalue(result, i, 5) << " ";
572 tuple << PQgetvalue(result, i, 6) << " ";
573 tuple << PQgetvalue(result, i, 7) << " ";
574 tuple << PQgetvalue(result, i, 8) << " ";
575 tuple << PQgetvalue(result, i, 9) << " ";
578 tuple >> td->dirPrice[dir].priceDayA;
579 td->dirPrice[dir].priceDayA /= 1024 * 1024;
580 tuple >> td->dirPrice[dir].priceDayB;
581 td->dirPrice[dir].priceDayB /= 1024 * 1024;
582 tuple >> td->dirPrice[dir].priceNightA;
583 td->dirPrice[dir].priceNightA /= 1024 * 1024;
584 tuple >> td->dirPrice[dir].priceNightB;
585 td->dirPrice[dir].priceNightB /= 1024 * 1024;
586 tuple >> td->dirPrice[dir].threshold;
587 tuple >> td->dirPrice[dir].hDay;
588 tuple >> td->dirPrice[dir].mDay;
589 tuple >> td->dirPrice[dir].hNight;
590 tuple >> td->dirPrice[dir].mNight;
593 if (std::fabs(td->dirPrice[dir].priceDayA - td->dirPrice[dir].priceNightA) < 1.0e-3 / pt_mega &&
594 std::fabs(td->dirPrice[dir].priceDayB - td->dirPrice[dir].priceNightB) < 1.0e-3 / pt_mega)
596 td->dirPrice[dir].singlePrice = true;
600 td->dirPrice[dir].singlePrice = false;
602 if (td->dirPrice[dir].threshold == (int)0xffFFffFF)
604 td->dirPrice[dir].noDiscount = true;
609 td->dirPrice[dir].noDiscount = false;
616 if (CommitTransaction())
618 printfd(__FILE__, "POSTGRESQL_STORE::RestoreTariff(): 'Failed to commit transaction'\n");
624 //-----------------------------------------------------------------------------