Unmodify orders changed by the subscriptions cron job

[marcogbarcellos]

Due to the issue #1337 , we need to check all contributors that backed collectives last month using virtual cards and subscribed to those collectives. If there's any(there's at least one @znarf we know), we will need to find a way to update all orders replacing the current payment method(which is likely the source payment method of those virtual cards) to use the virtual card payment method instead.

Technical Approach We can track those cases looking for all Transactions with a PaymentMethodId that has a type virtualcard, then through the same Transactions we will find the orders through the OrderId field and check whether the Transaction.PaymentMethodId matches the Order.PaymentMethodId.

[marcogbarcellos]

This query found that 38 transactions(76 in total, but with DEBIT and CREDIT) made using Virtual cards wich the transaction.PaymentMethodId is not the same as transaction.Order.PaymentMethodId when it should be:

select * from "Transactions" t 
left join "PaymentMethods" p on t."PaymentMethodId"=p.id 
left join "Orders" o on t."OrderId"=o.id 
where p.type='virtualcard' and o."PaymentMethodId"!=p.id;

Running this query locally(copied prod database)apparently fix the problem:

with tp as (
    select t."OrderId", p.id as pmid, p.type as pmtype from "Transactions" t 
left join "PaymentMethods" p on t."PaymentMethodId"=p.id
)
update "Orders" o set "PaymentMethodId"=pmid
from tp
where o.id = tp."OrderId" and tp.pmtype='virtualcard' and o."PaymentMethodId"!=tp.pmid;

[marcogbarcellos]

Ran this in staging and it's promising, it apparently fixed @znarf 's problem:

Will do some more tests tomorrow(check case by case, not all, but at least a number i feel comfortable to run it in prod) before running in production.

[marcogbarcellos]

So we will basically do the following steps:

1. find the transactions and orders that use virtualcard payment methods and don't match the same payment method:

   select string_agg(t.id::character varying, ',' order by t.id)
   from "Transactions" t 
   left join "PaymentMethods" p on t."PaymentMethodId"=p.id 
   left join "Orders" o on t."OrderId"=o.id 
   where p.type='virtualcard' and o."PaymentMethodId"!=p.id;

2. save the list of transaction ids:

   116189,116190,116201,116202,116203,116204,116993,116994,117045,117046,117047,117048,117049,117050,117145,117146,117229,117230,117231,117232,117341,117342,117423,117424,117919,117920,117921,117922,117929,117930,117957,117958,117959,117960,117961,117962,117992,117995,117996,117999,118023,118024,118029,118032,118305,118306,118335,118336,118357,118358,118369,118370,118374,118376,118569,118572,119123,119124,119407,119409,119431,119434,119436,119437,119439,119440,119450,119452,119524,119526,121255,121256,121939,121940,121941,121942

3. run the update query:

   with tp as (
   select t."OrderId", p.id as pmid, p.type as pmtype from "Transactions" t 
   left join "PaymentMethods" p on t."PaymentMethodId"=p.id
   )
   update "Orders" o set "PaymentMethodId"=pmid
   from tp
   where o.id = tp."OrderId" and tp.pmtype='virtualcard' and o."PaymentMethodId"!=tp.pmid;

4. look for the transactions and orders given the list of transaction above and check whether they now match the payment method:

select *
 from "Transactions" t 
left join "PaymentMethods" p on t."PaymentMethodId"=p.id 
left join "Orders" o on t."OrderId"=o.id 
where t.id in (116189,116190,116201,116202,116203,116204,116993,116994,117045,117046,117047,117048,117049,117050,117145,117146,117229,117230,117231,117232,117341,117342,117423,117424,117919,117920,117921,117922,117929,117930,117957,117958,117959,117960,117961,117962,117992,117995,117996,117999,118023,118024,118029,118032,118305,118306,118335,118336,118357,118358,118369,118370,118374,118376,118569,118572,119123,119124,119407,119409,119431,119434,119436,119437,119439,119440,119450,119452,119524,119526,121255,121256,121939,121940,121941,121942);

Making it perhaps simpler:

-- BEFORE UPDATE
select t."PaymentMethodId", o."PaymentMethodId" -- string_agg(t.id::character varying, ',' order by t.id)
 from "Transactions" t 
left join "PaymentMethods" p on t."PaymentMethodId"=p.id 
left join "Orders" o on t."OrderId"=o.id 
where p.type='virtualcard' and o."PaymentMethodId"!=p.id order by t."PaymentMethodId" DESC;

-- UPDATING
with tp as (
    select t."OrderId", p.id as pmid, p.type as pmtype from "Transactions" t 
left join "PaymentMethods" p on t."PaymentMethodId"=p.id
)
update "Orders" o set "PaymentMethodId"=pmid
from tp
where o.id = tp."OrderId" and tp.pmtype='virtualcard' and o."PaymentMethodId"!=tp.pmid;

-- AFTER UPDATE
select t."PaymentMethodId", o."PaymentMethodId" -- *
 from "Transactions" t 
left join "PaymentMethods" p on t."PaymentMethodId"=p.id 
left join "Orders" o on t."OrderId"=o.id 
where t.id in (116189,116190,116201,116202,116203,116204,116993,116994,117045,117046,117047,117048,117049,117050,117145,117146,117229,117230,117231,117232,117341,117342,117423,117424,117919,117920,117921,117922,117929,117930,117957,117958,117959,117960,117961,117962,117992,117995,117996,117999,118023,118024,118029,118032,118305,118306,118335,118336,118357,118358,118369,118370,118374,118376,118569,118572,119123,119124,119407,119409,119431,119434,119436,119437,119439,119440,119450,119452,119524,119526,121255,121256,121939,121940,121941,121942) order by t."PaymentMethodId" DESC;

-- the first query ran here should not return any result after the update
select t."PaymentMethodId", o."PaymentMethodId" -- string_agg(t.id::character varying, ',' order by t.id)
 from "Transactions" t 
left join "PaymentMethods" p on t."PaymentMethodId"=p.id 
left join "Orders" o on t."OrderId"=o.id 
where p.type='virtualcard' and o."PaymentMethodId"!=p.id order by t."PaymentMethodId" DESC;

[marcogbarcellos]

done in production successfully, @znarf can you check if your subscriptions look right in prod? I REALLY think so :)