def get_purchase_order_by_id(self,
entity_id,
supplier_id=None,
distributors=[]):
table = 'brewoptix-purchase-orders'
purchase_order = self._storage.get(table, entity_id)
if purchase_order:
purchase_order = clean(purchase_order)
if supplier_id and purchase_order["supplier_id"] != supplier_id:
raise NoSuchEntity("Purchase Order")
if purchase_order["distributor_id"] in distributors:
raise NoSuchEntity("Purchase Order")
purchase_order['order_date'] = datetime.utcfromtimestamp(
purchase_order['order_date']).isoformat().split('T')[0]
if "pack_date" in purchase_order:
purchase_order['pack_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['pack_date'])).split('T')[0]
if "ship_date" in purchase_order:
purchase_order['ship_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['ship_date'])).split('T')[0]
else:
raise NoSuchEntity("Purchase Order")
return purchase_order
def get_all_counts(self, supplier_id):
obj_type = 'counts'
query = {
'KeyConditionExpression':
Key('supplier_id').eq(supplier_id) & Key('obj_type').eq(obj_type),
'FilterExpression':
(Attr('latest').eq(True) & Attr('active').eq(True)),
'IndexName':
'by_supplier_id_and_obj_type'
}
response = self._storage.get_items(query)
counts_obj = []
for item in response['Items']:
# The 4 lines below can be uncommented if we move
# from ALL to KEYS_ONLY for the table
# entity_id = item['EntityID']
# count = self._storage.get(table, entity_id)
# count = clean(count)
count = json_util.loads(clean(item))
count['count_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(count['count_date'])).split('T')[0]
counts_obj.append(count)
return counts_obj
def get_all_on_hands(self, supplier_id):
table = 'brewoptix-on-hand-inventory'
query = {
'KeyConditionExpression': Key('supplier_id').eq(supplier_id),
'FilterExpression':
(Attr('latest').eq(True) & Attr('active').eq(True)),
'IndexName': 'by_supplier_id'
}
response = self._storage.get_items(table, query)
on_hands_obj = []
for item in response['Items']:
# The 4 lines below can be uncommented if we move
# from ALL to KEYS_ONLY for the table
# entity_id = item['EntityID']
# on_hand = self._storage.get(table, entity_id)
# on_hand = clean(on_hand)
on_hand = json_util.loads(clean(item))
on_hand['observation_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
on_hand['observation_date'])).split('T')[0]
on_hands_obj.append(on_hand)
return on_hands_obj
def get_all_adjustments(self, supplier_id):
table = 'brewoptix-adjustment-inventory'
query = {
'KeyConditionExpression': Key('supplier_id').eq(supplier_id),
'FilterExpression':
(Attr('Latest').eq(True) & Attr('Active').eq(True)),
'IndexName': 'by_supplier_id_and_adjustment_date'
}
response = self._storage.get_items(table, query)
adjustments_obj = []
for item in response['Items']:
# The 4 lines below can be uncommented if we move
# from ALL to KEYS_ONLY for the table
# entity_id = item['EntityID']
# adjustment_resp = self._storage.get(table, entity_id)
# adjustment = adjustment_resp['Items'][0]
# adjustment = clean(adjustment)
adjustment = json_util.loads(clean(item))
adjustment['adjustment_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
adjustment['adjustment_date'])).split('T')[0]
adjustments_obj.append(adjustment)
return adjustments_obj
def get_adjustment_by_adjustment_date_range(self,
supplier_id,
min_adjustment_date,
max_adjustment_date=None):
table = 'brewoptix-adjustment-inventory'
min_adjustment_date = maya.parse(
min_adjustment_date.split('T')[0]).epoch
if max_adjustment_date:
max_adjustment_date = maya.parse(
max_adjustment_date.split('T')[0]).epoch
if max_adjustment_date:
query = {
'KeyConditionExpression':
Key('supplier_id').eq(supplier_id)
& Key('adjustment_date').between(min_adjustment_date,
max_adjustment_date),
'FilterExpression':
Attr('latest').eq(True) & Attr('active').eq(True),
'IndexName':
'by_supplier_id_and_adjustment_date'
}
else:
query = {
'KeyConditionExpression':
Key('supplier_id').eq(supplier_id)
& Key('adjustment_date').gt(min_adjustment_date),
'FilterExpression':
Attr('latest').eq(True) & Attr('active').eq(True),
'IndexName':
'by_supplier_id_and_adjustment_date'
}
response = self._storage.get_items(table, query)
adjustments_obj = []
for item in response['Items']:
# The 4 lines below can be uncommented if we move
# from ALL to KEYS_ONLY for the table
# entity_id = item['EntityID']
# adjustment_resp = self._storage.get(table, entity_id)
# adjustment = adjustment_resp['Items'][0]
# adjustment = clean(adjustment)
adjustment = json_util.loads(clean(item))
adjustment['adjustment_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
adjustment['adjustment_date'])).split('T')[0]
adjustments_obj.append(adjustment)
return adjustments_obj
def clean(obj):
remove_variables = [
'previous_version', 'active', 'latest', 'changed_by_id'
]
for key in remove_variables:
obj.pop(key, None)
if obj and 'changed_on' in obj:
obj['changed_on'] = maya.to_iso8601(
datetime.utcfromtimestamp(obj['changed_on']))
return obj
def update_purchase_order(self, obj):
table = 'brewoptix-purchase-orders'
check_for_required_keys(obj, purchase_order_attributes)
# check if content datatype is right
content = {k: v for k, v in obj.items() if k not in base_attributes}
check_properties_datatypes(content, purchase_order_attributes)
if 'entity_id' not in obj:
raise BadParameters
obj['user_id'] = self._user_id
purchase_order = self._storage.get(table, obj['entity_id'])
if not purchase_order:
raise NoSuchEntity("Purchase Order")
obj['order_date'] = maya.parse(obj['order_date']).epoch
if "pack_date" in obj:
obj['pack_date'] = maya.parse(obj['pack_date']).epoch
if "ship_date" in obj:
obj['ship_date'] = maya.parse(obj['ship_date']).epoch
purchase_order_obj = self._storage.save(table, obj)
self.sns_publish("purchase-orders", obj) # publish notification
purchase_order = clean(purchase_order_obj)
purchase_order['order_date'] = datetime.utcfromtimestamp(
purchase_order['order_date']).isoformat().split('T')[0]
if "pack_date" in purchase_order:
purchase_order['pack_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['pack_date'])).split('T')[0]
if "ship_date" in purchase_order:
purchase_order['ship_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['ship_date'])).split('T')[0]
return purchase_order
def add_purchase_order(self, obj):
table = 'brewoptix-purchase-orders'
check_for_required_keys(obj, purchase_order_attributes)
content = {k: v for k, v in obj.items() if k not in base_attributes}
check_properties_datatypes(content, purchase_order_attributes)
obj['user_id'] = self._user_id
if 'order_number' not in obj or not obj['order_number']:
current_order_number = self._create_order_number(
obj['supplier_id'])
obj['order_number'] = current_order_number
# convert observation_month to epoch integer
obj['order_date'] = maya.parse(obj['order_date']).epoch
if "pack_date" in obj:
obj['pack_date'] = maya.parse(obj['pack_date']).epoch
if "ship_date" in obj:
obj['ship_date'] = maya.parse(obj['ship_date']).epoch
purchase_order_obj = self._storage.save(table, obj)
self.sns_publish("purchase-orders", obj) # publish notification
purchase_order = clean(purchase_order_obj)
purchase_order['order_date'] = datetime.utcfromtimestamp(
purchase_order['order_date']).isoformat().split('T')[0]
if "pack_date" in purchase_order:
purchase_order['pack_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['pack_date'])).split('T')[0]
if "ship_date" in purchase_order:
purchase_order['ship_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['ship_date'])).split('T')[0]
return purchase_order
def get_purchase_order_by_version(self, entity_id, version):
table = 'brewoptix-purchase-orders'
try:
obj = self._storage.get_by_version(table, entity_id, version)
purchase_order = obj['Items'][0]
print('purchase order:{}'.format(purchase_order))
purchase_order = clean(purchase_order)
purchase_order['order_date'] = datetime.utcfromtimestamp(
purchase_order['order_date']).isoformat().split('T')[0]
if "pack_date" in purchase_order:
purchase_order['pack_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['pack_date'])).split('T')[0]
if "ship_date" in purchase_order:
purchase_order['ship_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['ship_date'])).split('T')[0]
except ClientError:
print("Object not found.")
purchase_order = {}
return purchase_order
def get_count_by_count_date_range(self,
supplier_id,
min_count_date,
max_count_date=None):
obj_type = 'counts'
min_count_date = maya.parse(min_count_date.split('T')[0]).epoch
print(min_count_date)
if max_count_date:
max_count_date = maya.parse(max_count_date.split('T')[0]).epoch
print(max_count_date)
if max_count_date:
query = {
'KeyConditionExpression':
Key('supplier_id').eq(supplier_id)
& Key('obj_type').eq(obj_type),
'FilterExpression':
Attr('latest').eq(True) & Attr('active').eq(True)
& Attr('count_date').between(min_count_date, max_count_date),
'IndexName':
'by_supplier_id_and_obj_type'
}
else:
query = {
'KeyConditionExpression':
Key('supplier_id').eq(supplier_id)
& Key('obj_type').eq(obj_type),
'FilterExpression':
Attr('latest').eq(True) & Attr('active').eq(True)
& Attr('count_date').gt(min_count_date),
'IndexName':
'by_supplier_id_and_obj_type'
}
response = self._storage.get_items(query)
counts_obj = []
for item in response['Items']:
count = json_util.loads(clean(item))
count['count_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(count['count_date'])).split('T')[0]
counts_obj.append(count)
return counts_obj
def process_counts_queue(self, obj):
products = obj["products"]
products_agg = {}
supplier_id = obj["supplier_id"]
for product in products:
package_type_id = product['package_type_id']
if product["unit_quantity"] is not None:
quantity = int(product["unit_quantity"])
else:
quantity = 0
if product["pallet_quantity"] is not None and product[
"units_per_pallet"] is not None:
quantity += int(product["pallet_quantity"]) * int(
product["units_per_pallet"])
brand_id = product["brand_id"]
created_on = maya.to_iso8601(
datetime.utcfromtimestamp(obj['count_date'])).split("T")[0]
unique_id = brand_id + '_' + package_type_id
if unique_id in products_agg.keys():
products_agg[unique_id]["quantity"] += quantity
else:
products_agg[unique_id] = {
"supplier_id": supplier_id,
"brand_id": brand_id,
"package_type_id": package_type_id,
"quantity": quantity,
"actual": True,
"created_on": created_on
}
# delete all records with matching brand_id, package_type_id, created_on, actual=true
table = 'on_hand'
query = """
DELETE
FROM {TABLE}
WHERE brand_id='{BRAND_ID}' AND package_type_id='{PACK_ID}' AND created_on='{CREATED_ON}' AND actual=true""".format(
TABLE=table,
BRAND_ID=brand_id,
PACK_ID=package_type_id,
CREATED_ON=created_on)
self._aurora_storage._execute(query)
# aggregate by package_type_id
for item in products_agg.values():
if obj["active"] and "status" and obj[
"status"] == "complete": # we are recording only for this status
self._aurora_storage.save('on_hand', item)
# Re-calculate projections
created_on_minus_one = maya.parse(
item['created_on']).add(days=-1).iso8601().split("T")[0]
self.sqs_enqueue(
"projections", {
'user_id': self._user_id,
'supplier_id': supplier_id,
'brand_id': item['brand_id'],
'package_type_id': item['package_type_id'],
'start_date': created_on_minus_one,
}) # enqueue object
def process_adjustments_queue(self, obj):
print(obj)
supplier_id = obj["supplier_id"]
entity_id = obj['entity_id']
adjustment_date = maya.to_iso8601(
datetime.utcfromtimestamp(obj['adjustment_date'])).split("T")[0]
# Select all records with matching entity_id
table = "adjustments"
query = """SELECT brand_id, package_type_id, adjustment_date
FROM {TABLE}
USE INDEX (by_entity_id_and_supplier_id)
WHERE supplier_id='{SUPPLIER_ID}' AND entity_id='{ENTITY_ID}'
""".format(TABLE=table,
SUPPLIER_ID=supplier_id,
ENTITY_ID=entity_id)
results = self._aurora_storage.get_items(query)
# convert from response
keys = ["brand_id", "package_type_id", "adjustment_date"]
record_ids = {}
for result in results:
record = {}
for i, val in enumerate(result):
record[keys[i]] = val
record_ids[record['brand_id'] + '_' +
record['package_type_id']] = record
# delete all by entity_id from table
table = "adjustments"
query = """
DELETE
FROM `{TABLE}`
WHERE entity_id='{ENTITY_ID}' AND supplier_id='{SUPPLIER_ID}'
""".format(TABLE=table,
ENTITY_ID=entity_id,
SUPPLIER_ID=supplier_id)
resp = self._aurora_storage._execute(query)
if obj['active']: # put or post
brand_id = obj['brand_id']
package_type_id = obj['package_type_id']
quantity = obj['quantity']
item = {
"brand_id": brand_id,
"entity_id": entity_id,
"package_type_id": package_type_id,
"supplier_id": supplier_id,
"adjustment_date": adjustment_date,
"quantity": quantity,
}
_id = item['brand_id'] + '_' + item['package_type_id']
if _id not in record_ids or obj['adjustment_date'] < maya.parse(
record_ids[_id]["adjustment_date"]).epoch:
record_ids[_id] = item
# insert into aurora table
self._aurora_storage.save(table, item)
# trigger projections calculation for old records + new inserts
for item in record_ids.values():
brand_id = item["brand_id"]
package_type_id = item["package_type_id"]
# Re-calculate projections
self.sqs_enqueue(
"projections", {
'user_id': self._user_id,
'supplier_id': supplier_id,
'brand_id': brand_id,
'package_type_id': package_type_id,
'start_date': item['adjustment_date'],
}) # enqueue object
def get_purchase_orders_by_order_date_range(self,
min_order_date,
max_order_date=None,
supplier_id=None,
distributors=[]):
table = 'brewoptix-purchase-orders'
min_order_date = maya.parse(min_order_date.split('T')[0]).epoch
if max_order_date:
max_order_date = maya.parse(max_order_date.split('T')[0]).epoch
if supplier_id:
if max_order_date:
query = {
'KeyConditionExpression':
Key('supplier_id').eq(supplier_id) &
Key('order_date').between(min_order_date, max_order_date),
'FilterExpression':
Attr('latest').eq(True) & Attr('active').eq(True),
'IndexName':
'by_supplier_id_and_order_date'
}
else:
query = {
'KeyConditionExpression':
Key('supplier_id').eq(supplier_id)
& Key('order_date').gt(min_order_date),
'FilterExpression':
Attr('latest').eq(True) & Attr('active').eq(True),
'IndexName':
'by_supplier_id_and_order_date'
}
response = self._storage.get_items(table, query)
response_items = response['Items']
else:
response_items = []
for distributor in distributors:
if max_order_date:
query = {
'KeyConditionExpression':
Key('distributor_id').eq(distributor)
& Key('order_date').between(min_order_date,
max_order_date),
'FilterExpression':
Attr('latest').eq(True) & Attr('active').eq(True),
'IndexName':
'by_distributor_id_and_order_date'
}
else:
query = {
'KeyConditionExpression':
Key('distributor_id').eq(distributor)
& Key('order_date').gt(min_order_date),
'FilterExpression':
Attr('latest').eq(True) & Attr('active').eq(True),
'IndexName':
'by_distributor_id_and_order_date'
}
response = self._storage.get_items(table, query)
response_items.extend(response['Items'])
if supplier_id:
print("supplier:{}".format(response_items))
else:
print("distributor:{}".format(response_items))
purchase_orders_obj = []
for item in response_items:
# The 4 lines below can be uncommented if we move
# from ALL to KEYS_ONLY for the table
# entity_id = item['EntityID']
# purchase_order = self._storage.get(table, entity_id)
# purchase_order = clean(purchase_order)
purchase_order = json_util.loads(clean(item))
purchase_order['order_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['order_date'])).split('T')[0]
if "pack_date" in purchase_order:
purchase_order['pack_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['pack_date'])).split('T')[0]
if "ship_date" in purchase_order:
purchase_order['ship_date'] = maya.to_iso8601(
datetime.utcfromtimestamp(
purchase_order['ship_date'])).split('T')[0]
purchase_orders_obj.append(purchase_order)
return purchase_orders_obj
def process_purchase_orders_queue(self, obj):
print(obj)
entity_id = obj['entity_id']
supplier_id = obj["supplier_id"]
pack_date = maya.to_iso8601(datetime.utcfromtimestamp(
obj['pack_date'])).split("T")[0]
# Select all records with matching entity_id
table = "sales"
query = """SELECT brand_id, package_type_id, sale_date
FROM {TABLE}
USE INDEX (by_entity_id_and_supplier_id)
WHERE supplier_id='{SUPPLIER_ID}' AND entity_id='{ENTITY_ID}'
""".format(TABLE=table, SUPPLIER_ID=supplier_id, ENTITY_ID=entity_id)
results = self._aurora_storage.get_items(query)
# convert from response
keys = ["brand_id", "package_type_id", "sale_date"]
record_ids = {}
print("Records gotten from Aurora sales")
for result in results:
record = {}
for i, val in enumerate(result):
record[keys[i]] = val
record_ids[record['brand_id'] + '_' +
record['package_type_id']] = record
print(record)
# delete all records with matching entity_id
table = 'sales'
query = """
DELETE
FROM {TABLE}
WHERE supplier_id='{SUPPLIER_ID}' AND entity_id='{ENTITY_ID}'
""".format(TABLE=table,
SUPPLIER_ID=supplier_id,
ENTITY_ID=entity_id)
resp = self._aurora_storage._execute(query)
# add new records or update old records
if obj['active']:
products = obj["products"]
distributor_id = obj["distributor_id"]
for product in products:
brand_id = product['brand_id']
for brand_product in product["brandProducts"]:
package_type_id = brand_product['package_type_id']
quantity = brand_product['quantity']
record = {
"brand_id": brand_id,
"distributor_id": distributor_id,
"package_type_id": package_type_id,
"supplier_id": supplier_id,
"entity_id": entity_id,
"sale_date": pack_date,
"quantity": quantity,
}
_id = record['brand_id'] + '_' + record['package_type_id']
if _id not in record_ids or obj['pack_date'] < maya.parse(
record_ids[_id]["sale_date"]).epoch:
record_ids[_id] = record
self._aurora_storage.save("sales", record)
# trigger projections calculation for old records + new inserts
for item in record_ids.values():
brand_id = item["brand_id"]
package_type_id = item["package_type_id"]
# Re-calculate projections
self.sqs_enqueue(
"projections", {
'user_id': self._user_id,
"supplier_id": supplier_id,
'brand_id': brand_id,
'package_type_id': package_type_id,
'start_date': pack_date,
}) # enqueue object
