Python實現簡易信息分類存儲軟件

import tkinter as tk
import sqlite3
import csv
from threading import Thread
import shutil
import os
import time
from tkinter import  messagebox
from tkinter import filedialog
from tkinter import ttk




class App(tk.Frame):
    def __init__(self,master,*args,**kwargs):
        super().__init__(master,*args,**kwargs)
        self.dirdict={
            "新建":self.new,
            "查詢":self.search,
            "修改":self.edit,
            "刪除":self.delete,
            "匯總":self.totale,
            "導出":self.export,
            "後悔藥":self.regret
        }
        self.newdict={
                "咨詢信息":self.customer_information,
                "投標信息":self.bidding_information,
                "合同信息" :self.contract_information,
                "售後信息" :self.service_information,
            }
        self.newlabelsdict={
            "咨詢信息":["日期","公司名稱","聯系人","聯系電話","備註"],
            "投標信息":["招標單位","招標號","報名費","保證金","退保證金","開票信息",],
            "合同信息":["合同號","簽訂日期","數量","總價","客戶名稱","貨期","派工單號","發貨地址","回款批次","發票信息","開票信息","合同掃描件"],
            "售後信息":["產品型號","派工號","貨期","技術人員","安裝人員","驗收","售後1","售後2"],
        }
       
        self.prmkey={
            "咨詢信息":('company',1),
            "投標信息":('company',0),
            "合同信息":('contract',0),
            "售後信息":('jobnum',1),
        }
        self.new_zh_col={

            "咨詢信息":'consulting',
            "日期":"date","公司名稱":"company","聯系人":"contacts","聯系電話":"telephone","備註":"remarks",
            "投標信息":'bid',
            "招標單位":"company","招標號":"number","報名費":"enroll","保證金":"ensure","退保證金":"back","開票信息":"invoice",
            "合同信息":'contractinfo',
            "合同號":"contract","簽訂日期":"sdate","數量":"quantity","總價":"total","客戶名稱":"customer","貨期":"delivery","派工單號":"oddnum","發貨地址":"address","回款批次":"batch","發票信息":"cinfo","開票信息":"invoice","合同掃描件":"catpath",
            "售後信息":'service',
            "產品型號":"product","派工號":"jobnum","貨期":"delivery","技術人員":"artisan","安裝人員":"installer","驗收":"check","售後1":"service1","售後2":"service2",
        }
        self.pack(expand=1,fill="both")
        self.con=sqlite3.connect("treasure.db")
        self.creat_widget()

    def creat_widget(self):
        self.frameleft = tk.Frame(self,bg='RoyalBlue',relief="groove",borderwidth=2)
        self.frameleft.pack(side='left',expand='no',fill='y',anchor="n")
        
        for i in self.dirdict.keys():
            but=tk.Button(self.frameleft,text=i,width="10",)
            but.pack(side="top",expand="no",fill="x",anchor="n",padx=4,pady=5,)
            but.bind('<Button-1>', self.set_style)

        self.frameright = tk.Frame(self,bg='RoyalBlue',relief="groove",borderwidth=2)
        self.frameright.pack(side='right',expand='yes',fill='both',padx=3,pady=0)

        self.lf2 = tk.LabelFrame(self.frameright,text="信息",relief="groove",borderwidth=1,bg="Wheat")
        self.lf2.pack(side="top",expand=1,fill="both",pady=2,)
        self.totale()

    def set_style(self,event):
        for i in self.frameleft.winfo_children():
            if isinstance(i,tk.Button):
                i.config(fg="black")
        event.widget["fg"]="blue"
        self.reset(self.frameright)

        self.lf1 = tk.Frame(self.frameright,relief="groove",borderwidth=0,bg='RoyalBlue')
        self.lf1.pack(side="top",expand=0,fill="x",pady=2,)

        self.lf2 = tk.LabelFrame(self.frameright,text="信息",relief="groove",borderwidth=1,bg="Wheat")
        self.lf2.pack(side="top",expand=1,fill="both",pady=2,)
        self.dirdict.get(event.widget["text"],None)()
        self.lf2["text"]=event.widget["text"]

