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
