UIConroller.py

from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5 import uic
import sys
import cv2
from utils.Database import DB
from utils.ImageReceiver import ImageReceiver

from_class = uic.loadUiType("UI.ui")[0]

class WindowClass(QMainWindow, from_class) :
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.setWindowTitle("UI Manager")

        self.db = DB()

        # 입장 퇴장 시간 초기화
        self.minInTime = self.in_time_start.dateTime()
        self.maxInTime = self.in_time_end.dateTime()
        self.minOutTime = self.out_time_start.dateTime()
        self.maxOutTime = self.out_time_end.dateTime()
        
        # 검색, 리셋 버튼 함수 연결
        self.reset_btn.clicked.connect(self.reset)
        self.search_btn.clicked.connect(self.search)

        # 과일이 팔린 개수 로깅
        self.getFruits()

        # 테이블 컬럼 창 크기에 맞춰주기
        self.table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table_2.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table_3.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        # self.table_3.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
        
        
        self.pixmap = QPixmap()
        
        # self.player = ImageReceiver(filename=file[0], framerate=20)
        self.player = ImageReceiver(host='localhost', port=9090)
        self.player.daemon = True
        self.player.running = True
        self.player.update.connect(self.updatePlayer)
        self.player.start()
        

        # 불일치 로깅
        self.mismatch()

        # 매대 재고 가져오기
        self.getStock()

    def updatePlayer(self, frame):
        self.image = cv2.cvtColor(frame, cv2.COLOR_BGR2RGB)
        # print(frame)
        self.plotImg()

    
    def plotImg(self):
        h, w, c = self.image.shape

        qimage = QImage(self.image.data, w, h, w*c, QImage.Format_RGB888)

        self.pixmap = self.pixmap.fromImage(qimage)
        self.pixmap = self.pixmap.scaled(self.window.width(), self.window.height(), Qt.KeepAspectRatio)

        self.window.setPixmap(self.pixmap)
    
    
    
    def search(self):
        self.table.clearContents()
        self.getFruits()
        self.mismatch()
        self.getStock()
        # 고객 ID 입력값 변수로 저장
        self.customerId = self.customer_id.text()
        
        inTimeStart = self.in_time_start.dateTime().toString("yyyy-MM-dd HH:mm:ss")
        inTimeEnd = self.in_time_end.dateTime().toString("yyyy-MM-dd HH:mm:ss")
        outTimeStart = self.out_time_start.dateTime().toString("yyyy-MM-dd HH:mm:ss")
        outTimeEnd = self.out_time_end.dateTime().toString("yyyy-MM-dd HH:mm:ss")
        
        # db = DB()
        self.sql = """
            SELECT e.customerID,
            CASE WHEN e.enterStatus = '1' THEN e.enterenceTime ELSE NULL END AS inTime,
            CASE WHEN e.enterStatus = '0' THEN e.enterenceTime ELSE NULL END AS outTime,
            CASE WHEN e.enterStatus = '0' THEN f.fruitName ELSE NULL END AS fruitName,
            CASE WHEN e.enterStatus = '0' THEN po.outQuantity ELSE NULL END AS outQuantity,
            CASE WHEN e.enterStatus = '0' THEN po.outQuantity * f.price ELSE NULL END AS totalPrice
            FROM enterence e
            LEFT JOIN productOut po ON (po.customerID = e.customerID AND e.enterenceTime = po.outDate)
            LEFT JOIN fruits f ON po.fruitID = f.fruitID
        """
        if self.customerId:
            self.sql += f" WHERE e.customerID = {self.customerId}"

        # 날짜 범위 조건 추가
        if inTimeStart and inTimeEnd:
            self.sql += f" AND e.enterenceTime BETWEEN '{inTimeStart}' AND '{inTimeEnd}'"
            if outTimeStart and outTimeEnd:
                self.sql += f" AND e.enterenceTime BETWEEN '{outTimeStart}' AND '{outTimeEnd}'"
        else:
            if outTimeStart and outTimeEnd:
                self.sql += f" AND e.enterenceTime BETWEEN '{outTimeStart}' AND '{outTimeEnd}'"
        
        self.sql += " ORDER BY e.enterenceTime"
        self.table.setRowCount(0)

        self.db.execute(self.sql)
        result = self.db.fetchAll()
        print(result)

        # db.disconnect()
        if result is not None:
            for row in result:
                resultRow = self.table.rowCount()
                self.table.insertRow(resultRow)
                for i, v in enumerate(row):
                    if v is None: continue
                    self.table.setItem(resultRow, i, QTableWidgetItem(str(v)))
                    if i == 1:
                        break

            
    def reset(self):

        self.table.clearContents()
        

    def getFruits(self):
        # db=DB()
        
        self.sql = """
            SELECT po.outDate, f.fruitName, po.outQuantity
            FROM productOut po
            JOIN fruits f ON po.fruitID = f.fruitID
        """

        self.db.execute(self.sql)
        result = self.db.fetchAll()
        # db.disconnect()
        self.table_2.setRowCount(0)

        if result is not None:
            for row in result:
                resultRow = self.table_2.rowCount()
                self.table_2.insertRow(resultRow)
                for i, v in enumerate(row):
                    if v is None: continue
                    self.table_2.setItem(resultRow, i, QTableWidgetItem(str(v)))


    def mismatch(self):
        # db = DB()
        self.sql = """
            SELECT m.mismatchTime, f.fruitName, po.outQuantity
            FROM mismatchActionStand AS m
            JOIN
            fruits f ON m.fruitName = f.fruitName
            JOIN
            productOut po ON m.fruitName = f.fruitName
        """

        self.db.execute(self.sql)
        result = self.db.fetchAll()
        # db.disconnect()
        self.table_3.setRowCount(0)

        if result is not None:
            for row in result:
                resultRow = self.table_3.rowCount()
                self.table_3.insertRow(resultRow)
                for i, v in enumerate(row):
                    self.table_3.setItem(resultRow, i, QTableWidgetItem(str(v)))

    def getStock(self):
        # db = DB()
        self.sql = """
            select fruitName, stockStand
            from fruits
        """
        self.db.execute(self.sql)
        result = self.db.fetchAll()
        # db.disconnect()
        result_list = []

        print(result)
        if result is not None:
            for row in result:
                result_list.append(row)

            self.apple.setText(str(result_list[0][1]))
            self.banana.setText(str(result_list[1][1]))
            self.orange.setText(str(result_list[2][1]))

    def closeEvent(self, event):
        self.db.disconnect()
        self.player.stop()


if __name__ == "__main__":
    app = QApplication(sys.argv)
    myWindows = WindowClass()
    myWindows.show()
    sys.exit(app.exec_())