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_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 update_item(conn, sales: Tertiary_sales):
pass
with mySql.connect(host="localhost",
user="******",
password="******",
database="chinook") as conn:
cursor = conn.cursor()
# cursor.execute("UPDATE Tertiary_sales SET Name = 'TNMK' WHERE ArtistId = 276;")
cursor.execute(
f"UPDATE Tertiary_sales SET Name = '{items.brand}' WHERE ItemsId = {items.item};"
)
conn.commit()
def insert_item(conn, sales: Tertiary_sales):
with sqlite3.connect("tertiary_sales_database.db") as conn:
cursor = conn.cursor()
# cursor.execute("INSERT INTO Tertiary_sales VALUES (276,'Djamala');")
# cursor.execute(f"INSERT INTO Tertiary_sales VALUES (?,?)",(artist.item,f'{artist.brand}'))
cursor.execute(f"INSERT INTO sales VALUES (?,?,?,?,?,?,?,?)",
(f'({sales.year}', (f'{sales.month}'),
(f'{sales.item}'), (f'{sales.weight_penetration}'),
(f'{sales.sro}'), (f'{sales.quantity}'),
(f'{sales.volume_euro}')))
conn.commit()
def tert_reg_to_sqlite(self):
cursor, conn = simple_connection_to_base(base_path)
logging.info("; Connecting to 'local_main_base.db' ; OK")
cursor.execute("DROP TABLE tertiary_by_reg")
logging.info(
"; Dropping the table 'tertiary_by_reg' in 'local_main_base.db' ; OK"
)
cursor.execute(
"CREATE TABLE IF NOT EXISTS tertiary_by_reg (year, period_name,region, full_medication_name,market_org,quantity,volume,sro,weight_sro);"
)
path = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\tertiary_sales\\0.tertiary_by_region.xlsx"
logging.info(
"; Creating the table 'tertiary_by_reg' in 'local_main_base.db' ; OK"
)
wb_obj = openpyxl.load_workbook(path)
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)
logging.info(
f"; transform_tertiary_by_region' - number of rows - {rows_count} ; OK"
)
string = []
classified_base_2021 = []
for row in range(1, rows_count + 1):
str_ = []
for col in range(1, 10):
cell_obj = sheet_obj.cell(row=row, column=col)
str_.append(cell_obj.value)
string.append(str_)
logging.info("; Classifying received data ; OK")
for i in string[1:]:
x = Tertiary_workout()
string_class = x.classify(i)
classified_base_2021.append(string_class)
for string in classified_base_2021:
for row in string:
strin = [
row.year, row.period_name, row.region,
row.full_medication_name, row.market_org, row.quantity,
row.volume, row.sro, row.weight_sro
]
cursor.execute(
"INSERT INTO tertiary_by_reg VALUES (?,?,?,?,?,?,?,?,?);",
strin)
conn.commit()
logging.info("; Inserting received data to 'local_main_base.db' ; OK")
def tert_totals_to_sqlite(self):
cursor,conn = simple_connection_to_base(base_path)
logging.info("Connecting to 'local_main_base.db' - OK")
cursor.execute("DROP TABLE tertiary_totals")
logging.info("Dropping the table 'tertiary_totals' in 'local_main_base.db' - OK")
cursor.execute("CREATE TABLE IF NOT EXISTS tertiary_totals (year, period_name,period,trade_name,drug_form, brand, full_medication_name,market_org,quantity,volume,sro,weight_sro,penetration, weighted_penetration);")
path = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\tertiary_sales\\tertiary_month_actual.xlsx"
logging.info("Creating the table 'tertiary_totals' in 'local_main_base.db' - OK")
wb_obj = openpyxl.load_workbook(path)
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)
logging.info(f"transform_tertiary_by_region' - number of rows - {rows_count} - OK")
string = []
classified_base_2021 = []
for row in range(1, rows_count + 1):
str_ = []
for col in range(1, 15):
cell_obj = sheet_obj.cell(row=row, column=col)
str_.append(cell_obj.value)
string.append(str_)
logging.info("Classifying received data - OK")
for i in string[1:]:
trade_name = i[4]
brand = i[6]
drug_form = i[5]
period = i[2]
weight_sro = i[11]
sro = i[10]
volume = i[9]
quantity = i[8]
market_org = i[7]
full_medication_name = i[3]
period_name = i[1]
year = i[0]
weighted_penetration = i[13]
penetration = i[12]
x = Tertiary_by_hub_totals_download_structure(year, period_name,period,trade_name,drug_form, brand, full_medication_name,market_org,quantity,volume,sro,weight_sro,penetration, weighted_penetration)
string_class = x
classified_base_2021.append(string_class)
for row in classified_base_2021:
strin = [row.year, row.period_name,row.period,row.trade_name,row.drug_form, row.brand, row.full_medication_name,row.market_org,row.quantity,row.volume,row.sro,row.weight_sro,row.penetration, row.weighted_penetration]
cursor.execute("INSERT INTO tertiary_totals VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);",strin)
conn.commit()
logging.info("Inserting received data to 'local_main_base.db' - OK")
def ff_structure_to_sql(self):
cursor, conn = simple_connection_to_base(base_path)
logging.info(f'; Connecting to local base ; OK')
cursor.execute("DROP TABLE ff_structure_final")
logging.info(f'; Dropping ff_structure_final tab at local base ; OK')
cursor.execute(
"CREATE TABLE IF NOT EXISTS ff_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 ff_structure_final tab at local base ; OK')
report_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\preparation\\raw\\'
report_file = '1.structure.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, 11):
cell_obj = sheet_obj.cell(row=row, column=col)
str_.append(cell_obj.value)
string.append(str_)
for i in string:
x = Structure_ff(i[0], i[1], i[2], i[3], i[4], i[5], i[6], i[7],
i[8], i[9])
classified_base_2021.append(x)
for string in classified_base_2021:
# for row in string:
strin = [
string.code, string.name, string.territory, string.region,
string.regional_manager_code, string.regional_manager_name,
string.depart_sales_methond, string.boss_code,
string.boss_name, string.plan_multiplicator
]
cursor.execute(
"INSERT INTO ff_structure_final VALUES (?,?,?,?,?,?,?,?,?,?);",
strin)
logging.info(
f'; Inserting values to ff_structure_final tab at local base ; OK')
conn.commit()
print('OK')
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 ff_plan_to_sql(self):
plan_ff = self.get_splitted_plan_by_rep()
cursor, conn = simple_connection_to_base(base_path)
logging.info(f'; Connecting to local base ; OK')
cursor.execute("DROP TABLE ff_annual_sales_plan_final")
logging.info(f'; Dropping ff_annual_sales_plan_final tab at base ; OK')
cursor.execute(
"CREATE TABLE IF NOT EXISTS ff_annual_sales_plan_final (region_quadra, item_quadra, month_local, plan_rep,code, name);"
)
logging.info(f'; Creating ff_annual_sales_plan_final tab at base ; OK')
report_path = 'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\planning\\plan_by_rep\\'
report_file = 'plan_ready_ff.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, 7):
cell_obj = sheet_obj.cell(row=row, column=col)
str_.append(cell_obj.value)
string.append(str_)
for i in string:
x = Plan_ff_sql(i[0], i[1], i[2], i[3], i[4], i[5])
classified_base_2021.append(x)
for string in classified_base_2021:
# for row in string:
strin = [
string.region_quadra, string.item_quadra, string.month_local,
string.plan_packs, string.code, string.name
]
cursor.execute(
"INSERT INTO ff_annual_sales_plan_final VALUES (?,?,?,?,?,?);",
strin)
logging.info(
f'; Inserting values to ff_annual_sales_plan_final tab at base ; OK'
)
conn.commit()
print('OK')
def normalize_weekly_tertiary(self):
filename1 = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\transform_files\\0.tertiary_week_normalized.xlsx"
cursor, conn = simple_connection_to_base(base_path)
cursor.execute(f"SELECT distinct items.item_proxima from items")
results = cursor.fetchall()
list_crm = []
for i in results:
list_crm.append(i)
filename = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\transform_files\\0.tertiary_week_normalized.xlsx"
path = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\tertiary_sales\\tertiary_week_actual.xlsx"
wb_obj = openpyxl.load_workbook(path)
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)
logging.info(f"; number of rows - {rows_count} ; OK")
string = []
classified_base_2021 = []
for row in range(1, rows_count + 1):
str_ = []
for col in range(1, 70):
cell_obj = sheet_obj.cell(row=row, column=col)
str_.append(cell_obj.value)
string.append(str_)
for i in string[2:]:
count = 0
item_proxima_dirty = ''
index_ = process.extractOne(str(i[0])[7:], list_crm)
if index_[1] > 95:
item_proxima_dirty = str(str(i[0])[8:]).replace("('",
"").replace(
"',)", "")
else:
count += 1
w01 = i[2]
w02 = i[3]
w03 = i[4]
w04 = i[5]
w05 = i[6]
w06 = i[7]
w07 = i[8]
w08 = i[9]
w09 = i[10]
w10 = i[11]
w11 = i[12]
w12 = i[13]
w13 = i[14]
w14 = i[15]
w15 = i[16]
w16 = i[17]
w17 = i[18]
w18 = i[19]
w19 = i[20]
w20 = i[21]
w21 = i[22]
w22 = i[23]
w23 = i[24]
w24 = i[25]
w25 = i[26]
w26 = i[27]
w27 = i[28]
w28 = i[29]
w29 = i[30]
w30 = i[31]
w31 = i[32]
w32 = i[33]
w33 = i[34]
w34 = i[35]
w35 = i[36]
w36 = i[37]
w37 = i[38]
w38 = i[39]
w39 = i[40]
w40 = i[41]
w41 = i[42]
w42 = i[43]
w43 = i[44]
w44 = i[45]
w45 = i[46]
w46 = i[47]
w47 = i[48]
w48 = i[49]
w49 = i[50]
w50 = i[51]
w51 = i[52]
w52 = i[53]
w53 = i[54]
x = Tertiary_by_week_download_structure_for_power_bi(
item_proxima_dirty, w01, w02, w03, w04, w05, w06, w07, w08,
w09, w10, w11, w12, w13, w14, w15, w16, w17, w18, w19, w20,
w21, w22, w23, w24, w25, w26, w27, w28, w29, w30, w31, w32,
w33, w34, w35, w36, w37, w38, w39, w40, w41, w42, w43, w44,
w45, w46, w47, w48, w49, w50, w51, w52, w53)
string_class = x
classified_base_2021.append(string_class)
cursor, conn = simple_connection_to_base(base_path)
cursor.execute("DROP TABLE tertiary_week_normalized")
logging.info(
"; Dropping the table 'tertiary_week_normalized' in 'local_main_base.db' ; OK"
)
cursor.execute(
"CREATE TABLE IF NOT EXISTS tertiary_week_normalized (item_proxima_dirty,'01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53');"
)
path = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\transform_files\\0.tertiary_week_normalized.xlsx"
logging.info(
"; Creating the table 'tertiary_week_normalized' in 'local_main_base.db' ; OK"
)
for row in classified_base_2021:
strin = [
row.item_proxima_dirty, row.w01, row.w02, row.w03, row.w04,
row.w05, row.w06, row.w07, row.w08, row.w09, row.w10, row.w11,
row.w12, row.w13, row.w14, row.w15, row.w16, row.w17, row.w18,
row.w19, row.w20, row.w21, row.w22, row.w23, row.w24, row.w25,
row.w26, row.w27, row.w28, row.w29, row.w30, row.w31, row.w32,
row.w33, row.w34, row.w35, row.w36, row.w37, row.w38, row.w39,
row.w40, row.w41, row.w42, row.w43, row.w44, row.w45, row.w46,
row.w47, row.w48, row.w49, row.w50, row.w51, row.w52, row.w53
]
cursor.execute(
"INSERT INTO tertiary_week_normalized VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",
strin)
conn.commit()
logging.info("; Inserting received data to 'local_main_base.db' ; OK")
cursor, conn = simple_connection_to_base(base_path)
cursor.execute(
f"select items.item_quadra, tertiary_week_normalized.'01',tertiary_week_normalized.'02',tertiary_week_normalized.'03',tertiary_week_normalized.'04',tertiary_week_normalized.'05',tertiary_week_normalized.'06',tertiary_week_normalized.'07',tertiary_week_normalized.'08',tertiary_week_normalized.'09',tertiary_week_normalized.'10',tertiary_week_normalized.'11',tertiary_week_normalized.'12',tertiary_week_normalized.'13',tertiary_week_normalized.'14',tertiary_week_normalized.'15',tertiary_week_normalized.'16',tertiary_week_normalized.'17',tertiary_week_normalized.'18', "
+
"tertiary_week_normalized.'19',tertiary_week_normalized.'20',tertiary_week_normalized.'21',tertiary_week_normalized.'22',tertiary_week_normalized.'23',tertiary_week_normalized.'24',tertiary_week_normalized.'25',tertiary_week_normalized.'26',tertiary_week_normalized.'27',tertiary_week_normalized.'28',tertiary_week_normalized.'29',tertiary_week_normalized.'30',tertiary_week_normalized.'31',tertiary_week_normalized.'32',tertiary_week_normalized.'33',tertiary_week_normalized.'34',tertiary_week_normalized.'35',tertiary_week_normalized.'36', "
+
"tertiary_week_normalized.'37',tertiary_week_normalized.'38',tertiary_week_normalized.'39',tertiary_week_normalized.'40',tertiary_week_normalized.'41',tertiary_week_normalized.'42',tertiary_week_normalized.'43',tertiary_week_normalized.'44',tertiary_week_normalized.'45',tertiary_week_normalized.'46',tertiary_week_normalized.'47',tertiary_week_normalized.'48',tertiary_week_normalized.'49',tertiary_week_normalized.'50',tertiary_week_normalized.'51',tertiary_week_normalized.'52',tertiary_week_normalized.'53' from items join tertiary_week_normalized on tertiary_week_normalized.item_proxima_dirty = items.item_proxima group by items.item_quadra"
)
normalized_ = cursor.fetchall()
workbook1 = xlsxwriter.Workbook(filename1)
logging.info(f"; Opening f'{filename1}' for writing ; OK")
worksheet1 = workbook1.add_worksheet()
# Widen the first column to make the text clearer.
# worksheet.set_column('A:A', 20)
bold = workbook1.add_format({'bold': True}, )
worksheet1.write('A1', "item_proxima", bold)
worksheet1.write('B1', "01", bold)
worksheet1.write('C1', "02", bold)
worksheet1.write('D1', "03", bold)
worksheet1.write('E1', "04", bold)
worksheet1.write('F1', "05", bold)
worksheet1.write('G1', "06", bold)
worksheet1.write('H1', "07", bold)
worksheet1.write('I1', "08", bold)
worksheet1.write('J1', "09", bold)
worksheet1.write('K1', "10", bold)
worksheet1.write('L1', "11", bold)
worksheet1.write('M1', "12", bold)
worksheet1.write('N1', "13", bold)
worksheet1.write('O1', "14", bold)
worksheet1.write('P1', "15", bold)
worksheet1.write('Q1', "16", bold)
worksheet1.write('R1', "17", bold)
worksheet1.write('S1', "18", bold)
worksheet1.write('T1', "19", bold)
worksheet1.write('U1', "20", bold)
worksheet1.write('V1', "21", bold)
worksheet1.write('W1', "22", bold)
worksheet1.write('X1', "23", bold)
worksheet1.write('Y1', "24", bold)
worksheet1.write('Z1', "25", bold)
worksheet1.write('AA1', "26", bold)
worksheet1.write('AB1', "27", bold)
worksheet1.write('AC1', "28", bold)
worksheet1.write('AD1', "29", bold)
worksheet1.write('AE1', "30", bold)
worksheet1.write('AF1', "31", bold)
worksheet1.write('AG1', "32", bold)
worksheet1.write('AH1', "33", bold)
worksheet1.write('AI1', "34", bold)
worksheet1.write('AJ1', "35", bold)
worksheet1.write('AK1', "36", bold)
worksheet1.write('AL1', "37", bold)
worksheet1.write('AM1', "38", bold)
worksheet1.write('AN1', "39", bold)
worksheet1.write('AO1', "40", bold)
worksheet1.write('AP1', "41", bold)
worksheet1.write('AQ1', "42", bold)
worksheet1.write('AR1', "43", bold)
worksheet1.write('AS1', "44", bold)
worksheet1.write('AT1', "45", bold)
worksheet1.write('AU1', "46", bold)
worksheet1.write('AV1', "47", bold)
worksheet1.write('AW1', "48", bold)
worksheet1.write('AX1', "49", bold)
worksheet1.write('AY1', "50", bold)
worksheet1.write('AZ1', "51", bold)
worksheet1.write('BA1', "52", bold)
worksheet1.write('BB1', "53", bold)
logging.info("; Adding headers ; OK")
row_index = 1
for item in normalized_:
print(item)
worksheet1.write(int(row_index), int(0), str(item[0]))
worksheet1.write(int(row_index), int(1),
str(item[1]).replace(".", ","))
worksheet1.write(int(row_index), int(2),
str(item[2]).replace(".", ","))
worksheet1.write(int(row_index), int(3),
str(item[3]).replace(".", ","))
worksheet1.write(int(row_index), int(4),
str(item[4]).replace(".", ","))
worksheet1.write(int(row_index), int(5),
str(item[5]).replace(".", ","))
worksheet1.write(int(row_index), int(6),
str(item[6]).replace(".", ","))
worksheet1.write(int(row_index), int(7),
str(item[7]).replace(".", ","))
worksheet1.write(int(row_index), int(8),
str(item[8]).replace(".", ","))
worksheet1.write(int(row_index), int(9),
str(item[9]).replace(".", ","))
worksheet1.write(int(row_index), int(10),
str(item[10]).replace(".", ","))
worksheet1.write(int(row_index), int(11),
str(item[11]).replace(".", ","))
worksheet1.write(int(row_index), int(12),
str(item[12]).replace(".", ","))
worksheet1.write(int(row_index), int(13),
str(item[13]).replace(".", ","))
worksheet1.write(int(row_index), int(14),
str(item[14]).replace(".", ","))
worksheet1.write(int(row_index), int(15),
str(item[15]).replace(".", ","))
worksheet1.write(int(row_index), int(16),
str(item[16]).replace(".", ","))
worksheet1.write(int(row_index), int(17),
str(item[17]).replace(".", ","))
worksheet1.write(int(row_index), int(18),
str(item[18]).replace(".", ","))
worksheet1.write(int(row_index), int(19),
str(item[19]).replace(".", ","))
worksheet1.write(int(row_index), int(20),
str(item[20]).replace(".", ","))
worksheet1.write(int(row_index), int(21),
str(item[21]).replace(".", ","))
worksheet1.write(int(row_index), int(22),
str(item[22]).replace(".", ","))
worksheet1.write(int(row_index), int(23),
str(item[23]).replace(".", ","))
worksheet1.write(int(row_index), int(24),
str(item[24]).replace(".", ","))
worksheet1.write(int(row_index), int(25),
str(item[25]).replace(".", ","))
worksheet1.write(int(row_index), int(26),
str(item[26]).replace(".", ","))
worksheet1.write(int(row_index), int(27),
str(item[27]).replace(".", ","))
worksheet1.write(int(row_index), int(28),
str(item[28]).replace(".", ","))
worksheet1.write(int(row_index), int(29),
str(item[29]).replace(".", ","))
worksheet1.write(int(row_index), int(30),
str(item[30]).replace(".", ","))
worksheet1.write(int(row_index), int(31),
str(item[31]).replace(".", ","))
worksheet1.write(int(row_index), int(32),
str(item[32]).replace(".", ","))
worksheet1.write(int(row_index), int(33),
str(item[33]).replace(".", ","))
worksheet1.write(int(row_index), int(34),
str(item[34]).replace(".", ","))
worksheet1.write(int(row_index), int(35),
str(item[35]).replace(".", ","))
worksheet1.write(int(row_index), int(36),
str(item[36]).replace(".", ","))
worksheet1.write(int(row_index), int(37),
str(item[37]).replace(".", ","))
worksheet1.write(int(row_index), int(38),
str(item[38]).replace(".", ","))
worksheet1.write(int(row_index), int(39),
str(item[39]).replace(".", ","))
worksheet1.write(int(row_index), int(40),
str(item[40]).replace(".", ","))
worksheet1.write(int(row_index), int(41),
str(item[41]).replace(".", ","))
worksheet1.write(int(row_index), int(42),
str(item[42]).replace(".", ","))
worksheet1.write(int(row_index), int(43),
str(item[43]).replace(".", ","))
worksheet1.write(int(row_index), int(44),
str(item[44]).replace(".", ","))
worksheet1.write(int(row_index), int(45),
str(item[45]).replace(".", ","))
worksheet1.write(int(row_index), int(46),
str(item[46]).replace(".", ","))
worksheet1.write(int(row_index), int(47),
str(item[47]).replace(".", ","))
worksheet1.write(int(row_index), int(48),
str(item[48]).replace(".", ","))
worksheet1.write(int(row_index), int(49),
str(item[49]).replace(".", ","))
worksheet1.write(int(row_index), int(50),
str(item[50]).replace(".", ","))
worksheet1.write(int(row_index), int(51),
str(item[51]).replace(".", ","))
worksheet1.write(int(row_index), int(52),
str(item[52]).replace(".", ","))
worksheet1.write(int(row_index), int(53),
str(item[53]).replace(".", ","))
row_index += 1
workbook1.close()
def classified_stock_to_sqlite(self):
x = TestOnestockwoselecting()
x.test_onestockwoselecting()
time.sleep(30)
list_of_files = glob.glob(
'C:\\Users\\Anastasia Siedykh\\Downloads\\*.xlsx'
) # * means all if need specific format then *.csv
latest_file = max(list_of_files, key=os.path.getctime)
os.rename(
latest_file,
'C:\\Users\\Anastasia Siedykh\\Downloads\\stock_wh_to_merge.xlsx')
os.replace(
'C:\\Users\\Anastasia Siedykh\\Downloads\\stock_wh_to_merge.xlsx',
'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_wh_to_merge.xlsx'
)
x.merge_stock_and_all_years_secondary()
os.remove(
'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_wh_to_merge.xlsx'
)
os.remove(
'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_at_distributors_wh.xlsx'
)
os.rename(
'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_at_distributors_wh_new.xlsx',
'C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_at_distributors_wh.xlsx'
)
print('Stock file is updated')
cursor, conn = simple_connection_to_base(base_path)
logging.info("; Connecting to 'local_main_base.db' ; OK")
cursor.execute("DROP TABLE stock_at_distributors_wh")
logging.info(
"; Dropping the table 'stock_at_distributors_wh' at 'local_main_base.db' ; OK"
)
cursor.execute(
"CREATE TABLE IF NOT EXISTS stock_at_distributors_wh (week,date,country_region,distributor,item_quadra,quantity_packs,amount_euro,num);"
)
logging.info(
"; Creating the table 'stock_at_distributors_wh' at 'local_main_base.db' ; OK"
)
path = "C:\\Users\\Anastasia Siedykh\\Documents\\Backup\\KPI report\\MODULE SET V6\\raw_data_files\\stock_distributors_wh\\stock_at_distributors_wh.xlsx"
logging.info("; Opening the 'stock_at_distributors_wh.xlsx' ; OK")
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:])
logging.info(
f"; Calculating rows at 'stock_at_distributors_wh.xlsx' - number of rows - {rows_count} ; OK"
)
string = []
classified_base_2021 = []
for row in range(2, rows_count + 1):
str_ = []
for col in range(1, 9):
cell_obj = sheet_obj.cell(row=row, column=col)
if cell_obj is not None:
str_.append(cell_obj.value)
string.append(str_)
logging.info(f"; Launching stock classification ; OK")
for i in string:
x = CStock_quadra_workout()
string_class = x.classify_stock_quadra(i)
classified_base_2021.append(string_class)
logging.info(f"; Stock classified ; OK")
logging.info(f"; Inserting stock data to base ; OK")
for string in classified_base_2021:
for row in string:
strin = [
row.week, row.date, row.country_region, row.distributor,
row.item_quadra, row.quantity_packs, row.amount_euro,
row.num
]
cursor.execute(
"INSERT INTO stock_at_distributors_wh VALUES (?,?,?,?,?,?,?,?);",
strin)
conn.commit()
print('OK, check the base')
return classified_base_2021
def delete_item(conn):
with sqlite3.connect("tertiary_sales_database.db") as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM sales WHERE sales.item_id = '(90'")
#cursor.execute(f"DELETE FROM Tertiary_sales WHERE ItemsId = {sales.item}")
conn.commit()
