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()