import pandas as pd
import pymysql
import datetime


def con_sql(db,sql):
    cursor = db.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()[0]
    return result


def get_ctr():
    db = pymysql.connect(host='172.16.40.158', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
    yesterday = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y-%m-%d")
    print(yesterday)
    sql = "select count(*) from data_feed_exposure_precise where stat_date = '{}' and cid_type = 'diary'".format(yesterday)
    exposures = con_sql(db, sql)
    sql = "select count(*) from data_feed_click where stat_date = '{}' and cid_type = 'diary'".format(yesterday)
    clicks = con_sql(db, sql)
    db.close()
    print(exposures)
    print(clicks)
    print(clicks/exposures)


def all_con_sql(db,sql):
    cursor = db.cursor()
    cursor.execute(sql)
    result = list(cursor.fetchall())
    return result


def get_feed_ctr():
    db = pymysql.connect(host='172.16.40.158', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test')
    sql = "select date_str,diary_exp,diary_click,post_exp,post_click,qa_exp,qa_click from feed_exp_click"
    df = pd.DataFrame(all_con_sql(db,sql))
    db.close()
    print(df.head(2))

    df = df.rename(columns={0: "date_str",1:"diary_exp",2:"diary_click",3:"post_exp",4:"post_click",
                            5:"qa_exp",6:"qa_click"})
    for i in ["date_str","diary_exp","diary_click","post_exp","post_click","qa_exp","qa_click"]:
        df[i] = df[i].astype("int")
    df["total_exp"] = df["diary_exp"] + df["post_exp"] + df["qa_exp"]
    df["total_click"] = df["diary_click"] + df["post_click"] + df["qa_click"]
    df["total_ctr"] = df["total_click"]/df["total_exp"]
    df["diary_ctr"] = df["diary_click"]/df["diary_exp"]
    df["post_ctr"] = df["post_click"]/df["post_exp"]
    df["qa_ctr"] = df["qa_click"]/df["qa_exp"]
    print(df.head(2))
    df.to_csv("/home/gmuser/ctr.csv",index = False)


if __name__ == "__main__":
    get_feed_ctr()