Guest User

Untitled

a guest
Jun 20th, 2018
40
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.78 KB | None | 0 0
  1. # create table ms201_regularac(acctguid varchar(40) not null,regular boolean , regular_on_date int(10) default 99999999, PRIMARY KEY (acctguid));
  2.  
  3. #import cx_Oracle
  4.  
  5. import MySQLdb
  6. import time
  7. from time import localtime, strftime, sleep
  8. from datetime import datetime, timedelta
  9. import csv
  10. import sys
  11. # Refactor the following using __init__ & classes.
  12. # Meaning, create a class called Accounts & subclass smallAccounts.
  13. # Functions are separate. Methods are also functions BUT limited to a class.
  14. def today():
  15. return str(datetime.strftime(datetime.now(), '%Y%m%d'))
  16.  
  17. # databaseURL="192.168.31.150"
  18. # databaseReplicationURL="192.168.31.150"
  19. # databaseUser="root"
  20. # databasePassword="decisionM45#"
  21. # databaseName="mubcbs"
  22. # prodCode=200
  23. # ReportDate=20170707
  24.  
  25. databaseURL="10.0.30.16"
  26. databaseReplicationURL="10.0.30.28"
  27. databaseUser="root"
  28. databasePassword="decisionM45#"
  29. databaseName="sdcccbs"
  30. prodCode=284
  31. ReportDate=today()
  32.  
  33.  
  34. def getOneFinYear(startdate):
  35. mm1=str(startdate)[4:6]
  36. yy1=str(startdate)[0:4]
  37. if int(mm1)<=3:
  38. yend=yy1
  39. ystart=str(int(yy1)-1)
  40. else:
  41. yend=str(int(yy1)+1)
  42. ystart=yy1
  43. finstart=ystart+'0401'
  44. finend=yend+'0331'
  45. return str(finstart)+'-'+str(finend)
  46.  
  47. def getOneMonth(startdate):
  48. mm1=str(startdate)[4:6]
  49. yy1=str(startdate)[0:4]
  50. monthstart=yy1+mm1+'01'
  51. monthend=yy1+mm1+'31'
  52. return str(monthstart)+'-'+str(monthend)
  53.  
  54. def getTransactions(acctguid,onDate):
  55. afterDate="20180401"
  56. mysqldb = MySQLdb.connect(databaseReplicationURL,databaseUser,databasePassword,databaseName)
  57. mysqlcursor = mysqldb.cursor()
  58. 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)+"'"
  59. mysqlcursor.execute(mysqlstring)
  60. mysqldata =mysqlcursor.fetchall()
  61. mysqldb.close()
  62. return mysqldata
  63.  
  64. def executeMySQLQuery(query):
  65. mysqldb = MySQLdb.connect(databaseURL,databaseUser,databasePassword,databaseName)
  66. mysqlcursor = mysqldb.cursor()
  67. mysqlcursor.execute(query)
  68. mysqldb.commit()
  69. mysqlcursor.close()
  70. mysqldb.close()
  71. return 1
  72.  
  73. def getAllAccounts(code):
  74. lastCalculationDate="20180501"
  75. # Get only accounts whose regular==0.
  76. mysqldb = MySQLdb.connect(databaseReplicationURL,databaseUser,databasePassword,databaseName)
  77. mysqlcursor = mysqldb.cursor()
  78. # query="select guid from ms201 where tenant=(select tenant from ten01) and prodguid IN (select guid from ms901 where code="+str(code)+")"
  79. # 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"
  80. #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)+")"
  81.  
  82. 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"
  83.  
  84. mysqlcursor.execute(query)
  85. mysqldata =mysqlcursor.fetchall()
  86. mysqldb.close()
  87. return mysqldata
  88.  
  89. def getAcDetails(acctguid):
  90. mysqldb = MySQLdb.connect(databaseReplicationURL,databaseUser,databasePassword,databaseName)
  91. mysqlcursor = mysqldb.cursor()
  92. query="select opendate,closedate,lsttrndate from ms201 where tenant=(select tenant from ten01) and guid='"+str(acctguid)+"'"
  93. mysqlcursor.execute(query)
  94. mysqldata =mysqlcursor.fetchall()
  95. mysqldb.close()
  96. return mysqldata
  97.  
  98. def makeClean(transactions):
  99. reportingTx={}
  100. for txn in transactions:
  101. try:
  102. # Find the date on which the balance exceeded 50000.
  103. try:
  104. reportingTx['regularOn']
  105. except:
  106. if txn[0]>=50000:
  107. # print("Balance "+str(txn[0])+" "+str(txn[1]))
  108. reportingTx['regularOn']=[txn[1],"Balance became "+str(txn[0])]
  109.  
  110. try:
  111. reportingTx['balance'].append(txn[0])
  112. except:
  113. reportingTx={'balance': [txn[0]] }
  114.  
  115.  
  116.  
  117.  
  118. if txn[2]==1: # if Credit
  119. # chcek which financial year this txn[1] date belongs to
  120. finYear=getOneFinYear(txn[1])
  121. try:
  122. reportingTx['camount'][finYear]+=txn[3] # meaning, directly add to that financial year.
  123. except:
  124. try:
  125. reportingTx['camount'][finYear]=txn[3]
  126. except:
  127. reportingTx['camount']={finYear:txn[3]}
  128.  
  129. # Find the date on which the credits exceeded 100000.
  130. try:
  131. reportingTx['regularOn']
  132. except:
  133. if reportingTx['camount'][finYear]>100000:
  134. # print("Credits "+str(reportingTx['camount'][finYear])+" "+str(txn[1]))
  135. reportingTx['regularOn']=[txn[1],"Credit in "+str(finYear)+", "+str(reportingTx['camount'][finYear])]
  136.  
  137.  
  138.  
  139.  
  140. else: # If debit
  141. finMonth=getOneMonth(txn[1])
  142. try:
  143. reportingTx['damount'][finMonth]+=txn[3] # meaning, directly add to that Month.
  144. except:
  145. try:
  146. reportingTx['damount'][finMonth]=txn[3]
  147. except:
  148. reportingTx['damount']={finMonth:txn[3]}
  149. # Find the date on which the debits exceeded 10000.
  150. try:
  151. reportingTx['regularOn']
  152. except:
  153. if reportingTx['damount'][finMonth]>10000:
  154. # print("Debits "+str(reportingTx['damount'][finMonth])+" "+str(txn[1]))
  155. reportingTx['regularOn']=[txn[1],"Debit in "+str(finMonth)+", "+str(reportingTx['damount'][finMonth])]
  156. except Exception as e:
  157. print(txn)
  158. print(e)
  159. return reportingTx
  160.  
  161.  
  162.  
  163. # def isYearlyCredit(cleanedData):
  164. # isbigC=0
  165. # if 'camount' in cleanedData:
  166. # for yearr in cleanedData['camount']:
  167. # if (cleanedData['camount'][yearr]>100000) and isbigC==0:
  168. # isbigC=1 # If in ANY year the amount credited is more than 1 Lakh, then it is not a small account.
  169. # return isbigC
  170.  
  171. # def isMonthlyDebit(cleanedData):
  172. # isbigD=0
  173. # if 'damount' in cleanedData:
  174. # for mnths in cleanedData['damount']:
  175. # if (cleanedData['damount'][mnths]>10000) and isbigD==0:
  176. # isbigD=1 # If in ANY year the amount credited is more than 1 Lakh, then it is not a small account.
  177. # return isbigD
  178.  
  179.  
  180. def isRegular(acctguid,onDate):
  181. transactions=getTransactions(acctguid,onDate)
  182. # acDetails=getAcDetails(acctguid)
  183. cleanedData=makeClean(transactions)
  184. isBig=0
  185. regularOn=0
  186. reason=""
  187. if 'regularOn' in cleanedData:
  188. isBig=1
  189. regularOn=cleanedData['regularOn'][0]
  190. reason=cleanedData['regularOn'][1][:48]
  191. updateQuery="replace INTO ms201_regularac(acctguid,regular,regular_on_date,reason) VALUES ('"+str(acctguid)+"', "+str(isBig)+", "+str(regularOn)+",'"+reason+"')"
  192. executeMySQLQuery(updateQuery)
  193.  
  194. # try:
  195. # if (acDetails[0][2]>(int(onDate)-2000)) and (acDetails[0][0]<=onDate) and ( (acDetails[0][1]==0) or (acDetails[0][1]>onDate) ):
  196. # if cleanedData['regularOn']:
  197. # isBig=1
  198. # # if max(cleanedData['balance']) >= 50000:
  199. # # isBig=1
  200. # # else:
  201. # # yc=isYearlyCredit(cleanedData)
  202. # # print("Yearly Credit")
  203. # # print(yc)
  204. # # isBig=yc
  205. # # if yc==0:
  206. # # md=isMonthlyDebit(cleanedData)
  207. # # isBig=md
  208. # updateQuery="replace INTO ms201_regularac(acctguid,regular,regular_on_date) VALUES ('"+str(acctguid)+"', "+str(isBig)+", "+str(regularOn)+")"
  209. # executeMySQLQuery(updateQuery)
  210. # except Exception as e:
  211. # print(e)
  212. return isBig
  213.  
  214. accounts=getAllAccounts(prodCode)
  215. print(len(accounts))
  216. cnt=0
  217. for acct in accounts:
  218. isRegular(acct[0],ReportDate)
  219. cnt+=1
  220. if cnt%1000==0:
  221. print cnt
Add Comment
Please, Sign In to add comment