######################################新建內容###########################################################################
    def new(self):#新建總類
        def data_input(event):
            self.lf2.config(text=event.widget['text'])
            self.reset(self.lf2)
            self.newdict.get(event.widget['text'],None)(self.newlabelsdict[event.widget['text']])

        for i in self.newdict.keys():
            bu=tk.Button(self.lf1,text=i,)
            bu.pack(side="left",expand=1,fill="x",padx=5,pady=2,)
            bu.bind('<Button-1>', data_input)
        
    def customer_information(self,labellist):#新建客戶信息
        this="咨詢信息"
        for i in labellist:
            if i == labellist[-1]:
                tk.Label(self.lf2,text=i,bg="Wheat",width=10).pack(side="top",)
                tk.Text(self.lf2,height=6).pack(side="top",expand=1,pady=1,padx=5,fill="both")
            else:
                e=tk.Entry(self.lf2,)
                e.pack(side="top",expand=0,pady=1,padx=5,fill="x")
                l=tk.Label(e,text=i,bg="Wheat",width=10)
                l.pack(side="right",)

        def getdict():
            cusdict=self.super_get(labellist,self.lf2)
            if self.save_data("INSERT INTO consulting VALUES (?,?,?,?,?)", list(cusdict.values())):
                self.super_del(self.lf2)
           
        bu=tk.Button(self.lf2,text="確認提交",width=15,command=getdict)
        bu.pack(side="bottom",expand=0,padx=5,pady=2,)

    def bidding_information(self,labellist):#新建招標信息
        this="投標信息"
        for i in labellist:
            if i == labellist[-1]:
                tk.Label(self.lf2,text=i,bg="Wheat",width=10).pack(side="top",)
                tk.Text(self.lf2,height=6).pack(side="top",expand=1,pady=1,padx=5,fill="both")
            else:
                e=tk.Entry(self.lf2,)
                e.pack(side="top",expand=0,pady=1,padx=5,fill="x")
                l=tk.Label(e,text=i,bg="Wheat",width=10)
                l.pack(side="right",)

        def getdict():
            cusdict=self.super_get(labellist,self.lf2)
            if self.save_data("INSERT INTO bid VALUES (?,?,?,?,?,?)", list(cusdict.values())):
                self.super_del(self.lf2)
        bu=tk.Button(self.lf2,text="確認提交",width=15,command=getdict)
        bu.pack(side="bottom",expand=0,padx=5,pady=2,)
       
    def contract_information(self,labellist):#新建合同信息
        this="合同信息"
        def filenames():
            names=filedialog.askopenfilenames(title="上傳合同掃描件")  
            if names:
                filenamesentry.insert(0,",".join(names))
            
        for i in labellist:
            if i==labellist[0]:
                connum=tk.Entry(self.lf2,)
                connum.pack(side="top",expand=0,pady=1,padx=5,fill="x")
                tk.Label(connum,text=i,bg="Wheat",width=10).pack(side="right",)
            elif i == labellist[-2]:
                tk.Label(self.lf2,text=i,bg="Wheat",width=10).pack(side="top",)
                tk.Text(self.lf2,height=6).pack(side="top",expand=1,pady=1,padx=5,fill="both")
            elif i==labellist[-1]:
                filenamesentry=tk.Entry(self.lf2)
                filenamesentry.pack(side="top",expand=0,pady=2,padx=5,fill="x")
                filebut=tk.Button(filenamesentry,text="點擊上傳合同",height=1,command=filenames)
                filebut.pack(side="right",expand=0,padx=0,pady=0,)
            else:
                e=tk.Entry(self.lf2,)
                e.pack(side="top",expand=0,pady=1,padx=5,fill="x")
                tk.Label(e,text=i,bg="Wheat",width=10).pack(side="right",)

        def getdict():
            files=filenamesentry.get()
            if files:
                number=connum.get() if connum.get() else "無合同號"
                newcat=os.path.join(os.getcwd(),"mydata\{}{}".format(number,time.strftime(r"-%Y-%m-%d %H-%M-%S",time.localtime()),))
                os.mkdir(newcat)
                for i in files.split(","):
                    shutil.move(os.path.join(i),newcat)
                filenamesentry.delete(0, "end")
                filenamesentry.insert(0,newcat)
            cusdict=self.super_get(labellist,self.lf2)
            if self.save_data("INSERT INTO contractinfo VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", list(cusdict.values())):
                self.super_del(self.lf2)

        bu=tk.Button(self.lf2,text="確認提交",width=15,command=getdict)
        bu.pack(side="bottom",expand=0,padx=5,pady=2,fill="x")
       
    def service_information(self,labellist):#新建售後信息
        this="售後信息"
        for i in labellist:
            e=tk.Entry(self.lf2,)
            e.pack(side="top",expand=0,pady=1,padx=5,fill="x")
            l=tk.Label(e,text=i,bg="Wheat",width=10)
            l.pack(side="right",)
 
        def getdict():
            cusdict=self.super_get(labellist,self.lf2)
            # check=self.check(self.new_zh_col.get(this,None),self.prmkey.get(this,None)[0],cusdict.get())
            if self.save_data("INSERT INTO service VALUES (?,?,?,?,?,?,?,?)", list(cusdict.values())):
                self.super_del(self.lf2)

        bu=ttk.Button(self.lf2,text="確認提交",width=15,command=getdict)
        bu.pack(side="bottom",expand=0,padx=5,pady=2,)

