vendor-management-api/service/reports_service.py

from dao.reports_dao import *
from dao.vendor_dao import getAllVendorListDao
from dao.project_dao import getAllUsersFromMaster, getPOListForReport
from datetime import datetime
import os
import time
import xlsxwriter
from utils.UploadImage import uploadImagetoFolder
from service.voucher_approval_service import findApprovalTrackForVoucher


def getMouReportDataService(from_date, to_date, mongo):
    queryResult = []
    formatted_vendors = {}

    try:
        vendors = getAllVendorListDao(mongo)

        for vendor in vendors:
            formatted_vendors[vendor['id']] = vendor

        mou_list = getMOUReportsDataDao(from_date, to_date, mongo)
        i = 1
        for mou in mou_list:
            try:
                user_id = mou['user_id']
                vendor_id = mou['vendor_id']

                end_date = '-'
                mou_file = '-'

                if 'mou_expiry' in mou and mou['mou_expiry'] != 0:
                    end_date = datetime.fromtimestamp(mou['mou_expiry'] /
                                                      1000).strftime("%x")

                if 'mou_file' in mou:
                    mou_file = "http://65.2.132.48:9005/uploads/" + mou[
                        'mou_file']

                insert_obj = {
                    'Sr,No':
                    i,
                    'Party Type':
                    "Vendor",
                    'Contract Type':
                    "NA",
                    'Department':
                    mou['intervention'],
                    'School / Center':
                    "NA",
                    'Classes coverd/Staff':
                    "NA",
                    'vendor_name':
                    formatted_vendors[vendor_id]['v_name'],
                    'vendor_pan':
                    formatted_vendors[vendor_id]['v_pan'],
                    'project_type':
                    mou['project_type'],
                    'mop':
                    mou['MOP'],
                    'Start Date':
                    datetime.fromtimestamp(mou['created_on'] /
                                           1000).strftime("%x"),
                    'End date':
                    end_date,
                    'total_amount':
                    mou['po_total_amount'],
                    'amount_words':
                    mou['amount_words'],
                    'Status':
                    "NA",
                    'Reminder':
                    "NA",
                    'Email To':
                    "NA",
                    'Contract Value in Rs.':
                    "NA",
                    'Payment Terms':
                    "NA",
                    'Rate Details':
                    "NA",
                    'Deposit Amt':
                    "NA",
                    'Copy/Original or zerox':
                    'NA',
                    "Remarks":
                    mou['po_reason'],
                    "Remark":
                    "NA",
                    "Reminder":
                    "NA",
                    "sent":
                    "NA",
                    "Link 1":
                    mou_file,
                    "Link 2":
                    "",
                    "Link 3":
                    ""
                }

                queryResult.append(insert_obj)

                i += 1
            except Exception as err:
                print(err)

    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 getTdsReportDataService(from_date, to_date, mongo):
    response = []
    tds_list = []
    formatted_vendors = {}

    try:
        tds_list = tdsNameList(mongo)
        vendors = getAllVendorListDao(mongo)

        tds_name_g = ''

        # response.append({'':'','':'','':'','':'KOTAK EDUCATION','FOUNDATION, MUMBAI':'','':"",'':'','':'','':'','':'','':"",'':"",'':""})
        # response.append({'':'','':'','':'','':'','':'','':"",'':'','':'','':'','':'','':"",'':"",'':""})
        # response.append({'':'','':'','':'','':'DETAIL OF TDS','DEDUCTED & PAID FOR':'THE MONTH OF APR 20','':"",'':'','':'','':'','':'','':"",'':"",'':""})

        for vendor in vendors:
            formatted_vendors[vendor['id']] = vendor

        j = 1
        for tds_name in tds_list:
            voucher_list = getTdsReportsDataDao(from_date, to_date, tds_name,
                                                mongo)

            total_gross = 0
            total_tds = 0

            if j != 1 and len(voucher_list) > 0:
                response.append({
                    'Sr,No': 'Sr,No',
                    'PAN No.': 'PAN No.',
                    tds_name_g: tds_name,
                    'NAME AS PER PAN': 'NAME AS PER PAN',
                    'DEDUCTEES': 'DEDUCTEES',
                    'VN': "VN",
                    'GROSS AMT': 'GROSS AMT',
                    'RATE': 'RATE',
                    'TDS AMT': 'TDS AMT',
                    'D.O.P': 'D.O.P',
                    'BSR code': "BSR code",
                    'CIN No.': "CIN No.",
                    'DOP': "DOP"
                })

            i = 1
            j += 1
            for voucher in voucher_list:
                try:
                    vendor_id = voucher['vendor_id']
                    tds_amt = 0
                    gross_amt = 0
                    if 'product_gross_amount' in voucher:
                        gross_amt = voucher['product_gross_amount']
                        tds_amt = gross_amt - voucher['product_total_amount']

                    if i == 1:
                        tds_name_g = tds_name

                    response.append({
                        'Sr,No':
                        i,
                        'PAN No.':
                        formatted_vendors[vendor_id]['v_pan'],
                        tds_name_g:
                        formatted_vendors[vendor_id]['v_pan_name'],
                        'NAME AS PER PAN':
                        formatted_vendors[vendor_id]['v_pan_name'],
                        'DEDUCTEES':
                        voucher['tds_type'],
                        'VN':
                        "",
                        'GROSS AMT':
                        gross_amt,
                        'RATE':
                        voucher['tds_rate'],
                        'TDS AMT':
                        tds_amt,
                        'D.O.P':
                        '',
                        'BSR code':
                        "",
                        'CIN No.':
                        "",
                        'DOP':
                        ""
                    })

                    total_gross += gross_amt
                    total_tds += tds_amt

                    i += 1
                except Exception as err:
                    print(err)

            if total_gross != 0:
                response.append({
                    'Sr,No': '',
                    'PAN No.': '',
                    tds_name_g: '',
                    'NAME AS PER PAN': '',
                    'DEDUCTEES': '',
                    'VN': "",
                    'GROSS AMT': '',
                    'RATE': '',
                    'TDS AMT': '',
                    'D.O.P': '',
                    'BSR code': "",
                    'CIN No.': "",
                    'DOP': ""
                })
                response.append({
                    'Sr,No': '',
                    'PAN No.': '',
                    tds_name_g: '',
                    'NAME AS PER PAN': '',
                    'DEDUCTEES': '',
                    'VN': "",
                    'GROSS AMT': total_gross,
                    'RATE': '',
                    'TDS AMT': total_tds,
                    'D.O.P': '',
                    'BSR code': "",
                    'CIN No.': "",
                    'DOP': ""
                })
                response.append({
                    'Sr,No': '',
                    'PAN No.': '',
                    tds_name_g: '',
                    'NAME AS PER PAN': '',
                    'DEDUCTEES': '',
                    'VN': "",
                    'GROSS AMT': '',
                    'RATE': '',
                    'TDS AMT': '',
                    'D.O.P': '',
                    'BSR code': "",
                    'CIN No.': "",
                    'DOP': ""
                })
                response.append({
                    'Sr,No': '',
                    'PAN No.': '',
                    tds_name_g: '',
                    'NAME AS PER PAN': '',
                    'DEDUCTEES': '',
                    'VN': "",
                    'GROSS AMT': '',
                    'RATE': '',
                    'TDS AMT': '',
                    'D.O.P': '',
                    'BSR code': "",
                    'CIN No.': "",
                    'DOP': ""
                })

    except Exception as err:
        print(err)
        # insert_into_error_log({'timestamp':int(datetime.now().timestamp()*1000),'type':'api','err':str(err),'function':'validateLogin'},mongo)
        response = []

    return response


