Subversion Repositories SmartDukaan

from xlrd import open_workbook
from datetime import date, datetime, timedelta
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from xlrd import open_workbook
from xlutils.copy import copy
from xlwt.Workbook import Workbook
import MySQLdb
import smtplib
import time
import xlwt
import pymongo
from datetime import datetime
from elixir import *
from sqlalchemy.sql.expression import func
from pyodbc import Row
import Dial2VerifyFile
import optparse

parser = optparse.OptionParser()
parser.add_option("-d", "--d", dest="dbHost",
                      default="localhost",
                      type="string", help="The HOST where the database is running",
                      metavar="db_host")

(options, args) = parser.parse_args()


DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"

INSERT_QUERY= """
INSERT INTO dial2verifyStatus (session_id, api_key, mobile_number, verification_status, transaction_time, verification_time) VALUES ('%s', '%s', '%s', '%s', '%s', '%s');"""

def getDbConnection():
    return MySQLdb.connect(options.dbHost, DB_USER, DB_PASSWORD, DB_NAME)

def insertFOSAddressData():
    numbers=[]
    session_id=''
    api_key=''
    mobile_number=''
    verifcation_status=''
    transaction_time=None
    verification_time=None
    ttime=None
    vtime=None
    wb = open_workbook('/home/kshitij/Downloads/Dial2Verify_UI_Data.xlsx')   
    for s in wb.sheets():
        for row in range(s.nrows):
            for col in range(s.ncols):
                if col==0:
                    session_id=s.cell(row,col).value        
                elif col==1:
                    api_key=s.cell(row,col).value    
                elif col==2:
                    mobile_number=s.cell(row,col).value        
                elif col==3:        
                    verifcation_status=s.cell(row,col).value
                elif col==4:        
                    transaction_time=s.cell(row,col).value
                    print transaction_time
                    ttime=datetime.strptime(transaction_time,'%m/%d/%Y %H:%M:%S %p')
                elif col==5:
                    verification_time=s.cell(row,col).value
                    if verification_time is not None and len(verification_time)>0:
                        vtime=datetime.strptime(verification_time,'%m/%d/%Y %H:%M:%S %p')
                    #if col==2:
                    #    numbers.append(s.cell(row,col).value)
            if vtime is not None:            
                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'))
                print strw
            else:
                strw1=INSERT_QUERY % (session_id,api_key,mobile_number,verifcation_status,ttime.strftime('%Y-%m-%d %H:%M:%S'),vtime)
                print strw1    
    #print numbers
    #cleanlist = []
    #[cleanlist.append(x) for x in numbers if x not in cleanlist]
    #print cleanlist
                
def insertVerificationData(filePath):
    session_id=''
    api_key=''
    mobile_number_new=''
    verifcation_status=''
    transaction_time=None
    verification_time=None
    ttime=None
    vtime=None
    squery=''
    wb = open_workbook(filePath)
    conn = getDbConnection()
    cur = conn.cursor()
    s=wb.sheet_by_index(0)
    for row in range(s.nrows):
        for col in range(s.ncols):
            if row==0:
                continue
            elif col==0:
                session_id=s.cell(row,col).value        
            elif col==1:
                api_key=s.cell(row,col).value    
            elif col==2:
                mobile_number=s.cell(row,col).value
                mobile_number_new =str(mobile_number)       
            elif col==3:        
                verifcation_status=s.cell(row,col).value
            elif col==4:        
                transaction_time=s.cell(row,col).value
                ttime=datetime.strptime(transaction_time,'%m/%d/%Y %H:%M:%S %p')
            elif col==5:
                verification_time=s.cell(row,col).value
                if verification_time is not None and len(verification_time)>0:
                    vtime=datetime.strptime(verification_time,'%m/%d/%Y %H:%M:%S %p')
                else:
                    vtime=None    
        if row>0:            
            if vtime is not None:            
                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'))
                cur.execute(squery)
                conn.commit()
            else:
                squery=INSERT_QUERY % (session_id,api_key,mobile_number_new[0:10],verifcation_status,ttime.strftime('%Y-%m-%d %H:%M:%S'),vtime)
                cur.execute(squery)
                conn.commit()                
                    
def main():
    #insertFOSAddressData()
    filePath = Dial2VerifyFile.main()
    insertVerificationData(filePath)
if __name__ == '__main__':
    main()