Subversion Repositories SmartDukaan

Rev

Rev 16358 | Rev 16360 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 16358 Rev 16359
Line 28... Line 28...
28 
datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'
 28 
datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'
29 
 
 29 
 
30 
default_format = xlwt.XFStyle()
 30 
default_format = xlwt.XFStyle()
31 
 
 31 
 
32 
USER_SEGMENTATION_LINK_QUERY="""
 32 
USER_SEGMENTATION_LINK_QUERY="""
33 
select x.user_id,x.product_name,x.brand,x.category_id,x.count,x.source,x.weight
33 
select x.user_id,x.brand,x.category_id,x.count,x.source,x.weight
34 
  from
 34 
  from
35 
    (
 35 
    (
36 
    select c.user_id,c.product_name,c.brand,c.category_id,count(c.id) as count,"clicks" as source,(uw.weightage * count(c.id)) as weight
36 
    select c.user_id,c.brand,c.category_id,count(c.id) as count,"clicks" as source,(uw.weightage * count(c.id)) as weight
37 
    from clicks c join user_segmentation_weigthage uw on "clicks"=uw.weightage_type
 37 
    from clicks c join user_segmentation_weigthage uw on "clicks"=uw.weightage_type
38 
    where  c.category_id is not null and c.category_id !=0 and c.brand is not null and
38 
    where  c.category_id is not null and c.category_id !=0 and c.brand is not null and
39 
    c.brand not like '' and c.user_id!=0 group by c.category_id,c.brand,c.store_product_id,c.user_id
 39 
    c.brand not like '' and c.user_id!=0 group by c.category_id,c.brand,c.user_id
40
40
41 
    UNION
 41 
    UNION
42
42
43 
    select m.userId as user_id,m.productName as product_name,m.brand,m.categoryId as category_id,count(1) as count,"orders" as source,(uw.weightage * count(1)) as weight
 43 
    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
44 
    from merchantsuborders m join user_segmentation_weigthage uw on "orders"=uw.weightage_type
44 
    from merchantsuborders m join user_segmentation_weigthage uw on "orders"=uw.weightage_type
45 
    where m.categoryId is not null and m.brand is not null and m.userId!=0 group by m.categoryId,m.brand,m.productCode,m.userId
 45 
    where m.categoryId is not null and m.brand is not null and m.userId!=0 group by m.categoryId,m.brand,m.userId
46
46
47 
    UNION
 47 
    UNION
48
48
49 
    select fo.user_id,fo.title as product_name,fo.brand,fo.category as category_id,count(fo.id) as count,"orders" as source, (uw.weightage * count(fo.id)) as weight
 49 
    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
50 
    from flipkartorders fo join user_segmentation_weigthage uw on "orders"=uw.weightage_type
50 
    from flipkartorders fo join user_segmentation_weigthage uw on "orders"=uw.weightage_type
51 
    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.catalogId,fo.user_id
 51 
    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
52
52
53 
    UNION
 53 
    UNION
54
54
55 
    select uf.user_id,uf.filters as product_name,"NULL" as brand,"NULL" as category_id,count(1) as count,
 55 
    select bp.user_id,bp.brand,bp.category_id,count(1) as count,"preferences" as source,(uw.weightage* count(1) as weight
56 
    "filters" as source,(uw.weightage * count(1)) as weight from user_filters uf join user_segmentation_weigthage uw on "filters"=uw.weightage_type  where uf.type='brand' group by uf.filters,uf.user_id
 56 
    from brand_preferences bp join user_segmentation_weigthage uw on "preferences"=uw.weightage_type
- 
 
 57 
    where user_id!=0 and status='show' group by category_id,brand,user_id;
- 
 
 58 
 
57 
    ) as x
59 
) as x
58 
 
 60 
 
59 
"""
 61 
"""
60 
 
 62 
 
61 
 
 63 
 
62 
def getDbConnection():
 64 
def getDbConnection():
Line 108... Line 110...
108 
    workbook.save(TMP_FILE)
110 
    workbook.save(TMP_FILE)
109 
 
 111 
 
110 
 
 112 
 
111 
def addUsersForLinks():
 113 
def addUsersForLinks():
112 
    user_id=''
 114 
    user_id=''
113 
    product_name=''
 - 
 
114 
    brand=''
 115 
    brand=''
115 
    category_id=''
116 
    category_id=''
116 
    source=''
 117 
    source=''
117 
    count=''
 118 
    count=''
118 
    weight=''
 119 
    weight=''
Line 122... Line 123...
122 
    cursor = conn.cursor()
 123 
    cursor = conn.cursor()
123 
    cursor.execute(datesql)
 124 
    cursor.execute(datesql)
124 
    result = cursor.fetchall()
 125 
    result = cursor.fetchall()
125
126
126 
    for r in result:
 127 
    for r in result:
127 
        print r[3]
 - 
 
128 
        user_id=r[0]
 128 
        user_id=r[0]
129 
        product_name=r[1]
 - 
 
130 
        brand=r[2]
 129 
        brand=r[1]
131 
        if r[3] == 'Mobiles':
 130 
        if r[2] == 'Mobiles':
132 
            category_id=3
 131 
            category_id=3
133 
        elif r[3] == 'Tablets':
 132 
        elif r[2] == 'Tablets':
134 
            category_id=5
 133 
            category_id=5
135 
        else:
 134 
        else:
136 
            category_id=r[3]
135 
            category_id=r[2]
137 
        source=r[5]
 136 
        source=r[3]
138 
        count=r[4]
 137 
        count=r[4]
139 
        weight=r[6]
 138 
        weight=r[5]
140
139
141 
        sql = "insert into userlinkssegmentation (user_id, product_name, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s, %s,%s)"
 140 
        sql = "insert into userlinkssegmentation (user_id, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s,%s)"
142 
        print sql + ' ' +str(product_name)
 - 
 
143 
        cursor.execute(sql,(user_id,product_name,brand, category_id, source, count,weight))
 141 
        cursor.execute(sql,(user_id,brand, category_id, source, count,weight))
144 
        conn.commit()
 142 
        conn.commit()
145 
 
 143 
 
146 
    conn.close()
 144 
    conn.close()
147
145
148 
def deleteUserForLinks():
 146 
def deleteUserForLinks():
Line 181... Line 179...
181 
    mailServer.login(SENDER, PASSWORD)
 179 
    mailServer.login(SENDER, PASSWORD)
182 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
 180 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
183 
 
 181 
 
184 
 
 182 
 
185 
if __name__=='__main__':
 183 
if __name__=='__main__':
186 
    main()
 - 
 
187 

            184
            
    main()

          

          

            -
            
 

            185