remove-unwanted-transactions.sql 2.6 KB
--This script has been used to delete unwanted transactions for
--https://redmine.cooperatic.fr/issues/4725

--Several variables must be set :
--@nb : nb of transactions that should be removed
--@date_before : time just before the first transaction we want to remove (ok if includes other transaction as soon as amount is different ; ok to include the first transaction (the true one))
--@date_after : time just after the last transaction we want to remove (ok if includes other transaction as soon as amount is different ; ok to include the first transaction (the true one))
--@montant : positive
--set first name, last name and raison
--CAREFUL ! Variable not working in limit clause : https://stackoverflow.com/questions/245180/using-variable-in-a-limit-clause-in-mysql
--setting limit manualy is therefore REQUIRED in the two first DELETE FROM requests

--It is probably better to identify the flux that have to be deleted and to deduce the operations that have to be deleted as well
--instead of doing so, we sort the operations (prestataire and adherent) by flux_id before performing a DELETE LIMIT in order
--to be sure to delete the operations that are associated to the same flux.
--then we remove the flux

--the drawback of this technique is that it does not make sure that the FIRST flux is the one that is kept if it was in the time interval

set @nb = 42;
set @date_before = 'aaaa-mm-dd HH:MM:SS';
set @date_after = 'aaaa-mm-dd HH:MM:SS';
set @montant = 4242.42;

select @account_adherent := account_adherent.id from user
    inner join adherent on adherent.id = user.adherent_id
    inner join account_adherent on account_adherent.adherent_id = user.adherent_id
where user.firstname = 'Prénom' and user.lastname = 'Nom';
select @account_prestataire := account_prestataire.id from prestataire
    inner join account_prestataire on account_prestataire.prestataire_id = prestataire.id
where prestataire.raison = 'Kumbi' and currency = 'emlc';

DELETE FROM operation_adherent
WHERE account_id = @account_adherent AND montant = -@montant AND created_at > @date_before AND created_at < @date_after
    ORDER BY flux_id DESC LIMIT 0;

UPDATE account_adherent SET balance = balance + @nb*@montant WHERE id = @account_adherent;

DELETE FROM operation_prestataire
WHERE account_id = @account_prestataire AND montant = @montant AND created_at > @date_before AND created_at < @date_after
    ORDER BY flux_id DESC LIMIT 0;

UPDATE account_prestataire SET balance = balance - @nb*@montant WHERE id = @account_prestataire;

DELETE flux FROM flux LEFT JOIN operation_adherent oa ON oa.flux_id = flux.id WHERE oa.id IS NULL AND flux.type='adherent_prestataire';