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;