-
Notifications
You must be signed in to change notification settings - Fork 0
/
undoandtrans.py
130 lines (109 loc) · 4.99 KB
/
undoandtrans.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
# -*- coding: utf-8 -*-
import function
from database import database
######trans together
def transbanu(banu,quan,oldwana,newwana):
df=gettobetransinfo(banu,oldwana)
nwquan=getnewwanaquan(banu,newwana)
putinstock(df,newwana,quan,nwquan)
#####get appoined banu in appoined wana created a DataFrame style recording.
def gettobetransinfo(banu,wana):
sql="select * from stocktable where banu=%s and wana=%s"
rs=database.query(sql,banu,wana)
if len(rs)>0:
df=function.pd.DataFrame(rs)
else:df=''
return df
#####get appoined banu in new wana created a DataFrame style recording.
def getnewwanaquan(banu,nwana):
sql="select * from stocktable where banu=%s and wana=%s"
rs=database.query(sql,banu,nwana)
if len(rs)>0:
df=function.pd.DataFrame(rs)
nquan=df.quan[0]
else:
nquan=-1
return nquan
#######插入单条库存记录,如果这个批号在这个库存已记录则数量更新##########################################################################################################
def putinstock(df,nwana,nquan,nwquan):###get tobetrans banu info and new wana,new quan,decrease quan in old wana and inser to new wana with new quan.
ewda=function.strtoday()
sqlst="insert into stocktable (mana,banu,exda,past,pagw,quan,wana,ewda,pana) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)"
sqlu="update stocktable set quan=%s,ewda=%s where banu=%s and wana=%s"
if df.quan[0]>nquan:
if nwquan>-1:
squan=float(nwquan)+float(nquan)
database.execute(sqlu,squan,ewda,df.banu[0],nwana)
else:
database.execute(sqlst,df.mana[0],df.banu[0],df.exda[0],df.past[0],df.pagw[0],nquan,nwana,ewda,df.pana[0])
###decreas old wana quan
updateoldwanaquan(df,nquan)
elif df.quan[0]==nquan:
if nwquan>0:
squan=float(nwquan)+float(nquan)
database.execute(sqlu,squan,ewda,df.banu[0],nwana)
else:
database.execute(sqlst,df.mana[0],df.banu[0],df.exda[0],df.past[0],df.past[0],nquan,nwana,ewda,df.pana[0])
##delete old wana recording
deloldwanarecording(df.banu[0],df.wana[0])
else:
pass
########update old wana quan
def updateoldwanaquan(df,nquan):
squan=float(df.quan[0])-float(nquan)
sql="update stocktable set quan=%s where banu=%s and wana=%s"
database.execute(sql,squan,df.banu[0],df.wana[0])
########delete old wana recording
def deloldwanarecording(banu,wana):
sql="delete from stocktable where banu=%s and wana=%s"
database.execute(sql,banu,wana)
##undo delievery from delieverytable to stocktable due to saon.first step,select data from delieverytable due to saon and show on web to be confirm.then,after confirming,write these data to stocktable and delete these data in delieverytable.##
def undo(saon):
sql="select * from delieverytable where saon=%s"
rec=database.query(sql,saon)
if len(rec)>0:
df=function.pd.DataFrame(rec)
return df[['wana','mana','banu','exda','past','quan']]
else:
df=""
return df
#############根据出货文件号删除出货记录
def delsaonfromdelievery(saon):
function.database.execute("delete from delieverytable where saon=%s",saon)
function.database.execute("delete from saoncustomertable where saon=%s",saon)
#################################################################################################################
########将已出货明细还库存表。毛重根据包装规格和批号从进货表中查找。
def undodelievery(saon):
df=undo(saon)
n=0
while n<len(df):
inorupdatestock(df.ix[n])
n=n+1
delsaonfromdelievery(saon)
#######insert or update into stocktable
def inorupdatestock(dfe):
ewda=function.strtoday()
sqli="insert into stocktable (wana,mana,banu,exda,past,quan,pagw,ewda,pana) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)"
sqlu="update stocktable set quan=%s where banu=%s and wana=%s"
sqlq="select * from stocktable where banu=%s and wana=%s"
pagw=findpagw(dfe.banu,dfe.past)
rs=database.query(sqlq,dfe.banu,dfe.wana)
if len(rs)>0:
nwquan=getnewwanaquan(dfe.banu,dfe.wana)
squan=float(nwquan)+float(dfe.quan)
database.execute(sqlu,squan,dfe.banu,dfe.wana)
else:
database.execute(sqli,dfe.wana,dfe.mana,dfe.banu,dfe.exda,dfe.past,dfe.quan,pagw,ewda,"公斤/桶".decode('utf8'))
########毛重根据包装规格和批号从进货表中查找
def findpagw(banu,past):
sql="select * from putinwhtable where banu=%s and past=%s"
rs=database.query(sql,banu,past)
if len(rs)>0:
df=function.pd.DataFrame(rs)
pagw=df.pagw[0]
else:
pagw=-1
return pagw
#############################################################################################
if __name__ == '__main__':
#transbanu('AQH06770GN',21,'北京','广州')
undodelievery('saon')