Subversion Repositories SmartDukaan

Rev

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

Rev 16303 Rev 16312
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 
 
23 
def to_py_date(java_timestamp):
 154 
def to_py_date(java_timestamp):
24 
    date = datetime.datetime.fromtimestamp(java_timestamp)
 155 
    date = datetime.datetime.fromtimestamp(java_timestamp)
25 
    return date
 156 
    return date
26 
 
 157 
 
27 
class MerchantSubOrder():
 158 
class MerchantSubOrder():
Line 62... Line 193...
62 
 
 193 
 
63 
def getAndStoreMerchantSubOrders():
 194 
def getAndStoreMerchantSubOrders():
64 
    existingMaxOrderId = session.query(func.max(MerchantSubOrders.orderId)).one()
195 
    existingMaxOrderId = session.query(func.max(MerchantSubOrders.orderId)).one()
65 
    db = client.Dtr
 196 
    db = client.Dtr
66 
    collection = db.merchantOrder
 197 
    collection = db.merchantOrder
67 
    orders = collection.find({'orderId':{'$gt':int(0)}})
 198 
    orders = collection.find({'orderId':{'$gt':int(existingMaxOrderId[0])}})
68 
    catalogdb = client.Catalog
 199 
    catalogdb = client.Catalog
69 
    masterDataCollection = catalogdb.MasterData
 200 
    masterDataCollection = catalogdb.MasterData
70 
    for order in orders:
 201 
    for order in orders:
71 
        if order.has_key('subOrders'):
 202 
        if order.has_key('subOrders'):
72 
            orderId = order['orderId']
 203 
            orderId = order['orderId']
Line 82... Line 213...
82 
                if subOrder.has_key('unitPrice'):
 213 
                if subOrder.has_key('unitPrice'):
83 
                    unitPrice = subOrder['unitPrice']
 214 
                    unitPrice = subOrder['unitPrice']
84 
                productCode = "Undefined"
 215 
                productCode = "Undefined"
85 
                if subOrder.has_key('productCode'):
 216 
                if subOrder.has_key('productCode'):
86 
                    productCode = subOrder['productCode']
 217 
                    productCode = subOrder['productCode']
87 
		skuData=Mongo.getItemByMerchantIdentifier(productCode,storeId)
 218 
                    skuData=Mongo.getItemByMerchantIdentifier(productCode,storeId)
88 
                if(skuData.has_key('category_id')):
 219 
                    if(skuData.has_key('category_id')):
89 
                    category_id=skuData['category_id']
 220 
                        category_id=skuData['category_id']
90 
                else:
 221 
                    else:
91 
                    category_id=None
 222 
                        category_id=None
- 
 
 223
92 
                merchantSubOrder = MerchantSubOrder(orderId, merchantOrderId, subOrder['merchantSubOrderId'], storeId, userId, productCode, subOrder['productTitle'], category_id,subOrder['amountPaid'], subOrder['quantity'], unitPrice, subOrder['status'], createdOnInt)
 224 
                merchantSubOrder = MerchantSubOrder(orderId, merchantOrderId, subOrder['merchantSubOrderId'], storeId, userId, productCode, subOrder['productTitle'], category_id,subOrder['amountPaid'], subOrder['quantity'], unitPrice, subOrder['status'], createdOnInt)
93 
                product = None
 225 
                product = None
94 
                if storeId in (1,2,4,5):
 226 
                if storeId in (1,2,4,5):
95 
                    product = list(masterDataCollection.find({'identifier':productCode.strip(), 'source_id':storeId}))
 227 
                    product = list(masterDataCollection.find({'identifier':productCode.strip(), 'source_id':storeId}))
96 
                elif storeId == 3:
 228 
                elif storeId == 3:
Line 119... Line 251...
119 
                    merchantSubOrderDetail.createdTime = merchantSubOrder.createdTime
 251 
                    merchantSubOrderDetail.createdTime = merchantSubOrder.createdTime
120 
                    session.commit()
252 
                    session.commit()
121
253
122 
 
 254 
 
123 
def initiateUserGroupSegmentation():
 255 
def initiateUserGroupSegmentation():
124 
    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"
 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"
- 
 
 257 
    userGroupSegmentationQuery =USER_LINKS_SEGMENTATIONQUERY
125 
    cursor = db.cursor()
 258 
    cursor = db.cursor()
126
259
127 
    deleteAllUserGroups = "delete from pushnotificationusergroups"
 260 
#     deleteAllUserGroups = "delete from pushnotificationusergroups"
128 
    try:
 261 
#     try:
129 
        cursor.execute(deleteAllUserGroups)
 262 
#         cursor.execute(deleteAllUserGroups)
130 
        db.commit()
 263 
#         db.commit()
131 
    except:
 264 
#     except:
132 
        db.rollback()
 265 
#         db.rollback()
133
266
134 
    cursor.execute(userGroupSegmentationQuery)
 267 
    cursor.execute(userGroupSegmentationQuery)
135 
    allUserGroups = cursor.fetchall()
 268 
    allUserGroups = cursor.fetchall()
136 
    for userGroup in allUserGroups:
 269 
    for userGroup in allUserGroups:
137 
        min_price = userGroup[2]
 270 
        min_price = userGroup[2]
Line 142... Line 275...
142 
            max_price = 0.0
 275 
            max_price = 0.0
143 
        category_id = userGroup[5]
 276 
        category_id = userGroup[5]
144 
        if category_id is None:
 277 
        if category_id is None:
145 
            category_id= 0
 278 
            category_id= 0
146
279
147 
        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)
 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 
        print sql
 281 
        print sql
149 
        try:
 282 
        try:
150 
            dtrdb = MySQLdb.connect('localhost',"root","shop2020","dtr" )
 283 
            dtrdb = MySQLdb.connect('localhost',"root","shop2020","dtr" )
151 
            cursor = dtrdb.cursor()
 284 
            cursor = dtrdb.cursor()
152 
            cursor.execute(sql)
 285 
            cursor.execute(sql)