def stockMove(noteno, chargedate=None, check=False):
sql = '''
select h.noteno, h.notedate, h.oldhouseno, h.newhouseno,
m.wareno, m.amount, substring(h.oldhouseno,1,2) as olddept,
substring(h.newhouseno,1,2) as newdept, m.id
from warealloth h, wareallotm m
where h.noteno=m.noteno and h.noteno='%s'
''' % noteno
_cur.execute(sql)
rows = _cur.fetchall()
if _cur.rowcount==0:raise Exception
for row in rows:
# 移库单明细有可能数量为零，跳过
if row[5] == 0:
continue
old = row[2].strip()
new = row[3].strip()
if old in ('FX010101','FX01010103','FX01010104') and\
new in ('FX010101','FX01010103','FX01010104'):
continue
if old in ('FX01010103','FX01010104'):
old = 'FX010101'
if new in ('FX01010103','FX01010104'):
new = 'FX010101'
doc_yc = note()
doc_yc.noteno = row[0]
doc_yc.notedate = row[1]
doc_yc.department_id = row[6].strip()
doc_yc.warehouse_id = old
doc_yc.product_id = row[4].strip()
doc_yc.quantity = row[5]
doc_yc.notetype = 'YC'
doc_yc.chargedate = chargedate or doc_yc.notedate
s = warehouse.getStock(doc_yc.key)
if not s:
raise Exception(u'没有库存。%s,%s,%s' % (doc_yc.noteno, doc_yc.product_id,doc_yc.warehouse_id))
doc_yc.price = s.price
doc_yc.amount = doc_yc.quantity * doc_yc.price
warehouse.wareout_check(doc_yc)
doc_yr = note()
doc_yr.noteno = row[0]
doc_yr.notedate = row[1]
doc_yr.department_id = row[7].strip()
doc_yr.warehouse_id = new
doc_yr.product_id = row[4].strip()
doc_yr.quantity = row[5]
doc_yr.notetype = 'YR'
doc_yr.chargedate = chargedate or doc_yr.notedate
doc_yr.price = s.price
doc_yr.amount = doc_yr.quantity * doc_yr.price
warehouse.warein_check(doc_yr)
if not check:
warehouse.wareout(doc_yc)
warehouse.warein(doc_yr)
def sales(noteno, chargedate=None):
# ltrim(h.noteno) 自编号的单据号可能被人为在前加入空格，noteno已经去掉前后空格，导致查询为空
sql = u'''
SELECT h.notedate, RTRIM(h.noteno) as noteno, SUBSTRING(h.houseno,1,2) as department,
h.custno as partnerno, case when h.houseno in ('FX010101','FX01010103','FX01010104')
then 'FX010101' else h.houseno end as houseno,e.Name as saleman, m.wareno,
m.amount, m.price, m.curr
FROM WAREOUTM m, WAREOUTH h, EMPLOYE e
WHERE rtrim(ltrim(h.noteno))='%s' AND m.noteno=h.noteno and h.saleman=e.code
'''
sql = sql % noteno
_cur.execute(sql)
rows = _cur.fetchall()
docs = []
try:
for r in rows:
doc = note()
doc.notedate = r[0]
doc.noteno = r[1].strip()
doc.department_id = r[2]
doc.partner_id = r[3].strip()
doc.warehouse_id = r[4].strip()
doc.sales = r[5] and r[5].strip()
doc.product_id = r[6] and r[6].strip()
doc.quantity = r[7]
doc.price = r[8]
doc.amount = r[9]
doc.notetype = 'XS'
doc.chargedate = chargedate or doc.notedate
warehouse.wareout_check(doc)
docs.append(doc)
for doc in docs:
warehouse.wareout(doc)
except Exception, e:
raise Exception(u'记帐失败：%s;%s' % (noteno, e.message))
def returnOfPurchase(noteno, chargedate=None):
sql = '''
SELECT h.notedate, h.noteno, SUBSTRING(h.houseno,1,2) as department, h.provno as partnerno,
case when h.houseno in ('FX010101','FX01010103','FX01010104')
then 'FX010101' else h.houseno end as houseno, NULL as saleman, m.wareno,
m.amount as amount,
m.price as price, m.curr as curr
FROM REFUNDINH h, REFUNDINM m
WHERE h.noteno=m.noteno and h.noteno = '%s'
'''
sql = sql % noteno
_cur.execute(sql)
rows = _cur.fetchall()
for r in rows:
doc = note()
doc.notedate = r[0]
doc.noteno = r[1].strip()
doc.department_id = r[2]
doc.partner_id = r[3].strip()
doc.warehouse_id = r[4].strip()
doc.sales = r[5] and r[5].strip()
doc.product_id = r[6] and r[6].strip()
doc.quantity = r[7]
doc.price = r[8]
doc.amount = r[9]
doc.notetype = 'CT'
doc.chargedate = chargedate or doc.notedate
warehouse.wareout(doc)
def returnOfSales(noteno, chargedate=None):
sql = '''
SELECT h.notedate, m.noteno, SUBSTRING(houseno,1,2) as department, h.custno as partnerno,
case when h.houseno in ('FX010101','FX01010103','FX01010104')
then 'FX010101' else h.houseno end as houseno,
e.Name as saleman, m.wareno, m.amount as amount, m.price as price, m.curr as curr
FROM REFUNDOUTM m, REFUNDOUTH h, EMPLOYE e
where h.noteno = m.noteno and h.saleman=e.code and h.noteno = '%s'
'''
sql = sql % noteno
_cur.execute(sql)
rows = _cur.fetchall()
for r in rows:
doc = note()
doc.notedate = r[0]
doc.noteno = r[1].strip()
doc.department_id = r[2]
doc.partner_id = r[3].strip()
doc.warehouse_id = r[4].strip()
doc.sales = r[5] and r[5].strip()
doc.product_id = r[6] and r[6].strip()
doc.quantity = r[7]
doc.price = r[8]
doc.amount = r[9]
doc.notetype = 'XT'
doc.chargedate = chargedate or doc.notedate
warehouse.warein(doc)