Untitled
unknown
plain_text
5 years ago
8.3 kB
9
Indexable
from pymongo import MongoClient
from datetime import datetime
from pandas import DataFrame, ExcelWriter, concat
import argparse
from multiprocessing.pool import ThreadPool
from tqdm import tqdm
envs = {
"aceprod": {"con_str": "mongodb://afgaceprodcosmosaccount:pfxLWHufDn66rdkzRORciRVEZuJoJLGH2mvdbnV6PsDXnRwWN14yfG507QXPzuWY5lcXoczHoZ2NEeOywowhLw==@afgaceprodcosmosaccount.mongo.cosmos.azure.com:10255/?ssl=true&retrywrites=false",
"db_name": "afgaceprodcosmosdb"},
"truprod": {"con_str": "mongodb://afgtruprodcosmosaccount:qVXNk0DXwlOIUaanB1Tt4ywsQcxQbE19oq6tNHIsOC0wBNUXnZOtjwt6RiiJhnYRAX41a4Vb8H7nONxAw9lj9A==@afgtruprodcosmosaccount.mongo.cosmos.azure.com:10255/?ssl=true&replicaSet=globaldb&retrywrites=false",
"db_name": "afgtruprodcosmosdb"},
"kiabiprod": {"con_str": "mongodb://afgkiabiprodcosmosaccount:4OoiTK7dwZarzQm0JiBLWd4cTPUda0NeOSoQVQgiIi7nFXMZ9dXWWcTTRmznFLJxJ7PbIIyRU7L7GxwWNmereQ==@afgkiabiprodcosmosaccount.mongo.cosmos.azure.com:10255/?ssl=true&replicaSet=globaldb&retrywrites=false",
"db_name": "afgkiabiprodcosmosdb"},
"mnssgprod": {"con_str": "mongodb://afgmnssgprodcosmosaccount:VZuF60YBrv4qVWv94yjfJHopcDZNBYsgI08AUlvCOpVbhbkQX4KdbvmR5CxBNjD9z3BcPaWVa0Ze0pzycTGlHw==@afgmnssgprodcosmosaccount.mongo.cosmos.azure.com:10255/?ssl=true&replicaSet=globaldb&retrywrites=false&maxIdleTimeMS=120000&appName=@afgmnssgprodcosmosaccount@",
"db_name": "afgmnssgprodcosmosdb2"},
"mnshkprod": {"con_str": "mongodb://afgmnshkprodcosmosaccount:t7muy3Sa4REWrNjQYtaKG0PfBar2xCObwYQ3bmJZn22m378sl1DNnxQFcGahSG0yFpq6USkK7pWf15Pbng4xrQ==@afgmnshkprodcosmosaccount.mongo.cosmos.azure.com:10255/?ssl=true&replicaSet=globaldb&retrywrites=false&maxIdleTimeMS=120000&appName=@afgmnshkprodcosmosaccount@",
"db_name": "afgmnshkprodcosmosdb"},
"mnsuaeprod": {"con_str": "mongodb://afgmnsuaeprodcosmosaccount:eA7cRhMQT0zVk5Cygd8LsmJ08SbjatHcC0CRTwZN0l9XIvqlvB2fxiKduDVRA2d98diljac7ZwlqNUITGYpVpw==@afgmnsuaeprodcosmosaccount.mongo.cosmos.azure.com:10255/?ssl=true&replicaSet=globaldb&retrywrites=false&maxIdleTimeMS=120000&appName=@afgmnsuaeprodcosmosaccount@",
"db_name": "afgmnsuaeprodcosmosdb"}
}
class FetchFromDb():
def __init__(self, env_name):
self.env_name = env_name
self.env = envs[env_name]
self.client = MongoClient(self.env["con_str"])
self.db = self.client[self.env["db_name"]]
self.ord_col = self.db["commerce_order"]
self.final_list = []
def parse_to_list_df(self, i):
parsed_list = self.parse_db_value(i)
for i in parsed_list:
self.final_list.append(i)
# item['lineItems'][0]['priceInfo']['adjustments'][0]['adjustmentId']
def parse_db_value(self, i):
parsed_list = []
parsed_dict = None
if i['lineItems'] != []:
for item in i['lineItems']:
if item['priceInfo']['adjustments'] != []:
parsed_dict = {}
parsed_dict['orderNo'] = i['orderNo'] if 'orderNo' in i else ""
parsed_dict['promoId'] = item['priceInfo']['adjustments'][0]['adjustmentId'] if item['priceInfo']['adjustments'][0]['adjustmentId'] != "" else ""
parsed_list.append(parsed_dict)
if 'priceInfo' in i:
for item in i['shipments']:
if i['priceInfo']['adjustments'] != []:
parsed_dict = {}
parsed_dict['orderNo'] = i['orderNo'] if 'orderNo' in i else ""
parsed_dict['promoId'] = i['priceInfo']['adjustments'][0]['adjustmentId'] if i['priceInfo']['adjustments'][0]['adjustmentId'] != "" else ""
parsed_list.append(parsed_dict)
if 'shipments' in i:
for item in i['shipments']:
if item['priceInfo']['adjustments'] != []:
parsed_dict = {}
parsed_dict['orderNo'] = i['orderNo'] if 'orderNo' in i else ""
parsed_dict['promoId'] = item['priceInfo']['adjustments'][0]['adjustmentId']
parsed_list.append(parsed_dict)
return parsed_list
def fetch_custom(self, start_date, end_date):
start_time_obj = datetime.strptime(start_date, '%d-%m-%Y')
end_time_obj = datetime.strptime(end_date, '%d-%m-%Y')
coll_order_list = list(self.ord_col.find(
{"createdDate": {"$gte": start_time_obj,
"$lt": end_time_obj
},"status":"Submitted"}, {"_id": 0, "orderNo": 1, "orderDate": 1,
"shipments.priceInfo.adjustments": 1, "lineItems.priceInfo.adjustments": 1,
"lineItems.productName": 1, "priceInfo.adjustments": 1,"createdDate":1}))
for i in coll_order_list:
self.parse_to_list_df(i)
print(self.final_list)
# dto = datetime.now()
# grouped = df_order_single_prod_list.groupby("lineitemPromotionId")
# grouped_1 = df_order_single_prod_list.groupby("priceInfoPromotionId")
# for getting lineitem promotion list
# PromotionIds = []
# counts = []
# for getting priceinfo promotion list
# PromotionId = []
# count = []
# orderNo =[]
# for getting lineitem promotion list
# for count_items,group in grouped:
# PromotionIds.append(count_items)
# counts.append(len(group))
# orderNo.append(group['orderNo'].values)
# final_df = DataFrame({
# "PromotionIds": PromotionIds,
# "counts": counts,
# "orderNo": orderNo
# })
# for getting priceinfo promotion list
# for count_items,group in grouped_1:
# PromotionId.append(count_items)
# count.append(len(group))
# orderNo.append(group['orderNo'].values)
# final_df_2 = DataFrame({
# "PromotionId": PromotionId,
# "count": count,
# "orderNo": orderNo
# })
# for getting lineitem promotion list
# final_df1 =final_df.sort_values("counts", ascending=False)
# for getting priceinfo promotion list
# final_df2 =final_df_2.sort_values("count", ascending=False)
# for getting lineitem promotion list
# final_df1.to_excel(self.env_name + str(dto.year)+str(dto.month)+str(dto.day)+str(dto.hour)+str(dto.minute)+str(dto.second) + '_custom.xlsx',
# sheet_name="custom", index=False)
# for getting priceinfo promotion list
# final_df2.to_excel(self.env_name + str(dto.year)+str(dto.month)+str(dto.day)+str(dto.hour)+str(dto.minute)+str(dto.second) + '_custom.xlsx',
# sheet_name="custom", index=False)
if __name__ == "__main__":
fetch_from_db_aceprod = FetchFromDb(env_name="aceprod")
fetch_from_db_truprod = FetchFromDb(env_name="truprod")
fetch_from_db_kiabiprod = FetchFromDb(env_name="kiabiprod")
fetch_from_db_mnsprod = FetchFromDb(env_name="mnssgprod")
fetch_from_db_mnsprod = FetchFromDb(env_name="mnshkprod")
fetch_from_db_mnsprod = FetchFromDb(env_name="mnsuaeprod")
parser = argparse.ArgumentParser()
parser.add_argument('db', help='Enter which db to fetch. "uat" or "prod"')
parser.add_argument(
'fromdate', help='Enter the from date. Ex: "dd-mm-yyyy"')
parser.add_argument('todate', help='Enter the to date. Ex: "dd-mm-yyyy"')
args = parser.parse_args()
fetch_db = args.db
start_date = args.fromdate
end_date = args.todate
if fetch_db == "truprod":
fetch_from_db_truprod.fetch_custom(start_date, end_date)
elif fetch_db == "kiabiprod":
fetch_from_db_kiabiprod.fetch_custom(start_date, end_date)
elif fetch_db == "mnssgprod":
fetch_from_db_mnsprod.fetch_custom(start_date, end_date)
elif fetch_db == "aceprod":
fetch_from_db_aceprod.fetch_custom(start_date, end_date)
elif fetch_db == "mnshkprod":
fetch_from_db_mnsprod.fetch_custom(start_date, end_date)
elif fetch_db == "mnsuaeprod":
fetch_from_db_mnsprod.fetch_custom(start_date, end_date)Editor is loading...