| 16136 |
kshitij.so |
1 |
from xlrd import open_workbook
|
|
|
2 |
from datetime import date, datetime, timedelta
|
|
|
3 |
from email import encoders
|
|
|
4 |
from email.mime.base import MIMEBase
|
|
|
5 |
from email.mime.multipart import MIMEMultipart
|
|
|
6 |
from email.mime.text import MIMEText
|
|
|
7 |
from xlrd import open_workbook
|
|
|
8 |
from xlutils.copy import copy
|
|
|
9 |
from xlwt.Workbook import Workbook
|
|
|
10 |
import MySQLdb
|
|
|
11 |
import smtplib
|
|
|
12 |
import time
|
|
|
13 |
import xlwt
|
|
|
14 |
import pymongo
|
|
|
15 |
from datetime import datetime
|
|
|
16 |
from elixir import *
|
|
|
17 |
from sqlalchemy.sql.expression import func
|
|
|
18 |
from pyodbc import Row
|
|
|
19 |
import Dial2VerifyFile
|
|
|
20 |
import optparse
|
|
|
21 |
|
|
|
22 |
parser = optparse.OptionParser()
|
|
|
23 |
parser.add_option("-d", "--d", dest="dbHost",
|
|
|
24 |
default="localhost",
|
|
|
25 |
type="string", help="The HOST where the database is running",
|
|
|
26 |
metavar="db_host")
|
|
|
27 |
|
|
|
28 |
(options, args) = parser.parse_args()
|
|
|
29 |
|
|
|
30 |
|
|
|
31 |
DB_USER = "root"
|
|
|
32 |
DB_PASSWORD = "shop2020"
|
|
|
33 |
DB_NAME = "dtr"
|
|
|
34 |
|
|
|
35 |
INSERT_QUERY= """
|
|
|
36 |
INSERT INTO dial2verifyStatus (session_id, api_key, mobile_number, verification_status, transaction_time, verification_time) VALUES ('%s', '%s', '%s', '%s', '%s', '%s');"""
|
|
|
37 |
|
|
|
38 |
def getDbConnection():
|
|
|
39 |
return MySQLdb.connect(options.dbHost, DB_USER, DB_PASSWORD, DB_NAME)
|
|
|
40 |
|
|
|
41 |
def insertFOSAddressData():
|
|
|
42 |
numbers=[]
|
|
|
43 |
session_id=''
|
|
|
44 |
api_key=''
|
|
|
45 |
mobile_number=''
|
|
|
46 |
verifcation_status=''
|
|
|
47 |
transaction_time=None
|
|
|
48 |
verification_time=None
|
|
|
49 |
ttime=None
|
|
|
50 |
vtime=None
|
|
|
51 |
wb = open_workbook('/home/kshitij/Downloads/Dial2Verify_UI_Data.xlsx')
|
|
|
52 |
for s in wb.sheets():
|
|
|
53 |
for row in range(s.nrows):
|
|
|
54 |
for col in range(s.ncols):
|
|
|
55 |
if col==0:
|
|
|
56 |
session_id=s.cell(row,col).value
|
|
|
57 |
elif col==1:
|
|
|
58 |
api_key=s.cell(row,col).value
|
|
|
59 |
elif col==2:
|
|
|
60 |
mobile_number=s.cell(row,col).value
|
|
|
61 |
elif col==3:
|
|
|
62 |
verifcation_status=s.cell(row,col).value
|
|
|
63 |
elif col==4:
|
|
|
64 |
transaction_time=s.cell(row,col).value
|
|
|
65 |
print transaction_time
|
|
|
66 |
ttime=datetime.strptime(transaction_time,'%m/%d/%Y %H:%M:%S %p')
|
|
|
67 |
elif col==5:
|
|
|
68 |
verification_time=s.cell(row,col).value
|
|
|
69 |
if verification_time is not None and len(verification_time)>0:
|
|
|
70 |
vtime=datetime.strptime(verification_time,'%m/%d/%Y %H:%M:%S %p')
|
|
|
71 |
#if col==2:
|
|
|
72 |
# numbers.append(s.cell(row,col).value)
|
|
|
73 |
if vtime is not None:
|
|
|
74 |
strw=INSERT_QUERY % (session_id,api_key,mobile_number,verifcation_status,ttime.strftime('%Y-%m-%d %H:%M:%S'),vtime.strftime('%Y-%m-%d %H:%M:%S'))
|
|
|
75 |
print strw
|
|
|
76 |
else:
|
|
|
77 |
strw1=INSERT_QUERY % (session_id,api_key,mobile_number,verifcation_status,ttime.strftime('%Y-%m-%d %H:%M:%S'),vtime)
|
|
|
78 |
print strw1
|
|
|
79 |
#print numbers
|
|
|
80 |
#cleanlist = []
|
|
|
81 |
#[cleanlist.append(x) for x in numbers if x not in cleanlist]
|
|
|
82 |
#print cleanlist
|
|
|
83 |
|
|
|
84 |
def insertVerificationData(filePath):
|
|
|
85 |
session_id=''
|
|
|
86 |
api_key=''
|
|
|
87 |
mobile_number_new=''
|
|
|
88 |
verifcation_status=''
|
|
|
89 |
transaction_time=None
|
|
|
90 |
verification_time=None
|
|
|
91 |
ttime=None
|
|
|
92 |
vtime=None
|
|
|
93 |
squery=''
|
|
|
94 |
wb = open_workbook(filePath)
|
|
|
95 |
conn = getDbConnection()
|
|
|
96 |
cur = conn.cursor()
|
|
|
97 |
s=wb.sheet_by_index(0)
|
|
|
98 |
for row in range(s.nrows):
|
|
|
99 |
for col in range(s.ncols):
|
|
|
100 |
if row==0:
|
|
|
101 |
continue
|
|
|
102 |
elif col==0:
|
|
|
103 |
session_id=s.cell(row,col).value
|
|
|
104 |
elif col==1:
|
|
|
105 |
api_key=s.cell(row,col).value
|
|
|
106 |
elif col==2:
|
|
|
107 |
mobile_number=s.cell(row,col).value
|
|
|
108 |
mobile_number_new =str(mobile_number)
|
|
|
109 |
elif col==3:
|
|
|
110 |
verifcation_status=s.cell(row,col).value
|
|
|
111 |
elif col==4:
|
|
|
112 |
transaction_time=s.cell(row,col).value
|
|
|
113 |
ttime=datetime.strptime(transaction_time,'%m/%d/%Y %H:%M:%S %p')
|
|
|
114 |
elif col==5:
|
|
|
115 |
verification_time=s.cell(row,col).value
|
|
|
116 |
if verification_time is not None and len(verification_time)>0:
|
|
|
117 |
vtime=datetime.strptime(verification_time,'%m/%d/%Y %H:%M:%S %p')
|
|
|
118 |
else:
|
|
|
119 |
vtime=None
|
|
|
120 |
if row>0:
|
|
|
121 |
if vtime is not None:
|
|
|
122 |
squery=INSERT_QUERY % (session_id,api_key,mobile_number_new[0:10],verifcation_status,ttime.strftime('%Y-%m-%d %H:%M:%S'),vtime.strftime('%Y-%m-%d %H:%M:%S'))
|
|
|
123 |
cur.execute(squery)
|
|
|
124 |
conn.commit()
|
|
|
125 |
else:
|
|
|
126 |
squery=INSERT_QUERY % (session_id,api_key,mobile_number_new[0:10],verifcation_status,ttime.strftime('%Y-%m-%d %H:%M:%S'),vtime)
|
|
|
127 |
cur.execute(squery)
|
|
|
128 |
conn.commit()
|
|
|
129 |
|
|
|
130 |
def main():
|
|
|
131 |
#insertFOSAddressData()
|
|
|
132 |
filePath = Dial2VerifyFile.main()
|
|
|
133 |
insertVerificationData(filePath)
|
|
|
134 |
if __name__ == '__main__':
|
|
|
135 |
main()
|