import os
import pandas as pd
from openpyxl import load_workbook
from sqlalchemy import create_engine
import xlsxwriter
from datetime import datetime


def get_class_time():
    nowdate = datetime.now()
    dongji = datetime.strptime('%s-10-01' % datetime.now().strftime('%Y'), '%Y-%m-%d')
    xiaji = datetime.strptime('%s-05-01' % datetime.now().strftime('%Y'), '%Y-%m-%d')

    if dongji >= nowdate >= xiaji:
        # 上课时间全部提前十分钟准备
        first_on = '07:50'
        first_off = '09:50'
        second_on = '10:10'
        second_off = '12:10'
        third_on = '14:20'
        third_off = '16:20'
        fourth_on = '16:30'
        fourth_off = '18:30'
        fifth_on = '19:30'
        fifth_off = '21:30'
    else:
        first_on = '07:50'
        first_off = '09:50'
        second_on = '10:10'
        second_off = '12:10'
        third_on = '13:50'
        third_off = '16:10'
        fourth_on = '16:40'
        fourth_off = '18:00'
        fifth_on = '19:00'
        fifth_off = '21:00'

    return first_on, first_off, second_on, second_off, third_on, third_off, fourth_on, fourth_off, fifth_on, fifth_off


class DengJiExcel(object):
    def __init__(self):
        first_on, first_off, second_on, second_off, third_on, third_off, fourth_on, fourth_off, fifth_on, fifth_off = get_class_time()
        self.num_dict = {0: '零', 1: '一', 2: '二', 3: '三', 4: '四', 5: '五', 6: '六', 7: '七', 8: '八', 9: '九', 10: '十',
                         11: '十一', 12: '十二', 13: '十三', 14: '十四', 15: '十五', 16: '十六', 17: '十七', 18: '十八', 19: '十九',
                         20: '二十 '}
        self.time_dict = {'12': [first_on, first_off], '34': [second_on, second_off], '56': [third_on, third_off],
                          '78': [fourth_on, fourth_off], '910': [fifth_on, fifth_off]}
        self.engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/dengji?charset=utf8')

    def main(self, nowdate, jiaoshi):
        nowdate = nowdate
        jiaoshi = jiaoshi
        nl = nowdate.split('-')
        week = datetime(int(nl[0]), int(nl[1]), int(nl[2])).isocalendar()[1] + 18
        xingqi = datetime(int(nl[0]), int(nl[1]), int(nl[2])).isocalendar()[2]
        if xingqi == 6:
            xingqi = 1
        df = pd.read_excel('./class_table/明远楼' + jiaoshi + '.xls', header=1, skipfooter=1, index_col=[0, 1])
        df.index.set_names(['节次', '时间'], inplace=True)
        class_df = pd.DataFrame(df.values, index=['12', '34', '56', '78', '910'],
                                columns=['1', '2', '3', '4', '5'])
        df = pd.read_sql_table(nowdate, self.engine, index_col='id', parse_dates='date')
        if len(df) == 0:
            return
        l = df.jihao.str.split('-', expand=True)
        df['jiaoshi'], df['zuoweihao'] = l[0], l[1]
        df['zuoweihao'] = df['zuoweihao'].astype(int)
        df1 = df[df.jiaoshi == jiaoshi]
        if len(df1) == 0:
            return
        # 创建工作簿
        filename = './登记表/' + nowdate + '-' + jiaoshi + '.xlsx'
        workbook = xlsxwriter.Workbook(filename)
        f1 = workbook.add_format({'border': 0, 'font_size': 20, 'bold': True, 'align': 'center'})
        head_f = workbook.add_format({'border': 0, 'font_size': 15, 'bold': True, 'align': 'left'})
        f = workbook.add_format({'border': 1, 'font_size': 13, 'align': 'vcenter'})
        for jieci in ['12', '34', '56', '78', '910']:
            # 08:00直接比较会自动补全为今天的日期+08:00 所以得加上当天的时间
            df2 = df1[(df1['date'] >= nowdate + ' ' + self.time_dict[jieci][0]) & (
                    df1['date'] <= nowdate + ' ' + self.time_dict[jieci][1])]
            if len(df2) == 0:
                continue
            class_info = class_df.loc[jieci, str(xingqi)]
            if type(class_info) == str:
                teacher = class_info.split('/')[-3]
                class_name = class_info.split('/')[-2]
                class_sub = class_info.split('/')[0]
            else:
                teacher = '无数据'
                class_name = '无数据'
                class_sub = '无数据'
            worksheet = workbook.add_worksheet(jieci)
            # 设定第0到9列的列宽为15
            worksheet.set_column(0, 9, 15)
            # 设定行高
            for i in range(14):
                worksheet.set_row(i, 30)
            worksheet.merge_range(0, 0, 0, 9, '计算机使用登记表', f1)
            worksheet.merge_range(1, 0, 1, 1, '机房：\t%s' % jiaoshi, head_f)
            worksheet.merge_range(1, 3, 1, 4, '周次：\t第%s周' % self.num_dict[week], head_f)
            worksheet.merge_range(1, 6, 1, 7, '星期：\t%s' % self.num_dict[xingqi], head_f)
            worksheet.merge_range(1, 8, 1, 9, '使用日期：\t%s' % nowdate, head_f)
            worksheet.merge_range(2, 0, 2, 2, '班级：\t%s' % class_name, head_f)
            worksheet.merge_range(2, 3, 2, 5, '科目：\t%s' % class_sub, head_f)
            worksheet.merge_range(2, 6, 2, 7, '节次：\t%s、%s' % (jieci[0], jieci[1:]), head_f)
            worksheet.merge_range(2, 8, 2, 9, '任课老师：\t%s' % teacher, head_f)
            j = 4
            for i in range(1, 101):
                try:
                    # 取-1 以最后一个登记的人为准
                    name = df2[df2.zuoweihao == i].values[-1][0]
                    content = "{:<3}\t{:>6}".format(i, name)
                except:
                    content = '{:<3}'.format(i)
                worksheet.write(j, (i - 10 * (j - 4)) - 1, content, f)
                if i % 10 == 0:
                    j += 1
        workbook.close()
        wb = load_workbook(filename)
        # C:\ProgramData\Anaconda3\Lib\site-packages\openpyxl\worksheet\page.py
        sheetl = wb.sheetnames
        for sheet in sheetl:
            ws = wb[sheet]
            ws.print_area = 'A1:J14'
            # self.page_setup = PrintPageSetup(worksheet=self)
            ws.page_setup.scale = 85
            # 9 A4
            ws.set_printer_settings(paper_size=9, orientation='landscape')
            wb.save(filename)
            wb.close()
        print('%s成功生成%s的登记表' % (nowdate, room))


rooml = ['1214', '1211', '1210', '1206', '1102', '1108', '1112', '1113', '1114']
#
d = DengJiExcel()

for room in rooml:
    d.main(datetime.now().strftime('%Y-%m-%d'), room)
    print('成功生成%s的登记表' % datetime.now().strftime('%Y-%m-%d') + room)
print(' 当前时间%s' % datetime.now())