def statisticsWindow(): statTop = tk.Toplevel(width=900, height=550) statTop.title("数据分析") lable1 = tk.Label(statTop, text='总资产') lable1.place(x=130, y=30) text1 = tk.StringVar() data1 = tk.Label(statTop, textvariable=text1) data1.place(x=230, y=30) lable2 = tk.Label(statTop, text='总员工数') lable2.place(x=330, y=30) text2 = tk.StringVar() data2 = tk.Label(statTop, textvariable=text2) data2.place(x=430, y=30) lable3 = tk.Label(statTop, text='总客户数') lable3.place(x=530, y=30) text3 = tk.StringVar() data3 = tk.Label(statTop, textvariable=text3) data3.place(x=630, y=30) tree = ttk.Treeview(statTop, height=20, columns=('branchName', 'asset', 'saveAccount', 'loanAccount', 'staff', 'loan')) tree.column('branchName', width=130, anchor='center') tree.column('asset', width=100, anchor='center') tree.column('saveAccount', width=100, anchor='center') tree.column('loanAccount', width=100, anchor='center') tree.column('staff', width=100, anchor='center') tree.column('loan', width=130, anchor='center') tree.heading('branchName', text='支行名') tree.heading('asset', text='资产') tree.heading('saveAccount', text='储蓄账户数') tree.heading('loanAccount', text='支票账户数') tree.heading('staff', text='员工数') tree.heading('loan', text='贷款') tree["show"] = "headings" tree.place(x=110, y=80) sql1 = "select `支行名`, `资产` from bank.支行;" sql2 = "select count(*) from bank.储蓄账户 where `支行名`=%s;" sql3 = "select count(*) from bank.支票账户 where `支行名`=%s;" sql4 = "select count(*) from bank.员工 where `支行名`=%s;" sql5 = "select sum(`总金额`) from bank.贷款 where `支行名`=%s;" sql6 = "select sum(`资产`) from bank.支行;" sql7 = "select count(*) from bank.客户;" sql8 = "select count(*) from bank.员工;" connStat = dbop.mysqlConn() allAsset = connStat.execSQL(sql6, None) text1.set(allAsset.fetchone()) allClient = connStat.execSQL(sql7, None) text2.set(allClient.fetchone()) allStaff = connStat.execSQL(sql8, None) text3.set(allStaff.fetchone()) branchList = [] saveAccountList = [] loanAccountList = [] staffList = [] loanList = [] getBranch = connStat.execSQL(sql1, None) while True: item = getBranch.fetchone() if not item: break item = list(item) branchList.append(item) for item in branchList: getSaveAccount = connStat.execSQL(sql2, item[0]) while True: num = getSaveAccount.fetchone() if not num: break saveAccountList.append(num) for item in branchList: getLoanAccount = connStat.execSQL(sql3, item[0]) while True: num = getLoanAccount.fetchone() if not num: break loanAccountList.append(num) for item in branchList: staff = connStat.execSQL(sql4, item[0]) while True: num = staff.fetchone() if not num: break staffList.append(num) for item in branchList: getLoan = connStat.execSQL(sql5, item[0]) while True: num = getLoan.fetchone() if not num: break loanList.append(num) index = 0 for item in branchList: tree.insert('', 'end', values=[ item[0], item[1], saveAccountList[index], loanAccountList[index], staffList[index], loanList[index] ]) index += 1 statTop.mainloop()
def branchWindow(): branchTop = tk.Toplevel(width=900, height=550) branchTop.title(string='branchTop') branchTop.resizable(False, False) addDataBtn = tk.Button(branchTop, text='新建', height=4, width=10) addDataBtn.place(x=40, y=40) attrCombo = ttk.Combobox(branchTop, width=12, state='readonly') attrCombo['values'] = ('支行名', '城市', '资产') attrCombo.place(x=220, y=20) attrCombo.current(0) conditionEntry = tk.Entry(branchTop, width=30) conditionEntry.place(x=380, y=20) cacheBtn = tk.Button(branchTop, text='添加', width=10) cacheBtn.place(x=650, y=20) searchBtn = tk.Button(branchTop, text='查找', width=10) searchBtn.place(x=750, y=20) conditionList = tk.Listbox(branchTop, width=52, height=5) conditionList.place(x=220, y=50) tree = ttk.Treeview(branchTop, height=13, columns=('name', 'city', 'asset')) tree.column('name', width=400, anchor='center') tree.column('city', width=200, anchor='center') tree.column('asset', width=200, anchor='center') tree.heading('name', text='支行名') tree.heading('city', text='城市') tree.heading('asset', text='资产') tree["show"] = "headings" tree.place(x=40, y=200) ########################################## def addData(): newTop = tk.Toplevel(branchTop, width=660, height=300) newTop.resizable(False, False) newTop.overrideredirect(1) label1 = tk.Label(newTop, text='支行名') label1.place(x=100, y=50) entry1 = tk.Entry(newTop, width=30) entry1.place(x=60, y=100) label2 = tk.Label(newTop, text='城市') label2.place(x=350, y=50) entry2 = tk.Entry(newTop, width=18) entry2.place(x=300, y=100) label3 = tk.Label(newTop, text='资产') label3.place(x=500, y=50) entry3 = tk.Entry(newTop, width=18) entry3.place(x=450, y=100) confirmBtn = tk.Button(newTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(newTop, text='取消') cancelBtn.place(x=350, y=250) ######################## def confirmFunc(): branchName = entry1.get().strip() city = entry2.get().strip() asset = entry3.get().strip() sql = "insert into bank.支行(`支行名`,`城市`,`资产`) values(%s,%s,%s);" try: connBranch.execCommit(sql, (branchName, city, asset)) closeWindow() except Exception as e: tk.messagebox.showerror( "警告", "无法添加 %s,%s,%s!" % (branchName, city, asset)) print("Fail", e) def closeWindow(): newTop.destroy() confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) newTop.mainloop() def saveCondition(): attr = attrCombo.get() condition = conditionEntry.get().strip() conditionList.insert('end', attr + ':' + condition) def seachData(): olditems = tree.get_children() [tree.delete(olditem) for olditem in olditems] rawCondition = conditionList.get(0, 'end') if len(rawCondition) == 0: sql = "select * from bank.支行;" else: sql = genSQL("支行", rawCondition) alldata = connBranch.execSQL(sql, None) while True: item = alldata.fetchone() if not item: break print(item) tree.insert('', 'end', values=item) def removeCondition(*args): conditionList.delete(conditionList.curselection()[0]) def removeData(): item = tree.selection()[0] data = tree.item(item, "values") sql = 'delete from bank.支行 where `支行名`=%s and `城市`=%s and `资产`=%s;' try: connBranch.execCommit(sql, data) tree.delete(tree.selection()) except Exception as e: tk.messagebox.showerror("警告", "无法删除!") print("Fail", e) def editData(): item = tree.selection()[0] data = tree.item(item, "values") editTop = tk.Toplevel(branchTop, width=660, height=300) editTop.resizable(False, False) editTop.overrideredirect(1) label1 = tk.Label(editTop, text='支行名') label1.place(x=100, y=50) text1 = tk.StringVar() entry1 = tk.Entry(editTop, width=30, textvariable=text1) entry1.place(x=60, y=100) label2 = tk.Label(editTop, text='城市') label2.place(x=350, y=50) text2 = tk.StringVar() entry2 = tk.Entry(editTop, width=18, textvariable=text2) entry2.place(x=300, y=100) label3 = tk.Label(editTop, text='资产') label3.place(x=500, y=50) text3 = tk.StringVar() entry3 = tk.Entry(editTop, width=18, textvariable=text3) entry3.place(x=450, y=100) confirmBtn = tk.Button(editTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(editTop, text='取消') cancelBtn.place(x=350, y=250) ######################## def confirmFunc(): branchName = entry1.get().strip() city = entry2.get().strip() asset = entry3.get().strip() sql = "update bank.支行 set `支行名`=%s,`城市`=%s,`资产`=%s where `支行名`=%s and `城市`=%s and `资产`=%s;" try: connBranch.execCommit( sql, (branchName, city, asset, data[0], data[1], data[2])) closeWindow() except Exception as e: tk.messagebox.showerror("警告", "修改失败!") print("Fail", e) def closeWindow(): editTop.destroy() text1.set(data[0]) text2.set(data[1]) text3.set(data[2]) confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) editTop.mainloop() rightMenu = tk.Menu(branchTop) rightMenu.add_command(label='编辑', command=editData) rightMenu.add_command(label='删除', command=removeData) def popupmenu(event): try: rightMenu.post(event.x_root, event.y_root) except: pass def closePop(*args): rightMenu.unpost() connBranch = dbop.mysqlConn() conditionList.bind('<Button-3>', removeCondition) tree.bind('<Button-3>', popupmenu) tree.bind('<Button-1>', closePop) conditionList.bind('<Double-Button-1>', removeCondition) cacheBtn.config(command=saveCondition) addDataBtn.config(command=addData) searchBtn.config(command=seachData) branchTop.mainloop()
def loanWindow(): loanTop = tk.Toplevel(width=900, height=550) loanTop.title(string='loanTop') loanTop.resizable(False, False) addDataBtn = tk.Button(loanTop, text='新建', height=4, width=10) addDataBtn.place(x=40, y=40) attrCombo = ttk.Combobox(loanTop, width=14, state='readonly') attrCombo['values'] = ('贷款号', '支行名', '总金额', '当前状态') attrCombo.place(x=220, y=20) attrCombo.current(0) conditionEntry = tk.Entry(loanTop, width=30) conditionEntry.place(x=380, y=20) cacheBtn = tk.Button(loanTop, text='添加', width=10) cacheBtn.place(x=650, y=20) searchBtn = tk.Button(loanTop, text='查找', width=10) searchBtn.place(x=750, y=20) conditionList = tk.Listbox(loanTop, width=52, height=5) conditionList.place(x=220, y=50) tree = ttk.Treeview(loanTop, height=13, columns=('loanID', 'branchName', 'balance', 'status')) tree.column('loanID', width=200, anchor='center') tree.column('branchName', width=300, anchor='center') tree.column('balance', width=150, anchor='center') tree.column('status', width=150, anchor='center') tree.heading('loanID', text='贷款号') tree.heading('branchName', text='支行名') tree.heading('balance', text='总金额') tree.heading('status', text='当前状态') tree["show"] = "headings" tree.place(x=40, y=200) # loanTop.overrideredirect(1) def addData(): newTop = tk.Toplevel(loanTop, width=660, height=300) newTop.resizable(False, False) newTop.overrideredirect(1) label1 = tk.Label(newTop, text='贷款号') label1.place(x=40, y=50) entry1 = tk.Entry(newTop, width=20) entry1.place(x=40, y=100) label2 = tk.Label(newTop, text='支行名') label2.place(x=140, y=50) entry2 = tk.Entry(newTop, width=30) entry2.place(x=140, y=100) label3 = tk.Label(newTop, text='总金额') label3.place(x=290, y=50) entry3 = tk.Entry(newTop, width=10) entry3.place(x=290, y=100) label4 = tk.Label(newTop, text='客户') label4.place(x=340, y=50) entry4 = tk.Entry(newTop, width=20) entry4.place(x=340, y=100) label5 = tk.Label(newTop, text='银行负责人') label5.place(x=440, y=50) entry5 = tk.Entry(newTop, width=20) entry5.place(x=440, y=100) confirmBtn = tk.Button(newTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(newTop, text='取消') cancelBtn.place(x=350, y=250) ######################## def confirmFunc(): loanID = entry1.get().strip() branchName = entry2.get().strip() balance = entry3.get().strip() client = entry4.get().strip() staff = entry5.get().strip() sql1 = "insert into bank.共有(`贷款号`,`客户身份证号`) values(%s,%s);" sql2 = "insert into bank.负责(`员工身份证号`,`客户身份证号`,`负责人类型`) values(%s,%s,%s);" sql3 = "insert into bank.贷款(`贷款号`,`支行名`,`总金额`) values(%s,%s,%s);" try: connLoan.execCommit(sql3, (loanID, branchName, balance)) connLoan.execCommit(sql1, (loanID, client)) connLoan.execCommit(sql2, (staff, client, "贷款负责人_" + loanID)) closeWindow() except Exception as e: tk.messagebox.showerror( "警告", "无法添加 %s,%s,%s!" % (loanID, branchName, balance)) print("Fail", e) def closeWindow(): newTop.destroy() confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) newTop.mainloop() def saveCondition(): attr = attrCombo.get() condition = conditionEntry.get().strip() conditionList.insert('end', attr + ':' + condition) def seachData(): olditems = tree.get_children() [tree.delete(olditem) for olditem in olditems] rawCondition = conditionList.get(0, 'end') if len(rawCondition) == 0: sql = "select * from bank.贷款;" else: sql = genSQL("贷款", rawCondition) alldata = connLoan.execSQL(sql, None) while True: item = alldata.fetchone() if not item: break print(item) tree.insert('', 'end', values=item) def removeCondition(*args): conditionList.delete(conditionList.curselection()[0]) def removeData(): item = tree.selection()[0] data = tree.item(item, "values") if (data[3] == '发放中'): tk.messagebox.showerror("警告", "发放中贷款无法删除!") return sql1 = 'delete from bank.贷款 where `贷款号`=%s' sql2 = 'delete from bank.共有 where `贷款号`=%s' sql3 = 'delete from bank.负责 where `客户身份证号`=%s and `负责人类型`=%s' sql2_1 = 'select `客户身份证号` from bank.共有 where `贷款号`=%s' try: clinets = connLoan.execSQL(sql2_1, data[0]) while True: client = clinets.fetchone() if not client: break connLoan.execCommit(sql3, (client, '贷款负责人_' + data[0])) connLoan.execCommit(sql2, data[0]) connLoan.execCommit(sql1, data[0]) tree.delete(tree.selection()) except Exception as e: tk.messagebox.showerror("警告", "无法删除!") print("Fail", e) def editData(): item = tree.selection()[0] data = tree.item(item, "values") editTop = tk.Toplevel(loanTop, width=660, height=300) editTop.resizable(False, False) editTop.overrideredirect(1) label1 = tk.Label(editTop, text='贷款号') label1.place(x=100, y=50) text1 = tk.StringVar() entry1 = tk.Entry(editTop, width=30, textvariable=text1) entry1.place(x=60, y=100) label2 = tk.Label(editTop, text='支行名') label2.place(x=350, y=50) text2 = tk.StringVar() entry2 = tk.Entry(editTop, width=18, textvariable=text2) entry2.place(x=300, y=100) label3 = tk.Label(editTop, text='总金额') label3.place(x=500, y=50) text3 = tk.StringVar() entry3 = tk.Entry(editTop, width=18, textvariable=text3) entry3.place(x=450, y=100) confirmBtn = tk.Button(editTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(editTop, text='取消') cancelBtn.place(x=350, y=250) ######################## def confirmFunc(): loanID = entry1.get().strip() branchName = entry2.get().strip() balance = entry3.get().strip() sql = "update bank.贷款 set `贷款号`=%s,`支行名`=%s,`总金额`=%s where `贷款号`=%s and `支行名`=%s and `总金额`=%s;" try: connLoan.execCommit( sql, (loanID, branchName, balance, data[0], data[1], data[2])) closeWindow() except Exception as e: tk.messagebox.showerror("警告", "修改失败!") print("Fail", e) def closeWindow(): editTop.destroy() text1.set(data[0]) text2.set(data[1]) text3.set(data[2]) confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) editTop.mainloop() def issueLoan(): item = tree.selection()[0] data = tree.item(item, "values") issueTop = tk.Toplevel(loanTop, width=810, height=300) issueTop.resizable(False, False) issueTop.overrideredirect(1) label1 = tk.Label(issueTop, text='贷款号') label1.place(x=100, y=50) text1 = tk.StringVar() entry1 = tk.Entry(issueTop, width=30, textvariable=text1) entry1.place(x=60, y=100) label2 = tk.Label(issueTop, text='当前余额') label2.place(x=350, y=50) text2 = tk.StringVar() entry2 = tk.Entry(issueTop, width=18, textvariable=text2) entry2.place(x=300, y=100) label3 = tk.Label(issueTop, text='发放金额') label3.place(x=500, y=50) text3 = tk.StringVar() entry3 = tk.Entry(issueTop, width=18, textvariable=text3) entry3.place(x=450, y=100) label4 = tk.Label(issueTop, text='发放日期') label4.place(x=650, y=50) text4 = tk.StringVar() entry4 = tk.Entry(issueTop, width=18, textvariable=text4) entry4.place(x=600, y=100) confirmBtn = tk.Button(issueTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(issueTop, text='取消') cancelBtn.place(x=350, y=250) ######################################### def confirmFunc(): willIssue = float(entry3.get().strip()) balance = float(data[2]) issueDate = entry4.get().strip() if (willIssue > balance): tk.messagebox.showerror("警告", "余额不足!") return balance -= willIssue sql1 = "update bank.贷款 set `总金额`=%s,`当前状态`=%s where `贷款号`=%s;" sql2 = "insert into bank.支付情况(`支付日期`,`贷款号`,`支付金额`) values(%s,%s,%s);" sql3 = "update bank.支行 set `资产`=`资产`-%s where `支行名`=%s;" status = "发放中" if (balance == 0): status = "已全部发放" try: connLoan.execSQL(sql2, (issueDate, data[0], str(willIssue))) connLoan.execSQL(sql1, (str(balance), status, data[0])) connLoan.execCommit(sql3, (willIssue, data[1])) closeWindow() except Exception as e: tk.messagebox.showerror("警告", "发放失败!") print("Fail", e) def closeWindow(): issueTop.destroy() text1.set(data[0]) text2.set(data[2]) confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) issueTop.mainloop() def addUser(): item = tree.selection()[0] data = tree.item(item, "values") adduserTop = tk.Toplevel(loanTop, width=810, height=300) adduserTop.resizable(False, False) adduserTop.overrideredirect(1) label1 = tk.Label(adduserTop, text='贷款号') label1.place(x=100, y=50) text1 = tk.StringVar() entry1 = tk.Entry(adduserTop, width=30, textvariable=text1) entry1.place(x=60, y=100) label2 = tk.Label(adduserTop, text='客户身份证号') label2.place(x=350, y=50) text2 = tk.StringVar() entry2 = tk.Entry(adduserTop, width=18, textvariable=text2) entry2.place(x=300, y=100) label3 = tk.Label(adduserTop, text='员工身份证号') label3.place(x=500, y=50) text3 = tk.StringVar() entry3 = tk.Entry(adduserTop, width=18, textvariable=text3) entry3.place(x=450, y=100) confirmBtn = tk.Button(adduserTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(adduserTop, text='取消') cancelBtn.place(x=350, y=250) ######################################### def confirmFunc(): #loanID = entry1.get().strip() loanID = data[0] clientID = entry2.get().strip() staffID = entry3.get().strip() sql1 = "insert into bank.负责(`员工身份证号`,`客户身份证号`,`负责人类型`) values(%s,%s,%s);" sql2 = "insert into bank.共有(`贷款号`,`客户身份证号`) values(%s,%s);" try: connLoan.execSQL(sql1, (staffID, clientID, "贷款负责人_" + loanID)) connLoan.execCommit(sql2, (loanID, clientID)) closeWindow() except Exception as e: tk.messagebox.showerror("警告", "添加失败!") print("Fail", e) def closeWindow(): adduserTop.destroy() text1.set(data[0]) confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) adduserTop.mainloop() rightMenu = tk.Menu(loanTop) rightMenu.add_command(label='编辑', command=editData) rightMenu.add_command(label='删除', command=removeData) rightMenu.add_command(label='发放贷款', command=issueLoan) rightMenu.add_command(label='添加贷款人', command=addUser) def popupmenu(event): try: rightMenu.post(event.x_root, event.y_root) except: pass def closePop(*args): rightMenu.unpost() connLoan = dbop.mysqlConn() conditionList.bind('<Button-3>', removeCondition) tree.bind('<Button-3>', popupmenu) tree.bind('<Button-1>', closePop) conditionList.bind('<Double-Button-1>', removeCondition) cacheBtn.config(command=saveCondition) addDataBtn.config(command=addData) searchBtn.config(command=seachData) loanTop.mainloop()
def clientWindow(): clientTop = tk.Toplevel(width=900, height=550) clientTop.title(string='clientTop') clientTop.resizable(False, False) addDataBtn = tk.Button(clientTop, text='新建', height=4, width=10) addDataBtn.place(x=40, y=40) attrCombo = ttk.Combobox(clientTop, width=12, state='readonly') attrCombo['values'] = ('客户身份证号', '姓名', '联系电话', '家庭住址', '联系人姓名', '联系人手机号', '联系人email', '关系') attrCombo.place(x=220, y=20) attrCombo.current(0) conditionEntry = tk.Entry(clientTop, width=30) conditionEntry.place(x=380, y=20) cacheBtn = tk.Button(clientTop, text='添加', width=10) cacheBtn.place(x=650, y=20) searchBtn = tk.Button(clientTop, text='查找', width=10) searchBtn.place(x=750, y=20) conditionList = tk.Listbox(clientTop, width=52, height=5) conditionList.place(x=220, y=50) tree = ttk.Treeview(clientTop, height=13, columns=('ID', 'name', 'phone', 'address', 'contacts', 'phone_c', 'email', 'relation')) tree.column('ID', width=150, anchor='center') tree.column('name', width=50, anchor='center') tree.column('phone', width=100, anchor='center') tree.column('address', width=150, anchor='center') tree.column('contacts', width=100, anchor='center') tree.column('phone_c', width=100, anchor='center') tree.column('email', width=100, anchor='center') tree.column('relation', width=50, anchor='center') tree.heading('ID', text='客户身份证号') tree.heading('name', text='姓名') tree.heading('phone', text='联系电话') tree.heading('address', text='家庭住址') tree.heading('contacts', text='联系人姓名') tree.heading('phone_c', text='联系人手机号') tree.heading('email', text='联系人email') tree.heading('relation', text='关系') tree["show"] = "headings" tree.place(x=40, y=200) # clientTop.overrideredirect(1) ############################################ def addData(): newTop = tk.Toplevel(clientTop, width=900, height=300) newTop.resizable(False, False) newTop.overrideredirect(1) label1 = tk.Label(newTop, text='客户身份证号') label1.place(x=40, y=50) entry1 = tk.Entry(newTop, width=18) entry1.place(x=40, y=100) label2 = tk.Label(newTop, text='姓名') label2.place(x=190, y=50) entry2 = tk.Entry(newTop, width=6) entry2.place(x=190, y=100) label3 = tk.Label(newTop, text='联系电话') label3.place(x=240, y=50) entry3 = tk.Entry(newTop, width=12) entry3.place(x=240, y=100) label4 = tk.Label(newTop, text='家庭住址') label4.place(x=340, y=50) entry4 = tk.Entry(newTop, width=18) entry4.place(x=340, y=100) label5 = tk.Label(newTop, text='联系人姓名') label5.place(x=490, y=50) entry5 = tk.Entry(newTop, width=12) entry5.place(x=490, y=100) label6 = tk.Label(newTop, text='联系人手机号') label6.place(x=590, y=50) entry6 = tk.Entry(newTop, width=12) entry6.place(x=590, y=100) label7 = tk.Label(newTop, text='联系人email') label7.place(x=690, y=50) entry7 = tk.Entry(newTop, width=13) entry7.place(x=690, y=100) label8 = tk.Label(newTop, text='关系') label8.place(x=800, y=50) entry8 = tk.Entry(newTop, width=5) entry8.place(x=800, y=100) confirmBtn = tk.Button(newTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(newTop, text='取消') cancelBtn.place(x=350, y=250) ######################## def confirmFunc(): ID = entry1.get().strip() name = entry2.get().strip() phone = entry3.get().strip() address = entry4.get().strip() contacts = entry5.get().strip() phone_c = entry6.get().strip() email = entry7.get().strip() relation = entry8.get().strip() sql = "insert into bank.客户(`客户身份证号`,`姓名`,`联系电话`,`家庭住址`,`联系人姓名`,`联系人手机号`,`联系人email`,`关系`) values(%s,%s,%s,%s,%s,%s,%s,%s);" try: connClient.execCommit(sql, (ID, name, phone, address, contacts, phone_c, email, relation)) closeWindow() except Exception as e: tk.messagebox.showerror( "警告", "无法添加 %s,%s,%s,%s,%s,%s,%s,%s!" % (ID, name, phone, address, contacts, phone_c, email, relation)) print("Fail", e) def closeWindow(): newTop.destroy() confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) newTop.mainloop() def saveCondition(): attr = attrCombo.get() condition = conditionEntry.get().strip() conditionList.insert('end', attr + ':' + condition) def seachData(): olditems = tree.get_children() [tree.delete(olditem) for olditem in olditems] rawCondition = conditionList.get(0, 'end') if len(rawCondition) == 0: sql = "select * from bank.客户;" else: sql = genSQL("客户", rawCondition) alldata = connClient.execSQL(sql, None) while True: item = alldata.fetchone() if not item: break print(item) tree.insert('', 'end', values=item) def removeCondition(*args): conditionList.delete(conditionList.curselection()[0]) def removeData(): item = tree.selection()[0] data = tree.item(item, "values") sql = "delete from bank.客户 where `客户身份证号`=%s and `姓名`=%s and `联系电话`=%s and `家庭住址`=%s and `联系人姓名`=%s and `联系人手机号`=%s and `联系人email`=%s and `关系`=%s;" try: connClient.execCommit(sql, data) tree.delete(tree.selection()) except Exception as e: tk.messagebox.showerror("警告", "无法删除!") print("Fail", e) def editData(): item = tree.selection()[0] data = tree.item(item, "values") editTop = tk.Toplevel(clientTop, width=900, height=300) editTop.resizable(False, False) editTop.overrideredirect(1) label1 = tk.Label(editTop, text='客户身份证号') label1.place(x=40, y=50) text1 = tk.StringVar() entry1 = tk.Entry(editTop, width=18, textvariable=text1) entry1.place(x=40, y=100) label2 = tk.Label(editTop, text='姓名') label2.place(x=190, y=50) text2 = tk.StringVar() entry2 = tk.Entry(editTop, width=6, textvariable=text2) entry2.place(x=190, y=100) label3 = tk.Label(editTop, text='联系电话') label3.place(x=240, y=50) text3 = tk.StringVar() entry3 = tk.Entry(editTop, width=12, textvariable=text3) entry3.place(x=240, y=100) label4 = tk.Label(editTop, text='家庭住址') label4.place(x=340, y=50) text4 = tk.StringVar() entry4 = tk.Entry(editTop, width=18, textvariable=text4) entry4.place(x=340, y=100) label5 = tk.Label(editTop, text='联系人姓名') label5.place(x=490, y=50) text5 = tk.StringVar() entry5 = tk.Entry(editTop, width=12, textvariable=text5) entry5.place(x=490, y=100) label6 = tk.Label(editTop, text='联系人手机号') label6.place(x=590, y=50) text6 = tk.StringVar() entry6 = tk.Entry(editTop, width=12, textvariable=text6) entry6.place(x=590, y=100) label7 = tk.Label(editTop, text='联系人email') label7.place(x=690, y=50) text7 = tk.StringVar() entry7 = tk.Entry(editTop, width=13, textvariable=text7) entry7.place(x=690, y=100) label8 = tk.Label(editTop, text='关系') label8.place(x=800, y=50) text8 = tk.StringVar() entry8 = tk.Entry(editTop, width=5, textvariable=text8) entry8.place(x=800, y=100) confirmBtn = tk.Button(editTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(editTop, text='取消') cancelBtn.place(x=350, y=250) ######################## def confirmFunc(): ID = entry1.get().strip() name = entry2.get().strip() phone = entry3.get().strip() address = entry4.get().strip() contacts = entry5.get().strip() phone_c = entry6.get().strip() email = entry7.get().strip() relation = entry8.get().strip() sql = "update bank.客户 set `客户身份证号`=%s , `姓名`=%s , `联系电话`=%s , `家庭住址`=%s , `联系人姓名`=%s , `联系人手机号`=%s , `联系人email`=%s , `关系`=%s where `客户身份证号`=%s and `姓名`=%s and `联系电话`=%s and `家庭住址`=%s and `联系人姓名`=%s and `联系人手机号`=%s and `联系人email`=%s and `关系`=%s;" try: connClient.execCommit( sql, (ID, name, phone, address, contacts, phone_c, email, relation, data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7])) closeWindow() except Exception as e: tk.messagebox.showerror("警告", "修改失败!") print("Fail", e) def closeWindow(): editTop.destroy() text1.set(data[0]) text2.set(data[1]) text3.set(data[2]) text4.set(data[3]) text5.set(data[4]) text6.set(data[5]) text7.set(data[6]) text8.set(data[7]) confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) editTop.mainloop() rightMenu = tk.Menu(clientTop) rightMenu.add_command(label='编辑', command=editData) rightMenu.add_command(label='删除', command=removeData) def popupmenu(event): try: rightMenu.post(event.x_root, event.y_root) except: pass def closePop(*args): rightMenu.unpost() connClient = dbop.mysqlConn() conditionList.bind('<Button-3>', removeCondition) tree.bind('<Button-3>', popupmenu) tree.bind('<Button-1>', closePop) conditionList.bind('<Double-Button-1>', removeCondition) cacheBtn.config(command=saveCondition) addDataBtn.config(command=addData) searchBtn.config(command=seachData) clientTop.mainloop()
def accountWindow(): accountTop = tk.Toplevel(width=900, height=550) accountTop.title(string='accountTop') accountTop.resizable(False, False) addDataBtn = tk.Button(accountTop, text='新建', height=4, width=10) addDataBtn.place(x=40, y=40) attrCombo = ttk.Combobox(accountTop, width=14, state='readonly') attrCombo['values'] = ('账户号', '账户类型', '支行名', '余额', '开户日期', '最近访问日期', '利率', '货币类型', '透支额') attrCombo.place(x=220, y=20) attrCombo.current(0) conditionEntry = tk.Entry(accountTop, width=30) conditionEntry.place(x=380, y=20) cacheBtn = tk.Button(accountTop, text='添加', width=10) cacheBtn.place(x=650, y=20) searchBtn = tk.Button(accountTop, text='查找', width=10) searchBtn.place(x=750, y=20) conditionList = tk.Listbox(accountTop, width=52, height=5) conditionList.place(x=220, y=50) tree = ttk.Treeview(accountTop, height=13, columns=('accountID', 'type', 'balance', 'createDate', 'creator', 'lastVisit', 'rate', 'currencyType', 'overdraft')) tree.column('accountID', width=100, anchor='center') tree.column('type', width=70, anchor='center') tree.column('balance', width=50, anchor='center') tree.column('createDate', width=100, anchor='center') tree.column('creator', width=150, anchor='center') tree.column('lastVisit', width=100, anchor='center') tree.column('rate', width=50, anchor='center') tree.column('currencyType', width=70, anchor='center') tree.column('overdraft', width=100, anchor='center') tree.heading('accountID', text='账户号') tree.heading('type', text='账户类型') tree.heading('balance', text='余额') tree.heading('createDate', text='开户日期') tree.heading('creator', text='支行名') tree.heading('lastVisit', text='最近访问日期') tree.heading('rate', text='利率') tree.heading('currencyType', text='货币类型') tree.heading('overdraft', text='透支额') tree["show"] = "headings" tree.place(x=40, y=200) # accountTop.overrideredirect(1) def addData(): newTop = tk.Toplevel(accountTop, width=1050, height=300) newTop.resizable(False, False) newTop.overrideredirect(1) accountFlag = tk.BooleanVar() accountFlag.set(True) tk.Radiobutton(newTop, variable=accountFlag, text='储蓄账户', value=True).place(x=240, y=10) tk.Radiobutton(newTop, variable=accountFlag, text='支票账户', value=False).place(x=480, y=10) label1 = tk.Label(newTop, text='账户号') label1.place(x=40, y=50) entry1 = tk.Entry(newTop, width=8) entry1.place(x=40, y=100) label2 = tk.Label(newTop, text='余额') label2.place(x=120, y=50) entry2 = tk.Entry(newTop, width=12) entry2.place(x=120, y=100) label3 = tk.Label(newTop, text='开户日期') label3.place(x=240, y=50) entry3 = tk.Entry(newTop, width=12) entry3.place(x=240, y=100) label4 = tk.Label(newTop, text='支行名') label4.place(x=360, y=50) entry4 = tk.Entry(newTop, width=12) entry4.place(x=360, y=100) label5 = tk.Label(newTop, text='利率') label5.place(x=480, y=50) entry5 = tk.Entry(newTop, width=12) entry5.place(x=480, y=100) label6 = tk.Label(newTop, text='货币类型') label6.place(x=600, y=50) entry6 = tk.Entry(newTop, width=8) entry6.place(x=600, y=100) label7 = tk.Label(newTop, text='透支额') label7.place(x=680, y=50) entry7 = tk.Entry(newTop, width=8) entry7.place(x=680, y=100) label8 = tk.Label(newTop, text='客户') label8.place(x=760, y=50) entry8 = tk.Entry(newTop, width=12) entry8.place(x=760, y=100) label9 = tk.Label(newTop, text='负责人') label9.place(x=880, y=50) entry9 = tk.Entry(newTop, width=12) entry9.place(x=880, y=100) confirmBtn = tk.Button(newTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(newTop, text='取消') cancelBtn.place(x=350, y=250) ######################## def confirmFunc(): if (accountFlag.get()): accountType = "储蓄账户" accountID = entry1.get().strip() balance = entry2.get().strip() createDate = entry3.get().strip() branchName = entry4.get().strip() rate = entry5.get().strip() currencyType = entry6.get().strip() overdraft = entry7.get().strip() client = entry8.get().strip() staff = entry9.get().strip() sql1 = "insert into bank.账户(`账户号`,`支行名`,`余额`,`开户日期`) values(%s,%s,%s,%s);" sql2 = "insert into bank.储蓄账户(`账户号`,`支行名`,`余额`,`开户日期`,`利率`,`货币类型`) values(%s,%s,%s,%s,%s,%s);" sql3 = "insert into bank.拥有(`客户身份证号`,`账户号`,`最近访问日期`) values(%s,%s,%s);" sql4 = "insert into bank.负责(`员工身份证号`,`客户身份证号`,`负责人类型`) values(%s,%s,%s);" sql5 = "update bank.支行 set `资产`=`资产`+%s where `支行名`=%s;" try: connAccount.execSQL( sql1, (accountID, branchName, balance, createDate)) connAccount.execSQL(sql2, (accountID, branchName, balance, createDate, rate, currencyType)) connAccount.execSQL(sql3, (client, accountID, createDate)) connAccount.execSQL( sql4, (staff, client, "银行账户负责人_" + accountID)) connAccount.execCommit(sql5, (balance, branchName)) closeWindow() except Exception as e: tk.messagebox.showerror( "警告", "无法添加 %s,%s,%s,%s,%s,%s,%s!" % (accountID, accountType, balance, createDate, branchName, rate, currencyType)) print("Fail", e) else: accountType = "支票账户" accountID = entry1.get().strip() balance = entry2.get().strip() createDate = entry3.get().strip() branchName = entry4.get().strip() rate = entry5.get().strip() currencyType = entry6.get().strip() overdraft = entry7.get().strip() client = entry8.get().strip() staff = entry9.get().strip() sql1 = "insert into bank.账户(`账户号`,`支行名`,`余额`,`开户日期`) values(%s,%s,%s,%s);" sql2 = "insert into bank.支票账户(`账户号`,`支行名`,`余额`,`开户日期`,`利率`,`货币类型`,`透支额`) values(%s,%s,%s,%s,%s,%s,%s);" sql3 = "insert into bank.拥有(`客户身份证号`,`账户号`,`最近访问日期`) values(%s,%s,%s);" sql4 = "insert into bank.负责(`员工身份证号`,`客户身份证号`,`负责人类型`) values(%s,%s,%s);" sql5 = "update bank.支行 set `资产`=`资产`+%s where `支行名`=%s;" try: connAccount.execSQL( sql1, (accountID, branchName, balance, createDate)) connAccount.execSQL( sql2, (accountID, branchName, balance, createDate, rate, currencyType, overdraft)) connAccount.execSQL(sql3, (client, accountID, createDate)) connAccount.execSQL( sql4, (staff, client, "银行账户负责人_" + accountID)) connAccount.execCommit(sql5, (balance, branchName)) closeWindow() except Exception as e: tk.messagebox.showerror( "警告", "无法添加 %s,%s,%s,%s,%s,%s!" % (accountID, accountType, balance, createDate, branchName, overdraft)) print("Fail", e) def closeWindow(): newTop.destroy() confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) newTop.mainloop() def saveCondition(): attr = attrCombo.get() condition = conditionEntry.get().strip() conditionList.insert('end', attr + ':' + condition) def seachData(): olditems = tree.get_children() [tree.delete(olditem) for olditem in olditems] rawCondition = conditionList.get(0, 'end') if len(rawCondition) == 0: sql1 = "select * from bank.储蓄账户;" sql2 = "select * from bank.支票账户;" else: sql1 = genSQL("储蓄账户", rawCondition) sql2 = genSQL("支票账户", rawCondition) try: alldata1 = connAccount.execSQL(sql1, None) except: alldata1 = False if (alldata1 != False): for item in alldata1.fetchall(): print(item) tmp = connAccount.execSQL( "select * from bank.拥有 where `账户号`=%s;", item[0]) tmpdata = tmp.fetchall()[0] tree.insert('', 'end', values=(item[0], "储蓄账户", item[2], item[3], item[1], tmpdata[2], item[4], item[5], "/")) try: alldata2 = connAccount.execSQL(sql2, None) except: alldata2 = False if (alldata1 != False): for item in alldata1.fetchall(): print(item) tmp = connAccount.execSQL( "select * from bank.拥有 where `账户号`=%s;", item[0]) tmpdata = tmp.fetchall()[0] tree.insert('', 'end', values=(item[0], "支票账户", item[2], item[3], item[1], tmpdata[2], item[4], item[5], item[6])) def removeCondition(*args): conditionList.delete(conditionList.curselection()[0]) def removeData(): item = tree.selection()[0] data = tree.item(item, "values") sql1 = "delete from bank.账户 where `账户号`=%s;" sql2 = "delete from bank.储蓄账户 where `账户号`=%s;" sql3 = "delete from bank.支票账户 where `账户号`=%s;" sql4 = "delete from bank.拥有 where `账户号`=%s;" sql5 = "delete from bank.负责 where `负责人类型`=%s;" try: connAccount.execSQL(sql2, data[0]) connAccount.execSQL(sql3, data[0]) connAccount.execSQL(sql1, data[0]) connAccount.execSQL(sql4, data[0]) connAccount.execCommit(sql5, "银行账户负责人_" + data[0]) tree.delete(tree.selection()) except Exception as e: tk.messagebox.showerror("警告", "无法删除!") print("Fail", e) def editData(): item = tree.selection()[0] data = tree.item(item, "values") editTop = tk.Toplevel(accountTop, width=900, height=300) editTop.resizable(False, False) editTop.overrideredirect(1) label1 = tk.Label(editTop, text='账户号') label1.place(x=40, y=50) text1 = tk.StringVar() entry1 = tk.Entry(editTop, width=18, textvariable=text1) entry1.place(x=40, y=100) label2 = tk.Label(editTop, text='账户类型') label2.place(x=190, y=50) text2 = tk.StringVar() entry2 = tk.Entry(editTop, width=6, textvariable=text2) entry2.place(x=190, y=100) label3 = tk.Label(editTop, text='余额') label3.place(x=240, y=50) text3 = tk.StringVar() entry3 = tk.Entry(editTop, width=12, textvariable=text3) entry3.place(x=240, y=100) label4 = tk.Label(editTop, text='开户日期') label4.place(x=340, y=50) text4 = tk.StringVar() entry4 = tk.Entry(editTop, width=12, textvariable=text4) entry4.place(x=340, y=100) label5 = tk.Label(editTop, text='支行名') label5.place(x=440, y=50) text5 = tk.StringVar() entry5 = tk.Entry(editTop, width=18, textvariable=text5) entry5.place(x=440, y=100) label6 = tk.Label(editTop, text='利率') label6.place(x=590, y=50) text6 = tk.StringVar() entry6 = tk.Entry(editTop, width=12, textvariable=text6) entry6.place(x=590, y=100) label7 = tk.Label(editTop, text='货币类型') label7.place(x=690, y=50) text7 = tk.StringVar() entry7 = tk.Entry(editTop, width=6, textvariable=text7) entry7.place(x=690, y=100) label8 = tk.Label(editTop, text='透支额') label8.place(x=740, y=50) text8 = tk.StringVar() entry8 = tk.Entry(editTop, width=12, textvariable=text8) entry8.place(x=740, y=100) confirmBtn = tk.Button(editTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(editTop, text='取消') cancelBtn.place(x=350, y=250) ######################## def confirmFunc(): accountID = entry1.get().strip() accountType = entry2.get().strip() balance = entry3.get().strip() createDate = entry4.get().strip() branchName = entry5.get().strip() rate = entry6.get().strip() currencyType = entry7.get().strip() overdraft = entry8.get().strip() if (accountType == '储蓄账户'): sql = "update bank.储蓄账户 set `利率`=%s , `货币类型`=%s where `账户号`='%s';" try: connAccount.execCommit(sql, (rate, currencyType, data[0])) print(sql % (rate, currencyType, data[0])) closeWindow() except Exception as e: tk.messagebox.showerror("警告", "修改失败!") print("Fail", e) else: sql = "update bank.支票账户 set `透支额`=%s where `账户号`=%s;" try: connAccount.execCommit(sql, (overdraft, data[0])) closeWindow() except Exception as e: tk.messagebox.showerror("警告", "修改失败!") print("Fail", e) def closeWindow(): editTop.destroy() text1.set(data[0]) text2.set(data[1]) text3.set(data[2]) text4.set(data[3]) text5.set(data[4]) text6.set(data[6]) text7.set(data[7]) text8.set(data[8]) confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) editTop.mainloop() def addUser(): item = tree.selection()[0] data = tree.item(item, "values") adduserTop = tk.Toplevel(accountTop, width=810, height=300) adduserTop.resizable(False, False) adduserTop.overrideredirect(1) label1 = tk.Label(adduserTop, text='账户号') label1.place(x=100, y=50) text1 = tk.StringVar() entry1 = tk.Entry(adduserTop, width=30, textvariable=text1) entry1.place(x=60, y=100) label2 = tk.Label(adduserTop, text='客户身份证号') label2.place(x=350, y=50) text2 = tk.StringVar() entry2 = tk.Entry(adduserTop, width=18, textvariable=text2) entry2.place(x=300, y=100) label3 = tk.Label(adduserTop, text='员工身份证号') label3.place(x=500, y=50) text3 = tk.StringVar() entry3 = tk.Entry(adduserTop, width=18, textvariable=text3) entry3.place(x=450, y=100) label4 = tk.Label(adduserTop, text='添加日期') label4.place(x=650, y=50) text4 = tk.StringVar() entry4 = tk.Entry(adduserTop, width=18, textvariable=text4) entry4.place(x=600, y=100) confirmBtn = tk.Button(adduserTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(adduserTop, text='取消') cancelBtn.place(x=350, y=250) ######################################### def confirmFunc(): #loanID = entry1.get().strip() accountID = data[0] clientID = entry2.get().strip() staffID = entry3.get().strip() addDate = entry4.get().strip() sql1 = "insert into bank.负责(`员工身份证号`,`客户身份证号`,`负责人类型`) values(%s,%s,%s);" sql2 = "insert into bank.拥有(`账户号`,`客户身份证号`,`最近访问日期`) values(%s,%s,%s);" try: connAccount.execSQL( sql1, (staffID, clientID, "银行账户负责人_" + accountID)) connAccount.execCommit(sql2, (accountID, clientID, addDate)) closeWindow() except Exception as e: tk.messagebox.showerror("警告", "添加失败!") print("Fail", e) def closeWindow(): adduserTop.destroy() text1.set(data[0]) confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) adduserTop.mainloop() def saveAndget(): item = tree.selection()[0] data = tree.item(item, "values") sgTop = tk.Toplevel(accountTop, width=710, height=300) sgTop.resizable(False, False) sgTop.overrideredirect(1) label1 = tk.Label(sgTop, text='账户号') label1.place(x=100, y=50) text1 = tk.StringVar() entry1 = tk.Entry(sgTop, width=30, textvariable=text1) entry1.place(x=60, y=100) label2 = tk.Label(sgTop, text='余额') label2.place(x=350, y=50) text2 = tk.StringVar() entry2 = tk.Entry(sgTop, width=18, textvariable=text2) entry2.place(x=300, y=100) label3 = tk.Label(sgTop, text='存取款数') label3.place(x=500, y=50) text3 = tk.StringVar() entry3 = tk.Entry(sgTop, width=18, textvariable=text3) entry3.place(x=450, y=100) confirmBtn = tk.Button(sgTop, text='确认') confirmBtn.place(x=250, y=250) cancelBtn = tk.Button(sgTop, text='取消') cancelBtn.place(x=350, y=250) def confirmFunc(): total = 0.0 flag = False try: total = float(data[2]) + float(data[8]) flag = True except: total = float(data[2]) want = float(entry3.get().strip()) if (want > total): tk.messagebox.showerror("警告", "余额不足!") return sql1 = "update bank.储蓄账户 set `余额`=`余额`+ %s where `账户号`=%s;" sql2 = "update bank.支票账户 set `余额`=`余额`+ %s where `账户号`=%s;" sql3 = "update bank.账户 set `余额`=`余额`+ %s where `账户号`=%s;" sql4 = "update bank.支行 set `资产`=`资产`+ %s where `支行名`=%s;" print(sql1 % (want, data[0])) print(sql2 % (want, data[0])) print(sql3 % (want, data[0])) print(sql4 % (want, data[4])) try: connAccount.execSQL(sql1, (want, data[0])) connAccount.execSQL(sql2, (want, data[0])) connAccount.execSQL(sql3, (want, data[0])) connAccount.execCommit(sql4, (want, data[4])) closeWindow() except Exception as e: tk.messagebox.showerror("警告", "存取款失败!") print("Fail", e) def closeWindow(): sgTop.destroy() text1.set(data[0]) text2.set(data[2] + ' & ' + data[8]) confirmBtn.config(command=confirmFunc) cancelBtn.config(command=closeWindow) sgTop.mainloop() rightMenu = tk.Menu(accountTop) rightMenu.add_command(label='编辑', command=editData) rightMenu.add_command(label='删除', command=removeData) rightMenu.add_command(label='添加客户', command=addUser) rightMenu.add_command(label='存取款', command=saveAndget) def popupmenu(event): try: rightMenu.post(event.x_root, event.y_root) except: pass def closePop(*args): rightMenu.unpost() connAccount = dbop.mysqlConn() conditionList.bind('<Button-3>', removeCondition) tree.bind('<Button-3>', popupmenu) tree.bind('<Button-1>', closePop) conditionList.bind('<Double-Button-1>', removeCondition) cacheBtn.config(command=saveCondition) addDataBtn.config(command=addData) searchBtn.config(command=seachData) accountTop.mainloop()