#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/1/1 22:25
# @Author  : leon yan
# @Site    : 
# @File    : ProductRecord.py
# @Software: PyCharm
import datetime
import json
import time

import xlwt
from enum import Enum

from PyQt5.QtCore import pyqtSlot, Qt, QStringListModel, pyqtSignal, QDir
from PyQt5.QtGui import QBrush, QColor
from PyQt5.QtWidgets import (QApplication, QMainWindow, QDesktopWidget, QMessageBox, QAbstractItemView, QCheckBox,
                             QHBoxLayout, QWidget, QTableWidgetItem, QPushButton, QFileDialog)

import Globalist as gl
from lib.qpagenation import QPagenation
from lib.qsqledit import QSQL, DeleteDbRecord, DeleteDbRecord2, UpdateDB
from lib.qtablewidgetobj import QTableWidgetObj
from resource.ProductRecord_ui import Ui_QWFormTable


# 各单元格的类型(每一列)
class CellType(Enum):
    ctA = 1000
    ctB = 1001
    ctC = 1002
    ctD = 1003
    ctE = 1004
    ctF = 1005
    ctG = 1006
    ctH = 1007
    ctI = 1008
    ctJ = 1009
    ctK = 1010
    ctL = 1011
    ctM = 1012
    ctN = 1013
    ctO = 1014
    ctP = 1015


# 各字段在表格中的列号
class FieldColNum(Enum):
    colA = 0
    colB = 1
    colC = 2
    colD = 3
    colE = 4
    colF = 5
    colG = 6
    colH = 7
    colI = 8
    colJ = 9
    colK = 10
    colL = 11
    colM = 12
    colN = 13
    colO = 14
    colP = 15


'''分页处理'''


def doPageNation(lst, page, pagenum):
    obj = QPagenation(lst, page, pagenum)
    return obj.show(), obj.paginate(page, pagenum)


def doTotalPage(totalCounter, PageSize):
    aa = totalCounter % PageSize
    aa2 = totalCounter // PageSize
    if aa > 0:
        return aa2 + 1
    else:
        return aa2


def write_data_to_excel(name, lstData):
    # 实例化一个Workbook()对象(即excel文件)
    wbk = xlwt.Workbook()
    # 新建一个名为Sheet1的excel sheet。此处的cell_overwrite_ok =True是为了能对同一个单元格重复操作。
    sheet = wbk.add_sheet('Sheet1', cell_overwrite_ok=True)
    # 获取当前日期，得到一个datetime对象如：(2016, 8, 9, 23, 12, 23, 424000)
    strCurrentTime = datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d')
    for i in range(len(lstData)):
        for j in range(len(lstData[i])):
            sheet.write(i, j, lstData[i][j])
    wbk.save(name + "_" + strCurrentTime + '.xlsx')


