ERP/Rita/ERP_Rita/ERP.py

# coding=UTF-8
# This Python file uses the following encoding: utf-8
# 1229 Test
# TODO 資料表是否會重複開啟？ -> def?
from logging import info
from datetime import datetime
from operator import le
import string
from turtle import title
from flask import Flask, make_response, render_template, request, jsonify, send_from_directory, redirect, session
import pymysql
from flask_migrate import Migrate
import time
# from app import create_app
import pandas as pd
from sqlalchemy import true
import xlsxwriter
import pdfkit
from werkzeug.utils import secure_filename
import os


# import sys   #reload()之前必須要引入模組
# import importlib
# importlib.reload(sys)

app = Flask(__name__)

# 首頁測試
@app.route('/')
def index():
    if 'user_id' in session:
        return render_template('index.html', params=locals())
    else:
        return render_template('login.html')

@app.route('/logout')
def logout():
    if 'user_id' in session:
        del session['user_id']
        del session['user_name']
        del session['user_manager']
        del session['user_is_RDmanager']
        del session['user_is_MEmanager']
        del session['user_is_PUmanager']
    return redirect('/')

# 上傳測試 本機測試
CURRENT_PATH = os.path.dirname(__file__)
UPLOAD_FOLDER = os.path.join(CURRENT_PATH, 'static', 'Component_IMG')
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
ALLOWED_EXTENSIONS = set(['png', 'jpeg', 'jpg'])

def allowed_file(filename):
    print('.' in filename)
    print("filename: ", filename, " type(filename): ", type(filename))
    print(filename.split('.')[1].lower() in ALLOWED_EXTENSIONS)
    return '.' in filename and filename.split('.')[1].lower() in ALLOWED_EXTENSIONS

@app.route('/upload', methods=['GET', 'POST'])
def upload_file():
    if request.method == 'POST':
        uploaded_files = request.files.getlist("file[]")
        filenames = []
        for file in uploaded_files:
            print("file.filename: ", file.filename)
            if file and allowed_file(file.filename):
                filename = secure_filename(file.filename)
                file.save(os.path.join(app.config['UPLOAD_FOLDER'],filename))
                filenames.append(filename)
    return render_template('result.html', filenames=filenames)

@app.route('/upload/<filename>', methods=['GET', 'POST'])
def uploaded_file(filename):
    return send_from_directory(app.config['UPLOAD_FOLDER'],filename)



# 下載測試
@app.route('/download/<file>')
def download(file):
    return send_from_directory('PDF',file, as_attachment=True)

@app.route('/DL/<host>/<formtype>/<PR_Form_number>_<sup>')
def DL(host, formtype, PR_Form_number, sup):
    # 230 測試
    config = pdfkit.configuration(wkhtmltopdf='/usr/local/bin/wkhtmltopdf')
    # 本機測試
    # config = pdfkit.configuration(wkhtmltopdf='C:/Program Files/wkhtmltopdf/bin/wkhtmltopdf.exe')
    options = {
        'encoding': 'UTF-8',
        'cookie': [
            ('user_id', session['user_id'])
        ],
    }
    print("session: ", session)
    CURRENT_PATH = os.path.dirname(__file__)
    print("CURRENT_PATH: ", CURRENT_PATH)
    RFQ_Form_number = '0712' + PR_Form_number[4:8]
    # pdfkit.from_url("https://www.google.com.tw/", "C:/Users/USER/Desktop/out.pdf", configuration=config, options=options)
    # pdfkit.from_url('http://192.168.50.65:5012/RFQ_form_07110001Txx_T17', 
    #                 'C:/Users/USER/Desktop/RFQ-' + PR_Form_number + '_' + sup + '.pdf', configuration=config)
    # 本機測試
    # if formtype == 'PR':
    #     print("PDF_create PR")
    #     pdfkit.from_url('http://192.168.50.65:5012/PR_form_' + PR_Form_number + '_' + sup, 
    #                     'C:/Users/USER/Rita/ERP_Rita_tt/PDF/' + PR_Form_number[0:8] + sup + '.pdf', 
    #                     configuration=config)
    #     file = PR_Form_number[0:8] + sup + '.pdf'

    # elif formtype == 'RFQ':
    #     print("PDF_create RFQ")
    #     pdfkit.from_url('http://192.168.50.65:5012/RFQ_form_' + PR_Form_number + '_' + sup, 
    #                     'C:/Users/USER/Rita/ERP_Rita_tt/PDF/' + RFQ_Form_number + sup + '.pdf', 
    #                     configuration=config)
    #     file = RFQ_Form_number + sup + '.pdf'

    # else:
    #     print("PDF_create else RFQ")
    #     pdfkit.from_url('http://192.168.50.65:5012/RFQ_form_' + PR_Form_number + '_' + sup, 
    #                     'C:/Users/USER/Rita/ERP_Rita_tt/PDF/' + RFQ_Form_number + sup + '.pdf', 
    #                     configuration=config)
    #     file = RFQ_Form_number + sup + '.pdf'
    # 230 測試
    # pdfkit.from_url('http://192.168.50.106:5012/RFQ_form_' + PR_Form_number + '_' + sup, 
    #                 '/home/gs1801/Rita/ERP_Rita_tt/PDF/RFQ-' + PR_Form_number + '_' + sup + '.pdf', 
    #                 configuration=config)
    
    IP = request.remote_addr
    print("IP: ", IP)
    if formtype == 'PR':
        print("PDF_create PR")
        pdfkit.from_url(host + '/PR_form_' + PR_Form_number + '_' + sup, 
                        CURRENT_PATH + '/PDF/' + PR_Form_number[0:8] + sup + '.pdf', 
                        configuration=config)
        file = PR_Form_number[0:8] + sup + '.pdf'

    elif formtype == 'RFQ':
        print("PDF_create RFQ")
        pdfkit.from_url(host + '/RFQ_form_' + PR_Form_number + '_' + sup, 
                        CURRENT_PATH + '/PDF/' + RFQ_Form_number + sup + '.pdf', 
                        configuration=config)
        file = RFQ_Form_number + sup + '.pdf'

    else:
        print("PDF_create else RFQ")
        pdfkit.from_url(host + '/RFQ_form_' + PR_Form_number + '_' + sup, 
                        CURRENT_PATH + '/PDF/' + RFQ_Form_number + sup + '.pdf', 
                        configuration=config)
        file = RFQ_Form_number + sup + '.pdf'

    # return jsonify({
    #     "response":"OK"
    # })
    
    return send_from_directory('PDF',file, as_attachment=True)

# 登入
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'GET':
        if 'user_id' in session:
            return redirect('/')
        else:
            return render_template('login.html', title = 'GET')
    elif request.method == 'POST':
        user_id = request.form['user_id']
        print("user_id: ", user_id)
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        
        try:
            sql = "SELECT 員工編號, 員工英文名字, 直屬主管員工編號, RD部門主管, ME部門主管, PU部門主管 " + \
                "FROM 員工列表 " + \
                "WHERE 員工編號 = '" + user_id + "'"
            mycursor.execute(sql)
            user_name = mycursor.fetchall()
            print("user_id: ", user_name[0][0])
            print("user_name: ", user_name[0][1])
            print("user_manager: ", user_name[0][2])
            print("user_is_RDmanager: ", user_name[0][3])
            print("user_is_MEmanager: ", user_name[0][4])
            print("user_is_PUmanager: ", user_name[0][5])

            mydb.commit()
            mydb.close()

            if user_name:
                session['user_id'] = user_name[0][0]
                session['user_name'] = user_name[0][1]
                session['user_manager'] = user_name[0][2]
                session['user_is_RDmanager'] = user_name[0][3]
                session['user_is_MEmanager'] = user_name[0][4]
                session['user_is_PUmanager'] = user_name[0][5]

                # if session['user_is_PUmanager']:
                #     print('You are PUmanager')
                # elif session['user_is_RDmanager'] or session['user_is_MEmanager']:
                #     print('You are RD/MEmanager')
                # elif 'HRPU' in session['user_id']:
                #     print('You are PU')
                # elif 'HRRD' in session['user_id'] or 'HRME' in session['user_id']:
                #     print('You are RD/ME')

                # return redirect('/PR_user_07110001Txx')
                return redirect('/')
        except IndexError:
            return render_template('login.html', title = '無此帳號')

# 通知列表
@app.route('/notice', methods=['GET', 'POST'])
def notice():
    if request.method == 'GET':
        if 'user_id' in session:
            user_id = session['user_id']
            user_name = session['user_name']
            mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
            # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
            mycursor = mydb.cursor()

            # 取得現有請購單單號
            sql = "SELECT `通知編號`,`通知對象`,`日期`,`表單單號`,`內容`,`已完成`" + \
                    " FROM `通知列表`" + \
                    " WHERE `已完成`='0' AND `通知對象`='" + user_id + "'" + \
                    " ORDER BY `通知編號`"
            mycursor.execute(sql)
            notice_list = mycursor.fetchall()
            # print("None: ", notice_list == ())

            mydb.commit()
            mydb.close()
            user_id = session['user_id']
            
            if notice_list == ():
                return redirect('/PR_list')
            else:
                return render_template('notice.html', title = '通知', **locals())
        else:
            return render_template('login.html', title = 'GET')
    

