Subversion Repositories SmartDukaan

Rev

Rev 16312 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 16312 Rev 16385
Line 18... Line 18...
18 
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
 18 
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
19 
 
 19 
 
20 
DataService.initialize(db_hostname="localhost")
 20 
DataService.initialize(db_hostname="localhost")
21 
client = MongoClient('mongodb://localhost:27017/')
21 
client = MongoClient('mongodb://localhost:27017/')
22 
 
 22 
 
23 
USER_LINKS_SEGMENTATIONQUERY="""
 - 
 
24 
SELECT x.brand,
- 
 
25 
       x.userid,
- 
 
26 
       x.min_price,
- 
 
27 
       x.max_price,
- 
 
28 
       x.pricerange,
- 
 
29 
       x.category_id
- 
 
30 
FROM   (SELECT b.brand,
- 
 
31 
               b.user_id userid,
- 
 
32 
               p.min_price,
- 
 
33 
               p.max_price,
- 
 
34 
               p.category_id,
- 
 
35 
               Concat(p.min_price, '-', p.max_price) pricerange
- 
 
36 
        FROM   brand_preferences b
- 
 
37 
               LEFT OUTER JOIN price_preferences p
- 
 
38 
               ON  (b.user_id = p.user_id AND b.category_id=p.category_id)
 - 
 
39 
        WHERE  b.status = 'show'
- 
 
40 
               AND Trim(COALESCE(b.brand, '')) <> ''
- 
 
41 
               AND p.min_price > 0
- 
 
42 
               AND p.max_price > 0
- 
 
43 
        GROUP  BY b.brand,
- 
 
44 
                  pricerange,
- 
 
45 
                  p.category_id,
- 
 
46 
                  b.user_id
- 
 
47 
        UNION
- 
 
48 
        SELECT b.brand,
- 
 
49 
               b.user_id                             userid,
- 
 
50 
               p.min_price,
- 
 
51 
               p.max_price,
- 
 
52 
               b.category_id,
- 
 
53 
               Concat(p.min_price, '-', p.max_price) pricerange
- 
 
54 
        FROM   clicks b
- 
 
55 
               LEFT OUTER JOIN price_preferences p
- 
 
56 
               ON  (b.user_id = p.user_id AND b.category_id=p.category_id)
 - 
 
57 
        WHERE  Trim(COALESCE(b.brand, '')) <> ''
- 
 
58 
               AND p.min_price > 0
- 
 
59 
               AND p.max_price > 0
- 
 
60 
        GROUP  BY b.brand,
- 
 
61 
                  pricerange,
- 
 
62 
                  p.category_id,
- 
 
63 
                  b.category_id,
 - 
 
64 
                  b.user_id
- 
 
65 
        UNION
- 
 
66 
        SELECT b.brand,
- 
 
67 
               b.userid                              userid,
- 
 
68 
               p.min_price,
- 
 
69 
               p.max_price,
- 
 
70 
               p.category_id,
- 
 
71 
               Concat(p.min_price, '-', p.max_price) pricerange
- 
 
72 
        FROM   merchantsuborders b
- 
 
73 
               LEFT OUTER JOIN price_preferences p
- 
 
74 
               ON (b.userid = p.user_id AND b.categoryId =p.category_id)
 - 
 
75 
        WHERE  Trim(COALESCE(b.brand, '')) <> ''
- 
 
76 
               AND b.brand != 'NA'
- 
 
77 
               AND p.min_price > 0
- 
 
78 
               AND p.max_price > 0
- 
 
79 
        GROUP  BY b.brand,
- 
 
80 
                  pricerange,
- 
 
81 
                  p.category_id,
- 
 
82 
                  b.userid
- 
 
83 
      ) AS x
- 
 
84 
       JOIN brands br
- 
 
85 
         ON ( x.brand = br.NAME
- 
 
86 
              AND x.category_id = br.category_id )
- 
 
87 
GROUP  BY x.brand,
- 
 
88 
          x.userid,
- 
 
89 
          x.pricerange,
- 
 
90 
          x.category_id
- 
 
91 
UNION
- 
 
92 
SELECT y.brand,
- 
 
93 
       y.userid,
- 
 
94 
       y.min_price,
- 
 
95 
       y.max_price,
- 
 
96 
       y.pricerange,
- 
 
97 
       y.category_id
- 
 
98 
FROM   (SELECT b.brand,
- 
 
99 
               b.user_id                             userid,
- 
 
100 
               p.min_price,
- 
 
101 
               p.max_price,
- 
 
102 
               p.category_id,
- 
 
103 
               Concat(p.min_price, '-', p.max_price) pricerange
- 
 
104 
        FROM   brand_preferences b
- 
 
105 
               LEFT OUTER JOIN price_preferences p
- 
 
106 
               ON  (b.user_id = p.user_id AND b.category_id=p.category_id)
 - 
 
107 
        WHERE  b.status = 'show'
- 
 
108 
               AND Trim(COALESCE(b.brand, '')) <> ''
- 
 
109 
               AND p.min_price IS NULL
- 
 
110 
               AND p.max_price IS NULL
- 
 
111 
        GROUP  BY b.brand,
- 
 
112 
                  b.user_id
- 
 
113 
        UNION
- 
 
114 
        SELECT b.brand,
- 
 
115 
               b.user_id                             userid,
- 
 
116 
               p.min_price,
- 
 
117 
               p.max_price,
- 
 
118 
               b.category_id,
- 
 
119 
               Concat(p.min_price, '-', p.max_price) pricerange
- 
 
120 
        FROM   clicks b
- 
 
121 
               LEFT OUTER JOIN price_preferences p
- 
 
122 
               ON (b.user_id = p.user_id and b.category_id=p.category_id)
 - 
 
123 
        WHERE  Trim(COALESCE(b.brand, '')) <> ''
- 
 
124 
               AND p.min_price IS NULL
- 
 
125 
               AND p.max_price IS NULL
- 
 
126 
        GROUP  BY b.brand,
- 
 
127 
                  b.category_id,
 - 
 
128 
                  b.user_id
- 
 
129 
        UNION
- 
 
130 
        SELECT b.brand,
- 
 
131 
               b.userid                              userid,
- 
 
132 
               p.min_price,
- 
 
133 
               p.max_price,
- 
 
134 
               p.category_id,
- 
 
135 
               Concat(p.min_price, '-', p.max_price) pricerange
- 
 
136 
        FROM   merchantsuborders b
- 
 
137 
               LEFT OUTER JOIN price_preferences p
- 
 
138 
               ON (b.userid = p.user_id AND b.categoryId =p.category_id)
 - 
 
139 
        WHERE  Trim(COALESCE(b.brand, '')) <> ''
- 
 
140 
               AND b.brand != 'NA'
- 
 
141 
               AND p.min_price IS NULL
- 
 
142 
               AND p.max_price IS NULL
- 
 
143 
        GROUP  BY b.brand,
- 
 
144 
                  b.userid
- 
 
145 
        ) AS y
- 
 
146 
       JOIN brands br
- 
 
147 
         ON ( y.brand = br.NAME
- 
 
148 
              AND y.category_id = br.category_id )
- 
 
149 
GROUP  BY y.brand,
- 
 
150 
          y.userid,
- 
 
151 
          y.category_id
- 
 
152 
"""
 - 
 