#################################################################################################################

    def save_data(self,sqldoc,somedata,flag=False):#數據庫存儲存儲客戶信息
        cur = self.con.cursor()
        try:
            cur.execute(sqldoc,somedata)
            self.con.commit()
            messagebox.showinfo("稟報女王","女王萬歲，您又贏瞭")
            return True
        except Exception as e:
            messagebox.showwarning("急報女王","女王我出錯瞭:{}".format(e))
            return False

    def check(self,this,col,value):
        sqldoc="SELECT * FROM {} WHERE {} = '{}' ".format(this,col,value)
        cur = self.con.cursor()
        cur.execute(sqldoc)
        data=cur.fetchone()
        return data

    def find_data(self,sqldoc):
        cur = self.con.cursor()
        try:
            cur.execute(sqldoc)
            datas=cur.fetchall()
            if datas:
                return datas
            else:
                messagebox.showwarning("稟報女王","女王大人，小的什麼也沒搜到")
        except Exception as e:
            messagebox.showwarning("稟報女王","女王大人這是一次失誤{}".format(e))
            return None

    def del_data(self,sqldoc):
        cur = self.con.cursor()
        cur.execute(sqldoc)
        self.con.commit()
        messagebox.showinfo("喜報","女王無敵，敵人已消滅")

    def add_top(self,title):#創建頂級窗口
        top = tk.Toplevel(width=self.master.winfo_screenwidth()//4,height=self.master.winfo_screenheight()//4,)
        top.title(title)
        return top

    def reset(self,widget):#重置該組件，銷毀該組件所有子組件
        for i in widget.winfo_children():
            i.destroy()

    def super_get(self,labellist,wids):#獲取entry和text類得內容
        cusdict={}
        for i ,k in zip(labellist,[i for i in wids.winfo_children() if isinstance(i, (tk.Entry,tk.Text))]):
            if isinstance(k,tk.Entry):
                cusdict[i] = k.get()
            elif isinstance(k, tk.Text):
                cusdict[i] = k.get(1.0,'end')
            else:
                pass
        return cusdict

    def super_del(self,wids):#刪除entry和text類的內容
        for wid in wids.winfo_children():
            if isinstance(wid,tk.Text):
                wid.delete(1.0,"end")
            elif isinstance(wid, tk.Entry):
                wid.delete(0,"end")
            else:
                pass
    def super_insert(self,wids,text):#為entry或text類組件插入內容
        ins=[i for i in wids.winfo_children() if isinstance(i, (tk.Entry,tk.Text))]
        for wid,value in zip(ins,text):
            wid.insert("end",value)
           
    def creat_tree(self,wid,headers,height=4):#建立treeview組件
        
        tree=ttk.Treeview(wid,columns=headers,show='headings',height=height)
        for n,i in enumerate(headers):
            tree.column(i,width=60,)
            tree.heading(column=i,text=i)
        sc=ttk.Scrollbar(wid,)
        sc['command']=tree.yview
        sc.pack(side='right',fill='both')
        tree["yscrollcommand"]=sc.set
        tree.pack(side="top",fill="both",expand=1)
        return tree

    def tree_insert(self,table,datas):#插入數值
        # 插入數據
        if datas:
            for index, data in enumerate(datas):
                table.insert('', index, values=data)  
    
    def tree_del(self,obj):#清除組件內內容
        child=obj.get_children()
        for i in child:
            obj.delete(i)

    def create_lf1_children(self,parent,):#為一下項目提供篩選，搜索選項
        def change(event):
            cominfo["values"]=self.newlabelsdict[com.get()]
            cominfo.current(0)

        com=ttk.Combobox(parent,values=tuple(self.newlabelsdict.keys()),state="readonly",)
        com.pack(side="left",padx=2,pady=2)
        com.current(0)
        com.bind('<<ComboboxSelected>>', change)

        cominfo=ttk.Combobox(parent,state="readonly",values=self.newlabelsdict[com.get()])
        cominfo.pack(side="left",padx=2,pady=2)
        cominfo.current(0)

        e=ttk.Entry(parent,)
        e.pack(side="left",expand=1,pady=1,padx=5,fill="x")
        
        return com,cominfo,e

    def start_find(self,arc,colname,e):#便捷函數，為以下項目提供支持
        tablename, col = self.new_zh_col.get(arc,None),self.new_zh_col.get(colname,None)
        self.lf2.config(text=arc)
        headers=self.newlabelsdict.get(arc,None)
        table=self.creat_tree(self.lf2,headers)
        par=e.get()
        sqldoc="SELECT * FROM {} WHERE {} LIKE '%{}%' ".format(tablename,col,par) if par else "SELECT * FROM {} ".format(tablename)
        datas=self.find_data(sqldoc)
        return table ,datas
    
    def search(self):#查找數據
        def find():
            self.reset(self.lf2)
            arc=com.get()
            colname = cominfo.get()
            table,datas=self.start_find(arc,colname,e)
            self.tree_insert(table,datas)

        com, cominfo, e= self.create_lf1_children(self.lf1, )
        tk.Button(self.lf1,text="開始查找",command=find).pack(side="left",expand=0,padx=5,pady=2,)
        

    def edit(self):#編輯數據
        def tree_selected(event):
            name=com.get()
            value_e=event.widget.item(event.widget.selection()[0])['values']
            self.reset(self.lf2)
            self.newdict.get(name,None)(self.newlabelsdict[name])
            self.super_insert(self.lf2,value_e)
            
            
        def find():
            self.reset(self.lf2)
            arc=com.get()
            colname = cominfo.get()
            table,datas=self.start_find(arc,colname,e)
            self.tree_insert(table,datas)
            table.bind("<<TreeviewSelect>>", tree_selected)

        com, cominfo, e = self.create_lf1_children(self.lf1, )
        tk.Button(self.lf1,text="開始查找",command=find).pack(side="left",expand=0,padx=5,pady=2,)
        
   
    def delete(self):
        def tree_selected(event):
            name=com.get()
            value_e=event.widget.item(event.widget.selection()[0])['values']
            flag=messagebox.askokcancel('愛之深恨之切',"女王大人，確定要放棄它嘛")
            if flag:
                sqldoc="DELETE FROM {} WHERE {} = '{}' ".format(self.new_zh_col.get(name,None),self.prmkey.get(name,None)[0],value_e[self.prmkey.get(name,None)[1]])
                self.del_data(sqldoc)
                find()
            else:
                pass
        def find():
            self.reset(self.lf2)
            arc=com.get()
            colname = cominfo.get()
            table,datas=self.start_find(arc, cominfo, e)
            self.tree_insert(table,datas)
            table.bind("<<TreeviewSelect>>", tree_selected)

        com, cominfo, e = self.create_lf1_children(self.lf1, )
        tk.Button(self.lf1,text="開始查找",command=find).pack(side="left",expand=0,padx=5,pady=2,)

    def export(self):#導出數據，存為csv文件
        def ex():
            cur = self.con.cursor()
            file=os.path.join(os.getcwd(),"{}{}.csv".format("數據匯總",time.strftime(r"-%Y-%m-%d %H-%M",time.localtime()),))
            print(file)
            with open(file,"w",newline="") as dd:
                wter=csv.writer(dd)
                for i in self.newlabelsdict.keys():
                    sqldoc="SELECT * FROM {} ".format(self.new_zh_col.get(i,None))
                    cur.execute(sqldoc)
                    datas=cur.fetchall()
                    wter.writerow(self.newlabelsdict[i])
                    wter.writerows(datas)
                    wter.writerow("")
            messagebox.showinfo("喜報","女王陛下，數據已導出完成\n存儲位置{}".format(file))

        def beifen():
            t = Thread(target=ex)
            t.run()

        cur = self.con.cursor()
        for i, k in self.newlabelsdict.items():
            lf21=tk.LabelFrame(self.lf2,text=i,bg="Wheat")
            lf21.pack(side="top",fill="both",expand=1,pady=1)
            sqldoc="SELECT * FROM {} ".format(self.new_zh_col.get(i,None))
            cur.execute(sqldoc)
            datas=cur.fetchall()
            tk.Label(lf21,text="{}數據總數: {}條".format(i,len(datas)),font=("bold",15),bg="Wheat").pack(side="top",expand=1,fill="x")
        
        tk.Button(self.lf2,text="導出數據",command=beifen,width=15).pack(side="bottom",expand=0,padx=5,pady=2,)

    def totale(self):
        cur = self.con.cursor()
        for i, k in self.newlabelsdict.items():
            lf21=tk.LabelFrame(self.lf2,text=i)
            lf21.pack(side="top",fill="both",expand=1,pady=1)
            table=self.creat_tree(lf21,k)
            sqldoc="SELECT * FROM {} ".format(self.new_zh_col.get(i,None))
            cur.execute(sqldoc)
            datas=cur.fetchall()
            self.tree_insert(table,datas)

    def regret(self):
        def eat():
            self.con.rollback()
            messagebox.showinfo("回到從前","女王大人，我們再次回到瞭從前")

        tk.Label(self.lf2,text="女王陛下，該吃藥瞭！！",font=("bold",30,),bg="Wheat").pack(side="top",expand=1,fill="x")
        tk.Button(self.lf2,text="立即嗑藥",command=eat,width=15).pack(side="bottom",expand=0,padx=5,pady=2,)
       
        
        
       



if __name__ == "__main__":

    if not os.path.exists("mydata"):
        os.mkdir("mydata")

    root  = tk.Tk()
    # root.option_add("*Font", "微軟雅黑")
    root.iconbitmap('crown.ico')
    root.title("女王的寶庫")
    # root.attributes("-alpha", 0.9)透明度設置，奈何女神不需要
    root.geometry("{}x{}+{}+{}".format(root.winfo_screenwidth()//2,root.winfo_screenheight()//2,root.winfo_screenwidth()//4,root.winfo_screenheight()//4))
    app=App(root)
    app.mainloop()