search

ExpandedVenture / ConnectionSphere

Simple Service to Start Prospecting Online
0 stars 0 forks source link

Refund is generating removal of resources #283

Open leandrosardi opened 2 years ago

leandrosardi commented 2 years ago

Problem

When we refund a client because we couldn't deliver service properly, it is causing the resources are being removed from his/her account.

Solution

Extend the Additional period after the billing cycle. 2 weeks after the billing cycle.

leandrosardi commented 2 years ago 
-- extend all the existing expirtions
update movement set expiration_time=dateadd(ww,+6,expiration_time) where expiration_time is not null and expiration_time>getdate()

-- extend all the existing expirtions
update movement set expiration_time=dateadd(ww,+6,expiration_time) where expiration_time is not null and expiration_time<getdate() and expiration_end_time is not null --time>dateadd(dd,-15,getdate()) and
leandrosardi commented 2 years ago

Detection Query

select *
    from (
    select 
        c.id, c.name, 
        (
            select
                sum(
                    CASE
                    WHEN b.mc_amount3='49.00' THEN 5
                    WHEN b.mc_amount3='126.00' THEN 15
                    WHEN b.mc_amount3='261.00' THEN 40
                    ELSE 0
                    END
                )
            from paypal_subscription b
            where b.active=1
            and b.mc_amount3 in ('49.00','126.00','261.00')
            and b.id_client=c.id
        ) as accounts_credits,
        count(distinct u.id) as accounts_assigned
    from v_flywheel_clients c with (nolock)
    left join lnuser u with (nolock) on (c.id=u.id_client_allocated and u.delete_time is null)

    group by c.id, c.name
    --order by c.name
) as v
where v.accounts_credits > v.accounts_assigned
leandrosardi commented 2 years ago

Clients with Credits Missassignation

