量具管理－查询及修改功能

量具管理－查询及修改功能

此模块可以查询过期和即将过期量具的清单，并能够修改校准日期和查看校准报告
建立文件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()