Subversion Repositories SmartDukaan

'''
Created on 01-Jul-2015

@author: manas
'''
from datetime import date, datetime, timedelta
import MySQLdb
import time
import xlwt
from datetime import datetime

DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"
TMP_FILE = "/tmp/User_Address_Report.xls"  
con = None

date_format = xlwt.XFStyle()
date_format.num_format_str = 'yyyy/mm/dd'

datetime_format = xlwt.XFStyle()
datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'

default_format = xlwt.XFStyle()

NON_REGISTERED_USERS="""
SELECT  p.user_id as count FROM pushnotifications p join 
notification_campaigns n on p.notification_campaign_id=n.id 
WHERE p.message = 'NotRegistered' 
group by p.user_id having count(1) >=5;
"""

#select * from pushnotifications where datediff(now(),created) <= 7 and user_id=%s and status=1;
CHECK_NON_REGISTERED_USERS="""
select * from pushnotifications where datediff(now(),created) <=7 and datediff(now(),created)>=2 and user_id=%s and status=1;
"""

CHECK_STATUS_LAST_DAY_USERS="""
select * from pushnotifications where datediff(now(),created) <= 1 and user_id=%s and status=1;
"""

LAST_ACTIVE_QUERY="""
select last_active,datediff(now(),last_active) from useractive where user_id=%s; 
"""

INSERT_USER_ACTIVITY_STATUS= """INSERT INTO 
user_activity_status (user_id, comment,last_active,last_active_diff,first_not_registered_time,created) VALUES (%s,%s,%s,%s,%s,%s);"""

CHECK_USER_ACTIVITY_STATUS="""
select user_id,comment,last_active from user_activity_status where user_id=%s;
"""

DELETE_USER_ACTIVITY_STATUS="""
delete from user_activity_status where user_id=%s;
"""

CHECK_USER_PRESENT="""select * from user_activity_status where user_id=%s"""


CHECK_MAX_SUCCESS="""
select max(created) from pushnotifications where user_id=%s and message='success';
"""

CHECK_DATE_NOT_REGISTERED="""
select min(created) from pushnotifications where user_id=%s and message='NotRegistered' and created<'%s';
"""

CHECK_DATE_NOT_REGISTERED_ELSE="""
select min(created) from pushnotifications where user_id=%s and message='NotRegistered'
"""

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

def fetchNonRegisteredUsers():
    conn = getDbConnection()
    datesql=NON_REGISTERED_USERS 
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    print result
    for r in result:
        checkNonRegisteredUsers(r[0])


def checkNonRegisteredUsers(user_id):
    conn = getDbConnection()
    datesql=CHECK_NON_REGISTERED_USERS%user_id
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = len(cursor.fetchall())
    if result !=0:
        print 'Ignoring the userId since it has received one notification in the last 7 days ' + str(user_id)
    else:
        checkUserInTable(user_id)
        
    
def checkUserInTable(user_id):
    conn = getDbConnection()
    datesql= CHECK_USER_PRESENT%user_id
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = len(cursor.fetchall())
    if result==0:
        print 'User not present. Adding UserId' + str(user_id)
        lastActiveStatus(user_id)
    else:
        print 'UserId already present' + str(user_id)  
        checkSuccessLastDay(user_id)   


def checkSuccessLastDay(user_id):
    conn = getDbConnection()
    datesql= CHECK_STATUS_LAST_DAY_USERS%user_id
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    if len(result)==0:
        print 'User already present and need to check his status' + str(user_id)
        checkUserActivityStatus(user_id,0)
    else:
        print 'User Needs to be deleted if its present' + str(user_id)
        checkUserActivityStatus(user_id,1)


    