# 使用者名稱測試
@app.route('/user/<name>')
def user(name):
    return render_template('hello.html', name = name)

# 詢價單
# @app.route('/RFQ_user_<PR_Form_number>_<sup>')
# def RFQ_user(PR_Form_number, sup):
#     mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
#     # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
#     mycursor = mydb.cursor()

#     # 取得內部料號/零件名稱/原廠料號 新增內部料號時使用
#     sql = "SELECT 零件表.內部料號, 零件表.名稱, 規格表.原廠料號 " + \
#           "FROM 零件表 " + \
#           "INNER JOIN 規格表 " + \
#           "ON 零件表.內部料號 = 規格表.內部料號 "
#     mycursor.execute(sql)
#     Component_id_name_salesnum = mycursor.fetchall()

#     if sup[:1] == 'T':
#         sql = "SELECT " + PR_Form_number + ".內部料號, 零件表.名稱, 規格表.原廠料號, " + \
#             "數量, 規格表.單位, 零件表.供應商, 單價, 營業稅, 總金額, 連結, " + PR_Form_number + ".請購備註 " + \
#             "FROM ( " + PR_Form_number + " " + \
#             "INNER JOIN 零件表 " + \
#             "ON " + PR_Form_number + ".內部料號 = 零件表.內部料號 " + \
#             ") " + \
#             "INNER JOIN 規格表 " + \
#             "ON " + PR_Form_number + ".內部料號 = 規格表.內部料號 " + \
#             "WHERE 零件表.供應商 = '" + sup + "'"
#     else:
#         sql = "SELECT " + PR_Form_number + ".內部料號, 零件表.名稱, 規格表.原廠料號, " + \
#             "數量, 規格表.單位, 零件表.供應商, 單價, 營業稅, 總金額, 連結, " + PR_Form_number + ".備註 " + \
#             "FROM ( " + PR_Form_number + " " + \
#             "INNER JOIN 零件表 " + \
#             "ON " + PR_Form_number + ".內部料號 = 零件表.內部料號 " + \
#             ") " + \
#             "INNER JOIN 規格表 " + \
#             "ON " + PR_Form_number + ".內部料號 = 規格表.內部料號 "
#     print("sql: ", sql)
#     mycursor.execute(sql)
#     PR_data = mycursor.fetchall()
    

#     return render_template('RFQ_user_POtest.html', title = 'ERP 詢價', **locals())

# 請購單列表
@app.route('/PR_list')
def PR_list():
    if 'user_id' in session:
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()

        user_name = session['user_name']
        user_id = session['user_id']
        user_manager = session['user_manager']

        # # 取得現有請購單單號
        # sql = "SELECT `TABLE_NAME` " + \
        #         "FROM information_schema.TABLES " + \
        #         "WHERE TABLE_NAME LIKE '%0711%' AND TABLE_NAME LIKE '%Txx%' AND `TABLE_SCHEMA`='erp'"
        # mycursor.execute(sql)
        # PR_tables = mycursor.fetchall()

        # 通知提示
        sql = "SELECT `通知編號`,`通知對象`,`日期`,`表單單號`,`內容`,`已完成`" + \
                " FROM `通知列表`" + \
                " WHERE `已完成`='0' AND `通知對象`='" + user_id + "'" + \
                " ORDER BY `通知編號`"
        mycursor.execute(sql)
        notice_list = mycursor.fetchall()
        print("[PR_list] notice_list: ", notice_list)
        
        # 取得現有請購單單號
        sql = "SELECT `請購草稿單號`,`表單狀態`,`員工列表`.`員工英文名字`" + \
                "FROM `表單詳細資料`" + \
                "INNER JOIN `員工列表`" + \
                "ON `表單詳細資料`.`請購人員工編號` = `員工列表`.`員工編號`"
        mycursor.execute(sql)
        PR_tables = mycursor.fetchall()
        # print("PR_tables[-1][0][4:8]", PR_tables[-1][0][4:8])

        # 取得最新的請購單單號 +1
        try:
            new_PR_companyid = '{0:04d}'.format(int(PR_tables[-1][0][4:8]) + 1)
        except:
            new_PR_companyid = '0001'

        mydb.commit()
        mydb.close()

        return render_template('PR_list.html', title = 'ERP 請購列表', **locals())
    else:
        return render_template('login.html', title = 'GET')
    


# 新增 請購單
# @app.route('/PR_new_<PR_Form_number>')
# def PR_new(PR_Form_number):
#     mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
#     # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
#     mycursor = mydb.cursor()
#     # 取得內部料號/零件名稱/原廠料號 新增內部料號時使用
#     sql = "SELECT 零件表.內部料號, 零件表.名稱, 規格表.原廠料號 " + \
#           "FROM 零件表 " + \
#           "INNER JOIN 規格表 " + \
#           "ON 零件表.內部料號 = 規格表.內部料號 "
#     mycursor.execute(sql)
#     Component_id_name_salesnum = mycursor.fetchall()
#     # # 取得供應商編號/供應商名稱
#     # sql = "SELECT * FROM 供應商"
#     # mycursor.execute(sql)
#     # Company_id_name = mycursor.fetchall()
#     # # 取得所有請購單單號
#     # sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE `TABLE_NAME` LIKE '%0711%' ORDER BY TABLE_NAME"
#     # mycursor.execute(sql)
#     # PR_id = mycursor.fetchall()
#     # # 取得 `07110001T01` 現有資料
#     # try:
#     #     sql = "SELECT " + PR_Form_number + ".內部料號, 零件表.名稱, 規格表.原廠料號, " + \
#     #         "數量, 規格表.單位, 零件表.供應商, 單價, 關稅, 運費, 總金額, 付款狀況, " + PR_Form_number + ".備註 " + \
#     #         "FROM ( " + PR_Form_number + " " + \
#     #         "INNER JOIN 零件表 " + \
#     #         "ON " + PR_Form_number + ".內部料號 = 零件表.內部料號 " + \
#     #         ") " + \
#     #         "INNER JOIN 規格表 " + \
#     #         "ON " + PR_Form_number + ".內部料號 = 規格表.內部料號 "
#     #     print("sql: ", sql)
#     #     mycursor.execute(sql)
#     #     PR_data = mycursor.fetchall()
#     # except pymysql.err.ProgrammingError:
#     #     print("PR_user ERROR: pymysql.err.ProgrammingError")
#     #     PR_data = ''


#     mydb.close()

#     return render_template('PR_new.html', title = '建立請購單', **locals())

# 請購頁面
@app.route('/PR_form_<PRx_Form_number>_<PRcompany>')
def PR_form(PRx_Form_number, PRcompany):
    if True:
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        sql = "SELECT " + PRx_Form_number + ".內部料號, 零件表.名稱, 規格表.原廠料號, " + \
                "圖號, 數量, 連結, 供應商.公司" + \
                " FROM (" + \
                            "(" + PRx_Form_number + " " + \
                            "INNER JOIN 零件表 " + \
                            "ON " + PRx_Form_number + ".內部料號 = 零件表.內部料號 " + \
                            ") " + \
                        "INNER JOIN 規格表 " + \
                        "ON " + PRx_Form_number + ".內部料號 = 規格表.內部料號 " + \
                        ") " + \
                "INNER JOIN 供應商 " + \
                "ON 零件表.供應商 = 供應商.供應商 " + \
                " WHERE `採購公司`='" + PRcompany + "'"
        mycursor.execute(sql)
        PR_datas = mycursor.fetchall()

        # 代入：請購、請購主管、採購、採購主管姓名
        sql = "SELECT `員工列表`.`員工英文名字` FROM `表單詳細資料` " + \
                "INNER JOIN `員工列表` ON `表單詳細資料`.`請購人員工編號` = `員工列表`.`員工編號` " + \
                "WHERE `請購草稿單號` = '" + PRx_Form_number +  "'"
        mycursor.execute(sql)
        form_PR_user_datas = mycursor.fetchall()
        form_PR_user = form_PR_user_datas[0][0]
        sql = "SELECT `員工列表`.`員工英文名字` FROM `表單詳細資料` " + \
                "INNER JOIN `員工列表` ON `表單詳細資料`.`請購主管員工編號` = `員工列表`.`員工編號` " + \
                "WHERE `請購草稿單號` = '" + PRx_Form_number +  "'"
        mycursor.execute(sql)
        form_PR_manager_datas = mycursor.fetchall()
        form_PR_manager = form_PR_manager_datas[0][0]
        sql = "SELECT `員工列表`.`員工英文名字` FROM `表單詳細資料` " + \
                "INNER JOIN `員工列表` ON `表單詳細資料`.`採購人員工編號` = `員工列表`.`員工編號` " + \
                "WHERE `請購草稿單號` = '" + PRx_Form_number +  "'"
        mycursor.execute(sql)
        form_RFQ_user_datas = mycursor.fetchall()
        form_RFQ_user = form_RFQ_user_datas[0][0]
        sql = "SELECT `員工列表`.`員工英文名字` FROM `表單詳細資料` " + \
                "INNER JOIN `員工列表` ON `表單詳細資料`.`採購主管員工編號` = `員工列表`.`員工編號` " + \
                "WHERE `請購草稿單號` = '" + PRx_Form_number +  "'"
        mycursor.execute(sql)
        form_RFQ_manager_datas = mycursor.fetchall()
        form_RFQ_manager = form_RFQ_manager_datas[0][0]

        # 取得請購單用途
        sql = "SELECT `用途` FROM `表單詳細資料` WHERE `請購草稿單號` = '" + PRx_Form_number + "'"
        mycursor.execute(sql)
        form_purpose_data = mycursor.fetchall()
        form_purpose = form_purpose_data[0][0]

        mydb.commit()
        mydb.close()

        PR_Form_number = PRx_Form_number[0:8] + PRcompany

        if PR_datas == ():
            return render_template('form_not_exist.html', title = '請購單 ' + PR_Form_number, **locals())
        else:
            return render_template('PR_form.html', title = '請購單 ' + PR_Form_number, **locals())
    else:
        return render_template('login.html', title = '請先登入')

