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