Category: 02. Tương tác Python – MySQL Server

#python -m pip install mysql-connector-python
import mysql.connector
import traceback
import pandas as pd
class Connector:
    def __init__(self,server=None, port=None, database=None, username=None, password=None):
        self.server=server
        self.port=port
        self.database=database
        self.username=username
        self.password=password
    def connect(self):
        try:
            self.conn = mysql.connector.connect(
                host=self.server,
                port=self.port,
                database=self.database,
                user=self.username,
                password=self.password)
            return self.conn
        except:
            self.conn=None
            traceback.print_exc()
        return None

    def disConnect(self):
        if self.conn != None:
            self.conn.close()

    def queryDataset(self, sql):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            df = pd.DataFrame(cursor.fetchall())
            if not df.empty:
                df.columns=cursor.column_names
            return df
        except:
            traceback.print_exc()
        return None
    def getTablesName(self):
        cursor = self.conn.cursor()
        cursor.execute("Show tables;")
        results=cursor.fetchall()
        tablesName=[]
        for item in results:
            tablesName.append([tableName for tableName in item][0])
        return tablesName

class MetricsResult:
    def __init__(self,mae,mse,rmse,r2_score):
        self.MAE=mae
        self.MSE=mse
        self.RMSE=rmse
        self.R2_SCORE=r2_score
    def __str__(self):
        result="MAE=%s"%self.MAE+"\n"+"MSE=%s"%self.MSE+"\n"+"RMSE=%s"%self.RMSE+"\n"+"R2_SCORE=%s"%self.R2_SCORE+"\n"
        return result

class TrainedModel:
    def __init__(self,model=None,X_train=None,X_test=None,y_train=None,y_test=None,columns_input=None,column_target=None):
        self.model=model
        self.X_train = X_train
        self.X_test = X_test
        self.y_train = y_train
        self.y_test = y_test
        self.columns_input=columns_input
        self.column_target=column_target

