Subversion Repositories SmartDukaan

import MySQLdb
from dtr.storage.DataService import brand_preferences
from elixir import *
from dtr.storage import DataService, Mongo
from dtr.storage.DataService import user_filters
from sqlalchemy.sql.expression import func, between
from datetime import datetime,timedelta
DataService.initialize(echo=True)

DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"

USER_SEGMENTATION_LINK_QUERY="""
select x.user_id,x.brand,x.category_id,x.count,x.source,x.weight 
  from
    (
    select c.user_id,c.brand,c.category_id,count(c.id) as count,"clicks" as source,(uw.weightage * count(c.id)) as weight 
    from clicks c join user_segmentation_weigthage uw on "clicks"=uw.weightage_type
    where  c.category_id is not null and c.category_id !=0 and c.brand is not null and 
    c.brand not like '' and c.user_id!=0 and datediff(curdate(),c.created)<7 group by c.category_id,c.brand,c.user_id
    
    UNION
   
    select m.userId as user_id,m.brand,m.categoryId as category_id,count(1) as count,"orders" as source,(uw.weightage * count(1)) as weight
    from merchantsuborders m join user_segmentation_weigthage uw on "orders"=uw.weightage_type 
    where m.categoryId is not null and m.brand is not null and m.userId!=0 and datediff(curdate(),m.createdTime)<7 group by m.categoryId,m.brand,m.userId
  
    UNION
  
    select fo.user_id,fo.brand,fo.category as category_id,count(fo.id) as count,"orders" as source, (uw.weightage * count(fo.id)) as weight
    from flipkartorders fo join user_segmentation_weigthage uw on "orders"=uw.weightage_type 
    where  fo.category is not null and fo.brand is not null and  fo.brand not like '' and fo.user_id!=0 and fo.user_id is not null 
    and datediff(curdate(),fo.created)<7 group by fo.category,fo.brand,fo.user_id
    
    UNION
    
    select bp.user_id,bp.brand,bp.category_id,count(1) as count,"preferences" as source,(uw.weightage* count(1)) as weight
    from brand_preferences bp join user_segmentation_weigthage uw on "preferences"=uw.weightage_type  
    where user_id!=0 and status='show' and datediff(curdate(),bp.created)<7 group by bp.category_id,bp.brand,bp.user_id
) as x

"""

USER_STORE_PRODUCT_VIEW="""
    SELECT u.user_id,count(u.id) AS count, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(u.url,  '/', -1 ),'?',1),'%',1),'#',1)  
    AS skuBundleId,"product_view" as source,(uw.weightage* count(u.id)) as weight
    FROM user_urls u join user_segmentation_weigthage uw on "product_view"=uw.weightage_type 
    WHERE u.url LIKE '%store_products/view%' and u.user_id!=0 and 
    SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(u.url,  '/', -1 ),'?',1),'%',1),'#',1) not like ''
    and datediff(curdate(),u.created)<7
    group by u.user_id,skuBundleId; 
"""

REPLACE_QUERY="""
REPLACE INTO userlinkssegmentation( user_id, brand, source,category_id, count, weight ) 
SELECT user_id, brand, source,category_id, count, weight
FROM userlinkssegmentation7days
WHERE user_id =%s and brand=%s and category_id=%s
"""


userMap = {} #key - user_id val - brand map
userMobileCategory={}
userTabletCategory={}

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


def deleteUserForLinks():
    print '********** Deleting records from last 7 days tables *********'
    conn = getDbConnection()
    cursor = conn.cursor()
    deleteAllUserGroups = "delete from userlinkssegmentation7days"
    try:
        cursor.execute(deleteAllUserGroups)
        conn.commit()
    except:
        conn.rollback()
    finally:
        conn.close()    
        print '********** Completed Deleting records from last 7 days tables *********'

def addUsersForLinks():
    print '********** Inserting records in last 7 days table *********'
    user_id=''
    brand=''
    category_id='' 
    source=''
    count=''
    weight=''

    datesql= USER_SEGMENTATION_LINK_QUERY
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    
    for r in result:
        user_id=r[0]
        brand=r[1]
        if r[2] == 'Mobiles':
            category_id=3
        elif r[2] == 'Tablets':
            category_id=5
        else:
            category_id=r[2] 
        count=r[3]
        source=r[4]
        weight=r[5]
        sql = "insert into userlinkssegmentation7days (user_id, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s,%s)"
        cursor.execute(sql,(user_id,brand, category_id, source, count,weight))
        conn.commit()
    conn.close()
    print '********** Completed inserting records for orders,preferences and clicks in last 7 days table *********'
    
