import sys, sqlite3, time, datetime
from PyQt5.QtWidgets import (QApplication, QVBoxLayout, QMainWindow, QPushButton, QFormLayout, QGroupBox, QWidget,
                             QLabel, QComboBox, QFileDialog)
import xlsxwriter
import os
import datetime
import sqlite3 as sql
from PyQt5.QtWidgets import QApplication, QDialog, QTableWidgetItem
from design.UiAdmissionDialog import Ui_AdmissionDialog
from design.main import Ui_MainWindow
from design.UiAddComponentDialog import Ui_AddComponentDialog
from design.index import Ui_IndexWindow
import xlrd, xlwt
from openpyxl import load_workbook
from design.rem import Ui_RemWindow
from design.analizis import Ui_AnalizWindow

f1 = ""
f2 = ""
f3 = ""


class MainWidget(QMainWindow, Ui_IndexWindow):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.pushButton_input_complect.clicked.connect(self.open_complect)
        self.pushButton_load_form.clicked.connect(self.open_load_form)
        self.pushButton_remains_complect.clicked.connect(self.open_rem)

    def open_rem(self):
        self.n = Remeins()
        self.close()
        self.n.show()

    def open_complect(self):
        self.n = AdmissionDialog()
        self.close()
        self.n.show()

    def open_load_form(self):
        self.n = MyWidget()
        self.close()
        self.n.show()


class MyWidget(QMainWindow, Ui_MainWindow):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.pushButton_add_file_drons.clicked.connect(self.ch_exel_drons)
        self.pushButton_add_file_complect.clicked.connect(self.ch_exel_complect)
        self.pushButton_add_fil_tech_map.clicked.connect(self.ch_exel_tech_map)
        self.pushButton_close.clicked.connect(self.close_w)
        self.pushButton_load.clicked.connect(self.load)

    def ch_exel_drons(self):
        global f1
        f1 = QFileDialog.getOpenFileName(self, 'Выбрать файл дронов')[0]
        self.lineEdit_drons.setText(f1)

    def ch_exel_complect(self):
        global f2
        f2 = QFileDialog.getOpenFileName(self, 'Выбрать файл комплектующих')[0]
        self.lineEdit_complect.setText(f2)

    def ch_exel_tech_map(self):
        global f3
        f3 = QFileDialog.getOpenFileName(self, 'Выбрать файл Тех. карт')[0]
        self.lineEdit_tech_map.setText(f3)

    def close_w(self):
        self.close()

    def load(self):
        con = sqlite3.connect("ABP_db.db")
        cur = con.cursor()

        wb = load_workbook(f1)

        sheet = wb.get_sheet_by_name(wb.get_sheet_names()[0])
        i = 2
        while (1 == 1):
            if sheet['A' + str(i)].value == None:
                break
            else:
                num_dr = sheet['A' + str(i)].value
                name_dr = sheet['B' + str(i)].value
                price_dr = sheet['C' + str(i)].value
                print(num_dr, name_dr, price_dr)
                try:
                    price_dr = float(price_dr)
                except Exception as e:
                    i += 1
                    continue
                cur.execute(f"""INSERT INTO Drones (number, name, price) VALUES ({num_dr}, '{name_dr}', {price_dr})""")
                con.commit()
            i += 1
        con.commit()

        wb = load_workbook(f2)
        sheet = wb.get_sheet_by_name(wb.get_sheet_names()[0])

        i = 2
        while (1 == 1):
            if sheet['A' + str(i)].value == None:
                break
            else:
                num = sheet['A' + str(i)].value
                name = sheet['B' + str(i)].value
                categ = sheet['C' + str(i)].value
                print(num, "----", name, "-----", categ)
                cur.execute(f"""INSERT INTO Components (number, name, category) VALUES ({num}, '{name}', '{categ}')""")
            i += 1

        wb = load_workbook(f3)

        sheet = wb.get_sheet_by_name(wb.get_sheet_names()[0])
        i = 2
        prev_num_tech = 0
        while (1 == 1):
            if sheet['B' + str(i)].value == None:
                break
            else:
                num_tech = sheet['A' + str(i)].value
                if num_tech is None:
                    num_tech = prev_num_tech
                else:
                    prev_num_tech = num_tech
                name_tech = sheet['B' + str(i)].value
                comp_tech = sheet['C' + str(i)].value
                count_tech = sheet['D' + str(i)].value
                try:
                    count_tech = int(count_tech)
                except Exception as e:
                    i += 1
                    continue
                print(num_tech, "----", name_tech, "----", comp_tech, "----", count_tech)
                cur.execute(
                    f"""INSERT INTO Maps (number, drone, component, count) VALUES ({num_tech}, '{name_tech}', '{comp_tech}', {count_tech})""")
                con.commit()
            i += 1


import pyqtgraph


