from openpyxl import load_workbook
import pymysql

from oe_calendar.GoogleCal import GoogleCal


def _read(filepath):
    load_wb = load_workbook(filepath, data_only=True)
    return load_wb


def regist_schedule_tmp(filepath):
    _excel2Dic(filepath)


def remove_schedule_tmp():
    connect = pymysql.connect(host='52ch.kr', user='root', password='jwchjwch',
                              db='message', charset='utf8')

    try:
        with connect.cursor() as cursor:
            cur = connect.cursor()
            sql = "truncate schedule_tmp"

            print(sql)
            cur.execute(sql)
            connect.commit()
    finally:
        connect.close()


# 엑셀파일에서 읽어서, schedule_tmp 테이블로 입력
def _excel2Dic(filepath):
    load_wb = _read(filepath)
    dic_list = []

    load_ws = load_wb['Sheet1']
    rows = load_ws['A2':'F300']

    for row in rows:
        if row[0].value is not None:
            _d = row[0].value  # 날짜: 2020-01-01
            _t = row[1].value  # 시간: 04:30
            _n = row[2].value  # 이름: 김현래
            _c = str(row[3].value) if row[2].value is not None else ''  # calenar_seq
            _m = str(row[4].value) if row[3].value is not None else ''  # message_template_seq
            dic = {'date': _d, 'time': _t, 'name': _n, 'cal_id': _c, 'msg_id': _m}
            dic_list.append(dic)
        else:
            break
    # print(dic_list)
    for item in dic_list:

        if item['name'] is not None:
            connect = pymysql.connect(host='52ch.kr', user='root', password='jwchjwch',
                                      db='message', charset='utf8')
            try:
                with connect.cursor() as cursor:
                    cur = connect.cursor()
                    sql = "insert into schedule_tmp(sch_date, sch_time, name, cal_id, msg_id) value (\'" + str(item[
                        'date']) + "\',\'" + item['time'] + "\',\'" + item['name'] + "\',\'" + item['cal_id'] + "\',\'" + item['msg_id'] + "\')"
                    print(sql)
                    cur.execute(sql)
                    connect.commit()


            finally:
                connect.close()


# schedule_tmp 데이터로 예약문자에 등록하기
def booking():
    connect = pymysql.connect(host='52ch.kr', user='root', password='jwchjwch',
                              db='message', charset='utf8')

    try:
        with connect.cursor() as cursor:
            cur = connect.cursor()
            sql = ""
            sql += "insert into message_schedule(calendar_seq, schedule_date, schedule_content, notifications) "
            sql += "select "
            sql += "    s.cal_id as calendar_seq, concat(s.sch_date, ' ', s.sch_time) as schedule_date "
            sql += "    , t.`contents` as schedule_content "
            sql += "    , json_object(\'msg\', json_array(json_object(\'name\',m.name, \'grade\', m.grade, \'mobile\', m.mobile, \'user_id\', m.user_id))) as notifications "
            sql += "from schedule_tmp s "
            sql += "inner join member m ON m.user_id = s.name "
            sql += "inner join message_template t ON t.seq = s.msg_id "
            sql += "where s.sch_date >= CURRENT_DATE()"

            print(sql)
            cur.execute(sql)
            connect.commit()
    finally:
        connect.close()


def reg_cal():
    connect = pymysql.connect(host='52ch.kr', user='root', password='jwchjwch',
                              db='message', charset='utf8')

    try:
        with connect.cursor() as cursor:
            cur = connect.cursor()
            sql = ""
            sql += "select "
            sql += "    s.sch_date, m.name, m.mobile, m.grade, m.email "
            sql += "from schedule_tmp s "
            sql += "inner join member m ON m.user_id = s.name "
            sql += "where s.sch_date >= CURRENT_DATE()"

            print(sql)
            cur.execute(sql)
            # connect.commit()
            rows = cur.fetchall()

            for row in rows:
                print("{0} {1} {2} {3} {4}".format(row[0], row[1], row[2], row[3], row[4]))

                sch_date = str(row[0])
                name = str(row[1])
                mobile = str(row[2])
                grade = str(row[3])
                email = str(row[4]) if '' != row[4] else None
                name = name + grade + '님'
                google_cal = GoogleCal()
                google_cal.regist_schedule(sch_date, name, mobile, email)

    finally:
        connect.close()


if __name__ == '__main__':
    filepath = "C:\\Users\\user\\onedrive\\2021.03.27~2021.04.30.xlsx"
    regist_schedule_tmp(filepath)
    booking()
    reg_cal()

    # remove_schedule_tmp()
