Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # create table ms201_regularac(acctguid varchar(40) not null,regular boolean , regular_on_date int(10) default 99999999, PRIMARY KEY (acctguid));
- #import cx_Oracle
- import MySQLdb
- import time
- from time import localtime, strftime, sleep
- from datetime import datetime, timedelta
- import csv
- import sys
- # Refactor the following using __init__ & classes.
- # Meaning, create a class called Accounts & subclass smallAccounts.
- # Functions are separate. Methods are also functions BUT limited to a class.
- def today():
- return str(datetime.strftime(datetime.now(), '%Y%m%d'))
- # databaseURL="192.168.31.150"
- # databaseReplicationURL="192.168.31.150"
- # databaseUser="root"
- # databasePassword="decisionM45#"
- # databaseName="mubcbs"
- # prodCode=200
- # ReportDate=20170707
- databaseURL="10.0.30.16"
- databaseReplicationURL="10.0.30.28"
- databaseUser="root"
- databasePassword="decisionM45#"
- databaseName="sdcccbs"
- prodCode=284
- ReportDate=today()
- def getOneFinYear(startdate):
- mm1=str(startdate)[4:6]
- yy1=str(startdate)[0:4]
- if int(mm1)<=3:
- yend=yy1
- ystart=str(int(yy1)-1)
- else:
- yend=str(int(yy1)+1)
- ystart=yy1
- finstart=ystart+'0401'
- finend=yend+'0331'
- return str(finstart)+'-'+str(finend)
- def getOneMonth(startdate):
- mm1=str(startdate)[4:6]
- yy1=str(startdate)[0:4]
- monthstart=yy1+mm1+'01'
- monthend=yy1+mm1+'31'
- return str(monthstart)+'-'+str(monthend)
- def getTransactions(acctguid,onDate):
- afterDate="20180401"
- mysqldb = MySQLdb.connect(databaseReplicationURL,databaseUser,databasePassword,databaseName)
- mysqlcursor = mysqldb.cursor()
- mysqlstring="select cramt,trdate,cdflag,amount from tr001 where tenant=(select tenant from ten01) and trdate <= "+str(onDate)+" and trdate >= "+afterDate+" and acctguid='"+ str(acctguid)+"'"
- mysqlcursor.execute(mysqlstring)
- mysqldata =mysqlcursor.fetchall()
- mysqldb.close()
- return mysqldata
- def executeMySQLQuery(query):
- mysqldb = MySQLdb.connect(databaseURL,databaseUser,databasePassword,databaseName)
- mysqlcursor = mysqldb.cursor()
- mysqlcursor.execute(query)
- mysqldb.commit()
- mysqlcursor.close()
- mysqldb.close()
- return 1
- def getAllAccounts(code):
- lastCalculationDate="20180501"
- # Get only accounts whose regular==0.
- mysqldb = MySQLdb.connect(databaseReplicationURL,databaseUser,databasePassword,databaseName)
- mysqlcursor = mysqldb.cursor()
- # query="select guid from ms201 where tenant=(select tenant from ten01) and prodguid IN (select guid from ms901 where code="+str(code)+")"
- # query="select a.guid from ms201 a inner join ms201_regularac b on a.guid=b.acctguid where a.tenant=b.tenant and a.tenant=(select tenant from ten01) and a.prodguid IN (select guid from ms901 where code="+str(code)+") and b.regular!=1"
- #query="select guid from ms201 where tenant=(select tenant from ten01) and guid not in (select acctguid from ms201_regularac where tenant=(select tenant from ten01)) and prodguid IN (select guid from ms901 where code="+str(code)+")"
- query = "select acctguid from tr001 where tenant=(select tenant from ten01) and trdate>="+lastCalculationDate+" and glguid IN (select glguid from ms901 where code="+str(code)+") group by acctguid"
- mysqlcursor.execute(query)
- mysqldata =mysqlcursor.fetchall()
- mysqldb.close()
- return mysqldata
- def getAcDetails(acctguid):
- mysqldb = MySQLdb.connect(databaseReplicationURL,databaseUser,databasePassword,databaseName)
- mysqlcursor = mysqldb.cursor()
- query="select opendate,closedate,lsttrndate from ms201 where tenant=(select tenant from ten01) and guid='"+str(acctguid)+"'"
- mysqlcursor.execute(query)
- mysqldata =mysqlcursor.fetchall()
- mysqldb.close()
- return mysqldata
- def makeClean(transactions):
- reportingTx={}
- for txn in transactions:
- try:
- # Find the date on which the balance exceeded 50000.
- try:
- reportingTx['regularOn']
- except:
- if txn[0]>=50000:
- # print("Balance "+str(txn[0])+" "+str(txn[1]))
- reportingTx['regularOn']=[txn[1],"Balance became "+str(txn[0])]
- try:
- reportingTx['balance'].append(txn[0])
- except:
- reportingTx={'balance': [txn[0]] }
- if txn[2]==1: # if Credit
- # chcek which financial year this txn[1] date belongs to
- finYear=getOneFinYear(txn[1])
- try:
- reportingTx['camount'][finYear]+=txn[3] # meaning, directly add to that financial year.
- except:
- try:
- reportingTx['camount'][finYear]=txn[3]
- except:
- reportingTx['camount']={finYear:txn[3]}
- # Find the date on which the credits exceeded 100000.
- try:
- reportingTx['regularOn']
- except:
- if reportingTx['camount'][finYear]>100000:
- # print("Credits "+str(reportingTx['camount'][finYear])+" "+str(txn[1]))
- reportingTx['regularOn']=[txn[1],"Credit in "+str(finYear)+", "+str(reportingTx['camount'][finYear])]
- else: # If debit
- finMonth=getOneMonth(txn[1])
- try:
- reportingTx['damount'][finMonth]+=txn[3] # meaning, directly add to that Month.
- except:
- try:
- reportingTx['damount'][finMonth]=txn[3]
- except:
- reportingTx['damount']={finMonth:txn[3]}
- # Find the date on which the debits exceeded 10000.
- try:
- reportingTx['regularOn']
- except:
- if reportingTx['damount'][finMonth]>10000:
- # print("Debits "+str(reportingTx['damount'][finMonth])+" "+str(txn[1]))
- reportingTx['regularOn']=[txn[1],"Debit in "+str(finMonth)+", "+str(reportingTx['damount'][finMonth])]
- except Exception as e:
- print(txn)
- print(e)
- return reportingTx
- # def isYearlyCredit(cleanedData):
- # isbigC=0
- # if 'camount' in cleanedData:
- # for yearr in cleanedData['camount']:
- # if (cleanedData['camount'][yearr]>100000) and isbigC==0:
- # isbigC=1 # If in ANY year the amount credited is more than 1 Lakh, then it is not a small account.
- # return isbigC
- # def isMonthlyDebit(cleanedData):
- # isbigD=0
- # if 'damount' in cleanedData:
- # for mnths in cleanedData['damount']:
- # if (cleanedData['damount'][mnths]>10000) and isbigD==0:
- # isbigD=1 # If in ANY year the amount credited is more than 1 Lakh, then it is not a small account.
- # return isbigD
- def isRegular(acctguid,onDate):
- transactions=getTransactions(acctguid,onDate)
- # acDetails=getAcDetails(acctguid)
- cleanedData=makeClean(transactions)
- isBig=0
- regularOn=0
- reason=""
- if 'regularOn' in cleanedData:
- isBig=1
- regularOn=cleanedData['regularOn'][0]
- reason=cleanedData['regularOn'][1][:48]
- updateQuery="replace INTO ms201_regularac(acctguid,regular,regular_on_date,reason) VALUES ('"+str(acctguid)+"', "+str(isBig)+", "+str(regularOn)+",'"+reason+"')"
- executeMySQLQuery(updateQuery)
- # try:
- # if (acDetails[0][2]>(int(onDate)-2000)) and (acDetails[0][0]<=onDate) and ( (acDetails[0][1]==0) or (acDetails[0][1]>onDate) ):
- # if cleanedData['regularOn']:
- # isBig=1
- # # if max(cleanedData['balance']) >= 50000:
- # # isBig=1
- # # else:
- # # yc=isYearlyCredit(cleanedData)
- # # print("Yearly Credit")
- # # print(yc)
- # # isBig=yc
- # # if yc==0:
- # # md=isMonthlyDebit(cleanedData)
- # # isBig=md
- # updateQuery="replace INTO ms201_regularac(acctguid,regular,regular_on_date) VALUES ('"+str(acctguid)+"', "+str(isBig)+", "+str(regularOn)+")"
- # executeMySQLQuery(updateQuery)
- # except Exception as e:
- # print(e)
- return isBig
- accounts=getAllAccounts(prodCode)
- print(len(accounts))
- cnt=0
- for acct in accounts:
- isRegular(acct[0],ReportDate)
- cnt+=1
- if cnt%1000==0:
- print cnt
Add Comment
Please, Sign In to add comment