6. rpc_transactions.py

• This rpc module will proved details about all transactions

• It will save , edit , clone , delete and some other operations.

• create class transactions inherit the XMLRPC class

  • import the database connector
  • import the twisted modules for executing rpc calls and also to implement the server
  • reactor from the twisted library starts the server with a published object and listens on a given port.
  • inherit the class from XMLRPC to make it publishable as an rpc service.
  • import rpc_account module to create its instance and functions

  import dbconnect
  from twisted.web import xmlrpc, server
  from twisted.internet import reactor
  import rpc_account
  from datetime import datetime, time
  from sqlalchemy import func , and_ , or_
  from modules import blankspace
  
  class transaction(xmlrpc.XMLRPC):
          def __init__(self):
  
                  xmlrpc.XMLRPC.__init__(self)
  

• def xmlrpc_setTransaction(self,queryParams_master,queryParams_details,client_id):

  • This function is used to create a new voucher.
  • adds a new voucher in the database given its reference number and transaction details (dr and cr), along with narration and the date.
  • 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 xmlrpc_getProjectcodeByProjectName xmlrpc_setVoucherMaster xmlrpc_setVoucherDetails
  • and call xmlrpc_getAccountCodeByAccountName from rpc_account.py to get accountcode by accountname
  • input: queryParams_master list will contain: reference number,transaction date ,voucher type,project name,narration queryParams_details list will contain: DrCr flag,AccountName,the amount for the respective account.
  • function returns “success”.

  def xmlrpc_setTransaction(self,queryParams_master,queryParams_details,client_id):
          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_setVoucherMaster(self,queryParams,client_id):

  • 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.
  • queryParams list will contain : reference Number,the actual transaction date,voucher type project name , Narration
  • return vouchercode

  def xmlrpc_setVoucherMaster(self,queryParams,client_id):
  
          connection = dbconnect.engines[client_id].connect()
          Session = dbconnect.session(bind=connection)
          VoucherCode = Session.query(func.count(dbconnect.VoucherMaster.vouchercode)).scalar()
          if VoucherCode == None:
                  VoucherCode = 0
                  VoucherCode = VoucherCode + 1
          else:
                  VoucherCode = VoucherCode + 1
  
          system_date = datetime.today() # sqlite take datetime or date object for TIMESTAMP
          reffdate =  datetime.strptime(str(queryParams[1]),"%d-%m-%Y")
          # add all values in the account table
  
          Session.add(dbconnect.VoucherMaster(\
                  VoucherCode,queryParams[0],system_date,reffdate,queryParams[2],1,queryParams[3],queryParams[4]))
  
          Session.commit()
          Session.close()
          connection.connection.close()
  
          return VoucherCode
  

• def xmlrpc_getProjectcodeByProjectName(self,queryParams,client_id):

  • 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

  def xmlrpc_getProjectcodeByProjectName(self,queryParams,client_id):
          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()
          print result
          if result == None:
                  return 0
          else:
                  projectCode = result[0]
                  return projectCode
  

• def xmlrpc_getProjectNameByProjectCode(self,queryParams,client_id):

  • function to get projectname acouding to projectcode

  • input: It will take only one input projectcode

  • output: it will return projectname if projectcode match

    else returns None String

  def xmlrpc_getProjectNameByProjectCode(self,queryParams,client_id):
          connection = dbconnect.engines[client_id].connect()
          Session = dbconnect.session(bind=connection)
          result = Session.query(dbconnect.Projects.projectname).\
                filter(dbconnect.Projects.projectcode == queryParams[0]).first()
          Session.close()
          connection.connection.close()
          if result == None:
                  return result
          else:
                  projectname = result[0]
                  return projectname
  

• def xmlrpc_setVoucherDetails(self,queryParams,client_id):

  • it set voucher details which will be use in setTransaction
  • input: Dr or Cr flag,AccountName,the amount for the respective account.
  • output: it returns “success ” as string

  def xmlrpc_setVoucherDetails(self,queryParams,client_id):
          connection = dbconnect.engines[client_id].connect()
          Session = dbconnect.session(bind=connection)
          Session.add(dbconnect.VoucherDetails(\
                  queryParams[0],queryParams[1],queryParams[2],queryParams[3]))
  
          Session.commit()
          Session.close()
          connection.connection.close()
          return "success"
  

