def Pacnet_api(self,start_date):
"""
Downloads Transactions from Pacnet portal
Input parameters:
start_date - Date fo the transactions needs to be downloaded.
An XML is formed with date and time value and it will be processed by the portal url to download the transactions.
Downloaded transactions will be inserted into respective portal tables using table_insert().
The request id is a unique field for each run.
"""
strUserName = '******'
strPass = '******'
strUrl = 'https://raven.deepcovelabs.com/realtime/payments'
requestID =str((dates.toDate(str(start_date))+datetime.timedelta(1)).strftime("%Y-%m-%d"))
startDate=(dates.toDate(str(start_date))).strftime("%Y-%m-%d")+'T00:00:00.Z'
endTime=(dates.toDate(str(start_date))).strftime("%Y-%m-%d")+'T23:59:59.Z'
strTimezone = (dates.toDate(str(datetime.date.today()))-datetime.timedelta(1)).strftime("%Y-%m-%d")+'T00:00:00.Z'
data = strUserName+strTimezone+requestID
signature = hmac.new(strPass,data,sha).hexdigest()
reqString = 'RAPIVersion=2&UserName=lendingstreamllc.api&Timestamp='+strTimezone+'&RequestID='+requestID+\
'&Signature='+str(signature)+'&ReportFormat=RavenPaymentFile_v1.0&StartTime='+startDate+'&EndTime='+endTime+\
'&ResultFields=PRN Status TrackingNumber RequestID Amount Currency Timestamp'
field_name=['PRN,Status,TrackingNumber,RequestID,Amount,Currency,Timestamp']
try:
response = urllib2.urlopen(strUrl,reqString).readlines()
except Exception,e:
print str(e)
def compareMerchantPG(self, portal_tran_type = 0, pg_cc_rtn_msg = 0,table_field_name = None,tran_status = 'Approved',):
self.flag = 0
try:
cur_date=datetime.date.today().strftime("%Y-%m-%d")
file_date=(dates.toDate(cur_date)).strftime("%Y%m%d")
start_dt=(dates.toDate(cur_date)-datetime.timedelta(32)).strftime("%Y-%m-%d")
end_dt=(dates.toDate(cur_date)-datetime.timedelta(2)).strftime("%Y-%m-%d")
if self.ms_name =='Barclays':
logger.info(" PORTAL:: Inside Barclays Portal for comparison :: ")
del_sql="delete from DERIVED_Data.Barclays_portal_dup"
self.tmsconn.execute(del_sql)
beg_date=(dates.toDate(self.run_date)-datetime.timedelta(1)).strftime("%Y-%m-%d")
while beg_date < cur_date:
logger.info("Barclays Portal:: Computing LS Loans :: ")
#myObj = offline_accounts_script.accounts(beg_date,'len5aw','68678','Waterfall1','Barclays_LS')
#myObj.reportDoc()
logger.info("Barclays Portal:: Computing Zebit Loans :: ")
#myObj = offline_accounts_script.accounts(beg_date,'zeb5aw','68680','Waterfall2','Barclays_ZEBIT')
#myObj.reportDoc()
repdate = (dates.toDate(beg_date)-datetime.timedelta(1)).strftime("%Y%m%d")
repdate_dt = (dates.toDate(beg_date)-datetime.timedelta(1)).strftime("%Y-%m-%d")
self.insertReportDate(repdate_dt,table_field_name)
update_sql = "update DERIVED_Data.PortalvsPG_app a join (select '%s' as ReportDate,count(*) as TotalRec,sum(CapturedAmount)/100 as TotalSum, \
'%s' as ms_name from DERIVED_Data.%s where OrderId like '%s%%' or OrderId like 'UI%s%%') b \
on a.ReportDate=b.ReportDate and a.merchant_name=b.ms_name set a.PORTAL_TOTAL_AMT = b.TotalSum , \
a.PORTAL_TOTAL_RECORDS = b.TotalRec "%(repdate_dt,self.ms_name,self.ms_name+"_portal",repdate,repdate)
self.tmsconn.execute(update_sql)
logger.info("Barclays Portal:: Updated Portal data into PortalvsPG_app table :: "+str(update_sql))
beg_date = (dates.toDate(beg_date)+datetime.timedelta(1)).strftime("%Y-%m-%d")
update_sql_pg = "update DERIVED_Data.PortalvsPG_app a join \
(select '%s' as ReportDate,count(*) as TotalRec,sum(amount) as TotalSum,'%s' as ms_name \
from Payment_Gateway_Live.pg_request_response where (OrderId like '%s%%' or OrderId like 'UI%s%%') and ms_name = '%s' and \
cc_return_msg rlike 'approved') b on a.ReportDate=b.ReportDate and a.merchant_name=b.ms_name \
set a.PG_TOTAL_AMT = b.TotalSum ,a.PG_TOTAL_RECORDS = b.TotalRec"%(repdate_dt,self.ms_name,repdate,repdate,self.ms_name)
self.tmsconn.execute(update_sql_pg)
logger.info("Barclays Portal:: Updated PG data into PortalvsPG_app table :: "+str(update_sql_pg))
query2="select ReportDate,ifnull(PG_TOTAL_AMT,0) as PG_TOTAL_AMT,ifnull(PORTAL_TOTAL_AMT,0) as PORTAL_TOTAL_AMT,ifnull(PG_TOTAL_RECORDS,0)\
as PG_TOTAL_RECORDS,ifnull(PORTAL_TOTAL_RECORDS,0) as PORTAL_TOTAL_RECORDS,ifnull(MisIdentifyAmt,0) as MisIdentifyAmt,\
ifnull(MisIdentifyReason,'NIL') as MisIdentifyReason from DERIVED_Data.PortalvsPG_app \
where merchant_name = '%s' and date(ReportDate) between '%s' and '%s' order by ReportDate desc"\
%(self.ms_name,start_dt,end_dt)
res_set = self.tmsconn.get_all_results(query2)
barclays_mismatch="select ReportDate,ifnull(PG_TOTAL_AMT,0) as PG_TOTAL_AMT,ifnull(PORTAL_TOTAL_AMT,0) as PORTAL_TOTAL_AMT,ifnull(PG_TOTAL_RECORDS,0)\
as PG_TOTAL_RECORDS,ifnull(PORTAL_TOTAL_RECORDS,0) as PORTAL_TOTAL_RECORDS,ifnull(MisIdentifyAmt,0) as MisIdentifyAmt,\
ifnull(MisIdentifyReason,'NIL') as MisIdentifyReason from DERIVED_Data.PortalvsPG_app \
where merchant_name = '%s' and ReportDate between '2013-01-01' and DATE_SUB(curdate(),INTERVAL 33 day) and PG_TOTAL_AMT!=PORTAL_TOTAL_AMT \
and (abs((PG_TOTAL_AMT-PORTAL_TOTAL_AMT)) - ifnull(MisIdentifyAmt,0.00))!=0"%(self.ms_name)
barclays_res=self.tmsconn.get_all_results(barclays_mismatch)
dup_sel_sql="select OrderId as orderid,CapturedAmount as amount from DERIVED_Data.%s"%(self.ms_name+"_portal_dup")
res_dup_sel_sql=self.tmsconn.get_all_results(dup_sel_sql)
if len(res_dup_sel_sql)>15:
dup_sel_sql="select concat(count(OrderId),'(Total count)') as orderid,sum(CapturedAmount) as amount from DERIVED_Data.%s"%(self.ms_name+"_portal_dup")
res_dup_sel_sql=self.tmsconn.get_all_results(dup_sel_sql)
sub_barclays_result = "select * from DERIVED_Data.PortalvsPG_app where merchant_name = '%s' and ReportDate between DATE_SUB(curdate(),INTERVAL 32 day) \
and DATE_SUB('%s',INTERVAL 2 day) and PG_TOTAL_AMT!=PORTAL_TOTAL_AMT and \
(abs((PG_TOTAL_AMT-PORTAL_TOTAL_AMT)) - ifnull(MisIdentifyAmt,0.00))!=0"%(self.ms_name,repdate_dt)
res_sub_barclays_result = self.tmsconn.get_all_results(sub_barclays_result)
if res_sub_barclays_result or barclays_res:
self.sub = "<p><b><font color = 'red'>"+ self.ms_name + " Portal and PG - Amount Comparison</font></b></p>"
else:
self.sub = ""
self.html_creation(res_set,start_dt,end_dt,res_dup_sel_sql,barclays_res)
logger.info("Barclays Portal:: Success in Sending Mail :: ")
if self.ms_name =='Barclays_EPDQ':
logger.info("Portal:: Inside Barclays_EPDQ :: ")
beg_date=(dates.toDate(self.run_date)-datetime.timedelta(1)).strftime("%Y-%m-%d")
del_sql="delete from DERIVED_Data.Barclays_EPDQ_portal_dup"
self.tmsconn.execute(del_sql)
while beg_date < cur_date:
myObj = Barclays_EPDQ.accounts(beg_date,'len5aw','epdq1024254','LSW@terFa1#','Barclays_EPDQ_LS')
myObj.reportDoc()
myObj = Barclays_EPDQ.accounts(beg_date,'zeb5aw','epdq1024255','ZebW@terFa1#','Barclays_EPDQ_ZEBIT')
myObj.reportDoc()
#self.read_file(beg_date,store='Barclays')
logger.info("Barclays_EPDQ Portal:: Updated Zebit loans ::")
repdate = (dates.toDate(beg_date)-datetime.timedelta(1)).strftime("%Y%m%d")
repdate_dt = (dates.toDate(beg_date)-datetime.timedelta(1)).strftime("%Y-%m-%d")
self.insertReportDate(repdate_dt,table_field_name)
update_sql = "update DERIVED_Data.PortalvsPG_app a join (select '%s' as ReportDate,count(*) as TotalRec,cast(sum(Total) as decimal(10,2)) \
as TotalSum, '%s' as ms_name from DERIVED_Data.%s where (REF like '%s%%' or REF like 'UI%s%%') and \
REF not in ('20140807_01_2094826','20140807_02_2094827','20140806_01_2094821','20140806_01_2094816') \
and status in %s ) b \
on a.ReportDate=b.ReportDate and a.merchant_name='%s' set a.PORTAL_TOTAL_AMT = b.TotalSum , \
a.PORTAL_TOTAL_RECORDS = b.TotalRec "%\
(repdate_dt,self.ms_name,self.ms_name+"_portal",repdate,repdate,portal_tran_type,table_field_name)
self.tmsconn.execute(update_sql)
logger.info("Barclays_EPDQ Portal:: Updated Portal data into PortalvsPG_app Table ::"+str(update_sql))
beg_date = (dates.toDate(beg_date)+datetime.timedelta(1)).strftime("%Y-%m-%d")
update_sql_pg = "update DERIVED_Data.PortalvsPG_app a join \
(select '%s' as ReportDate,count(*) as TotalRec,cast(sum(amount)as decimal(10,2)) as TotalSum,'%s' as ms_name \
from Payment_Gateway_Live.pg_request_response where (OrderId like '%s%%' or OrderId like 'UI%s%%') and ms_name = '%s' and \
cc_return_msg in %s ) b on a.ReportDate=b.ReportDate and a.merchant_name= '%s' \
set a.PG_TOTAL_AMT = b.TotalSum ,a.PG_TOTAL_RECORDS = b.TotalRec"% \
(repdate_dt,self.ms_name,repdate,repdate,self.ms_name,pg_cc_rtn_msg, table_field_name)
self.tmsconn.execute(update_sql_pg)
logger.info("Barclays_EPDQ Portal:: Updated PG data into PortalvsPG_app Table ::"+str(update_sql_pg))
query2="select ReportDate,ifnull(PG_TOTAL_AMT,0) as PG_TOTAL_AMT,ifnull(PORTAL_TOTAL_AMT,0) as PORTAL_TOTAL_AMT,ifnull(PG_TOTAL_RECORDS,0)\
as PG_TOTAL_RECORDS,ifnull(PORTAL_TOTAL_RECORDS,0) as PORTAL_TOTAL_RECORDS,ifnull(MisIdentifyAmt,0) as MisIdentifyAmt,\
ifnull(MisIdentifyReason,'NIL') as MisIdentifyReason from DERIVED_Data.PortalvsPG_app \
where merchant_name = '%s' and date(ReportDate) between '%s' and '%s' order by ReportDate desc"\
%(table_field_name,start_dt,end_dt)
res_set = self.tmsconn.get_all_results(query2)
barclays_epdq_mismatch="select ReportDate,ifnull(PG_TOTAL_AMT,0) as PG_TOTAL_AMT,ifnull(PORTAL_TOTAL_AMT,0) as PORTAL_TOTAL_AMT,ifnull(PG_TOTAL_RECORDS,0)\
as PG_TOTAL_RECORDS,ifnull(PORTAL_TOTAL_RECORDS,0) as PORTAL_TOTAL_RECORDS,ifnull(MisIdentifyAmt,0) as MisIdentifyAmt,\
ifnull(MisIdentifyReason,'NIL') as MisIdentifyReason from DERIVED_Data.PortalvsPG_app \
where merchant_name = '%s' and ReportDate between '2013-01-01' and DATE_SUB(curdate(),INTERVAL 33 day) and \
ReportDate not in ('2013-12-13','2013-12-06') and PG_TOTAL_AMT!=PORTAL_TOTAL_AMT \
and (abs((PG_TOTAL_AMT-PORTAL_TOTAL_AMT)) - ifnull(MisIdentifyAmt,0.00))!=0"%(table_field_name)
barclays_epdq_res=self.tmsconn.get_all_results(barclays_epdq_mismatch)
dup_sel_sql="select REF as orderid,TOTAL as amount from DERIVED_Data.%s"%(self.ms_name+"_portal_dup")
res_dup_sel_sql=self.tmsconn.get_all_results(dup_sel_sql)
sub_barclays_epdq_result = "select * from DERIVED_Data.PortalvsPG_app where merchant_name = '%s' and ReportDate between DATE_SUB(curdate(),INTERVAL 32 day) \
and DATE_SUB('%s',INTERVAL 2 day) and PG_TOTAL_AMT!=PORTAL_TOTAL_AMT and \
(abs((PG_TOTAL_AMT-PORTAL_TOTAL_AMT)) - ifnull(MisIdentifyAmt,0.00))!=0"%(self.ms_name, repdate_dt)
res_sub_barclays_epdq_result = self.tmsconn.get_all_results(sub_barclays_epdq_result)
if res_sub_barclays_epdq_result or barclays_epdq_res:
self.sub = "<p><b><font color = 'red'>"+ self.ms_name + " Portal and PG - Amount Comparison</font></b></p>"
else:
self.sub = ""
self.html_creation(res_set,start_dt,end_dt,res_dup_sel_sql,barclays_epdq_res,tran_status)
logger.info("Barclays_EPDQ Portal:: Seccess in Sending Mail ::")
if self.ms_name == 'optimalPayments':
logger.info("Portal:: Inside OptimalPayments ::")
op_start_dt=(dates.toDate(self.run_date)-datetime.timedelta(1)).strftime("%Y-%m-%d")
op_date=(dates.toDate(op_start_dt)).strftime("%Y%m%d")
del_sql="delete from DERIVED_Data.optimalPayments_portal_dup"
self.tmsconn.execute(del_sql)
while op_start_dt < cur_date:
self.OP_api(op_start_dt)
self.insertReportDate(op_start_dt,self.ms_name)
update_sql = "update DERIVED_Data.PortalvsPG_app a join (select '%s' as ReportDate,count(*) as TotalRec,cast(sum(AMOUNT) as decimal(10,2)) \
as TotalSum, '%s' as ms_name from DERIVED_Data.%s where (MERCHANTTRANSID like '%s%%' or MERCHANTTRANSID like 'UI%s%%')) b \
on a.ReportDate=b.ReportDate and a.merchant_name=b.ms_name set a.PORTAL_TOTAL_AMT = b.TotalSum , \
a.PORTAL_TOTAL_RECORDS = b.TotalRec "%(op_start_dt,self.ms_name,self.ms_name+"_portal",op_date,op_date)
self.tmsconn.execute(update_sql)
logger.info("OptimalPayments Portal:: Updated Portal data into PortalvsPG_app Table ::"+str(update_sql))
update_sql_pg = "update DERIVED_Data.PortalvsPG_app a join \
(select '%s' as ReportDate,count(*) as TotalRec,sum(amount) as TotalSum,'%s' as ms_name \
from Payment_Gateway_Live.pg_request_response where (orderid like '%s%%' or orderid like 'UI%s%%') and ms_name = '%s' and \
cc_return_msg='Approved.') b on a.ReportDate=b.ReportDate and a.merchant_name=b.ms_name set a.PG_TOTAL_AMT = b.TotalSum ,\
a.PG_TOTAL_RECORDS = b.TotalRec"\
%(op_start_dt,self.ms_name,op_date,op_date,self.ms_name)
self.tmsconn.execute(update_sql_pg)
logger.info("OptimalPayments Portal:: Updated PG data into PortalvsPG_app Table ::"+str(update_sql_pg))
op_start_dt = (dates.toDate(op_start_dt)+datetime.timedelta(1)).strftime("%Y-%m-%d")
op_date=(dates.toDate(op_start_dt)).strftime("%Y%m%d")
query2="select ReportDate,ifnull(PG_TOTAL_AMT,0) as PG_TOTAL_AMT,ifnull(PORTAL_TOTAL_AMT,0) as PORTAL_TOTAL_AMT,ifnull(PG_TOTAL_RECORDS,0)\
as PG_TOTAL_RECORDS,ifnull(PORTAL_TOTAL_RECORDS,0) as PORTAL_TOTAL_RECORDS,ifnull(MisIdentifyAmt,0) as MisIdentifyAmt,\
ifnull(MisIdentifyReason,'NIL') as MisIdentifyReason from DERIVED_Data.PortalvsPG_app \
where merchant_name = '%s' and date(ReportDate) between '%s' and '%s' order by ReportDate desc"\
%(self.ms_name,start_dt,end_dt)
res_set = self.tmsconn.get_all_results(query2)
OP_mismatch="select ReportDate,ifnull(PG_TOTAL_AMT,0) as PG_TOTAL_AMT,ifnull(PORTAL_TOTAL_AMT,0) as PORTAL_TOTAL_AMT,ifnull(PG_TOTAL_RECORDS,0)\
as PG_TOTAL_RECORDS,ifnull(PORTAL_TOTAL_RECORDS,0) as PORTAL_TOTAL_RECORDS,ifnull(MisIdentifyAmt,0) as MisIdentifyAmt,\
ifnull(MisIdentifyReason,'NIL') as MisIdentifyReason from DERIVED_Data.PortalvsPG_app \
where merchant_name = '%s' and ReportDate between '2013-01-01' and DATE_SUB(curdate(),INTERVAL 33 day) and PG_TOTAL_AMT!=PORTAL_TOTAL_AMT and\
(abs((PG_TOTAL_AMT-PORTAL_TOTAL_AMT)) - ifnull(MisIdentifyAmt,0.00)) "%(self.ms_name)
OP_res=self.tmsconn.get_all_results(OP_mismatch)
dup_sel_sql="select MERCHANTTRANSID as orderid,Amount as amount from DERIVED_Data.%s"%(self.ms_name+"_portal_dup")
res_dup_sel_sql=self.tmsconn.get_all_results(dup_sel_sql)
if len(res_dup_sel_sql)>15:
dup_sel_sql="select count(MERCHANTTRANSID) as orderid,sum(Amount) as amount from DERIVED_Data.%s"%(self.ms_name+"_portal_dup")
res_dup_sel_sql=self.tmsconn.get_all_results(dup_sel_sql)
sub_op_result = "select * from DERIVED_Data.PortalvsPG_app where merchant_name = '%s' and ReportDate between DATE_SUB(curdate(),INTERVAL 32 day) \
and '%s' and PG_TOTAL_AMT!=PORTAL_TOTAL_AMT and \
(abs((PG_TOTAL_AMT-PORTAL_TOTAL_AMT)) - ifnull(MisIdentifyAmt,0.00))!=0"%(self.ms_name,end_dt)
res_sub_op_result = self.tmsconn.get_all_results(sub_op_result)
if res_sub_op_result or OP_res:
self.sub = "<p><b><font color = 'red'> Mismatch in "+ self.ms_name + " Portal and PG - Amount Comparison</font></b></p>"
else:
self.sub = ""
self.html_creation(res_set,start_dt,end_dt,res_dup_sel_sql,OP_res)
logger.info("OptimalPayments Portal:: Success in Sending Mail ::")
if self.ms_name == 'PACNET':
logger.info("Portal:: Inside PACNET ::")
pcn_start_dt=(dates.toDate(self.run_date)-datetime.timedelta(1)).strftime("%Y-%m-%d")
pcn_date=(dates.toDate(pcn_start_dt)).strftime("%Y%m%d")
del_sql="delete from DERIVED_Data.PACNET_portal_dup"
self.tmsconn.execute(del_sql)
while pcn_start_dt < cur_date:
#self.read_file(pcn_start_dt)
self.Pacnet_api(pcn_start_dt)
self.insertReportDate(pcn_start_dt,self.ms_name)
ins_ordr_sql="update DERIVED_Data.PACNET_portal a join Payment_Gateway_Live.pg_request_response b on b.refund_id = TrackingNumber \
set a.Pacnet_orderid =b.orderid where a.Pacnet_orderid is null and ms_name='%s'" %\
(self.ms_name)
self.tmsconn.execute(ins_ordr_sql)
update_sql = "update DERIVED_Data.PortalvsPG_app a join (select '%s' as ReportDate,count(*) as TotalRec,sum(Amount)/100 as TotalSum, \
'%s' as ms_name from DERIVED_Data.%s where (Pacnet_orderid like '%s%%' or Pacnet_orderid like 'UI%s%%')\
and (Status = 'Approved' or Status like '%s%%' or Status like '%s%%')) b on a.ReportDate=b.ReportDate \
and a.merchant_name=b.ms_name set a.PORTAL_TOTAL_AMT = b.TotalSum ,a.PORTAL_TOTAL_RECORDS = b.TotalRec " \
%(pcn_start_dt,self.ms_name,self.ms_name+"_portal",pcn_date,pcn_date,'Returned','RetrievalRequested')
self.tmsconn.execute(update_sql)
logger.info("PACNET Portal:: Updated Portal data into PortalvsPG_app Table ::"+str(update_sql))
update_sql_pg = "update DERIVED_Data.PortalvsPG_app a join \
(select '%s' as ReportDate,count(*) as TotalRec,sum(amount) as TotalSum,'%s' as ms_name \
from Payment_Gateway_Live.pg_request_response where (orderid like '%s%%' or orderid like 'UI%s%%') and OrderId not in\
(30397084,30397086,30397087,30397089,30397090,30397091,30397088,30398763,30398765,30398769) and cc_return_msg rlike 'approved'\
and ms_name = '%s') b on a.ReportDate=b.ReportDate and a.merchant_name=b.ms_name set a.PG_TOTAL_AMT = b.TotalSum ,\
a.PG_TOTAL_RECORDS = b.TotalRec"\
%(pcn_start_dt,self.ms_name,pcn_date,pcn_date,self.ms_name)
self.tmsconn.execute(update_sql_pg)
logger.info("PACNET Portal:: Updated PG data into PortalvsPG_app Table ::"+str(update_sql_pg))
pcn_start_dt = (dates.toDate(pcn_start_dt)+datetime.timedelta(1)).strftime("%Y-%m-%d")
pcn_date=(dates.toDate(pcn_start_dt)).strftime("%Y%m%d")
query2="select ReportDate,ifnull(PG_TOTAL_AMT,0) as PG_TOTAL_AMT,ifnull(PORTAL_TOTAL_AMT,0) as PORTAL_TOTAL_AMT,ifnull(PG_TOTAL_RECORDS,0)\
as PG_TOTAL_RECORDS,ifnull(PORTAL_TOTAL_RECORDS,0) as PORTAL_TOTAL_RECORDS,ifnull(MisIdentifyAmt,0) as MisIdentifyAmt,\
ifnull(MisIdentifyReason,'NIL') as MisIdentifyReason from DERIVED_Data.PortalvsPG_app \
where merchant_name = '%s' and date(ReportDate) between '%s' and '%s' order by ReportDate desc"\
%(self.ms_name,start_dt,end_dt)
res_set = self.tmsconn.get_all_results(query2)
pacnet_mismatch="select ReportDate,ifnull(PG_TOTAL_AMT,0) as PG_TOTAL_AMT,ifnull(PORTAL_TOTAL_AMT,0) as PORTAL_TOTAL_AMT,ifnull(PG_TOTAL_RECORDS,0)\
as PG_TOTAL_RECORDS,ifnull(PORTAL_TOTAL_RECORDS,0) as PORTAL_TOTAL_RECORDS,ifnull(MisIdentifyAmt,0) as MisIdentifyAmt,\
ifnull(MisIdentifyReason,'NIL') as MisIdentifyReason from DERIVED_Data.PortalvsPG_app \
where merchant_name = '%s' and ReportDate between '2013-01-01' and DATE_SUB(curdate(),INTERVAL 33 day) and PG_TOTAL_AMT!=PORTAL_TOTAL_AMT\
and (abs((PG_TOTAL_AMT-PORTAL_TOTAL_AMT)) - ifnull(MisIdentifyAmt,0.00))"%(self.ms_name)
pacnet_res=self.tmsconn.get_all_results(pacnet_mismatch)
dup_sel_sql="select trackingnumber as orderid,Amount as amount from DERIVED_Data.%s"%(self.ms_name+"_portal_dup")
res_dup_sel_sql=self.tmsconn.get_all_results(dup_sel_sql)
if len(res_dup_sel_sql)>15:
dup_sel_sql="select concat(count(trackingnumber),'(Total count)') as orderid,sum(Amount) as amount from DERIVED_Data.%s"%(self.ms_name+"_portal_dup")
res_dup_sel_sql=self.tmsconn.get_all_results(dup_sel_sql)
sub_pacnet_result = "select * from DERIVED_Data.PortalvsPG_app where merchant_name = '%s' and ReportDate between DATE_SUB(curdate(),INTERVAL 32 day) \
and DATE_SUB('%s',INTERVAL 2 day) and PG_TOTAL_AMT!=PORTAL_TOTAL_AMT and \
(abs((PG_TOTAL_AMT-PORTAL_TOTAL_AMT)) - ifnull(MisIdentifyAmt,0.00))!=0"%(self.ms_name, pcn_start_dt)
res_sub_pacnet_result = self.tmsconn.get_all_results(sub_pacnet_result)
if res_sub_pacnet_result or pacnet_res :
self.sub = "<p><b><font color = 'red'>"+ self.ms_name + " Portal and PG - Amount Comparison</font></b></p>"
else:
self.sub = ""
self.html_creation(res_set,start_dt,end_dt,res_dup_sel_sql,pacnet_res)
logger.info("PACNET Portal:: Success in Sending Mail ::")
except Exception,e:
print str(e)
self.flag=1
self.html_creation(self.run_date,self.run_date,self.run_date)