def getTallyReportDataService(from_date, to_date, mongo):
    response = []
    formatted_vendors = {}

    try:
        vendors = getAllVendorListDao(mongo)
        for vendor in vendors:
            formatted_vendors[vendor['id']] = vendor

        voucher_list = getTallyReportsDataDao(from_date, to_date, mongo)
        i = 1

        for voucher in voucher_list:
            try:
                vendor_id = voucher['vendor_id']
                tds_amt = 0
                gross_amt = 0
                gst_amt = 0
                v_name = ''
                v_code = ''
                v_address = ''

                if vendor_id in formatted_vendors:
                    v_name = formatted_vendors[vendor_id]['v_name']
                    v_address = formatted_vendors[vendor_id]['v_address']
                    v_code = formatted_vendors[vendor_id]['v_code']
                elif 'vendor_name' in voucher:
                    v_name = voucher['vendor_name']
                    v_address = voucher['vendor_address']

                if 'product_gross_amount' in voucher:
                    gross_amt = voucher['product_gross_amount']
                    tds_amt = gross_amt - voucher['product_total_amount']

                if 'od' in voucher:
                    for order_details in voucher['od']:
                        gst_amt += order_details['product_gst']

                gross_amt = voucher['product_gross_amount']
                total_amt = voucher['product_total_amount']
                add_charges = voucher['po_additional_charges']
                tds_amt = gross_amt - total_amt
                response.append({
                    'Sr,No':
                    i,
                    'Bill Date.':
                    datetime.fromtimestamp(voucher['voucher_created_on'] /
                                           1000).strftime("%x"),
                    'Voucher Type':
                    'Purchase',
                    'Expense Head':
                    voucher['budget_head'],
                    'Status':
                    "Dr",
                    'Party Name':
                    v_name,
                    'Party Code':
                    v_code,
                    'Address':
                    v_address,
                    'State':
                    '',
                    'Amount':
                    total_amt - gst_amt - add_charges,
                    'Total Tax':
                    gst_amt,
                    'Tds':
                    tds_amt,
                    'Additional Charges':
                    add_charges,
                    'Total Amount':
                    gross_amt,
                    'Bill No.':
                    voucher['voucher_code'],
                    'Bill Date':
                    "",
                    'Remark':
                    ""
                })

                i += 1
            except Exception as err:
                print(err)

    except Exception as err:
        print(err)
        response = []

    return response


