此模块可以查询过期和即将过期量具的清单，并能够修改校准日期和查看校准报告 建立文件queryclass.py
# -*- coding: utf-8 -*- import os import sqlite3 import tkinter.ttk from tkinter import * from tkinter.messagebox import * import constant import datetime import baseclass import framebaseclass import calendarclass class query(framebaseclass.baseFrame): def __init__(self,master,user,framename): self.top=master self.user=user self.framename=framename super( ).__init__(self.top,self.user,self.framename) columns = ("编号","量具编码","描述","型号","使用人","校准到期时间") self.tree=ttk.Treeview(self.top, show="headings", columns=columns) self.tree.column("编号",width=30,anchor='center') self.tree.column("量具编码",width=60,anchor='center') self.tree.column("描述",width=160,anchor='center') self.tree.column("型号",width=60,anchor='center') self.tree.column("使用人",width=60,anchor='center') self.tree.column("校准到期时间",width=60,anchor='center') self.tree.heading("编号",text="编号") self.tree.heading("量具编码",text="量具编码") self.tree.heading("描述",text="描述") self.tree.heading("型号",text="型号") self.tree.heading("使用人",text="使用人") self.tree.heading("校准到期时间",text="校准到期时间") self.tree.place(relx=0.01,rely=0.05,relwidth=0.98,relheight=0.4) label_status=Label(self.top,text="有效状态:",justify=RIGHT) label_status.place(relx=0,rely=0.01,relwidth=0.1,height=20) self.var_status=constant.STATUS self.combo_status=tkinter.ttk.Combobox(self.top,value=tuple(self.var_status)) self.combo_status.place(relx=0.10,rely=0.01,relwidth=0.12,height=20) self.combo_status.set("Available") label_calibration=Label(self.top,text="校验状态:",justify=RIGHT) label_calibration.place(relx=0.23,rely=0.01,relwidth=0.1,height=20) self.var_calibration=["已过期","即将过期"] self.combo_calibration=tkinter.ttk.Combobox(self.top,value=tuple(self.var_calibration)) self.combo_calibration.place(relx=0.31,rely=0.01,relwidth=0.1,height=20) self.combo_calibration.set("已过期") buttonquery=Button(self.top,text="查询",command=self.query_data) buttonquery.place(relx=0.82,rely=0.01,width=100,height=20) label_date=Label(self.top,text="当前日期:",justify=RIGHT) label_date.place(relx=0.41,rely=0.01,relwidth=0.1,height=20) nowday=(datetime.datetime.now()+datetime.timedelta(days=0)).strftime("%Y-%m-%d") self.date=StringVar() self.date.set(nowday) self.entry_date=ttk.Entry(self.top,textvariable=self.date ) self.entry_date.place(relx=0.51,rely=0.01,relwidth=0.1,height=20) label_sum=Label(self.top,text="查询结果共:",justify=RIGHT) label_sum.place(relx=0.65,rely=0.01,relwidth=0.1,height=20) label_sum=Label(self.top,text="项",justify=RIGHT) label_sum.place(relx=0.76,rely=0.01,relwidth=0.03,height=20) self.var_sum=StringVar() self.entry_sum=Entry(self.top,textvariable=self.var_sum) self.entry_sum.place(relx=0.73,rely=0.01,relwidth=0.03,height=20) columns1 = ("量具编码","量具描述","型号","校准周期","状态","最新校准日期","校准报告") self.tree1=ttk.Treeview(self.top, show="headings", columns=columns1) self.tree1.column("量具编码",width=60,anchor='center') self.tree1.column("量具描述",width=160,anchor='center') self.tree1.column("型号",width=60,anchor='center') self.tree1.column("校准周期",width=60,anchor='center') self.tree1.column("状态",width=60,anchor='center') self.tree1.column("最新校准日期",width=60,anchor='center') self.tree1.column("校准报告",width=60,anchor='center') self.tree1.heading("量具编码",text="量具编码") self.tree1.heading("量具描述",text="量具描述") self.tree1.heading("型号",text="型号") self.tree1.heading("校准周期",text="校准周期") self.tree1.heading("状态",text="状态") self.tree1.heading("最新校准日期",text="最新校准日期") self.tree1.heading("校准报告",text="校准报告") self.tree1.place(relx=0.01,rely=0.57,relwidth=0.98,relheight=0.3) label_ID=Label(self.top,text="输入量具编码:",justify=RIGHT) label_ID.place(relx=0.01,rely=0.52,relwidth=0.1,height=20) self.var_ID=StringVar() self.entry_ID=Entry(self.top,textvariable=self.var_ID) self.entry_ID.place(relx=0.11,rely=0.52,relwidth=0.1,height=20) self.var_ID.set(self.get_first_from_db()) label_year=Label(self.top,text="校准报告年份",justify=RIGHT) label_year.place(relx=0.23,rely=0.52,relwidth=0.12,height=20) self.year=constant.YEAR self.combo_year=tkinter.ttk.Combobox(self.top,value=tuple(self.year)) self.combo_year.place(relx=0.33,rely=0.52,relwidth=0.05,height=20) year=(datetime.datetime.now()).strftime("%Y") self.combo_year.set(year) buttonquery=Button(self.top,text="查询:",command=self.query_by_code) buttonquery.place(relx=0.45,rely=0.52,relwidth=0.06,height=20) buttonreport=Button(self.top,text="打开校准报告",command=self.read_fields) buttonreport.place(relx=0.85,rely=0.52,relwidth=0.1,height=20) buttonsave=Button(self.top,text="保存",command=self.update_database) buttonsave.place(relx=0.65,rely=0.52,relwidth=0.06,height=20) self.menu = Menu(self.top,tearoff=0) self.menu.add_command(label="选择并删除",command=self.delete_tree_conent) self.menu.add_command(label="清空全部:",command=self.delete_tree_all) self.menu.add_separator() self.menu.add_command(label="打开校准报告",command=self.open_pdf) self.tree1.bind('<Double-1>', self.choosetree1) self.query_data() self.entry_date.bind('<Button-1>',self.get_date) self.tree1.bind("<Button-3>",self.popupmenu) def get_expire_from_db(self): status=self.combo_status.get() date=self.date.get() self.conn=sqlite3.connect("database.db") c=self.conn.cursor() sql="SELECT ID,Code量具编码,Description描述,Type型号,Used_By使用人,Next_Calibration校准到期时间\ FROM instruments_info WHERE Status状态='%s' AND Next_Calibration校准到期时间<'%s' AND Next_Calibration校准到期时间 <>''" % (status,date) #print(sql) c.execute(sql) li=c.fetchall() self.conn.commit() self.conn.close() return li def get_willbeexpire_from_db(self): status=self.combo_status.get() date_raw=self.date.get() stringtodate=datetime.datetime.strptime(date_raw, '%Y-%m-%d') date=(stringtodate+datetime.timedelta(days=30)).strftime("%Y-%m-%d") #print(date) self.conn=sqlite3.connect("database.db") c=self.conn.cursor() sql="SELECT ID,Code量具编码,Description描述,Type型号,Used_By使用人,Next_Calibration校准到期时间\ FROM instruments_info WHERE Status状态='%s' AND Next_Calibration校准到期时间 BETWEEN '%s' AND '%s' " % (status,date_raw,date) #print(sql) c.execute(sql) li=c.fetchall() self.conn.commit() self.conn.close() return li def get_data_by_code(self): code=self.entry_ID.get() year=self.combo_year.get() F="".join(["Certificate_",year,"校准报告 "]) self.conn=sqlite3.connect("database.db") c=self.conn.cursor() sql="SELECT Code量具编码,Description描述,Type型号,Calibration_Period校准周期,Status状态,Last_Calibration上次校准时间 ,%s FROM instruments_info WHERE Code量具编码='%s' " % (F,code,) #print(sql) c.execute(sql) li=c.fetchall() self.conn.commit() self.conn.close() return li def query_data(self): x=self.tree.get_children() for i in x: self.tree.delete(i) option=self.combo_calibration.get() if option=="已过期": content=self.get_expire_from_db() self.var_sum.set(len(content)) else: content=self.get_willbeexpire_from_db() self.var_sum.set(len(content)) for item in content: self.tree.insert("" ,"end",values=(item[0],item[1],item[2],item[3],item[4],item[5])) def query_by_code(self): content=self.get_data_by_code() for item in content: self.tree1.insert("" ,"end",values=(item[0],item[1],item[2],item[3],item[4],item[5],item[6])) def choosetree1(self,event): for self.item in self.tree1.selection(): item_text =self.tree1.item(self.item, "values") print("item",item_text) self.row = self.tree1.identify_row(event.y) print("row",self.row,event.y) self.column=self.tree1.identify_column(event.x) print("column",self.column,event.x) cn = int(str(self.column).replace('#',''),16) rn = int(str(self.row).replace('I',''),16) if self.column=='#6': self.var_content=StringVar() self.entryedit=Entry(self.top,textvariable=self.var_content) self.entryedit.place(x=16+(cn-1)*150, y=400+rn*20,width=130,height =20) self.entryedit.bind('<Button-1>',self.get_date_inner) self.okb = ttk.Button(self.top, text='OK', width=3, command=self.saveedit) self.okb.place(x=100+(cn-1)*150, y=430+rn*20) if self.column=='#5': self.status=constant.STATUS self.entryedit =tkinter.ttk.Combobox(self.top,value=tuple(self.status)) self.entryedit.place(x=16+(cn-1)*150, y=400+rn*20,width=130,height =20) self.okb = ttk.Button(self.top, text='OK', width=3, command=self.saveedit) self.okb.place(x=100+(cn-1)*150, y=430+rn*20) if self.column=='#7': self.var_pdf=StringVar() self.entryedit=Entry(self.top,textvariable=self.var_pdf) self.entryedit.place(x=16+(cn-1)*150, y=400+rn*20,width=130,height =20) self.okb = ttk.Button(self.top, text='OK', width=3, command=self.saveedit) self.okb.place(x=100+(cn-1)*150, y=430+rn*20) def saveedit(self): self.tree1.set(self.item, column=self.column, value=self.entryedit.get()) self.entryedit.destroy() self.okb.destroy() def open_pdf(self): for self.item in self.tree1.selection(): item_text =self.tree1.item(self.item, "values") print("item",item_text[6]) filename=str(item_text[6])+".pdf" path=os.getcwd()+"\\report_"+self.combo_year.get() file=os.path.join(path,filename) os.startfile(file) def delete_tree_conent(self): x=self.tree1.get_children() #print("x",x) line=self.tree1.selection() if x: self.tree1.delete(line) else: showwarning('警告', "没有数据可以删除！") def delete_tree_all(self): x=self.tree1.get_children() print(self.tree1) if x: for item in x: self.tree1.delete(item) else: showwarning('警告', "没有数据可以删除！") def popupmenu(self,event): self.menu.post(event.x_root,event.y_root) def update_database(self): count=0 for item in self.tree1.get_children(): item_text = self.tree1.item(item,"values") code=item_text[0] period=item_text[3] status=item_text[4] date=item_text[5] if date=="None": datenext="None" else: stringtodate=datetime.datetime.strptime(date, '%Y-%m-%d') datenext=(stringtodate+datetime.timedelta(days=int(period))).strftime("%Y-%m-%d") year=self.combo_year.get() F="".join(["Certificate_",year,"校准报告"]) pdfreport=item_text[6] if F not in self.read_fields(): self.add_field(F) self.conn=sqlite3.connect("database.db") c=self.conn.cursor() sql="UPDATE instruments_info SET Status状态='%s' , Last_Calibration上次校准时间='%s',Next_Calibration校准到期时间='%s',%s='%s' WHERE Code量具编码='%s' " % (status,date,datenext,F,pdfreport,code) c.execute(sql) count+=1 self.conn.commit() self.conn.close() self.top.withdraw() showinfo('提示', "共保存"+str(count)+"条记录") self.top.deiconify() def get_date(self,event): for date1 in [calendarclass.Calendar((500, 400), 'ur').selection()] : if date1: self.date.set(date1) def get_date_inner(self,event): for date1 in [calendarclass.Calendar((600, 500), 'ur').selection()] : if date1: self.var_content.set(date1) def add_field(self,fields): self.conn=sqlite3.connect("database.db") c=self.conn.cursor() sql="alter table instruments_info add '%s' TEXT"%fields c.execute(sql) self.conn.commit() self.conn.close() def read_fields(self): get_fields_from_db=[] self.conn=sqlite3.connect("database.db") c=self.conn.cursor() sql="PRAGMA table_info(instruments_info)" c.execute(sql) li=c.fetchall() for line in li: get_fields_from_db.append(line[1]) self.conn.commit() self.conn.close() return get_fields_from_db def get_first_from_db(self): self.conn=sqlite3.connect("database.db") c=self.conn.cursor() sql="SELECT Code量具编码 FROM instruments_info order by ID ASC LIMIT 1" c.execute(sql) li=c.fetchone()[0] self.conn.commit() self.conn.close() return li if __name__ =="__main__": root = Tk() query(root,"usertest","frame") root.mainloop()