class Analiz(QMainWindow, Ui_AnalizWindow):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.lineEdit_end.textChanged.connect(self.onChenged)

    def onChenged(self):
        if (check(self.lineEdit_beg.text())) and (check(self.lineEdit_end.text())):
            str = list(map(int, self.lineEdit_beg.text().split(".")))
            be = datetime.datetime(str[2], str[1], str[0])
            str = list(map(int, self.lineEdit_end.text().split(".")))

            self.graphicsView.clear()
            en = datetime.datetime(str[2], str[1], str[0])
            con = sqlite3.connect("ABP_db.db")
            cur = con.cursor()
            hist = cur.execute("SELECT * FROM Admissions").fetchall()
            gr = []
            for i in hist:
                if i[3] != '':
                    gr.append(i)

            delta = en - be
            be_d = list(map(int, self.lineEdit_beg.text().split('.')))
            p = 0
            c = 0

            while (p < len(gr)):
                q = gr[p]
                da = list(map(int, q[5].split('.')))
                if (da[2] >= be_d[2]) and (da[1] >= be_d[1]) and (da[0] >= be_d[0]):
                    break
                if q[3] != '':
                    c += q[4]
                p += 1

            delta_1 = datetime.datetime(2020, 3, 27) - datetime.datetime(2020, 3, 26)
            i = be
            ans = []
            ans_data = []
            ans.append(c)
            zn = 0
            ans_data.append(zn)
            while (en - i).days != 0:
                i += delta_1
                zn+=1
                data = list(map(int, i.date().strftime("%Y.%m.%d").split('.')))
                ans_data.append(zn)  # i.date().strftime("%d.%m.%Y")
                data[2], data[0] = data[0], data[2]
                print(data)
                for j in gr:
                    print(j)
                    d1 = list(map(int,j[5].split('.')))
                    print(d1)
                    if d1 == data and j[3] != '':
                        c += j[4]
                ans.append(c)
            print(ans)
            print(ans_data)
            self.graphicsView.plot(ans_data,ans, pen='g')
        else:
            self.graphicsView.clear()
def check(str):
    if (len(str.split('.')) == 3):
        if (len(str.split('.')[0]) == 2) and (len(str.split('.')[1]) == 2) and len(str.split('.')[2]) == 4:
            return True
        else:
            return False
    else:
        return False


def create_exel():
    con = sql.connect("ABP_db.db")
    cur = con.cursor()
    items = cur.execute(f"""SELECT * FROM Admissions""").fetchall()
    workbook = xlsxwriter.Workbook(
        'История' + datetime.datetime.strftime(datetime.datetime.now(), "_%Y_%m_%d__%H_%M_%S") + '.xlsx')
    worksheet = workbook.add_worksheet()
    for row in range(len(items)):
        worksheet.write(row, 0, items[row][0])
        worksheet.write(row, 1, items[row][1])
        worksheet.write(row, 2, items[row][2])
        worksheet.write(row, 3, items[row][3])
        worksheet.write(row, 4, items[row][4])
    workbook.close()


class Remeins(QMainWindow, Ui_RemWindow):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.pushButton_run.clicked.connect(self.run)
        self.pushButton_clode.clicked.connect(self.cl)
        self.lineEdit_data.setText(datetime.datetime.strftime(datetime.datetime.now(), "%d.%m.%Y"))
        self.pushButton_print.clicked.connect(self.print)

    def print(self):
        con = sqlite3.connect("ABP_db.db")
        cur = con.cursor()
        date = self.lineEdit_data.text().split(".")
        hist = cur.execute("SELECT * FROM Admissions").fetchall()
        comp = cur.execute(f"""SELECT * FROM Components""").fetchall()
        count = dict()
        for i in comp:
            count[i[1]] = 0
        for i in hist:
            dt_hist = i[5].split(".")
            if (int(date[2]) >= int(dt_hist[2]) and int(date[1]) >= int(dt_hist[1]) and int(date[0]) >= int(
                    dt_hist[0])):
                count[i[2]] += i[4]

        workbook = xlsxwriter.Workbook('Остатки' + date[0] + "_" + date[1] + "_" + date[2] + '.xlsx')
        worksheet = workbook.add_worksheet()
        for row in range(len(count)):
            worksheet.write(row, 0, str(comp[row][0]))
            worksheet.write(row, 1, comp[row][1])
            worksheet.write(row, 2, count[comp[row][1]])
        workbook.close()
        os.startfile('Остатки' + date[0] + "_" + date[1] + "_" + date[2] + '.xlsx', "print")

    def cl(self):
        self.close()

    def run(self):
        con = sqlite3.connect("ABP_db.db")
        cur = con.cursor()
        date = self.lineEdit_data.text().split(".")
        hist = cur.execute("SELECT * FROM Admissions").fetchall()
        print(hist)
        comp = cur.execute(f"""SELECT * FROM Components""").fetchall()
        print(comp)
        count = dict()
        for i in comp:
            count[i[1]] = 0
        for i in hist:
            dt_hist = i[5].split(".")
            if (int(date[2]) >= int(dt_hist[2]) and int(date[1]) >= int(dt_hist[1]) and int(date[0]) >= int(
                    dt_hist[0])):
                count[i[2]] += i[4]
        print(count)
        self.tableWidget_rem.setRowCount(len(comp))
        for i in range(len(comp)):
            print(comp[i][0])
            print(comp[i][1])
            print(count[comp[i][1]])

            self.tableWidget_rem.setItem(i, 0, QTableWidgetItem(comp[i][1]))
            self.tableWidget_rem.setItem(i, 1, QTableWidgetItem(str(count[comp[i][1]])))


