def GetByFilter(offset, limit, params):
sql = '''select * from (SELECT count(distinct nodeid,pid)unlock_num,fname,fid,count(1)total_attr_num ,s_uid FROM superstars
%s group by fid order by unlock_num desc, total_attr_num desc, id desc)a %s '''
t_sql = '''select count(1)count from (SELECT count(distinct nodeid,pid)unlock_num,fname,fid,count(1)total_attr_num FROM superstars
%s group by fid order by unlock_num desc, total_attr_num desc)a '''
text_array = []
val_array = []
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get('s_uid'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
_limit = ''
count = 0
if limit:
_limit = ' limit %s , %s ' % (offset, limit)
t_sql = t_sql % whereString
counts = yield DbAccessor.Select('default', t_sql, tuple(val_array))
count = counts[0]['count']
sql = sql % (whereString, _limit)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return count, list_data
def GetByFilter(offset, limit, params):
sql = '''select partner_name,group_concat(device_level)device_level_l,group_concat(t_own_num)t_own_num_l,sum(t_own_num)t_own_num from
(SELECT device_level,partner_name,sum(own_num)t_own_num FROM partner_device %s group by partner_name , device_level)a
group by partner_name order by sum(t_own_num) desc %s '''
t_sql = '''select count(distinct partner_name)count from partner_device %s '''
text_array = []
val_array = []
channel_list = params.get('channel_list', '')
server_list = params.get('server_list', '')
if params.get('end_time'):
d_date = int(
datetime.datetime.strptime(params.get('end_time'),
"%Y-%m-%d %H:%M:%S").strftime('%Y%m%d'))
else:
d_date = int(datetime.datetime.now().strftime('%Y%m%d'))
text_array.append("`d_date` = %s")
val_array.append(d_date)
# if params.get('server_list'):
# _sql = "(`ch` = %s and `s_uid` in%s)"
# __server_list = []
# for _server_list in params.get('server_list'):
# _server_list = _server_list.split(',')
# channel_name = _server_list[0]
# del _server_list[0]
# __server_list.append(_sql)
# val_array.append(channel_name)
# val_array.append(tuple(_server_list))
# text_array.append(' (' + (' or '.join(__server_list)) + ') ')
if server_list and channel_list:
__sql = "(`ch` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in tuple(channel_list.split(',')):
__server_list.append(__sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`ch` in%s")
val_array.append(tuple(channel_list.split(',')))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
_limit = ''
count = 0
if limit:
_limit = ' limit %s , %s ' % (offset, limit)
t_sql = t_sql % whereString
counts = yield DbAccessor.Select('default', t_sql, tuple(val_array))
count = counts[0]['count']
sql = sql % (whereString, _limit)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return count, list_data
def GetByFilter(offset, limit, params, count=None):
sql = '''SELECT sum(num) as num, sum(avg) as avg,start_time
FROM daily_online %s group by d_date order by start_time %s limit %s,%s'''
text_array = []
val_array = []
if params.get('start_time'):
text_array.append("`start_time` >= %s")
val_array.append(
datetime.datetime.strptime(params.get('start_time'),
"%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`start_time` <= %s")
val_array.append(
datetime.datetime.strptime(params.get('end_time'),
"%Y-%m-%d %H:%M:%S"))
if params.get('server_list'):
text_array.append("`s_uid` in%s")
val_array.append(params.get('server_list'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
order_by = 'desc' if not params.get('order_by') else params.get('order_by')
sql = sql % (whereString, order_by + ',id desc', offset, limit)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
if count:
_sql = '''select count(distinct d_date) as total from daily_online %s''' % (
whereString)
total = yield DbAccessor.Select('default', _sql, tuple(val_array))
return list_data, total[0]['total']
return list_data
def dataofplayer(offset, limit, params):
text_array = []
val_array = []
sql = '''
select * from ( SELECT * FROM superstartoplayer %s order by ak_num desc, king_num desc, reset_num desc)a %s;
'''
t_sql = '''
select count(1)count from ( SELECT * FROM superstartoplayer %s order by ak_num desc, king_num desc, reset_num desc)a ;
'''
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get('s_uid'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
_limit = ''
count = 0
if limit:
_limit = ' limit %s , %s ' % (offset, limit)
t_sql = t_sql % whereString
counts = yield DbAccessor.Select('default', t_sql, tuple(val_array))
count = counts[0]['count']
sql = sql % (whereString, _limit)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return count, list_data
def GetByFilter(offset,limit,params, count = None):
sql = '''SELECT name, type, b_type, sum(challenge_num)challenge_num, sum(success)success, sum(player_num)player_num,
avg(avg_time)avg_time,sum(create_room_num)create_room_num,sum(dis_room_num)dis_room_num FROM task_checkpoint %s group by b_type, name %s %s'''
t_sql = '''select count(1)total from (SELECT id FROM task_checkpoint %s group by b_type, name)t'''
text_array = []
val_array = []
channel_list = params.get('channel_list','')
server_list = params.get('server_list','')
if params.get('b_type'):
text_array.append("`b_type` = %s")
val_array.append(params.get('b_type'))
if params.get('start_time'):
text_array.append("`time` >= %s")
val_array.append(datetime.datetime.strptime(params.get('start_time'), "%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`time` <= %s")
val_array.append(datetime.datetime.strptime(params.get('end_time'), "%Y-%m-%d %H:%M:%S"))
if server_list and channel_list:
__sql = "(`ch` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in tuple(channel_list.split(',')):
__server_list.append(__sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`ch` in%s")
val_array.append(tuple(channel_list.split(',')))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
orderString = ''
_limit = ''
total = 0
if count:
order_by = 'asc' if params.get('order_by')=='desc' else 'desc'
orderString = ' order by name ' + order_by
_limit = ' limit %s, %s' % (offset, limit)
t_sql = t_sql % whereString
_total = yield DbAccessor.Select('default', t_sql, tuple(val_array))
total = _total[0]['total']
sql = sql % (whereString, orderString, _limit)
list_data = yield DbAccessor.Select('default',sql,tuple(val_array))
return total,list_data
def GetByFilter(offset, limit, params, count=None):
sql = '''SELECT sum(num) as num, level
FROM ad_level %s group by level order by level %s limit %s,%s'''
text_array = []
val_array = []
channel_list = params.get('channel_list', '')
server_list = params.get('server_list', '')
if params.get('_type'):
text_array.append("`type` = %s")
val_array.append(params.get('_type'))
if params.get('d_date'):
text_array.append("`d_date` = %s")
val_array.append(params.get('d_date'))
# if params.get('start_time'):
# text_array.append("`create_time` >= %s")
# val_array.append(datetime.datetime.strptime(params.get('start_time'), "%Y-%m-%d %H:%M:%S"))
# if params.get('end_time'):
# text_array.append("`create_time` <= %s")
# val_array.append(datetime.datetime.strptime(params.get('end_time'), "%Y-%m-%d %H:%M:%S"))
if server_list and channel_list:
_sql = "(`channel_name` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in tuple(channel_list.split(',')):
__server_list.append(_sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`channel_name` in%s")
val_array.append(tuple(channel_list.split(',')))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
order_by = 'desc' if not params.get('order_by') else params.get('order_by')
sql = sql % (whereString, order_by, offset, limit)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
if count:
_sql = '''select count(1)total from (SELECT * FROM ad_level %s group by level)a''' % (
whereString)
total = yield DbAccessor.Select('default', _sql, tuple(val_array))
return list_data, total[0]['total']
return list_data
def total_select(params):
sql = '''
select sum(three_days_ltv) as three_days_ltv, sum(three_days_income) as three_days_income,
sum(seven_days_ltv) as seven_days_ltv, sum(seven_days_income) as seven_days_income,
sum(half_moon_ltv) as half_moon_ltv , sum(half_moon_income) as half_moon_income,
sum(one_month_ltv) as one_month_ltv, sum(one_month_income) as one_month_income,
sum(new_account_num) as new_account_num
from ltv_value
'''
text_array = []
val_array = []
channel_list = params.get('channel_list', '')
server_list = params.get('server_list', '')
if params.get('start_time'):
text_array.append("`create_time` >= %s")
val_array.append(
datetime.datetime.strptime(params.get('start_time'),
"%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`create_time` <= %s")
val_array.append(
datetime.datetime.strptime(params.get('end_time'),
"%Y-%m-%d %H:%M:%S"))
# if params.get('server_list'):
# _sql = "(`channel_name` = %s and `s_uid` in%s)"
# __server_list = []
# for _server_list in params.get('server_list'):
# _server_list = _server_list.split(',')
# channel_name = _server_list[0]
# del _server_list[0]
# __server_list.append(_sql)
# val_array.append(channel_name)
# val_array.append(tuple(_server_list))
# text_array.append(' (' + (' or '.join(__server_list)) + ') ')
if server_list and channel_list:
__sql = "(`channel_name` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in tuple(channel_list.split(',')):
__server_list.append(__sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`channel_name` in%s")
val_array.append(tuple(channel_list.split(',')))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
if whereString:
sql += whereString
items = yield DbAccessor.Select('default', sql, tuple(val_array))
return items
def getdataking(params):
text_array = []
val_array = []
sql = '''
SELECT count(1)count FROM superstars %s
'''
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get('s_uid'))
if params.get('fid'):
text_array.append('`fid` = %s ')
val_array.append(params.get('fid'))
if params.get('quality'):
text_array.append('`quality` = %s')
val_array.append(params.get('quality'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % whereString
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return list_data
def player_role_all_sup(params):
text_array = []
val_array = []
sql = '''
select count(1)count from (SELECT count(distinct nodeid)node_count FROM superstardetail %s group by fid)a where node_count = 35
'''
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get('s_uid'))
if params.get('op_type'):
text_array.append('`op_type` = %s')
val_array.append(params.get('op_type'))
if params.get('pid'):
text_array.append('`pid` = %s')
val_array.append(params.get('pid'))
if params.get('end_time'):
text_array.append('`time` <= %s')
val_array.append(params.get('end_time'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % (whereString)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return list_data[0].get('count', 0)
def sup_p_numdata(params):
text_array = []
val_array = []
sql = '''
SELECT count(distinct pid)sup_p_num FROM superstardetail %s
'''
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get('s_uid'))
if params.get('op_type'):
text_array.append('`op_type` = %s')
val_array.append(params.get('op_type'))
if params.get('fid'):
text_array.append('`fid` = %s')
val_array.append(params.get('fid'))
if params.get('end_time'):
text_array.append('`time` <= %s')
val_array.append(params.get('end_time'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % (whereString)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return list_data[0].get('sup_p_num', 0)
def playerdatareset(params):
text_array = []
val_array = []
sql = '''
SELECT sum(basediamond)basediamond,sum(lock_user_diamond)lock_user_diamond,lock_type,count(1)lock_num FROM
superstardetail %s group by lock_type;
'''
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get('s_uid'))
if params.get('op_type'):
text_array.append('`op_type` = %s')
val_array.append(params.get('op_type'))
if params.get('pid'):
text_array.append('`pid` = %s')
val_array.append(params.get('pid'))
if params.get('end_time'):
text_array.append('`time` <= %s')
val_array.append(params.get('end_time'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % (whereString)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return list_data
def supattrtype(params):
text_array = []
val_array = []
sql = '''
select count(1)count,attr_type from (select attr_type from
( SELECT * FROM superstardetail %s order by id desc)a group by pid,nodeid,nodeseat)a group by attr_type;
'''
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get('s_uid'))
if params.get('op_type'):
text_array.append('`op_type` in%s')
val_array.append(params.get('op_type'))
if params.get('end_time'):
text_array.append('`time` <= %s')
val_array.append(params.get('end_time'))
if params.get('fid'):
text_array.append('`fid` = %s ')
val_array.append(params.get('fid'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % (whereString)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return list_data
def dataofsupersgeam(params):
sql = '''
SELECT sum(ak_num)ak_num, sum(reset_num)reset_num,sum(ldiamond)ldiamond,
sum(oneattr_reset)oneattr_reset,sum(oneuserld)oneuserld,sum(twoattr_reste)twoattr_reste
,sum(twouser_ld)twouser_ld FROM superstardatagram %s;
'''
text_array = []
val_array = []
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get("s_uid"))
if params.get('year'):
text_array.append('`year` = %s')
val_array.append(params.get('year'))
if params.get('month'):
text_array.append('`month` = %s')
val_array.append(params.get('month'))
if params.get('week'):
text_array.append('`week` = %s')
val_array.append(params.get('week'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % (whereString)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return list_data
def dataofsupers(params):
sql = '''
SELECT count(distinct pid)st_p_num FROM superstars %s
'''
text_array = []
val_array = []
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get("s_uid"))
if params.get('d_date'):
text_array.append('`d_date` = %s')
val_array.append(params.get('d_date'))
if params.get('year'):
text_array.append('`year` = %s')
val_array.append(params.get('year'))
if params.get('month'):
text_array.append('`month` = %s')
val_array.append(params.get('month'))
if params.get('week'):
text_array.append('`week` = %s')
val_array.append(params.get('week'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % (whereString)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return list_data
def dataofsuperbywm(params):
sql = '''SELECT * FROM %s %s limit 1'''
table_name = 'superstarym'
text_array = []
val_array = []
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get('s_uid'))
if params.get('week'):
table_name = 'superstarweek'
text_array.append('`week`= %s')
val_array.append(params.get('week'))
elif params.get('month'):
text_array.append('`month` = %s')
val_array.append(params.get('month'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % (table_name, whereString)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return list_data
def GetByFilter(params):
sql = '''SELECT sum(new_equip) as new_equip, sum(new_account) as new_account,
sum(actoin) as actoin,sum(pay_account) as pay_account,sum(pay_income) as pay_income,hours,create_time
FROM hours_statistics %s group by hours'''
text_array = []
val_array = []
if params.get('start_time'):
text_array.append("`create_time` >= %s")
start_time = datetime.datetime.strptime(params.get('start_time'), "%Y-%m-%d %H:%M:%S")
val_array.append(start_time)
end_time = datetime.datetime(start_time.year, start_time.month, start_time.day,23, 59, 59)
text_array.append("`create_time` <= %s")
val_array.append(end_time)
if params.get('server_list'):
_sql = "(`channel_name` = %s and `s_uid` in%s)"
__server_list = []
for _server_list in params.get('server_list'):
_server_list = _server_list.split(',')
channel_name = _server_list[0]
del _server_list[0]
__server_list.append(_sql)
val_array.append(channel_name)
val_array.append(tuple(_server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql %(whereString)
sql +=' order by id '
list_data = yield DbAccessor.Select('default',sql,tuple(val_array))
return list_data
def total_select(params):
sql = '''
select sum(new_equipment) as new_equipment, sum(new_login_account) as new_login_account,
sum(pay_income) as pay_income,sum(login_account) as login_account, sum(pay_account) as pay_account from overview
'''
text_array = []
val_array = []
if params.get('start_time'):
text_array.append("`create_time` >= %s")
val_array.append(
datetime.datetime.strptime(params.get('start_time'),
"%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`create_time` <= %s")
val_array.append(
datetime.datetime.strptime(params.get('end_time'),
"%Y-%m-%d %H:%M:%S"))
if params.get('server_list'):
text_array.append("`channel_name` in %s")
val_array.append((params.get('server_list')))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
if whereString:
sql += whereString
items = yield DbAccessor.Select('default', sql, tuple(val_array))
return items
def total_select(params):
sql = '''
select sum(start_inventory) as start_inventory, sum(atm_get) as atm_get,
sum(system_output) as system_output, sum(total_drain) as total_drain,
sum(end_inventory) as end_inventory from currency_cond
'''
text_array = []
val_array = []
channel_list = params.get('channel_list', '')
server_list = params.get('server_list', '')
if params.get('c_type'):
text_array.append("`c_type` = %s")
val_array.append(params.get('c_type'))
# if params.get('server_list'):
# _sql = "(`channel_name` = %s and `s_uid` in%s)"
# __server_list = []
# for _server_list in params.get('server_list'):
# _server_list = _server_list.split(',')
# channel_name = _server_list[0]
# del _server_list[0]
# __server_list.append(_sql)
# val_array.append(channel_name)
# val_array.append(tuple(_server_list))
# text_array.append(' (' + (' or '.join(__server_list)) + ') ')
if server_list and channel_list:
_sql = "(`channel_name` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in tuple(channel_list.split(',')):
__server_list.append(_sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`channel_name` in%s")
val_array.append(tuple(channel_list.split(',')))
if params.get('start_time'):
text_array.append("`create_time` >= %s")
val_array.append(
datetime.datetime.strptime(params.get('start_time'),
"%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`create_time` <= %s")
val_array.append(
datetime.datetime.strptime(params.get('end_time'),
"%Y-%m-%d %H:%M:%S"))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
if whereString:
sql += whereString
items = yield DbAccessor.Select('default', sql, tuple(val_array))
return items
def annex_information():
sql = '''
SELECT data FROM origin_data
'''
list_data = yield DbAccessor.Select('default',sql,())
if list_data:
return list_data[0]
return dict()
def total_select(params):
sql = '''
select ld.level, sum(ld.num) as num from (select * from daily_levelbase %s) as dlb left join
level_dis as ld on dlb.id = ld.b_id group by level
'''
text_array = []
val_array = []
channel_list = params.get('channel_list', '')
server_list = params.get('server_list', '')
if params.get('type_flag'):
text_array.append("`type_flag` = %s")
val_array.append(params.get('type_flag'))
if params.get('start_time'):
text_array.append("`time` >= %s")
val_array.append(
datetime.datetime.strptime(params.get('start_time'),
"%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`time` <= %s")
val_array.append(
datetime.datetime.strptime(params.get('end_time'),
"%Y-%m-%d %H:%M:%S"))
# if params.get('server_list'):
# _sql = "(`channel_name` = %s and `s_uid` in%s)"
# __server_list = []
# for _server_list in params.get('server_list'):
# _server_list = _server_list.split(',')
# channel_name = _server_list[0]
# del _server_list[0]
# __server_list.append(_sql)
# val_array.append(channel_name)
# val_array.append(tuple(_server_list))
# text_array.append(' (' + (' or '.join(__server_list)) + ') ')
if server_list and channel_list:
__sql = "(`channel_name` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in tuple(channel_list.split(',')):
__server_list.append(__sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`channel_name` in%s")
val_array.append(tuple(channel_list.split(',')))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % whereString
items = yield DbAccessor.Select('default', sql, tuple(val_array))
return items
def GetGuildOutputCon(type_flag=None):
sql = '''
SELECT id,name,type_flag FROM guild_data_way
'''
if type_flag is not None:
whereString = ' where type_flag=%s' % type_flag
sql += whereString
list_data = yield DbAccessor.Select('default', sql, ())
return list_data
def playerdata(offset, limit, params):
text_array = []
val_array = []
sql = '''
select s_uid,pid,pname,count(distinct pid,fid,nodeid)ak_num,count(1)total_attr_num from
( SELECT * FROM superstardetail %s order by id desc)a group by pid order by ak_num desc,
total_attr_num desc,id desc %s;
'''
t_sql = '''
select count(1)count from ( select id from
( SELECT * FROM superstardetail %s )a group by pid)b;
'''
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get('s_uid'))
if params.get('op_type'):
text_array.append('`op_type` = %s')
val_array.append(params.get('op_type'))
if params.get('end_time'):
text_array.append('`time` <= %s')
val_array.append(params.get('end_time'))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
t_sql = t_sql % whereString
__limit = " limit %s, %s " % (offset, limit)
sql = sql % (whereString, __limit)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
count_data = yield DbAccessor.Select('default', t_sql, tuple(val_array))
return count_data[0].get('count', 0), list_data
def total_select(params):
sql = '''
SELECT sum(bosshp)booshp_left,sum(kill_time)kill_time FROM guild_boss_record
'''
kill_sql = '''
SELECT count(1)as kill_success FROM guild_boss_record where result = 1
'''
text_array = []
val_array = []
if params.get('s_uid'):
text_array.append("`s_uid` = %s")
val_array.append(params.get('s_uid'))
if params.get('guild_lv'):
text_array.append("`guild_lv` in %s")
val_array.append(params.get('guild_lv'))
if params.get('prosperity_lv'):
text_array.append("`prosperity_lv` in %s")
val_array.append(params.get('prosperity_lv'))
if params.get('guild_id'):
text_array.append("`guild_id` in%s")
val_array.append(params.get('guild_id'))
if params.get('guild_name'):
text_array.append("`guild_name` in%s ")
val_array.append(params.get('guild_name'))
whereString = ' and '.join(text_array)
if whereString:
kill_sql += ' and ' + whereString
whereString = ' where ' + whereString
if whereString:
sql += whereString
items = yield DbAccessor.Select('default', sql, tuple(val_array))
kill_items = yield DbAccessor.Select('default', kill_sql, tuple(val_array))
return items[0], kill_items[0]['kill_success']
def total_select(params):
sql = '''
select sum(new_account_num) as new_account_num, avg(one_day_ltv) as one_day_ltv,
avg(two_day_ltv) as two_day_ltv, avg(three_days_ltv) as three_days_ltv,
avg(four_day_ltv) as four_day_ltv , avg(five_day_ltv) as five_day_ltv,
avg(six_day_ltv) as six_day_ltv, avg(seven_days_ltv) as seven_days_ltv,
avg(eight_day_ltv) as eight_day_ltv, avg(nine_day_ltv) as nine_day_ltv,
avg(ten_day_ltv) as ten_day_ltv, avg(eleven_day_ltv) as eleven_day_ltv,
avg(twelve_day_ltv) as twelve_day_ltv, avg(thirteen_day_ltv) as thirteen_day_ltv,
avg(fourteen_day_ltv) as fourteen_day_ltv, avg(half_moon_ltv) as half_moon_ltv,
avg(one_month_ltv) as one_month_ltv, avg(forty_five_ltv) as forty_five_ltv,
avg(sixty_ltv) as sixty_ltv,avg(seventy_five_ltv) as seventy_five_ltv,
avg(ninety_ltv) as ninety_ltv,avg(four_month_ltv) as four_month_ltv,
avg(five_month_ltv) as five_month_ltv,avg(six_month_ltv) as six_month_ltv
from ltv_value
'''
text_array = []
val_array = []
channel_list = params.get('channel_list','')
server_list = params.get('server_list','')
if params.get('start_time'):
text_array.append("`create_time` >= %s")
val_array.append(datetime.datetime.strptime(params.get('start_time'), "%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`create_time` <= %s")
val_array.append(datetime.datetime.strptime(params.get('end_time'), "%Y-%m-%d %H:%M:%S"))
if server_list and channel_list:
__sql = "(`channel_name` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in tuple(channel_list.split(',')):
__server_list.append(__sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`channel_name` in%s")
val_array.append(tuple(channel_list.split(',')))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
if whereString:
sql += whereString
items = yield DbAccessor.Select('default',sql,tuple(val_array))
return items
def GetGuildopmsg(offset, limit, params):
sql = '''
select opmsg.*,srv.name from (SELECT * FROM guild_operate_msg %s %s %s) as opmsg left join
(select uid,name from servers_data) as srv on opmsg.s_uid = srv.uid
'''
count_sql = '''
SELECT count(1) as count FROM guild_operate_msg %s
'''
text_array = []
val_array = []
if params.get('s_uid'):
text_array.append("`s_uid` = %s")
val_array.append(params.get('s_uid'))
if params.get('guild_id'):
text_array.append("`guild_id` in%s")
val_array.append(params.get('guild_id'))
if params.get('guild_name'):
text_array.append("`guild_name` in%s ")
val_array.append(params.get('guild_name'))
if params.get('pid_list'):
text_array.append("`operated_p_id` in%s ")
val_array.append(params.get('pid_list'))
whereString = ''
orderString = ' order by operate_time desc '
oplimit = ''
if text_array:
whereString = ' where '
whereString += ' and '.join(text_array)
if params.get('order'):
orderString = 'order by id desc'
sql = sql % (whereString, orderString, oplimit)
count_sql = count_sql % whereString
# order_by = 'desc' if not params.get('order_by') else params.get('order_by')
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
count = yield DbAccessor.Select('default', count_sql, tuple(val_array))
return count[0].get('count', 0), list_data
def total_select(params):
sql = '''
select avg(once_retain) as once_retain,
avg(three_retain) as three_retain, avg(four_retain) as four_retain,
avg(five_retain) as five_retain , avg(six_retain) as six_retain,
avg(seven_retain) as seven_retain, avg(fifteen_retain) as fifteen_retain,
avg(thirty_retain) as thirty_retain, avg(sixty_retain) as sixty_retain,
avg(ninety_retain) as ninety_retain, avg(forty_five_retain) as forty_five_retain,
avg(seventy_five_retain) as seventy_five_retain, sum(regist_account) as regist_account,
sum(login_account) as login_account, sum(new_login_accont) as new_login_accont
from player_retain
'''
text_array = []
val_array = []
channel_list = params.get('channel_list', '')
server_list = params.get('server_list', '')
if params.get('start_time'):
text_array.append("`create_time` >= %s")
val_array.append(
datetime.datetime.strptime(params.get('start_time'),
"%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`create_time` <= %s")
val_array.append(
datetime.datetime.strptime(params.get('end_time'),
"%Y-%m-%d %H:%M:%S"))
if server_list and channel_list:
__sql = "(`channel_name` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in tuple(channel_list.split(',')):
__server_list.append(__sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`channel_name` in%s")
val_array.append(tuple(channel_list.split(',')))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
if whereString:
sql += whereString
items = yield DbAccessor.Select('default', sql, tuple(val_array))
return items
def total_select(params):
sql = '''
select sum(new_login_accont) as new_login_accont, sum(login_account) as login_account,
sum(pay_account_num) as pay_account_num, sum(income) as income,
sum(first_pay_account) as first_pay_account , sum(first_pay_account_income) as first_pay_account_income,
sum(new_login_pay_num) as new_login_pay_num, sum(new_login_pay_income) as new_login_pay_income,
avg(one_retain_days) as one_retain_days, avg(three_retain_days) as three_retain_days,
avg(seven_retain_days) as seven_retain_days,avg(average_number_online) as average_number_online,
sum(atm_num) as atm_num from mg_daily_newspaper
'''
text_array = []
val_array = []
channel_list = params.get('channel_list', '')
server_list = params.get('server_list', '')
if params.get('start_time'):
text_array.append("`create_time` >= %s")
val_array.append(
datetime.datetime.strptime(params.get('start_time'),
"%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`create_time` <= %s")
val_array.append(
datetime.datetime.strptime(params.get('end_time'),
"%Y-%m-%d %H:%M:%S"))
if server_list and channel_list:
__sql = "(`channel_name` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in channel_list.split(','):
__server_list.append(__sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`channel_name` in%s")
val_array.append(tuple(channel_list.split(',')))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
if whereString:
sql += whereString
items = yield DbAccessor.Select('default', sql, tuple(val_array))
return items
def GetPlayerNum(params):
text_array = []
val_array = []
channel_list = params.get('channel_list','')
server_list = params.get('server_list','')
sql = '''select count(distinct pid)player_num, count(*)total_num, name, type,b_type, ch as channel_name, s_uid from
relic_hero %s group by name,b_type'''
if params.get('start_time'):
text_array.append("`time` >= %s")
val_array.append(datetime.datetime.strptime(params.get('start_time'), "%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`time` <= %s")
val_array.append(datetime.datetime.strptime(params.get('end_time'), "%Y-%m-%d %H:%M:%S"))
if server_list and channel_list:
__sql = "(`ch` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in tuple(channel_list.split(',')):
__server_list.append(__sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`ch` in%s")
val_array.append(tuple(channel_list.split(',')))
if params.get('name_type'):
_sql2 = "(`name` = %s and `b_type` =%s)"
name_type = []
for _name_type in params.get('name_type'):
_name_type = _name_type.split(',')
name_type.append(_sql2)
val_array.append(_name_type[0])
val_array.append(_name_type[1])
text_array.append(' (' + (' or '.join(name_type)) + ') ')
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % whereString
list_data = yield DbAccessor.Select('default',sql,tuple(val_array))
return list_data
def export(params):
sql = ''' SELECT * FROM ad_datas %s %s'''
text_array = []
val_array = []
channel_list = params.get('channel_list', '')
server_list = params.get('server_list', '')
if params.get('start_time'):
text_array.append("`create_time` >= %s")
val_array.append(
datetime.datetime.strptime(params.get('start_time'),
"%Y-%m-%d %H:%M:%S"))
if params.get('end_time'):
text_array.append("`create_time` <= %s")
val_array.append(
datetime.datetime.strptime(params.get('end_time'),
"%Y-%m-%d %H:%M:%S"))
if server_list and channel_list:
_sql = "(`channel_name` = %s and `s_uid` in%s)"
server_list = tuple(server_list.split(','))
__server_list = []
for channel_name in tuple(channel_list.split(',')):
__server_list.append(_sql)
val_array.append(channel_name)
val_array.append(tuple(server_list))
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
elif server_list:
text_array.append("`s_uid` in%s")
val_array.append(tuple(server_list.split(',')))
elif channel_list:
text_array.append("`channel_name` in%s")
val_array.append(tuple(channel_list.split(',')))
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
order_by = 'desc' if not params.get('order_by') else params.get('order_by')
orderString = ' order by create_time ' + order_by
sql = sql % (whereString, orderString)
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return list_data
def getdatayearmw(params):
text_array = []
val_array = []
sql = '''
SELECT count(distinct pid)st_p_num FROM superstars %s
'''
if params.get('s_uid'):
text_array.append('`s_uid` = %s')
val_array.append(params.get('s_uid'))
if params.get('month'):
month_sql = "(`year` = %s and `month` = %s)"
__server_list = []
for __month in params.get('month'):
for k, v in __month.items():
__server_list.append(month_sql)
val_array.append(k)
val_array.append(v)
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
if params.get('week'):
month_sql = "(`year` = %s and `week` = %s)"
__server_list = []
for __month in params.get('week'):
for k, v in __month.items():
__server_list.append(month_sql)
val_array.append(k)
val_array.append(v)
text_array.append(' (' + (' or '.join(__server_list)) + ') ')
whereString = ' and '.join(text_array)
if whereString:
whereString = ' where ' + whereString
sql = sql % whereString
list_data = yield DbAccessor.Select('default', sql, tuple(val_array))
return list_data