#-*-coding:utf-8-*-
import datetime, xlwt, os,pymysql

import yaml
import os
BASEPATH = os.path.dirname(__file__)


#读取数据库的配置(当前文件夹下的db.default.yaml)
def dbConfig():
    stream = file(os.path.join(BASEPATH,'db.default.yaml'), 'r')
    dicts = yaml.load(stream)
    return dicts

# 获取当前日期做文件名
time = datetime.datetime.now().strftime('%Y-%m-%d')

# 获取execl保存的路径
file_path = os.path.abspath(os.path.join(os.path.dirname("__file__"))) + r"\execl\%s.xls" % time
# print file_path


def sava_execl(file_path):

   # 获取前七天的时间
    T1 = datetime.datetime.now().strftime('%Y-%m-%d')
    T2 = (datetime.datetime.now() - datetime.timedelta(days=1)).strftime('%Y-%m-%d')
    T3 = (datetime.datetime.now() - datetime.timedelta(days=2)).strftime('%Y-%m-%d')
    T4 = (datetime.datetime.now() - datetime.timedelta(days=3)).strftime('%Y-%m-%d')
    T5 = (datetime.datetime.now() - datetime.timedelta(days=4)).strftime('%Y-%m-%d')
    T6 = (datetime.datetime.now() - datetime.timedelta(days=5)).strftime('%Y-%m-%d')
    T7 = (datetime.datetime.now() - datetime.timedelta(days=6)).strftime('%Y-%m-%d')

    try:
        # 链接数据库
        option = dbConfig()['table_name']  # 获取连接的配置
        host = option['host']
        user = option['user']
        passwd = option['passwd']
        db = option['db']
        conn = pymysql.connect(host=host, user=user, passwd=passwd, db=db, charset="utf8")
        cur = conn.cursor()

        # 读取数据模糊查询并排序
        sql = """select * from cxf_tracking where datetime like '{}%' 
                 or datetime like '{}%' or datetime like '{}%' 
                 or datetime like '{}%' or datetime like '{}%'
                 or datetime like '{}%' or datetime like '{}%'
                 order by datetime   
        """.format(T1, T2, T3, T4, T5, T6, T7)
        count = cur.execute(sql)  # 查看前一个星期发布新闻的条数
        fields = cur.description
        results = cur.fetchall()
        print results,
         # results = results[:len(results)]

       # print '有', len(results), '条数据要求推送',results
       conn.commit()
       cur.close()




       # 写入execl表格
       workbook = xlwt.Workbook()
       sheet = workbook.add_sheet('table_cxf_tracking', cell_overwrite_ok=True)

       # 写上字段信息
       for field in range(0, len(fields)):
           sheet.write(0, field, fields[field][0])
           # 在表格第一行写入mysql中字段的信息
           # print field,fields[field][0],

       # 获取并写入数据段信息
       for row in range(1, len(results) + 1):
           for col in range(0, len(fields)):
               if (results[row - 1][col]) is not None:
                   sheet.write(row, col, u'%s' % results[row - 1][col])

       try:
           # 保存execl
           workbook.save(r'{}'.format(file_path))
       except Exception as e:
           print u'已存在', e
           pass
       return

   except Exception as e:
       print e
       pass

sava_execl(file_path)