tasks.py 33 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804
  1. # -*- coding: utf-8 -*-
  2. # !/usr/bin/env python
  3. import datetime
  4. from collections import OrderedDict
  5. import arrow
  6. import requests
  7. from bson import ObjectId
  8. from celery.utils.log import get_task_logger
  9. from django.conf import settings
  10. from django.core.cache import cache
  11. from typing import TYPE_CHECKING, Optional, Union
  12. from apilib.utils_datetime import timestamp_to_dt, to_datetime
  13. from apps.web.agent.models import Agent
  14. from apps.web.agent.withdraw import AgentWithdrawRetryService
  15. from apps.web.common.models import WithdrawRecord
  16. from apps.web.common.proxy import ClientConsumeModelProxy
  17. from apps.web.common.transaction import WithdrawStatus
  18. from apps.web.constant import Const, MONTH_DATE_KEY, MANAGER_EXPORT_EXCEL_TYPE, DEALER_CONSUMPTION_AGG_KIND_TRANSLATION
  19. from apps.web.core import ROLE
  20. from apps.web.core.payment import WithdrawGateway
  21. from apps.web.core.payment.ali import AliPayWithdrawGateway, AlipayWithdrawQueryResult
  22. from apps.web.core.payment.wechat import WechatWithdrawQueryResult, WechatWithdrawGateway
  23. from apps.web.core.sysparas import SysParas
  24. from apps.web.core.utils import generate_excel_report
  25. from apps.web.dealer.models import DealerRechargeRecord, Dealer
  26. from apps.web.dealer.proxy import DealerIncomeProxy
  27. from apps.web.dealer.withdraw import DealerWithdrawRetryService
  28. from apps.web.device.models import Device, Group
  29. from apps.web.exceptions import WithdrawOrderNotExist
  30. from apps.web.helpers import get_inhouse_wechat_manager_mp_proxy
  31. from apps.web.management.models import Manager
  32. from apps.web.management.utils import get_dealerMap_by_managerId, query_device_income, query_device_consumption, \
  33. get_user_stats, get_feed_back_stats, query_total_dealer_income_top, query_user_consume_frequency, \
  34. get_device_being_used_trend
  35. from apps.web.report.models import DealerMonthlyStat
  36. from apps.web.user.models import RechargeRecord, CardRechargeOrder, MyUser, ConsumeRecord
  37. if TYPE_CHECKING:
  38. from apps.web.common.models import CapitalUser
  39. logger = get_task_logger(__name__)
  40. def _tell_finance_manager(account, obj, content):
  41. system_alarmer_list = SysParas.get_system_alarmer() # type: dict
  42. if not system_alarmer_list or len(system_alarmer_list) == 0:
  43. logger.debug('not set system alarm manager.')
  44. return
  45. for system_alarmer in system_alarmer_list:
  46. openId = system_alarmer.get('openId', None)
  47. if not openId:
  48. logger.debug('not set system alarm manager.')
  49. continue
  50. proxy = get_inhouse_wechat_manager_mp_proxy()
  51. proxy.notify(openId, 'system_alarm_notify', **{
  52. 'title': u'{}同学,系统告警被触发,请关注,尽快处理。'.format(system_alarmer.get('username', '')),
  53. 'account': account,
  54. 'object': obj,
  55. 'content': content,
  56. 'time': arrow.now().format('YYYY-MM-DD HH:mm:ss')
  57. })
  58. def weekly_notify_finance_manager():
  59. """
  60. 默认为星期3和星期5推送
  61. 提醒财务经理处理财务信息
  62. [0] 注意调配资金池,确保客户能够正常提现
  63. :return:
  64. """
  65. now = datetime.datetime.now()
  66. weekday = now.weekday()
  67. logger.info('executing weekly_notify_finance_manager, today is %s' % (now.strftime('%Y-%m-%d %H:%M:%S'),))
  68. #: 只在周一周三周五提醒
  69. if weekday in (0, 2, 4):
  70. offset = now - datetime.timedelta(days = weekday + 1)
  71. withdrawal_sum = WithdrawRecord.objects(postTime__lte = now, postTime__gte = offset).sum('actualPay')
  72. appendix = u'今日为周%s,请注意额外添置!' % (weekday + 1,)
  73. return _tell_finance_manager(account = u'微付乐', obj = u'资金池',
  74. content = (u'请注意调配情况,本周一到今天的总额为%.2f' % (withdrawal_sum,)) + appendix)
  75. def whale_withdraw_order_alert():
  76. """
  77. 提现出现大单,预警资金池情况
  78. :return:
  79. """
  80. return _tell_finance_manager(account = u'微付乐',
  81. obj = u'资金池',
  82. content = u'请注意,有大单(金额 >= %s),请及时检查池情况!' % (settings.WHALE_WITHDRAWAL_ORDER_AMOUNT,))
  83. def withdraw_error_alert(err_type):
  84. """
  85. 当提现出现错误(大概率下的资金池不足),及时提醒财务经理进行操作
  86. :return:
  87. """
  88. if err_type == 'NOTENOUGH':
  89. return _tell_finance_manager(account = u'微付乐',
  90. obj = u'资金池',
  91. content = u'[重要]微信支付商户号资金不足,客户无法提现,请及时处理!')
  92. def check_website_is_online():
  93. """
  94. 每隔一段时间向几个页面发出请求,以确保页面能够正常工作
  95. 带上特定的user-agent
  96. :return:
  97. """
  98. url = settings.SERVER_END_BASE_URL
  99. response = requests.get(url, timeout = 15)
  100. if response.status_code != 200:
  101. _tell_finance_manager(account = u'微付乐', object = u'服务器', content = u'服务器网站无法连通,请及时检查!')
  102. def backup_wechat_subscription_account_messages():
  103. """
  104. 每隔三天,腾讯会清空用户发至公众号的信息。
  105. 这里需要备份,以确保
  106. [0] 能够及时收集用户反馈
  107. [1] 能够追踪用户出现问题时,对系统发的图片以及
  108. :return:
  109. """
  110. def backup_third_party_order_bills():
  111. """
  112. 将微信与支付宝的订单拉取备份,以方便后台对账
  113. :return:
  114. """
  115. def generate_simCharge_excel_report(filepath, queryAttrs):
  116. queryAttrs['dateTimeAdded__lte'] = timestamp_to_dt(queryAttrs['dateTimeAdded__lte'])
  117. queryAttrs['dateTimeAdded__gte'] = timestamp_to_dt(queryAttrs['dateTimeAdded__gte'])
  118. managerId = queryAttrs['managerId']
  119. startTime = queryAttrs['dateTimeAdded__gte']
  120. endTime = queryAttrs['dateTimeAdded__lte']
  121. agentId = queryAttrs.get('agentId', '')
  122. dealerId = queryAttrs.get('dealerId', '')
  123. if agentId == '' and dealerId == '':
  124. agentsIdList = [str(_['_id']) for _ in Agent.get_collection().find({'managerId': managerId}, {'_id': 1})]
  125. dealersIdList = [str(_['_id']) for _ in
  126. Dealer.get_collection().find({'agentId': {'$in': agentsIdList}}, {'_id': 1})]
  127. simRechargeRecords = DealerRechargeRecord.objects(
  128. dealerId__in = dealersIdList,
  129. status = DealerRechargeRecord.PayState.Paid,
  130. finishedTime__gte = startTime,
  131. finishedTime__lte = endTime
  132. )
  133. elif agentId != '' and dealerId == '':
  134. dealersIdList = [str(_['_id']) for _ in Dealer.get_collection().find({'agentId': agentId}, {'_id': 1})]
  135. simRechargeRecords = DealerRechargeRecord.objects(
  136. dealerId__in = dealersIdList,
  137. status = DealerRechargeRecord.PayState.Paid,
  138. finishedTime__gte = startTime,
  139. finishedTime__lte = endTime
  140. )
  141. elif agentId != '' and dealerId != '':
  142. simRechargeRecords = DealerRechargeRecord.objects(
  143. dealerId = dealerId,
  144. status = DealerRechargeRecord.PayState.Paid,
  145. finishedTime__gte = startTime,
  146. finishedTime__lte = endTime
  147. )
  148. else:
  149. return None
  150. records = []
  151. for simRechargeRecord in simRechargeRecords.order_by('-finishedTime'):
  152. dealer = Dealer.objects(id = simRechargeRecord.dealerId).first()
  153. if not dealer:
  154. logger.error(u'该经销商用户不存在, id=%s' % simRechargeRecord.dealerId)
  155. return None
  156. agent = Agent.objects(id = dealer.agentId).first()
  157. if not agent:
  158. logger.error(u'该代理商用户不存在, id=%s' % dealer.agentId)
  159. return None
  160. device = Device.objects(devNo = simRechargeRecord.items[0]['devNo']).first()
  161. if not device:
  162. logger.error(u'该设备信息不存在, devNo=%s' % simRechargeRecord.items[0]['devNo'])
  163. return None
  164. try:
  165. sumPrice = str(simRechargeRecord.sum_of_price)
  166. except Exception as e:
  167. continue
  168. records.append(
  169. OrderedDict([
  170. (u'经销商', dealer.nickname),
  171. (u'手机号码', dealer.username),
  172. (u'所属代理商', agent.nickname),
  173. (u'设备编号', device.logicalCode),
  174. (u'设备类型', device.devTypeName),
  175. (u'设备注册时间', device.dateTimeAdded),
  176. (u'充值金额', sumPrice),
  177. (u'充值日期', datetime.datetime.strftime(simRechargeRecord.finishedTime, Const.DATETIME_FMT))
  178. ])
  179. )
  180. generate_excel_report(filepath, records)
  181. def generate_dealerWithDraw_excel_report(filepath, queryAttrs):
  182. queryAttrs['dateTimeAdded__lte'] = timestamp_to_dt(queryAttrs['dateTimeAdded__lte'])
  183. queryAttrs['dateTimeAdded__gte'] = timestamp_to_dt(queryAttrs['dateTimeAdded__gte'])
  184. managerId = queryAttrs['managerId']
  185. startTime = queryAttrs['dateTimeAdded__gte']
  186. endTime = queryAttrs['dateTimeAdded__lte']
  187. agentsIdList = [str(_['_id']) for _ in Agent.get_collection().find({'managerId': managerId}, {'_id': 1})]
  188. dealersIdList = [str(_['_id']) for _ in
  189. Dealer.get_collection().find({'agentId': {'$in': agentsIdList}}, {'_id': 1})]
  190. withdrawRecords = WithdrawRecord.objects(
  191. ownerId__in = dealersIdList, role = ROLE.dealer,
  192. status = WithdrawStatus.SUCCEEDED,
  193. postTime__gte = startTime,
  194. postTime__lte = endTime
  195. )
  196. records = []
  197. for withdrawRecord in withdrawRecords.order_by('-postTime'):
  198. dealer = Dealer.objects(id = withdrawRecord.ownerId).first()
  199. if not dealer:
  200. logger.error(u'没有找到该经销商, id=%s' % withdrawRecord.ownerId)
  201. return None
  202. records.append(
  203. OrderedDict([
  204. (u'经销商', dealer.nickname),
  205. (u'手机号码', dealer.username),
  206. (u'提现金额', str(withdrawRecord.amount)),
  207. (u'提现日期', datetime.datetime.strftime(withdrawRecord.postTime, Const.DATETIME_FMT))
  208. ])
  209. )
  210. generate_excel_report(filepath, records)
  211. def generate_manager_map_options():
  212. midList = [str(_['_id']) for _ in list(Manager.get_collection().find({}, {'_id': 1}))]
  213. for mid in midList:
  214. # todo 先过滤掉自己的厂商, 数据太大会给celery造成负担, 后期可以改成有需要的厂商进行显示
  215. if mid == '59974b4b8732d6480fb699e5':
  216. continue
  217. agentIdList = [str(_['_id']) for _ in
  218. list(Agent.get_collection().find({'managerId': mid}, {'_id': 1}))]
  219. dealerDict = {str(item['_id']): item['nickname'] for item in
  220. Dealer.get_collection().find({'agentId': {'$in': agentIdList}}, {'_id': 1, 'nickname': 1})}
  221. cache.set('getAllDeviceIncomeList_%s' % mid, query_device_income(dealerDict.keys()), 24 * 60 * 60)
  222. cache.set('getAllDeviceConsumption_%s' % mid, query_device_consumption(dealerDict.keys()), 24 * 60 * 60)
  223. cache.set('getAllUserStatistics_%s' % mid, get_user_stats(dealerDict.keys()), 24 * 60 * 60)
  224. cache.set('getAllFeedbackStatistics_%s' % mid, get_feed_back_stats(dealerDict.keys()), 24 * 60 * 60)
  225. cache.set('getDealerIncomeTotalTop_%s' % mid, query_total_dealer_income_top(dealerDict), 24 * 60 * 60)
  226. cache.set('getUserConsumeFrequency_%s' % mid, query_user_consume_frequency(dealerDict.keys()), 24 * 60 * 60)
  227. cache.set('getDevBeingUsedTrend_%s' % mid, get_device_being_used_trend(dealerDict.keys()), 24 * 60 * 60)
  228. def generate_biz_stats_for_manager(filepath, queryAttrs):
  229. endTime = timestamp_to_dt(queryAttrs['dateTimeAdded__lte'])
  230. startTime = timestamp_to_dt(queryAttrs['dateTimeAdded__gte'])
  231. agentId = queryAttrs.get('agentId')
  232. dealerId = queryAttrs.get('dealerId')
  233. managerId = queryAttrs['managerId']
  234. dealerMap = get_dealerMap_by_managerId(managerId = managerId, agentId = agentId, dealerId = dealerId)
  235. dealerIds = dealerMap.keys()
  236. if queryAttrs['type'] == MANAGER_EXPORT_EXCEL_TYPE.RECHARGE:
  237. queryset = RechargeRecord.get_recharged_records(
  238. ownerId__in = dealerIds,
  239. dateTimeAdded__lte = endTime,
  240. dateTimeAdded__gte = startTime
  241. )
  242. records = [
  243. OrderedDict([
  244. (u"订单号", record.orderNo),
  245. (u"支付网关订单号", record.wxOrderNo),
  246. (u"用户昵称", record.nickname),
  247. (u"经销商", dealerMap[record.ownerId]['nickname']),
  248. (u"代理商名称", dealerMap[record.ownerId]['agentName']),
  249. (u"设备编码", record.logicalCode),
  250. (u"设备类型", record.dev_type_name),
  251. (u"金额", str(record.money)),
  252. (u"地址", record.groupName),
  253. (u"时间", record.to_datetime_str(record.dateTimeAdded))
  254. ])
  255. for record in queryset
  256. ]
  257. elif queryAttrs['type'] == MANAGER_EXPORT_EXCEL_TYPE.RECHARGE_CARD:
  258. queryset = RechargeRecord.get_card_recharged_records(
  259. ownerId__in = dealerIds,
  260. dateTimeAdded__lte = endTime,
  261. dateTimeAdded__gte = startTime
  262. )
  263. records = []
  264. for record in queryset: # type: RechargeRecord
  265. order = CardRechargeOrder.get_by_rechargeRecord(record)
  266. entry = OrderedDict([
  267. (u"订单号", record.orderNo),
  268. (u"支付网关订单号", record.wxOrderNo),
  269. (u"卡号", order.cardNo if order else ''),
  270. (u"用户昵称", record.nickname),
  271. (u"经销商", dealerMap[record.ownerId]['nickname']),
  272. (u"代理商名称", dealerMap[record.ownerId]['agentName']),
  273. (u"设备编码", record.logicalCode),
  274. (u"设备类型", record.dev_type_name),
  275. (u"金额", str(record.money)),
  276. (u"地址", record.groupName),
  277. (u"时间", record.to_datetime_str(record.dateTimeAdded))
  278. ])
  279. records.append(entry)
  280. else:
  281. return
  282. generate_excel_report(filepath, records)
  283. def check_withdraw_via_bank():
  284. """
  285. 由于不能实时获取是否微信或者支付宝成功给银行卡转账了,需要每日进行查询并更新状态
  286. 主要处理银行退单的情况,则需要将款项返还给经销商/代理商
  287. :return:
  288. """
  289. def process_withdraw(record):
  290. # type: (WithdrawRecord)->None
  291. try:
  292. payee = ROLE.from_role_id(record.role, str(record.ownerId)) # type: CapitalUser
  293. handler = payee.new_withdraw_handler(record)
  294. try:
  295. gateway = WithdrawGateway.from_withdraw_gateway_key(
  296. record.withdrawGatewayKey,
  297. record.extras.get(
  298. 'gateway_version', 'v1')) # type: Optional[WechatWithdrawGateway, AliPayWithdrawGateway]
  299. query_result = gateway.get_transfer_result_via_bank(
  300. record.order) # type: Union[WechatWithdrawQueryResult, AlipayWithdrawQueryResult]
  301. errcode, errmsg = query_result.error_desc
  302. if query_result.is_failed or query_result.is_refund:
  303. handler.revoke(remarks = errcode, description = errmsg)
  304. elif query_result.is_successful:
  305. handler.approve(finishedTime = query_result.finished_time, extra = query_result.extra)
  306. except WithdrawOrderNotExist:
  307. logger.warning('withdraw order<orderNo={}> is not exist.'.format(record.order))
  308. handler.revoke(remarks = u'订单不存在', description = u'提现失败')
  309. except Exception as e:
  310. logger.exception(e)
  311. def process_v3_withdraw(record):
  312. # type: (WithdrawRecord)->None
  313. try:
  314. version = record.extras.get('gateway_version', 'v1')
  315. if version != 'v3':
  316. logger.warning('record<id={}> is not v3.'.format(str(record.id)))
  317. return
  318. payee = ROLE.from_role_id(record.role, str(record.ownerId)) # type: CapitalUser
  319. handler = payee.new_withdraw_handler(record)
  320. try:
  321. gateway = WithdrawGateway.from_withdraw_gateway_key(
  322. record.withdrawGatewayKey,
  323. record.extras.get(
  324. 'gateway_version', 'v1')) # type: WechatWithdrawGateway
  325. query_result = gateway.get_transfer_result_via_changes(
  326. record.order) # type: WechatWithdrawQueryResult
  327. errcode, errmsg = query_result.error_desc
  328. if query_result.is_failed or query_result.is_refund:
  329. handler.revoke(remarks = errcode, description = errmsg)
  330. elif query_result.is_successful:
  331. handler.approve(finishedTime = query_result.finished_time, extra = query_result.extra)
  332. except WithdrawOrderNotExist:
  333. logger.warning('withdraw order<orderNo={}> is not exist.'.format(record.order))
  334. handler.revoke(remarks = u'订单不存在', description = u'提现失败')
  335. except Exception as e:
  336. logger.exception(e)
  337. records = list(WithdrawRecord.get_processing_via_bank())
  338. for record in records:
  339. process_withdraw(record)
  340. records = list(WithdrawRecord.get_processing_via_v3())
  341. for record in records:
  342. process_v3_withdraw(record)
  343. def check_and_retry_withdraw():
  344. def process_dealer_withdraw(record):
  345. # type: (WithdrawRecord)->None
  346. try:
  347. DealerWithdrawRetryService(record).execute()
  348. except Exception as e:
  349. logger.exception(e)
  350. def process_agent_withdraw(record):
  351. # type: (WithdrawRecord)->None
  352. try:
  353. AgentWithdrawRetryService(record).execute()
  354. except Exception as e:
  355. logger.exception(e)
  356. records = list(WithdrawRecord.get_failed_records())
  357. for record in records: # type: WithdrawRecord
  358. if (datetime.datetime.now() - record.postTime).days > 5:
  359. logger.debug('record<id={}> has not been done in five days.'.format(str(record.id)))
  360. continue
  361. logger.debug('now retry withdraw for record<id={}, orderNo={}>'.format(str(record.id), record.order))
  362. if record.role == ROLE.dealer:
  363. process_dealer_withdraw(record)
  364. elif record.role == ROLE.agent:
  365. process_agent_withdraw(record)
  366. else:
  367. logger.error('invalid withdraw role')
  368. def manager_export_charge_order_excel_from_db(filepath, queryDict):
  369. logger.error('start manager_export_charge_order_excel_from_db,filepath=%s,query=%s' % (filepath, queryDict))
  370. startDate = queryDict.get('startTime', None)
  371. endDate = queryDict.get('endTime', None)
  372. dealerId = queryDict.get('dealerId', '')
  373. agentId = queryDict.get('agentId', '')
  374. managerId = queryDict.get('managerId', '')
  375. chargeTypeDict = {'recharge': u'充值', 'sendcoin': u'赠币', 'refund': u'退币', 'chargeCard': u'卡充值',
  376. 'chargeVirtualCard': u'虚拟卡充值'}
  377. userDict = {}
  378. if agentId == '' and dealerId == '':
  379. agentDict = {}
  380. agentsIdList = []
  381. for agent in Agent.get_collection().find({'managerId': managerId}, {'_id': 1, 'username': 1, 'nickname': 1}):
  382. agentDict[str(agent['_id'])] = '%s %s' % (agent['nickname'], agent['username'])
  383. agentsIdList.append(str(agent['_id']))
  384. dealerDict = {}
  385. dealersIdList = []
  386. for dealer in Dealer.get_collection().find({'agentId': {'$in': agentsIdList}},
  387. {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1}):
  388. dealerDict[str(dealer['_id'])] = {'dealerName': '%s %s' % (dealer['nickname'], dealer['username']),
  389. 'agentName': agentDict.get(dealer['agentId'], '')}
  390. dealersIdList.append(str(dealer['_id']))
  391. records = RechargeRecord.objects(ownerId__in = dealersIdList,
  392. result = 'success',
  393. dateTimeAdded__gte = to_datetime(startDate),
  394. dateTimeAdded__lte = to_datetime(endDate),
  395. via__nin = ['sendcoin', 'refund']).order_by('-dateTimeAdded')
  396. elif agentId != '' and dealerId == '':
  397. agents = Agent.get_collection().find({'_id': ObjectId(agentId)}, {'_id': 1, 'username': 1, 'nickname': 1})
  398. dealerDict = {}
  399. dealersIdList = []
  400. for dealer in Dealer.get_collection().find({'agentId': agentId},
  401. {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1}):
  402. dealerDict[str(dealer['_id'])] = {'dealerName': '%s %s' % (dealer['nickname'], dealer['username']),
  403. 'agentName': '%s %s' % (agents[0]['nickname'], agents[0]['username'])}
  404. dealersIdList.append(str(dealer['_id']))
  405. records = RechargeRecord.objects(ownerId__in = dealersIdList,
  406. result = 'success',
  407. dateTimeAdded__gte = to_datetime(startDate),
  408. dateTimeAdded__lte = to_datetime(endDate),
  409. via__nin = ['sendcoin', 'refund']).order_by('-dateTimeAdded')
  410. elif agentId != '' and dealerId != '':
  411. agents = Agent.get_collection().find({'_id': ObjectId(agentId)}, {'_id': 1, 'username': 1, 'nickname': 1})
  412. dealers = Dealer.get_collection().find({'_id': ObjectId(dealerId)},
  413. {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1})
  414. dealerDict = {}
  415. dealerDict[str(dealers[0]['_id'])] = {'dealerName': '%s %s' % (dealers[0]['nickname'], dealers[0]['username']),
  416. 'agentName': '%s %s' % (agents[0]['nickname'], agents[0]['username'])}
  417. records = RechargeRecord.objects(ownerId = dealerId,
  418. result = 'success',
  419. dateTimeAdded__gte = to_datetime(startDate),
  420. dateTimeAdded__lte = to_datetime(endDate),
  421. via__nin = ['sendcoin', 'refund']).order_by('-dateTimeAdded')
  422. else:
  423. records = []
  424. groupIds = []
  425. rcdList = []
  426. for obj in records:
  427. rcdList.append(obj)
  428. groupIds.append(obj.groupId)
  429. groupIds = list(set(groupIds))
  430. partnerDict = {}
  431. for groupId in groupIds:
  432. group = Group.get_group(groupId)
  433. if group is None:
  434. continue
  435. partnerDict.update(group.get('partnerDict', {}))
  436. rows = []
  437. for rcd in rcdList:
  438. if rcd.via in ['refund', 'sendcoin']:
  439. continue
  440. # 检查下如果是卡充值,就需要检查是否已经完成整个流程
  441. if rcd.via == 'chargeCard' and CardRechargeOrder.objects.filter(rechargeNo = rcd.id,
  442. status = 'finished').count() == 0:
  443. continue
  444. openId = rcd.openId
  445. if not userDict.has_key(openId):
  446. try:
  447. user = MyUser.objects.filter(openId = openId).only('openId', 'nickname', 'sex', 'groupId', 'country',
  448. 'province', 'city').first()
  449. sex = u'未知'
  450. if user.sex == 0:
  451. sex = u'女'
  452. elif user.sex == 1:
  453. sex = u'男'
  454. else:
  455. pass
  456. user = {'sex': sex, 'nickname': user.nickname,
  457. 'zone': '%s%s%s' % (user.country, user.province, user.city)}
  458. userDict[openId] = user
  459. except Exception, e:
  460. continue
  461. else:
  462. user = userDict[openId]
  463. try:
  464. proxyRcd = DealerIncomeProxy.objects.get(ref_id = rcd.id)
  465. except Exception, e:
  466. continue
  467. # 获取代理商、经销商的收入
  468. amountDict = DealerIncomeProxy.get_agent_partner_allocated_money(rcd.ownerId, proxyRcd.partition, partnerDict)
  469. dataList = [
  470. (u'代理商', dealerDict.get(rcd.ownerId, {}).get('agentName')),
  471. (u'经销商', dealerDict.get(rcd.ownerId, {}).get('dealerName')),
  472. (u'逻辑编码', rcd.logicalCode),
  473. (u'IMEI', rcd.devNo),
  474. (u'设备类型', rcd.dev_type_name),
  475. (u'组名称', rcd.groupName),
  476. (u'组内编号', rcd.groupNumber),
  477. (u'组地址', rcd.address),
  478. (u'用户昵称', user.get('nickname', '')),
  479. (u'用户性别', user.get('sex', '')),
  480. (u'用户地域', user.get('zone', '')),
  481. (u'订单号', rcd.orderNo),
  482. (u'下单时间', rcd.time),
  483. (u'第三方支付单号', rcd.wxOrderNo),
  484. (u'支付方式', u'微信' if rcd.gateway == 'wechat' else u'支付宝'),
  485. (u'是否快捷支付', 'yes' if rcd.isQuickPay else 'no'),
  486. (u'充值金额', str(rcd.money)),
  487. (u'充值金币', str(rcd.coins)),
  488. (u'充值方式', chargeTypeDict.get(rcd.via, '')), # 卡充值、虚拟卡充值、直接充值等
  489. (u'设备负责人分配收入', str(amountDict.get('ownerAmount'))),
  490. (u'代理商分配金额', str(amountDict.get('agentAmount')))
  491. ]
  492. # 把所有合伙人的分配都记录下来
  493. for partnerIncome in amountDict.get('partnerDict').values():
  494. dataList.append((u'合伙人分配金额:%s(%s)' % (partnerIncome['nickname'], partnerIncome['username']),
  495. str(partnerIncome['money'])))
  496. rows.append(OrderedDict(dataList))
  497. generate_excel_report(filepath, rows)
  498. logger.error('finished export_charge_order_excel_from_db')
  499. def manager_export_consume_order_excel_from_db(filepath, queryDict):
  500. logger.error('start manager_export_consume_order_excel_from_db,filepath=%s,query=%s' % (filepath, queryDict))
  501. startDate = queryDict.get('startTime', None)
  502. endDate = queryDict.get('endTime', None)
  503. dealerId = queryDict.get('dealerId', '')
  504. agentId = queryDict.get('agentId', '')
  505. managerId = queryDict.get('managerId', '')
  506. records = []
  507. dealerDict = {}
  508. dealersIdList = []
  509. if agentId == '' and dealerId == '':
  510. agentDict = {}
  511. agentsIdList = []
  512. for agent in Agent.get_collection().find({'managerId': managerId}, {'_id': 1, 'username': 1, 'nickname': 1}):
  513. agentDict[str(agent['_id'])] = '%s %s' % (agent['nickname'], agent['username'])
  514. agentsIdList.append(str(agent['_id']))
  515. for dealer in Dealer.get_collection().find({'agentId': {'$in': agentsIdList}},
  516. {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1}):
  517. dealerDict[str(dealer['_id'])] = {'dealerName': '%s %s' % (dealer['nickname'], dealer['username']),
  518. 'agentName': agentDict.get(dealer['agentId'], '')}
  519. dealersIdList.append(str(dealer['_id']))
  520. elif agentId != '' and dealerId == '':
  521. agents = Agent.get_collection().find({'_id': ObjectId(agentId)}, {'_id': 1, 'username': 1, 'nickname': 1})
  522. for dealer in Dealer.get_collection().find({'agentId': agentId},
  523. {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1}):
  524. dealerDict[str(dealer['_id'])] = {'dealerName': '%s %s' % (dealer['nickname'], dealer['username']),
  525. 'agentName': '%s %s' % (agents[0]['nickname'], agents[0]['username'])}
  526. dealersIdList.append(str(dealer['_id']))
  527. elif agentId != '' and dealerId != '':
  528. agents = Agent.get_collection().find({'_id': ObjectId(agentId)}, {'_id': 1, 'username': 1, 'nickname': 1})
  529. dealers = Dealer.get_collection().find({'_id': ObjectId(dealerId)},
  530. {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1})
  531. dealerDict[str(dealers[0]['_id'])] = {'dealerName': '%s %s' % (dealers[0]['nickname'], dealers[0]['username']),
  532. 'agentName': '%s %s' % (agents[0]['nickname'], agents[0]['username'])}
  533. dealersIdList = [dealerId]
  534. else:
  535. pass
  536. rcdList = []
  537. servicedKeysTemp = []
  538. for rcd in ClientConsumeModelProxy.get_data_list(startTime = startDate,
  539. endTime = endDate,
  540. ownerId__in = dealersIdList,
  541. isNormal = True): # type: ConsumeRecord
  542. servicedKeysTemp.extend(rcd.servicedInfo.keys())
  543. rcdList.append(rcd)
  544. servicedKeysTemp = list(set(servicedKeysTemp))
  545. servicedKeys = []
  546. for key in servicedKeysTemp:
  547. if key in DEALER_CONSUMPTION_AGG_KIND_TRANSLATION:
  548. servicedKeys.append(key)
  549. else:
  550. continue
  551. userDict = {}
  552. records = []
  553. for rcd in rcdList:
  554. openId = rcd.openId
  555. if not userDict.has_key(openId):
  556. try:
  557. user = MyUser.objects.filter(openId = openId).only('openId', 'nickname', 'sex', 'groupId', 'country',
  558. 'province', 'city').first()
  559. sex = u'未知'
  560. if user.sex == 0:
  561. sex = u'女'
  562. elif user.sex == 1:
  563. sex = u'男'
  564. else:
  565. pass
  566. user = {'sex': sex, 'nickname': user.nickname,
  567. 'zone': '%s%s%s' % (user.country, user.province, user.city)}
  568. userDict[openId] = user
  569. except Exception, e:
  570. continue
  571. else:
  572. user = userDict[openId]
  573. dataList = [
  574. (u'代理商', dealerDict.get(rcd.ownerId, {}).get('agentName')),
  575. (u'经销商', dealerDict.get(rcd.ownerId, {}).get('dealerName')),
  576. (u'逻辑编码', rcd.logicalCode),
  577. (u'IMEI', rcd.devNo),
  578. (u'设备端口', rcd.attachParas.get('port', '-')),
  579. (u'设备类型', rcd.dev_type_name),
  580. (u'组名称', rcd.groupName),
  581. (u'组内编号', rcd.groupNumber),
  582. (u'组地址', rcd.address),
  583. (u'用户昵称', user.get('nickname', '')),
  584. (u'用户性别', user.get('sex', '')),
  585. (u'用户地域', user.get('zone', '')),
  586. (u'订单号', rcd.orderNo),
  587. (u'下单时间', rcd.to_datetime_str(rcd.dateTimeAdded)),
  588. (u'花费金币', str(rcd.coin)),
  589. (u'消费备注', str(rcd.remarks) if rcd.remarks else u'扫码消费'),
  590. (u'端口', str(rcd.attachParas.get('port', '')))
  591. ]
  592. # 把消费细节也记录到excel表中
  593. for key in servicedKeys:
  594. dataList.append((DEALER_CONSUMPTION_AGG_KIND_TRANSLATION.get(key), str(rcd.servicedInfo.get(key, '-'))))
  595. records.append(OrderedDict(dataList))
  596. generate_excel_report(filepath, records)
  597. def manager_export_dealer_info_excel_from_db(filepath, queryDict):
  598. def count_devices(ownerId):
  599. devNoList = [device['devNo'] for device in
  600. Device.get_collection().find({'ownerId': ownerId, 'groupId': {'$ne': ''}}, {'devNo': 1, '_id': 0})]
  601. allCount = len(devNoList)
  602. devDict = Device.get_dev_by_nos(devNoList)
  603. if not devDict:
  604. return 0, 0, 0
  605. onlineCount = 0
  606. for devNo, dev in devDict.items():
  607. if dev["online"]:
  608. onlineCount += 1
  609. offlineCount = allCount - onlineCount
  610. return onlineCount, offlineCount, allCount
  611. logger.error('start manager_export_dealer_info_excel_from_db,filepath=%s,query=%s' % (filepath, queryDict))
  612. mid = queryDict.get("mid")
  613. agentId = queryDict.get('agentId', None)
  614. searchKey = queryDict.get('searchKey', None)
  615. dealers = Dealer.search(searchKey)
  616. agentMap = {str(agent.id): agent.username for agent in Agent.objects(managerId=mid)}
  617. agentIds = agentMap.keys()
  618. if agentId:
  619. dealers = dealers.filter(agentId=agentId)
  620. elif agentIds:
  621. dealers = dealers.filter(agentId__in=agentIds)
  622. dataList = []
  623. for d in dealers:
  624. onlineCount, offlineCount, allCount = count_devices(str(d.id))
  625. now = datetime.datetime.now()
  626. today = now.strftime(Const.DATE_FMT)
  627. monthDay = MONTH_DATE_KEY.format(year=now.year, month=now.month)
  628. rcds = DealerMonthlyStat.get_collection().find({'dealerId': ObjectId(d.id), 'date': monthDay},
  629. {'addedUserCount': 1})
  630. if rcds.count() == 0:
  631. userCountAddedThisMonth = 0
  632. else:
  633. info = rcds[0]
  634. userCountAddedThisMonth = info.get('addedUserCount', 0)
  635. lis = [
  636. (u'商家名称', d.nickname),
  637. (u'联系方式', d.username),
  638. (u'注册时间', d.dateTimeAdded.strftime(Const.DATETIME_FMT)),
  639. (u'用户数', d.userCount),
  640. (u'本月添加用户数', userCountAddedThisMonth),
  641. (u'设备总数', allCount),
  642. (u'在线设备数', onlineCount),
  643. (u'离线设备数', offlineCount),
  644. (u'商家余额', str(d.total_balance)),
  645. ]
  646. dataList.append(OrderedDict(lis))
  647. generate_excel_report(filepath, dataList)