def create_terms_wise_outstanding(branc_names):
outstanding_df = pd.read_sql_query(""" select
SUM(CASE WHEN TERMS='CASH' THEN OUT_NET END) AS TotalOutStandingOnCash,
SUM(CASE WHEN TERMS not like '%CASH%' THEN OUT_NET END) AS TotalOutStandingOnCredit
from [ARCOUT].dbo.[CUST_OUT]
where AUDTORG like ? AND [INVDATE] <= convert(varchar(8),DATEADD(D,0,GETDATE()),112)
""", func.con, params={branc_names})
cash = int(outstanding_df['TotalOutStandingOnCash'])
credit = int(outstanding_df['TotalOutStandingOnCredit'])
data = [cash, credit]
total = cash + credit
total = 'Total \n' + func.joker(total)
colors = ['#f9ff00', '#ff8600']
legend_element = [Patch(facecolor='#f9ff00', label='Cash'),
Patch(facecolor='#ff8600', label='Credit')]
# -------------------new code--------------------------
ca = func.joker(cash)
cre = func.joker(credit)
DataLabel = [ca, cre]
# -----------------------------------------------------
fig1, ax = plt.subplots()
wedges, labels, autopct = ax.pie(data, colors=colors, labels=DataLabel, autopct='%.1f%%', startangle=90,
pctdistance=.7)
plt.setp(autopct, fontsize=14, color='black', fontweight='bold')
plt.setp(labels, fontsize=14, fontweight='bold')
ax.text(0, -.1, total, ha='center', fontsize=14, fontweight='bold', backgroundcolor='#00daff')
centre_circle = plt.Circle((0, 0), 0.50, fc='white')
fig = plt.gcf()
fig.gca().add_artist(centre_circle)
# Equal aspect ratio ensures that pie is drawn as a circle
plt.title('1. Total Outstanding', fontsize=16, fontweight='bold', color='#3e0a75')
ax.axis('equal')
plt.legend(handles=legend_element, loc='lower left',
fontsize=11)
plt.tight_layout()
plt.savefig('./Images/terms_wise_outstanding.png')
print('01: Terms wise Outstanding Generated')
plt.close()
def category_wise_credit(branch_name):
credit_category_df = pd.read_sql_query(
""" Select isnull(case when Days_Diff>0 then 'Matured Credit' else 'Regular Credit' End, 0) as 'Category',isnull(Sum(OUT_NET), 0) as Amount from
(select INVNUMBER,INVDATE,
CUSTOMER,TERMS,MAINCUSTYPE,
CustomerInformation.CREDIT_LIMIT_DAYS,
datediff([dd] , CONVERT (DATETIME , LTRIM(cust_out.INVDATE) , 102) , GETDATE())+1-CREDIT_LIMIT_DAYS as Days_Diff,
OUT_NET from [ARCOUT].dbo.[CUST_OUT]
join ARCHIVESKF.dbo.CustomerInformation
on [CUST_OUT].CUSTOMER = CustomerInformation.IDCUST
where [ARCOUT].dbo.[CUST_OUT].AUDTORG like ? and TERMS<>'Cash') as TblCredit
group by case when Days_Diff>0 then 'Matured Credit' else 'Regular Credit' end
""",
func.con,
params={branch_name})
matured = int(credit_category_df.Amount.iloc[0])
not_mature = int(credit_category_df.Amount.iloc[1])
values = [matured, not_mature]
colors = ['#ffb667', '#b35e00']
legend_element = [
Patch(facecolor='#ffb667', label='Matured'),
Patch(facecolor='#b35e00', label='Not Mature')
]
total_credit = matured + not_mature
total_credit = 'Total \n' + func.joker(total_credit)
# ------------------new code--------------------
matured = func.joker(matured)
not_mature = func.joker(not_mature)
DataLabel = [matured, not_mature]
# -----------------------------------------------
fig1, ax = plt.subplots()
wedges, labels, autopct = ax.pie(values,
colors=colors,
labels=DataLabel,
autopct='%.1f%%',
startangle=120,
pctdistance=.7)
plt.setp(autopct, fontsize=14, color='black', fontweight='bold')
plt.setp(labels, fontsize=14, fontweight='bold')
ax.text(0,
-.1,
total_credit,
ha='center',
fontsize=14,
fontweight='bold',
backgroundcolor='#00daff')
# draw circle
centre_circle = plt.Circle((0, 0), 0.50, fc='white')
fig = plt.gcf()
fig.gca().add_artist(centre_circle)
# Equal aspect ratio ensures that pie is drawn as a circle
plt.title('2. Credit Outstanding',
fontsize=16,
fontweight='bold',
color='#3e0a75')
ax.axis('equal')
plt.legend(handles=legend_element, loc='lower left', fontsize=11)
plt.tight_layout()
plt.savefig('./Images/category_wise_credit.png')
print('2. Category wise Credit Generated ')
plt.close()
def matured_credit(branch_name):
mature_credit_df = pd.read_sql_query("""
Select case when MAINCUSTYPE='RETAIL' then 'Retail' else 'Institute' end as CustType,Sum(OUT_NET) as Amount from
(select INVNUMBER,INVDATE,
CUSTOMER,TERMS,MAINCUSTYPE,
CustomerInformation.CREDIT_LIMIT_DAYS,
datediff([dd] , CONVERT (DATETIME , LTRIM(cust_out.INVDATE) , 102) , GETDATE())+1-CREDIT_LIMIT_DAYS as Days_Diff,
OUT_NET from [ARCOUT].dbo.[CUST_OUT]
join ARCHIVESKF.dbo.CustomerInformation
on [CUST_OUT].CUSTOMER = CustomerInformation.IDCUST
where [ARCOUT].dbo.[CUST_OUT].AUDTORG like ? and TERMS<>'Cash') as TblCredit
where Days_Diff>0
group by case when MAINCUSTYPE='RETAIL' then 'Retail' else 'Institute' end
""",
func.con,
params={branch_name})
Institution = int(mature_credit_df.Amount.iloc[0])
try:
retail = int(mature_credit_df.Amount.iloc[1])
except:
retail = 0
values = [Institution, retail]
colors = ['#f213e5', '#bcf303']
legend_element = [
Patch(facecolor='#f213e5', label='Institution'),
Patch(facecolor='#bcf303', label='Retail')
]
Sector_total_matured = Institution + retail
Sector_total_matured = 'Total \n' + func.joker(Sector_total_matured)
Institution = func.joker(Institution)
retail = func.joker(retail)
DataLabel = [Institution, retail]
fig1, ax = plt.subplots()
wedges, labels, autopct = ax.pie(values,
colors=colors,
labels=DataLabel,
autopct='%.1f%%',
startangle=130,
pctdistance=.7)
plt.setp(autopct, fontsize=14, color='black', fontweight='bold')
plt.setp(labels, fontsize=14, fontweight='bold')
ax.text(0,
-.1,
Sector_total_matured,
ha='center',
fontsize=14,
fontweight='bold',
backgroundcolor='#00daff')
# draw circle
centre_circle = plt.Circle((0, 0), 0.50, fc='white')
fig = plt.gcf()
fig.gca().add_artist(centre_circle)
# Equal aspect ratio ensures that pie is drawn as a circle
plt.title('3. Matured Credit',
fontsize=16,
fontweight='bold',
color='#3e0a75')
ax.axis('equal')
plt.legend(handles=legend_element, loc='lower left', fontsize=11)
plt.tight_layout()
plt.savefig('./Images/matured_credit.png')
plt.close()
print('3. Sector wise Credit- Matured Generated')
def cash_outstanding(branch_name):
try:
sector_wise_cash_df = pd.read_sql_query(""" Select case when MAINCUSTYPE='RETAIL' then 'Retail' else 'Institute' end as CustType,Sum(OUT_NET) as Amount from
(select INVNUMBER,INVDATE,
CUSTOMER,TERMS,MAINCUSTYPE,
CustomerInformation.CREDIT_LIMIT_DAYS,
datediff([dd] , CONVERT (DATETIME , LTRIM(cust_out.INVDATE) , 102) , GETDATE())+1-CREDIT_LIMIT_DAYS as Days_Diff,
OUT_NET from [ARCOUT].dbo.[CUST_OUT]
join ARCHIVESKF.dbo.CustomerInformation
on [CUST_OUT].CUSTOMER = CustomerInformation.IDCUST
where [ARCOUT].dbo.[CUST_OUT].AUDTORG like ? and TERMS='Cash') as TblCredit
group by case when MAINCUSTYPE='RETAIL' then 'Retail' else 'Institute' end """, func.con,
params={branch_name})
Institution = int(sector_wise_cash_df.Amount.iloc[0])
retail = int(sector_wise_cash_df.Amount.iloc[1])
values = [Institution, retail]
colors = ['#a9e11a', '#1798ca']
legend_element = [Patch(facecolor='#a9e11a', label='Institution'),
Patch(facecolor='#1798ca', label='Retail')]
sector_total_credit = Institution + retail
sector_total_credit = "Total \n" + func.joker(sector_total_credit)
Institution = func.joker(Institution)
retail = func.joker(retail)
DataLabel = [Institution, retail]
fig1, ax = plt.subplots()
wedges, labels, autopct = ax.pie(values, colors=colors, labels=DataLabel, autopct='%.1f%%', startangle=120,
pctdistance=.7)
plt.setp(autopct, fontsize=14, color='black', fontweight='bold')
plt.setp(labels, fontsize=14, fontweight='bold')
ax.text(0, -.1, sector_total_credit, ha='center', fontsize=14, fontweight='bold', backgroundcolor='#b1bec1')
# draw circle
centre_circle = plt.Circle((0, 0), 0.50, fc='white')
fig = plt.gcf()
fig.gca().add_artist(centre_circle)
# Equal aspect ratio ensures that pie is drawn as a circle
plt.title('7. Cash Outstanding', fontsize=16, fontweight='bold', color='#3e0a75')
ax.axis('equal')
plt.legend(handles=legend_element, loc='lower left', fontsize=11)
plt.tight_layout()
plt.savefig('./Images/Category_wise_cash.png')
plt.close()
print('7. Category wise Cash Generated')
except:
plt.figure(figsize=(12.81, 4.8))
plt.title('7. Cash Outstanding', fontsize=16, fontweight='bold', color='#3e0a75')
plt.text(0.5, 0.5, str('Sorry! this figure cannot be generated'), fontsize=25,
color='red',
horizontalalignment='center', verticalalignment='center')
plt.axis('off')
# plt.show()
plt.savefig('./Images/Category_wise_cash.png')
plt.close()
print('No data')