import ComboCheckBox
import datetime
import os
import subprocess
import sys
import time

import openpyxl as oy
from PyQt5.QtCore import Qt
from PyQt5.QtWidgets import QApplication, QWidget, QDesktopWidget, \
    QHBoxLayout, QVBoxLayout, QPushButton, QLineEdit, \
    QTableWidget, QTableWidgetItem, QLabel, \
    QMessageBox, \
    QTreeWidget, QTreeWidgetItem

import pandas as pds
import numpy as np


class MainWindows(QWidget):
    def __init__(self):
        super().__init__()
        self.address_line = None
        self.fileListTb = None
        self.table = None
        self.lab = None
        self.sourcePath = ''
        self.destPath = ''
        self.table_layout = None
        self.selectFile = None
        self.car_nums_input = None

        # 窗体标题
        self.setWindowTitle('数据计算')
        # 窗体尺寸
        self.resize(1200, 700)
        self.setMinimumSize(600, 400)
        self.setWindowIconText("数据计算")
        # 窗体位置
        qr = self.frameGeometry()
        cp = QDesktopWidget().availableGeometry().center()
        qr.moveCenter(cp)

        # 创建顶部垂直方向布局 1
        layout = QVBoxLayout()
        layout.addLayout(self.init_header())
        layout.addLayout(self.init_form())
        # 创建表单垂直方向布局
        layout.addLayout(self.init_table())
        # 创建底部垂直方向布局
        layout.addLayout(self.init_bottom())
        self.setLayout(layout)
        self.show()

    def init_header(self):
        # 创建顶部菜单布局 1
        head_layout = QHBoxLayout()
        # 添加按钮

        self.address_line = address_line = QLineEdit()
        address_line.setText('C:/Users/yukan/Desktop')
        address_line.setFixedWidth(200)
        address_line.setPlaceholderText("请输入文件所在的目录")

        list_btn = QPushButton("获取文件列表")
        list_btn.setFixedSize(100, 25)
        list_btn.clicked.connect(self.event_add_click)

        list_btn3 = QPushButton("车号车数列转行")
        list_btn3.setFixedSize(100, 25)
        list_btn3.clicked.connect(lambda: self.start_calc(1))

        list_btn4 = QPushButton("结算表")
        list_btn4.setFixedSize(100, 25)
        list_btn4.clicked.connect(lambda: self.start_calc(2))

        list_btn5 = QPushButton("加班天数")
        list_btn5.setFixedSize(100, 25)
        list_btn5.clicked.connect(lambda: self.start_calc(3))

        self.car_nums_select = car_nums_select = ComboCheckBox.ComboCheckBox()
        car_nums_select.setDisabled(True)
        car_nums_select.setPlaceholderText("请输入车号")
        car_nums_select.setFixedWidth(150)
        car_nums_select.setFixedHeight(20)
        desc_label = QLabel()
        desc_label.setText('*****下面的表格仅显示每张表格的前20行数据做为预览*****')
        head_layout.addWidget(address_line)
        head_layout.addWidget(car_nums_select)
        head_layout.addWidget(list_btn)
        head_layout.addWidget(list_btn3)
        head_layout.addWidget(list_btn4)
        head_layout.addWidget(list_btn5)
        head_layout.addWidget(desc_label)
        head_layout.addStretch()
        return head_layout

    def init_form(self):
        form_layout = QHBoxLayout()
        form_layout.addStretch()
        return form_layout

    def init_table(self):
        self.table_layout = table_layout = QHBoxLayout()
        self.fileListTb = QTreeWidget()
        self.fileListTb.setColumnCount(1)
        self.fileListTb.setFixedWidth(200)
        self.fileListTb.setHeaderLabels(['文件列表'])
        self.fileListTb.clicked.connect(self.click_file_item)
        self.table = tab = QTableWidget(0, 300)

        table_layout.addWidget(self.fileListTb)
        table_layout.addWidget(tab)
        return table_layout

    def init_bottom(self):
        bottom_layout = QHBoxLayout()
        self.lab = lab = QLabel()
        lab.setText("休息中")
        lab.setFixedWidth(200)
        bottom_layout.addWidget(lab)
        bottom_layout.addStretch()
        return bottom_layout

    def event_add_click(self):
        try:
            self.table_layout.removeWidget(self.fileListTb)
            self.table_layout.removeWidget(self.table)
            self.fileListTb = QTreeWidget()
            self.fileListTb.setColumnCount(1)
            self.fileListTb.setFixedWidth(200)
            self.fileListTb.setHeaderLabels(['文件列表'])
            self.fileListTb.clicked.connect(self.click_file_item)
            self.table_layout.addWidget(self.fileListTb)
            self.table_layout.addWidget(self.table)
            self.fileListTb.expandAll()
            self.sourcePath = self.address_line.text()
            dirs = os.listdir(self.address_line.text())
            for dir in dirs:
                if dir.endswith('.xlsx'):
                    root = QTreeWidgetItem(self.fileListTb)
                    root.setText(0, dir)
                    root.setToolTip(0, dir)
        except Exception as e:
            QMessageBox.warning(self, '提示', str(e))
            print(e)

    def click_file_item(self):
        try:
            self.lab.setText('获取数据中...')
            self.lab.repaint()
            item = self.fileListTb.currentItem()
            self.selectFile = item.text(0)
            path = self.sourcePath + '\\' + item.text(0)
            # 计算并显示表格数据
            df = pds.read_excel(path, header=1, index_col=None, nrows=20, dtype=str)
            dt = pds.read_excel(path, header=1, index_col=None, usecols=['车号'], dtype=str)
            dt = dt['车号'].dropna().drop_duplicates().sort_values()
            v = np.delete(dt.values, 0)
            self.car_nums_select.loadItems(v.tolist())
            self.car_nums_select.setDisabled(False)
            self.table.clear()
            for index, row in df.iterrows():
                self.table.insertRow(index)
                for i in range(len(df.columns)):
                    cell = QTableWidgetItem(str(row[i]))
                    if str(row[i]) == 'nan':
                        cell = QTableWidgetItem('')
                    cell.setFlags(Qt.ItemIsEnabled)
                    self.table.setItem(index, i, cell)
            self.lab.setText('获取读取完成')
            self.lab.repaint()
        except FileNotFoundError as err:
            QMessageBox.warning(self, '提示', str(err))
            print(err)
        except Exception as e:
            QMessageBox.warning(self, '提示', str(e))
        finally:
            pass

    def config_source(self):
        from dialog import AlertDialog
        dialog = AlertDialog(self)
        dialog.setWindowModality(Qt.ApplicationModal)
        dialog.exec_()

    def start_calc(self, num):
        try:
            self.lab.setText('执行中...')
            self.lab.repaint()
            #
            df = pds.read_excel(self.sourcePath + '/' + self.selectFile,
                                usecols=['司机姓名', '车号',
                                         '发货时间', '车数',
                                         '工程名称', '销售方量',
                                         '施工部位',
                                         '补方',
                                         '退料', '公里数',
                                         '超时，过磅，接水，退料',
                                         '区间', '单据类型'],
                                header=1, dtype=str)
            # 获取 选中的车号
            car_nums = self.car_nums_select.Selectlist()
            if len(car_nums) == 0:
                if num == 1:
                    self.create_one_table(df)
                # # 生成结算表汇总
                if num == 2:
                    self.create_two_table(df)
                # 生成加班人员汇总
                if num == 3:
                    self.get_jia_ban_people_name(df)

            else:
                self.create_two_table(df[df['车号'].isin(car_nums)], '汇总')
                time.sleep(3)
                for num in car_nums:
                    time.sleep(3)
                    # 生成结算表汇总
                    self.create_two_table(df[df['车号'] == num], num)

            subprocess.Popen(r'explorer /select, ' + self.sourcePath)
            self.lab.setText('休息中...')
            self.lab.repaint()

        except (TypeError, PermissionError) as e:
            QMessageBox.warning(self, '提示', '系统错误!')
            self.write_logs(e)
        except ValueError as e:
            print(e)
            QMessageBox.warning(self, '提示', '文件错误!')

    def sum(self, nums, df):
        return df[nums].at[3] + df[nums].at[4] + df[nums].at[5] + df[nums].at[6] + df[nums].at[7]

    def multi(self, row1, row2, col, df):
        return df[row1].at[col] * df[row2].at[col]

    def task_start_callback(self, row_index):
        cell_status = QTreeWidgetItem("2")
        cell_status.setFlags(Qt.ItemIsEnabled)
        self.table.setItem(row_index, 6, cell_status)

    def write_logs(self, content):
        with open('log.text', mode='a+') as f:
            old = f.read()
            f.seek(0)
            f.writelines(str(content) + '\n')
            f.writelines(old)
            f.flush()
            f.close()

    def create_one_table(self, df):
        start_time = time.time()
        cell_name = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
                     'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK',
                     'AL', 'AM', 'AN', 'AO', 'AP']
        """
            计算结算表
        :return: 无返回值
        """
        """
            车号 司机姓名 车数(时间相差五分钟算一车,方量为0 的不计算)
            生成第一种表格
            :param df: df 对象
            :return: 无返回值 直接输出 excel 文件
        """
        dt = df[['司机姓名', '车号', '发货时间', '车数']]
        dt = dt[~dt['车数'].isna()]
        # 删除没有结算方量的数据
        car_nums = dt['车号'].dropna().drop_duplicates()
        keyValues = {}
        length = len(car_nums.values)
        current_nums = 0
        for item in car_nums.values:
            current_nums += 1
            self.lab.setText('执行进度:' + str(current_nums) + '/' + str(length))
            self.lab.repaint()
            keyValues[item] = self.get_data_list(dt, item)

        max_length = 0
        for item in keyValues:
            if max_length < len(keyValues[item]):
                max_length = len(keyValues[item])

        workbook = oy.load_workbook('./Blank.xlsx')
        ws = workbook['Sheet1']
        keyValues = sorted(keyValues.items(), key=lambda x: x[0])
        nums = 1
        for key in keyValues:
            ws['A' + str(nums)] = key[0]
            cou = 1
            for i in key[1]:
                if str(i) == 'nan' or i == 0:
                    continue
                ws[cell_name[cou] + str(nums)] = i
                cou = cou + 1
            nums += 1
        c_t = datetime.datetime.now().strftime('%Y-%m-%d%H%M%S')
        workbook.save(self.sourcePath + '/' + c_t + "-行专列.xlsx")
        workbook.close()

        # wf = pds.DataFrame(keyValues, index=None)
        # wf = wf.transpose()
        # wf = wf.sort_index()
        # c_t = datetime.datetime.now().strftime('%Y-%m-%d%H%M%S')
        # wf.to_excel(self.sourcePath + '/' + c_t + 'ONE-' + self.selectFile, na_rep=0, float_format=float, index=False,
        #             header=None)
        end_time = time.time()
        QMessageBox.warning(self, '导出成功', "转换表导出成功,耗时%.2f" % (end_time - start_time) + "秒")

    def sum_carNums(self, df, name):
        n = df[df['司机姓名'] == name]
        num = 0
        time_list = []
        for index, item in n.iterrows():
            if str(item['车数']) != 'nan':
                if self.has_sub_five_m(item['发货时间'], time_list):
                    time_list.append(item['发货时间'])
                    num += int(item['车数'])
        return num

    def has_sub_five_m(self, s, times):
        format = '%Y-%m-%d %H:%M:%S'
        if str(s) == 'nan':
            return True
        if len(times) == 0:
            return True
        for item in times:
            it = datetime.datetime.strptime(item, format)
            st = datetime.datetime.strptime(s, format)
            sec = (it - st).total_seconds()
            sec = abs(sec)
            if sec / 60 < 5:
                return False
        return True

    def get_data_list(self, df, car_nums):
        t = df[df['车号'] == car_nums]
        result = []
        n = t['司机姓名'].drop_duplicates()
        for item in n.values:
            result.append(item)
            result.append(self.sum_carNums(t, item))
        return result

    def create_two_table(self, df, car_nums=None):
        workbook = oy.load_workbook('./结算表2.xlsx')
        c = 'D'
        try:
            start_time = time.time()
            s = ''
            if car_nums is not None:
                s = car_nums
                workbook = oy.load_workbook('./单车结算表.xlsx')
                c = 'E'
            """
                生成第二张表 结算单
            :return:
            """
            self.lab.setText(s + '结算表开始计算...')
            self.lab.repaint()
            t = df['发货时间']
            format = '%Y-%m-%d'
            dt = datetime.datetime.strptime(t.values[0][0:7] + '-01', format)
            last_day = pds.date_range(start=dt, periods=1, freq='M').day.tolist()[0]
            dt2 = datetime.datetime.strptime(t.values[0][0:7] + '-' + str(last_day), format)
            start_d = dt.strftime('%Y年%m月%d日')
            last_d = dt2.strftime('%Y年%m月%d日')
            self.lab.setText('结算:8')
            self.lab.repaint()
            # 计算方量
            a1 = self.get_fang_liang_by_range(df, [0, 8])
            a2 = self.get_fang_liang_by_range(df, [8, 15])
            a3 = self.get_fang_liang_by_range(df, [15, 25])
            a4 = self.get_fang_liang_by_range(df, [25, 26])
            a5 = self.get_fang_liang_by_range(df, [26, 27])
            a6 = self.get_fang_liang_by_range(df, [27, 28])
            self.lab.setText('结算:7')
            self.lab.repaint()
            # 计算补方部分数据

            ws = workbook['结算']
            if car_nums is not None:
                ws['A5'] = car_nums
            # 所属期间：2023年7月01日-2023年7月31日
            ws['A2'] = '所属期间：' + start_d + '-' + last_d
            # 计算 普通运输范围
            ws[c + '5'] = a1[0]
            ws[c + '6'] = a2[0]
            ws[c + '7'] = a3[0]
            ws[c + '8'] = a4[0]
            ws[c + '9'] = a5[0]
            ws[c + '10'] = a6[0]
            ws[c + '11'] = a1[2]
            self.lab.setText('结算:6')
            self.lab.repaint()
            # 计算补方运输范围
            ws[c + '14'] = a1[1]
            ws[c + '15'] = a2[1]
            ws[c + '16'] = a3[1]
            ws[c + '17'] = a4[1]
            ws[c + '18'] = a5[1]
            ws[c + '19'] = a6[1]
            ws[c + '20'] = a1[3]
            self.lab.setText('结算:5')
            self.lab.repaint()
            if car_nums is None:
                # 计算接水洗泵数据
                jie_shui = self.get_jie_shui(df)
                ws['D24'] = jie_shui
                self.lab.setText('结算:4')
                self.lab.repaint()
                # 送水到工地
                song_shui = self.get_song_shui(df)
                ws['D25'] = song_shui
                self.lab.setText('结算:3')
                self.lab.repaint()
                # 应收一拖多运费补贴 取区间那一列的数据
                yun_fei = self.get_yunfei_butie(df)
                ws['D28'] = yun_fei[0]
                ws['D29'] = yun_fei[1]
                ws['D30'] = yun_fei[2]
                ws['D31'] = yun_fei[3]
                self.lab.setText('结算:2')
                self.lab.repaint()
                # 应收超时费
                chao_shi = self.get_chao_shi(df)
                ws['D37'] = chao_shi[0]
                ws['D38'] = chao_shi[1]
                ws['D39'] = chao_shi[2]
                ws['D42'] = chao_shi[3]
                ws['D43'] = chao_shi[4]
                ws['D44'] = chao_shi[5]
                self.lab.setText('结算:1')
                self.lab.repaint()
            c_t = datetime.datetime.now().strftime('%Y-%m-%d%H%M%S')
            workbook.save(self.sourcePath + '/' + s + '---' + c_t + "-结算表.xlsx")
            workbook.close()
            end_time = time.time()
            QMessageBox.warning(self, '导出成功', s + "结算表导出成功,耗时%.2f" % (end_time - start_time) + "秒")
        except Exception as e:
            print(e)
            QMessageBox.warning(self, '提示', '系统错误!')

    def get_fang_liang_by_range(self, df, range):
        # 第一个数据
        a1 = 0
        # 第二个数据 - 补方
        a2 = 0
        # 计算瓶改管
        a3 = 0
        # 瓶改管 补方
        a4 = 0
        for index, item in df.iterrows():
            if str(item['工程名称']) != 'nan' and '瓶改' in item['工程名称']:
                if str(item['销售方量']) != 'nan':
                    a3 += float(item['销售方量'])
                if str(item['补方']) != 'nan':
                    a4 += float(item['补方'])
            elif range[0] < float(item['公里数']) <= range[1]:
                if str(item['销售方量']) != 'nan':
                    a1 += float(item['销售方量'])
                if str(item['退料']) != 'nan':
                    a1 += float(item['退料'])
                if str(item['补方']) != 'nan':
                    a2 += float(item['补方'])

        return a1, a2, a3, a4

    def get_jie_shui(self, df):
        a = 0
        for index, item in df.iterrows():
            if '接水洗泵' in str(item['超时，过磅，接水，退料']):
                if '四车' in item['超时，过磅，接水，退料'] or '4车' in item['超时，过磅，接水，退料']:
                    a += 4
                if '三车' in item['超时，过磅，接水，退料'] or '3车' in item['超时，过磅，接水，退料']:
                    a += 3
                elif '两车' in item['超时，过磅，接水，退料'] or '2车' in item['超时，过磅，接水，退料']:
                    a += 2
                else:
                    a += 1
        return a

    def get_song_shui(self, df):
        a = 0
        for index, item in df.iterrows():
            if '清水一车' in str(item['施工部位']):
                a += 1

        return a

    def get_yunfei_butie(self, df):
        a1 = 0
        a2 = 0
        a3 = 0
        a4 = 0
        for index, item in df.iterrows():
            if '1-10' in str(item['区间']):
                a1 += 1
            elif '10-15' in str(item['区间']):
                a2 += 1
            elif '15-20' in str(item['区间']):
                a3 += 1
            elif '20-30' in str(item['区间']):
                a4 += 1
        return a1, a2, a3, a4

    def get_chao_shi(self, df):
        a1 = 0
        a2 = 0
        a3 = 0
        a4 = 0
        a5 = 0
        a6 = 0
        for index, item in df.iterrows():
            if '合同单' in str(item['单据类型']) or '1' in str(item['单据类型']):
                st = str(item['超时，过磅，接水，退料'])[0:4]
                if 2.3 < float(st) <= 3.3:
                    a1 += 1
                elif 3.3 < float(st) <= 4.3:
                    a2 += 1
                elif 4.3 < float(st) <= 5.3:
                    a3 += 1
            elif '现金单' in str(item['单据类型']) or '2' in str(item['单据类型']):
                st = str(item['超时，过磅，接水，退料'])[0:4]
                if 2.3 < float(st) <= 3.3:
                    a4 += 1
                elif 3.3 < float(st) <= 4.3:
                    a5 += 1
                elif 4.3 < float(st) <= 5.3:
                    a6 += 1
        return a1, a2, a3, a4, a5, a6

    def get_jia_ban_people_name(self, df):
        a = []
        dt = df[['司机姓名', '发货时间']].dropna()
        for index, item in dt.iterrows():
            # 判断发货时间是否在21:00-06:00
            if item['发货时间'][11:19] >= '21:00:00':
                if item['司机姓名'] != '':
                    item['发货时间'] = item['发货时间'][0:10]
                    a.append(item)

        dt = pds.DataFrame(a).drop_duplicates().groupby('司机姓名')
        b = []
        for name, item in dt:
            b.append([name, len(item)])
        pds.DataFrame(b).to_excel(self.sourcePath + '/发货时间在2100-0600.xlsx', header=None, index=None)
        # dt.to_excel(self.sourcePath + '/发货时间在2100-0600.xlsx', header=None, index='司机姓名')


if __name__ == '__main__':
    ap = QApplication(sys.argv)
    window = MainWindows()
    window.show()
    sys.exit(ap.exec_())