箱号绑定程序

可以实现绑定箱号于装箱产品的流水号：

import os
import sys
import time
import openpyxl
import tkinter as tk
import tkinter.messagebox
from ttkbootstrap.dialogs.dialogs import *
import tktooltip as tips
import pymysql


def warning_prepare(text):
    tk.messagebox.showinfo(title="警告", message="{}".format(text))


def warning_gui(text):
    Messagebox.show_info(title="警告", message="{}".format(text))


def write_number(machine_number, info):
    case_write = openpyxl.load_workbook('{}.xlsx'.format(info[2]))
    sheet = case_write.active
    biggest = sheet.max_row
    for k in range(biggest - int(info[1]) + 1, biggest + 1):
        if not sheet.cell(k, 2).value:
            sheet.cell(k, 2).value = machine_number
            p.set(k + int(info[1]) - biggest + 1)
            if k == biggest:
                old_str = sheet.cell(biggest - int(info[1]) + 1, 1).value[len(info[3]):]
                new_numb = int(old_str) + 1
                sheet.cell(biggest + 1, 1).value = info[3] + str(new_numb)
                sheet.merge_cells('A{}:A{}'.format(biggest + 1, biggest + int(info[1])))
                p.set(1)
            break  # 跳出循环、只写入一次

    case_write.save('{}.xlsx'.format(info[2]))


def alter_db(item, chart_name, info, barcode, box_number):
    conn = pymysql.connect(host=item[0].strip('\n'),
                           user=item[1].strip('\n'),
                           passwd=item[2].strip('\n'),
                           db=item[3].strip('\n'),
                           port=int(item[4].strip('\n')),
                           charset=item[5].strip('\n')
                           )
    curser = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 若表不存在则创建表
    sql = 'create table if not exists %s(ID bigint primary key auto_increment, \
    model varchar(255), \
    order_number varchar(255), \
    barcode varchar(255) unique, \
    case_number varchar(255), \
    time varchar(255));' % chart_name
    curser.execute(sql)
    # 写入数据
    sql = 'insert into {} (model, order_number, barcode, case_number, time) value(%s, %s, %s, %s, %s)'.format(
        chart_name)
    now = time.strftime("%Y/%m/%d/%H:%M:%S")
    piece_of_information = (info[0], info[2], barcode, box_number, now)
    curser.execute(sql, piece_of_information)
    conn.commit()  # 没有此语句将无法保存数据库的修改
    curser.close()
    conn.close()


def check_db(item, chart_name, barcode):
    conn = pymysql.connect(host=item[0].strip('\n'),
                           user=item[1].strip('\n'),
                           passwd=item[2].strip('\n'),
                           db=item[3].strip('\n'),
                           port=int(item[4].strip('\n')),
                           charset=item[5].strip('\n')
                           )
    curser = conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = "select * from %s where barcode = '%s';" % (chart_name, barcode)
    curser.execute(sql)
    result = curser.fetchone()
    curser.close()
    conn.close()
    if result:
        if result['station'] == 'FQC04_TEST':
            return True
        else:
            return False
    else:
        return False


