dbtest

# -*- coding:gb2312 -*-

from tkinter import ttk
from tkinter import *
from tkinter import messagebox
import sqlite3
import os

class JXC(object):
    def __init__(self):
        self.db_path = os.getcwd()+'\\jxc.db'
        self.conn = self.get_conn()
        self.buyIndex = 1
        self.sellIndex = 1
        '''初始顶层框架'''
        self.top = Tk()
        self.top.title('简易进销存 1.0')
        self.top.geometry('1366x768')
        self.top.resizable(0, 0)

        """设置主页标签(简易进销存)"""
        self.startLabel = Label(self.top, text='简易进销存 ', font=('Arial', 16, 'bold'))
        self.startLabel.pack(fill=X, expand=0)

        """设置选项Buton """
        self.optFrm = Frame(self.top, height=10)
        self.optFrm.pack(fill=X, expand=0)
        self.introduceBtn = Button(self.optFrm, text='使用说明', width=10, command=self.showStartFrm)
        self.introduceBtn.pack(side=LEFT)
        self.buyBtn = Button(self.optFrm, text='添加进货', width=10, command=self.showBuyFrm)
        self.buyBtn.pack(side=LEFT)
        self.sellBtn = Button(self.optFrm, text='添加出货', width=10, command=self.showSellFrm)
        self.sellBtn.pack(side=LEFT)
        self.SearchBtn = Button(self.optFrm, text='查询页面', width=10, command=self.showSearchFrm)
        self.SearchBtn.pack(side=LEFT)

        """使用说明Frame"""
        self.startFrm = Frame(self.top)
        """添加进货Frame"""
        self.buyFrm = Frame(self.top)
        """添加进货Frame"""
        self.sellFrm = Frame(self.top)
        """查询按钮Frame"""
        self.SearchFrm = Frame(self.top)
    #--------------------------------------------------
    """显示不同页面信息"""
    def showStartFrm(self):
        self.startFrm.pack(fill='both', expand=1)
        self.buyFrm.pack_forget()
        self.sellFrm.pack_forget()
        self.SearchFrm.pack_forget()
    def showBuyFrm(self):
        self.startFrm.pack_forget()
        self.buyFrm.pack(fill='both', expand=1)
        self.sellFrm.pack_forget()
        self.SearchFrm.pack_forget()
    def showSellFrm(self):
        self.startFrm.pack_forget()
        self.buyFrm.pack_forget()
        self.sellFrm.pack(fill='both', expand=1)
        self.SearchFrm.pack_forget()
    def showSearchFrm(self):
        self.startFrm.pack_forget()
        self.buyFrm.pack_forget()
        self.sellFrm.pack_forget()
        self.SearchFrm.pack(fill='both', expand=1)

    #-----------------------------------------------------
    """设置使用说明页面信息及功能"""
    def setStartSubFrm(self):
        illustrationText = """"
                            使用说明

        """
        self.illustrLabel = Label(self.startFrm, text=illustrationText, font=('Arial', 14, 'bold'))
        self.illustrLabel.pack()

    #-----------------------------------------------------
    """设置添加进货页面信息及功能"""
    def setBuySubFrm(self):
        option = ['订单日期', '物品名称', '物品编号', '进货价格', '进货数量', '进货厂商']
        labelText = ['添加新的进货订货单', '已添加的进货订单']
        self.subBuyFrmUp = Frame(self.buyFrm)
        self.subBuyFrmUp.pack(fill=X, expand=0)
        self.subBuyFrmDown = Frame(self.buyFrm)
        self.subBuyFrmDown.pack(fill=X, expand=0)
        self.addBuyInfoLabel = Label(self.subBuyFrmUp, text=labelText[0], font=('Arial', 14, 'bold'), bg='gray')
        self.addBuyInfoLabel.pack(fill=X, expand=0)
        self.subBuyFrmUpOperate = Frame(self.subBuyFrmUp)
        self.subBuyFrmUpOperate.pack()
        self.showBuyInfoLabel = Label(self.subBuyFrmDown, text=labelText[1], font=('Arial', 14, 'bold'), bg='gray')
        self.showBuyInfoLabel.pack(fill=X, expand=0)
        self.subBuyFrmDownShow = Frame(self.subBuyFrmDown)
        self.subBuyFrmDownShow.pack()
        """添加进货页面的标签"""
        self.dateBuyLabel = Label(self.subBuyFrmUpOperate, text=option[0], font=('Arial', 12), width=15)
        self.dateBuyLabel.grid(row=0, column=0, columnspan=15)
        self.itemBuyLabel = Label(self.subBuyFrmUpOperate, text=option[1], font=('Arial', 12), width=15)
        self.itemBuyLabel.grid(row=1, column=0, columnspan=15)
        self.identBuyLabel = Label(self.subBuyFrmUpOperate, text=option[2], font=('Arial', 12), width=15)
        self.identBuyLabel.grid(row=2, column=0, columnspan=15)
        self.priBuyLabel = Label(self.subBuyFrmUpOperate, text=option[3], font=('Arial', 12), width=15)
        self.priBuyLabel.grid(row=3, column=0, columnspan=15)
        self.numBuyLabel = Label(self.subBuyFrmUpOperate, text=option[4], font=('Arial', 12), width=15)
        self.numBuyLabel.grid(row=4, column=0, columnspan=15)
        self.whereBuyLabel = Label(self.subBuyFrmUpOperate, text=option[5], font=('Arial', 12), width=15)
        self.whereBuyLabel.grid(row=5, column=0, columnspan=15)
        """添加进货页面的输入框"""
        self.dateBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[0], font=('Arial', 12), width=25)
        self.dateBuyEntry.grid(row=0, column=15, columnspan=15)
        self.itemBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[1], font=('Arial', 12), width=25)
        self.itemBuyEntry.grid(row=1, column=15, columnspan=15)
        self.identBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[2], font=('Arial', 12), width=25)
        self.identBuyEntry.grid(row=2, column=15, columnspan=15)
        self.priBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[3], font=('Arial', 12), width=25)
        self.priBuyEntry.grid(row=3, column=15, columnspan=15)
        self.numBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[4], font=('Arial', 12), width=25)
        self.numBuyEntry.grid(row=4, column=15, columnspan=15)
        self.whereBuyEntry = Entry(self.subBuyFrmUpOperate, text=option[5], font=('Arial', 12), width=25)
        self.whereBuyEntry.grid(row=5, column=15, columnspan=15)
        """添加进货页面的清楚和保存按钮"""
        self.clcBuyBtn = Button(self.subBuyFrmUpOperate, text='清除', activebackground='gray', command=self.clearBuyEntry)
        self.clcBuyBtn.grid(row=7, column=15, columnspan=5)
        self.saveBuyBtn = Button(self.subBuyFrmUpOperate, text='保存', activebackground='gray', command=self.showBuyInfo)
        self.saveBuyBtn.grid(row=7, column=26, columnspan=5)
        """显示新添加的内容"""
        self.treeBuy = ttk.Treeview(self.subBuyFrmDownShow, height=20)
        self.treeBuy['column'] = tuple(option)
        self.treeBuy.heading("#0", text='订单序号')
        self.treeBuy.column("#0", minwidth=0, width=140)
        for item in option:
            self.treeBuy.column(item, minwidth=0, width=140)
            self.treeBuy.heading(item, text=item)
        self.treeBuy.pack()
    def getBuyInput(self):
        newInput = []
        newInput.append(self.dateBuyEntry.get())
        newInput.append(self.itemBuyEntry.get())
        newInput.append(self.identBuyEntry.get())
        newInput.append(self.priBuyEntry.get())
        newInput.append(self.numBuyEntry.get())
        newInput.append(self.whereBuyEntry.get())
        for i in range(len(newInput)):
            if newInput[i] == '':
                newInput[i] = None
        return tuple(newInput)
    def clearBuyEntry(self):
        self.itemBuyEntry.delete(0, END)
        self.identBuyEntry.delete(0, END)
        self.priBuyEntry.delete(0, END)
        self.numBuyEntry.delete(0, END)
        self.whereBuyEntry.delete(0, END)
        self.dateBuyEntry.delete(0, END)
    def showBuyInfo(self):
        val = messagebox.askyesno('警告', '保存该订单信息?')
        if val:
            messagebox.showinfo('提示', '订单信息保存成功!')
            order = self.getBuyInput()
            self.clearBuyEntry()
            self.updateTree(self.treeBuy, order)
            self.save_data(self.conn, order, True)

    #-----------------------------------------------------
    """设置添加出货页面信息及功能"""
    def setSellSubFrm(self):
        option = ['订单日期', '物品名称', '物品编号', '销售价格', '销售数量', '客户信息']
        labelText = ['添加新的销售订货单', '已添加的销售订单']
        self.subSellFrmUp = Frame(self.sellFrm)
        self.subSellFrmUp.pack(fill=X, expand=0)
        self.subSellFrmDown = Frame(self.sellFrm)
        self.subSellFrmDown.pack(fill=X, expand=0)
        self.addSellInfoLabel = Label(self.subSellFrmUp, text=labelText[0], font=('Arial', 14, 'bold'), bg='gray')
        self.addSellInfoLabel.pack(fill=X, expand=0)
        self.subSellFrmUpOperate = Frame(self.subSellFrmUp)
        self.subSellFrmUpOperate.pack()
        self.showSellInfoLabel = Label(self.subSellFrmDown, text=labelText[1], font=('Arial', 14, 'bold'), bg='gray')
        self.showSellInfoLabel.pack(fill=X, expand=0)
        self.subSellFrmDownShow = Frame(self.subSellFrmDown)
        self.subSellFrmDownShow.pack()
        """添加出货页面的标签"""
        self.dateSellLabel = Label(self.subSellFrmUpOperate, text=option[0], font=('Arial', 12), width=15)
        self.dateSellLabel.grid(row=0, column=0, columnspan=15)
        self.itemSellLabel = Label(self.subSellFrmUpOperate, text=option[1], font=('Arial', 12), width=15)
        self.itemSellLabel.grid(row=1, column=0, columnspan=15)
        self.identSellLabel = Label(self.subSellFrmUpOperate, text=option[2], font=('Arial', 12), width=15)
        self.identSellLabel.grid(row=2, column=0, columnspan=15)
        self.priSellLabel = Label(self.subSellFrmUpOperate, text=option[3], font=('Arial', 12), width=15)
        self.priSellLabel.grid(row=3, column=0, columnspan=15)
        self.numSellLabel = Label(self.subSellFrmUpOperate, text=option[4], font=('Arial', 12), width=15)
        self.numSellLabel.grid(row=4, column=0, columnspan=15)
        self.whereSellLabel = Label(self.subSellFrmUpOperate, text=option[5], font=('Arial', 12), width=15)
        self.whereSellLabel.grid(row=5, column=0, columnspan=15)
        """添加出货页面的输入框"""
        self.dateSellEntry = Entry(self.subSellFrmUpOperate, text=option[0], font=('Arial', 12), width=25)
        self.dateSellEntry.grid(row=0, column=15, columnspan=15)
        self.itemSellEntry = Entry(self.subSellFrmUpOperate, text=option[1], font=('Arial', 12), width=25)
        self.itemSellEntry.grid(row=1, column=15, columnspan=15)
        self.identSellEntry = Entry(self.subSellFrmUpOperate, text=option[2], font=('Arial', 12), width=25)
        self.identSellEntry.grid(row=2, column=15, columnspan=15)
        self.priSellEntry = Entry(self.subSellFrmUpOperate, text=option[3], font=('Arial', 12), width=25)
        self.priSellEntry.grid(row=3, column=15, columnspan=15)
        self.numSellEntry = Entry(self.subSellFrmUpOperate, text=option[4], font=('Arial', 12), width=25)
        self.numSellEntry.grid(row=4, column=15, columnspan=15)
        self.whereSellEntry = Entry(self.subSellFrmUpOperate, text=option[5], font=('Arial', 12), width=25)
        self.whereSellEntry.grid(row=5, column=15, columnspan=15)
        """添加出货页面的清楚和保存按钮"""
        self.clcSellBtn = Button(self.subSellFrmUpOperate, text='清除', activebackground='gray',
                                 command=self.clearSellEntry)
        self.clcSellBtn.grid(row=7, column=15, columnspan=5)
        self.saveSellBtn = Button(self.subSellFrmUpOperate, text='保存', activebackground='gray',
                                  command=self.showSellInfo)
        self.saveSellBtn.grid(row=7, column=26, columnspan=5)
        """显示出货页面新添加的内容"""
        self.treeSell = ttk.Treeview(self.subSellFrmDownShow, height=20)
        self.treeSell['column'] = tuple(option)
        self.treeSell.heading("#0", text='订单序号')
        self.treeSell.column("#0", minwidth=0, width=140)
        for item in option:
            self.treeSell.column(item, minwidth=0, width=140)
            self.treeSell.heading(item, text=item)
        self.treeSell.pack()
    def getSellInput(self):
        newInput = []
        newInput.append(self.dateSellEntry.get())
        newInput.append(self.itemSellEntry.get())
        newInput.append(self.identSellEntry.get())
        newInput.append(self.priSellEntry.get())
        newInput.append(self.numSellEntry.get())
        newInput.append(self.whereSellEntry.get())
        for i in range(len(newInput)):
            if newInput[i] == '':
                newInput[i] = None
        return tuple(newInput)
    def clearSellEntry(self):
        self.itemSellEntry.delete(0, END)
        self.identSellEntry.delete(0, END)
        self.priSellEntry.delete(0, END)
        self.numSellEntry.delete(0, END)
        self.whereSellEntry.delete(0, END)
        self.dateSellEntry.delete(0, END)
    def showSellInfo(self):
        val = messagebox.askyesno('警告', '保存该订单信息?')
        if val:
            messagebox.showinfo('提示', '订单信息保存成功!')
            order = self.getSellInput()
            self.clearSellEntry()
            self.updateTree(self.treeSell, order)
            self.save_data(self.conn,order,False)
    #-----------------------------------------------------
    """设置查询页面信息及功能"""
    def setSearchFrm(self):
        self.SearchUpFrm = Frame(self.SearchFrm)
        self.SearchUpFrm.pack(fill=X, expand=0)
        self.SearchDownFrm = Frame(self.SearchFrm)
        self.SearchDownFrm.pack(fill=X, expand=0)
        #-----------------------------------------------------------------------
        self.SearchUpInfoLabel1 = Label(self.SearchUpFrm, text='物品名称',font=('Arial', 14, 'bold'))
        self.SearchUpInfoLabel1.grid(row=0, column=0, columnspan=2)
        self.SearchUpEntry1 = Entry(self.SearchUpFrm)
        self.SearchUpEntry1.grid(row=0, column=2, columnspan=5)

        self.SearchUpInfoLabel2 = Label(self.SearchUpFrm, text='物品编号',font=('Arial', 14, 'bold'))
        self.SearchUpInfoLabel2.grid(row=0, column=8, columnspan=2)
        self.SearchUpEntry2 = Entry(self.SearchUpFrm)
        self.SearchUpEntry2.grid(row=0, column=10, columnspan=5)

        self.SearchUpInfoLabel3 = Label(self.SearchUpFrm, text='查询日期',font=('Arial', 14, 'bold'))
        self.SearchUpInfoLabel3.grid(row=0, column=16, columnspan=2)
        self.SearchUpEntry31 = Entry(self.SearchUpFrm)
        self.SearchUpEntry31.grid(row=0, column=18, columnspan=5)
        self.SearchUpInfoLabel3 = Label(self.SearchUpFrm, text='---', font=('Arial', 14, 'bold'))
        self.SearchUpInfoLabel3.grid(row=0, column=23, columnspan=1)
        self.SearchUpEntry32 = Entry(self.SearchUpFrm)
        self.SearchUpEntry32.grid(row=0, column=24, columnspan=5)

        self.SearchUpInfoLabel4 = Label(self.SearchUpFrm, text='进货厂商',font=('Arial', 14, 'bold'))
        self.SearchUpInfoLabel4.grid(row=0, column=32, columnspan=2)
        self.SearchUpEntry4 = Entry(self.SearchUpFrm)
        self.SearchUpEntry4.grid(row=0, column=34, columnspan=5)

        self.SearchUpInfoLabel5 = Label(self.SearchUpFrm, text='出货厂商',font=('Arial', 14, 'bold'))
        self.SearchUpInfoLabel5.grid(row=0, column=36, columnspan=2)
        self.SearchUpEntry5 = Entry(self.SearchUpFrm)
        self.SearchUpEntry5.grid(row=0, column=38, columnspan=5)

        self.SearchUpInfoBtn1 = Button(self.SearchUpFrm, text='查询流水', activebackground='gray', command=self.search_statement_fun)
        self.SearchUpInfoBtn1.grid(row=2, column=10, columnspan=5)
        self.SearchUpInfoBtn2 = Button(self.SearchUpFrm, text='查询库存', activebackground='gray', command=self.search_stockList_fun)
        self.SearchUpInfoBtn2.grid(row=2, column=25, columnspan=5)

        self.treeSearchStatement = ttk.Treeview(self.SearchDownFrm, height=30)
        optionStatement = ['物品名称', '物品编号', '物品价格', '进货数量', '进货厂商', '销售价格', '销售数量', '客户信息']
        self.treeSearchStatement['column'] = optionStatement
        self.treeSearchStatement.heading("#0", text='日期')
        self.treeSearchStatement.column("#0", minwidth=0, width=96)
        for item in optionStatement:
            self.treeSearchStatement.heading(item, text=item)
            self.treeSearchStatement.column(item, minwidth=0, width=96)

        self.treeSearchStock = ttk.Treeview(self.SearchDownFrm, height=30)
        optionStock = ['物品名称', '物品编号', '库存数量']
        self.treeSearchStock['column'] = optionStock
        self.treeSearchStock.heading("#0", text='日期')
        self.treeSearchStock.column("#0", minwidth=0, width=96)
        for item in optionStock:
            self.treeSearchStock.heading(item, text=item)
            self.treeSearchStock.column(item, minwidth=0, width=96)
    def getSearchInput(self):
        newInput = []
        newInput.append(self.SearchUpEntry1.get())
        newInput.append(self.SearchUpEntry2.get())
        newInput.append(self.SearchUpEntry31.get())
        newInput.append(self.SearchUpEntry32.get())
        newInput.append(self.SearchUpEntry4.get())
        newInput.append(self.SearchUpEntry5.get())
        for i in range(len(newInput)):
            if newInput[i] == '':
                newInput[i] = None
        return tuple(newInput)
    def search_statement_fun(self):
        itemList = self.getSearchInput()
        if itemList.count(None)==5:
            sql = "SELECT * FROM statementList"
        else:
            sql = "SELECT * FROM statementList WHERE "
            newList = []
            if itemList[0] != None:
                sql += '物品名称=? and '
                newList.append(itemList[0])
            if itemList[1] != None:
                sql += '物品编号=? and '
                newList.append(itemList[1])
            if itemList[2] != None:
                sql += '日期>=? and '
                newList.append(itemList[2])
            if itemList[3] != None:
                sql += '日期<=? and '
                newList.append(itemList[3])
            if itemList[4] != None:
                sql += '进货厂商=? and '
                newList.append(itemList[4])
            if itemList[5] != None:
                sql += '出货厂商=? and '
                newList.append(itemList[5])
            sql = sql+'id>=1 '
        cu = get_cursor()
        cu.execute(sql,tuple(newList))
        results = cu.fetchall()
        self.treeSearchStatement.pack()
        self.treeSearchStock.pack_forget()
        self.treeSearchStatement.delete(*self.treeSearchStatement.get_children())
        index = 0
        for row in results:
            index += 1
            self.treeSearchStatement.insert('', index,text=index , values=row)
        cu.close()
    def search_stockList_fun(self):
        sql = "SELECT * FROM stockList"
        cu = get_cursor()
        cu.execute(sql)
        results = cu.fetchall()
        self.treeSearchStatement.pack_forget()
        self.treeSearchStock.pack()
        self.treeSearchStock.delete(*self.treeSearchStock.get_children())
        for row in results:
            self.treeSearchStock.insert('', index, text=index, values=row)
        cu.close()
    def updateTree(self,root,order):
        if root == self.treeBuy:
            root.insert('',self.buyIndex,text=self.buyIndex,values=order)
            self.buyIndex += 1
        elif root == self.treeSell:
            root.insert('',self.sellIndex,text=self.sellIndex,values=order)
            self.buyIndex += 1

    def get_conn(self):
        return sqlite3.connect(self.db_path)
    def get_cursor(self):
        if self.conn is not None:
            return self.conn.cursor()
    def create_table(self):
        cu = self.get_cursor()
        statementSql = """CREATE TABLE IF NOT EXISTS statementList (
            'id' integer PRIMARY KEY AUTOINCREMENT,
            '物品名称' varchar(40) NOT NULL,
            '物品编号' varchar(40) ,
            '日期' varchar(40) ,
            '进货价格' int(40) ,
            '进货数量' int(40) ,
            '进货厂商' varchar(40) ,
            '出货价格' int(40) ,
            '出货数量' int(40) ,
            '出货厂商' varchar(40)
            )"""
        cu.execute(statementSql)
        stockSql = """CREATE TABLE IF NOT EXISTS stockList (
            '物品名称' varchar(40) PRIMARY KEY NOT NULL UNIQUE,
            '物品编号' varchar(40) ,
            '库存数量' int(40) default 0
            )"""
        cu.execute(stockSql)
        cu.close()
        self.conn.commit()
    def save_data(self,data,INorOutFlag):
        cu = get_cursor()
        if data[0] != None:
            if INorOutFlag:
                sql = """INSERT INTO statementList 
                    (物品名称,物品编号,日期,进货价格,进货数量,进货厂商)
                    VALUES(?,?,?,?,?,?)"""
            else:
                sql = """INSERT INTO statementList 
                    (物品名称,物品编号,日期,出货价格,出货数量,出货厂商)
                    VALUES(?,?,?,?,?,?)"""
            cu.execute(sql, data)
            self.update_stockList(cu, data)
            cu.close()
            self.conn.commit()
    def update_stockList(self, cu, data):
        if data[0] != None:
            try:
                insertSql = "INSERT INTO stockList VALUES (?,?,?)"
                dataUsed = (data[0], data[1], data[4])
                cu.execute(insertSql, dataUsed)
            except sqlite3.IntegrityError:
                updateSql = "UPDATE statementList SET 库存数量=库存数量+? WHERE 物品名称=?"
                dataUsed = (data[4], data[0])
                cu.execute(updateSql, dataUsed)

    def start(self):
        self.setSearchFrm()
        self.setStartSubFrm()
        self.setBuySubFrm()
        self.setSellSubFrm()
        self.showStartFrm()
        self.top.mainloop()

    def main():
        jxc = JXC()
        jxc.start()


if __name__ == '__main__':
    main()

转载于:https://www.cnblogs.com/vkeys/p/8570981.html