vendor-management-api/service/project_service.py

from dao.project_dao import *
from dao.vendor_dao import getVendorDetailsDao
from utils.error_log import insert_into_error_log
from utils.UploadImage import uploadImagetoFolder
from datetime import datetime
from fpdf import FPDF
import os
import time
from PyPDF2 import PdfFileMerger, PdfFileReader
from utils.Email import sendEmail


def addProjectService(files, data, app, mongo):
    queryResult = {}
    try:
        insert_doc = {
            'id': '',
            'user_id': '',
            'project_name': '',
            'intervention': '',
            'project_type': '',
            'mou_expiry': 0,
            'quotations': [],
            'amount':0,
            'maintainence_amount_limit': False,
            'amount_limit': False,
            'req_exception':False,
            'status': 'SAVED'
        }

        insert_doc['project_name'] = data['p_name']  # Project Name
        insert_doc['intervention'] = data['intervention']  # Intervention
        insert_doc['project_type'] = data['p_type']  # Project Type

        if data['id'] == '0':
            insert_doc['po_submitted'] = False
            insert_doc['created_on'] = int(datetime.now().timestamp() * 1000)
        else:
            insert_doc['updated_on'] = int(datetime.now().timestamp() * 1000)

        suffix = []
        for key in data:
            if key.startswith('v_name'):
                suffix.append(key[len('v_name'):])

        quotations = []
        # Upload quotations
        for value in suffix:
            append = False
            quote = {'v_name': '', 'exception': '', 'note': ''}
            if 'v_name' + str(value) in data and data['v_name' +
                                                      str(value)] != '':
                append = True
                quote['v_name'] = data['v_name' + str(value)]

            if 'exception' + str(value) in data:
                quote['exception'] = True
            else:
                quote['exception'] = False

            if 'note' + str(value) in data:
                quote['note'] = data['note' + str(value)]

            if 'quote_file' + str(value) in files:
                quote_file = (files['quote_file' + str(value)])
                quotImageResponse = uploadImagetoFolder(
                    quote_file, 'quote_' + str(value), app)
                if quotImageResponse['status'] == 'success':
                    quote['quote_file'] = quotImageResponse['message']
                # quoteImageResponse=uploadImagetoFolder(quote_file,'QUOT_')
            if append == True:
                quotations.append(quote)

        insert_doc['quotations'] = quotations

        if 'mou_expiry' in data and data['mou_expiry'] != '':
            mou_expiry = data['mou_expiry']  # MOU Expiry
            insert_doc['mou_expiry'] = (int(
                datetime.strptime(str(mou_expiry),
                                  "%m/%d/%Y").strftime('%s'))) * 1000

        if 'id' in data:
            insert_doc['id'] = data['id']
        if 'user_id' in data:
            insert_doc['user_id'] = data['user_id']

        if 'maintainence_amount_limit' in data:
            insert_doc['maintainence_amount_limit'] = True

        if 'amount_limit' in data:
            insert_doc['amount_limit'] = True

        if 'req_exception' in data:
            insert_doc['req_exception'] = True

        if 'req_exception_note' in data:
            insert_doc['req_exception_note'] = data['req_exception_note']
        
        file_found=False
        # Upload MOU File
        if 'mou' in files:    
            mou_file = files['mou']
            mouImageResponse = uploadImagetoFolder(mou_file, 'mou_', app)
            if mouImageResponse['status'] == 'success':
                file_found=True
                insert_doc['mou_file'] = mouImageResponse['message']

        # Upload Comparative Statement
        if 'comparative_statement' in files:
            comparative_statement_file = files['comparative_statement']
            compImageResponse = uploadImagetoFolder(comparative_statement_file,
                                                    'comparative_statement',
                                                    app)
            if compImageResponse['status'] == 'success':
                insert_doc['comparative_statement'] = compImageResponse[
                    'message']

        # For Opex Donation
        if data['p_type'] == 'opex_donation':
            if 'registration_copy' in files:
                # Upload registration_copy
                registration_copy_file = files['registration_copy']
                regImageResponse = uploadImagetoFolder(registration_copy_file,
                                                       'reg_', app)
                if regImageResponse['status'] == 'success':
                    insert_doc['registration_copy'] = regImageResponse[
                        'message']

            if 'deed_copy' in files:
                # Upload deed_copy
                deed_copy_file = files['deed_copy']
                deedImageResponse = uploadImagetoFolder(
                    deed_copy_file, 'deed_', app)
                if deedImageResponse['status'] == 'success':
                    insert_doc['deed_copy'] = deedImageResponse['message']

            if '80_g_copy' in files:
                # Upload 80_g_copy
                G80_file = files['80_g_copy']
                g80ImageResponse = uploadImagetoFolder(G80_file, '80g_', app)
                if g80ImageResponse['status'] == 'success':
                    insert_doc['80g_copy'] = g80ImageResponse['message']

            if '12_a_copy' in files:
                # Upload 12_a_copy
                a12_file = files['12_a_copy']
                a12ImageResponse = uploadImagetoFolder(a12_file, '12a_', app)
                if a12ImageResponse['status'] == 'success':
                    insert_doc['12a_copy'] = a12ImageResponse['message']

            if 'audit_sheet' in files:
                # Upload audit_sheet
                audit_sheet_file = files['audit_sheet']
                auditImageResponse = uploadImagetoFolder(
                    audit_sheet_file, 'audit_', app)
                if auditImageResponse['status'] == 'success':
                    insert_doc['audit_sheet'] = auditImageResponse['message']

            if 'board_resolution' in files:
                # Upload board_resolution
                board_resolution_file = files['board_resolution']
                boardImageResponse = uploadImagetoFolder(
                    board_resolution_file, 'board_', app)
                if boardImageResponse['status'] == 'success':
                    insert_doc['board_resolution'] = boardImageResponse[
                        'message']

            if 'donation_letter' in files:
                # Upload donation_letter
                donation_letter_file = files['donation_letter']
                donationImageResponse = uploadImagetoFolder(
                    donation_letter_file, 'donation_', app)
                if donationImageResponse['status'] == 'success':
                    insert_doc['donation_letter'] = donationImageResponse[
                        'message']

        if 'amount' in data:
            insert_doc['amount']=float(data['amount'])
        
        insert_into_db=True
        
        if insert_doc['req_exception']==False or float(insert_doc['amount'])>=25000 or float(insert_doc['amount'])>=100000:
            # Mou file and PO is mandatory
            if file_found==False or insert_doc['mou_expiry']==0:
                insert_into_db=False

        if insert_into_db==True:
            insertIntoProjectMaster(insert_doc, mongo)

            queryResult = {
                'status': 'success',
                'message': 'Requirement added successfully!'
            }
        else:
            queryResult = {
                'status': 'failure',
                'message': 'Mou file and expiry is mandatory!'
            }
    except Exception as err:
        queryResult = {'status': 'failure', 'message': 'Something went wrong!'}
        print(err)

    return queryResult