accounts_credits    accounts_assigned   id  name
40  0   9908D2AB-575C-45EB-A2D9-1FEF84E2FDBC        ERTC Angels
40  0   7EF5636B-F533-4F75-96B3-5215F79CACF8        ERTC Rebate US
15  0   B258015B-DF01-4E9B-B4CE-5E0459851276        Engage Marketing & Media Inc.
5   0   BE4C2A1F-E14D-467C-AF31-6DD46E107FD9    Janquest
15  0   00D2796B-F906-4375-907E-CF1B9965C605        Employee Retention Tax Relief
15  0   AC385C06-1066-4259-9EC8-FAE1AC83CB7E        Burk Solutions LLC
40  39  DB999815-AB7B-4325-9F9D-70438522A5BB        Lets Build Your Business
40  0   4BD1A949-BD68-4040-BB42-94A8A4729C07    ERTC Funding Help
15  14  606C3EEA-D585-407D-BDA3-A210A0910655    Tax Max Credit 
40  39  FE308074-95D6-4DC1-B154-AE0D960D071A    MGR Global Ventures, LLC
5   0   585E1828-309F-49D5-9890-B32DB15EC7E7        Small Business Recovery Pro Inc.
5   0   73917E59-4448-4923-80BC-BC418E6A0D91        Icy Leads
10  5   D6958A59-C602-48D0-B6B4-DA0F68F7EB27        Selfless Marketer
15  0   7C0EC6B2-45CB-4629-A3AF-609C38DB61AC    WANdisco
40  39  29123F26-DD6D-41A0-91F0-9A75B056F172        MDDigital
5   0   67B51822-2F9B-4A48-B5FB-069822F1A106        JVAS
40  1   977E9E99-BAD8-490A-B81E-A45946B91DF5        Retention And Recovery Network
40  39  8360ADD5-36EE-492A-A089-A97BF861035E        Paul Cordle LLC
15  14  A327FD8E-3BE3-4568-9CC8-C30852BBB502        HodiMedia LLC
leandrosardi commented 2 years ago 
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '9908D2AB-575C-45EB-A2D9-1FEF84E2FDBC', getdate(), 1, 0, 0, -40, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '7EF5636B-F533-4F75-96B3-5215F79CACF8', getdate(), 1, 0, 0, -40, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'B258015B-DF01-4E9B-B4CE-5E0459851276', getdate(), 1, 0, 0, -15, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'BE4C2A1F-E14D-467C-AF31-6DD46E107FD9', getdate(), 1, 0, 0, -5, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '00D2796B-F906-4375-907E-CF1B9965C605', getdate(), 1, 0, 0, -15, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'AC385C06-1066-4259-9EC8-FAE1AC83CB7E', getdate(), 1, 0, 0, -15, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'DB999815-AB7B-4325-9F9D-70438522A5BB', getdate(), 1, 0, 0, -1, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '4BD1A949-BD68-4040-BB42-94A8A4729C07', getdate(), 1, 0, 0, -40, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '606C3EEA-D585-407D-BDA3-A210A0910655', getdate(), 1, 0, 0, -1, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'FE308074-95D6-4DC1-B154-AE0D960D071A', getdate(), 1, 0, 0, -1, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '585E1828-309F-49D5-9890-B32DB15EC7E7', getdate(), 1, 0, 0, -5, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '73917E59-4448-4923-80BC-BC418E6A0D91', getdate(), 1, 0, 0, -5, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'D6958A59-C602-48D0-B6B4-DA0F68F7EB27', getdate(), 1, 0, 0, -5, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '7C0EC6B2-45CB-4629-A3AF-609C38DB61AC', getdate(), 1, 0, 0, -15, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '29123F26-DD6D-41A0-91F0-9A75B056F172', getdate(), 1, 0, 0, -1, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '67B51822-2F9B-4A48-B5FB-069822F1A106', getdate(), 1, 0, 0, -5, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '977E9E99-BAD8-490A-B81E-A45946B91DF5', getdate(), 1, 0, 0, -39, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '8360ADD5-36EE-492A-A089-A97BF861035E', getdate(), 1, 0, 0, -1, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'A327FD8E-3BE3-4568-9CC8-C30852BBB502', getdate(), 1, 0, 0, -1, 0, 'acc', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '9908D2AB-575C-45EB-A2D9-1FEF84E2FDBC', getdate(), 1, 0, 0, -8800, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '7EF5636B-F533-4F75-96B3-5215F79CACF8', getdate(), 1, 0, 0, -8800, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'B258015B-DF01-4E9B-B4CE-5E0459851276', getdate(), 1, 0, 0, -3300, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'BE4C2A1F-E14D-467C-AF31-6DD46E107FD9', getdate(), 1, 0, 0, -1100, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '00D2796B-F906-4375-907E-CF1B9965C605', getdate(), 1, 0, 0, -3300, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'AC385C06-1066-4259-9EC8-FAE1AC83CB7E', getdate(), 1, 0, 0, -3300, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'DB999815-AB7B-4325-9F9D-70438522A5BB', getdate(), 1, 0, 0, -8800, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '4BD1A949-BD68-4040-BB42-94A8A4729C07', getdate(), 1, 0, 0, -8800, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '606C3EEA-D585-407D-BDA3-A210A0910655', getdate(), 1, 0, 0, -3300, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'FE308074-95D6-4DC1-B154-AE0D960D071A', getdate(), 1, 0, 0, -8800, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '585E1828-309F-49D5-9890-B32DB15EC7E7', getdate(), 1, 0, 0, -1100, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '73917E59-4448-4923-80BC-BC418E6A0D91', getdate(), 1, 0, 0, -1100, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'D6958A59-C602-48D0-B6B4-DA0F68F7EB27', getdate(), 1, 0, 0, -2200, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '7C0EC6B2-45CB-4629-A3AF-609C38DB61AC', getdate(), 1, 0, 0, -3300, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '29123F26-DD6D-41A0-91F0-9A75B056F172', getdate(), 1, 0, 0, -8800, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '67B51822-2F9B-4A48-B5FB-069822F1A106', getdate(), 1, 0, 0, -1100, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '977E9E99-BAD8-490A-B81E-A45946B91DF5', getdate(), 1, 0, 0, -8800, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), '8360ADD5-36EE-492A-A089-A97BF861035E', getdate(), 1, 0, 0, -8800, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);
insert into movement (id, id_client, create_time, [type], paypal1_amount, amount, credits, profits_amount, product_code, expiration_time, [description], bonus_amount) values (newid(), 'A327FD8E-3BE3-4568-9CC8-C30852BBB502', getdate(), 1, 0, 0, -3300, 0, 'drx', dateadd(ww,+6,getdate()), 'adding bonus credits because old credits were removed by glitch #283',0);