def delete_one(info, item, chart_name):
    def sure():
        # 删除本地表格的记录
        xlsx_object = openpyxl.load_workbook('{}.xlsx'.format(info[2]))
        sheet_object = xlsx_object.active
        biggest_1 = sheet_object.max_row
        for k in range(biggest_1 - int(info[1]) + 1, biggest_1 + 1):
            if not sheet_object.cell(k, 2).value:
                if k == biggest_1 - int(info[1]) + 1:
                    sheet_object.unmerge_cells('A{}:A{}'.format(biggest_1 - int(info[1]) + 1, biggest_1))
                    sheet_object.cell(biggest_1 - int(info[1]) + 1, 1).value = None
                    sheet_object.cell(biggest_1 - int(info[1]), 2).value = None
                    sheet_object.delete_rows(biggest_1 - int(info[1])+1, biggest_1)
                    check_label.configure(text="删除成功")
                    break
                else:
                    sheet_object.cell(k-1, 2).value = None
                    check_label.configure(text="删除成功")
                    break
        xlsx_object.save('{}.xlsx'.format(info[2]))
        # 删除数据库的记录
        conn = pymysql.connect(host=item[0].strip('\n'),
                               user=item[1].strip('\n'),
                               passwd=item[2].strip('\n'),
                               db=item[3].strip('\n'),
                               port=int(item[4].strip('\n')),
                               charset=item[5].strip('\n')
                               )
        curser = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = 'select * from %s order by ID desc limit 1' % chart_name
        curser.execute(sql)
        last_id = curser.fetchone()
        sql = "delete from %s where ID = '%s'" % (chart_name, last_id['ID'])
        curser.execute(sql)
        conn.commit()  # 没有此语句将无法保存数据库的修改
        curser.close()
        conn.close()

        app.update()  # 显示“删除成功”
        app.after(1000)
        app.destroy()

    def deny():
        app.destroy()

    app = ttk.Toplevel(title="删除上条")
    check_label = ttk.Label(app, text="确认要删除上条信息吗？")
    check_label.grid(row=0, column=0, columnspan=2, pady=50)
    check_button = ttk.Button(app, text="是", bootstyle="outline-toolbutton", command=sure)
    check_button.grid(row=1, column=0, padx=20, pady=10, sticky='e')
    deny_button = ttk.Button(app, text="否", bootstyle="outline-toolbutton", command=deny)
    deny_button.grid(row=1, column=1, padx=20, pady=10, sticky='w')
    app.mainloop()


def replace_one(info, item, chart_name):
    def sure():
        old_code = old_entry.get()
        new_code = new_entry.get()
        # 更新本地表格
        replace_xlsx = openpyxl.load_workbook('{}.xlsx'.format(info[2]))
        replace_sheet = replace_xlsx.active
        replace_column = replace_sheet['B']
        j = []
        for cel in replace_column:
            j.append(cel.value)
        if new_code in j:
            warning_gui("重码了，请排查流水号")
        else:
            if old_code not in j:
                warning_gui("需替换的条码不存在")
            else:
                for cel in replace_column:
                    if cel.value == old_code:
                        cel.value = new_code
                        replace_xlsx.save('{}.xlsx'.format(info[2]))
                        old_label.destroy()
                        old_entry.destroy()
                        new_label.destroy()
                        new_entry.destroy()
                        check_button.destroy()
                        deny_button.destroy()
                        success_label = ttk.Label(app_1, text='替换成功')
                        success_label.grid(row=0, column=0, pady=50, padx=50)
        # 更新数据库 (可能存在本地文件报错，但数据库能执行的情况)
        conn = pymysql.connect(host=item[0].strip('\n'),
                               user=item[1].strip('\n'),
                               passwd=item[2].strip('\n'),
                               db=item[3].strip('\n'),
                               port=int(item[4].strip('\n')),
                               charset=item[5].strip('\n')
                               )
        curser = conn.cursor(cursor=pymysql.cursors.DictCursor)
        now = time.strftime("%Y/%m/%d/%H:%M:%S")
        sql = "update %s set barcode = '%s', time = '%s' where barcode = '%s'" % (chart_name, new_code, now, old_code)
        curser.execute(sql)
        conn.commit()  # 没有此语句将无法保存数据库的修改
        curser.close()
        conn.close()

        app_1.update()
        app_1.after(1000)
        app_1.destroy()

    def deny():
        app_1.destroy()

    app_1 = ttk.Toplevel(title="替换一条")
    old_label = ttk.Label(app_1, text="请输入原条码：")
    old_label.grid(row=0, column=0, padx=10, pady=10)
    new_label = ttk.Label(app_1, text="请输入新条码：")
    new_label.grid(row=1, column=0, padx=10, pady=10)
    old_entry = ttk.Entry(app_1)
    old_entry.grid(row=0, column=1, padx=10, pady=10)
    new_entry = ttk.Entry(app_1)
    new_entry.grid(row=1, column=1, padx=10, pady=10)
    check_button = ttk.Button(app_1, text="确定", bootstyle="outline-toolbutton", command=sure)
    check_button.grid(row=2, column=0, padx=20, pady=10, sticky='e')
    deny_button = ttk.Button(app_1, text="取消", bootstyle="outline-toolbutton", command=deny)
    deny_button.grid(row=2, column=1, padx=20, pady=10)
    app_1.mainloop()


