def logCreditChangeQuery(self,action,admin_id,user_ids,per_user_credit,admin_credit,remote_address,comment):
"""
log credit change to credit_change table
action(string): action that credit changed in, should be referenced by self.CREDIT_CHANGE_ACTIONS
admin_id(integer): Admin issueing the credit change for user
user_ids(list of integers): user ids that credit being changed
per_user_credit(float): credit change for each of users
admin_credit(float): credit admin spent, equals to count of users * per_user_credit
remote_address(str): remote ip of admin while changing credit
comment(str): comment of credit change
"""
self.__creditChangeCheckInput(remote_address,comment)
change_id=self.__getNewCreditChangeID()
ibs_query=IBSQuery()
ibs_query=ibs_db.createInsertQuery("credit_change",{"credit_change_id":change_id,
"action":self.getActionID(action),
"admin_id":admin_id,
"per_user_credit":per_user_credit,
"admin_credit":admin_credit,
"remote_addr":dbText(remote_address),
"comment":dbText(comment)
})
for user_id in user_ids:
ibs_query+=ibs_db.createInsertQuery("credit_change_userid",{"user_id":user_id,
"credit_change_id":change_id})
return ibs_query
def __insertPoolQuery(self, ippool_id, ippool_name, comment):
return ibs_db.createInsertQuery(
"ippool", {
"ippool_id": ippool_id,
"ippool_name": dbText(ippool_name),
"ippool_comment": dbText(comment)
})
def __addInterfaceQuery(self, interface_id, interface_name, comment):
return ibs_db.createInsertQuery(
"bw_interface", {
"interface_id": interface_id,
"interface_name": dbText(interface_name),
"comment": dbText(comment)
})
def __addAttributeQuery(self, ras_id, attr_name, attr_value):
return ibs_db.createInsertQuery(
"ras_attrs", {
"ras_id": ras_id,
"attr_name": dbText(attr_name),
"attr_value": dbText(attr_value)
})
def __addPermQuery(self, admin_id, perm_name, perm_value):
return ibs_db.createInsertQuery(
"admin_perms", {
"admin_id": admin_id,
"perm_name": dbText(perm_name),
"perm_value": dbText(perm_value)
})
def __addServiceQuery(self,leaf_service_id,leaf_id,protocol,_filter,rate_kbits,ceil_kbits):
return ibs_db.createInsertQuery("bw_leaf_services",{"leaf_service_id":leaf_service_id,
"leaf_id":leaf_id,
"protocol":dbText(protocol),
"filter":dbText(_filter),
"rate_kbits":rate_kbits,
"ceil_kbits":ceil_kbits})
def insertVoIPUserAttrsQuery(self,user_id,voip_username,voip_password):
"""
insert user voip attributes in "voip_users" table
"""
return ibs_db.createInsertQuery("voip_users",{"voip_username":dbText(voip_username),
"voip_password":dbText(voip_password),
"user_id":user_id})
def __addPortQuery(self,ras_id,port_name,phone,_type,comment):
return ibs_db.createInsertQuery("ras_ports",{"ras_id":ras_id,
"port_name":dbText(port_name),
"phone":dbText(phone),
"type":dbText(_type),
"comment":dbText(comment)
})
def __insertBasicUserQuery(self,user_id,credit,owner_id,group_id):
"""
XXX : Change this function to use SQL Stored Procedures
"""
return ibs_db.createInsertQuery("users",{"user_id":user_id,
"credit":credit,
"owner_id":owner_id,
"group_id":group_id})
def logDepositChangeQuery(self,changer_admin_id,admin_id,deposit_change,comment,remote_addr):
deposit_change_id=self.__getNewDepositChangeID()
return ibs_db.createInsertQuery("admin_deposit_change",{"admin_id":changer_admin_id,
"to_admin_id":admin_id,
"deposit_change":deposit_change,
"comment":dbText(comment),
"remote_addr":dbText(remote_addr),
"admin_deposit_change_id":deposit_change_id})
def __addNodeQuery(self,node_id,interface_id,parent_id,rate_kbits,ceil_kbits):
if parent_id==None:
parent_id="NULL"
return ibs_db.createInsertQuery("bw_node",{ "node_id":node_id,
"interface_id":interface_id,
"parent_id":parent_id,
"rate_kbits":rate_kbits,
"ceil_kbits":ceil_kbits})
def __addGroupQuery(self, group_id, group_name, comment, owner_id):
return ibs_db.createInsertQuery(
"groups", {
"group_id": group_id,
"group_name": dbText(group_name),
"comment": dbText(comment),
"owner_id": owner_id
})
def insertUserAttrQuery(self,user_id,attr_name,attr_value):
"""
XXX:change to use stored procedures
"""
return ibs_db.createInsertQuery("user_attrs",{"user_id":user_id,
"attr_name":dbText(attr_name),
"attr_value":dbText(attr_value)}
)
def __lockAdminQuery(self, admin_id, reason, locker_admin_id):
return ibs_db.createInsertQuery(
"admin_locks", {
"admin_id": admin_id,
"reason": dbText(reason),
"locker_admin_id": locker_admin_id,
"lock_id": "nextval('admin_locks_lock_id_seq')"
})
def __insertAddUserQuery(self, add_user_id, admin_id, type_id, comment):
return ibs_db.createInsertQuery(
"add_user_saves", {
"add_user_save_id": add_user_id,
"admin_id": admin_id,
"type": type_id,
"comment": dbText(comment)
})
def logDepositChangeQuery(self,changer_admin_id,admin_id,deposit_change,comment,remote_addr):
deposit_change_id=self.__getNewDepositChangeID()
return ibs_db.createInsertQuery("admin_deposit_change",{"admin_id":changer_admin_id,
"to_admin_id":admin_id,
"deposit_change":deposit_change,
"comment":dbText(comment),
"remote_addr":dbText(remote_addr),
"admin_deposit_change_id":deposit_change_id})
def __addLeafQuery(self,leaf_id,leaf_name,parent_id,interface_id,default_rate_kbits,default_ceil_kbits,total_rate_kbits,total_ceil_kbits):
return ibs_db.createInsertQuery("bw_leaf",{ "leaf_id":leaf_id,
"leaf_name":dbText(leaf_name),
"parent_id":parent_id,
"interface_id":interface_id,
"default_rate_kbits":default_rate_kbits,
"default_ceil_kbits":default_ceil_kbits,
"total_rate_kbits":total_rate_kbits,
"total_ceil_kbits":total_ceil_kbits})
def __addPortQuery(self, ras_id, port_name, phone, _type, comment):
return ibs_db.createInsertQuery(
"ras_ports", {
"ras_id": ras_id,
"port_name": dbText(port_name),
"phone": dbText(phone),
"type": dbText(_type),
"comment": dbText(comment)
})
def __addNewRasQuery(self,ras_id,ras_ip,ras_type,radius_secret):
"""
return query for adding new ras
"""
return ibs_db.createInsertQuery("ras",{"ras_id":ras_id,
"ras_ip":dbText(ras_ip),
"ras_type":dbText(ras_type),
"radius_secret":dbText(radius_secret)
})
def __addBwStaticIPQuery(self, static_ip_id, ip_addr, tx_leaf_id,
rx_leaf_id):
return ibs_db.createInsertQuery(
"bw_static_ip", {
"bw_static_ip_id": static_ip_id,
"ip": dbText(ip_addr),
"transmit_leaf_id": tx_leaf_id,
"receive_leaf_id": rx_leaf_id
})
def __insertConnectionQuery(self,connection_log_id,user_id,credit_used,login_time,logout_time,successful,_type,ras_id):
return ibs_db.createInsertQuery("connection_log",{"user_id":user_id,
"connection_log_id":connection_log_id,
"credit_used":credit_used,
"login_time":dbText(login_time),
"logout_time":dbText(logout_time),
"successful":successful,
"service":_type,
"ras_id":ras_id})
def __addPrefixQuery(self,tariff_id,prefix_id,code,name,cpm,free_sec,min_duration,round_to):
return ibs_db.createInsertQuery("tariff_prefix_list",{"tariff_id":tariff_id,
"prefix_id":prefix_id,
"prefix_code":dbText(code),
"prefix_name":dbText(name),
"cpm":cpm,
"free_seconds":free_sec,
"min_duration":min_duration,
"round_to":round_to})
def __insertAddUserDetailsQuery(self, add_user_id, user_id, username,
password):
return ibs_db.createInsertQuery(
"add_user_save_details", {
"add_user_save_id": add_user_id,
"user_id": user_id,
"username": dbText(username),
"password": dbText(password)
})
def __insertPlanUserAttrsQuery(self, user_id, mac, ip, ras_id):
return ibs_db.createInsertQuery(
"persistent_lan_users",
{
"persistent_lan_mac": dbText(mac),
"persistent_lan_ip": dbText(ip),
"persistent_lan_ras_id": ras_id,
"user_id": user_id,
},
)
def __addPrefixQuery(self,tariff_id,prefix_id,code,name,cpm,free_sec,min_duration,round_to,min_chargable_duration):
return ibs_db.createInsertQuery("tariff_prefix_list",{"tariff_id":tariff_id,
"prefix_id":prefix_id,
"prefix_code":dbText(code),
"prefix_name":dbText(name),
"cpm":cpm,
"free_seconds":free_sec,
"min_duration":min_duration,
"round_to":round_to,
"min_chargable_duration":min_chargable_duration})
def __addChargeRuleDowsQuery(self,dows_container,charge_rule_id):
"""
return query for inserting "dows_container" to charge_rule "charge_rule_id"
"""
query=""
for dow in dows_container:
query+=ibs_db.createInsertQuery("charge_rule_day_of_weeks",{"charge_rule_id":charge_rule_id,
"day_of_week":dow.getIntValue()
})
return query
def insertVoIPUserAttrsQuery(self, user_id, voip_username, voip_password):
"""
insert user voip attributes in "voip_users" table
"""
return ibs_db.createInsertQuery(
"voip_users", {
"voip_username": dbText(voip_username),
"voip_password": dbText(voip_password),
"user_id": user_id
})
def insertToDefsQuery(self):
"""
return a query to insert variable "var_name" with value "var_value" to "defs" table
value is pickled in order to keep variable type
"""
from core.db import ibs_db
return ibs_db.createInsertQuery("defs",{"name":dbText(self.name),
"value":dbText(pickle.dumps(self.value))
})
def __addServiceQuery(self, leaf_service_id, leaf_id, protocol, _filter,
rate_kbits, ceil_kbits):
return ibs_db.createInsertQuery(
"bw_leaf_services", {
"leaf_service_id": leaf_service_id,
"leaf_id": leaf_id,
"protocol": dbText(protocol),
"filter": dbText(_filter),
"rate_kbits": rate_kbits,
"ceil_kbits": ceil_kbits
})
def __addChargeRuleDowsQuery(self, dows_container, charge_rule_id):
"""
return query for inserting "dows_container" to charge_rule "charge_rule_id"
"""
query = ""
for dow in dows_container:
query += ibs_db.createInsertQuery("charge_rule_day_of_weeks", {
"charge_rule_id": charge_rule_id,
"day_of_week": dow.getIntValue()
})
return query
def __insertToTableQuery(self, _date, table_name, val_dic):
"""
insert values for now in snapshot table
val_dic(dic): dic in format {ras_id:value}
"""
query = ibs_query.IBSQuery()
for ras_id in val_dic:
query += ibs_db.createInsertQuery(table_name,
{"snp_date":dbText(_date),
"ras_id":ras_id,
"value":val_dic[ras_id]})
return query
def insertToDefsQuery(self):
"""
return a query to insert variable "var_name" with value "var_value" to "defs" table
value is pickled in order to keep variable type
"""
from core.db import ibs_db
return ibs_db.createInsertQuery(
"defs", {
"name": dbText(self.name),
"value": dbText(pickle.dumps(self.value))
})
def __addNodeQuery(self, node_id, interface_id, parent_id, rate_kbits,
ceil_kbits):
if parent_id == None:
parent_id = "NULL"
return ibs_db.createInsertQuery(
"bw_node", {
"node_id": node_id,
"interface_id": interface_id,
"parent_id": parent_id,
"rate_kbits": rate_kbits,
"ceil_kbits": ceil_kbits
})
def insertNormalUserAttrsQuery(self, user_id, normal_username, normal_password):
"""
insert user normal attributes in "normal_users" table
"""
return ibs_db.createInsertQuery(
"normal_users",
{
"normal_username": dbText(normal_username),
"normal_password": dbText(normal_password),
"user_id": user_id,
},
)
def __addChargeRulePortsQuery(self,ports_list,charge_rule_id):
"""
return query for inserting "ports" to charge_rule "charge_rule_id"
"""
if None in ports_list:
ports_list=[dbNull(None)]
query=""
for port in ports_list:
query+=ibs_db.createInsertQuery("charge_rule_ports",{"charge_rule_id":charge_rule_id,
"ras_port":dbText(port)
})
return query
def __addChargeRulePortsQuery(self, ports_list, charge_rule_id):
"""
return query for inserting "ports" to charge_rule "charge_rule_id"
"""
if None in ports_list:
ports_list = [dbNull(None)]
query = ""
for port in ports_list:
query += ibs_db.createInsertQuery("charge_rule_ports", {
"charge_rule_id": charge_rule_id,
"ras_port": dbText(port)
})
return query
def __addVoIPChargeRuleQuery(self,rule_obj):
"""
return query for inserting rule_obj properties into charge_rules table
"""
ras_id=self.__convertRasID(rule_obj.getRasID())
return ibs_db.createInsertQuery("voip_charge_rules",{"charge_id":rule_obj.charge_obj.getChargeID(),
"charge_rule_id":rule_obj.getRuleID(),
"start_time":dbText(rule_obj.start_time),
"end_time":dbText(rule_obj.end_time),
"tariff_id":rule_obj.tariff_id,
"ras_id":ras_id
})
def __addNewAdminQuery(self,admin_id,username,password,name,comment,creator_id):
"""
return query to insert new admin
"""
return ibs_db.createInsertQuery("admins",{"admin_id":admin_id,
"username":dbText(username),
"password":dbText(password),
"name":dbText(name.strip()),
"comment":dbText(comment.strip()),
"creator_id":dbText(creator_id),
"deposit":0,
"due":0
})
def __addLeafQuery(self, leaf_id, leaf_name, parent_id, interface_id,
default_rate_kbits, default_ceil_kbits,
total_rate_kbits, total_ceil_kbits):
return ibs_db.createInsertQuery(
"bw_leaf", {
"leaf_id": leaf_id,
"leaf_name": dbText(leaf_name),
"parent_id": parent_id,
"interface_id": interface_id,
"default_rate_kbits": default_rate_kbits,
"default_ceil_kbits": default_ceil_kbits,
"total_rate_kbits": total_rate_kbits,
"total_ceil_kbits": total_ceil_kbits
})
def __addNewRasQuery(self, ras_id, ras_ip, ras_description, ras_type,
radius_secret, comment):
"""
return query for adding new ras
"""
return ibs_db.createInsertQuery(
"ras", {
"ras_id": ras_id,
"ras_ip": dbText(ras_ip),
"ras_description": dbText(ras_description),
"ras_type": dbText(ras_type),
"radius_secret": dbText(radius_secret),
"comment": dbText(comment)
})
def __addVoIPChargeRuleQuery(self, rule_obj):
"""
return query for inserting rule_obj properties into charge_rules table
"""
ras_id = self.__convertRasID(rule_obj.getRasID())
return ibs_db.createInsertQuery(
"voip_charge_rules", {
"charge_id": rule_obj.charge_obj.getChargeID(),
"charge_rule_id": rule_obj.getRuleID(),
"start_time": dbText(rule_obj.start_time),
"end_time": dbText(rule_obj.end_time),
"tariff_id": rule_obj.tariff_id,
"ras_id": ras_id
})
def __postMessageToTableQuery(self,
table_name,
message_id,
user_id,
message_text,
strip_tags=True):
if strip_tags:
message_text = dbText(message_text)
else:
message_text = "'%s'" % escapeSlashes(message_text)
return ibs_db.createInsertQuery(
table_name, {
"message_id": message_id,
"user_id": user_id,
"message_text": message_text
})
def __addNewAdminQuery(self, admin_id, username, password, name, comment,
creator_id):
"""
return query to insert new admin
"""
return ibs_db.createInsertQuery(
"admins", {
"admin_id": admin_id,
"username": dbText(username),
"password": dbText(password),
"name": dbText(name.strip()),
"comment": dbText(comment.strip()),
"creator_id": dbText(creator_id),
"deposit": 0,
"due": 0
})
def __addInternetChargeRuleQuery(self,rule_obj):
"""
return query for inserting rule_obj properties into charge_rules table
"""
ras_id=self.__convertRasID(rule_obj.getRasID())
return ibs_db.createInsertQuery("internet_charge_rules",{"charge_id":rule_obj.charge_obj.getChargeID(),
"charge_rule_id":rule_obj.getRuleID(),
"start_time":dbText(rule_obj.start_time),
"end_time":dbText(rule_obj.end_time),
"cpm":float(rule_obj.cpm),
"cpk":float(rule_obj.cpk),
"assumed_kps":integer(rule_obj.assumed_kps),
"bandwidth_limit_kbytes":integer(rule_obj.bandwidth_limit),
"bw_transmit_leaf_id":dbNull(rule_obj.bw_tx_leaf_id),
"bw_receive_leaf_id":dbNull(rule_obj.bw_rx_leaf_id),
"ras_id":ras_id
})
def __addInternetChargeRuleQuery(self, rule_obj):
"""
return query for inserting rule_obj properties into charge_rules table
"""
ras_id = self.__convertRasID(rule_obj.getRasID())
return ibs_db.createInsertQuery(
"internet_charge_rules", {
"charge_id": rule_obj.charge_obj.getChargeID(),
"charge_rule_id": rule_obj.getRuleID(),
"start_time": dbText(rule_obj.start_time),
"end_time": dbText(rule_obj.end_time),
"cpm": float(rule_obj.cpm),
"cpk": float(rule_obj.cpk),
"assumed_kps": integer(rule_obj.assumed_kps),
"bandwidth_limit_kbytes": integer(rule_obj.bandwidth_limit),
"bw_transmit_leaf_id": dbNull(rule_obj.bw_tx_leaf_id),
"bw_receive_leaf_id": dbNull(rule_obj.bw_rx_leaf_id),
"ras_id": ras_id
})
def __insertTariffQuery(self,tariff_id,tariff_name,comment):
return ibs_db.createInsertQuery("voip_charge_rule_tariff",{"tariff_id":tariff_id,
"tariff_name":dbText(tariff_name),
"comment":dbText(comment)})
def __addPermQuery(self,admin_id,perm_name,perm_value):
return ibs_db.createInsertQuery("admin_perms",{"admin_id":admin_id,
"perm_name":dbText(perm_name),
"perm_value":dbText(perm_value)})
def __addNewPermTemplateQuery(self,template_name,template_id):
return ibs_db.createInsertQuery("admin_perm_templates",{"template_id":template_id,
"template_name":dbText(template_name)
})
def __insertCallerIDsQuery(self,user_id, caller_ids):
return "".join(map(lambda caller_id: ibs_db.createInsertQuery("caller_id_users",
{"caller_id":dbText(caller_id),
"user_id":user_id}), caller_ids))
def __addNewPermTemplateQuery(self, template_name, template_id):
return ibs_db.createInsertQuery("admin_perm_templates", {
"template_id": template_id,
"template_name": dbText(template_name)
})
def __addGroupQuery(self,group_id,group_name,comment,owner_id):
return ibs_db.createInsertQuery("groups",{"group_id":group_id,
"group_name":dbText(group_name),
"comment":dbText(comment),
"owner_id":owner_id
})
def __addIPToPoolQuery(self,ippool_id,ip):
return ibs_db.createInsertQuery("ippool_ips",{"ippool_id":ippool_id,
"ip":dbText(ip)})
def __addAttributeQuery(self,ras_id,attr_name,attr_value):
return ibs_db.createInsertQuery("ras_attrs",{"ras_id":ras_id,
"attr_name":dbText(attr_name),
"attr_value":dbText(attr_value)
})
def __insertPlanUserAttrsQuery(self,user_id,mac,ip,ras_id):
return ibs_db.createInsertQuery("persistent_lan_users",{"persistent_lan_mac":dbText(mac),
"persistent_lan_ip":dbText(ip),
"persistent_lan_ras_id":ras_id,
"user_id":user_id})
def __insertConnectionDetailQuery(self,connection_log_id,name,value):
return ibs_db.createInsertQuery("connection_log_details",{"name":dbText(name),"value":dbText(value),"connection_log_id":connection_log_id})
def __addIPpoolToRasQuery(self,ras_id,ippool_id):
return ibs_db.createInsertQuery("ras_ippools",{"ras_id":ras_id,"ippool_id":ippool_id})
def __addIPToPoolQuery(self, ippool_id, ip):
return ibs_db.createInsertQuery("ippool_ips", {
"ippool_id": ippool_id,
"ip": dbText(ip)
})
def insertGroupAttrQuery(self,group_id,attr_name,attr_value):
return ibs_db.createInsertQuery("group_attrs",{"group_id":to_int(group_id,"group id"),
"attr_name":dbText(attr_name),
"attr_value":dbText(attr_value)})
def __insertTariffQuery(self,tariff_id,tariff_name,comment):
return ibs_db.createInsertQuery("voip_charge_rule_tariff",{"tariff_id":tariff_id,
"tariff_name":dbText(tariff_name),
"comment":dbText(comment)})
def __insertPoolQuery(self,ippool_id,ippool_name,comment):
return ibs_db.createInsertQuery("ippool",{"ippool_id":ippool_id,
"ippool_name":dbText(ippool_name),
"ippool_comment":dbText(comment)
})