153 
 
 - 
 
154 
def to_py_date(java_timestamp):
 23 
def to_py_date(java_timestamp):
155 
    date = datetime.datetime.fromtimestamp(java_timestamp)
 24 
    date = datetime.datetime.fromtimestamp(java_timestamp)
156 
    return date
 25 
    return date
157 
 
 26 
 
158 
class MerchantSubOrder():
 27 
class MerchantSubOrder():
Line 190... Line 59...
190 
            self.createdTime = None
 59 
            self.createdTime = None
191 
        self.userId = userId
 60 
        self.userId = userId
192
61
193 
 
 62 
 
194 
def getAndStoreMerchantSubOrders():
 63 
def getAndStoreMerchantSubOrders():
195 
    existingMaxOrderId = session.query(func.max(MerchantSubOrders.orderId)).one()
64 
    existingMaxOrderId = session.query(func.max(MerchantSubOrders.orderId)).one()
196 
    db = client.Dtr
 65 
    db = client.Dtr
197 
    collection = db.merchantOrder
 66 
    collection = db.merchantOrder
198 
    orders = collection.find({'orderId':{'$gt':int(existingMaxOrderId[0])}})
 67 
    orders = collection.find({'orderId':{'$gt':int(existingMaxOrderId[0])}})
199 
    catalogdb = client.Catalog
 68 
    catalogdb = client.Catalog
