def add_card(self, conn, data):
# validation
for param in ['id_user', 'pan', 'cvc', 'expiration_date']:
if not data.get(param):
raise ValidationError('Missing param %s' % param)
id_user = data['id_user']
cardholder_name = data.get('cardholder_name', '')
pan = data['pan']
cvc = data['cvc']
expire = data['expiration_date']
repeat = data.get('repeat')
if not is_valid_cc_num(pan):
raise ValidationError('Invalid param pan')
if not is_valid_cvc(cvc):
raise ValidationError('Invalid param cvc')
try:
datetime.strptime(expire, '%m%y')
except:
raise ValidationError('Invalid param expiration_date')
results = db_utils.select(conn,
'credit_card',
where={
'id_user': id_user,
'cc_num': mask_cc_num(pan),
'expiration_date': expire,
'valid': True,
})
if results:
raise ValidationError('Duplicated Card')
values = {
'id_user': id_user,
'cardholder_name': cardholder_name,
'cc_num': mask_cc_num(pan),
'expiration_date': expire,
'repeat': bool(repeat),
'valid': False,
}
id_card = db_utils.insert(conn,
'credit_card',
values=values,
returning='id')[0]
cli = Paybox()
token = cli.register_card(id_card,
id_user,
pan,
cvc,
expire,
repeat=bool(repeat))
db_utils.update(conn,
'credit_card',
values={
'paybox_token': token,
'valid': True
},
where={'id': id_card})
return id_card
def _is_filed_referenced(self, conn, users_id, field, field_orig_table,
check_id):
""" Check whether address/phone_num used by user in past and current
shipments or invoices. And Mark the old address/phone_num as invalid.
"""
if not check_id.isdigit():
return False
assert field in ['id_shipaddr', 'id_billaddr', 'id_phone']
assert field_orig_table in ['users_address', 'users_phone_num']
results = db_utils.join(conn, ('order_shipment_details', 'orders'),
columns=(field, ),
on=[('order_shipment_details.id_order',
'orders.id')],
where={'orders.id_user': users_id})
# billing_result = db_utils.join(conn,
# ('invoices', 'orders'),
# columns=(field,),
# on=[('invoices.id_order', 'orders.id')],
# where={'orders.id_user': users_id})
#
referenced = [int(check_id)] in results
# mark old address/phone number as invalid.
if referenced:
db_utils.update(conn,
field_orig_table,
values={'valid': False},
where={'id': int(check_id)})
return referenced
def setUp(self):
super(TestCoupon, self).setUp()
self.id_brand = 1000001
self.bo_user = 1000002
self.id_coupon = None
with db_utils.get_conn() as conn:
db_utils.update(conn, "coupons", values={'valid': False})
def on_success_login(self, req, resp, conn, users_id, auth_token):
ip_address = get_client_ip(req)
headers = get_hashed_headers(req)
csrf_token = gen_csrf_token()
delta = timedelta(seconds=settings.USER_AUTH_COOKIE_EXPIRES)
utc_expiry = datetime.utcnow() + delta
values = {
"users_id": users_id,
"ip_address": ip_address,
"headers": headers,
"csrf_token": csrf_token,
"cookie_expiry": utc_expiry
}
db_utils.update(conn,
"users_logins",
values={"cookie_expiry": datetime.utcnow()},
where={
"users_id": users_id,
"ip_address": ip_address,
"headers": headers,
"cookie_expiry__gt": datetime.utcnow()
})
db_utils.insert(conn, "users_logins", values=values)
# set cookie
expiry = gen_cookie_expiry(utc_expiry)
auth_cookie = make_auth_cookie(expiry, csrf_token, auth_token,
users_id)
set_cookie(resp, USER_AUTH_COOKIE_NAME, auth_cookie, expiry=expiry)
self._set_basket_data(req, resp, users_id)
def auth_update(self, conn, users_id, raw_password):
""" Re-calculate user's encrypted password. Update new password,
salt, hash_algorithm, hash_iteration_count into database.
"""
auth_token, result = encrypt_password(raw_password)
db_utils.update(conn, 'users', values=result, where={'id': users_id})
return auth_token
def _coupon_redeemed(conn, id_coupon, id_order, id_invoice):
results = db_utils.select(conn,
'coupon_redeemed',
where={
'id_coupon': id_coupon,
'id_order': id_order,
})
if not results:
return
update_values = {
'id_invoice': id_invoice,
'order_status': ORDER_STATUS_FOR_COUPON.PAID,
}
if len(results) == 1 and results[0]['id_invoice'] is None:
db_utils.update(conn,
'coupon_redeemed',
values=update_values,
where={'id': results[0]['id']})
else:
values = {
'id_coupon': results[0]['id_coupon'],
'id_user': results[0]['id_user'],
'id_order': results[0]['id_order'],
'redeemed_time': results[0]['redeemed_item'],
'account_address': results[0]['account_address'],
'account_phone': results[0]['account_phone'],
'user_agent': results[0]['user_agent'],
}
values.update(update_values)
db_utils.insert(conn, 'coupon_redeemed', values=values)
def _on_post(self, req, resp, conn, **kwargs):
ticket_id = req.get_param('ticket_id')
priority = req.get_param('priority')
escalation = req.get_param('escalation')
update_values = {}
if priority:
if priority.isdigit() and int(
priority) not in TICKET_PRIORITY.toDict().values():
raise ValidationError('INVALID_PARAM_PRIORITY')
update_values.update({'priority': priority})
if escalation:
escalation = req.get_param('escalation') in ('True', 'true')
update_values.update({'escalation': escalation})
if escalation:
update_values.update({'escalation_time': datetime.utcnow()})
rows = db_utils.select(self.conn,
'ticket',
columns=('id', ),
where={'id': ticket_id},
limit=1)
if not rows or len(rows) == 0:
raise ValidationError('INVALID_PARAM_TICKET_ID')
db_utils.update(conn,
"ticket",
values=update_values,
where={'id': ticket_id})
return {"res": RESP_RESULT.S, "err": ""}
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 update_shipping_fee(conn, id_shipment, id_postage, shipping_fee):
try:
shipping_fee = to_round(shipping_fee)
update(conn,
'shipping_supported_services',
values={'id_postage': id_postage},
where={'id_shipment': id_shipment})
update(conn,
'shipping_fee',
values={'shipping_fee': shipping_fee},
where={'id_shipment': id_shipment})
conn.commit()
logging.info('update_shipping_fee:'
'id_postage: %s, '
'shipping_fee: %s, '
'for shipment: %s,' %
(id_postage, shipping_fee, id_shipment))
except Exception, e:
conn.rollback()
logging.error('update_shipping_fee err: %s, '
'args: id_shipment: %s,'
'id_postage: %s, '
'shipping_fee: %s' %
(e, id_shipment, id_postage, shipping_fee),
exc_info=True)
raise
def up_order_log(conn, id_order, sellers):
for id_brand, shops in sellers.iteritems():
for id_shop in shops:
status = get_order_status(conn, id_order, id_brand, [id_shop])
where = {
'id_order': id_order,
'id_brand': id_brand,
'id_shop': id_shop
}
log = select(conn, 'orders_log', where=where)
if log:
log = log[0]
else:
continue
v = None
if status == ORDER_STATUS.PENDING:
if log['pending_date'] is None:
v = {'pending_date': datetime.utcnow().date()}
elif status == ORDER_STATUS.AWAITING_PAYMENT:
if log['waiting_payment_date'] is None:
v = {'waiting_payment_date': datetime.utcnow().date()}
elif status == ORDER_STATUS.AWAITING_SHIPPING:
if log['waiting_shipping_date'] is None:
v = {'waiting_shipping_date': datetime.utcnow().date()}
elif status == ORDER_STATUS.COMPLETED:
if log['completed_date'] is None:
v = {'completed_date': datetime.utcnow().date()}
if v is not None:
where = {
'id_order': id_order,
'id_brand': id_brand,
'id_shop': id_shop
}
update(conn, 'orders_log', values=v, where=where)
def _update_phone_num(self, conn, req, users_id):
number_dict = {}
for p in req._params:
if p.startswith('country_num_'):
number_dict[p.replace('country_num_', '')] = {}
columns = ('country_num', 'phone_num', 'phone_num_desp')
for num_id in number_dict:
number_dict[num_id]['users_id'] = users_id
for c in columns:
p = req.get_param('%s_%s' % (c, num_id)) or ''
if c == 'phone_num' and not re.match(phone_num_reexp, p):
raise ValidationError('INVALID_PHONE_NUMBER')
number_dict[num_id][c] = p
num_changed = self._item_changed(conn, users_id, columns,
"users_phone_num", num_id,
number_dict[num_id])
if num_changed:
num_referenced = self._is_filed_referenced(
conn, users_id, 'id_phone', 'users_phone_num', num_id)
else:
num_referenced = False
if num_id.isdigit() and int(num_id) == 0 \
or num_changed and num_referenced:
db_utils.insert(conn,
"users_phone_num",
values=number_dict[num_id])
else:
db_utils.update(conn,
"users_phone_num",
values=number_dict[num_id],
where={'id': num_id})
def _update_parent_ticket(self, conn, parent_id):
db_utils.update(conn,
"ticket",
values={
'replied': True,
'locked': False
},
where={'id': parent_id})
def _update_address(self, conn, req, users_id):
addr_dict = {}
for p in req._params:
if p.startswith('country_code_'):
addr_dict[p.replace('country_code_', '')] = {}
columns = ('addr_type', 'address', 'city', 'postal_code',
'country_code', 'province_code', 'address_desp', 'address2',
'full_name')
for addr_id in addr_dict:
addr_dict[addr_id]['users_id'] = users_id
for c in columns:
p = req.get_param('%s_%s' % (c, addr_id)) or ''
if c == 'addr_type' and p.isdigit() \
and int(p) not in ADDR_TYPE.toDict().values():
raise ValidationError('INVALID_ADDR_TYPE')
if c == 'address' and not re.match(addr_reexp, p):
raise ValidationError('INVALID_ADDRESS')
if c == 'city' and not re.match(city_reexp, p):
raise ValidationError('INVALID_CITY')
if c == 'postal_code' and not re.match(postal_code_reexp, p):
raise ValidationError('INVALID_POSTAL_CODE')
addr_dict[addr_id][c] = p
if int(addr_id) > 0 and \
int(addr_dict[addr_id]['addr_type']) == ADDR_TYPE.Both:
raise ValidationError('INVALID_ADDR_TYPE')
addr_changed = self._item_changed(conn, users_id, columns,
"users_address", addr_id,
addr_dict[addr_id])
if addr_changed:
addr_referenced = self._is_filed_referenced(
conn, users_id, 'id_shipaddr', 'users_address', addr_id) \
or self._is_filed_referenced(
conn, users_id, 'id_billaddr', 'users_address', addr_id)
else:
addr_referenced = False
if (addr_id.isdigit() and int(addr_id) == 0 and int(
addr_dict[addr_id]['addr_type']) == ADDR_TYPE.Both):
# insert two address records if add type is ADDR_TYPE.Both
for t in (ADDR_TYPE.Shipping, ADDR_TYPE.Billing):
addr_dict[addr_id]['addr_type'] = t
db_utils.insert(conn,
"users_address",
values=addr_dict[addr_id])
elif (addr_id.isdigit() and int(addr_id) == 0
or addr_changed and addr_referenced):
db_utils.insert(conn,
"users_address",
values=addr_dict[addr_id])
else:
db_utils.update(conn,
"users_address",
values=addr_dict[addr_id],
where={'id': addr_id})
def update_trans(conn, values, where):
values.update({'update_time': datetime.now()})
update(conn,
'transactions',
values=values,
where=where)
logging.info("trans_updated: "
"values - %s, "
"where - %s", values, where)
def test_expired_session(self):
auth_cookie = self.login()
data = _parse_auth_cookie(auth_cookie.value.strip('"'))
with db_utils.get_conn() as conn:
db_utils.update(conn,
"users_logins",
{"cookie_expiry": datetime.datetime.utcnow()},
where={"users_id": data["users_id"]})
self.fail_auth_verification("LOGIN_REQUIRED_ERR_INVALID_USER")
def update_account_address(self, id_user, country, province, city):
with db_utils.get_conn() as conn:
db_utils.update(conn,
"users_address",
values={
"country_code": country,
"province_code": province,
"city": city
},
where={'users_id': id_user})
def cookie_verify(conn, req, resp):
""" Verify cookie to check if user is in login status, update csrf
token if pass cookie verification.
Checked:
* MAC
* Auth, IP, request headers, csrf(for post request)
conn: database connection.
req: request.
resp: response will send to client.
"""
cookie = get_cookie(req)
if not cookie:
raise ValidationError('LOGIN_REQUIRED_ERR_UNSET_COOKIE')
# 'USER_AUTH' should be in the cookie.
required_fields = [USER_AUTH_COOKIE_NAME]
for field in required_fields:
if not cookie.has_key(field):
raise ValidationError('LOGIN_REQUIRED_ERR_EMPTY_COOKIE')
# authenticated information should be contained in 'USER_AUTH'
auth_fields = ['exp', 'csrf', 'auth', 'digest', 'users_id']
user_auth = cookie.get(USER_AUTH_COOKIE_NAME).value
user_auth = _parse_auth_cookie(user_auth)
for field in auth_fields:
if not user_auth.has_key(field):
raise ValidationError('LOGIN_REQUIRED_ERR_MISSING_DATA: %s' %
field)
# check if the cookie has been tampered with before going any further
_hmac_verify(user_auth)
users_id = user_auth['users_id']
ip = get_client_ip(req)
headers = get_hashed_headers(req)
login_id = _user_verify(conn, users_id, user_auth, ip, headers)
if req.method == 'POST':
# set new csrf to cookie and database.
csrf_token = gen_csrf_token()
auth_cookie = make_auth_cookie(user_auth['exp'], csrf_token,
user_auth['auth'], users_id)
db_utils.update(conn,
'users_logins',
values={'csrf_token': csrf_token},
where={'id': login_id})
conn.commit()
set_cookie(resp,
USER_AUTH_COOKIE_NAME,
auth_cookie,
expiry=user_auth['exp'])
return users_id
def query_vessel_details(conn, search_by, q, force=False, exact_match=False):
fields = VESSEL_FIELDS + VESSEL_NAV_FIELDS + VESSEL_POS_FIELDS
sql = """
select %s
from vessel
join vessel_navigation
on (vessel.id=vessel_navigation.id_vessel)
join vessel_position
on (vessel.id=vessel_position.id_vessel)
left join country
on (vessel.country_isocode=country.iso)
where vessel.%s=%%s
and vessel_position.time >= vessel_navigation.departure_time
and vessel_position.time <= vessel_navigation.arrival_time
order by vessel_position.time desc limit 1
""" % (','.join(fields), search_by)
if not force and (search_by != 'name' or exact_match):
detail = db_utils.query(conn, sql, (q, ))
if len(detail) > 0 and \
not need_fetch_new_pos(dict(zip(fields, detail[0]))['time']):
detail_results = []
for item in detail:
item_dict = dict(zip(fields, item))
detail_obj = init_vessel_detail_obj(item_dict, [item_dict])
detail_obj.update_portnames(conn)
detail_results.append(detail_obj.toDict())
return detail_results
records, old_records = getVesselDs().getVesselInfo(**{search_by: q})
detail_results = []
for d in records:
try:
detail_obj = VesselDetailInfo(**d)
detail_obj.update_portnames(conn)
_save_result(conn, detail_obj)
update_interval = _get_update_interval(detail_obj)
now = datetime.datetime.utcnow()
delta = datetime.timedelta(seconds=update_interval)
db_utils.update(conn,
"vessel",
values={'next_update_time': now + delta},
where={'mmsi': str(detail_obj.mmsi)})
detail_results.append(detail_obj.toDict())
except DatabaseError, e:
conn.rollback()
logging.error('Server DB Error: %s', (e, ), exc_info=True)
detail_results.append(d)
def _save_ticket(self, conn, values):
ticket_id = db_utils.insert(conn,
"ticket",
values=values,
returning='id')[0]
# attachment
form_params = cgi.parse_qs(self.request.query_string)
for _id in form_params.get('attachment', []):
db_utils.update(conn,
"ticket_attachment",
values={'id_ticket': ticket_id},
where={'id': _id})
return ticket_id
def _modify_order_shipment_detail(conn, id_order, id_shipaddr, id_billaddr,
id_phone):
values = {
'id_shipaddr': id_shipaddr,
'id_billaddr': id_billaddr,
'id_phone': id_phone,
}
update(conn,
'order_shipment_details',
values=values,
where={'id_order': id_order})
logging.info('order_shipment_details for order %s modified values: %s',
id_order, values)
def _update_portname(self, conn, port_name, locode):
if not port_name: return
existing_name = self._query_portname(conn, locode)
values = {'locode': locode, 'name': port_name}
if existing_name is None:
db_utils.insert(conn, "port", values=values)
else:
if existing_name != port_name:
db_utils.update(conn,
"port",
values=values,
where={'locode': locode})
def _create_free_order_item(conn,
id_order,
id_sale,
orig_id_item,
item_update_value,
quantity=1):
sale = CachedSale(id_sale).sale
id_item = _create_order_item(conn, sale, 0, sale.brand.id)
db_utils.update(conn,
"order_items",
values=item_update_value,
where={'id': id_item})
_create_order_details(conn, id_order, id_item, quantity)
_create_shipping_list(conn, id_item, orig_id_item, quantity=quantity)
def coupon_delete(self, req, resp, conn):
id_brand = req.get_param('id_issuer')
if not id_brand:
raise ValidationError('COUPON_ERR_INVALID_ID_BRAND')
id_coupon = req.get_param('id_coupon')
coupons = db_utils.select(
conn, 'coupons', where={'id': id_coupon})
if not coupons:
raise ValidationError('COUPON_ERR_INVALID_ID_COUPON')
if coupons[0]['id_brand'] != int(id_brand):
raise ValidationError('COUPON_ERR_INVALID_COUPON_FOR_BRAND')
db_utils.update(conn, 'coupons', values={'valid': False},
where={'id': id_coupon})
return {'id_coupon': id_coupon}
def update_invoice(conn, id_iv, values, iv=None):
where = {'id': id_iv}
values['update_time'] = datetime.now()
if iv is None or iv['id'] != int(id_iv):
iv = get_invoice_by_id(conn, id_iv)
if iv is None:
logging.error('iv_update_err: invoice %s not exist',
id_iv)
return
r = update(conn,
"invoices",
values=values,
where=where,
returning='id')
if (values.get('status') and
int(values.get('status') != iv['status'])):
insert(conn,
'invoice_status',
values={'id_invoice': id_iv,
'status': iv['status'],
'amount_paid': iv['amount_paid'],
'timestamp': iv['update_time']})
seller = get_seller(conn, iv['id_shipment'])
from models.order import up_order_log
up_order_log(conn, iv['id_order'], seller)
if int(values.get('status')) == INVOICE_STATUS.INVOICE_PAID:
from models.coupon import redeem_coupon_for_shipment
redeem_coupon_for_shipment(conn, iv['id_order'], iv['id_shipment'], id_iv)
logging.info("invoice_%s updated: %s", id_iv, values)
return r and r[0] or None
def send_container_arrival_notif(container, last_pod):
logging.info("Sending container arrival notif for %s" % container)
with db_utils.get_conn() as conn:
msg = "Container(%s) is arriving in %s" % (container, last_pod)
notifs = db_utils.select(conn, "container_arrival_notif",
columns=("id", "email"),
where={'container': container,
'done': False})
for _id, _email in notifs:
send_html_email(_email,
msg,
msg)
db_utils.update(conn, "container_arrival_notif",
values={'done': True},
where={'id': _id})
conn.commit()
def update_trans(conn, values, where=None):
values.update({'update_time': datetime.now()})
r = update(conn,
"transactions",
values=values,
where=where,
returning="id")
logging.info('transaction_update, values - %s, where - %s', values, where)
return r
def _on_post(self, req, resp, conn, **kwargs):
try:
sid = req.get_param('sid')
users_id = req.get_param('users_id') or None
where = {'sid': sid}
v_log = select(conn, 'visitors_log', where=where)
if len(v_log) > 0:
update(conn,
'visitors_log', {'users_id': users_id},
where={'sid': sid})
else:
values = {'sid': sid, 'users_id': users_id}
insert(conn, 'visitors_log', values=values)
return {'result': RESP_RESULT.S}
except Exception, e:
logging.error('visitors_log_err: %s', e, exc_info=True)
return {'result': RESP_RESULT.F}
def _on_post(self, req, resp, conn, **kwargs):
ticket_id = req.get_param('ticket_id')
useful = req.get_param('useful') in ('True', 'true')
feedback = TICKET_FEEDBACK.USEFUL if useful else TICKET_FEEDBACK.USELESS
rows = db_utils.select(self.conn,
'ticket',
columns=('parent_id', 'fo_recipient'),
where={'id': ticket_id},
limit=1)
if rows and len(rows) == 1 \
and rows[0][0] != 0 and rows[0][1] is not None:
db_utils.update(conn,
"ticket",
values={'feedback': feedback},
where={'id': ticket_id})
else:
raise ValidationError('INVALID_PARAM_TICKET_ID')
return {"res": RESP_RESULT.S, "err": ""}
def send_vessel_arrival_notif(detail_vessel_obj):
logging.info("Sending vessel arrival notif for %s" % detail_vessel_obj.name)
msg = "Vessel %s is arriving in %s" % (
detail_vessel_obj.name,
detail_vessel_obj.departure_portname
or detail_vessel_obj.departure_locode)
with db_utils.get_conn() as conn:
notifs = db_utils.select(conn, "vessel_arrival_notif",
columns=("id", "email"),
where={'mmsi': str(detail_vessel_obj.mmsi),
'done': False})
for _id, _email in notifs:
send_html_email(_email,
msg,
msg)
db_utils.update(conn, "vessel_arrival_notif",
values={'done': True},
where={'id': _id})
conn.commit()
def update_shipping_fee(conn, id_shipment, values):
where = {'id_shipment': id_shipment}
if 'handling_fee' in values:
values['handling_fee'] = to_round(values['handling_fee'])
if 'shipping_fee' in values:
values['shipping_fee'] = to_round(values['shipping_fee'])
r = update(conn,
"shipping_fee",
values=values,
where=where,
returning="id")
return r and r[0] or None
