ERP简单输入输出系统

# -*- coding: utf-8 -*- 


#!/usr/bin/env python3

#@Author= Wesley Lau

import sqlite3                  #导入sqlite3
import tkinter                  #导入tkinter
from   tkinter  import ttk      #导入内部包
from   tkinter  import *
import datetime 
import time
import math


def click():
	
	#深度优先遍历输入result：3次
	for i in inputorigin:
		if (i[0].get()).strip():
			getfather=cu.execute("SELECT 父物料名称 from MRP where 子物料名称="+"'"+i[0].get()+"'")
			for row in getfather:
				getf=row[0]
			depth_input(i[0].get(),getf,int(i[1].get()),datetime.datetime.strptime(i[2].get(), "%Y-%m-%d") )

	#解决库存:纯数据库解决问题
	stock() 
	showResult()



def depth_input(node,nodefather,num,end):

	#数据库写入当前节点信息
	get=cu.execute("SELECT * from MRP where 父物料名称="+"'"+nodefather+"'"+"and 子物料名称="+"'"+node+"'")
	for row in get:	
		num=math.ceil((num*int(row[3]))/(1-float(row[4])))
		start=end-datetime.timedelta(days=int(row[7]))-\
		datetime.timedelta(days=int(row[8]))-datetime.timedelta(days=int(row[9]))
		if int(row[5])>0 or int(row[6])>0:
			stocknode.append(node)
			stocknum.append(int(row[5])+int(row[6]))
		cu.execute("INSERT INTO result VALUES('%s','%d','%s','%d','%s','%s')"\
		%(row[2],row[10],row[1],num,start.strftime("%Y-%m-%d"),end.strftime("%Y-%m-%d")));
		con.commit()
		
	#寻找子数据
	Son=[]
	component=cu.execute("SELECT 子物料名称 from MRP where 父物料名称="+"'"+node+"'")
	for row in component:
		Son.append(row[0])

	#再找子数据 迭代
	for i in Son:
		depth_input(i,node,num,start)



def stock():

	#先去找日程下达日期，排序，再更新需求数量

	for i in stocknode:
		startday=[]
		k=stocknode.index(i)
		get=cu.execute("SELECT 日程下达日期 from result where 物料名称="+"'"+i+"'")
		for row in get:
			startday.append(row[0])
		startday=sorted(startday)
		
		for n in startday:
			
			get=cu.execute("SELECT 需求数量 from result where 日程下达日期="+"'"+n+"'"+"and 物料名称="+"'"+i+"'")
			for p in get:
				num=p[0]
			
			if num<stocknum[k]:
				cu.execute("UPDATE result set 需求数量=0 where 日程下达日期="+"'"+n+"'"+"and 物料名称="+"'"+i+"'")
				con.commit()
				stocknum[k]=stocknum[k]-num
			else:
				q=num-stocknum[k]
				cu.execute("UPDATE result set 需求数量="+str(q)+" where 日程下达日期="+"'"+n+"'"+"and 物料名称="+"'"+i+"'")
				con.commit()
				stocknum[k]=0

def showResult():
	j=0
	getresult=cu.execute("SELECT * from result ")
	for row in getresult:
		tree.insert('',j,values=(row[0],row[1],row[2],row[3],row[4],row[5]))
		j+=j


if __name__=='__main__':

	#打开数据库
	con = sqlite3.connect('DBbase.sqlite3')                                      
	cu  = con.cursor()
	

	#输入窗口tkinter设计
	mrp=tkinter.Tk()                                      
	mrp.title("MRP计算")                                  
	mrp.geometry("650x600")                               

	#Label设计
	tkinter.Label(mrp,text="MRP computing",font=15,height=5).grid(row=0,column=0,columnspan=3)
	tkinter.Label(mrp,text="产品名称(眼镜/镜框)").grid(row=1, column=1) #标签  
	tkinter.Label(mrp,text="数量").grid(row=1, column=2)
	tkinter.Label(mrp,text="完成日期（0000-0-0）").grid(row=1, column=3)

	#输入框设计，获得输入值
	input00=tkinter.Entry(mrp)
	input00.grid(row=2, column=1)                       
	input01=tkinter.Entry(mrp)
	input01.grid(row=2, column=2)
	input02=tkinter.Entry(mrp)
	input02.grid(row=2, column=3)
	input10=tkinter.Entry(mrp)
	input10.grid(row=3, column=1)
	input11=tkinter.Entry(mrp)
	input11.grid(row=3, column=2)
	input12=tkinter.Entry(mrp)
	input12.grid(row=3, column=3)
	input20=tkinter.Entry(mrp)
	input20.grid(row=4, column=1)
	input21=tkinter.Entry(mrp)
	input21.grid(row=4, column=2)
	input22=tkinter.Entry(mrp)
	input22.grid(row=4, column=3)
	inputorigin=[[input00,input01,input02],[input10,input11,input12],[input20,input21,input22]]



	#初始化库存变量
	stocknode=[]
	stocknum=[]
	#按钮设计
	tkinter.Button(mrp,text="提交",command=click,width=12).grid(row=5,column=3)  

	tkinter.Label(mrp,text="MRP Result",font=10,height=3).grid(row=6,column=0,columnspan=3)                               #输出,建立表格
	tree=ttk.Treeview(mrp,show="headings",columns=("调配方式","物料号","物料名称","需求数量","日程下达日期","日程完成日期")) 
	tree.grid(row=7,columnspan=5) 
	tree.column("调配方式",width=100,anchor='center')   																	    #表示列,不显示  
	tree.column("物料号",width=100,anchor='center')
	tree.column("物料名称",width=100,anchor='center') 
	tree.column("需求数量",width=100,anchor='center')
	tree.column("日程下达日期",width=100,anchor='center')
	tree.column("日程完成日期",width=100,anchor='center')
	tree.heading("调配方式",text="调配方式")                                                                                    #显示表头  
	tree.heading("物料号",text="物料号")  
	tree.heading("物料名称",text="物料名称")  
	tree.heading("需求数量",text="需求数量")  
	tree.heading("日程下达日期",text="日程下达日期")  
	tree.heading("日程完成日期",text="日程完成日期")     

	

	vbar = ttk.Scrollbar(mrp,orient=VERTICAL,command=tree.yview)                                                                 #滚动条
	tree.configure(yscrollcommand=vbar.set)
	tree.grid(row=7,columnspan=5,sticky=NSEW)
	vbar.grid(row=7,column=6,sticky=NS)



	#关闭数据库
	# con.close()

	#进入主循环  
	mrp.mainloop()