def addItemToInventory(item_id):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"""INSERT INTO inventory (item_id, fetched) VALUES (%s,1)""",
(item_id, ))
conn.commit()
inventory_id = cursor.lastrowid
item = Item(item_id).getObj()
Indexer().indexItems(query_condition=' AND i.item_id=' + str(item_id))
return {'item': item, 'inventory_id': inventory_id}
def updateLendStatus(lender_id, status_id):
if not Lend.getLendStatusDetails(status_id):
return False
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("""UPDATE lenders SET status_id = %d WHERE lender_id = %d"""
%(status_id, lender_id))
conn.commit()
if status_id in [1,2,3]:
cursor.execute("""SELECT l.inventory_id, l.user_id, iv.item_id
FROM lenders l
INNER JOIN inventory iv ON iv.inventory_id = l.inventory_id
WHERE l.lender_id = %s""",(lender_id,))
data = cursor.fetchone()
user = User(data[1])
Lend.sendLendNotification(lender_id, status_id, user)
if status_id == 3:
cursor.execute("""UPDATE inventory SET in_stock = 1, fetched = 1 WHERE
inventory_id = %s""",(data[0],))
conn.commit()
cursor.execute("""UPDATE lenders SET pickup_date = CURRENT_TIMESTAMP WHERE
lender_id = %s""",(lender_id,))
conn.commit()
Indexer().indexItems(query_condition=' AND i.item_id='+str(data[2]))
Mailer.thankyou(user)
'''
NOTE Later
if status_id == 5:
cursor.execute("""UPDATE inventory SET in_stock = 0 WHERE
inventory_id = (SELECT inventory_id FROM lenders WHERE lender_id = %d)"""
%(lender_id))
conn.commit()
'''
return True
def updateInventoryPostOrder(self, item_ids, user_id):
inventory_ids = self.getInventoryIds(item_ids)
#update order_history and clear stock in inventory
connect = mysql.connect()
for inventory_item in inventory_ids:
order_history_cursor = connect.cursor()
order_history_cursor.execute("INSERT INTO order_history (inventory_id, \
item_id, order_id) VALUES (%d, %d, %d)" %(inventory_item['inventory_id'], \
inventory_item['item_id'], self.order_id))
connect.commit()
order_history_cursor.close()
# NOTE preventing inventory count messup from admins
if user_id not in Utils.getAdmins():
update_stock_cursor = connect.cursor()
update_stock_cursor.execute(
"UPDATE inventory SET in_stock = 0 WHERE \
inventory_id = %d" % (inventory_item['inventory_id']))
connect.commit()
Indexer().indexItems(query_condition=' AND i.item_id=' +
str(inventory_item['item_id']))
update_stock_cursor.close()
def saveCollectionData(data, collection_item_ids=''):
conn = mysql.connect()
cursor = conn.cursor()
slug_url = slugify(data['name'])[:100]
if not int(data['collection_id']):
cursor.execute(
"""INSERT INTO collections (name, description, price,
return_days, partial_order, category_id, slug_url) VALUES (%s, %s, %s, %s, %s, %s, %s)""",
(data['name'], data['description'], data['price'],
data['return_days'], data['partial_order'],
data['category_id'], slug_url))
conn.commit()
collection_id = cursor.lastrowid
else:
collection_id = data['collection_id']
cursor.execute(
"""UPDATE collections SET name = %s, description = %s,
price = %s, return_days = %s, category_id = %s, date_edited = CURRENT_TIMESTAMP,
partial_order = %s, slug_url = %s WHERE collection_id = %s""",
(data['name'], data['description'], data['price'],
data['return_days'], data['category_id'], data['partial_order'],
slug_url, collection_id))
conn.commit()
cursor.execute(
"""DELETE FROM collections_metadata WHERE collection_id = %s""",
(collection_id, ))
conn.commit()
if data['metadata']:
metadata_pairs = []
for meta in data['metadata'].split(";"):
key, value = meta.split(":")
metadata_pairs.append(tuple([collection_id, key, value]))
cursor.executemany(
"""INSERT INTO collections_metadata (collection_id, meta_key, meta_value)
VALUES (%s, %s, %s)""", metadata_pairs)
conn.commit()
update_item_order = []
insert_item_order = []
item_ids = []
original_items = collection_item_ids
for item in data['items'].split(";"):
key, value = item.split(":")
key = int(key)
item_ids.append(key)
if key in original_items:
update_item_order.append(tuple([value, collection_id, key]))
else:
insert_item_order.append(tuple([value, collection_id, key]))
cursor.executemany(
"""UPDATE collections_items SET sort_order = %s,
date_edited = CURRENT_TIMESTAMP WHERE collection_id = %s AND item_id = %s""",
update_item_order)
conn.commit()
cursor.executemany(
"""INSERT INTO collections_items (sort_order, collection_id, item_id)
VALUES (%s, %s, %s)""", insert_item_order)
conn.commit()
format_chars = ",".join(["%s"] * len(item_ids))
cursor.execute(
"""DELETE FROM collections_items
WHERE collection_id = %s AND item_id NOT IN (""" + format_chars +
""")""", (tuple([collection_id]) + tuple(item_ids)))
conn.commit()
Indexer().indexCollections(query_condition='c.collection_id=' +
str(collection_id))
#NOTE for start session cals
if collection_id in [4, 5]:
Notifications().startDataUpdate()
from app import cache
cache_key = 'collection_' + str(collection_id)
cache.set(cache_key, None)
return True
def updateOrderStatus(self, status_id):
conn = mysql.connect()
update_cursor = conn.cursor()
all_orders = self.getOrderInfo(fetch_all=True)
if 'order' in all_orders:
all_order_ids = Order.fetchAllOrderIds(all_orders)
order_info = all_orders['order']
else:
all_order_ids = str(all_orders['order_id'])
order_info = all_orders
update_cursor.execute(
"UPDATE orders SET order_status = %s WHERE order_id IN (" +
all_order_ids + ")", (status_id, ))
conn.commit()
# Update inventory and respective dates
current_ts = datetime.datetime.now(pytz.timezone('Asia/Calcutta'))
if status_id == 7:
update_cursor.execute(
"""UPDATE inventory SET in_stock = 1 WHERE
inventory_id IN (SELECT inventory_id FROM order_history WHERE
order_id = %s)""", (self.order_id, ))
conn.commit()
update_cursor.execute(
"UPDATE orders SET order_return = %s WHERE order_id = %s", (
current_ts,
self.order_id,
))
conn.commit()
self.logEditOrderDetails(
{'order_return': current_ts},
{'order_return': order_info['order_return']}, ['order_return'])
elif status_id == 2:
update_cursor.execute(
"""UPDATE inventory SET fetched = 1 WHERE
inventory_id IN (SELECT inventory_id FROM order_history WHERE
order_id = %s)""", (self.order_id, ))
conn.commit()
elif status_id == 4:
item_return_days = Item.getCustomProperties(
order_info['items'], order_info['collection'] if
order_info['from_collection'] else None)['custom_return_days']
new_order_return = Utils.getDefaultReturnTimestamp(
current_ts, item_return_days)
update_cursor.execute(
"""UPDATE orders SET delivery_date = %s,
order_return = %s WHERE order_id = %s""",
(current_ts, new_order_return, self.order_id))
conn.commit()
self.logEditOrderDetails(
{
'delivery_date': current_ts,
'order_return': new_order_return
}, {
'delivery_date': order_info['delivery_date'],
'order_return': order_info['order_return']
}, ['delivery_date', 'order_return'])
update_cursor.close()
if status_id in [3, 4, 5, 6]:
self.sendOrderNotification(status_id)
elif status_id == 7:
Indexer().indexItems(
query_condition=' AND i.item_id IN (' + ",".join(
[str(_['item_id'])
for _ in self.getOrderInfo()['items']]) + ")")
return self.getOrderInfo()
def indexer():
from app.scripts import Indexer
Indexer().indexItems(query_condition=' AND (i.item_id = 410)', limit='10')
def insertItem(data):
conn = mysql.connect()
cursor = conn.cursor()
if 'author' in data['goodreads'] and data['goodreads']['author']:
author = data['goodreads']['author']
elif 'author' in data['amazon'] and data['amazon']['author']:
author = data['amazon']['author']
else:
author = ''
cursor.execute(
"""SELECT item_id FROM items WHERE item_name LIKE %s AND author LIKE %s""",
(data['amazon']['title'], author))
match = cursor.fetchone()
if match:
return {'_id': int(match[0])}
price = data['amazon']['list_price'] if data['amazon'][
'list_price'] else data['amazon']['offer_price']
price = re.sub('\..*$', '', price)
price = price.replace(',', '')
summary = ''
if 'gr_summary' in data['goodreads'] and data['goodreads'][
'gr_summary']:
summary = data['goodreads']['gr_summary']
elif 'amzn_summary' in data['amazon']:
for key in data['amazon']['amzn_summary']:
if data['amazon']['amzn_summary'][key]:
summary = data['amazon']['amzn_summary'][key]
break
slug_url = slugify(data['amazon']['title'])[:100]
cursor.execute(
"""INSERT INTO items (item_name, price, author, ratings,
num_ratings, num_reviews, language, asin, goodreads_id, summary, slug_url) VALUES
(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
(data['amazon']['title'],
price, author, data['goodreads']['avg_rating'].replace(
' rating', ''), data['goodreads']['num_ratings'].replace(
' rating', ''), data['goodreads']['num_review'],
data['goodreads']['language'], data['amazon']['amazon_id'],
data['goodreads']['gr_id'], summary, slug_url))
conn.commit()
item_id = cursor.lastrowid
cursor.execute(
"""INSERT INTO item_isbn (item_id, isbn_10,isbn_13,
num_pages, binding_type) VALUES
(%s,%s,%s,%s,%s)""",
(item_id, data['amazon']['isbn_10'], data['amazon']['isbn_13'],
data['goodreads']['num_page'], data['goodreads']['bind_type']))
conn.commit()
for isbn in data['goodreads']['isbns']:
cursor.execute(
"""INSERT INTO item_isbn (item_id, isbn_13) VALUES (%s, %s)""",
(item_id, isbn))
conn.commit()
global_categories = {}
for genres in data['goodreads']['genres']:
for genre in genres[0].split(","):
if genre not in global_categories:
cursor.execute(
"""SELECT category_id FROM categories WHERE category_name = %s""",
(genre, ))
cat_id = cursor.fetchone()
if cat_id:
global_categories[genre] = cat_id[0]
cursor.execute(
"""INSERT INTO items_categories (item_id, category_id)
VALUES (%s, %s)""",
(item_id, global_categories[genre]))
else:
genre_slug = slugify(genre)
cursor.execute(
"""INSERT INTO categories (category_name, slug_url) VALUES (%s, %s)""",
(genre, genre_slug))
conn.commit()
global_categories[genre] = cursor.lastrowid
cursor.execute(
"""INSERT INTO items_categories (item_id, category_id)
VALUES (%s, %s)""",
(item_id, global_categories[genre]))
conn.commit()
s3conn = S3Connection('AKIAIN4EU63OJMW63H6A',
'k97pZ8rmwkqLdeW+L4QOIKrCDIg3YR/uY/BifLU3')
bucket = s3conn.get_bucket('ostrich-catalog')
basepath = webapp.config['S3_IMAGE_BUCKET']
if 'img_small' in data['amazon'] and data['amazon']['img_small']:
url = data['amazon']['img_small']
elif 'img_large' in data['amazon'] and data['amazon']['img_large']:
url = data['amazon']['img_large']
if url:
parsed = urlparse.urlparse(url)
ext = os.path.splitext(parsed.path)[1]
path = basepath + str(item_id) + ext
r = requests.get(url)
if r.status_code >= 200 and r.status_code < 300:
content = r.content
key = Key(bucket)
key.key = path
key.set_contents_from_string(content)
cursor.execute(
"""UPDATE items SET img_small = %s WHERE item_id = %s""",
(path, item_id))
conn.commit()
Indexer().indexItems(query_condition=' AND i.item_id=' + str(item_id))
client = MongoClient(webapp.config['MONGO_DB'])
db = client.ostrich
final_data = data['goodreads']
final_data.update(data['amazon'])
#if not db.items.find({'_id': final_data['_id']}).count():
# db.items.insert_one(final_data)
db.items.update_one({'_id': int(item_id)}, {'$set': final_data},
upsert=True)
final_data['item id'] = item_id
return final_data