Subversion Repositories SmartDukaan

Rev

Rev 16303 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
15795 manish.sha 1 
import os
2 
import re
3 
import smtplib
4 
import csv
5 
import MySQLdb
16300 manas 6 
from dtr.storage import DataService, Mongo
15795 manish.sha 7 
from dtr.storage.DataService import MerchantSubOrders
8 
from elixir import *
9 
from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound
10 
from sqlalchemy.sql import func
11 
from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, \
12 
    between
13 
import datetime
14 
from pymongo.mongo_client import MongoClient
16300 manas 15 
 
15804 manish.sha 16 
import optparse
15795 manish.sha 17 
 
18 
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
19 
 
20 
DataService.initialize(db_hostname="localhost")
21 
client = MongoClient('mongodb://localhost:27017/')
22 
 
16312 manas 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 
 
15795 manish.sha 154 
def to_py_date(java_timestamp):
155 
    date = datetime.datetime.fromtimestamp(java_timestamp)
156 
    return date
157 
 
158 
class MerchantSubOrder():
159 
    orderId = None
160 
    merchantOrderId = None
161 
    merchantSubOrderId = None
162 
    storeId = None
163 
    userId = None
164 
    productCode = None
165 
    brand = None
166 
    productName = None
16300 manas 167 
    category_id=None
15795 manish.sha 168 
    amountPaid = None
169 
    quantity = None
170 
    unitPrice = None
171 
    status = None
172 
    createdTime = None
173 
 
16300 manas 174 
    def __init__(self,orderId, merchantOrderId, merchantSubOrderId, storeId, userId, productCode, productName,category_id, amountPaid, quantity,unitPrice, status, createdTime):
15795 manish.sha 175 
        self.orderId = orderId
176 
        self.merchantOrderId = merchantOrderId
177 
        self.merchantSubOrderId = merchantSubOrderId
178 
        self.storeId = storeId
179 
        self.userId = userId
180 
        self.productCode = productCode
181 
        self.productName= productName
16300 manas 182 
        self.category_id= category_id
15795 manish.sha 183 
        self.amountPaid = amountPaid
184 
        self.quantity = quantity
185 
        self.unitPrice = unitPrice
186 
        self.status = status
15798 manish.sha 187 
        if createdTime is not None:
188 
            self.createdTime = to_py_date(createdTime)
189 
        else:
190 
            self.createdTime = None
15795 manish.sha 191 
        self.userId = userId
192 
 
16300 manas 193 
 
15795 manish.sha 194 
def getAndStoreMerchantSubOrders():
16302 manas 195 
    existingMaxOrderId = session.query(func.max(MerchantSubOrders.orderId)).one()
15795 manish.sha 196 
    db = client.Dtr
197 
    collection = db.merchantOrder
16312 manas 198 
    orders = collection.find({'orderId':{'$gt':int(existingMaxOrderId[0])}})
15795 manish.sha 199 
    catalogdb = client.Catalog
200 
    masterDataCollection = catalogdb.MasterData
201 
    for order in orders:
202 
        if order.has_key('subOrders'):
203 
            orderId = order['orderId']
204 
            merchantOrderId = order['merchantOrderId']
205 
            storeId = order['storeId']
206 
            userId = order['userId']
15798 manish.sha 207 
            createdOnInt = None
208 
            if order.has_key('createdOnInt'):
209 
                createdOnInt = order['createdOnInt']
15795 manish.sha 210 
            subOrders = order['subOrders']
211 
            for subOrder in subOrders:
15801 manish.sha 212 
                unitPrice = 0
213 
                if subOrder.has_key('unitPrice'):
214 
                    unitPrice = subOrder['unitPrice']
15802 manish.sha 215 
                productCode = "Undefined"
216 
                if subOrder.has_key('productCode'):
217 
                    productCode = subOrder['productCode']
16312 manas 218 
                    skuData=Mongo.getItemByMerchantIdentifier(productCode,storeId)
219 
                    if(skuData.has_key('category_id')):
220 
                        category_id=skuData['category_id']
221 
                    else:
222 
                        category_id=None
223 
 
16302 manas 224 
                merchantSubOrder = MerchantSubOrder(orderId, merchantOrderId, subOrder['merchantSubOrderId'], storeId, userId, productCode, subOrder['productTitle'], category_id,subOrder['amountPaid'], subOrder['quantity'], unitPrice, subOrder['status'], createdOnInt)
15795 manish.sha 225 
                product = None
226 
                if storeId in (1,2,4,5):
15803 manish.sha 227 
                    product = list(masterDataCollection.find({'identifier':productCode.strip(), 'source_id':storeId}))
