def refresh_list():
rs1 = run_select_sql(
"select loc_id from locations where loc_type = 'Warehouse' order by loc_id"
)
warehouse_list = [i[0] for i in rs1]
combobox_warehouse_2['values'] = warehouse_list
combobox_warehouse_2.set('')
combobox_warehouse_3['values'] = warehouse_list
combobox_warehouse_3.set('')
rs2 = run_select_sql(
"select loc_id from locations where loc_type in ('AREA','STO') order by loc_id"
)
location_list = [i[0] for i in rs2]
combobox_location_2['values'] = location_list
combobox_location_2.set('')
combobox_location_3.set('')
def sign_to_userinfo():
global usrs_info
datecode = 'Polestar' + datetime.datetime.now().strftime('%Y%m%d') + new_name.get().upper()
if new_invitation_code.get().upper() != str_to_hex(datecode).upper():
tk.messagebox.showerror('错误', '邀请码不正确，联系管理员获取。')
else:
np = new_pwd.get()
npf = new_pwd_confirm.get()
nn = new_name.get().upper()
if np != npf:
tk.messagebox.showerror('错误', '密码和确认密码不一致！')
elif nn in usrs_info:
tk.messagebox.showerror('错误', '此用户已存在！')
else:
sql_insert = '''
insert into user_password (username, password) values ('{}','{}')
'''.format(nn, np)
ret = run_iud_sql(sql_insert)
if ret == ['Error in database change operation!']:
tk.messagebox.showerror('错误', '更新数据库异常，联系系统管理员。')
else:
rs_user = run_select_sql('select username, password from user_password')
usrs_info = {i[0]: i[1] for i in rs_user}
tk.messagebox.showinfo('欢迎', '你已成功注册！')
w_signup.destroy()
def refresh_location_list3(arg1):
rs = run_select_sql(
"select loc_id, subloc_id from location_structure order by loc_id, subloc_id"
)
df = pd.DataFrame(rs)
location_list = list(df[df[0] == e_warehouse_3.get()][1])
combobox_location_3['values'] = location_list
combobox_location_3.current(0)
def run(self):
sql1 = "update job_config set is_running = 'Y' where job_name = '{}'".format(self.job_name)
run_iud_sql(sql1)
print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), " ",
'Step1. Set the job: {} status to running, prevent duplicated running job.'.format(self.job_name),
file=logfile)
rs = run_select_sql("select job_seq.nextval from dual")
job_seq = rs[0][0]
sql2 = "insert into job_logs (job_id, job_name, start_time, active) values ('{}', '{}', sysdate, 'Y')".format(
job_seq, self.job_name)
run_iud_sql(sql2)
print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), " ",
'Insert records into job_logs table successfully.', file=logfile)
# get data
rs1 = run_select_sql(self.sql_run)
try:
df = pd.DataFrame(rs1)
df.columns = self.excel_columns.split(',')
file_name = 'Job' + self.job_name + '_' + datetime.datetime.now().strftime('%Y%m%d%H%M%S') + '.xlsx'
output_file_name = output_temp_dir + file_name
writer = pd.ExcelWriter(output_file_name)
df.to_excel(writer, index=False)
writer.save()
print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), " ",
'Step2. Get the data for job: {} successfully and generate excel file successfully.'.format(
self.job_name),
file=logfile)
# try to get email receiver list and send email.
sql_email = "select receiver from job_emails where job_name = '{}' and active = 'Y'".format(self.job_name)
rs2 = run_select_sql(sql_email)
receivers = ';'.join([i[0] for i in rs2])
email_instance = Email(self.email_title, self.email_content, receivers, output_file_name, file_name)
email_instance.send_mail()
except:
pass
sql3 = "update job_logs set finish_time = sysdate, active = 'N' where job_id = '{}'".format(job_seq)
run_iud_sql(sql3)
sql4 = "update job_config set is_running = 'N' where job_name = '{}'".format(self.job_name)
run_iud_sql(sql4)
print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), " ",
'Step4. Set the job: {} status to finished, waiting for next running.'.format(self.job_name),
file=logfile)
def search():
rs = run_select_sql(
"select partno, part_descr, min_qty, max_qty, valid_day, supplier_id, invalid_flag, chem_flag, unit, serial_flag from parts where partno like upper('%{}%') and rownum = 1"
.format(e_partno_input.get()))
try:
rslt = [i if i != None else ''
for i in rs[0]] # change None to '' in result from sql
e_partno.set(rslt[0])
e_descr.set(rslt[1])
e_min.set(rslt[2])
e_max.set(rslt[3])
e_warrenty.set(rslt[4])
e_supplier.set(rslt[5])
# 根据invalid flag和chemical flag的内容，改变选项
if rslt[6] == 'X':
e_invalid.set(1)
else:
e_invalid.set(0)
if rslt[7] == 'X':
e_shelf.set(1)
else:
e_shelf.set(0)
# 根据单位，改变当前单位选项
if rslt[8] == '其他':
combobox_unit.current(0)
elif rslt[8] == '个':
combobox_unit.current(1)
elif rslt[8] == '瓶':
combobox_unit.current(2)
elif rslt[8] == '桶':
combobox_unit.current(3)
else:
pass
# 改变serial flag的值
if rslt[9] == 'X':
e_serial.set(1)
else:
e_serial.set(0)
except:
e_partno.set('')
e_descr.set('')
e_min.set('')
e_max.set('')
e_warrenty.set('')
e_supplier.set('')
e_invalid.set(0)
e_shelf.set(0)
combobox_unit.current(1)
e_serial.set(0)
def generate():
if e_p_content.get() and e_q_content.get():
try:
rs = run_select_sql(
"select part_descr, supplier_id from parts where partno = '{}'"
.format(e_p_content.get()))
descr = rs[0][0]
v = rs[0][1]
except:
descr = ''
v = 'DUMMY'
if e_s_content.get().replace(' ', '').replace('\n', ''):
sn = e_s_content.get().replace(' ', '').replace('\n', '')
else:
rs = run_select_sql("select label_seq.nextval from dual")
sn = 'SYS{}'.format(rs[0][0])
manual_generate_qr_code_new(e_p_content.get(), e_q_content.get(),
v, e_n_content.get(), sn,
e_b_content.get(), '',
e_d_content.get(), descr, p_user)
tk.messagebox.showinfo('成功', '二维码成功生成，已保存在程序目录下的“newlabels”子目录')
else:
tk.messagebox.showerror('错误', '物料号、数量为必填字段！')
def search():
rs = run_select_sql(
"select loc_id, loc_type, loc_descr from locations where loc_id like '%{}%' and rownum = 1"
.format(e_loc_1.get()))
try:
rslt = [i if i != None else ''
for i in rs[0]] # change None to '' in result from sql
except:
rslt = ['', '', '']
e_loc_1.set(rslt[0])
if rslt[1] == 'Warehouse':
combobox_loc_type1.current(0)
elif rslt[1] == 'AREA':
combobox_loc_type1.current(1)
elif rslt[1] == 'STO':
combobox_loc_type1.current(2)
else:
pass
e_loc_descr1.set(rslt[2])
try:
logfile = open("job_scheduler.log", "a")
'''
First step, get the job details that should be run.
the sql combined 2 conditions:
1. find the job list with conditions: active status: Y & not in running status & has past its start time today
2. find the last running records for this job. if now - last run time > job interval setting, then should call a new job.
'''
sql_to_get_jobs = '''
select jc.job_name, jc.sql_run, jc.EMAIL_TITLE, jc.email_content, jl.last_start, jc.report_column_names from job_config jc left join (select job_name, max(start_time) last_start from job_logs where start_time > trunc(sysdate) group by job_name) jl
on (jc.JOB_NAME = jl.JOB_NAME) where jc.start_time < to_char(sysdate, 'HH24:MI:SS') and jc.IS_RUNNING = 'N' and jc.active = 'Y' and ((sysdate - jl.last_start)*24*60 > jc.JOB_INTERVAL_MINUTES or jl.last_start is NULL)
'''
rs = run_select_sql(sql_to_get_jobs)
if rs:
for job in rs:
run_job = Job_instance(logfile, job[0], job[1], job[2], job[3], job[4], job[5])
run_job.show()
run_job.run()
else:
print('*' * 100, file=logfile)
print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), " ", 'No jobs should be called.', file=logfile)
logfile.close()
print("finished at", datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
sleep(60)
except Exception as e:
print("have exception at", datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), type(e), e)
def location_structure(p_user):
pic_dir = os.getcwd() + '\\' + 'pics' + '\\'
gm = tk.Toplevel()
gm.iconbitmap(pic_dir + 'doge.ico')
gm.geometry('675x900+600+50')
gm.title('库位结构维护')
gm.resizable(0, 0)
# attach image
canvas = tk.Canvas(gm,
width=675,
height=900,
highlightthickness=0,
borderwidth=0)
image_file = tk.PhotoImage(file=pic_dir + 'location_structure_chn.gif')
image = canvas.create_image(0, 0, anchor='nw', image=image_file)
canvas.place(x=0, y=0, anchor='nw')
e_loc_1 = tk.StringVar()
tk.Entry(canvas, textvariable=e_loc_1, width=19,
font=('微软雅黑', 12)).place(x=100, y=218)
e_loc_descr1 = tk.StringVar()
tk.Entry(canvas, textvariable=e_loc_descr1, width=29,
font=('微软雅黑', 12)).place(x=135, y=250)
e_loc_type1 = tk.StringVar()
combobox_loc_type1 = ttk.Combobox(canvas,
width=12,
textvariable=e_loc_type1,
font=('微软雅黑', 10))
combobox_loc_type1.place(x=505, y=250)
combobox_loc_type1['values'] = ['仓库', '库存区域', '库存地点']
combobox_loc_type1.current(0)
def search():
rs = run_select_sql(
"select loc_id, loc_type, loc_descr from locations where loc_id like '%{}%' and rownum = 1"
.format(e_loc_1.get()))
try:
rslt = [i if i != None else ''
for i in rs[0]] # change None to '' in result from sql
except:
rslt = ['', '', '']
e_loc_1.set(rslt[0])
if rslt[1] == 'Warehouse':
combobox_loc_type1.current(0)
elif rslt[1] == 'AREA':
combobox_loc_type1.current(1)
elif rslt[1] == 'STO':
combobox_loc_type1.current(2)
else:
pass
e_loc_descr1.set(rslt[2])
tk.Button(canvas,
text='查询',
width=6,
borderwidth=1,
font=('微软雅黑', 9, 'bold'),
command=search).place(x=280, y=218)
def update():
p_loc = e_loc_1.get()
p_loc_descr = e_loc_descr1.get()
dict = {'仓库': 'Warehouse', '库存区域': 'AREA', '库存地点': 'STO'}
p_loc_type = dict.get(e_loc_type1.get(), 'unknown')
ret = run_oracle_function('update_locations',
[p_loc, p_loc_descr, p_loc_type, p_user])
if ret == '0':
tk.messagebox.showinfo('成功', '库位信息更新成功！')
elif ret == '2':
tk.messagebox.showerror('错误', '该位置已存在库位结构中，若要更改类型，先移除库位结构关系')
else:
tk.messagebox.showerror('错误', '发生其他异常，请检查重试或联系管理员')
refresh_list()
tk.Button(canvas,
text='更新',
width=6,
borderwidth=1,
font=('微软雅黑', 9, 'bold'),
command=update).place(x=420, y=218)
def new():
p_loc = e_loc_1.get()
p_loc_descr = e_loc_descr1.get()
dict = {'仓库': 'Warehouse', '库存区域': 'AREA', '库存地点': 'STO'}
p_loc_type = dict.get(e_loc_type1.get(), 'unknown')
ret = run_oracle_function('create_locations',
[p_loc, p_loc_descr, p_loc_type, p_user])
if ret == '0':
tk.messagebox.showinfo('成功', '库位信息添加成功！')
elif ret == '2':
tk.messagebox.showerror('错误', '该位置已存在！')
else:
tk.messagebox.showerror('错误', '发生其他异常，请检查重试或联系管理员')
refresh_list()
tk.Button(canvas,
text='新建',
width=6,
borderwidth=1,
font=('微软雅黑', 9, 'bold'),
command=new).place(x=490, y=218)
def delete():
p_loc = e_loc_1.get()
p_loc_descr = e_loc_descr1.get()
dict = {'仓库': 'Warehouse', '库存区域': 'AREA', '库存地点': 'STO'}
p_loc_type = dict.get(e_loc_type1.get(), 'unknown')
ret = run_oracle_function('delete_locations',
[p_loc, p_loc_descr, p_loc_type, p_user])
if ret == '0':
tk.messagebox.showinfo('成功', '库位信息删除成功！')
elif ret == '2':
tk.messagebox.showerror('错误', '该位置已存在库位结构中，若要删除，先移除库位结构关系')
elif ret == '3':
tk.messagebox.showerror('错误', '该位置上还有库存，不能直接删除库存地点')
else:
tk.messagebox.showerror('错误', '发生其他异常，请检查重试或联系管理员')
refresh_list()
tk.Button(canvas,
text='删除',
width=6,
borderwidth=1,
font=('微软雅黑', 9, 'bold'),
command=delete).place(x=560, y=218)
# 库位信息有更新或者新增后，均需要刷新当前的下拉列表，定义一个general的function
def refresh_list():
rs1 = run_select_sql(
"select loc_id from locations where loc_type = 'Warehouse' order by loc_id"
)
warehouse_list = [i[0] for i in rs1]
combobox_warehouse_2['values'] = warehouse_list
combobox_warehouse_2.set('')
combobox_warehouse_3['values'] = warehouse_list
combobox_warehouse_3.set('')
rs2 = run_select_sql(
"select loc_id from locations where loc_type in ('AREA','STO') order by loc_id"
)
location_list = [i[0] for i in rs2]
combobox_location_2['values'] = location_list
combobox_location_2.set('')
combobox_location_3.set('')
# 第二组选择框和按钮
e_warehouse_2 = tk.StringVar()
combobox_warehouse_2 = ttk.Combobox(canvas,
width=22,
textvariable=e_warehouse_2,
font=('微软雅黑', 12))
combobox_warehouse_2.place(x=45, y=440)
rs1 = run_select_sql(
"select loc_id from locations where loc_type = 'Warehouse' order by loc_id"
)
warehouse_list = [i[0] for i in rs1]
combobox_warehouse_2['values'] = warehouse_list
e_location_2 = tk.StringVar()
combobox_location_2 = ttk.Combobox(canvas,
width=22,
textvariable=e_location_2,
font=('微软雅黑', 12))
combobox_location_2.place(x=280, y=440)
rs2 = run_select_sql(
"select loc_id from locations where loc_type in ('AREA','STO') order by loc_id"
)
location_list = [i[0] for i in rs2]
combobox_location_2['values'] = location_list
def bind():
p_loc = e_warehouse_2.get()
p_subloc = e_location_2.get()
ret = run_oracle_function('bind_locations', [p_loc, p_subloc, p_user])
if ret == '0':
tk.messagebox.showinfo('成功', '库位结构添加成功！')
else:
tk.messagebox.showerror(
'错误', '库存区域/地点{}已经存在于仓库{}下！'.format(p_subloc, ret))
tk.Button(canvas,
text='添加\n关联',
width=6,
height=2,
borderwidth=1,
font=('微软雅黑', 10, 'bold'),
command=bind).place(x=560, y=418)
# 第三组选择框和按钮
def refresh_location_list3(arg1):
rs = run_select_sql(
"select loc_id, subloc_id from location_structure order by loc_id, subloc_id"
)
df = pd.DataFrame(rs)
location_list = list(df[df[0] == e_warehouse_3.get()][1])
combobox_location_3['values'] = location_list
combobox_location_3.current(0)
e_warehouse_3 = tk.StringVar()
combobox_warehouse_3 = ttk.Combobox(canvas,
width=22,
textvariable=e_warehouse_3,
font=('微软雅黑', 12))
combobox_warehouse_3.place(x=45, y=640)
rs1 = run_select_sql(
"select loc_id from locations where loc_type = 'Warehouse' order by loc_id"
)
warehouse_list = [i[0] for i in rs1]
combobox_warehouse_3['values'] = warehouse_list
combobox_warehouse_3.bind('<<ComboboxSelected>>', refresh_location_list3)
e_location_3 = tk.StringVar()
combobox_location_3 = ttk.Combobox(canvas,
width=22,
textvariable=e_location_3,
font=('微软雅黑', 12))
combobox_location_3.place(x=280, y=640)
def unbind():
p_loc = e_warehouse_3.get()
p_subloc = e_location_3.get()
ret = run_oracle_function('unbind_locations',
[p_loc, p_subloc, p_user])
if ret == '0':
tk.messagebox.showinfo('成功', '库位结构删除成功！')
elif ret == '2':
tk.messagebox.showerror('错误', '库存地点/区域下还有库存，不允许修改库位结构！')
else:
tk.messagebox.showerror('错误', '发生其他异常，请检查或联系系统管理员')
tk.Button(canvas,
text='删除\n关联',
width=6,
height=2,
borderwidth=1,
font=('微软雅黑', 10, 'bold'),
command=unbind).place(x=560, y=618)
label_user = tk.Label(canvas,
text=' 当前登录用户：' + p_user + ' ',
font=('微软雅黑', 10, 'bold italic')).place(x=20,
y=860,
anchor='nw')
gm.mainloop()
def GR_1(p_user, p_hostname, p_ip):
pic_dir = os.getcwd() + '\\' + 'pics' + '\\'
gr_1 = tk.Toplevel()
gr_1.iconbitmap(pic_dir + 'doge.ico')
gr_1.geometry('1200x700+350+150')
gr_1.title('入库扫描')
gr_1.resizable(0, 0)
# attach image
canvas = tk.Canvas(gr_1,
width=1200,
height=700,
highlightthickness=0,
borderwidth=0)
image_file = tk.PhotoImage(file=pic_dir + 'GR_1_chn.gif')
image = canvas.create_image(0, 0, anchor='nw', image=image_file)
canvas.place(x=0, y=0, anchor='nw')
x0, y0 = 20, 50
def refresh_location_list(arg1):
# if warehouse_choose.get() == 'IT_Office_Warehouse':
# combobox_LO['values'] = ['IT_TEMP_AREA_2','IT_Office_Rack01','IT_Office_Rack02']
# combobox_LO.current(0)
# elif warehouse_choose.get() == 'IT_TCF_Warehouse':
# combobox_LO['values'] = ['IT_TEMP_AREA_1','IT_TCF_Rack01','IT_TCF_Rack02']
# combobox_LO.current(0)
df2 = pd.DataFrame(rs2)
location_list = list(df2[df2[0] == warehouse_choose.get()][1])
combobox_LO['values'] = location_list
combobox_LO.current(0)
rs1 = run_select_sql(
"select loc_id from locations where loc_type = 'Warehouse'")
df1 = pd.DataFrame(rs1)
warehouse_list = list(df1[0])
rs2 = run_select_sql("select loc_id, subloc_id from location_structure")
warehouse_choose = tk.StringVar()
combobox_WH = ttk.Combobox(canvas,
width=30,
font=('微软雅黑', 15),
textvariable=warehouse_choose)
combobox_WH.place(x=65 + x0, y=140 + y0, anchor='nw')
combobox_WH['values'] = warehouse_list
combobox_WH.bind('<<ComboboxSelected>>', refresh_location_list)
location_choose = tk.StringVar()
combobox_LO = ttk.Combobox(canvas,
width=30,
font=('微软雅黑', 15),
textvariable=location_choose)
combobox_LO.place(x=527 + x0, y=140 + y0, anchor='nw')
# combobox_WH['values'] = ['IT_Office_Warehouse','IT_TCF_Warehouse']
# combobox_WH.bind('<<ComboboxSelected>>',show_choose)
def confirm_choose():
go_next = tk.messagebox.askyesno(
'提示', '你选择了仓库：' + warehouse_choose.get() + '和收货地点：' +
location_choose.get() + '，继续？')
if go_next == True:
gr_1.destroy()
GR_2(p_user, p_hostname, p_ip, location_choose.get())
else:
pass
b = tk.Button(canvas,
text='选好了',
width=15,
font=('微软雅黑', 13, 'bold'),
command=confirm_choose).place(x=950, y=550)
# b1 = tk.Button(GR_Main, width = 5, height = 2, text = 'GR', command = show_gr)
# b1.config(justify = tk.LEFT)
# b1.pack()
label_user = tk.Label(canvas,
text=' 当前登录用户：' + p_user + ' ',
font=('微软雅黑', 10, 'bold italic')).place(x=20,
y=650,
anchor='nw')
gr_1.mainloop()
x1 = 320
b_login = tk.Button(canvas, text='登录', width=10, font=('微软雅黑', 9, 'bold'), command=usr_login).place(x=20 + x1, y=310,
anchor='nw')
b_signup = tk.Button(canvas, text='注册', width=10, font=('微软雅黑', 9, 'bold'), command=usr_signup).place(x=140 + x1, y=310,
anchor='nw')
# b_exit = tk.Button(canvas, text = 'Exit', width = 10, font=('Calibri', 8), command = ).place(x = 260+x1, y = 320,
# anchor = 'nw')
# find the newest app version, judge if this is the newest app version.
sql = '''
select vers_id from
(select * from app_versions order by release_date desc)
where rownum = 1
'''
rs_ver = run_select_sql(sql)
if app_version != rs_ver[0][0]:
tk.messagebox.showerror('错误', '这不是最新的客户端，请从IT公盘下载最新客户端使用！')
exit()
rs_user = run_select_sql('select username, password from user_password')
usrs_info = {i[0]: i[1] for i in rs_user}
w_login.mainloop()
'''
长相思，在长安。
络纬秋啼金井阑，微霜凄凄簟色寒。
孤灯不明思欲绝，卷帷望月空长叹。
美人如花隔云端！