200 
    masterDataCollection = catalogdb.MasterData
 69 
    masterDataCollection = catalogdb.MasterData
Line 251... Line 120...
251 
                    merchantSubOrderDetail.createdTime = merchantSubOrder.createdTime
 120 
                    merchantSubOrderDetail.createdTime = merchantSubOrder.createdTime
252 
                    session.commit()
121 
                    session.commit()
253
122
254 
 
 123 
 
255 
def initiateUserGroupSegmentation():
 124 
def initiateUserGroupSegmentation():
256 
    #userGroupSegmentationQuery ="select x.brand, x.userid, x.min_price, x.max_price, x.pricerange, x.category_id from (select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from brand_preferences b left outer join price_preferences p on b.user_id= p.user_id where b.status='show' and trim(coalesce(b.brand, '')) <>'' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.user_id UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from clicks b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.user_id UNION select b.brand, s.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from search_terms s join (select distinct brand from brand_preferences where trim(coalesce(brand, '')) <>'') b on lower(s.search_term) like concat('%', lower(b.brand), '%') left outer join price_preferences p on s.user_id= p.user_id where p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, s.user_id UNION select b.brand, b.userId userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from merchantsuborders b left outer join price_preferences p on b.userId= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.userId UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from flipkartorders b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.user_id) as x join brands br on (x.brand=br.name and x.category_id=br.category_id) group by x.brand, x.userid, x.pricerange, x.category_id UNION select y.brand, y.userid, y.min_price, y.max_price, y.pricerange, y.category_id from (select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from brand_preferences b left outer join price_preferences p on b.user_id= p.user_id where b.status='show' and trim(coalesce(b.brand, '')) <>'' and p.min_price is null and p.max_price is null group by b.brand, b.user_id UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from clicks b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and p.min_price is null and p.max_price is null group by b.brand, b.user_id UNION select b.brand, s.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from search_terms s join (select distinct brand from brand_preferences where trim(coalesce(brand, '')) <>'') b on lower(s.search_term) like concat('%', lower(b.brand), '%') left outer join price_preferences p on s.user_id= p.user_id where p.min_price is null and p.max_price is null group by b.brand, s.user_id UNION select b.brand, b.userId userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from merchantsuborders b left outer join price_preferences p on b.userId= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price is null and p.max_price is null group by b.brand, b.userId UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from flipkartorders b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price is null and p.max_price is null group by b.brand, b.user_id) as y join brands br on (y.brand=br.name and y.category_id=br.category_id) group by y.brand, y.userid, y.category_id"
 125 
    userGroupSegmentationQuery ="select x.brand, x.userid, x.min_price, x.max_price, x.pricerange, x.category_id from (select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from brand_preferences b left outer join price_preferences p on b.user_id= p.user_id where b.status='show' and trim(coalesce(b.brand, '')) <>'' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.user_id UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from clicks b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.user_id UNION select b.brand, s.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from search_terms s join (select distinct brand from brand_preferences where trim(coalesce(brand, '')) <>'') b on lower(s.search_term) like concat('%', lower(b.brand), '%') left outer join price_preferences p on s.user_id= p.user_id where p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, s.user_id UNION select b.brand, b.userId userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from merchantsuborders b left outer join price_preferences p on b.userId= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.userId UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from flipkartorders b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.user_id) as x join brands br on (x.brand=br.name and x.category_id=br.category_id) group by x.brand, x.userid, x.pricerange, x.category_id UNION select y.brand, y.userid, y.min_price, y.max_price, y.pricerange, y.category_id from (select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from brand_preferences b left outer join price_preferences p on b.user_id= p.user_id where b.status='show' and trim(coalesce(b.brand, '')) <>'' and p.min_price is null and p.max_price is null group by b.brand, b.user_id UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from clicks b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and p.min_price is null and p.max_price is null group by b.brand, b.user_id UNION select b.brand, s.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from search_terms s join (select distinct brand from brand_preferences where trim(coalesce(brand, '')) <>'') b on lower(s.search_term) like concat('%', lower(b.brand), '%') left outer join price_preferences p on s.user_id= p.user_id where p.min_price is null and p.max_price is null group by b.brand, s.user_id UNION select b.brand, b.userId userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from merchantsuborders b left outer join price_preferences p on b.userId= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price is null and p.max_price is null group by b.brand, b.userId UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from flipkartorders b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price is null and p.max_price is null group by b.brand, b.user_id) as y join brands br on (y.brand=br.name and y.category_id=br.category_id) group by y.brand, y.userid, y.category_id"