def filters():
    print '********** Inserting records for filters in last 7 day table *********'
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute("select weightage from user_segmentation_weigthage where weightage_type='filters'")
    result = cursor.fetchall()
    filter_weight=result[0][0]
    todayDate=datetime.today().date()
    data = session.query((user_filters)).filter_by(type="brand").filter(user_filters.created.between(todayDate-timedelta(days=6),todayDate)).all()
    for d in data:
        if userMap.has_key(d.user_id):
            brandMap = userMap.get(d.user_id) 
        else:
            brandMap = {}
        brands = d.filters.split('|')
        for brand in brands:
            if brandMap.has_key(brand):
                brandMap[brand] = brandMap.get(brand) + 1
            else:
                brandMap[brand] = 1
        userMap[d.user_id] = brandMap
    for x, y in userMap.iteritems():
        for bra,cow in y.iteritems():
            user_id=x
            brand=bra
            count=cow
            category_id=3
            weight=cow*filter_weight
            source='filters'
            sql = "insert into userlinkssegmentation7days (user_id, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s,%s)"
            cursor.execute(sql,(user_id,brand, category_id, source, count,weight))
            conn.commit()
    
    conn.close()
    print '********** Completed inserting records for filters in last 7 day table *********'
    
def storeProductView():
    print '********** Inserting records for store product view in last 7 day table *********'
    user_id=''
    brand=''
    category_id='' 
    source=''
    count=''
    weight=''

    datesql= USER_STORE_PRODUCT_VIEW
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    
    for r in result:
        skuData = Mongo.getSkuBrandData(int(r[2]))
        if len(skuData) !=0:
            user_id=r[0]
            brand=skuData['brand']
            category_id =skuData['category_id']
            source=r[3]
            count=r[1]
            if category_id==3:
                if userMobileCategory.has_key(user_id):
                    brandMap = userMobileCategory.get(user_id)
                else:
                    brandMap = {}
                if brandMap.has_key(brand):
                    brandMap[brand] = brandMap.get(brand) + r[1]
                else:
                    brandMap[brand] = r[1]
                userMobileCategory[user_id] = brandMap
            if category_id==5:
                if userTabletCategory.has_key(user_id):
                    brandCategoryMap = userTabletCategory.get(user_id)
                else:
                    brandCategoryMap = {}
                if brandCategoryMap.has_key(brand):
                    brandCategoryMap[brand] = brandCategoryMap.get(brand) + r[1]
                else:
                    brandCategoryMap[brand] = r[1]
                userTabletCategory[user_id] = brandCategoryMap    
        else:
            print 'Sku bundle Id not present',r[2]
    
    
    cursor.execute("select weightage from user_segmentation_weigthage where weightage_type='product_view'")
    result = cursor.fetchall()
    product_view_weight=result[0][0]
    
    for x, y in userMobileCategory.iteritems():
        for bra,cow in y.iteritems():
            user_id=x
            brand= bra
            count=cow
            weight=cow*product_view_weight
            source='product_view'
            category_id=3
            sql = "insert into userlinkssegmentation7days (user_id, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s,%s)"
            cursor.execute(sql,(user_id,brand, category_id, source, count,weight))
            conn.commit()
            
    for x, y in userTabletCategory.iteritems():
        for bra,cow in y.iteritems():
            user_id=x
            brand= bra
            count=cow
            weight=cow*product_view_weight
            source='product_view'
            category_id=5
            sql = "insert into userlinkssegmentation7days (user_id, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s,%s)"
            cursor.execute(sql,(user_id,brand, category_id, source, count,weight))
            conn.commit()
    conn.close()
    
    print '********** Completed inserting records for store product view in last 7 day table *********'
    
