三天時間給女神定制一款資訊分類存盤軟體

時間緊任務重，女神提出的要求有模棱兩可，只能自己考慮各種情況，除了用python還有誰能這么短的時間搞出來，
程式界面，增刪改查不能少，后悔藥也需要給女神準備上，由于最后需要打包給女神用，所以選擇了python的自帶庫，tkinter撰寫界面，我覺得也不是那么丑，資料存盤用sqlite3資料庫，可以匯出成csv檔案，完全用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()