In [ ]:
import os
import MySQLdb
db = MySQLdb.connect(
host=os.environ['ECOMMERCE_REPLICA_DB_HOST'],
database='ecommerce',
user=os.environ['ECOMMERCE_REPLICA_DB_USER'],
password=os.environ['ECOMMERCE_REPLICA_DB_PASSWORD']
)
cursor = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute('SELECT 1;')
print("Database connected!")
In [ ]:
BASKET_IDS = [11847171,
12348665,
16391142,
21958533,
21973833,
27314362,
27748648,
28289723,
28396435,
29473313,
29585379,
30181937,
30340584,
30354034,
30466682,
30634160,
30652686,
30658770,
30694235,
30707215,
30717047,
30726879,
30727264,
30753175,
30768486,
30786806,
30797429,
30817614,
30820696,
30823784,
30824059,
30826829,
30827722,
30845036,
30884834,
30918455,
30920528,
30920647,
30925217,
30961465,
30981445,
30986317,
30999483,
31005124,
31017269,
31017710,
31018511,
31023939,
31025787,
31026655,
31036123,
31037121,
31038699,
31041248,
31043490,
31045134,
31046284,
31047432,
31047485,
31047990,
31048120,
31048122,
31048130,
31048156,
31048161,
31048167,
31048169,
31048182,
31048201,
31048211,
31048221,
31048229,
31048235,
31048237,
31048243,
31048248,
31048257,
31048266,
31048268,
31048273,
31048279,
31048293,
31048303,
31048309,
31048316,
31048319,
31048327,
31048332,
31048344,
31048355,
31048358,
31048359,
31048365,
31048368,
31048371,
31048380,
31048381,
31048384,
31048388,
31048391,
31048396,
31048402,
31048405,
31048411,
31048416,
31048422,
31048424,
31048431,
31048433,
31048442,
31048448,
31048453,
31048454,
31048457,
31048461,
31048467,
31048468,
31048473,
31048474,
31048475,
31048477,
31048481,
31048488,
31048500,
31048513,
31048515,
31048521,
31048538,
31048541,
31048554,
31048555,
31048566,
31048568,
31048569,
31048593,
31048599,
31048602,
31048604,
31048612,
31048613,
31048623,
31048625,
31048626,
31048628,
31048632,
31048633,
31048635,
31048639,
31048641,
31048647,
31048650,
31048663,
31048664,
31048691,
31048701,
31048703,
31048704,
31048715,
31048717,
31048718,
31048720,
31048721,
31048736,
31048751,
31048754,
31048758,
31048764,
31048777,
31048781,
31048782,
31048784,
31048791,
31048807,
31048817,
31048838,
31048839,
31048850,
31048864,
31048871,
31048875,
31048885,
31048889,
31048898,
31048901,
31048905,
31048910,
31048917,
31048922,
31048944,
31048945,
31048953,
31048954,
31048958,
31048963,
31048969,
31048993,
31048997,
31048998,
31049016,
31049023,
31049027,
31049029,
31049035,
31049048,
31049052,
31049066,
31049075,
31049077,
31049080,
31049091,
31049102,
31049107,
31049111,
31049112,
31049114,
31049116,
31049120,
31049122,
31049128,
31049130,
31049135,
31049136,
31049139,
31049150,
31049154,
31049170,
31049172,
31049178,
31049182,
31049183,
31049189,
31049192,
31049197,
31049205,
31049209,
31049210,
31049213,
31049214,
31049218,
31049220,
31049222,
31049233,
31049237,
31049240,
31049250,
31049253,
31049267,
31049270,
31049295,
31049299,
31049310,
31049315,
31049316,
31049324,
31049325,
31049328,
31049336,
31049338,
31049341,
31049346,
31049350,
31049365,
31049366,
31049373,
31049374,
31049377,
31049390,
31049394,
31049401,
31049408,
31049420,
31049422,
31049427,
31049428,
31049432,
31049436,
31049438,
31049442,
31049446,
31049455,
31049461,
31049462,
31049469,
31049472,
31049486,
31049489,
31049495,
31049498,
31049515,
31049528,
31049529,
31049532,
31049534,
31049537,
31049547,
31049550,
31049562,
31049567,
31049601,
31049604,
31049614,
31049618,
31049635,
31049641,
31049642,
31049652,
31049655,
31049667,
31053781]
format_strings = ','.join(['%s'] * len(BASKET_IDS))
# All affected baskets
cursor.execute("""
SELECT
b.id,
b.owner_id,
u.username,
u.email,
p.id AS product_id,
ordered_baskets.number
FROM
basket_basket b
JOIN ecommerce_user u ON b.owner_id = u.id
JOIN basket_line bl ON b.id = bl.basket_id
JOIN catalogue_product p ON bl.product_id = p.id
JOIN basket_basket_vouchers bv ON b.id = bv.basket_id
JOIN voucher_voucher v ON bv.voucher_id = v.id
JOIN voucher_voucher_offers vo ON v.id = vo.voucher_id
JOIN offer_conditionaloffer co ON vo.conditionaloffer_id = co.id
LEFT JOIN (
SELECT
b.owner_id,
p.id AS product_id,
o.number
FROM
basket_basket b
JOIN basket_basket_vouchers bv ON b.id = bv.basket_id
JOIN voucher_voucher v ON bv.voucher_id = v.id
JOIN voucher_voucher_offers vo ON v.id = vo.voucher_id
JOIN offer_conditionaloffer co ON vo.conditionaloffer_id = co.id
JOIN ecommerce_user u ON b.owner_id = u.id
JOIN order_order o ON b.id = o.basket_id
JOIN order_line ol ON o.id = ol.order_id
JOIN catalogue_product p ON ol.product_id = p.id
WHERE
v.code = 'CYBEREDX15'
) ordered_baskets ON ordered_baskets.owner_id = b.owner_id AND ordered_baskets.product_id = p.id
WHERE
v.code = 'CYBEREDX15'
AND b.id IN (%s);
"""% format_strings, tuple(BASKET_IDS))
affected_baskets = cursor.fetchall()
print('Affected baskets: %d' % len(affected_baskets))
affected_user_ids = set([basket['owner_id'] for basket in affected_baskets])
print('Affected users: %d' % len(affected_user_ids))
In [ ]:
# Baskets that definitely need to be refunded, because the user purchased the product in another order
baskets_to_refund = []
refund_candidates = []
for basket in affected_baskets:
if basket['number']:
baskets_to_refund.append(basket)
else:
refund_candidates.append(basket)
print(len(baskets_to_refund))
print(len(refund_candidates))
In [ ]:
# Find baskets that are duplicate payments so we can refund all but one of the payments
more_baskets_to_refund = []
reviewed_basket_ids = set()
for candidate in refund_candidates:
if candidate['id'] in reviewed_basket_ids:
continue;
for potential_duplicate in refund_candidates:
if candidate['id'] != potential_duplicate['id'] \
and candidate['product_id'] == potential_duplicate['product_id'] \
and candidate['owner_id'] == potential_duplicate['owner_id']:
more_baskets_to_refund.append(potential_duplicate)
reviewed_basket_ids.add(potential_duplicate['id'])
refund_me = baskets_to_refund + more_baskets_to_refund
print(len(refund_me))
columns = ('owner_id', 'username', 'email', 'product_id', 'basket_id',)
print(','.join(columns))
for row in refund_me:
print('%s,%s,%s,%s,%s' % (row['owner_id'], row['username'], row['email'], row['product_id'], row['id'],))
In [ ]:
# Find baskets for which we need to create orders
baskets_to_create_orders = []
for candidate in refund_candidates:
if candidate not in more_baskets_to_refund:
baskets_to_create_orders.append(candidate)
print('Orders need to be created for %d baskets' % len(baskets_to_create_orders))
In [ ]:
# List baskets for which orders are needed
columns = ('owner_id', 'product_id', 'basket_id',)
print(','.join(columns))
for row in baskets_to_create_orders:
print('%s,%s,%s,%s,%s' % (row['owner_id'], row['username'], row['email'], row['product_id'], row['id'],))
In [ ]:
# Show all baskets and the number of associated transactions
basket_ids = [basket['id'] for basket in baskets_to_create_orders]
print(len(basket_ids))
format_strings = ','.join(['%s'] * len(basket_ids))
cursor.execute("""
SELECT
b.id,
COUNT(1) AS 'count'
FROM
basket_basket b
JOIN payment_paymentprocessorresponse ppr ON ppr.basket_id = b.id
WHERE
b.id IN (%s)
AND ppr.transaction_id IS NOT NULL
GROUP BY
b.id
ORDER BY
COUNT(1) DESC
;
""" % format_strings, tuple(basket_ids))
transactions = cursor.fetchall()
print(len(transactions))
print(transactions)
In [ ]:
# List TRANSACTIONS that need to be refunded
basket_ids = [basket['id'] for basket in baskets_to_refund]
print(len(basket_ids))
format_strings = ','.join(['%s'] * len(basket_ids))
cursor.execute("""
SELECT
b.id,
b.owner_id,
u.username,
u.email,
p.id AS product_id,
ppr.transaction_id
FROM
basket_basket b
JOIN ecommerce_user u ON b.owner_id = u.id
JOIN basket_line bl ON b.id = bl.basket_id
JOIN catalogue_product p ON bl.product_id = p.id
JOIN basket_basket_vouchers bv ON b.id = bv.basket_id
JOIN voucher_voucher v ON bv.voucher_id = v.id
JOIN voucher_voucher_offers vo ON v.id = vo.voucher_id
JOIN offer_conditionaloffer co ON vo.conditionaloffer_id = co.id
JOIN payment_paymentprocessorresponse ppr ON ppr.basket_id = b.id
WHERE
v.code = 'CYBEREDX15'
AND b.status = 'Frozen'
AND b.id IN (%s);
""" % format_strings, tuple(basket_ids))
transactions = cursor.fetchall()
print(len(transactions))
print(transactions)