# 詢價頁面
@app.route('/RFQ_form_<PR_Form_number>_<sup>')
def RFQ_form(PR_Form_number, sup):
    if True:
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        sql = "SELECT `公司`, `聯絡人`, `地址`, `電話`, `傳真`, `email`, `統一編號`, `供應商`" + \
                " FROM `供應商` " + \
                " WHERE `供應商`='" + sup + "'"
        mycursor.execute(sql)
        sup_datas = mycursor.fetchall()
        sql = "SELECT " + PR_Form_number + ".內部料號, 零件表.名稱, 規格表.原廠料號, " + \
                "圖號, 數量, 單位" + \
                " FROM (" + PR_Form_number + " " + \
                "INNER JOIN 零件表 " + \
                "ON " + PR_Form_number + ".內部料號 = 零件表.內部料號 " + \
                ") " + \
                "INNER JOIN 規格表 " + \
                "ON " + PR_Form_number + ".內部料號 = 規格表.內部料號 " + \
                " WHERE `供應商`='" + sup + "'"
        mycursor.execute(sql)
        PR_datas = mycursor.fetchall()

        mydb.commit()
        mydb.close()

        if PR_datas == ():
            return render_template('form_not_exist.html', title = '無詢價單 ' + PR_Form_number + '_' + sup, **locals())
        else:
            return render_template('RFQ_form.html', title = '詢價單' + PR_Form_number + '_' + sup, **locals())
    else:
        return render_template('login.html', title = '請先登入')

# 採購輸入頁面
@app.route('/PO_user_<PO_Form_number>')
def PO_user(PO_Form_number):
    if 'user_id' in session:
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        sql = "SELECT 零件表.內部料號, 零件表.名稱, 規格表.原廠料號, 規格表.圖號, 數量, 規格表.單位, 供應商.公司, 金額, 關稅, 含稅運費, 總金額, " + \
              "儲位, 儲位修改時間, 預計進貨日, 實際進貨日, 到貨數量, 剩餘數量, 發票號碼, 供應商出貨單, 檢測報告" + \
              " FROM (" + \
              " (採購單零件項目列表" + \
              " INNER JOIN 零件表" + \
              " ON 採購單零件項目列表.內部料號 = 零件表.內部料號" + \
              " )" + \
              " INNER JOIN 規格表" + \
              " ON 採購單零件項目列表.內部料號 = 規格表.內部料號" + \
              " )" + \
              " INNER JOIN 供應商" + \
              " ON 採購單零件項目列表.供應商 = 供應商.供應商" + \
              " WHERE 採購單零件項目列表.`採購單號`='" + PO_Form_number + "'"
        mycursor.execute(sql)
        PO_datas = mycursor.fetchall()
        # print("PO_datas: ", PO_datas)
        # print("PO_datas[0]: ", PO_datas[0])
        # print("PO_datas[0][0]: ", PO_datas[0][0])

        sql = "SELECT * FROM `採購單詳細資料列表` WHERE `採購單號`='" + PO_Form_number + "'"
        mycursor.execute(sql)
        PO_form_datas = mycursor.fetchall()
        PO_form_datas = PO_form_datas[0]
        # print("PO_form_datas: ", PO_form_datas)

        sql = "SELECT `儲位名稱` FROM `儲位列表`"
        mycursor.execute(sql)
        stock_datas = mycursor.fetchall()
        # print("stock_datas: ", stock_datas)
        
        mydb.commit()
        mydb.close()

        user_name = session['user_name']
        user_id = session['user_id']
        user_manager = session['user_manager']

        if PO_datas == ():
            return render_template('form_not_exist.html', title = '採購單 ' + PO_Form_number, **locals())
        else:
            return render_template('PO_user.html', title = '採購單 ' + PO_Form_number, **locals())
    else:
        return render_template('login.html', title = 'GET')


# 採購頁面
@app.route('/PO_form_<PO_Form_number>')
def PO_form(PO_Form_number):
    if 'user_id' in session:
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        sql = "SELECT 零件表.名稱, 規格表.原廠料號, 規格表.圖號, 數量, 規格表.單位, 供應商.公司, 金額, 關稅, 含稅運費, 總金額" + \
              " FROM (" + \
              " (採購單零件項目列表" + \
              " INNER JOIN 零件表" + \
              " ON 採購單零件項目列表.內部料號 = 零件表.內部料號" + \
              " )" + \
              " INNER JOIN 規格表" + \
              " ON 採購單零件項目列表.內部料號 = 規格表.內部料號" + \
              " )" + \
              " INNER JOIN 供應商" + \
              " ON 採購單零件項目列表.供應商 = 供應商.供應商" + \
              " WHERE 採購單零件項目列表.`採購單號`='" + PO_Form_number + "'"
        mycursor.execute(sql)
        PO_datas = mycursor.fetchall()

        sql = "SELECT * FROM `採購單詳細資料列表` WHERE `採購單號`='" + PO_Form_number + "'"
        mycursor.execute(sql)
        PO_form_datas = mycursor.fetchall()
        PO_form_datas = PO_form_datas[0]
        
        mydb.commit()
        mydb.close()

        if PO_datas == ():
            return render_template('form_not_exist.html', title = '採購單 ' + PO_Form_number, **locals())
        else:
            return render_template('PO_form.html', title = '採購單 ' + PO_Form_number, **locals())
    else:
        return render_template('login.html', title = 'GET')

# 採購頁面 未到貨 PO 區
@app.route('/PO_backlog_stock')
def PO_backlog_stock():
    if 'user_id' in session:
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        nowtime = time.strftime("%Y-%m-%d", time.localtime())
        print("nowtime: ", nowtime)
        sql = "SELECT `採購單號`, 零件表.名稱, 供應商.公司, `預計進貨日`, `實際進貨日`, `剩餘數量`, 規格表.單位, `發票號碼`" + \
                " FROM (" + \
                "     (採購單零件項目列表" + \
                "     INNER JOIN 零件表" + \
                "     ON 採購單零件項目列表.內部料號 = 零件表.內部料號" + \
                "     )" + \
                "     INNER JOIN 規格表" + \
                "     ON 採購單零件項目列表.內部料號 = 規格表.內部料號" + \
                " )" + \
                " INNER JOIN 供應商" + \
                " ON 採購單零件項目列表.供應商 = 供應商.供應商" + \
                " WHERE `剩餘數量` > 0"
        # " WHERE `預計進貨日` != '' AND `預計進貨日` <= '" + nowtime + "' AND `實際進貨日` = ''"
        mycursor.execute(sql)
        backlog_stocks = mycursor.fetchall()
        # print("backlog_stocks: ", backlog_stocks)
        
        mydb.commit()
        mydb.close()

        return render_template('PO_backlog_stock.html', title = '採購未到貨', **locals())
    else:
        return render_template('login.html', title = 'GET')

