Bulk add discount group to all clients

Hi everyone,

I would like to add a discount group (already existing in my OpenVPMS) to all clients in the database.

Is there any way to accomplish this?

 

I was thinking a MySql Insert statement might be the only way but then I'm lost as to the update of id's, linkId's etc etc.

 

As a bit of background: I'm running a competition on Advocate sales and I need discount to be applied to any client buying any Advocate product up to a certain date.

 

Thanks,

Anton

 

 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Bulk add discount group to all clients

The SQL below can be used to add relationships between active customers and a discount group in OpenVPMS 2.x.
I recommend testing on a backup before deploying to a production environment.

Note that it won't add a discount relationship if one already exists, even if it has expired.

 

The following SQL shows which customers will be updated. Replace XXDiscount with the actual discount group name.

select customer.entity_id, customer.name, customer.description
from entities customer
join entities discount
    on discount.arch_short_name = 'entity.discountGroupType'
        and discount.name = 'XXDiscount'
        and discount.active = 1
where customer.arch_short_name = 'party.customerperson' and customer.active = 1
    and not exists (select *
                    from entity_links ldiscount
                    where ldiscount.source_id = customer.entity_id
                        and ldiscount.target_id = discount.entity_id
                        and ldiscount.arch_short_name = 'entityLink.customerDiscount');

The following SQL performs the actual update.

Replace the text in bold with the real values. E.g, it assumes the discount starts on 20/09 and ends on the 27/9.

 

insert into entity_links (version, linkId, arch_short_name, arch_version, name, description, active_start_time, active_end_time, sequence, source_id, target_id)
select 1,
    UUID(),
    'entityLink.customerDiscount',
    '1.0',
    'Customer Discount',
    'Customer Discount (20/09/2020 - 27/09/2020)',  # replace with actual date range
    '2020-09-20',                                   # replace with actual discount start date
    '2020-09-27',                                   # replace with actual discount end date
    0,
    customer.entity_id,
    discount.entity_id
from entities customer
join entities discount
    on discount.arch_short_name = 'entity.discountGroupType'
        and discount.name = 'XXDiscount'
        and discount.active = 1
where customer.arch_short_name = 'party.customerperson' and customer.active = 1
    and not exists (select *
                    from entity_links ldiscount
                    where ldiscount.source_id = customer.entity_id
                        and ldiscount.target_id = discount.entity_id
                        and ldiscount.arch_short_name = 'entityLink.customerDiscount');
                   

 

 

Re: Bulk add discount group to all clients

This is awesome, thank you Tim!

 

Syndicate content