예제 #1
0
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}
예제 #2
0
파일: report.py 프로젝트: shunova/lend
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}