def getDepriciationReportService(request_data, app, mongo):
    queryResult = {'status': 'failure', 'message': 'Something went wrong!'}

    try:
        current_date = datetime.now()
        fy_year = str(current_date.year - 1) + '-' + str(current_date.year)
        if current_date.month < 4:
            fy_year = str(current_date.year -
                          2) + '-' + str(current_date.year - 1)

        
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], 'far.xlsx')
        # Get Asset depriciation percentage
        dep_per, age = getAssetDepriciationDataDao(request_data['category'],
                                                   mongo)

        # Get Asset Master of requested category
        assets = getAssetReportsDataDao(request_data['category'], mongo)

        headers = [
            "Sr. No.", "Date", "Invoice / Voucher Number", "Location",
            "Intervention", "Quantity", "Decription of the Asset",
            "Group of Asset", "Depreciation %", "Status", "Purchase Cost",
            "Additions", "Write Off", "Sale", "Depreciation",
            "Written Down Value (WDV)"
        ]
        # ========================== #

        # Create a workbook and add a worksheet.
        workbook = xlsxwriter.Workbook(filepath)
        worksheet = workbook.add_worksheet()
        worksheet.freeze_panes(0, 3)
        # Start from the first cell. Rows and columns are zero indexed.
        row = 0
        col = 0


        worksheet.write(row, 0, 'KOTAK')
        worksheet.write(row, 1, 'EDUCATION FOUNDATION, MUMBAI')

        row += 1

        worksheet.write(row, 0, 'Format')
        worksheet.set_column(row, 1, len('for fixed assets: '+request_data['category']))
        worksheet.write(row, 1, 'for fixed assets: '+request_data['category'])

        row += 1

        merge_format = workbook.add_format({'border': 1,'bold': True,'align': 'center',})

        worksheet.merge_range('L3:O3', 'For the F.Y. '+fy_year, merge_format)
        worksheet.write(row, 15, 'As per 31st March '+fy_year.split('-')[1],merge_format)

        row+=1

        worksheet.set_row(row, 20,merge_format)
        column = 0
        for header in headers:
            if column > 1:
                worksheet.set_column(row, column, len(header))
            worksheet.write(row, column, header)
            column += 1

        # ========================== #

        i = 1
        for asset in assets:
            if 'voucher_code' in asset:
                try:
                    row += 1
                    purchase_date = datetime.strptime(asset['purchase_date'],
                                                    '%m/%d/%Y')

                    year_diff = current_date.year - purchase_date.year
                    if year_diff > age:
                        year_diff = age

                    amount = int(asset['amount'])
                    dep_amount = 0
                    wdv = 0
                    sale = '-'
                    write_off = '-'

                    if 'sale' in asset and asset['sale'] == True:
                        sale = asset['sale_amount']

                    if 'write_off' in asset and asset['write_off'] == True:
                        write_off = asset['write_off_amount']

                    try:
                        if year_diff != 0:
                            for k in range(1, year_diff):
                                dep_amount += (amount * dep_per) / 100
                                amount = amount - dep_amount
                    except Exception as err:
                        print(err)

                    wdv = int(asset['amount']) - dep_amount
                    worksheet.set_column(row, column, len(header))
                    worksheet.write(row, 0, i)
                    worksheet.write(row, 1, asset['purchase_date'])
                    worksheet.write(row, 2, asset['voucher_code'])
                    worksheet.write(row, 3, asset['place'])
                    worksheet.write(row, 4, asset['intervention'])
                    worksheet.write(row, 5, 1)
                    worksheet.write(row, 6, asset['discription'])
                    worksheet.write(row, 7, request_data['category'])
                    worksheet.write(row, 8, dep_per)
                    worksheet.write(row, 9, asset['computer_type'])
                    worksheet.write(row, 10, asset['amount'])
                    worksheet.write(row, 11, '-')
                    worksheet.write(row, 12, write_off)
                    worksheet.write(row, 13, sale)
                    worksheet.write(row, 14, dep_amount)
                    worksheet.write(row, 15, wdv)

                    i += 1
                except Exception as err:
                    print(err)
        workbook.close()

        # If created on is of current year and purchase date is between april to march than depri. will be calculated of full
        # if purchase date is between oct to march than depri. will be calculated of half

        # Past three depri created
        queryResult = {
            'status': 'success',
            'message': 'file uploaded successfully!'
        }
    except Exception as err:
        print(err)

    return queryResult


