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"
    cur5.execute(sql5)
    labels5 = cur5.fetchall()
    labels5 = [g[0] for g in labels5]
    print(labels5)

    cur6 = conn.cursor()
    # 獲取欄位資料
    sql6 = "select DISTINCT 系統 from abc"
    cur6.execute(sql6)
    labels6 = cur6.fetchall()
    labels6 = [g[0] for g in labels6]
    print(labels6)

    cur7 = conn.cursor()
    # 獲取欄位資料
    sql7 = "select DISTINCT 模組圖名 from abc"
    cur7.execute(sql7)
    labels7 = cur7.fetchall()
    labels7 = [g[0] for g in labels7]
    print(labels7)

    cur8 = conn.cursor()
    # 獲取欄位資料
    sql8 = "select DISTINCT 零件圖名 from abc"
    cur8.execute(sql8)
    labels8 = cur8.fetchall()
    labels8 = [g[0] for g in labels8]
    print(labels8)

    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']
        t5 = request.values['Test5']
        t6 = request.values['Test6']
        t7 = request.values['Test7']
        t8 = request.values['Test8']
        #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)
        data1 = 'select * from abc'
        if t5 == '' and t6 == '' and t7 == '' and t8 == '':
            pass
        else:
            data += ' WHERE '
            if t5 != '':
                data += '產品 = \'' + t5 + '\' '
                if t6 != '':
                    data += ' AND '
                elif t7 != '':
                    data += ' AND '
                elif t8 != '':
                    data += ' AND '
            if t6 != '':
                data += '系統 = \'' + t6 + '\' '
                if t7 != '':
                    data += ' AND '
                elif t8 != '':
                    data += ' AND '

            if t7 != '':
                data += '模組圖名 = \'' + t7 + '\' '
                if t8 != '':
                    data += ' AND '
            if t8 != '':
                data += '零件圖名 = \'' + t8 + '\' '

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

        return render_template('Inquire1.html', content=content, labels=labels)

    return render_template('Inquire.html',labels1=labels1,labels2=labels2,labels3=labels3,labels4=labels4,
                           labels5=labels5,labels6=labels6,labels7=labels7,labels8=labels8)





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