def add(cls, req):
_ip = req.get('ip')
_cpu = req.get('cpu')
_ram = req.get('ram')
_time = req.get('time')
_sql = 'insert into performs(ip,cpu,ram,time) VALUES (%s,%s,%s,%s)'
SQL.excute_sql(_sql, (_ip, _cpu, _ram, _time), False)
def user_add(cls, params):
username = params.get('username')
password = params.get('password')
age = params.get('age')
telphone = params.get('telphone')
email = params.get('email')
_sql_select = 'select * from user where username = %s'
_sql_insert = 'insert into user(username,password,age,telphone,email) values(%s,%s,%s,%s,%s)'
agrs1 = (username, )
_sql_count, rt_list = SQL.excute_sql(_sql_select, agrs1)
if _sql_count != 0:
return False, username + '已存在,请尝试其他的名字'
args2 = (username, md5_str(password), age, telphone, email)
SQL.excute_sql(_sql_insert, args2)
return True, '添加成功'
def change_passwd(cls, uid, upass):
_sql = 'update user set password = %s where id = %s'
_args = (md5_str(upass), uid)
_sql_count, rt_list = SQL.excute_sql(_sql, _args)
if _sql_count:
return True, '修改成功'
return False, '修改失败'
def user_del(cls, id, username):
_sql = 'delete from user where id=%s and username=%s'
args = (id, username)
_sql_count, rt_list = SQL.excute_sql(_sql, args)
if _sql_count != 0:
return True
return False
def get_idc_name(cls):
_sql = 'select idc_id,idc_name from idc_name'
rt = []
_cnt, _rt_list = SQL.excute_sql(_sql)
for i in _rt_list:
rt.append(i)
return rt
def user_add(cls,params):
username = params.get('username')
password = params.get('password')
cname = params.get('cname')
telphone = params.get('telphone')
email = params.get('email')
permission = params.get('permission', '0')
_sql_select = 'select * from user where username = %s'
_sql_insert = 'insert into user(username,password,cname,telphone,email,permission) values(%s,%s,%s,%s,%s,%s)'
agrs1 = (username,)
_sql_count, rt_list = SQL.excute_sql(_sql_select, agrs1)
if _sql_count != 0:
return False, username + '已存在,请尝试其他的名字'
args2 = (username, md5_str(password), cname, telphone, email, permission)
SQL.excute_sql(_sql_insert, args2)
return True, '添加成功'
def change_passwd(cls,uid, upass):
_sql = 'update user set password = %s where id = %s'
_args = (md5_str(upass), uid)
print _args
_sql_count, rt_list = SQL.excute_sql(_sql, _args)
if _sql_count:
return True, '修改成功'
return False, '修改失败'
# @classmethod
# def user_reset(cls,id, username):
# _sql = 'update user set password = %s where id=%s and username=%s'
# newpassword = ''.join([choice(string.ascii_letters + string.digits) for i in range(8)])
# args = (md5_str(newpassword), id, username)
# _sql_count, rt_list = SQL.excute_sql(_sql, args)
# if _sql_count != 0:
# return True, '重置成功', newpassword
# return False, '重置失败', newpassword
#
# @classmethod
# def validate_mpass(cls,params):
# mgrpass = params.get('mgrpass')
# mgruser = '******'
# ip = params.get('ip')
# cmd = params.get('cmd').split('\n')
# _sql = 'select * from user where username=%s and password=%s'
# _args = (mgruser,md5_str(mgrpass))
# _sql_count,rt_list = SQL.excute_sql(_sql,_args)
# if _sql_count != 0 :
# _ssh = Ssh_cmd(ip,cmd)
# return _ssh.ssh_cmd()
# return False,'管理员密码验证失败'
def log2_time_status(cls):
_sql = 'select date_format(logtime,"%%Y-%%m-%%d %%H:00:00"),status,count(*) from access_logs2 where logtime >= %s group by logtime,status;'
_last_time = time.strftime('2014-08-%d %H:%M:%S',
time.localtime(time.time() -
4 * 24 * 60 * 60)) #最近24小时的
_cnt, _rt_list = SQL.excute_sql(_sql, (_last_time, ))
_legends = []
_times = []
_datas = []
_temp_dict = {}
for _time, _status, _cnt in _rt_list:
if _status not in _legends:
_legends.append(_status)
if _time not in _times:
_times.append(_time)
_temp_dict.setdefault(_status, {})
_temp_dict[_status][_time] = _cnt
for _status, _stat in _temp_dict.items():
_node = {
'name': _status,
'type': 'bar',
'barWidth': 30,
'stack': 'web_time_code',
'data': [_stat.get(x, 0) for x in _times]
}
_datas.append(_node)
return _legends, _times, _datas
def get_list(cls):
_column = 'id,sn,ip,hostname,os,cpu,ram,disk,idc_name,admin,business,purchase_date,warranty,vendor,model,status'
_columns = _column.split(',')
_sql = 'select {column} from assets,idc_name where assets.status=0 and assets.idc_id = idc_name.idc_id;'.format(
column=_column)
_cnt, _rt_list = SQL.excute_sql(_sql)
return [dict(zip(_columns, i)) for i in _rt_list]
def delete(cls, id):
_sql = 'update assets set status = 1 where id=%s'
_args = (id, )
_cnt, _rtlist = SQL.excute_sql(_sql, _args)
if _cnt != 0:
return True, '删除成功'
return False, '删除失败'
def user_del(cls,id, username):
_sql = 'delete from user where id=%s and username=%s'
args = (id, username)
_sql_count, rt_list = SQL.excute_sql(_sql, args)
if _sql_count != 0:
return True, '删除成功'
return False, '删除失败'
def get_list(cls):
colloens = ('id', 'username', 'password', 'age', 'telphone', 'email')
_sql = 'select * from user'
rt = []
sql_count, rt_list = SQL.excute_sql(_sql) # 函数调用
for i in rt_list:
rt.append(dict(zip(colloens, i)))
return rt
def get_list(cls):
colloens = ('id', 'username', 'password', 'cname', 'telphone', 'email', 'permission')
_sql = 'select * from user'
rt = []
sql_count, rt_list = SQL.excute_sql(_sql) # 函数调用
for i in rt_list:
rt.append(dict(zip(colloens, i)))
return rt
def user_reset(cls,id, username):
_sql = 'update user set password = %s where id=%s and username=%s'
newpassword = ''.join([choice(string.ascii_letters + string.digits) for i in range(8)])
args = (md5_str(newpassword), id, username)
_sql_count, rt_list = SQL.excute_sql(_sql, args)
if _sql_count != 0:
return True, '重置成功', newpassword
return False, '重置失败', newpassword
def stat_export(collections,tablename,ptname,date):
_sql = 'select * from {tablename} where ptname = %s order by date'.format(tablename=tablename)
_args = (ptname,)
sql_count, rt_list = SQL.excute_sql(_sql,_args)
rt = []
for i in rt_list:
rt.append(dict(zip(collections,i)))
return rt if sql_count != 0 else None
def log_access(cls, top=10):
colloens = ('id', 'ip', 'url', 'code', 'nums')
_sql = 'select * from access_logs order by nums desc limit %s'
args = (top, )
rt = []
_sql_count, rt_list = SQL.excute_sql(_sql, args)
for x in rt_list:
rt.append(dict(zip(colloens, x)))
return rt
def add_new_before_select(params):
idcname = params.get('idcname')
date = params.get('date')
_sql = 'select * from idc_data where idcname = %s and date = %s'
_args = (idcname, date)
_sql_count, rt_list = SQL.excute_sql(_sql, _args)
if _sql_count != 0:
return False, idcname + ' ' + date + '已存在'
return True, '进行入库操作'
def stat_export(collections, tablename, ptname, date):
_sql = 'select * from {tablename} where ptname = %s order by date'.format(
tablename=tablename)
_args = (ptname, )
sql_count, rt_list = SQL.excute_sql(_sql, _args)
rt = []
for i in rt_list:
rt.append(dict(zip(collections, i)))
return rt if sql_count != 0 else None
def delete_idcinfo(params):
id = params.get('id')
idcname = params.get('idcname')
date = params.get('date')
_sql = 'delete from idc_data where id = %s and date = %s and idcname = %s'
_args = (id, date, idcname)
_sql_count, rt_list = SQL.excute_sql(_sql, _args, fetch=False)
if _sql_count != 0:
return True, '删除成功'
return False, '删除失败'
def get_new_idcinfo(_local_date=month_get()):
colloens = ('id', 'date', 'idcname', 'cabinet', 'cabinet_price',
'host_amount', 'bandwidth', 'bandwidth_price',
'bandwidth_amount', 'combined', 'status', 'info')
_sql = 'select * from idc_data where date = %s'
_args = (_local_date, )
rt = []
_sql_count, rt_list = SQL.excute_sql(_sql, _args)
for i in rt_list:
rt.append(dict(zip(colloens, i)))
return _local_date, rt
def log2_code_list(cls):
_sql = 'select status,count(status) from access_logs2 group by status'
_cnt, _rt_list = SQL.excute_sql(_sql)
status_legend = []
status_data = []
_code_list = []
if _cnt != 0:
for _status, _count in _rt_list:
status_legend.append(_status)
status_data.append({'name': _status, 'value': _count})
return status_legend, status_data
return [], []
def user_update(cls, params):
username = params.get('username')
id = params.get('id')
age = params.get('age')
telphone = params.get('telphone')
email = params.get('email')
_sql = 'update user set age=%s ,telphone=%s ,email=%s where id=%s and username=%s'
args = (age, telphone, email, id, username)
_sql_count, rt_list = SQL.excute_sql(_sql, args)
if _sql_count != 0:
return True, '更新成功'
return False, '更新失败'
def validate_mpass(cls, params):
mgrpass = params.get('mgrpass')
mgruser = '******'
ip = params.get('ip')
cmd = params.get('cmd').split('\n')
_sql = 'select * from user where username=%s and password=%s'
_args = (mgruser, md5_str(mgrpass))
_sql_count, rt_list = SQL.excute_sql(_sql, _args)
if _sql_count != 0:
_ssh = Ssh_cmd(ip, cmd)
return _ssh.ssh_cmd()
return False, '管理员密码验证失败'
def user_update(cls,params):
username = params.get('username')
id = params.get('id')
cname = params.get('cname')
telphone = params.get('telphone')
email = params.get('email')
permission = params.get('permission','0')
_sql = 'update user set cname=%s ,telphone=%s ,email=%s , permission=%s where id=%s and username=%s'
args = (cname, telphone, email, permission, id, username)
_sql_count, rt_list = SQL.excute_sql(_sql, args)
if _sql_count != 0:
return True, '更新成功'
return False, '更新失败'
def create(cls,params):
_collent = []
_values = []
for k, v in params.items():
_collent.append(k)
_values.append(v)
_sql = 'insert into assets({coll}) values%s'.format(coll=','.join(_collent))
_args = (tuple(_values),)
# print tuple(_values)
_cnt, _rtlist = SQL.excute_sql(_sql, _args)
if _cnt != 0:
return True, '添加成功'
return False, '入库失败'
def validate_create(cls, params):
collent = params.keys()
result = {}
for i in collent:
if params[i] == '':
result[i] = '%s 不能为空' % i
# 检查SN的唯一
sn = params.get('sn').strip()
if len(sn) >= 6:
_sql = 'select * from assets where sn = %s and status = 0'
_args = (sn, )
_cnt, rt_list = SQL.excute_sql(_sql, _args)
if _cnt != 0:
result['sn'] = 'SN编码已存在'
else:
result['sn'] = 'SN编码太短'
# 检查IP的唯一
ip = params.get('ip').strip()
if cls.ip_check(ip):
_sql = 'select * from assets where ip = %s and status = 0'
_args = (ip, )
_cnt, rt_list = SQL.excute_sql(_sql, _args)
if _cnt != 0:
result['ip'] = 'IP地址已存在'
else:
result['ip'] = 'IP地址不合法'
# 检查主机名的唯一
hostname = params.get('hostname').strip()
_sql = 'select * from assets where hostname = %s and status = 0'
_args = (hostname, )
_cnt, rt_list = SQL.excute_sql(_sql, _args)
if _cnt != 0:
result['hostname'] = '主机名已存在'
if not result:
return cls.create(params)
return False, result.values()
def get_list(cls,ip):
_sql = 'select cpu,ram,time from performs where ip=%s and time >=%s ORDER by time asc'
_args = (ip,time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time() - 60*30)))
_cnt,_rt_list = SQL.excute_sql(_sql,_args)
if _cnt != 0:
cpu_list = []
ram_list = []
time_list = []
for _cpu,_ram,_time in _rt_list:
cpu_list.append(round(_cpu,2))
ram_list.append(round(_ram,2))
time_list.append(_time.strftime('%H:%M'))
return time_list,cpu_list,ram_list
return False,'',''
def get_by_id(cls,aid):
_column = 'id,sn,ip,hostname,os,cpu,ram,disk,idc_id,admin,business,purchase_date,warranty,vendor,model,status'
# _sql = 'select {coll} from assets,idc_name where assets.status=0 and assets.idc_id = idc_name.idc_id and id = %s'.format(coll=_column)
_sql = 'select {coll} from assets where id = %s'.format(coll=_column)
_args = (aid,)
_cnt, _rt_list = SQL.excute_sql(_sql, _args)
rt = []
if _cnt != 0:
for x in range(len(_column.split(','))):
if _column.split(',')[x] == 'purchase_date':
rt.append((_column.split(',')[x], _rt_list[0][x].strftime("%Y-%m-%d")))
continue
rt.append((_column.split(',')[x], _rt_list[0][x]))
return dict(rt)
return ''
def get_idc_info_date():
now_year = datetime.datetime.now().strftime('%Y')
_sql_all = 'select sum(combined) from idc_data where date like %s group by date order by date;'
_sql_alone = 'select date,idcname,combined from idc_data where date like %s order by date'
_args = (now_year + '%', )
_sql_count_all, rt_list_all = SQL.excute_sql(_sql_all, _args)
_sql_count, rt_list = SQL.excute_sql(_sql_alone, _args)
'''
计算每月总收入-------------
'''
rt = []
# print rt_list_all,_sql_count
for x in rt_list_all:
rt.append(float(x[0]))
all_date = []
all_date.append({'data': rt, 'name': '总支出'})
'''
计算每个机房每个月的收入
'''
rs = []
for x in rt_list:
if len(rs) != 0:
for y in rs:
if y['name'] == x[1]:
y['data'].append(float(x[2]))
status = 0
break
else:
status = 1
else:
status = 1
if status == 1:
rs.append({'name': x[1], 'data': [float(x[2])]})
# 返回总支出和单机房月支持的列表
return all_date + rs
def has_alarm(ip):
_sql = 'select cpu,ram from performs where ip = %s and time >= %s order by time desc limit %s'
_time = datetime.datetime.now() - datetime.timedelta(minutes=5)
_args = (ip, _time.strftime('%Y-%m-%d %H:%M:%S'), CNT)
_rt_cnt, _rt_list = SQL.excute_sql(_sql, _args)
if _rt_cnt >= CNT:
_cpu_alarm = True
_ram_alarm = True
for _cpu, _ram in _rt_list:
if _cpu < CPU_PERCENT:
_cpu_alarm = False
if _ram < RAM_PERCENT:
_ram_alarm = False
return _cpu_alarm, _ram_alarm
return False, False
def server_status(base_url, current_page=1, avg_page=17):
collections = ('id', 'name', 'server', 'batt_t', 'match_t', 'enro_t',
'pvr_t', 'tran_t', 'ext_t', 'war_t', 'conf_t', 'start_batt',
'start_match', 'start_enro', 'start_pvr', 'start_state',
'start_trans', 'match_v', 'batt_v', 'pvr_v', 'state_v',
'trans_v', 'billingid')
_sql = 'select * from info'
sql_count, rt_list = SQL.excute_sql(_sql)
rt = []
for i in rt_list:
rt.append(dict(zip(collections, i)))
page = Pager(current_page, avg_page)
return rt[page.start_page:page.end_page], page.page_str(
len(rt), base_url) if sql_count != 0 else None
def log_code_list(cls):
_sql = 'select code,sum(nums) from access_logs group by code'
_sql_count, _rt_list = SQL.excute_sql(_sql)
_code_list = []
all_code = 0
if _sql_count != 0:
for _code, _nums in _rt_list:
all_code += _nums
for _code, _nums in _rt_list:
rt = {}
rt['name'] = _code
rt['y'] = round(_nums / all_code, 2)
_code_list.append(rt)
return _code_list
return []
def update(cls,params):
_column = 'sn,ip,hostname,os,cpu,ram,disk,idc_id,admin,business,purchase_date,warranty,vendor,model'
id = params.get('id')
rt_set = []
_args = []
for i in _column.split(','):
# rt_set.append(i+'='+'\'%s\'' % params[i]) #预处理的方式是不需要加''的
rt_set.append('{collens}=%s'.format(collens=i))
_args.append(params.get(i))
_args.append(id)
_sql = 'update assets set {coll} where id = %s'.format(coll=','.join(rt_set))
# _args = (id,)
_cnt, _rtlist = SQL.excute_sql(_sql, _args)
if _cnt != 0:
return True, '更新成功'
return False, '更新失败'
def log2_map(cls):
_sql = 'select city,lat,lng,count(city) from access_logs2 group by city,lat,lng;'
_server_ip = '211.151.99.93'
_server_addr = '北京'
_server_lat = ' 117.10'
_server_lng = '40.13'
_cnt, _rt_list = SQL.excute_sql(_sql)
_map_geocoord = {}
_map_markline = []
_map_markpoint = []
if _cnt != 0:
for _city, _lat, _lng, _nums in _rt_list:
_map_geocoord[_city] = [_lng, _lat]
_map_markline.append([{
'name': _city,
'value': _nums
}, {
'name': _server_addr
}])
_map_markpoint.append({'name': _city, 'value': _nums})
# print _map_geocoord
# print _map_markline
# print _map_markpoint
_map_geocoord[_server_addr] = [_server_lat, _server_lng]
#
# _map_geocoord = {
# '上海': [121.4648, 31.2891],
# '北京': [116.4551, 40.2539],
# '大连': [122.2229, 39.4409],
# '广州': [113.5107, 23.2196]
# }
# _map_markline = [
# [{"name": '上海', "value": 95}, {"name": '北京'}],
# [{"name": '广州', "value": 90}, {"name": '北京'}],
# [{"name": '大连', "value": 80}, {"name": '北京'}]
# ]
#
# _map_markpoint = [
# {"name": '上海', "value": 95},
# {"name": '广州', "value": 90},
# {"name": '大连', "value": 80}
# ]
return _map_geocoord, _map_markline, _map_markpoint
return [], [], []
def add_new_idcinfo(params):
idcname = params.get('idcname')
date = params.get('date')
cabinet = params.get('cabinet')
cabinet_price = params.get('cabinet_price')
host_amount = params.get('host_amount')
bandwidth = params.get('bandwidth')
bandwidth_price = params.get('bandwidth_price')
bandwidth_amount = float(bandwidth) * float(bandwidth_price)
combined = float(host_amount) + float(bandwidth_amount)
status = params.get('status')
info = params.get('info')
# print date,idcname,cabinet,cabinet_price,host_amount,bandwidth,bandwidth_price,bandwidth_amount,combined,status,info
_sql = 'insert into idc_data(date,idcname,cabinet,cabinet_price,host_amount,bandwidth,bandwidth_price,bandwidth_amount,combined,status,info) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
_args = (date, idcname, cabinet, cabinet_price, host_amount, bandwidth,
bandwidth_price, bandwidth_amount, combined, status, info)
_sql_count, rt_list = SQL.excute_sql(_sql, _args, fetch=False)
if _sql_count != 0:
return True, '添加成功'
return False, '添加失败'
def update_idcinfo(params):
id = params.get('id')
idcname = params.get('idcname')
date = params.get('date')
cabinet = params.get('cabinet')
cabinet_price = params.get('cabinet_price')
host_amount = params.get('host_amount')
bandwidth = params.get('bandwidth')
bandwidth_price = params.get('bandwidth_price')
bandwidth_amount = float(bandwidth) * float(bandwidth_price)
combined = float(host_amount) + float(bandwidth_amount)
status = params.get('status')
info = params.get('info')
_sql = 'update idc_data set date = %s, idcname = %s , cabinet = %s , cabinet_price = %s, host_amount = %s, ' \
'bandwidth = %s, bandwidth_price = %s, bandwidth_amount = %s,combined = %s,status = %s,info = %s where id = %s'
_args = (date, idcname, cabinet, cabinet_price, host_amount, bandwidth,
bandwidth_price, bandwidth_amount, combined, status, info, id)
_sql_count, rt_list = SQL.excute_sql(_sql, _args, fetch=False)
if _sql_count != 0:
return True, '更新成功'
return False, '更新失败'
def stat_import(params):
_sql = 'insert into frame_stat(ptname,date,frame_49,frame_55,frame_60,frame_49_new,frame_55_new,frame_60_new) values (%s,%s,%s,%s,%s,%s,%s,%s)'
_args_4399 = params['4399frame']
_args_7k7k = params['7k7kframe']
for _args in _args_4399,_args_7k7k:
SQL.excute_sql(_sql,_args,fetch=False)
_sql = 'insert into caton_stat(ptname,date,caton_5,caton_15,caton_30,caton_50,caton_70,caton_95,caton_100,caton_5_new,caton_15_new,caton_30_new,caton_50_new,caton_70_new,caton_95_new,caton_100_new) values ( \
%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
_args_4399 = params['4399caton']
_args_7k7k = params['7k7kcaton']
for _args in _args_4399,_args_7k7k:
SQL.excute_sql(_sql, _args, fetch=False)
_sql = 'insert into load_stat(ptname,date,load_config,load_res) values (%s,%s,%s,%s)'
_args_4399 = params['4399load']
_args_7k7k = params['7k7kload']
for _args in _args_4399, _args_7k7k:
SQL.excute_sql(_sql, _args, fetch=False)
_sql = 'insert into memory_stat(ptname,date,memory_500,memory_700,memory_900,memory_1100) values (%s,%s,%s,%s,%s,%s)'
_args_4399 = params['4399memory']
_args_7k7k = params['7k7kmemory']
for _args in _args_4399, _args_7k7k:
SQL.excute_sql(_sql, _args, fetch=False)
_sql = 'insert into mouse_stat(ptname,date,mouse_20,mouse_30,mouse_50,mouse_75,mouse_20_new,mouse_30_new,mouse_50_new,mouse_75_new) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
_args_4399 = params['4399mouse']
_args_7k7k = params['7k7kmouse']
for _args in _args_4399, _args_7k7k:
SQL.excute_sql(_sql, _args, fetch=False)
_sql = 'insert into enter_game_stat(ptname,date,success,battle_res_load,army_success,room_success,lobby_success,frist_root_success,frist_lobby_success,frist_battle_success,fighting_drop, \
frist_fighting_drop,ladder_drop,frist_ladder_drop,trans_drop,browser,ladder_match) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
_args_4399 = params['4399enter']
_args_7k7k = params['7k7kenter']
for _args in _args_4399, _args_7k7k:
SQL.excute_sql(_sql, _args, fetch=False)
def validate_login(cls,username, password):
_columns = ('id','username')
_sql = 'select * from user where username = %s and password = %s'
args = (username, md5_str(password))
sql_count, rt_list = SQL.excute_sql(_sql, args)
return dict(zip(_columns,rt_list[0])) if sql_count != 0 else None