def stock_change_line(self):
store_filter = self.data_filter.store_filter
start_date = self.request.params.get('start_date')
end_date = self.request.params.get('end_date')
stock_change_type_id = self.request.params.get('stock_change_type_id')
flag = self.request.params.get('flag')
product_id = self.request.params.get('product_id')
store_id = self.request.params.get('store_id')
stores_ = select([stores.c.id]).where(store_filter(stores.c.id, self.user.id))
if store_id:
stores_ = stores_.where(stores.c.id == store_id)
stores_ = stores_.cte('stores_')
a, b = stock_changes, stock_change_lines
fields = [a.c.employee_id, a.c.department_id, b.c.id, a.c.id.label('form_id'), literal('stock_change').label('form_name'), a.c.created_at, a.c.code, a.c.store_id, a.c.memo, b.c.product_id, b.c.quantity, b.c.a_price, b.c.amount, a.c.flag, a.c.stock_change_type_id]
from_obj = a.join(stores_, a.c.store_id == stores_.c.id).join(b, a.c.id == b.c.stock_change_id)
q = select(fields, from_obj=[from_obj]).where(a.c.status == 6)
start_date, end_date = report_default_date_range(start_date, end_date)
q = q.where(and_(a.c.created_at >= start_date, a.c.created_at <= end_date))
if flag is not None:
q = q.where(a.c.flag == flag)
if store_id:
q = q.where(a.c.store_id == store_id)
if stock_change_type_id:
q = q.where(a.c.stock_change_type_id == stock_change_type_id)
if product_id:
q = q.where(b.c.product_id == product_id)
session = self.create_session()
try:
start, limit = self.request.start, self.request.limit
_total_row = None
if start and limit is not None:
u = q.alias('u')
total, _total_row = process_total_row(self.request, session, u, u.c.id)
rows = session.execute(q.offset(start).limit(limit).order_by(a.c.created_at))
else:
rows = session.execute(q).fetchall()
total = len(rows)
results = []
for row in rows:
results.append(ObjectDict(row))
finally:
session.close()
format_ = self.request.params.get('format')
if format_:
return export_xls(self.request, 'stock_change_line.py', results)
return {"total": total, "success": True, "root": results, "total_row": _total_row}
def lend_line(self):
store_filter = self.request.service.data_filter.store_filter
params = self.request.get('params')
department_id = params.get('department_id')
start_date = params.get('start_date')
end_date = params.get('end_date')
product_type_id = params.get('product_type_id')
partner_id = params.get('partner_id') or None
product_type_lcode = None
employee_id = params.get('employee_id') or None
brand_id = params.get('brand_id') or None
product_id = params.get('product_id') or None
area_id = params.get('area_id') or None
format = params.get('format') or None
area_lcode = None
brand_lcode = None
code = params.get('code')
store_id = params.get('store_id')
form_name = params.get('form_name')
show_zero = to_boolean(self.request.params.get('show_zero'))
lcode = None
session = self.create_session()
try:
# 货品类型
stores_ = select([stores.c.id]).where(store_filter(stores.c.id, self.request.user.id))
if store_id:
stores_ = stores_.where(stores.c.id == store_id)
stores_ = stores_.cte('stores_')
if product_type_id:
product_type_lcode = session.query(ProductType.lcode).filter_by(id=product_type_id).scalar()
if department_id:
lcode = session.query(Department.lcode).filter_by(id=department_id).scalar()
if area_id:
area_lcode = session.query(Area.lcode).filter_by(id=area_id).scalar()
if brand_id:
brand_lcode = session.query(Brand.lcode).filter_by(id=brand_id).scalar()
def apply_filter(t, line_t, q):
if product_type_lcode:
q = q.where(product_types.c.lcode.startswith(product_type_lcode))
if lcode:
q = q.where(departments.c.lcode.startswith(lcode))
if area_lcode:
q = q.where(areas.c.lcode.startswith(area_lcode))
if brand_lcode:
q = q.where(brands.c.lcode.startswith(brand_lcode))
if start_date:
q = q.where(t.c.created_at >= start_date)
if end_date:
q = q.where(t.c.created_at <= end_date)
if partner_id:
q = q.where(t.c.partner_id == partner_id)
if employee_id:
q = q.where(t.c.employee_id == employee_id)
if product_id:
q = q.where(line_t.c.product_id == product_id)
if code:
q = q.where(t.c.code == code)
if store_id:
q = q.where(t.c.store_id == store_id)
return q
def _create_q(h, l, join_clause):
if h == lends:
cols = [literal('lend').label('form_name'), l.c.quantity, (l.c.quantity * l.c.a_price).label('amount'), l.c.return_quantity, (l.c.return_quantity * l.c.a_price).label('return_amount'), (l.c.quantity - l.c.return_quantity).label('un_return_quantity'), ((l.c.quantity - l.c.return_quantity) * l.c.a_price).label('un_return_amount'), h.c.return_at]
else:
cols = [literal('lend_return').label('form_name'), null().label('quantity'), null().label('amount'), l.c.quantity.label('return_quantity'), (l.c.quantity * l.c.a_price).label('return_amount'), null().label('un_return_quantity'), null().label('un_return_amount'), null().label('return_at')]
fields = [h.c.memo, l.c.a_price, l.c.id.label('form_line_id'), h.c.id.label('form_id'), h.c.code.label('form_code'), h.c.created_at, h.c.partner_id, h.c.store_id, h.c.employee_id, h.c.department_id, l.c.product_id] + cols
q = select(fields, from_obj=[l.join(h, join_clause).join(stores_, h.c.store_id == stores_.c.id).join(products, l.c.product_id == products.c.id).join(
product_types, products.c.product_type_id == product_types.c.id).join(partners, h.c.partner_id == partners.c.id).join(areas, partners.c.area_id == areas.c.id).join(departments, h.c.department_id == departments.c.id).join(brands, products.c.brand_id == brands.c.id)]).where(h.c.status == 6)
if h == lends:
if not show_zero:
q = q.where((l.c.quantity - l.c.return_quantity) > 0)
return apply_filter(h, l, q)
_all = []
if not form_name:
_all.append(_create_q(lends, lend_lines, lends.c.id == lend_lines.c.lend_id))
_all.append(_create_q(lend_returns, lend_return_lines, lend_returns.c.id == lend_return_lines.c.lend_return_id))
u = union_all(*_all).alias('u')
else:
if form_name == 'lend':
u = _create_q(lends, lend_lines, lends.c.id == lend_lines.c.lend_id).alias('u')
elif form_name == 'lend_return':
u = _create_q(lend_returns, lend_return_lines, lend_returns.c.id == lend_return_lines.c.lend_return_id).alias('u')
else:
raise Exception(u'不支持的表单名 %s' % form_name)
start = self.request.start
limit = self.request.limit
_total_row_data = None
if start is not None and limit:
total, _total_row_data = process_total_row(self.request, session, u, u.c.form_line_id)
_results = session.execute(select([u]).offset(start).limit(limit).order_by(u.c.created_at, u.c.form_line_id)).fetchall()
else:
q = select([u]).order_by(u.c.created_at, u.c.form_line_id)
_results = session.execute(q).fetchall()
total = len(_results)
finally:
session.close()
results = []
for item in _results:
rec = ObjectDict(item)
if not rec.return_at:
rec.over_due = False
else:
if rec.return_at < datetime.now() and rec.un_return_quantity:
rec.over_due = True
else:
rec.over_due = False
results.append(rec)
if format == 'excel':
return export_xls(self.request, 'lend_line.py', results)
return {"total": total, "total_row": _total_row_data, "root": results}