def get_No_Stock_Records():
excel_data_df = pd.read_excel('Data/NOStock.xlsx',
sheet_name='Sheet1',
usecols=['BSL NO', 'BRAND'])
bslno = ofn.create_dup_count_list(excel_data_df, 'BSL NO')
brand = ofn.create_dup_count_list(excel_data_df, 'BRAND')
wb = xlrd.open_workbook('Data/NOStock.xlsx')
sh = wb.sheet_by_name('Sheet1')
# print('No Sales dataset Start printing in HTML')
tabletd = ""
for i in range(1, sh.nrows):
tabletd = tabletd + "<tr>\n"
for j in range(0, 1):
# BSL NO
if (bslno[i - 1] != 0):
tabletd = tabletd + "<td class=\"serial\" rowspan=\"" + str(
bslno[i - 1]) + "\"> " + str(int(sh.cell_value(
i, j))) + "</td>\n"
for j in range(1, 2):
# Brand
if (brand[i - 1] != 0):
tabletd = tabletd + "<td class=\"brandtd\" rowspan=\"" + str(
brand[i - 1]) + "\">" + str(sh.cell_value(i,
j)) + "</td>\n"
for j in range(2, 3):
# ITemNo
tabletd = tabletd + "<td class=\"serial\">" + str(
int((sh.cell_value(i, j)))) + "</td>\n"
for j in range(3, 4):
# item name
tabletd = tabletd + "<td class=\"descriptiontd\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(4, 5):
# unit
tabletd = tabletd + "<td class=\"number_style\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(5, 6):
# Total Ordered
tabletd = tabletd + "<td class=\"number_style\">" + str(
ofn.number_style(str(int(sh.cell_value(i, j))))) + "</td>\n"
for j in range(6, 7):
# Estimated Sales
tabletd = tabletd + "<td class=\"number_style\">" + str(
ofn.number_style(str(int(sh.cell_value(i, j))))) + "</td>\n"
table1 = tabletd + "</tr>\n"
print("15. No Stock Item: Last 3 Months table created \n")
return table1
def item_stock_days():
excel_data_df = pd.read_excel('Data/item_wise_stock_days.xlsx',
sheet_name='Sheet1',
usecols=['BSL NO', 'BRAND'])
# bslno = excel_data_df['BSL NO'].tolist()
# brand = excel_data_df['BRAND'].tolist()
bslno = ofn.create_dup_count_list(excel_data_df, 'BSL NO')
brand = ofn.create_dup_count_list(excel_data_df, 'BRAND')
wb = xlrd.open_workbook('Data/item_wise_stock_days.xlsx')
sh = wb.sheet_by_name('Sheet1')
# print('No Sales dataset Start printing in HTML')
#
tabletd = ""
for i in range(1, sh.nrows):
tabletd = tabletd + "<tr>\n"
for j in range(0, 1):
# BSL NO
if (bslno[i - 1] != 0):
tabletd = tabletd + "<td class=\"serial\" rowspan=\"" + str(
bslno[i - 1]) + "\"> " + str(int(sh.cell_value(
i, j))) + "</td>\n"
for j in range(1, 2):
# Brand
if (brand[i - 1] != 0):
tabletd = tabletd + "<td class=\"brandtd\" rowspan=\"" + str(
brand[i - 1]) + "\">" + str(sh.cell_value(i,
j)) + "</td>\n"
for j in range(2, 3):
# ITemNo
tabletd = tabletd + "<td class=\"serial\">" + str(
int((sh.cell_value(i, j)))) + "</td>"
for j in range(3, 4):
# item name
tabletd = tabletd + "<td class=\"serial\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(4, 5):
# unit
tabletd = tabletd + "<td class=\"remarks\">" + str(
(sh.cell_value(i, j))) + "</td>"
for j in range(5, 36):
# Estimated Sales
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
set_color(sh.cell_value(i, j)) + "\">" + seperator(sh.cell_value(i, j)) + "</td>\n"
table6 = tabletd + "</tr>\n"
print('16. Item wise stock days table created\n')
return table6
def item_wise_yesterday_no_sales_Records():
df_y_scock = pd.read_excel('Data/yesterday_no_sales.xlsx')
excel_data_df = pd.read_excel(
'Data/yesterday_no_sales.xlsx',
sheet_name='Sheet1',
usecols=['BSL NO', 'BRAND', 'ISL NO', 'Item Name', 'UOM'])
bslno = ofn.create_dup_count_list(excel_data_df, 'BSL NO')
brand = ofn.create_dup_count_list(excel_data_df, 'BRAND')
wb = xlrd.open_workbook('Data/yesterday_no_sales.xlsx')
sh = wb.sheet_by_name('Sheet1')
print('Yesterday No sales ')
#
tabletd = ""
for i in range(1, sh.nrows):
tabletd = tabletd + "<tr>\n"
for j in range(0, 1):
# BSL NO
if (bslno[i - 1] != 0):
tabletd = tabletd + "<td class=\"serial\" rowspan=\"" + str(
bslno[i - 1]) + "\"> " + str(int(sh.cell_value(
i, j))) + "</td>\n"
for j in range(1, 2):
# Brand
if (brand[i - 1] != 0):
tabletd = tabletd + "<td class=\"brandtd\" rowspan=\"" + str(
brand[i - 1]) + "\">" + str(sh.cell_value(i,
j)) + "</td>\n"
for j in range(2, 3):
# ITemNo
tabletd = tabletd + "<td class=\"central\">" + str(
int((sh.cell_value(i, j)))) + "</td>\n"
for j in range(3, 4):
# item name
tabletd = tabletd + "<td class=\"descriptiontd\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(4, 5):
# unit
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
table1 = tabletd + "</tr>\n"
print("item wise yesterday No sale table Created")
return table1
def aging_stock_summary_status(name):
df = pd.read_sql_query("""select *
from
(select * from
(select Aging as Aging_Days,BRAND,count (distinct T2.ITEMNO) as TotalSKU,itemname , sum(QTYAVAIL) AS TotalStock, T1.AUDTORG from
(select ITEMNO,case when [location] = ('4001') then 'SKF Mirpur Plant'
when [location] = ('4005') then 'SKF Tongi Plant'
when [location] = ('4016') then 'SKF Rupganj Plant'
else AUDTORG end as AUDTORG,CASE
WHEN AGEING = 'Within 15 Days' THEN 'Within 15 Days'
WHEN AGEING = 'Within 30 Days' THEN 'Within 30 Days'
WHEN AGEING = 'Within 60 Days' THEN 'Within 60 Days'
WHEN AGEING = 'Within 90 Days' THEN 'Within 90 Days'
WHEN AGEING = 'Within 180 Days' THEN 'Within 180 Days'
WHEN AGEING = 'Within 210 Days' THEN 'Within 210 Days'
WHEN AGEING = 'Expired' THEN 'Expired'
END as Aging
,QTYAVAIL,EXPIRYDATE from ICStockCurrent_Lot WHERE AUDTORG<>'SKFDAT') as T1
LEFT JOIN
(select ITEMNO,ITEMNAME,BRAND,PACKSIZE,GPMNAME from PRINFOSKF)as T2
ON (T1.ITEMNO=T2.ITEMNO)
WHERE T2.GPMNAME like ? AND Aging in ('Within 15 Days','Within 30 Days', 'Within 60 Days', 'Within 90 Days', 'Within 180 Days', 'Within 210 Days', 'Expired')
Group by BRAND, Aging,AUDTORG, itemname
)as T3
) t
pivot
(sum(TotalStock)
for AUDTORG in ([BOGSKF],[BSLSKF],[COMSKF],[COXSKF],[CTGSKF],[CTNSKF],[DNJSKF],[FENSKF],[FRDSKF],
[GZPSKF],[HZJSKF],[JESSKF],[KHLSKF],[KRNSKF],[KSGSKF],[KUSSKF],[MHKSKF],[MIRSKF],[MLVSKF],[MOTSKF],
[MYMSKF],[NAJSKF],[NOKSKF],[PATSKF],[PBNSKF],[RAJSKF],[RNGSKF],[SAVSKF],[SYLSKF],[TGLSKF],[VRBSKF],[SKF Rupganj Plant],[SKF Mirpur Plant],[SKF Tongi Plant]
)) AS piv
order by CASE
WHEN Aging_Days = 'Within 15 Days' THEN 1
WHEN Aging_Days = 'Within 30 Days' THEN 2
WHEN Aging_Days = 'Within 60 Days' THEN 3
WHEN Aging_Days = 'Within 90 Days' THEN 4
WHEN Aging_Days = 'Within 180 Days' THEN 5
WHEN Aging_Days = 'Within 210 Days' THEN 6
WHEN Aging_Days = 'Expired' THEN 7
END
""",
connection,
params={name})
# column_values = range(1, len(df))
# print(column_values)
# sys.exit()
# df2 = df.fillna(0)
writer = pd.ExcelWriter('Data/aging_stock_summary.xlsx', engine='openpyxl')
wb = writer.book
df.to_excel(writer, index=False)
# print('a')
wb.save('Data/aging_stock_summary.xlsx')
# print('data')
cols_aging = [0]
df_age = pd.read_excel('Data/aging_stock_summary.xlsx', usecols=cols_aging)
# print(df_age)
df_age = df_age.replace(['Within 15 Days'], '1 - 15 Days')
df_age = df_age.replace(['Within 30 Days'], '16 - 30 Days')
df_age = df_age.replace(['Within 60 Days'], '31 - 60 Days')
df_age = df_age.replace(['Within 90 Days'], '61 - 90 Days')
df_age = df_age.replace(['Within 180 Days'], '91 - 180 Days')
df_age = df_age.replace(['Within 210 Days'], '181 - 210 Days')
# print(df_age)
cols_brand = [1]
df_bran = pd.read_excel('Data/aging_stock_summary.xlsx',
usecols=cols_brand)
cols_all = [
2, 3, 4, 5, 6, 7, 8, 9, 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
]
df_al = pd.read_excel('Data/aging_stock_summary.xlsx', usecols=cols_all)
writer = pd.ExcelWriter('Data/aging_stock_summary_copy.xlsx',
engine='xlsxwriter')
df_age.to_excel(writer,
sheet_name='Sheet1',
index=False,
startcol=0,
startrow=0)
df_bran.to_excel(writer,
sheet_name='Sheet1',
index=False,
startcol=1,
startrow=0)
df_al.to_excel(writer,
sheet_name='Sheet1',
index=False,
startcol=2,
startrow=0)
# worksheet = writer.sheets['Sheet1']
# worksheet.set_column('D:D', 40)
# worksheet.set_column('F:AZ', 20)
# worksheet.set_column('BA:CE', 15)
# worksheet.set_column('CF:CJ', 20)
writer.save()
# sys.exit()
#
column_values = range(1, len(df) + 1)
# df_new = pd.DataFrame({'BSL NO': column_values})
df_new = pd.DataFrame({'ISL NO': column_values})
wb = load_workbook('Data/aging_stock_summary_copy.xlsx')
ws = wb['Sheet1']
# for index, row in df_new.iterrows():
# cell = 'B%d' % (index + 2)
# ws[cell] = row[0]
#
# ws.cell(row=1, column=2).value = 'BSL NO'
for index, row in df_new.iterrows():
cell = 'C%d' % (index + 2)
ws[cell] = row[0]
ws.cell(row=1, column=3).value = 'ISL NO'
wb.save('Data/aging_stock_summary_copy.xlsx')
#
print('11. Branch - Brand - SKU wise Stock Aging Status Dataaved')
def check(val):
if val == '':
return '-'
else:
return int(val)
# sys.exit()
excel_data_df = pd.read_excel('Data/aging_stock_summary_copy.xlsx',
sheet_name='Sheet1',
usecols=['Aging_Days', 'BRAND'])
aging_days = ofn.create_dup_count_list(excel_data_df, 'Aging_Days')
# brand = ofn.create_dup_count_list(excel_data_df, 'BRAND')
wb = xlrd.open_workbook('Data/aging_stock_summary_copy.xlsx')
sh = wb.sheet_by_name('Sheet1')
tabletd = ""
for i in range(1, sh.nrows):
tabletd = tabletd + "<tr>\n"
for j in range(0, 1):
# BSL NO
if (aging_days[i - 1] != 0):
tabletd = tabletd + "<td class=\"serial\" rowspan=\"" + str(
(aging_days[i - 1])) + "\"> " + str(sh.cell_value(
i, j)) + "</td>\n"
for j in range(1, 2):
# Brand
tabletd = tabletd + "<td class=\"y_desc_sales\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(2, 3):
# ITemNo
tabletd = tabletd + "<td class=\"sl_center\">" + str(
int(sh.cell_value(i, j))) + "</td>\n"
for j in range(3, 4):
# ITemNo
tabletd = tabletd + "<td class=\"y_desc_sales\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(4, 5):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(5, 6):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(6, 7):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(7, 8):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(8, 9):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(9, 10):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(10, 11):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(11, 12):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(12, 13):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(13, 14):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(14, 15):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(15, 16):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(16, 17):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(17, 18):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(18, 19):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(19, 20):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(20, 21):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(21, 22):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(22, 23):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(23, 24):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(24, 25):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(25, 26):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(26, 27):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(27, 28):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(28, 29):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(29, 30):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(30, 31):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(31, 32):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(32, 33):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(33, 34):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(34, 35):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(35, 36):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(36, 37):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
for j in range(37, 38):
tabletd = tabletd + "<td class=\"number_style\">" + str(
(check(sh.cell_value(i, j)))) + "</td>\n"
table = tabletd + "</tr>\n"
print(
"11.1. Branch - Brand - SKU wise Stock Aging Status: Summary Table created \n"
)
return table
def item_wise_yesterday_sales_Records():
df_y_scock = pd.read_excel('Data/gpm_data.xlsx')
df_stock = df_y_scock.drop(df_y_scock.columns[[
6, 7, 8, 9, 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, 54, 55, 56, 57, 58, 59, 60,
61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78,
79, 80, 81, 82, 83, 84
]],
axis=1)
yesterday_sales = df_stock.loc[df_stock['YesterdaySales'] >= 1]
yesterday_no_sales = df_stock.loc[df_stock['YesterdaySales'] <= 0]
yesterday_no_sales = yesterday_no_sales[[
'BSL NO', 'BRAND', 'ISL NO', 'Item Name', 'UOM'
]]
yesterday_sales.to_excel('Data/item_wise_yesterday_sales.xlsx',
index=False)
yesterday_no_sales.to_excel('Data/yesterday_no_sales.xlsx', index=False)
print('yesterday sales and no sales excel generated')
excel_data_df = pd.read_excel('Data/item_wise_yesterday_sales.xlsx',
sheet_name='Sheet1',
usecols=[
'BSL NO', 'BRAND', 'ISL NO', 'Item Name',
'UOM', 'YesterdaySales'
])
bslno = ofn.create_dup_count_list(excel_data_df, 'BSL NO')
brand = ofn.create_dup_count_list(excel_data_df, 'BRAND')
wb = xlrd.open_workbook('Data/item_wise_yesterday_sales.xlsx')
sh = wb.sheet_by_name('Sheet1')
print('No Sales dataset Start printing in HTML')
#
tabletd = ""
for i in range(1, sh.nrows):
tabletd = tabletd + "<tr>\n"
for j in range(0, 1):
# BSL NO
if (bslno[i - 1] != 0):
tabletd = tabletd + "<td class=\"serial\" rowspan=\"" + str(
bslno[i - 1]) + "\"> " + str(int(sh.cell_value(
i, j))) + "</td>\n"
for j in range(1, 2):
# Brand
if (brand[i - 1] != 0):
tabletd = tabletd + "<td class=\"brandtd\" rowspan=\"" + str(
brand[i - 1]) + "\">" + str(sh.cell_value(i,
j)) + "</td>\n"
for j in range(2, 3):
# ITemNo
tabletd = tabletd + "<td class=\"central\">" + str(
int((sh.cell_value(i, j)))) + "</td>\n"
for j in range(3, 4):
# item name
tabletd = tabletd + "<td class=\"descriptiontd\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(4, 5):
# unit
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(5, 6):
# unit
tabletd = tabletd + "<td class=\"style1\">" + \
ofn.number_style(str(int((sh.cell_value(i,j))))) + "</td>\n"
table1 = tabletd + "</tr>\n"
print("item wise yesterday sale table Created")
return table1
def branch_wise_stocks_Records():
print('Branch wise stock ...')
df = pd.read_excel('Data/gpm_data.xlsx')
excel_data_df = df[[
'BSL NO', 'BRAND', 'ISL NO', 'Item Name', 'UOM', 'BOG', 'BSL', 'COM',
'COX', 'CTG', 'CTN', 'DNJ', 'FEN', 'FRD', 'GZP', 'HZJ', 'JES', 'KHL',
'KRN', 'KSG', 'KUS', 'MHK', 'MIR', 'MLV', 'MOT', 'MYM', 'NAJ', 'NOK',
'PAT', 'PBN', 'RAJ', 'RNG', 'SAV', 'SYL', 'TGL', 'VRB'
]]
bslno = ofn.create_dup_count_list(excel_data_df, 'BSL NO')
brand = ofn.create_dup_count_list(excel_data_df, 'BRAND')
wb = xlrd.open_workbook('Data/gpm_data.xlsx')
sh = wb.sheet_by_name('Sheet1')
print('branch Wise dataset Start printing in HTML')
tabletd = ""
for i in range(1, sh.nrows):
tabletd = tabletd + "<tr>\n"
for j in range(0, 1):
# BSL NO
if (bslno[i - 1] != 0):
tabletd = tabletd + "<td class=\"serial\" rowspan=\"" + str(
bslno[i - 1]) + "\"> " + str(int(sh.cell_value(
i, j))) + "</td>\n"
for j in range(1, 2):
# Brand
if (brand[i - 1] != 0):
tabletd = tabletd + "<td class=\"brandtd\" rowspan=\"" + str(
brand[i - 1]) + "\">" + str(sh.cell_value(i,
j)) + "</td>\n"
for j in range(2, 3):
# ITemNo
tabletd = tabletd + "<td class=\"central\">" + str(
int((sh.cell_value(i, j)))) + "</td>\n"
for j in range(3, 4):
# item name
tabletd = tabletd + "<td class=\"description\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(4, 5):
# unit
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(5, 6):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i,j))))) + "</td>\n"
for j in range(6, 7):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(7, 8):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(8, 9):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(9, 10):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(10, 11):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(11, 12):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(12, 13):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(13, 14):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(14, 15):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(15, 16):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(16, 17):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(17, 18):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(18, 19):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(19, 20):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(20, 21):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(21, 22):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(22, 23):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(23, 24):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(24, 25):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(25, 26):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(26, 27):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(27, 28):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(28, 29):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(29, 30):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(30, 31):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(31, 32):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(32, 33):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(33, 34):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(34, 35):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
for j in range(35, 36):
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" \
+ str(ofn.branch_warning(sh.cell_value(i, j))) + "\">" \
+ ofn.number_style(str(int((sh.cell_value(i, j))))) + "</td>\n"
table1 = tabletd + "</tr>\n"
print("No Sales table Created")
return table1
def get_branch_aging_stock_status(name):
df = pd.read_sql_query(""" Select * from
(select *
from
(
Select * from
(select dense_rank() OVER (ORDER BY BRAND ) [BSL NO], BRAND,(select ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS [ISL NO],ITEMNAME,AUDTORG, Max(AGEING) AS AGEING from
(select ITEMNO,
case when [location] = ('4001') then 'SKF Mirpur Plant'
when [location] = ('4005') then 'SKF Tongi Plant'
when [location] = ('4016') then 'SKF Rupganj Plant'
else AUDTORG end as AUDTORG,AGEING,QTYAVAIL,EXPIRYDATE from ICStockCurrent_Lot) as T1
LEFT JOIN
(select ITEMNO,ITEMNAME,BRAND,PACKSIZE,GPMNAME from PRINFOSKF )as T2
ON (T1.ITEMNO=T2.ITEMNO)
WHERE T2.GPMNAME like ?
Group by BRAND,ITEMNAME,AUDTORG ) as T3
) src
pivot
(Max(AGEING)
for AUDTORG in ([BOGSKF],[BSLSKF],[COMSKF],[COXSKF],[CTGSKF],[CTNSKF],[DNJSKF],[FENSKF],[FRDSKF],[GZPSKF],[HZJSKF],[JESSKF],[KHLSKF],[KRNSKF],[KSGSKF],[KUSSKF],[MHKSKF],[MIRSKF],[MLVSKF],[MOTSKF],[MYMSKF],[NAJSKF],[NOKSKF],[PATSKF],[PBNSKF],[RAJSKF],[RNGSKF],[SAVSKF],[SKFDAT],[SYLSKF],[TGLSKF],[VRBSKF],[SKF Rupganj Plant],[SKF Mirpur Plant],[SKF Tongi Plant]
)) AS piv
) as TblAging
left join
(select *
from
(Select * from
(select dense_rank() OVER (ORDER BY BRAND ) [BSL NO], BRAND,ITEMNAME,AUDTORG, Sum(QTYAVAIL) AS TotalStock from
(select ITEMNO,case when [location] = ('4001') then 'SKF Mirpur Plant'
when [location] = ('4005') then 'SKF Tongi Plant'
when [location] = ('4016') then 'SKF Rupganj Plant'
else AUDTORG end as AUDTORG,AGEING,QTYAVAIL,EXPIRYDATE from ICStockCurrent_Lot) as T1
LEFT JOIN
(select ITEMNO,ITEMNAME,BRAND,PACKSIZE,GPMNAME from PRINFOSKF )as T2
ON (T1.ITEMNO=T2.ITEMNO)
WHERE T2.GPMNAME like ?
Group by BRAND,ITEMNAME,AUDTORG ) as T3
) src
pivot
(
sum(TotalStock)
for AUDTORG in ([BOGSKF],[BSLSKF],[COMSKF],[COXSKF],[CTGSKF],[CTNSKF],[DNJSKF],[FENSKF],[FRDSKF],[GZPSKF],[HZJSKF],[JESSKF],[KHLSKF],[KRNSKF],[KSGSKF],[KUSSKF],[MHKSKF],[MIRSKF],[MLVSKF],[MOTSKF],[MYMSKF],[NAJSKF],[NOKSKF],[PATSKF],[PBNSKF],[RAJSKF],[RNGSKF],[SAVSKF],[SKFDAT],[SYLSKF],[TGLSKF],[VRBSKF],[SKF Rupganj Plant],[SKF Mirpur Plant],[SKF Tongi Plant]
)
) AS piv) as tblsStock
on (tblsStock.ITEMNAME=TblAging.ITEMNAME)
""",
connection,
params=(name, name))
# column_values = range(1, len(df))
# print(column_values)
# sys.exit()
writer = pd.ExcelWriter('Data/branch_wise_aging_stock.xlsx',
engine='openpyxl')
wb = writer.book
df.to_excel(writer, index=False)
wb.save('Data/branch_wise_aging_stock.xlsx')
column_values = range(1, len(df) + 1)
df_new = pd.DataFrame({'ISL NO': column_values})
wb = load_workbook('Data/branch_wise_aging_stock.xlsx')
ws = wb['Sheet1']
for index, row in df_new.iterrows():
cell = 'C%d' % (index + 2)
ws[cell] = row[0]
wb.save('Data/branch_wise_aging_stock.xlsx')
print(
'17. Branch - Brand - SKU wise Stock Aging Status: Detailed data saved'
)
# sys.exit()
excel_data_df = pd.read_excel('Data/branch_wise_aging_stock.xlsx',
sheet_name='Sheet1',
usecols=['BSL NO', 'BRAND'])
bslno = ofn.create_dup_count_list(excel_data_df, 'BSL NO')
brand = ofn.create_dup_count_list(excel_data_df, 'BRAND')
wb = xlrd.open_workbook('Data/branch_wise_aging_stock.xlsx')
sh = wb.sheet_by_name('Sheet1')
tabletd = ""
for i in range(1, sh.nrows):
tabletd = tabletd + "<tr>\n"
for j in range(0, 1):
# BSL NO
if (bslno[i - 1] != 0):
tabletd = tabletd + "<td class=\"serial\" rowspan=\"" + str(
(bslno[i - 1])) + "\"> " + str(int(sh.cell_value(
i, j))) + "</td>\n"
for j in range(1, 2):
# Brand
if (brand[i - 1] != 0):
tabletd = tabletd + "<td class=\"brandtd\" rowspan=\"" + str(
brand[i - 1]) + "\">" + str(sh.cell_value(i,
j)) + "</td>\n"
for j in range(2, 3):
# ITemNo
tabletd = tabletd + "<td class=\"serial\">" + str(
int(sh.cell_value(i, j))) + "</td>\n"
for j in range(3, 4):
# ITemNo
tabletd = tabletd + "<td class=\"y_desc_sales\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(4, 5):
bog = sh.cell_value(i, j)
for j in range(5, 6):
bsl = sh.cell_value(i, j)
for j in range(6, 7):
com = sh.cell_value(i, j)
for j in range(7, 8):
cox = sh.cell_value(i, j)
for j in range(8, 9):
ctg = sh.cell_value(i, j)
for j in range(9, 10):
ctn = sh.cell_value(i, j)
for j in range(10, 11):
dnj = sh.cell_value(i, j)
for j in range(11, 12):
fen = sh.cell_value(i, j)
for j in range(12, 13):
frd = sh.cell_value(i, j)
for j in range(13, 14):
gzp = sh.cell_value(i, j)
for j in range(14, 15):
hzj = sh.cell_value(i, j)
for j in range(15, 16):
jes = sh.cell_value(i, j)
for j in range(16, 17):
khl = sh.cell_value(i, j)
for j in range(17, 18):
krn = sh.cell_value(i, j)
for j in range(18, 19):
ksg = sh.cell_value(i, j)
for j in range(19, 20):
kus = sh.cell_value(i, j)
for j in range(20, 21):
mhk = sh.cell_value(i, j)
for j in range(21, 22):
mir = sh.cell_value(i, j)
for j in range(22, 23):
mlv = sh.cell_value(i, j)
for j in range(23, 24):
mot = sh.cell_value(i, j)
for j in range(24, 25):
mym = sh.cell_value(i, j)
for j in range(25, 26):
naj = sh.cell_value(i, j)
for j in range(26, 27):
nok = sh.cell_value(i, j)
for j in range(27, 28):
pat = sh.cell_value(i, j)
for j in range(28, 29):
pbn = sh.cell_value(i, j)
for j in range(29, 30):
raj = sh.cell_value(i, j)
for j in range(30, 31):
rng = sh.cell_value(i, j)
for j in range(31, 32):
sav = sh.cell_value(i, j)
for j in range(33, 34):
syl = sh.cell_value(i, j)
for j in range(34, 35):
tgl = sh.cell_value(i, j)
for j in range(35, 36):
vrb = sh.cell_value(i, j)
for j in range(36, 37):
Rupganj = sh.cell_value(i, j)
for j in range(37, 38):
Mirpur = sh.cell_value(i, j)
for j in range(38, 39):
Tongi = sh.cell_value(i, j)
for j in range(42, 43):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(bog)) + "\">"\
+ str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(43, 44):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(bsl)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(44, 45):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(com)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(45, 46):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(cox)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(46, 47):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(ctg)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(47, 48):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(ctn)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(48, 49):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(dnj)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(49, 50):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(fen)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(50, 51):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(frd)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(51, 52):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(gzp)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(52, 53):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(hzj)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(53, 54):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(jes)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(54, 55):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(khl)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(55, 56):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(krn)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(56, 57):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(ksg)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(57, 58):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(kus)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(58, 59):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(mhk)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(59, 60):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(mir)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(60, 61):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(mlv)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(61, 62):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(mot)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(62, 63):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(mym)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(63, 64):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(naj)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(64, 65):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(nok)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(65, 66):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(pat)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(66, 67):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(pbn)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(67, 68):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(raj)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(68, 69):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(rng)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(69, 70):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(sav)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(71, 72):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(syl)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(72, 73):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(tgl)) + "\">"+ str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(73, 74):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(vrb)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(74, 75):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(Rupganj)) + "\">" + str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(75, 76):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(Mirpur)) + "\">"+ str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
for j in range(76, 77):
tabletd = tabletd + "<td class=\"number_style\"style=\"background-color:" + \
str(ofn.status_color(Tongi)) + "\">" + \
str(ofn.integer_converter(sh.cell_value(i, j))) + "</td>\n"
table = tabletd + "</tr>\n"
print(
"17.1. Branch - Brand - SKU wise Stock Aging Status: Detailed table Created \n"
)
return table
def get_Sales_and_Stock_Records():
excel_data_df = pd.read_excel('Data/html_data_Sales_and_Stock.xlsx',
sheet_name='Sheet1',
usecols=['BSL NO', 'BRAND'])
bslno = ofn.create_dup_count_list(excel_data_df, 'BSL NO')
brand = ofn.create_dup_count_list(excel_data_df, 'BRAND')
wb = xlrd.open_workbook('Data/html_data_Sales_and_Stock.xlsx')
sh = wb.sheet_by_name('Sheet1')
# print('GPM Salse and Stock dataset Start printing in HTML')
tabletd = ""
for i in range(1, sh.nrows):
tabletd = tabletd + "<tr>\n"
for j in range(0, 1):
# BSL NO
if (bslno[i - 1] != 0):
tabletd = tabletd + "<td class=\"serial\" rowspan=\"" + str(
bslno[i - 1]) + "\"> " + str(int(sh.cell_value(
i, j))) + "</td>\n"
for j in range(1, 2):
# Brand
if (brand[i - 1] != 0):
tabletd = tabletd + "<td class=\"brandtd\" rowspan=\"" + str(
brand[i - 1]) + "\">" + str(sh.cell_value(i,
j)) + "</td>\n"
for j in range(2, 3):
# ITemNo
tabletd = tabletd + "<td class=\"central\">" + str(
int((sh.cell_value(i, j)))) + "</td>\n"
for j in range(3, 4):
# item name
tabletd = tabletd + "<td class=\"description\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(4, 5):
# unit
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(5, 6):
# avg sales Day
avg_sales = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(avg_sales))) + "</td>\n"
for j in range(6, 7):
# Monthly Sales Target
msalestar = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(msalestar))) + "</td>\n"
for j in range(7, 8):
# MTD sales Target
mtdsalestar = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(mtdsalestar))) + "</td>\n"
for j in range(8, 9):
# actual sales mtd
mtdsales = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(mtdsales))) + "</td>\n"
for j in range(9, 10):
# MTD Sales Achv %
mtd_salesacv = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
str(mtd_salesacv)) + '%' + "</td>\n"
for j in range(10, 11):
# Monthly Sales Achv %
m_salesacv = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
str(m_salesacv)) + '%' + "</td>\n"
for j in range(11, 12):
# Monthly Sales Trend
monthlytrend = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(monthlytrend))) + "</td>\n"
for j in range(12, 13):
# monthly sales trend acv
monthlyachiv = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(monthlyachiv))) + "</td>\n"
for j in range(13, 14):
# RemaingStock
RemaingStock = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(RemaingStock))) + "</td>\n"
for j in range(14, 15):
# NationwideStock
NationwideStock = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"nation_wide\">" + str(
ofn.number_style(str(NationwideStock))) + "</td>\n"
for j in range(15, 16):
# SKF Mirpur Plant
mirpur = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(mirpur))) + "</td>\n"
for j in range(16, 17):
# SKF Rupganje
Rupganje = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(Rupganje))) + "</td>\n"
for j in range(17, 18):
# SKF Tongi Plant
tongi = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(tongi))) + "</td>\n"
for j in range(18, 19):
bog = int(sh.cell_value(i, j))
for j in range(19, 20):
bsl = int(sh.cell_value(i, j))
for j in range(20, 21):
com = int(sh.cell_value(i, j))
for j in range(21, 22):
cox = int(sh.cell_value(i, j))
for j in range(22, 23):
ctg = int(sh.cell_value(i, j))
for j in range(23, 24):
ctn = int(sh.cell_value(i, j))
for j in range(24, 25):
dnj = int(sh.cell_value(i, j))
for j in range(25, 26):
fen = int(sh.cell_value(i, j))
for j in range(26, 27):
frd = int(sh.cell_value(i, j))
for j in range(27, 28):
gzp = int(sh.cell_value(i, j))
for j in range(28, 29):
hzj = int(sh.cell_value(i, j))
for j in range(29, 30):
jes = int(sh.cell_value(i, j))
for j in range(30, 31):
khl = int(sh.cell_value(i, j))
for j in range(31, 32):
krn = int(sh.cell_value(i, j))
for j in range(32, 33):
ksg = int(sh.cell_value(i, j))
for j in range(33, 34):
kus = int(sh.cell_value(i, j))
for j in range(34, 35):
mhk = int(sh.cell_value(i, j))
for j in range(35, 36):
mir = int(sh.cell_value(i, j))
for j in range(36, 37):
mlv = int(sh.cell_value(i, j))
for j in range(37, 38):
mot = int(sh.cell_value(i, j))
for j in range(38, 39):
mym = int(sh.cell_value(i, j))
for j in range(39, 40):
naj = int(sh.cell_value(i, j))
for j in range(40, 41):
nok = int(sh.cell_value(i, j))
for j in range(41, 42):
pat = int(sh.cell_value(i, j))
for j in range(42, 43):
pbn = int(sh.cell_value(i, j))
for j in range(43, 44):
raj = int(sh.cell_value(i, j))
for j in range(44, 45):
rng = int(sh.cell_value(i, j))
for j in range(45, 46):
sav = int(sh.cell_value(i, j))
for j in range(46, 47):
syl = int(sh.cell_value(i, j))
for j in range(47, 48):
tgl = int(sh.cell_value(i, j))
for j in range(48, 49):
vrb = int(sh.cell_value(i, j))
for j in range(49, 50):
# TDCL Central
tdcl_val = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(tdcl_val))) + "</td>\n"
for j in range(50, 51):
# Branch Total
tdcl_val = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"num_style\">" + str(
ofn.number_style(str(tdcl_val))) + "</td>\n"
for j in range(51, 52):
BOG = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(bog, BOG)) + "\">" + str(ofn.number_style(str(BOG))) + "</td>\n"
for j in range(52, 53):
BSL = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(bsl, BSL)) + "\">" + str(ofn.number_style(str(BSL))) + "</td>\n"
for j in range(53, 54):
COM = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(com, COM)) + "\">" + str(ofn.number_style(str(COM))) + "</td>\n"
for j in range(54, 55):
COX = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(cox, COX)) + "\">" + str(ofn.number_style(str(COX))) + "</td>\n"
for j in range(55, 56):
CTG = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(ctg, CTG)) + "\">" + str(ofn.number_style(str(CTG))) + "</td>\n"
for j in range(56, 57):
CTN = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(ctn, CTN)) + "\">" + str(ofn.number_style(str(CTN))) + "</td>\n"
for j in range(57, 58):
DNJ = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(dnj, DNJ)) + "\">" + str(ofn.number_style(str(DNJ))) + "</td>\n"
for j in range(58, 59):
FEN = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(fen, FEN)) + "\">" + str(ofn.number_style(str(FEN))) + "</td>\n"
for j in range(59, 60):
FRD = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(frd, FRD)) + "\">" + str(ofn.number_style(str(FRD))) + "</td>\n"
for j in range(60, 61):
GZP = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(gzp, GZP)) + "\">" + str(ofn.number_style(str(GZP))) + "</td>\n"
for j in range(61, 62):
HZJ = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(hzj, HZJ)) + "\">" + str(ofn.number_style(str(HZJ))) + "</td>\n"
for j in range(62, 63):
JES = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(jes, JES)) + "\">" + str(ofn.number_style(str(JES))) + "</td>\n"
for j in range(63, 64):
KHL = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(khl, KHL)) + "\">" + str(ofn.number_style(str(KHL))) + "</td>\n"
for j in range(64, 65):
KRN = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(krn, KRN)) + "\">" + str(ofn.number_style(str(KRN))) + "</td>\n"
for j in range(65, 66):
KSG = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(ksg, KSG)) + "\">" + str(ofn.number_style(str(KSG))) + "</td>\n"
for j in range(66, 67):
KUS = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(kus, KUS)) + "\">" + str(ofn.number_style(str(KUS))) + "</td>\n"
for j in range(67, 68):
MHK = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(mhk, MHK)) + "\">" + str(ofn.number_style(str(MHK))) + "</td>\n"
for j in range(68, 69):
MIR = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(mir, MIR)) + "\">" + str(ofn.number_style(str(MIR))) + "</td>\n"
for j in range(69, 70):
MLV = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(mlv, MLV)) + "\">" + str(ofn.number_style(str(MLV))) + "</td>\n"
for j in range(70, 71):
MOT = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(mot, MOT)) + "\">" + str(ofn.number_style(str(MOT))) + "</td>\n"
for j in range(71, 72):
MYM = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(mym, MYM)) + "\">" + str(ofn.number_style(str(MYM))) + "</td>\n"
for j in range(72, 73):
NAJ = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(naj, NAJ)) + "\">" + str(ofn.number_style(str(NAJ))) + "</td>\n"
for j in range(73, 74):
NOK = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(nok, NOK)) + "\">" + str(ofn.number_style(str(NOK))) + "</td>\n"
for j in range(74, 75):
PAT = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(pat, PAT)) + "\">" + str(ofn.number_style(str(PAT))) + "</td>\n"
for j in range(75, 76):
PBN = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(pbn, PBN)) + "\">" + str(ofn.number_style(str(PBN))) + "</td>\n"
for j in range(76, 77):
RAJ = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(raj, RAJ)) + "\">" + str(ofn.number_style(str(RAJ))) + "</td>\n"
for j in range(77, 78):
RNG = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(rng, RNG)) + "\">" + str(ofn.number_style(str(RNG))) + "</td>\n"
for j in range(78, 79):
SAV = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(sav, SAV)) + "\">" + str(ofn.number_style(str(SAV))) + "</td>\n"
for j in range(79, 80):
SYL = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + str(
ofn.warning(syl, SYL)) + "\">" + str(ofn.number_style(
str(SYL))) + "</td>\n"
for j in range(80, 81):
TGL = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(tgl, TGL)) + "\">" + str(ofn.number_style(str(TGL))) + "</td>\n"
for j in range(81, 82):
VRB = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(vrb, VRB)) + "\">" + str(ofn.number_style(str(VRB))) + "</td>\n"
for j in range(82, 83):
VRB = int(sh.cell_value(i, j))
tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
str(ofn.warning(vrb, VRB)) + "\">" + str(ofn.number_style(str(VRB))) + "</td>\n"
table = tabletd + "</tr>\n"
print("Master Table Created")
return table
def get_Sales_and_Stock_Records():
excel_data_df = pd.read_excel('Data/html_data_Sales_and_Stock.xlsx',
sheet_name='Sheet1',
usecols=['BSL NO', 'BRAND'])
bslno = ofn.create_dup_count_list(excel_data_df, 'BSL NO')
brand = ofn.create_dup_count_list(excel_data_df, 'BRAND')
wb = xlrd.open_workbook('Data/html_data_Sales_and_Stock.xlsx')
sh = wb.sheet_by_name('Sheet1')
# wb2 = xlrd.open_workbook('Data/item_wise_stock_days.xlsx')
# sh = wb2.sheet_by_name('Sheet1')
# print('GPM Salse and Stock dataset Start printing in HTML')
tabletd = ""
for i in range(1, sh.nrows):
tabletd = tabletd + "<tr>\n"
for j in range(0, 1):
# BSL NO
if (bslno[i - 1] != 0):
tabletd = tabletd + "<td id=\"serial\" rowspan=\"" + str(
bslno[i - 1]) + "\"> " + str(int(sh.cell_value(
i, j))) + "</td>\n"
for j in range(1, 2):
# Brand
if (brand[i - 1] != 0):
tabletd = tabletd + "<td class=\"brandtd\" rowspan=\"" + str(
brand[i - 1]) + "\">" + str(sh.cell_value(i,
j)) + "</td>\n"
for j in range(2, 3):
# ITemNo
tabletd = tabletd + "<td id=\"serial\">" + str(
int((sh.cell_value(i, j)))) + "</td>\n"
for j in range(3, 4):
# item name
tabletd = tabletd + "<td class=\"y_desc_sales\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(4, 5):
# unit
tabletd = tabletd + "<td id=\"number_style2\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(5, 6):
# avg sales Day
avg_sales = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
ofn.number_style(str(avg_sales))) + "</td>\n"
for j in range(6, 7):
# Monthly Sales Target
msalestar = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
ofn.number_style(str(msalestar))) + "</td>\n"
for j in range(7, 8):
# Months Target
months_target = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
ofn.number_style(str(months_target))) + "</td>\n"
for j in range(8, 9):
# MTD Target
mtdtarget = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
ofn.number_style(str(mtdtarget))) + "</td>\n"
for j in range(9, 10):
# MTD Sales Actual
mtd_sales = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
str(mtd_sales)) + "</td>\n"
for j in range(10, 11):
# MTD Sales Achv %
try:
mtd_achiv = "{:.2f}".format((mtd_sales / mtdtarget) * 100)
except:
mtd_achiv = 0
tabletd = tabletd + "<td id=\"number_style2\">" + str(
str(mtd_achiv)) + '%' + "</td>\n"
for j in range(11, 12):
# Monthly Sales Achv %
lmtarget = "{:.2f}".format(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
str(lmtarget)) + '%' + "</td>\n"
for j in range(12, 13):
# lm mtd sALES TARGET
lmtargeta = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
str(lmtargeta)) + "</td>\n"
for j in range(13, 14):
# LM actual sales
lmactual_sales = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
str(lmactual_sales)) + "</td>\n"
for j in range(14, 15):
# LM MTD Sales Achv %
try:
lmmtd_achiv = "{:.2f}".format(
(lmactual_sales / lmtargeta) * 100)
except:
lmmtd_achiv = 0
tabletd = tabletd + "<td id=\"number_style2\">" + str(
str(lmmtd_achiv)) + '%' + "</td>\n"
for j in range(14, 15):
tmtrend = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"ssstd\">" + str(
ofn.number_style(str(tmtrend))) + "</td>\n"
for j in range(15, 16):
s_trendacv = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"ssstd\">" + str(
ofn.number_style(str(s_trendacv))) + '%' + "</td>\n"
for j in range(16, 17):
# remaining qty
remailing = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
str(remailing)) + "</td>\n"
for j in range(17, 18):
# SKF Rupganje
nationwide = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
str(nationwide)) + "</td>\n"
for j in range(18, 19):
# SKF Tongi Plant
SKF = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
ofn.number_style(str(SKF))) + "</td>\n"
for j in range(19, 20):
# SKF Tongi Plant
mirpur = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
ofn.number_style(str(mirpur))) + "</td>\n"
for j in range(20, 21):
# SKF tongi Plant
tongi = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
ofn.number_style(str(tongi))) + "</td>\n"
for j in range(21, 22):
# SKF rup Plant
rup = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
ofn.number_style(str(rup))) + "</td>\n"
for j in range(22, 23):
bog = sh.cell_value(i, j)
for j in range(23, 24):
bsl = sh.cell_value(i, j)
for j in range(24, 25):
com = sh.cell_value(i, j)
for j in range(25, 26):
cox = sh.cell_value(i, j)
for j in range(26, 27):
ctg = sh.cell_value(i, j)
for j in range(27, 28):
cetn = sh.cell_value(i, j)
for j in range(28, 29):
dnj = sh.cell_value(i, j)
for j in range(29, 30):
fen = sh.cell_value(i, j)
for j in range(30, 31):
frd = sh.cell_value(i, j)
for j in range(31, 32):
gzp = sh.cell_value(i, j)
for j in range(32, 33):
cnd = sh.cell_value(i, j)
for j in range(33, 34):
jes = sh.cell_value(i, j)
for j in range(34, 35):
khl = sh.cell_value(i, j)
for j in range(35, 36):
krn = sh.cell_value(i, j)
for j in range(36, 37):
ksg = sh.cell_value(i, j)
for j in range(37, 38):
kus = sh.cell_value(i, j)
for j in range(38, 39):
tej = sh.cell_value(i, j)
for j in range(39, 40):
mir = sh.cell_value(i, j)
for j in range(40, 41):
mlv = sh.cell_value(i, j)
for j in range(41, 42):
mot = sh.cell_value(i, j)
for j in range(42, 43):
mym = sh.cell_value(i, j)
for j in range(43, 44):
naj = sh.cell_value(i, j)
for j in range(44, 45):
nok = sh.cell_value(i, j)
for j in range(45, 46):
pat = sh.cell_value(i, j)
for j in range(46, 47):
pbn = sh.cell_value(i, j)
for j in range(47, 48):
raj = sh.cell_value(i, j)
for j in range(48, 49):
rng = sh.cell_value(i, j)
for j in range(49, 50):
sav = sh.cell_value(i, j)
for j in range(50, 51):
syl = sh.cell_value(i, j)
for j in range(51, 52):
tgl = sh.cell_value(i, j)
for j in range(52, 53):
bbr = sh.cell_value(i, j)
for j in range(53, 54):
# TDCL Central
tdcl_val = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
ofn.number_style(str(tdcl_val))) + "</td>\n"
for j in range(54, 55):
# Branch Total
tdcl_val = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\">" + str(
ofn.number_style(str(tdcl_val))) + "</td>\n"
# # need to correct order
for j in range(55, 56):
BOG = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(bog, BOG)) + "\">" + ofn.day_calculator(bog, BOG) + "</td>\n"
for j in range(56, 57):
BSL = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\" style=\"background-color:" + \
str(ofn.warning(bsl, BSL)) + "\">" + ofn.day_calculator(bsl, BSL) + "</td>\n"
for j in range(57, 58):
COM = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(com, COM)) + "\">" + ofn.day_calculator(com, COM) + "</td>\n"
for j in range(58, 59):
COX = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(cox, COX)) + "\">" + ofn.day_calculator(cox, COX) + "</td>\n"
for j in range(59, 60):
CTG = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(ctg, CTG)) + "\">" + ofn.day_calculator(ctg, CTG) + "</td>\n"
for j in range(60, 61):
CTN = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(cetn, CTN)) + "\">" + ofn.day_calculator(cetn, CTN) + "</td>\n"
for j in range(61, 62):
DNJ = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(dnj, DNJ)) + "\">" + ofn.day_calculator(dnj, DNJ) + "</td>\n"
for j in range(62, 63):
FEN = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(fen, FEN)) + "\">" + ofn.day_calculator(fen, FEN) + "</td>\n"
for j in range(63, 64):
FRD = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(frd, FRD)) + "\">" + ofn.day_calculator(frd, FRD) + "</td>\n"
for j in range(64, 65):
GZP = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(gzp, GZP)) + "\">" + ofn.day_calculator(gzp, GZP) + "</td>\n"
for j in range(65, 66):
HZJ = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(cnd, HZJ)) + "\">" + ofn.day_calculator(cnd, HZJ) + "</td>\n"
for j in range(66, 67):
JES = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(jes, JES)) + "\">" + ofn.day_calculator(jes, JES) + "</td>\n"
for j in range(67, 68):
KHL = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(khl, KHL)) + "\">" + ofn.day_calculator(khl, KHL) + "</td>\n"
for j in range(68, 69):
KRN = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(krn, KRN)) + "\">" + ofn.day_calculator(krn, KRN) + "</td>\n"
for j in range(69, 70):
KSG = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(ksg, KSG)) + "\">" + ofn.day_calculator(ksg, KSG) + "</td>\n"
for j in range(70, 71):
KUS = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(kus, KUS)) + "\">" + ofn.day_calculator(kus, KUS) + "</td>\n"
for j in range(71, 72):
MHK = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(tej, MHK)) + "\">" + ofn.day_calculator(tej, MHK) + "</td>\n"
for j in range(72, 73):
MIR = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(mir, MIR)) + "\">" + ofn.day_calculator(mir, MIR) + "</td>\n"
for j in range(73, 74):
MLV = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(mlv, MLV)) + "\">" + ofn.day_calculator(mlv, MLV) + "</td>\n"
for j in range(74, 75):
MOT = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(mot, MOT)) + "\">" + ofn.day_calculator(mot, MOT) + "</td>\n"
for j in range(75, 76):
MYM = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(mym, MYM)) + "\">" + ofn.day_calculator(mym, MYM) + "</td>\n"
for j in range(76, 77):
NAJ = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(naj, NAJ)) + "\">" + ofn.day_calculator(naj, NAJ) + "</td>\n"
for j in range(77, 78):
NOK = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(nok, NOK)) + "\">" + ofn.day_calculator(nok, NOK) + "</td>\n"
for j in range(78, 79):
PAT = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(pat, PAT)) + "\">" + ofn.day_calculator(pat, PAT) + "</td>\n"
for j in range(79, 80):
PBN = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(pbn, PBN)) + "\">" + ofn.day_calculator(pbn, PBN) + "</td>\n"
for j in range(80, 81):
RAJ = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(raj, RAJ)) + "\">" + ofn.day_calculator(raj, RAJ) + "</td>\n"
for j in range(81, 82):
RNG = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(rng, RNG)) + "\">" + ofn.day_calculator(rng, RNG) + "</td>\n"
for j in range(82, 83):
SAV = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(sav, SAV)) + "\">" + ofn.day_calculator(sav, SAV) + "</td>\n"
for j in range(83, 84):
SYL = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(syl, SYL)) + "\">" + ofn.day_calculator(syl, SYL) + "</td>\n"
for j in range(84, 85):
TGL = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(tgl, TGL)) + "\">" + ofn.day_calculator(tgl, TGL) + "</td>\n"
for j in range(85, 86):
VRB = int(sh.cell_value(i, j))
tabletd = tabletd + "<td id=\"number_style2\"style=\"background-color:" + \
str(ofn.warning(bbr, VRB)) + "\">" + ofn.day_calculator(bbr, VRB) + "</td>\n"
# for j in range(86, 87):
# VRB = int(sh.cell_value(i, j))
# tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
# str(ofn.warning(bbr, VRB)) + "\">" + str(ofn.number_style(str(VRB))) + "</td>\n"
#
# for j in range(87, 88):
# VRB = int(sh.cell_value(i, j))
# tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
# str(ofn.warning(bbr, VRB)) + "\">" + str(ofn.number_style(str(VRB))) + "</td>\n"
#
# for j in range(88, 89):
# VRB = int(sh.cell_value(i, j))
# tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
# str(ofn.warning(bbr, VRB)) + "\">" + str(ofn.number_style(str(VRB))) + "</td>\n"
#
# for j in range(89, 90):
# VRB = int(sh.cell_value(i, j))
# tabletd = tabletd + "<td class=\"remarks\"style=\"background-color:" + \
# str(ofn.warning(bbr, VRB)) + "\">" + str(ofn.number_style(str(VRB))) + "</td>\n"
table = tabletd + "</tr>\n"
print("18. Master Details Table Created")
return table
def branch_wise_stocks_Records():
df = pd.read_excel('Data/item_wise_stock_data.xlsx')
bslno = ofn.create_dup_count_list(df, 'BSL')
brand = ofn.create_dup_count_list(df, 'BRAND')
wb = xlrd.open_workbook('Data/item_wise_stock_data.xlsx')
sh = wb.sheet_by_name('Sheet1')
print('Item wise Wise dataset Start printing in HTML')
tabletd = ""
for i in range(1, sh.nrows):
tabletd = tabletd + "<tr>\n"
for j in range(0, 1):
# BSL NO
if (bslno[i - 1] != 0):
tabletd = tabletd + "<td class=\"style1 \">" + \
str(int(sh.cell_value(i, j))) + "</td>\n"
for j in range(1, 2):
# Brand
if (brand[i - 1] != 0):
tabletd = tabletd + "<td class=\"style1\" rowspan=\"" + str(
brand[i - 1]) + "\">" + str(sh.cell_value(i,
j)) + "</td>\n"
for j in range(2, 3):
# ITemNo
tabletd = tabletd + "<td class=\"style1 \">" + str(
int((sh.cell_value(i, j)))) + "</td>\n"
for j in range(3, 4):
# item name
tabletd = tabletd + "<td class=\"style1 \">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(4, 5):
# unit
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(5, 6):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(6, 7):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(7, 8):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(8, 9):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(9, 10):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(10, 11):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(11, 12):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(12, 13):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(13, 14):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(14, 15):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(15, 16):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(16, 17):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(17, 18):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(18, 19):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(19, 20):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(20, 21):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(21, 22):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(22, 23):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(23, 24):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(24, 25):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(25, 26):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(26, 27):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(27, 28):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(28, 29):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(29, 30):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(30, 31):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(31, 32):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(32, 33):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(33, 34):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(34, 35):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
for j in range(35, 36):
tabletd = tabletd + "<td class=\"style1\">" + str(
(sh.cell_value(i, j))) + "</td>\n"
table1 = tabletd + "</tr>\n"
print("No Sales table Created")
return table1