def handleHttp(request: dict): """app佣金报表查询""" objRep = cResp() agentId = request.get('agentId', '') dateTime = request.get('date', 0) if not dateTime: now = timeHelp.getNow() year = timeHelp.getYear(now) month = timeHelp.getMonth(now) else: year = timeHelp.getYear(dateTime) month = timeHelp.getMonth(dateTime) try: with (yield from classSqlBaseMgr.getInstance().objDbMysqlMgr) as conn: sql = "select * from dj_agent_commission where agentId=%s and dateYear=%s and dateMonth=%s" params = [agentId, year, month] result = yield from conn.execute(sql,params) if result.rowcount <= 0: return classJsonDump.dumps(objRep) else: for var_row in result: commissionDict = {} commissionDict['date'] = str(var_row.dateYear)+'/'+str(var_row.dateMonth) commissionDict['activelyAccountNum'] = var_row.activeAccount commissionDict['winLoss'] = '%.2f'%round(var_row.winLoss/100,2) commissionDict['platformCost'] = '%.2f'%round(var_row.platformCost/100,2) commissionDict['depositDrawingCost'] = '%.2f'%round(var_row.depositDrawingCost/100,2) commissionDict['dividend'] = '%.2f'%round((var_row.backWater+var_row.bonus)/100,2) commissionDict['netProfit'] = '%.2f'%round(var_row.netProfit/100,2) commissionDict['commissionRate'] = '{}%'.format(var_row.commissionRate*100) commissionDict['commission'] = '%.2f'%round(var_row.commission/100,2) commissionDict['status'] = var_row.status objRep.data.append(commissionDict) return classJsonDump.dumps(objRep) except Exception as e: logging.exception(e)
def handleHttp(request: dict): """获取每月历史账单信息""" objRep = cResp() agentId = request.get('agentId', '') pn = request.get('pn', 1) if not pn: raise exceptionLogic(errorLogic.client_param_invalid) year = timeHelp.getYear(timeHelp.getNow()) month = timeHelp.getMonth(timeHelp.getNow()) try: with (yield from classSqlBaseMgr.getInstance().objDbMysqlMgr) as conn: sql = "select * from dj_agent_commission WHERE agentId=%s ORDER BY dateYear DESC ,dateMonth DESC limit %s offset %s" listRet = yield from conn.execute( sql, [agentId, MSG_PAGE_COUNT, (pn - 1) * MSG_PAGE_COUNT]) resList = yield from listRet.fetchall() for x in resList: if x['dateYear'] == year and x['dateMonth'] == month: continue data = cData() data.yearMonth = "{}/{}".format(x['dateYear'], x['dateMonth']) data.regCount = x['newAccount'] data.loginCount = x['activeAccount'] data.totalWater = "%.2f" % round(x['water'] / 100, 2) data.winLoss = "%.2f" % round(x['netProfit'] / 100, 2) data.commission = "%.2f" % round(x['commission'] / 100, 2) objRep.data.append(data) count_sql = "select count(billId) from dj_agent_commission WHERE agentId=%s" countRet = yield from conn.execute(count_sql, [agentId]) count = yield from countRet.fetchone() objRep.count = count[0] return classJsonDump.dumps(objRep) except Exception as e: logging.exception(e) raise exceptionLogic(errorLogic.db_error)
def handleHttp(request: dict): """佣金报表""" objRep = cResp() agentId = request.get('agentId', '') startTime = request.get('startTime', 0) endTime = request.get('endTime', 0) pn = request.get('pn', 1) if not pn: raise exceptionLogic(errorLogic.client_param_invalid) if not all([startTime, endTime]): sql = "select * from dj_agent_commission where agentId=%s order by dateYear desc,dateMonth desc limit %s offset %s" params=[agentId,10,(pn-1)*10] else: startYear = timeHelp.getYear(startTime) startMonth = timeHelp.getMonth(startTime) endYear = timeHelp.getYear(endTime) endMonth = timeHelp.getMonth(endTime) sql = "select * from dj_agent_commission where agentId=%s and (dateYear between %s and %s) and (dateMonth between %s and %s) order by dateYear desc,dateMonth desc limit %s offset %s" params=[agentId,startYear,endYear,startMonth,endMonth,10,(pn-1)*10] try: with (yield from classSqlBaseMgr.getInstance().objDbMysqlMgr) as conn: result = yield from conn.execute(sql,params) if result.rowcount <= 0: return classJsonDump.dumps(objRep) else: for var_row in result: # productList = [] # pingboDict = {} # probetDict = {} # pingboDict['name'] = '平博体育' # pingboDict['winLoss'] = '%.2f'%round(var_row.pingboWinLoss/100,2) # pingboDict['platformCost'] = '%.2f'%round(var_row.pingboCost/100,2) # pingboDict['rate'] = '{}%'.format(agentConfig['平博体育']*100) # productList.append(pingboDict) # # probetDict['name'] = '电竞竞猜' # probetDict['winLoss'] = '%.2f'%round(var_row.probetWinLoss/100,2) # probetDict['platformCost'] = '%.2f'%round(var_row.probetCost/100,2) # probetDict['rate'] = '{}%'.format(agentConfig['电竞竞猜'] * 100) # productList.append(probetDict) commissionDict = {} commissionDict['date'] = str(var_row.dateYear)+'/'+str(var_row.dateMonth) commissionDict['activelyAccountNum'] = var_row.activeAccount commissionDict['pingboWinLoss'] = '%.2f'%round(var_row.pingboWinLoss/100,2) commissionDict['probetWinLoss'] = '%.2f'%round(var_row.probetWinLoss/100,2) commissionDict['pingboCost'] = '%.2f'%round(var_row.pingboCost/100,2) commissionDict['probetCost'] = '%.2f'%round(var_row.probetCost/100,2) commissionDict['pingboRate'] = '{}%'.format(var_row.probetRate * 100) commissionDict['probetRate'] = '{}%'.format(var_row.pingboRate * 100) commissionDict['depositDrawingCost'] = '%.2f'%round(var_row.depositDrawingCost/100,2) commissionDict['backWater'] = '%.2f'%round(var_row.backWater/100,2) commissionDict['bonus'] = '%.2f'%round(var_row.bonus/100,2) commissionDict['netProfit'] = '%.2f'%round(var_row.netProfit/100,2) commissionDict['balance'] = '%.2f'%round(-(var_row.preBalance/100),2) commissionDict['commissionRate'] = '{}%'.format(var_row.commissionRate*100) commissionDict['commission'] = '%.2f'%round(var_row.commission/100,2) commissionDict['status'] = var_row.status objRep.data.append(commissionDict) count = yield from classSqlBaseMgr.getInstance().getAgentCommissionCount(agentId,startTime,endTime) objRep.count = count return classJsonDump.dumps(objRep) except Exception as e: logging.exception(e)
def handleHttp(request: dict): """代理概况查询""" objRep = cResp() agentId = request.get('agentId', '') startTime = request.get('startTime', 0) endTime = request.get('endTime', 0) pn = request.get('pn', 1) if not all([startTime, endTime, pn]): raise exceptionLogic(errorLogic.client_param_invalid) year = timeHelp.getYear(startTime) month = timeHelp.getMonth(startTime) if month <= 1: preYear = year - 1 preMonth = 12 else: preYear = year preMonth = month - 1 try: agentConfig = yield from getAgentConfig() if not agentConfig: raise exceptionLogic(errorLogic.data_not_valid) agentList, count = yield from classSqlBaseMgr.getInstance( ).getAgentData(agentId, pn) for agentId in agentList: with (yield from classSqlBaseMgr.getInstance().objDbMysqlMgr) as conn: sql = "select balance from dj_agent_commission where agentId=%s and dateYear=%s and dateMonth=%s" result = yield from conn.execute(sql, [agentId, preYear, preMonth]) if result.rowcount <= 0: preBalance = 0 else: for var_row in result: preBalance = var_row.balance allAccountNum = yield from classSqlBaseMgr.getInstance( ).getAccountCountByAgent(agentId) activelyAccountNum = yield from classSqlBaseMgr.getInstance( ).getActivelyAccount(agentId, startTime, endTime) depositNum = yield from classSqlBaseMgr.getInstance( ).getAccountAllDeposit(agentId, startTime, endTime) drawingNum = yield from classSqlBaseMgr.getInstance( ).getAccountAllDrawing(agentId, startTime, endTime) balanceNum = yield from classSqlBaseMgr.getInstance( ).getAccountAllCoin(agentId, startTime, endTime) pinboAllWinLoss = yield from classSqlBaseMgr.getInstance( ).getAccountPingboAllWinLoss(agentId, startTime, endTime) probetAllWinLoss = yield from classSqlBaseMgr.getInstance( ).getAccountProbetAllWinCoin(agentId, startTime, endTime) probetWinLoss = round(probetAllWinLoss) pinboWinLoss = round(pinboAllWinLoss * 100) # 平台费 if pinboWinLoss >= 0: pingboPlatformCost = 0 else: pingboPlatformCost = round(-pinboWinLoss * agentConfig['平博体育']) if probetWinLoss >= 0: probetPlatformCost = 0 else: probetPlatformCost = round(-probetWinLoss * agentConfig['电竞竞猜']) platformCost = pingboPlatformCost + probetPlatformCost #反水红利 pingboBackWater = yield from classSqlBaseMgr.getInstance( ).getAccountPinboBackWater(agentId, startTime, endTime) probetBackWater = yield from classSqlBaseMgr.getInstance( ).getAccountProbetBackWater(agentId, startTime, endTime) activetyBonus = yield from classSqlBaseMgr.getInstance( ).getAccountActivetyBonus(agentId, startTime, endTime) # 反水 backWater = round(pingboBackWater + probetBackWater) # 返奖 activetyBonus = round(activetyBonus) # 存提手续费 depositDrawingPoundage = yield from getDepositDrawingPoundage( agentId, startTime, endTime) # 净利润,净输赢 netWin = probetWinLoss + pingboPlatformCost + pinboWinLoss + probetPlatformCost + depositDrawingPoundage + activetyBonus + backWater if netWin <= 0: a = -netWin else: a = netWin for var_value in commission_config.values(): if var_value['leastNetWin'] <= a <= var_value['mostNetWin']: level = var_value['level'] proportion = agentConfig[level] iBalance = netWin + preBalance if iBalance < 0: # 佣金 commission = round(-iBalance * proportion) else: commission = 0 agentSurvey = {} agentSurvey['agentId'] = agentId agentSurvey['allAccountNum'] = allAccountNum agentSurvey['activelyAccountNum'] = activelyAccountNum agentSurvey['depositNum'] = '%.2f' % round(depositNum / 100, 2) agentSurvey['drawingNum'] = '%.2f' % round(drawingNum / 100, 2) agentSurvey['balanceNum'] = '%.2f' % round(balanceNum / 100, 2) agentSurvey['netWin'] = '%.2f' % round(netWin / 100, 2) agentSurvey['platformCost'] = '%.2f' % round(platformCost / 100, 2) agentSurvey['depositdrawingCost'] = '%.2f' % round( depositDrawingPoundage / 100, 2) agentSurvey['commission'] = '%.2f' % round(commission / 100, 2) agentSurvey['commissionRate'] = proportion agentSurvey['dividend'] = '%.2f' % ( (backWater + activetyBonus) / 100) agentSurvey['preBalance'] = '%.2f' % (preBalance / 100) objRep.data.append(agentSurvey) if pn == 1: fileName = __name__ nameList = fileName.split('.') methodName = nameList.pop() # 日志 dictActionBill = { 'billType': 'adminActionBill', 'accountId': request.get('accountId', ''), 'action': "代理概况查询", 'actionTime': timeHelp.getNow(), 'actionMethod': methodName, 'actionDetail': "代理:{}概况查询".format(agentId), 'actionIp': request.get('srcIp', ''), } logging.getLogger('bill').info(json.dumps(dictActionBill)) objRep.count = count return classJsonDump.dumps(objRep) except Exception as e: logging.error(repr(e))
def handleHttp(request: dict): """代理提款查询""" objRep = cResp() agentId = request.get('agentId', '') year = request.get('year', 0) month = request.get('month', 0) pn = request.get('pn', 1) if not all([agentId, pn]): raise exceptionLogic(errorLogic.client_param_invalid) objPlayerData = yield from classDataBaseMgr.getInstance().getPlayerData( agentId) if objPlayerData is None: raise exceptionLogic(errorLogic.player_data_not_found) if objPlayerData.iUserType != 2: raise exceptionLogic(errorLogic.agent_data_not_found) try: conn = classSqlBaseMgr.getInstance() if (not month) and (not year): sql_count = "select count(orderId) from dj_coin_history where accountId='{}' and tradeType=2".format( agentId) sql = "select orderTime,coinNum,tradeState,accountId,endTime,transTo from dj_coin_history where accountId='{}' and tradeType=2 order by orderTime desc limit {} offset {}".format( agentId, 10, (pn - 1) * 10) elif year and (not month): begin = datetime(year, 1, 1) stop = datetime(year + 1, 1, 1) start_time = timeHelp.date2timestamp(begin) end_time = timeHelp.date2timestamp(stop) sql_count = "select count(orderId) from dj_coin_history where accountId='{}' and tradeType=2 and orderTime between {} and {}".format( agentId, start_time, end_time) sql = "select orderTime,coinNum,tradeState,accountId,endTime,transTo from dj_coin_history where accountId='{}' and tradeType=2 and orderTime between {} and {} order by orderTime desc limit {} offset {}".format( agentId, start_time, end_time, 10, (pn - 1) * 10) elif (not year) and month: year = datetime.now().year begin = datetime(year, month, 1) if month == 12: stop = datetime(year + 1, 1, 1) else: stop = datetime(year, month + 1, 1) start_time = timeHelp.date2timestamp(begin) end_time = timeHelp.date2timestamp(stop) sql_count = "select count(orderId) from dj_coin_history where accountId='{}' and tradeType=2 and orderTime between {} and {}".format( agentId, start_time, end_time) sql = "select orderTime,coinNum,tradeState,accountId,endTime,transTo from dj_coin_history where accountId='{}' and tradeType=2 and orderTime between {} and {} order by orderTime desc limit {} offset {}".format( agentId, start_time, end_time, 10, (pn - 1) * 10) else: begin = datetime(year, month, 1) if month == 12: stop = datetime(year + 1, 1, 1) else: stop = datetime(year, month + 1, 1) start_time = timeHelp.date2timestamp(begin) end_time = timeHelp.date2timestamp(stop) sql_count = "select count(orderId) from dj_coin_history where accountId='{}' and tradeType=2 and orderTime between {} and {} limit {} offset {}".format( agentId, start_time, end_time, 10, (pn - 1) * 10) sql = "select orderTime,coinNum,tradeState,accountId,endTime,transTo from dj_coin_history where accountId='{}' and tradeType=2 and orderTime between {} and {} limit {} offset {}".format( agentId, start_time, end_time, 10, (pn - 1) * 10) count_ret = yield from conn._exeCute(sql_count) listRet = yield from conn._exeCute(sql) datas = yield from listRet.fetchall() if count_ret.rowcount <= 0: return classJsonDump.dumps(objRep) else: for var in count_ret: count = var[0] dataList = [] for x in datas: dataDict = {} dataDict['date'] = str(timeHelp.getYear( x.orderTime)) + '/' + str(timeHelp.getMonth(x.orderTime)) dataDict['orderTime'] = x.orderTime dataDict['coinNum'] = "%.2f" % round(x.coinNum / 100, 2) dataDict['tradeState'] = x.tradeState dataDict['accountId'] = x.accountId dataDict['endTime'] = x.endTime dataDict['transTo'] = x.transTo dataList.append(dataDict) objRep.count = count objRep.data = dataList if pn == 1: fileName = __name__ nameList = fileName.split('.') methodName = nameList.pop() # 日志 dictActionBill = { 'billType': 'adminActionBill', 'accountId': request.get('accountId', ''), 'action': "查询代理提款信息", 'actionTime': timeHelp.getNow(), 'actionMethod': methodName, 'actionDetail': "查询代理:{}提款信息".format(agentId), 'actionIp': request.get('srcIp', ''), } logging.getLogger('bill').info(json.dumps(dictActionBill)) return classJsonDump.dumps(objRep) except exceptionLogic as e: logging.exception(e) raise e
def handleHttp(request: dict): """app佣金报表""" objRep = cResp() agentId = request.get('agentId', '') dateTime = request.get('date', 0) if not dateTime: raise exceptionLogic(errorLogic.client_param_invalid) year = timeHelp.getYear(dateTime) month = timeHelp.getMonth(dateTime) if month <= 1: preYear = year - 1 preMonth = 12 else: preYear = year preMonth = month - 1 try: with (yield from classSqlBaseMgr.getInstance().objDbMysqlMgr) as conn: sql_pre = "select netProfit,balance from dj_agent_commission where agentId=%s and dateYear=%s and dateMonth=%s" params_pre = [agentId, preYear, preMonth] ret = yield from conn.execute(sql_pre, params_pre) if ret.rowcount <= 0: pre_netProfit = 0.00 pre_balance = 0.00 else: for var in ret: pre_netProfit = var.netProfit pre_balance = var.balance sql = "select * from dj_agent_commission where agentId=%s and dateYear=%s and dateMonth=%s" params = [agentId, year, month] result = yield from conn.execute(sql, params) if result.rowcount <= 0: return classJsonDump.dumps(objRep) else: for var_row in result: commissionDict = {} productList = [] pingboDict = {} probetDict = {} pingboDict['name'] = '平博体育' pingboDict['winLoss'] = '%.2f' % round( var_row.pingboWinLoss / 100, 2) pingboDict['platformCost'] = '%.2f' % round( var_row.pingboCost / 100, 2) pingboDict['total'] = '%.2f' % round( (var_row.pingboWinLoss + var_row.pingboCost) / 100, 2) productList.append(pingboDict) probetDict['name'] = '电竞竞猜' probetDict['winLoss'] = '%.2f' % round( var_row.probetWinLoss / 100, 2) probetDict['platformCost'] = '%.2f' % round( var_row.probetCost / 100, 2) probetDict['total'] = '%.2f' % round( (var_row.probetWinLoss + var_row.probetCost) / 100, 2) productList.append(probetDict) commissionDict['product'] = productList commissionDict['subtotal'] = '%.2f' % round( (var_row.pingboWinLoss + var_row.pingboCost + var_row.probetWinLoss + var_row.probetCost) / 100, 2) commissionDict['depositDrawingCost'] = '%.2f' % round( var_row.depositDrawingCost / 100, 2) commissionDict['dividend'] = '%.2f' % round( (var_row.backWater + var_row.bonus) / 100, 2) commissionDict['commissionRate'] = '{}%'.format( var_row.commissionRate * 100) commissionDict['pre_netProfit'] = '%.2f' % round( pre_netProfit / 100, 2) commissionDict['pre_balance'] = '%.2f' % round( -(pre_balance / 100), 2) commissionDict['balance'] = '%.2f' % round( -(var_row.balance / 100), 2) commissionDict['commission'] = '%.2f' % round( var_row.commission / 100, 2) objRep.data.append(commissionDict) return classJsonDump.dumps(objRep) except Exception as e: logging.exception(e)
def calcMonthCommission(): strLastCheckTime = yield from classDataBaseMgr.getInstance().getCalcMonthCommissionTime() if strLastCheckTime is None: yield from classDataBaseMgr.getInstance().setCalcMonthCommissionTime(timeHelp.getNow()) iLastCheckTime = int(strLastCheckTime) if not timeHelp.isSameMonth(iLastCheckTime, timeHelp.getNow()): # 计算月佣金 yield from classDataBaseMgr.getInstance().setCalcMonthCommissionTime(timeHelp.getNow()) startTime = timeHelp.lastMonthStartTimestamp() endTime = timeHelp.monthStartTimestamp() year = timeHelp.getYear(startTime) month = timeHelp.getMonth(startTime) if month <= 1: preYear = year - 1 preMonth = 12 else: preYear = year preMonth = month - 1 agentConfig = yield from getAgentConfig() probetRate = agentConfig['电竞竞猜'] pingboRate = agentConfig['平博体育'] try: sql = "select agentId from dj_agent" result = yield from classSqlBaseMgr.getInstance()._exeCute(sql) if result.rowcount <= 0: logging.info(repr("无代理用户")) else: for var in result: agentId = var.agentId with (yield from classSqlBaseMgr.getInstance().objDbMysqlMgr) as conn: sql = "select balance from dj_agent_commission where agentId=%s and dateYear=%s and dateMonth=%s" result = yield from conn.execute(sql, [agentId, preYear, preMonth]) if result.rowcount <= 0: preBalance = 0 else: for var_row in result: preBalance = var_row.balance newAccountNum = yield from classSqlBaseMgr.getInstance().getNewAccountCount(agentId, startTime,endTime) activelyAccountNum = yield from classSqlBaseMgr.getInstance().getActivelyAccount(agentId, startTime,endTime) pinboAllWinLoss = yield from classSqlBaseMgr.getInstance().getAccountPingboAllWinLoss(agentId,startTime,endTime) probetAllWinLoss = yield from classSqlBaseMgr.getInstance().getAccountProbetAllWinCoin(agentId,startTime,endTime) probetWinLoss = round(probetAllWinLoss) pinboWinLoss = round(pinboAllWinLoss * 100) # 平台费 if pinboWinLoss >= 0: pingboPlatformCost = 0 else: pingboPlatformCost = round(-pinboWinLoss * agentConfig['平博体育']) if probetWinLoss >= 0: probetPlatformCost = 0 else: probetPlatformCost = round(-probetWinLoss * agentConfig['电竞竞猜']) # 总输赢 winLoss = pinboWinLoss + probetWinLoss # 平台费 platformCost = pingboPlatformCost + probetPlatformCost pingboBackWater = yield from classSqlBaseMgr.getInstance().getAccountPinboBackWater(agentId,startTime,endTime) probetBackWater = yield from classSqlBaseMgr.getInstance().getAccountProbetBackWater(agentId,startTime,endTime) activetyBonus = yield from classSqlBaseMgr.getInstance().getAccountActivetyBonus(agentId, startTime,endTime) pinboAllWater = yield from classSqlBaseMgr.getInstance().getAccountPingboWater(agentId, startTime,endTime) probetAllWater = yield from classSqlBaseMgr.getInstance().getAccountProbetWater(agentId, startTime,endTime) pingboWater = round(pinboAllWater * 100) probetWater = round(probetAllWater) allWater = pingboWater + probetWater # 反水 backWater = round(pingboBackWater + probetBackWater) activetyBonus = round(activetyBonus) # 存提手续费 depositDrawingPoundage = yield from getDepositDrawingPoundage(agentId, startTime, endTime) # 净利润,净输赢 netProfit = winLoss + platformCost + depositDrawingPoundage + activetyBonus + backWater # 月结余 iBalance = netProfit + preBalance if netProfit <= 0: netProfit = -netProfit for var_value in commission_config.values(): if var_value['leastNetWin'] <= netProfit <= var_value['mostNetWin']: level = var_value['level'] proportion = agentConfig[level] else: netProfit = -netProfit proportion = agentConfig['一档'] if iBalance < 0: commission = round(-iBalance * proportion) iBalance = 0 else: commission = 0 sql = "select billId from dj_agent_commission where dateYear={} and dateMonth={} and agentId='{}'".format( year, month, agentId) result = yield from classSqlBaseMgr.getInstance()._exeCute(sql) if result.rowcount <= 0: billId = str(uuid.uuid1()) if commission <= 0: status = 2 else: status = 1 objAgentCommissionData = yield from addCommissionBill(billId, agentId, year, month, newAccountNum, activelyAccountNum, probetWinLoss, pinboWinLoss, winLoss,probetRate,pingboRate, probetPlatformCost, pingboPlatformCost, platformCost, depositDrawingPoundage, backWater,activetyBonus, allWater, netProfit,preBalance,iBalance, proportion, commission, status) yield from classDataBaseMgr.getInstance().addAgentCommissionData(objAgentCommissionData) else: for var in result: billId = var.billId if commission <= 0: status = 2 else: status = 1 objAgentCommissionData = yield from addCommissionBill(billId, agentId, year, month, newAccountNum, activelyAccountNum, probetWinLoss, pinboWinLoss, winLoss,probetRate,pingboRate, probetPlatformCost, pingboPlatformCost, platformCost, depositDrawingPoundage, backWater,activetyBonus, allWater, netProfit,preBalance,iBalance, proportion, commission, status) yield from classDataBaseMgr.getInstance().addAgentCommissionData(objAgentCommissionData, billId) except Exception as e: #logging.exception(e) logging.exception(e) raise e