def init_image_service(app):
"""
Initialize image store service
"""
image_store = app.config['IMAGE_STORE_SERVICE']
if image_store is not None:
Info.set_image_store_service(image_store(app))
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 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_image_service(app):
"""
Initialize image store service
"""
image_store = app.config['IMAGE_STORE_SERVICE']
if image_store is not None:
Info.set_image_store_service(image_store(app))
def _handle_image_delete(mapper, conn, target):
"""
Delete the local or remote image from 3rd service when delete the image.
if path is not empty, assume local storage, delete using after_delete
listener, if public_id is not null, assume using cloud service, delete
using public_id.
:param mapper:
:param conn:
:param target: image object to be deleted
:return:
"""
# TODO.xqliu The delete of image is not working for local image store
Info.get_image_store_service().remove(target.path, target.public_id)
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 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 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 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 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 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 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 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 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 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_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 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 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 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 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_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 update_need_advice_flag(self):
def get_time_in_second(key):
return int(time.time())
from psi.app.service import Info
from psi.app.models import ProductInventory
from flask import current_app
last_update = Info.get('need_advice_last_update_timestamp', get_time_in_second)
if int((time.time() - last_update)) > current_app.config['NEED_ADVICE_UPDATE_SECONDS']:
session = Info.get_db().session
all_products = Product.query.all()
for p in all_products:
p.need_advice = False
session.add(p)
products = ProductInventory.query.order_by(ProductInventory.weekly_average_profit).limit(50).all()
for p in products:
p.need_advice = True
session.add(p)
session.commit()
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 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 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 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 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_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 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_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_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 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 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 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 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 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 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 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 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 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_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 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 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 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 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 save_image(owner, image_file):
"""
Save image to an owner object
:param service: The service to save the image,
use to isolate different storage service
:param owner: Owner of this image object
:param image_file: File storage object of the image
:return: The image object created
"""
from psi.app.models import Image
service = Info.get_image_store_service()
if owner.image is not None:
existing_img_public_id = owner.image.path
service.remove(existing_img_public_id)
image = Image()
owner.image = image
public_id = str(uuid.uuid4())
result = service.save(image_file, public_id=public_id)
owner.image.path = result['url']
owner.image.public_id = public_id
return image
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()