记账系统

1.python代码

import pymysql
import mysql_function
import easygui
class billing_system1():

    def __init__(self,mysql_function):
        self.conn = pymysql.connect(host="localhost",user="root",password="1451964253",database="billing_system")
        self.cursor = self.conn.cursor()
        self.print_condition = {"1":self.query,"2":self.insert,"3":self.pay_back,"4":self.user_insert,"5":self.itme_insert}
        self.title = mysql_function.title
        self.window_name = mysql_function.window_name
        self.mysql = mysql_function.MYSQL
    
    def user_insert(self):
        while True:
            name = easygui.multenterbox(self.window_name[0],self.window_name[1],self.title[0:2])
            if None == name:return
            if bool(name[0]) != False and bool(name[1]) != False:break
            easygui.msgbox("请输入符合的信息！",self.window_name[0],'Yes')
            continue
        self.cursor.execute(self.mysql["USER_QUERY"],name)
        result = self.cursor.fetchone()
        if type(result) == type(None):
            r = self.cursor.execute(self.mysql["JOIN_USER"],name)
            self.conn.commit()
            easygui.msgbox("用户以添加",self.window_name[0],'Yes')  
        else:easygui.msgbox("用户以存在！",self.window_name[0],'Yes')

    def itme_insert(self):
        while True:
            itme = easygui.multenterbox(self.window_name[5],self.window_name[2], self.title[2:5])
            if None == itme:return
            if bool(itme[0]) != False and bool(itme[1]) != False and bool(itme[2]) != False:break
            easygui.msgbox("请输入符合的信息！",self.window_name[0],'Yes')
            continue
            itme [2] = ("%s.0" %itme[2] if itme[2] not in "." else itme[2])
        self.cursor.execute(self.mysql["itme_QUERY"],[itme[0],itme[2]]) 
        result = self.cursor.fetchone()
        if type(result) == type(None):
            self.cursor.execute(self.mysql["itme_insert_sql"],itme)
            self.conn.commit()
            easygui.msgbox("商品以添加",self.window_name[5],'Yes')
        else:easygui.msgbox("商品以存在！",self.window_name[5],'Yes')
                
    def query (self):
        name = easygui.multenterbox(self.window_name[2],self.window_name[1], self.title[0])
        if None == name:return
        self.cursor.execute(self.mysql["QUERY"],name)
        result = self.cursor.fetchall()
        if bool(result) == False :easygui.msgbox("暂未有账目",self.window_name[2],'Yes')
        else:
            list_1 = []
            for i in result:list_1.append("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n" %(str(i[0]),str(i[1]),str(i[2]),str(i[3]),str(i[4]),str(i[5]),str(i[6]),str(i[7]),str(i[8]),str(i[9])))
            easygui.msgbox("".join(list_1),self.window_name[2],'Yes')
            
    def insert(self):
        while True:
            name = student = easygui.multenterbox(self.window_name[3],self.window_name[2],[self.title[0],self.title[2],self.title[5]])
            if None == name:return
            if bool(name[0]) != False and bool(name[1]) != False and bool(name[2])!= False:break
            easygui.msgbox("请输入符合的信息！",self.window_name[0],'Yes')
            continue
        self.cursor.execute(self.mysql["query_user_itme"],[name[0],name[1]])
        result = self.cursor.fetchone()
        if type(result) == type(None):
            easygui.msgbox("没有该商品或用户需要添加！",self.window_name[3],'Yes')
        else:
            self.cursor.execute(self.mysql["JOIN_INTEGRAL"],[name[0],name[1],name[2],name[2],name[1]])
            self.conn.commit()
            easygui.msgbox("账目以记录！",self.window_name[3],'Yes')

    def pay_back (self):
        while True:
            itme_bill = easygui.multenterbox(self.window_name[4],self.window_name[2], [self.title[6],self.title[0],self.title[2]])
            if None == itme_bill:return
            if bool(itme_bill[0]) != False and bool(itme_bill[1]) != False and bool(itme_bill[2]) != False :break
            easygui.msgbox("请输入符合的信息！",self.window_name[0],'Yes')
            continue
        self.cursor.execute(self.mysql["pay_back_query"],itme_bill)
        result = self.cursor.fetchone()
        if type(result) == type(None):easygui.msgbox("没有该账目！",self.window_name[4],'Yes')
        else:
            self.cursor.execute(self.mysql["itme_pay_back"],itme_bill)
            self.conn.commit()
            easygui.msgbox("商品以还款",self.window_name[4],'Yes')

    def choice (self):
        while True:
            command = easygui.buttonbox("查询：1\n计账：2\n还款：3\n用户：4\n商品：5",'记账系统',('1','2','3',"4","5"))
            if command == None:break
            self.print_condition[command]()
        self.cursor.close()   
        self.conn.close()

