def purchase(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, m.price, m.curr
FROM WAREINH h, WAREINM 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 = 'CR'
doc.chargedate = chargedate or doc.notedate
warehouse.warein(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)
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 qc():
query = "select * from bi_jxc where notetype='QC' and computed=0"
_cur.execute(query)
rows = _cur.fetchall()
if len(rows)==1:
query = "select uValue from uparameter where uSection = 'options' and uSymbol = 'beginmonth'"
_cur.execute(query)
row = _cur.fetchone()
beginmonth = row[0].strip()
query = "select uValue from uparameter where uSection = 'options' and uSymbol = 'beginyear'"
_cur.execute(query)
row = _cur.fetchone()
beginyear = row[0].strip()
if int(beginmonth) < 10 and beginmonth != '1':
beginmonth = ' ' + str(int(beginmonth) - 1)
elif beginmonth == '1':
beginyear = str(int(beginyear) - 1)
beginmonth = 12
period = beginyear + beginmonth
# begindate = datetime.date(int(beginyear), int(beginmonth),
# calendar.monthrange(int(beginyear), int(beginmonth))[1])
query = u'''
select SUBSTRING(houseno,1,2) as department, houseno, wareno,
sum(amount) as amount, sum(curr)/sum(amount) as price,
sum(curr) as curr, 'QC' as type
from waresum where period = '%(period)s' and amount <> 0 group by wareno, houseno
'''
query = query % {'period':period}
_cur.execute(query)
rows = _cur.fetchall()
for row in rows:
doc = {
'department_id':row['department'],
'warehouse_id':row['houseno'],
'product_id':row['wareno'],
'quantity':row['amount'],
'price':row['price'],
}
warehouse.warein(doc)
analysis.session.commit()
sql ="update bi_jxc set computed=1 where notetype='QC'"
_cur.execute(sql)
_conn.commit()