import base64
import datetime as dt
import gcsfs
import html
import json
import os
import pandas as pd
import pdfgen
import requests
import sqlalchemy
from base64 import b64decode
from bson.objectid import ObjectId
from google.cloud import storage
from pytz import UTC
from jinja2 import Environment, FileSystemLoader
conn_url = os.environ['sql_conn']
engine = sqlalchemy.create_engine(conn_url)
conn = engine.connect()
client = storage.Client()
bucket_name = 'bcdn-testbook'
templates_path = 'invoices/template/'
pdf_path = "invoices/invoice_pdfs/"
env = Environment(loader=FileSystemLoader('/'))
def hello_world(request):
if request.method != 'POST':
return ('invalid method', 200)
data = request.get_json(silent=True)
transId = data.get('transId')
if not transId:
return ('transId is missing', 400)
inv_date = dt.datetime.strptime(ObjectId(transId).generation_time.strftime("%m-%Y"), "%m-%Y")
apr_22 = dt.datetime.strptime("04-2022", "%m-%Y")
mar_23 = dt.datetime.strptime("03-2023", "%m-%Y")
apr_23 = dt.datetime.strptime("04-2023", "%m-%Y")
oct_23 = dt.datetime.strptime("10-2023", "%m-%Y")
if apr_22 <= inv_date <= mar_23:
query = f"SELECT * FROM invoice_data_22_23 WHERE transId = '{transId}';"
invoice_dict = generateInvoicePdf(query, transId)
return ({'data': invoice_dict, 'dates': inv_date}, 200)
elif apr_23 <= inv_date < oct_23:
query = f"SELECT * FROM invoice_data_23_24 WHERE transId = '{transId}';"
invoice_dict = generateInvoicePdf(query, transId)
return ({'data': invoice_dict, 'dates': inv_date}, 200)
elif inv_date >= oct_23:
query = f"SELECT * FROM invoice_data_new_23_24 WHERE transId = '{transId}';"
invoice_dict = generateMultipleInvoicePdf(query, transId)
return ({'data': invoice_dict, 'dates': inv_date}, 200)
else:
return ('Invalid date', 400)
# CreateINV PDF
def createInvoicePdf(context, transId, pdf_name):
bucket = client.get_bucket(bucket_name)
blob = bucket.blob(os.path.join(templates_path, 'invoice_template_new.html'))
template_str = blob.download_as_string().decode('utf-8')
template = env.from_string(template_str)
html_str = template.render(context)
encoded_html = base64.b64encode(html_str.encode('utf-8')).decode('utf-8')
url = "https://in5jf9a235.execute-api.ap-south-1.amazonaws.com/prod/htmltopdf"
headers = {
"ssotoken": ""
}
payload = {
"html_base64": encoded_html,
"options.windowStatus":"print"
}
htmlToPdf = requests.post(url, json=payload, headers = headers)
pdf_base64 = htmlToPdf.text
pdf_base64 = json.loads(pdf_base64)
pdf_data = pdf_base64['pdf_base64']
pdf_data = b64decode(pdf_data, validate=True)
pdf_blob = bucket.blob(os.path.join(pdf_path, pdf_name))
pdf_blob.upload_from_string(pdf_data, content_type='application/pdf')
download_link_pdf = f"https://bcdn.testbook.com/invoices/invoice_pdfs/{pdf_name}"
return download_link_pdf
# Pass and PassPro calculations for Invoice amount along with taxes are as follows: E-Book: 5% Tax and Course: 18% Tax #####################################
def passCalculations(bookedAmount, centerState, userState2):
CGST1 = 0
SGST1 = 0
IGST1 = 0
CGST2 = 0
SGST2 = 0
IGST2 = 0
bookedAmountWOTaxEbook = ((int(bookedAmount)*90)/100)/1.05
bookedAmountWOTaxEvaluation = ((int(bookedAmount)*10)/100)/1.18
if centerState == userState2:
CGST1 = (bookedAmountWOTaxEbook*2.5)/100
SGST1 = (bookedAmountWOTaxEbook*2.5)/100
CGST2 = (bookedAmountWOTaxEvaluation*9)/100
SGST2 = (bookedAmountWOTaxEvaluation*9)/100
elif centerState != userState2:
IGST1 = (bookedAmountWOTaxEbook*5)/100
IGST2 = (bookedAmountWOTaxEvaluation*18)/100
bookedAmountWOTaxEbook = round(bookedAmountWOTaxEbook, 2)
CGST1 = round(CGST1, 2)
SGST1 = round(SGST1, 2)
IGST1 = round(IGST1, 2)
bookedAmountWOTaxEvaluation = round(bookedAmountWOTaxEvaluation, 2)
CGST2 = round(CGST2, 2)
SGST2 = round(SGST2, 2)
IGST2 = round(IGST2, 2)
total = bookedAmountWOTaxEbook + CGST1 + SGST1 + IGST1 + bookedAmountWOTaxEvaluation + CGST2 + SGST2 + IGST2
total = round(total, 1)
return total, bookedAmountWOTaxEbook, CGST1, SGST1, IGST1, bookedAmountWOTaxEvaluation, CGST2, SGST2, IGST2
# Skill and Super calculations for Product Invoice amount along with taxes are as follows: Course: 18% Tax ########################
def skillSuperProductCalculation(bookedAmount, centerState, userState2):
CGST1 = 0
SGST1 = 0
IGST1 = 0
bookedAmountWOTax = int(bookedAmount)/1.18
if centerState == userState2:
CGST1 = (bookedAmountWOTax*9)/100
SGST1 = (bookedAmountWOTax*9)/100
elif centerState != userState2:
IGST1 = (bookedAmountWOTax*18)/100
bookedAmountWOTax = round(bookedAmountWOTax, 2)
CGST1 = round(CGST1, 2)
SGST1 = round(SGST1, 2)
IGST1 = round(IGST1, 2)
total = bookedAmountWOTax + CGST1 + SGST1 + IGST1
total = round(total, 1)
return total, bookedAmountWOTax, CGST1, SGST1, IGST1
# Skill and Super calculations for Ebook Invoice amount along with taxes are as follows: Course: 5% Tax ########################
def skillSuperEbookCalculation(bookedAmount, centerState, userState2):
CGST1 = 0
SGST1 = 0
IGST1 = 0
bookedAmountWOTax = int(bookedAmount)/1.05
if centerState == userState2:
CGST1 = (bookedAmountWOTax*2.5)/100
SGST1 = (bookedAmountWOTax*2.5)/100
elif centerState != userState2:
IGST1 = (bookedAmountWOTax*5)/100
bookedAmountWOTax = round(bookedAmountWOTax, 2)
CGST1 = round(CGST1, 2)
SGST1 = round(SGST1, 2)
IGST1 = round(IGST1, 2)
total = bookedAmountWOTax + CGST1 + SGST1 + IGST1
total = round(total, 1)
return total, bookedAmountWOTax, CGST1, SGST1, IGST1
# Generate Invoices from Financial Year April 22 to September 23
def generateInvoicePdf(query, transId):
data = pd.read_sql(query, con=conn)
singleInvoice = data[data['transId'] == transId].reset_index().drop(columns='index')
invType = singleInvoice.loc[0,'InvoiceType']
transId2 = singleInvoice.loc[0,'transId']
invoice = singleInvoice.loc[0,'Invoice']
transDate = dt.datetime.strptime(singleInvoice.loc[0,'transDate'][0:10], "%Y-%m-%d").strftime('%d-%b-%Y')
Name = singleInvoice.loc[0,'Name']
Email = singleInvoice.loc[0,'Email']
Phone = singleInvoice.loc[0,'Phone']
userState = singleInvoice.loc[0, 'userState']
if userState is not None:
userState2 = userState[3::]
else:
userState2 = "Maharashtra"
centerState = singleInvoice.loc[0, 'centerState']
pname = singleInvoice.loc[0,'pname']
SAC = singleInvoice.loc[0,'SAC']
bookedAmount = singleInvoice.loc[0, 'bookedAmount']
address = singleInvoice.loc[0,'Address']
GSTIN = singleInvoice.loc[0,'GSTIN']
PAN = singleInvoice.loc[0, 'PAN']
courseName = singleInvoice.loc[0, 'courseName']
transMonth = singleInvoice.loc[0,'transMonth']
purchaseType = singleInvoice.loc[0,'purchaseType']
if courseName == 'Pass' or courseName == 'PassPro':
total, bookedAmountWOTaxEbook, CGST1, SGST1, IGST1, bookedAmountWOTaxEvaluation, CGST2, SGST2, IGST2 = passCalculations(bookedAmount, centerState, userState2)
if courseName == 'PassPro':
pname = "Online TB Pass Pro - E-Book|Online TB Pass Pro - Evaluation"
else:
pname = "Online TB Pass- E-Book|Online TB Pass- Evaluation"
context = {
"invType": invType,
"transId2": transId2,
"invoice": invoice,
"transDate": transDate,
"Name": Name,
"Email": Email,
"Phone": Phone,
"userState": userState,
"pname": pname,
"SAC": SAC,
"bookedAmount": bookedAmount,
"address": address,
"GSTIN": GSTIN,
"PAN": PAN,
"courseName": courseName,
"bookedAmountWOTaxEbook": bookedAmountWOTaxEbook,
"CGST1": CGST1,
"SGST1": SGST1,
"IGST1": IGST1,
"bookedAmountWOTaxEvaluation": bookedAmountWOTaxEvaluation,
"CGST2": CGST2,
"SGST2": SGST2,
"IGST2": IGST2,
"total": total
}
if courseName == 'Super' or courseName == 'Skill':
total, bookedAmountWOTax, CGST1, SGST1, IGST1 = skillSuperProductCalculation(bookedAmount, centerState, userState2)
pname = pname + " - Course"
context = {
"invType": invType,
"transId2": transId2,
"invoice": invoice,
"transDate": transDate,
"Name": Name,
"Email": Email,
"Phone": Phone,
"userState": userState,
"pname": pname,
"SAC": SAC,
"bookedAmount": bookedAmount,
"address": address,
"GSTIN": GSTIN,
"PAN": PAN,
"courseName": courseName,
"bookedAmountWOTax": bookedAmountWOTax,
"CGST1": CGST1,
"SGST1": SGST1,
"IGST1": IGST1,
"total": total,
"isProduct": True
}
pdf_name = f"invoice_{transId}.pdf"
download_link_pdf = createInvoicePdf(context, transId, pdf_name)
invoice_dict = {
"transId": transId,
"pdf_link": download_link_pdf,
"purchaseType": purchaseType,
"courseName": courseName,
"centerState": centerState,
"userState": userState,
"transMonth": transMonth,
"context": str(context)
}
return invoice_dict
# Generate Invoices from Financial Year October 23 to Further
def generateMultipleInvoicePdf(query, transId):
download_link_book = ""
download_link_ebook = ""
download_link_product = ""
data = pd.read_sql(query, con=conn)
singleInvoice = data[data['transId'] == transId].reset_index().drop(columns='index')
invType = singleInvoice.loc[0,'InvoiceType']
transId2 = singleInvoice.loc[0,'transId']
transDate = dt.datetime.strptime(singleInvoice.loc[0,'transDate'][0:10], "%Y-%m-%d").strftime('%d-%b-%Y')
Name = singleInvoice.loc[0,'Name']
Email = singleInvoice.loc[0,'Email']
Phone = singleInvoice.loc[0,'Phone']
userState = singleInvoice.loc[0, 'userState']
if userState is not None:
userState2 = userState[3::]
else:
userState2 = "Maharashtra"
centerState = singleInvoice.loc[0, 'centerState']
pname = singleInvoice.loc[0,'pname']
SAC = singleInvoice.loc[0,'SAC']
prodBookedAmount = singleInvoice.loc[0, 'ProductRevenue']
ebookBookedAmount = singleInvoice.loc[0, 'EBookRevenue']
bookBookedAmount = singleInvoice.loc[0, 'BookRevenue']
isProduct = singleInvoice.loc[0, 'isProduct']
isEBook = singleInvoice.loc[0, 'isEBook']
isBook = singleInvoice.loc[0, 'isBook']
address = singleInvoice.loc[0,'Address']
GSTIN = singleInvoice.loc[0,'GSTIN']
PAN = singleInvoice.loc[0, 'PAN']
courseName = singleInvoice.loc[0, 'courseName']
transMonth = singleInvoice.loc[0,'transMonth']
purchaseType = singleInvoice.loc[0,'purchaseType']
invoice_dict = {
"transId": transId,
"purchaseType": purchaseType,
"courseName": courseName,
"centerState": centerState,
"userState": userState,
"transMonth": transMonth,
}
if courseName == 'Super' or courseName == 'Skill':
if isProduct == "True":
total, prodBookedAmountWOTax, CGST1, SGST1, IGST1 = skillSuperProductCalculation(prodBookedAmount, centerState, userState2)
pnameProduct = pname + " - Course"
invoiceProduct = singleInvoice.loc[0,'Invoice_Product']
context = {
"invType": invType,
"transId2": transId2,
"invoice": invoiceProduct,
"transDate": transDate,
"Name": Name,
"Email": Email,
"Phone": Phone,
"userState": userState,
"pname": pnameProduct,
"SAC": SAC,
"bookedAmount": prodBookedAmount,
"address": address,
"GSTIN": GSTIN,
"PAN": PAN,
"courseName": courseName,
"bookedAmountWOTax": prodBookedAmountWOTax,
"CGST1": CGST1,
"SGST1": SGST1,
"IGST1": IGST1,
"total": total,
"isProduct": True
}
pdf_name = f"invoice_product_{transId}.pdf"
download_link_product = createInvoicePdf(context, transId, pdf_name)
invoice_dict["pdf_link_product"] = download_link_product
if isEBook == "True":
total, prodBookedAmountWOTax, CGST1, SGST1, IGST1 = skillSuperEbookCalculation(ebookBookedAmount, centerState, userState2)
pnameEbook = pname + " - Ebook"
invoiceEbook = singleInvoice.loc[0,'Invoice_EBook']
context = {
"invType": invType,
"transId2": transId2,
"invoice": invoiceEbook,
"transDate": transDate,
"Name": Name,
"Email": Email,
"Phone": Phone,
"userState": userState,
"pname": pnameEbook,
"SAC": SAC,
"bookedAmount": prodBookedAmount,
"address": address,
"GSTIN": GSTIN,
"PAN": PAN,
"courseName": courseName,
"bookedAmountWOTax": prodBookedAmountWOTax,
"CGST1": CGST1,
"SGST1": SGST1,
"IGST1": IGST1,
"total": total,
"isEBook": True
}
pdf_name = f"invoice_ebook_{transId}.pdf"
download_link_ebook = createInvoicePdf(context, transId, pdf_name)
invoice_dict["pdf_link_ebook"] = download_link_ebook
if isBook == "True":
pnameBook = pname + " - Book"
invoiceBook = singleInvoice.loc[0,'Invoice_Book']
context = {
"invType": invType,
"transId2": transId2,
"invoice": invoiceBook,
"transDate": transDate,
"Name": Name,
"Email": Email,
"Phone": Phone,
"userState": userState,
"pname": pnameBook,
"SAC": SAC,
"bookedAmount": prodBookedAmount,
"address": address,
"GSTIN": GSTIN,
"PAN": PAN,
"courseName": courseName,
"bookedAmountWOTax": bookBookedAmount,
"CGST1": 0,
"SGST1": 0,
"IGST1": 0,
"total": bookBookedAmount,
"isBook": True
}
pdf_name = f"invoice_book_{transId}.pdf"
download_link_book = createInvoicePdf(context, transId, pdf_name)
invoice_dict["pdf_link_book"] = download_link_book
return invoice_dict
def generateMultipleInvoicePdfTest(query, transId):
data = pd.read_sql(query, con=conn)
singleInvoice = data[data['transId'] == transId].reset_index().drop(columns='index')
invoice_book = singleInvoice.loc[0,'Invoice_Book']
invoice_ebook = singleInvoice.loc[0,'Invoice_EBook']
invoice_product = singleInvoice.loc[0,'Invoice_Product']
invoice_dict = {
"transId": transId,
"invoice_book": invoice_book,
"invoice_ebook": invoice_ebook,
"invoice_product": invoice_product
}
return invoice_dict