def prepare_gui():
    prepare = tk.Tk()
    prepare.title("信息预设窗口")
    prepare.geometry("410x260")
    prepare.iconbitmap('image\\仓库.ico')
    prepare.resizable(height=False, width=False)
    frame_top = tk.LabelFrame(prepare, text="输入新信息开始", width=400, height=160)
    frame_bottom = tk.LabelFrame(prepare, text='使用记录快速开始', width=400, height=80)
    frame_top.grid(row=0, column=0, padx=5, pady=3)
    frame_top.grid_propagate(flag=False)
    frame_bottom.grid(row=1, column=0, padx=5, pady=3)
    frame_bottom.grid_propagate(flag=False)

    """ 定义机型 """
    model_label = tk.Label(frame_top, text="生产机型：")
    model_input_box = tk.Entry(frame_top, width=15)  # 输入框
    model_label.grid(row=0, column=0, padx=5, pady=10, sticky='e')
    model_input_box.grid(row=0, column=1, padx=5, pady=10, sticky='w')

    """ 定义每箱数量"""
    capacity_label = tk.Label(frame_top, text="每箱容量：")
    capacity_input_box = tk.Entry(frame_top, width=15)
    capacity_label.grid(row=0, column=2, padx=5, pady=10, sticky='e')
    capacity_input_box.grid(row=0, column=3, columnspan=2, padx=5, pady=10, sticky='w')

    """生产工单号"""
    the_order_number_label = tk.Label(frame_top, text="生产工单：")
    the_order_number_input_box = tk.Entry(frame_top, width=15)
    the_order_number_label.grid(row=1, column=0, padx=5, pady=10, sticky='e')
    the_order_number_input_box.grid(row=1, column=1, padx=5, pady=10, sticky='w')
    tip_3 = tips.ToolTip(the_order_number_input_box, msg="此处填写的内容即为表格名称", bg='LightYellow')

    """从那一箱开始绑定"""
    the_package_label = tk.Label(frame_top, text="开始箱号：")
    the_package_number_input_box_1 = tk.Entry(frame_top, width=9)
    the_package_number_input_box_2 = tk.Entry(frame_top, width=4)
    the_package_label.grid(row=1, column=2, padx=5, pady=10, sticky='e')
    the_package_number_input_box_1.grid(row=1, column=3, padx=2, pady=10, sticky='w')
    the_package_number_input_box_2.grid(row=1, column=4, padx=2, pady=10, sticky='w')
    tip_1 = tips.ToolTip(the_package_number_input_box_1, msg="填箱号固定不变的部分\n没有时可不填,eg:TW-1009-",
                         bg='LightYellow')
    tip_2 = tips.ToolTip(the_package_number_input_box_2, msg="填箱号变化部分\neg:001", bg='LightYellow')

    project_name = tk.StringVar()
    quantity_per_case = tk.StringVar()
    order_no = tk.StringVar()
    package_no_fix = tk.StringVar()
    package_no_chg = tk.StringVar()

    def kill():
        model = model_input_box.get()  # 获取输入框内容
        capacity = capacity_input_box.get()
        the_order_number = the_order_number_input_box.get()
        package_1 = the_package_number_input_box_1.get()
        package_2 = the_package_number_input_box_2.get()
        if model == '':
            warning_prepare("生产机型输入为空")
        elif capacity == '':
            warning_prepare("每箱数量输入为空")
        elif the_order_number == '':
            warning_prepare("生产工单输入为空")
        elif package_2 == '':
            warning_prepare("开始箱号输入为空")
        else:
            project_name.set(model)
            quantity_per_case.set(capacity)
            order_no.set(the_order_number)
            package_no_fix.set(package_1)
            package_no_chg.set(package_2)
            prepare.after(1000)
            prepare.destroy()

    """确定按钮"""
    sure_button_1 = tk.Button(frame_top, text="确定", command=kill, width=8)
    sure_button_1.grid(row=2, column=0, columnspan=5, pady=5)

    """查询历史记录"""
    with open('record.py', 'r') as f:
        record = f.readlines()
    last = record[-1].strip('\n')
    record_list = last.split(' ')  # 将记录转化为列表
    query_label = tk.Label(frame_bottom, text='上次记录：')
    query_label.grid(row=0, column=0, padx=2, pady=10)
    query_comb = tk.Label(frame_bottom, text=last)
    query_comb.grid(row=0, column=1, padx=2, pady=10)

    def kill_2():
        if last == '':
            warning_prepare("无历史记录")
        else:
            project_name.set(record_list[0])
            quantity_per_case.set(record_list[1])
            order_no.set(record_list[2])
            package_no_fix.set(record_list[3])
            package_no_chg.set(record_list[4])
            prepare.after(1000)
            prepare.destroy()

    sure_button_2 = tk.Button(frame_bottom, text="确定", command=kill_2, width=8)
    sure_button_2.grid(row=0, column=2, padx=5, pady=5)

    prepare.mainloop()
    return [project_name.get(), quantity_per_case.get(), order_no.get(), package_no_fix.get(), package_no_chg.get()]


