Skip to content

Instantly share code, notes, and snippets.

@lfborjas
Last active November 14, 2015 06:49
Show Gist options
  • Save lfborjas/72f2b061451993329879 to your computer and use it in GitHub Desktop.
Save lfborjas/72f2b061451993329879 to your computer and use it in GitHub Desktop.
Flipping: M Nov 2015
CREATE TEMPORARY TABLE boxes_to_restore
(rebillable_id int(10) unsigned,
bh_id int(10) unsigned,
billing_frequency tinyint(3) unsigned,
prev_cycle_status int(10) unsigned,
unique index (rebillable_id),
unique index (bh_id));
INSERT INTO boxes_to_restore (rebillable_id, bh_id, billing_frequency, prev_cycle_status)
SELECT rs.rebillable_id as rebillable_id, bh2.id as bh_id, rs.billing_frequency as billing_frequency, COALESCE(SUM(ctl.result='success')*100 - SUM(ctl.result='failure'),0) as prev_cycle_status
FROM rebillable_subscriptions rs
JOIN box_history bh ON bh.subscription_id=rs.rebillable_id AND bh.shipping_cycle_id=108
JOIN box_history bh2 ON bh2.subscription_id=rs.rebillable_id AND bh2.shipping_cycle_id=109
LEFT JOIN card_transactions_log ctl ON ctl.box_history_id=bh.id
WHERE rs.billing_status = 2
GROUP BY rs.rebillable_id
HAVING prev_cycle_status >= 0
ORDER BY rs.rebillable_id asc;
-- Query OK, 778 rows affected (0.83 sec)
-- Records: 778 Duplicates: 0 Warnings: 0
--- after the BBB
UPDATE rebillable_subscriptions rs
JOIN boxes_to_restore btr ON btr.rebillable_id=rs.rebillable_id
SET rs.billing_status=2;
UPDATE box_history bh
JOIN boxes_to_restore btr ON btr.bh_id=bh.id
SET bh.is_on_hold = 1, bh.is_reserved = 1;
SELECT *
FROM boxes_to_restore
INTO OUTFILE '/tmp/boxes_to_restore.20151114.csv'
FIELDS TERMINATED BY ',';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment