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