精炼mysql的数据，并且存放到redis中。

# -*- coding:utf8 -*-
'''
Created on 2016年11月29日

@author: qiancheng
'''
import MySQLdb
from email.mime.text import MIMEText
from email.header import Header
import smtplib
import redis
import datetime
from decimal import Decimal



class do_redis():
    def __init__(self,_redishost,_redisport,_redispasswd,_db):    
        do_redis=redis.ConnectionPool(host=_redishost,port=_redisport,password=_redispasswd,db=_db)
        self.redis_cli=redis.Redis(connection_pool=do_redis)
        
    def set_redis(self,_hash_name,_hash_keys):
        self.redis_cli.hmset(_hash_name, _hash_keys)
    
    def get_redis(self,_hash_name):
        print self.redis_cli.hgetall(_hash_name)
        

class mail_to:
    def __init__(self,_message):
        self.message=_message
        mail_host="115.231.109.86"  #smtp-server
        mail_user="qiancheng"    #username
        mail_pass="xxxx"  #password
        sender = 'qiancheng@showjoy.com'
        receivers = ['xxxx@showjoy.com'] 
        message = MIMEText(self.message,'plain','utf-8')
        message['From'] = Header("qiancheng@showjoy.com<qiancheng@showjoy.com>")
        message['To'] =  Header("xxxx@showjoy.com<xxxx@showjoy.com>")
        subject = '达人店交易信息统计'
        message['Subject'] = Header(subject,'utf-8')
        try:
            smtpObj=smtplib.SMTP()
            smtpObj.connect(mail_host)
            smtpObj.login(mail_user,mail_pass)  
            smtpObj.sendmail(sender, receivers, message.as_string())
            smtpObj.close()
        except smtplib.SMTPException:
            print("Error: 无法发送邮件")


shopdbinfo={'host':'xxxxx','user':'root','port':22306,'passwd':'xxxx','db':'shop'}
tradedbinfo={'host':'xxxxx','user':'root','port':10306,'passwd':'xxxx','db':'trade'}


class do_db(object):
    def __init__(self,_host,_port,_user,_passwd,_db):
        self.host=_host
        self.port=_port
        self.passwd=_passwd
        self.dbf=_db
        self.user=_user
        self.db=MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,port=self.port,db=self.dbf)
        self.cursor=self.db.cursor(MySQLdb.cursors.DictCursor)

    def execsql(self,_sql):
        self.sql=_sql
        self.cursor.execute(self.sql)
        return self.cursor.fetchoneDict()
    
    def closedb(self):
        self.cursor.close()
        self.db.close()

