a) What is the main intent of this program - why would someone have written it?

b) How might the output be relevant to the operation and management of Muesli AG?

 

1 ### Run parameters ### 2 control = '2022-12-02' 2 import pyodbc 3 import pandas as pd 5 1 2 def some func(r): 3 4 6 7 8 9 # Import Libraries 14 15 16 1 # Function definitions 1 # SQL 2 sqlInv= f""" 3 select trim(Location Code) as Loc, trim(Material_Code) as Mat, Date, Quantity as Closing 4 from Inventory_Counts 5 where Date >= '{control}' and Location Code != '02' 6 order by Location_Code, Material_Code, Date 7 2 8 9 sqlSales = f""" 10 select trim(si.Material_Code) as Mat, c. Region, so.Delivery_Date as Date, sum(si.Quantity) as Sold from Sales Order_Items si 11 12 13 if r in ['01', '62', '63', '64', '11', '12', '13', '14', '15']: return '02N' elif r in ['06', '08', '09', '16']: return '025' ****** elif r in ['05', '07', '10']: return '02W" else: return None. 33 34 inner join Sales_Orders so on so. Doc_Number = si.Doc_Number inner join Customers c on c. Customer_ID = so. Customer_ID where Delivery_Date> '{control}' group by c. Region, si.Material_Code, so.Delivery_Date # Main program cxn = pyodbc.connect("DRIVER={SQL Server}; SERVER=dlyle.database.windows.net; DATABASE=MuesliAG; UID-student; PWD=Viz (Data)") 3 4 dfSales = pd. read_sql (sqlSales, cxn, parse_dates = 'Date') 5 dfSales ['Loc'] = dfSales. Region.apply(some_func) 6 dfSales = dfSales.groupby(['Loc', 'Mat', 'Date']).sum().reset_index() 7 8 dfInv = pd.read_sql(sqlInv, cxn, parse_dates = 'Date') 9 10 varl 11 var2 = 12 for r in dfInv.itertuples(): 13 14 if dfInv.loc [r. Index, 'Loc'] == var1 and dfInv. loc [r. Index, 'Mat'] == var2: df Inv. loc [r. Index, 'Opening'] = dfInv.loc [r.Index-1, Closing'] else: 15 16 17 18 19 data pd.merge( 20 11 = var1 = dfInv.loc [r. Index, 'Loc'] var2 = dfInv.loc [r. Index, 'Mat'] df Inv [dfInv.Date > control], dfSales, 21 22 23 ).fillna (0) 24 25 dfMean = data[(data.Sold < data.Opening) & (data.Sold > 0)].groupby(['Loc', 'Mat']).Sold.mean() 26 data pd.merge( 27 28 = how="left', on=['Loc', 'Mat', 'Date'] data, dfMean, how="left', on=['Loc', 'Mat'], suffixes=[None, '_Mean'] 29 30 ) 31 32 dfout = ( data[ (data.Opening >= data.Sold_Mean) & (data.Sold_Mean > 0)].groupby(['Loc', 'Mat']). Date.count() /data.groupby(['Loc', 'Mat']).Date.count() 35 ) 36 dfout = pd.merge( 37 dfout, 38 dfMean, 39 how- 'outer', on=['Loc', 'Mat'] 40 ).fillna (0) 1 # Output 2 dfout.to_csv('output.csv', index_label=['Location', 'Material_Code'], header=['KPI1', 'KPI2'])