def get_price_packs_report_df_2020(self): cursor, conn = simple_connection_to_base(base_path) logging.info( f'; Connecting to local base; OK ; get_price_packs_report_df_2020(self) ; Prices_to_sql()' ) logging.info( f'; Executing sql to get month-item-sum_packs data; OK ; get_price_packs_report_df_2020(self) ; Prices_to_sql()' ) cursor.execute( f"SELECT secondary_2020_629.month, secondary_2020_629.item_quadra, sum(secondary_2020_629.sales_packs) from secondary_2020_629 group by secondary_2020_629.month, secondary_2020_629.item_quadra" ) results = cursor.fetchall() sales_classifyed = [] for row in results: x = Sales_pack(row[0], row[1], row[2]) sales_classifyed.append(x) cursor = simple_connection_to_base(base_path) cursor.execute( f"SELECT items_actual_connected_2020.month, items_actual_connected_2020.sku, items_actual_connected_2020.brand, items_actual_connected_2020.cip from items_actual_connected_2020 where items_actual_connected_2020.year = '2020' and items_actual_connected_2020.year <> 'year' group by items_actual_connected_2020.month, items_actual_connected_2020.sku, items_actual_connected_2020.brand" ) results = cursor.fetchall() cip_classifyed = [] for row in results: x = Sales_price(row[0], row[1], row[2], row[3]) cip_classifyed.append(x) df_list_month = [] df_list_item = [] df_list_cip = [] df_list_packs = [] df_list_brand = [] for sale in sales_classifyed: for cip in cip_classifyed: if sale.month == cip.month and sale.item_quadra == cip.item_quadra: df_list_month.append(sale.month) df_list_item.append(sale.item_quadra) df_list_brand.append(cip.brand) df_list_cip.append(float(str(cip.cip).replace(",", "."))) df_list_packs.append(int(sale.sales_packs)) df_report = pd.DataFrame({ "month": df_list_month, "item": df_list_item, "brand": df_list_brand, "cip_price": df_list_cip, "packs_secondary_sales": df_list_packs }) report_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\price_packs_report\\' report_file = 'df_price_packs_2020.xlsx' full_path_report = report_path + report_file df_report['euro_secondary_sales'] = df_report['cip_price'] * df_report[ 'packs_secondary_sales'] df_report.to_excel(full_path_report, sheet_name='price_sales_upload_2020', index=False) logging.info( f'; Exporting report to {full_path_report} at sheet price_sales_upload_2020; OK ; get_price_packs_report_df_2020(self) ; Prices_to_sql()' ) return df_report
def kam_plan_to_sql(self): cursor,conn = simple_connection_to_base(base_path) cursor.execute("DROP TABLE kam_annual_sales_plan_final") cursor.execute( "CREATE TABLE IF NOT EXISTS kam_annual_sales_plan_final (month_local,item_quadra,code,manager_name,cip_kam,plan_packs,plan_euro_kam);") report_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\preparation\\raw\\' report_file = '2.kam_plan_by_manager.xlsx' full_path_report = report_path + report_file wb_obj = openpyxl.load_workbook(full_path_report) sheet_obj = wb_obj.active rows_count = str(sheet_obj.calculate_dimension()).rsplit(':') print(rows_count) rows_count = int(str(rows_count[1])[1:]) print(rows_count) string = [] classified_base_2021 = [] for row in range(2, rows_count + 1): str_ = [] for col in range(1, 8): cell_obj = sheet_obj.cell(row=row, column=col) str_.append(cell_obj.value) string.append(str_) y = Plan_kam_by_manager_classification() for i in string: x = y.classify(i) classified_base_2021.append(x) cursor.execute( "INSERT INTO kam_annual_sales_plan_final VALUES (?,?,?,?,?,?,?);", x) conn.commit() logging.info("; Exporting kam plan to database ; OK ; kam_plan_to_sql(self) ; Kam_plans_by_manager") print('OK')
def get_secondary_insight_2021(): cursor,conn = simple_connection_to_base(base_path) logging.info("; Connecting to base ; OK ; get_secondary_insight_2021() ; Independant method") cursor.execute( f"select secondary_2021_629.year, secondary_2021_629.ff_region, secondary_2021_629.month, secondary_2021_629.item_quadra, sum(secondary_2021_629.sales_packs) from secondary_2021_629 where secondary_2021_629.year <> 'Год' group by secondary_2021_629.year, secondary_2021_629.month, secondary_2021_629.ff_region, secondary_2021_629.item_quadra") reg = cursor.fetchall()[1:] logging.info( "; Running sql ; OK ; get_secondary_insight_2021() ; Independant method") packs = [] regions = [] year = [] month = [] sku = [] for i in reg: x = CDataframe_ff_classify(i[0], i[1], i[2], i[3], i[4]) regions.append(x.region) packs.append(float(x.packs_sold)) year.append(x.year) month.append(x.month) sku.append(x.item_quadra) df2 = pd.DataFrame({"year": year, "regions": regions, "month": month, "item_quadra": sku, "packs_sold": packs }) df2['total_packs'] = df2.groupby(['item_quadra', 'month', 'year'])['packs_sold'].transform(sum) df2['region_share'] = df2['packs_sold'] / df2['total_packs'] df2['sales_type'] = 'sale_in' df2[1:].to_excel('C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\1.dataframe_secondary_totals_2021.xlsx', index=False) logging.info("; Exporting Dataframe to 1.dataframe_secondary_totals_2021.xlsx ; OK ; get_secondary_insight_2021() ; Independant method") print("OK") df_2021_sale_in = df2[0:] return df_2021_sale_in
def get_kam_plan(self): structure_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\preparation\\raw\\' file = '2.structure_kam.xlsx' full_path = structure_path + file df = pd.read_excel(full_path) logging.info(f'; Reading KAM structure file ({file}) at {structure_path} ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') df_num = df.to_numpy() logging.info(f'; Converting file ({file}) at {structure_path} to numpy array ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') code_ = [] cursor,conn = simple_connection_to_base(base_path) logging.info(f'; Connecting to local sqlite base ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') cursor.execute("DROP TABLE kam_structure_final") logging.info(f'; Dropping kam_structure_final tab in base ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') cursor.execute( "CREATE TABLE IF NOT EXISTS kam_structure_final (code,name,territory,region,regional_manager_code,regional_manager_name,depart_sales_methond,boss_code,boss_name,plan_multiplicator);") logging.info(f'; Creating kam_structure_final tab in base ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') dict_code_name = {} for i in df_num: plan_multiplicator = i[9] boss_name = i[8] boss_code = i[7] depart_sales_methond = i[6] regional_manager_name = i[5] regional_manager_code = i[4] region = i[3] territory = i[2] name = i[1] code = i[0] x = Structure_kam(code, name, territory, region, regional_manager_code, regional_manager_name, depart_sales_methond, boss_code, boss_name, plan_multiplicator) dict_code_name.update({x.code: x.name}) code_.append(x.code) strin = [x.code, x.name, x.territory, x.region, x.regional_manager_code, x.regional_manager_name,x.depart_sales_methond,x.boss_code,x.boss_name,x.plan_multiplicator] cursor.execute( "INSERT INTO kam_structure_final VALUES (?,?,?,?,?,?,?,?,?,?);", strin) logging.info(f'; Inserting values to kam_structure_final tab in base ; OK ; get_kam_plan(self) ; Kam_plans_by_manager') conn.commit() logging.info(f'; Reading raw Kam plan by manager; OK ; get_kam_plan(self) ; Kam_plans_by_manager') kam_page_name_SF03 = "Старченко уп" items_dict, df_plan_by_manager_SF03 = self.get_df_plan_for_manager_by_manager(kam_page_name_SF03,dict_code_name) kam_page_name_SF01 = "Аношина уп" items_dict, df_plan_by_manager_SF01 = self.get_df_plan_for_manager_by_manager(kam_page_name_SF01,dict_code_name) kam_page_name_SF02 = "Ковальчук уп" items_dict, df_plan_by_manager_SF02 = self.get_df_plan_for_manager_by_manager(kam_page_name_SF02,dict_code_name) dfs_list = [df_plan_by_manager_SF01,df_plan_by_manager_SF02,df_plan_by_manager_SF03] merge = pd.DataFrame() for df in dfs_list: merge = merge.append(df,ignore_index=True) file_merged = '2.kam_plan_by_manager.xlsx' logging.info(f'; Creating a common plan file {file_merged} at {structure_path} ; OK; get_kam_plan(self) ; Kam_plans_by_manager') full_path_ = structure_path + file_merged merge.to_excel(full_path_,index=False) logging.info(f'; Creating dataframe and export it to excel; OK ; get_kam_plan(self) ; Kam_plans_by_manager') return merge
def get_df_plan_for_manager_by_manager(self,page,dict_code_name): cursor, conn = simple_connection_to_base(base_path) logging.info(f'; Connecting to local base ; OK ; get_df_plan_for_manager_by_manager(self,page,dict_code_name) ; Kam_plans_by_manager') cursor.execute( f"SELECT distinct items_actual_connected.sku_translit, items_actual_connected.sku from items_actual_connected where items_actual_connected.sku <> 'sku' and items_actual_connected.sku <> 'Випросал Б мазь 75г в тубе АКЦИЯ'") results = cursor.fetchall() logging.info(f'; Executing sql ; OK ; get_df_plan_for_manager_by_manager(self,page,dict_code_name) ; Kam_plans_by_manager') items_dict = {} for i in results: item_translit = i[0] item_quadra = i[1] x = {item_translit: item_quadra} items_dict.update(x) logging.info(f'; Creating item dictionary like translit\item_quadra ; OK ; get_df_plan_for_manager_by_manager(self,page,dict_code_name) ; Kam_plans_by_manager') structure_path_ = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\preparation\\raw\\kam\\' file_ = '2.total_plan_for_kam_department.xlsx' full_path_ = structure_path_ + file_ df_1_plan_raw = pd.read_excel(full_path_,page ) logging.info(f'; Connecting to {full_path_} ; OK ; get_df_plan_for_manager_by_manager(self,page,dict_code_name) ; Kam_plans_by_manager') logging.info(f'; Converting dataframe to numpy array ; OK ; get_df_plan_for_manager_by_manager(self,page,dict_code_name) ; Kam_plans_by_manager') df_1_num = df_1_plan_raw.to_numpy() df_list_month_local = [] df_list_item_quadra = [] df_list_code = [] df_list_cip_kam = [] df_list_plan_packs = [] df_list_plan_euro_kam = [] df_list_code_name = [] for i in df_1_num: month_local = i[0] item_quadra = items_dict[i[1]] code = i[2] cip_kam = i[3] plan_packs = i[4] plan_euro_kam = i[5] y = Plan_kam_by_manager(month_local, item_quadra, code, cip_kam, plan_packs, plan_euro_kam) df_list_month_local.append(y.month_local) df_list_item_quadra.append(y.item_translit) df_list_code.append(y.code) df_list_cip_kam.append(y.cip_kam) df_list_plan_packs.append(y.plan_packs) df_list_plan_euro_kam.append(y.plan_euro_kam) df_list_code_name.append(dict_code_name[y.code]) df_plan_kam = pd.DataFrame({ "month_local": df_list_month_local, "item_quadra": df_list_item_quadra, "code": df_list_code, "manager_name":df_list_code_name, "cip_kam": df_list_cip_kam, "plan_packs": df_list_plan_packs, "plan_euro_kam": df_list_plan_euro_kam }) logging.info(f'; Creating individual KAM plan dataframe ; OK ; get_df_plan_for_manager_by_manager(self,page,dict_code_name) ; Kam_plans_by_manager') return items_dict, df_plan_kam
def get_tertiary_insight(): cursor,conn = simple_connection_to_base(base_path) logging.info("; Connecting to base ; OK ; get_tertiary_insight() ; Independant method") cursor.execute( f"SELECT tertiary_by_reg.year, regions.region_quadra, ymm.month_local, items.item_quadra, tertiary_by_reg.quantity from tertiary_by_reg JOIN ymm on ymm.month = tertiary_by_reg.period_name and ymm.year = tertiary_by_reg.year JOIN items on items.item_proxima = tertiary_by_reg.full_medication_name join regions on regions.region_proxima = tertiary_by_reg.region group by tertiary_by_reg.year, regions.region_quadra, ymm.month_local, items.item_quadra") reg = cursor.fetchall() logging.info("; Running sql ; OK ; get_tertiary_insight() ; Independant method") packs = [] regions = [] year = [] month = [] sku = [] for i in reg: x = CDataframe_ff_classify(i[0], i[1], i[2], i[3], i[4]) regions.append(x.region) packs.append(str(x.packs_sold).replace(".", ",")) year.append(x.year) month.append(x.month) sku.append(x.item_quadra) df = pd.DataFrame({"year": year, "regions": regions, "month": month, "item_quadra": sku, "packs_sold": packs }) df.to_excel('C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\0.transform_tertiary_dataframe.xlsx', index=False) logging.info("; Export Dataframe to excel '0.transform_tertiary_dataframe.xlsx' ; OK ; get_tertiary_insight() ; Independant method") values = [] transform_file = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\0.transform_tertiary_dataframe.xlsm' import xlwings as xw wb = xw.Book(transform_file) app = wb.app logging.info("; Running VBA macro..... ; OK ; get_tertiary_insight() ; Independant method") macro_vba = app.macro(f"'{transform_file}'!open_tab") logging.info("; Opening tab ; OK ; get_tertiary_insight() ; Independant method") macro_vba() macro_vba = app.macro(f"'{transform_file}'!refresh") macro_vba() macro_vba = app.macro(f"'{transform_file}'!close_tab") logging.info("; Closing tab ; OK ; get_tertiary_insight() ; Independant method") macro_vba() logging.info("; VBA macro ; OK ; get_tertiary_insight() ; Independant method") wb.save() wb.close() df2 = pd.read_excel(transform_file) df2 = df2.rename(columns={'regions_quadra': 'regions'}) df2['total_packs'] = df2.groupby(['item_quadra', 'month', 'year'])['packs_sold'].transform(sum) df2['region_share'] = df2['packs_sold'] / df2['total_packs'] df2['sales_type'] = 'sale_out' df2.to_excel('C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\1.dataframe_tertiary_totals.xlsx', index=False) logging.info("; Exporting Dataframe to 1.dataframe_tertiary_totals.xlsx ; OK ; get_tertiary_insight() ; Independant method") print("OK") df_sale_out = df2[0:] return df_sale_out
def get_total_country_plan(self): structure_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\preparation\\raw\\' out_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\preparation\\raw\\' file = '1.total_country_by_region_plan_packs.xlsm' file_out = '1.total_country_by_region_plan_packs_clean.xlsx' full_path = structure_path + file full_path_out = out_path + file_out df = pd.read_excel(full_path,sheet_name='out') logging.info( f'; Reading file ({file}) at {structure_path} ; OK ; get_total_country_plan(self) ; Kam_plans_by_manager') df_num = df.to_numpy() logging.info( f'; Converting file ({file}) at {structure_path} to numpy array ; OK ; get_total_country_plan(self) ; Kam_plans_by_manager') cursor, conn = simple_connection_to_base(base_path) cursor.execute( f"SELECT distinct items_actual_connected.sku_translit, items_actual_connected.sku from items_actual_connected where items_actual_connected.sku <> 'sku' and items_actual_connected.sku <> 'Випросал Б мазь 75г в тубе АКЦИЯ'") results = cursor.fetchall() logging.info(f'; Executing sql ; OK ; get_total_country_plan(self) ; Kam_plans_by_manager') items_dict = {} for i in results: item_translit = i[0] item_quadra = i[1] x = {item_translit: item_quadra} items_dict.update(x) print(df_num) region = [] sku_translit = [] month_local = [] packs_planned = [] sku_quadra = [] for i in df_num: region.append(i[0]) sku_translit.append(i[1]) month_local.append(i[2]) packs_planned.append(i[3]) sku_quadra.append(items_dict[i[1]]) print(sku_quadra) df_final = pd.DataFrame({ "region":region, "sku_quadra":sku_quadra, "month":month_local, "planned_packs":packs_planned }) df_final.to_excel(full_path_out,index=False) #todo to put into results in GoogleDisk return df_final
def items_actual_to_base(self): logging.info( f'; Connecting to local base; OK ; items_actual_to_base(self) ; Prices_to_sql()' ) cursor, conn = simple_connection_to_base(base_path) cursor.execute("DROP TABLE items_actual_connected") logging.info( f'; Dropping tab items_actual_connected at local base; OK ; items_actual_to_base(self) ; Prices_to_sql()' ) cursor.execute( "CREATE TABLE IF NOT EXISTS items_actual_connected ('year', 'month', 'promotion', 'purpose', 'sku', 'sku_sales_report', 'sku_translit', 'brand', 'cip');" ) logging.info( f'; Creating tab items_actual_connected at local base; OK ; items_actual_to_base(self) ; Prices_to_sql()' ) dir_ = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\material_master\\" file1 = '1.item_actual_updated.xlsx' path = dir_ + file1 wb_obj = openpyxl.load_workbook(path) sheet_obj = wb_obj.active rows_count = str(sheet_obj.calculate_dimension()).rsplit(':') rows_count = int(str(rows_count[1])[1:]) string = [] classified_base_2021 = [] for row in range(1, rows_count): str_ = [] for col in range(1, 10): cell_obj = sheet_obj.cell(row=row, column=col) str_.append(cell_obj.value) string.append(str_) for i in string: x = Prices_to_sql() string_class = x.classify_prices(i) classified_base_2021.append(string_class) for string in classified_base_2021: cursor.execute( "INSERT INTO items_actual_connected VALUES (?,?,?,?,?,?,?,?,?);", string) logging.info( f'; Inserting values to tab items_actual_connected at local base from {file1} at {dir_}; OK ; items_actual_to_base(self) ; Prices_to_sql()' ) conn.commit() print('OK')
def read_kam_plan(self): logging.info("; Connecting to 'local_main_base.db' ; OK ; read_kam_plan(self) ; Kam_plans_by_manager") plan_list = [] cursor = simple_connection_to_base(base_path) cursor.execute(f"select distinct kam_plan.item_quadra, kam_plan.code, kam_plan.month, ymm.quarter, kam_plan.plan_packs, kam_plan.plan_euro from kam_plan join ymm on ymm.month_local = kam_plan.month") results = cursor.fetchall() for i in results: y_1 = str(i[0]) y_2 = str(i[1]) y_3 = str(i[2]) y_4 = str(i[3]) y_5 = str(i[4]) y_6 = str(i[5]).replace(',', '.') z = Kam_plan_download_structure(y_1, y_2, y_3,y_4, y_5, y_6) plan_list.append(z) logging.info("; Receiving kam plan list ; OK ; read_kam_plan(self) ; Kam_plans_by_manager") return plan_list