from flask import Flask, render_template, request, make_response
import pymysql
import datetime
import pandas as pd
import numpy as np
from io import BytesIO
from urllib.parse import quote
import xlsxwriter



app = Flask(__name__)

# 为了方便路由之前传递变量
class DataStore():
    a = None


selec_data = DataStore()

# 封装SQL 函数
def func(sql, m='r'):
    py = pymysql.connect('127.0.0.1', 'root', '123456', 'office',
                         charset='utf8', cursorclass=pymysql.cursors.DictCursor)
    cursor = py.cursor()
    try:
        cursor.execute(sql)
        if m == 'r':
            data = cursor.fetchall()
        elif m == 'w':
            py.commit()
            data = cursor.rowcount
    except:
        data = False
        py.rollback()
    py.close()
    return data


# 首页
@app.route('/')
def index():
    return render_template('index.html')


# 接受添加的数据，并写入数据库中
@app.route("/adds/", methods=["POST", "GET"])
def adds():
    data = dict(request.form)
    form_time = request.form.get('date_input')
    # print("form_time:{}".format(form_time))
    agent_num = request.form.get('agent_num')
    # print("agent_num:{}".format(agent_num))
    # print("agent_num类型:{}".format(type(agent_num)))
    # print(data)
    form_proj = request.form.get('select_proj')
    #print(form_proj)

    agent_id = func( "select agent_num from outbounds where agent_num='{}' GROUP BY agent_num".format(agent_num))
    agent_id = np.array(agent_id)
    #print("agentid: {}".format(agent_id))
    #print("agentid类型: {}".format(type(agent_id)))
    if agent_id:
        agent_id = str(agent_id[0]["agent_num"])
    else:
        agent_id = 0
    
    db_proj = func("select select_proj from outbounds where agent_num='{}' and select_proj='{}' ".format(agent_num, form_proj))
    db_proj = np.array(db_proj)
    #print("db_proj: {}".format(db_proj))
    #print("db_proj类型: {}".format(type(db_proj)))
    if db_proj:
        db_proj = str(db_proj[0]["select_proj"])
    else:
        db_proj = 0

    # print("agentid: {}".format(agent_id))
    # print("agentid类型: {}".format(type(agent_id)))
    sql_ins = "insert into outbounds values ('{date_input}','{agent_num}','{agent_name}','{select_proj}','{agent_outcnt}','{agent_anscnt}','{agent_ordercnt}','{agent_memo}')".format(**data)
    sql_upd = "update outbounds set agent_outcnt='{agent_outcnt}', agent_anscnt='{agent_anscnt}', agent_ordercnt='{agent_ordercnt}', agent_memo='{agent_memo}' where agent_num='{agent_num}' and select_proj='{select_proj}'".format(
        **data)
    now_time = datetime.datetime.now().strftime('%Y/%m/%d')
    # print("now_time:{}".format(now_time))
    # print(now_time == form_time)
    # print(agent_id == agent_num)
    # res = func(sql_ins, m='w')
    # if res:
    #     return '<script>alert("添加成功");location.href="/";</script>'
    # else:
    #     return '<script>alert("添加失败");location.href="/";</script>'
    #if form_proj == db_proj:
    if now_time == form_time and agent_id == agent_num and db_proj == form_proj:
        res = func(sql_upd, m='w')
        # print(res)
        if res:
            return '<script>alert("更新成功");location.href="/";</script>'
        else:
            return '<script>alert("更新失败");location.href="/";</script>'
    else:
        res = func(sql_ins, m='w')
        # print(res)
        if res:
            return '<script>alert("添加成功");location.href="/";</script>'
        else:
            return '<script>alert("添加失败");location.href="/";</script>'



# 转到查询页面
@app.route('/select', methods=["POST", "GET"])
def selec():
    startTime = request.args.get('starttime')
    #endTime = request.args.get('endtime')
    agentId = request.args.get('agentnum')
    agentName = request.args.get('agentname')
    form_selec_proj = request.args.get('select_proj')
    selec_sql = "select * from outbounds where date_input='{}' and agent_num='{}' and agent_name='{}' and select_proj='{}'".format(startTime, agentId, agentName,form_selec_proj)
    data = func(selec_sql)
    selec_data.a = data
    #print(data)
    return render_template('select.html', userlist=data)

# 导出数据文件
@app.route('/download', methods=["POST", "GET"])
def download():
    print(selec_data.a)
    new_data = selec_data.a
    output = BytesIO()
    # 写excel
    workbook = xlsxwriter.Workbook(output)  # 先创建一个book，直接写到io中

    sheet = workbook.add_worksheet('sheet1')
    fileds = ['日期', '工号', '姓名', '主推业务', '外呼量', '接通量', '主推成单量', '备注']

    # 写入数据到A1一列
    sheet.write_row('A1', fileds)

    # 遍历有多少行数据
    for i in range(len(new_data)):
        # 遍历有多少列数据
        for x in range(len(fileds)):
            key = [key for key in new_data[i].keys()]
            sheet.write(i + 1, x, new_data[i][key[x]])
            print('当前行：{}  当前列：{}  数据：{}'.format(str(i), str(x), new_data[i][key[x]]))
    workbook.close()  # 需要关闭
    output.seek(0)  # 找到流的起始位置
    resp = make_response(output.getvalue())
    basename = '坐席数据.xlsx'

    # 转码，支持中文名称
    resp.headers["Content-Disposition"] = "attachment; filename*=UTF-8''{utf_filename}".format(
        utf_filename=quote(basename.encode('utf-8'))
    )

    resp.headers['Content-Type'] = 'application/x-xlsx'
    return resp

# 转到修改业务页面
# @app.route('/select', methods=["POST", "GET"])
# def selec():
#     startTime = request.args.get('starttime')
#     #endTime = request.args.get('endtime')
#     agentId = request.args.get('agentnum')
#     agentName = request.args.get('agentname')
#     form_selec_proj = request.args.get('select_proj')
#     selec_sql = "select * from outbounds where date_input='{}' and agent_num='{}' and agent_name='{}' and select_proj='{}'".format(
#         startTime, agentId, agentName, form_selec_proj)
#     data = func(selec_sql)
#     selec_data.a = data
#     #print(data)
#     return render_template('select.html', userlist=data)
    




# 运行
if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port='5000')