# 檢驗報告介面
@app.route('/IR_user_<IR_form_number>')
def IR_user(IR_form_number):
    if 'user_id' in session:
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        # nowtime = time.strftime("%Y-%m-%d", time.localtime())
        # print("nowtime: ", nowtime)
        sql = "SELECT `檢驗單號`, `檢驗人員_管理部`, `檢驗人員_機構部`, `檢驗人員_研發部`, `檢驗人員_主管決議`, `日期`, " + \
            "零件表.名稱, `採購單號`, 規格表.圖號,`來貨批次`, `類型`, " + \
            "`管理部數量結果`, `管理部數量說明`, `管理部型號結果`, `管理部型號說明`, `管理部規格結果`, `管理部規格說明`, `管理部外觀完整性結果`, `管理部外觀完整性說明`, " + \
            "`管理部試組合結果`, `管理部試組合說明`, `管理部保固期限結果`, `管理部保固期限說明`, `管理部技術文件結果`, `管理部技術文件說明`, `管理部使用保養注意事項結果`, `管理部使用保養注意事項說明`, " + \
            "`研發部尺寸結果`, `研發部尺寸說明`, `研發部組合狀況結果`, `研發部組合狀況說明`, `研發部運作狀況結果`, `研發部運作狀況說明`, `研發部可靠度結果`, `研發部可靠度說明`, " + \
            "`主管決議說明`, `圖號連結`, `決議`" + \
            " FROM " + \
            " ( 檢驗報告列表" + \
            " INNER JOIN 零件表 " + \
            " ON 檢驗報告列表.內部料號 = 零件表.內部料號 " + \
            " ) " + \
            " INNER JOIN 規格表 " + \
            " ON 檢驗報告列表.內部料號 = 規格表.內部料號 " + \
            " WHERE `檢驗單號`='" + IR_form_number + "'"
 
        mycursor.execute(sql)
        IR_datas = mycursor.fetchall()[0]
        # print("IR_datas: ", IR_datas)

        user_name = session['user_name']
        
        mydb.commit()
        mydb.close()

        return render_template('IR_user.html', title = '檢驗報告 ' + IR_form_number, **locals())
    else:
        return render_template('login.html', title = 'GET')

# PDF 測試
@app.route('/PDF_create/<formtype>/<PR_Form_number>_<sup>')
def PDF_create(formtype, PR_Form_number, sup):
    # 230 測試
    # config = pdfkit.configuration(wkhtmltopdf='/usr/local/bin/wkhtmltopdf')
    # 本機測試
    config = pdfkit.configuration(wkhtmltopdf='C:/Program Files/wkhtmltopdf/bin/wkhtmltopdf.exe')
    options = {
        'encoding': 'UTF-8',
        'cookie': [
            ('user_id', session['user_id']),
            ('user_name', session['user_name'])
        ],
    }
    RFQ_Form_number = '0712' + PR_Form_number[4:8]
    # pdfkit.from_url("https://www.google.com.tw/", "C:/Users/USER/Desktop/out.pdf", configuration=config, options=options)
    # pdfkit.from_url('http://192.168.50.65:5012/RFQ_form_07110001Txx_T17', 
    #                 'C:/Users/USER/Desktop/RFQ-' + PR_Form_number + '_' + sup + '.pdf', configuration=config)
    # 本機測試
    # if formtype == 'PR':
    #     print("PDF_create PR")
    #     pdfkit.from_url('http://192.168.50.65:5012/PR_form_' + PR_Form_number + '_' + sup, 
    #                     'C:/Users/USER/Rita/ERP_Rita_tt/PDF/' + PR_Form_number[0:8] + sup + '.pdf', 
    #                     configuration=config)
    #     file = PR_Form_number[0:8] + sup + '.pdf'

    # elif formtype == 'RFQ':
    #     print("PDF_create RFQ")
    #     pdfkit.from_url('http://192.168.50.65:5012/RFQ_form_' + PR_Form_number + '_' + sup, 
    #                     'C:/Users/USER/Rita/ERP_Rita_tt/PDF/' + RFQ_Form_number + sup + '.pdf', 
    #                     configuration=config)
    #     file = RFQ_Form_number + sup + '.pdf'

    # else:
    #     print("PDF_create else RFQ")
    #     pdfkit.from_url('http://192.168.50.65:5012/RFQ_form_' + PR_Form_number + '_' + sup, 
    #                     'C:/Users/USER/Rita/ERP_Rita_tt/PDF/' + RFQ_Form_number + sup + '.pdf', 
    #                     configuration=config)
    #     file = RFQ_Form_number + sup + '.pdf'
    # 230 測試
    # pdfkit.from_url('http://192.168.50.106:5012/RFQ_form_' + PR_Form_number + '_' + sup, 
    #                 '/home/gs1801/Rita/ERP_Rita_tt/PDF/RFQ-' + PR_Form_number + '_' + sup + '.pdf', 
    #                 configuration=config)
    if formtype == 'PR':
        print("PDF_create PR")
        pdfkit.from_url('192.168.50.106:5012/PR_form_' + PR_Form_number + '_' + sup, 
                        '/home/gs1801/Rita/ERP_Rita_tt/PDF/' + PR_Form_number[0:8] + sup + '.pdf', 
                        configuration=config)
        file = PR_Form_number[0:8] + sup + '.pdf'

    elif formtype == 'RFQ':
        print("PDF_create RFQ")
        pdfkit.from_url('192.168.50.106:5012/RFQ_form_' + PR_Form_number + '_' + sup, 
                        '/home/gs1801/Rita/ERP_Rita_tt/PDF/' + RFQ_Form_number + sup + '.pdf', 
                        configuration=config)
        file = RFQ_Form_number + sup + '.pdf'

    else:
        print("PDF_create else RFQ")
        pdfkit.from_url('192.168.50.106:5012/RFQ_form_' + PR_Form_number + '_' + sup, 
                        '/home/gs1801/Rita/ERP_Rita_tt/PDF/' + RFQ_Form_number + sup + '.pdf', 
                        configuration=config)
        file = RFQ_Form_number + sup + '.pdf'

    return jsonify({
        "response":"OK"
    })

