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 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 %I:%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 %I:%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 deleteDial2VerifyRecords():

    conn = getDbConnection()

    datesql="delete from dial2verifyStatus"

    cursor = conn.cursor()

    cursor.execute(datesql)

    conn.commit()           

def main():

    filePath = Dial2VerifyFile.main()

    deleteDial2VerifyRecords()

    insertVerificationData(filePath)

if __name__ == '__main__':

    main()