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_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_Deploy(self,queryParams):
"""
* Purpose:
- The function will generate the database name
based on the organisation name and time stap.
- 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.
- This function deploys a database instance for
an organisation for a given financial year.
- It will call to getConnection and establish the connection
for created database
- also create the metadata(tables) given in ``dbconnect`` for that organisation
using sqlAlchemy.
- create the ``Views`` for the particular organisation.
- It add manually ``groupnames`` ad ``subgroups`` to it's corresponding class
tables ``Groups`` and ``subGroups``
* Input:
- [organisation name,From date,todate,organisation_type]
* Output:
- returns boolean True and client_id
"""
queryParams = blankspace.remove_whitespaces(queryParams)
# 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
#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
del queryParams[3] #delete orgtype
queryParams.append(result_dbname) #dbname
queryParams.append("0") #rollover flag
self.xmlrpc_writeToXmlFile(queryParams,"/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,voucher_master.cheque_no\
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 \
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,'automatic',0),
dbconnect.Flags(None,'manually',0)\
])
Session.commit()
Session.close()
connection.close()
return True,self.client_id
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
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
