# -*- coding:UTF-8 -*-
# @Time  : 2020/11/13 11:08
# @File  : daily_search_word_count.py
# @email : litao@igengmei.com
# @author : litao

# coding=utf-8
import hashlib

import pymysql
# from elasticsearch import Elasticsearch
import datetime
# from maintenance.func_send_email_with_file import send_file_email
# import zipfile

# es = Elasticsearch([
#     {
#         'host': '172.16.31.17',
#         'port': 9200,
#     }, {
#         'host': '172.16.31.11',
#         'port': 9200,
#     }])

if __name__ == "__main__":
    tag_names_list = []
    tag_names_list_week = []
    all_data_day = []
    all_data_week = []
    db_zhengxing_eagle = pymysql.connect(host="172.16.30.136", port=3306, user="doris",
                                         password="o5gbA27hXHHm",
                                         db="doris_prod",
                                         charset='utf8',
                                         cursorclass=pymysql.cursors.DictCursor)
    zhengxing_cursor = db_zhengxing_eagle.cursor()

    for count_date in range(0,3):
        tag_names_list_week = []
        date = datetime.datetime.now().date() - datetime.timedelta(days=count_date)
        print(str(date))
        yesterday_date = datetime.datetime.now().date() - datetime.timedelta(days=count_date+1)
        # date_str = yesterday_date.strftime("%Y%m%d")
        sql = """select keywords,sum(sorted) as nums,sum(uv) as uvs  from api_search_word where is_delete = 0 and 
        create_time >= "{yesterday_date}" and create_time <= "{date}" group by keywords  order by  nums  desc""".\
            format(yesterday_date=str(yesterday_date),date=str(date))
        print(sql)
        zhengxing_cursor.execute("set names 'UTF8'")
        zhengxing_cursor.execute(sql)
        data = zhengxing_cursor.fetchall()

        for name in list(data):
            word = name.get("keywords", None)
            nums = name.get("nums", 0)
            uv = name.get("uvs", 0)

            tag_names_list_week.append((word, nums, uv,str(yesterday_date)))

        db = pymysql.connect(host='172.16.40.158', port=4000, user='st_user', passwd='aqpuBLYzEV7tML5RPsN1pntUzFy',
                             db='jerry_prod')
        cursor = db.cursor()
        insert_sql = """replace into daily_search_word_count(
                       word, nums, uv,pid,partition_day) VALUES(
                      %s,%s,%s,%s,%s);"""
        insert_list = []
        for count, item in enumerate(tag_names_list_week):
            word, nums, uv, date_str = item
            partition_date = date_str
            pid = hashlib.md5((partition_date+word).encode("utf8")).hexdigest()
            insert_sql = (word, nums, uv, pid,date_str)
            insert_list.append(insert_sql)
            print(insert_sql)
            if count % 100 == 0:
                # cursor.execute("set names 'UTF8'")
                res = cursor.executemany(insert_sql,insert_list)
                db.commit()
                # print(res)
                insert_list = []
        res = cursor.executemany(insert_sql,insert_list)
        db.commit()
        print(res)