find_uncharged_and_unbind_dev.py 5.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173
  1. # -*- coding: utf-8 -*-
  2. # !/usr/bin/env python
  3. import os, sys
  4. from collections import OrderedDict
  5. import datetime
  6. PROJECT_ROOT = os.path.join(os.path.abspath(os.path.split(os.path.realpath(__file__))[0] + "/.."), '..')
  7. sys.path.insert(0, PROJECT_ROOT)
  8. os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'configs.production')
  9. from script.base import init_env
  10. init_env(interactive = False)
  11. from apps.web.device.models import Device
  12. from apps.web.core.utils import generate_excel_report
  13. from apilib.utils_datetime import to_datetime, first_day_datetime_of_month
  14. from apps.web.user.models import ConsumeRecord
  15. from apps.web.dealer.models import Dealer
  16. months = [5]
  17. def print_uncharged_dev(startTime, endTime):
  18. dealerDict = {}
  19. devObjs = Device.objects.filter(simExpireDate__gte = startTime,
  20. simExpireDate__lte = endTime,
  21. simStatus = 'updated',
  22. ownerId__ne = None)
  23. for devObj in devObjs: # type: Device
  24. if devObj.logicalCode.startswith('B'):
  25. print('device<{}> is bt device.'.format(devObj.logicalCode))
  26. continue
  27. ownerId = devObj.ownerId
  28. if not ownerId:
  29. continue
  30. if ownerId not in dealerDict:
  31. dealer = Dealer.objects.get(id = ownerId)
  32. dealerDict[ownerId] = {
  33. 'username': dealer.username,
  34. 'nickname': dealer.nickname,
  35. 'count': 1,
  36. 'devTypeDict': {
  37. devObj.devType['name']: 1
  38. },
  39. 'devNos': [devObj.devNo]
  40. }
  41. continue
  42. dealerDict[ownerId]['count'] += 1
  43. dealerDict[ownerId]['devNos'].append(devObj.devNo)
  44. if not dealerDict[ownerId]['devTypeDict'].has_key(devObj.devType['name']):
  45. dealerDict[ownerId]['devTypeDict'][devObj.devType['name']] = 1
  46. else:
  47. dealerDict[ownerId]['devTypeDict'][devObj.devType['name']] += 1
  48. dealerList = dealerDict.values()
  49. def cmp_by_count(x, y):
  50. if x['count'] < y['count']:
  51. return -1
  52. elif x['count'] > y['count']:
  53. return 1
  54. return 0
  55. dealerList = sorted(dealerList, cmp_by_count)
  56. allCount = 0
  57. records = []
  58. for info in dealerList:
  59. tempList = [u'%s的设备有%s台' % (k, v) for k, v in info['devTypeDict'].items()]
  60. print info['username'], info['nickname'], info['count'], ','.join(tempList), info['devNos']
  61. dataList = [
  62. (u'经销商账号', info['username']),
  63. (u'经销商昵称', info['nickname']),
  64. (u'数量', info['count']),
  65. (u'设备类型分布', ','.join(tempList)),
  66. (u'过期设备列表', ','.join(info['devNos'])),
  67. (u'拜访记录', ''),
  68. (u'备注', '')
  69. ]
  70. records.append(OrderedDict(dataList))
  71. allCount += info['count']
  72. generate_excel_report(u'过期未充值.xlsx', records, localSave = True)
  73. print u'未充值的设备合计:', allCount
  74. def print_unregister_dev(startTime, endTime):
  75. dealerDict = {}
  76. devObjs = Device.get_collection().find({'ownerId': '', 'registerLog.operation': 'unregister'},
  77. {'registerLog': 1, 'devNo': 1})
  78. for devObj in devObjs:
  79. operLog = devObj['registerLog'][-1]
  80. ownerId = str(operLog['ownerId'])
  81. if not (operLog['dateTimeAdded'] >= startTime and operLog['dateTimeAdded'] <= endTime):
  82. continue
  83. if not dealerDict.has_key(ownerId):
  84. dealer = Dealer.objects.get(id = ownerId)
  85. dealerDict[ownerId] = {
  86. 'id': ownerId,
  87. 'username': dealer.username,
  88. 'nickname': dealer.nickname,
  89. 'count': 1,
  90. 'devNos': [devObj['devNo']]
  91. }
  92. continue
  93. dealerDict[ownerId]['count'] += 1
  94. dealerDict[ownerId]['devNos'].append(devObj['devNo'])
  95. dealerList = dealerDict.values()
  96. def cmp_by_count(x, y):
  97. if x['count'] < y['count']:
  98. return -1
  99. elif x['count'] > y['count']:
  100. return 1
  101. return 0
  102. dealerList = sorted(dealerList, cmp_by_count)
  103. allCount = 0
  104. records = []
  105. for info in dealerList:
  106. rcds = ConsumeRecord.get_collection().find({'devNo': {'$in': info['devNos']}}, {'devType': 1})
  107. devTypeNames = [rcd['devType'] for rcd in rcds[0:10]]
  108. devTypeNames = list(set(devTypeNames))
  109. print info['username'], info['nickname'], info['count'], ','.join(devTypeNames), info['id'], info['devNos']
  110. allCount += info['count']
  111. dataList = [
  112. (u'经销商账号', info['username']),
  113. (u'经销商昵称', info['nickname']),
  114. (u'数量', info['count']),
  115. (u'设备类型分布', ','.join(devTypeNames)),
  116. (u'经销商ID', info['id']),
  117. (u'过期设备列表', ','.join(info['devNos'])),
  118. (u'拜访记录', ''),
  119. (u'备注', '')
  120. ]
  121. records.append(OrderedDict(dataList))
  122. generate_excel_report(u'解绑定设备.xlsx', records, localSave = True)
  123. print u'解绑的设备合计:', allCount
  124. for month in months:
  125. startTime = to_datetime('2020-%s-01 00:00:00' % month)
  126. timeTemp = startTime + datetime.timedelta(hours = 24 * 40)
  127. endTime = first_day_datetime_of_month(timeTemp)
  128. print u'如下是到期没有充值的名单'
  129. print_uncharged_dev(startTime, endTime)
  130. print '--------------------------------------------------------------------------------------------------------'
  131. print u'如下是解绑设备的名单'
  132. print_unregister_dev(startTime, endTime)
  133. print 'finished.'