def do_date_correction():
db = CouponsAlchemyDB()
query = 'select count(1) c, `to` from vouchers group by `to` order by c desc'
count_date_list = db.execute_raw_sql(query, dict())
done_date = parser.parse("2016-08-30 18:30:00.000000")
for count_date_map in count_date_list:
date_value = count_date_map['to']
if date_value == done_date:
continue
get_voucher_query = 'select `code`, `from` from vouchers where `to` = :to'
code_from_list = db.execute_raw_sql(get_voucher_query, {'to': date_value})
code_from_list = [{'from': code_from_dict['from'].isoformat(), 'code': code_from_dict['code']} for code_from_dict in code_from_list]
lists = list(chunks(code_from_list, 5000))
for a_list in lists:
body = [
{
"coupons": a_list,
"update": {
"to": date_value.isoformat()
}
}
]
r = do_it_now(body=body, params={'force': u'true'})
with open('/var/log/couponlogs/grocery/output_date_correction.log', 'a+') as f:
f.write(r.text)
f.close()
def do_iocl_date_correction():
to = '2016-06-14'
db = CouponsAlchemyDB()
get_voucher_query = "select code, `from`, `to` from all_vouchers where `to`> :to and ( code rlike '20[1-5][0-9][0-9]{6}NEW' or code rlike '20[1-5][0-9][0-9]{6}TWO' or code rlike '20[1-5][0-9][0-9]{6}REP')"
code_from_list = db.execute_raw_sql(get_voucher_query, {'to': to})
code_from_list = [{'to': code_from_dict['to'].isoformat(), 'from': code_from_dict['from'].isoformat(), 'code': code_from_dict['code']} for code_from_dict in code_from_list]
lists = list(chunks(code_from_list, 2000))
for a_list in lists:
body = list()
for coupon in a_list:
if coupon['code'] == '2016006271REP':
continue
body_dict = {
"coupons": [{
'code': coupon['code'],
'from': coupon['from']
}],
"update": {
"to": '2016-01-01'
}
}
body.append(body_dict)
r = do_it_now(body=body)
with open('/Users/abhi/Documents/output_ibl_expire.log', 'a+') as f:
f.write(r.text)
f.close()
def find_voucher_at_the_date(code, date, db=None):
if not db:
db = CouponsAlchemyDB()
query = 'select * from all_vouchers where code=:code and `from` <= :date and `to >= :date`'
args = {'code': code, 'date': date}
voucher_dict = db.execute_raw_sql(query, args)
if voucher_dict:
voucher = Vouchers.from_dict(voucher_dict[0])
return voucher
return False
def recreate_ibl():
to = '2016-06-14'
db = CouponsAlchemyDB()
get_voucher_query = "select code, `from`, `to` from all_vouchers where `to`> :to and ( code rlike '20[1-5][0-9][0-9]{6}NEW' or code rlike '20[1-5][0-9][0-9]{6}TWO' or code rlike '20[1-5][0-9][0-9]{6}REP')"
code_from_list = db.execute_raw_sql(get_voucher_query, {'to': to})
code_from_list = [{'to': code_from_dict['to'].isoformat(), 'from': code_from_dict['from'].isoformat(), 'code': code_from_dict['code']} for code_from_dict in code_from_list]
print code_from_list.__len__()
lists = list(chunks(code_from_list, 200))
# process_one_list(lists[0])
threads = [gevent.spawn(process_one_list, a_list) for a_list in lists]
gevent.joinall(threads)
def is_validity_period_exclusive_for_freebie_voucher_code(
existing_voucher_dict, db=None):
sql = 'select * from `auto_benefits` where type=:type and zone=:zone and ('
if existing_voucher_dict.get('cart_range_min'):
where_clause1 = '( ((cart_range_min is null or (:cart_range_min >= cart_range_min)) && (cart_range_max is null or (:cart_range_min <= cart_range_max))) or ( (:cart_range_min is null or (cart_range_min >= :cart_range_min)) && (:cart_range_max is null or (cart_range_min <= :cart_range_max))) ) '
else:
where_clause1 = '(cart_range_min is null)'
if existing_voucher_dict.get('cart_range_max'):
where_clause2 = '( ((cart_range_min is null or (:cart_range_max >= cart_range_min)) && (cart_range_max is null or (:cart_range_max <= cart_range_max)) ) or ( (:cart_range_min is null or (cart_range_max >= :cart_range_min)) && (:cart_range_max is null or (cart_range_max <= :cart_range_max))) )'
else:
where_clause2 = '(cart_range_max is null)'
date_overlapping_caluse = '(((:from >= `from` && :from <= `to`) or (:to >= `from` && :to <= `to`)) or ((`from` >= :from && `from` <= :to) or (`to` >= :from && `to` <= :to) ))'
if existing_voucher_dict.get('type') is VoucherType.auto_freebie.value:
if existing_voucher_dict.get('range_min'):
where_clause3 = '( ((range_min is null or (:range_min >= range_min)) && (range_max is null or (:range_min <= range_max))) or ( (:range_min is null or (range_min >= :range_min)) && (:range_max is null or (range_min <= :range_max))) )'
else:
where_clause3 = '(range_min is null)'
if existing_voucher_dict.get('range_max'):
where_clause4 = '( ((range_min is null or (:range_max >= range_min)) && (range_max is null or (:range_max <= range_max)) ) or ( (:range_min is null or (range_max >= :range_min)) && (:range_max is null or (range_max <= :range_max))) )'
else:
where_clause4 = '(range_max is null)'
sql += '(' + where_clause1 + ' or ' + where_clause2 + ' or ' + where_clause3 +\
' or ' + where_clause4 + ') && ('+date_overlapping_caluse+')' + ') and variants=:variants'
else:
sql += '(' + where_clause1 + ' or ' + where_clause2 + ') && (' + date_overlapping_caluse + ')' + ')'
if not db:
db = CouponsAlchemyDB()
existing_voucher_list = db.execute_raw_sql(sql, existing_voucher_dict)
if existing_voucher_list:
error_list = list()
for existing_voucher in existing_voucher_list:
voucher = Vouchers.find_one_by_id(existing_voucher['voucher_id'])
if voucher.code != existing_voucher_dict.get('code'):
msg = u'Voucher {} overlaps ranges with this voucher with values cart_range_min: {}, cart_range_max: {}'.format(
voucher.code, existing_voucher['cart_range_min'],
existing_voucher['cart_range_max'])
if existing_voucher_dict.get('type'):
msg += u', range_min: {}, range_max: {}'.format(
existing_voucher['range_min'],
existing_voucher['range_max'])
error_list.append(msg)
continue
error_list.append(u'Expire the voucher {} and recreate'.format(
voucher.code))
if error_list:
return False, error_list
return True, None
def is_validity_period_exclusive_for_voucher_code(voucher, db=None):
if not db:
db = CouponsAlchemyDB()
date_overlapping_caluse = '(((:from >= `from` && :from <= `to`) or (:to >= `from` && :to <= `to`)) or ((`from` >= :from && `from` <= :to) or (`to` >= :from && `to` <= :to) ))'
date_overlap_params = {
'from': voucher.from_date,
'to': voucher.to_date,
'code': voucher.code
}
sql = "select * from all_vouchers where code=:code && (" + date_overlapping_caluse + ")"
voucher_list = db.execute_raw_sql(sql, date_overlap_params)
if voucher_list:
return False, u'Vouchers with overlapping dates found for code {}'.format(
voucher.code)
return True, None
def fetch_auto_benefits(order, freebie_type=VoucherType.regular_freebie):
assert isinstance(order, OrderData)
variant_total_map = dict()
item_list = list()
item_variant_map = dict()
if freebie_type is VoucherType.auto_freebie:
for item in order.items:
variant_total_map[item.variant] = variant_total_map.get(
item.variant, 0.0) + (item.price * item.quantity)
list_of_item_id = item_variant_map.get(item.variant, list())
list_of_item_id.append(item.item_id)
item_variant_map[item.variant] = list_of_item_id
where_clause, params = get_where_clauses(variant_total_map)
sql = 'select v.*,a.`type`, a.`variants`, a.`zone` from `vouchers` v join (select * from `auto_benefits` where (type = :type and zone in :zone and (' + where_clause + ') and (cart_range_min is null or cart_range_min <= :ordertotal) and (cart_range_max is null or cart_range_max >= :ordertotal) and :now > `from` and :now < `to`)) a on v.id=a.`voucher_id`'
else:
for item in order.items:
item_list.append(item.item_id)
params = dict()
sql = 'select v.*,a.`type`, a.`variants`, a.`zone` from `vouchers` v join (select * from `auto_benefits` where (type = :type and zone in :zone and (cart_range_min is null or cart_range_min <= :ordertotal) and (cart_range_max is null or cart_range_max >= :ordertotal) and :now > `from` and :now < `to`)) a on v.id=a.`voucher_id`'
params['ordertotal'] = order.total_price
params['type'] = freebie_type.value
params['zone'] = order.zone
params['now'] = datetime.datetime.utcnow()
db = CouponsAlchemyDB()
l = db.execute_raw_sql(sql, params)
if not l:
return
for voucher_dict in l:
effective_voucher = Vouchers.from_dict(voucher_dict)
effective_voucher.get_rule()
success_dict = {
'voucher': effective_voucher,
}
if freebie_type is VoucherType.auto_freebie:
success_dict['total'] = variant_total_map[voucher_dict['variants']]
success_dict['item_id_list'] = item_variant_map[
voucher_dict['variants']]
else:
success_dict['total'] = order.total_price
success_dict['item_id_list'] = item_list
order.existing_vouchers.append(success_dict)
def update_keys_in_input_list(data_list):
db = CouponsAlchemyDB()
db.begin()
change_id = None
try:
db.insert_row("all_vouchers_log_sequence")
query = 'select last_insert_id() as id'
max_change_log = db.execute_raw_sql(query, dict())
change_id = max_change_log[0]['id']
db.commit()
except Exception as e:
logger.exception(e)
db.rollback()
change_id = None
success_list = list()
error_list = list()
if change_id:
for data in data_list:
success_list_in_this_data, error_list_in_this_data = update_values_in_this_list_of_coupons(
data, change_id=change_id)
success_list += success_list_in_this_data
error_list += error_list_in_this_data
return success_list, error_list
def do_expire_iocl_and_recreate_with_valid_on_first_order():
date_value = "2016-08-30 18:30:00.000000"
expire_date_value = "2015-08-30 18:30:00.000000"
expire_date = parser.parse(expire_date_value)
date_value = parser.parse(date_value)
db = CouponsAlchemyDB()
get_voucher_query = 'select code, `from` from all_vouchers where `to`=:to and code not in (select av.code from all_vouchers av join `voucher_use_tracker` vut on av.id=vut.`voucher_id` where `to`=:to)'
code_from_list = db.execute_raw_sql(get_voucher_query, {'to': date_value})
code_from_list = [{'from': code_from_dict['from'].isoformat(), 'code': code_from_dict['code']} for code_from_dict in code_from_list]
code_all_list = [code_from_dict['code'] for code_from_dict in code_from_list]
lists = list(chunks(code_from_list, 5000))
for a_list in lists:
body = [
{
"coupons": a_list,
"update": {
"to": expire_date.isoformat()
}
}
]
r = do_it_now(body=body)
with open('/var/log/couponlogs/grocery/output_iocl.log', 'a+') as f:
f.write(r.text)
f.close()
codes_all_rule_list = list(chunks(code_all_list, 5000))
for codes_for_68_rule in codes_all_rule_list:
body = {
"code": codes_for_68_rule,
"from": "2016-06-18 00:00:00",
"description": "arti-IOCL punjab",
"rules": [
{
"benefits": {
"amount": 250,
"freebies": [
[]
]
},
"description": "arti-IOCL punjab",
"criteria": {
"cart_range_min": 250,
"no_of_uses_allowed_per_user": 1,
"no_of_total_uses_allowed": 1,
"valid_on_order_no": [1],
"channels": [
0,
1
],
"location": {
"country": [
1
],
"state": [
47
]
}
}
}
],
"custom": "{\"Param\":\"\"}",
"to": "2016-08-30T18:30:00",
"user_id": "1205565",
"type": 2
}
r = create_it_now(body=body)
with open('/var/log/couponlogs/grocery/create_iocl.log', 'a+') as f:
f.write(r.text)
f.close()