from matplotlib import pyplot as plt
import seaborn as sns
import pandas as pd
class PurchaseStatistic:
    def __init__(self,connector=None):
        self.connector = connector
        self.lasted_df=None
    def execPurchaseHistory(self,tableName=None):
        if tableName==None:
            sql="select * from purchasehistory"
        else:
            sql = "select * from %s"%tableName
        self.df=self.connector.queryDataset(sql)
        self.lasted_df=self.df
        return self.df
    def printHead(self,row):
        print(self.df.head(row))
    def printTail(self,row):
        print(self.df.tail(row))
    def printInfo(self):
        print(self.df.info())
    def printDecsribe(self):
        print(self.df.describe())
    def dateProcessing(self):
        self.df['invoice_date'] = pd.to_datetime(self.df['invoice_date'] , format = '%d/%m/%Y')
        self.df['month'] = self.df['invoice_date'].dt.month
        self.df['year'] = self.df['invoice_date'].dt.year
        self.lasted_df = self.df
    def processGenderDistribution(self):
        self.dfGender = self.df.gender.value_counts().reset_index()
        self.lasted_df = self.dfGender
        return self.dfGender
    def processAgeDistribution(self):
        self.dfAges = self.df.age.value_counts().reset_index()
        self.dfAges.sort_values(by=['age'], ascending=True, inplace=True)
        self.lasted_df = self.dfAges
        return self.dfAges
    def processAgeDistribution(self,fromAge,toAge):
        self.dfAges = self.df[(self.df.age >= fromAge) & (self.df.age <= toAge)].age.value_counts().reset_index()
        self.dfAges.sort_values(by=['age'], ascending=True,inplace=True)
        self.lasted_df = self.dfAges
        return self.dfAges
    def visualizePieChart(self,df,columnLabel,columnStatistic,title,legend=True):
        explode=[0.1]
        for i in range(len(df[columnLabel])-1):
            explode.append(0)
        plt.figure(figsize=(8, 6))
        plt.pie(df[columnStatistic], labels=df[columnLabel], autopct='%1.2f%%',explode=explode)
        if legend:
            plt.legend(df[columnLabel])
        plt.title(title)
        plt.show()
    def visualizePlotChart(self,df,columnX,columnY,title):
        plt.figure(figsize=(8, 6))
        plt.plot(df[columnX], df[columnY])
        plt.legend([columnX,columnY])
        plt.title(title)
        plt.xlabel(columnX)
        plt.ylabel(columnY)
        plt.grid()
        plt.show()
    def processCategoryDistribution(self):
        self.dfCategory = self.df.category.value_counts().reset_index()
        self.lasted_df = self.dfCategory
        return self.dfCategory
    def processGenderAndCategoryCounter(self):
        self.df_gender_order = self.df[['gender', 'category']]\
                                   .groupby(['gender', 'category'])\
                                   .value_counts()\
                                   .reset_index(name="count")
        self.lasted_df = self.df_gender_order
        return self.df_gender_order
    def processCategorySpending(self):
        self.df_cate_spending = self.df.groupby(['category'])["price"].sum().reset_index(name="price")
        self.lasted_df = self.df_cate_spending
        return self.df_cate_spending
    def processGenderCategorySpending(self):
        self.df_gender_cate_spending = self.df.groupby(['gender','category'])["price"].sum().reset_index(name="price")
        self.lasted_df = self.df_gender_cate_spending
        return self.df_gender_cate_spending
    def visualizeCountPlot(self,df,columnX,columnY,hueColumn,title):
        plt.figure(figsize=(8, 6))
        ax=sns.countplot(x=columnX,hue=hueColumn,data=df)
        plt.title(title)
        plt.xlabel(columnX)
        plt.ylabel(columnY)
        plt.grid()
        plt.legend()
        plt.show()
    def visualizeBarPlot(self,df,columnX,columnY,hueColumn,title,alpha=0.8,width=0.6):
        plt.figure(figsize=(8, 6))
        plt.ticklabel_format(useOffset=False, style='plain')
        ax=sns.barplot(data=df,x=columnX,y=columnY,hue=hueColumn,alpha=alpha,width=width)
        plt.title(title)
        plt.xlabel(columnX)
        plt.ylabel(columnY)
        plt.grid()
        plt.legend()
        plt.show()
    def visualizeBarChart(self,df,columnX,columnY,title):
        plt.figure(figsize=(8, 6))
        plt.ticklabel_format(useOffset=False, style='plain')
        plt.bar(df[columnX],df[columnY])
        plt.title(title)
        plt.xlabel(columnX)
        plt.ylabel(columnY)
        plt.grid()
        plt.show()
    def visualizeScatterPlot(self,df,columnX,columnY,title):
        plt.figure(figsize=(8, 6))
        plt.ticklabel_format(useOffset=False, style='plain')
        sns.scatterplot(data=df,x= columnX,y=columnY)
        plt.title(title)
        plt.xlabel(columnX)
        plt.ylabel(columnY)
        plt.grid()
        plt.show()
    def processPaymentMethod(self):
        self.payment = self.df['payment_method'].value_counts().reset_index(name="count").rename(columns={"index": "payment_method"})
        self.lasted_df = self.payment
        return self.payment
    def processShoppingMall(self):
        self.dfShoppingMall = self.df['shopping_mall'].value_counts().reset_index(name="count").rename(columns={"index": "shopping_mall"})
        self.lasted_df = self.dfShoppingMall
        return self.dfShoppingMall
    def processAgeOrderFrequence(self):
        #self.dfAgeGender = self.df.groupby(['age', 'gender'])['age'].value_counts().reset_index(name="count")
        self.dfAgeGender = self.df[['age', 'gender']].groupby(['age', 'gender']).value_counts().reset_index(name="count")
        self.lasted_df = self.dfAgeGender
        return self.dfAgeGender
    def processAgeSalesAmount(self):
        self.dfSalesAmount = self.df.copy(deep=True)
        self.dfSalesAmount['sales_amount'] = self.dfSalesAmount['quantity'] * self.dfSalesAmount['price']
        self.lasted_df = self.dfSalesAmount
        return self.dfSalesAmount
    def processMonthlySalesAmount(self):
        self.dfMonthlySalesAmount=self.df.copy(deep=True)
        self.dfMonthlySalesAmount['sales_amount'] = self.dfMonthlySalesAmount['quantity'] * self.dfMonthlySalesAmount['price']

        self.dfMonthlySalesAmount['invoice_date'] = pd.to_datetime(self.dfMonthlySalesAmount['invoice_date'],
                                            format='%d/%m/%Y')  # convert invoice date to date time format

        self.dfMonthlySalesAmount['month'] = self.dfMonthlySalesAmount['invoice_date'].dt.month

        self.dfMonthlySalesAmount = self.dfMonthlySalesAmount.groupby('month')['sales_amount'].sum().reset_index()
        self.lasted_df = self.dfMonthlySalesAmount
        return self.dfMonthlySalesAmount
    def visualizeLinePlotChart(self,df,columnX,columnY,tile,hue=None):
        plt.figure(figsize=(8, 6))
        plt.ticklabel_format(useOffset=False,style="plain")
        sns.lineplot(data=df,x=columnX, y=columnY, marker='o', color='orange',hue=hue)
        plt.xlabel(columnX)
        plt.ylabel(columnY)
        plt.title(tile)
        plt.legend(loc='upper right')
        plt.xticks(range(1, 13), ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'])
        plt.show()
    def processMonthlyAndYearSalesAmount(self):
        self.dfMonthlyAndYearSalesAmount = self.df.copy(deep=True)
        self.dfMonthlyAndYearSalesAmount['invoice_date'] = pd.to_datetime(self.dfMonthlyAndYearSalesAmount['invoice_date'],
                                                                   format='%d/%m/%Y')
        self.dfMonthlyAndYearSalesAmount['month'] = self.dfMonthlyAndYearSalesAmount['invoice_date'].dt.month
        self.dfMonthlyAndYearSalesAmount['year'] = self.dfMonthlyAndYearSalesAmount['invoice_date'].dt.year
        self.dfMonthlyAndYearSalesAmount['sales_amount'] = self.dfMonthlyAndYearSalesAmount['quantity'] * self.dfMonthlyAndYearSalesAmount['price']
        self.dfMonthlyAndYearSalesAmount = self.dfMonthlyAndYearSalesAmount.groupby(['year', 'month'], as_index=False).agg({'sales_amount': 'sum'})
        self.lasted_df = self.dfMonthlyAndYearSalesAmount
        return self.dfMonthlyAndYearSalesAmount

# Features encoding
from sklearn.preprocessing import LabelEncoder
import seaborn as sns
from matplotlib import pyplot as plt

from Models.PurchaseStatistic import PurchaseStatistic


class PurchaseMLModel(PurchaseStatistic):
    def __init__(self,connector=None):
        super().__init__(connector)
        self.le = LabelEncoder()
    def processTransformByColumns(self,df,columns):
        for col in columns:
            x=df[col]
            df[col] = self.le.fit_transform(x)
    def processTransform(self):
        categorical_feature = ['gender', 'category', 'payment_method', 'shopping_mall']
        numerical_feature = ['age', 'quantity', 'month', 'year']
        dropping = ['customer_id', 'invoice_no', 'day', 'invoice_date']
        result = ['price']
        self.dfTransform=self.df.copy(deep=True)
        self.dfTransform[["day", "month", "year"]] = self.dfTransform["invoice_date"].str.split("/", expand=True)
        self.dfTransform.drop(dropping, axis=1, inplace=True)
        for col in categorical_feature:
            x=self.dfTransform[col]
            self.dfTransform[col] = self.le.fit_transform(x)
        return self.dfTransform
    def buildCorrelationMatrix(self,df):
        plt.figure(figsize=(8, 6))
        df_corr = df.corr(numeric_only=True)  # Generate correlation matrix
        ax = sns.heatmap(df_corr, annot=True)
        plt.show()
    

import pandas as pd
from matplotlib import pyplot as plt

from sklearn.model_selection import train_test_split
#data modelling
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_absolute_error, mean_squared_error,r2_score
from sklearn.preprocessing import StandardScaler, LabelEncoder

from Models.MetricsResult import MetricsResult
from Models.PurchaseMLModel import PurchaseMLModel
from Models.TrainedModel import TrainedModel
from Utils.FileUtil import FileUtil

class PurchaseLinearRegression(PurchaseMLModel):
    def __init__(self,connector=None):
        super().__init__(connector)
        self.le = LabelEncoder()
        self.sc_std = StandardScaler()
    def processTrain(self,columns_input,column_target,test_size,random_state):
        self.execPurchaseHistory()
        self.processTransform()
        print(self.dfTransform.columns)
        print(self.dfTransform.iloc[0])
        y = self.dfTransform[column_target]
        X = self.dfTransform[columns_input]
        print("X=",X)
        print("y=", y)
        self.X_train, self.X_test, self.y_train, self.y_test = train_test_split(X, y, test_size=test_size, random_state=random_state)
        self.trainedmodel=TrainedModel()
        self.trainedmodel.X_train=self.X_train
        self.trainedmodel.X_test=self.X_test
        self.trainedmodel.y_train=self.y_train
        self.trainedmodel.y_test=self.y_test
        self.trainedmodel.columns_input=columns_input
        self.trainedmodel.column_target=column_target
        #self.sc_std = StandardScaler()
        self.X_train = self.sc_std.fit_transform(self.X_train)
        self.X_test = self.sc_std.transform(self.X_test)
        self.lr = LinearRegression()
        self.model = self.lr.fit(self.X_train, self.y_train)
        self.trainedmodel.model=self.model
    def visualizeActualAndPredictResult(self):
        plt.figure(figsize=(8, 6))
        plt.scatter(self.lr.predict(self.X_train), self.y_train)
        plt.xlabel('Predicted value of Y')
        plt.ylabel('Real value of Y')
        plt.show()
    def evaluate(self):
        pred = self.model.predict(self.X_test)
        mae=mean_absolute_error(self.y_test, pred)
        mse = mean_squared_error(self.y_test, pred, squared=True)
        rmse = mean_squared_error(self.y_test, pred, squared=False)
        r2score=r2_score(self.y_test, pred)
        return MetricsResult(mae,mse,rmse,r2score)
    def predictPriceFromGenderAndAge(self,gender,age):
        data_gender = {'gender': ["Male", "Female"]}
        df_gender = pd.DataFrame(data=data_gender)
        df_gender_transform = self.le.fit_transform(df_gender)
        col_gender = 0
        if gender == 'Male':
            col_gender = 0
        else:
            col_gender = 1
        data = [[df_gender_transform[col_gender], age]]
        input_transform = self.sc_std.transform(data)
        pred = self.predict(input_transform)
        return pred
    def predictPriceFromGenderAndAgeAndPayment(self,gender,age,payment_method):
        data_gender= {'gender': ["Male", "Female"]}
        df_gender=pd.DataFrame(data=data_gender)
        df_gender_transform=self.le.fit_transform(df_gender)
        data_payment_method={"payment_method":["Credit Card","Debit Card","Cash"]}
        df_payment_method=pd.DataFrame(data=data_payment_method)
        df_payment_method_transform=self.le.fit_transform(df_payment_method)
        col_gender=0
        if gender == 'Male':
            col_gender=0
        else:
            col_gender = 1
        col_payment=0
        if payment_method=="Credit Card":
            col_payment=0
        elif payment_method=="Debit Card":
            col_payment=1
        else:
            col_payment = 2
        data = [[df_gender_transform[col_gender], age,df_payment_method_transform[col_payment]]]

        input_transform = self.sc_std.transform(data)
        pred = self.predict(input_transform)
        return pred
    def predict(self,columns_input):
        pred = self.model.predict(columns_input)
        return pred
    def saveModel(self,fileName):
        ret=FileUtil.saveModel(self.trainedmodel,fileName)
        return ret
    def loadModel(self,fileName):
        self.trainedmodel=FileUtil.loadModel(fileName)
        self.sc_std.fit_transform(self.trainedmodel.X_train)
        self.model=self.trainedmodel.model
        return self.model

from Connectors.Connector import Connector
from Models.PurchaseStatistic import PurchaseStatistic

connector=Connector(server="localhost",port=3306,database="lecturer_retails",username="root",password="@Obama123")
connector.connect()
pm=PurchaseStatistic()
pm.connector=connector
pm.execPurchaseHistory()
dfGender=pm.processGenderDistribution()
print(dfGender)
pm.visualizePieChart(dfGender,"gender","count","Gender Distribution")

dfAge=pm.processAgeDistribution(30,50)
print(dfAge)
pm.visualizePlotChart(dfAge,"age","count","Age Distribution 30~50")

dfCategory=pm.processCategoryDistribution()
print(dfCategory)
pm.visualizePieChart(dfCategory,"category","count","Categories Distribution",legend=False)

dfCateSpending=pm.processCategorySpending()
print(dfCateSpending)
pm.visualizeBarChart(dfCateSpending,"category","price","Distribution category and Spending")


dfGenderCategory=pm.processGenderAndCategoryCounter()
print(dfGenderCategory)
pm.visualizeCountPlot(pm.df,"category","count","gender","Distribution gender and category")


dfPayment=pm.processPaymentMethod()
print(dfPayment)
pm.visualizePieChart(dfPayment,"payment_method","count","Payment Distribution",legend=False)

dfShoppingMall=pm.processShoppingMall()
print(dfShoppingMall)
pm.visualizePieChart(dfShoppingMall,"shopping_mall","count","Shopping Mall Distribution",legend=False)

dfGenderCateSpending=pm.processGenderCategorySpending()
print(dfGenderCateSpending)
pm.visualizeBarPlot(dfGenderCateSpending,"category","price","gender","Male and Female category Total Price Spend")

dfAgeGender=pm.processAgeOrderFrequence()
print(dfAgeGender)
pm.visualizeScatterPlot(dfAgeGender,"age","count","Age VS Order Frequence")

dfMonthlySalesAmount=pm.processMonthlySalesAmount()
print(dfMonthlySalesAmount)
pm.visualizeLinePlotChart(dfMonthlySalesAmount,"month","sales_amount","Monthly Variation in Sales Amount")

dfMonthlyAndYearSalesAmount=pm.processMonthlyAndYearSalesAmount()
print(dfMonthlyAndYearSalesAmount)
pm.visualizeLinePlotChart(dfMonthlyAndYearSalesAmount,"month","sales_amount","Monthly Variation in Sales Amount Over Years",hue="year")

from Connectors.Connector import Connector
from Models.PurchaseMLModel import PurchaseMLModel

connector=Connector(server="localhost",port=3306,database="lecturer_retails",username="root",password="@Obama123")
connector.connect()
pm=PurchaseMLModel(connector)
pm.execPurchaseHistory()

dfTransform=pm.processTransform()
print(dfTransform.head())
pm.buildCorrelationMatrix(dfTransform)

from Connectors.Connector import Connector
from Models.PurchaseLinearRegression import PurchaseLinearRegression

connector=Connector(server="localhost",port=3306,database="lecturer_retails",username="root",password="@Obama123")
connector.connect()
pm=PurchaseLinearRegression(connector=connector)
pm.processTrain(["gender","age"],"price",0.2,0)
#pm.processTrain(["gender","age","payment_method"],"price")
#pm.visualizeActualAndPredictResult()

eresult=pm.evaluate()
print(eresult)

gender="Male"
age=61
pred=pm.predictPriceFromGenderAndAge(gender,age)
print("Gender=%s and Age=%s=>Price=%s"%(gender,age,pred))

gender="Female"
age=61
pred=pm.predictPriceFromGenderAndAge(gender,age)
print("Gender=%s and Age=%s=>Price=%s"%(gender,age,pred))

print("------------------"*10)
pm=PurchaseLinearRegression()
pm.connector=connector
pm.processTrain(["gender","age","payment_method"],"price",0.2,0)

eresult=pm.evaluate()
print(eresult)

gender="Male"
age=61
payment="Credit Card"
pred=pm.predictPriceFromGenderAndAgeAndPayment(gender,age,payment)
print("Gender=%s and Age=%s and payment=%s=>Price=%s"%(gender,age,payment,pred))

gender="Male"
age=61
payment="Debit Card"
pred=pm.predictPriceFromGenderAndAgeAndPayment(gender,age,payment)
print("Gender=%s and Age=%s and payment=%s=>Price=%s"%(gender,age,payment,pred))

gender="Male"
age=61
payment="Cash"
pred=pm.predictPriceFromGenderAndAgeAndPayment(gender,age,payment)
print("Gender=%s and Age=%s and payment=%s=>Price=%s"%(gender,age,payment,pred))

ret=pm.saveModel("../Assets/LR_mymodel.zip")
print("ret save model=%s"%ret)

from Models.PurchaseLinearRegression import PurchaseLinearRegression

pm=PurchaseLinearRegression()
pm.loadModel("../Assets/TrainedModel_GenderAgePayment.zip")

gender="Female"
age=61
payment="Cash"
pred=pm.predictPriceFromGenderAndAgeAndPayment(gender,age,payment)
print("Gender=%s and Age=%s and payment=%s=>Price=%s"%(gender,age,payment,pred))
#Gender=Female and Age=61 and payment=Cash=>Price=[692.98688316]

import pickle
import traceback
class FileUtil:
    @staticmethod
    def saveModel(model,filename):
        try:
            pickle.dump(model,open(filename,'wb'))
            return True
        except:
            traceback.print_exc()
            return False
    @staticmethod
    def loadModel(filename):
        try:
            model=pickle.load(open(filename,'rb'))
            return model
        except:
            traceback.print_exc()
            return None

from enum import Enum

class ChartType(Enum):
    PieChart=1
    LinePlotChart=2
    BarChart=3
    BarPlot=4
    MultiBarChart=5
    ScatterPlot=6

import seaborn as sns
class ChartHandle:
    def getExplode(self,df,columnLabel):
        explode = [0.1]
        for i in range(len(df[columnLabel]) - 1):
            explode.append(0)
        return explode
    def visualizePieChart(self,figure,canvas,df,columnLabel,columnStatistic,title,legend=True):
        explode=self.getExplode(df,columnLabel)
        figure.clear()
        ax = figure.add_subplot(111)
        ax.pie(df[columnStatistic], labels=df[columnLabel], autopct='%1.2f%%', explode=explode)
        if legend:
            ax.legend(df[columnLabel],loc ='lower right')
        ax.set_title(title)
        canvas.draw()
    def visualizeLinePlotChart(self,figure,canvas,df,columnX,columnY,tile,hue=None,xticks=False):
        figure.clear()
        ax = figure.add_subplot(111)
        ax.ticklabel_format(useOffset=False,style="plain")
        ax.grid()
        sns.lineplot(data=df,x=columnX, y=columnY, marker='o', color='orange',hue=hue)
        ax.set_xlabel(columnX)
        ax.set_ylabel(columnY)
        ax.set_title(tile)
        ax.legend(loc ='lower right')
        #ax.set_xticks(range(1, 13), ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'])
        if xticks==True:
            ax.set_xticks(range(1, 13), ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'])
        canvas.draw()
    def visualizeBarChart(self,figure,canvas,df,columnX,columnY,title):
        figure.clear()
        ax = figure.add_subplot(111)
        ax.ticklabel_format(useOffset=False, style="plain")
        ax.grid()
        ax.bar(df[columnX],df[columnY])
        ax.set_title(title)
        ax.set_xlabel(columnX)
        ax.set_ylabel(columnY)
        canvas.draw()
    def visualizeBarPlot(self,figure,canvas,df,columnX,columnY,hueColumn,title,alpha=0.8,width=0.6):
        figure.clear()
        ax = figure.add_subplot(111)
        ax.ticklabel_format(useOffset=False, style="plain")
        ax.grid()
        ax=sns.barplot(data=df,x=columnX,y=columnY,hue=hueColumn,alpha=alpha,width=width)
        ax.set_title(title)
        ax.set_xlabel(columnX)
        ax.set_ylabel(columnY)
        canvas.draw()
    def visualizeMultiBarChart(self,figure,canvas,df,columnX,columnY,hueColumn,title):
        figure.clear()
        ax = figure.add_subplot(111)
        ax.ticklabel_format(useOffset=False, style="plain")
        ax.grid()
        sns.countplot(x=columnX, hue=hueColumn, data=df)
        ax.set_title(title)
        ax.set_xlabel(columnX)
        ax.set_ylabel(columnY)
        canvas.draw()
    def visualizeScatterPlot(self,figure,canvas,df,columnX,columnY,title):
        figure.clear()
        ax = figure.add_subplot(111)
        ax.ticklabel_format(useOffset=False, style="plain")
        ax.grid()
        sns.scatterplot(data=df,x= columnX,y=columnY)
        ax.set_title(title)
        ax.set_xlabel(columnX)
        ax.set_ylabel(columnY)
        canvas.draw()

# Form implementation generated from reading ui file 'E:\Elearning\MLBAProject\UI\DatabaseConnect.ui'
#
# Created by: PyQt6 UI code generator 6.6.1
#
# WARNING: Any manual changes made to this file will be lost when pyuic6 is
# run again.  Do not edit this file unless you know what you are doing.


from PyQt6 import QtCore, QtGui, QtWidgets


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(359, 333)
        icon = QtGui.QIcon()
        icon.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_logo.jpg"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        MainWindow.setWindowIcon(icon)
        self.centralwidget = QtWidgets.QWidget(parent=MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.groupBox = QtWidgets.QGroupBox(parent=self.centralwidget)
        self.groupBox.setGeometry(QtCore.QRect(20, 10, 321, 271))
        self.groupBox.setObjectName("groupBox")
        self.label = QtWidgets.QLabel(parent=self.groupBox)
        self.label.setGeometry(QtCore.QRect(20, 30, 51, 16))
        self.label.setObjectName("label")
        self.lineEditServer = QtWidgets.QLineEdit(parent=self.groupBox)
        self.lineEditServer.setGeometry(QtCore.QRect(80, 30, 201, 22))
        self.lineEditServer.setObjectName("lineEditServer")
        self.lineEditPort = QtWidgets.QLineEdit(parent=self.groupBox)
        self.lineEditPort.setGeometry(QtCore.QRect(80, 60, 201, 22))
        self.lineEditPort.setObjectName("lineEditPort")
        self.label_2 = QtWidgets.QLabel(parent=self.groupBox)
        self.label_2.setGeometry(QtCore.QRect(40, 60, 31, 16))
        self.label_2.setObjectName("label_2")
        self.lineEditDatabase = QtWidgets.QLineEdit(parent=self.groupBox)
        self.lineEditDatabase.setGeometry(QtCore.QRect(80, 100, 201, 22))
        self.lineEditDatabase.setObjectName("lineEditDatabase")
        self.label_3 = QtWidgets.QLabel(parent=self.groupBox)
        self.label_3.setGeometry(QtCore.QRect(10, 100, 61, 16))
        self.label_3.setObjectName("label_3")
        self.lineEditUser = QtWidgets.QLineEdit(parent=self.groupBox)
        self.lineEditUser.setGeometry(QtCore.QRect(80, 140, 201, 22))
        self.lineEditUser.setObjectName("lineEditUser")
        self.label_4 = QtWidgets.QLabel(parent=self.groupBox)
        self.label_4.setGeometry(QtCore.QRect(30, 140, 41, 16))
        self.label_4.setObjectName("label_4")
        self.lineEditPassword = QtWidgets.QLineEdit(parent=self.groupBox)
        self.lineEditPassword.setGeometry(QtCore.QRect(80, 180, 201, 22))
        self.lineEditPassword.setEchoMode(QtWidgets.QLineEdit.EchoMode.Password)
        self.lineEditPassword.setObjectName("lineEditPassword")
        self.label_5 = QtWidgets.QLabel(parent=self.groupBox)
        self.label_5.setGeometry(QtCore.QRect(10, 180, 61, 20))
        self.label_5.setObjectName("label_5")
        self.pushButtonConnect = QtWidgets.QPushButton(parent=self.groupBox)
        self.pushButtonConnect.setGeometry(QtCore.QRect(40, 220, 111, 31))
        icon1 = QtGui.QIcon()
        icon1.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_connect.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonConnect.setIcon(icon1)
        self.pushButtonConnect.setObjectName("pushButtonConnect")
        self.pushButtonClose = QtWidgets.QPushButton(parent=self.groupBox)
        self.pushButtonClose.setGeometry(QtCore.QRect(190, 220, 101, 31))
        icon2 = QtGui.QIcon()
        icon2.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_shutdown.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonClose.setIcon(icon2)
        self.pushButtonClose.setObjectName("pushButtonClose")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(parent=MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 359, 26))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(parent=MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        self.pushButtonClose.clicked.connect(MainWindow.close) # type: ignore
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "Trần Duy Thanh"))
        self.groupBox.setTitle(_translate("MainWindow", "Connection Setting:"))
        self.label.setText(_translate("MainWindow", "Server:"))
        self.lineEditServer.setText(_translate("MainWindow", "localhost"))
        self.lineEditPort.setText(_translate("MainWindow", "3306"))
        self.label_2.setText(_translate("MainWindow", "Port:"))
        self.lineEditDatabase.setText(_translate("MainWindow", "lecturer_retails"))
        self.label_3.setText(_translate("MainWindow", "Database:"))
        self.lineEditUser.setText(_translate("MainWindow", "root"))
        self.label_4.setText(_translate("MainWindow", "User:"))
        self.lineEditPassword.setText(_translate("MainWindow", "@Obama123"))
        self.label_5.setText(_translate("MainWindow", "Password:"))
        self.pushButtonConnect.setText(_translate("MainWindow", "Connect"))
        self.pushButtonClose.setText(_translate("MainWindow", "Close"))
        self.pushButtonClose.setShortcut(_translate("MainWindow", "Esc"))

import traceback

from PyQt6.QtCore import Qt
from PyQt6.QtWidgets import QMessageBox

from Connectors.Connector import Connector
from UI.DatabaseConnect import Ui_MainWindow


class DatabaseConnectEx(Ui_MainWindow):
    def setupUi(self, MainWindow):
        super().setupUi(MainWindow)
        self.MainWindow=MainWindow
        self.pushButtonConnect.clicked.connect(self.connectDatabase)
    def connectDatabase(self):
        try:
            self.connector=Connector()
            self.connector.server=self.lineEditServer.text()
            self.connector.port=(int)(self.lineEditPort.text())
            self.connector.database=self.lineEditDatabase.text()
            self.connector.username=self.lineEditUser.text()
            self.connector.password=self.lineEditPassword.text()
            self.connector.connect()
            self.msg=QMessageBox()
            self.msg.setText("Connect database successful!")
            self.msg.setWindowTitle("Info")
            #self.msg.show()
            self.MainWindow.close()
            if self.parent!=None:
                self.parent.checkEnableWidget(True)
        except:
            traceback.print_exc()
            self.msg = QMessageBox()
            self.msg.setText("Connect database failed")
            self.msg.setWindowTitle("Info")
            self.msg.show()
    def show(self):
        self.MainWindow.setWindowModality(Qt.WindowModality.ApplicationModal)
        self.MainWindow.show()

# Form implementation generated from reading ui file 'E:\Elearning\MLBAProject\UI\MainWindow.ui'
#
# Created by: PyQt6 UI code generator 6.6.1
#
# WARNING: Any manual changes made to this file will be lost when pyuic6 is
# run again.  Do not edit this file unless you know what you are doing.


from PyQt6 import QtCore, QtGui, QtWidgets


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(1279, 852)
        icon = QtGui.QIcon()
        icon.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_logo.jpg"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        MainWindow.setWindowIcon(icon)
        self.centralwidget = QtWidgets.QWidget(parent=MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.verticalLayout_2 = QtWidgets.QVBoxLayout(self.centralwidget)
        self.verticalLayout_2.setObjectName("verticalLayout_2")
        self.tabWidget = QtWidgets.QTabWidget(parent=self.centralwidget)
        self.tabWidget.setObjectName("tabWidget")
        self.tab = QtWidgets.QWidget()
        self.tab.setObjectName("tab")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.tab)
        self.verticalLayout.setObjectName("verticalLayout")
        self.gridLayout = QtWidgets.QGridLayout()
        self.gridLayout.setObjectName("gridLayout")
        self.groupBox_3 = QtWidgets.QGroupBox(parent=self.tab)
        self.groupBox_3.setMinimumSize(QtCore.QSize(0, 200))
        self.groupBox_3.setMaximumSize(QtCore.QSize(16777215, 200))
        self.groupBox_3.setBaseSize(QtCore.QSize(0, 200))
        self.groupBox_3.setObjectName("groupBox_3")
        self.verticalLayout_4 = QtWidgets.QVBoxLayout(self.groupBox_3)
        self.verticalLayout_4.setObjectName("verticalLayout_4")
        self.tableWidgetStatistic = QtWidgets.QTableWidget(parent=self.groupBox_3)
        self.tableWidgetStatistic.setMinimumSize(QtCore.QSize(0, 180))
        self.tableWidgetStatistic.setMaximumSize(QtCore.QSize(16777215, 180))
        self.tableWidgetStatistic.setBaseSize(QtCore.QSize(0, 180))
        self.tableWidgetStatistic.setObjectName("tableWidgetStatistic")
        self.tableWidgetStatistic.setColumnCount(2)
        self.tableWidgetStatistic.setRowCount(0)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidgetStatistic.setHorizontalHeaderItem(0, item)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidgetStatistic.setHorizontalHeaderItem(1, item)
        self.verticalLayout_4.addWidget(self.tableWidgetStatistic)
        self.gridLayout.addWidget(self.groupBox_3, 0, 1, 1, 1)
        self.groupBox_4 = QtWidgets.QGroupBox(parent=self.tab)
        self.groupBox_4.setObjectName("groupBox_4")
        self.verticalLayout_3 = QtWidgets.QVBoxLayout(self.groupBox_4)
        self.verticalLayout_3.setObjectName("verticalLayout_3")
        self.verticalLayoutPlot = QtWidgets.QVBoxLayout()
        self.verticalLayoutPlot.setObjectName("verticalLayoutPlot")
        self.verticalLayout_3.addLayout(self.verticalLayoutPlot)
        self.gridLayout.addWidget(self.groupBox_4, 1, 1, 1, 1)
        self.groupBox = QtWidgets.QGroupBox(parent=self.tab)
        self.groupBox.setMinimumSize(QtCore.QSize(400, 0))
        self.groupBox.setMaximumSize(QtCore.QSize(400, 16777215))
        self.groupBox.setBaseSize(QtCore.QSize(400, 0))
        self.groupBox.setObjectName("groupBox")
        self.verticalLayout_6 = QtWidgets.QVBoxLayout(self.groupBox)
        self.verticalLayout_6.setObjectName("verticalLayout_6")
        self.verticalLayoutFunctions = QtWidgets.QVBoxLayout()
        self.verticalLayoutFunctions.setObjectName("verticalLayoutFunctions")
        self.pushButtonPurchaseRatesByGender = QtWidgets.QPushButton(parent=self.groupBox)
        icon1 = QtGui.QIcon()
        icon1.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_gender.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonPurchaseRatesByGender.setIcon(icon1)
        self.pushButtonPurchaseRatesByGender.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonPurchaseRatesByGender.setObjectName("pushButtonPurchaseRatesByGender")
        self.verticalLayoutFunctions.addWidget(self.pushButtonPurchaseRatesByGender)
        self.horizontalLayout = QtWidgets.QHBoxLayout()
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.label_2 = QtWidgets.QLabel(parent=self.groupBox)
        self.label_2.setMaximumSize(QtCore.QSize(30, 16777215))
        self.label_2.setObjectName("label_2")
        self.horizontalLayout.addWidget(self.label_2)
        self.lineEditFromAge = QtWidgets.QLineEdit(parent=self.groupBox)
        self.lineEditFromAge.setMaximumSize(QtCore.QSize(30, 16777215))
        self.lineEditFromAge.setObjectName("lineEditFromAge")
        self.horizontalLayout.addWidget(self.lineEditFromAge)
        self.label = QtWidgets.QLabel(parent=self.groupBox)
        self.label.setMaximumSize(QtCore.QSize(30, 16777215))
        self.label.setObjectName("label")
        self.horizontalLayout.addWidget(self.label)
        self.lineEditToAge = QtWidgets.QLineEdit(parent=self.groupBox)
        self.lineEditToAge.setMaximumSize(QtCore.QSize(30, 16777215))
        self.lineEditToAge.setObjectName("lineEditToAge")
        self.horizontalLayout.addWidget(self.lineEditToAge)
        self.pushButtonPurchaseRatesByAgeGroup = QtWidgets.QPushButton(parent=self.groupBox)
        icon2 = QtGui.QIcon()
        icon2.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_age.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonPurchaseRatesByAgeGroup.setIcon(icon2)
        self.pushButtonPurchaseRatesByAgeGroup.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonPurchaseRatesByAgeGroup.setObjectName("pushButtonPurchaseRatesByAgeGroup")
        self.horizontalLayout.addWidget(self.pushButtonPurchaseRatesByAgeGroup)
        self.verticalLayoutFunctions.addLayout(self.horizontalLayout)
        self.pushButtonPurchaseCountingByCategory = QtWidgets.QPushButton(parent=self.groupBox)
        icon3 = QtGui.QIcon()
        icon3.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_category.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonPurchaseCountingByCategory.setIcon(icon3)
        self.pushButtonPurchaseCountingByCategory.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonPurchaseCountingByCategory.setObjectName("pushButtonPurchaseCountingByCategory")
        self.verticalLayoutFunctions.addWidget(self.pushButtonPurchaseCountingByCategory)
        self.pushButtonPurchaseValueByCategory = QtWidgets.QPushButton(parent=self.groupBox)
        icon4 = QtGui.QIcon()
        icon4.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_cate_value.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonPurchaseValueByCategory.setIcon(icon4)
        self.pushButtonPurchaseValueByCategory.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonPurchaseValueByCategory.setObjectName("pushButtonPurchaseValueByCategory")
        self.verticalLayoutFunctions.addWidget(self.pushButtonPurchaseValueByCategory)
        self.pushButtonPurchaseByCategoryAndGender = QtWidgets.QPushButton(parent=self.groupBox)
        icon5 = QtGui.QIcon()
        icon5.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_cate_gender.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonPurchaseByCategoryAndGender.setIcon(icon5)
        self.pushButtonPurchaseByCategoryAndGender.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonPurchaseByCategoryAndGender.setObjectName("pushButtonPurchaseByCategoryAndGender")
        self.verticalLayoutFunctions.addWidget(self.pushButtonPurchaseByCategoryAndGender)
        self.pushButtonPaymentMethod = QtWidgets.QPushButton(parent=self.groupBox)
        icon6 = QtGui.QIcon()
        icon6.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_payment.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonPaymentMethod.setIcon(icon6)
        self.pushButtonPaymentMethod.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonPaymentMethod.setObjectName("pushButtonPaymentMethod")
        self.verticalLayoutFunctions.addWidget(self.pushButtonPaymentMethod)
        self.pushButtonPurchaseRatesByShoppingMall = QtWidgets.QPushButton(parent=self.groupBox)
        icon7 = QtGui.QIcon()
        icon7.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_shopping_mall.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonPurchaseRatesByShoppingMall.setIcon(icon7)
        self.pushButtonPurchaseRatesByShoppingMall.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonPurchaseRatesByShoppingMall.setObjectName("pushButtonPurchaseRatesByShoppingMall")
        self.verticalLayoutFunctions.addWidget(self.pushButtonPurchaseRatesByShoppingMall)
        self.pushButtonProductSpendingByGender = QtWidgets.QPushButton(parent=self.groupBox)
        icon8 = QtGui.QIcon()
        icon8.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_spending_gender.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonProductSpendingByGender.setIcon(icon8)
        self.pushButtonProductSpendingByGender.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonProductSpendingByGender.setObjectName("pushButtonProductSpendingByGender")
        self.verticalLayoutFunctions.addWidget(self.pushButtonProductSpendingByGender)
        self.pushButtonPurchaseFrequenceByAge = QtWidgets.QPushButton(parent=self.groupBox)
        icon9 = QtGui.QIcon()
        icon9.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_frequence_age.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonPurchaseFrequenceByAge.setIcon(icon9)
        self.pushButtonPurchaseFrequenceByAge.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonPurchaseFrequenceByAge.setObjectName("pushButtonPurchaseFrequenceByAge")
        self.verticalLayoutFunctions.addWidget(self.pushButtonPurchaseFrequenceByAge)
        self.pushButtonSalesFluctuationsByMonth = QtWidgets.QPushButton(parent=self.groupBox)
        icon10 = QtGui.QIcon()
        icon10.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_sales_monthly.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonSalesFluctuationsByMonth.setIcon(icon10)
        self.pushButtonSalesFluctuationsByMonth.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonSalesFluctuationsByMonth.setObjectName("pushButtonSalesFluctuationsByMonth")
        self.verticalLayoutFunctions.addWidget(self.pushButtonSalesFluctuationsByMonth)
        self.pushButtonSalesFlucuationsByYearAndMonth = QtWidgets.QPushButton(parent=self.groupBox)
        icon11 = QtGui.QIcon()
        icon11.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_sales_yearly_monthly.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonSalesFlucuationsByYearAndMonth.setIcon(icon11)
        self.pushButtonSalesFlucuationsByYearAndMonth.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonSalesFlucuationsByYearAndMonth.setObjectName("pushButtonSalesFlucuationsByYearAndMonth")
        self.verticalLayoutFunctions.addWidget(self.pushButtonSalesFlucuationsByYearAndMonth)
        self.verticalLayout_6.addLayout(self.verticalLayoutFunctions)
        self.gridLayout.addWidget(self.groupBox, 0, 0, 2, 1)
        self.verticalLayout.addLayout(self.gridLayout)
        icon12 = QtGui.QIcon()
        icon12.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_statistic_tab.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.tabWidget.addTab(self.tab, icon12, "")
        self.tab_2 = QtWidgets.QWidget()
        self.tab_2.setObjectName("tab_2")
        self.groupBox_2 = QtWidgets.QGroupBox(parent=self.tab_2)
        self.groupBox_2.setGeometry(QtCore.QRect(20, 10, 521, 100))
        self.groupBox_2.setMinimumSize(QtCore.QSize(150, 100))
        self.groupBox_2.setObjectName("groupBox_2")
        self.radioButtonGenderAge = QtWidgets.QRadioButton(parent=self.groupBox_2)
        self.radioButtonGenderAge.setGeometry(QtCore.QRect(20, 30, 201, 20))
        self.radioButtonGenderAge.setChecked(True)
        self.radioButtonGenderAge.setObjectName("radioButtonGenderAge")
        self.radioButtonGenderAgePayment = QtWidgets.QRadioButton(parent=self.groupBox_2)
        self.radioButtonGenderAgePayment.setGeometry(QtCore.QRect(20, 60, 291, 20))
        self.radioButtonGenderAgePayment.setObjectName("radioButtonGenderAgePayment")
        self.groupBox_6 = QtWidgets.QGroupBox(parent=self.tab_2)
        self.groupBox_6.setGeometry(QtCore.QRect(20, 210, 521, 151))
        self.groupBox_6.setObjectName("groupBox_6")
        self.label_7 = QtWidgets.QLabel(parent=self.groupBox_6)
        self.label_7.setGeometry(QtCore.QRect(20, 20, 61, 16))
        self.label_7.setObjectName("label_7")
        self.label_8 = QtWidgets.QLabel(parent=self.groupBox_6)
        self.label_8.setGeometry(QtCore.QRect(20, 50, 61, 16))
        self.label_8.setObjectName("label_8")
        self.label_9 = QtWidgets.QLabel(parent=self.groupBox_6)
        self.label_9.setGeometry(QtCore.QRect(20, 80, 61, 16))
        self.label_9.setObjectName("label_9")
        self.label_10 = QtWidgets.QLabel(parent=self.groupBox_6)
        self.label_10.setGeometry(QtCore.QRect(20, 110, 81, 16))
        self.label_10.setObjectName("label_10")
        self.lineEditMAE = QtWidgets.QLineEdit(parent=self.groupBox_6)
        self.lineEditMAE.setGeometry(QtCore.QRect(110, 20, 271, 22))
        self.lineEditMAE.setObjectName("lineEditMAE")
        self.lineEditMSE = QtWidgets.QLineEdit(parent=self.groupBox_6)
        self.lineEditMSE.setGeometry(QtCore.QRect(110, 50, 271, 22))
        self.lineEditMSE.setObjectName("lineEditMSE")
        self.lineEditRMSE = QtWidgets.QLineEdit(parent=self.groupBox_6)
        self.lineEditRMSE.setGeometry(QtCore.QRect(110, 80, 271, 22))
        self.lineEditRMSE.setObjectName("lineEditRMSE")
        self.lineEditR2SCore = QtWidgets.QLineEdit(parent=self.groupBox_6)
        self.lineEditR2SCore.setGeometry(QtCore.QRect(110, 110, 271, 22))
        self.lineEditR2SCore.setObjectName("lineEditR2SCore")
        self.pushButtonEvaluate = QtWidgets.QPushButton(parent=self.groupBox_6)
        self.pushButtonEvaluate.setGeometry(QtCore.QRect(390, 60, 111, 41))
        icon13 = QtGui.QIcon()
        icon13.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_evaluate.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonEvaluate.setIcon(icon13)
        self.pushButtonEvaluate.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonEvaluate.setObjectName("pushButtonEvaluate")
        self.groupBox_7 = QtWidgets.QGroupBox(parent=self.tab_2)
        self.groupBox_7.setGeometry(QtCore.QRect(20, 120, 521, 81))
        self.groupBox_7.setObjectName("groupBox_7")
        self.lineEditRandomState = QtWidgets.QLineEdit(parent=self.groupBox_7)
        self.lineEditRandomState.setGeometry(QtCore.QRect(120, 50, 261, 20))
        self.lineEditRandomState.setObjectName("lineEditRandomState")
        self.label_5 = QtWidgets.QLabel(parent=self.groupBox_7)
        self.label_5.setGeometry(QtCore.QRect(20, 50, 91, 16))
        self.label_5.setObjectName("label_5")
        self.label_4 = QtWidgets.QLabel(parent=self.groupBox_7)
        self.label_4.setGeometry(QtCore.QRect(350, 20, 21, 16))
        self.label_4.setObjectName("label_4")
        self.label_3 = QtWidgets.QLabel(parent=self.groupBox_7)
        self.label_3.setGeometry(QtCore.QRect(20, 20, 61, 16))
        self.label_3.setObjectName("label_3")
        self.lineEditTestSize = QtWidgets.QLineEdit(parent=self.groupBox_7)
        self.lineEditTestSize.setGeometry(QtCore.QRect(120, 20, 221, 20))
        self.lineEditTestSize.setObjectName("lineEditTestSize")
        self.pushButtonTrainModel = QtWidgets.QPushButton(parent=self.groupBox_7)
        self.pushButtonTrainModel.setGeometry(QtCore.QRect(390, 30, 111, 41))
        icon14 = QtGui.QIcon()
        icon14.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_trainmodel.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonTrainModel.setIcon(icon14)
        self.pushButtonTrainModel.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonTrainModel.setObjectName("pushButtonTrainModel")
        self.groupBox_8 = QtWidgets.QGroupBox(parent=self.tab_2)
        self.groupBox_8.setGeometry(QtCore.QRect(20, 370, 521, 71))
        self.groupBox_8.setObjectName("groupBox_8")
        self.label_11 = QtWidgets.QLabel(parent=self.groupBox_8)
        self.label_11.setGeometry(QtCore.QRect(10, 30, 41, 16))
        self.label_11.setObjectName("label_11")
        self.lineEditPath = QtWidgets.QLineEdit(parent=self.groupBox_8)
        self.lineEditPath.setGeometry(QtCore.QRect(50, 30, 281, 22))
        self.lineEditPath.setObjectName("lineEditPath")
        self.pushButtonSavePath = QtWidgets.QPushButton(parent=self.groupBox_8)
        self.pushButtonSavePath.setGeometry(QtCore.QRect(340, 30, 41, 28))
        self.pushButtonSavePath.setObjectName("pushButtonSavePath")
        self.pushButtonSaveModel = QtWidgets.QPushButton(parent=self.groupBox_8)
        self.pushButtonSaveModel.setGeometry(QtCore.QRect(390, 20, 111, 41))
        icon15 = QtGui.QIcon()
        icon15.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_savemodel.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonSaveModel.setIcon(icon15)
        self.pushButtonSaveModel.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonSaveModel.setObjectName("pushButtonSaveModel")
        self.groupBox_9 = QtWidgets.QGroupBox(parent=self.tab_2)
        self.groupBox_9.setGeometry(QtCore.QRect(20, 450, 521, 71))
        self.groupBox_9.setObjectName("groupBox_9")
        self.pushButtonLoadModel = QtWidgets.QPushButton(parent=self.groupBox_9)
        self.pushButtonLoadModel.setGeometry(QtCore.QRect(10, 20, 111, 41))
        icon16 = QtGui.QIcon()
        icon16.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_loadmodel.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonLoadModel.setIcon(icon16)
        self.pushButtonLoadModel.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonLoadModel.setObjectName("pushButtonLoadModel")
        self.lineEditLocationLoadTrainedModel = QtWidgets.QLineEdit(parent=self.groupBox_9)
        self.lineEditLocationLoadTrainedModel.setGeometry(QtCore.QRect(130, 30, 371, 22))
        self.lineEditLocationLoadTrainedModel.setObjectName("lineEditLocationLoadTrainedModel")
        self.groupBox_10 = QtWidgets.QGroupBox(parent=self.tab_2)
        self.groupBox_10.setGeometry(QtCore.QRect(20, 530, 521, 201))
        self.groupBox_10.setObjectName("groupBox_10")
        self.label_12 = QtWidgets.QLabel(parent=self.groupBox_10)
        self.label_12.setGeometry(QtCore.QRect(20, 20, 101, 16))
        self.label_12.setObjectName("label_12")
        self.lineEditGender = QtWidgets.QLineEdit(parent=self.groupBox_10)
        self.lineEditGender.setGeometry(QtCore.QRect(130, 20, 241, 22))
        self.lineEditGender.setObjectName("lineEditGender")
        self.lineEditAge = QtWidgets.QLineEdit(parent=self.groupBox_10)
        self.lineEditAge.setGeometry(QtCore.QRect(130, 50, 241, 22))
        self.lineEditAge.setObjectName("lineEditAge")
        self.label_13 = QtWidgets.QLabel(parent=self.groupBox_10)
        self.label_13.setGeometry(QtCore.QRect(20, 50, 101, 16))
        self.label_13.setObjectName("label_13")
        self.lineEditPaymentMethod = QtWidgets.QLineEdit(parent=self.groupBox_10)
        self.lineEditPaymentMethod.setGeometry(QtCore.QRect(130, 80, 241, 22))
        self.lineEditPaymentMethod.setObjectName("lineEditPaymentMethod")
        self.label_14 = QtWidgets.QLabel(parent=self.groupBox_10)
        self.label_14.setGeometry(QtCore.QRect(20, 80, 101, 16))
        self.label_14.setObjectName("label_14")
        self.pushButtonPredict = QtWidgets.QPushButton(parent=self.groupBox_10)
        self.pushButtonPredict.setGeometry(QtCore.QRect(130, 110, 111, 41))
        icon17 = QtGui.QIcon()
        icon17.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_prediction.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonPredict.setIcon(icon17)
        self.pushButtonPredict.setIconSize(QtCore.QSize(32, 32))
        self.pushButtonPredict.setObjectName("pushButtonPredict")
        self.lineEditPredictedPrice = QtWidgets.QLineEdit(parent=self.groupBox_10)
        self.lineEditPredictedPrice.setGeometry(QtCore.QRect(130, 160, 241, 22))
        self.lineEditPredictedPrice.setObjectName("lineEditPredictedPrice")
        self.label_15 = QtWidgets.QLabel(parent=self.groupBox_10)
        self.label_15.setGeometry(QtCore.QRect(20, 160, 101, 16))
        self.label_15.setObjectName("label_15")
        icon18 = QtGui.QIcon()
        icon18.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_machinelearning_tab.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.tabWidget.addTab(self.tab_2, icon18, "")
        self.verticalLayout_2.addWidget(self.tabWidget)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(parent=MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 1279, 26))
        self.menubar.setObjectName("menubar")
        self.menuSystem = QtWidgets.QMenu(parent=self.menubar)
        self.menuSystem.setAutoFillBackground(False)
        self.menuSystem.setTearOffEnabled(False)
        self.menuSystem.setSeparatorsCollapsible(True)
        self.menuSystem.setToolTipsVisible(True)
        self.menuSystem.setObjectName("menuSystem")
        self.menuHelp = QtWidgets.QMenu(parent=self.menubar)
        self.menuHelp.setObjectName("menuHelp")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(parent=MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)
        self.actionConnection = QtGui.QAction(parent=MainWindow)
        icon19 = QtGui.QIcon()
        icon19.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_connection.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.actionConnection.setIcon(icon19)
        self.actionConnection.setObjectName("actionConnection")
        self.actionSave_trained_Machine_Learning_Model = QtGui.QAction(parent=MainWindow)
        icon20 = QtGui.QIcon()
        icon20.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_save.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.actionSave_trained_Machine_Learning_Model.setIcon(icon20)
        self.actionSave_trained_Machine_Learning_Model.setObjectName("actionSave_trained_Machine_Learning_Model")
        self.actionLoad_trained_Machine_Learning_Model = QtGui.QAction(parent=MainWindow)
        icon21 = QtGui.QIcon()
        icon21.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_load.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.actionLoad_trained_Machine_Learning_Model.setIcon(icon21)
        self.actionLoad_trained_Machine_Learning_Model.setObjectName("actionLoad_trained_Machine_Learning_Model")
        self.actionExit = QtGui.QAction(parent=MainWindow)
        icon22 = QtGui.QIcon()
        icon22.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_shutdown.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.actionExit.setIcon(icon22)
        self.actionExit.setObjectName("actionExit")
        self.actionInstructions = QtGui.QAction(parent=MainWindow)
        icon23 = QtGui.QIcon()
        icon23.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_instruction.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.actionInstructions.setIcon(icon23)
        self.actionInstructions.setObjectName("actionInstructions")
        self.actionAbout = QtGui.QAction(parent=MainWindow)
        icon24 = QtGui.QIcon()
        icon24.addPixmap(QtGui.QPixmap("E:\\Elearning\\MLBAProject\\UI\\../Images/ic_about.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.actionAbout.setIcon(icon24)
        self.actionAbout.setObjectName("actionAbout")
        self.menuSystem.addAction(self.actionConnection)
        self.menuSystem.addSeparator()
        self.menuSystem.addAction(self.actionSave_trained_Machine_Learning_Model)
        self.menuSystem.addSeparator()
        self.menuSystem.addAction(self.actionLoad_trained_Machine_Learning_Model)
        self.menuSystem.addSeparator()
        self.menuSystem.addAction(self.actionExit)
        self.menuHelp.addAction(self.actionInstructions)
        self.menuHelp.addSeparator()
        self.menuHelp.addAction(self.actionAbout)
        self.menubar.addAction(self.menuSystem.menuAction())
        self.menubar.addAction(self.menuHelp.menuAction())

        self.retranslateUi(MainWindow)
        self.tabWidget.setCurrentIndex(0)
        self.actionExit.triggered.connect(MainWindow.close) # type: ignore
        QtCore.QMetaObject.connectSlotsByName(MainWindow)
        MainWindow.setTabOrder(self.tabWidget, self.pushButtonPurchaseRatesByGender)
        MainWindow.setTabOrder(self.pushButtonPurchaseRatesByGender, self.tableWidgetStatistic)
        MainWindow.setTabOrder(self.tableWidgetStatistic, self.lineEditFromAge)
        MainWindow.setTabOrder(self.lineEditFromAge, self.lineEditToAge)
        MainWindow.setTabOrder(self.lineEditToAge, self.pushButtonPurchaseRatesByAgeGroup)
        MainWindow.setTabOrder(self.pushButtonPurchaseRatesByAgeGroup, self.pushButtonPurchaseCountingByCategory)
        MainWindow.setTabOrder(self.pushButtonPurchaseCountingByCategory, self.pushButtonPurchaseValueByCategory)
        MainWindow.setTabOrder(self.pushButtonPurchaseValueByCategory, self.pushButtonPurchaseByCategoryAndGender)
        MainWindow.setTabOrder(self.pushButtonPurchaseByCategoryAndGender, self.pushButtonPaymentMethod)
        MainWindow.setTabOrder(self.pushButtonPaymentMethod, self.pushButtonPurchaseRatesByShoppingMall)
        MainWindow.setTabOrder(self.pushButtonPurchaseRatesByShoppingMall, self.pushButtonProductSpendingByGender)
        MainWindow.setTabOrder(self.pushButtonProductSpendingByGender, self.pushButtonPurchaseFrequenceByAge)
        MainWindow.setTabOrder(self.pushButtonPurchaseFrequenceByAge, self.pushButtonSalesFluctuationsByMonth)
        MainWindow.setTabOrder(self.pushButtonSalesFluctuationsByMonth, self.pushButtonSalesFlucuationsByYearAndMonth)
        MainWindow.setTabOrder(self.pushButtonSalesFlucuationsByYearAndMonth, self.lineEditTestSize)
        MainWindow.setTabOrder(self.lineEditTestSize, self.lineEditRandomState)
        MainWindow.setTabOrder(self.lineEditRandomState, self.pushButtonTrainModel)
        MainWindow.setTabOrder(self.pushButtonTrainModel, self.lineEditMAE)
        MainWindow.setTabOrder(self.lineEditMAE, self.lineEditMSE)
        MainWindow.setTabOrder(self.lineEditMSE, self.lineEditRMSE)
        MainWindow.setTabOrder(self.lineEditRMSE, self.lineEditR2SCore)
        MainWindow.setTabOrder(self.lineEditR2SCore, self.pushButtonEvaluate)
        MainWindow.setTabOrder(self.pushButtonEvaluate, self.lineEditPath)
        MainWindow.setTabOrder(self.lineEditPath, self.pushButtonSavePath)
        MainWindow.setTabOrder(self.pushButtonSavePath, self.pushButtonSaveModel)
        MainWindow.setTabOrder(self.pushButtonSaveModel, self.pushButtonLoadModel)
        MainWindow.setTabOrder(self.pushButtonLoadModel, self.lineEditLocationLoadTrainedModel)
        MainWindow.setTabOrder(self.lineEditLocationLoadTrainedModel, self.lineEditGender)
        MainWindow.setTabOrder(self.lineEditGender, self.lineEditAge)
        MainWindow.setTabOrder(self.lineEditAge, self.lineEditPaymentMethod)
        MainWindow.setTabOrder(self.lineEditPaymentMethod, self.pushButtonPredict)
        MainWindow.setTabOrder(self.pushButtonPredict, self.lineEditPredictedPrice)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "Trần Duy Thanh"))
        self.groupBox_3.setTitle(_translate("MainWindow", "List of Data:"))
        item = self.tableWidgetStatistic.horizontalHeaderItem(0)
        item.setText(_translate("MainWindow", "Gender"))
        item = self.tableWidgetStatistic.horizontalHeaderItem(1)
        item.setText(_translate("MainWindow", "Number Sales"))
        self.groupBox_4.setTitle(_translate("MainWindow", "Chart Visualization:"))
        self.groupBox.setTitle(_translate("MainWindow", "Functions:"))
        self.pushButtonPurchaseRatesByGender.setText(_translate("MainWindow", "(1) Purchase rates by gender"))
        self.label_2.setText(_translate("MainWindow", "From:"))
        self.lineEditFromAge.setText(_translate("MainWindow", "30"))
        self.label.setText(_translate("MainWindow", "To:"))
        self.lineEditToAge.setText(_translate("MainWindow", "50"))
        self.pushButtonPurchaseRatesByAgeGroup.setText(_translate("MainWindow", "(2) Purchase rates by age group"))
        self.pushButtonPurchaseCountingByCategory.setText(_translate("MainWindow", "(3) Purchase counting by category"))
        self.pushButtonPurchaseValueByCategory.setText(_translate("MainWindow", "(4) Purchase value by category"))
        self.pushButtonPurchaseByCategoryAndGender.setText(_translate("MainWindow", "(5) Purchases by category and gender"))
        self.pushButtonPaymentMethod.setText(_translate("MainWindow", "(6) Payment method: Cash, Credit, Debit card"))
        self.pushButtonPurchaseRatesByShoppingMall.setText(_translate("MainWindow", "(7) Purchase rates by Shopping Mall"))
        self.pushButtonProductSpendingByGender.setText(_translate("MainWindow", "(8) Products spending by gender"))
        self.pushButtonPurchaseFrequenceByAge.setText(_translate("MainWindow", "(9) Purchase frequency by age"))
        self.pushButtonSalesFluctuationsByMonth.setText(_translate("MainWindow", "(10) Statistics on sales fluctuations by month"))
        self.pushButtonSalesFlucuationsByYearAndMonth.setText(_translate("MainWindow", "(11) Statistics on sales fluctuations by year and month"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab), _translate("MainWindow", "Statistics:"))
        self.groupBox_2.setTitle(_translate("MainWindow", "Choose Independent and Dependent variables:"))
        self.radioButtonGenderAge.setText(_translate("MainWindow", "Gender, Age => Price"))
        self.radioButtonGenderAgePayment.setText(_translate("MainWindow", "Gender, Age, Payment Method=>Price"))
        self.groupBox_6.setTitle(_translate("MainWindow", "Evaluation"))
        self.label_7.setText(_translate("MainWindow", "MAE:"))
        self.label_8.setText(_translate("MainWindow", "MSE:"))
        self.label_9.setText(_translate("MainWindow", "RMSE:"))
        self.label_10.setText(_translate("MainWindow", "R2_SCORE:"))
        self.pushButtonEvaluate.setText(_translate("MainWindow", "Evaluate"))
        self.groupBox_7.setTitle(_translate("MainWindow", "Train Model:"))
        self.lineEditRandomState.setText(_translate("MainWindow", "0"))
        self.label_5.setText(_translate("MainWindow", "Random State:"))
        self.label_4.setText(_translate("MainWindow", "%"))
        self.label_3.setText(_translate("MainWindow", "Test Size:"))
        self.lineEditTestSize.setText(_translate("MainWindow", "20"))
        self.pushButtonTrainModel.setText(_translate("MainWindow", "Train Model"))
        self.groupBox_8.setTitle(_translate("MainWindow", "Save Trained Model"))
        self.label_11.setText(_translate("MainWindow", "Path:"))
        self.pushButtonSavePath.setText(_translate("MainWindow", "..."))
        self.pushButtonSaveModel.setText(_translate("MainWindow", "Save model"))
        self.groupBox_9.setTitle(_translate("MainWindow", "Load trained Model:"))
        self.pushButtonLoadModel.setText(_translate("MainWindow", "Load Model"))
        self.groupBox_10.setTitle(_translate("MainWindow", "Prediction:"))
        self.label_12.setText(_translate("MainWindow", "Gender:"))
        self.label_13.setText(_translate("MainWindow", "Age:"))
        self.label_14.setText(_translate("MainWindow", "Payment Method:"))
        self.pushButtonPredict.setText(_translate("MainWindow", "Predict"))
        self.label_15.setText(_translate("MainWindow", "Predicted Price:"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_2), _translate("MainWindow", "Machine Learning"))
        self.menuSystem.setTitle(_translate("MainWindow", "System"))
        self.menuHelp.setTitle(_translate("MainWindow", "Help"))
        self.actionConnection.setText(_translate("MainWindow", "Connect Database"))
        self.actionConnection.setShortcut(_translate("MainWindow", "Ctrl+D"))
        self.actionSave_trained_Machine_Learning_Model.setText(_translate("MainWindow", "Save trained ML Model"))
        self.actionSave_trained_Machine_Learning_Model.setShortcut(_translate("MainWindow", "Ctrl+S"))
        self.actionLoad_trained_Machine_Learning_Model.setText(_translate("MainWindow", "Load trained ML Model"))
        self.actionLoad_trained_Machine_Learning_Model.setShortcut(_translate("MainWindow", "Ctrl+O"))
        self.actionExit.setText(_translate("MainWindow", "Exit"))
        self.actionExit.setShortcut(_translate("MainWindow", "Esc"))
        self.actionInstructions.setText(_translate("MainWindow", "Instructions"))
        self.actionAbout.setText(_translate("MainWindow", "About"))

