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 stock_change_type(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.stock_change_type_id, a.c.flag, func.sum(b.c.quantity).label('quantity'), func.sum(b.c.amount).label('amount')]
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)
q = q.group_by(a.c.stock_change_type_id, a.c.flag)
session = self.create_session()
try:
rows = session.execute(q).fetchall()
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_type.py', results)
return results