def getProjectList(u_id, mongo):  # Validate email id and password
    queryResult = []
    try:
        queryResult = getProjectListByUID(u_id, mongo)
    except Exception as err:
        # insert_into_error_log({'timestamp':int(datetime.now().timestamp()*1000),'type':'api','err':str(err),'function':'validateLogin'},mongo)
        queryResult = []

    return queryResult

def getAllProjectList(mongo):  # Validate email id and password
    queryResult = []
    try:
        queryResult = getAllProjectListDao(mongo)
    except Exception as err:
        queryResult = []

    return queryResult

def getPOPendingLisForApprovals(u_id,mongo):
    queryResult = []
    try:
        queryResult = getPendingPOForApprovalDao(u_id, mongo)
    except Exception as err:
        queryResult = []

    return queryResult

def getProjectDetails(id, mongo):  # Validate email id and password
    queryResult = []
    try:
        queryResult = getProjectDetailsDao(id, mongo)
    except Exception as err:
        # insert_into_error_log({'timestamp':int(datetime.now().timestamp()*1000),'type':'api','err':str(err),'function':'validateLogin'},mongo)
        queryResult = []

    return queryResult

def submitProjectDetails(request_obj, mongo):  # Submit Project
    queryResult = {}
    try:
        submitProjectDao(request_obj, mongo)
        queryResult = {
            'status': 'success',
            'message': 'Requirement submitted successfully!'
        }
    except Exception as err:
        # insert_into_error_log({'timestamp':int(datetime.now().timestamp()*1000),'type':'api','err':str(err),'function':'validateLogin'},mongo)
        queryResult = {'status': 'failure', 'message': 'Something went wrong!'}

    return queryResult

