# 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/', methods=['GET', 'POST']) def uploaded_file(filename): return send_from_directory(app.config['UPLOAD_FOLDER'],filename) # 下載測試 @app.route('/download/') def download(file): return send_from_directory('PDF',file, as_attachment=True) @app.route('/DL///_') 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/') def user(name): return render_template('hello.html', name = name) # 詢價單 # @app.route('/RFQ_user__') # 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_') # 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__') 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__') 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_') 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_') 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_') 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//_') 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_') 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_', 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_', 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__', 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/', 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//', 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//', 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/', 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)