# 請購頁面：採購主管、請購主管、採購人員、請購人
@app.route('/PR_user_<PR_Form_number>')
def PR_user(PR_Form_number):
    if 'user_id' in session:
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        
        # 取得內部料號/零件名稱/原廠料號 新增內部料號時使用
        sql = "SELECT 零件表.內部料號, 零件表.名稱, 規格表.原廠料號 " + \
                "FROM 零件表 " + \
                "INNER JOIN 規格表 " + \
                "ON 零件表.內部料號 = 規格表.內部料號 "
        mycursor.execute(sql)
        Component_id_name_salesnum = mycursor.fetchall()
        
        # # 取得所有請購單單號
        # sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE `TABLE_NAME` LIKE '%0711%' ORDER BY TABLE_NAME"
        # mycursor.execute(sql)
        # PR_id = mycursor.fetchall()
        # # 取得 `07110001T01` 現有資料

        user_name = session['user_name']
        user_id = session['user_id']
        user_manager = session['user_manager']

        # # 取得部門主管英文名字, 送出請購單用
        # try:
        #     sql = "SELECT `員工英文名字` FROM `員工列表` WHERE `員工編號`='" + user_manager + "'"
        #     mycursor.execute(sql)
        #     user_manager_data = mycursor.fetchall()
        #     # print(user_manager_data[0][0])
        #     user_manager_name = user_manager_data[0][0]
        # except:
        #     user_manager_name = 'No_Manager'

        # 取得 PR_Form_number 表單狀態
        sql = "SELECT `表單狀態`,`採購人員工編號`,`請購人員工編號`,`請購主管員工編號` FROM `表單詳細資料` WHERE `請購草稿單號`='" + PR_Form_number + "'"
        mycursor.execute(sql)
        form_status_data = mycursor.fetchall()
        form_status = form_status_data[0][0]
        form_RFQ_user = form_status_data[0][1]
        form_PR_user = form_status_data[0][2]
        form_PR_manager = form_status_data[0][3]

        # 取得請購單用途
        sql = "SELECT `用途` FROM `表單詳細資料` WHERE `請購草稿單號` = '" + PR_Form_number + "'"
        mycursor.execute(sql)
        form_purpose_data = mycursor.fetchall()
        form_purpose = form_purpose_data[0][0]
        
        try:
            if session['user_is_PUmanager']:
                # print('You are PUmanager')
                # 請購單內容
                sql = "SELECT " + PR_Form_number + ".內部料號, 零件表.名稱, 規格表.原廠料號, " + \
                    "數量, 規格表.單位, 規格表.圖片, 零件表.供應商, 連結, 請購人, " + \
                    "幣別, 單價, 營業稅, 總金額, 交期, 詢價備註, 詢價單單號, 供應商報價單號, 採購主管核可, 採購公司, " + \
                    PR_Form_number + ".請購備註 " + \
                    "FROM ( " + PR_Form_number + " " + \
                    "INNER JOIN 零件表 " + \
                    "ON " + PR_Form_number + ".內部料號 = 零件表.內部料號 " + \
                    ") " + \
                    "INNER JOIN 規格表 " + \
                    "ON " + PR_Form_number + ".內部料號 = 規格表.內部料號 "
                mycursor.execute(sql)
                PR_data = mycursor.fetchall()
                # 請購主管之下屬列表
                sql = "SELECT `員工編號`,`員工英文名字` FROM `員工列表` WHERE `直屬主管員工編號` = '" + user_id + "'"
                mycursor.execute(sql)
                staff_data = mycursor.fetchall()
                # staff_data = [g[0] for g in staff_data]
                print("staff_data: ", staff_data)

                mydb.close()
                return render_template('RFQ_manager.html', title = PR_Form_number + ' 詢價_主管', **locals())

            elif session['user_is_RDmanager'] or session['user_is_MEmanager']:
                # print('You are RD/MEmanager')
                sql = "SELECT " + PR_Form_number + ".內部料號, 零件表.名稱, 規格表.原廠料號, " + \
                    "數量, 規格表.單位, 規格表.圖片, 零件表.供應商, 連結, 請購人, 請購核可, 請購備註, 採購主管核可 " + \
                    "FROM ( " + PR_Form_number + " " + \
                    "INNER JOIN 零件表 " + \
                    "ON " + PR_Form_number + ".內部料號 = 零件表.內部料號 " + \
                    ") " + \
                    "INNER JOIN 規格表 " + \
                    "ON " + PR_Form_number + ".內部料號 = 規格表.內部料號 "
                mycursor.execute(sql)
                PR_data = mycursor.fetchall()
                # 請購主管之下屬列表
                sql = "SELECT `員工英文名字` FROM `員工列表` WHERE `直屬主管員工編號` = '" + user_id + "'"
                mycursor.execute(sql)
                staff_data = mycursor.fetchall()
                staff_data = [g[0] for g in staff_data]
                print("staff_data: ", staff_data)
                # 請購單之請購人
                sql = "SELECT `請購人員工編號` FROM `表單詳細資料` WHERE `請購草稿單號`='" + PR_Form_number + "'"
                mycursor.execute(sql)
                PR_person_data = mycursor.fetchall()
                PR_person = PR_person_data[0][0]
                # 詢價主管
                RFQ_manager = "HRMA0001T01"
                mydb.close()
                return render_template('PR_manager.html', title = PR_Form_number + ' 請購_主管', **locals())

            elif 'HRPU' in session['user_id']:
                # print('You are PU')
                sql = "SELECT " + PR_Form_number + ".內部料號, 零件表.名稱, 規格表.原廠料號, " + \
                    "數量, 規格表.單位, 規格表.圖片, 零件表.供應商, 連結, " + PR_Form_number + ".請購備註, 請購人, 請購核可, 採購公司, " + \
                    "幣別, 單價, 營業稅, 總金額, 交期, 詢價備註, 詢價單單號, 供應商報價單號, 採購主管核可 " + \
                    "FROM ( " + PR_Form_number + " " + \
                    "INNER JOIN 零件表 " + \
                    "ON " + PR_Form_number + ".內部料號 = 零件表.內部料號 " + \
                    ") " + \
                    "INNER JOIN 規格表 " + \
                    "ON " + PR_Form_number + ".內部料號 = 規格表.內部料號 "
                mycursor.execute(sql)
                PR_data = mycursor.fetchall()
                # 獲取供應商欄位
                Supplier = "SELECT 供應商, 公司 FROM 供應商"
                mycursor.execute(Supplier)
                Supplier_data = mycursor.fetchall()
                # # 取得部門主管英文名字, 送出請購單用
                # sql = "SELECT `員工英文名字` FROM `員工列表` WHERE `員工編號`='" + user_manager + "'"
                # mycursor.execute(sql)
                # user_manager_data = mycursor.fetchall()
                # # print(user_manager_data[0][0])
                # user_manager_name = user_manager_data[0][0]
                # # 取得供應商名稱和供應商編號
                sql = "SELECT DISTINCT `零件表`.`供應商`, `供應商`.`公司` " + \
                        "FROM ( `" + PR_Form_number + "` " + \
                        "INNER JOIN `零件表` ON `" + PR_Form_number + "`.`內部料號` = `零件表`.`內部料號` ) " + \
                        "INNER JOIN `供應商` ON `零件表`.`供應商` = `供應商`.`供應商` " + \
                        "ORDER BY `零件表`.`供應商`"
                mycursor.execute(sql)
                RFQ_supplier_data = mycursor.fetchall()
                mydb.close()
                return render_template('RFQ_user.html', title = PR_Form_number + ' 詢價', **locals())

            elif 'HRRD' in session['user_id'] or 'HRME' in session['user_id']:
                # print('You are RD/ME')
                sql = "SELECT " + PR_Form_number + ".內部料號, 零件表.名稱, 規格表.原廠料號, " + \
                    "數量, 規格表.單位, 規格表.圖片, 零件表.供應商, 連結, 請購人, 請購核可, 請購備註, 採購主管核可 " + \
                    "FROM ( " + PR_Form_number + " " + \
                    "INNER JOIN 零件表 " + \
                    "ON " + PR_Form_number + ".內部料號 = 零件表.內部料號 " + \
                    ") " + \
                    "INNER JOIN 規格表 " + \
                    "ON " + PR_Form_number + ".內部料號 = 規格表.內部料號 "
                mycursor.execute(sql)
                PR_data = mycursor.fetchall()
                # # 取得部門主管英文名字, 送出請購單用
                # sql = "SELECT `員工英文名字` FROM `員工列表` WHERE `員工編號`='" + user_manager + "'"
                # mycursor.execute(sql)
                # user_manager_data = mycursor.fetchall()
                # # print(user_manager_data[0][0])
                # user_manager_name = user_manager_data[0][0]
                mydb.close()
                return render_template('PR_user.html', title = PR_Form_number + ' 請購', **locals())
        
        except pymysql.err.ProgrammingError:
            print("PR_user ERROR: pymysql.err.ProgrammingError")
            PR_data = ''
            return redirect('/')
    else:
        return render_template('login.html', title = 'GET')

# 查詢 內部料號 出現資料表 / 修改內部料號
@app.route('/search_companyid', methods=['GET', 'POST'])
def search_companyid():
    if 'user_id' in session:
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        # 取得內部料號/名稱/原廠料號
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        sql = "SELECT 零件表.內部料號, 零件表.名稱, 規格表.原廠料號 " + \
            "FROM 零件表 " + \
            "INNER JOIN 規格表 " + \
            "ON 零件表.內部料號 = 規格表.內部料號 "
        mycursor.execute(sql)
        Component_id_name_salesnum = mycursor.fetchall()
        
        # 取得會計科目欄位
        AccountingSubjects = "SELECT * FROM 會計科目表"
        mycursor.execute(AccountingSubjects)
        AccountingSubjects_data = mycursor.fetchall()
        # print("AccountingSubjects_data", AccountingSubjects_data)     
        # # (('01', '成品'), ('02', '系統成品'), ('03', '模組半成品'), ('04', '零件'), ('05', '工具'))

        # 取得類別欄位
        Category = "SELECT * FROM 類別表"
        mycursor.execute(Category)
        Category_data = mycursor.fetchall()
        
        # 取得供應商
        Supplier = "SELECT * FROM 供應商"
        mycursor.execute(Supplier)
        Supplier_data = mycursor.fetchall()

        return render_template('search_companyid.html', title='查詢內部料號', **locals())
    else:
        return render_template('login.html', title = 'GET')
    

# 刪除 內部料號 出現資料表 零件
@app.route('/delete_companyid_table_<search_company_id>', methods=['GET'])
def delete_companyid_table(search_company_id):
    if 'user_id' in session:
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        sql = "SELECT `TABLE_NAME` " + \
            "FROM information_schema.columns " +  \
            "WHERE `COLUMN_NAME`='內部料號' AND `TABLE_SCHEMA`='erp'"
        # print("sql: ", sql)
        mycursor.execute(sql)
        table_data = mycursor.fetchall()
        table_data = [g[0].replace('t', 'T') for g in table_data]
        print("table_data: ", table_data)           #  ['03040001T01', '03040002T01', '03040003T01', ... ]

        exist_BOM_list = []
        for i in range(0, len(table_data)):
            print("table_data[", i , "]: ", table_data[i])
            sql = "SELECT `內部料號` " + \
                "FROM `" + table_data[i] + "` " +  \
                "WHERE `內部料號`='" + search_company_id + "'"
            print("[delete_companyid_table] sql: ", sql)
            mycursor.execute(sql)
            has_id_data = mycursor.fetchall()
            # print("has_id_data:", has_id_data)

            if len(has_id_data) > 0:
                exist_BOM_list.append(table_data[i])
                print("exist_BOM_list: ", exist_BOM_list)
                delete_sql = "DELETE FROM `" + table_data[i] + "` WHERE `內部料號` = '" + search_company_id + "'"
                print("delete_sql: " + delete_sql)
                mycursor.execute(delete_sql)
                delete_id_data = mycursor.fetchall()
        mydb.commit()
        mydb.close()

        return jsonify({"exist_BOM":exist_BOM_list})
    else:
        return render_template('login.html', title = 'GET')
    

