WebSVN – SmartDukaan – Diff – /trunk/PyDTR/src/scripts/UserSegmentationForLinks.py

 import MySQLdb
-import xlwt
-from email import encoders
-from email.mime.base import MIMEBase
-from email.mime.multipart import MIMEMultipart
-from xlrd import open_workbook
-from xlutils.copy import copy
-import smtplib
 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"
-#TMP_FILE = "/home/kshitij/Downloads/User_Segmentation_Links.xls"
-TMP_FILE = "User_Segmentation_Links.xls"
-SENDER = "cnc.center@shop2020.in"
-PASSWORD = "5h0p2o2o"
-SUBJECT = "User Segmentation Report"
-SMTP_SERVER = "smtp.gmail.com"
-SMTP_PORT = 587
-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()
 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 group by c.category_id,c.brand,c.user_id
+    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 group by m.categoryId,m.brand,m.userId
+    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 group by fo.category,fo.brand,fo.user_id
+    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' group by bp.category_id,bp.brand,bp.user_id
+    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(u.url,  '/', -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(u.url,  '/', -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 generateUserSegmentationLinksReport():
+def deleteUserForLinks():
-    datesql= USER_SEGMENTATION_LINK_QUERY
+    print '********** Deleting records from last 7 days tables *********'
     conn = getDbConnection()
-    global workbook
-    workbook = xlwt.Workbook()
-    worksheet = workbook.add_sheet("User Segmentation")
-    boldStyle = xlwt.XFStyle()
-    style = xlwt.XFStyle()
-    pattern = xlwt.Pattern()
-    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
-    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
-    style.pattern = pattern
-    f = xlwt.Font()
-    f.bold = True
-    boldStyle.font = f
-    column = 0
-    row = 0
-    worksheet.write(row,column,'User Id',style)
-    worksheet.write(row,column+1,'Product Name',style)
-    worksheet.write(row,column+2,'Brand',style)
-    worksheet.write(row,column+3,'Category Id',style)
-    worksheet.write(row,column+4,'Count',style)
-    worksheet.write(row,column+5,'Source',style)
-    worksheet.write(row,column+6,'Weight',style)
     cursor = conn.cursor()
-    cursor.execute(datesql)
-    result = cursor.fetchall()
+    deleteAllUserGroups = "delete from userlinkssegmentation7days"
+    try:
-    for r in result:
-        row=row+1
-        worksheet.write(row,column,r[0])
-        worksheet.write(row,column+1,r[1])
-        worksheet.write(row,column+2,r[2])
+        cursor.execute(deleteAllUserGroups)
-        worksheet.write(row,column+3,r[3])
+        conn.commit()
-        worksheet.write(row,column+4,r[4])
+    except:
-        worksheet.write(row,column+5,r[5])
+        conn.rollback()
-        worksheet.write(row,column+6,r[6])
+    finally:
-    conn.close()
+        conn.close()
-    workbook.save(TMP_FILE)
+        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=''
         else:
             category_id=r[2]
         count=r[3]
         source=r[4]
         weight=r[5]
-        sql = "insert into userlinkssegmentation (user_id, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s,%s)"
+        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 deleteUserForLinks():
+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:
-    deleteAllUserGroups = "delete from userlinkssegmentation"
+        print 'No Data to be sanitized for repeating orders'
-    try:
+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(deleteAllUserGroups)
+        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])
-    except:
+    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()
-        conn.rollback()
+    cursor = conn.cursor()
+    cursor.execute(datesql,(userId,brand))
+    result = cursor.fetchall()
-    finally:
+    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.close()
+        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():
-    #generateUserSegmentationLinksReport()
+    print datetime.now()
+    print '********** Starting to update links for Users *********'
     deleteUserForLinks()
     addUsersForLinks()
-def sendmail(email, message, fileName, title):
-    if email == "":
-        return
+    filters()
-    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
-    mailServer.ehlo()
+    storeProductView()
-    mailServer.starttls()
+    findRepeatingOrders()
-    mailServer.ehlo()
+    findRepeatingClicks()
-    msg = MIMEMultipart()
+    checkCountForUpdate()
-    msg['Subject'] = title
-    msg.preamble = title
-    fileMsg = MIMEBase('application', 'vnd.ms-excel')
-    fileMsg.set_payload(file(TMP_FILE).read())
-    encoders.encode_base64(fileMsg)
-    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
+    print '********** Finished updating records for user links *********'
-    msg.attach(fileMsg)
-    MAILTO = ['manas.kapoor@saholic.com']
-    mailServer.login(SENDER, PASSWORD)
-    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
 if __name__=='__main__':
     main()

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/scripts/UserSegmentationForLinks.py – Rev 16362 → 16625