def updateAutoCleanStates(self, tables_dic): """ tables_dic(dic): dic of table_name=>(date,date_unit) date and date_unit will be passed to RelativeDate, and should be valid for initializer """ query = "" for table_name in tables_dic: if table_name not in self.__tables: raise GeneralException( errorText("REPORTS", "INVALID_CLEAN_TABLE") % table_name) date, date_unit = tables_dic[table_name] date = to_int(date, "%s auto clean" % table_name) if date < 0: raise GeneralException( errorText("REPORTS", "INVALID_AUTO_CLEAN_TABLE_DATE") % (date, table_name)) state_obj = self.getStateObj(table_name) query += state_obj.updateValueQuery( RelativeDate(date, date_unit).getDateSeconds()) db_main.getHandle().transactionQuery(query)
def __updatePortDB(self, ras_obj, port_names, phones, _type, comments): query = "" for _index in range(len(port_names)): query += self.__updatePortQuery(ras_obj.getRasID(), port_names[_index], phones[_index], _type, comments[_index]) db_main.getHandle().transactionQuery(query)
def changeDeposit(self, changer_admin_name, admin_name, deposit_change, comment, remote_addr): """ change deposit of admin "admin_name" changer_admin_name(str): name of admin, changing the deposit admin_name(str): name of admin that deposit changes deposit_change(float): amount of change comment(str): remote_addr(str): remote ip address of deposit changer """ self.__changeDepositCheckInput(changer_admin_name, admin_name, deposit_change, comment, remote_addr) changer_admin_obj = self.__getAdminLoader().getAdminByName( changer_admin_name) admin_obj = self.__getAdminLoader().getAdminByName(admin_name) query = "" query += admin_main.getDepositChangeLogActions().logDepositChangeQuery( changer_admin_obj.getAdminID(), admin_obj.getAdminID(), deposit_change, comment, remote_addr) query += self.__changeDepositQuery(admin_obj.getAdminID(), deposit_change) query += ias_main.getActionsManager().logEvent("CHANGE_DEPOSIT", changer_admin_name, deposit_change, admin_name) db_main.getHandle().transactionQuery(query) admin_obj.changeDeposit(deposit_change)
def __addLeafDB(self, leaf_id, leaf_name, parent_id, interface_id, default_rate_kbits, default_ceil_kbits, total_rate_kbits, total_ceil_kbits): db_main.getHandle().transactionQuery( self.__addLeafQuery(leaf_id, leaf_name, parent_id, interface_id, default_rate_kbits, default_ceil_kbits, total_rate_kbits, total_ceil_kbits))
def __addVoIPChargeRuleToDB(self,rule_obj): """ add rule_obj properties to DB """ charge_rule_id=self.__getNewChargeRuleID() rule_obj.setRuleID(charge_rule_id) query=self.__addVoIPChargeRuleAndPortsQuery(rule_obj) db_main.getHandle().transactionQuery(query)
def __updateCharge(self,charge_id,name,comment,visible_to_all): """ update a charge rule information in DB """ db_main.getHandle().update("charges",{"name":dbText(name), "comment":dbText(comment), "visible_to_all":dbText(visible_to_all) },"charge_id=%s"%charge_id)
def __addVoIPChargeRuleToDB(self, rule_obj): """ add rule_obj properties to DB """ charge_rule_id = self.__getNewChargeRuleID() rule_obj.setRuleID(charge_rule_id) query = self.__addVoIPChargeRuleAndPortsQuery(rule_obj) db_main.getHandle().transactionQuery(query)
def __deleteAddUserSavesDB(self, add_user_save_ids, admin_restricted, admin_obj): query = "" for add_user_save_id in add_user_save_ids: query += self.__deleteAddUserSaveQuery(add_user_save_id, admin_restricted, admin_obj.getAdminID()) db_main.getHandle().transactionQuery(query)
def __addNewAdminDB(self, admin_id, username, password, name, comment, creator_id): """ insert the new admin information into admins table """ query = self.__addNewAdminQuery(admin_id, username, password, name, comment, creator_id) query += self.__addNewAdminIASQuery(username, creator_id) db_main.getHandle().transactionQuery(query)
def __updateCharge(self, charge_id, name, comment, visible_to_all): """ update a charge rule information in DB """ db_main.getHandle().update( "charges", { "name": dbText(name), "comment": dbText(comment), "visible_to_all": dbText(visible_to_all) }, "charge_id=%s" % charge_id)
def __updateAttributeDB(self,ras_obj,attrs): query="" for attr_name in attrs: if attrs[attr_name]!=ras_obj.getAttribute(attr_name): if ras_obj.hasAttribute(attr_name): query+=self.__updateAttributeQuery(ras_obj.getRasID(),attr_name,attrs[attr_name]) else: query+=self.__addAttributeQuery(ras_obj.getRasID(),attr_name,attrs[attr_name]) db_main.getHandle().transactionQuery(query)
def __insertNewCharge(self,charge_id,name,comment,charge_type,admin_id,visible_to_all): """ insert the new charge to db """ db_main.getHandle().insert("charges",{"charge_id":charge_id, "name":dbText(name), "comment":dbText(comment), "charge_type":dbText(charge_type), "admin_id":admin_id, "visible_to_all":dbText(visible_to_all) })
def cleanLogs(self, table, date): """ clean all logs of "table" before that "date", "date" will be passed directly to database, so it can contain database clauses """ try: query = self.__tables[table].replace("%%s", date) except KeyError: raise GeneralException( errorText("REPORTS", "INVALID_CLEAN_TABLE") % table) db_main.getHandle().transactionQuery(query)
def __updateAttributeDB(self, ras_obj, attrs): query = "" for attr_name in attrs: if attrs[attr_name] != ras_obj.getAttribute(attr_name): if ras_obj.hasAttribute(attr_name): query += self.__updateAttributeQuery( ras_obj.getRasID(), attr_name, attrs[attr_name]) else: query += self.__addAttributeQuery(ras_obj.getRasID(), attr_name, attrs[attr_name]) db_main.getHandle().transactionQuery(query)
def __insertNewCharge(self, charge_id, name, comment, charge_type, admin_id, visible_to_all): """ insert the new charge to db """ db_main.getHandle().insert( "charges", { "charge_id": charge_id, "name": dbText(name), "comment": dbText(comment), "charge_type": dbText(charge_type), "admin_id": admin_id, "visible_to_all": dbText(visible_to_all) })
def __getCurValueDB(self): cur_val = db_main.getHandle().get("ibs_states", "name=" + dbText(self.state_name)) if len(cur_val) == 0: raise GeneralException("Zero length value for state %s" % self.state_name) return cur_val[0]["value"]
def __getAllActiveRasIDs(self): """ return a list of all ras_id s from table "ras" """ ras_ids = db_main.getHandle().get("ras", "active='t'", 0, -1, "", ["ras_id"]) return [m["ras_id"] for m in ras_ids]
def __rasIPExistsInDB(self, ras_ip): """ return True if ras with ip "ras_ip" exists in "ras" table. it's useful for checking for inactive rases, as they aren't in memory """ return db_main.getHandle().getCount("ras", "ras_ip=%s" % dbText(ras_ip))
def getLastDestination(self, voip_username): """ return last destination dialed by user or empty string if no number was dialed yet """ loaded_user = user_main.getUserPool().getUserByVoIPUsername( voip_username) ret = db_main.getHandle().selectQuery("""select value from connection_log,connection_log_details where connection_log.connection_log_id=connection_log_details.connection_log_id and service=2 and user_id=%s and name='called_number' order by login_time limit 1""" % loaded_user.getUserID()) try: return ret[0]["value"] except IndexError: return ""
def __getChargeRuleIDs(self, charge_obj): """ return a list of rules and their properties belongs to charge_id """ rules_table = getRulesTable(charge_obj.getType()) return db_main.getHandle().get( rules_table, "charge_id=%s" % charge_obj.getChargeID())
def __getAllIPpoolIds(self): """ return a list of ippool id's from ippool table """ ippoolids_dic = db_main.getHandle().get("ippool", "true", 0, -1, "ippool_id", ["ippool_id"]) return map(lambda x: x["ippool_id"], ippoolids_dic)
def __getRulePorts(self, charge_rule_id): """ return a list of ports belongs to charge_rule_id """ ports = db_main.getHandle().get("charge_rule_ports", "charge_rule_id=%s" % charge_rule_id) return [m["ras_port"] for m in ports]
def __getAllAdminIDs(self): """ return a list of all admin_ids from "admins" table """ admin_ids = db_main.getHandle().get("admins", "true", 0, -1, "", ["admin_id"]) return [m["admin_id"] for m in admin_ids]
def __directQueryGetTotalDuration(self, conditions): if self.hasCondFor("show_total_duration"): return db_main.getHandle().get( "connection_log", conditions, 0, -1, "", ["extract(epoch from sum(logout_time-login_time)) as sum" ])[0]["sum"] return -1
def __getDayOfWeeksDB(self,charge_rule_id): """ return a list of day of week integers belongs to charge_rule_id """ dows=db_main.getHandle().get("charge_rule_day_of_weeks","charge_rule_id=%s"%charge_rule_id) return [m["day_of_week"] for m in dows]
def __getGroupInfoDB(self,group_id): """ get group info of group with id "group_id" from "groups" table and return (group_id,group_name) tuple """ group_info=db_main.getHandle().get("groups","group_id=%s"%to_int(group_id,"group id")) return (group_info[0]["group_id"],group_info[0]["group_name"],group_info[0]["comment"],group_info[0]["owner_id"])
def _getConnectionLogsByTempTable(self, _from, to, order_by, desc, date_type): """ temp table creation is more expensive but required if we have conditions on multiple tables """ db_handle = db_main.getHandle(True) try: self.__createTempTable(db_handle) total_rows = self.__tempTableGetTotalResultsCount(db_handle) if total_rows == 0: return (0, 0, "00:00:00", 0, 0, []) total_credit_used = self.__tempTableGetTotalCreditUsed(db_handle) total_duration = self.__tempTableGetTotalDuration(db_handle) total_in, total_out = self.__tempTableGetTotalInOuts(db_handle) connections = self.__tempTableGetConnections( db_handle, _from, to, order_by, desc) connection_details = self.__getConnectionDetails( db_handle, connections) finally: try: self.dropTempTable(db_handle, "connection_log_report") except: logException(LOG_DEBUG) db_handle.releaseHandle() return (total_rows, total_credit_used, total_duration, total_in, total_out, self.__createReportResult(connections, connection_details, date_type))
def _getConnectionLogsByDirectQuery(self, _from, to, order_by, desc, date_type): """ get connection logs by directly query the table this is faster than creating temp table, but possible if we have only conditions from connection_log table """ conditions = self.getTable( "connection_log").getRootGroup().getConditionalClause() total_rows = self.__directQueryGetTotalResultsCount(conditions) if total_rows == 0: return (0, 0, "00:00:00", 0, 0, []) total_credit_used = self.__directQueryGetTotalCreditUsed(conditions) total_duration = self.__directQueryGetTotalDuration(conditions) total_in, total_out = self.__directQueryGetTotalInOuts(conditions) connections = self.__directQueryGetConnections(conditions, _from, to, order_by, desc) connection_details = self.__getConnectionDetails( db_main.getHandle(), connections) return (total_rows, total_credit_used, total_duration, total_in, total_out, self.__createReportResult(connections, connection_details, date_type))
def __directQueryGetConnections(self, conditions, _from, to, order_by, desc): return db_main.getHandle( ).get("connection_log", conditions, _from, to, (order_by, desc), [ "*", "extract(epoch from logout_time-login_time) as duration_seconds" ])
def getUserIDsWithBasicAttr(self,attr_name,attr_value): """ return user_ids whom attr_name value in basic attrs is attr_value """ user_ids=db_main.getHandle().get("users","%s=%s"%(attr_name,attr_value), 0,-1,("user_id",True),["user_id"]) return map(lambda dic:dic["user_id"],user_ids)
def __getAllChargeIDs(self): """ return a list of charge id's from database """ charge_ids = db_main.getHandle().get("charges", "true", 0, -1, "", ["charge_id"]) return [m["charge_id"] for m in charge_ids]
def __getDayOfWeeksDB(self, charge_rule_id): """ return a list of day of week integers belongs to charge_rule_id """ dows = db_main.getHandle().get("charge_rule_day_of_weeks", "charge_rule_id=%s" % charge_rule_id) return [m["day_of_week"] for m in dows]
def __getRasInfoDB(self,ras_ip): """ return information of ras with ip "ras_ip" from db return value is the same as ibs_db.get,it a list of dics so it may be zero length or has a zero index, that the dic of info [] or [{ras_info}] """ return db_main.getHandle().get("ras","ras_ip=%s"%dbText(ras_ip))
def __getRasInfoDB(self, ras_ip): """ return information of ras with ip "ras_ip" from db return value is the same as ibs_db.get,it a list of dics so it may be zero length or has a zero index, that the dic of info [] or [{ras_info}] """ return db_main.getHandle().get("ras", "ras_ip=%s" % dbText(ras_ip))
def __checkSendToAllMessageIDs(self, message_ids): cond = " or ".join( map(lambda message_id: "message_id=%s" % message_id, message_ids)) _count = db_main.getHandle().getCount( "user_messages", "(%s) and user_id is null" % cond) if _count: raise GeneralException( errorText("MESSAGES", "CANT_DELETE_SEND_TO_ALL"))
def __getLastMessageIDForUser(self, user_id): ret = db_main.getHandle().selectQuery( "select max(message_id) as max from user_messages where user_id = %s" % user_id) if ret and ret[0]["max"] != None: return ret[0]["max"] else: return -1
def __getLastToAllMessageID(self): ret = db_main.getHandle().selectQuery( "select max(message_id) as max from user_messages where user_id is null" ) if ret and ret[0]["max"] != None: return ret[0]["max"] else: return -1
def getUserIDsWithAttr(self,attr_name,attr_value): """ return user_ids whom attr_name value is attr_value, of course user should have attr_name! """ user_ids=db_main.getHandle().get("user_attrs","attr_name=%s and attr_value=%s"%(dbText(attr_name),dbText(attr_value)), 0,-1,("user_id",True),["user_id"]) return map(lambda dic:dic["user_id"],user_ids)
def __getInUsages(self, conditions, _from, to): in_usage_query = "select user_id, sum(value::bigint) as sum " + \ "from connection_log_details,connection_log " + \ "where %s and "%conditions + \ "name='bytes_in' and " + \ "connection_log.connection_log_id=connection_log_details.connection_log_id " + \ "group by user_id order by sum(value::bigint) desc offset %s limit %s"%(_from, to-_from) return db_main.getHandle().selectQuery(in_usage_query, 1) #tuple result
def __fetchBasicUserInfo(self,user_id): """ fetch basic user info by user id and return a dic of user informations or None if there's no such id """ basic_user_info=db_main.getHandle().get("users","user_id=%s"%user_id) if len(basic_user_info)==0: return None return basic_user_info[0]
def getGroupIDsWithAttr(self,attr_name,attr_value): """ return group_ids whom attr_name value is attr_value, of course group should have attr_name! """ group_ids=db_main.getHandle().get("group_attrs","attr_name=%s and attr_value=%s"%(dbText(attr_name),dbText(attr_value)), 0,-1,("group_id",True),["group_id"]) return map(lambda dic:dic["group_id"],group_ids)
def __getAdminBasicInfoDB(self,admin_id): """ return dic of admin basic information from "admin" table """ try: return db_main.getHandle().get("admins","admin_id=%s"%admin_id)[0] except: logException(LOG_ERROR,"AdminLoader.__getAdminBasicInfoDB") raise
def __fetchUserAttrs(self,user_id): """ return a dictionary of user attributes in format {attr_name:attr_value} """ user_attrs={} db_user_attrs=db_main.getHandle().get("user_attrs","user_id=%s"%user_id) for user_dic in db_user_attrs: user_attrs[user_dic["attr_name"]]=user_dic["attr_value"] return user_attrs
def __fetchPersistentLanAttrs(self,user_id): """ return a dictionary of persistent_lan_users table attributes in format {attr_name:attr_value} """ plan_attrs={} plan_db_attrs=db_main.getHandle().get("persistent_lan_users","user_id=%s"%user_id) if len(plan_db_attrs)==1: for attr_name in ["persistent_lan_ip","persistent_lan_mac","persistent_lan_ras_id"]: plan_attrs[attr_name]=plan_db_attrs[0][attr_name] return plan_attrs
def __getChargeInfo(self,charge_id): """ return a dic of charge properties or raise an exception on error """ try: charge_info=db_main.getHandle().get("charges","charge_id=%s"%charge_id)[0] except: logException("ChargeLoader.getChargeInfo: error in getting charge information") raise return charge_info
def __fetchNormalUserAttrsByUserID(self,user_id): """ fetch normal user info from "normal_users" table, using user_id of user return a dic of attributes in format {attr_name:attr_value} """ normal_attrs={} normal_db_attrs=db_main.getHandle().get("normal_users","user_id=%s"%user_id) if len(normal_db_attrs)==1: normal_attrs["normal_username"]=normal_db_attrs[0]["normal_username"] normal_attrs["normal_password"]=normal_db_attrs[0]["normal_password"] return normal_attrs
def __fetchVoIPUserAttrsByUserID(self,user_id): """ fetch voip user info from "voip_users" table, using user_id of user return a dic of attributes in format {attr_name:attr_value} """ voip_attrs={} voip_db_attrs=db_main.getHandle().get("voip_users","user_id=%s"%user_id) if len(voip_db_attrs)==1: voip_attrs["voip_username"]=voip_db_attrs[0]["voip_username"] voip_attrs["voip_password"]=voip_db_attrs[0]["voip_password"] return voip_attrs
def getUserIDs(self, _from, to, order_by, desc): """ return a tuple of (result_count,user_id_list) """ query = self.getSearchQuery() db_handle = db_main.getHandle(True) self.__createResultTable(db_handle, query) result_count = self.__getResultCount(db_handle) db_dic = self.__applyOrderBy(db_handle, _from, to, order_by, desc) self.__dropResultTable(db_handle) db_handle.releaseHandle() return (result_count, [m["user_id"] for m in db_dic])
def normalUsernameExists(normal_username): """ check if normal_username currently exists normal_username(iterable object can be multistr or list): username that will be checked return a list of exists usernames NOTE: This is not thread safe XXX: test & check where_clause length """ if len(normal_username) == 0: return [] where_clause = " or ".join(map(lambda username: "******" % dbText(username), normal_username)) users_db = db_main.getHandle().get("normal_users", where_clause, 0, -1, "", ["normal_username"]) return [m["normal_username"] for m in users_db]
def planMacExists(self, mac): """ check if mac currently exists in plan_macs mac(iterable object can be multistr or list): mac that will be checked return a list of exists macs NOTE: This is not thread safe XXX: test & check where_clause length """ if len(mac) == 0: return [] where_clause = " or ".join(map(lambda m: "persistent_lan_mac=%s" % dbText(m), mac)) users_db = db_main.getHandle().get("persistent_lan_users", where_clause, 0, -1, "", ["persistent_lan_mac"]) return [m["persistent_lan_mac"] for m in users_db]
def changeDeposit(self,changer_admin_name,admin_name,deposit_change,comment,remote_addr): """ change deposit of admin "admin_name" changer_admin_name(str): name of admin, changing the deposit admin_name(str): name of admin that deposit changes deposit_change(int): amount of change comment(str): remote_addr(str): remote ip address of deposit changer """ self.__changeDepositCheckInput(changer_admin_name,admin_name,deposit_change,comment,remote_addr) changer_admin_obj=self.__getAdminLoader().getAdminByName(changer_admin_name) admin_obj=self.__getAdminLoader().getAdminByName(admin_name) query="" query+=admin_main.getDepositChangeLogActions().logDepositChangeQuery(changer_admin_obj.getAdminID(), admin_obj.getAdminID(), deposit_change, comment, remote_addr) query+=self.__changeDepositQuery(admin_obj.getAdminID(),deposit_change) db_main.getHandle().transactionQuery(query) admin_obj.changeDeposit(deposit_change)
def getConnectionLogs(self,_from,to,order_by,desc,date_type): db_handle=db_main.getHandle(True) self.__createTempTable(db_handle) total_rows=self.__getTotalResultsCount(db_handle) if total_rows==0: return (0,0,"00:00:00",[]) total_credit_used=self.__getTotalCreditUsed(db_handle) total_duration=self.__getTotalDuration(db_handle) connections=self.__getConnections(db_handle,_from,to,order_by,desc) connection_details=self.__getConnectionDetails(db_handle,connections) self.dropTempTable(db_handle,"connection_log_report") db_handle.releaseHandle() return (total_rows,total_credit_used,total_duration,self.__createReportResult(connections,connection_details,date_type))
def getAddUserSaves(self,_from,to,order_by,desc,date_type): """ return (total_rows,list of results) """ db_handle=db_main.getHandle(True) self.__createTempTable(db_handle) total_rows=self.__getTotalResultsCount(db_handle) if total_rows==0: return (0,[]) add_user_saves=self.__getAddUserSaves(db_handle,_from,to,order_by,desc) details=self.__getAddUserSaveDetails(db_handle,add_user_saves) self.dropTempTable(db_handle,"add_user_save_report") db_handle.releaseHandle() return (total_rows,self.__createReportResult(add_user_saves,details,date_type))
def __fetchVoIPUserAttrsByVoIPUsername(self,voip_username): """ fetch voip user info from "voip_users" table, using voip username of user return a dic of attributes in format {attr_name:attr_value} or None if voip_username doesn't exists """ voip_attrs={} voip_db_attrs=db_main.getHandle().get("voip_users","voip_username=%s"%dbText(voip_username)) if len(voip_db_attrs)==1: voip_attrs["user_id"]=voip_db_attrs[0]["user_id"] voip_attrs["voip_username"]=voip_db_attrs[0]["voip_username"] voip_attrs["voip_password"]=voip_db_attrs[0]["voip_password"] return voip_attrs else: return None
def __fetchNormalUserAttrsByNormalUsername(self,normal_username): """ fetch normal user info from "normal_users" table, using normal username of user return a dic of attributes in format {attr_name:attr_value} or None if normal_username doesn't exists """ normal_attrs={} normal_db_attrs=db_main.getHandle().get("normal_users","normal_username=%s"%dbText(normal_username)) if len(normal_db_attrs)==1: normal_attrs["user_id"]=normal_db_attrs[0]["user_id"] normal_attrs["normal_username"]=normal_db_attrs[0]["normal_username"] normal_attrs["normal_password"]=normal_db_attrs[0]["normal_password"] return normal_attrs else: return None
def getCreditChanges(self, _from, to, order_by, desc, date_type): db_handle = db_main.getHandle(True) self.__createTempTable(db_handle) total_rows = self.__getTotalResultsCount(db_handle) if total_rows == 0: return (0, 0, 0, []) total_per_user_credit = self.__getTotalPerUserCredit(db_handle) total_admin_credit = self.__getTotalAdminCredit(db_handle) credit_changes = self.__getCreditChanges(db_handle, _from, to, order_by, desc) user_ids = self.__getUserIDs(db_handle, credit_changes) self.dropTempTable(db_handle, "credit_change_report") db_handle.releaseHandle() return ( total_rows, total_per_user_credit, total_admin_credit, self.__createReportResult(credit_changes, user_ids, date_type), )
def getPersistentLanUsers(self,ras_id): """ return a list of dics, containin """ return db_main.getHandle().get("persistent_lan_users","persistent_lan_ras_id=%s"%ras_id)
def __getNewUserID(self): """ return a new unique user_id from """ return db_main.getHandle().seqNextVal("users_user_id_seq")
def __addNewAdminDB(self,admin_id,username,password,name,comment,creator_id): """ insert the new admin information into admins table """ db_main.getHandle().transactionQuery(self.__addNewAdminQuery(admin_id,username,password,name,comment,creator_id))
def __changePasswordDB(self,username,password): db_main.getHandle().update("admins", {"password":dbText(password)}, "username=%s"%dbText(username) )