예제 #1
0
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
예제 #2
0
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
예제 #3
0
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
예제 #4
0
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
예제 #5
0
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
예제 #6
0
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
예제 #7
0
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
예제 #8
0
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
예제 #9
0
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)
예제 #10
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)
예제 #11
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
예제 #12
0
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
예제 #13
0
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
예제 #14
0
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
예제 #15
0
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
예제 #16
0
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
예제 #17
0
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
예제 #18
0
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
예제 #19
0
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()
예제 #20
0
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
예제 #22
0
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']
예제 #24
0
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
예제 #26
0
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
예제 #28
0
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
예제 #29
0
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
예제 #30
0
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