15795 manish.sha 228 
                elif storeId == 3:
15803 manish.sha 229 
                    product = list(masterDataCollection.find({'secondaryIdentifier':productCode.strip(), 'source_id':storeId}))
15800 manish.sha 230 
                if product is not None and len(product)>0:
15795 manish.sha 231 
                    merchantSubOrder.brand = product[0]["brand"]
232 
                else:
233 
                    merchantSubOrder.brand = 'NA'
16302 manas 234 
 
15795 manish.sha 235 
                existingMerchantSubOrder = MerchantSubOrders.query.filter(MerchantSubOrders.orderId == orderId).filter(MerchantSubOrders.merchantOrderId == merchantOrderId).filter(MerchantSubOrders.merchantSubOrderId == merchantSubOrder.merchantSubOrderId).first()
236 
                if existingMerchantSubOrder is None:
237 
                    merchantSubOrderDetail = MerchantSubOrders()
238 
                    merchantSubOrderDetail.orderId = merchantSubOrder.orderId
239 
                    merchantSubOrderDetail.merchantOrderId = merchantSubOrder.merchantOrderId
240 
                    merchantSubOrderDetail.merchantSubOrderId = merchantSubOrder.merchantSubOrderId
241 
                    merchantSubOrderDetail.storeId = merchantSubOrder.storeId
242 
                    merchantSubOrderDetail.userId = merchantSubOrder.userId
243 
                    merchantSubOrderDetail.productCode = merchantSubOrder.productCode
244 
                    merchantSubOrderDetail.brand = merchantSubOrder.brand
245 
                    merchantSubOrderDetail.productName = merchantSubOrder.productName
16300 manas 246 
                    merchantSubOrderDetail.categoryId = merchantSubOrder.category_id
15795 manish.sha 247 
                    merchantSubOrderDetail.amountPaid = merchantSubOrder.amountPaid
248 
                    merchantSubOrderDetail.quantity = merchantSubOrder.quantity
249 
                    merchantSubOrderDetail.unitPrice = merchantSubOrder.unitPrice
250 
                    merchantSubOrderDetail.status = merchantSubOrder.status
251 
                    merchantSubOrderDetail.createdTime = merchantSubOrder.createdTime
252 
                    session.commit()
253 
 
254 
 
15804 manish.sha 255 
def initiateUserGroupSegmentation():
16312 manas 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
15804 manish.sha 258 
    cursor = db.cursor()
259 
 
16312 manas 260 
#     deleteAllUserGroups = "delete from pushnotificationusergroups"
261 
#     try:
262 
#         cursor.execute(deleteAllUserGroups)
263 
#         db.commit()
264 
#     except:
265 
#         db.rollback()
15804 manish.sha 266 
 
267 
    cursor.execute(userGroupSegmentationQuery)
268 
    allUserGroups = cursor.fetchall()
269 
    for userGroup in allUserGroups:
15810 manish.sha 270 
        min_price = userGroup[2]
271 
        if min_price is None:
272 
            min_price = 0.0
273 
        max_price = userGroup[3]
274 
        if max_price is None:
275 
            max_price = 0.0
276 
        category_id = userGroup[5]
277 
        if category_id is None:
278 
            category_id= 0
279 
 
16312 manas 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)
15805 manish.sha 281 
        print sql
15804 manish.sha 282 
        try:
283 
            dtrdb = MySQLdb.connect('localhost',"root","shop2020","dtr" )
284 
            cursor = dtrdb.cursor()
285 
            cursor.execute(sql)
286 
            dtrdb.commit()
287 
            dtrdb.close()
288 
        except:
289 
            dtrdb.rollback()
290 
            dtrdb.close()
291 
 
15795 manish.sha 292 
def main():
15804 manish.sha 293 
    parser = optparse.OptionParser()
294 
    parser.add_option("-T", "--segmenttype", dest="segmenttype",
295 
                      default="group",
296 
                      type="str", help="To avoid not needed order backups",
297 
                      metavar="SEGMENTTYPE")
298 
    (options, args) = parser.parse_args()
299 
    if options.segmenttype == 'full':
300 
        getAndStoreMerchantSubOrders()
301 
        initiateUserGroupSegmentation()
302 
    if options.segmenttype == 'group':
303 
        initiateUserGroupSegmentation()
15807 manish.sha 304 
    if options.segmenttype == 'order':
305 
        getAndStoreMerchantSubOrders()
15795 manish.sha 306 
    db.close()
307 
    if session.is_active:
308 
        print "session is active. closing it."
309 
        session.close()
310 
    client.close()
311 
 
312 
if __name__=='__main__':
16302 manas 313 
    main()