def post(self):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
email = request.json["email"]
user = User.query.filter_by(email=email).first()
if user:
if user.active == 1:
user.active = 0
elif user.active == 0:
user.active = 1
db_session.commit()
return {
"version": api_version,
"message":"Member status for {} is changed to {}".format(email, user.active),
"data": {
"email": email,
"active": user.active
}
}, 200
return {
"version": api_version,
"message":"Member {} does not exist".format(email),
"data": {}
}, 404
def put(self, member_id):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
if "application/json" in request.headers["Content-Type"]:
username = request.json.get("username")
suppliers = request.json.get("suppliers")
contact = request.json.get("contact")
company = request.json.get("company")
active = request.json.get("active")
address = request.json.get("address")
role = request.json.get("role")
user = User.query.filter_by(id=member_id).first()
if user:
if username is not None:
user.username = username
if company is not None:
user.company = company
if contact is not None:
user.contact = str(contact)
if suppliers is not None:
user.suppliers = str(suppliers)
if active is not None:
user.active = active
if address is not None:
user.address = address
if role is not None and Role.query.filter_by(
name=role).first():
role_list = list(map(lambda x: x.name, user.roles))
user_datastore.remove_role_from_user(
user.email, str(role_list[0]))
db_session.commit()
user_datastore.add_role_to_user(user.email, role)
db_session.commit()
user_updated = User.query.filter_by(id=member_id).first()
return {
"version":
api_version,
"message":
"Update {}(id: {}) info".format(user_updated.email,
user_updated.id),
"data": {
"id": user_updated.id,
"email": user_updated.email,
"username": user_updated.username,
"suppliers": ast.literal_eval(user_updated.suppliers),
"company": user_updated.company,
"contact": user_updated.contact,
"address": user_updated.address,
"active": user_updated.active,
"role": list(map(lambda x: x.name, user_updated.roles))
}
}, 200
return {
"version": api_version,
"message": "Check header and data type",
"data": {}
}, 404
def get(self, role_id):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
role = Role.query.filter_by(id=role_id).first()
data = role.as_dict()
return {
"version": api_version,
"message": 'Role id: {} - {}'.format(role.id, role.label),
"data": data
}, 200
def get(self):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
ops_history = Logging.query.order_by(
Logging.timestampe.desc()).limit(300)
data = [log.as_dict() for log in ops_history]
return {
"version": api_version,
"message": 'Ops history',
"data": data
}, 200
def get(self, member_id):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
user = User.query.filter_by(id=member_id).first()
# order_user = User.query.order_by(User.username).all()
data = user.as_dict()
data["suppliers"] = ast.literal_eval(data["suppliers"])
data["roles"] = list(map(lambda x: x.name, user.roles))
return {
"version": api_version,
"message": 'User id: {} - {}'.format(user.id, user.email),
"data": data
}, 200
def get(self, supplier_id):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
if check_member_supplier(supplier_id, current_user.email) == False:
return {
"message":
'Missing authorization to view info for this supplier',
}, 401
all_download = OutboundDownload.query.filter_by(
supplier=supplier_id).all()
data = [download.as_dict() for download in all_download]
# Finds up to the 10 most recent entries for every user with inner query. Then deletes everything not returned by the inner query.
result = OutboundDownload.query.filter_by(supplier=supplier_id). \
order_by(OutboundDownload.created_at.desc()). \
slice(2,(len(all_download)))
sq = OutboundDownload.query.filter_by(supplier=supplier_id). \
order_by(OutboundDownload.created_at.desc()). \
limit(2). \
with_for_update()
q = update(OutboundDownload).where(
OutboundDownload.supplier == sq.as_scalar()).values(
{"active": False})
db_session.execute(q)
db_session.commit()
print(q)
all_download2 = OutboundDownload.query.filter_by(
supplier=supplier_id).all()
result2 = OutboundDownload.query.filter_by(
supplier=supplier_id).count()
resultdata = [download.as_dict() for download in all_download2]
# print(result2)
# print(len(all_download))
return {
"version":
api_version,
"message":
"downloadable outbound file for supplier: {}".format(supplier_id),
"data":
resultdata
}, 200
def put(self):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
if "application/json" in request.headers["Content-Type"]:
email = request.json["email"]
username = request.json.get("username")
suppliers = request.json.get("suppliers")
contact = request.json.get("contact")
company = request.json.get("company")
active = request.json.get("active")
user = User.query.filter_by(email=email).first()
if user:
if username is not None:
user.username = username
if company is not None:
user.company = company
if contact is not None:
user.contact = str(contact)
if suppliers is not None:
user.suppliers = str(suppliers)
if active is not None:
user.active = active
db_session.commit()
user_updated = User.query.filter_by(email=email).first()
return {
"version": api_version,
"message":"Update {} member info".format(email),
"data": {
"email": email,
"username": user_updated.username,
"suppliers": {"data":user_updated.suppliers},
"company": user_updated.company,
"contact": user_updated.contact,
"active": user_updated.active
}
}, 200
return {
"version": api_version,
"message":"Check header and data type",
"data": {}
}, 404
def put(self, role_id):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
if "application/json" in request.headers["Content-Type"]:
description = request.json.get("description")
label = request.json.get("label")
render_structure = request.json.get("render_structure")
role = Role.query.filter_by(id=role_id).first()
if role:
if description is not None:
role.description = description
if label is not None:
role.label = label
if render_structure is not None:
role.render_structure = render_structure
db_session.commit()
role_updated = Role.query.filter_by(id=role_id).first()
return {
"version":
api_version,
"message":
"Update {}(id: {}) info".format(role_updated.label,
role_updated.id),
"data": {
"id": role_updated.id,
"description": role_updated.description,
"label": role_updated.label,
"render_structure": role_updated.render_structure
}
}, 200
return {
"version": api_version,
"message": "Check header and data type",
"data": {}
}, 404
def get(self, member_id):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
if "application/json" in request.headers["Content-Type"]:
user = user_datastore.find_user(id=member_id)
ops_history = Logging.query.filter_by(email=user.email).all()
data = [log.as_dict() for log in ops_history]
return {
"version":
api_version,
"message":
'Ops history for {} (User id {})'.format(user.email, user.id),
"data":
data
}, 200
return {
"version": api_version,
"message": "Please check your input data type",
"data": {}
}, 404
def post(self):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
if "application/json" in request.headers["Content-Type"]:
email = request.json["email"]
print('\n\n\n\n', email, '\n\n\n\n')
password = request.json["password"]
username = request.json.get("username")
current_login_ip = request.remote_addr
company = request.json.get("company")
contact = request.json.get("contact")
address = request.json.get("address")
roles = request.json.get("role")
####### NEED TO UPDATE TO DYNAMICALLY SEARCH AND INDEX INPUT FROM CLIENT ######
try:
suppliers = request.json["suppliers"]
except:
# suppliers_list = Supplier.query.order_by(Supplier.email).all()
# data = [supplier.as_dict() for supplier in suppliers_list]
suppliers = []
pass
# suppliers_list = Supplier.query.order_by(Supplier.email).all()
# data = [supplier.as_dict() for supplier in suppliers_list]
if user_datastore.get_user(email):
return {
"version": api_version,
"message": "User {} exist. Please login".format(email),
"data": {}
}, 422
elif not re.match(r"[^@]+@[^@]+\.[^@]+", email):
return {
"version": api_version,
"message": "Please check your email format.",
"data": {}
}, 422
elif len(password) < 6 or not password:
return {
"version": api_version,
"message": "Password must be at least 6 characters long.",
"data": {}
}, 422
# create user and add user role as default
user_datastore.create_user(email=email,
password=hash_password(password),
username=username,
current_login_ip=current_login_ip,
suppliers=str(suppliers),
company=company,
contact=contact,
address=address)
db_session.commit()
if roles and Role.query.filter_by(name=roles).first():
user_datastore.add_role_to_user(email, roles)
else:
user_datastore.add_role_to_user(email, "user")
db_session.commit()
roles = user_datastore.find_user(email=email).roles
user = User.query.filter_by(email=email).first()
return {
"version": api_version,
"message": "User {} created.".format(email),
"data": {
"id": user.id,
"email": user.email,
"roles": list(map(lambda x: x.name, roles)),
"suppliers": ast.literal_eval(user.suppliers),
"company": user.company,
"contact": user.contact,
"address": user.address,
"active": user.active
}
}, 200
def get(self, supplier_id):
page = request.args.get('_page')
limit = request.args.get('_limit')
keyword = request.args.get('keyword')
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
if not client:
return {
"version": api_version,
"message": "Server database error"
}, 500
# Select data from query_table_sop table from warehouse_tw
if keyword:
query_table_sop = """
SELECT
main_table.*,
CASE WHEN main_table.order_status = 'COMPLETED' AND return_info.return_status = 'REFUND_PAID'
THEN 'UPDATED STATUS'
ELSE main_table.order_status
END AS 'integrated_order_status'
FROM (
SELECT
table_ssu.item_id,
table_ssu.supplier_id,
table_op.ordersn,
table_op.order_time AS 'create_time',
CONCAT(table_op.item_name, "_", table_op.variation_name) AS "sku_name",
table_op.item_id + "_" + table_op.variation_id AS "sku_id",
table_op.item_id AS 'supplier_sku_id',
table_op.variation_quantity_purchased AS 'quantity',
table_op.variation_original_price AS 'orig_sale_prince',
table_op.variation_discounted_price AS 'current_sale_price',
table_op.order_status
FROM (
SELECT distinct
item_id,
supplier_id
FROM twk_table_ssu table_ssu
WHERE supplier_id="{}"
) AS table_ssu
INNER JOIN (
SELECT table_sop.*, table_oi.order_status
FROM table_sop
LEFT JOIN s_table_oi table_oi
ON table_sop.ordersn=table_oi.ordersn
WHERE (
table_sop.ordersn IN ('{}')
OR table_sop.item_id IN ('{}')
)
ORDER BY order_time DESC
) AS table_op
ON table_ssu.item_id=table_op.item_id
LIMIT {}, {}
) AS main_table
LEFT JOIN ( SELECT ordersn AS table_ro, status AS return_status FROM table_sri WHERE status = "REFUND_PAID") return_info
ON main_table.ordersn=return_info.table_ro
""".format(supplier_id, keyword, keyword,
(int(page) - 1) * int(limit),
int(limit) + 1)
if not keyword:
keyword = "all"
query_table_sop = """
SELECT
main_table.*,
CASE WHEN main_table.order_status = 'COMPLETED' AND return_info.return_status = 'REFUND_PAID'
THEN 'COMPLETED_REFUND_PAID'
ELSE main_table.order_status
END AS 'integrated_order_status'
FROM (
SELECT
table_ssu.item_id,
table_ssu.supplier_id,
table_op.ordersn,
table_op.order_time AS 'create_time',
CONCAT(table_op.item_name, "_", table_op.variation_name) AS "sku_name",
table_op.item_id + "_" + table_op.variation_id AS "sku_id",
table_op.item_id AS 'supplier_sku_id',
table_op.variation_quantity_purchased AS 'quantity',
table_op.variation_original_price AS 'orig_sale_prince',
table_op.variation_discounted_price AS 'current_sale_price',
table_op.order_status
FROM (
SELECT distinct
item_id,
supplier_id
FROM twk_table_ssu table_ssu
WHERE supplier_id="{}"
) AS table_ssu
INNER JOIN (
SELECT table_sop.*, table_oi.order_status
FROM table_sop
LEFT JOIN s_table_oi table_oi
ON table_sop.ordersn=table_oi.ordersn
ORDER BY order_time DESC
) AS table_op
ON table_ssu.item_id=table_op.item_id
LIMIT {}, {}
) AS main_table
LEFT JOIN ( SELECT ordersn AS table_ro, status AS return_status FROM table_sri WHERE status = "REFUND_PAID") return_info
ON main_table.ordersn=return_info.table_ro
""".format(supplier_id, (int(page) - 1) * int(limit),
int(limit) + 1)
response_table_sop = client._requests(query_table_sop)
count = 1
for i in response_table_sop["data"]:
i["create_time"] = str(i["create_time"])
i["index"] = (int(page) - 1) * int(limit) + count
count = count + 1
# query_s_table_oi = """
# SELECT supplier_id, supplier_name FROM table_tsp
# INNER JOIN (SELECT supplier_id FROM table_tsp LIMIT 10) AS my_results USING(supplier_id);
# """
return {
"version":
api_version,
"message":
"Get outbound order {}. Page:{} Limit:{} Keyword:{}".format(
supplier_id, page, limit, keyword),
"data":
response_table_sop["data"]
}, 200
def get(self, supplier_id):
if check_member_role(["admin"], current_user.email) == False:
return {
"message": 'Missing authorization to retrieve content',
}, 401
if check_member_supplier(supplier_id, current_user.email) == False:
return {
"message":
'Missing authorization to view info for this supplier',
}, 401
start_date = request.args.get('start_date')
end_date = request.args.get('end_date')
keyword = request.args.get('keyword')
if start_date and end_date and start_date > end_date:
return {
"version": api_version,
"message":
"Please check your date input. Start date is greater than end date.",
"data": {}
}, 422
# Select data from query_table_sop table from warehouse_tw
if keyword:
query_table_sop = """
SELECT
main_table.*,
CASE WHEN main_table.order_status = 'COMPLETED' AND return_info.return_status = 'REFUND_PAID'
THEN 'COMPLETED_REFUND_PAID'
ELSE main_table.order_status
END AS 'integrated_order_status'
FROM (
SELECT
table_ssu.item_id,
table_ssu.supplier_id,
table_op.ordersn,
table_op.order_time AS 'create_time',
CONCAT(table_op.item_name, "_", table_op.variation_name) AS "sku_name",
table_op.item_id + "_" + table_op.variation_id AS "sku_id",
table_op.item_id AS 'supplier_sku_id',
table_op.variation_quantity_purchased AS 'quantity',
table_op.variation_original_price AS 'orig_sale_prince',
table_op.variation_discounted_price AS 'current_sale_price',
table_op.order_status
FROM (
SELECT distinct
item_id,
supplier_id
FROM twk_table_ssu table_ssu
WHERE supplier_id="{}"
) AS table_ssu
INNER JOIN (
SELECT table_sop.*, table_oi.order_status
FROM table_sop
LEFT JOIN s_table_oi table_oi
ON table_sop.ordersn=table_oi.ordersn
WHERE (
(table_sop.ordersn IN ('{}') OR table_sop.item_id IN ('{}'))
AND (table_sop.order_time BETWEEN '{}' AND '{}')
)
ORDER BY order_time DESC
) AS table_op
ON table_ssu.item_id=table_op.item_id
LIMIT 30000
) AS main_table
LEFT JOIN ( SELECT ordersn AS table_ro, status AS return_status FROM table_sri WHERE status = "REFUND_PAID") return_info
ON main_table.ordersn=return_info.table_ro
""".format(supplier_id, keyword, keyword, start_date, end_date)
if not keyword:
keyword = 'all'
query_table_sop = """
SELECT
main_table.*,
CASE WHEN main_table.order_status = 'COMPLETED' AND return_info.return_status = 'REFUND_PAID'
THEN 'COMPLETED_REFUND_PAID'
ELSE main_table.order_status
END AS 'integrated_order_status'
FROM (
SELECT
table_ssu.item_id,
table_ssu.supplier_id,
table_op.ordersn,
table_op.order_time AS 'create_time',
CONCAT(table_op.item_name, "_", table_op.variation_name) AS "sku_name",
table_op.item_id + "_" + table_op.variation_id AS "sku_id",
table_op.item_id AS 'supplier_sku_id',
table_op.variation_quantity_purchased AS 'quantity',
table_op.variation_original_price AS 'orig_sale_prince',
table_op.variation_discounted_price AS 'current_sale_price',
table_op.order_status
FROM (
SELECT distinct
item_id,
supplier_id
FROM twk_table_ssu table_ssu
WHERE supplier_id="{}"
) AS table_ssu
INNER JOIN (
SELECT table_sop.*, table_oi.order_status
FROM table_sop
LEFT JOIN s_table_oi table_oi
ON table_sop.ordersn=table_oi.ordersn
WHERE table_sop.order_time BETWEEN '{}' AND '{}'
ORDER BY order_time DESC
) AS table_op
ON table_ssu.item_id=table_op.item_id
LIMIT 30000
) AS main_table
LEFT JOIN ( SELECT ordersn AS table_ro, status AS return_status FROM table_sri WHERE status = "REFUND_PAID") return_info
ON main_table.ordersn=return_info.table_ro
""".format(supplier_id, start_date, end_date)
response_table_sop = client._requests(query_table_sop)
for i in response_table_sop["data"]:
i["create_time"] = str(i["create_time"])
# Download as xlsx
df = pd.DataFrame(response_table_sop["data"])
file_path = app.root_path + os.path.sep + "download" + os.path.sep
file_name = "outbound_{}_{}_{}_{}.xlsx".format(supplier_id, start_date,
end_date, keyword)
df.to_excel(file_path + file_name, sheet_name='outbound', index=False)
# Save information to database
exist_search_entry = OutboundDownload.query.filter_by(supplier=supplier_id). \
filter_by(start_date=start_date). \
filter_by(end_date=end_date). \
filter_by(keyword=keyword).first()
if exist_search_entry:
exist_search_entry.created_at = datetime.now()
db_session.commit()
print("update time to {}".format(datetime.now()))
else:
new_xlsx_entry = OutboundDownload(supplier_id, start_date,
end_date, keyword, file_name)
db_session.add(new_xlsx_entry)
db_session.commit()
print("created new entry")
return {
"version":
api_version,
"message":
"download outbound {} {} {}".format(start_date, end_date, keyword),
# "data": response_table_sop["data"]
"data": {
"url": "/download_outbound/{}".format(file_name),
"start_date": start_date,
"end_date": end_date,
"supplier_id": supplier_id,
"filename": file_name
}
}, 200