def approveDeclinePOService(request_obj,mongo,app):
    queryResult={}
    try:
        update_obj={}
        flag=request_obj['status']
        po_details=getProjectDetailsDao(request_obj['po_id'],mongo)
        remark=request_obj["reason"]
        seniorId=request_obj['user_id']
        timestamp=int(datetime.now().timestamp())*1000
        approverName = ""
        approverLevel = ""
        approverId = ""
        approvalTrackLevel = ""
        
        for po in po_details:
            user_id=po['user_id']
            seniorLevel1=po['approval_track']['senior_level_1']
            seniorLevel2=po['approval_track']['senior_level_2']
            seniorLevel3=po['approval_track']['senior_level_3']
                    
            # if senior id = senior level1
            if seniorLevel1["id"]==seniorId:
                approverName = seniorLevel1["name"]
                approverId = seniorLevel1["id"]
                approverLevel = seniorLevel1["level"]
                approvalTrackLevel = "senior_level_1"

                #  If expense approved
                if flag=="APPROVED":
                    if seniorLevel2["id"]=="NA":
                        if seniorLevel3["id"]=="NA":
                            update_obj["po_status"]=flag
                            create_email(po,"APPROVED",approverName,approverLevel,mongo,app)
                        else:
                            update_obj["approval_track.senior_level_3.status"]= "PENDING"
                    else:
                        update_obj["approval_track.senior_level_2.status"]= "PENDING"
                
            #  if senior id = senior level2
            if seniorLevel2["id"]==seniorId:
                approverName = seniorLevel2["name"]
                approverId = seniorLevel2["id"]
                approverLevel = seniorLevel2["level"]
                approvalTrackLevel = "senior_level_2"

                #  If expense approved
                if flag=="APPROVED":
                    if seniorLevel3["id"]=="NA":
                        update_obj["po_status"]=flag
                        create_email(po,"APPROVED",approverName,approverLevel,mongo,app)
                    else:
                        update_obj["approval_track.senior_level_3.status"]= "PENDING"
                    
            # if senior id = senior level3
            if seniorLevel3["id"]==seniorId:
                approverName = seniorLevel3["name"]
                approverId = seniorLevel3["id"]
                approverLevel = seniorLevel3["level"]
                approvalTrackLevel = "senior_level_3"

                #  If expense approved
                if flag=="APPROVED":
                    update_obj["po_status"]=flag
                    create_email(po,"APPROVED",approverName,approverLevel,mongo,app)
            
            update_obj["approval_track." + approvalTrackLevel + ".status"]=flag
            update_obj["approval_track." + approvalTrackLevel + ".info"]={"status":flag,"remark":remark,"updated_on":timestamp}

            # for disapproved
            if flag=="DECLINED":
                update_obj["po_status"]= "DECLINED"
                
            # Status Log 
            approval_status={
                "user_id":user_id,
                "po_id":request_obj['po_id'],
                "timestamp":timestamp,
                "action":flag,
                "approver_name":approverName,
                "approver_level":approverLevel,
                "approver_id":approverId,
                "type":"approval",
                "remark":remark
            }

            # Update Purchase Order
            updatePurchaseOrderApprovalStatus(update_obj,request_obj['po_id'],mongo) 

            # Insert into PO-status-log
            insertPOStatusLog(approval_status,mongo)

        queryResult = {
            'status': 'success',
            'message': 'Purchase Order '+flag+' successfully!'
        }
    except Exception as err:
        queryResult = {
            'status': 'failure',
            'message': 'Something went wrong!'
        }
        print(err)

    return queryResult