# 查詢 內部料號 出現資料表
@app.route('/search_companyid_table_<search_company_id>', methods=['GET'])
def search_companyid_table(search_company_id):
    mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
    # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
    mycursor = mydb.cursor()
    sql = "SELECT `TABLE_NAME` " + \
          "FROM information_schema.columns " +  \
          "WHERE `COLUMN_NAME`='內部料號' AND `TABLE_SCHEMA`='erp'"
    # print("sql: ", sql)
    mycursor.execute(sql)
    table_data = mycursor.fetchall()
    table_data = [g[0].replace('t', 'T') for g in table_data]
    print("table_data: ", table_data)           #  ['03040001T01', '03040002T01', '03040003T01', ... ]
    sql_data = table_data
    
    has_id_table_list = []
    # 從 erp 資料庫內找到有符合內部料號的 table
    for i in range(0, len(table_data)):
        sql = "SELECT `內部料號` " + \
            "FROM " + table_data[i] + " " +  \
            "WHERE `內部料號`='" + search_company_id + "'"
        mycursor.execute(sql)
        has_id_data = mycursor.fetchall()
        # print("has_id_data:", has_id_data)

        if len(has_id_data) > 0:
            has_id_table_list.append(table_data[i])
    print("has_id_table_list:", has_id_table_list)

    # 查詢是否有此 BOM 表 (資料表)
    has_table_list = []
    sql = "SELECT `TABLE_NAME` FROM information_schema.TABLES WHERE TABLE_NAME='" + search_company_id + "'"
    # sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE `TABLE_NAME` LIKE '%0711%' ORDER BY TABLE_NAME"
    print("has_table_list sql:", sql)
    mycursor.execute(sql)
    has_id_table_data = mycursor.fetchall()
    print("has_id_table_data:", has_id_table_data)
    if len(has_id_table_data) > 0:
        has_table_list.append(search_company_id)
    print("has_table_list:", has_table_list)

    mydb.commit()
    mydb.close()

    return jsonify({"sql_data":sql_data,
                    "has_id_table_list":has_id_table_list,
                    "has_table_list":has_table_list})



# 更新 內部料號
@app.route('/update_companyid_table_<old_company_id>_<new_company_id>', methods=['GET'])
def update_companyid_table(old_company_id, new_company_id):
    mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
    # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
    mycursor = mydb.cursor()
    sql = "SELECT `TABLE_NAME` " + \
          "FROM information_schema.columns " +  \
          "WHERE `COLUMN_NAME`='內部料號' AND `TABLE_SCHEMA`='erp'"
    # print("sql: ", sql)
    mycursor.execute(sql)
    table_data = mycursor.fetchall()
    table_data = [g[0].replace('t', 'T') for g in table_data]
    print("table_data: ", table_data)           #  ['03040001T01', '03040002T01', '03040003T01', ... ]
    
    has_id_table_list = []
    # 從 erp 資料庫內找到有符合內部料號的 table
    for i in range(0, len(table_data)):
        sql = "SELECT `內部料號` " + \
              "FROM " + table_data[i] + " " +  \
              "WHERE `內部料號`='" + old_company_id + "'"
        mycursor.execute(sql)
        has_id_data = mycursor.fetchall()
        # print("has_id_data:", has_id_data)

        if len(has_id_data) > 0:
            has_id_table_list.append(table_data[i])
    print("has_id_table_list:", has_id_table_list)
    # # 更新資料表內之內部料號
    for j in range(0, len(has_id_table_list)):
        if has_id_table_list[j] == '零件表':
            sql = "UPDATE `零件表` SET `內部料號`='" + new_company_id + "',`會計科目`='" + new_company_id[0:2] + "',`類別`='" + new_company_id[2:4] + "',`流水號`='" + new_company_id[4:8] + "',`供應商`='" + new_company_id[8:11] + "'" + \
                  " WHERE `內部料號`='" + old_company_id + "'"
        else :
            sql = "UPDATE `" + has_id_table_list[j] + "` SET `內部料號`='" + new_company_id + "'" + \
                  " WHERE `內部料號`='" + old_company_id + "'"
        mycursor.execute(sql)
        has_id_data_update = mycursor.fetchall()
        print("UPDATE TABLE item: ", sql)
        print("UPDATE TABLE item SQL: ", has_id_data_update)

    # 查詢是否有此 BOM 表 (資料表)
    has_table_list = []
    sql = "SELECT `TABLE_NAME` FROM information_schema.TABLES WHERE TABLE_NAME='" + old_company_id + "' AND`TABLE_SCHEMA`='erp'"
    # sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE `TABLE_NAME` LIKE '%0711%' ORDER BY TABLE_NAME"
    print("has_table_list sql:", sql)
    mycursor.execute(sql)
    has_id_table_data = mycursor.fetchall()
    print("has_id_table_data:", has_id_table_data)
    if len(has_id_table_data) > 0:
        has_table_list.append(old_company_id)
        # # 更新 BOM 表名稱
        sql = "RENAME TABLE " + old_company_id + " TO " + new_company_id + ""
        mycursor.execute(sql)
        has_id_table_data_update = mycursor.fetchall()
        print("has_table_list SQL:", has_id_table_data_update)
        print("UPDATE TABLE name: ", sql)
    print("has_table_list:", has_table_list)

    print(new_company_id)
    print(has_id_table_list)
    print(has_table_list)

    mydb.commit()
    mydb.close()

    return jsonify({"new_company_id":new_company_id,
                    "has_id_table_list":has_id_table_list,
                    "has_table_list":has_table_list})



# 建立 BOM 表
@app.route('/create_BOM', methods=['GET', 'POST'])
def create_BOM():
    if 'user_id' in session:
        # 紀錄使用者名稱 USERNAME
        USERNAME = session['user_name']
         # 開啟本機 erp 資料庫
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()
        # 獲取資料表內資料
        sql = '''SELECT 內部料號, 會計科目表.會計科目_名稱, 類別表.類別_名稱, 流水號, 供應商.公司, 名稱
                    FROM (
                            (零件表
                            INNER JOIN 供應商
                            ON 供應商.供應商 = 零件表.供應商
                            )
                        INNER JOIN 類別表
                        ON 類別表.類別_編號 = 零件表.類別
                    ) 
                    INNER JOIN 會計科目表 
                    ON 會計科目表.會計科目_編號 = 零件表.會計科目
                    '''
        mycursor.execute(sql)
        content = mycursor.fetchall()
        
        # 獲取欄位名稱
        sql = "SHOW FIELDS FROM 零件表"
        mycursor.execute(sql)
        labels = mycursor.fetchall()
        # print("labels: ", labels)                   
        # # labels:  (('產品', 'varchar(4)', 'YES', '', None, ''), ('系統', 'varchar(5)', 'YES', '', None, ''), 
        labels = [g[0] for g in labels]
        # print("labels: ", labels)                   
        # # labels:  ['產品', '系統', '系統圖號', '狀態', '進貨狀態', '序號', '組序號', 

        # 獲取會計科目欄位
        AccountingSubjects = "SELECT * FROM 會計科目表"
        mycursor.execute(AccountingSubjects)
        AccountingSubjects_data = mycursor.fetchall()
        # print("AccountingSubjects_data", AccountingSubjects_data)     
        # # (('01', '成品'), ('02', '系統成品'), ('03', '模組半成品'), ('04', '零件'), ('05', '工具'))

        # 獲取類別欄位
        Category = "SELECT * FROM 類別表"
        mycursor.execute(Category)
        Category_data = mycursor.fetchall()

        # 獲取供應商欄位
        Supplier = "SELECT * FROM 供應商"
        mycursor.execute(Supplier)
        Supplier_data = mycursor.fetchall()

        # 獲取零件名稱
        Component = "SELECT 名稱 FROM 零件表"
        mycursor.execute(Component)
        Component_data = mycursor.fetchall()

        # # 獲取零件內部料號
        # Company_ID = "SELECT 內部料號 FROM 零件表"
        # mycursor.execute(Company_ID)
        # Company_ID_data = mycursor.fetchall()

        # 獲取零件名稱
        Component_id_name = "SELECT 內部料號, 名稱 FROM 零件表"
        mycursor.execute(Component_id_name)
        Component_id_name = mycursor.fetchall()

        # 取得內部料號/零件名稱/原廠料號 新增內部料號時使用
        sql = "SELECT 零件表.內部料號, 零件表.名稱, 規格表.原廠料號 " + \
            "FROM 零件表 " + \
            "INNER JOIN 規格表 " + \
            "ON 零件表.內部料號 = 規格表.內部料號 "
        mycursor.execute(sql)
        Component_id_name_salesnum = mycursor.fetchall()

        mydb.close()

        return render_template('create_BOM.html', title='建立 BOM 表', **locals())

    else:
        return render_template('login.html', title = 'GET')
   


