from PyQt5 import QtCore, QtWidgets
import sys
import pymysql
import dp_copy.rczy_sql as rczysql
import dp_copy.wccyr_sql as wccyrsql


class Ui_ANOVAWindow(object):


    def setupUi(self, ANOVAWindow):
        ANOVAWindow.setObjectName("ANOVAWindow")
        ANOVAWindow.resize(600, 400)
        self.label = QtWidgets.QLabel(ANOVAWindow)
        self.centralwidget = QtWidgets.QWidget(ANOVAWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.label.setGeometry(QtCore.QRect(30, 40, 70, 30))  # 字体
        self.label.setMinimumSize(QtCore.QSize(71, 31))
        self.label.setObjectName("label")

        self.label_2 = QtWidgets.QLabel(self.centralwidget)
        self.label_2.setGeometry(QtCore.QRect(30, 80, 70, 30))
        self.label_2.setMinimumSize(QtCore.QSize(71, 31))
        self.label_2.setObjectName("label_2")

        self.label_3 = QtWidgets.QLabel(self.centralwidget)
        self.label_3.setGeometry(QtCore.QRect(300, 10, 70, 30))
        self.label_3.setMinimumSize(QtCore.QSize(71, 31))
        self.label_3.setObjectName("label_3")

        self.KeywordBox = QtWidgets.QLineEdit(self.centralwidget)
        self.KeywordBox.setGeometry(QtCore.QRect(130, 40, 120, 30))  # keyword框
        self.KeywordBox.setObjectName("KeywordBox")

        self.KeywordBox1 = QtWidgets.QLineEdit(self.centralwidget)
        self.KeywordBox1.setGeometry(QtCore.QRect(130, 80, 120, 30))  # keyword框
        self.KeywordBox1.setObjectName("KeywordBox1")

        self.result_KeywordBox = QtWidgets.QLineEdit(self.centralwidget)
        self.result_KeywordBox.setGeometry(QtCore.QRect(300, 40, 300, 200))  # keyword框
        self.result_KeywordBox.setObjectName("KeywordBox2")

        self.DataCButton = QtWidgets.QPushButton(self.centralwidget)  # 按钮
        self.DataCButton.setGeometry(QtCore.QRect(130, 120, 121, 30))
        self.DataCButton.setObjectName("DataCButton")

        ANOVAWindow.setCentralWidget(self.centralwidget)
        self.statusbar = QtWidgets.QStatusBar(ANOVAWindow)
        self.statusbar.setObjectName("statusbar")
        ANOVAWindow.setStatusBar(self.statusbar)

        self.retranslateUi(ANOVAWindow)
        QtCore.QMetaObject.connectSlotsByName(ANOVAWindow)

    def retranslateUi(self, ANOVAWindow):
        _translate = QtCore.QCoreApplication.translate
        ANOVAWindow.setWindowTitle(_translate("ANOVAWindow", "数据比对器"))  # 标题名
        self.label.setText(_translate("ANOVAWindow", "<html><head/><body><p><span style=\" font-size:11pt;\">项目名称</span></p></body></html>"))
        self.label_2.setText(_translate("ANOVAWindow", "<html><head/><body><p><span style=\" font-size:11pt;\">数据名称</span></p></body></html>"))
        self.label_3.setText(_translate("ANOVAWindow", "<html><head/><body><p><span style=\" font-size:11pt;\">查询结果</span></p></body></html>"))
        self.DataCButton.setText(_translate("ANOVAWindow", "查询"))

    def setupFunction(self):
        self.DataCButton.clicked.connect(self.get_sum)
        # self.get_sum()

    def get_sum(self):
        self.project_name = self.KeywordBox.text()  # 获取第一个文本框中的内容存入adder1
        self.data_name = self.KeywordBox1.text()  # 获取第二个文本框中的内容存入adder2
        print(self.project_name, self.data_name)
        if self.data_name:
            self.index(self.project_name, self.data_name)

    def index(self, project, name):
        print(project, name)
        if name not in wccyrsql.data_dict or name not in rczysql.data_dict:
            resp = '查询数据名错误'
            print(resp)
            self.result_KeywordBox.setText(resp)
            return resp
        else:
            if project == 'wccyr':
                conn = pymysql.connect(host='172.17.33.195', port=3306, db='wccyr_test', user='wuchetest', password='wuchetest2019#', charset='utf8')
                dict = {name: rczysql.data_dict[name]}
            elif project == 'rczy':
                conn = pymysql.connect(host='172.17.33.195', port=3306, db='rczy', user='etims', password='etims2018#', charset='utf8')
                dict = {name: wccyrsql.data_dict[name]}
            else:
                resp = '项目名错误'
                print(resp)
                self.result_KeywordBox.setText(resp)
                return resp
            cur = conn.cursor()
            sql = dict.get(name)[1]
            cur.execute(sql, dict.get(name)[0])
            resp = cur.fetchall()
            print(resp)
            self.result_KeywordBox.setText(str(resp))
            cur.close()
            return resp


if __name__ == "__main__":
    app =QtWidgets.QApplication(sys.argv)   # 创建一个QApplication,也就是你要开发的软件app
    ANOVAWindow = QtWidgets.QMainWindow()   # 创建一个ANOVAWindow,用来装载你需要的各种组件、控件
    ui = Ui_ANOVAWindow()
    ui.setupUi(ANOVAWindow)  # 执行类中的setupUi方法,方法的参数是第二步中创建的QMainWindow
    ui.setupFunction()  # 执行类中的setupFunction方法
    ANOVAWindow.show()  # 执行QMainWindow的show()方法,显示这个QMainWindow
    sys.exit(app.exec_())  # 使用exit()或者点击关闭按钮退出QApplication
import datetime

current = datetime.datetime.strptime(datetime.datetime.now().strftime("%Y-%m-%d"), "%Y-%m-%d")
before_year = current - datetime.timedelta(days=365)
before_mouth = current - datetime.timedelta(days=30)
current_time = current.strftime("%Y-%m-%d %H:%M:%S")
before_year_time = before_year.strftime("%Y-%m-%d %H:%M:%S")
before_mouth_time = before_mouth.strftime("%Y-%m-%d %H:%M:%S")

org_seq = '/root/蓝物流/'

ndljyf_sql = '''SELECT count(*), sum(b.payment_fee) FROM `tms_transport` a
 left join tb_tms_transport_fee b
 on a.id = b.transport_id
 where a.org_seq like %s
 and a.fact_reach_date BETWEEN %s and %s
 and a.status in (3, 4, 5)
 and a.del_flag = 0;'''


ndljydl_sql = '''SELECT count(*) FROM tms_transport
 where org_seq like %s
 and fact_reach_date BETWEEN %s and %s
 and status in (3, 4, 5)
 and del_flag = 0;'''


ndljyf_parmas = [org_seq, before_year_time, current_time]
ndljydl_parmas = [org_seq, before_year_time, current_time]

data_dict = {
    'ndljyf': [ndljyf_parmas, ndljyf_sql],
    'ndljydl': [ndljydl_parmas, ndljydl_sql]
}
import datetime

current = datetime.datetime.strptime(datetime.datetime.now().strftime("%Y-%m-%d"), "%Y-%m-%d")
before_year = current - datetime.timedelta(days=365)
before_mouth = current - datetime.timedelta(days=30)
current_time = current.strftime("%Y-%m-%d %H:%M:%S")
before_year_time = before_year.strftime("%Y-%m-%d %H:%M:%S")
before_mouth_time = before_mouth.strftime("%Y-%m-%d %H:%M:%S")

org_seq = '/root/蓝物流/%'

ndljyf_sql = '''SELECT count(*), sum(b.payment_fee) FROM `tms_transport` a
 left join tb_tms_transport_fee b
 on a.id = b.transport_id
 where a.org_seq like %s
 and  a.sign_date BETWEEN %s and %s
 and a.status in (3, 4, 5)
 and a.del_flag = 0;'''


ndljydl_sql = '''SELECT count(*) FROM tms_transport
 where org_seq like %s
 and fact_reach_date BETWEEN %s and %s
 and status in (3, 4, 5)
 and del_flag = 0;'''


ndljyf_parmas = [org_seq, before_year_time, current_time]
ndljydl_parmas = [org_seq, before_year_time, current_time]

data_dict = {
    'ndljyf': [ndljyf_parmas, ndljyf_sql],
    'ndljydl': [ndljydl_parmas, ndljydl_sql]
}