def createPOService(data,app, mongo):
    queryResult = {}
    try:
        timestamp=int(datetime.now().timestamp())*1000
        total_amount = 0
        user_id=""
        insert_doc = {
            'po_year': (str(time.strftime("%y", time.localtime())) + '-' +
                        str(int(time.strftime("%y", time.localtime())) + 1)),
            'po_status':'PENDING'
        }

        if 'id' in data and data['id'] != '0':
            project_id = data['id']

            order_id = ''
            
            suffix = []
            for key in data:
                if key.startswith('product_dec'):
                    suffix.append(key[len('product_dec'):])

            od = []
            # Upload Order Details
            for value in suffix:
                append = False
                quote = {
                    'product_dec': '',
                    'product_qty': '',
                    'product_amount': '',
                    'product_gst': ''
                }
                if 'product_dec' + str(value) in data and data[
                        'product_dec' + str(value)] != '':
                    append = True
                    quote['product_dec'] = data['product_dec' + str(value)]

                if 'product_qty' + str(value) in data:
                    quote['product_qty'] = int(data['product_qty' +
                                                    str(value)])

                if 'product_amount' + str(value) in data:
                    quote['product_amount'] = float(data['product_amount' +
                                                       str(value)])
                                                
                if 'product_gst' + str(value) in data:
                    quote['product_gst'] = float(data['product_gst' +
                                                       str(value)])


                if append == True:
                    od.append(quote)

            insert_doc['po_created_on'] = int(datetime.now().timestamp() *1000)
            insert_doc['od'] = od
            insert_doc['po_submitted'] = True
            
            if 'po_note' in data:
                insert_doc['po_note']=data['po_note']

            # if 'product_gst' in data:
            #     insert_doc['po_gst'] = int(data['product_gst'])

            # if 'product_gst_amt' in data:
            #     insert_doc['po_gst_amt'] = int(data['product_gst_amt'])

            if 'p_additional_charges'  in data:
                insert_doc['po_additional_charges'] = float(data['p_additional_charges'])

            if 'product_total_amount' in data:
                insert_doc['po_total_amount'] = float(data['product_total_amount' ])
                total_amount += float(data['product_total_amount'])

            # Fetch product details
            project_details = getProjectDetailsDao(project_id, mongo)
            for project in project_details:
                order_id = project['intervention']+ '/'+ create_po_id(mongo)
                user_id = project['user_id']
                
                insert_doc['approval_track']=getApprovalTrack(total_amount,user_id,mongo)

            if 'vendors' in data and data['vendors'] != '':
                vendor_id = data['vendors']  # MOU Expiry
                insert_doc['vendor_id'] = vendor_id

            if 'po_reason' in data and data['po_reason'] != '':
                po_reason = data['po_reason']  # MOU Expiry
                insert_doc['po_reason'] = po_reason

            if 'MOP' in data and data['MOP'] != '':
                MOP = data['MOP']  # MOU Expiry
                insert_doc['MOP'] = MOP

            if 'amount_words' in data and data['amount_words'] != '':
                amount_words = data['amount_words']  # MOU Expiry
                insert_doc['amount_words'] = amount_words

            if 'po_mou_expiry' in data and data['po_mou_expiry'] != '':
                po_mou_expiry = data['po_mou_expiry']  # MOU Expiry
                insert_doc['po_mou_expiry'] = (int(
                    datetime.strptime(str(po_mou_expiry),
                                    "%m/%d/%Y").strftime('%s'))) * 1000

            insert_doc['po_order_id'] = order_id
            updatePurchaseOrder(insert_doc, project_id, mongo)

            # Insert into PO-status-log
            insertPOStatusLog({
                    "user_id":user_id,
                    "po_id":project_id,
                    "timestamp":timestamp,
                    "action":"SUBMITTED"
                },mongo)

            createPoPdfService(project_id,app,mongo)

            queryResult = {
                'status': 'success',
                'message': 'Purchase order created successfully!'
            }
        else:
            queryResult = {
                'status': 'failure',
                'message': 'Something went wrong!'
            }
    
    except Exception as err:
        queryResult = {'status': 'failure', 'message': 'Something went wrong!'}
        print(err)

    return queryResult

def createPoPdfService(p_id, app, mongo):
    queryResult = {}
    try:
        # fetch purchase order details
        purchase_order = getProjectDetailsDao(p_id, mongo)
        for order in purchase_order:
            if 'vendor_id' in order:
                vendor_id = order['vendor_id']
                # Get Vendor Details
                vendor_details = getVendorDetailsDao(vendor_id, mongo)
                for vendor_detail in vendor_details:
                    createPDF(app, order, vendor_detail)
    except Exception as err:
        queryResult = {'status': 'failure', 'message': 'Something went wrong!'}
        print(err)