import random
from random import random
import plotly.graph_objects as go

from PyQt6 import QtGui, QtCore
from PyQt6.QtCore import Qt, QSize
from PyQt6.QtGui import QAction, QIcon, QPixmap
from PyQt6.QtWidgets import QMessageBox, QTableWidgetItem, QMainWindow, QDialog, QComboBox, QPushButton, QCheckBox, \
    QListWidgetItem, QFileDialog
from matplotlib import pyplot as plt
import seaborn as sns
from matplotlib.backends.backend_qt5agg import FigureCanvasQTAgg as FigureCanvas
from matplotlib.backends.backend_qt5agg import NavigationToolbar2QT as NavigationToolbar

from Connectors.Connector import Connector
from Models.PurchaseLinearRegression import PurchaseLinearRegression
from Models.PurchaseStatistic import PurchaseStatistic
from UI.ChartHandle import ChartHandle
from UI.DatabaseConnectEx import DatabaseConnectEx
from UI.MainWindow import Ui_MainWindow
import traceback


import matplotlib

from matplotlib.backends.backend_qt5agg import FigureCanvasQTAgg
from matplotlib.figure import Figure


from matplotlib.backends.backend_qt5agg import NavigationToolbar2QT as NavigationToolbar
import random


class MainWindowEx(Ui_MainWindow):
    def __init__(self):
        self.purchaseLinearRegression = PurchaseLinearRegression()
        self.databaseConnectEx=DatabaseConnectEx()
        self.databaseConnectEx.parent=self
        self.chartHandle= ChartHandle()
    def setupUi(self, MainWindow):
        super().setupUi(MainWindow)
        self.MainWindow=MainWindow
        self.verticalLayoutFunctions.setAlignment(Qt.AlignmentFlag.AlignTop)
        self.setupPlot()

        self.actionConnection.triggered.connect(self.openDatabaseConnectUI)

        self.pushButtonPurchaseRatesByGender.clicked.connect(self.showPurchaseRatesByGender)
        self.pushButtonSalesFlucuationsByYearAndMonth.clicked.connect(self.showSalesFlucuationsByYearAndMonth)
        self.pushButtonPurchaseCountingByCategory.clicked.connect(self.showPurchaseCountingByCategory)
        self.pushButtonPurchaseRatesByAgeGroup.clicked.connect(self.showPurchaseRatesByAgeGroup)
        self.pushButtonPurchaseCountingByCategory.clicked.connect(self.showPurchaseCountingByCategory)
        self.pushButtonPurchaseValueByCategory.clicked.connect(self.showPurchaseValueByCategory)
        self.pushButtonPurchaseByCategoryAndGender.clicked.connect(self.showPurchaseByCategoryAndGender)
        self.pushButtonPaymentMethod.clicked.connect(self.showPaymentMethod)
        self.pushButtonPurchaseRatesByShoppingMall.clicked.connect(self.showPurchaseRatesByShoppingMall)
        self.pushButtonProductSpendingByGender.clicked.connect(self.showProductSpendingByGender)
        self.pushButtonPurchaseFrequenceByAge.clicked.connect(self.showShowPurchaseFrequenceByAge)
        self.pushButtonSalesFluctuationsByMonth.clicked.connect(self.showpushButtonSalesFluctuationsByMonth)
        self.checkEnableWidget(False)

        self.pushButtonTrainModel.clicked.connect(self.processTrainModel)
        self.pushButtonEvaluate.clicked.connect(self.processEvaluateTrainedModel)
        self.pushButtonSavePath.clicked.connect(self.processPickSavePath)
        self.pushButtonSaveModel.clicked.connect(self.processSaveTrainedModel)
        self.pushButtonLoadModel.clicked.connect(self.processLoadTrainedModel)
        self.pushButtonPredict.clicked.connect(self.processPrediction)
    def show(self):
        self.MainWindow.show()
    def checkEnableWidget(self,flag=True):
        self.pushButtonPurchaseRatesByGender.setEnabled(flag)
        self.pushButtonPurchaseRatesByAgeGroup.setEnabled(flag)
        self.pushButtonPurchaseCountingByCategory.setEnabled(flag)
        self.pushButtonPurchaseValueByCategory.setEnabled(flag)
        self.pushButtonPurchaseByCategoryAndGender.setEnabled(flag)
        self.pushButtonPaymentMethod.setEnabled(flag)
        self.pushButtonPurchaseRatesByShoppingMall.setEnabled(flag)

        self.pushButtonProductSpendingByGender.setEnabled(flag)
        self.pushButtonPurchaseFrequenceByAge.setEnabled(flag)
        self.pushButtonSalesFluctuationsByMonth.setEnabled(flag)
        self.pushButtonSalesFlucuationsByYearAndMonth.setEnabled(flag)

    def setupPlot(self):
        self.figure = plt.figure()
        self.canvas = FigureCanvas(self.figure)
        self.toolbar = NavigationToolbar(self.canvas, self.MainWindow)

        # adding tool bar to the layout
        self.verticalLayoutPlot.addWidget(self.toolbar)
        # adding canvas to the layout
        self.verticalLayoutPlot.addWidget(self.canvas)
    def openDatabaseConnectUI(self):
        dbwindow = QMainWindow()
        self.databaseConnectEx.setupUi(dbwindow)
        self.databaseConnectEx.show()
    def showDataIntoTableWidget(self,df):
        self.tableWidgetStatistic.setRowCount(0)
        self.tableWidgetStatistic.setColumnCount(len(df.columns))
        for i in range(len(df.columns)):
            columnHeader = df.columns[i]
            self.tableWidgetStatistic.setHorizontalHeaderItem(i, QTableWidgetItem(columnHeader))
        row = 0
        for item in df.iloc:
            arr = item.values.tolist()
            self.tableWidgetStatistic.insertRow(row)
            j=0
            for data in arr:
                self.tableWidgetStatistic.setItem(row, j, QTableWidgetItem(str(data)))
                j=j+1
            row = row + 1

    def showPurchaseCountingByCategory(self):
        self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        self.purchaseLinearRegression.execPurchaseHistory()
        self.purchaseLinearRegression.processCategoryDistribution()
        print(self.purchaseLinearRegression.dfCategory)

        df = self.purchaseLinearRegression.dfCategory

        self.showDataIntoTableWidget(df)

        columnLabel = "category"
        columnStatistic = "count"
        title = "Categories Distribution"
        legend = False
        #self.visualizePieChart(df, columnLabel, columnStatistic, title, legend)
        self.chartHandle.visualizePieChart(self.figure,self.canvas,df, columnLabel, columnStatistic, title, legend)

    def showPurchaseRatesByGender(self):
        self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        self.purchaseLinearRegression.execPurchaseHistory()
        self.purchaseLinearRegression.processGenderDistribution()
        print(self.purchaseLinearRegression.dfGender)

        df = self.purchaseLinearRegression.dfGender

        self.showDataIntoTableWidget(df)

        columnLabel = "gender"
        columnStatistic = "count"
        title = "Gender Distribution"
        legend = True
        self.chartHandle.visualizePieChart(self.figure,self.canvas,df, columnLabel, columnStatistic, title, legend)
        #self.visualizePieChart(df, columnLabel, columnStatistic, title, legend)

    def showSalesFlucuationsByYearAndMonth(self):
        self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        self.purchaseLinearRegression.execPurchaseHistory()
        self.purchaseLinearRegression.processMonthlyAndYearSalesAmount()
        print(self.purchaseLinearRegression.dfMonthlyAndYearSalesAmount)
        df = self.purchaseLinearRegression.dfMonthlyAndYearSalesAmount
        self.showDataIntoTableWidget(df)
        self.chartHandle.visualizeLinePlotChart(self.figure,self.canvas, self.purchaseLinearRegression.dfMonthlyAndYearSalesAmount, "month", "sales_amount",
                                    "Monthly Variation in Sales Amount Over Years", hue="year", xticks=True)
        #self.visualizeLinePlotChart(self.purchaseLinearRegression.dfMonthlyAndYearSalesAmount, "month", "sales_amount",
        #                            "Monthly Variation in Sales Amount Over Years", hue="year", xticks=True)
    def showPurchaseRatesByAgeGroup(self):
        self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        self.purchaseLinearRegression.execPurchaseHistory()
        fromAge=int(self.lineEditFromAge.text())
        toAge=int(self.lineEditToAge.text())
        self.purchaseLinearRegression.processAgeDistribution(fromAge,toAge)
        print(self.purchaseLinearRegression.dfAges)

        df = self.purchaseLinearRegression.dfAges

        self.showDataIntoTableWidget(df)
        columnLabel= "age"
        columnStatistic ="count"
        title= "Age Distribution %s~%s"%(fromAge,toAge)
        hue = None
        self.chartHandle.visualizeLinePlotChart(self.figure,self.canvas,df, columnLabel, columnStatistic,title, hue)
        #self.visualizeLinePlotChart(df, columnLabel, columnStatistic,title, hue)
    def showPurchaseCountingByCategory(self):
        self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        self.purchaseLinearRegression.execPurchaseHistory()
        df = self.purchaseLinearRegression.processCategoryDistribution()
        self.showDataIntoTableWidget(df)
        columnLabel = "category"
        columnStatistic = "count"
        title = "Categories Distribution"
        legend = False
        hue=None
        self.chartHandle.visualizeLinePlotChart(self.figure,self.canvas,df, columnLabel, columnStatistic, title, hue)
        #self.visualizePieChart(df, columnLabel, columnStatistic, title, legend)
    def showPurchaseValueByCategory(self):
        # self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        # self.purchaseLinearRegression.execPurchaseHistory()
        df = self.purchaseLinearRegression.processCategorySpending()
        self.showDataIntoTableWidget(df)
        columnLabel = "category"
        columnStatistic = "price"
        title = "Distribution category and Spending"
        self.chartHandle.visualizeBarChart(self.figure,self.canvas,df,columnLabel,columnStatistic,title)
        #self.visualizeBarChart(df,columnLabel,columnStatistic,title)
    def showPurchaseByCategoryAndGender(self):
        # self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        # self.purchaseLinearRegression.execPurchaseHistory()
        df = self.purchaseLinearRegression.processGenderAndCategoryCounter()
        self.showDataIntoTableWidget(df)
        df=self.purchaseLinearRegression.df
        columnLabel = "category"
        columnStatistic = "count"
        hue="gender"
        title = "Distribution gender and category"
        self.chartHandle.visualizeMultiBarChart(self.figure,self.canvas,df, columnLabel, columnStatistic,hue, title)
        #self.visualizeMultiBarChart(df, columnLabel, columnStatistic,hue, title)
    def showPaymentMethod(self):
        # self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        # self.purchaseLinearRegression.execPurchaseHistory()
        df = self.purchaseLinearRegression.processPaymentMethod()
        self.showDataIntoTableWidget(df)
        columnLabel = "payment_method"
        columnStatistic = "count"
        title = "Payment Distribution"
        legend = False
        self.chartHandle.visualizePieChart(self.figure,self.canvas,df, columnLabel, columnStatistic, title, legend)
        #self.visualizePieChart(df, columnLabel, columnStatistic, title, legend)
    def showPurchaseRatesByShoppingMall(self):
        # self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        # self.purchaseLinearRegression.execPurchaseHistory()
        df = self.purchaseLinearRegression.processShoppingMall()
        self.showDataIntoTableWidget(df)
        columnLabel = "shopping_mall"
        columnStatistic = "count"
        title = "Shopping Mall Distribution"
        legend = False
        self.chartHandle.visualizePieChart(self.figure,self.canvas,df, columnLabel, columnStatistic, title, legend)
        #self.visualizePieChart(df, columnLabel, columnStatistic, title, legend)
    def showProductSpendingByGender(self):
        # self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        # self.purchaseLinearRegression.execPurchaseHistory()
        df = self.purchaseLinearRegression.processGenderCategorySpending()
        self.showDataIntoTableWidget(df)
        columnLabel = "category"
        columnStatistic = "price"
        hue="gender"
        title = "Male and Female category Total Price Spend"
        legend = False
        self.chartHandle.visualizeBarPlot(self.figure,self.canvas,df, columnLabel, columnStatistic,hue, title)
        #self.visualizeBarPlot(df, columnLabel, columnStatistic,hue, title)

    def showShowPurchaseFrequenceByAge(self):
        # self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        # self.purchaseLinearRegression.execPurchaseHistory()
        df = self.purchaseLinearRegression.processAgeOrderFrequence()
        self.showDataIntoTableWidget(df)
        columnLabel = "age"
        columnStatistic = "count"
        title = "Age VS Order Frequence"
        self.chartHandle.visualizeScatterPlot(self.figure,self.canvas,df, columnLabel,columnStatistic, title)
        #self.visualizeScatterPlot(df, columnLabel,columnStatistic, title)

    def showpushButtonSalesFluctuationsByMonth(self):
        # self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        # self.purchaseLinearRegression.execPurchaseHistory()

        df=self.purchaseLinearRegression.processMonthlySalesAmount()
        print(df)

        self.showDataIntoTableWidget(df)
        columnLabel = "month"
        columnStatistic = "sales_amount"
        title = "Monthly Variation in Sales Amount"
        hue = None
        self.chartHandle.visualizeLinePlotChart(self.figure,self.canvas,df, columnLabel, columnStatistic, title, hue)
    def processTrainModel(self):
        columns_input=["gender","age"]
        column_target="price"
        if self.radioButtonGenderAgePayment.isChecked():
            columns_input=["gender","age","payment_method"]
        test_size=float(self.lineEditTestSize.text())/100
        random_state=int(self.lineEditRandomState.text())
        self.purchaseLinearRegression = PurchaseLinearRegression()
        self.purchaseLinearRegression.connector = self.databaseConnectEx.connector
        self.purchaseLinearRegression.processTrain(
            columns_input,
            column_target,
            test_size,
            random_state)
        dlg = QMessageBox(self.MainWindow)
        dlg.setWindowTitle("Info")
        dlg.setIcon(QMessageBox.Icon.Information)
        dlg.setText("Train machine learning model successful!")
        buttons = QMessageBox.StandardButton.Yes
        dlg.setStandardButtons(buttons)
        button = dlg.exec()
    def processEvaluateTrainedModel(self):
        result = self.purchaseLinearRegression.evaluate()
        self.lineEditMAE.setText(str(result.MAE))
        self.lineEditMSE.setText(str(result.MSE))
        self.lineEditRMSE.setText(str(result.RMSE))
        self.lineEditR2SCore.setText(str(result.R2_SCORE))
    def processPickSavePath(self):
        filters = "trained model file (*.zip);;All files(*)"
        filename, selected_filter = QFileDialog.getSaveFileName(
            self.MainWindow,
            filter=filters,
        )
        self.lineEditPath.setText(filename)
    def processSaveTrainedModel(self):
        trainedModelPath=self.lineEditPath.text()
        if trainedModelPath=="":
            return
        ret = self.purchaseLinearRegression.saveModel(trainedModelPath)
        dlg = QMessageBox(self.MainWindow)
        dlg.setWindowTitle("Info")
        dlg.setIcon(QMessageBox.Icon.Information)
        dlg.setText(f"Saved Trained machine learning model successful at [{trainedModelPath}]!")
        buttons = QMessageBox.StandardButton.Yes
        dlg.setStandardButtons(buttons)
        button = dlg.exec()
    def processLoadTrainedModel(self):
        # setup for QFileDialog
        filters = "trained model file (*.zip);;All files(*)"
        filename, selected_filter = QFileDialog.getOpenFileName(
            self.MainWindow,
            filter=filters,
        )
        if filename=="":
            return
        self.lineEditLocationLoadTrainedModel.setText(filename)
        self.purchaseLinearRegression.loadModel(filename)
        dlg = QMessageBox(self.MainWindow)
        dlg.setWindowTitle("Info")
        dlg.setIcon(QMessageBox.Icon.Information)
        dlg.setText(f"Load Trained machine learning model successful from [{filename}]!")
        buttons = QMessageBox.StandardButton.Yes
        dlg.setStandardButtons(buttons)
        button = dlg.exec()
    def processPrediction(self):
        gender = self.lineEditGender.text()
        age = int(self.lineEditAge.text())
        payment = self.lineEditPaymentMethod.text()
        if len(self.purchaseLinearRegression.trainedmodel.columns_input)==3:
            predicted_price = self.purchaseLinearRegression.predictPriceFromGenderAndAgeAndPayment(gender, age, payment)
        else:
            predicted_price = self.purchaseLinearRegression.predictPriceFromGenderAndAge(gender, age)
        self.lineEditPredictedPrice.setText(str(predicted_price[0]))

from PyQt6.QtWidgets import QApplication, QMainWindow

from UI.MainWindowEx import MainWindowEx

qApp=QApplication([])
qmainWindow=QMainWindow()
window=MainWindowEx()
window.setupUi(qmainWindow)
window.show()
qApp.exec()