Untitled
unknown
plain_text
4 years ago
8.3 kB
6
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...