def get_order_monitor():
    

    now=datetimes
    day=str(now.strftime('%Y-%m-%d'))+'%'
    _times=day
    
    #计算前一日的统计信息sql语句
    '''sql_recommission='SELECT shop_commission_order_r.ORDER_NUMBER recommission_resql from shop.shop_commission_order_r,shop.shop_commission \
    where SHOP_COMMISSION_ID = ID and shop_commission.IS_DELETE = 0 and \
    shop_commission_order_r.gmt_modified BETWEEN DATE_SUB(NOW(),INTERVAL 1 day) and NOW() \
    group by SHOP_ID,COMMISSION,COMMISSION_TYPE,shop_commission_order_r.GMT_CREATE,ORDER_NUMBER,SKU_ID \
    having count(*)>1'
    '''
    sql_oneday_recruit='select count(DISTINCT sr.recruit_shop_id)*360 one_day_startshop \
    from shop.shop_commission_recruit_r as sr \
    where sr.gmt_modified like \''+_times+'\' and sr.is_delete = 0'
    sql_oneday_recruit_comm='select sum(sc.COMMISSION) one_day_startshop_commisson \
    from shop.shop_commission as sc \
    where (sc.COMMISSION_TYPE = 201 or sc.COMMISSION_TYPE = 301) \
    and sc.gmt_modified like \''+_times+'\' and  sc.is_delete = 0'
    sql_oneday_order='SELECT SUM(tpr.actual_price) one_day_order FROM trade.trade_pay_record tpr \
    LEFT JOIN trade.trade_order_pay_record_r topr ON tpr.id = topr.pay_record_id \
    LEFT JOIN trade.trade_order tr ON topr.order_number = tr.order_number \
    WHERE tpr.paid_time like \''+_times+'\' AND tr.source_type = 1 and tr.IS_CANCEL = 0'
    sql_oneday_order_comm='select sum(sc.COMMISSION) one_day_order_commission from shop.shop_commission as sc \
    where (sc.COMMISSION_TYPE = 200 or sc.COMMISSION_TYPE = 300 and sc.is_delete = 0) \
    and sc.gmt_modified like \''+_times+'\''
    
    
    
    '''计算一天开店收入'''
    todo=do_db(shopdbinfo['host'],shopdbinfo['port'],shopdbinfo['user'],shopdbinfo['passwd'],shopdbinfo['db'])
    one_day_startshop = todo.execsql(sql_oneday_recruit)['one_day_startshop']
    todo.closedb()
    if one_day_startshop is None:
        one_day_startshop = 0
       
    
    '''计算一天开店发放的收益'''
    todo=do_db(shopdbinfo['host'],shopdbinfo['port'],shopdbinfo['user'],shopdbinfo['passwd'],shopdbinfo['db'])
    one_day_startshop_commisson = todo.execsql(sql_oneday_recruit_comm)['one_day_startshop_commisson']
    todo.closedb()
    if one_day_startshop_commisson is None:
        one_day_startshop_commisson = 0
    
    '''查询重复发放收益'''
    '''todo=do_db(shopdbinfo['host'],shopdbinfo['port'],shopdbinfo['user'],shopdbinfo['passwd'],shopdbinfo['db'])
    recommission_resql = todo.execsql(sql_recommission)
    todo.closedb()
    if recommission_resql is None:
        recommission_resql = 0
    '''
    '''计算一天订单总额'''
    todo=do_db(tradedbinfo['host'],tradedbinfo['port'],tradedbinfo['user'],tradedbinfo['passwd'],tradedbinfo['db'])
    one_day_order = todo.execsql(sql_oneday_order)['one_day_order']
    todo.closedb()
    if one_day_order is None:
        one_day_order = 0

    '''计算一天订单收益总额'''
    todo=do_db(shopdbinfo['host'],shopdbinfo['port'],shopdbinfo['user'],shopdbinfo['passwd'],shopdbinfo['db'])
    one_day_order_commission = todo.execsql(sql_oneday_order_comm)['one_day_order_commission']
    todo.closedb()
    if one_day_order_commission is None:
        one_day_order_commission = 0
        
    if one_day_startshop != 0:
        recruitpercent=one_day_startshop_commisson/one_day_startshop*100
    else:
        recruitpercent=0
    if one_day_order != 0:
        orderpercent=one_day_order_commission/one_day_order*100
    else:
        orderpercent=0
    '''if recommission_resql != 0:
        recommission_message='一天内有重复发放收益的订单存在，请查询数据库确认！'
    else:
        recommission_message='一天内没有重复发放收益。'
        '''
    '''final_result='%s \n一天开店总金额：%d，一天开店发放收益：%d，收益发放比例：%.2f \n一天达人店订单总额：%d，一天达人店订单发放收益：%d。收益发放比例：%.2f' \
           %(recommission_message,one_day_startshop,one_day_startshop_commisson,recruitpercent,one_day_order,one_day_order_commission,orderpercent)
    #print (str(final_result))'''
    order_monitor_info={'startshop':float(Decimal(str(one_day_startshop)).quantize(Decimal('0.00'))),
                        'startshop_commisson':float(Decimal(str(one_day_startshop_commisson)).quantize(Decimal('0.00'))),
                        'recruitpercent':float(Decimal(str(recruitpercent)).quantize(Decimal('0.00'))),
                        'order':float(Decimal(str(one_day_order)).quantize(Decimal('0.00'))),
                        'order_commission':float(Decimal(str(one_day_order_commission)).quantize(Decimal('0.00'))),
                        'orderpercent':float(Decimal(str(orderpercent)).quantize(Decimal('0.00')))
                        }#算出订单统计的结果并且保留2位小数
    return order_monitor_info
    #mail_to(str(final_result))
    
def set_order_monitor(_order_monitor_info):     
    
    now=datetimes

    key_name=str(now.strftime('%Y-%m-%d'))
    hash_name='order_monitor'#hash的名字 
    test_redis_info={'redishost':'192.168.0.223','redisport':'6380','password':'xxxxx','db':10}#线下的redis信息
    increment_redis_info={'redishost':'xxxx','redisport':'6379','password':'xxxxxx','db':8}#线上的redis信息
    hash_keys={key_name:str(_order_monitor_info)}#key_name就是日期，value就是string化的统计的订单结果
    
    if env=='test':
        r=do_redis(test_redis_info['redishost'],test_redis_info['redisport'],test_redis_info['password'],test_redis_info['db'])
    elif env=='increment':
        r=do_redis(increment_redis_info['redishost'],increment_redis_info['redisport'],increment_redis_info['password'],increment_redis_info['db'])
    else:
        print '请选择正确的环境参数'
        exit()
    r.set_redis(hash_name,hash_keys)
    r.get_redis(hash_name)        
    
if __name__ == '__main__':
    global datas
    global year
    global month
    global day
    global datetimes
    global env
    env='increment'
    datas='all'
    year=int(datetime.datetime.now().strftime('%Y'))
    month=int(datetime.datetime.now().strftime('%m'))
    day=int(datetime.datetime.now().strftime('%d'))
    d1=datetime.datetime(2016,07,01)
    d2=datetime.datetime(year,month,day)
    if datas=='all':
        day_values=int((d2-d1).days)
    elif datas=='one':
        day_values=0
    else:
        print '请选择正确的数据量参数'
        exit()
    for i in range(0,day_values+1):
        datetimes=datetime.date(datetime.date.today().year,datetime.date.today().month,datetime.date.today().day)-datetime.timedelta(i)
        order_monitor_result=get_order_monitor()
        set_order_monitor(order_monitor_result)