def lastActiveStatus(user_id):
    conn = getDbConnection()
    datesql=LAST_ACTIVE_QUERY%user_id
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    last_active_difference=0
    last_active=''
    status=''
    dateNotRegistered = checknotRegisteredDate(user_id)
    if len(result)==0:
        status='No activity'
        last_active_difference = 'NA'
        last_active='NA'
    else:    
        for r in result:
            last_active_difference = r[1]
            last_active=r[0]
            print last_active
            if last_active_difference >=30:
                status= 'Uninstall'
            elif last_active_difference <30 and last_active_difference>=15:
                status = 'Probable uninstall'    
            elif last_active_difference <15:
                status = 'Notification issue'    
            else:
                status='Others'
    cursor.execute(INSERT_USER_ACTIVITY_STATUS, (user_id,status,last_active,last_active_difference,dateNotRegistered,datetime.now()))
    conn.commit()
    
def checknotRegisteredDate(user_id):
    conn = getDbConnection()
    datesql=CHECK_MAX_SUCCESS%user_id
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    for r in result:
        if r[0] is None:
            conn = getDbConnection()
            dateSql=CHECK_DATE_NOT_REGISTERED_ELSE%user_id
            #print 'In if' + dateSql
            cursor = conn.cursor()
            cursor.execute(dateSql)
            date = cursor.fetchall()
            #print 'Result in if ' + str(date)
            for d in date:
                return d[0]
        else:
            conn = getDbConnection()
            datesql=CHECK_DATE_NOT_REGISTERED%(user_id,r[0])
            #print datesql
            cursor = conn.cursor()
            cursor.execute(datesql)
            date = cursor.fetchall()
            #print 'Result in else ' + str(date)
            for r in date:
                if r[0] is None:
                    conn = getDbConnection()
                    datesql=CHECK_DATE_NOT_REGISTERED_ELSE%(user_id)
                    cursor = conn.cursor()
                    cursor.execute(datesql)
                    date = cursor.fetchall()
                    for r in date:
                        return r[0]
                    #print 'In else in if ' + str(date) 
                else:
                    return r[0]    

def lastActiveReturnStatus(user_id):
    conn = getDbConnection()
    datesql=LAST_ACTIVE_QUERY%user_id
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    last_active_difference=0
    last_active=''
    status=''
    if len(result)==0:
        status='No activity'
        last_active_difference = 'NA'
        last_active='NA'
    else:    
        for r in result:
            last_active_difference = r[1]
            last_active=r[0]
            print last_active
            if last_active_difference >=30:
                status= 'Uninstall'
            elif last_active_difference <30 and last_active_difference>=15:
                status = 'Probable uninstall'    
            elif last_active_difference <15:
                status = 'Notification issue'    
            else:
                status='Others'
    return status    
           
def checkUserActivityStatus(user_id,flag):
    conn = getDbConnection()
    datesql=CHECK_USER_ACTIVITY_STATUS%user_id
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    if len(result) ==0:
        print 'User not added into the system, hence ignored' + str(user_id)
    else:
        if flag==1:
            conn = getDbConnection()
            datesql=DELETE_USER_ACTIVITY_STATUS%user_id
            cursor = conn.cursor()
            cursor.execute(datesql)
            print str(user_id) +' User successfully deleted as he has become active'    
        elif flag==0:
            for r in result:
                if r[1]==lastActiveStatus(user_id):
                    print 'Status same as earlier hence not updated for UserId' + str(user_id)
                    pass
                else:
                    print 'Result different for userId' + str(user_id)
                    print r[1] + 'Status in '  + lastActiveReturnStatus(user_id)
                    #Need to update the status for the user here.    
                
def main():
    dropTable()
    fetchNonRegisteredUsers()
#    print(checknotRegisteredDate("3102"))

def dropTable():
    conn = getDbConnection()
    datesql="delete from user_activity_status"
    cursor = conn.cursor()
    cursor.execute(datesql)
    conn.commit()
    
if __name__ == '__main__':
    main()
#select min(created) from pushnotifications where message='NotRegistered' and user_id=2913;
#select p.status from pushnotifications p join notification_campaigns n on p.notification_campaign_id =n.id where datediff(now(),n.created)=1 and p.user_id=2913;