# 新增零件清單項目
@app.route('/create_component_table_item', methods=['GET', 'POST'])
def create_component_table_item():
    if 'user_id' in session:
        # 開啟本機 erp 資料庫
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()

        # # --- 新增零件用 -----------------------------------------------------------------------------------------
        # 獲取會計科目欄位
        AccountingSubjects = "SELECT * FROM 會計科目表"
        mycursor.execute(AccountingSubjects)
        AccountingSubjects_data = mycursor.fetchall()
        # print("AccountingSubjects_data", AccountingSubjects_data)     
        # # (('01', '成品'), ('02', '系統成品'), ('03', '模組半成品'), ('04', '零件'), ('05', '工具'))

        # 獲取類別欄位
        Category = "SELECT * FROM 類別表"
        mycursor.execute(Category)
        Category_data = mycursor.fetchall()

        # 獲取供應商欄位
        Supplier = "SELECT * FROM 供應商"
        mycursor.execute(Supplier)
        Supplier_data = mycursor.fetchall()

        # 獲取零件名稱
        Component = "SELECT 名稱 FROM 零件表"
        mycursor.execute(Component)
        Component_data = mycursor.fetchall()

        # # --- 新增會計科目項目用 -----------------------------------------------------------------------------------------
        accountingsubject_sql = 'SELECT 會計科目_編號 FROM 會計科目表 ORDER BY 會計科目_編號 DESC LIMIT 1'
        # print("accountingsubject_sql: ", accountingsubject_sql)
        mycursor.execute(accountingsubject_sql)
        content = mycursor.fetchall()

        try:
            # print("content: ", content[0][0], type(content[0][0]))
            new_as_sn = int(content[0][0])+1
            new_as_sn = '{0:02d}'.format(new_as_sn)
        except IndexError:
            new_as_sn = '01'

        # # --- 新增類別項目用 -----------------------------------------------------------------------------------------
        category_sql = 'SELECT 類別_編號 FROM 類別表 ORDER BY 類別_編號 DESC LIMIT 1'
        mycursor.execute(category_sql)
        content = mycursor.fetchall()

        try:
            new_ca_sn = int(content[0][0])+1
            new_ca_sn = '{0:02d}'.format(new_ca_sn)
        except IndexError:
            new_ca_sn = '01'

        # --- 新增供應商項目用 -----------------------------------------------------------------------------------------
        supplier_sql = 'SELECT `供應商` FROM `供應商` WHERE `供應商` LIKE "T%" ORDER BY `供應商` DESC LIMIT 1'
        mycursor.execute(supplier_sql)
        content = mycursor.fetchall()

        try:
            print("content[0][0]: ", content[0][0])
            first_digit_number = content[0][0][0:1]
            second_digit_number = content[0][0][1:2]
            third_digit_number = content[0][0][2:]
            # new_su_sn = int(content[0][0][1:])+1
            # new_su_sn = content[0][0][:1] + '{0:02d}'.format(new_su_sn)
            print("first_digit_number: ", first_digit_number)
            print("second_digit_number: ", second_digit_number)
            print("third_digit_number: ", third_digit_number)
            second_digit_list = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 
                                'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
            if second_digit_number in second_digit_list and third_digit_number == '9':
                l = int(second_digit_list.index(second_digit_number))
                print(second_digit_list[l+1])
                new_su_sn = second_digit_list[l+1] + '0'
            elif second_digit_number in second_digit_list and third_digit_number != '9':
                new_su_sn = second_digit_number + str(int(third_digit_number)+1)

        except IndexError:
            print("!!!!! IndexError")
            new_su_sn = '01'
        except ValueError:
            print("!!!!! ValueError")
            new_su_sn = 'ERROR'
        
        mydb.commit()
        mydb.close()


        return render_template('create_component_table_item.html', title='新增零件', **locals())
    else:
        return render_template('login.html', title = 'GET')


# 找到 供應商編號 +1
@app.route('/sup_get/<first_digit_number>', methods=['GET', 'POST'])
def sup_get(first_digit_number):
    # 開啟本機 erp 資料庫
    mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
    # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
    mycursor = mydb.cursor()
    supplier_sql = 'SELECT `供應商` FROM `供應商` WHERE `供應商` LIKE "' + first_digit_number + '%" ORDER BY `供應商` DESC LIMIT 1'
    mycursor.execute(supplier_sql)
    content = mycursor.fetchall()

    try:
        print("content[0][0]: ", content[0][0])
        first_digit_number = content[0][0][0:1]
        second_digit_number = content[0][0][1:2]
        third_digit_number = content[0][0][2:]
        print("first_digit_number: ", first_digit_number)
        print("second_digit_number: ", second_digit_number)
        print("third_digit_number: ", third_digit_number)
        second_digit_list = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 
                             'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
        if second_digit_number in second_digit_list and third_digit_number == '9':
            l = int(second_digit_list.index(second_digit_number))
            new_su_sn = second_digit_list[l+1] + '0'
        elif second_digit_number in second_digit_list and third_digit_number != '9':
            new_su_sn = second_digit_number + str(int(third_digit_number)+1)
        else:
            new_su_sn = int(content[0][0][1:])+1
            new_su_sn = '{0:02d}'.format(new_su_sn)
            if new_su_sn == '100': new_su_sn = 'A0'
            
            

    except IndexError:
        print("!!!!! IndexError")
        new_su_sn = '01'
    # except ValueError:
    #     print("!!!!! ValueError")
    #     new_su_sn = 'ERROR'

    mydb.commit()
    mydb.close()
    
    return jsonify({"sup_sn":new_su_sn})
    

# ERP 測試 四層下拉式選單關聯
@app.route('/drop_down_list', methods=['GET', 'POST'])
def drop_down_list():
    conn = pymysql.connect(
        host='52.69.200.169', 
        port=3306, 
        user='coffee',
        password='skyeye', 
        database='Coffee', 
        charset='utf8'
    )
    cur = conn.cursor()
    #獲取欄位資料
    sql = "select * from product_info"
    cur.execute(sql)
    content = cur.fetchall()
    #獲取欄位名稱
    sql = "SHOW FIELDS FROM product_info"
    cur.execute(sql)
    labels = cur.fetchall()
    # print("labels: ", labels)                   # labels:  (('產品', 'varchar(4)', 'YES', '', None, ''), ('系統', 'varchar(5)', 'YES', '', None, ''), 
    labels = [g[0] for g in labels]
    # print("labels: ", labels)                   # labels:  ['產品', '系統', '系統圖號', '狀態', '進貨狀態', '序號', '組序號',
    return render_template('drop_down_list.html', labels=labels, content=content)

# update_Picture
@app.route('/update_Picture', methods=['GET', 'POST'])
def update_Picture():
    mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
    # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
    mycursor = mydb.cursor()
    
    # 取得內部料號/零件名稱/原廠料號 新增內部料號時使用
    sql = "SELECT 零件表.內部料號, 零件表.名稱, 規格表.原廠料號 " + \
            "FROM 零件表 " + \
            "INNER JOIN 規格表 " + \
            "ON 零件表.內部料號 = 規格表.內部料號 "
    mycursor.execute(sql)
    Component_id_name_salesnum = mycursor.fetchall()

    mydb.commit()
    mydb.close()

    return render_template('update_Picture.html', **locals())

# ERP 測試
@app.route('/search', methods=['GET', 'POST'])
def search():
    if 'user_id' in session:
        # 開啟本機 erp 資料庫
        mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
        # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
        mycursor = mydb.cursor()

        # 獲取欄位資料
        # sql = '''SELECT * FROM table_component_name'''
        sql = '''SELECT 內部料號, 會計科目表.會計科目_名稱, 類別表.類別_名稱, 流水號, 供應商.公司, 名稱
                    FROM (
                            (零件表
                            INNER JOIN 供應商
                            ON 供應商.供應商 = 零件表.供應商
                            )
                        INNER JOIN 類別表
                        ON 類別表.類別_編號 = 零件表.類別
                    ) 
                    INNER JOIN 會計科目表 
                    ON 會計科目表.會計科目_編號 = 零件表.會計科目
                    '''
        mycursor.execute(sql)
        content = mycursor.fetchall()

        #獲取欄位名稱
        sql = "SHOW FIELDS FROM 零件表"
        mycursor.execute(sql)
        labels = mycursor.fetchall()
        # print("labels: ", labels)                   
        # # labels:  (('產品', 'varchar(4)', 'YES', '', None, ''), ('系統', 'varchar(5)', 'YES', '', None, ''), 
        labels = [g[0] for g in labels]
        # print("labels: ", labels)                   
        # # labels:  ['產品', '系統', '系統圖號', '狀態', '進貨狀態', '序號', '組序號', 

        # 獲取會計科目欄位
        AccountingSubjects = "SELECT * FROM 會計科目表"
        mycursor.execute(AccountingSubjects)
        AccountingSubjects_data = mycursor.fetchall()
        # print("AccountingSubjects_data", AccountingSubjects_data)     
        # # (('01', '成品'), ('02', '系統成品'), ('03', '模組半成品'), ('04', '零件'), ('05', '工具'))

        # 獲取類別欄位
        Category = "SELECT * FROM 類別表"
        mycursor.execute(Category)
        Category_data = mycursor.fetchall()

        # 獲取供應商欄位
        Supplier = "SELECT * FROM 供應商"
        mycursor.execute(Supplier)
        Supplier_data = mycursor.fetchall()

        # 獲取零件名稱
        Component = "SELECT 名稱 FROM 零件表"
        mycursor.execute(Component)
        Component_data = mycursor.fetchall()

        mydb.close()
        
        return render_template('search.html', title = 'ERP Search', **locals())
    else:
        return render_template('login.html', title = 'GET')