• def xmlrpc_getTransactions(self,queryParams,client_id):

  • 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: [vouchercode , voucherflag , reff_date , voucher_reference,

    transaction_amount,show_narration]

  def xmlrpc_getTransactions(self,queryParams,client_id):
          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':
  
                  print "In No Project"
                  statement = "select vouchercode,typeflag,reffdate,reference,amount,narration\
                                  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)
                  print "main params"
                  print str(project_code)
                  print queryParams[0]
                  print queryParams[1]
                  print queryParams[2]
                  statement = "select vouchercode, typeflag ,reffdate,reference,amount,narration\
                                  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:
  
                  transactionlist.append([row[0],row[1],row[2],row[3],'%.2f'%(row[4]),row[5]])
  
  
          return transactionlist
  

• def xmlrpc_getParticulars(self,queryParams,client_id):

  • to get list of Particulars from the database given input parameters

  • it will retrive acount name list from view_voucherbook accounts which is involved in transactions

  • if it is involve then

    • it will query to ‘view_voucherbook’ view in (rpc.main)
    • and gives the list of account names

    else

    • it will query to ‘view_voucherbook’ view in (rpc.main)
    • and gives the empty list

  input: [voucher_code,type_flag] output: [accountnames]

  def xmlrpc_getParticulars(self,queryParams,client_id):
          statement = "select account_name\
                          from view_voucherbook\
                          where vouchercode = '"+str(queryParams[0])+"'\
                          and typeflag ='"+queryParams[1]+"' \
                          and flag = 1\
                          order by account_name"
          result = dbconnect.engines[client_id].execute(statement).fetchall()
          accountnames = []
          for row in result:
                  accountnames.append(row.account_name)
  
          return accountnames
  