def main_gui(info):
    root = ttk.Window()
    root.title("箱号绑定系统")  # 窗口名称
    root.geometry("870x500")
    root.iconbitmap('image\\system.ico')  # 设置窗口图标
    root.resizable(height=False, width=False)

    ttn = ttk.IntVar()  # 入库总量
    ttn.set(0)
    temp_record = ttk.StringVar()
    temp_record.set(info[4])
    global p
    p = ttk.IntVar()  # 下一个入库的机器的位号
    p.set(0)
    with open('database_config_write.txt', 'r') as f_1:
        item_write = f_1.readlines()
    with open('chart_config_write.txt', 'r') as f_2:
        chart_name_write = f_2.readline()
    with open('database_config_read.txt', 'r') as f_3:
        item_read = f_3.readlines()
    with open('chart_config_read.txt', 'r') as f_4:
        chart_name_read = f_4.readline()

    os.chdir('report\\{}'.format(info[0]))

    frame_up_left = ttk.Labelframe(root, text="预设信息区", width=500, height=148, bootstyle='info')
    frame_right = ttk.Labelframe(root, text="信息展示区", width=350, height=400, bootstyle='primary')
    frame_middle_left = ttk.Labelframe(root, text="信息处理区", width=500, height=248, bootstyle='warning')
    frame_bottom = ttk.Frame(root, width=800, height=100)

    frame_up_left.grid(row=0, column=0, padx=5, pady=2)
    frame_up_left.grid_propagate(flag=False)
    frame_right.grid(row=0, rowspan=2, column=1, padx=5, pady=2)
    frame_right.grid_propagate(flag=False)
    frame_middle_left.grid(row=1, column=0, padx=5, pady=2)
    frame_middle_left.grid_propagate(flag=False)
    frame_bottom.grid(row=2, column=0, columnspan=2, padx=5, pady=2)
    frame_bottom.grid_propagate(flag=False)

    """ 定义机型 """
    model_label = ttk.Label(frame_up_left, text="生产机型：" + info[0])
    model_label.grid(row=0, column=0, padx=35, pady=15, sticky='w')

    """ 定义每箱数量"""
    capacity_label = ttk.Label(frame_up_left, text="每箱容量：" + info[1])
    capacity_label.grid(row=0, column=1, padx=5, pady=15, sticky='w')

    """生产工单号"""
    the_order_number_label = ttk.Label(frame_up_left, text="订单号：" + info[2])
    the_order_number_label.grid(row=1, column=0, padx=35, pady=15, sticky='w')

    """箱号标签"""
    the_package_start_number = ttk.Label(frame_up_left, text="开始箱号："+info[3]+info[4])
    the_package_start_number.grid(row=1, column=1, padx=5, pady=15, sticky='w')

    """条码样本"""
    sample_of_code_label = ttk.Label(frame_middle_left, text="扫描条码样本：")
    sample_of_code_label.grid(row=0, column=0, padx=20, pady=10, sticky='e')

    sample_of_code_entry = ttk.Entry(frame_middle_left)
    sample_of_code_entry.grid(row=0, column=1, columnspan=2, pady=10, sticky='w')

    """绑定箱号输入框"""
    input_label = ttk.Label(frame_middle_left, text="扫描车身标签：")
    input_label.grid(row=2, column=0, padx=20, pady=10, sticky='e')
    input_number = ttk.Entry(frame_middle_left)
    input_number.grid(row=2, column=1, columnspan=2, pady=10, sticky='w')

    """当前箱号标签"""
    present_no_label = ttk.Label(frame_middle_left, text="开始箱号:{}".format(info[4]))
    present_no_label.grid(row=1, column=0, padx=20, pady=10, sticky='e')

    present_storage_no = ttk.Label(frame_middle_left, text="开始默认位号：0")
    present_storage_no.grid(row=1, column=1, columnspan=2, padx=30, pady=10, sticky='w')
    """按钮"""
    delete_button = ttk.Button(frame_middle_left, text="清除上条信息", bootstyle='danger',
                               command=lambda: delete_one(info, item_write, chart_name_write))
    delete_button.grid(row=3, column=0, pady=5, sticky='w')
    replace_button = ttk.Button(frame_middle_left, text="替换一条信息", bootstyle='default',
                                command=lambda: replace_one(info, item_write, chart_name_write))
    replace_button.grid(row=3, column=1, pady=5, sticky='w')
    switch = ttk.IntVar()
    switch.set(0)
    check_button = ttk.Checkbutton(frame_middle_left, bootstyle="round-toggle", text='前工序确认',
                                   variable=switch, onvalue=1, offvalue=0)
    check_button.grid(row=3, column=2, padx=5, pady=5, sticky='w')

    """信息展示区"""
    total_number_label = ttk.Label(frame_right, text="今日入库总量：")
    total_number_label.grid(row=0, column=0, pady=10, sticky='e')

    total_number = ttk.Label(frame_right, text=ttn.get(), foreground='red')
    total_number.grid(row=0, column=1, pady=10, sticky='w')
    recent_label = ttk.Label(frame_right, text="最近入库{}台明细：".format(info[1]))
    recent_label.grid(row=1, column=0, sticky='e')

    recent_num = ttk.Label(frame_right, text="")
    recent_num.grid(row=2, column=0, columnspan=2, sticky='e')

    """作者标签"""
    designer = ttk.Label(frame_bottom, text="Designer: Jianbo.Ai\nVer:3.2", font=('Arial', 10))  # 作者标签
    designer.place(relx=0, rely=0.4)

    """时间标签"""
    begin_refresh = time.strftime("%Y/%m/%d\n%H:%M:%S")
    show_time = ttk.Label(frame_bottom, text=begin_refresh, font=('Arial', 10))
    show_time.place(relx=0.88, rely=0.4)

    """上一个入库条码"""

    def refresh():
        """刷新时间"""
        now_refresh = time.strftime("%Y/%m/%d\n%H:%M:%S")
        show_time.configure(text=now_refresh)
        # 持续获取预设框的值
        example_code = sample_of_code_entry.get()
        # 持续获取输入框的值
        barcode = input_number.get()
        if len(barcode) != 0 and len(example_code) != 0:
            if len(barcode) == len(example_code):
                """判断是否重码"""
                sheet = openpyxl.load_workbook('{}.xlsx'.format(info[2])).active
                judge_repeat_column = sheet['B']
                j = []
                for col in judge_repeat_column:
                    j.append(col.value)
                if barcode in j:
                    warning_gui("重码了，请排查流水号")
                    input_number.delete('0', 'end')  # 清空输入框
                else:         # 未重码
                    if switch.get():
                        check_result = check_db(item_read, chart_name_read, barcode)
                        if check_result:
                            write_number(barcode, info)
                            ttn.set(ttn.get() + 1)
                            work_book = openpyxl.load_workbook('{}.xlsx'.format(info[2]))
                            work_sheet = work_book.active
                            the__max__row = work_sheet.max_row
                            if p.get() == 1:
                                box_number = work_sheet.cell(the__max__row + 1 - 2 * int(info[1]), 1).value
                            else:
                                box_number = work_sheet.cell(the__max__row + 1 - int(info[1]), 1).value
                            alter_db(item_write, chart_name_write, info, barcode, box_number)  # 将数据写入数据库
                            input_number.delete('0', 'end')  # 清空输入框
                            """列表一箱的数据"""
                            column_b = work_sheet['B'][-20:]
                            t_list = []
                            for col_1 in column_b:
                                t_list.append(col_1.value)
                            b = list(filter(None, t_list))[-int(info[1]):]
                            recent_num.configure(text='\n'.join(b))
                            present_no_label.configure(text="当前箱号:{}".format(work_sheet.cell(the__max__row - int(info[1]) + 1, 1).value[len(info[3]):]))
                            present_storage_no.configure(text="当前位号：{}".format(p.get()))
                            total_number.configure(text=ttn.get())
                            temp_record.set(work_sheet.cell(the__max__row - int(info[1]) + 1, 1).value[len(info[3]):])
                        else:
                            warning_gui('该机器未过FQC4测试站，禁止入库！')
                            input_number.delete('0', 'end')  # 清空小条码输入框
                    else:
                        write_number(barcode, info)
                        ttn.set(ttn.get() + 1)
                        work_book = openpyxl.load_workbook('{}.xlsx'.format(info[2]))
                        work_sheet = work_book.active
                        the__max__row = work_sheet.max_row
                        if p.get() == 1:
                            box_number = work_sheet.cell(the__max__row + 1 - 2 * int(info[1]), 1).value
                        else:
                            box_number = work_sheet.cell(the__max__row + 1 - int(info[1]), 1).value
                        alter_db(item_write, chart_name_write, info, barcode, box_number)  # 将数据写入数据库
                        input_number.delete('0', 'end')  # 清空输入框
                        """列表一箱的数据"""
                        column_b = work_sheet['B'][-20:]
                        t_list = []
                        for col_1 in column_b:
                            t_list.append(col_1.value)
                        b = list(filter(None, t_list))[-int(info[1]):]
                        recent_num.configure(text='\n'.join(b))
                        present_no_label.configure(text="当前箱号:{}"
                                                   .format(work_sheet.cell(the__max__row-int(info[1]) + 1, 1).value[len(info[3]):]))
                        present_storage_no.configure(text="当前位号：{}".format(p.get()))
                        total_number.configure(text=ttn.get())
                        temp_record.set(work_sheet.cell(the__max__row-int(info[1]) + 1, 1).value[len(info[3]):])
        root.after(1000, refresh)  # 函数嵌套，形成循环

    root.after(1000, refresh)

    def save_record():
        a = [info[0], info[1], info[2], info[3], temp_record.get()]
        q = ' '.join(a)
        os.chdir('..')
        os.chdir('..')
        with open('record.py', 'a') as s:
            s.write(q)
            s.write('\n')
        root.destroy()

    root.protocol('WM_DELETE_WINDOW', save_record)  # 修改关闭窗口按键功能
    root.mainloop()  # 运行窗体


