This rpc module will provide details about all reports
It privides calculation’s for all reports and it’s display
it genarate Ledger,trial balance,balancesheet,profit and loss and so on ...
- create class reports inherit the XMLRPC class
- import modules as needed
import dbconnect import rpc_account import rpc_transaction import rpc_groups import rpc_getaccountsbyrule from twisted.web import xmlrpc, server from twisted.internet import reactor from datetime import datetime,time from modules import blankspace from sqlalchemy import or_ , func , and_ class reports(xmlrpc.XMLRPC): def __init__(self): xmlrpc.XMLRPC.__init__(self)def xmlrpc_getLedger(self,queryParams,client_id):
This function give a complete ledger for given account.
input: [accountname,financialStart,fromdate,todate,projectname]
- output: Returns a grid (2 dimentional list ) with columns as
[Date, Particulars, Reference number, Dr, Cr , vouchercode]
def xmlrpc_getLedger(self,queryParams,client_id): # first let's get the details of the given account regarding the # Balance and its Dr/Cr side. # note that we use the calculateBalance function which gives us # the B/f balance, C/F balance group type and type of the balance. # we will not use this if a project is specified, as there is no # point in displaying opening balance. # if all transactions are to be searched then project # queryparams[4] will be "No Project". queryParams = blankspace.remove_whitespaces(queryParams) balanceRow = self.xmlrpc_calculateBalance([queryParams[0],queryParams[1],queryParams[2],queryParams[3]],client_id) if queryParams[4] == "No Project": # calculateBalance will return opening balance openingBalance = balanceRow[1] else: # if ledger of account which is not under any project # will have opening balance 0.00 openingBalance = 0.00 # declare the ledgerGrid as a blank list. # we will fill it up through a for loop where every iteration will append a row with 5 columns. ledgerGrid = [] # Let's start with 0 for Total Dr and total Cr amounts. totalDr = 0.00 totalCr = 0.00 if openingBalance != 0: # since we know that balance is not 0, we must decide if it is Cr or Dr balance. # This can be found out depending on the opening_baltype from the stored procedure calculateBalance. if balanceRow[5] == "Dr": #this makes the first row of the grid. #note that the total Dr is also set. Same will happen in the next condition for Cr. openingdate = datetime.strptime(str(queryParams[1]),"%d-%m-%Y").strftime("%d-%m-%Y") ledgerGrid.append([openingdate,"Opening Balance b/f","",'%.2f'%(openingBalance),"","",""]) if balanceRow[5] == "Cr": openingdate = datetime.strptime(str(queryParams[1]),"%d-%m-%Y").strftime("%d-%m-%Y") ledgerGrid.append([openingdate,"Opening Balance b/f","","",'%.2f'%(openingBalance),"",""]) else: # its 0 so will be set to 0. totalDr= 0.00 totalCr = 0.00 # create the instance of transaction transaction = rpc_transaction.transaction() # call getTransactions to get the transaction details for this account. transactions = transaction.xmlrpc_getTransactions([\ queryParams[0],queryParams[2],queryParams[3],queryParams[4]],client_id) # fill up the grid with the rows for transactions. for transactionRow in transactions: ledgerRow = [] date = str(transactionRow[2]).split(" ") #print type(str(transactionRow[2]).split("")) transactionDate = datetime.strptime(date[0],"%Y-%m-%d").strftime("%d-%m-%Y") # if the transaction had the amount at Dr side then particulars must have the names of accounts involved in Cr. if transactionRow[1] == "Dr": particulars = transaction.xmlrpc_getParticulars([transactionRow[0],"Cr"],client_id) #may be more than one account was involved at the other side so loop through. particular = [] for particularRow in particulars: particular.append(particularRow) if len(particular) == 1: ledgerRow.append(transactionDate) ledgerRow.append(particular[0]) ledgerRow.append(transactionRow[3]) ledgerRow.append('%.2f'%(float(transactionRow[4]))) totalDr = totalDr + float(transactionRow[4]) ledgerRow.append("") ledgerRow.append(transactionRow[5]) else: accountNames = "" refno = transactionRow[3] drAmount = '%.2f'%(float(transactionRow[4])) crAmount = "" narration = transactionRow[5] for i in range (0, len(particular)): if (i == len(particular)-1): accountNames = accountNames + particular[i] else: transactionDate = transactionDate + "\n" accountNames = accountNames + particular[i] + "\n" refno = refno + "\n" drAmount = drAmount + "\n" crAmount = crAmount + "\n" narration = narration + "\n" ledgerRow.append(transactionDate) ledgerRow.append(accountNames) ledgerRow.append(refno) ledgerRow.append(drAmount) ledgerRow.append(crAmount) ledgerRow.append(narration) if transactionRow[1] == "Cr": particulars = transaction.xmlrpc_getParticulars([transactionRow[0],"Dr"],client_id) particular = [] for particularRow in particulars: particular.append(particularRow) if len(particular) == 1: ledgerRow.append(transactionDate) ledgerRow.append(particular[0]) ledgerRow.append(transactionRow[3]) ledgerRow.append("") ledgerRow.append('%.2f'%(float(transactionRow[4]))) totalCr = totalCr + float(transactionRow[4]) ledgerRow.append(transactionRow[5]) else: accountNames = "" refno = transactionRow[3] drAmount = "" crAmount = '%.2f'%(float(transactionRow[4])) narration = transactionRow[5] for i in range (0, len(particular)): if (i == len(particular)-1): accountNames = accountNames + particular[i] else: transactionDate = transactionDate + "\n" accountNames = accountNames + particular[i] + "\n" refno = refno + "\n" drAmount = drAmount + "\n" crAmount = crAmount + "\n" narration = narration + "\n" ledgerRow.append(transactionDate) ledgerRow.append(accountNames) ledgerRow.append(refno) ledgerRow.append(drAmount) ledgerRow.append(crAmount) ledgerRow.append(narration) ledgerRow.append(transactionRow[0]) ledgerGrid.append(ledgerRow) #the transactions have been filled up duly. #now for the total dRs and Crs, we have added them up nicely during the grid loop. ledgerGrid.append(["","Total of Transactions","",'%.2f'%(totalDr),'%.2f'%(totalCr),"",""]) if queryParams[4] == "No Project": ledgerGrid.append(["","","","","","",""]) grandTotal = 0.00 closingdate = datetime.strptime(str(queryParams[3]),"%d-%m-%Y").strftime("%d-%m-%Y") if balanceRow[6] == "Dr": #this is a Dr balance which will be shown at Cr side. #Difference will be also added to Cr for final balancing. ledgerGrid.append([closingdate,"Closing Balance b/f","","",'%.2f'%(balanceRow[2]),"",""]) grandTotal =float(balanceRow[4]) + float(balanceRow[2]) if balanceRow[6] == "Cr": #now exactly the opposit, see the explanation in the if condition preceding this one. ledgerGrid.append([closingdate,"Closing Balance b/f","",'%.2f'%(balanceRow[2]),"","",""]) grandTotal =float(balanceRow[3]) + float(balanceRow[2]) ledgerGrid.append(["","Grand Total","",'%.2f'%(grandTotal),'%.2f'%(grandTotal),"",""]) #we are ready with the complete ledger, so lets send it out! return ledgerGriddef xmlrpc_calculateBalance(self,queryParams,client_id):
- calculate closing balance of given accounts
- Returns a grid of 4 columns and number of rows depending on number of accounts.
- input: [accountname,org_financial_from,report_from_date,report_to_date]
- returns: [group_name,bal_brought,curbal,total_DrBal,total_CrBal,opening_baltype,baltype]
def xmlrpc_calculateBalance(self,queryParams,client_id): # get the groupname accourding to ac statement = "select groupname\ from group_subgroup_account\ where accountname = '"+queryParams[0]+"'" result = dbconnect.engines[client_id].execute(statement).fetchone() group_name = result[0] statement = "select openingbalance\ from group_subgroup_account\ where accountname = '"+queryParams[0]+"'" result = dbconnect.engines[client_id].execute(statement).fetchone() opening_balance = result[0] #print "opening_balance" #print opening_balance financial_fromdate = str(datetime.strptime(str(queryParams[1]),"%d-%m-%Y")) report_fromdate = str(datetime.strptime(str(queryParams[2]),"%d-%m-%Y")) report_todate = str(datetime.strptime(str(queryParams[3]),"%d-%m-%Y")) if financial_fromdate == report_fromdate: if opening_balance == 0: bal_brought = opening_balance opening_baltype = 0 baltype = 0 if (opening_balance < 0) and (group_name == 'Current Asset' \ or group_name == 'Fixed Assets'\ or group_name == 'Investment' \ or group_name == 'Loans(Asset)' \ or group_name == 'Miscellaneous Expenses(Asset)'): bal_brought = abs(opening_balance) opening_baltype = 'Cr' baltype = 'Cr' if (opening_balance > 0) and (group_name == 'Current Asset' \ or group_name =='Fixed Assets'\ or group_name == 'Investment' \ or group_name == 'Loans(Asset)' \ or group_name == 'Miscellaneous Expenses(Asset)'): bal_brought = opening_balance opening_baltype = 'Dr' baltype = 'Dr' if (opening_balance < 0 ) and (group_name == 'Corpus' \ or group_name == 'Capital' \ or group_name == 'Current Liability' \ or group_name == 'Loans(Liability)' \ or group_name == 'Reserves'): bal_brought = abs(opening_balance) opening_baltype = 'Dr' baltype = 'Dr' if (opening_balance > 0) and (group_name == 'Corpus' \ or group_name == 'Capital' \ or group_name == 'Current Liability'\ or group_name == 'Loans(Liability)'\ or group_name == 'Reserves'): bal_brought = opening_balance opening_baltype = 'Cr' baltype = 'Cr' #print baltype else: statement = "select sum(amount) as dr_amount\ from view_voucherbook \ where account_name = '"+queryParams[0]+"'\ and typeflag = 'Dr' \ and reffdate >= '"+financial_fromdate+"' \ and reffdate < '"+report_fromdate+"'\ and flag = 1" result = dbconnect.engines[client_id].execute(statement).fetchone() total_dr_upto_from = result[0] statement = "select sum(amount) as cr_amount \ from view_voucherbook \ where account_name ='"+queryParams[0]+"'\ and typeflag = 'Cr' \ and reffdate >= '"+financial_fromdate+"' \ and reffdate < '"+report_fromdate+"' \ and flag = 1" result = dbconnect.engines[client_id].execute(statement).fetchone() total_cr_upto_from = result[0] if total_dr_upto_from == None: total_dr_upto_from = 0 if total_cr_upto_from == None: total_cr_upto_from = 0 if opening_balance == 0: bal_brought = opening_balance if (opening_balance < 0) and (group_name == 'Current Asset'\ or group_name == 'Fixed Assets'\ or group_name == 'Investment' \ or group_name == 'Loans(Asset)' \ or group_name == 'Miscellaneous Expenses(Asset)'): total_cr_upto_from = total_cr_upto_from + abs(opening_balance) if (opening_balance > 0) and (group_name == 'Current Asset'\ or group_name == 'Fixed Assets'\ or group_name == 'Investment'\ or group_name == 'Loans(Asset)'\ or group_name == 'Miscellaneous Expenses(Asset)'): total_dr_upto_from = total_dr_upto_from + opening_balance if (opening_balance < 0) and (group_name == 'Corpus'\ or group_name == 'Capital'\ or group_name == 'Current Liability'\ or group_name == 'Loans(Liability)'\ or group_name == 'Reserves'): total_dr_upto_from = total_dr_upto_from + abs(opening_balance) if (opening_balance > 0) and (group_name == 'Corpus'\ or group_name == 'Capital'\ or group_name == 'Current Liability'\ or group_name == 'Loans(Liability)'\ or group_name == 'Reserves'): total_cr_upto_from = total_cr_upto_from + opening_balance if total_dr_upto_from > total_cr_upto_from: bal_brought = total_dr_upto_from - total_cr_upto_from baltype = 'Dr' opening_baltype = 'Dr' if total_dr_upto_from < total_cr_upto_from: bal_brought = total_cr_upto_from - total_dr_upto_from baltype = 'Cr' opening_baltype = 'Cr' statement = "select sum(amount) as dr_amount\ from view_voucherbook\ where typeflag = 'Dr'\ and account_name = '"+queryParams[0]+"'\ and reffdate >= '"+report_fromdate+"'\ and reffdate <= '"+report_todate+"' \ and flag = 1" result = dbconnect.engines[client_id].execute(statement).fetchone() total_DrBal = result[0] statement = "select sum(amount) as cr_amount\ from view_voucherbook\ where typeflag ='Cr'\ and account_name = '"+queryParams[0]+"'\ and reffdate >= '"+report_fromdate+"'\ and reffdate <= '"+report_todate+"'\ and flag = 1" result = dbconnect.engines[client_id].execute(statement).fetchone() total_CrBal = result[0] if total_CrBal == None: total_CrBal = 0 if total_DrBal == None: total_DrBal = 0 if baltype == 'Dr': total_DrBal = total_DrBal + bal_brought if baltype == 'Cr': total_CrBal = total_CrBal + bal_brought if total_DrBal > total_CrBal: curbal = total_DrBal - total_CrBal baltype = 'Dr' else: curbal = total_CrBal - total_DrBal baltype = 'Cr' calculate_balancelist = [group_name,bal_brought,curbal,total_DrBal,total_CrBal,opening_baltype,baltype] return calculate_balancelist
- def xmlrpc_getTrialBalance(self,queryParams,client_id):
gets trial balance as on the given date.
returns a grid of 4 columns and number of rows depending on number of accounts.
this function returns a grid of 4 columns contaning trial balance.
number of rows in this grid will depend on the number of accounts in the database.
the function first makes a call to the getAllAccounts from rpc_account to get accountlist
then a loop runs through the list of accounts. on every iteration it calls the calculateBalance and passes the account as a parameter along with the financial start from_date and to_date
note that trial balance is always calculated from the starting of the financial year.
also in the for loop we see if the typeflag for the balance for given account is Dr or Cr.
if the balance is Dr then we put the amount in the 4th column, with the 5th column blank.
if the typeflag is credit then we put the amount in the 5th row, leaving the 4th as blank. and vice varsa
input: [org_financial_from,from_date,to_date]
- output: [serial no , accountname , groupname , debit bal , creadit bal ]
and [total debit , total credit]
def xmlrpc_getTrialBalance(self,queryParams,client_id): queryParams = blankspace.remove_whitespaces(queryParams) account = rpc_account.account() accounts = account.xmlrpc_getAllAccountNames(client_id) trialBalance = [] srno =1 total_dr = 0.00 total_cr = 0.00 for account in accounts: closingRow = self.xmlrpc_calculateBalance([account,queryParams[0],queryParams[1],queryParams[2]],client_id) if float(closingRow[2])!= 0: trialRow = [] trialRow.append(srno) trialRow.append(account) trialRow.append(closingRow[0]) if closingRow[6] == "Cr": total_cr = total_cr + float(closingRow[2]) trialRow.append("") trialRow.append('%.2f'%float(closingRow[2])) if closingRow[6] == "Dr": total_dr = total_dr + float(closingRow[2]) trialRow.append('%.2f'%float(closingRow[2])) trialRow.append("") srno = srno +1 trialBalance.append(trialRow) total_balances = ["","","",'%.2f'%total_dr,'%.2f'%total_cr] trialBalance.append(total_balances) return trialBalancedef xmlrpc_getGrossTrialBalance(self,queryParams,client_id):
just like the getTrialBalance, this function returns list of balances of all accounts.
instead of the current balance it provides the total Dr and total Cr for all accounts.
- input: [serial no , accountname , groupname , debit bal , creadit bal ]
and [total debit , total credit]
def xmlrpc_getGrossTrialBalance(self,queryParams,client_id): account = rpc_account.account() accounts = account.xmlrpc_getAllAccountNames(client_id) trialBalance = [] srno =1 total_dr = 0.00 total_cr = 0.00 for acc in accounts: closingRow = self.xmlrpc_calculateBalance(\ [acc,queryParams[0],queryParams[1],queryParams[2]],client_id) if float(closingRow[3]) != 0 or float(closingRow[4]) != 0: trialRow = [] trialRow.append(srno) trialRow.append(acc) trialRow.append(closingRow[0]) trialRow.append('%.2f'%float(closingRow[3])) trialRow.append('%.2f'%float(closingRow[4])) total_dr = total_dr + float(closingRow[3]) total_cr = total_cr + float(closingRow[4]) srno = srno +1 trialBalance.append(trialRow) total_balances = ['','','','%.2f'%total_dr,'%.2f'%total_cr] trialBalance.append(total_balances) return trialBalance
- def xmlrpc_getExtendedTrialBalance(self,queryParams,client_id):
- gets extended trial balance as on the given date.
- Returns a grid of 7 columns and number of rows depending on number of accounts.
def xmlrpc_getExtendedTrialBalance(self,queryParams,client_id): queryParams = blankspace.remove_whitespaces(queryParams) account = rpc_account.account() accounts = account.xmlrpc_getAllAccountNames(client_id) trialBalance = [] srno =1 total_dr = 0.00 total_cr = 0.00 total_ExtendedCr = 0.00 total_ExtendedDr = 0.00 for acc in accounts: closingRow = self.xmlrpc_calculateBalance(\ [acc,queryParams[0],queryParams[1],queryParams[2]],client_id) if float(closingRow[1]) != 0 or float(closingRow[3]) != 0 or float(closingRow[4]) != 0: trialRow = [] trialRow.append(srno) trialRow.append(acc) trialRow.append(closingRow[0]) if float(closingRow[1]) != 0 and closingRow[5] == "Dr": trialRow.append('%.2f'%float(closingRow[1])+"(Dr)") trialRow.append('%.2f'%(float(closingRow[3])- float(closingRow[1]))) total_dr = total_dr + (float(closingRow[3]) - float(closingRow[1])) trialRow.append('%.2f'%float(closingRow[4])) total_cr = total_cr +float(closingRow[4]) if float(closingRow[1]) != 0 and closingRow[5] == "Cr": trialRow.append('%.2f'%float(closingRow[1])+"(Cr)") trialRow.append('%.2f'%float(closingRow[3])) total_dr = total_dr + float(closingRow[3]) trialRow.append('%.2f'%(float(closingRow[4])- float(closingRow[1]))) total_cr = total_cr + (float(closingRow[4]) - float(closingRow[1])) if float(closingRow[1]) == 0: trialRow.append("") trialRow.append('%.2f'%float(closingRow[3])) total_dr = total_dr + float(closingRow[3]) trialRow.append('%.2f'%float(closingRow[4])) total_cr = total_cr + float(closingRow[4]) if closingRow[6] == "Dr": trialRow.append('%.2f'%float(closingRow[2])) trialRow.append("") total_ExtendedDr = total_ExtendedDr + float(closingRow[2]) if closingRow[6] == "Cr": trialRow.append("") trialRow.append('%.2f'%float(closingRow[2])) total_ExtendedCr = total_ExtendedCr + float(closingRow[2]) srno = srno +1 trialBalance.append(trialRow) total_balances = ['','','','','%.2f'%total_dr,'%.2f'%total_cr,'%.2f'%total_ExtendedDr,'%.2f'%total_ExtendedCr] trialBalance.append(total_balances) return trialBalance
- def xmlrpc_getProjectStatementReport(self,queryParams,client_id):
- it gives project statement report
- input: [projectname,financial_fromdate,fromdate,todate]
- output: list of list [serialno,accountname,groupname,totalDr,totalCr]
def xmlrpc_getProjectStatementReport(self,queryParams,client_id): account = rpc_account.account() group = rpc_groups.groups() projectAccounts =account.xmlrpc_getAccountNamesByProjectName([str(queryParams[0])],client_id) totalDr = 0.00 totalCr = 0.00 srno = 1 projectStatement = [] for accountRow in projectAccounts: groupRow = group.xmlrpc_getGroupNameByAccountName([accountRow],client_id) accountGroup = groupRow[0] resultRow = self.xmlrpc_getProjectStatement(\ [queryParams[0],accountRow,queryParams[1],queryParams[2],queryParams[3]],client_id) if(('%.2f'%float(resultRow[0])!= "0.00" )or('%.2f'%float(resultRow[1])!="0.00")): statementRow = [srno,accountRow,accountGroup,'%.2f'%float(resultRow[0]),'%.2f'%float(resultRow[1])] totalDr = totalDr + resultRow[0] totalCr = totalCr + resultRow[1] srno = srno +1 projectStatement.append(statementRow) projectStatement.append(["","","",'%.2f'%float(totalDr),'%.2f'%float(totalCr)]) return projectStatementdef xmlrpc_getProjectStatement(self,queryParams,client_id):
- it gives project statement grid .
- input: [projectname,accountname,financial_fromdate,fromdate,todate]
- output: [total_debit,total_credit]
def xmlrpc_getProjectStatement(self,queryParams,client_id): financial_fromdate = str(datetime.strptime(str(queryParams[2]),"%d-%m-%Y")) report_fromdate = str(datetime.strptime(str(queryParams[3]),"%d-%m-%Y")) report_todate = str(datetime.strptime(str(queryParams[4]),"%d-%m-%Y")) transaction = rpc_transaction.transaction() projectcode = transaction.xmlrpc_getProjectcodeByProjectName([queryParams[0]],client_id) statement = "select sum(amount)\ from view_voucherbook\ where projectcode = '"+str(projectcode)+"'\ and account_name = '"+str(queryParams[1])+"'\ and reffdate >= '"+report_fromdate+"'\ and reffdate <= '"+report_todate+"' \ and typeflag = 'Dr'\ and flag = 1" totalDr = dbconnect.engines[client_id].execute(statement).fetchone() total_dr = totalDr[0] statement = "select sum(amount)\ from view_voucherbook\ where projectcode = '"+str(projectcode)+"'\ and account_name = '"+str(queryParams[1])+"'\ and reffdate >= '"+report_fromdate+"'\ and reffdate <= '"+report_todate+"' \ and typeflag = 'Cr'\ and flag = 1" totalCr = dbconnect.engines[client_id].execute(statement).fetchone() total_cr = totalCr[0] if total_dr == None: total_dr = 0.00 if total_cr == None: total_cr = 0.00 return [total_dr,total_cr]def xmlrpc_getBalancesheet(self,queryParams,client_id):
- gets trial balance as on the given date.
- returns a grid of 4 columns and number of rows depending on number of accounts.
- This function returns a grid of 4 columns contaning balancesheet.
- Number of rows in this grid will depend on the number of accounts in the database.
- input: [org_financial_from,from_date,to_date]
def xmlrpc_getBalancesheet(self,queryParams,client_id): assetGrpCodes = [6,2,10,9,13] liabilitiesGrpCodes = [1,3,11,12] balancesheet = [] assetSrno = 1; liabilitiesSrno = 1 total_asset_balances = 0.00; total_liabilities_balances = 0.00 tot_capital = 0.00 tot_currliabilities = 0.00 tot_loansliabilities = 0.00 tot_reserves = 0.00 tot_fixedasset = 0.00 tot_currentasset = 0.00 tot_loansasset = 0.00 tot_investment = 0.00 tot_miscExpense = 0.00 account = rpc_account.account() for grpCode in liabilitiesGrpCodes: accounts = account.xmlrpc_getAccountNamesByGroupCode([grpCode],client_id) if accounts != []: for acc in accounts: assetrow = []; liabilitiesrow = [] closingRow = self.xmlrpc_calculateBalance(\ [acc,queryParams[0],queryParams[1],queryParams[2]],client_id) if closingRow[6] == "Cr": closingBalanceAmount = float(closingRow[2]) else: closingBalanceAmount = - float(closingRow[2]) if closingBalanceAmount != 0: liabilitiesrow.append(liabilitiesSrno) liabilitiesrow.append(grpCode) liabilitiesrow.append(acc) liabilitiesrow.append('%.2f'%(closingBalanceAmount)) if (grpCode == 1): tot_capital += closingBalanceAmount if (grpCode == 3): tot_currliabilities += closingBalanceAmount if (grpCode == 11): tot_loansliabilities += closingBalanceAmount if (grpCode == 12): tot_reserves += closingBalanceAmount total_liabilities_balances += closingBalanceAmount balancesheet.append(liabilitiesrow) liabilitiesSrno += 1 for grpCode in assetGrpCodes: accounts = account.xmlrpc_getAccountNamesByGroupCode([grpCode],client_id) if accounts != []: for acc in accounts: assetrow = []; liabilitiesrow = [] closingRow = self.xmlrpc_calculateBalance(\ [acc,queryParams[0],queryParams[1],queryParams[2]],client_id) if closingRow[6] == "Dr": closingBalanceAmount = float(closingRow[2]) else: closingBalanceAmount = - float(closingRow[2]) if closingBalanceAmount != 0: assetrow.append(assetSrno) assetrow.append(grpCode) assetrow.append(acc) assetrow.append('%.2f'%(closingBalanceAmount)) if (grpCode == 6): tot_fixedasset += closingBalanceAmount if (grpCode == 2): tot_currentasset += closingBalanceAmount if (grpCode == 10): tot_loansasset += closingBalanceAmount if (grpCode == 9): tot_investment += closingBalanceAmount if (grpCode == 13): tot_miscExpense += closingBalanceAmount total_asset_balances += closingBalanceAmount balancesheet.append(assetrow) assetSrno += 1 balancesheet.append(assetSrno - int(1)) balancesheet.append(liabilitiesSrno - int(2)) balancesheet.append('%.2f'%(float(tot_investment))) balancesheet.append('%.2f'%(float(tot_loansasset))) balancesheet.append('%.2f'%(float(tot_currentasset))) balancesheet.append('%.2f'%(float(tot_fixedasset))) balancesheet.append('%.2f'%(float(tot_miscExpense))) balancesheet.append('%.2f'%(float(tot_currliabilities))) balancesheet.append('%.2f'%(float(tot_loansliabilities))) balancesheet.append('%.2f'%(float(tot_capital))) balancesheet.append('%.2f'%(float(tot_reserves))) balancesheet.append('%.2f'%(float(total_liabilities_balances))) balancesheet.append('%.2f'%(float(total_asset_balances))) return balancesheetdef xmlrpc_getBalancesheetDisplay(self,queryParams,client_id):
- note that this function is not do any calculations it is just for the format for front end display.
- it will take the return grid of getBalancesheet.
- to rearrange the data in a particular manner for display.
- input: [org_financial_from,from_date,to_date,reportflag,orgtype,balancesheet_type]
def xmlrpc_getBalancesheetDisplay(self,queryParams,client_id): # flag to check balancesheet or profitloss reportFlag = queryParams[3] balancesheet_type = queryParams[5] # type of balancesheet orgtype = queryParams[4] # type of organisation, NGO or Profit Making finallist = [] # initialize empty list for final grid corpuslist = [] # initialize empty list for left table(Corpus or Capital) assetslist = [] # initialize empty list for final grid(Properties n Assets) LeftList = [] # get the values of balacesheet to rearrange it trialdata = self.xmlrpc_getBalancesheet(queryParams,client_id) baltrialdata = trialdata assSrno = trialdata[len(trialdata) - int(13)] liaSrno = trialdata[len(trialdata) - int(12)] if (assSrno > liaSrno): rowFlag = "liabilities" rows = assSrno - liaSrno elif (assSrno < liaSrno): rowFlag = "asset" rows = liaSrno - assSrno else: rowFlag = "" assetrowcolor = assSrno + int(4) liabilitiesrowcolor = liaSrno + int(4) tot_miscellaneous = trialdata[len(trialdata) - int(7)] tot_investment = trialdata[len(trialdata) - int(11)] tot_loansasset = trialdata[len(trialdata) - int(10)] tot_currentasset = trialdata[len(trialdata) - int(9)] tot_fixedasset = trialdata[len(trialdata) - int(8)] tot_capital = trialdata[len(trialdata) - int(4)] tot_currlia = trialdata[len(trialdata) - int(6)] tot_loanlia = trialdata[len(trialdata) - int(5)] tot_reserves = trialdata[len(trialdata) - int(3)] ballength = len(trialdata) - int(13) lialength = len(trialdata) - int(1) asslength = len(trialdata) - int(2) reportFlag = "balancesheet" # To get NetProfit and NetLoss from profit and loss and display in balncesheet profitloss = self.xmlrpc_getProfitLossDisplay(queryParams,client_id) totalDr = trialdata[lialength] totalCr = trialdata[asslength] Flag = profitloss[0] pnlcr = float(totalCr) + float(profitloss[1]) pnldr = float(totalDr) + float(profitloss[1]) pnl1 = '%.2f'%float(pnlcr) pnl2 = '%.2f'%float(pnldr) Rcount = 0 # counter to count rightside(assets colo rows) Lcount = 0 # counter to count leftside(Capital& Liabilities row) if Flag =="netProfit": if float(totalDr) > float(pnlcr): difamount ='%.2f'%(float(totalDr) - float(pnlcr)) else: difamount = '%.2f'%(float(pnlcr)-float(totalDr)) else: if float(totalCr) > float(pnldr): difamount = '%.2f'%(float(totalCr) - float(pnldr)) else: difamount = '%.2f'%(float(pnldr)-float(totalCr))# get the values of balacesheet to rearrange it if balancesheet_type == "Conventional Balance Sheet": if (orgtype == "NGO"): groupname = "Corpus & Liabilities" if (orgtype == "Profit Making"): groupname = "Capital & Liabilities" corpuslist.append([groupname,"Debit","Credit","Total Amount"]) #Lcount = Lcount+1 if (tot_capital != "0.00"): if (orgtype == "NGO"): groupname="CORPUS" if (orgtype == "Profit Making"): groupname="CAPITAL" corpuslist.append([groupname,"","",""]) Lcount = Lcount+1 for i in range (0, ballength): if (baltrialdata[i][1] == 1): accountname = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): corpuslist.append([" "+accountname,"",'%.2f'%(abs(amount)),""]) else: corpuslist.append([" "+accountname,'%.2f'%(amount),"",""]) Lcount = Lcount+1 corpuslist.append(["","","",tot_capital]) Lcount = Lcount+1 if (tot_reserves != "0.00"): corpuslist.append(["RESERVES","","",""]) Lcount = Lcount+1 for i in range (0, ballength): if (baltrialdata[i][1] == 12): accountname = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): corpuslist.append([" "+accountname,"",'%.2f'%(abs(amount)),""]) else: corpuslist.append([" "+accountname,'%.2f'%(amount),"",""]) Lcount = Lcount+1 corpuslist.append(["","","",tot_reserves]) Lcount = Lcount+1 if (tot_loanlia != "0.00"): corpuslist.append(["LOANS(Liability)","","",""]) Lcount = Lcount+1 for i in range (0, ballength): if (baltrialdata[i][1] == 11): accountname = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): corpuslist.append([" "+accountname,"",'%.2f'%(abs(amount)),""]) else: corpuslist.append([" "+accountname,'%.2f'%(amount),"",""]) Lcount = Lcount+1 corpuslist.append(["","","",tot_loanlia]) Lcount = Lcount+1 if (tot_currlia != "0.00"): corpuslist.append(["CURRENT LIABILITIES","","",""]) Lcount = Lcount+1 for i in range (0, ballength): if (baltrialdata[i][1] == 3): accountname = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount < 0): corpuslist.append([" "+accountname,"",'%.2f'%(abs(amount)),""]) else: corpuslist.append([" "+accountname,'%.2f'%(amount),"",""]) Lcount = Lcount+1 corpuslist.append(["","","",tot_currlia]) Lcount = Lcount+1 if (Flag != "netLoss"): if (orgtype != "NGO"): flag = "NET PROFIT" else: flag = "NET SURPLUS" if (profitloss[1] != "0.00"): corpuslist.append(["",flag,profitloss[1],""]) Lcount = Lcount+1 #else: #corpuslist.append(["","","",""]) #Lcount = Lcount+1 if (rowFlag == "liabilities"): for i in range (0, rows): corpuslist.append(["","","",""]) Lcount = Lcount+1 ############# ASSETS ################### assetslist.append(["Property & Assets","Debit","Credit","Total amount"]) #Rcount = Rcount+1 if (tot_fixedasset != "0.00"): assetslist.append(["FIXED ASSETS","","",""]) Rcount = Rcount+1 for i in range (0, ballength): if (baltrialdata[i][1] == 6): accountname = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): assetslist.append([" "+accountname,"",'%.2f'%(abs(amount)),""]) else: assetslist.append([" "+accountname,'%.2f'%(amount),"",""]) Rcount = Rcount+1 assetslist.append(["","","",tot_fixedasset]) Rcount = Rcount+1 if (tot_currentasset != "0.00"): groupname="CURRENT ASSETS" assetslist.append([groupname,"","",""]) Rcount = Rcount+1 for i in range (0, ballength): if (baltrialdata[i][1] == 2): accountname = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount < 0): assetslist.append([" "+accountname,"",'%.2f'%(abs(amount)),""]) else: assetslist.append([" "+accountname,'%.2f'%(amount),"",""]) Rcount = Rcount+1 assetslist.append(["","","",tot_currentasset]) Rcount = Rcount+1 if (tot_loansasset != "0.00"): groupname="LOANS(Asset)" assetslist.append([groupname,"","",""]) Rcount = Rcount+1 for i in range (0, ballength): if (baltrialdata[i][1] == 10): accountname = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): assetslist.append([" "+accountname,"",'%.2f'%(abs(amount)),""]) else: assetslist.append([" "+accountname,'%.2f'%(amount),"",""]) Rcount = Rcount+1 assetslist.append(["","","",tot_loansasset]) Rcount = Rcount+1 if (tot_investment != "0.00"): groupname="INVESTMENTS" assetslist.append([groupname,"","",""]) Rcount = Rcount+1 for i in range (0, ballength): if (baltrialdata[i][1] == 9): accountname = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): assetslist.append([" "+accountname,"",'%.2f'%(abs(amount)),""]) else: assetslist.append([" "+accountname,'%.2f'%(amount),"",""]) Rcount = Rcount+1 assetslist.append(["","","",tot_investment]) Rcount = Rcount+1 if (tot_miscellaneous != "0.00"): groupname="MISCELLANEOUS EXPENSES(Asset)" assetslist.append([groupname,"","",""]) Rcount = Rcount+1 for i in range (0, ballength): if (baltrialdata[i][1] == 13): accountname = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): assetslist.append([" "+accountname,"",'%.2f'%(abs(amount)),""]) else: assetslist.append([" "+accountname,'%.2f'%(amount),"",""]) Rcount = Rcount+1 assetslist.append(["","","",tot_miscellaneous]) Rcount = Rcount+1 if (Flag == "netLoss"): if (orgtype != "NGO"): flag = "NET LOSS" else: flag = "NET DEFICIT" if (profitloss[1] != "0.00"): assetslist.append(["",flag,profitloss[1],""])# net surplus or net profit Rcount = Rcount+1 #else: #assetslist.append(["","","",""]) #Rcount = Rcount+1 if (Lcount > Rcount): diff = Lcount - Rcount for i in range(0,diff): assetslist.append(["","","",""]) else: diff = Rcount - Lcount for i in range(0,diff): corpuslist.append(["","","",""]) if (Flag == "netLoss"): if (difamount != "0.00"): totalCr else: totalCr corpuslist.append(["TOTAL","","",totalCr]) if (difamount != "0.00"): pnl2 else: pnl2 assetslist.append(["TOTAL","","",pnl2]) if (Flag != "netLoss"): if (difamount != "0.00"): pnl1 else: pnl1 corpuslist.append(["TOTAL","","",pnl1]) if (difamount != "0.00"): totalDr else: totalDr assetslist.append(["TOTAL","","",totalDr]) finallist.append(corpuslist) finallist.append(assetslist) difflist = [] # initialize empty list for empty spaces difflist.append(difamount) finallist.append(difflist) return finallist if balancesheet_type == "Sources and Application of Funds": LeftList.append(["Particulars","Debit","Credit","Amount","Amount"]) LeftList.append(["SOURCES OF FUNDS","","","",""]) if (tot_capital != "0.00"): if (orgtype == "NGO"): LeftList.append([" CORPUS","","","",""]) if (orgtype == "Profit Making"): LeftList.append([" OWNER'S CAPITAL","","","",""]) for i in range (0, ballength): if (baltrialdata[i][1] == 1): account = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): LeftList.append([" "+account,"",'%.2f'%(abs(amount)),"",""]) else: LeftList.append([" "+account,'%.2f'%(amount),"","",""]) if (orgtype == "NGO"): LeftList.append(["TOTAL CORPUS","","","",tot_capital]) else: LeftList.append(["TOTAL CAPITAL","","","",tot_capital]) if (profitloss[1] != "0.00"): LeftList.append([" ADD: RESERVES","","","",""]) for i in range (0, ballength): if (baltrialdata[i][1] == 12): account =baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): LeftList.append([" "+account,"",'%.2f'%(abs(amount)),"",""]) else: LeftList.append([" "+account,'%.2f'%(amount),"","",""]) if (Flag == "netLoss"): if (orgtype != "NGO"): flag ="Net Loss" else: flag="Net Deficit" LeftList.append([" "+flag,profitloss[1],"","",""]) amount = float(tot_reserves) - float(profitloss[1]) LeftList.append(["TOTAL RESERVES & SURPLUS","","","",'%.2f'%float(amount)]) else: if (orgtype != "NGO"): flag ="Net Profit" else: flag ="Net Surplus" amount =float(tot_reserves) + float(profitloss[1]) LeftList.append([" "+flag,profitloss[1],"","",""]) LeftList.append(["TOTAL RESERVES & SURPLUS","","","",'%.2f'%float(amount)]) if (tot_miscellaneous != "0.00"): LeftList.append([" LESS: MISCELLANEOUS EXPENSES(Asset)","","","",""]) for i in range (0, ballength): if (baltrialdata[i][1] == 13): account =baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): LeftList.append([" "+account,"",'%.2f'%(abs(amount)),"",""]) else: LeftList.append([" "+account,'%.2f'%(amount),"","",""]) LeftList.append(["TOTAL MISCELLANEOUS EXPENSES(Asset)","","",tot_miscellaneous,""]) if (Flag == "netLoss"): amount = float(tot_reserves) - float(profitloss[1]) - float(tot_miscellaneous) else: amount = float(tot_reserves) + float(profitloss[1]) - float(tot_miscellaneous) if (amount != 0): LeftList.append(["TOTAL OF OWNER'S FUNDS","","","",'%.2f'%float(amount)]) if (tot_loanlia != "0.00"): LeftList.append([" BORROWED FUNDS","","","",""]) LeftList.append([" LOANS(Liability)","","","",""]) for i in range (0, ballength): if (baltrialdata[i][1] == 11): account = baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): LeftList.append([" "+account,"",'%.2f'%(abs(amount)),"",""]) else: LeftList.append([" "+account,'%.2f'%(amount),"","",""]) LeftList.append(["TOTAL BORROWED FUNDS","","","",tot_loanlia]) if (difamount != "0.00"): if (Flag == "netLoss"): amount= float(tot_capital) + \ float(tot_loanlia) + \ (float(tot_reserves) - float(profitloss[1]) - float(tot_miscellaneous)) else: amount= float(tot_capital) + \ float(tot_loanlia) + \ (float(tot_reserves) + float(profitloss[1]) - float(tot_miscellaneous)) LeftList.append(["TOTAL FUNDS AVAILABLE / CAPITAL EMPLOYED","","","",'%.2f'%float(amount)]) else: if (Flag == "netLoss"): amount= float(tot_capital) + \ float(tot_loanlia) + \ (float(tot_reserves) - float(profitloss[1]) - float(tot_miscellaneous)) else: amount= float(tot_capital) + \ float(tot_loanlia) + \ (float(tot_reserves) + float(profitloss[1]) - float(tot_miscellaneous)) LeftList.append(["TOTAL FUNDS AVAILABLE / CAPITAL EMPLOYED","","","",'%.2f'%float(amount)]) finallist.append(LeftList) ###################### secound list #################### RightList = [] RightList.append(["Particulars","Debit","Credit","Amount","Amount"]) RightList.append(["APPLICATION OF FUNDS","","","",""]) if (tot_fixedasset != "0.00"): RightList.append([" FIXED ASSETS","","","",""]) for i in range (0, ballength): if (baltrialdata[i][1] == 6): account=baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): RightList.append([" "+account,"",'%.2f'%(abs(amount)),"",""]) else: RightList.append([" "+account,'%.2f'%(amount),"","",""]) RightList.append(["TOTAL FIXED ASSETS(Net)","","","",tot_fixedasset]) if (tot_investment != "0.00"): RightList.append([" INVESTMENT","","","",""]) for i in range (0, ballength): if (baltrialdata[i][1] == 9): account =baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): RightList.append([" "+account,"",'%.2f'%(abs(amount)),"",""]) else: RightList.append([" "+account,'%.2f'%(amount),"","",""]) RightList.append(["TOTAL LONG TERM INVESTMENTS","","","",tot_investment]) if (tot_loansasset != "0.00"): RightList.append([" LOANS(Asset)","","","",""]) for i in range (0, ballength): if (baltrialdata[i][1] == 10): account =baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): RightList.append([" "+account,"",'%.2f'%(abs(amount)),"",""]) else: RightList.append([" "+account,'%.2f'%(amount),"","",""]) RightList.append(["TOTAL LOANS(Asset)","","","",tot_loansasset]) if (tot_currentasset != "0.00"): RightList.append([" WORKING CAPITAL","","","",""]) RightList.append([" CURRENT ASSETS","","","",""]) for i in range (0, ballength): if (baltrialdata[i][1] == 2): account=baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): RightList.append([" "+account,"",'%.2f'%(abs(amount)),"",""]) else: RightList.append([" "+account,'%.2f'%(amount),"","",""]) RightList.append(["TOTAL CURRENT ASSETS","","",tot_currentasset,""]) if (tot_currlia != "0.00"): RightList.append([" LESS:","","","",""]) RightList.append([" CURRENT LIABILITIES","","","",""]) for i in range (0, ballength): if (baltrialdata[i][1] == 3): account=baltrialdata[i][2] amount = float(baltrialdata[i][3]) if(amount<0): RightList.append([" "+account,"",'%.2f'%(abs(amount)),"",""]) else: RightList.append([" "+account,'%.2f'%(amount),"","",""]) RightList.append(["TOTAL CURRENT LIABILITIES","","",tot_currlia,""]) amount = float(tot_currentasset) - float(tot_currlia) if (amount != 0): RightList.append(["NET CURRENT ASSETS OR WORKING CAPITAL","","","",'%.2f'%float(amount)]) RightList.append([" CURRENT ASSETS","","","",""]) if (difamount != "0.00"): amount= float(tot_fixedasset) + \ float(tot_investment) + \ float(tot_loansasset) + (float(tot_currentasset) - float(tot_currlia)) RightList.append(["TOTAL FUNDS AVAILABLE / CAPITAL EMPLOYED","","","",'%.2f'%float(amount)]) else: amount= float(tot_fixedasset) + \ float(tot_investment) + \ float(tot_loansasset) + (float(tot_currentasset) - float(tot_currlia)) RightList.append(["TOTAL FUNDS AVAILABLE / CAPITAL EMPLOYED","","","",'%.2f'%float(amount)]) finallist.append(RightList) if (difamount != "0.00"): flag = difamount else: flag="" finallist.append([flag]) return finallist
- def xmlrpc_getProfitLossReport(self,queryParams,client_id):
- gets profit and loss as on the given date.
- Returns a grid of 4 columns and number of rows depending on number of accounts.
- This function returns a grid of 4 columns contaning profit and loss details.
- Number of rows in this grid will depend on the number of accounts in the database.
- For profit and loss the accounts from group direct income and indirect income and expence are invoke.
- The function first makes a call to the``getAccountsByGroupCode`` and stores the list.
- then a loop runs through the list of accounts. on every iteration it calls the calculateBalance and passes the account as a parameter along with the financial start, Calculate_from and calculate_to.
- Note: profit and loss is always calculated from the starting of the financial year.
- the total of each group of accounts is calculated separately for calculation purpose.
- input: [org_financial_from,from_date,to_date]
- output: List of list [serial no,groupcode,accountname,amount,balancetype]
def xmlrpc_getProfitLossReport(self,queryParams,client_id): grpCodes = [4,5,7,8] profitloss = [] srno = 1 total_dirInc_balances = 0.00; total_dirExp_balances =0.00 total_indirInc_balances =0.00; total_indirExp_balances = 0.00 grossProfit = 0.00 ; grossLoss = 0.00 netProfit = 0.00 ; netLoss = 0.00 account = rpc_account.account() for grpCode in grpCodes: accounts = account.xmlrpc_getAccountNamesByGroupCode([grpCode],client_id) if accounts != []: for acc in accounts: profitlossrow = [] closingRow = self.xmlrpc_calculateBalance(\ [acc,queryParams[0],queryParams[1],queryParams[2]],client_id) print "closingRow" print closingRow profitlossrow.append(srno) profitlossrow.append(grpCode) profitlossrow.append(acc) profitlossrow.append('%.2f'%(float(closingRow[2]))) profitlossrow.append(str(closingRow[6])) srno = srno + 1 profitloss.append(profitlossrow) # Direct Expense if grpCode == 5: if str(closingRow[6]) == "Dr": total_dirExp_balances = total_dirExp_balances + float(closingRow[2]) else: total_dirInc_balances = total_dirInc_balances + float(closingRow[2]) # Indirect Expense if grpCode == 8: if str(closingRow[6]) == "Dr": total_indirExp_balances = total_indirExp_balances + float(closingRow[2]) else: total_indirInc_balances = total_indirInc_balances + float(closingRow[2]) # Direct Income if grpCode == 4: if str(closingRow[6]) == "Cr": total_dirInc_balances = total_dirInc_balances + float(closingRow[2]) else: total_dirExp_balances = total_dirExp_balances + float(closingRow[2]) # Indirect Income if grpCode == 7: if str(closingRow[6]) == "Cr": total_indirInc_balances = total_indirInc_balances + float(closingRow[2]) else: total_indirExp_balances = total_indirExp_balances + float(closingRow[2]) profitloss.append('%.2f'%(float(total_dirInc_balances))) profitloss.append('%.2f'%(float(total_dirExp_balances))) profitloss.append('%.2f'%(float(total_indirInc_balances))) profitloss.append('%.2f'%(float(total_indirExp_balances))) if (total_dirInc_balances > total_dirExp_balances): grossProfit = total_dirInc_balances - total_dirExp_balances profitloss.append("grossProfit") # gross profit C/O profitloss.append('%.2f'%(float(grossProfit))) # add gross profit C/o to Total amount of II totalnetprofit = total_indirInc_balances + grossProfit # Total Net Profit Check with the Total amount of IE if(totalnetprofit > total_indirExp_balances): netProfit = totalnetprofit - total_indirExp_balances grandTotal = netProfit+total_indirExp_balances profitloss.append("netProfit") profitloss.append('%.2f'%(float(netProfit))) # profitloss.append('%.2f'%(float(totalnetprofit))) profitloss.append('%.2f'%(float(grandTotal))) # final total amount print "grossprofitGrid" print profitloss else: netLoss = total_indirExp_balances - totalnetprofit grandTotal = netLoss + totalnetprofit profitloss.append("netLoss") profitloss.append('%.2f'%(float(netLoss))) profitloss.append('%.2f'%(float(total_indirExp_balances))) profitloss.append('%.2f'%(float(grandTotal))) print "grossLossGrid" print profitloss else: grossLoss = total_dirExp_balances - total_dirInc_balances profitloss.append("grossLoss") profitloss.append('%.2f'%(float(grossLoss))) totalnetloss = total_indirExp_balances + grossLoss if(totalnetloss > total_indirInc_balances): netLoss = totalnetloss - total_indirInc_balances grandTotal = netLoss+totalnetloss profitloss.append("netLoss") profitloss.append('%.2f'%(float(netLoss))) profitloss.append('%.2f'%(float(totalnetloss))) profitloss.append('%.2f'%(float(grandTotal))) else: netProfit = total_indirInc_balances - totalnetloss grandTotal = netProfit+total_indirInc_balances profitloss.append("netProfit") profitloss.append('%.2f'%(float(netProfit))) profitloss.append('%.2f'%(float(total_indirInc_balances))) profitloss.append('%.2f'%(float(grandTotal))) return profitloss
- def xmlrpc_getProfitLossDisplay(self,queryParams,client_id):
- it takes the return grid of getProfitLossReport
- this function is just for the arrangement of grid for display
- input:[org_financial_from,from_date,to_date,reportflag,orgtype]
def xmlrpc_getProfitLossDisplay(self,queryParams,client_id): orgtype=queryParams[4] trialdata = self.xmlrpc_getProfitLossReport(queryParams,client_id) print "trialdata" print trialdata length = len(trialdata) - int(10) grandTotal =trialdata[len(trialdata) - int(1)] print "grandToatal" print grandTotal netTotal = trialdata[len(trialdata) - int(2)] dirincm = trialdata[len(trialdata) - int(10)] direxp = trialdata[len(trialdata) - int(9)] indirincm = trialdata[len(trialdata) - int(8)] indirexp = trialdata[len(trialdata) - int(7)] grossFlag = trialdata[len(trialdata) - int(6)] grossProfitloss = trialdata[len(trialdata) - int(5)] print "grossProfitloss" print grossProfitloss netFlag = trialdata[len(trialdata) - int(4)] netProfitloss = trialdata[len(trialdata) - int(3)] print "trialdata" print trialdata finalList =[] DEList=[] DIList=[] DEcount = 0 DIcount = 0 IEcount = 0 IIcount = 0 if queryParams[3] == "balancesheet": return [netFlag, netProfitloss] else: ############### Direct Expense Accounts ################ DEList.append(["","Particulars","Amount"]) DEList.append(["","Direct Expense",""]) for i in range (0, length): # groupcode 5 is Direct Expense if (trialdata[i][1] == 5): if (trialdata[i][4] == "Dr"): if (trialdata[i][3]!="0.00"): DEList.append(["To,",trialdata[i][2],trialdata[i][3]]) DEcount = DEcount+1 # groupcode 4 is Direct Income if (trialdata[i][1] == 4): if (trialdata[i][4] == "Dr"): if (trialdata[i][3]!="0.00"): DEList.append(["To,",trialdata[i][2],trialdata[i][3]]) DEcount = DEcount + 1 if (grossFlag == "grossProfit"): if (orgtype == "Profit Making"): flag = "Gross Profit C/O" if (orgtype == "NGO"): flag ="Gross Surplus C/O" if (grossProfitloss != "0.00"): DEcount = DEcount+1 DEList.append(["To,",flag,grossProfitloss]) ################### Direct Income Accounts ############## DIList.append(["","Particulars","Amount"]) DIList.append(["","Direct Income",""]) for i in range (0, length): # groupcode 4 is Direct Income if (trialdata[i][1] == 4): if (trialdata[i][4] == "Cr"): if (trialdata[i][3]!="0.00"): DIList.append(["By,",trialdata[i][2],trialdata[i][3]]) DIcount = DIcount+1 # groupcode 5 is Direct Expense if (trialdata[i][1] == 5): if (trialdata[i][4] == "Cr"): if (trialdata[i][3]!="0.00"): DIList.append(["By,",trialdata[i][2],trialdata[i][3]]) DIcount = DIcount+1 if (grossFlag == "grossLoss"): if (orgtype == "Profit Making"): flag="Gross Loss C/O" if (orgtype == "NGO"): flag="Gross Deficit C/O" if (grossProfitloss != "0.00"): DIcount = DIcount+1 DIList.append(["By,",flag,grossProfitloss]) ############### for empty coloum in Direct Expense ######## if (DIcount > DEcount): diff = DIcount - DEcount for i in range(0,diff): DEList.append(["","",""]) ############### Total amount for direct expense ########## if (grossFlag == "grossProfit"): amount = dirincm if (grossFlag == "grossLoss"): amount= direxp DEList.append(["","Total",amount]) ####################### Indirect Expense Accounts ##################### DEList.append(["","",""]) DEList.append(["","Indirect Expense",""]) if (grossFlag == "grossLoss"): if (orgtype == "Profit Making"): flag ="Gross Loss B/F" if (orgtype == "NGO"): flag ="Gross Deficit B/F" if (grossProfitloss != "0.00"): IEcount = IEcount+1 DEList.append(["To,",flag,grossProfitloss]) for i in range (0, length): # groupcode 8 for Indirect Expense if (trialdata[i][1] == 8): if (trialdata[i][4] == "Dr"): if (trialdata[i][3]!="0.00"): DEList.append(["To,",trialdata[i][2],trialdata[i][3]]) IEcount = IEcount+1 # groupcode 7 for Indirect Income if (trialdata[i][1] == 7): if (trialdata[i][4] == "Dr"): if (trialdata[i][3]!="0.00"): DEList.append(["To,",trialdata[i][2],trialdata[i][3]]) IEcount = IEcount+1 if (grossFlag == "grossProfit" and netFlag == "netProfit"): if (orgtype == "Profit Making"): flag = "Net Profit" if (orgtype == "NGO"): flag = "Net Surplus" if (netProfitloss != "0.00"): IEcount = IEcount+1 DEList.append(["To,",flag,netProfitloss]) if (grossFlag == "grossLoss" and netFlag == "netProfit"): if (orgtype == "Profit Making"): flag = "Net Profit" if (orgtype == "NGO"): flag = "Net Surplus" if (netProfitloss != "0.00"): IEcount = IEcount+1 DEList.append(["To,",flag,netProfitloss]) ############## for empty coloum in Indirect Expense ###### if (IIcount > IEcount): diff = IIcount - IEcount for i in range(0,diff): DEList.append(["","",""]) ############# Total of IE amount ################## if (netFlag == "netLoss"): amount=netTotal if (netFlag == "netProfit"): amount=grandTotal DEList.append(["","Total",amount]) finalList.append(DEList) # Final list for entire first coloumn DE + IE ############### for empty coloum in Direct Income ###### if (DEcount > DIcount): diff = DEcount - DIcount for i in range(0,diff): DIList.append(["","",""]) #################### Total of DI ammount ############### if (grossFlag == "grossProfit"): amount= dirincm if (grossFlag == "grossLoss"): amount= direxp DIList.append(["","Total",amount]) ################### Indirect Income Accounts ########### DIList.append(["","",""]) DIList.append(["","Indirect Income",""]) if (grossFlag == "grossProfit"): if (orgtype == "Profit Making"): flag = "Gross Profit B/F" if (orgtype == "NGO"): flag = "Gross Surplus B/F" if (grossProfitloss != "0.00"): IIcount = IIcount+1 DIList.append(["By,",flag,grossProfitloss]) for i in range (0, length): # groupcode 7 is for Indirect Income if (trialdata[i][1] == 7): if (trialdata[i][4] == "Cr"): if (trialdata[i][3]!="0.00"): DIList.append(["By,",trialdata[i][2],trialdata[i][3]]) IIcount = IIcount+1 # groupcode 8 is for Indirect Expense if (trialdata[i][1] == 8): if (trialdata[i][4] == "Cr"): if (trialdata[i][3]!="0.00"): DIList.append(["By,",trialdata[i][2],trialdata[i][3]]) IIcount = IIcount+1 if (grossFlag == "grossProfit" and netFlag == "netLoss"): if (orgtype == "Profit Making"): flag = "Net Loss" if (orgtype == "NGO"): flag = "Net Deficit" if (netProfitloss != "0.00"): IIcount = IIcount+1 DIList.append(["By,",flag,netProfitloss]) if (grossFlag == "grossLoss" and netFlag == "netLoss"): if (orgtype == "Profit Making"): flag = "Net Loss" if (orgtype == "NGO"): flag = "Net Deficit" if (netProfitloss != "0.00"): IIcount = IIcount+1 DIList.append(["By,",flag,netProfitloss]) ############## for empty coloum in Indirect Income ############# if (IEcount > IIcount): diff = int(IEcount - IIcount) for i in range(0,diff): DIList.append(["","",""]) ################ Total of II amount ################### if (netFlag == "netLoss"): amount= netTotal if (netFlag == "netProfit"): amount= grandTotal DIList.append(["","Total",amount]) finalList.append(DIList) # Final list of 2nd coloumn DI +II return finalList
- def xmlrpc_getReconLedger(self,queryParams,client_id):
- to get a complete ledger for given bank account.
- information taken from view_voucherbook
- for getting ledger it takes the result of rpc_getLedger.
- it expects a list of queryParams which contains
- input: [accountname,financialStart,fromdate,todate,projectname]
- Returns a grid (2 dimentional list ) with columns as Date, Particulars, Reference number, Dr amount, Cr amount, narration, Clearance Date and Memo.
- Note that It will display the value of clearance date and memo for only those transactions which are cleared.
- The last row will just contain the grand total which will be equal at credit and debit side.
- 2nd last row contains the closing balance.
- 3rd last row contains just the total Dr and total Cr.
- if the closing balance (carried forward ) is debit then it will be shown at credit side and if it is credit will be shown at debit side.
def xmlrpc_getReconLedger(self,queryParams,client_id): # create the instance of transaction transaction = rpc_transaction.transaction() #first let's get the details of the given account regarding the #Balance and its Dr/Cr side by calling getLedger function. #note that we use the getClearanceDate function which gives us #the clearance date and memo for each account in the ledger. print "params========" print queryParams ledgerResult = self.xmlrpc_getLedger(queryParams,client_id) reconResult =[] #lets declare voucheLcounter to zero voucheLcounter = 0 vouchercodeRecords= transaction.xmlrpc_getTransactions([\ queryParams[0],queryParams[2],queryParams[3],\ queryParams[4]],client_id) # following delete operations are done for avoiding clearance date #and memo in opening balance, totaldr, totalcr and grand total rows. if ledgerResult[0][1] == "Opening Balance b/f": del ledgerResult[0] #opening balance row del ledgerResult[len(ledgerResult)-1] #grand total row del ledgerResult[len(ledgerResult)-1] #closing balance row del ledgerResult[len(ledgerResult)-1] #total Dr and Cr row del ledgerResult[len(ledgerResult)-1] # empty row voucherCodes = [] for vc in vouchercodeRecords: voucherCodes.append(int(vc[0])) for ledgerRow in ledgerResult: if len(str(ledgerRow[0])) == 10: print "yes" reconRow = [] reconRow.append(voucherCodes[voucheLcounter]) #voucher code reconRow.append(ledgerRow[0]) #voucher date reconRow.append(str(ledgerRow[1])) #particular reconRow.append(ledgerRow[2]) #ref no reconRow.append(ledgerRow[3]) #Dr amount reconRow.append(ledgerRow[4]) #Cr amount reconRow.append(ledgerRow[5]) #narration clearanceDates =self.xmlrpc_getClearanceDate([\ str(ledgerRow[1]),voucherCodes[voucheLcounter]],client_id) if clearanceDates == []: reconRow.append("") reconRow.append("") else: for datesRow in clearanceDates: clrdate = str(datesRow.clearancedate).split(" ") clrDate = datetime.strptime(clrdate[0],"%Y-%m-%d").strftime("%d-%m-%Y") clrMemo = datesRow.memo reconRow.append(clrDate) reconRow.append(clrMemo) voucheLcounter = voucheLcounter + 1 reconResult.append(reconRow) return reconResult
- def xmlrpc_setBankReconciliation(self,queryParams,client_id):
- it sets the bankrecon table in database as saves transaction details of those transactions which are cleared with clearance date and memo in table bankrecon
- Also sets the reconcode(reconciliation code) for the respective transaction.
- input: It expects a list of queryParams which contains [vouchercode(datatype:integer),reffdate(datatype:timestamp), accountname(datatype:varchar),dramount(datatype:numeric), cramount(datatype:numeric),clearancedate(datatype:timestamp), memo(datatype:text)]
def xmlrpc_setBankReconciliation(self,queryParams,client_id): # lets create a list containing vouchercode,reffdate,accountname. for clearRow in queryParams: sp_params = [clearRow[0],clearRow[1],clearRow[2]] #if dr_amount is blank, append 0 as dr_amount and respective cr_amount. if clearRow[3] == "": sp_params.append(0) sp_params.append(clearRow[4]) #if cr_amount is blank, append 0 as cr_amount and respective dr_amount. if clearRow[4] == "": sp_params.append(clearRow[3]) sp_params.append(0) #Now, lets append respective clearance date and memo sp_params.append(clearRow[5]) sp_params.append(clearRow[6]) #Finally we are ready to set the bankrecon table. success = self.xmlrpc_setBankRecon(sp_params,client_id) return success
- def xmlrpc_setBankRecon(self,queryParams,client_id):
- to set the cleared transaction
- input: [vouchercode,reffdate,accountname,dramount,cramount,clearencedate,memo]
- output : String success
def xmlrpc_setBankRecon(self,queryParams,client_id): queryParams = blankspace.remove_whitespaces(queryParams) reffdate = datetime.strptime(str(queryParams[1]),"%d-%m-%Y") clearencedate = datetime.strptime(str(queryParams[5]),"%d-%m-%Y") connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) reconcode = Session.query(func.max(dbconnect.BankRecon.reconcode)).scalar() if reconcode == None: reconcode = 0 reconcode = reconcode + 1 else: reconcode = reconcode + 1 result = Session.query(dbconnect.BankRecon).\ filter(and_(dbconnect.BankRecon.accountname == queryParams[2],\ dbconnect.BankRecon.vouchercode == queryParams[0])).\ first() if result == None: if queryParams[3] == 0: # add all values in the bankrecon table Session.add(dbconnect.BankRecon(reconcode,queryParams[0],reffdate,queryParams[2],\ 0,queryParams[4],clearencedate,queryParams[6])) Session.commit() else: # add all values in the bankrecon table Session.add(dbconnect.BankRecon(reconcode,queryParams[0],reffdate,queryParams[2],\ queryParams[3],0,clearencedate,queryParams[6])) Session.commit() else: Session.query(dbconnect.BankRecon).\ filter(and_(dbconnect.BankRecon.accountname == queryParams[2],\ dbconnect.BankRecon.vouchercode == queryParams[0])).\ delete() Session.commit() if queryParams[3] == 0: # add all values in the bankrecon table Session.add(dbconnect.BankRecon(reconcode,queryParams[0],reffdate,queryParams[2],\ 0,queryParams[4],clearencedate,queryParams[6])) Session.commit() else: # add all values in the bankrecon table Session.add(dbconnect.BankRecon(reconcode,queryParams[0],reffdate,queryParams[2],\ queryParams[3],0,clearencedate,queryParams[6])) Session.commit() Session.close() connection.connection.close() return "success"
- def xmlrpc_getClearanceDate(self,queryParams,client_id):
- to get the Clearancedate of cleared transaction
- input: [accountname ,vouchercode]
- output: [clearance date , memo]
def xmlrpc_getClearanceDate(self,queryParams,client_id): connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.BankRecon).\ filter(and_(dbconnect.BankRecon.accountname==queryParams[0],\ dbconnect.BankRecon.vouchercode==queryParams[1])).\ all() return result
- def xmlrpc_deleteClearedRecon(self,queryParams,client_id):
- to uncleared the cleared trasaction and delete cleared entry from bankrecon table
- input: [accountname,vouchercode,todate]
def xmlrpc_deleteClearedRecon(self,queryParams,client_id): clearencedate = str(datetime.strptime(str(queryParams[2]),"%d-%m-%Y")) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.BankRecon).\ filter(and_(dbconnect.BankRecon.accountname==queryParams[0],\ dbconnect.BankRecon.vouchercode==queryParams[1],\ dbconnect.BankRecon.clearancedate < clearencedate)).delete() Session.commit() Session.close() connection.connection.close() if result == True: return True else: return Falsedef xmlrpc_updateBankRecon(self,queryParams, flags, client_id):
returns all uncleared transactions from the starting of financial year to the end date of given period OR
all uncleared transactions from the starting of financial year to the end date of given period with all cleared transactions of the given period if cleared_tran_flag is true with Bank Reconciliation Statement for the given period of time.
- input:
- list 1: [account name, financial start, fromdate and todate,projectname]
- list 2: [cleared_tran_flag]
this function returns a grid of 9 columns and number of rows depending on number of uncleared OR uncleared+cleared transactions in the database.
After appending transactions in grid, it appends Bank Reconciliation statement.
- A grid of 9 columns contains:
- [vouchercode, transaction date, accountname, reference number,dramount, cramount, narration, clearance date and memo]
The function first makes a call to the previous function getLedger and passes the account as a parameter along with the financial start, Calculate_from and calculate_to.
note that balance is always calculated from the starting of the financial year.
- Then, on every iteration it calls following functions
- getTransactions: to get trnsactions from starting date of
financial year to the end date of given period
- getParticulars: to get all particulars(account names)
for that period
- getOnlyClearedTransactions: to filter out all uncleared
transactions and their details.
if cleared_tran_flag is True, it calls getReconLedger function to get the list of cleared transactionsand then it compares ReconGrid with list of cleared transactions to ignore duplicate transactions and finally added bank reconciliation statement.
def xmlrpc_updateBankRecon(self,queryParams, flags, client_id): cleared_tran_flag = flags[0] ReconGrid = [] totalDbt = 0.00 totalCdt = 0.00 # create the instance of transaction transaction = rpc_transaction.transaction() #now lets get the transaction details for this account. transactions =transaction.xmlrpc_getTransactions([\ queryParams[0],queryParams[1],queryParams[3],queryParams[4]],client_id) # [vouchercode , voucherflag , reff_date , voucher_reference,transaction_amount,show_narration] # fill up the grid with the rows for transactions. for transactionRow in transactions: # if the transaction had the amount at Dr side then particulars # must have the names of accounts involved in Cr. if transactionRow[1] == "Dr": particulars = transaction.xmlrpc_getParticulars([transactionRow[0],"Cr"],client_id) # [voucher_code,type_flag] ledgerRow = [] #may be more than one account was involved at the other side so loop through. if len(particulars) == 1: for particularRow in particulars: cleared =transaction.xmlrpc_getOnlyClearedTransactions([\ str(particularRow),int(transactionRow[0]),\ queryParams[1],queryParams[3]],client_id) if cleared == False: reff_date = str(transactionRow[2]).split(" ") reff_date= datetime.strptime(str(reff_date[0]),"%Y-%m-%d").strftime("%d-%m-%Y") ledgerRow.append(transactionRow[0]) ledgerRow.append(reff_date) ledgerRow.append(particularRow) ledgerRow.append(transactionRow[3]) ledgerRow.append('%.2f'%(float(transactionRow[4]))) totalDbt = totalDbt + float(transactionRow[4]) ledgerRow.append("") ledgerRow.append(transactionRow[5]) ReconGrid.append(ledgerRow) if transactionRow[1] == "Cr": particulars = transaction.xmlrpc_getParticulars([transactionRow[0],"Dr"],client_id) # [voucher_code,type_flag] ledgerRow = [] #may be more than one account was involved a tthe other side so loop through. if len(particulars) == 1: for particularRow in particulars: cleared =transaction.xmlrpc_getOnlyClearedTransactions(\ [str(particularRow),int(transactionRow[0]),\ queryParams[1],queryParams[3]],client_id) if cleared == False: reff_date = str(transactionRow[2]).split(" ") reff_date= datetime.strptime(str(reff_date[0]),"%Y-%m-%d").strftime("%d-%m-%Y") ledgerRow.append(transactionRow[0]) ledgerRow.append(reff_date) ledgerRow.append(particularRow) ledgerRow.append(transactionRow[3]) ledgerRow.append("") ledgerRow.append('%.2f'%(float(transactionRow[4]))) ledgerRow.append(transactionRow[5]) totalCdt = totalCdt + float(transactionRow[4]) ReconGrid.append(ledgerRow) for row in ReconGrid: row.append("") #clearance date row.append("") #memo # if cleared transaction flag is true then, if cleared_tran_flag == True: ReconLedger = self.xmlrpc_getReconLedger(queryParams,client_id) voucher_list = [] for v_code in ReconLedger: voucher_list.append(v_code[0]) for row in ReconGrid: if row[0] not in voucher_list : #row[0] is v_code ReconLedger.append(row) #arrange rows order by date ReconGrid = ReconLedger ReconGrid = sorted(ReconGrid,key=lambda x: datetime.strptime(str(x[1]),"%d-%m-%Y")) # lets add recon statement ReconGrid.append(["","","","Total",'%.2f'%(totalDbt),'%.2f'%(totalCdt),"","",""]) #lets start making Reconcilition Statement, ReconGrid.append(["","RECONCILIATION STATEMENT","","","","AMOUNT"]) #get the ledger Grid result, ledgerResult = self.xmlrpc_getLedger(queryParams,client_id) BankBal = 0.00 closingBal = 0.00 midTotal = 0.00 #lets get the closing row for closing balance closingBalRow = ledgerResult[len(ledgerResult)-2] #total of Dr and Cr TotalDrCrRow = ledgerResult[len(ledgerResult)-4] # if opening balance is debit then add opening balance to # total debit amount else to total credit amount if ledgerResult[0][2] =="": openingBalRow = ledgerResult[0] if openingBalRow[3] != "": TotalDrCrRow[3] = float(TotalDrCrRow[3]) + float(openingBalRow[3]) else: TotalDrCrRow[4] = float(TotalDrCrRow[4]) + float(openingBalRow[4]) balancedate = str(queryParams[2]) ClosingBalance = float(TotalDrCrRow[3]) - float(TotalDrCrRow[4]) if ClosingBalance == 0: ReconGrid.append([balancedate,"Balance as per our book on "+balancedate,"","","",closingBalRow[3]]) closingBal = float(closingBalRow[3]) else: if closingBalRow[3] != "": ReconGrid.append([balancedate,"Balance as per our book (Credit) on "+balancedate,"","","",closingBalRow[3]]) closingBal = float(closingBalRow[3]) if closingBalRow[4] != "": ReconGrid.append([balancedate,"Balance as per our book (Debit) on "+balancedate,"","","",closingBalRow[4]]) closingBal = float(closingBalRow[4]) if ClosingBalance >= 0: if totalCdt != 0: ReconGrid.append(["","Add: Cheques issued but not presented","","","","+ "+'%.2f'%(totalCdt)]) else: ReconGrid.append(["","Add: Cheques issued but not presented","","","",'%.2f'%(totalCdt)]) midTotal = closingBal + totalCdt ReconGrid.append(["","","","","",""+'%.2f'%(midTotal)]) if totalDbt != 0: ReconGrid.append(["","Less: Cheques deposited but not cleared","","","","- "+'%.2f'%(totalDbt)]) else: ReconGrid.append(["","Less: Cheques deposited but not cleared","","","",'%.2f'%(totalDbt)]) BankBal = midTotal - totalDbt if ClosingBalance < 0: if totalCdt != 0: ReconGrid.append(["","Add: Cheques issued but not presented","","","","+ "+'%.2f'%(totalCdt)]) else: ReconGrid.append(["","Add: Cheques issued but not presented","","","",'%.2f'%(totalCdt)]) midTotal = totalCdt - closingBal ReconGrid.append(["","","","","",""+'%.2f'%(abs(midTotal))]) if totalDbt != 0: ReconGrid.append(["","Less: Cheques deposited but not cleared","","","","- "+'%.2f'%(totalDbt)]) else: ReconGrid.append(["","Less: Cheques deposited but not cleared","","","",'%.2f'%(totalDbt)]) BankBal = midTotal - totalDbt if BankBal < 0: ReconGrid.append(["","Balance as per Bank (Debit)","","","",'%.2f'%(abs(BankBal))]) if BankBal > 0: ReconGrid.append(["","Balance as per Bank (Credit)","","","",'%.2f'%(abs(BankBal))]) if BankBal == 0: ReconGrid.append(["","Balance as per Bank","","","",'%.2f'%(abs(BankBal))]) return ReconGriddef xmlrpc_getCashFlow(self,queryParams,client_id):
- The function will return a grid with 4 columns.
- first 2 columns will have the account name and its sum of received amount, while next 2 columns will have the same
- for amount paid.first we make a call to get CashFlowAccounts
- for the list of accounts falling under Bank or Cash subgroups.
- Then a loop will run through the list and get the list of payment and receipts as mentioned above.
- every row will contain a pair of as below account:amount for payment and receipt each.
- input: financial_from ,start_date and end_date
def xmlrpc_getCashFlow(self,queryParams,client_id): # declare the cashFlowGrid, rlist, plist as a blank list. # we will fill up cashFlowGrid by appending rlist and plist. # rlist will contain the cashflow of received accounts. # plist will contain the cashflow of paid accounts. cashFlowGrid = [] rlist = [] plist = [] account = rpc_account.account() getjournal = rpc_getaccountsbyrule.getaccountsbyrule() rlist.append(["Account Name","Amount(Inflow)"]) #rlist.append(["Opening Balance",""]) #Let's start with 0 for totalreceivedamount and totalpaid amounts. totalreceivedamount = 0.00 totalpaidamount = 0.00 #first let's get the list of all accounts coming under cash or #bank subgroup and their respective opening balance. cashBankAccounts=account.xmlrpc_getCashFlowOpening(client_id) #fill up the rlist with the rows for cashFlowAccounts. #also maintaining a list of cash and bank accounts will facilitate #the loop for getting actual cash flow. cbAccounts = [] for acc in cashBankAccounts: openingRow = [] #openingRow.append("ob") openingRow.append(acc[0])# accountname for opening balance cbAccounts.append(acc[0])# accountname for closing balance cfAccountsRows = getjournal.xmlrpc_getJournalAccounts(client_id) # now we will run a nested loop for getting cash flow for all non-cash/bank accounts # the outer loop will run through the list of all the cfAccounts # and check for any transactions on them involving bank or # cash based accounts for which we have a list of cbAccounts # needless to say this process will happen once for recieved and one for paid transactions. for acc in cfAccountsRows: receivedAmount = 0.00 for cb in cbAccounts: # check how much amount Debit in Cash & bank Accounts(received) receivedRow = account.xmlrpc_getCashFlowReceivedAccounts([\ str(acc),str(cb),queryParams[1],queryParams[2]],client_id) if receivedRow != None: receivedAmount = receivedAmount + float(str(receivedRow[0])) if receivedAmount != 0: rlist.append([acc,'%.2f'% receivedAmount]) totalreceivedamount = totalreceivedamount + float(receivedAmount) plist.append(["Account name","Amount(Outflow)"]) for acc in cfAccountsRows: paidAmount = 0.00 for cb in cbAccounts: # check how much amount Credit in Cash & bank Accounts(Paid) paidRow =account.xmlrpc_getCashFlowPaidAccounts([\ str(acc),str(cb),queryParams[1],queryParams[2]],client_id) if paidRow!= None: paidAmount = paidAmount + float(str(paidRow[0])) if paidAmount != 0: plist.append([acc,'%.2f'% paidAmount]) totalpaidamount = totalpaidamount + float(paidAmount) # fill up the plist with the rows for cashFlowAccounts only if paidRow is not none. # now sum up the totalpaid amounts. # Now lets equate the row of rlist and plist. rlength = len(rlist) plength = len(plist) # if length of rlist is greater than plist then append the blank lists # times of difference in rlist and plist into the plist or vice versa. if rlength > plength: diflength = rlength - plength for d in range(0,diflength): plist.append(["",""]) if rlength < plength: diflength = plength - rlength for d in range(0,diflength): rlist.append(["",""]) #now append the total receivedamount and total paidamount in respective lists i.e. rlist and plist rlist.append(["Total",'%.2f'% totalreceivedamount]) plist.append(["Total",'%.2f'% totalpaidamount]) Netlist =[] if totalreceivedamount > totalpaidamount: diflength = totalreceivedamount - totalpaidamount Netlist.append('%.2f'%diflength) else: diflength = totalpaidamount - totalreceivedamount Netlist.append("(-)"+'%.2f'%diflength) #now append rlist and plist to cashFlowGrid cashFlowGrid.append(rlist) cashFlowGrid.append(plist) cashFlowGrid.append(Netlist) return cashFlowGrid