def xmlrpc_deleteVoucher(self,queryParams,client_id):
try:
dbconnect.execproc("deleteVoucher", dbconnect.engines[client_id],queryParams)
return True
except:
return False
print queryParams
def xmlrpc_getVoucherMaster(self, queryParams, client_id):
"""
purpose: returns a record from the voucher master containing single row data for a given transaction.
Returns list containing data from voucher_master.
description:
This function is used along with rpc_ getVoucherDetails to search a complete voucher.
Useful while editing or cloning.
The function takes one parameter which is a list containing vouchercode.
The function makes use of the getvoucherMaster stored procedure and returns a list containing,
* referencenumber
*reffdate
* vouchertype
* project name
* and voucherNarration.
"""
voucherMasterResult = dbconnect.execproc("getVoucherMaster",
dbconnect.engines[client_id],
queryParams)
voucherRow = voucherMasterResult.fetchone()
project = dbconnect.execproc("getProjectNameByCode",
dbconnect.engines[client_id],
[int(voucherRow["project_code"])])
projectRow = project.fetchone()
projectName = projectRow["project_name"]
if projectName == None:
projectName = "No Project"
voucherMaster = [
voucherRow["voucher_reference"],
voucherRow["reff_date"].strftime("%d-%m-%Y"),
voucherRow["voucher_type"], voucherRow["voucher_narration"],
projectName
]
print queryParams
return voucherMaster
def xmlrpc_deleteAccount(self, queryParams, client_id):
'''
Purpose : Function for deleting accounts. For this we have used hasOpeningBalance,
hasTransactions & deleteAccount stored procedures. With the help of hasTransactions
we are able to find out whether the given account has any transactions or not. The stored procedure tells that if there is any voucher entry of that account name return true or else return false
The second stored procedure hasOpeningBalance returns true if opening balance for that account exists or else returns false
The third stored procedure deleteAccount deletes that particular accountname
'''
hasOpeningBalance = dbconnect.execproc("hasOpeningBalance",
dbconnect.engines[client_id],
[str(queryParams[0])])
hasOpeningBalanceRow = hasOpeningBalance.fetchone()
print hasOpeningBalanceRow["success"]
hasTransactions = dbconnect.execproc("hasTransactions",
dbconnect.engines[client_id],
[str(queryParams[0])])
hasTransactionsRow = hasTransactions.fetchone()
print hasTransactionsRow["success"]
if hasOpeningBalanceRow["success"] == False and hasTransactionsRow[
"success"] == False:
try:
dbconnect.execproc("deleteAccount",
dbconnect.engines[client_id],
[str(queryParams[0])])
return True
except:
return False
else:
return False
def xmlrpc_getGrossTrialBalance(self,queryParams,client_id):
'''
purpose:
just like the getTrialBalance, this function too returns list of balances of all accounts.
However it has a difference in that it provides the total Dr and total Cr for all accounts, instead of the difference.
description:
Similar to the getTrial balance function this one returns a grid, but instead of current balance, it returns total Dr and total Cr in the grid.
This function too uses the calculateBalance stored procedure after getting list of accounts.
'''
accounts = dbconnect.execproc("getAllAccounts", dbconnect.engines[client_id],[])
trialBalance = []
srno =1
total_dr = 0.00
total_cr = 0.00
for account in accounts:
closingBalance = dbconnect.execproc("calculateBalance", dbconnect.engines[client_id], [str(account[0]),queryParams[0],queryParams[1],queryParams[2]])
closingRow = closingBalance.fetchone()
if float(closingRow["total_DrBal"]) != 0 or float(closingRow["total_CrBal"]) != 0:
trialRow = []
trialRow.append(srno)
trialRow.append(account["accountname"])
trialRow.append(closingRow["group_name"])
trialRow.append('%.2f'%float(closingRow["total_DrBal"]))
trialRow.append('%.2f'%float(closingRow["total_CrBal"]))
total_dr = total_dr + float(closingRow["total_DrBal"])
total_cr = total_cr + float(closingRow["total_CrBal"])
srno = srno +1
trialBalance.append(trialRow)
total_balances = ['%.2f'%total_dr,'%.2f'%total_cr]
trialBalance.append(total_balances)
return trialBalance
def xmlrpc_getAccount(self, queryParams, client_id):
"""
purpose: Searches and returns account details. Search is based on either accountcode or account name.
function takes one parameter queryParams of type list containing,
*searchFlag as integer (1 means search by account code and 2 means account name )
* searchValue as text (value depends on the searchFlag)
description:
This function queries the group_subgroup_account view and fetches the following.
*groupname
* subgroupname (if any )
* accountcode
* accountname
* openingbalance
The function makes a call to stored procedure named getAccount.
Refer to rpc_main -> rpc_deploy function for the complete spec of the stored procedure.
"""
result = dbconnect.execproc("getAccount", dbconnect.engines[client_id], queryParams)
row = result.fetchone()
hasTransactionResult = dbconnect.execproc("hasTransactions", dbconnect.engines[client_id], [str(row[3])])
hasTransactionRow = hasTransactionResult.fetchone()
hasTransactionFlag = hasTransactionRow["success"]
hasOpeningBalanceResult = dbconnect.execproc("hasOpeningBalance", dbconnect.engines[client_id], [str(row[3])])
hasOpeningBalanceRow = hasOpeningBalanceResult.fetchone()
hasOpeningBalanceFlag = hasOpeningBalanceRow["success"]
if row[1] == None:
return list([row[2], row[0], 0.00, row[3], row[4],hasTransactionFlag, hasOpeningBalanceFlag])
else:
return list([row[2], row[0], row[1], row[3], row[4],hasTransactionFlag, hasOpeningBalanceFlag ])
def xmlrpc_getVoucherMaster(self, queryParams,client_id):
"""
purpose: returns a record from the voucher master containing single row data for a given transaction.
Returns list containing data from voucher_master.
description:
This function is used along with rpc_ getVoucherDetails to search a complete voucher.
Useful while editing or cloning.
The function takes one parameter which is a list containing vouchercode.
The function makes use of the getvoucherMaster stored procedure and returns a list containing,
* referencenumber
*reffdate
* vouchertype
* project name
* and voucherNarration.
"""
voucherMasterResult = dbconnect.execproc("getVoucherMaster", dbconnect.engines[client_id],queryParams)
voucherRow = voucherMasterResult.fetchone()
project = dbconnect.execproc("getProjectNameByCode",dbconnect.engines[client_id],[int(voucherRow["project_code"])])
projectRow = project.fetchone()
projectName = projectRow["project_name"]
if projectName == None:
projectName = "No Project"
voucherMaster = [voucherRow["voucher_reference"],voucherRow["reff_date"].strftime("%d-%m-%Y"),voucherRow["voucher_type"],voucherRow["voucher_narration"],projectName]
print queryParams
return voucherMaster
def xmlrpc_deleteVoucher(self, queryParams, client_id):
try:
dbconnect.execproc("deleteVoucher", dbconnect.engines[client_id],
queryParams)
return True
except:
return False
print queryParams
def xmlrpc_getReconLedger(self,queryParams,client_id):
'''
Purpose : returns a complete ledger for given bank account. Information taken from view_voucherbook
Parameters : For getting ledger it takes the result of rpc_getLedger. It expects a list of queryParams which contains[accountname(datatype:text),fromdate(datatype:timestamp),todate(datatype:timestamp)]
description:
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.
'''
#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.
ledgerResult = self.xmlrpc_getLedger(queryParams,client_id)
reconResult =[]
#lets declare vouchercounter to zero
voucherCounter = 0
transactions = dbconnect.execproc("getTransactions", dbconnect.engines[client_id],[queryParams[0],queryParams[1],queryParams[2],queryParams[4]])
# following delete operations are done for avoiding clearance date and memo in opening balance, totaldr, totalcr and grand total rows.
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 = []
vouchercodeRecords = transactions.fetchall()
for vc in vouchercodeRecords:
voucherCodes.append(int(vc[0]))
#lets append required rows in new list.
for ledgerRow in ledgerResult:
reconRow = []
reconRow.append(ledgerRow[0]) #voucher date
if (len(ledgerRow[1])==1):
for acc in ledgerRow[1]:
reconRow.append(acc) #particular
reconRow.append(ledgerRow[2]) #ref no
reconRow.append(voucherCodes[voucherCounter]) #voucher code
reconRow.append(ledgerRow[3]) #Dr amount
reconRow.append(ledgerRow[4]) #Cr amount
reconRow.append(ledgerRow[5]) #narration
clearanceDates = dbconnect.execproc("getClearanceDate",dbconnect.engines[client_id],[str(ledgerRow[1][0]),voucherCodes[voucherCounter]])
if clearanceDates.rowcount != 0:
for datesRow in clearanceDates:
clrDate = datesRow["c_date"].strftime("%d-%m-%Y")
clrMemo = datesRow["memo_recon"]
reconRow.append(clrDate)
reconRow.append(clrMemo)
else:
reconRow.append("")
reconRow.append("")
voucherCounter = voucherCounter + 1
reconResult.append(reconRow)
return reconResult
def xmlrpc_setStockQty(self, queryParams, client_id):
'''
Purpose: Function to insert data into stock quantity table which keeps track of quantity of all products.
I/O Parameters: queryParams which contain product key, transactiondate (yyyy-mm-dd format), quantity, bill number and stockflag
Returns: True on successfull insertion of data into table
Description : While setting stock to UP, we set stockflag to 1 and for stock down stockflag is 0
'''
dbconnect.execproc("setStockQty", dbconnect.engines[client_id],queryParams)
return True
def xmlrpc_setStockQty(self, queryParams, client_id):
'''
Purpose: Function to insert data into stock quantity table which keeps track of quantity of all products.
I/O Parameters: queryParams which contain product key, transactiondate (yyyy-mm-dd format), quantity, bill number and stockflag
Returns: True on successfull insertion of data into table
Description : While setting stock to UP, we set stockflag to 1 and for stock down stockflag is 0
'''
dbconnect.execproc("setStockQty", dbconnect.engines[client_id],
queryParams)
return True
def xmlrpc_setTransaction(self, queryParams_master, queryParams_details,
client_id):
"""
Purpose: adds a new voucher in the database given its reference number and transaction details (dr and cr), along with narration and the date.
Purpose:
This function is used to create a new voucher. A voucher code is generated automatically while the user gives optional reference number.
The entire transaction is recorded in terms of Dr and Cr and the respected amounts.
The function utilises 2 stored procedures.
setVoucherMaster and setVoucherDetails.
For voucher master the function takes queryParams_master containing,
* reference Number
*system Date (on which the voucher was entered )
* the actual transaction date
*Voucher type,
*project name,
* Narration,
*purchase order number,
*purchase order date and
*purchase order amount
The setVoucherDetails takes a 2 dimensional list containing,
rows with columns,
* DrCr flag,
AccountName (from which account code will be procured by the said stored procedure)
* the amount for the respective account.
The function returns true if successful or false otherwise.
"""
print queryParams_details
print queryParams_master
prj = self.xmlrpc_getProjectcodeByName([str(queryParams_master[4])],
client_id)
del queryParams_master[4]
queryParams_master.insert(4, prj)
print queryParams_master
success = dbconnect.execproc("setVoucherMaster",
dbconnect.engines[client_id],
queryParams_master)
successRow = success.fetchone()
voucherCode = successRow[0]
print "query for masters is successful and voucher code is " + str(
voucherCode)
for detailRow in queryParams_details:
accountCodeResult = dbconnect.execproc(
"getAccountCode", dbconnect.engines[client_id], [detailRow[1]])
accountCodeRow = accountCodeResult.fetchone()
accountCode = accountCodeRow["account_code"]
dbconnect.execproc("setVoucherDetails",
dbconnect.engines[client_id], [
voucherCode,
str(detailRow[0]),
str(accountCode),
float(detailRow[2])
])
return 1
def xmlrpc_setAccount(self, queryParams, client_id):
"""
Purpose: Adds an account in the account table, under a selected group and optionally a subgroup. Account code is either auto generated or entered by the user
Depending on the preference choosen by the user.
description:
This function inserts a row in the account table.
Function takes one parameter named queryParams which is a list containing,
* groupname as string at position 0
* subgroupflag as string at position 1
* subgroupname (optionally) at position 2
* account name: string at position 3
* accountcodetype: string at position 4
* openingbalance: integer at position 5
* currentBalance: integer at position 6
* suggestedcode: string at position 7
Function makes a call to the stored procedure setAccount, which does the actual insertion of the row
and also inserts a row in the subgroups table if user has entered a new subgroup name that does not exist.
Refer class rpc_main -> rpc_deploy for the exact specification of setAccount.
Returns true if successful and false otherwise.
"""
print queryParams
sp_params = [queryParams[0], queryParams[3]]
if queryParams[2] == "":
if queryParams[1] == "No Sub-Group":
print "we are not taking subgroup "
sp_params.append("null")
else:
sp_params.append(queryParams[1])
if queryParams[1] == "Create New Sub-Group":
print "there is a new subgroup created"
sp_params.append(queryParams[2])
print queryParams[1]
if queryParams[0] == "Direct Income" or queryParams[
0] == "Direct Expense" or queryParams[
0] == "Indirect Income" or queryParams[
0] == "Indirect Expense":
sp_params.append(0)
else:
sp_params.append(queryParams[5])
account_creation_date = str(strftime("%Y-%m-%d %H:%M:%S"))
sp_params.append(account_creation_date)
sp_params.append(sp_params[3])
if queryParams[7] == "":
sp_params.append("null")
else:
sp_params.append(queryParams[7])
#execute here
print "here is what we send to the execproc as a param list "
print sp_params
dbconnect.execproc("setAccount", dbconnect.engines[client_id],
sp_params)
return True
def xmlrpc_setProjects(self,queryParams,client_id):
"""
Purpose: function for saving projects if any for a particular organisation
i/p parameters: orgname, projectname
o/p parameter : true or false
"""
dbconnect.execproc("setProject", dbconnect.engines[client_id], queryParams)
description='Created Project '+queryParams[0]
#ipaddress='localhost'
dbconnect.setLog([6,description],client_id)
return True
def xmlrpc_setAccount(self, queryParams, client_id):
"""
Purpose: Adds an account in the account table, under a selected group and optionally a subgroup. Account code is either auto generated or entered by the user
Depending on the preference choosen by the user.
description:
This function inserts a row in the account table.
Function takes one parameter named queryParams which is a list containing,
* groupname as string at position 0
* subgroupflag as string at position 1
* subgroupname (optionally) at position 2
* account name: string at position 3
* accountcodetype: string at position 4
* openingbalance: integer at position 5
* currentBalance: integer at position 6
* suggestedcode: string at position 7
Function makes a call to the stored procedure setAccount, which does the actual insertion of the row
and also inserts a row in the subgroups table if user has entered a new subgroup name that does not exist.
Refer class rpc_main -> rpc_deploy for the exact specification of setAccount.
Returns true if successful and false otherwise.
"""
print queryParams
sp_params = [queryParams[0], queryParams[3]]
if queryParams[2] == "":
if queryParams[1] == "No Sub-Group":
print "we are not taking subgroup "
sp_params.append("null")
else:
sp_params.append(queryParams[1])
if queryParams[1] == "Create New Sub-Group" :
print "there is a new subgroup created"
sp_params.append(queryParams[2])
print queryParams[1]
if queryParams[0] == "Direct Income" or queryParams[0] == "Direct Expense" or queryParams[0] == "Indirect Income" or queryParams[0] == "Indirect Expense":
sp_params.append(0)
else:
sp_params.append(queryParams[5])
account_creation_date = str(strftime("%Y-%m-%d %H:%M:%S"))
sp_params.append(account_creation_date)
sp_params.append(sp_params[3])
if queryParams[7] == "":
sp_params.append("null")
else:
sp_params.append(queryParams[7])
#execute here
print "here is what we send to the execproc as a param list "
print sp_params
dbconnect.execproc("setAccount", dbconnect.engines[client_id], sp_params)
return True
def xmlrpc_curStockQty(self, queryParams, client_id):
'''
Purpose: Function to get the current quantity of the given product
I/O Parameters: queryParams which contains searchFlag and searchValue
Returns: total number of stock
Description: To serch product by code we pass searchFlag =1 and to search product by name we pass searchFlag = 0. searchValue will either be product code or product name. If searchFlag is 1 then we only search the stockqty table for all the rows matching given product code and count the quantiy by reading stocflag. If searchFlag is 0 then we first get product code by searching for givne product name in genericproduct table. Then same procedure as above. To the sql function curStockQty we pass spqueryParams which contains searchFlag, searchValue and primerykey
'''
totalstock = 0
spqueryParams = [queryParams[0], queryParams[0], queryParams[0]]
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
res = Session.query(dbconnect.StockQty).all()
Session.commit()
for l in res:
#spqueryParams for curStockQty(the sql function) are (searchFlag, searchValue, primarykey)
spqueryParams = [queryParams[0], queryParams[1], l.stockcode]
search = dbconnect.execproc("curStockQty",
dbconnect.engines[client_id],
spqueryParams)
stock = search.fetchall()
stqview = []
for stockrow in stock:
stqview.extend([
stockrow["stockcode"], stockrow["prodcode"],
stockrow["quantity"], stockrow["stockflag"]
])
if stqview == [None, None, None, None]:
continue
else:
if stqview[3] == 1:
totalstock = totalstock + stqview[2]
else:
totalstock = totalstock - stqview[2]
return totalstock
def xmlrpc_searchProductDetails(self, queryParams, client_id):
'''
purpose: Searches and returns product details. Search is based on either productcode or product name.
function takes one parameter queryParams of type list containing,
*searchFlag as integer (1 means search by product code and 2 means product name )
* searchValue as text (value depends on the searchFlag)
description:
This function queries the genericproduct table and fetches the following
* product_name
* vendor_name
* prod_desc
* sale_price
* open_qty
* cur_qty
* u_o_m
The function makes a call to stored procedure named searchProductDetails.
Refer to rpc_main -> rpc_deploy function for the complet spec of the stored procedure.
'''
searchedDetails = dbconnect.execproc("searchProductDetails",
dbconnect.engines[client_id],
queryParams)
products = searchedDetails.fetchall()
productView = []
for productRow in products:
productView.append([
productRow["product_name"], productRow["vendor_name"],
productRow["prod_desc"],
float(productRow["sale_price"]), productRow["open_qty"],
productRow["cur_qty"], productRow["u_o_m"]
])
return list(productView)
def xmlrpc_curStockQty(self, queryParams, client_id):
'''
Purpose: Function to get the current quantity of the given product
I/O Parameters: queryParams which contains searchFlag and searchValue
Returns: total number of stock
Description: To serch product by code we pass searchFlag =1 and to search product by name we pass searchFlag = 0. searchValue will either be product code or product name. If searchFlag is 1 then we only search the stockqty table for all the rows matching given product code and count the quantiy by reading stocflag. If searchFlag is 0 then we first get product code by searching for givne product name in genericproduct table. Then same procedure as above. To the sql function curStockQty we pass spqueryParams which contains searchFlag, searchValue and primerykey
'''
totalstock=0
spqueryParams=[queryParams[0],queryParams[0],queryParams[0]]
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
res = Session.query(dbconnect.StockQty).all()
Session.commit()
for l in res:
#spqueryParams for curStockQty(the sql function) are (searchFlag, searchValue, primarykey)
spqueryParams=[queryParams[0],queryParams[1],l.stockcode]
search=dbconnect.execproc("curStockQty",dbconnect.engines[client_id],spqueryParams)
stock=search.fetchall()
stqview=[]
for stockrow in stock:
stqview.extend([stockrow["stockcode"],stockrow["prodcode"],stockrow["quantity"],stockrow["stockflag"]])
if stqview==[None,None,None,None]:
continue;
else:
if stqview[3]==1:
totalstock = totalstock + stqview[2]
else:
totalstock = totalstock - stqview[2]
return totalstock
def xmlrpc_getLastReference(self,queryParams,client_id):
reference = dbconnect.execproc("getLastReference", dbconnect.engines[client_id],queryParams)
lastReff = reference.fetchone()
if lastReff["reffno"] == None:
return ''
else:
return lastReff["reffno"]
def xmlrpc_editAccount(self, queryParams, client_id):
"""
purpose: modifies an account based on account code. alters account name and opening balance.
function takes one parameter queryParams, which is a list containing accountname,accountcode, groupname and new opening balance.
returns the newly updated current balance.
description:
this function will edit an account and change either account name, oepning balance or both.
the account is fetched internally by the software on the basis of account code, even if it was searched by client using account name.
If the function is successful,it will return the newly updated current balance.
If the groupname sent in the queryParams is direct or indirect income, or direct or indirect expence, then the oepning balance is sent as 0.
this function uses the editAccount stored procedure.
Refer rpc_main -> rpc_deploy for the complete spec of the stored said stored procedure.
"""
spQueryParams = [queryParams[0], queryParams[1]]
if queryParams[2] == "Direct Income" or queryParams[
2] == "Indirect Income" or queryParams[
2] == "Direct Expense" or queryParams[
2] == "Indirect Expense":
print "sending openingbalance as 0"
spQueryParams.append(0)
else:
spQueryParams.append(queryParams[3])
print spQueryParams
result = dbconnect.execproc("editAccount",
dbconnect.engines[client_id],
spQueryParams)
row = result.fetchone()
return row[0]
def xmlrpc_getSuggestedCode(self, queryParams, client_id):
"""
purpose: decides the code to be suggested on the basis of provided 3 characters at list queryParams[0] 2 from group and 1 from the account name
returns a string containing the suggested code.
description:
function takes the 2 characters of selected group and first character of account.
The 2 characters of the selected group are determined in the front end.
The first character of the entered account is then appended to the former.
For example, an account SBI in group Current Asset will send CAS as the 3 characters as queryParams[0]
The function then executes a stored procedure getSuggestedCode and checks if an account exists with a code starting with the given 3 characters.
if an account did exist then the given 3 characters will be postfixed with total count of existing similar account codes + 100.
If no such account is found then 100 will be concatinated to the first 3 chars.
for example if no account exists with an account code starting with CAS, then the suggested code will be CAS100.
Next time an account with 3 chars as CAS is entered, then it will be CAS101.
to see the entire spec of stored procedure getSuggestedCode, refer rpc_main -> rpc_deploy.
"""
result = dbconnect.execproc("getSuggestedCode",
dbconnect.engines[client_id],
[queryParams[0]])
row = result.fetchone()
SuggestedAccountCode = row[0]
if SuggestedAccountCode == 0:
return str(queryParams[0] + "100")
else:
SuggestedAccount = SuggestedAccountCode + 100
return str(queryParams[0]) + str(SuggestedAccount)
def xmlrpc_searchVoucher(self, queryParams, client_id):
"""
Returns one or more vouchers given the reference number, date range, narration or amount (which ever specified) takes one parameter queryParams as list.
List contains searchFlag integer (1 implies serch by reference, 2 as search by date range, 3 as search by narration and 4 as amount at position 0
reference number text at position 1, start range date at 2, end range date at 3, narration phrase at position 4 and amount at position 5.
returns a 2 dimensional list containing one or more records from voucher_master
description:
The function is used to get the list of vouchers on the basis of either reference number (which can be duplicate), or date range, or some words from narration or amount.
This means one or more vouchers could be by the same reference number or within a given date range, narration or amount.
The list thus returned contains all details of a given voucher except its exact transactions, i.e the records from voucher_master.
The function makes use of the searchVouchers store procedure, following the getVoucherDetails stored procedures.
"""
searchedVouchers = dbconnect.execproc("searchVouchers",
dbconnect.engines[client_id],
queryParams)
vouchers = searchedVouchers.fetchall()
voucherView = []
for voucherRow in vouchers:
resultAccounts = dbconnect.execproc("getVoucherDetails",
dbconnect.engines[client_id],
[voucherRow[0]])
voucherAccounts = resultAccounts.fetchall()
drAccount = ""
crAccount = ""
drCounter = 1
crCounter = 1
for va in voucherAccounts:
if va["transactionflag"] == "Dr" and drCounter == 2:
drAccount = va["accountname"] + "+"
if va["transactionflag"] == "Dr" and drCounter == 1:
drAccount = va["accountname"]
drCounter = drCounter + 1
if va["transactionflag"] == "Cr" and crCounter == 2:
crAccount = va["accountname"] + "+"
if va["transactionflag"] == "Cr" and crCounter == 1:
crAccount = va["accountname"]
crCounter = crCounter + 1
totalAmount = str(voucherRow["total_amount"])
voucherView.append([
voucherRow["voucher_code"], voucherRow["reference_number"],
voucherRow["reff_date"].strftime("%d-%m-%Y"),
voucherRow["voucher_type"], drAccount, crAccount,
(totalAmount), voucherRow["voucher_narration"]
])
print queryParams
return voucherView
def xmlrpc_deleteClearedRecon(self,queryParams,client_id):
print "this is delete query param"
print queryParams
result = dbconnect.execproc("deleteClearedRecon", dbconnect.engines[client_id],[queryParams[0],queryParams[1],queryParams[2] ])
if result == True:
return True
else:
return False
def xmlrpc_getAllProjects(self,client_id):
result = dbconnect.execproc("getAllProjects",dbconnect.engines[client_id],[])
projectRecords = result.fetchall()
projects = []
for row in projectRecords:
projects.append([row["project_code"],row["project_name"]])
return projects
def xmlrpc_getReceivableAccounts(self,queryParams,client_id):
result = dbconnect.execproc("getReceivables", dbconnect.engines[client_id], queryParams)
if result == []:
return False
else:
recievableAccounts = []
for row in result:
recievableAccounts.append(row[0])
return recievableAccounts
def xmlrpc_getPaymentAccounts(self,queryParams,client_id):
result = dbconnect.execproc("getPayments",dbconnect.engines[client_id], queryParams)
if result == []:
return False
else:
paymentAccounts = []
for row in result:
paymentAccounts.append(row[0])
return paymentAccounts
def xmlrpc_getProjectcodeByName(self,queryParams,client_id):
result = dbconnect.execproc("getProjectCodeByName", dbconnect.engines[client_id], queryParams)
row = result.fetchone()
projectCode = row["project_code"]
if projectCode == None:
return 0
else:
return projectCode
def xmlrpc_getLastReference(self, queryParams, client_id):
reference = dbconnect.execproc("getLastReference",
dbconnect.engines[client_id],
queryParams)
lastReff = reference.fetchone()
if lastReff["reffno"] == None:
return ''
else:
return lastReff["reffno"]
def xmlrpc_getReceivableAccounts(self, queryParams, client_id):
result = dbconnect.execproc("getReceivables",
dbconnect.engines[client_id], queryParams)
if result == []:
return False
else:
recievableAccounts = []
for row in result:
recievableAccounts.append(row[0])
return recievableAccounts
def xmlrpc_getPaymentAccounts(self, queryParams, client_id):
result = dbconnect.execproc("getPayments",
dbconnect.engines[client_id], queryParams)
if result == []:
return False
else:
paymentAccounts = []
for row in result:
paymentAccounts.append(row[0])
return paymentAccounts
def xmlrpc_setGenericProduct(self,queryParams, client_id):
'''
Purpose: Adds a product in the genericproduct table, Product code is entered by the user which is primary key itself
Description:
This function inserts a row in the genericproduct table.
Function takes one parameter named queryParams which is a list containing,
* productcode as string at position 0
* productname as text at position 1
* vendorname as text at position 2
* product description as text at position 3
* sale_price as numeric at position 4
* open_qty as integer at position 5
* unit of measurement as integer at position 6
Function makes a call to the stored procedure setGenericProduct, which does the actual insertion of the row.
Refer class rpc_main -> rpc_deploy for the exact specification of setGenericProduct.
Returns true if successful and false otherwise.
'''
dbconnect.execproc("setGenericProduct", dbconnect.engines[client_id],queryParams)
return True
def xmlrpc_getProjectcodeByName(self, queryParams, client_id):
result = dbconnect.execproc("getProjectCodeByName",
dbconnect.engines[client_id], queryParams)
row = result.fetchone()
projectCode = row["project_code"]
if projectCode == None:
return 0
else:
return projectCode
def xmlrpc_getTrialBalance(self,queryParams,client_id):
"""
Purpose: gets trial balance as on the given date.
Returns a grid of 4 columns and number of rows depending on number of accounts.
description:
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 stored procedure getAllAccounts and stors 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 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 procured 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.
"""
accounts = dbconnect.execproc("getAllAccounts", dbconnect.engines[client_id],[])
trialBalance = []
srno =1
total_dr = 0.00
total_cr = 0.00
for account in accounts:
closingBalance = dbconnect.execproc("calculateBalance", dbconnect.engines[client_id], [str(account[0]),queryParams[0],queryParams[1],queryParams[2]])
closingRow = closingBalance.fetchone()
if float(closingRow["curbal"]) != 0:
trialRow = []
trialRow.append(srno)
trialRow.append(account["accountname"])
trialRow.append(closingRow["group_name"])
if closingRow["baltype"] == "Cr":
total_cr = total_cr + float(closingRow["curbal"])
trialRow.append("")
trialRow.append('%.2f'%float(closingRow["curbal"]))
if closingRow["baltype"] == "Dr":
total_dr = total_dr + float(closingRow["curbal"])
trialRow.append('%.2f'%float(closingRow["curbal"]))
trialRow.append("")
srno = srno +1
trialBalance.append(trialRow)
total_balances = ['%.2f'%total_dr,'%.2f'%total_cr]
trialBalance.append(total_balances)
return trialBalance
def xmlrpc_setGenericProduct(self, queryParams, client_id):
'''
Purpose: Adds a product in the genericproduct table, Product code is entered by the user which is primary key itself
Description:
This function inserts a row in the genericproduct table.
Function takes one parameter named queryParams which is a list containing,
* productcode as string at position 0
* productname as text at position 1
* vendorname as text at position 2
* product description as text at position 3
* sale_price as numeric at position 4
* open_qty as integer at position 5
* unit of measurement as integer at position 6
Function makes a call to the stored procedure setGenericProduct, which does the actual insertion of the row.
Refer class rpc_main -> rpc_deploy for the exact specification of setGenericProduct.
Returns true if successful and false otherwise.
'''
dbconnect.execproc("setGenericProduct", dbconnect.engines[client_id],
queryParams)
return True
def xmlrpc_accountExists(self, queryParams, client_id):
'''
Purpose : Function for finding if an account already exists with the supplied name.
Parameters : queryParams which is a list containing one element, accountname as string.
Returns : 1 if account name exists and 0 if not.
Description : Querys the account table and sees if an account name similar to one provided as a parameter exists.
We can ensure that no duplicate account is ever entered because if a similar account exists like the one in queryparams[0[ then we won't allow another entry with same name.
'''
result = dbconnect.execproc("accountExists", dbconnect.engines[client_id], [queryParams[0]])
row = result.fetchone()
return str(row[0])
def xmlrpc_setTransaction(self,queryParams_master,queryParams_details,client_id):
"""
Purpose: adds a new voucher in the database given its reference number and transaction details (dr and cr), along with narration and the date.
Purpose:
This function is used to create a new voucher. A voucher code is generated automatically while the user gives optional reference number.
The entire transaction is recorded in terms of Dr and Cr and the respected amounts.
The function utilises 2 stored procedures.
setVoucherMaster and setVoucherDetails.
For voucher master the function takes queryParams_master containing,
* reference Number
*system Date (on which the voucher was entered )
* the actual transaction date
*Voucher type,
*project name,
* Narration,
*purchase order number,
*purchase order date and
*purchase order amount
The setVoucherDetails takes a 2 dimensional list containing,
rows with columns,
* DrCr flag,
AccountName (from which account code will be procured by the said stored procedure)
* the amount for the respective account.
The function returns true if successful or false otherwise.
"""
print queryParams_details; print queryParams_master
prj = self.xmlrpc_getProjectcodeByName([str(queryParams_master[4])],client_id)
del queryParams_master[4]
queryParams_master.insert(4,prj)
print queryParams_master
success = dbconnect.execproc("setVoucherMaster", dbconnect.engines[client_id],queryParams_master)
successRow = success.fetchone()
voucherCode = successRow[0]
print "query for masters is successful and voucher code is " + str(voucherCode)
for detailRow in queryParams_details:
accountCodeResult = dbconnect.execproc("getAccountCode",dbconnect.engines[client_id],[detailRow[1]])
accountCodeRow = accountCodeResult.fetchone()
accountCode = accountCodeRow["account_code"]
dbconnect.execproc("setVoucherDetails",dbconnect.engines[client_id],[voucherCode,str(detailRow[0]),str(accountCode),float(detailRow[2])])
return 1
def xmlrpc_subgroupExists(self, queryParams, client_id):
"""
purpose: Checks if the new subgroup typed by the user already exists.
This will validate and prevent any duplication.
Description: The function takes queryParams as its parameter and contains one element, the subgroupname as string.
Returns True if the subgroup exists and False otherwise
"""
result = dbconnect.execproc("subgroupExists",
dbconnect.engines[client_id],
[queryParams[0]])
row = result.fetchone()
return str(row[0])
def xmlrpc_setSubGroup(self, queryParams, client_id):
'''
Purpose :function for adding new subgroups in table subgroups
Parameters : groupname(datatype:text), subgroupname(datatype:text)
Returns : returns 1 when successful, 0 when failed
Description : Adds new subgroup to the database.
When successful it returns 1 otherwise it returns 0.
'''
res = dbconnect.execproc("setSubGroup", dbconnect.engines[client_id],
[queryParams[0], queryParams[1]])
row = res.fetchone()
return str(row[0])
def xmlrpc_editVoucher(self, queryParams_master, queryParams_details,
client_id):
proj_code = dbconnect.execproc("getProjectCodeByName",
dbconnect.engines[client_id],
[queryParams_master[2]])
projectRow = proj_code.fetchone()
projectCode = projectRow[0]
if projectCode == None:
projectCode = 0
del queryParams_master[2]
queryParams_master.insert(2, projectCode)
successResult = dbconnect.execproc("editVoucherMaster",
dbconnect.engines[client_id],
queryParams_master)
successRow = successResult.fetchone()
if successRow["success"] == True:
dbconnect.execproc("deleteVoucherDetails",
dbconnect.engines[client_id],
[queryParams_master[0]])
for detailRow in queryParams_details:
sp_details = []
sp_details.append(queryParams_master[0])
sp_details.append(detailRow[0])
if float(detailRow[2]) == 0:
sp_details.append("Dr")
sp_details.append(float(detailRow[1]))
if float(detailRow[1]) == 0:
sp_details.append("Cr")
sp_details.append((detailRow[2]))
dbconnect.execproc("editVoucherDetails",
dbconnect.engines[client_id], sp_details)
return True
def xmlrpc_getAccount(self, queryParams, client_id):
"""
purpose: Searches and returns account details. Search is based on either accountcode or account name.
function takes one parameter queryParams of type list containing,
*searchFlag as integer (1 means search by account code and 2 means account name )
* searchValue as text (value depends on the searchFlag)
description:
This function queries the group_subgroup_account view and fetches the following.
*groupname
* subgroupname (if any )
* accountcode
* accountname
* openingbalance
The function makes a call to stored procedure named getAccount.
Refer to rpc_main -> rpc_deploy function for the complete spec of the stored procedure.
"""
result = dbconnect.execproc("getAccount", dbconnect.engines[client_id],
queryParams)
row = result.fetchone()
hasTransactionResult = dbconnect.execproc("hasTransactions",
dbconnect.engines[client_id],
[str(row[3])])
hasTransactionRow = hasTransactionResult.fetchone()
hasTransactionFlag = hasTransactionRow["success"]
hasOpeningBalanceResult = dbconnect.execproc(
"hasOpeningBalance", dbconnect.engines[client_id], [str(row[3])])
hasOpeningBalanceRow = hasOpeningBalanceResult.fetchone()
hasOpeningBalanceFlag = hasOpeningBalanceRow["success"]
if row[1] == None:
return list([
row[2], row[0], 0.00, row[3], row[4], hasTransactionFlag,
hasOpeningBalanceFlag
])
else:
return list([
row[2], row[0], row[1], row[3], row[4], hasTransactionFlag,
hasOpeningBalanceFlag
])
def xmlrpc_accountExists(self, queryParams, client_id):
'''
Purpose : Function for finding if an account already exists with the supplied name.
Parameters : queryParams which is a list containing one element, accountname as string.
Returns : 1 if account name exists and 0 if not.
Description : Querys the account table and sees if an account name similar to one provided as a parameter exists.
We can ensure that no duplicate account is ever entered because if a similar account exists like the one in queryparams[0[ then we won't allow another entry with same name.
'''
result = dbconnect.execproc("accountExists",
dbconnect.engines[client_id],
[queryParams[0]])
row = result.fetchone()
return str(row[0])
def xmlrpc_getProjectStatement(self,queryParams,client_id):
"""
Purpose, gets the project statement for a given project.
The function takes 1 arguement a list of query params.
The list contains one string, project name.
returns the total incoming and out going for a given project.
Description:
The function takes one arguement named queryParams which is a single element list.
The element contains projectname.
On the basis of this projectname, a list of accounts is produced.
this list of accounts contains those accounts which are involved in transactions corresponding to the given project.
However this list will be further filtered in a final list of accounts
with only those names which are not in the subgroup bank or cash.
after this filteration the call to the stored procedure for getting sum of total Crs and Drs is made.
This function makes use of getProjectAccounts,getGroupNameByAccountName,getProjectStatement,getSubGroupByAccount
"""
projectAccountResult = dbconnect.execproc("getProjectAccounts",dbconnect.engines[client_id],[str(queryParams[0])])
projectAccounts = projectAccountResult.fetchall()
totalDr = 0.00
totalCr = 0.00
srno = 1
projectStatement = []
for accountRow in projectAccounts:
group = dbconnect.execproc("getGroupNameByAccountName",dbconnect.engines[client_id],[str(accountRow["accname"])])
groupRow = group.fetchone()
groupForAccount = groupRow["groupname"]
if groupForAccount == "Direct Income" or groupForAccount == "Direct Expense" or groupForAccount == "Indirect Income" or groupForAccount == "Indirect Expense":
groupResult = dbconnect.execproc("getGroupNameByAccountName",dbconnect.engines[client_id],[str(accountRow["accname"])])
groupRow = groupResult.fetchone()
accountGroup = groupRow["groupname"]
result = dbconnect.execproc("getProjectStatement", dbconnect.engines[client_id],[queryParams[0],str(accountRow["accname"]),queryParams[1],queryParams[2],queryParams[3]])
resultRow = result.fetchone()
statementRow = [srno,accountRow["accname"],accountGroup,'%.2f'%float(resultRow["totalDr"]),'%.2f'%float(resultRow["totalCr"])]
totalDr = totalDr + resultRow["totalDr"]
totalCr = totalCr + resultRow["totalCr"]
srno = srno +1
projectStatement.append(statementRow)
projectStatement.append(["","","",'%.2f'%float(totalDr),'%.2f'%float(totalCr)])
return projectStatement
def xmlrpc_searchVoucher(self,queryParams,client_id):
"""
Returns one or more vouchers given the reference number, date range, narration or amount (which ever specified) takes one parameter queryParams as list.
List contains searchFlag integer (1 implies serch by reference, 2 as search by date range, 3 as search by narration and 4 as amount at position 0
reference number text at position 1, start range date at 2, end range date at 3, narration phrase at position 4 and amount at position 5.
returns a 2 dimensional list containing one or more records from voucher_master
description:
The function is used to get the list of vouchers on the basis of either reference number (which can be duplicate), or date range, or some words from narration or amount.
This means one or more vouchers could be by the same reference number or within a given date range, narration or amount.
The list thus returned contains all details of a given voucher except its exact transactions, i.e the records from voucher_master.
The function makes use of the searchVouchers store procedure, following the getVoucherDetails stored procedures.
"""
searchedVouchers = dbconnect.execproc("searchVouchers", dbconnect.engines[client_id], queryParams)
vouchers = searchedVouchers.fetchall()
voucherView = []
for voucherRow in vouchers:
resultAccounts = dbconnect.execproc("getVoucherDetails",dbconnect.engines[client_id],[voucherRow[0]])
voucherAccounts = resultAccounts.fetchall()
drAccount = ""
crAccount = ""
drCounter = 1
crCounter = 1
for va in voucherAccounts:
if va["transactionflag"] == "Dr" and drCounter == 2:
drAccount = va["accountname"] + "+"
if va["transactionflag"] == "Dr" and drCounter == 1:
drAccount = va["accountname"]
drCounter = drCounter +1
if va["transactionflag"] == "Cr" and crCounter == 2:
crAccount = va["accountname"] + "+"
if va["transactionflag"] == "Cr" and crCounter == 1:
crAccount = va["accountname"]
crCounter = crCounter +1
totalAmount = str(voucherRow["total_amount"])
voucherView.append([voucherRow["voucher_code"],voucherRow["reference_number"],voucherRow["reff_date"].strftime("%d-%m-%Y"),voucherRow["voucher_type"],drAccount,crAccount,(totalAmount),voucherRow["voucher_narration"]])
print queryParams
return voucherView
def xmlrpc_deleteAccount(self,queryParams,client_id):
'''
Purpose : Function for deleting accounts. For this we have used hasOpeningBalance,
hasTransactions & deleteAccount stored procedures. With the help of hasTransactions
we are able to find out whether the given account has any transactions or not. The stored procedure tells that if there is any voucher entry of that account name return true or else return false
The second stored procedure hasOpeningBalance returns true if opening balance for that account exists or else returns false
The third stored procedure deleteAccount deletes that particular accountname
'''
hasOpeningBalance = dbconnect.execproc("hasOpeningBalance", dbconnect.engines[client_id], [str(queryParams[0])])
hasOpeningBalanceRow = hasOpeningBalance.fetchone()
print hasOpeningBalanceRow["success"]
hasTransactions = dbconnect.execproc("hasTransactions", dbconnect.engines[client_id], [str(queryParams[0])])
hasTransactionsRow = hasTransactions.fetchone()
print hasTransactionsRow["success"]
if hasOpeningBalanceRow["success"] == False and hasTransactionsRow["success"] == False:
try:
dbconnect.execproc("deleteAccount", dbconnect.engines[client_id],[str(queryParams[0])])
return True
except:
return False
else:
return False
def xmlrpc_getGroupByCode(self, queryParams, client_id):
"""
purpose: gets record for group given its code.
description:
returns a row containing details for a group given its code.
the record contains groupcode and groupname.
The function calls the getGroupByCode stored procedure which actually returns the said record.
Refer rpc_class rpc_main -> rpc_deploy function for specifications of the stored procedure.
"""
result = dbconnect.execproc("getGroupByCode",
dbconnect.engines[client_id],
[queryParams[0]])
row = result.fetchone()
return row
def xmlrpc_getDrOpeningBalance(self, client_id):
"""
Purpose: calculates the total debit opening balance for all accounts with Dr opening balance. Functions takes no arguement and returns a float value.
Description:
when adding an account we tend to know what is the total of all debit and credit opening balances.
This function calculates the total for all accounts with Dr as opening balance.
function executes the getTotalDrOpeningBalance for the expected result as float.
refer rpc_main -> rpc_deploy for the complet spec of the said procedure.
"""
result = dbconnect.execproc("getTotalDrOpeningBalance", dbconnect.engines[client_id])
row = result.fetchone()
if row[0] == None:
return 0.00
else:
return row[0]
def xmlrpc_setBankRecon(self,queryParams,client_id):
'''
Purpose : 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.
Parameters : 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)]
'''
# 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])
print sp_params
#Finally we are ready to set the bankrecon table.
dbconnect.execproc("setBankRecon",dbconnect.engines[client_id],sp_params)
return True
def xmlrpc_getAllGroups(self, client_id):
'''
purpose:gets the list of all standard groups.
description:
returns list containing group name.
'''
res = dbconnect.execproc("getAllGroups", dbconnect.engines[client_id])
if res == []:
return False
else:
result = []
for row in res:
result.append(
[row["groupcode"], row["groupname"], row["groupdesc"]])
return result
def xmlrpc_getBankList(self,client_id):
"""
Purpose: returns list of all bank accounts.
"""
#let's get all the bank accounts where subgroup name is 'Bank'.
getallbankaccounts = dbconnect.execproc("getAllBankAccounts", dbconnect.engines[client_id])
#if their is no bank account created in subgroup returns empty list.
if getallbankaccounts == []:
return False
else:
bankaccount = []
#this is to append bank accounts in list bankaccount.
for row in getallbankaccounts:
bankaccount.append(row[0])
#we are ready with list of all bank accounts.
return bankaccount
def xmlrpc_getAllAccounts(self, client_id):
"""
purpose: returns the list of all accountnames in the database.
description: returns the list of name of all accounts.
if there are no accounts to return then returns False.
The function calls the getAllAccounts stored procedure.
refer the class rpc_main -> rpc_deploy method for complete spec.
"""
result = dbconnect.execproc("getAllAccounts", dbconnect.engines[client_id])
res = result.fetchall()
accountnames = []
if res == []:
return accountnames
for r in res:
accountnames.append(r[0])
return accountnames
def xmlrpc_getDrOpeningBalance(self, client_id):
"""
Purpose: calculates the total debit opening balance for all accounts with Dr opening balance. Functions takes no arguement and returns a float value.
Description:
when adding an account we tend to know what is the total of all debit and credit opening balances.
This function calculates the total for all accounts with Dr as opening balance.
function executes the getTotalDrOpeningBalance for the expected result as float.
refer rpc_main -> rpc_deploy for the complet spec of the said procedure.
"""
result = dbconnect.execproc("getTotalDrOpeningBalance",
dbconnect.engines[client_id])
row = result.fetchone()
if row[0] == None:
return 0.00
else:
return row[0]
def xmlrpc_getVoucherDetails(self,queryParams,client_id):
"""
purpose: gets the transaction related details given a vouchercode.
returns a 2 dymentional list containing rows with 3 columns.takes one parameter QueryParams, which is list containing vouchercode
description:
The function used to get the detailed view of a voucher given its vouchercode.
returns a 2 dymentional list containing rows for a transaction.
the 3 columns are, accountname, typeflag (Cr/Dr) and amount.
The function uses the getVoucherDetails stored procedure.
"""
transactions = dbconnect.execproc("getVoucherDetails",dbconnect.engines[client_id],queryParams)
transactionRecords = transactions.fetchall()
voucherDetails = []
for transactionRow in transactionRecords:
voucherDetails.append([transactionRow["accountname"],transactionRow["transactionFlag"],'%.2f'%float(transactionRow["transactionamount"])])
print queryParams
return voucherDetails
def xmlrpc_getAllAccounts(self, client_id):
"""
purpose: returns the list of all accountnames in the database.
description: returns the list of name of all accounts.
if there are no accounts to return then returns False.
The function calls the getAllAccounts stored procedure.
refer the class rpc_main -> rpc_deploy method for complete spec.
"""
result = dbconnect.execproc("getAllAccounts",
dbconnect.engines[client_id])
res = result.fetchall()
accountnames = []
if res == []:
return accountnames
for r in res:
accountnames.append(r[0])
return accountnames
def xmlrpc_getSubGroupByAccount(self,queryParams,client_id):
'''
Purpose :function for extracting all rows of view_account based on groupname
Parameters : QueryParams, list containing groupname(datatype:text)
Returns : List when successful, False when failed
Description : Querys the view_account which is created based on the account ,subgroups and groups table.It retrieves all rows of view_account based on groupname order by subgroupname.
When successful it returns the list of lists in which each list contain each row that are retrived from view otherwise it returns False.
'''
res = dbconnect.execproc("getSubGroupByAccount",dbconnect.engines[client_id],[queryParams[0]])
if res == []:
return False
else:
result = []
for row in res:
result.append([row["subgroupname"]])
return result
def xmlrpc_getJournalAccounts(self,client_id):
"""
Purpose: fetches the list of all accounts which are used in a journal voucher. Takes no arguments and returns list of accounts.
If no accounts are found for journal then returns false.
description:
This function is called for populating the account's list with all the accounts for journal.
Note that journal voucher involves all accounts, except cash and bank accounts.
This function calls the getJournal stored procedure for fetching the said list of account names.
refer to class rpc_main -> rpc_deploy for complete spec of the said stored procedure.
"""
result = dbconnect.execproc("getJournal", dbconnect.engines[client_id],[])
if result == []:
return False
else:
journalAccounts = []
for row in result:
journalAccounts.append(row[0])
return journalAccounts
def xmlrpc_getAccountCodeListByCode(self,queryParams,client_id):
'''
Purpose : Function for extracting accountcode list from account table
I/O Parameters : queryparam which contain value of accountcode field
Returns : accountcode(datatype:list) when successful, False when failed
Description : Querys the account tables and it retrives the accountcode depending upon the initial characters from rows in account table.
When successful it returns the list consists of elements who matches that character i.e accountcode(datatype:string) else it returns False.
'''
print str(queryParams)
result = dbconnect.execproc("getAccountCodeListByCode", dbconnect.engines[client_id],[str(queryParams[0])])
res = result.fetchall()
print res
if res == []:
return False
accountcode = []
for r in res:
accountcode.append(r[0])
return accountcode
def xmlrpc_getJournalAccounts(self, client_id):
"""
Purpose: fetches the list of all accounts which are used in a journal voucher. Takes no arguments and returns list of accounts.
If no accounts are found for journal then returns false.
description:
This function is called for populating the account's list with all the accounts for journal.
Note that journal voucher involves all accounts, except cash and bank accounts.
This function calls the getJournal stored procedure for fetching the said list of account names.
refer to class rpc_main -> rpc_deploy for complete spec of the said stored procedure.
"""
result = dbconnect.execproc("getJournal", dbconnect.engines[client_id],
[])
if result == []:
return False
else:
journalAccounts = []
for row in result:
journalAccounts.append(row[0])
return journalAccounts
def xmlrpc_getSubGroupByAccount(self, queryParams, client_id):
'''
Purpose :function for extracting all rows of view_account based on groupname
Parameters : QueryParams, list containing groupname(datatype:text)
Returns : List when successful, False when failed
Description : Querys the view_account which is created based on the account ,subgroups and groups table.It retrieves all rows of view_account based on groupname order by subgroupname.
When successful it returns the list of lists in which each list contain each row that are retrived from view otherwise it returns False.
'''
res = dbconnect.execproc("getSubGroupByAccount",
dbconnect.engines[client_id],
[queryParams[0]])
if res == []:
return False
else:
result = []
for row in res:
result.append([row["subgroupname"]])
return result
def xmlrpc_getPurchaseReturnAccounts(self, queryParams, client_id):
"""
Purpose: gets the list of accounts for purchases return either for credit or debit side.
Function takes one parameter queryParams which is a list containing only one element, cr_dr_flag.
Returns list of accounts else false if not found.
description:
returns a list of accounts pertaining to purchases return.
If the input parameter in queryParams[0] is Cr then only the credit side of accounts is returned else debit side of accounts is returned in form of list.
The function makes a call to the getPurchasesReturn stored procedure.
For detailed spec of the said procedure refer to the class xmlrpc_main -> xmlrpc_deploy method.
"""
result = dbconnect.execproc("getPurchasesReturn",
dbconnect.engines[client_id], queryParams)
if result == []:
return False
else:
purchasereturnAccounts = []
for row in result:
purchasereturnAccounts.append(row[0])
return purchasereturnAccounts
def xmlrpc_getAccountCodeListByCode(self, queryParams, client_id):
'''
Purpose : Function for extracting accountcode list from account table
I/O Parameters : queryparam which contain value of accountcode field
Returns : accountcode(datatype:list) when successful, False when failed
Description : Querys the account tables and it retrives the accountcode depending upon the initial characters from rows in account table.
When successful it returns the list consists of elements who matches that character i.e accountcode(datatype:string) else it returns False.
'''
print str(queryParams)
result = dbconnect.execproc("getAccountCodeListByCode",
dbconnect.engines[client_id],
[str(queryParams[0])])
res = result.fetchall()
print res
if res == []:
return False
accountcode = []
for r in res:
accountcode.append(r[0])
return accountcode
def xmlrpc_getVoucherDetails(self, queryParams, client_id):
"""
purpose: gets the transaction related details given a vouchercode.
returns a 2 dymentional list containing rows with 3 columns.takes one parameter QueryParams, which is list containing vouchercode
description:
The function used to get the detailed view of a voucher given its vouchercode.
returns a 2 dymentional list containing rows for a transaction.
the 3 columns are, accountname, typeflag (Cr/Dr) and amount.
The function uses the getVoucherDetails stored procedure.
"""
transactions = dbconnect.execproc("getVoucherDetails",
dbconnect.engines[client_id],
queryParams)
transactionRecords = transactions.fetchall()
voucherDetails = []
for transactionRow in transactionRecords:
voucherDetails.append([
transactionRow["accountname"],
transactionRow["transactionFlag"],
'%.2f' % float(transactionRow["transactionamount"])
])
print queryParams
return voucherDetails
