20160801_create_payments_table.sql 1.58 KB
Newer Older
Denis S. Valdenaire's avatar
Denis S. Valdenaire committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
DROP TABLE IF EXISTS lud_payments;

CREATE TABLE `lud_payments` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `subscription_id` int(11) NOT NULL,
 `payment_method_id` int(11) NOT NULL,
 `amount` float(5,2) NOT NULL DEFAULT '0.00',
 `comments` text NOT NULL,
 `check_bank_name` varchar(64) NULL,
 `check_owner_name` varchar(64) NULL,
 `created_at` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `subscription_id` (`subscription_id`),
 CONSTRAINT `lud_payments_ibfk_1` FOREIGN KEY (`subscription_id`) REFERENCES `lud_subscriptions` (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO lud_payments (subscription_id, payment_method_id, amount, comments, created_at)
SELECT id, payment_method_id, price, 
    CASE WHEN comments IS NULL THEN
        '(Importé automatiquement)'
    ELSE 
        CONCAT('(Importé automatiquement) ', comments)
    END AS comments, now()
FROM `lud_subscriptions`;

-- backup lud_subscriptions
CREATE TABLE lud_subscriptions_bck AS SELECT * FROM lud_subscriptions;

-- rename some columns as deprecated
ALTER TABLE `lud_subscriptions` DROP FOREIGN KEY `fk_member_sub_payment_method_id`;
ALTER TABLE `lud_subscriptions` CHANGE `payment_method_id` `payment_method_id_bck` INT(11) NOT NULL;

33 34 35 36 37 38 39 40
DROP TABLE lud_payment_methods;

alter table lud_payments add payment_method_name varchar(32) not NULL after payment_method_id;

update lud_payments set payment_method_name = 'ludotheque_new_payment_check' where payment_method_id = 1;
update lud_payments set payment_method_name = 'ludotheque_new_payment_cash' where payment_method_id = 2;

alter table lud_payments drop column payment_method_id;