• def xmlrpc_searchVoucher(self,queryParams,client_id):

  • The function is used to get the list of vouchers on the basis of either reference number (which can be duplicate),or date range, or some words from narration.
  • This means one or more vouchers could be by the same reference number or within a given date range.
  • The list thus returned contains all details of a given voucher except its exact transactions, i.e the records from voucher_master.
  • The function calls 3 definations fron the same class file xmlrpc_searchVouchers xmlrpc_getVoucherAmount xmlrpc_getVoucherDetails
  • returns one or more vouchers given the reference number or date range (which ever specified)takes one parameter queryParams as list.
  • input: [searchFlag , refeence_no , from_date , to_date , narration ]
  • searchFlag integer (1 implies serch by reference,2 as search by date range and 3 as search by narration.
  • returns a 2 dimensional list containing one or more records from voucher_master
  • output:[vouchercode , refeence_no , reffdate,vouchertype,dramount ,cramount , totalamount , narration ]

  def xmlrpc_searchVoucher(self,queryParams,client_id):
          queryParams = blankspace.remove_whitespaces(queryParams)
          vouchers = self.xmlrpc_searchVouchers(queryParams,client_id)
          voucherView = []
          for voucherRow in vouchers:
  
                  amtRow = self.xmlrpc_getVoucherAmount([voucherRow[0]],client_id)
                  voucherAccounts = self.xmlrpc_getVoucherDetails([voucherRow[0]],client_id)
                  drAccount = ""
                  crAccount = ""
                  drCounter = 1
                  crCounter = 1
                  for va in voucherAccounts:
                          if va[1] == "Dr" and drCounter == 2:
                                  drAccount = va[0] + "+"
                          if va[1] == "Dr" and drCounter == 1:
                                  drAccount = va[0]
                                  drCounter = drCounter +1
                          if va[1] == "Cr" and crCounter == 2:
                                  crAccount = va[0] + "+"
                          if va[1] == "Cr" and crCounter == 1:
                                  crAccount = va[0]
                                  crCounter = crCounter +1
  
                  totalAmount = '%.2f'%(amtRow)
  
                  voucherView.append([voucherRow[0],voucherRow[1],voucherRow[2],voucherRow[3],\
                  drAccount,crAccount,totalAmount,voucherRow[4]])
  
          return voucherView
  

• def xmlrpc_searchVouchers(self,queryParams,client_id):

  • This function will be usefull in the searchVouchers to get Complete details or information about transaction
  • input: [searchFlag,ref_no,from_date, to_date ,narration]
  • output: [vouchercode , refeence_no , reffdate,vouchertype,narration ]

  def xmlrpc_searchVouchers(self,queryParams,client_id):
          connection = dbconnect.engines[client_id].connect()
          Session = dbconnect.session(bind=connection)
          from_date = datetime.strptime(str(queryParams[2]),"%d-%m-%Y")
          to_date = datetime.strptime(str(queryParams[3]),"%d-%m-%Y")
          if queryParams[0] == 1:
                  result = Session.query(dbconnect.VoucherMaster).\
                                          filter(and_(dbconnect.VoucherMaster.reference == queryParams[1],\
                                          dbconnect.VoucherMaster.flag == 1)).\
                                          order_by(dbconnect.VoucherMaster.reffdate).all()
                  print "search voucher by reference no"
  
          if queryParams[0] == 2:
  
                  result = Session.query(dbconnect.VoucherMaster).\
                                          filter(and_(dbconnect.VoucherMaster.reffdate >= from_date,\
                                          dbconnect.VoucherMaster.reffdate <= to_date,\
                                          dbconnect.VoucherMaster.flag == 1)).\
                                          order_by(dbconnect.VoucherMaster.reffdate).all()
                  print "search by date "
  
          if queryParams[0] == 3:
                  result = Session.query(dbconnect.VoucherMaster).\
                  filter(and_(dbconnect.VoucherMaster.flag == 1,\
                  (or_(dbconnect.VoucherMaster.narration.like(str(queryParams[4])+'%'),\
                  dbconnect.VoucherMaster.narration.like('%'+str(queryParams[4])+'%'),\
                  dbconnect.VoucherMaster.narration.like('%'+str(queryParams[4])))))).\
                                          order_by(dbconnect.VoucherMaster.reffdate).all()
  
  
          if result == []:
                  return result
          else:
                  voucherlists = []
  
                  for row in result:
                          reffdate = str(row.reffdate).split(" ")
                          ref_date = datetime.strptime(reffdate[0],"%Y-%m-%d").strftime("%d-%m-%Y")
                          voucherlists.append([row.vouchercode,row.reference,ref_date,row.vouchertype,row.narration])
  
                  return voucherlists
  

• def xmlrpc_getVoucherAmount(self,queryParams,client_id):

  • to get amount of particular transaction
  • input: [voucher_code]
  • output: [totalamount]

  def xmlrpc_getVoucherAmount(self,queryParams,client_id):
          queryParams = blankspace.remove_whitespaces(queryParams)
          statement = "select sum(amount) as totalamount\
                                  from view_voucherbook\
                                  where vouchercode = '"+str(queryParams[0])+"'\
                                  and typeflag ='Cr'"
  
          result = dbconnect.engines[client_id].execute(statement).fetchone()
  
          if result[0] == None:
                  return []
          else:
                  return result[0]
  

• def xmlrpc_getVoucherDetails(self,queryParams,client_id):

  • gets the transaction related details given a vouchercode.
  • input: [voucher_code]
  • returns 2 dimentional list containing rows with 3 columns.
  • output: [accountname,typeflag,amount]

  def xmlrpc_getVoucherDetails(self,queryParams,client_id):
          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])])
  
          return voucherdetails
  

