def banner():
date = datetime.today()
day = str(date.day) + '/' + str(date.month) + '/' + str(date.year)
tz_NY = pytz.timezone('Asia/Dhaka')
datetime_BD = datetime.now(tz_NY)
time = datetime_BD.strftime("%I:%M %p")
date = datetime.today()
x = dd.datetime.now()
day = str(date.day) + '-' + str(x.strftime("%b")) + '-' + str(date.year)
tz_NY = pytz.timezone('Asia/Dhaka')
datetime_BD = datetime.now(tz_NY)
time = datetime_BD.strftime("%I:%M %p")
img = Image.open(dir.get_directory() + "/images/new_ai.png")
name = ImageDraw.Draw(img)
timestore = ImageDraw.Draw(img)
tag = ImageDraw.Draw(img)
branch = ImageDraw.Draw(img)
font = ImageFont.truetype(dir.get_directory() +
"/images/Stencil_Regular.ttf",
40,
encoding="unic")
name_font = ImageFont.truetype(dir.get_directory() +
"/images/Lobster-Regular.ttf",
30,
encoding="unic")
font1 = ImageFont.truetype(dir.get_directory() + "/images/ROCK.ttf",
30,
encoding="unic")
font2 = ImageFont.truetype(dir.get_directory() + "/images/ROCK.ttf",
22,
encoding="unic")
report_name = 'GPM '
# n = gpm_name
tag.text((25, 8), 'SK+F', (255, 255, 255), font=font)
branch.text((25, 130),
report_name + "Sales and Stock Report", (255, 209, 0),
font=font1)
# name.text((25, 180), n , (255, 255, 255), font=name_font)
timestore.text((25, 178), time + "\n" + day, (255, 255, 255), font=font2)
img.save(dir.get_directory() + "/images/banner_ai.png")
# img.show()
print("Banner Created")
def get_custom_scripts_path():
return path.get_directory('scripts')
def get_packages_metadata_path():
return path.get_directory('metadata', 'packages')
def get_library_path():
return path.get_directory('library')
def get_packages_path():
return path.get_directory('packages')
<img src="cid:visit" > <br>
<img src="cid:strike" > <br>
<img src="cid:lpc" > <br>
<img src="cid:ds_val" > <br>
<br> """ + tbl.all_table + """
<h4> This report is system generated. If you have any query please contact with AI Team. </h4>
<img src="cid:logo" height='150' width='250'> <br>
""", 'html')
msgAlternative.attach(msgText)
# Attached top 10 leave
part = MIMEBase('application', "octet-stream")
file_location = path.get_directory() + './Data/sku_wise_target_sales.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', "attachment; filename= %s" % filename)
msgRoot.attach(part)
# # -------- Dashboard --------------------------------------
fp = open(dirpath + './Images/dashboard.png', 'rb')
dash = MIMEImage(fp.read())
fp.close()
dash.add_header('Content-ID', '<dash>')
def dash_kpi_generator(name):
total_sku = pd.read_sql_query(
"""select gpmname,count(distinct BRAND) as 'total brand',count(itemno) as 'total_SKU' from PRINFOSKF
where status=1
and gpmname like ?
group by gpmname
""",
conn,
params={name})
total_sku_list = total_sku['total_SKU'].to_list()
total_brand_list = total_sku['total brand'].tolist()
sold_sku = pd.read_sql_query(
"""select count(distinct item) as 'Sold_SKU' from OESalesDetails
where item in(select itemno from PRINFOSKF
where status=1
and gpmname like ? )
and transtype = 1
and left(transdate,6)=CONVERT(varchar(6), dateAdd(day,0,getdate()), 112)
""",
conn,
params={name})
sold_sku_list = sold_sku['Sold_SKU'].to_list()
no_sales_sku = total_sku_list[0] - sold_sku_list[0]
no_stock_sku = pd.read_sql_query(
"""select count(a.itemno) 'no stock item' from
(select itemno from PRINFOSKF
where status=1
and gpmname like ? ) as a
left join
(select itemno,isnull(sum(QTYONHAND),0) as stock from ICStockStatusCurrentLOT
group by itemno) as b
on a.itemno = b.itemno
where stock = 0 """,
conn,
params={name})
no_stock_sku_list = no_stock_sku['no stock item'].to_list()
read_file_for_all_data = pd.read_excel('./Data/gpm_data.xlsx')
total_target = read_file_for_all_data['MTD Sales Target'].to_list()
total_sales = read_file_for_all_data['Actual Sales MTD'].to_list()
if sum(total_target) == 0:
achivemet = 0
else:
achivemet = (sum(total_sales) / sum(total_target)) * 100
today = str(date.today())
datee = datetime.strptime(today, "%Y-%m-%d")
date_val = int(datee.day)
num_days = monthrange(datee.year, datee.month)[1]
trend = (sum(total_sales) / date_val) * num_days
trend_achivement = (sum(total_sales) / trend) * 100
def currency_converter(num):
num_size = len(str(num))
if num_size >= 8:
number = str(round((num / 10000000), 2)) + 'Cr'
elif num_size >= 7:
number = str(round(num / 1000000, 2)) + 'M'
elif num_size >= 4:
number = str(int(num / 1000)) + 'K'
else:
number = num
return number
image = Image.open(dir.get_directory() + "/images/dash_kpi.png")
draw = ImageDraw.Draw(image)
font = ImageFont.truetype(
dir.get_directory() + '/Images/FrancoisOne-Regular.ttf', 30)
draw.text((80, 80),
str(total_brand_list[0]),
font=font,
fill=(39, 98, 236))
draw.text((270, 80), str(total_sku_list[0]), font=font, fill=(39, 98, 236))
draw.text((460, 80), str(sold_sku_list[0]), font=font, fill=(39, 98, 236))
draw.text((650, 80), str(no_sales_sku), font=font, fill=(39, 98, 236))
draw.text((840, 80),
str(no_stock_sku_list[0]),
font=font,
fill=(39, 98, 236))
draw.text((70, 220),
str(currency_converter(sum(total_target))),
font=font,
fill=(255, 255, 255))
draw.text((260, 220),
str(currency_converter(sum(total_sales))),
font=font,
fill=(255, 255, 255))
draw.text((435, 220),
str(round(achivemet, 1)) + '%',
font=font,
fill=(255, 255, 255))
draw.text((625, 220),
str(currency_converter(round(trend, 1))),
font=font,
fill=(255, 255, 255))
draw.text((830, 220),
str(round(trend_achivement, 1)) + '%',
font=font,
fill=(255, 255, 255))
# image.show()
image.save('./Images/dashboard.png')
print('Dash generated')
def dash_kpi_generator(name):
total_sku = pd.read_sql_query(
""" select gpmname,count(distinct BRAND) as 'total brand',count(itemno) as 'total_SKU' from PRINFOSKF
where status=1 and brand in (select distinct brand from GPMBRAND where Name like ?)
and gpmname like ?
group by gpmname
""",
conn,
params=(name, name))
total_sku_list = total_sku['total_SKU'].to_list()
total_brand_list = total_sku['total brand'].tolist()
# print('one')
sold_sku = pd.read_sql_query(
""" select count(distinct item) as 'Sold_SKU' from OESalesDetails
where item in(select itemno from PRINFOSKF
where status=1 and brand in (select distinct brand from GPMBRAND where Name like ?)
and gpmname like ?)
and transtype = 1
and left(transdate,6)=CONVERT(varchar(6), dateAdd(day,0,getdate()), 112)
""",
conn,
params=(name, name))
sold_sku_list = sold_sku['Sold_SKU'].to_list()
# no_sales_sku = total_sku_list[0] - sold_sku_list[0]
no_stock_sku = pd.read_sql_query(
"""select count(a.itemno) 'no stock item' from
(select itemno from PRINFOSKF
where status=1 and brand in (select distinct brand from GPMBRAND where Name like ?)
and gpmname like ? ) as a
left join
(select itemno,isnull(sum(QTYONHAND),0) as stock from ICStockStatusCurrentLOT
group by itemno) as b
on a.itemno = b.itemno
where stock = 0 """,
conn,
params=(name, name))
no_stock_sku_list = no_stock_sku['no stock item'].to_list()
read_file_for_all_data = pd.read_excel('./Data/gpm_data.xlsx')
total_target = read_file_for_all_data['MTD Sales Target'].to_list()
total_sales = read_file_for_all_data['Actual Sales MTD'].to_list()
# print('three')
# if sum(total_target) == 0:
# achivemet = 0
# else:
# achivemet = (sum(total_sales) / sum(total_target)) * 100
from datetime import datetime
today = str(date.today())
datee = datetime.strptime(today, "%Y-%m-%d")
date_val = int(datee.day)
num_days = monthrange(datee.year, datee.month)[1]
trend = (sum(total_sales) / date_val) * num_days
# if trend <= 0:
# trend_achivement = 0
# else:
# trend_achivement = (sum(total_sales) / trend) * 100
# def currency_converter(num):
# num_size = len(str(num))
# if num_size >= 4:
# number = str(int(num / 1000)) + 'K'
# else:
# number = num
# return number
sold_sku_percentage = str(
round((sold_sku_list[0] / total_sku_list[0]) * 100)) + '%'
# print(sold_sku_percentage)
yesterdaySalesQty = pd.read_excel(dir.get_directory() +
'/Data/html_data_Sales_and_Stock.xlsx')
# y = yesterdaySalesQty['YesterdaySalesQty'] != 0
y2 = yesterdaySalesQty['BRAND'].unique()
brand_coverage = str(round((len(y2) / total_brand_list[0]) * 100)) + '%'
# print(No_sold_sku_percentage)
No_stock_sku_percentage = str(
round((no_stock_sku_list[0] / total_sku_list[0]) * 100)) + '%'
# print(No_stock_sku_percentage)
# ----------------------------------------------------------------------------------------------------
# ---------------------------------kpi 6-10 ------------------------------------------
gpm_target = pd.read_sql_query(""" Declare @CurrentMonth NVARCHAR(MAX);
Declare @DaysInMonth NVARCHAR(MAX);
Declare @DaysInMonthtilltoday NVARCHAR(MAX);
SET @CurrentMonth = convert(varchar(6), GETDATE(),112)
SET @DaysInMonth = DAY(EOMONTH(GETDATE()))
SET @DaysInMonthtilltoday = right(convert(varchar(8), GETDATE(),112),2)
select CP01 as ExecutiveName, isnull(sum(TRGQTY),0) as TargetQty,isnull(sum(TRGVAL)/@DaysInMonth,0) as Targetvalue
from PRINFOSKF
left join ARCSECONDARY.dbo.PRODUCT_WISE_TRG
on PRODUCT_WISE_TRG.ITEM = PRINFOSKF.ITEMNO
where YRM = CONVERT(varchar(6), dateAdd(month,0,getdate()),
112) and GPMNAME like ?
and PRINFOSKF.brand in (select distinct brand from GPMBRAND where Name like ?)
group by CP01
order by CP01 asc
""",
conn,
params=(name, name))
GPM_target_list = gpm_target['Targetvalue'].to_list()
# print(GPM_target_list)
GPM_TARGET_SUM = sum(GPM_target_list)
# print('four')
if len(str(int(GPM_TARGET_SUM))) >= 8:
Target_value_in_crore = str("{:.2f}".format(
(GPM_TARGET_SUM / 10000000), 2)) + ' Cr'
else:
Target_value_in_crore = str("{:.2f}".format(
(GPM_TARGET_SUM / 1000000), 2)) + ' M'
# print('target = ', Target_value_in_crore)
gpm_sales_inv_line = pd.read_sql_query("""
select GPMNAME,GPMID,EXID,CP01,isnull(sum(EXTINVMISC), 0) as SalesValue,
isnull(count(distinct INVNUMBER), 0) as num_of_inv,
isnull(sum(LINECOUNT), 0) as num_of_inv_line from (
select item,INVNUMBER,sum(EXTINVMISC) as EXTINVMISC,sum(QTYSHIPPED) as QTYSHIPPED,count(item) as LINECOUNT from OESalesDetails
where TRANSDATe= convert(varchar(10),getdate()-1, 112) and TRANSTYPE=1
group by item,INVNUMBER) sales
right join
(select ITEMNO,GPMNAME,GPMID,EXID,CP01 from PRINFOSKF where GPMNAME like ?
and brand in (select distinct BRAND from gpmbrand where [name] like ? )) as item
on sales.ITEM=item.ITEMNO
group by GPMNAME,GPMID,EXID,CP01
""",
conn,
params=(name, name))
GPM_sales_list = gpm_sales_inv_line['SalesValue'].to_list()
gpm_invoice_list = gpm_sales_inv_line['num_of_inv'].to_list()
gpm_invoice_line_list = gpm_sales_inv_line['num_of_inv_line'].to_list()
# print(GPM_sales_list)
GPM_SALES_SUM = sum(GPM_sales_list)
gpm_invoice_sum = sum(gpm_invoice_list)
gpm_invoice_line_sum = sum(gpm_invoice_line_list)
# print(gpm_invoice_sum)
# print('Sales', GPM_SALES_SUM)
# print('five')
if len(str(int(GPM_SALES_SUM))) >= 8:
Sales_value_in_crore = str("{:.2f}".format(
(GPM_SALES_SUM / 10000000), 2)) + ' Cr'
else:
Sales_value_in_crore = str("{:.2f}".format(
(GPM_SALES_SUM / 1000000), 2)) + ' M'
# print('Sales', Sales_value_in_crore)
try:
achievement_target_sales = str("{:.2f}".format(
((GPM_SALES_SUM / GPM_TARGET_SUM) * 100), 2)) + ' %'
# print(achievement_target_sales)
except:
achievement_target_sales = str(0) + ' %'
brand_no_of_invoice = str("{:.2f}".format(gpm_invoice_sum / 1000,
2)) + ' K'
# print(brand_no_of_invoice)
if gpm_invoice_sum <= 0:
average_no_of_invoice_line = 0
else:
average_no_of_invoice_line = str("{:.2f}".format(
gpm_invoice_line_sum / gpm_invoice_sum, 2))
# print(average_no_of_invoice_line)
# # --------------------------- MTD summery --------------------------------------------------
# # ------------------------------------------------------------------------------------------
mtd_sales_df = pd.read_sql_query(
""" select GPMNAME,GPMID,EXID,CP01,isnull(sum(EXTINVMISC), 0) as SalesValue,
isnull(count(distinct INVNUMBER), 0) as num_of_inv,
isnull(sum(LINECOUNT), 0) as num_of_inv_line from (
select item,INVNUMBER,sum(EXTINVMISC) as EXTINVMISC,sum(QTYSHIPPED) as QTYSHIPPED,count(item) as LINECOUNT from OESalesDetails
where TRANSDATE between convert(varchar(8),DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),112)
and convert(varchar(10),getdate()-1, 112)
group by item,INVNUMBER) sales
right join
(select ITEMNO,GPMNAME,GPMID,EXID,CP01 from PRINFOSKF where GPMNAME like ?
and brand in (select distinct BRAND from gpmbrand where [name] like ? )) as item
on sales.ITEM=item.ITEMNO
group by GPMNAME,GPMID,EXID,CP01 """,
conn,
params=(name, name))
mtd_sale = sum(mtd_sales_df.SalesValue.tolist())
if len(str(int(mtd_sale))) >= 8:
mtd_sales = str("{:.2f}".format((mtd_sale / 10000000), 2)) + ' Cr'
else:
mtd_sales = str("{:.2f}".format((mtd_sale / 1000000), 2)) + ' M'
# # ----- MTD Target --------------------------------------------
import datetime
now = datetime.datetime.now()
current_date = now.day
mtd_target = GPM_TARGET_SUM * (current_date - 1)
if len(str(int(mtd_target))) >= 8:
mtd_targets = str("{:.2f}".format((mtd_target / 10000000), 2)) + ' Cr'
else:
mtd_targets = str("{:.2f}".format((mtd_target / 1000000), 2)) + ' M'
# # ----------------- mtd_achivement ---------------------------------------------------------
try:
mtd_achiv = str("{:.2f}".format((mtd_sale / mtd_target) * 100)) + ' %'
except:
mtd_achiv = '0 %'
# # ------------- Growth Rate ------------------------------------------------------------------
growth_df = pd.read_sql_query(
""" select sum(LastMonthMTDSales) as LastMonthMTDSales, sum(ThisMonthMTDSales) as ThisMonthMTDSales
from (
select ITEM,
sum(case when TRANSDATE between convert(varchar(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0), 112)
and left(convert(varchar(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1,0), 112), 6) + right(convert(varchar(8), GETDATE()-1, 112), 2)
and TRANSTYPE=1 then EXTINVMISC else 0 end) as LastMonthMTDSales,
isnull(sum(case when TRANSDATE between convert(varchar(8),DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),112)
and convert(varchar(10),getdate()-1, 112) and TRANSTYPE=1 then EXTINVMISC else 0 end), 0) as ThisMonthMTDSales
from OESalesDetails
group by ITEM
) sales
right join
(select ITEMNO,GPMNAME,GPMID,EXID,CP01 from PRINFOSKF where GPMNAME like ?
and brand in (select distinct BRAND from gpmbrand where [name] like ? )) as item
on sales.ITEM=item.ITEMNO
where LastMonthMTDSales is not null
""",
conn,
params=(name, name))
last_month_mtd_sales = int(growth_df.LastMonthMTDSales)
thismonth_mtd_sales = int(growth_df.ThisMonthMTDSales)
mtd_growth = str("{:.2f}".format(
((thismonth_mtd_sales - last_month_mtd_sales) / last_month_mtd_sales) *
100)) + ' %'
# # ------------- MTD Trend --------------------------------------------------------------------
days_in_month = calendar.monthrange(now.year, now.month)[1]
try:
mtd_trend = ((mtd_sale / (current_date - 1)) * days_in_month)
if len(str(int(mtd_trend))) >= 8:
mtd_trend = str("{:.2f}".format((mtd_trend / 10000000), 2)) + ' Cr'
else:
mtd_trend = str("{:.2f}".format((mtd_trend / 1000000), 2)) + ' M'
except:
mtd_trend = '0'
# # -------------------------------------------------------------------------------------------
image = Image.open(dir.get_directory() + "/Images/dash_kpi.png")
draw = ImageDraw.Draw(image)
font = ImageFont.truetype(
dir.get_directory() + '/Images/FrancoisOne-Regular.ttf', 30)
draw.text((80, 83),
str(total_brand_list[0]),
font=font,
fill=(39, 98, 236))
draw.text((225, 83),
str(len(y2)) + ' (' + brand_coverage + ')',
font=font,
fill=(39, 95, 236))
draw.text((460, 83), str(total_sku_list[0]), font=font, fill=(39, 98, 236))
draw.text((610, 83),
str(sold_sku_list[0]) + ' (' + sold_sku_percentage + ')',
font=font,
fill=(39, 98, 236))
draw.text((805, 83),
str(no_stock_sku_list[0]) + ' (' + No_stock_sku_percentage + ')',
font=font,
fill=(39, 98, 236))
draw.text((60, 177), Target_value_in_crore, font=font, fill=(39, 98, 236))
draw.text((250, 177), Sales_value_in_crore, font=font, fill=(39, 98, 236))
draw.text((425, 177),
achievement_target_sales,
font=font,
fill=(39, 98, 236))
draw.text((635, 177), brand_no_of_invoice, font=font, fill=(39, 98, 236))
draw.text((835, 177),
str(average_no_of_invoice_line),
font=font,
fill=(39, 98, 236))
draw.text((60, 315), mtd_targets, font=font, fill=(0, 0, 0))
draw.text((250, 315), mtd_sales, font=font, fill=(0, 0, 0))
draw.text((425, 315), mtd_achiv, font=font, fill=(0, 0, 0))
draw.text((610, 315), mtd_trend, font=font, fill=(0, 0, 0))
draw.text((820, 315), mtd_growth, font=font, fill=(0, 0, 0))
# image.show()
image.save('./Images/dashboard.png')
print('3. Dash generated\n')
def banner(name):
date = datetime.today()
day = str(date.day) + '/' + str(date.month) + '/' + str(date.year)
tz_NY = pytz.timezone('Asia/Dhaka')
datetime_BD = datetime.now(tz_NY)
time = datetime_BD.strftime("%I:%M %p")
date = datetime.today()
x = dd.datetime.now()
day = str(date.day) + '-' + str(x.strftime("%b")) + '-' + str(date.year)
tz_NY = pytz.timezone('Asia/Dhaka')
datetime_BD = datetime.now(tz_NY)
time = datetime_BD.strftime("%I:%M %p")
df_designation = pd.read_sql_query(
""" select Name, Designation from GPMBRAND
where Name like ?
group by Name,Designation """,
dbc.connection,
params={name})
date_time = pd.read_sql_query(
""" select convert(varchar, max(datetime),6) as [date],
FORMAT(CAST(max(datetime) AS datetime), 'hh:mm tt') as [time]
from OESalesDetails
where LEFT(TRANSDATE, 8) = convert(varchar(8), getdate(), 112) """,
dbc.connection)
up_date = str(date_time["date"][0])
up_time = date_time['time'][0]
# print(type(up_date), type(up_time))
#
# im = Image.open(dir.get_directory() + '/Images/Person.png')
# im = im.resize((120, 120));
# bigsize = (im.size[0] * 10, im.size[1] * 10)
# mask = Image.new('L', bigsize, 0)
# draw = ImageDraw.Draw(mask)
# draw.ellipse((0, 0) + bigsize, fill=255)
# mask = mask.resize(im.size, Image.ANTIALIAS)
# im.putalpha(mask)
# background = Image.open(dir.get_directory() + '/Images/new_ai.png')
# background.paste(im, (820, 70), im)
# background.save(dir.get_directory() + '/images/overlap.png')
img = Image.open(dir.get_directory() + "/Images/new_ai.png")
update_time = ImageDraw.Draw(img)
timestore = ImageDraw.Draw(img)
tag = ImageDraw.Draw(img)
branch = ImageDraw.Draw(img)
font = ImageFont.truetype(dir.get_directory() +
"/images/Stencil_Regular.ttf",
40,
encoding="unic")
name_font = ImageFont.truetype(dir.get_directory() +
"/images/Lobster-Regular.ttf",
30,
encoding="unic")
font1 = ImageFont.truetype(dir.get_directory() + "/images/ROCK.ttf",
30,
encoding="unic")
font2 = ImageFont.truetype(dir.get_directory() + "/images/ROCK.ttf",
18,
encoding="unic")
font3 = ImageFont.truetype(dir.get_directory() + "/images/ROCK.ttf",
12,
encoding="unic")
report_name = 'GPM '
# n = gpm_name
# tag.text((25, 8), 'SK+F', (255, 255, 255), font=font)
branch.text((24, 150),
report_name + "Sales and Stock Report (" + day + ")",
(255, 209, 0),
font=font1)
# name.text((25, 180), n , (255, 255, 255), font=name_font)
# timestore.text((25, 200), time + "\n" + day, (255, 255, 255), font=font2)
timestore.text((25, 200), name, (244, 118, 1), font=font2)
timestore.text((25, 220),
"Group Product Manager", (255, 255, 255),
font=font2)
timestore.text((815, 10),
"Last Data Update Time\n" + str(up_date) + ' , ' + up_time,
(255, 255, 255),
font=font3)
# timestore.text((705, 180), "Name", (255, 255, 255), font=font2)
# timestore.text((705, 180), "Designation", (255, 255, 255), font=font2)
img.save(dir.get_directory() + "/images/banner_ai.png")
# img.show()
print("1. Banner Created \n ")
def get_custom_scripts_path():
return path.get_directory('scripts')
def send_mail(gpm_name):
import Functions.banner_code as ban
import Functions.generate_data as gdata
import Functions.dashboard as dash
import Functions.cumulative_target_sales as cm
import Functions.executive_wise_sales_target as ex
import Functions.brand_wise_target_sales as b
ban.banner()
# gdata.GenerateReport(gpm_name)
# dash.dash_kpi_generator(gpm_name)
# cm.cumulative_target_sales(gpm_name)
# ex.executive_sales_target(gpm_name)
# b.brand_wise_target_sales()
#
# # # --------- Add Image Border ---------------------------------------
from PIL import Image
# da = Image.open("./Images/dashboard.png")
# imageSize = Image.new('RGB', (962, 297))
# imageSize.paste(da, (1, 0))
# imageSize.save("./Images/dashboard.png")
#
# kpi1 = Image.open("./Images/Cumulative_Day_Wise_Target_vs_Sales.png")
# imageSize = Image.new('RGB', (962, 481))
# imageSize.paste(kpi1, (1, 0))
# imageSize.save("./Images/Cumulative_Day_Wise_Target_vs_Sales.png")
#
# kpi2 = Image.open("./Images/executive_wise_target_vs_sold_quantity.png")
# imageSize = Image.new('RGB', (962, 481))
# imageSize.paste(kpi2, (1, 0))
# imageSize.save("./Images/executive_wise_target_vs_sold_quantity.png")
# #
# kpi3 = Image.open("./Images/brand_wise_target_vs_sold_quantity.png")
# imageSize = Image.new('RGB', (962, 481))
# imageSize.paste(kpi3, (1, 0))
# imageSize.save("./Images/brand_wise_target_vs_sold_quantity.png")
# # -------------------------------------------------------------------
# # ------ Branch wise stock summery -----------------------------------
import sys
# df = pd.read_excel('Data/gpm_data.xlsx')
#
# cols = range(52, 83)
# df2 = pd.read_excel('Data/gpm_data.xlsx', usecols=cols)
# l = list(df2.columns)
#
# cols2 = range(19, 50)
# df3 = pd.read_excel('Data/gpm_data.xlsx', usecols=cols2)
# l2 = list(df3.columns)
#
# final_list = []
#
# for branches, avgq in zip(l, l2):
# branch_current_stock = df[branches].to_list()
#
# branch_avg_sell = df[avgq].to_list()
#
# for each, each2 in zip(branch_current_stock, branch_avg_sell):
# if each == 0:
# index = branch_current_stock.index(each)
# branch_current_stock[index] = 1
# if each2 == 0:
# index2 = branch_avg_sell.index(each2)
# branch_avg_sell[index2] = 1
#
# np.seterr(divide='ignore', invalid='ignore')
# branch_stock_limit = np.divide(branch_current_stock, branch_avg_sell)
# branch_stock_limit_list = list(branch_stock_limit)
#
# new_list_nill = []
# new_list_super_under_stock = []
# new_list_under_stock = []
# new_list_normal_stock = []
# new_list_over_stock = []
# new_list_super_over_stock = []
#
# for i in branch_stock_limit_list:
# # Color for NIL
# if i <= 0:
# new_list_nill.append(i)
# # Super Under Stock
# elif i <= 15:
# new_list_super_under_stock.append(i)
#
# # Under Stock
# elif i <= 35:
# new_list_under_stock.append(i)
#
# # Color for Normal
# elif i <= 45:
# new_list_normal_stock.append(i)
#
# # Color for Over Stock
# elif i <= 60:
# new_list_over_stock.append(i)
# else:
# # Super over stock
# new_list_super_over_stock.append(i)
#
# sum_new_list_nill = len(new_list_nill)
# sum_new_list_super_under_stock = len(new_list_super_under_stock)
# sum_new_list_under_stock = len(new_list_under_stock)
# sum_new_list_normal_stock = len(new_list_normal_stock)
# sum_new_list_over_stock = len(new_list_over_stock)
# sum_new_list_super_over_stock = len(new_list_super_over_stock)
#
# sum_list = [sum_new_list_nill, sum_new_list_super_under_stock, sum_new_list_under_stock,
# sum_new_list_normal_stock, sum_new_list_over_stock, sum_new_list_super_over_stock]
# final_list.append(sum_list)
#
# workbook = xlsxwriter.Workbook('Data/branch_wise_nil_us_ss.xlsx')
# worksheet = workbook.add_worksheet()
#
# len = range(1, len(l) + 1)
#
# for i, j in zip(l, len):
# worksheet.write(j, 0, i)
#
# stock_list = ['Branch', 'Nill', 'Super Under Stock', 'Under Stock', 'Normal Stock', 'Over Stock',
# 'Super Over Stock']
#
# len2 = range(0, 7)
# for i, j in zip(stock_list, len2):
# worksheet.write(0, j, i)
#
# # Branch wise value set
# all_row_length = range(0, 31)
# all_len = range(0, 6)
#
# for a in all_row_length:
# for b in all_len:
# c = final_list[a][b]
# # print(c)
# worksheet.write(a + 1, b + 1, c)
# workbook.close()
# print('Branch wise stock summery generated')
# # ------------- HTML generating section ------------------------------
data = pd.read_excel('./Data/html_data_Sales_and_Stock.xlsx')
if data.empty:
print('No data for print')
else:
print('Layout Generating')
layout.generate_layout(gpm_name)
# to = gpm.getGPMEmail(gpm_name)
#
# if (to == ['*****@*****.**', '']):
# to = ['*****@*****.**', '']
# cc = ['', '']
# bcc = ['', '']
# print('Report Sending to = ', to)
to = ['*****@*****.**', '']
cc = ['', '']
bcc = ['', '']
msgRoot = MIMEMultipart('related')
me = '*****@*****.**'
# to = to
# cc = ['*****@*****.**', '*****@*****.**', '*****@*****.**']
# bcc = ['*****@*****.**', '*****@*****.**', '*****@*****.**']
recipient = to + cc + bcc
date = datetime.today()
today = str(date.day) + '/' + str(date.month) + '/' + str(
date.year) + ' ' + date.strftime("%I:%M %p")
# # ------------ Group email --------------------
subject = "Brand Wise Sales and Stock Report " + today
email_server_host = 'mail.transcombd.com'
port = 25
msgRoot['From'] = me
msgRoot['To'] = ', '.join(to)
msgRoot['Cc'] = ', '.join(cc)
msgRoot['Bcc'] = ', '.join(bcc)
msgRoot['Subject'] = subject
# msgRoot['to'] = recipient
# msgRoot['from'] = me
# msgRoot['subject'] = subject
msgAlternative = MIMEMultipart('alternative')
msgRoot.attach(msgAlternative)
msgText = MIMEText('This is the alternative plain text message.')
msgAlternative.attach(msgText)
# # We reference the image in the IMG SRC attribute by the ID we give it below
msgText = MIMEText(
"""
""" + layout.generate_layout(gpm_name) + """
""", 'html')
msgAlternative.attach(msgText)
# --- Read Banner Images
fp = open(d.get_directory() + '/images/banner_ai.png', 'rb')
banner_ai = MIMEImage(fp.read())
fp.close()
banner_ai.add_header('Content-ID', '<banner_ai>')
msgRoot.attach(banner_ai)
# --- Read Dashboard KPI Images
fp = open(d.get_directory() + '/images/dashboard.png', 'rb')
dash = MIMEImage(fp.read())
fp.close()
dash.add_header('Content-ID', '<dash>')
msgRoot.attach(dash)
# --- Read Cumulative Target & Sales Images
fp = open(
d.get_directory() + '/images/Cumulative_Day_Wise_Target_vs_Sales.png',
'rb')
cm = MIMEImage(fp.read())
fp.close()
cm.add_header('Content-ID', '<cm>')
msgRoot.attach(cm)
# --- Read Cumulative Target & Sales Images
fp = open(
d.get_directory() +
'/images/executive_wise_target_vs_sold_quantity.png', 'rb')
executive = MIMEImage(fp.read())
fp.close()
executive.add_header('Content-ID', '<executive>')
msgRoot.attach(executive)
# --- Read Cumulative Target & Sales Images
fp = open(
d.get_directory() + '/images/brand_wise_target_vs_sold_quantity.png',
'rb')
brand = MIMEImage(fp.read())
fp.close()
brand.add_header('Content-ID', '<brand>')
msgRoot.attach(brand)
# Add GPM sales and stock dataset
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory() + '/Data/Sales_and_Stock.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# Add GPM No Sales dataset
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory() + '/Data/NoSales.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# Add GPM No Stock dataset
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory() + '/Data/NoStock.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# #----------- Finally send mail and close server connection -----
server = smtplib.SMTP(email_server_host, port)
server.ehlo()
print('\n-----------------')
print('Sending Mail')
server.sendmail(me, recipient, msgRoot.as_string())
print('Mail Send')
print('-------------------')
server.close()
def send_mail(gpm_name):
start_time = time.time()
print(
'This project has 19 KPI, It takes time to generate. Keep patience.\n')
import Functions.banner_code as ban
import Functions.generate_data as gdata
import Functions.dashboard as dash
import Functions.cumulative_target_sales as cm
import Functions.executive_wise_sales_target as ex
import Functions.executive_stackedbarchart1 as stacked
import Functions.brand_wise_target_sales as b
import Functions.brand_wise_aging_information_bar as brand_bar
import Functions.SKU_wise_aging_information_bar as SKU_bar
import Functions.quantity_wise_aging_information_bar as quantity_bar
import Functions.item_wise_stock_days_data as item_stock_days_data
import Functions.branch_stock_summery_data as bsdata
import Functions.arranging_the_column_size as attachdata
ban.banner(gpm_name) # 01
gdata.GenerateReport(gpm_name) # 02
dash.dash_kpi_generator(gpm_name) # 03
cm.cumulative_target_sales(gpm_name) # 4
ex.executive_sales_target(gpm_name) # 5
stacked.executives_brand_target_sales_chart(gpm_name) # 6
b.brand_wise_target_sales() # 7
brand_bar.stock_aging_chart(gpm_name) # 8.1
SKU_bar.stock_aging_chart(gpm_name) # 8.2
quantity_bar.stock_aging_chart(gpm_name) # 8.3
item_stock_days_data.create_item_wise_stock_days_data(
) # 9 This data used in branch_stock_summery.py file
bsdata.branch_stock_summery_data() # 10
## 11 to 17 KPI are comes from "design_report_layout.py" file in ascending order.
# --------- Add Image Border ---------------------------------------
from PIL import Image
da = Image.open("./Images/dashboard.png")
imageSize = Image.new('RGB', (962, 375))
imageSize.paste(da, (1, 0))
imageSize.save("./Images/dashboard.png")
kpi1 = Image.open("./Images/Cumulative_Day_Wise_Target_vs_Sales.png")
imageSize = Image.new('RGB', (962, 481))
imageSize.paste(kpi1, (1, 0))
imageSize.save("./Images/Cumulative_Day_Wise_Target_vs_Sales.png")
kpi2 = Image.open("./Images/executive_wise_target_vs_sold_quantity.png")
imageSize = Image.new('RGB', (962, 481))
imageSize.paste(kpi2, (1, 0))
imageSize.save("./Images/executive_wise_target_vs_sold_quantity.png")
kpistock = Image.open("./Images/mainexecutive.png")
imageSize = Image.new('RGB', (1802, 901))
imageSize.paste(kpistock, (1, 0))
imageSize.save("./Images/main_executive_with_margin.png")
kpi3 = Image.open("./Images/brand_wise_target_vs_sold_quantity.png")
imageSize = Image.new('RGB', (1802, 601))
imageSize.paste(kpi3, (1, 0))
imageSize.save("./Images/brand_wise_target_vs_sold_quantity.png")
kpi7_1 = Image.open("./Images/brand_wise_aging_stock_information.png")
kpi7_2 = Image.open("./Images/SKU_wise_aging_stock_information.png")
kpi7_3 = Image.open("./Images/Quantity_wise_aging_stock_information.png")
img = Image.new('RGB', (1804, 481))
img.paste(kpi7_1, (1, 0))
img.paste(kpi7_2, (602, 0))
img.paste(kpi7_3, (1203, 0))
img.save("./Images/aging_stock_information.png")
# to = gpm.getGPMEmail(gpm_name)
# print('mail send to ', to)
# if (to == ['*****@*****.**', '']):
# to = ['*****@*****.**', '']
# cc = ['', '']
# bcc = ['', '']
# print('Report Sending to = ', to)
# to = ['*****@*****.**', '*****@*****.**']
# cc = ['*****@*****.**', '']
# bcc = ['*****@*****.**','*****@*****.**', '*****@*****.**']
# to = ['*****@*****.**', '*****@*****.**','*****@*****.**']
# cc = ['*****@*****.**', '*****@*****.**']
# bcc = ['*****@*****.**', '*****@*****.**', '']
msgRoot = MIMEMultipart('related')
me = '*****@*****.**'
to = ['*****@*****.**', '']
cc = ['', '']
bcc = ['', '']
# cc = ['*****@*****.**', '*****@*****.**', '*****@*****.**']
# bcc = ['*****@*****.**', '']
# cc = ['', '']
# bcc = ['*****@*****.**', '*****@*****.**', '*****@*****.**']
print('mail send to ', to)
recipient = to + cc + bcc
date = datetime.today()
today = str(date.day) + '/' + str(date.month) + '/' + str(
date.year) + ' ' + date.strftime("%I:%M %p")
# # ------------ Group email -------------------------
subject = "Brand Wise Sales and Stock Report " + today
email_server_host = 'mail.transcombd.com'
port = 25
msgRoot['From'] = me
msgRoot['To'] = ', '.join(to)
msgRoot['Cc'] = ', '.join(cc)
msgRoot['Bcc'] = ', '.join(bcc)
msgRoot['Subject'] = subject
# msgRoot['to'] = recipient
# msgRoot['from'] = me
# msgRoot['subject'] = subject
msgAlternative = MIMEMultipart('alternative')
msgRoot.attach(msgAlternative)
msgText = MIMEText('This is the alternative plain text message.')
msgAlternative.attach(msgText)
# # We reference the image in the IMG SRC attribute by the ID we give it below
msgText = MIMEText(
"""
""" + layout.generate_layout(gpm_name) + """
""", 'html')
msgAlternative.attach(msgText)
# --- Read Banner Images
fp = open(d.get_directory() + '/images/banner_ai.png', 'rb')
banner_ai = MIMEImage(fp.read())
fp.close()
banner_ai.add_header('Content-ID', '<banner_ai>')
msgRoot.attach(banner_ai)
# --- Read Dashboard KPI Images
fp = open(d.get_directory() + '/images/aging_stock_information.png', 'rb')
aging = MIMEImage(fp.read())
fp.close()
aging.add_header('Content-ID', '<aging>')
msgRoot.attach(aging)
# --- Read Dashboard KPI Images
fp = open(d.get_directory() + '/images/dashboard.png', 'rb')
dash = MIMEImage(fp.read())
fp.close()
dash.add_header('Content-ID', '<dash>')
msgRoot.attach(dash)
# --- Read Cumulative Target & Sales Images
fp = open(
d.get_directory() + '/images/Cumulative_Day_Wise_Target_vs_Sales.png',
'rb')
cm = MIMEImage(fp.read())
fp.close()
cm.add_header('Content-ID', '<cm>')
msgRoot.attach(cm)
# --- Read Cumulative Target & Sales Images
fp = open(
d.get_directory() +
'/images/executive_wise_target_vs_sold_quantity.png', 'rb')
executive = MIMEImage(fp.read())
fp.close()
executive.add_header('Content-ID', '<executive>')
msgRoot.attach(executive)
fp = open(d.get_directory() + '/images/main_executive_with_margin.png',
'rb')
executive_main = MIMEImage(fp.read())
fp.close()
executive_main.add_header('Content-ID', '<executive_main>')
msgRoot.attach(executive_main)
# --- Read Cumulative Target & Sales Images
fp = open(
d.get_directory() + '/images/brand_wise_target_vs_sold_quantity.png',
'rb')
brand = MIMEImage(fp.read())
fp.close()
brand.add_header('Content-ID', '<brand>')
msgRoot.attach(brand)
# # --- Logo
# fp = open(d.get_directory() + '/images/logo.png', 'rb')
# logo = MIMEImage(fp.read())
# fp.close()
# logo.add_header('Content-ID', '<logo>')
# msgRoot.attach(logo)
# # # -----------------------------------------------------
# # # --------- 19 Process all attached data --------------
attachdata.dataFormating()
# # 1. Add GPM branch wise aging Stock dataset
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory(
) + '/Data/branch_wise_aging_stock_copy.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# 2. Add GPM branch wise stock status dataset
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory(
) + '/Data/branch_wise_stock_status_copy.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# 3. Add GPM item wise yesterday sales dataset
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory(
) + '/Data/item_wise_yesterday_sales_copy.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# 4. Add GPM last three month no sales dataset
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory(
) + '/Data/NoSales_last_three_month_copy.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# 5. Add GPM No Stock last three month dataset
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory(
) + '/Data/NoStock_last_three_month_copy.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# 6. Add GPM yesterday no sales dataset
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory() + '/Data/yesterday_no_sales_copy.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# 7. Add GPM yesterday no sales dataset
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory(
) + '/Data/branch_wise_detailed_data_Sales_and_Stock-Copy.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# 7. Region wise items yesterday sales
part = MIMEBase('application', "octet-stream")
file_location = d.get_directory(
) + '/Data/items_region_wise_yesterday_sales.xlsx'
filename = os.path.basename(file_location)
attachment = open(file_location, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
"attachment; filename= %s" % filename)
msgRoot.attach(part)
# #----------- Finally send mail and close server connection -----
server = smtplib.SMTP(email_server_host, port)
server.ehlo()
print('\n-----------------')
print('Sending Mail')
server.sendmail(me, recipient, msgRoot.as_string())
print('Mail Send')
print('-------------------')
server.close()
print('Time takes = ', round((time.time() - start_time) / 60, 2), 'Min')
def get_packages_metadata_path():
return path.get_directory('metadata', 'packages')
def get_library_path():
return path.get_directory('library')
def get_packages_path():
return path.get_directory('packages')