abuyunRecharge/statistics.py

# -*- coding:utf-8 -*-

# @Time    : 2018/5/22 1:25 PM

# @Author  : Swing

import pymongo
import time
import re
import email_util

# MONGO_HOST = 'mongodb://logdb:logdb@dds-uf6da0fedc9881d41450-pub.mongodb.rds.aliyuncs.com:3717,dds-uf6da0fedc9881d42459-pub.mongodb.rds.aliyuncs.com:3717/logdb?replicaSet=mgset-12835903'
MONGO_HOST = 'mongodb://logdb:logdb@dds-uf6da0fedc9881d41154-pub.mongodb.rds.aliyuncs.com:3717,dds-uf6da0fedc9881d42456-pub.mongodb.rds.aliyuncs.com:3717/logdb?replicaSet=mgset-12835903'

def daily_statistic():

    now_date: time = time.localtime(time.time())
    year = now_date.tm_year
    month = now_date.tm_mon
    day = now_date.tm_mday

    month_str = str(year) + '-'
    if month < 10:
        month_str += '0'
    month_str += str(month)
    day_str = month_str + '-'
    if day < 10:
        day_str += '0'
    day_str += str(day)

    date_reg = re.compile(day_str)
    area_reg = re.compile(r'(^[5][5-9][^\d])|(^[6][0-5][^\d])')

    city_residential_query_args = {'date': date_reg, 'area': area_reg, 'property_type': '普通住宅', 'decoration': {'$in': ['精装修', '豪华装修']}}
    city_department_query_args = {'date': date_reg, 'area': area_reg, 'property_type': '公寓', 'decoration': {'$in': ['精装修', '豪华装修']}}

    part_residential_query_args = {'date': date_reg, 'area': area_reg, 'property_type': '普通住宅', 'decoration': {'$in': ['精装修', '豪华装修']}}
    part_department_query_args = {'date': date_reg, 'area': area_reg, 'property_type': '公寓', 'decoration': {'$in': ['精装修', '豪华装修']}}

    city_residential_query_args_sold = {'date': date_reg, 'property_type': '普通住宅'}
    city_department_query_args_sold = {'date': date_reg, 'property_type': '公寓'}

    part_residential_query_args_sold = {'date': date_reg, 'property_type': '普通住宅'}
    part_department_query_args_sold = {'date': date_reg, 'property_type': '公寓'}

    task_args = [
        city_residential_query_args,
        city_department_query_args,
        part_residential_query_args,
        part_department_query_args,
        city_residential_query_args_sold,
        city_department_query_args_sold,
        part_residential_query_args_sold,
        part_department_query_args_sold
                 ]

    city_residential_average_rental = 0
    city_department_average_rental = 0
    part_residential_average_rental = 0
    part_department_average_rental = 0
    city_residential_average_sold = 0
    city_department_average_sold = 0
    part_residential_average_sold = 0
    part_department_average_sold = 0

    db = __open_db()
    for i in range(len(task_args)):
        col_name = ''
        if i in [0, 1]:
            col_name = 'rental_house'
        elif i in [2, 3]:
            col_name = 'sjk_rental_house'
        elif i in [4, 5]:
            col_name = 'nb_resold_house'
        elif i in [6, 7]:
            col_name = 'sjk_resold_house'

        data_list = __query_data(col_name, task_args[i], db)
        if data_list.count() == 0:
            continue
        average = __calculate_average_price(data_list)
        if i == 0:
            city_residential_average_rental = average
        elif i == 1:
            city_department_average_rental = average
        elif i == 2:
            part_residential_average_rental = average
        elif i == 3:
            part_department_average_rental = average
        elif i == 4:
            city_residential_average_sold = average
        elif i == 5:
            city_department_average_sold = average
        elif i == 6:
            part_residential_average_sold = average
        elif i == 7:
            part_department_average_sold = average

    # 住宅出租均价
    rental_residential_filter_args = {
        'day': day_str,
        'property_type': '1'
    }
    rental_residential_update_args = {
        'city_price': city_residential_average_rental if city_residential_average_rental > 0 else 2290,
        'part_price': part_residential_average_rental if part_residential_average_rental > 0 else 3100,
        'month': month_str,
        'update_date': time.strftime('%Y-%m-%d %H:%M:%S'),
        'house_id': '109'
    }

    __update_data(db, 'rental_statistics', rental_residential_filter_args, rental_residential_update_args)

    # 公寓出租均价
    rental_department_filter_args = {
        'day': day_str,
        'property_type': '2'
    }
    rental_department_update_args = {
        'city_price': city_department_average_rental if city_department_average_rental > 0 else 2330,
        'part_price': part_department_average_rental if part_department_average_rental > 0 else 2850,
        'month': month_str,
        'update_date': time.strftime('%Y-%m-%d %H:%M:%S'),
        'house_id': '109'
    }

    __update_data(db, 'rental_statistics', rental_department_filter_args, rental_department_update_args)

    # 住宅出售均价
    sold_residential_filter_args = {
        'day': day_str,
        'property_type': '1'
    }
    sold_residential_update_args = {
        'city_price': city_residential_average_sold if city_residential_average_sold > 0 else 19920,
        'part_price': part_residential_average_sold if part_residential_average_sold > 0 else 24450,
        'month': month_str,
        'update_date': time.strftime('%Y-%m-%d %H:%M:%S'),
        'house_id': '109'
    }

    __update_data(db, 'sold_statistics', sold_residential_filter_args, sold_residential_update_args)

    # 公寓出售均价
    sold_department_filter_args = {
        'day': day_str,
        'property_type': '2'
    }
    sold_department_update_args = {
        'city_price': city_department_average_sold if city_department_average_sold > 0 else 15650,
        'part_price': part_department_average_sold if part_department_average_sold > 0 else 21560,
        'month': month_str,
        'update_date': time.strftime('%Y-%m-%d %H:%M:%S'),
        'house_id': '109'
    }

    __update_data(db, 'sold_statistics', sold_department_filter_args, sold_department_update_args)

    # 关闭数据库连接
    db.client.close()

    # 每日统计数据 通知谷晓晨
    statistic_data = '宁波公寓租金: ' + str(city_department_average_rental) + '\n'
    statistic_data += ('宁波住宅租金: ' + str(city_residential_average_rental) + '\n')
    statistic_data += ('三江口公寓租金: ' + str(part_department_average_rental) + '\n')
    statistic_data += ('三江口住宅租金: ' + str(part_residential_average_rental) + '\n')
    statistic_data += ('宁波公寓售价: ' + str(city_department_average_sold) + '\n')
    statistic_data += ('宁波住宅售价: ' + str(city_residential_average_sold) + '\n')
    statistic_data += ('三江口公寓售价: ' + str(part_department_average_sold) + '\n')
    statistic_data += ('三江口住宅售价: ' + str(part_residential_average_sold) + '\n')
    email_util.send_email('每日统计数据', statistic_data, ['guxc@elab-plus.com', 'liuhx@elab-plus.com', 'zhaojh@elab-plus.com'])


