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()