def create_po_id(mongo):
    order_id = ''
    try:

        po_year = (str(time.strftime("%y", time.localtime())) + '-' +
                   str(int(time.strftime("%y", time.localtime())) + 1))
        # get PO Year
        db_year = getPOYear(mongo)
        po_count = '01'
        for year_obj in db_year:
            if po_year == year_obj['year']:
                # Fetch total PO created ID
                po_count = getInserterdPoCount(po_year, mongo)
                po_count = str(po_count +
                               1) if po_count + 1 > 9 else '0' + str(po_count +
                                                                     1)

            else:
                # insert new year object
                insertPOYear(po_year, mongo)

        order_id = (po_year + '/' + po_count)

    except Exception as err:
        print(err)

    return order_id

def getPOStatusLogs(po_id,mongo,app):
    queryResult = []
    try:
        queryResult = getPOStatusLog(po_id, mongo)
    except Exception as err:
        queryResult = []

    return queryResult

def getLastAssignedSenior(senior_obj):
    lastSeniorKey=''
    try:
        strToIntObj={int(k):v for k,v in senior_obj.items()}
        lastSeniorKey=strToIntObj[sorted(strToIntObj)[-1]]
    except Exception as err:
        print(err)
    
    return lastSeniorKey

def vendorAcceptanceService(files, data, app, mongo):
    queryResult = {}
    timestamp=int(datetime.now().timestamp())*1000
    try:
        po_id=data['id']
        if po_id!="":
            updateDoc={'vendor_action':'DECLINED','vendor_acceptance':True}
            if 'vendor_action' in data:
                updateDoc['vendor_action']=data['vendor_action']

            # Upload Vendir Acceptance Proof
            if 'vendor_acceptance_proof' in files:
                vendor_acceptance_proof = files['vendor_acceptance_proof']
                vendorImageResponse = uploadImagetoFolder(vendor_acceptance_proof, 'po_vendor_accpt_', app)
                if vendorImageResponse['status'] == 'success':
                    updateDoc['mou_file'] = vendorImageResponse['message']
 
            updatePurchaseOrderApprovalStatus(updateDoc,po_id, mongo)

            # Insert into PO-status-log
            insertPOStatusLog({
                    "user_id":data['user_id'],
                    "po_id":po_id,
                    "timestamp":timestamp,
                    "type":"vendor_acceptance",
                    "action":updateDoc['vendor_action']
                },mongo)

            queryResult = {
                'status': 'success',
                'message': 'Status Updated Successfully!'
            }
        else:
            queryResult = {'status': 'failure', 'message': 'Invalid PO ID!'}
    except Exception as err:
        queryResult = {'status': 'failure', 'message': 'Something went wrong!'}
        print(err)

    return queryResult

def getApprovalTrack(total_amount,user_id,mongo):
    approval_track = {"senior_level_1":{},"senior_level_2":{},"senior_level_3":{}}
    try:
        # Fetch user Details from user master
        user_list=getUserDetailsFromMaster(user_id,mongo)
        
        for user_obj in user_list:
            sernior_level="expense_senior_level_5000_"
            if total_amount>10000:
                sernior_level="expense_senior_level_25000_"
            
            senior_level_1_key=getLastAssignedSenior(user_obj[sernior_level+'I'])
            senior_level_1_name=user_obj[sernior_level+'I_name']
            
            senior_level_2_key=getLastAssignedSenior(user_obj[sernior_level+'II'])
            senior_level_2_name=user_obj[sernior_level+'II_name']

            senior_level_3_key=getLastAssignedSenior(user_obj[sernior_level+'III'])
            senior_level_3_name=user_obj[sernior_level+'III_name']

            approval_track['senior_level_1']={
                    "level" : "Senior Level 1",
                    "name" : senior_level_1_name,
                    "id" : senior_level_1_key,
                    "status" : "PENDING"
                }
            approval_track['senior_level_2']={
                    "level" : "Senior Level 2",
                    "name" : senior_level_2_name,
                    "id" : senior_level_2_key,
                    "status" : "NOT VISIBLE"
                }
            approval_track['senior_level_3']={
                    "level" : "Senior Level 3",
                    "name" : senior_level_3_name,
                    "id" : senior_level_3_key,
                    "status" : "NOT VISIBLE"
                }
            
    except Exception as err:
        print(err)

    return approval_track

