def lend_common(self, created_a, parent_line_type, child_line_type):
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
show_zero = to_boolean(params.get('show_zero'))
store_id = params.get('store_id')
area_lcode = None
brand_lcode = None
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()
a, lcode_col = created_a(area_lcode, product_type_lcode)
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 store_id:
q = q.where(t.c.store_id == store_id)
return q
def _create_over_due(h, l, join_clause):
if parent_line_type == "product_type":
cols = [product_types.c.id, l.c.product_id, lcode_col.label('lcode')]
elif parent_line_type == "area":
cols = [areas.c.id, h.c.partner_id, lcode_col.label('lcode')]
elif parent_line_type == "employee":
cols = [h.c.employee_id]
fields = cols + [l.c.id.label('line_id'),
(l.c.quantity - l.c.return_quantity).label('over_due_quantity'),
((l.c.quantity - l.c.return_quantity) * l.c.a_price).label('over_due_amount')]
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).where((l.c.quantity - l.c.return_quantity) > 0).where(h.c.return_at < datetime.now())
return apply_filter(h, l, q)
def _create_q(h, l, join_clause):
quantity = l.c.quantity
if parent_line_type == "product_type":
cols = [product_types.c.id, l.c.product_id, lcode_col.label('lcode')]
elif parent_line_type == "area":
cols = [areas.c.id, h.c.partner_id, lcode_col.label('lcode')]
elif parent_line_type == "employee":
cols = [h.c.employee_id]
fields = cols + [l.c.id.label('line_id'), quantity,
l.c.amount,
l.c.return_quantity,
h.c.return_at,
(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')]
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 not show_zero:
q = q.where((l.c.quantity - l.c.return_quantity) > 0)
return apply_filter(h, l, q)
d = _create_q(lends, lend_lines, lends.c.id == lend_lines.c.lend_id).alias('d')
d2 = _create_over_due(lends, lend_lines, lends.c.id == lend_lines.c.lend_id).alias('d2')
fields = [a.c.id, a.c.name, func.sum(d.c.quantity).label('quantity'), func.sum(d.c.amount).label('amount'), func.sum(d.c.return_quantity).label('return_quantity'), func.sum(d.c.return_amount).label('return_amount'), func.sum(d.c.un_return_quantity).label('un_return_quantity'), func.sum(d.c.un_return_amount).label('un_return_amount'), func.sum(d2.c.over_due_quantity).label('over_due_quantity'), func.sum(d2.c.over_due_amount).label('over_due_amount')]
if lcode_col is not None:
q = select(fields, from_obj=[a.join(d, d.c.lcode == a.c.lcode).outerjoin(d2, d.c.line_id == d2.c.line_id)]).group_by(a.c.id, a.c.name).order_by(a.c.name)
else:
q = select(fields, from_obj=[a.join(d, d.c.employee_id == a.c.id).outerjoin(d2, d2.c.line_id == d.c.line_id)]).group_by(a.c.id, a.c.name).order_by(a.c.name)
_results = session.execute(q).fetchall()
results = []
_results2 = None # 该货品类型下属产品
fields = [func.sum(d.c.quantity).label('quantity'), func.sum(d.c.amount).label('amount'), func.sum(d.c.return_quantity).label('return_quantity'), func.sum(d.c.return_amount).label('return_amount'), func.sum(d.c.un_return_quantity).label('un_return_quantity'), func.sum(d.c.un_return_amount).label('un_return_amount'), func.sum(d2.c.over_due_quantity).label('over_due_quantity'), func.sum(d2.c.over_due_amount).label('over_due_amount')]
if child_line_type == "product":
if product_type_id:
fields = [products.c.id, products.c.name] + fields
q = select(fields, from_obj=[products.join(d, products.c.id == d.c.product_id).outerjoin(d2, d.c.line_id == d2.c.line_id)]).group_by(products.c.id, products.c.name).where(products.c.product_type_id == product_type_id).order_by(products.c.name)
_results2 = session.execute(q).fetchall()
elif child_line_type == "partner":
if area_id:
fields = [partners.c.id, partners.c.name] + fields
q = select(fields, from_obj=[partners.join(d, partners.c.id == d.c.partner_id).outerjoin(d2, d.c.line_id == d2.c.line_id)]).group_by(partners.c.id, partners.c.name).where(partners.c.area_id == area_id).order_by(partners.c.name)
_results2 = session.execute(q).fetchall()
for item in _results:
rec = ObjectDict(line_id=item.id,
line_type=parent_line_type,
name=item.name,
amount=item.amount,
quantity=item.quantity,
return_quantity=item.return_quantity,
return_amount=item.return_amount,
un_return_quantity=item.un_return_quantity,
un_return_amount=item.un_return_amount,
over_due_quantity=item.over_due_quantity,
over_due_amount=item.over_due_amount)
results.append(rec)
if _results2:
for item in _results2:
rec = ObjectDict(line_id=item.id,
line_type=child_line_type,
name=item.name,
amount=item.amount,
quantity=item.quantity,
return_quantity=item.return_quantity,
return_amount=item.return_amount,
un_return_quantity=item.un_return_quantity,
un_return_amount=item.un_return_amount,
over_due_quantity=item.over_due_quantity,
over_due_amount=item.over_due_amount)
results.append(rec)
if format == 'excel':
return export_xls(self.request, 'lend.py', results)
return results
finally:
session.close()
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}
