def _order_iv_info(conn, order_id, id_brand, id_shops):
sp_query = (
"SELECT id "
"FROM shipments as spm "
"WHERE status <> %s "
"AND id_order = %s "
"AND id_brand = %s "
)
params = [SHIPMENT_STATUS.DELETED, order_id, id_brand]
if id_shops:
sp_query += "AND spm.id_shop in %s "
params.append(tuple(id_shops))
sp_r = query(conn, sp_query, params)
sp_r = [item[0] for item in sp_r]
# generated invoices for order.
iv_query = (
"SELECT id_shipment "
"FROM invoices as iv "
"LEFT JOIN shipments as spm "
"ON iv.id_shipment = spm.id "
"WHERE iv.id_order = %s "
"AND spm.id_brand = %s ")
params = [order_id, id_brand]
if id_shops:
iv_query += "AND spm.id_shop in %s "
params.append(tuple(id_shops))
iv_r = query(conn, iv_query, params)
iv_r = [item[0] for item in iv_r]
return sp_r, iv_r
def _all_order_items_packed(conn, id_order, id_brand=None, id_shops=None):
item_qtt_sql = ("SELECT sum(quantity) " "FROM order_details as od ")
where = 'WHERE od.id_order=%s '
where_v = [
id_order,
]
if id_brand is not None or id_shops is not None:
item_qtt_sql += 'JOIN order_items as oi on od.id_item = oi.id '
if id_brand is not None:
where += 'and oi.id_brand = %s '
where_v.append(id_brand)
if id_shops:
where += 'and oi.id_shop in %s '
where_v.append(tuple(id_shops))
item_qtt_sql += where
item_qtt = query(conn, item_qtt_sql, where_v)[0][0]
grouped_qtt_sql = ("SELECT sum(spl.packing_quantity) "
"FROM shipping_list as spl "
"JOIN shipments as sp "
"ON spl.id_shipment = sp.id ")
where = "WHERE sp.id_order = %s AND sp.status <> %s "
where_v = [id_order, SHIPMENT_STATUS.DELETED]
if id_brand is not None:
where += "and sp.id_brand = %s "
where_v.append(id_brand)
if id_shops:
where += 'and sp.id_shop in %s '
where_v.append(tuple(id_shops))
grouped_qtt_sql += where
packed_qtt = query(conn, grouped_qtt_sql, where_v)[0][0]
return item_qtt == packed_qtt
def _on_get(self, req, resp, conn, **kwargs):
imo = req.get_param('imo')
mmsi = req.get_param('mmsi')
if not imo and not mmsi:
raise ValidationError('INVALID_REQUEST')
if imo:
search_by = 'imo'
q = imo
else:
search_by = 'mmsi'
q = mmsi
fields = ['vessel.id'] + VESSEL_FIELDS + VESSEL_NAV_FIELDS
sql = """
select %s
from vessel
join vessel_navigation
on (vessel.id=vessel_navigation.id_vessel)
left join country
on (vessel.country_isocode=country.iso)
where vessel.%s=%%s
order by created desc limit 1
""" % (','.join(fields), search_by)
vessel_nav = db_utils.query(conn, sql, (q, ))
return_obj = {}
if len(vessel_nav) > 0:
vessel_nav = vessel_nav[0]
vessel_nav_dict = dict(zip(fields, vessel_nav))
id_vessel = vessel_nav_dict['vessel.id']
sql = """
select %s
from vessel_position
where id_vessel = %%s
and time >= %%s and time <= %%s
order by time desc
""" % ','.join(VESSEL_POS_FIELDS)
positions = db_utils.query(
conn, sql, (id_vessel, vessel_nav_dict['departure_time'],
vessel_nav_dict['arrival_time']))
if positions:
positions = [
dict(zip(VESSEL_POS_FIELDS, pos)) for pos in positions
]
detail_obj = init_vessel_detail_obj(vessel_nav_dict, positions)
detail_obj.update_portnames(conn)
return_obj = detail_obj.toDict()
return {'object': return_obj, 'res': RESP_RESULT.S}
def order_item_quantity(conn, id_item):
query_str = ("SELECT quantity "
"FROM order_details "
"WHERE order_details.id_item = %s ")
r = query(conn, query_str, (id_item, ))
return r and r[0][0] or None
def _on_get(self, req, resp, conn, **kwargs):
sql = """select id from ticket
where exists (
select 1 from ticket as inner_ticket
where inner_ticket.thread_id = ticket.id
%(filter_sql)s
)
%(sort_sql)s %(page_sql)s
"""
params = []
filter_sql = []
self._filter(req, filter_sql, params)
sort_sql = []
self._sort(req, sort_sql, params)
page_sql = []
self._paginate(req, page_sql, params)
threads = db_utils.query(
self.conn, sql % {
'filter_sql': ''.join(filter_sql),
'sort_sql': ''.join(sort_sql),
'page_sql': ''.join(page_sql)
}, params)
thread_ids = [t[0] for t in threads]
return self.get_threads_details(thread_ids)
def _get_invoice_info_for_order_item(conn, item_id):
fields, columns = zip(*[
('shipment_id', 'shipments.id'),
('total_amount', 'amount_due'),
('currency', 'currency'),
('due_within', 'due_within'),
('shipping_within', 'shipping_within'),
('invoice_item', 'invoice_items'),
])
query_str = (
"SELECT %s FROM shipping_list "
"LEFT JOIN shipments "
"ON shipments.id = shipping_list.id_shipment "
"LEFT JOIN invoices "
"ON shipments.id = invoices.id_shipment "
"WHERE id_item = %%s "
"ORDER BY shipping_list.id_shipment, shipping_list.id_item")\
% ', '.join(columns)
results = query(conn, query_str, params=[
item_id,
])
results = [dict(zip(fields, r)) for r in results]
for r in results:
if r['invoice_item']:
items_dict = dict([(i['@id'], i)
for i in ujson.loads(r['invoice_item'])])
r['invoice_item'] = ujson.dumps(items_dict.get(str(item_id)) or {})
else:
r['invoice_item'] = ujson.dumps({})
return results
def delete_order(conn, order_id, brand_id, shops_id):
fields, columns = zip(*(ORDER_ITEM_FIELDS_COLUMNS))
query_str = """
SELECT %s
FROM orders
LEFT JOIN order_details
ON order_details.id_order = orders.id
LEFT JOIN order_items
ON order_items.id = order_details.id_item
WHERE orders.id = %%s
ORDER BY confirmation_time, order_items.id
""" % (', '.join(columns))
results = query(conn, query_str, params=[order_id])
allowed = True
for result in results:
order_item = dict(zip(fields, result))
if (order_item['brand_id'] != int(brand_id)
or order_item['shop_id'] not in shops_id):
allowed = False
break
if allowed:
update(conn, 'orders', values={'valid': False}, where={'id': order_id})
from models.coupon import cancel_coupon_for_order
cancel_coupon_for_order(conn, order_id)
return allowed
def get_threads_details(self, thread_ids):
if not thread_ids:
return []
columns = ("id", "thread_id", "subject", "message", "priority",
"feedback", "id_order", "id_shipment", "fo_author",
"bo_author", "created")
sql = ("select %s from ticket"
" where thread_id in (%s)"
" order by thread_id, created" %
(",".join(columns), ",".join(map(str, thread_ids))))
rows = db_utils.query(self.conn, sql)
thread_dict = {}
cached_user_dict = {}
for row in rows:
row_dict = dict(zip(columns, row))
row_dict['attachments'] = self.get_attachments_info(row_dict['id'])
if row_dict['fo_author']:
if row_dict['fo_author'] not in cached_user_dict:
cached_user_dict[row_dict['fo_author']] = \
get_user_info(self.conn, row_dict['fo_author'])
extra_user_info = cached_user_dict[row_dict['fo_author']]
row_dict.update(extra_user_info)
if row_dict['thread_id'] not in thread_dict:
thread_dict[row_dict['thread_id']] = []
thread_dict[row_dict['thread_id']].append(row_dict)
return [thread_dict.get(thread_id, []) for thread_id in thread_ids]
def _get_paid_time_list(conn, status, order_id):
if status == ORDER_STATUS.COMPLETED:
fields, columns = zip(
*[('shop_id', 'id_shop'), ('timestamp',
'invoice_status.timestamp')])
query_str = (
"SELECT %s FROM shipments "
"LEFT JOIN invoices "
"ON shipments.id = invoices.id_shipment "
"LEFT JOIN invoice_status "
"ON invoices.id = invoice_status.id_invoice "
"WHERE shipments.id_order = %%s "
"AND invoice_status.status = %%s ") \
% ', '.join(columns)
else:
fields, columns = zip(
*[('shop_id', 'id_shop'), ('timestamp', 'invoices.update_time')])
query_str = (
"SELECT %s FROM shipments "
"LEFT JOIN invoices "
"ON shipments.id = invoices.id_shipment "
"WHERE shipments.id_order = %%s "
"AND invoices.status = %%s ") \
% ', '.join(columns)
results = query(conn,
query_str,
params=[order_id, INVOICE_STATUS.INVOICE_PAID])
return [dict(zip(fields, r)) for r in results]
def order_iv_sent_status(conn, order_id, id_brand, id_shops):
sp_r, iv_r = _order_iv_info(conn, order_id, id_brand, id_shops)
if len(sp_r) > 0 and len(iv_r) == 0:
return ORDER_IV_SENT_STATUS.NO_SENT
elif len(set(sp_r) - set(iv_r)) > 0:
return ORDER_IV_SENT_STATUS.PART_SENT
# sum packing quantity for order.
pk_qty_query = (
"SELECT sum(quantity) "
"FROM shipping_list as spl "
"JOIN shipments as spm "
"ON spl.id_shipment = spm.id "
"WHERE spm.id_order = %s "
"AND spm.status <> %s "
"AND spm.id_brand = %s "
)
params = [order_id, SHIPMENT_STATUS.DELETED, id_brand]
if id_shops:
pk_qty_query += "AND spm.id_shop in %s "
params.append(tuple(id_shops))
pk_qty = query(conn, pk_qty_query, params)
pk_qty = pk_qty and pk_qty[0][0] or 0
# sum order items quantity for order.
od_qty_query = (
"SELECT sum(quantity) "
"FROM order_details as od "
"JOIN order_items as oi "
"ON od.id_item = oi.id "
"WHERE od.id_order = %s "
)
params = [order_id]
if id_shops:
od_qty_query += "AND oi.id_shop in %s"
params.append(tuple(id_shops))
od_qty = query(conn, od_qty_query, params)[0][0]
if od_qty - pk_qty > 0:
return ORDER_IV_SENT_STATUS.WAITING_SPM_CREATE
else:
return ORDER_IV_SENT_STATUS.SENT
def get_iv_numbers(conn, iv_ids):
sql = """SELECT invoice_number
FROM invoices
WHERE id in %s
"""
r = query(conn, sql, [tuple(iv_ids)])
return [item[0] for item in r]
def user_accessable_order(conn, id_order, id_user):
query_str = ("SELECT id_user " "FROM orders " "WHERE id = %s")
r = query(conn, query_str, (id_order, ))
if not r:
return False
else:
return int(r[0][0]) == int(id_user)
def shipping_list_item_packing_quantity(conn, id_shipment, id_order_item):
query_str = ("SELECT packing_quantity "
"FROM shipping_list as spl "
"WHERE spl.id_item = %s "
"AND spl.id_shipment = %s ")
r = query(conn, query_str, (id_order_item, id_shipment))
return r and r[0][0] or None
def _shipping_list_item(self, id_shipment):
sql = """SELECT id
FROM shipping_list
WHERE id_shipment=%s
"""
with db_utils.get_conn() as conn:
results = db_utils.query(conn, sql, (id_shipment, ))
return [item[0] for item in results]
def get_invoices_by_shipments(conn, id_shipments):
assert len(id_shipments) > 0
sql = """SELECT *
FROM invoices
WHERE id_shipment in %s"""
r = query(conn, sql, [tuple(id_shipments)])
return r
def _order_item(self, id_order):
sql = """SELECT id_item
FROM order_details
WHERE id_order=%s
"""
with db_utils.get_conn() as conn:
results = db_utils.query(conn, sql, (id_order, ))
return [item[0] for item in results]
def get_telephone_id(self, users_id):
sql = """SELECT id
FROM users_phone_num
WHERE users_id = %s
"""
with db_utils.get_conn() as conn:
phone_id = db_utils.query(conn, sql, (users_id, ))
return phone_id
def get_addr_id(self, users_id):
sql = """SELECT id, addr_type
FROM users_address
WHERE users_id = %s
"""
with db_utils.get_conn() as conn:
addrs = db_utils.query(conn, sql, (users_id, ))
return addrs
def create_trans(conn,
id_order,
id_user,
id_invoices,
iv_numbers,
amount_due,
currency,
invoices_data,
status=TRANS_STATUS.TRANS_OPEN,
create_time=None,
update_time=None,
cookie=None):
if isinstance(id_invoices, list):
id_invoices = ujson.dumps(id_invoices)
# if there is an existing open transaction for this order and invoices,
# return it directly to avoid creating duplicate transactions
sql = """
SELECT id, cookie
FROM transactions
WHERE id_order = %(id_order)s AND id_invoices = '%(id_invoices)s' AND status != %(status)s ;
""" % ({
'id_order': id_order,
'id_invoices': id_invoices,
'status': TRANS_STATUS.TRANS_FAIL,
})
check = query(conn, sql)
if len(check) > 0:
return check[0][0]
if (create_time == None):
create_time = datetime.now()
if (update_time == None):
update_time = datetime.now()
values = {
'id_order': id_order,
'id_user': id_user,
'id_invoices': id_invoices,
'iv_numbers': iv_numbers,
'status': status,
'create_time': create_time,
'update_time': update_time,
'amount_due': amount_due,
'currency': currency,
'invoices_data': invoices_data
}
if cookie is not None:
values['cookie'] = cookie
trans_id = insert(conn, 'transactions', values=values, returning='id')
logging.info('transaction created: id: %s, values: %s', trans_id[0],
values)
return trans_id[0]
def order_item_packing_quantity(conn, id_order_item):
sql = ("SELECT sum(spl.packing_quantity) "
"FROM shipping_list as spl "
"JOIN shipments as spm "
"ON spl.id_shipment = spm.id "
"WHERE id_item = %s "
"AND spm.status <> %s")
r = query(conn, sql, (id_order_item, SHIPMENT_STATUS.DELETED))
return r and r[0][0] or 0
def get_supported_services(conn, id_shipment):
query_str = ("SELECT %s "
"FROM shipping_supported_services "
"WHERE id_shipment = %%s" %
", ".join(SHIPMENT_SERVICES_FIELDS))
r = query(conn, query_str, (id_shipment, ))
serv_list = []
for item in r:
serv_list.append(dict(zip(SHIPMENT_SERVICES_FIELDS, item)))
return serv_list
def user_accessable_shipment(conn, id_shipment, id_user):
query_str = ("SELECT * "
"FROM shipments as spm "
"JOIN orders as o "
"ON spm.id_order = o.id "
"WHERE spm.id = %s "
"AND o.id_user = %s")
r = query(conn, query_str, (id_shipment, id_user))
return len(r) > 0
def _order_need_confirmation(conn, id_order, id_brand=None, id_shops=None):
query_str = "select 1 from shipments where id_order=%s and status=%s"
params = [id_order, SHIPMENT_STATUS.CONFIRMING]
if id_brand:
query_str += " and id_brand=%s"
params.append(id_brand)
if id_shops:
query_str += 'and id_shop in %s '
params.append(tuple(id_shops))
confirming_items = query(conn, query_str, params=params)
return confirming_items and len(confirming_items) > 0
def get_shipping_fee(conn, id_shipment):
query_str = ("SELECT %s "
"FROM shipping_fee "
"WHERE id_shipment = %%s" % ", ".join(SHIPMENT_FEE_FIELDS))
r = query(conn, query_str, (id_shipment, ))
assert len(r) != 0, 'No fee info for shipment %s' % id_shipment
assert len(r) == 1, ("One shipment should only have one "
"fee record: shipment:%s, result: %s" %
(id_shipment, r))
return dict(zip(SHIPMENT_FEE_FIELDS, r[0]))
def _get_redeemed_times(conn, id_coupon):
results = db_utils.query(
conn, "select id from coupon_redeemed "
"where id_coupon=%s and order_status in (%s, %s) "
"union "
"select id from store_credit_redeemed "
"where id_coupon=%s and order_status in (%s, %s) ", [
id_coupon, ORDER_STATUS_FOR_COUPON.PENDING,
ORDER_STATUS_FOR_COUPON.PAID
] * 2)
return len(results)
def get_spl_by_item(conn, id_shipment, id_item):
query_str = ("SELECT %s "
"FROM shipping_list "
"WHERE id_shipment=%%s "
"AND id_item=%%s "
"ORDER BY id" % ", ".join(SPL_ITEM_FIELDS))
r = query(conn, query_str, (id_shipment, id_item))
shipping_list = []
for item in r:
shipping_list.append(dict(zip(SPL_ITEM_FIELDS, item)))
return shipping_list and shipping_list[0] or None
def get_shipments_by_id(conn, id_shipments):
assert len(id_shipments) > 0
query_str = ("SELECT %s "
"FROM shipments "
"WHERE id in %%s " % ", ".join(SHIPMENT_FIELDS))
r = query(conn, query_str, [tuple(id_shipments)])
shipment_list = []
for item in r:
shipment_list.append(dict(zip(SHIPMENT_FIELDS, item)))
return shipment_list
def get_orders_log(conn, from_, to):
try:
q = ("SELECT * "
"FROM orders_log "
"WHERE (pending_date >= %s AND pending_date < %s) or "
"(waiting_payment_date >= %s AND waiting_payment_date < %s) or "
"(waiting_shipping_date >= %s AND waiting_shipping_date < %s) or "
"(completed_date >=%s AND completed_date < %s)")
r = query(conn, q, (from_, to, from_, to, from_, to, from_, to))
return r
except Exception, e:
logging.error('get_orders_log: %s', e, exc_info=True)
def _get_order_carrier_ids(conn, order_id):
query_str = ("SELECT id_postage, supported_services FROM shipments "
"JOIN shipping_supported_services "
"ON shipments.id = shipping_supported_services.id_shipment "
"WHERE id_order = %s "
"AND id_postage is not null")
results = query(conn, query_str, params=[
order_id,
])
return [
str(ujson.loads(s_mapping)[str(s_id)]) for s_id, s_mapping in results
]
def get_user_address(conn, user_id, addr_id=None, addr_type=None):
"""
@param conn: database connection
@param user_id: user's id.
@param addr_id: id of users_address
@param addr_type: type of users_address
@return: [{'id': ...,
'address': ...,
'address2': ...,
'city': ...,
'country_code': ...,
'province_code': ...,
'postal_code': ...,
'full_name': ...},
...]
"""
fields, columns = zip(*ADDR_FIELDS_COLUMNS)
where = "WHERE users_id = %s "
params = [user_id]
if addr_id:
where += "AND id = %s "
params.append(addr_id)
if addr_type and addr_type in (ADDR_TYPE.Shipping, ADDR_TYPE.Billing):
where += "AND addr_type in (%s, %s) "
params.append(addr_type, ADDR_TYPE.Both)
query_str = (
"SELECT %s "
"FROM users_address "
"LEFT JOIN country "
"ON users_address.country_code = country.iso "
"LEFT JOIN country_calling_code "
"ON users_address.country_code = country_calling_code.country_code "
"LEFT JOIN province "
"ON users_address.province_code = province.code "
"AND users_address.country_code = province.country_code "
"%s"
"ORDER BY users_address.id") % (", ".join(columns), where)
results = query(conn, query_str, params=params)
customer_name = None
addrs = [dict(zip(fields, result)) for result in results]
for addr in addrs:
if not addr['full_name']:
if not customer_name:
profile = get_user_profile(conn, user_id)
customer_name = ' '.join(
[profile['first_name'], profile['last_name']])
addr['full_name'] = customer_name
return addrs