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 * User manipulation methods
25 * $Date: 2010/01/19 11:07:25 $
29 #include "stg/const.h"
30 #include "firebird_store.h"
33 //-----------------------------------------------------------------------------
34 int FIREBIRD_STORE::GetUsersList(std::vector<std::string> * usersList) const
36 STG_LOCKER lock(&mutex);
38 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amRead, til, tlr);
39 IBPP::Statement st = IBPP::StatementFactory(db, tr);
46 st->Execute("select name from tb_users");
50 usersList->push_back(name);
55 catch (IBPP::Exception & ex)
58 strError = "IBPP exception";
59 printfd(__FILE__, ex.what());
65 //-----------------------------------------------------------------------------
66 int FIREBIRD_STORE::AddUser(const std::string & name) const
68 STG_LOCKER lock(&mutex);
70 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amWrite, til, tlr);
71 IBPP::Statement st = IBPP::StatementFactory(db, tr);
76 st->Prepare("execute procedure sp_add_user(?, ?)");
83 catch (IBPP::Exception & ex)
86 strError = "IBPP exception";
87 printfd(__FILE__, ex.what());
93 //-----------------------------------------------------------------------------
94 int FIREBIRD_STORE::DelUser(const std::string & login) const
96 STG_LOCKER lock(&mutex);
98 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amWrite, til, tlr);
99 IBPP::Statement st = IBPP::StatementFactory(db, tr);
104 st->Prepare("execute procedure sp_delete_user(?)");
110 catch (IBPP::Exception & ex)
113 strError = "IBPP exception";
114 printfd(__FILE__, ex.what());
120 //-----------------------------------------------------------------------------
121 int FIREBIRD_STORE::SaveUserStat(const USER_STAT & stat,
122 const std::string & login) const
124 STG_LOCKER lock(&mutex);
126 return SaveStat(stat, login);
128 //-----------------------------------------------------------------------------
129 int FIREBIRD_STORE::SaveStat(const USER_STAT & stat,
130 const std::string & login,
134 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amWrite, til, tlr);
135 IBPP::Statement st = IBPP::StatementFactory(db, tr);
140 st->Prepare("select pk_user from tb_users where name = ?");
145 strError = "User \"" + login + "\" not found in database";
146 printfd(__FILE__, "User '%s' not found in database\n", login.c_str());
152 st->Prepare("select first 1 pk_stat from tb_stats where fk_user = ? order by stats_date desc");
158 strError = "No stat info for user \"" + login + "\"";
159 printfd(__FILE__, "No stat info for user '%s'\n", login.c_str());
166 IBPP::Timestamp actTime;
167 time_t2ts(stat.lastActivityTime, &actTime);
168 IBPP::Timestamp addTime;
169 time_t2ts(stat.lastCashAddTime, &addTime);
172 ym2date(year, month, &dt);
176 st->Prepare("update tb_stats set \
179 last_activity_time = ?, \
181 last_cash_add_time = ?, \
186 st->Set(1, stat.cash);
187 st->Set(2, stat.freeMb);
189 st->Set(4, stat.lastCashAdd);
191 st->Set(6, (int32_t)stat.passiveTime);
198 for(int i = 0; i < DIR_NUM; i++)
200 st->Prepare("update tb_stats_traffic set \
203 where fk_stat = ? and dir_num = ?");
204 st->Set(1, (int64_t)stat.monthUp[i]);
205 st->Set(2, (int64_t)stat.monthDown[i]);
215 catch (IBPP::Exception & ex)
218 strError = "IBPP exception";
219 printfd(__FILE__, ex.what());
225 //-----------------------------------------------------------------------------
226 int FIREBIRD_STORE::SaveUserConf(const USER_CONF & conf,
227 const std::string & login) const
229 STG_LOCKER lock(&mutex);
231 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amWrite, til, tlr);
232 IBPP::Statement st = IBPP::StatementFactory(db, tr);
237 st->Prepare("select pk_user from tb_users where name = ?");
242 strError = "User \"" + login + "\" not found in database";
243 printfd(__FILE__, "User '%s' not found in database\n", login.c_str());
251 IBPP::Timestamp creditExpire;
252 time_t2ts(conf.creditExpire, &creditExpire);
254 st->Prepare("update tb_users set \
260 disabled_detail_stat = ?, \
267 fk_tariff = (select pk_tariff from tb_tariffs \
269 fk_tariff_change = (select pk_tariff from tb_tariffs \
271 fk_corporation = (select pk_corporation from tb_corporations \
276 st->Set(1, conf.address);
277 st->Set(2, (bool)conf.alwaysOnline);
278 st->Set(3, conf.credit);
279 st->Set(4, creditExpire);
280 st->Set(5, (bool)conf.disabled);
281 st->Set(6, (bool)conf.disabledDetailStat);
282 st->Set(7, conf.email);
283 st->Set(8, conf.group);
284 st->Set(9, conf.note);
285 st->Set(10, (bool)conf.passive);
286 st->Set(11, conf.password);
287 st->Set(12, conf.phone);
288 st->Set(13, conf.tariffName);
289 st->Set(14, conf.nextTariff);
290 st->Set(15, conf.corp);
291 st->Set(16, conf.realName);
297 st->Prepare("delete from tb_users_services where fk_user = ?");
302 st->Prepare("insert into tb_users_services (fk_user, fk_service) \
303 values (?, (select pk_service from tb_services \
305 for(std::vector<std::string>::const_iterator it = conf.service.begin(); it != conf.service.end(); ++it)
313 st->Prepare("delete from tb_users_data where fk_user = ?");
319 st->Prepare("insert into tb_users_data (fk_user, data, num) values (?, ?, ?)");
320 for (std::vector<std::string>::const_iterator it = conf.userdata.begin(); it != conf.userdata.end(); ++it)
329 st->Prepare("delete from tb_allowed_ip where fk_user = ?");
333 st->Prepare("insert into tb_allowed_ip (fk_user, ip, mask) values (?, ?, ?)");
334 for(size_t i = 0; i < conf.ips.Count(); i++)
337 st->Set(2, (int32_t)conf.ips[i].ip);
338 st->Set(3, (int32_t)conf.ips[i].mask);
343 catch (IBPP::Exception & ex)
346 strError = "IBPP exception";
347 printfd(__FILE__, ex.what());
353 //-----------------------------------------------------------------------------
354 int FIREBIRD_STORE::RestoreUserStat(USER_STAT * stat,
355 const std::string & login) const
357 STG_LOCKER lock(&mutex);
359 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amRead, til, tlr);
360 IBPP::Statement st = IBPP::StatementFactory(db, tr);
365 st->Prepare("select pk_user from tb_users where name = ?");
370 strError = "User \"" + login + "\" not found in database";
371 printfd(__FILE__, "User '%s' not found in database\n", login.c_str());
378 st->Prepare("select first 1 pk_stat, cash, free_mb, last_activity_time, \
379 last_cash_add, last_cash_add_time, passive_time from tb_stats \
380 where fk_user = ? order by stats_date desc");
385 strError = "No stat info for user \"" + login + "\"";
386 printfd(__FILE__, "No stat info for user '%s'\n", login.c_str());
393 st->Get(2, stat->cash);
394 st->Get(3, stat->freeMb);
395 IBPP::Timestamp actTime;
397 st->Get(5, stat->lastCashAdd);
398 IBPP::Timestamp addTime;
401 st->Get(7, passiveTime);
403 stat->passiveTime = passiveTime;
405 stat->lastActivityTime = ts2time_t(actTime);
407 stat->lastCashAddTime = ts2time_t(addTime);
410 st->Prepare("select * from tb_stats_traffic where fk_stat = ?");
413 for(int i = 0; i < DIR_NUM; i++)
419 st->Get(5, (int64_t &)stat->monthUp[dir]);
420 st->Get(4, (int64_t &)stat->monthDown[dir]);
430 catch (IBPP::Exception & ex)
433 strError = "IBPP exception";
434 printfd(__FILE__, ex.what());
440 //-----------------------------------------------------------------------------
441 int FIREBIRD_STORE::RestoreUserConf(USER_CONF * conf,
442 const std::string & login) const
444 STG_LOCKER lock(&mutex);
446 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amRead, til, tlr);
447 IBPP::Statement st = IBPP::StatementFactory(db, tr);
452 st->Prepare("select tb_users.pk_user, tb_users.address, tb_users.always_online, \
453 tb_users.credit, tb_users.credit_expire, tb_users.disabled, \
454 tb_users.disabled_detail_stat, tb_users.email, tb_users.grp, \
455 tb_users.note, tb_users.passive, tb_users.passwd, tb_users.phone, \
456 tb_users.real_name, tf1.name, tf2.name, tb_corporations.name \
457 from tb_users left join tb_tariffs tf1 \
458 on tf1.pk_tariff = tb_users.fk_tariff \
459 left join tb_tariffs tf2 \
460 on tf2.pk_tariff = tb_users.fk_tariff_change \
461 left join tb_corporations \
462 on tb_corporations.pk_corporation = tb_users.fk_corporation \
463 where tb_users.name = ?");
468 strError = "User \"" + login + "\" not found in database";
469 printfd(__FILE__, "User '%s' not found in database", login.c_str());
475 // Getting base config
476 st->Get(2, conf->address);
479 conf->alwaysOnline = test;
480 st->Get(4, conf->credit);
481 IBPP::Timestamp timestamp;
482 st->Get(5, timestamp);
484 conf->creditExpire = ts2time_t(timestamp);
487 conf->disabled = test;
489 conf->disabledDetailStat = test;
490 st->Get(8, conf->email);
491 st->Get(9, conf->group);
492 st->Get(10, conf->note);
494 conf->passive = test;
495 st->Get(12, conf->password);
496 st->Get(13, conf->phone);
497 st->Get(14, conf->realName);
498 st->Get(15, conf->tariffName);
499 st->Get(16, conf->nextTariff);
500 st->Get(17, conf->corp);
502 if (conf->tariffName == "")
503 conf->tariffName = NO_TARIFF_NAME;
504 if (conf->corp == "")
505 conf->corp = NO_CORP_NAME;
509 st->Prepare("select name from tb_services \
510 where pk_service in \
511 (select fk_service from tb_users_services \
512 where fk_user = ?)");
519 conf->service.push_back(name);
524 st->Prepare("select data, num from tb_users_data where fk_user = ? order by num");
531 st->Get(1, conf->userdata[i]);
536 st->Prepare("select ip, mask from tb_allowed_ip \
544 st->Get(1, (int32_t &)im.ip);
545 st->Get(2, (int32_t &)im.mask);
551 catch (IBPP::Exception & ex)
554 strError = "IBPP exception";
555 printfd(__FILE__, ex.what());
561 //-----------------------------------------------------------------------------
562 int FIREBIRD_STORE::WriteUserChgLog(const std::string & login,
563 const std::string & admLogin,
565 const std::string & paramName,
566 const std::string & oldValue,
567 const std::string & newValue,
568 const std::string & message = "") const
570 STG_LOCKER lock(&mutex);
572 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amWrite, til, tlr);
573 IBPP::Statement st = IBPP::StatementFactory(db, tr);
577 std::string temp = ""; // Composed message for log
582 temp += "Admin \"" + admLogin + "\", ";
583 temp += inet_ntostring(admIP);
585 temp = temp + message;
586 //----------------------------------------------------------------------------------------
587 // Checking and inserting parameters in params table
588 st->Prepare("select pk_parameter from tb_parameters where name = ?");
589 st->Set(1, paramName);
594 st->Prepare("insert into tb_parameters (name) values (?)");
595 st->Set(1, paramName);
599 //----------------------------------------------------------------------------------------
600 st->Prepare("insert into tb_params_log \
601 (fk_user, fk_parameter, event_time, from_val, to_val, comment) \
602 values ((select pk_user from tb_users \
604 (select pk_parameter from tb_parameters \
608 st->Set(2, paramName);
610 st->Set(4, oldValue);
611 st->Set(5, newValue);
617 catch (IBPP::Exception & ex)
620 strError = "IBPP exception";
621 printfd(__FILE__, ex.what());
627 //-----------------------------------------------------------------------------
628 int FIREBIRD_STORE::WriteUserConnect(const std::string & login, uint32_t ip) const
630 STG_LOCKER lock(&mutex);
632 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amWrite, til, tlr);
633 IBPP::Statement st = IBPP::StatementFactory(db, tr);
640 st->Prepare("execute procedure sp_append_session_log(?, ?, 'c', ?)");
643 st->Set(3, (int32_t)ip);
647 catch (IBPP::Exception & ex)
650 strError = "IBPP exception";
651 printfd(__FILE__, ex.what());
657 //-----------------------------------------------------------------------------
658 int FIREBIRD_STORE::WriteUserDisconnect(const std::string & login,
659 const DIR_TRAFF & up,
660 const DIR_TRAFF & down,
661 const DIR_TRAFF & sessionUp,
662 const DIR_TRAFF & sessionDown,
665 const std::string & /*reason*/) const
667 STG_LOCKER lock(&mutex);
669 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amWrite, til, tlr);
670 IBPP::Statement st = IBPP::StatementFactory(db, tr);
677 st->Prepare("execute procedure sp_append_session_log(?, ?, 'd', 0)");
683 st->Prepare("insert into tb_sessions_data \
684 (fk_session_log, dir_num, session_upload, \
685 session_download, month_upload, month_download) \
686 values (?, ?, ?, ?, ?, ?)");
687 for(int i = 0; i < DIR_NUM; i++)
691 st->Set(3, (int64_t)sessionUp[i]);
692 st->Set(4, (int64_t)sessionDown[i]);
693 st->Set(5, (int64_t)up[i]);
694 st->Set(6, (int64_t)down[i]);
700 catch (IBPP::Exception & ex)
703 strError = "IBPP exception";
704 printfd(__FILE__, ex.what());
710 //-----------------------------------------------------------------------------
711 int FIREBIRD_STORE::WriteDetailedStat(const std::map<IP_DIR_PAIR, STAT_NODE> & statTree,
713 const std::string & login) const
715 STG_LOCKER lock(&mutex);
717 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amWrite, til, tlr);
718 IBPP::Statement st = IBPP::StatementFactory(db, tr);
720 IBPP::Timestamp statTime, now;
723 time_t2ts(lastStat, &statTime);
728 std::map<IP_DIR_PAIR, STAT_NODE>::const_iterator it;
729 it = statTree.begin();
730 st->Prepare("insert into tb_detail_stats \
731 (till_time, from_time, fk_user, dir_num, \
732 ip, download, upload, cost) \
733 values (?, ?, (select pk_user from tb_users \
736 while (it != statTree.end())
739 st->Set(2, statTime);
741 st->Set(4, it->first.dir);
742 st->Set(5, (int32_t)it->first.ip);
743 st->Set(6, (int64_t)it->second.down);
744 st->Set(7, (int64_t)it->second.up);
745 st->Set(8, it->second.cash);
752 catch (IBPP::Exception & ex)
755 strError = "IBPP exception";
756 printfd(__FILE__, ex.what());
762 //-----------------------------------------------------------------------------
763 int FIREBIRD_STORE::SaveMonthStat(const USER_STAT & stat, int month, int year, const std::string & login) const
765 STG_LOCKER lock(&mutex);
767 IBPP::Transaction tr = IBPP::TransactionFactory(db, IBPP::amWrite, til, tlr);
768 IBPP::Statement st = IBPP::StatementFactory(db, tr);
775 if (SaveStat(stat, login, year, month))
784 st->Prepare("execute procedure sp_add_stat(?, 0, 0, ?, 0, ?, 0, ?)");
795 st->Prepare("insert into tb_stats_traffic \
796 (fk_stat, dir_num, upload, download) \
797 values (?, ?, 0, 0)");
799 for(int i = 0; i < DIR_NUM; i++)
808 catch (IBPP::Exception & ex)
811 strError = "IBPP exception";
812 printfd(__FILE__, ex.what());
818 //-----------------------------------------------------------------------------