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');"""

CHECK_VERIFIED_USERS="""

select a.id,a.verification_time from  (select d.mobile_number,u.mobile_number as ue,u.id,u.email,d.verification_status, d.verification_time,u.mobile_number_last_updated, timediff(d.verification_time,u.mobile_number_last_updated) as ti,u.mobile_verified  from dial2verifyStatus d join users u on d.mobile_number=u.mobile_number  where d.verification_status='Verified' and u.mobile_verified!=1  and lower(u.referrer) not like 'emp%'  and timediff(d.verification_time,u.mobile_number_last_updated) < time('00:05:00')  and timediff(d.verification_time,u.mobile_number_last_updated) >= time('00:00:00')) a  where a.mobile_number=a.ue  and date(a.verification_time)<=(curdate() - interval 1 day) group by a.id;

"""

UPDATE_USER_VERIFIED_STATUS="""

update users set mobile_verified=1, mobile_number_last_updated='%s' where id=%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()                

    conn.close()

def deleteDial2VerifyRecords():

    conn = getDbConnection()

    datesql="delete from dial2verifyStatus"

    cursor = conn.cursor()

    cursor.execute(datesql)

    conn.commit()

    conn.close()

def checkUsersStatus():

    conn = getDbConnection()

    datesql=CHECK_VERIFIED_USERS

    cursor = conn.cursor()

    cursor.execute(datesql)

    result = cursor.fetchall()

    for r in result:

        updateUserStatus(r[0],r[1])

    conn.close()

def updateUserStatus(userId,updateDate):

    conn = getDbConnection()

    datesql=UPDATE_USER_VERIFIED_STATUS%(updateDate,userId)

    print datesql

    cursor = conn.cursor()

    cursor.execute(datesql)

    conn.commit()

    conn.close()

def main():

    filePath = Dial2VerifyFile.main()

    deleteDial2VerifyRecords()

    insertVerificationData(filePath)

    checkUsersStatus()

if __name__ == '__main__':

    main()