import pandas as pd
import time
import sys
from PyQt5 import QtWidgets
from PyQt5.QtWidgets import QApplication, QFileDialog, QMainWindow
from PyQt5.QtWidgets import QWidget, QMessageBox, QInputDialog
from db import myHdfsClient, myHiveConn
from visual import Ui_MainWindow
import os
os.environ["CUDA_VISIBLE_DEVICES"] = "-1"


class myWindow(QMainWindow, Ui_MainWindow):

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.setupUi(self)
        self.setFixedSize(870, 542)
        self.stackedWidget.setCurrentIndex(0)
        self.lineEdit_3.show()
        self.canDown = False

        self.client = myHdfsClient(hosts='139.9.238.165:50070', user_name='hadoop')
        self.hiveCon = myHiveConn(host='139.9.238.165', port=10000, username='hadoop', database='dblab')

        # 连接槽与槽函数
        self.pushButton.clicked.connect(self.btn_cliked)
        self.pushButton_2.clicked.connect(self.btn2_cliked)
        self.pushButton_3.clicked.connect(self.btn3_cliked)
        self.pushButton_4.clicked.connect(self.btn4_cliked)
        self.pushButton_5.clicked.connect(self.btn5_cliked)
        self.pushButton_6.clicked.connect(self.btn6_cliked)
        self.pushButton_7.clicked.connect(self.btn7_cliked)
        self.pushButton_8.clicked.connect(self.btn8_cliked)
        self.pushButton_9.clicked.connect(self.btn9_cliked)
        self.pushButton_10.clicked.connect(self.btn10_cliked)
        self.pushButton_11.clicked.connect(self.btn11_cliked)
        self.pushButton_13.clicked.connect(self.btn13_cliked)
        self.comboBox.currentTextChanged.connect(self.cbChanged)

        # 查询页面的页码
        self.currentPage = 0

    # "上传"按钮槽函数
    def btn_cliked(self):
        self.stackedWidget.setCurrentIndex(0)

    # "下载"按钮槽函数
    def btn2_cliked(self):
        self.stackedWidget.setCurrentIndex(1)

        # 在大文字框中显示所有文件
        ls_sta = self.client.list_status()
        self.textEdit_2.clear()
        self.textEdit_2.append("Owner\tGroup\t\tSize(B)\tLast Modified\tName")
        for ls in ls_sta:
            # ls['modificationTime'] 是13位毫秒级时间戳，t/1000才能作为localtime()的参数
            t = time.localtime(float(ls['modificationTime'] / 1000))
            self.textEdit_2.append(str(ls['owner']) + '\t'
                                  + str(ls['group']) + '\t'
                                   + str(ls['length']) + '\t'
                                   + str(t.tm_year) + '/' + str(t.tm_mon) + '/' + str(t.tm_mday) + ' ' + str(t.tm_hour) + ':' + str(t.tm_min) + '\t'
                                   + str(ls['pathSuffix']))

    # "查询"按钮槽函数
    def btn3_cliked(self):
        self.stackedWidget.setCurrentIndex(2)

    # "推荐查询"按钮
    def bnt10_cliked(self):
        self.stackedWidget.setCurrecntIndex(3)

    # "选择文件"按钮槽函数
    def btn4_cliked(self):
        filePath, filetype = QFileDialog.getOpenFileName(self.centralwidget, "选择文件", "C:\\Users\\nhf17\\Desktop","All(*.csv *.txt);;(csv(*.csv);;txt(*.txt)")

        if len(filePath) > 0:
            # 把文件路径打印到小文字框中
            self.lineEdit.setText(filePath)
            # 把文件内容打印到大文字框中
            self.textEdit.clear()
            with open(filePath, encoding='utf-8') as file:
                for line in file:
                    self.textEdit.append(line)

    def cbChanged(self):
        if self.comboBox.currentText() == 'visit_date':
            self.lineEdit_3.close()
        else:
            self.lineEdit_3.show()

    # 查询界面"确定"按钮槽函数
    def btn5_cliked(self):
        # 获取查询关键字和属性
        attribute = self.comboBox.currentText()
        value = self.lineEdit_3.text()
        if attribute == 'visit_date':
            s, e = self.dateEdit.date(), self.dateEdit_2.date()
            value = "'{}-{}-{}'".format(s.year(), s.month(), s.day()) + "?" + "'{}-{}-{}'".format(e.year(), e.month(), e.day())

        # hive 执行查询
        self.hiveCon.select(attribute, value)

        # 把查询结果打印在大文字框内
        self.textEdit_3.clear()
        self.textEdit_3.append("id   uid   item_id   behavior_type   item_category   visit_date   province")
        self.currentPage = 0
        res = self.hiveCon.page_at(0)
        for row in res:
            self.textEdit_3.append(str(row))
        self.textEdit_3.append('共计' + str(len(self.hiveCon.data)) + '条记录')

    # "确定上传"按钮槽函数
    def btn6_cliked(self):
        path = self.lineEdit.text()  # 获取要上传的文件名
        if len(path) > 0:
            if self.hiveCon.loadData(self.client, path):
                print("load successfully")
            else:
                QMessageBox.information(self, "error", "文件已存在,请更改文件名")
        else:
            QMessageBox.information(self, "error", "请选择文件！")

    # 下载界面"确定下载"按钮槽函数
    def btn7_cliked(self):
        fileName = self.lineEdit_2.text()
        if len(fileName) > 0:
            locPath, ft = QFileDialog.getSaveFileName(self, "文件保存", "C:\\Users\\nhf17\\Desktop\\" + fileName, "All(*)")
            res = self.client.downLoad(fileName, locPath)
            if res:
                print("Download successfully")
            else:
                QMessageBox.information(self, "error", "文件不存在")

    # 查询页面 "下一页" 按钮
    def btn8_cliked(self):
        # 查询结果打印在大文字框内
        self.textEdit_3.clear()
        self.textEdit_3.append("id   uid\item_id   behavior_type   item_category   visit_date   province")

        if (self.currentPage+1)*24 < len(self.hiveCon.data):
            self.currentPage += 1

        res = self.hiveCon.page_at(self.currentPage)

        for row in res:
            self.textEdit_3.append(str(row))
        self.textEdit_3.append('共计' + str(len(self.hiveCon.data)) + '条记录')

    # 查询页面 "上一页" 按钮
    def btn9_cliked(self):
        # 查询结果打印在大文字框内
        self.textEdit_3.clear()
        self.textEdit_3.append("id   uid\item_id   behavior_type   item_category   visit_date   province")

        if self.currentPage > 0:
            self.currentPage -= 1

        res = self.hiveCon.page_at(self.currentPage)

        for row in res:
            self.textEdit_3.append(str(row))
        self.textEdit_3.append('共计' + str(len(self.hiveCon.data)) + '条记录')

    # 查询界面 "下载查询结果" 按钮
    def btn13_cliked(self):
        locPath, ft = QFileDialog.getSaveFileName(self, "文件保存", "C:\\Users\\nhf17\\Desktop\\", "excel(*.xlsx)")
        df = pd.DataFrame(self.hiveCon.data,
                          index=[i for i in range(len(self.hiveCon.data))],
                          columns=['id','uid','item_id','behavior_type','item_category','visit_date','province'])
        df.to_excel(locPath)

    # 推荐查询
    def btn10_cliked(self):
        self.stackedWidget.setCurrentIndex(3)
        res = self.hiveCon.show_recommendation()
        self.textEdit_4.clear()
        self.textEdit_4.append("uid\titem_category")
        for line in res:
            self.textEdit_4.append(str(line))

    # "推荐查询"界面查询按钮
    def btn11_cliked(self):
        # 获取输入的 uid
        uid = self.lineEdit_4.text()
        if len(uid) > 0:
            res = self.hiveCon.query(uid)
            self.textEdit_4.clear()
            self.textEdit_4.append("uid\titem_category")
            for line in res:
                self.textEdit_4.append(uid + '的推荐商品种类为:\n' + str(line))


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

"""
优化方向：
    1.查询时可限定多个属性值
    2.增加hdfs更换目录功能，hive更换数据库与表功能
"""