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 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 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 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 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 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 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 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 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 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 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 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()