def gen_charge_data_excel(self, start: int, end: int):
"""生成每月充值账单excel表"""
limit = 1000
count = 0
offset = 0
sheetName = "charge_data_tbl-{}".format(timestamp2Str(start, "%Y-%m"))
objExcel = GenExcel(sheetName)
try:
while True:
sql = "select orderid as '订单id',accountName as '用户名',payway as '支付方式',money as '金额',gold as '金币数',create_time as '创建日期',dealDate as '交易日期',trxNo as '三方单号',status_str as '状态' " \
"from onlinePay where status=1 and create_time between FROM_UNIXTIME({start}) and FROM_UNIXTIME({end}) order by create_time desc " \
"limit {limit} offset {offset};".format(start=start, end=end, limit=limit, offset=offset)
dataList = self._cache_mysql.get_list('default', sql)
if len(dataList) == 0:
break
if count == 0:
objExcel.write_excel_row0(list(dataList[0].keys()))
objExcel.write_excel_data(dataList)
offset = limit + offset
count += 1
except Exception as e:
ERROR_MSG(repr(e))
ERROR_MSG("生成【{}】有误，请手动重新生成！！".format(sheetName))
finally:
objExcel.save_file()
return sheetName
def gen_integral_flow_excel(self, start: int, end: int):
"""生成统计月的积分流水excel表"""
limit = 1000
count = 0
offset = 0
sheetName = "integral_flow_tbl-{}".format(timestamp2Str(
start, "%Y-%m"))
objExcel = GenExcel(sheetName)
try:
while True:
sql = "select id,accountName as '用户名',integral as '数量',reasons as '操作描述',createTime as '日期',remarks as '备注' " \
"from integral_log where createTime between FROM_UNIXTIME({start}) and FROM_UNIXTIME({end}) order by createTime desc " \
"limit {limit} offset {offset};".format(start=start,end=end,limit=limit,offset=offset)
dataList = self._cache_mysql.get_list('default', sql)
if len(dataList) == 0:
break
if count == 0:
objExcel.write_excel_row0(list(dataList[0].keys()))
objExcel.write_excel_data(dataList)
offset = limit + offset
count += 1
except Exception as e:
ERROR_MSG(repr(e))
ERROR_MSG("生成【{}】有误，请手动重新生成！！".format(sheetName))
finally:
del objExcel
return sheetName
def getMaxOnlineSqlByTime(start: int):
"""时间内同时在线人数最大值Sql"""
sql = "select GREATEST("
for x in range(24):
column = str(x) + "hour"
for i in range(1, 4):
a_column = column + str(i * 15)
if x == 0 and i == 1:
sql += str(a_column)
else:
sql = sql + ',' + str(a_column)
date = timestamp2Str(start, "%Y-%m-%d")
sql += ") as max_online from statistics_players_online where days='%s';" % (
date)
return sql
def getAvgOnlineTimeByTime(start: int, cache_mysql: object):
"""时间内线人数在线总时长Sql"""
sql = "select sum("
for x in range(24):
column = str(x) + "hour"
for i in range(1, 4):
if x == 23 and i == 3:
a_column = column + str(i * 15)
else:
a_column = column + str(i * 15) + "+"
sql = sql + str(a_column)
date = timestamp2Str(start, "%Y-%m-%d")
sql += ") as sum_online from statistics_players_online where days='%s';" % (
date)
dictSumOL = cache_mysql.get_one('default', sql)
sumOL = dictSumOL.get('sum_online') if dictSumOL.get('sum_online') else 0
return sumOL * 15
def getACUSqlByTime(start: int):
"""ACU时间内每小时在线人数最大值Sql"""
sql = "select "
for x in range(24):
sql += "GREATEST("
column = str(x) + "hour"
for i in range(1, 4):
a_column = column + str(i * 15)
if i == 3:
sql = sql + str(a_column)
else:
sql = sql + str(a_column) + ','
if x == 23:
sql += ") as max_online_%d " % x
else:
sql += ") as max_online_%d ," % x
date = timestamp2Str(start, "%Y-%m-%d")
sql += " from statistics_players_online where days='%s';" % (date)
return sql
def gen_gold_flow_excel(self, start: int, end: int):
"""生成统计月的金币流水excel表"""
limit = 1000
offset = 0
count = 0
sheetName = "gold_flow_tbl-{}".format(timestamp2Str(start, "%Y-%m"))
objExcel = GenExcel(sheetName)
try:
while True:
sql = "select * from ("
index = 1
for dbname in self._cache_mysql.db_info.keys():
sql_select = "(select id,accountName as '用户名',gold as '金币',fee as '税金',operation as '操作',reasons as '操作描述',createTime as '日期',remarks as '备注' " \
"from {databaseName}.gold_log where createTime between FROM_UNIXTIME({start}) and FROM_UNIXTIME({end}))".format(
databaseName=g_cache_mysql.db_info[dbname]["databaseName"], start=start, end=end)
if index == 1:
sql += sql_select
else:
sql += "union all" + sql_select
index += 1
sql += ") as gold order by '日期' desc limit {} offset {};".format(
limit, offset)
dataList = self._cache_mysql.get_list('default', sql)
if len(dataList) == 0:
break
if count == 0:
objExcel.write_excel_row0(list(dataList[0].keys()))
objExcel.write_excel_data(dataList)
offset = limit + offset
count += 1
except Exception as e:
ERROR_MSG(repr(e))
ERROR_MSG("生成【{}】有误，请手动重新生成！！".format(sheetName))
finally:
objExcel.save_file()
return sheetName