def xmlrpc_getGroupCodeByGroupName(self,queryParams,client_id): ''' * Purpose: - function for extracting groupcpde of group based on groupname. - query to retrive groupcode requested groupname by client. * Input: - groupname(datatype:text) , client_id(datatype:integer) * Output: - returns list containing groupcode if its not None else will return false. ''' queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Groups).\ filter(dbconnect.Groups.groupname == queryParams[0]).\ first() Session.close() connection.connection.close() if result != None: return [result.groupcode] else: return []
def xmlrpc_getUserRole(self,queryParams,client_id): ''' * Purpose: - It will provide information of user based on username and password return list containing username userrole if condition is true else return false * Input: - [username , password ] * Output - it returns list of username and userrole ''' queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Users).\ filter(dbconnect.Users.username == queryParams[0]).\ first() Session.close() connection.connection.close() if result != None: return result.userrole else: return ""
def xmlrpc_updateOrg(self, queryParams, client_id):
'''
Purpose: updating the orgdetails after edit organisation
Input: queryParams[
orgcode,orgaddress,orgcountry,orgstate,
orgcity,orgpincode,orgtelno,orgfax,orgemail,
orgwebsite,orgmvat,orgstax,orgregno,
orgregdate,orgfcrano,orgfcradate,orgpan]
client_id
Output: It will returns String "upadted successfully"
'''
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Organisation).\
filter(dbconnect.Organisation.orgcode == queryParams[0]).\
update({'orgaddr': queryParams[1],'orgcountry':queryParams[2],'orgstate':queryParams[3],\
'orgcity': queryParams[4],'orgpincode':queryParams[5],'orgtelno':queryParams[6],\
'orgfax':queryParams[7],'orgemail':queryParams[8],'orgwebsite':queryParams[9],\
'orgmvat':queryParams[10],'orgstax':queryParams[11],'orgregno':queryParams[12],\
'orgregdate':queryParams[13],'orgfcrano':queryParams[14],'orgfcradate':queryParams[15],\
'orgpan':queryParams[16]})
Session.commit()
Session.close()
connection.connection.close()
return "upadted successfully"
def xmlrpc_getPreferences(self, queryParams, client_id):
"""
Purpose: Finding the appropriate preferences
if flag no is "2" then will return
accountcode flag value.
If flag no is "1" then will return
refeno flag value
Input: queryParams[flagname]
Output: It returns flagno depnd on flagname
"""
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Flags).\
filter(dbconnect.Flags.flagno == queryParams[0]).\
first()
if result == []:
return result
else:
return result.flagname
Session.close()
connection.connection.close()
def xmlrpc_getOrganisation(self, client_id):
"""
Purpose: function to get all the details of organisation from database
Input: client_id
Output: It will return list of organisation details
"""
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Organisation).all()
Session.close()
connection.connection.close()
if result == []:
return result
else:
orgdetail_list = []
for l in result:
orgdetail_list.append([\
l.orgcode,l.orgtype,l.orgname,l.orgaddr,\
l.orgcity,l.orgpincode,l.orgstate,l.orgcountry,\
l.orgtelno,l.orgfax,l.orgwebsite,l.orgemail,\
l.orgpan,l.orgmvat,l.orgstax,l.orgregno,\
l.orgregdate,l.orgfcrano,l.orgfcradate\
])
return orgdetail_list
def xmlrpc_setPreferences(self, queryParams, client_id):
"""
Purpose: function for update flags for project
,manually created account code and voucher
reference number i/p parameters: Flag
No(datatype:integer) , FlagName
(datatype:text) o/p parameter : True
Description : if flag no is "2" then will update
accountcode flag value as either
"manually" or "automatic"(default) if
flag no is "1" then will update refeno
flag value as either "mandatory" or
"optional"
"""
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
Session.query(dbconnect.Flags).\
filter(dbconnect.Flags.flagno == queryParams[0]).\
update({'flagname':queryParams[1]})
Session.commit()
Session.close()
connection.connection.close()
return True
def xmlrpc_setOrganisation(self, queryParams, client_id):
"""
Purpose : Function for add organisation details in database
Input : if orgtype is 'NGO then
[orgname,orgtype,orgcountry,orgstate,orgcity,orgaddr,orgpincode,
orgtelno, orgfax, orgwebsite, orgemail, orgpan, "", "",
orgregno, orgregdate, orgfcrano, orgfcradate]
else
[orgname,orgtype,orgcountry,orgstate,orgcity,orgaddr,orgpincode,
orgtelno, orgfax, orgwebsite, orgemail, orgpan,orgmvat,orgstax,
"", "", "", ""]
Output: Returns boolean True if added successfully else False
"""
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
print "org details"
print queryParams
Session.add_all([\
dbconnect.Organisation(\
queryParams[0],queryParams[1],queryParams[2],queryParams[3],\
queryParams[4],queryParams[5],queryParams[6],queryParams[7],\
queryParams[8],queryParams[9],queryParams[10],queryParams[11],\
queryParams[12],queryParams[13],queryParams[14],\
queryParams[15],queryParams[16],queryParams[17])\
])
Session.commit()
Session.close()
connection.connection.close()
return True
def xmlrpc_getorgTypeByname(self, queryParams, client_id):
'''
Purpose: Function for get Organisation Type for provided organisation
Querys the Organisation table and sees if an orgname
similar to one provided as a parameter.
if it exists then it will return orgtype related orgname
Input: queryParams[orgname(datatype:string)]
Output: orgtype if orgname match else eturn false string
'''
print queryParams[0]
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Organisation).\
filter(dbconnect.Organisation.orgname == queryParams[0]).\
first()
Session.close()
connection.connection.close()
if result == None:
return "0"
else:
return result.orgtype
def xmlrpc_hasProjectTransactions(self, queryParams, client_id):
"""
Purpose: Function to find out whether the given projectname
has any transactions or not
It will take projectname as a first parameter and
then getprojetcode to delete project
Input: queryParams[projectname(datatype:String)]
Output: It returns strig "1" when transaction with projectname
is present else return "0"
"""
transaction = rpc_transaction.transaction()
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
projectcode = transaction.xmlrpc_getProjectcodeByProjectName(
[queryParams[0]], client_id)
statement = "select count(vouchercode) as vouchercodeCount\
from view_voucherbook\
where projectcode ='" + str(projectcode) + "'"
result = dbconnect.engines[client_id].execute(statement).fetchone()
Session.close()
connection.connection.close()
if result[0] == 0:
return 0
if result[0] > 0:
return 1
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.
'''
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(func.count(dbconnect.Account.accountname)).\
filter((func.lower(dbconnect.Account.accountname)) == queryParams[0].lower()).\
scalar()
Session.commit()
Session.close()
connection.connection.close()
if result == 0:
return "0"
else:
return "1"
def xmlrpc_getAllGroups(self, client_id):
'''
purpose : function to get all groups present in the groups table
input parameters : client_id(datatype:integer) from client side
output : returns list containing group groupcode(datatype:integer),
groupname(datatype:text),groupdescription(datatype:text).
Description : Querys the Groups table.
It retrieves all rows of groups table based on groupname.
When successful it returns the list of lists ,
in which each list contain each row that are retrived from groups table
otherwise it returns false.
'''
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Groups).order_by(
dbconnect.Groups.groupname).all()
Session.close()
connection.connection.close()
#print result
if result == []:
return result
else:
grouplist = []
for i in range(0, len(result)):
grouplist.append([
result[i].groupcode, result[i].groupname,
result[i].groupdesc
])
#print grouplist
return grouplist
def xmlrpc_getLastLoginTiming(self,queryParams,client_id):
'''
* Purpose:
- function to get the last login timing of user
* Input:
- [username , userrole]
* Output:
- return time
'''
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Users).\
filter(dbconnect.Users.userrole == queryParams[0]).\
filter(dbconnect.Users.username == queryParams[1]).\
first()
Session.close()
connection.connection.close()
if result != None:
return result.login_time
else:
return []
def xmlrpc_setSubGroup(self, queryParams, client_id):
'''
Purpose :function for adding new subgroups in table subgroups
Parameters : groupname(datatype:text), subgroupname(datatype:text) , client_id (datatype:integer)
Returns : returns 1 when successful, 0 when subgroupname(datatype:text) is null
Description : Adds new subgroup to the database.
When successful it returns 1 otherwise it returns 0.
'''
queryParams = blankspace.remove_whitespaces(queryParams)
# call getGroupCodeByGroupName func to get groupcode
result = self.xmlrpc_getGroupCodeByGroupName([queryParams[0]],
client_id)
# call getSubGroupCodeBySubGroupName fun to get subgroupcode
#result = self.xmlrpc_getSubGroupCodeBySubGroupName([queryParams[1]],client_id)
if result != None:
group_code = result[0]
#print group_code
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
Session.add_all([dbconnect.subGroups(group_code, queryParams[1])])
Session.commit()
Session.close()
connection.connection.close()
if queryParams[1] == "null":
return "0"
else:
return "1"
else:
return []
def xmlrpc_AdminForgotPassword(self,queryParams,client_id): ''' * purpose: - this function is to check if userrole ``admin`` is pesent and provide access to organisation by cross checking security question and anwer provided in case of forgotten password. * Input: - [question , answer , userrole] * Output: - if ``admin`` then return ``true`` else retrun ``false`` ''' connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) role_exist = Session.query(dbconnect.Users).filter(dbconnect.Users.userrole == "admin").\ first() if role_exist != None: result = Session.query(dbconnect.Users).filter(dbconnect.Users.question == queryParams[0]).\ filter(dbconnect.Users.answer == queryParams[1]).\ filter(dbconnect.Users.userrole == queryParams[2]).first() Session.close() connection.connection.close() if result == None: return False else: return True
def xmlrpc_getUserNemeOfOperatorRole(self,client_id): ''' * Purpose: - It will provides list of all operator's present in user table * Output - it returns list of usernames ''' connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Users.username,(dbconnect.Users.login_time),(dbconnect.Users.logout_time)).\ filter(dbconnect.Users.userrole == "operator").all() Session.close() connection.connection.close() resultList = [] if result != []: for row in result: userlist = [] userlist.append(row.username) userlist.append(row.login_time) userlist.append(row.logout_time) resultList.append(userlist) print "user list" print resultList return resultList else: return result
def xmlrpc_setLoginLogoutTiming(self,queryParams,client_id):
'''
* Purpose:
- function to update login and logout timing of user
* Input:
- [username , userrole, login_time, logout_time]
* Output:
- return ``True``
'''
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
login_time = str(datetime.strptime(str(queryParams[2]),"%d-%m-%Y %H:%M:%S"))
logout_time = str(datetime.strptime(str(queryParams[3]),"%d-%m-%Y %H:%M:%S"))
#update
result = Session.query(dbconnect.Users).filter(dbconnect.Users.username == queryParams[0]).\
filter(dbconnect.Users.userrole == queryParams[1]).\
update({'login_time':login_time,'logout_time':logout_time})
Session.commit()
Session.close()
connection.connection.close()
return True
def xmlrpc_deleteProjectName(self, queryParams, client_id):
"""
Purpose: Function for deleting project.
For this we have used hasProjectTransactions
& deleteProject rpc functions.
With the help of hasProjectTransactions we are able to
find out whether the given project has any transactions or not.
deleteProject delete that particular projectname which has no transaction
Input: queryParams[projectname(datatype:String)]
Output: if hasTransaction is "0" then it returns string "project deleted"
"""
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
hasProjectTransactions = self.xmlrpc_hasProjectTransactions(
[str(queryParams[0])], client_id)
Session.close()
connection.connection.close()
if (str(hasProjectTransactions) == "0"):
self.xmlrpc_deleteProject([str(queryParams[0])], client_id)
return "project deleted"
elif (str(hasProjectTransactions) == "1"):
return "has transaction"
def xmlrpc_getAllAccountNames(self, client_id):
"""
Purpose: Function to get the list of all accountnames
in the database.
Output: returns the list of all acountnames.
else returns empty list
"""
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Account.accountname).\
order_by(dbconnect.Account.accountname).\
all()
Session.commit()
Session.close()
connection.connection.close()
accountnames = []
if result == None:
return []
else:
for row in result:
accountnames.append(row.accountname)
return accountnames
def xmlrpc_getorgTypeByname(self, queryParams, client_id): ''' * Purpose: - function for get Organisation Type for provided organisation - querys the Organisation table and sees if an orgname similar to one provided as a parameter. - if it exists then it will return orgtype related orgname * Input: - [orgname(datatype:string)] * Output: - returns orgtype if orgname match else return false string ''' queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Organisation).\ filter(dbconnect.Organisation.orgname == queryParams[0]).\ first() Session.close() connection.connection.close() print "getorgtype" print result.orgtype if result == None: return "0" else: return result.orgtype
def xmlrpc_hasTransactions(self, queryParams, client_id): """ * Purpose: - function to find out whether the given account has any transactions or not. * Input: - accountname as string. * Output: - if there is any voucher entry of that accountname return 1 or else return 0 """ connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) statement = 'select count(vouchercode) as vouchercodeCount\ from view_voucherbook\ where account_name ="'+str(queryParams[0])+'"' result = dbconnect.engines[client_id].execute(statement).fetchone() Session.close() connection.connection.close() if result[0] == 0: return 0 if result[0] > 0: return 1
def xmlrpc_isUserUnique(self,queryParams,client_id): ''' * Purpose: - this function to check the given user is unique - this function will be usefull when add new user so, it avoid duplicate username * Input: - [username] * Output: - if given username exist the return ``True`` else return ``False`` ''' queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Users).filter(dbconnect.Users.username == queryParams[0]).first() Session.close() connection.close() if result == None: return True else: return False
def xmlrpc_changeUserName(self,queryParams,client_id):
'''
* Purpose:
- It will facilitate user to change username
based on there old_username and password
* Input:
- [old_username,new_username,password,userrole]
* Output:
- return ``False`` if given user is not present with old_password,userrole
else it update username and return ``True``
'''
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
queryParams = blankspace.remove_whitespaces(queryParams)
password = blankspace.remove_whitespaces([queryParams[2].encode('base64').rstrip()])
result = Session.query(dbconnect.Users.userid).filter(dbconnect.Users.username == queryParams[0]).\
filter(dbconnect.Users.userpassword == password[0]).\
filter(dbconnect.Users.userrole == queryParams[3]).first()
if result == None:
Session.close()
connection.connection.close()
return False
else:
result = Session.query(dbconnect.Users).filter(dbconnect.Users.userid == result.userid).\
update({'username':queryParams[1]})
Session.commit()
Session.close()
connection.connection.close()
return True
def xmlrpc_isUserExist(self,queryParams,client_id):
'''
* Purpose:
- function to check for valid password and userrole and
username
* Input:
- [username , password , userrole]
* Output:
- if username, password and userole is valid then
return ``True`` else return ``False``
'''
queryParams = blankspace.remove_whitespaces(queryParams)
print queryParams
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
password = blankspace.remove_whitespaces([queryParams[1].encode('base64').rstrip()])
result = Session.query(dbconnect.Users).filter(dbconnect.Users.username == queryParams[0]).\
filter(dbconnect.Users.userpassword == password[0]).\
filter(dbconnect.Users.userrole == queryParams[2]).first()
Session.close()
connection.connection.close()
if result == None:
return False
else:
return True
def xmlrpc_getSubGroupCodeByAccountName(self,queryParams,client_id): ''' * Purpose: - function for extracting subgroup code of group based on accountname - query the account table to retrive subgroupcode for reqested accountname * Input: - accountname(datatype:text),client_id(datatype:integer) * Output: - returns list containing subgroupcode if its not None else will return false. ''' queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Account).\ filter(dbconnect.Account.accountname == queryParams[0]).\ first() Session.close() connection.connection.close() if result != None: return [result.subgroupcode] else: return []
def xmlrpc_updateOrg(self,queryParams,client_id):
'''
* Purpose:
- updating the orgdetails after edit organisation
* Input:
- [orgcode,orgaddress,orgcountry,orgstate,orgcity,orgpincode,orgtelno,orgfax,orgemail,
orgwebsite,orgmvat,orgstax,orgregno,orgregdate,orgfcrano,orgfcradate,orgpan],client_id
* Output:
- It will returns String "upadted successfully"
'''
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Organisation).\
filter(dbconnect.Organisation.orgcode == queryParams[0]).\
update({'orgaddr': queryParams[1],'orgcountry':queryParams[2],'orgstate':queryParams[3],\
'orgcity': queryParams[4],'orgpincode':queryParams[5],'orgtelno':queryParams[6],\
'orgfax':queryParams[7],'orgemail':queryParams[8],'orgwebsite':queryParams[9],\
'orgmvat':queryParams[10],'orgstax':queryParams[11],'orgregno':queryParams[12],\
'orgregdate':queryParams[13],'orgfcrano':queryParams[14],'orgfcradate':queryParams[15],\
'orgpan':queryParams[16]})
Session.commit()
Session.close()
connection.connection.close()
return "upadted successfully"
def xmlrpc_getOrganisation(self,client_id): """ * Purpose: - function to get all the details of organisation from database * Input: - client_id * Output: - return list of organisation details """ connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Organisation).all() Session.close() connection.connection.close() if result == []: return result else: orgdetail_list = [] for l in result: orgdetail_list.append([\ l.orgcode,l.orgtype,l.orgname,l.orgaddr,\ l.orgcity,l.orgpincode,l.orgstate,l.orgcountry,\ l.orgtelno,l.orgfax,l.orgwebsite,l.orgemail,\ l.orgpan,l.orgmvat,l.orgstax,l.orgregno,\ l.orgregdate,l.orgfcrano,l.orgfcradate\ ]) return orgdetail_list
def xmlrpc_getPreferences(self,queryParams,client_id): """ * Purpose: - finding the appropriate preferences for accountcode for given flag no - if flag no is ``2`` then will return accountcode flag value. - if flag no is ``1`` then will return rollover flag value * Input: - [flagno] * Output: - It returns list of flagname and set falg depend on flagno - set flag is set to make it one time activity. """ queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Flags).\ filter(dbconnect.Flags.flagno == queryParams[0]).\ first() Session.close() connection.connection.close() if result == []: return result else: print [result.flagname,result.set_flag] return [result.flagname,result.set_flag]
def xmlrpc_getAccountNameByAccountCode(self, queryParams, client_id): """ * Purpose: - function to get accountname provided the accountcode - querys the account table and sees if an acountcode similar to one provided as a parameter. - if it exists then it will return accountname * Input: - [accountcode] * Output: - return accountname if present else empty list """ connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Account.accountname).\ filter(dbconnect.Account.accountcode == queryParams[0]).\ first() Session.close() connection.connection.close() if result == None: return [] else: return result[0]
def xmlrpc_getAccountNamesByGroupCode(self, queryParams, client_id):
"""
Purpose: To get accountname accourding to given groupcode
Input: [groupcode]
Output: it will return list of accountname else return empty list
"""
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Account.accountname).\
filter(dbconnect.Account.groupcode == queryParams[0]).\
order_by(dbconnect.Account.accountname).all()
Session.commit()
Session.close()
connection.connection.close()
accountnames = []
if result == []:
return result
else:
for account in result:
accountnames.append(str(account[0]))
return accountnames
def xmlrpc_getAllAccountCodes(self,client_id): """ * Purpose: - it will return list of all accountcodes present in the account table. * Input: - no input argument * Output: - returns the list of all accountcode else it returns empty list. """ connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Account.accountcode).\ order_by(dbconnect.Account.accountcode).\ all() Session.commit() Session.close() connection.connection.close() accountcodes = [] if result == None: return [] else: for row in result: accountcodes.append(row.accountcode) return accountcodes
def xmlrpc_getProjectcodeByProjectName(self,queryParams,client_id): """ * Purpose: - function to get projectcode acouding to projectname * Input: - it will take only one input projectname * Output: - it will return projectcode if projectname match else returns 0 """ # execute here connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Projects.projectcode).\ filter(dbconnect.Projects.projectname == queryParams[0]).first() Session.close() connection.connection.close() if result == None: return 0 else: projectCode = result[0] return projectCode
def xmlrpc_getAccountCodeByAccountName(self, queryParams, client_id): """ * Purpose: - Function for get an accountcode for given accountname. * Input: - queryParams which is a list containing one element, accountname as string. * Output: - returns accountcode if it exist for given accountname else returns empty list """ connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Account.accountcode).\ filter(dbconnect.Account.accountname == queryParams[0]).\ first() Session.commit() Session.close() connection.connection.close() if result == None: return [] else: return result[0]
def xmlrpc_getOnlyClearedTransactions(self,queryParams,client_id): """ * Purpose: - This function will check for cleared transactions * Input: - [accountname,vouchercode,financialstart,todate] * Output: - if transaction is exist in bankrecon table - it will return boolean True else False """ from_date = str(datetime.strptime(str(queryParams[2]),"%d-%m-%Y")) to_date = str(datetime.strptime(str(queryParams[3]),"%d-%m-%Y")) accObj = rpc_account.account() accountcode = accObj.xmlrpc_getAccountCodeByAccountName([queryParams[0]], client_id) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.BankRecon).filter(and_(dbconnect.BankRecon.accountcode==accountcode,\ dbconnect.BankRecon.vouchercode==queryParams[1],\ dbconnect.BankRecon.clearancedate >= from_date,\ dbconnect.BankRecon.clearancedate <= to_date)).\ first() Session.close() connection.connection.close() if result != None: return True else: return False
def xmlrpc_chequeNoExist(self, queryParams, client_id): """ * Purpose: - Function for finding if an cheque_no already exists with the supplied code. * Input: - cheque_no (datatype:string) * Output: - return "1" if cheque_no exists and "0" if not. """ queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(func.count(dbconnect.VoucherMaster.cheque_no)).\ filter(dbconnect.VoucherMaster.cheque_no == queryParams[0]).\ scalar() Session.close() connection.connection.close() if result == 0: return "0" else: return "1"
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_deleteVoucher(self,queryParams,client_id):
"""
* Purpose:
- This function will not completely delete voucherdetails
but it will set the flag 0 instead 1
- so it will be like disabled for search voucher
* Input:
- [voucherno]
* Output:
- returns boolean True if deleted else False
"""
queryParams = blankspace.remove_whitespaces(queryParams)
try:
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
Session.query(dbconnect.VoucherMaster).\
filter(dbconnect.VoucherMaster.vouchercode == queryParams[0]).\
update({'flag':0})
Session.commit()
Session.close()
connection.connection.close()
return True
except:
return False
def xmlrpc_setOrganisation(self,queryParams,client_id): """ * Purpose: - function for add organisation details in database * Input: - if orgtype is ``NGO`` then [orgname,orgtype,orgcountry,orgstate,orgcity,orgaddr,orgpincode, orgtelno, orgfax, orgwebsite, orgemail, orgpan, "", "", orgregno, orgregdate, orgfcrano, orgfcradate] - else: [orgname,orgtype,orgcountry,orgstate,orgcity,orgaddr,orgpincode, orgtelno, orgfax, orgwebsite, orgemail, orgpan,orgmvat,orgstax, "", "", "", ""] * Output: - returns boolean True if added successfully else False """ queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) Session.add_all([\ dbconnect.Organisation(\ queryParams[0],queryParams[1],queryParams[2],queryParams[3],\ queryParams[4],queryParams[5],queryParams[6],queryParams[7],\ queryParams[8],queryParams[9],queryParams[10],queryParams[11],\ queryParams[12],queryParams[13],queryParams[14],\ queryParams[15],queryParams[16],queryParams[17])\ ]) Session.commit() Session.close() connection.connection.close() return True
def xmlrpc_accountExists(self, queryParams, client_id): """ * Purpose: - function for finding if an account already exists with the supplied name. - queryParams which is a list containing one element, accountname as string. - 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. * Output: - if account name exists returns 1 else 0 . """ queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(func.count(dbconnect.Account.accountname)).\ filter((func.lower(dbconnect.Account.accountname)) == queryParams[0].lower()).\ scalar() Session.commit() Session.close() connection.connection.close() if result == 0: return "0" else: return "1"
def xmlrpc_getAccountNamesByGroupCode(self,queryParams,client_id): """ * Purpose: - to get accountname accourding to given groupcode. - it query to the ``Account`` tables. * Input: - [groupcode] * Output: - it will return list of accountname else return empty list """ connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Account.accountname).\ filter(dbconnect.Account.groupcode == queryParams[0]).\ order_by(dbconnect.Account.accountname).all() Session.commit() Session.close() connection.connection.close() accountnames = [] if result == []: return result else: for account in result: accountnames.append(str(account[0])) return accountnames
def xmlrpc_getAllGroups(self,client_id): ''' * Purpose: - function to get all groups present in the groups table - querys the ``Groups`` table,it retrieves all rows of groups table based on groupname. - when successful it returns the list of lists - list contain each row that are retrived from groups table * Input: - client_id(datatype:integer) from client side. * Output: - returns list containing groupcode(datatype:integer),groupname(datatype:text),groupdescription(datatype:text). ''' connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Groups).order_by(dbconnect.Groups.groupname).all() Session.close() connection.connection.close() #print result if result == []: return result else: grouplist = [] for i in range(0,len(result)): grouplist.append([result[i].groupcode, result[i].groupname, result[i].groupdesc]) #print grouplist return grouplist
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. - The function takes queryParams as its parameter and contains one element, the subgroupname as string. * Input: - subgroupname(datatype:text) * Output: - returns ``1`` if the subgroup exists else ``0``. ''' queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(func.count(dbconnect.subGroups.subgroupname)).\ filter((func.lower(dbconnect.subGroups.subgroupname)) == queryParams[0].lower()).scalar() Session.close() connection.connection.close() if result == 0: return "0" else: return "1"
def xmlrpc_setSubGroup(self,queryParams,client_id):
'''
* Purpose:
- used ``subGroups`` table to query .
- function for adding new subgroups in table subgroups
* Input:
- groupname(datatype:text), subgroupname(datatype:text) , client_id (datatype:integer)
* Output:
- returns 1 when successful, 0 when subgroupname(datatype:text) is null
- When successful it returns 1 otherwise it returns 0.
'''
queryParams = blankspace.remove_whitespaces(queryParams)
# call getGroupCodeByGroupName func to get groupcode
result = self.xmlrpc_getGroupCodeByGroupName([queryParams[0]],client_id)
# call getSubGroupCodeBySubGroupName fun to get subgroupcode
#result = self.xmlrpc_getSubGroupCodeBySubGroupName([queryParams[1]],client_id)
if result != None:
group_code = result[0]
#print group_code
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
Session.add_all([dbconnect.subGroups(group_code,queryParams[1])])
Session.commit()
Session.close()
connection.connection.close()
if queryParams[1]=="null":
return "0"
else:
return "1"
else:
return []
def xmlrpc_getAllAccountNamesOrderByDate(self, client_id): """ * Purpose: - Function to get the list of all accountnames in the database order by date. * Output: - returns the list of all acountnameselse returns empty list """ connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(dbconnect.Account.accountname).\ all() Session.commit() Session.close() connection.connection.close() accountnames = [] if result == None: return [] else: for row in result: accountnames.append(row.accountname) return accountnames
def xmlrpc_accountCodeExists(self, queryParams, client_id): """ * Purpose: - Function for finding if an accountcode already exists with the supplied code. * Input: - accountode(datatype:string) * Output: - return "1" if accountcode exists and "0" if not. """ queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) result = Session.query(func.count(dbconnect.Account.accountcode)).\ filter((func.lower(dbconnect.Account.accountcode)) == queryParams[0].lower()).\ scalar() Session.close() connection.connection.close() if result == 0: return "0" else: return "1"
def xmlrpc_getGroupCodeByName(self, queryParams, client_id):
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
res = Session.query(dbconnect.Groups.groupcode).filter(
dbconnect.Groups.groupname == queryParams[0]).first()
Session.close()
connection.connection.close()
if res != None:
return res
else:
return False
def xmlrpc_editAccount(self, queryParams, client_id):
"""
Purpose: Modifies an account based on account code.
alters account name and opening balance.
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.
Input: [accountname, accountcode, groupname and new_opening_balance]
Output: [Current_balance]
"""
queryParams = blankspace.remove_whitespaces(queryParams)
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(float(queryParams[3]))
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Account).\
filter(dbconnect.Account.accountcode == spQueryParams[1]).first()
resultParams = [float(result.openingbalance), float(result.balance)]
if resultParams[0] == spQueryParams[2]:
result = Session.query(dbconnect.Account).\
filter(dbconnect.Account.accountcode == spQueryParams[1]).\
update({'accountname': spQueryParams[0]})
else:
final_balance = (spQueryParams[2] -
resultParams[0]) + resultParams[1]
result = Session.query(dbconnect.Account).\
filter(dbconnect.Account.accountcode == spQueryParams[1]).\
update({'accountname': spQueryParams[0],'openingbalance': spQueryParams[2],'balance': final_balance})
Session.commit()
Session.close()
connection.connection.close()
return final_balance
def xmlrpc_getGroupNameByAccountName(self, queryParams, client_id):
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
res = Session.query(dbconnect.Groups).select_from(
join(dbconnect.Groups, dbconnect.Account)).filter(
dbconnect.Account.accountname == queryParams[0]).first()
Session.close()
connection.connection.close()
if res != None:
return [res.groupname]
else:
return False
def xmlrpc_deleteAccountNameMaster(self, queryParams, client_id):
'''
Purpose: Function for deleting accounts.
For this we have used hasOpeningBalance,hasTransactions &
deleteAccount rpc functions.
With the help of hasTransactions we are able to find out whether
the given account has any transactions or not.
It tells that if there is any voucher entry of that accountname
return 1 or else return 0
The function hasOpeningBalance returns 1 if opening balance
for that account exists or else returns 0
and third function deleteAccount deletes that particular accountname
Input: [accountname,flag] or [accountcode,flag]
Output: if hasOpenibalance is 0 and hasTransaction is 0
returns string "account deleted"
if hasOpenibalance is 1 and hasTransaction is 1
returns string "has both opening balance and trasaction"
'''
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
#if flag is 1, that means first element is account name
if queryParams[1] == 1:
accName = str(queryParams[0])
hasOpeningBalance = self.xmlrpc_hasOpeningBalance([accName],
client_id)
hasTransactions = self.xmlrpc_hasTransactions([accName], client_id)
else:
#if flag is 2, that means first element is account code,
#we have to get accountname by accountcode
accName = self.xmlrpc_getAccountNameByAccountCode(
[str(queryParams[0])], client_id)
hasOpeningBalance = self.xmlrpc_hasOpeningBalance([accName],
client_id)
hasTransactions = self.xmlrpc_hasTransactions([accName], client_id)
Session.close()
connection.connection.close()
if (str(hasOpeningBalance) == "0" and str(hasTransactions) == "0"):
self.xmlrpc_deleteAccount([accName], client_id)
return "account deleted"
elif (str(hasOpeningBalance) == "1" and str(hasTransactions) == "1"):
return "has both opening balance and trasaction"
elif (str(hasOpeningBalance) == "1"):
return "has opening balance"
elif (str(hasTransactions) == "1"):
return "has transaction"
def xmlrpc_getCategories(self,client_id): connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) res = Session.query(dbconnect.CategoryMaster).all() Session.close() if res == []: return False else: res1 = [] i = 0 for i in range(0,len(res)): res1.append([res[i].categorycode, res[i].categoryname, res[i].hint]) return res1
def xmlrpc_getCategorySpecs(self,queryParams,client_id): connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) res = Session.query(dbconnect.CategoryDetails.attrname,dbconnect.CategoryDetails.attrtype,dbconnect.CategoryDetails.attrisnull).filter(dbconnect.CategoryDetails.categorycode == queryParams[0]).all() Session.close() if res == []: return False else: res1 = [] i = 0 for i in range(0,len(res)): res1.append([res[i].attrname, res[i].attrtype, res[i].attrisnull]) return res1
def xmlrpc_getCategory(self,queryParams,client_id): connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) if queryParams[0] == 'code': res = Session.query(dbconnect.ProductMaster.categorycode).filter(dbconnect.ProductMaster.prodcode == queryParams[1]).order_by(dbconnect.ProductMaster.prodcode).first() else: res = Session.query(dbconnect.ProductMaster.categorycode).filter(dbconnect.ProductMaster.prodname == queryParams[1]).order_by(dbconnect.ProductMaster.prodcode).first() result = Session.query(dbconnect.CategoryMaster.categoryname).filter(dbconnect.CategoryMaster.categorycode == res.categorycode).first() Session.close() if result != None: return [res.categorycode, result.categoryname] else: return False
def parseData(sx, addr, data):
opcode, ts, identity, verif, encrypted, actdata = pack.unpack_json(data)
if encrypted:
query = session.query(identity=identity).first()
if query:
opcode, ts, identity, verif, encrypted, actdata = pack.unpack(data, key=query.key)
operation=None
for op, code in pack.opcode_mapping.items():
if code==opcode:
operation=op
if operation=='register':
_key=secrets.token_hex(16).encode()
key = base64.b64encode(_key).decode()
identity = secrets.token_hex(16)
keycollection(rand=actdata['rand'], key=key).save()
connection_send(sx,addr,pack.pack_json('success',{
'message':'Identity created',
'identity': identity
}, encrypted=True, key=_key))
elif operation=='tokenexchange':
session(identity = identity, token = actdata['token'], key = query.key).save()
def xmlrpc_setProjects(self, queryParams, client_id):
"""
Purpose: Function for set projects for a particular
organisation
Input: queryParams[projectname(datatype:text)]
Output: Returns boolean true if projectname added
"""
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
Session.add_all([dbconnect.Projects(None, queryParams[0])])
Session.commit()
return True
def xmlrpc_getSubGroupByName(self, queryParams, client_id):
'''
Purpose :function for extracting data from subgroup table by namewise
i/p parameters : subgroupname
o/p parameters :subgroupcode
'''
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
res = Session.query(dbconnect.subGroups).filter(
dbconnect.subGroups.subgroupname == queryParams[0]).order_by(
dbconnect.subGroups.groupcode).first()
Session.close()
connection.connection.close()
if res != None:
return res.subgroupcode
else:
return False
def xmlrpc_deleteProject(self, queryParams, client_id):
"""
Purpose: Function for deleting project name
Input: queryParams[projectname(datatype:String)]
Output: returns 1 String , when project is deleted
"""
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Projects).\
filter(dbconnect.Projects.projectname == queryParams[0]).\
delete()
Session.commit()
Session.close()
connection.connection.close()
return "1"
def xmlrpc_deleteAccount(self, queryParams, client_id):
'''
Purpose: Function for deleting accountname row
Input: accountname as string.
Output: returns 1 when account is deleted
'''
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Account).\
filter(dbconnect.Account.accountname == queryParams[0]).\
delete()
Session.commit()
Session.close()
connection.connection.close()
return "1"
def xmlrpc_getGroupNameByAccountName(self, queryParams, client_id):
'''
xmlrpc_getGroupNameByAccountName :purpose
function for extracting groupname from group table by account name
i/p parameters : accountname
o/p parameters : groupname
'''
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Groups).select_from(join(dbconnect.Groups,dbconnect.Account)).\
filter(and_(dbconnect.Account.accountname == queryParams[0],\
dbconnect.Groups.groupcode == dbconnect.Account.groupcode)).\
first()
Session.close()
connection.connection.close()
if result != None:
return [result.groupname]
else:
return []
def xmlrpc_getSubGroupCodeBySubGroupName(self, queryParams, client_id):
'''
purpose: function for extracting subgroupcpde of group based on subgroupname
input parameters : subgroupname(datatype:text) , client_id(datatype:integer)
output : returns list containing subgroupcode if its not None else will return false.
Description : query the subgroup table to retrive subgroupcode for reuested subgroupname
'''
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.subGroups).\
filter(dbconnect.subGroups.subgroupname == queryParams[0]).\
first()
Session.close()
connection.connection.close()
if result != None:
return [result.subgroupcode]
else:
return []
def xmlrpc_setCategoryMaster(self,queryParams_Master, queryParams_Details,client_id):
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
Session.add_all([dbconnect.CategoryMaster(queryParams_Master[0],queryParams_Master[1])])
Session.commit()
lstCategoryName = [queryParams_Master[0]]
res = Session.query(dbconnect.CategoryMaster.categorycode).filter(dbconnect.CategoryMaster.categoryname == lstCategoryName[0]).first()
for row in queryParams_Details:
insertRow = [res.categorycode] + row
category = Session.query(dbconnect.CategoryDetails).all()
for i in category:
if i.attrname == insertRow[1]:
Session.add_all([dbconnect.CategoryDetails(insertRow[0],insertRow[1],insertRow[2],insertRow[3])])
Session.commit()
return True
dbconnect.engines[client_id].execute("alter table productmaster add column "+insertRow[1]+" text")
Session.add_all([dbconnect.CategoryDetails(insertRow[0],insertRow[1],insertRow[2],insertRow[3])])
Session.commit()
Session.close()
return "insert ok"
def xmlrpc_hasOpeningBalance(self, queryParams, client_id):
'''
Purpose: Function to find out whether the given account
has opening balance
Input: accountname(datatype:string)
Output: if opening balance of accountname is
0 then return "0" else return "1"
'''
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Account.openingbalance).\
filter(dbconnect.Account.accountname == queryParams[0]).\
scalar()
Session.close()
connection.connection.close()
if result == 0:
return "0"
else:
return "1"