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_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_Import(self,queryParams):
"""
* Purpose:
- import database from /export directory to /ABT/abt/db
and write org tags in /ABT/abt/abt.xml file
* Input:
- qureyParams:[organisationName,financialFrom,financialTo,database,rollover_flag]
* Output:
- import database and write org tags in xml file
"""
#print "queryParams"
#writting to xml file
count = self.xmlrpc_writeToXmlFile(queryParams,"/ABT/abt/abt.xml");
if(count != 0):
#print "deleting the existing database"
os.unlink("/ABT/abt/db/"+queryParams[3])
#print "import"
#encrypt the database name
encrypted_db = blankspace.remove_whitespaces([queryParams[3].encode('base64').rstrip()])
#adding database with all data in db folder
#os.chdir("/ABT/abt/export/")
connection = dbconnect.engines[self.client_id].raw_connection()
self.restore_db(connection, db_file="C:/ABT/abt/db/"+queryParams[3], filename= "C:/ABT/abt/export/"+encrypted_db[0]+".sql")
#os.system("sqlite3 /ABT/abt/db/"+queryParams[3]+"< /ABT/abt/export/"+encrypted_db[0])
if os.path.exists("/ABT/abt/export/"):
os.system("adb -e push C:/ABT/abt/export /mnt/sdcard/export/")
else:
print"No file found"
return "success"
def xmlrpc_editProject(self, queryParams, client_id):
"""
* Purpose:
- function for edit projectname
- it will alter projectname ans update it.
* Input:
- [projectcode,projectname]
* Output:
- return string ``updated successfully``
"""
queryParams = blankspace.remove_whitespaces(queryParams)
transaction = rpc_transaction.transaction()
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Projects).\
filter(dbconnect.Projects.projectcode == queryParams[0]).\
update({'projectname': queryParams[1]})
Session.commit()
Session.close()
connection.connection.close()
return "upadted successfully"
def xmlrpc_getCityNames(self, queryParams):
"""
purpose: This is function to get city list
present in the "/opt/abt/places.db"
with its respective state name
Input: [statename]
Output: return list of all the city names
present in "/opt/abt/places.db"
"""
try:
conn = sqlite.connect("/opt/abt/places.db")
cur = conn.cursor()
result = cur.execute(
"select city from state_city where state = '%s'" %
str(queryParams[0]))
rows = result.fetchall()
cities = []
for row in rows:
cities.append(row[0])
citieslist = blankspace.remove_whitespaces(cities)
return citieslist
except:
return []
def xmlrpc_getSubGroupsByGroupName(self, queryParams, client_id):
'''
Purpose :function for extracting all rows of view_group_subgroup based on groupname
Parameters : QueryParams, list containing groupname(datatype:text)
Returns : List of all subgroups when successful, else list containing strings
Description : Querys the view_group_subgroup which is created based on the account ,subgroups and groups table.
It retrieves all rows of view_group_subgroup 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 list in which two default subgroup strings.
'''
queryParams = blankspace.remove_whitespaces(queryParams)
statement = "select subgroupname\
from view_group_subgroup\
where groupname ='" + queryParams[0] + "'\
order by subgroupname"
result = dbconnect.engines[client_id].execute(statement).fetchall()
subgrouplist = []
if result == []:
subgrouplist.append("No Sub-Group")
subgrouplist.append("Create New Sub-Group")
#print subgrp
return subgrouplist
else:
subgrouplist.append("No Sub-Group")
for l in range(0, len(result)):
subgrouplist.append(result[l][0])
subgrouplist.append("Create New Sub-Group")
#print subgrp
return subgrouplist
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_getSubGroupsByGroupName(self,queryParams,client_id):
'''
Purpose :function for extracting all rows of view_group_subgroup based on groupname
Parameters : QueryParams, list containing groupname(datatype:text)
Returns : List of all subgroups when successful, else list containing strings
Description : Querys the view_group_subgroup which is created based on the account ,subgroups and groups table.
It retrieves all rows of view_group_subgroup 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 list in which two default subgroup strings.
'''
queryParams = blankspace.remove_whitespaces(queryParams)
statement = "select subgroupname\
from view_group_subgroup\
where groupname ='"+queryParams[0]+"'\
order by subgroupname"
result=dbconnect.engines[client_id].execute(statement).fetchall()
subgrouplist = []
if result == []:
subgrouplist.append("No Sub-Group")
subgrouplist.append("Create New Sub-Group")
#print subgrp
return subgrouplist
else:
subgrouplist.append("No Sub-Group")
for l in range(0,len(result)):
subgrouplist.append(result[l][0])
subgrouplist.append("Create New Sub-Group")
#print subgrp
return subgrouplist
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_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_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_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_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_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_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_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_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_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_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_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_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_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_getVoucherDetails(self,queryParams,client_id): """ purpose: gets the transaction related details given a vouchercode. ''' Input parameters : [voucher_code] ''' returns 2 dimentional list containing rows with 3 columns. takes one parameter vouchercode+ ''' Output Parameters : [accountname,typeflag,amount] ''' """ queryParams = blankspace.remove_whitespaces(queryParams) statement = "select account_name,typeflag,amount\ from view_voucherbook\ where vouchercode = '"+str(queryParams[0])+"'\ and flag = 1 " result = dbconnect.engines[client_id].execute(statement).fetchall() voucherdetails = [] if result == None: return [] else: for row in result: voucherdetails.append([row[0],row[1],'%.2f'%float(row[2])]) print voucherdetails return voucherdetails
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_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_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_Import(self,queryParams):
"""
* Purpose:
- import database from /export directory to /opt/abt/db
and write org tags in /opt/abt/abt.xml file
* Input:
- qureyParams:[organisationName,financialFrom,financialTo,database,rollover_flag]
* Output:
- import database and write org tags in xml file
"""
print queryParams
#writting to xml file
count = self.xmlrpc_writeToXmlFile(queryParams,"/opt/abt/abt.xml");
if(count != 0):
print "deleting the existing database"
os.system("rm -rf /opt/abt/db/"+queryParams[3])
#encrypt the database name
encrypted_db = blankspace.remove_whitespaces([queryParams[3].rstrip()])
#adding database with all data in db folder
os.system("sqlite3 /opt/abt/db/"+queryParams[3]+"< /opt/abt/export/"+encrypted_db[0])
if os.path.exists("$HOME/export/"):
os.system("/opt/abt/adb -e push $HOME/export/ /mnt/sdcard/export/")
else:
print"No file found"
return "success"
def xmlrpc_getVoucherDetails(self,queryParams,client_id): """ * Purpose: - gets the transaction related details given a vouchercode. * Input: - [voucherno] * Output: - returns 2 dimentional list containing rows with 3 columns. - [accountname,typeflag,amount]e,typeflag,amount] """ queryParams = blankspace.remove_whitespaces(queryParams) statement = 'select account_name,typeflag,amount,cheque_no\ from view_voucherbook\ where vouchercode = "'+str(queryParams[0])+'"\ and flag = 1 ' result = dbconnect.engines[client_id].execute(statement).fetchall() voucherdetails = [] if result == None: return [] else: for row in result: if row[3] == None: voucherdetails.append([row[0],row[1],'%.2f'%float(row[2]),""]) else: voucherdetails.append([row[0],row[1],'%.2f'%float(row[2]),row[3]]) return voucherdetails
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_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_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_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_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_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_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_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. This function is used to create a new voucher. The entire transaction is recorded in terms of Dr and Cr and the respected amounts. The function call 3 funtions from same file rpc_transation.py 1 . xmlrpc_getProjectcodeByProjectName 2 . xmlrpc_setVoucherMaster 3 . xmlrpc_setVoucherDetails and call 1 function from rpc_account.py "to get accountcode by accountname" 1 . xmlrpc_getAccountCodeByAccountName queryParams_master list will contain : * reference Number * the actual transaction date * Voucher type * project name * Narration queryParams_details list will contain : * DrCr flag, * AccountName * the amount for the respective account. The function returns "success" . """ queryParams_master = blankspace.remove_whitespaces(queryParams_master) print "queryParams_master" print queryParams_master projectcode = self.xmlrpc_getProjectcodeByProjectName([queryParams_master[3]],client_id) params_master = [queryParams_master[0],queryParams_master[1],queryParams_master[2],projectcode,queryParams_master[4]] print "params master" print params_master vouchercode = self.xmlrpc_setVoucherMaster(params_master,client_id) print "query for masters is successful and voucher code is " + str(vouchercode) for detailRow in queryParams_details: queryParams_details = blankspace.remove_whitespaces(detailRow) account = rpc_account.account(); accountcode = account.xmlrpc_getAccountCodeByAccountName([detailRow[1]],client_id); params_details = [vouchercode,str(detailRow[0]),str(accountcode),float(detailRow[2])] self.xmlrpc_setVoucherDetails(params_details,client_id) return 1
def xmlrpc_getTransactions(self,queryParams,client_id): """ * Purpose: - get voucher details from the database given input parameters - it will chech for Project exist or not - if 'No Project' then + it will query to ``view_voucherbook`` view in (rpc.main) + gives the details of transactions which is under 'No Project' - else + it will query to ``view_voucherbook`` view in (rpc.main) + gives the details of transactions which is under given project name. - it will call ``xmlrpc_getProjectcodeByProjectName`` from same file ``rpc_transation.py`` to get projectcode for given projectname * Input: - [accountname,from_date,to_date,projectname] * Output: - [voucherno , voucherflag , reff_date , voucher_reference, transaction_amount,show_narration,cheque_no] """ queryParams = blankspace.remove_whitespaces(queryParams) from_date = str(datetime.strptime(str(queryParams[1]),"%d-%m-%Y")) to_date = str(datetime.strptime(str(queryParams[2]),"%d-%m-%Y")) if queryParams[3] == 'No Project': statement = 'select vouchercode,typeflag,reffdate,reference,amount,narration,cheque_no\ from view_voucherbook\ where account_name = "'+queryParams[0]+'"\ and reffdate >= "'+from_date+'"\ and reffdate <= "'+to_date+'"\ and flag = 1\ order by reffdate' result = dbconnect.engines[client_id].execute(statement).fetchall() else: project_code = self.xmlrpc_getProjectcodeByProjectName([str(queryParams[3])],client_id) statement = 'select vouchercode, typeflag ,reffdate,reference,amount,narration,cheque_no\ from view_voucherbook\ where account_name = "'+queryParams[0]+'"\ and projectcode = "'+str(project_code)+'"\ and reffdate >= "'+from_date +'"\ and reffdate <= "'+to_date+'"\ and flag = 1\ order by reffdate' result = dbconnect.engines[client_id].execute(statement).fetchall() transactionlist = [] for row in result: if row[6] == None: transactionlist.append([row[0],row[1],row[2],row[3],'%.2f'%(row[4]),row[5],""]) else: transactionlist.append([row[0],row[1],row[2],row[3],'%.2f'%(row[4]),row[5],row[6]]) return transactionlist
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 string
- 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:
- returns string ``edit successfully``
"""
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)]
if resultParams[0] == spQueryParams[2]:
result = Session.query(dbconnect.Account).\
filter(dbconnect.Account.accountcode == spQueryParams[1]).\
update({'accountname': spQueryParams[0]})
else:
#SSfinal_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]})
Session.commit()
Session.close()
connection.connection.close()
return "edit successfully"
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_setUser(self,queryParams,client_id):
"""
* Purpose:
- This function is to add the user details
- it will add all the input details in the user
table present in the ``dbconnect.py``
* Input:
-[firstname,lastname,username,password,gender,userrole,question,answer]
"""
queryParams = blankspace.remove_whitespaces(queryParams)
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
username = queryParams[0]
password = blankspace.remove_whitespaces([queryParams[1].encode('base64').rstrip()])
user_role = queryParams[3]
Session.add(dbconnect.Users(\
username,password[0],queryParams[2],user_role,queryParams[4],queryParams[5],"",""))
Session.commit()
print "sign up"
return "Sign up sccessfull"
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_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_subgroupExists(self, queryParams, client_id):
'''
purpose: Checks if the new subgroup typed by the user already exists.
input parameters : subgroupname(datatype:text)
output : Returns True if the subgroup exists and False otherwise
Description: This will validate and prevent any duplication.
The function takes queryParams as its parameter and contains one element, the subgroupname as string.
'''
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()
print "subgroup exist"
print result
if result == 0:
return "0"
else:
return "1"
def xmlrpc_editProject(self, queryParams, client_id):
"""
Purpose: function for edit projectname
Input: queryParams[projectcode,projectname]
Output: Return string when it updated successfully
"""
queryParams = blankspace.remove_whitespaces(queryParams)
transaction = rpc_transaction.transaction()
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
result = Session.query(dbconnect.Projects).\
filter(dbconnect.Projects.projectcode == queryParams[0]).\
update({'projectname': queryParams[1]})
Session.commit()
Session.close()
connection.connection.close()
return "upadted successfully"
def xmlrpc_getSuggestedCode(self, queryParams, client_id):
"""
purpose: To get code on the basis of provided 3 characters at list
queryParams[0]
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.
Input: first two charector of groupname and first charecto of the accountname
returns a string containing the suggested code.
"""
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
queryParams = blankspace.remove_whitespaces(queryParams)
SuggestedAccountCode = Session.query(func.count(dbconnect.Account.accountcode)).\
filter(dbconnect.Account.accountcode.like(str(queryParams[0])+'%')).scalar()
if SuggestedAccountCode == 0:
return str(queryParams[0] + "100")
else:
SuggestedAccount = SuggestedAccountCode + 100
return str(queryParams[0]) + str(SuggestedAccount)
Session.commit()
Session.close()
connection.connection.close()
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_getUser(self,queryParams,client_id): ''' def xmlrpc_getUser(self,queryParams,client_id): purpose It will provide information of user based on username and password return list containing useename , userrole if condition is true else return false ''' queryParams = blankspace.remove_whitespaces(queryParams) connection = dbconnect.engines[client_id].connect() Session = dbconnect.session(bind=connection) res = Session.query(dbconnect.Users).\ filter(dbconnect.Users.username == queryParams[0]).\ filter(dbconnect.Users.userpassword == queryParams[1]).\ first() Session.close() connection.connection.close() if res != None: dbconnect.addUser(client_id,queryParams[0]) lst=[res.username, res.userrole] print lst print dbconnect.userlist return lst else: return False
def xmlrpc_setAccount(self, queryParams, client_id):
"""
Purpose: Adds an account in the account table, under a selected
group and optionally a subgroup.
depending on the preference choosen by the user.
This function insert a row in the account table.
it takes one parameter named queryParams which is a list containing,
Input: queryParams[groupname,subgroupname,newsubgroupname,accountname,
accountcodetype,openingbalance,currentBalance,suggestedcode]
Output: Returns String "success"
"""
group = rpc_groups.groups()
queryParams = blankspace.remove_whitespaces(queryParams)
sp_params = [
queryParams[0], queryParams[3]
] # create sp_params list contain groupname , accountname
if queryParams[
2] == "": # check for the new-subgroupname if blank then
if queryParams[
1] == "No Sub-Group": # check for the subgroupname if "No Sub-Group"
sp_params.append(
"null"
) # append null to sp_params list as 3rd parameter else
else:
sp_params.append(
queryParams[1]
) # else append subgroupname sp_params list as 3rd parameter
if queryParams[
1] == "Create New Sub-Group": # subgroupname is "Create New Sub-Group" then
sp_params.append(
queryParams[2]
) # append new-subgroupname to sp_params list as 4rd parameter
if queryParams[0] == "Direct Income" or queryParams[0] == "Direct Expense"\
or queryParams[0] == "Indirect Income" or queryParams[0] == "Indirect Expense": # check for groupname
sp_params.append(
0) # if above groupname then append 0 as opening balance
else:
sp_params.append(queryParams[5])
now = datetime.today(
) # sqlite take datetime or date object for TIMESTAMP
#date = now.strftime("%Y-%m-%d %H:%M:%S")
sp_params.append(
now) # append the current date of system while setting account
sp_params.append(sp_params[3]) # append accountname
if queryParams[7] == "": # chech for suggested account code
sp_params.append("null") # if blank then append "null"
else:
sp_params.append(queryParams[7]) # else append suggestedcode
# execute here
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
# call getGroupCodeByGroupName() pass param groupname will return groupcode
group_code = group.xmlrpc_getGroupCodeByGroupName([sp_params[0]],
client_id)
# check for accountcode if null
if sp_params[6] == 'null': # then
result = Session.query(dbconnect.Account.accountcode).\
order_by(dbconnect.Account.accountcode).\
all()
accountcode = []
if result == []:
maxcode = []
else:
for row in result:
accountcode.append(int(row.accountcode))
maxcode = accountcode
if maxcode == []:
maxcode = 0
sp_params[6] = int(maxcode) + 1
else:
maxcode = max(maxcode)
sp_params[6] = int(maxcode) + 1
# check for new-subgropname if null
if sp_params[2] == 'null': # then
# add all values in the account table
Session.add(dbconnect.Account(\
sp_params[6],group_code[0],"",sp_params[1],sp_params[3],sp_params[4],sp_params[5]))
Session.commit()
else:
# if new-subgroupname is present then call getSubGroupCodeBySubGroupName pass params new-subgroupname
# it will return subgroupcode or False
subgroup_code = group.xmlrpc_getSubGroupCodeBySubGroupName(
[sp_params[2]], client_id)
# check for subgroupcode if False
if subgroup_code == []: # then
# call setSubGroup pass params groupname , new-subgroupname , client-id
group.xmlrpc_setSubGroup([sp_params[0], sp_params[2]],
client_id)
# call getSubGroupCodeBySubGroupName pass params new-subgroupname return subgroupcode
subgroup_code = group.xmlrpc_getSubGroupCodeBySubGroupName(
[sp_params[2]], client_id)
# add all the values in the account table
Session.add(dbconnect.Account(\
sp_params[6],group_code[0],subgroup_code[0],sp_params[1],\
sp_params[3],sp_params[4],sp_params[5]))
Session.commit()
Session.close()
connection.connection.close()
return "success"
def xmlrpc_Deploy(self,queryParams):
"""
Purpose: This function deploys a database instance for
an organisation for a given financial year.
The function will generate the database name
based on the organisation name provided The name
of the database is a combination of,
First character of organisation name,
time stap as "dd-mm-yyyy-hh-MM-ss-ms"
An entry will be made in the xml file
for the currosponding organisation.
Input: [organisation name,From date,to
date,organisation type]
Output: Returns boolean True and client_id
"""
queryParams = blankspace.remove_whitespaces(queryParams)
abtconf=et.parse("/opt/abt/abt.xml")
abtroot = abtconf.getroot()
org = et.SubElement(abtroot,"organisation") #creating an organisation tag
org_name = et.SubElement(org,"orgname")
# assigning client queryparams values to variables
name_of_org = queryParams[0] # name of organisation
db_from_date = queryParams[1]# from date
db_to_date = queryParams[2] # to date
organisationType = queryParams[3] # organisation type
org_name.text = name_of_org #assigning orgnisation name value in orgname tag text of abt.xml
financial_year_from = et.SubElement(org,"financial_year_from") #creating a new tag for financial year from-to
financial_year_from.text = db_from_date
financial_year_to = et.SubElement(org,"financial_year_to")
financial_year_to.text = db_to_date
dbname = et.SubElement(org,"dbname")
#creating database name for organisation
org_db_name = name_of_org[0:1]
time = datetime.datetime.now()
str_time = str(time.microsecond)
new_microsecond = str_time[0:2]
result_dbname = org_db_name + str(time.year) + str(time.month) + str(time.day) + str(time.hour)\
+ str(time.minute) + str(time.second) + new_microsecond
dbname.text = result_dbname #assigning created database name value in dbname tag text of abt.xml
abtconf.write("/opt/abt/abt.xml")
# getting client_id for the given orgnisation and from and to date
self.client_id = dbconnect.getConnection([name_of_org,db_from_date,db_to_date])
try:
metadata = dbconnect.Base.metadata
metadata.create_all(dbconnect.engines[self.client_id])
except:
print "cannot create metadata"
Session = scoped_session(sessionmaker(bind=dbconnect.engines[self.client_id]))
dbconnect.engines[self.client_id].execute(\
"create view view_account as \
select groups.groupname, account.accountcode, account.accountname, account.subgroupcode\
from groups, account where groups.groupcode = account.groupcode\
order by groupname;")
dbconnect.engines[self.client_id].execute(\
"create view view_voucherbook as \
select voucher_master.vouchercode,voucher_master.flag,voucher_master.reference,\
voucher_master.voucherdate,voucher_master.reffdate,voucher_master.vouchertype,account.accountname\
as account_name,voucher_details.typeflag,voucher_details.amount,\
voucher_master.narration,voucher_master.projectcode\
from voucher_master,voucher_details,account as account \
where voucher_master.vouchercode = voucher_details.vouchercode \
and voucher_details.accountcode = account.accountcode;")
dbconnect.engines[self.client_id].execute(\
"create view view_group_subgroup as \
select groups.groupcode, groups.groupname,subgroups.subgroupcode,subgroups.subgroupname\
from groups, subgroups where groups.groupcode = subgroups.groupcode \
order by groupname;")
dbconnect.engines[self.client_id].execute(\
"create view group_subgroup_account as select groups.groupname,\
subgroups.subgroupname,account.accountcode,account.accountname,account.openingbalance,\
account.balance\
from groups join account on (groups.groupcode = account.groupcode)\
left outer join subgroups\
on (account.subgroupcode = subgroups.subgroupcode) order by groupname;")
connection = dbconnect.engines[self.client_id].raw_connection()
cur = connection.cursor()
if (organisationType == "Profit Making"):
Session.add_all([\
dbconnect.Groups('Capital',''),\
dbconnect.Groups('Current Asset',''),\
dbconnect.Groups('Current Liability',''),\
dbconnect.Groups('Direct Income','Income refers to consumption\
opportunity gained by an entity within a specified time frame.'),\
dbconnect.Groups('Direct Expense','This are the expenses to be incurred for\
operating the buisness.'),\
dbconnect.Groups('Fixed Assets',''),\
dbconnect.Groups('Indirect Income','Income refers to consumption opportunity\
gained by an entity within a specified time frame.'),\
dbconnect.Groups('Indirect Expense','This are the expenses to be incurred\
for operating the buisness.'),\
dbconnect.Groups('Investment',''),\
dbconnect.Groups('Loans(Asset)',''),\
dbconnect.Groups('Loans(Liability)',''),\
dbconnect.Groups('Reserves',''),\
dbconnect.Groups('Miscellaneous Expenses(Asset)','')\
])
Session.commit()
else:
Session.add_all([\
dbconnect.Groups('Corpus',''),\
dbconnect.Groups('Current Asset',''),\
dbconnect.Groups('Current Liability',''),\
dbconnect.Groups('Direct Income','Income refers to consumption\
opportunity gained by an entity within a specified time frame.'),\
dbconnect.Groups('Direct Expense','This are the \
expenses to be incurred for operating the buisness.'),\
dbconnect.Groups('Fixed Assets',''),\
dbconnect.Groups('Indirect Income','Income refers to consumption \
opportunity gained by an entity within a specified time frame.'),\
dbconnect.Groups('Indirect Expense','This are the \
expenses to be incurred for operating the buisness.'),\
dbconnect.Groups('Investment',''),\
dbconnect.Groups('Loans(Asset)',''),\
dbconnect.Groups('Loans(Liability)',''),\
dbconnect.Groups('Reserves',''),\
dbconnect.Groups('Miscellaneous Expenses(Asset)','')\
])
Session.commit()
Session.add_all([\
dbconnect.subGroups('2','Bank'),\
dbconnect.subGroups('2','Cash'),\
dbconnect.subGroups('2','Inventory'),\
dbconnect.subGroups('2','Loans & Advance'),\
dbconnect.subGroups('2','Sundry Debtors'),\
dbconnect.subGroups('3','Provisions'),
dbconnect.subGroups('3','Sundry Creditors for Expense'),\
dbconnect.subGroups('3','Sundry Creditors for Purchase'),\
dbconnect.subGroups('6','Building'),\
dbconnect.subGroups('6','Furniture'),\
dbconnect.subGroups('6','Land'),\
dbconnect.subGroups('6','Plant & Machinery'),\
dbconnect.subGroups('9','Investment in Shares & Debentures'),\
dbconnect.subGroups('9','Investment in Bank Deposits'),\
dbconnect.subGroups('11','Secured'),\
dbconnect.subGroups('11','Unsecured')\
])
Session.commit()
Session.add_all([\
dbconnect.Flags(None,'mandatory'),\
dbconnect.Flags(None,'automatic')\
])
Session.commit()
Session.close()
connection.close()
return True,self.client_id