class AddComponentDialog(QDialog, Ui_AddComponentDialog):
    def __init__(self, table_tw, parent):
        super().__init__()

        self.setupUi(self)
        self.table_tw = table_tw
        self.con = sql.connect("ABP_db.db")
        self.parent = parent

        cur = self.con.cursor()
        items = cur.execute(f"""SELECT name FROM Components""").fetchall()
        for item in items:
            self.component_cb.addItem(item[0])
        cur.close()
        self.OK_btn.clicked.connect(self.add_row)
        self.cancel_btn.clicked.connect(self.close)

    def add_row(self):
        component_name = self.component_cb.currentText()
        cur = self.con.cursor()
        categ = cur.execute(f"""SELECT category FROM Components
                                WHERE name like '{component_name}'""").fetchone()[0]
        if categ == "Аккумуляторные батареи":
            if self.serial_num_le.text() == '':
                self.message_lb.setText("Введите серийный номер для АКБ")
                return
            if self.count_sb.value() != 1:
                self.message_lb.setText("Для АКБ можно указать кол-во только равное 1")
                return
        else:
            if self.serial_num_le.text() != '':
                self.message_lb.setText("Серийный номер можно выбрать только для АКБ")
                return
        self.pos = self.table_tw.rowCount()
        self.table_tw.setRowCount(self.table_tw.rowCount() + 1)
        self.table_tw.setItem(self.pos, 0, QTableWidgetItem(component_name))
        self.table_tw.setItem(self.pos, 1, QTableWidgetItem(self.serial_num_le.text()))
        self.table_tw.setItem(self.pos, 2, QTableWidgetItem(str(self.count_sb.value())))
        self.parent.written = False
        self.close()


class AdmissionDialog(QDialog, Ui_AdmissionDialog):
    def __init__(self):
        super().__init__()
        self.setupUi(self)

        self.written = True

        self.row_count = 0
        self.table_tw.setRowCount(self.row_count)

        self.num = 1
        self.con = sql.connect("ABP_db.db")
        cur = self.con.cursor()
        res = cur.execute(f"""SELECT number from Admissions""").fetchall()
        if res:
            self.num = max(map(lambda x: x[0], res)) + 1
        self.number_lb.setText(str(self.num))

        now = datetime.datetime.today()
        self.date = f'{now.day}.{now.month}.{now.year}'
        self.date_lb.setText(self.date)

        self.ok_btn.clicked.connect(lambda: (self.ok_func(), self.close()))
        self.write_btn.clicked.connect(self.write_func)
        self.close_btn.clicked.connect(self.close_func)
        self.add_row_btn.clicked.connect(self.add_row_func)
        self.del_row_btn.clicked.connect(self.del_row_func)

    def close_func(self):
        if self.table_tw.rowCount():
            if not self.written:
                self.message_lb1.setText('Есть несохраненные данные.')
                self.message_lb2.setText('Чтобы все равно закрыть, нажмите ещё раз')
                self.written = True
            else:
                create_exel()
                self.close()
        else:
            create_exel()
            self.close()

    def del_row_func(self):
        items = self.table_tw.selectedItems()
        if items:
            self.table_tw.removeRow(items[0].row())

    def add_row_func(self) -> None:
        self.add_row_dialog = AddComponentDialog(self.table_tw, self)
        self.add_row_dialog.show()

    def ok_func(self) -> None:
        self.write_func()
        self.close()

    def write_func(self) -> None:
        person = self.person_name_le.text()
        cur = self.con.cursor()
        for i in range(self.table_tw.rowCount()):
            items = []
            for j in range(3):
                items.append(self.table_tw.item(i, j).text())
            cur.execute(f"""INSERT INTO Admissions (number, person, component, serial, count, date)
                            VALUES ({self.num}, '{person}', '{items[0]}', '{items[1]}', '{items[2]}', '{self.date}')""")
            self.con.commit()
        self.table_tw.setRowCount(0)
        self.written = True


app = QApplication(sys.argv)
ex = Analiz()
ex.show()
sys.exit(app.exec_())