| Line 12... |
Line 12... |
| 12 |
from sqlalchemy.sql import func
|
12 |
from sqlalchemy.sql import func
|
| 13 |
from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, \
|
13 |
from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, \
|
| 14 |
between
|
14 |
between
|
| 15 |
import datetime
|
15 |
import datetime
|
| 16 |
from pymongo.mongo_client import MongoClient
|
16 |
from pymongo.mongo_client import MongoClient
|
| - |
|
17 |
import optparse
|
| 17 |
|
18 |
|
| 18 |
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
|
19 |
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
|
| 19 |
cursor = db.cursor()
|
- |
|
| 20 |
|
20 |
|
| 21 |
DataService.initialize(db_hostname="localhost")
|
21 |
DataService.initialize(db_hostname="localhost")
|
| 22 |
client = MongoClient('mongodb://localhost:27017/')
|
22 |
client = MongoClient('mongodb://localhost:27017/')
|
| 23 |
|
23 |
|
| 24 |
def to_py_date(java_timestamp):
|
24 |
def to_py_date(java_timestamp):
|
| Line 109... |
Line 109... |
| 109 |
merchantSubOrderDetail.status = merchantSubOrder.status
|
109 |
merchantSubOrderDetail.status = merchantSubOrder.status
|
| 110 |
merchantSubOrderDetail.createdTime = merchantSubOrder.createdTime
|
110 |
merchantSubOrderDetail.createdTime = merchantSubOrder.createdTime
|
| 111 |
session.commit()
|
111 |
session.commit()
|
| 112 |
|
112 |
|
| 113 |
|
113 |
|
| 114 |
#userGroupSegmentationQuery ="select x.brand, GROUP_CONCAT(distinct x.userids) userids, x.min_price, x.max_price, x.pricerange, x.category_id from (select b.brand, GROUP_CONCAT(distinct b.user_id) userids, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from brand_preferences b join price_preferences p on b.user_id= p.user_id where b.status='show' and trim(coalesce(b.brand, '')) <>'' group by b.brand, pricerange, p.category_id UNION select b.brand, GROUP_CONCAT(distinct b.user_id) userids, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from clicks b join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' group by b.brand, pricerange, p.category_id UNION select b.brand, GROUP_CONCAT(distinct s.user_id) userids, 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), '%') join price_preferences p on s.user_id= p.user_id group by b.brand, pricerange, p.category_id) as x group by x.brand, x.pricerange, x.category_id"
|
114 |
def initiateUserGroupSegmentation():
|
| 115 |
|
115 |
userGroupSegmentationQuery ="select x.brand, GROUP_CONCAT(distinct x.userid) userids, 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 join price_preferences p on b.user_id= p.user_id where b.status='show' and trim(coalesce(b.brand, '')) <>'' 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 join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' 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), '%') join price_preferences p on s.user_id= p.user_id 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 join price_preferences p on b.userId= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' group by b.brand, pricerange, p.category_id, b.userId) as x group by x.brand, x.pricerange, x.category_id"
|
| - |
|
116 |
cursor = db.cursor()
|
| - |
|
117 |
|
| - |
|
118 |
deleteAllUserGroups = "delete from pushnotificationusergroups"
|
| - |
|
119 |
try:
|
| - |
|
120 |
cursor.execute(deleteAllUserGroups)
|
| - |
|
121 |
db.commit()
|
| - |
|
122 |
except:
|
| - |
|
123 |
db.rollback()
|
| - |
|
124 |
|
| - |
|
125 |
cursor.execute(userGroupSegmentationQuery)
|
| - |
|
126 |
allUserGroups = cursor.fetchall()
|
| - |
|
127 |
for userGroup in allUserGroups:
|
| - |
|
128 |
sql = "insert into pushnotificationusergroups (brand, userids, min_price, max_price, pricerange, category_id) values('%s', '%s', %f, %f, '%s', %d)"%(userGroup[0], userGroup[1], userGroup[2], userGroup[3], userGroup[4], userGroup[5])
|
| - |
|
129 |
try:
|
| - |
|
130 |
dtrdb = MySQLdb.connect('localhost',"root","shop2020","dtr" )
|
| - |
|
131 |
cursor = dtrdb.cursor()
|
| - |
|
132 |
cursor.execute(sql)
|
| - |
|
133 |
dtrdb.commit()
|
| - |
|
134 |
dtrdb.close()
|
| - |
|
135 |
except:
|
| - |
|
136 |
dtrdb.rollback()
|
| - |
|
137 |
dtrdb.close()
|
| - |
|
138 |
|
| 116 |
def main():
|
139 |
def main():
|
| - |
|
140 |
parser = optparse.OptionParser()
|
| - |
|
141 |
parser.add_option("-T", "--segmenttype", dest="segmenttype",
|
| - |
|
142 |
default="group",
|
| - |
|
143 |
type="str", help="To avoid not needed order backups",
|
| - |
|
144 |
metavar="SEGMENTTYPE")
|
| - |
|
145 |
(options, args) = parser.parse_args()
|
| - |
|
146 |
if options.segmenttype == 'full':
|
| 117 |
getAndStoreMerchantSubOrders()
|
147 |
getAndStoreMerchantSubOrders()
|
| - |
|
148 |
initiateUserGroupSegmentation()
|
| - |
|
149 |
if options.segmenttype == 'group':
|
| - |
|
150 |
initiateUserGroupSegmentation()
|
| 118 |
db.close()
|
151 |
db.close()
|
| 119 |
if session.is_active:
|
152 |
if session.is_active:
|
| 120 |
print "session is active. closing it."
|
153 |
print "session is active. closing it."
|
| 121 |
session.close()
|
154 |
session.close()
|
| 122 |
client.close()
|
155 |
client.close()
|