# -*- coding: UTF-8 -*-
import pymysql
import datetime
import pandas as pd

DIRECTORY_PATH = "/data2/ffm/"


def get_data():
    conn2db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='eagle')
    cursor = conn2db.cursor()
    sql = "select device_id from eagle.ffm_diary_queue where device_id regexp '[5|6]$'"
    cursor.execute(sql)
    result = cursor.fetchall()
    df = pd.DataFrame(list(result))
    df.to_csv("/data2/ffm/10.3-10.9device_id.csv", index=None)
    device_id = tuple(df[0].values.tolist())
    cursor.close()
    return device_id


def ctr():
    device_id = get_data()
    db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
    cursor = db.cursor()
    date = []
    exp_list = []
    click_list = []
    rate_list = []
    a = "2018-10-0"
    for i in range(3,10):
        date.append(a+str(i))
        sql_exp = "select count(cid) from data_feed_exposure " \
                  "where cid_type = 'diary' and stat_date= '{}' and " \
                  "device_id in {}".format(a+str(i),device_id)
        cursor.execute(sql_exp)
        exp = cursor.fetchone()[0]
        exp_list.append(exp)
        sql_click = "select count(cid) from data_feed_click " \
                  "where cid_type = 'diary' and stat_date= '{}' and " \
                  "device_id in {}".format(a + str(i), device_id)
        cursor.execute(sql_click)
        click = cursor.fetchone()[0]
        click_list.append(click)
        print(a+str(i)+"曝光数：" + str(exp))
        print(a + str(i) + "点击数：" + str(click))
        if exp != 0:
            rate = click/exp
            rate_list.append(rate)
            print(a+str(i)+"点击率：" + str(rate))
    db.close()
    df = pd.DataFrame()
    df["date"] = date
    df["exp"] = exp_list
    df["click"] = click_list
    df["ctr"] = rate_list
    print(df)
    df.to_csv(DIRECTORY_PATH+"10.3-10.9ctr.csv",index=None)


if __name__ == "__main__":
    ctr()

