def indexItems(self, query_condition='', limit='', custom_keys={}):
search_query = """SELECT i.item_id, i.item_name, i.author, i.price,i.ratings,
i.num_ratings, i.num_reviews, i.img_small, i.asin, i.goodreads_id, i.summary, i.slug_url,
(select group_concat(c.category_name SEPARATOR '|') FROM categories c
INNER JOIN items_categories ic ON ic.category_id = c.category_id WHERE
ic.item_id = i.item_id) AS categories
FROM items i WHERE i.active=1"""
if query_condition:
search_query += query_condition
if limit:
search_query += ' LIMIT ' + limit
cursor = mysql.connect().cursor()
cursor.execute(search_query)
num_items = cursor.rowcount
for num in range(num_items):
record = Utils.fetchOneAssoc(cursor)
print record['item_id']
if record['categories'] is not None:
record['categories'] = record['categories'].split("|")
else:
record['categories'] = []
record = self.fetchItemProperties(record, custom_keys)
record = self.extendItemProperties(record)
record = self.handleUnicode(record)
self.indexItemObject(record)
def indexCollections(self, query_condition):
#TODO fetch collection object from Collection class
cursor = mysql.connect().cursor()
condition = 'WHERE c.partial_order = 0'
if query_condition:
condition += ' AND ' + query_condition
cursor.execute("""SELECT c.*,
(select group_concat(ci.item_id SEPARATOR ',') FROM collections_items ci
WHERE ci.collection_id = c.collection_id) AS item_ids
FROM collections c """ + condition)
num_items = cursor.rowcount
for i in range(num_items):
collection = Utils.fetchOneAssoc(cursor)
collection['item_ids'] = [
int(_) for _ in collection['item_ids'].split(',')
]
collection['metadata'] = self.fetchCollectionsMetadata(
collection['collection_id'])
try:
self.es.index(index=self.es_index,
doc_type='collections',
id=collection['collection_id'],
body=collection,
refresh=True)
self.updateItems(collection['item_ids'], collection['name'])
except Exception, e:
print str(e), collection['collection_id']
def returnDateExtensionReminder():
current_timestamp = Utils.getCurrentTimestamp()
return_date = Utils.getDefaultReturnTimestamp(current_timestamp, 3)
cursor = mysql.connect().cursor()
cursor.execute("""SELECT order_id, user_id
FROM orders
WHERE DATE(order_return) = DATE('%s') AND order_status >=4 AND
order_id NOT IN (SELECT DISTINCT parent_id FROM orders) AND bought = 0
""" % (return_date))
num_items = cursor.rowcount
for num in range(num_items):
query_data = Utils.fetchOneAssoc(cursor)
user = User(query_data['user_id'])
print 'order_id:', query_data['order_id']
notification_data = {
"notification_id": 3,
"entity_id": query_data['order_id'],
"order_type": "borrow",
"title": "3 Days Left to Return the Book",
"message": "Not finised yet? Extend the reading period.",
"expanded_text": "You can extend the reading period by going to the order's page in \"My Orders\".",
"order_type": "borrow"
}
Notifications(user.gcm_id).sendNotification(notification_data)
#if int(query_data['user_id']) in Utils.getAdmins():
upsellEmail(query_data['order_id'])
def user_followup():
client = MongoClient(webapp.config['MONGO_DB'])
db = client.ostrich
cursor = mysql.connect().cursor()
ts = str(date.today() - timedelta(1))
cursor.execute(
""" select user_id, name, phone, date_created from users
where DATE(date_created) = %s and
user_id not in (select distinct user_id from orders) """,
(ts, ))
users = []
for i in range(cursor.rowcount):
users.append(OrderedDict(Utils.fetchOneAssoc(cursor)))
for user in users:
distinct_q = []
cursor.execute(
"""select query, timestamp from search_fails where user_id = %s""",
(user['user_id'], ))
user['fails'] = []
for i in range(cursor.rowcount):
q = Utils.fetchOneAssoc(cursor)
if q['query'] not in distinct_q:
user['fails'].append(q)
distinct_q.append(q['query'])
user['searches'] = []
for query in db.search_log.find({"user_id": str(user['user_id'])}):
if query['q'] not in distinct_q:
user['searches'].append({
"query": query['q'],
"timestamp": str(query['timestamp'])
})
distinct_q.append(query['q'])
if users:
users = json.dumps({'users': users}, indent=4)
Mailer.genericMailer(
{
'subject': 'Dropped Users for ' + str(ts),
'body': users
},
recipients=['*****@*****.**', '*****@*****.**'])
def getData(self, review_id, user_id, item_id):
cursor = mysql.connect().cursor()
if review_id:
cursor.execute("""SELECT * FROM reviews WHERE review_id = %s""",
(review_id, ))
else:
cursor.execute(
"""SELECT * FROM reviews WHERE item_id = %s AND user_id = %s""",
(item_id, user_id))
self.data = Utils.fetchOneAssoc(cursor)
cursor.close()
def pickupTimeslot():
current_timestamp = Utils.getCurrentTimestamp()
return_date = Utils.getDefaultReturnTimestamp(current_timestamp, 1)
cursor = mysql.connect().cursor()
cursor.execute("""SELECT order_id, user_id, pickup_slot
FROM orders
WHERE DATE(order_return) = DATE('%s') AND order_status >=4 AND
order_id NOT IN (SELECT DISTINCT parent_id FROM orders)
""" % (return_date))
num_items = cursor.rowcount
for num in range(num_items):
query_data = Utils.fetchOneAssoc(cursor)
user = User(query_data['user_id'])
ts = Order.getTimeSlot(query_data['pickup_slot'])
ts = Utils.cleanTimeSlot(ts)
notification_data = {
"notification_id":
3,
"entity_id":
query_data['order_id'],
"order_type":
"borrow",
"title":
"Return Date Tomorrow",
"message":
"Would you like to extend your reading period?",
"expanded_text":
"You can extend the reading period by going to the order's page in \"My Orders\". We'd contact you shortly to confirm your current pickup time: %s"
% ts,
"order_type":
"borrow"
}
Notifications(user.gcm_id).sendNotification(notification_data)