if __name__ == "__main__":

    obj=billing_system1(mysql_function)
    obj.choice()

2.自定文件 mysql_function

title = [["姓名"],["电话"],["商品"],["单位"],["单价"],["数量"],["id"]]
window_name = ['添加用户：', '记账系统', '查询记账','添加账目','还款系统','添加商品']
MYSQL = {"QUERY":"select id,name,telephone_number,datetime,trade_name,unit,unit_price,number,total_price,pay_back from (select * from accounting_table left join user_table on accounting_table.userid = user_table.user_id where name = %s) as user_accounting left join commodity_list on user_accounting.itmeid = commodity_list.itme_id" 
        ,"query_user_itme":"select * from (SELECT name FROM user_table WHERE NAME = %s GROUP BY name)as a ,(SELECT trade_name FROM commodity_list WHERE trade_name = %s GROUP BY trade_name)as b "
        ,"JOIN_INTEGRAL": "INSERT INTO accounting_table ( userid, itmeid, datetime, number, total_price,pay_back )VALUES((SELECT user_id FROM user_table WHERE NAME = %s ),(SELECT itme_id FROM commodity_list WHERE trade_name = %s ),now( ),%s ,(select unit_price*%s from commodity_list where trade_name = %s),0)"
        ,"USER_QUERY": "select name,telephone_number from user_table where name =%s and telephone_number =%s"
        ,"JOIN_USER": "insert user_table (name,telephone_number) values (%s,%s)"
        ,"itme_QUERY":"select trade_name,unit_price from commodity_list where trade_name = %s and unit_price = %s"
        ,"itme_insert_sql":"insert commodity_list (trade_name,unit,unit_price) values (%s,%s,%s)"
        ,"itme_pay_back":" update accounting_table set pay_back = 1 where id = %s and userid = (select user_id from user_table where name = %s) and itmeid = (select itmeid from commodity_list where trade_name = %s)"
        ,"pay_back_query":"select id,userid,itmeid from accounting_table where  id = %s and userid = (select user_id from user_table where name = %s) and itmeid = (select itme_id from commodity_list where trade_name = %s)"}

3.mysql代码

3.1 用户表

CREATE TABLE `user_table`  (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `telephone_number` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

3.2 商品表

CREATE TABLE `commodity_list`  (
  `itme_id` int(11) NOT NULL AUTO_INCREMENT,
  `trade_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `unit` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `unit_price` decimal(10, 1) NOT NULL,
  PRIMARY KEY (`itme_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

3.3 关系表

CREATE TABLE `accounting_table`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `itmeid` int(11) NOT NULL,
  `datetime` datetime(0) NOT NULL,
  `number` int(10) NOT NULL,
  `total_price` decimal(10, 2) NOT NULL,
  `pay_back` int(1) UNSIGNED ZEROFILL NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_and_bill`(`userid`) USING BTREE,
  INDEX `commodity_and_bill`(`itmeid`) USING BTREE,
  CONSTRAINT `commodity_and_bill` FOREIGN KEY (`itmeid`) REFERENCES `commodity_list` (`itme_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `user_and_bill` FOREIGN KEY (`userid`) REFERENCES `user_table` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;