def getVoucherMISReportDataService(from_date, to_date, mongo):
    queryResult = []
    formatted_users = {}
    formatted_vendors = {}
    formatted_po = {}
    try:
        users = getAllUsersFromMaster(mongo)
        vendors = getAllVendorListDao(mongo)
        purchase_orders = getPOListForReport(mongo)

        for user in users:
            formatted_users[user['key']] = user

        for vendor in vendors:
            formatted_vendors[vendor['id']] = vendor

        for po in purchase_orders:
            formatted_po[po['id']] = po['po_order_id']

        vouchers = getAllVoucherAnalyticsDataDao(from_date, to_date, mongo)
        for voucher in vouchers:
            try:
                user_id = voucher['user_id']
                vendor_code=''
                vendor_name=''
                vendor_pan=''
                v_type='Registered'
                pending_level=''
                pending_senior_name=''
                v_account_no=''
                v_ifsc=''

                if 'vendor_id' in voucher:
                    vendor_id = voucher['vendor_id']
                    vendor_code=formatted_vendors[vendor_id]['v_code']
                    vendor_name=formatted_vendors[vendor_id]['v_name']
                    vendor_pan=formatted_vendors[vendor_id]['v_pan']
                    v_account_no=formatted_vendors[vendor_id]['v_account_no']
                    v_ifsc=formatted_vendors[vendor_id]['v_ifsc']
                elif 'vendor_name' in voucher:
                    vendor_name=voucher['vendor_name']
                    v_type='Un-registered'
                    if 'v_account_no' in voucher:
                        v_account_no=voucher['v_account_no']
                        v_ifsc=voucher['v_ifsc']
                
                if 'approval_track' in voucher:
                    for level in voucher['approval_track']:
                        status=voucher['approval_track'][level]['status']
                        if status=='PENDING':
                            pending_level=voucher['approval_track'][level]['level']
                            pending_senior_name=voucher['approval_track'][level]['name']
                
                invoice_url=[]
                for invoice in voucher['invoices']:
                    invoice_url.append('http://65.2.132.48:9005/uploads/'+invoice)
                

                insert_obj = {
                    'voucher_code': voucher['voucher_code'],
                    'intervention': voucher['intervention'],
                    'emp_id': formatted_users[user_id]['emp_id'],
                    'user_name': formatted_users[user_id]['first_name'],
                    'vendor_type':v_type,
                    'vendor_code':vendor_code,
                    'vendor_name': vendor_name,
                    'vendor_pan': vendor_pan,
                    'vendor_account_no':v_account_no,
                    'vendor_ifsc':v_ifsc,
                    'project_type': voucher['project_type'],
                    'mop': voucher['MOP'],
                    'budget_head': voucher['budget_head'],
                    'payment_type': voucher['payment_type'],
                    'tds_rate': voucher['tds_rate'],
                    'additional_charges': voucher['po_additional_charges'],
                    'total_amount': voucher['product_total_amount'],
                    'amount_words': voucher['amount_words'],
                    'status':voucher['voucher_status'],
                    'pending_at':pending_level,
                    'pending_senior_name':pending_senior_name,
                    'invoice':invoice_url
                }


                quotations=[]
                if voucher['po_id'] in formatted_po:
                    po_details=formatted_po[voucher['po_id']]['quotations']
                    quotations=po_details['quotations']
                    insert_obj['po_id']=voucher['po_id']
                else:
                    insert_obj['po_id']='NA'
                    if 'quotations' in voucher:
                        quotations=voucher['quotations']

                if len(quotations)!=0:
                    k=1
                    for quotation in quotations:
                        insert_obj['vendor_name'+str(k)]=quotation['v_name']
                        insert_obj['exception'+str(k)]=quotation['exception']
                        insert_obj['note'+str(k)]=quotation['note']
                        insert_obj['quote_file'+str(k)]='http://65.2.132.48:9005/uploads/'+quotation['quote_file']
                        k+=1
                else:
                    for k in range(1,4):
                        insert_obj['vendor_name'+str(k)]=''
                        insert_obj['exception'+str(k)]=''
                        insert_obj['note'+str(k)]=''
                        insert_obj['quote_file'+str(k)]=''
                
                # print(insert_obj)
                queryResult.append(insert_obj)

            except Exception as err:
                print(err)

    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