def findRepeatingOrders():
    print '********** Call to sanitize data for orders if any multiple *********'
    datesql= "select user_id,brand,category_id from userlinkssegmentation7days where source='orders' group by user_id,brand,category_id having count(source)=2;"
    conn = getDbConnection()
    cursor = conn.cursor()
    rows_count = cursor.execute(datesql)
    if rows_count>0:
        result = cursor.fetchall()
        for r in result:
            print r
            findDetails(r[0],r[1],r[2])
    else:
        print 'No Data to be sanitized for repeating orders'            
        
def findDetails(userId,brand,category):
    datesql= "select sum(count),sum(weight) from userlinkssegmentation7days where user_id=%s and source='orders' and brand=%s group by source;"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql,(userId,brand))
    result = cursor.fetchall()
    for r in result:
        deletesql="delete from userlinkssegmentation7days where user_id=%s and brand=%s and source='orders'"
        cursor = conn.cursor()
        cursor.execute(deletesql,(userId,brand))
        conn.commit()
        insertsql="insert into userlinkssegmentation7days (user_id, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s,%s)"
        cursor = conn.cursor()
        cursor.execute(insertsql,(userId,brand,category,'orders',r[0],r[1]))
        conn.commit()

def findRepeatingClicks():
    print '********** Call to sanitize data for product view if any multiple *********'
    datesql= "select user_id,brand,category_id from userlinkssegmentation7days where source='product_view' group by user_id,brand,category_id having count(source)=2;"
    conn = getDbConnection()
    cursor = conn.cursor()
    rows_count = cursor.execute(datesql)
    if rows_count>0:
        result = cursor.fetchall()
        for r in result:
            print r
            findViewDetails(r[0],r[1],r[2])
    else:
        print 'No Data to be sanitized for product view'            
        
def findViewDetails(userId,brand,category):
    datesql= "select sum(count),sum(weight) from userlinkssegmentation7days where user_id=%s and source='product_view' and brand=%s group by source;"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql,(userId,brand))
    result = cursor.fetchall()
    for r in result:
        deletesql="delete from userlinkssegmentation7days where user_id=%s and brand=%s and source='product_view'"
        cursor = conn.cursor()
        cursor.execute(deletesql,(userId,brand))
        conn.commit()
        insertsql="insert into userlinkssegmentation7days (user_id, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s,%s)"
        cursor = conn.cursor()
        cursor.execute(insertsql,(userId,brand,category,'product_view',r[0],r[1]))
        conn.commit()        
    
    
def checkCountForUpdate():
    print '********** Checking users brand whose cumulative weight is greater than 3 *********'
    datesql= "select user_id,brand,category_id from userlinkssegmentation7days group by user_id,brand having sum(weight) >=3;"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    for r in result:
        checkUserForUpdate(r[0],r[1],r[2])
    conn.close()    
def checkUserForUpdate(userId,brand,category):
    print '********** Checking users brand present or not in the master table *********'
    datesql= "select distinct brand from userlinkssegmentation where user_id=%s and brand=%s and category_id=%s" 
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql,(userId,brand,category))
    result = cursor.fetchall()
    if len(result)==0:
        insertNewUserLinkDetails(userId,brand,category)
    else:
        userReplaceData(userId,brand,category)
    conn.close()    

def userReplaceData(userId,brand,category):
    print 'Data that needs to be updated for', userId,brand,category
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(REPLACE_QUERY,(userId,brand,category))
    conn.commit()
    conn.close()

def insertNewUserLinkDetails(userId,brand,category):
    print 'Data needs to be inserted as the brand was not present earlier', userId,brand,category
    conn = getDbConnection()
    selectNewUser="select * from userlinkssegmentation7days where user_id=%s and brand=%s and category_id=%s"
    cursor = conn.cursor()
    cursor.execute(selectNewUser,(userId,brand,category))
    result = cursor.fetchall()
    print 'Result while inserting new record in master',result
    for r in result:
        sql = "insert into userlinkssegmentation (user_id, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s,%s)"
        cursor.execute(sql,(r[0],r[1],r[2], r[3], r[4],r[5]))
        conn.commit()
    conn.close()    
def main():
    print datetime.now()
    print '********** Starting to update links for Users *********'
    deleteUserForLinks()
    addUsersForLinks()
    filters()
    storeProductView()
    findRepeatingOrders()
    findRepeatingClicks()
    checkCountForUpdate()
    print '********** Finished updating records for user links *********'
if __name__=='__main__':
    main()