manyi/script/spider/shankailaidianAnalyze.py

# -*- coding: utf-8 -*-
# !/usr/bin/env python

import os, sys,time,datetime
import urllib, urllib2, sys
import requests
from mongoengine import register_connection, PointField, DynamicDocument, StringField
import simplejson as json
import ssl
import xlrd
from xlrd import xldate_as_tuple
from collections import OrderedDict
PROJECT_ROOT = os.path.join(os.path.abspath(os.path.split(os.path.realpath(__file__))[0] + "/.."), '..')
sys.path.insert(0, PROJECT_ROOT)

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "configs.testing")

from script.base import init_env

init_env(interactive = False)

from apps.web.core.db import Searchable
from apps.web.core.utils import generate_excel_report

register_connection(alias = 'spider',
                    name = 'spider',
                    host = '211.159.224.10',
                    port = 27119,
                    username = 'service',
                    password = 'oOzjoQcO5DyyiN97AY0NpzJ6vztjNpx5',
                    authentication_source = 'admin')

class shankailaidianDevice(Searchable):
    meta = {
        'collection': 'shankailaidian_device',
        'db_alias': 'spider',
        'unique_together': {'vStationNo'}
    }



records = []
owerDict = {}
ii = 0
for rcd in shankailaidianDevice.get_collection().find({}):
    ii += 1
    if ii % 100 == 0:
        print ii
    
    addYear = rcd['tAddTime'].split('-')[0]
    
    if rcd['vHelpMobile'] in owerDict:
        owerDict[rcd['vHelpMobile']]['count'] += 1
        owerDict[rcd['vHelpMobile']]['income'] += float(rcd['dTotalIncome'])
        if owerDict[rcd['vHelpMobile']].has_key(addYear):
            owerDict[rcd['vHelpMobile']][addYear] += 1
        else:
            owerDict[rcd['vHelpMobile']][addYear] = 1
        
    else:
        owerDict[rcd['vHelpMobile']] = {'count':1,'income':float(rcd['dTotalIncome']),addYear:1}


for mobile,info in owerDict.items():
    ii += 1
    if ii % 100 == 0:
        print ii
    dataList = [
        (u'电话', mobile),
        (u'数目', info['count']),
        (u'总收入', info['income']),
        (u'分配', info['income']*0.2),
        (u'单台设备均分', info['income']*0.2/info['count']),
        (u'2022', info.get('2022',0)),
        (u'2021', info.get('2021',0)),
        (u'2020', info.get('2020',0)),
        (u'2019', info.get('2019',0)),
        (u'2018', info.get('2018',0)),
    ]
    

    records.append(OrderedDict(dataList))
generate_excel_report('F:/shankailaidian_tongji3.xlsx', records,True)
print('OK')