257 
    userGroupSegmentationQuery =USER_LINKS_SEGMENTATIONQUERY
 - 
 
258 
    cursor = db.cursor()
 126 
    cursor = db.cursor()
259
127
260 
#     deleteAllUserGroups = "delete from pushnotificationusergroups"
 128 
    deleteAllUserGroups = "delete from pushnotificationusergroups"
261 
#     try:
 129 
    try:
262 
#         cursor.execute(deleteAllUserGroups)
 130 
        cursor.execute(deleteAllUserGroups)
263 
#         db.commit()
 131 
        db.commit()
264 
#     except:
 132 
    except:
265 
#         db.rollback()
 133 
        db.rollback()
266
134
267 
    cursor.execute(userGroupSegmentationQuery)
 135 
    cursor.execute(userGroupSegmentationQuery)
268 
    allUserGroups = cursor.fetchall()
 136 
    allUserGroups = cursor.fetchall()
269 
    for userGroup in allUserGroups:
 137 
    for userGroup in allUserGroups:
270 
        min_price = userGroup[2]
 138 
        min_price = userGroup[2]
Line 275... Line 143...
275 
            max_price = 0.0
 143 
            max_price = 0.0
276 
        category_id = userGroup[5]
 144 
        category_id = userGroup[5]
277 
        if category_id is None:
 145 
        if category_id is None:
278 
            category_id= 0
 146 
            category_id= 0
279
147
280 
        sql = "insert into userlinksgroupsegmentation (brand, userids, min_price, max_price, pricerange, category_id) values('%s', %d, %f, %f, '%s', %d)"%(userGroup[0], userGroup[1], min_price, max_price, userGroup[4], category_id)
 148 
        sql = "insert into pushnotificationusergroups (brand, userids, min_price, max_price, pricerange, category_id) values('%s', %d, %f, %f, '%s', %d)"%(userGroup[0], userGroup[1], min_price, max_price, userGroup[4], category_id)
281 
        print sql
 149 
        print sql
282 
        try:
 150 
        try:
283 
            dtrdb = MySQLdb.connect('localhost',"root","shop2020","dtr" )
 151 
            dtrdb = MySQLdb.connect('localhost',"root","shop2020","dtr" )
284 
            cursor = dtrdb.cursor()
 152 
            cursor = dtrdb.cursor()
285 
            cursor.execute(sql)
 153 
            cursor.execute(sql)
Line 308... Line 176...
308 
        print "session is active. closing it."
 176 
        print "session is active. closing it."
309 
        session.close()
 177 
        session.close()
310 
    client.close()
 178 
    client.close()
311
179
312 
if __name__=='__main__':
 180 
if __name__=='__main__':
313 
    main()
 181 
    main()
314 

            182