class QProductRecord(QMainWindow, Ui_QWFormTable):
    def __init__(self, parent=None, *args, **kwargs):
        super().__init__(parent, *args, **kwargs)
        # gl.init()
        self.setupUi(self)
        self.model = QStringListModel(self)
        self.searchStr.returnPressed.connect(self.on_btnSearch_clicked)
        self.pagenumber.setText("")
        self.recordamount.setText("")
        self.searchStr.setText('')

        self.objtablewidget = QTableWidgetObj()

        '''2021-01-21'''
        '''表格用'''
        self._tableInitialized = False
        self.rowCount = 0
        self._tabStyle()
        self.bTableLineSelected = 0
        self.tableWidgetLine = []

        self.orderSeq = "desc"
        self.searchWhere = " 1 = 1 "
        self.strWhere = ""
        self.byOrder = " order by id "
        self.totalCounter = 0
        ''''''
        self.DbResult = None
        ''''''
        self.PageSize = 30
        self.totalPage = 1
        self.needPage = 1
        self.before = 0
        self.next = 0
        self.current = 0
        ''''''
        self.ScannedNum = 0
        self.AbnormalNum = 0
        self.pcsTable()
        self.CurrentTime = datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S')
        ''''''

    def pcsTable(self):
        if self.searchWhere:
            self.strWhere = self.searchWhere
        else:
            self.strWhere = " 1 =1 "
        retDbResult = self.rdProductLog("ProductLog", None, None, None, self.strWhere)  # 读数据库
        self.totalCounter = retDbResult[1]
        self.pcsPageNation(retDbResult[0])
        ''''''
        self.DbResult = retDbResult[0]
        # print(self.DbResult)

    '''读数据库'''

    def rdProductLog(self, tblName, dictCondition=None, strQueryType=None, eNum=None, strWhere=None):
        strOrder = self.byOrder + self.orderSeq
        aFields = ['id', 'LogTime', 'TodayInitPower', 'TotalPower', 'TodayInitAmount', 'TotalAmount', "DatePower", "DateAmount", "Operator"]
        obj = QSQL(tblName, dictCondition, strOrder, aFields, strQueryType, eNum, strWhere)
        rdData = obj.showLst()
        jd = json.loads(rdData)
        return jd, len(jd)

    '''Json转换,分页'''

    def pcsPageNation(self, lst):
        retLst = doPageNation(lst, self.needPage, self.PageSize)  # 分页处理
        # print(retLst)
        newLst = retLst[0][0]
        # print(newLst)
        self.totalPage = doTotalPage(self.totalCounter, self.PageSize)
        self.before = retLst[1]['before']
        self.current = retLst[1]['current']
        self.next = retLst[1]['next']
        '''表格显示'''
        self.do_OpenTable(newLst)
        self.pagenumber.setText(str(self.current) + " / " + str(self.totalPage))
        self.recordamount.setText(str(self.totalCounter))

    def __del__(self):
        print("Batch is deleted")

    def _tabStyle(self):
        # #表头颜色
        # headColTab = self.tableWidget.horizontalHeader()
        # headColTab.setStyleSheet("QHeaderView::section{background:#ffffff;color:#454545}")
        # headRowTab = self.tableWidget.verticalHeader()
        # headRowTab.setStyleSheet("QHeaderView::section{background:#ffffff;color:#454545}")
        #
        # #设定列宽
        self.tableWidget.horizontalHeader().resizeSection(0, 60)  # 选择
        self.tableWidget.horizontalHeader().resizeSection(1, 80)  # id
        self.tableWidget.horizontalHeader().resizeSection(2, 200)  # 日期
        self.tableWidget.horizontalHeader().resizeSection(3, 150)  # 今日耗电
        self.tableWidget.horizontalHeader().resizeSection(4, 150)  # 累计耗电
        self.tableWidget.horizontalHeader().resizeSection(5, 150)  # 今日产量
        self.tableWidget.horizontalHeader().resizeSection(6, 150)  # 累计产量
        self.tableWidget.horizontalHeader().resizeSection(7, 150)  # 今日产量
        self.tableWidget.horizontalHeader().resizeSection(8, 150)  # 累计产量
        self.tableWidget.horizontalHeader().resizeSection(9, 100)  # 操作者
        self.tableWidget.setContentsMargins(0, 0, 0, 0)
        self.tableWidget.verticalHeader().setDefaultSectionSize(40)  # 缺省行高
        self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)
        '''slot signal'''
        self.tableWidget.horizontalHeader().sectionClicked.connect(self.HorSectionClicked)  # 表头单击信号
        # self.tableWidget.setContextMenuPolicy(Qt.CustomContextMenu)  # 允许右键产生子菜单
        # self.tableWidget.customContextMenuRequested.connect(self.generateMenu)  # 右键菜单
        # self.tableWidget.doubleClicked.connect(self._editRow)

        self.rowCount = 0
        self.tableWidgetLine = []

    def _editRow(self):
        CurrentRow = self.tableWidget.currentRow()
        dbId = int(self.tableWidget.item(CurrentRow, 1).text())
        self.currentDbId.emit(dbId)

    def generateMenu(self, pos):
        actionItem = self.objtablewidget.generateEditMenu(pos, self.tableWidget)
        if actionItem == 'Edit': self._editRow()

    '''点击表头信号'''

    @pyqtSlot(int)
    def HorSectionClicked(self, index):
        if index == 0:
            for line in self.tableWidgetLine:
                if line[0].isChecked():
                    line[0].setChecked(False)
                else:
                    line[0].setChecked(True)
        if 0 < index <= 2:
            self.do_order(index)

    def _rdRowValue(self, rowNum):
        lst = []
        for i in range(self.COL_COUNT):
            item = self.itemModel.item(rowNum, i)
            if item:
                aa = (item.text())
            else:
                aa = None
            lst.append(aa)
        return lst

    '''新建或打开至表格'''

    def do_OpenTable(self, tabs):
        # 先清除列表
        self.objtablewidget.delAllTablineM2(self.tableWidgetLine, self.tableWidget)
        self.rowCount = self.tableWidget.rowCount()  # 表格行数
        # 添加至表格
        print(tabs)
        for i in range(len(tabs)):
            a0 = tabs[i][0]  #
            a1 = tabs[i][1]  #
            a2 = tabs[i][2]  #
            a3 = tabs[i][3]  #
            a4 = tabs[i][4]  #
            a5 = tabs[i][5]  #
            a6 = tabs[i][6]  #
            a7 = tabs[i][7]  #
            a8 = tabs[i][8]  #
            # a10 = tabs[i][10]  #
            # a11 = tabs[i][11]  #
            args = [a0, a1, a2, a3, a4, a5, a6, a7, a8]
            NewRowCount = self.rowCount + i
            self.tableWidget.insertRow(NewRowCount)
            self.__createItemsRow(NewRowCount, *args)

    '''创建表格并显示'''

    def __createItemsRow(self, rowNo, *args):
        nameTable = self.tableWidget
        # a
        # create an cell widget
        chkBox = QCheckBox(nameTable)
        h = QHBoxLayout()
        h.setAlignment(Qt.AlignCenter)
        h.addWidget(chkBox)
        w = QWidget()
        w.setLayout(h)
        nameTable.setCellWidget(rowNo, FieldColNum.colA.value, w)

        # a0
        item = QTableWidgetItem(str(args[0]), CellType.ctB.value)
        item.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
        font = item.font()
        font.setPointSize(8)
        item.setFont(font)
        item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsUserCheckable | Qt.AlignCenter)  # 不允许编辑文字
        nameTable.setItem(rowNo, FieldColNum.colB.value, item)
        # a1
        item = QTableWidgetItem(str(args[1]), CellType.ctC.value)
        item.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
        font = item.font()
        font.setPointSize(8)
        item.setFont(font)
        item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsUserCheckable)  # 不允许编辑文字
        nameTable.setItem(rowNo, FieldColNum.colC.value, item)
        # a2
        item = QTableWidgetItem(str(args[2]), CellType.ctD.value)
        item.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
        font = item.font()
        font.setPointSize(8)
        item.setFont(font)
        item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsUserCheckable)  # 不允许编辑文字
        nameTable.setItem(rowNo, FieldColNum.colD.value, item)

        # a3
        item = QTableWidgetItem(str(args[3]), CellType.ctE.value)
        item.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
        font = item.font()
        font.setPointSize(8)
        item.setFont(font)
        item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsUserCheckable)  # 不允许编辑文字
        nameTable.setItem(rowNo, FieldColNum.colE.value, item)

        # a4
        item = QTableWidgetItem(str(args[4]), CellType.ctF.value)
        item.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
        font = item.font()
        font.setPointSize(8)
        item.setFont(font)
        item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsUserCheckable)  # 不允许编辑文字
        nameTable.setItem(rowNo, FieldColNum.colF.value, item)

        # a5
        item = QTableWidgetItem(str(args[5]), CellType.ctG.value)
        item.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
        font = item.font()
        font.setPointSize(8)
        item.setFont(font)
        item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsUserCheckable)  # 不允许编辑文字
        nameTable.setItem(rowNo, FieldColNum.colG.value, item)

        # a6
        item = QTableWidgetItem(str(args[6]), CellType.ctH.value)
        item.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
        font = item.font()
        font.setPointSize(8)
        item.setFont(font)
        item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsUserCheckable)  # 不允许编辑文字
        nameTable.setItem(rowNo, FieldColNum.colH.value, item)

        # a7
        item = QTableWidgetItem(str(args[7]), CellType.ctI.value)
        item.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
        font = item.font()
        font.setPointSize(8)
        item.setFont(font)
        item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsUserCheckable)  # 不允许编辑文字
        nameTable.setItem(rowNo, FieldColNum.colI.value, item)

        # a8
        item = QTableWidgetItem(str(args[8]), CellType.ctJ.value)
        item.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
        font = item.font()
        font.setPointSize(8)
        item.setFont(font)
        item.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsUserCheckable)  # 不允许编辑文字
        nameTable.setItem(rowNo, FieldColNum.colJ.value, item)

        self._tableInitialized = True
        self.tableWidgetLine.append(
            [chkBox, args[0], args[1], args[2], args[3], args[4], args[5], args[6], args[7], args[8]])
    '''
    SLOT 信号链接
    排序， 翻页链接
    '''
    @pyqtSlot()
    def doPrintLabel(self):
        pass

    @pyqtSlot(int)
    def do_order(self, oderIndex):
        if oderIndex == 1:
            self.byOrder = "order by id "
            if self.orderSeq == "asc":
                self.orderSeq = "desc"
            else:
                self.orderSeq = "asc"
        if oderIndex == 2:
            self.byOrder = "order by LogTime "
            if self.orderSeq == "asc":
                self.orderSeq = "desc"
            else:
                self.orderSeq = "asc"

        self.needPage = 1
        self.pcsTable()

    @pyqtSlot()
    def on_btnFirst_clicked(self):
        if self.current > 1:
            self.needPage = 1
            self.pcsTable()

    @pyqtSlot()
    def on_btnBefore_clicked(self):
        if self.current > 1:
            self.needPage = self.before
            self.pcsTable()

    @pyqtSlot()
    def on_btnNext_clicked(self):
        if self.current < self.totalPage:
            self.needPage = self.next
            self.pcsTable()

    @pyqtSlot()
    def on_btnLast_clicked(self):
        if self.current < self.totalPage:
            self.needPage = self.totalPage
            self.pcsTable()

    '''
    搜索，删除，新建
    '''

    @pyqtSlot()
    def on_btnSearch_clicked(self):
        self.needPage = 1
        strWhere = "1 = 1"
        strWhere = strWhere + " and LogTime like '%" + self.searchStr.text() + "%' "
        self.searchWhere = strWhere
        self.pcsTable()

    @pyqtSlot()
    def on_btnDelete_clicked(self):
        tbllineObj = self.tableWidgetLine
        LstNeedDeleteId = []
        for line in tbllineObj:
            if line[0].isChecked():
                LstNeedDeleteId.append(line[1])
        if LstNeedDeleteId is None or LstNeedDeleteId == []:
            QMessageBox.information(self, "Tips", "No rows selected to delete")
            return
        '''删除确认?'''
        ret = QMessageBox.question(self, "Delete confirmation", "Are you sure you want to delete the selected list rows?")
        if ret != QMessageBox.Yes:
            return
        DeleteDbRecord2('ProductLog', LstNeedDeleteId)
        ''''''
        self.pcsTable()

    @pyqtSlot()
    def on_btnExport_clicked(self):
        if self.DbResult == [] or self.DbResult is None:
            return
        curDir = QDir.currentPath()  # 获取当前路径
        try:
            aFile, filt = QFileDialog.getSaveFileName(self,
                                                      "Open files", curDir, "All files(*.xlsx)")
            write_data_to_excel(aFile, self.DbResult)
        except BaseException as e:
            print(e)


if __name__ == '__main__':
    import sys

    app = QApplication(sys.argv)
    thisWindowObj = QProductRecord()

    thisWindowObj.show()
    sys.exit(app.exec_())