• def xmlrpc_getVoucherMaster(self,queryParams,client_id):

  • This function is used along with getVoucherDetails to searchVoucher (get complete voucher) useful while editing or cloning.
  • The function takes one parameter which is a list containing vouchercode.
  • returns a record from the voucher master containing single row data for a given transaction.
  • This function call defination xmlrpc_getProjectNameByProjectCode which is in the same file rpc_transaction to get project name
  • Returns list containing data from voucher_master.
  • input: [voucher_code]
  • output:[reference,reffdate,vouchertype,projectname,narrartion]

  def xmlrpc_getVoucherMaster(self,queryParams,client_id):
          queryParams = blankspace.remove_whitespaces(queryParams)
          connection = dbconnect.engines[client_id].connect()
          Session = dbconnect.session(bind=connection)
  
          result = Session.query(dbconnect.VoucherMaster).\
                                  filter(and_(dbconnect.VoucherMaster.vouchercode == str(queryParams[0]),\
                                  dbconnect.VoucherMaster.flag == 1)).\
                                  order_by(dbconnect.VoucherMaster.reffdate).first()
  
          if result == None:
                  return []
          else :
                  reffdate = str(result.reffdate).split(" ")
                  ref_date = datetime.strptime(reffdate[0],"%Y-%m-%d").strftime("%d-%m-%Y")
                  voucherRow = [result.reference,ref_date,result.vouchertype,result.narration,result.projectcode]
                  projectName = self.xmlrpc_getProjectNameByProjectCode([int(voucherRow[4])],client_id)
                  if projectName == None:
                          projectName = "No Project"
                  voucherMaster = [voucherRow[0],voucherRow[1],voucherRow[2],voucherRow[3],projectName]
  
                  return voucherMaster
  

• def xmlrpc_deleteVoucher(self,queryParams,client_id):

  • 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: [vouchercode]

  def xmlrpc_deleteVoucher(self,queryParams,client_id):
          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_editVoucher(self,queryParams_master,queryParams_details,client_id):

  • this function is used to create a edit voucher.
  • adds a new voucher in the database given its reference number and transaction details (dr and cr), along with narration and the date.
  • The entire transaction is recorded in terms of Dr and Cr and the respected amounts.
  • The function call four funtions from same file rpc_transation.py xmlrpc_getProjectcodeByProjectName xmlrpc_editVoucherMaster xmlrpc_deleteVoucherDetails xmlrpc_editVoucherDetails.
  • queryParams_master list will contain : [vouchercode,reffdate,project name,Narration]
  • queryParams_details list will contain : [AccountName,dr amount,cr amount]
  • returns “success”.

  def xmlrpc_editVoucher(self,queryParams_master,queryParams_details,client_id):
          projectCode = self.xmlrpc_getProjectcodeByProjectName([queryParams_master[2]],client_id)
  
          if projectCode == None:
                  projectCode = 0
          del queryParams_master[2]
          queryParams_master.insert(2,projectCode)
          editParams=[queryParams_master[0],queryParams_master[1],queryParams_master[2],queryParams_master[3]]
          successRow = self.xmlrpc_editVoucherMaster(editParams,client_id)
          if successRow == "success":
                  delete = self.xmlrpc_deleteVoucherDetails([queryParams_master[0]],client_id)
  
                  for detailRow in queryParams_details:
                          sp_details = []
                          sp_details.append(queryParams_master[0])
                          sp_details.append(detailRow[0])
                          if float(detailRow[2]) == 0:
                                  sp_details.append("Dr")
                                  sp_details.append(float(detailRow[1]))
                          if float(detailRow[1]) == 0:
                                  sp_details.append("Cr")
                                  sp_details.append((detailRow[2]))
  
                          result = self.xmlrpc_editVoucherDetails(sp_details,client_id)
          return result
  

• def xmlrpc_editVoucherMaster(self,queryParams,client_id):

  • it update vouchermaster table depend on given vouchercode
  • input: [vouchercode,reffdate,projectcode,narration]
  • output: String “success”

  def xmlrpc_editVoucherMaster(self,queryParams,client_id):
          connection = dbconnect.engines[client_id].connect()
          Session = dbconnect.session(bind=connection)
          reff_date = datetime.strptime(str(queryParams[1]),"%d-%m-%Y")
          result = Session.query(dbconnect.VoucherMaster ).\
                          filter(dbconnect.VoucherMaster.vouchercode == queryParams[0]).\
                          update({'reffdate': reff_date,'projectcode': queryParams[2],'narration':  queryParams[3]})
          Session.commit()
          Session.close()
          connection.connection.close()
          return "success"
  