var = prepare_gui()  # 启动预设框
"""无输入关闭预设框则退出程序"""
for i in range(0, 3):
    if var[i] == "":
        sys.exit()
"""判断是否存在机型名称文件夹，否则创建"""
if not os.path.exists('report\\{}'.format(var[0])):
    os.mkdir('report\\{}'.format(var[0]))
    os.chdir('report\\{}'.format(var[0]))
    workbook = openpyxl.Workbook()
    worksheet = workbook.active
    worksheet.cell(1, 1).value = var[3] + var[4]
    worksheet.merge_cells('A{}:A{}'.format(1, int(var[1])))
    workbook.save('{}.xlsx'.format(var[2]))
    os.chdir('..')
    os.chdir('..')  # 返回上级目录以加载图标
else:
    os.chdir('report\\{}'.format(var[0]))
    if os.path.exists('{}.xlsx'.format(var[2])):  # 文件已存在则需判断输入的开始箱号是否与表格中的最后箱号一致
        workbook = openpyxl.load_workbook('{}.xlsx'.format(var[2]))
        worksheet = workbook.active
        the_max_row = worksheet.max_row
        if worksheet.cell(the_max_row - int(var[1]) + 1, 1).value == var[3] + var[4]:
            pass
        else:
            warning_prepare("起始箱号异常")
            sys.exit()

    else:  # 文件不存在则创建新文件并写入起始箱号，合并单元格
        workbook = openpyxl.Workbook()
        worksheet = workbook.active
        worksheet.cell(1, 1).value = var[3] + var[4]
        worksheet.merge_cells('A{}:A{}'.format(1, int(var[1])))
        workbook.save('{}.xlsx'.format(var[2]))
    os.chdir('..')  # 返回上级目录以加载图标
    os.chdir('..')

main_gui(var)