def create_or_update_inventory_transaction(self): from psi.app.models.inventory_transaction import InventoryTransactionLine, InventoryTransaction from psi.app.models.enum_values import EnumValues if self.type.code == const.DIRECT_SHIPPING_TYPE_KEY: it_type = EnumValues.get(const.SALES_OUT_INV_TRANS_TYPE_KEY) else: it_type = EnumValues.get(const.FRANCHISE_SALES_OUT_INV_TRANS_TYPE_KEY) it = self.inventory_transaction if it is None: it = InventoryTransaction() it.type = it_type self.inventory_transaction = it it.date = self.date it.organization = self.organization for line in self.lines: itl = line.inventory_transaction_line if itl is None: itl = InventoryTransactionLine() itl.quantity = -line.quantity itl.product = line.product itl.price = line.price itl.in_transit_quantity = 0 itl.inventory_transaction = it line.inventory_transaction_line = itl self.update_saleable_qty_in_purchase_inv_lines(line) Info.get_db().session.add(it)
def init_all(app, migrate=True): init_logging(app) # === Important notice to the maintainer === # This line was use to database = init_db(app) # But we found session can not be cleaned among different # Unit tests, so we add this to avoid issue # sqlalchemy-object-already-attached-to-session # http://stackoverflow.com/questions/24291933/sqlalchemy-object-already-attached-to-session # A similar issue was captured on # https://github.com/jarus/flask-testing/issues/32 # Please don't try to modify the follow four lines. # Please don't try to modify the follow four lines. # Please don't try to modify the follow four lines. if Info.get_db() is None: database = init_db(app) else: database = Info.get_db() database.init_app(app) security = init_flask_security(app, database) init_migrate(app, database) if migrate: with app.app_context(): upgrade(directory=MIGRATION_DIR) init_https(app) init_admin_views(app, database) babel = init_babel(app) api = init_flask_restful(app) init_reports(app, api) init_jinja2_functions(app) # init_debug_toolbar(app) init_image_service(app) socket_io = init_socket_io(app) define_route_context(app, database, babel) # define a context processor for merging flask-admin's template context # into the flask-security views. @security.context_processor def security_context_processor(): from flask import url_for return dict( get_url=url_for ) @app.teardown_appcontext def shutdown_session(exception=None): database = Info.get_db() database.session.remove() return socket_io
def test_saleable_qty(self): from psi.app.services.purchase_order import PurchaseOrderService with self.test_client: from tests.fixture import login_as_admin login_as_admin(self.test_client) from psi.app.models import EnumValues db = Info.get_db() po = of.purchase_order(number_of_line=1, type=EnumValues.get( const.DIRECT_PO_TYPE_KEY)) recv = PurchaseOrderService.create_receiving_if_not_exist(po) from psi.app.utils import db_util db_util.save_objects_commit(po, recv) recv.status = EnumValues.get(const.RECEIVING_COMPLETE_STATUS_KEY) inv_trans = recv.operate_inv_trans_by_recv_status() new_po = recv.update_purchase_order_status() db_util.save_objects_commit(new_po, inv_trans) self.assertEquals(inv_trans.lines[0].quantity, po.lines[0].quantity) self.assertEquals(inv_trans.lines[0].saleable_quantity, po.lines[0].quantity) self.assertEquals(inv_trans.lines[0].in_transit_quantity, 0) self.assertEqual(inv_trans.date, recv.date) self.assertEqual(inv_trans.lines[0].product, po.lines[0].product) self.assertEquals(inv_trans.lines[0].quantity, recv.lines[0].quantity) self.assertEquals(inv_trans.lines[0].saleable_quantity, recv.lines[0].quantity) self.assertEquals(inv_trans.lines[0].in_transit_quantity, 0) self.assertEqual(inv_trans.date, recv.date) self.assertEqual(inv_trans.lines[0].product, recv.lines[0].product)
def test_logic(): fixture.login_as_admin(self.test_client) user, password = object_faker.user(role_names=[ 'franchise_sales_order_create', 'franchise_sales_order_view', 'franchise_sales_order_edit', 'product_view' ]) franchise_so_type = EnumValues.get(FRANCHISE_SO_TYPE_KEY) sales_order = object_faker.sales_order(creator=user, number_of_line=1, type=franchise_so_type) db_util.save_objects_commit(sales_order, user) so_id = sales_order.id shipped_status = EnumValues.get(SO_SHIPPED_STATUS_KEY) fixture.login_user(self.test_client, user.email, password) rv = self.test_client.put('/api/sales_order/' + str(so_id), follow_redirects=True, data=dict(status_id=shipped_status.id)) self.assertIn(b'message', rv.data) self.assertIn(b'Status update successfully', rv.data) self.assertEqual(rv.status_code, 200) so_from_db = Info.get_db().session.query(SalesOrder).get(so_id) self.assertIsNotNone(so_from_db) self.assertEquals(SO_SHIPPED_STATUS_KEY, so_from_db.status.code)
def get_total(report_type, period_type, period_number, year): if report_type == 'amount_compare_with_last_period': sql = sqls.GET_AMOUNT_BY_YEAR_SQL.format(period_type, period_number, year) elif report_type == 'profit_compare_with_last_period': sql = sqls.GET_PROFIT_BY_YEAR_SQL.format(period_type, period_number, year) results = Info.get_db().engine.execute(sql).fetchall() return results[0][0]
def put(self, sales_order_id): try: args = parser.parse_args() status_id = args['status_id'] session = Info.get_db().session sales_order = session.query(SalesOrder).get(sales_order_id) status = session.query(EnumValues).get(status_id) if status is not None and status.type.code == SO_STATUS_KEY: if sales_order.status.code == SO_CREATED_STATUS_KEY: if status.code == SO_SHIPPED_STATUS_KEY or status.code == SO_DELIVERED_STATUS_KEY: sales_order.status_id = status_id shipping = SalesOrderService.create_or_update_shipping(sales_order) session.add(sales_order) session.add(shipping) SalesOrderService.update_related_po_status(sales_order, PO_SHIPPED_STATUS_KEY) elif status.code == SO_INVALID_STATUS_KEY: sales_order.status_id = status_id session.add(sales_order) po = SalesOrderService.update_related_po_status(sales_order, PO_REJECTED_STATUS_KEY) recvs = po.po_receivings for recv in recvs: session.delete(recv) session.commit() return dict(message=gettext('Status update successfully'), status='success'), 200 else: return dict(message=gettext('Status update not allowed'), status='error'), 201 else: return dict(message=gettext('Invalid sales order status parameter'), status='error'), 201 except Exception as e: return dict(message=gettext('Failed to change sales order status<br>{0}').format(e.message), status='error'), 201
def test_logic(): fixture.login_as_admin(self.test_client) user, password = object_faker.user(role_names=[ 'franchise_sales_order_create', 'franchise_sales_order_view', 'franchise_sales_order_edit', 'product_view' ]) franchise_so_type = EnumValues.get(FRANCHISE_SO_TYPE_KEY) sales_order = object_faker.sales_order(creator=user, number_of_line=1, type=franchise_so_type) db_util.save_objects_commit(sales_order, user) so_id = sales_order.id shipped_status = EnumValues.get(SO_SHIPPED_STATUS_KEY) fixture.login_user(self.test_client, user.email, password) rv = self.test_client.put('/api/sales_order/' + str(so_id), follow_redirects=True, data=dict(status_id=shipped_status.id)) self.assertIn('message', rv.data) self.assertIn('Status update successfully', rv.data) self.assertEqual(rv.status_code, 200) so_from_db = Info.get_db().session.query(SalesOrder).get(so_id) self.assertIsNotNone(so_from_db) self.assertEquals(SO_SHIPPED_STATUS_KEY, so_from_db.status.code)
def sales_amount_report(r_type, r_period): limit = get_limit(r_period) sql = sqls.SALES_AMOUNT_REPORT_SQL.format('WEEK', 'WW', limit) \ if r_period == 'week' \ else sqls.SALES_AMOUNT_REPORT_SQL.format('MONTH', 'Mon', limit) results = Info.get_db().engine.execute(sql).fetchall() labels, totals = [], [] for r in results: labels.append("{0}, {1}".format(int(r[0]), gettext(r[2]))) totals.append(float(r[3])) avg = str(format_util.format_decimal(sum(totals) / float(len(totals)))) if len(totals) > 0 else 0 labels.reverse() totals.reverse() ct = gettext(r_type.capitalize()) cp = gettext(r_period.capitalize()) label_tot = gettext('Total Sales {0} Per {1}').format(ct, cp) label_avg = gettext('Average Sales {0}(Past {1} {2}(s)): {3}').format(ct, 24, cp, avg) return dict( data={ "labels": labels, "details": { "average_amount": { "label": label_avg, "data": [avg] * len(totals), "style": "average" }, "total": { "label": label_tot, "data": totals, "style": "major" } } }, status='success' )
def save_objects_commit(*objects): """ Save objects and commit to database :param objects: Objects to save """ db = Info.get_db() save_objects(objects) db.session.commit()
def update_related_po_status(sales_order, status_code): purchase_order = SalesOrderService.get_related_po(sales_order) session = Info.get_db().session if purchase_order is not None: status = EnumValues.get(status_code) purchase_order.status = status session.add(purchase_order) return purchase_order
def save_objects(objects): """ Save objects without commit them to database :param objects: objects to save """ db = Info.get_db() for obj in objects: if obj is not None: db.session.add(obj)
def cleanup_database(app_context): with app_context: db = Info.get_db() db.session.remove() db.engine.execute('DROP TABLE alembic_version') db.engine.execute('DROP VIEW sales_order_detail') db.session.commit() db.reflect() db.drop_all()
def filter_by_organization(object_type, user=current_user): """ Filter object by user's organization :param object_type: Object type to filter :param user: User('s Organization) to use for the filter :return: List of object filter by the user's organisation """ db = Info.get_db() return db.session.query(object_type).filter_by(organization_id=user.organization_id).all()
def get_related_po(sales_order): rt = EnumValues.get(const.FRANCHISE_PO_TO_SO_RT_KEY) session = Info.get_db().session related_value, purchase_order = None, None if sales_order.type.code == const.FRANCHISE_SO_TYPE_KEY: related_value = session.query(RelatedValues).filter_by(to_object_id=sales_order.id, relation_type_id=rt.id).first() if related_value is not None: purchase_order = session.query(PurchaseOrder).get(related_value.from_object_id) return purchase_order
def after_model_change(self, form, model, is_created): """ :param form: form object from the UI :param model: model, when on after_model_change, it has got id field and necessary default value from DB. :param is_created: True if model was created, False if model was updated :return: None Update left and right field of all impacted organization vai raw sql, and also update left and right of the newly added organization to it's parent's current right and current right + 1 """ from sqlalchemy import text from psi.app.service import Info db = Info.get_db() str_id = getattr(form, "parent").raw_data[0] int_id = int( str_id) if str_id is not None and str_id != u"__None" and len( str_id) > 0 else None parent = db.session.query(Organization).get( int_id) if int_id is not None else None if is_created: # New create # update all exiting node with right and left bigger than current parent's right - 1 if parent is not None: model.parent = parent elif parent is not None: # Changing parent of a subtree or leaf. # Refer to http://stackoverflow.com/questions/889527/move-node-in-nested-set for detail. lft = model.lft rgt = model.rgt parent_rgt = parent.rgt ts = int(rgt - lft + 1) # step 1: temporary "remove" moving node, change lft and right to negative integer # step 2: decrease left and/or right position values of currently 'lower' items (and parents) # step 3: increase left and/or right position values of future 'lower' items (and parents) # step 4: move node (ant it's subtree) and update it's parent item id c = parent_rgt - ts if parent_rgt > rgt else parent_rgt d = parent_rgt - rgt - 1 if parent_rgt > rgt else parent_rgt - rgt - 1 + ts sql = [ '{u} lft = 0 - lft, rgt = 0 - rgt where lft >= {lft} and rgt <={rgt}' .format(u=self.uos, lft=lft, rgt=rgt), '{u} lft = lft - {ts} where lft > {rgt}'.format(u=self.uos, ts=ts, rgt=rgt), '{u} rgt = rgt - {ts} where rgt > {rgt}'.format(u=self.uos, ts=ts, rgt=rgt), '{u} lft = lft + {ts} where lft >= {c}'.format(ts=ts, c=c, u=self.uos), '{u} rgt = rgt + {ts} where rgt >= {c}'.format(ts=ts, c=c, u=self.uos), '{u} lft = 0-lft+{d}, rgt = 0-rgt + {d} where lft <= 0-{lft} and rgt >= 0-{rgt}' .format(d=d, lft=lft, rgt=rgt, u=self.uos) ] for s in sql: db.engine.execute(text(s)) db.session.commit()
def filter_by_organization(object_type, user=current_user): """ Filter object by user's organization :param object_type: Object type to filter :param user: User('s Organization) to use for the filter :return: List of object filter by the user's organisation """ db = Info.get_db() return db.session.query(object_type).filter_by( organization_id=user.organization_id).all()
def test_purchase_order_hide_then_show_list_page(self): from psi.app.service import Info from psi.app.models.role import Role from psi.app.utils import save_objects_commit role = Info.get_db().session.query(Role).filter_by( name='purchase_price_view').first() user, password = object_faker.user( ['product_view', 'direct_purchase_order_view']) save_objects_commit(user, role) fixture.login_user(self.test_client, user.email, password) rv = self.test_client.get(url_for('dpo.index_view'), follow_redirects=True) self.assertEqual(rv.status_code, 200) self.assertNotIn( b'<th class="column-header col-goods_amount">', rv.data, b"goods amount should not exits in purchase order " b"list page") self.assertNotIn( b'<th class="column-header col-total_amount">', rv.data, b"total amount should not exits in purchase order " b"list page") self.assertNotIn( b'<th class="column-header col-all_expenses">', rv.data, b"all expenses should not exits in purchase order " b"list page") rv = self.test_client.get(url_for('product.index_view'), follow_redirects=True) self.assertNotIn( b'<th class="column-header col-purchase_price">', rv.data, b"purchase price field should not exit in product " b"list page") user.roles.append(role) save_objects_commit(user, role) rv = self.test_client.get(url_for('dpo.index_view'), follow_redirects=True) self.assertEqual(rv.status_code, 200) self.assertIn( b'<th class="column-header col-goods_amount">', rv.data, b"goods amount should exist in purchase order list page") self.assertIn( b'<th class="column-header col-total_amount">', rv.data, b"total amount should exist in purchase order list page") self.assertIn( b'<th class="column-header col-all_expenses">', rv.data, b"all expenses should exist in purchase order list page") rv = self.test_client.get(url_for('product.index_view'), follow_redirects=True) self.assertIn( b'<th class="column-header col-purchase_price">', rv.data, b"purchase price field should exits in product list page") fixture.logout_user(self.test_client)
def delete_by_id(obj_type, id_to_del, commit=True): """ Delete model object by value :type obj_type: db.Model :type id_to_del: int """ db = Info.get_db() obj = db.session.query(obj_type).get(id_to_del) db.session.delete(obj) if commit: db.session.commit()
def populate_obj(self, obj, name): from flask import request from psi.app.service import Info from psi.app.utils import db_util from psi.app.utils import file_util images_to_del = request.form.get('images-to-delete') if len(images_to_del) > 0: to_del_ids = images_to_del.split(',') for to_del_id in to_del_ids: db_util.delete_by_id(self.object_type, to_del_id, commit=False) files = request.files.getlist('images_placeholder') images = getattr(obj, name) for f in files: if len(f.filename) > 0: image_owner = self.object_type() image = file_util.save_image(image_owner, f) Info.get_db().session.add(image) Info.get_db().session.add(image_owner) images.append(image_owner) setattr(obj, name, images)
def get_by_name(object_type, val, user=current_user): """ Get the first model object via query condition of name field :param object_type: Object type :param val: value of the name :param user: user context, default to current login user. :return: The object if found, otherwise None """ db = Info.get_db() if hasattr(object_type, 'organization_id'): return db.session.query(object_type).filter_by(name=val, organization_id=user.organization_id).first() return db.session.query(object_type).filter_by(name=val).first()
def get_by_external_id(object_type, external_id, user=current_user): """ Get model object via external_id, a field names "external_id" should exists :param object_type: Object type :param external_id: external id :param user: user context, default to current login user. :return: The object if found, otherwise None """ db = Info.get_db() if hasattr(object_type, 'organization_id'): return db.session.query(object_type).filter_by(external_id=external_id, organization_id=user.organization_id).first() return db.session.query(object_type).filter_by(external_id=external_id).first()
def upgrade(): from psi.app.models import Supplier, Product, Customer from psi.app.service import Info # Follow line is needed to persist all existing migration to DB and avoid # tests to complain supplier, product and customer tables does not exist op.get_bind().execute(text("COMMIT;")) db = Info.get_db() set_mnemonic_for_all(db, fields=['id', 'name'], obj_type="supplier") set_mnemonic_for_all(db, fields=['id', 'name'], obj_type="product") set_mnemonic_for_all(db, fields=['id', 'first_name', 'last_name'], obj_type="customer") db.session.commit()
def get_by_name(object_type, val, user=current_user): """ Get the first model object via query condition of name field :param object_type: Object type :param val: value of the name :param user: user context, default to current login user. :return: The object if found, otherwise None """ db = Info.get_db() if hasattr(object_type, 'organization_id'): return db.session.query(object_type).filter_by( name=val, organization_id=user.organization_id).first() return db.session.query(object_type).filter_by(name=val).first()
def test_logic(): from psi.app.models import Receiving, EnumValues from psi.app.views import ReceivingAdmin from psi.app.service import Info receiving = Receiving() complete_status = EnumValues.get(RECEIVING_COMPLETE_STATUS_KEY) receiving.status = complete_status db_session = Info.get_db().session receiving_admin = ReceivingAdmin(Receiving, db_session, name=lazy_gettext("Receiving"), category=lazy_gettext('Purchase'), menu_icon_type=ICON_TYPE_GLYPH, menu_icon_value='glyphicon-import') self.assertRaises(ValidationError, receiving_admin.on_model_delete, receiving)
def get_result_raw_sql(sql): """ Get first returned value of a raw sql as a tuple :param sql: The sql to execute :return: a tuple contains first result """ res = Info.get_db().engine.execute(sql) results = res.fetchall() result = None for r in results: result = r break return result
def after_model_delete(self, model): """ Adjust left and right value for organizations in DB after deleting the model. :param model: Model to delete :return: None """ from sqlalchemy import text from psi.app.service import Info db = Info.get_db() width = model.rgt - model.lft + 1 sql = text("{u} rgt = rgt-{w} WHERE rgt > {rgt};{u} lft = lft-{w} WHERE lft > {lft}".format(rgt=model.rgt, lft=model.lft, w=width, u=self.uos)) db.engine.execute(sql) db.session.commit()
def get_next_id(obj_type): """ Get next id of a obj_type :param obj_type: Object type :return: current id plus one """ from sqlalchemy import text db = Info.get_db() sql = text("select nextval('{0}_id_seq')".format(obj_type.__tablename__)) result = db.engine.execute(sql) next_val = 1 for row in result: next_val = row[0] return next_val
def id_query_to_obj(obj_type, query): """ Query for objects from a list of id :param obj_type: object type :param query: query to get list of ids :return: list of objects """ db = Info.get_db() raw_result = query.all() ids = [] for r in raw_result: ids.append(getattr(r, 'id')) obj_result = db.session.query(obj_type).filter(obj_type.id.in_(ids)).all() return obj_result
def test_logic(): po = object_faker.purchase_order() po.status = EnumValues.get(const.PO_RECEIVED_STATUS_KEY) receiving = Receiving() receiving.purchase_order = po receiving.date = datetime.now() receiving.status = EnumValues.get(const.RECEIVING_DRAFT_STATUS_KEY) db = Info.get_db() db.session.add(po) db.session.add(receiving) db.session.commit() receiving_returned = receiving.filter_by_po_id(1)[0] self.assertEqual(receiving, receiving_returned)
def after_model_delete(self, model): """ Adjust left and right value for organizations in DB after deleting the model. :param model: Model to delete :return: None """ from sqlalchemy import text from psi.app.service import Info db = Info.get_db() width = model.rgt - model.lft + 1 sql = text( "{u} rgt = rgt-{w} WHERE rgt > {rgt};{u} lft = lft-{w} WHERE lft > {lft}" .format(rgt=model.rgt, lft=model.lft, w=width, u=self.uos)) db.engine.execute(sql) db.session.commit()
def get_by_external_id(object_type, external_id, user=current_user): """ Get model object via external_id, a field names "external_id" should exists :param object_type: Object type :param external_id: external id :param user: user context, default to current login user. :return: The object if found, otherwise None """ db = Info.get_db() if hasattr(object_type, 'organization_id'): return db.session.query(object_type).filter_by( external_id=external_id, organization_id=user.organization_id).first() return db.session.query(object_type).filter_by( external_id=external_id).first()
def update_saleable_qty_in_purchase_inv_lines(self, ship_line): from psi.app.models import InventoryTransactionLine, InventoryInOutLink avail_inv_trans = Info.get_db().session.query(InventoryTransactionLine) \ .filter(InventoryTransactionLine.saleable_quantity > 0, InventoryTransactionLine.product_id == ship_line.product.id) \ .order_by(InventoryTransactionLine.id).all() to_update_purchase_inventory_line, inventory_in_out_links = [],[] for recv_iv_trans in avail_inv_trans: remain_qty = ship_line.quantity if recv_iv_trans.saleable_quantity >= ship_line.quantity: recv_iv_trans.saleable_quantity = recv_iv_trans.saleable_quantity \ - ship_line.quantity remain_qty = 0 else: recv_iv_trans.saleable_quantity = 0 remain_qty = ship_line.quantity \ - recv_iv_trans.saleable_quantity link = InventoryInOutLink() link.date = datetime.now() link.product = ship_line.product link.in_price = recv_iv_trans.price link.in_date = recv_iv_trans.itl_receiving_line.receiving.date link.receiving_line_id = recv_iv_trans.itl_receiving_line.id link.out_price = ship_line.price link.out_date = ship_line.shipping.date link.out_quantity = ship_line.quantity link.shipping_line = ship_line link.organization = ship_line.shipping.organization to_update_purchase_inventory_line.append(recv_iv_trans) inventory_in_out_links.append(link) if remain_qty == 0: break for l in to_update_purchase_inventory_line: Info.get_db().session.add(l) for l in inventory_in_out_links: Info.get_db().session.add(l)
def get_next_code(object_type, user=current_user): """ Get next code of object :param object_type: Type of the model :param user User context, default to current login user. :return: Value of next available code field(current max code plus 1 and format to 6 decimal(with leading zeros) """ db = Info.get_db() if hasattr(object_type, 'organization_id'): obj = db.session.query(object_type).filter_by(organization_id=user.organization_id).order_by(desc(object_type.id)).first() else: obj = db.session.query(object_type).order_by(desc(object_type.id)).first() if obj is None: return '{0:06d}'.format(1) return '{0:06d}'.format(1 + int(obj.code))
def compare_period_on_period(r_type, r_period): sql = sqls.PERIOD_ON_PERIOD_AMOUNT_REPORT_SQL \ if r_type == 'amount_period_on_period' \ else sqls.PERIOD_ON_PERIOD_PROFIT_REPORT_SQL sql = sql.format(r_period.capitalize()) results = Info.get_db().engine.execute(sql).fetchall() current, previous = None, None if len(results) >= 1: current = results[0][2] if len(results) >= 2: previous = results[1][2] change, result_str = cal_percent_and_change_type(current, previous) return dict(data={ "data": result_str, "change": change }, status='success')
def sales_profit_report(r_type, r_period): limit = get_limit(r_period) sql = sqls.SALES_PROFIT_REPORT_SQL.format(r_period, limit) labels, profits, totals = [], [], [] results = Info.get_db().engine.execute(sql).fetchall() for r in results: labels.append("{0}, {1}".format(int(r[0]), gettext(int(r[1])))) totals.append(float(r[2])) profits.append(float(r[3])) length = len(profits) total_avg = str(format_util.format_decimal(sum(totals) / length)) if length > 0 else 0 profit_avg = str(format_util.format_decimal(sum(profits) / length)) if length > 0 else 0 label_avg = gettext('Average Sales {0}(Past {1} {2}(s)): {3}') label_tot = gettext('Total Sales {0} Per {1}') ct = gettext(r_type.capitalize()) cp = gettext(r_period.capitalize()) act = gettext("Amount") return dict( data={ "labels": labels, "details": { "average_profit": { "label": label_avg.format(ct, limit, cp, profit_avg), "data": [profit_avg] * length, "style": "average" }, "average_total": { "label": label_avg.format(act, limit, cp, total_avg), "data": [total_avg] * length, "style": "secondary_average" }, "total": { "label": label_tot.format(act, cp), "data": totals, "style": "secondary" }, "profit": { "label": label_tot.format(ct, cp), "data": profits, "style": "major" } } }, status='success' )
def test_logic(): from psi.app.models import Receiving, EnumValues from psi.app.views import ReceivingAdmin from psi.app.service import Info receiving = Receiving() complete_status = EnumValues.get(RECEIVING_COMPLETE_STATUS_KEY) receiving.status = complete_status db_session = Info.get_db().session receiving_admin = ReceivingAdmin( Receiving, db_session, name=lazy_gettext("Receiving"), category=lazy_gettext('Purchase'), menu_icon_type=ICON_TYPE_GLYPH, menu_icon_value='glyphicon-import') self.assertRaises(ValidationError, receiving_admin.on_model_delete, receiving)
def after_model_change(self, form, model, is_created): """ :param form: form object from the UI :param model: model, when on after_model_change, it has got id field and necessary default value from DB. :param is_created: True if model was created, False if model was updated :return: None Update left and right field of all impacted organization vai raw sql, and also update left and right of the newly added organization to it's parent's current right and current right + 1 """ from sqlalchemy import text from psi.app.service import Info db = Info.get_db() str_id = getattr(form, "parent").raw_data[0] int_id = int(str_id) if str_id is not None and str_id != u"__None" and len(str_id) > 0 else None parent = db.session.query(Organization).get(int_id) if int_id is not None else None if is_created: # New create # update all exiting node with right and left bigger than current parent's right - 1 if parent is not None: model.parent = parent elif parent is not None: # Changing parent of a subtree or leaf. # Refer to http://stackoverflow.com/questions/889527/move-node-in-nested-set for detail. lft = model.lft rgt = model.rgt parent_rgt = parent.rgt ts = int(rgt - lft + 1) # step 1: temporary "remove" moving node, change lft and right to negative integer # step 2: decrease left and/or right position values of currently 'lower' items (and parents) # step 3: increase left and/or right position values of future 'lower' items (and parents) # step 4: move node (ant it's subtree) and update it's parent item id c = parent_rgt - ts if parent_rgt > rgt else parent_rgt d = parent_rgt - rgt - 1 if parent_rgt > rgt else parent_rgt - rgt - 1 + ts sql = ['{u} lft = 0 - lft, rgt = 0 - rgt where lft >= {lft} and rgt <={rgt}'.format(u=self.uos, lft=lft, rgt=rgt), '{u} lft = lft - {ts} where lft > {rgt}'.format(u=self.uos, ts=ts, rgt=rgt), '{u} rgt = rgt - {ts} where rgt > {rgt}'.format(u=self.uos, ts=ts, rgt=rgt), '{u} lft = lft + {ts} where lft >= {c}'.format(ts=ts, c=c, u=self.uos), '{u} rgt = rgt + {ts} where rgt >= {c}'.format(ts=ts, c=c, u=self.uos), '{u} lft = 0-lft+{d}, rgt = 0-rgt + {d} where lft <= 0-{lft} and rgt >= 0-{rgt}'.format(d=d, lft=lft, rgt=rgt, u=self.uos)] for s in sql: db.engine.execute(text(s)) db.session.commit()
def tearDown(self): from psi.app.service import Info fixture.cleanup_database(self.app_context) Info.get_db().get_engine(self.app).dispose() self.app_context.pop()
from decimal import Decimal from psi.app import const from psi.app.models.data_security_mixin import DataSecurityMixin from psi.app.service import Info from psi.app.utils.format_util import format_decimal from sqlalchemy import Column, Integer, ForeignKey, Numeric, Text, DateTime, select, func from sqlalchemy.ext.hybrid import hybrid_property from sqlalchemy.orm import backref, relationship db = Info.get_db() class InventoryInOutLink(db.Model, DataSecurityMixin): __tablename__ = 'inventory_in_out_link' id = Column(Integer, primary_key=True) date = Column(DateTime, nullable=False) product_id = Column(Integer, ForeignKey('product.id'), nullable=False) product = relationship('Product', foreign_keys=[product_id]) in_price = Column(Numeric(precision=8, scale=2, decimal_return_scale=2), nullable=False) in_date = Column(DateTime, nullable=False) receiving_line_id = Column(Integer, ForeignKey('receiving_line.id'), nullable=False) receiving_line = relationship('ReceivingLine', backref=backref('inventory_links', uselist=True), foreign_keys=[receiving_line_id]) out_price = Column(Numeric(precision=8, scale=2, decimal_return_scale=2), nullable=False) out_date = Column(DateTime, nullable=False) out_quantity = Column(Numeric(precision=8, scale=2, decimal_return_scale=2), nullable=False)
def filter_by_po_id(po_id): return Info.get_db().session.query(Receiving).filter_by(purchase_order_id=po_id).all()
def shutdown_session(exception=None): database = Info.get_db() database.session.remove()