• def xmlrpc_editVoucherDetails(self,queryParams,client_id):

  • this add the accountcode,typeflag and amount
  • input: [vouchercode,accountname,amount,narration]
  • output: String “success”

  def xmlrpc_editVoucherDetails(self,queryParams,client_id):
          connection = dbconnect.engines[client_id].connect()
          Session = dbconnect.session(bind=connection)
          result = Session.query(dbconnect.Account.accountcode).\
                          filter(dbconnect.Account.accountname == queryParams[1]).\
                          first()
          account_code = result.accountcode
          Session.add(dbconnect.VoucherDetails(\
                          vouchercode = queryParams[0],\
                          accountcode = account_code,\
                          typeflag = queryParams[2],\
                          amount = queryParams[3]\
          ))
          Session.commit()
          Session.close()
          connection.connection.close()
          return "success"
  

• def xmlrpc_deleteVoucherDetails(self,queryParams,client_id):

  • it will delete voucher depend on given voucher code
  • input: [vouchercode]
  • output: String “deleted”

  def xmlrpc_deleteVoucherDetails(self,queryParams,client_id):
          connection = dbconnect.engines[client_id].connect()
          Session = dbconnect.session(bind=connection)
          Session.query(dbconnect.VoucherDetails).\
          filter(dbconnect.VoucherDetails.vouchercode==queryParams[0]).\
          delete()
          Session.commit()
          Session.close()
          connection.connection.close()
          return "deleted"
  

• def xmlrpc_getOnlyClearedTransactions(self,queryParams,client_id):

  • 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

  def xmlrpc_getOnlyClearedTransactions(self,queryParams,client_id):
          from_date = str(datetime.strptime(str(queryParams[2]),"%d-%m-%Y"))
          to_date = str(datetime.strptime(str(queryParams[3]),"%d-%m-%Y"))
          connection = dbconnect.engines[client_id].connect()
          Session = dbconnect.session(bind=connection)
          result = Session.query(dbconnect.BankRecon).filter(and_(dbconnect.BankRecon.accountname==queryParams[0],\
                          dbconnect.BankRecon.vouchercode==queryParams[1],\
                          dbconnect.BankRecon.clearancedate >= from_date,\
                          dbconnect.BankRecon.clearancedate <= to_date)).\
                          first()
          Session.close()
          connection.connection.close()
          if result != None:
                  return True
          else:
                  return False
  

• def xmlrpc_getLastReference(self,queryParams,client_id):

  • To get last reference number for respective vouchertype
  • input: [vouchertype]
  • output: [reffno]

  def xmlrpc_getLastReference(self,queryParams,client_id):
          statement = "select count(vouchercode)\
                          from view_voucherbook\
                          where vouchertype = '"+queryParams[0]+"'"
          maxcode = dbconnect.engines[client_id].execute(statement).fetchone()
  
          if maxcode[0] > 0:
                  statement = "select reference\
                                  from view_voucherbook\
                                  where vouchercode = (\
                                                          select max(vouchercode)from view_voucherbook \
                                                          where vouchertype = '"+queryParams[0]+"'\
                                                          and flag = 1)"
                  reffno = dbconnect.engines[client_id].execute(statement).fetchone()
                  reffno = reffno.reference
          else :
                  reffno = str(maxcode[0]+1)
  
          return reffno
  

• def xmlrpc_getLastReffDate(self,queryParams,client_id):

  • To get last reference date for respective vouchertype
  • input:[financial_start,voucher_type]
  • output parameters:[reference_date]

  def xmlrpc_getLastReffDate(self,queryParams,client_id):
  
          statement = "select count(vouchercode)\
                          from view_voucherbook\
                          where vouchertype = '"+queryParams[1]+"'"
          maxcode = dbconnect.engines[client_id].execute(statement).fetchone()
          reff_date = datetime.strptime(str(queryParams[0]),"%d-%m-%Y")
          if maxcode[0] > 0:
                  statement = "select reffdate\
                                  from view_voucherbook\
                                  where vouchercode = (\
                                                          select max(vouchercode)from view_voucherbook \
                                                          where vouchertype = '"+queryParams[1]+"'\
                                                          and flag = 1)"
                  reff_date= dbconnect.engines[client_id].execute(statement).fetchone()
                  reff_date = str(reff_date.reffdate).split(" ")
                  reff_date = datetime.strptime(reff_date[0],"%Y-%m-%d").strftime("%d-%m-%Y")
  
          else :
                  reff_date = str(queryParams[0])
  
          return reff_date