def getAllPOListService(mongo):
    queryResult = []
    try:
        queryResult = getPOList(mongo)
    except Exception as err:
        queryResult = []

    return queryResult

class MyFPDF(FPDF):
    pass

def createPDF(app, purchase_order, vendor_detail):
    try:
        order_id=""
        po_created_on=""
        po_note=""
        po_mou_expiry=""
        if 'po_order_id' in purchase_order:
            order_id=purchase_order['po_order_id']

        if 'po_created_on' in purchase_order:
            po_created_on=datetime.fromtimestamp(int(purchase_order['po_created_on'])/1000).strftime("%d %B, %Y")

        if 'po_note' in purchase_order:
            po_note=purchase_order['po_note']

        if 'po_mou_expiry' in purchase_order:
            po_mou_expiry=datetime.fromtimestamp(int(purchase_order['po_mou_expiry'])/1000).strftime("%d %B, %Y")

        authorised_by=""
        try:
            if "po_status" in purchase_order and purchase_order["po_status"]=="APPROVED" and 'approval_track' in purchase_order:
                if 'senior_level_1' in purchase_order['approval_track'] and purchase_order['approval_track']['senior_level_1']['status']=="APPROVED":
                    authorised_by=purchase_order['approval_track']['senior_level_1']['name']
                if 'senior_level_2' in purchase_order['approval_track'] and purchase_order['approval_track']['senior_level_2']['status']=="APPROVED":
                    authorised_by=purchase_order['approval_track']['senior_level_2']['name']
                if 'senior_level_3' in purchase_order['approval_track'] and purchase_order['approval_track']['senior_level_3']['status']=="APPROVED":
                    authorised_by=purchase_order['approval_track']['senior_level_3']['name']
        except Exception as err:
            print(err)

        filepath = os.path.join(app.config['STATIC_IMG'], 'kef-logo.jpg')
        pdf = MyFPDF(orientation='P', unit='mm', format='Letter')
        # Effective page width, or just epw
        epw = pdf.w - 6 * pdf.l_margin

        pdf.set_left_margin(30)
        pdf.add_page()
        pdf.image(filepath, 70, 10, 70, 30)
        
        pdf.ln(30.00)
        pdf.set_font('Times', '', 11)
        pdf.multi_cell(epw, 3.81,
            '1st Floor, North Side, Ujagar Compound, Adjacent to Deonar Bus Depot, Off. Sion Trombay Road, Deonar, Mumbai 400 088',
           align='C' )

        # Set Header
        pdf.ln(15)
        pdf.set_font('Times', '', 18)
        pdf.cell(epw, 3.81, 'PURCHASE ORDER', align='C', border=0)

        pdf.ln(8.81)
        pdf.set_font('Times', 'U', 11)
        pdf.cell(epw, 3.81, 'PO REF:', border=0)
        pdf.set_xy(47.0, pdf.get_y())
        pdf.set_font('Times', '', 11)
        pdf.cell(epw, 3.81, order_id.upper(), border=0)

        pdf.ln(3.81)
        pdf.set_font('Times', 'U', 11)
        pdf.cell(epw, 3.81, 'DATE:', border=0)
        pdf.set_xy(43.0, pdf.get_y())
        pdf.set_font('Times', '', 11)
        pdf.cell(epw, 3.81, po_created_on.upper(), border=0)

        pdf.ln(3.81)
        pdf.set_font('Times', 'U', 11)
        pdf.cell(epw, 3.81, 'YOUR REF:', border=0)
        pdf.set_xy(52.0, pdf.get_y())
        pdf.set_font('Times', '', 11)
        pdf.cell(epw, 3.81, 'YOUR QUOTATION DATED '+po_mou_expiry.upper(), border=0)

        pdf.ln(3.81)
        pdf.set_font('Times', 'U', 11)
        pdf.multi_cell(epw, 3.81, 'BILLING ADDRESS: KOTAK EDUCATION FOUNDATION, GR FLOOR & 1 S T FLOOR UJAGAR COMPOUND, DEONAR, MUMBAI: 400088', border=0)
        # pdf.set_xy(67.0, pdf.get_y())
        # pdf.set_font('Times', '', 11)
        # pdf.cell(epw, 3.81,'',border=0)
        # pdf.ln(3.81)
        # pdf.cell(epw, 3.81, '', border=0)

        # pdf.ln(3.81)
        pdf.set_font('Times', 'U', 11)
        pdf.multi_cell(epw, 3.81, 'SHIP TO: KOTAK EDUCATION FOUNDATION, GR FLOOR & 1 S T FLOOR UJAGAR COMPOUND, DEONAR, MUMBAI: 400088', border=0)
        # pdf.set_xy(47.0, pdf.get_y())
        # pdf.set_font('Times', '', 11)
        # pdf.cell(epw, 3.81,'',border=0)
        # pdf.ln(3.81)
        # pdf.cell(epw, 3.81, '', border=0)

        pdf.ln(12.81)
        pdf.set_font('Times', 'BU', 11)
        pdf.cell(epw, 3.81, 'TO BE ISSUED TO:', border=0)

        pdf.ln(8.81)
        pdf.set_font('Times', 'U', 11)
        pdf.cell(epw, 3.81, 'VENDOR NAME:', border=0)
        pdf.set_xy(62.0, pdf.get_y())
        pdf.set_font('Times', '', 11)
        pdf.cell(epw, 3.81, vendor_detail['v_name'].upper(), border=0)

        pdf.ln(3.81)
        pdf.set_font('Times', 'U', 11)
        pdf.multi_cell(epw, 3.81, 'VENDOR ADDRESS: ' +vendor_detail['v_address'].upper(), border=0)
        
        pdf.set_font('Times', 'U', 11)
        pdf.cell(epw, 3.81, 'VENDOR CODE:', border=0)
        pdf.set_xy(60.0, pdf.get_y())
        pdf.set_font('Times', '', 11)
        pdf.cell(epw, 3.81, str(vendor_detail['v_code']), border=0)

        # pdf.set_xy(10.0, 120.0)
        pdf.ln(8.81)
        pdf.set_font('Times', '', 11)
        pdf.multi_cell(epw, 3.81,
            'We hereby place this Purchase order on yourselves for supply of the following materials and delivery of the following services, as per terms and conditions given in this Purchase Order.',
            border=0)

        pdf.ln(8.81)
        # pdf.set_right_margin(30)
        pdf.set_font('Times', 'B', 11)
        pdf.cell(epw, 3.81, 'MATERIAL / SERVICES ORDER', align='C', border=0)

        pdf.ln(5.84)
        # Add table
        

        # Set column width to 1/4 of effective page width to distribute content
        # evenly across table and page
        col_width = epw / 6

        # Since we do not need to draw lines anymore, there is no need to separate
        # headers from data matrix.

        data = [['Sr no.', 'Product Description', 'Qty', 'Price','Gst','Amount']]

        if 'od' in purchase_order:
            i = 1
            for order in purchase_order['od']:
                data.append([
                    i, order['product_dec'], order['product_qty'],
                    order['product_amount'],order['product_gst'],(order['product_amount']*order['product_qty'])+order['product_gst']
                ])
                i += 1

            data.append(['', 'Additional Charges', '','', purchase_order['po_additional_charges']])
            # data.append(['', 'GST : '+ str(purchase_order['po_gst'])+'%', '','', purchase_order['po_gst_amt']])
            data.append(['', 'Total Amount', '','', purchase_order['po_total_amount']])

        # Text height is the same as current font size
        th = pdf.font_size

        pdf.set_font('Times', '', 11.0)

        i = 1
        
        # Here we add more padding by passing 2*th as height
        for row in data:
            
            ybefore=pdf.get_y()
            # pdf.set_xy(col_width, 2 * th)
            j=1
            cell_margin=0
            for datum in row:
                if i == 1:
                    pdf.set_font('Times', 'B', 11.0)
                    
                else:
                    pdf.set_font('Times', '', 11.0)
                
                if j==2:
                    
                    pdf.multi_cell(68,3.81, str(datum),border=0)
                    pdf.set_xy(cell_margin+(68)+pdf.l_margin, ybefore)
                    cell_margin+=68
                else:
                    
                    pdf.multi_cell(22,3.81, str(datum),border=0)
                    pdf.set_xy(cell_margin+(22)+pdf.l_margin, ybefore)
                    cell_margin+=22

                j+=1
                    # pdf.multi_cell(col_width, 2 * th, str(datum), border=1)
                # Enter data in colums
                
            # ybefore=pdf.get_y()
            i += 1
            pdf.ln(2 * 3.81)


        if 'intervention' in purchase_order and purchase_order['intervention']=='IT':
            pdf.ln(8.81)
            # pdf.set_right_margin(30)
            pdf.set_font('Times', 'B', 11)
            pdf.cell(epw, 3.81, 'DELIVERY TIMELINES', align='C', border=0)

            pdf.ln(5.84)
            col_width = epw / 7
            it_data = [['Particulars', 'Quantity', 'Location', 'Mode of delivery','Delivery Timelines','Remarks'],
            ['','','','','','',''],
            ['','','','','','',''],
            ['','','','','','','']]

            # Text height is the same as current font size
            th = pdf.font_size

            pdf.set_font('Times', '', 11.0)

            k = 1
            
            # Here we add more padding by passing 2*th as height
            for it_row in it_data:
                
                ybefore=pdf.get_y()
                # pdf.set_xy(col_width, 2 * th)
                j=1
                cell_margin=0
                for datum in it_row:
                    if k == 1:
                        pdf.set_font('Times', 'B', 11.0)
                        
                    else:
                        pdf.set_font('Times', '', 11.0)
                    
                    if j==1:
                        
                        pdf.multi_cell(68,3.81, str(datum),border=0)
                        pdf.set_xy(cell_margin+(68)+pdf.l_margin, ybefore)
                        cell_margin+=68
                    else:
                        
                        pdf.multi_cell(22,3.81, str(datum),border=0)
                        pdf.set_xy(cell_margin+(22)+pdf.l_margin, ybefore)
                        cell_margin+=22

                    j+=1
                        # pdf.multi_cell(col_width, 2 * th, str(datum), border=1)
                    # Enter data in colums
                    
                # ybefore=pdf.get_y()
                k += 1
                pdf.ln(2 * 3.81)


        pdf.set_font('Times', '', 11)
        # pdf.set_xy(10.0, 236.0)
        pdf.ln(3.81)
        pdf.cell(epw, 3.81,
            '*Note: '+po_note,
            border=0)

        pdf.ln(8.81)
        pdf.cell(epw, 3.81,'Authorised Signatory',border=0)

        pdf.ln(3.81)
        pdf.cell(epw, 3.81,authorised_by,border=0)
            

        # ======== Write File ==========
        pdf.output(os.path.join(app.config['PO_PDF'], purchase_order['id']+'.pdf'), 'F')

        # Call the PdfFileMerger
        mergedObject = PdfFileMerger()
        
        # I had 116 files in the folder that had to be merged into a single document
        # Loop through all of them and append their pages
        mergedObject.append(PdfFileReader(os.path.join(app.config['PO_PDF'], purchase_order['id']+'.pdf'), 'rb'))
        
        if 'intervention' in purchase_order and purchase_order['intervention']=='Nirmaan':
            mergedObject.append(PdfFileReader(os.path.join(app.config['PO_PDF'], 'infra_tc.pdf'), 'rb'))
        else:
            mergedObject.append(PdfFileReader(os.path.join(app.config['PO_PDF'], 'Formate-2-5.pdf'), 'rb'))
        
        # Write all the files into a file which is named as shown below
        mergedObject.write(os.path.join(app.config['PO_PDF'], purchase_order['id']+'.pdf'))

    except Exception as err:
        print(err)

def create_email(po,status,approverName,approverLevel,mongo,app):
    try:
        # get user details
        users=getUserDetailsFromMaster(po['user_id'],mongo)
        vendor_name="______________"
        vendor_details=getVendorDetailsDao(po['vendor_id'],mongo)
        for vendor in vendor_details:
            vendor_name=vendor['v_name']

        for user in users:
            name=user['first_name']
            reciever_email=user['personal_email_id']
            subject="PO approval & vendor acceptance"
            file_name=po['id']+'.pdf'
            pdf_attach=True
            
            body="Dear "+name+",<br /><br /> The PO of "+vendor_name+" vendor has been approved by your "+approverLevel +" <br />Kindly forward the attached PO for vendor acceptance, after approved by vendor, kindly take a screenshot & attached the same in vendor acceptance column for further processing." 

            
            sendEmail(file_name,reciever_email,body,pdf_attach,subject)
    except Exception as err:
        print(err)