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