# 找到 serial number 最大值後 +1
@app.route('/sn_get/<AccountSubject>/<Category>', methods=['GET', 'POST'])
def sn_get(AccountSubject, Category):
    # 開啟本機 erp 資料庫
    mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
    # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
    mycursor = mydb.cursor()
    # 取得流水號最大值
    sql = 'SELECT 流水號 FROM 零件表 '
    sql += 'WHERE 會計科目 = "' + AccountSubject + '" AND 類別 = "' + Category + '" ORDER BY 流水號 DESC LIMIT 1'
    print("sql: ", sql)

    mycursor.execute(sql)
    content = mycursor.fetchall()
    try:
        print("content: ", content[0][0], type(content[0][0]))
        new_sn = int(content[0][0])+1
        new_sn = '{0:04d}'.format(new_sn)
    except IndexError:
        new_sn = '0001'

    mydb.close()
    
    return jsonify({"new_sn":new_sn})

# 找到 serial number 最大值後 +1
@app.route('/table_sn_get', methods=['GET', 'POST'])
def table_sn_get():
    # 開啟本機 erp 資料庫
    mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
    # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
    mycursor = mydb.cursor()
    
    info = request.args.to_dict('sql')
    sql = info['sql']

    mycursor.execute(sql)
    content = mycursor.fetchall()
    try:
        print("content: ", content[0][0], type(content[0][0]))
        new_sn = int(content[0][0][4:8])+1
        new_sn = '{0:04d}'.format(new_sn)
        new_table_id = str(content[0][0][0:4]) + str(new_sn) + str(content[0][0][8:11]).upper()
    except IndexError:
        new_sn = '0001'
        new_table_id = '07120001T01'            # TODO 例外處理 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    print("[table_sn_get] new_sn:", new_sn)
    print("[table_sn_get] new_table_id:", new_table_id)
    mydb.close()
    
    return jsonify({"new_sn":new_sn,
                    "new_table_id":new_table_id})

# 從名稱找到內部料號
# @app.route('/sn_get/<AccountSubject>/<Category>', methods=['GET', 'POST'])
# def zn_get(AccountSubject, Category):
#     # 開啟本機 erp 資料庫
#     mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
    # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
#     mycursor = mydb.cursor()
#     # 取得流水號最大值
#     sql = 'SELECT 流水號 FROM 零件表 '
#     sql += 'WHERE 會計科目 = "' + AccountSubject + '" AND 類別 = "' + Category + '" ORDER BY 流水號 DESC LIMIT 1'
#     print("sql: ", sql)

#     mycursor.execute(sql)
#     content = mycursor.fetchall()
#     try:
#         print("content: ", content[0][0], type(content[0][0]))
#         new_sn = int(content[0][0])+1
#         new_sn = '{0:04d}'.format(new_sn)
#     except IndexError:
#         new_sn = '0001'

#     mydb.close()
    
#     return jsonify({"new_sn":new_sn})


# 匯出 Excel
@app.route('/export_excel/<BOM_company_id>', methods=['GET'])
def export_excel(BOM_company_id):
    mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
    # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
    mycursor = mydb.cursor()
    info = request.args.to_dict('sql')
    sql = info['sql']
    # print("sql: ", sql)
    mycursor.execute(sql)
    sql_data = mycursor.fetchall()
    sql_data = [g for g in sql_data]
    print("sql_data: ", sql_data)

    sql_data_new = []                      # 外面的 LIST
    # BOM 表欄位名稱
    sql_field = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + BOM_company_id + "'"
    print("sql_field: ", sql_field)
    mycursor.execute(sql_field)
    sql_field = mycursor.fetchall()
    sql_field = [g for g in sql_field]
    F = []
    for f in range(0, len(sql_field)):
        F.append(sql_field[f][0])
    # 增加輸出 BOM 表易讀性, 增加 零件表.名稱
    F.insert(1, '名稱')
    sql_data_new.append(F)
    print("sql_data_new: ", sql_data_new)

    # BOM 表內容
    for i in range(0, len(sql_data)):
        J = []                              # 裡面的 LIST
        for j in range(0, len(sql_data[i])):
            J.append(sql_data[i][j])
        sql_data_new.append(J)
    print("sql_data_new: ", sql_data_new, type(sql_data_new))

    CURRENT_PATH = os.path.dirname(__file__)
    print("CURRENT_PATH: ", CURRENT_PATH)
    # 匯出成 Excel
    df = pd.DataFrame(sql_data_new)
    # linux 下載 OK
    # '_' + str(datetime.today().strftime("%m%d")) + '.xlsx'
    # 230 測試
    writer = pd.ExcelWriter(CURRENT_PATH + '/PDF/BOM_' + str(BOM_company_id) + '.xlsx',  engine='xlsxwriter')
    # 本機測試
    # writer = pd.ExcelWriter('C:/Users/USER/Rita/ERP_Rita_tt/PDF/BOM_' + str(BOM_company_id) + '.xlsx',  engine='xlsxwriter')
    df.to_excel(writer, sheet_name=BOM_company_id, encoding='utf8', header=None, index=False)
    writer.save()

    mydb.commit()
    mydb.close()


    return jsonify({"file_location":'C:/Users/USER/Downloads/BOM_' + str(BOM_company_id) + '.xlsx',
                    "sql_data_new":sql_data_new})



# # 下載 server 內的 Excel BOM 表
# @app.route('/server_get_Excel', methods=['GET'])
# def server_get_Excel():
#     filename = 'BOM_03030001T01_1228.xlsx'
#     return send_from_directory('ExcelData', filename, as_attachment=True)

# 從資料表找出符合篩選條件之資料
@app.route('/sql_get', methods=['GET'])
def sql_get():
    mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='Gold@53743001', database='erp', charset='utf8')
    # mydb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='g53743001', database='erp', charset='utf8')
    mycursor = mydb.cursor()
    # 取得網頁傳回來的 SQL 指令
    # sql = "SELECT * FROM 零件表 WHERE 會計科目 = 04"
    info = request.args.to_dict('sql')
    sql = info['sql']
    print("[sql_get]sql: ", sql)
    try:
        mycursor.execute(sql)
        sql_data = mycursor.fetchall()
    except pymysql.err.IntegrityError:
        sql_data = "請注意 ! 單據不可新增重複的零件"
    
    # 獲取欄位名稱
    # 拆解取得資料表資料
    # print("sql_data: ", sql_data)
    # # sql_data:  (('04030001T01', '04', '03', '0001', 'T01', '電阻-18Ω'), ... )

    # Rita 這裡在測試日期格式修改, 優化後調整
    # if 'INNER JOIN 庫存表' in sql:
    #     labels = []
    #     for g in sql_data:
    #         # timeArray = time.strptime(a, "%Y-%m-%d %H:%M:%S")
    #         print("g[0]: ", g[0], type(g[0]), g[0].strftime("%Y-%m-%d %H:%M:%S"))
            
    #         timeString = g[0].strftime("%Y-%m-%d %H:%M:%S")
    #         print("timeString: ", timeString, type(timeString))
    #         labels.append(timeString)
    #         # labels.append(time.strftime("%Y-%m-%d %H:%M:%S"))
    #         labels.append(g[1])
    #         labels.append(g[2])
    # else:
    #     labels = [g for g in sql_data]
    labels = [g for g in sql_data]
    # print("[sql_get]labels: ", labels)

    # 取得欄位資料
    if 'INNER JOIN 規格表' in sql:
        sql_field = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '規格表'"
    elif 'INNER JOIN 庫存表' in sql:
        sql_field = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '庫存表' LIMIT 3"
    elif 'INNER JOIN 銷貨表' in sql:
        sql_field = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '銷貨表'"
    elif 'INNER JOIN 進貨表' in sql:
        sql_field = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '進貨表'"
    # elif 'INNER JOIN 零件表' in sql:
    #     table_name = sql[sql.find('`')+1:]
    #     table_name = table_name[:table_name.find('`')]
    #     # sql_field = "SELECT 零件表.內部料號, 零件表.名稱, " + table_name + ".數量, " + table_name + ".腳位, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS"
    #     sql_field = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '零件表'"
    elif '`' in sql:
        table_name = sql[sql.find('`')+1:]
        table_name = table_name[:table_name.find('`')]
        sql_field = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + table_name + "'"
    else:
        sql_field = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '零件表'"
    print("[sql_get]sql_field: ", sql_field)
    mycursor.execute(sql_field)
    sql_field = mycursor.fetchall()
    # print("[sql_get_finish]sql_field: ", sql_field)
    print("[sql_get_finish]labels: ", labels)

    mydb.commit()
    mydb.close()
     
    return jsonify({"sql_data":sql_data,
                    "labels":labels,
                    "sql_field":sql_field})





#自動關閉所有未使用、掛著的連接
@app.teardown_appcontext
def shutdown_session(exception=None):
    # db.session.remove()
    pass

if __name__ == '__main__':
    # login 將 id / name 存入 Session 時使用, 不用會 RuntimeError
    app.config['SECRET_KEY'] = 'ERPSessionKey'
    app.run(host='0.0.0.0', port=5012, debug=True, threaded=True)