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


app = Flask(__name__)

conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='g53743001',
    db='erp',
    charset='utf8'
)


@app.route('/')
def index():
    return render_template('index.html')


@app.route('/submit/')
def form():
    return render_template('update.html')

#新增產品清單內容
@app.route('/add_Nu', methods=['POST', 'GET'])
def add_Nu():

    if request.method == 'POST':
    
        會計科目 = request.form['會計科目']
        名稱1 = request.form['名稱1']
        類別 = request.form['類別']
        名稱2 = request.form['名稱2']
        廠商 = request.form['廠商']
        名稱3 = request.form['名稱3']
        if 會計科目 == '' and 名稱1 == '' and 類別 == '' and 名稱2 == ''and 廠商 == ''and 名稱3 == '':
            print('都沒輸入，直接略過')
            pass
        else:
            if 會計科目 != '' and 名稱1 != '':
                cur = conn.cursor()

                cur.execute(''' INSERT INTO accounting_subjects VALUES(%s,%s)'''
                               ,(會計科目, 名稱1))
                conn.commit()
                cur.close()
            else:
                print('accounting_subjects error')
            if 類別 != '' and 名稱2 != '':
                cur = conn.cursor()

                cur.execute(''' INSERT INTO category VALUES(%s,%s)'''
                               ,(類別, 名稱2))
                conn.commit()
                cur.close()
            else:
                print('category error')
            if 廠商 != ''and 名稱3 != '':
                cur = conn.cursor()

                cur.execute(''' INSERT INTO company VALUES(%s,%s)'''
                               ,(廠商, 名稱3))
                conn.commit()
                cur.close()
            else:
                print('company error')
                
    return render_template('index.html')


@app.route('/check/', methods=['GET', 'POST'])
def check():
    cur = conn.cursor()
    # 獲取欄位名稱
    sql = "select concat(會計科目,類別,流水號,供應商),名稱 from parts_list ORDER BY concat(會計科目,類別,流水號,供應商) ASC"
    cur.execute(sql)
    labels = cur.fetchall()
    print(labels)
    labels = [g for g in labels]

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

        t = request.values['Test']
        #t = t[2:13]
        print(t)
        #cur = conn.cursor()
        # 獲取欄位資料
        cur.execute("""select * from parts_list WHERE concat(會計科目,類別,流水號,供應商) = {}""".format(repr(t[2:13])))
        content = cur.fetchall()
        print(content)
        sql1 = "SELECT 名稱 FROM parts_list"
        cur.execute(sql1)
        pdname = cur.fetchall()
        pdname = [g for g in pdname]

        sql2 = "SELECT * FROM accounting_subjects"
        cur.execute(sql2)
        accounting_subjects = cur.fetchall()
        accounting_subjects = [g for g in accounting_subjects]

        sql3 = "SELECT * FROM category"
        cur.execute(sql3)
        category = cur.fetchall()
        category = [g for g in category]

        sql4 = "SELECT * FROM company"
        cur.execute(sql4)
        company = cur.fetchall()
        company = [g for g in company]

        return render_template('check1.html', content=content, labels=labels,pdname=pdname,accounting_subjects=accounting_subjects,category=category,company=company)

    return render_template('check.html', labels=labels)


@app.route('/update/')
def update():
    # 獲取頁面輸入內容，提供給sql使用
    userinfoId = request.args.get('userinfoId')
    name = request.args.get('name')  # 更新後的數據返回到頁面上面顯示
    # return render_template('update.html')


# 修改資料庫資料
@app.route('/updateaction/', methods=['POST'])
def updateaction():
    cur = conn.cursor()
    params = request.args if request.method == 'GET' else request.form

    serial_number = params.get('serial_number')
    pdname = params.get('pdname')
    accounting_subjects = params.get('accounting_subjects')
    category = params.get('category')
    company = params.get('company')
    print(pdname)
    print(accounting_subjects[2:4])
    print(category[2:4])
    print(company[2:4])

    sql = "update parts_list set 會計科目='%s',類別='%s',供應商='%s',名稱='%s'"" where 流水號='%s'" \
          %(accounting_subjects[2:4], category[2:4], company[2:4], pdname, serial_number)  # 執行插入操作

    cur.execute(sql)
    conn.commit()

   


    return render_template('index.html')  # 返回更新後的數據，確認是否更新成功






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