# 计算平均值
def __calculate_average_price(data_list):
    price_list = []
    for data in data_list:
        try:
            date_str = re.search(r'[0-9]{4,}', data['price']).group()
            if date_str:
                  price_list.append(float(date_str))

        except Exception as err:
            pass
    sum = 0
    for price in price_list:
        sum += price
    if len(price_list) == 0:
        return 0
    average = int(round(sum / len(price_list)))

    return average


# db 操作
def __query_data(collection, query_args, db):
    coll = db[collection]
    data_list = coll.find(query_args).sort('data', -1)
    return data_list


# 更新数据
def __update_data(db, collection, filter, update):
    coll = db[collection]
    coll.update_one(filter, {'$set': update}, upsert=True)


# 打开数据库
def __open_db():
    # client = pymongo.MongoClient('101.132.106.154', authSource='logdb', authMechanism='SCRAM-SHA-1')
    client = pymongo.MongoClient(MONGO_HOST, authSource='logdb')
    db = client['logdb']
    return db

# list = [
#     '2018-05-01',
#     '2018-05-02',
#     '2018-05-03',
#     '2018-05-04',
#     '2018-05-05',
#     '2018-05-06',
#     '2018-05-07',
#     '2018-05-08',
#     '2018-05-09',
#     '2018-05-10',
#     '2018-05-11',
#     '2018-05-12',
#     '2018-05-13',
#     '2018-05-14',
#     '2018-05-15',
#     '2018-05-16',
#     '2018-05-17',
#     '2018-05-18',
#     '2018-05-19',
#     '2018-05-20',
#     '2018-05-21',
#     '2018-05-22'
# ]
#
# for i in list:
# daily_statistic()