from flask import Flask, render_template,request,url_for, redirect
import pymysql
import pandas as pd
import csv

app = Flask(__name__)



# MySQL configurations
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='g53743001',
    db='erptest',
    charset='utf8'
)


@app.route('/Inquire_from/',methods=['GET','POST'])
def Inquire_from():
    cur1 = conn.cursor()
    # 獲取欄位資料
    sql1 = "select DISTINCT 產品 from abc"
    cur1.execute(sql1)
    labels1 = cur1.fetchall()
    labels1 = [g[0] for g in labels1]
    #print(labels1)
    
    cur2 = conn.cursor()
    # 獲取欄位資料
    sql2 = "select DISTINCT 系統 from abc"
    cur2.execute(sql2)
    labels2 = cur2.fetchall()
    labels2 = [g[0] for g in labels2]
    #print(labels2)
   
    cur3 = conn.cursor()
    # 獲取欄位資料
    sql3 = "select DISTINCT 模組圖名 from abc"
    cur3.execute(sql3)
    labels3 = cur3.fetchall()
    labels3 = [g[0] for g in labels3]
    #print(labels3)
   
    cur4 = conn.cursor()
    # 獲取欄位資料
    sql4 = "select DISTINCT 零件圖名 from abc"
    cur4.execute(sql4)
    labels4 = cur4.fetchall()
    labels4 = [g[0] for g in labels4]
    #print(labels4)
    

    cur5 = conn.cursor()
    # 獲取欄位資料
    sql5 = "select DISTINCT 系統 from abc WHERE 產品 = '乾燥貨櫃' "
    cur5.execute(sql5)
    labels5 = cur5.fetchall()
    labels5 = [g[0] for g in labels5]
    print(labels5)


    if request.method == 'POST':
        sql = "SHOW FIELDS FROM abc"
        cur1.execute(sql)
        labels = cur1.fetchall()
        labels = [g[0] for g in labels]
        # ----------------------------------------#

        t1 = request.values['Test1']
        t2 = request.values['Test2']
        t3 = request.values['Test3']
        t4 = request.values['Test4']
        # print(t1)
        # print(t2)
        # print(t3)
        # print(t4)


        cur = conn.cursor()

        #
        data = 'select * from abc'
        if t1 == '' and t2 == '' and t3 == '' and t4 == '':
            pass
        else:
            data += ' WHERE '
            if t1 != '':
                data += '產品 = \'' + t1 + '\' '
                if t2 != '':
                    data += ' AND '
                elif t3 != '':
                    data += ' AND '
                elif t4 != '':
                    data += ' AND '
            if t2 != '':
                data += '系統 = \'' + t2 + '\' '
                if t3 != '':
                    data += ' AND '
                elif t4 != '':
                    data += ' AND '

            if t3 != '':
                data += '模組圖名 = \'' + t3 + '\' '
                if t4 != '':
                    data += ' AND '
            if t4 != '': data += '零件圖名 = \'' + t4 + '\' '

        print(data)
        # 進資料庫
        cur.execute(data)




        content = cur.fetchall()
        # print(content)
        return render_template('Inquire1.html', content=content, labels=labels)

    return render_template('two_list.html',labels1=labels1,labels2=labels2,labels3=labels3,labels4=labels4,labels5=labels5)





if __name__ == '__main__':
    app.run(debug=True,port=5050)