Jump to content
PauloV

[Merge Clients] - How It Should Work ;)

Recommended Posts

Hello,

 

For some time we are thinking in a way to add a function to finally Merge Clients work and we faced some problems like for exemple "Modules" and "Plugins" that each one fetch in is own way the Client ID and we cannot track each way they work ;)

 

So, maybe add a special function to Modules/Plugins to do this job, and return the current or new/merged client ID and this way it will always tell the Correct ID, and we dont need to fetch Client ID from DataBase like we do right now.

 

First, add a new database Client Field called for exemple "merged_ids" that will store as an array all Client ID's that are merged into, also add a new "Client Status" called "Merged" and if this status is active, it tells Blesta this client is no longer active and its merged in a new Client ID.

 

So the new function will always first check if the Status is not "Merged" and second will check it there is any ID that we are lokking for are not in "merged_ids" and then it will return the correct user ID ti that Module/Plugin to associate the DATA

 

Any touths how it will work?

 

I think its better to start thinking now, because later it will be very dificult to us developpers to correct all Modules/Plugins that we are working on, and it will break if "Merge Client" is on the run ;)

 

Thanks,

PV

Share this post


Link to post
Share on other sites

in v2.5 that function was exist and it was very usefful for us , specially when we found a duplicate client account , or if the client want to merge two account they have .

in v3.3 that function not exist , and is planned for future release, and i think is better to talk about it now how it should work .

@PauloV , maybe when mergin clients, the client row should be removed and let the user row or maybe the reverse , in V2.5 the merge clients delete the merged account from the database and add contacts, services, invoices, tickets and transactions from old client to the new client , so no trace for the old user_id .

i think like this scenario is workable .

i vote the paulov idea to quick add this function , because the more plugins/modules created before it , can cause some trouble after it created .

i beleive this function is not present in the core now because for some good reasons, or it just for lock of time ?

Share this post


Link to post
Share on other sites

what is the best approuch to achieve this task by a custom plugin?

Core Table

- Update Services to include the destination client_id
- Update Invoices to include the destination client_id
- Update Transactions to include the destination client_id
- Update Invoices to include the destination client_id
- Update Contacts to include the destination client_id
- Update Packages Set to include the destination client_id
- Update Emails Log to include the destination client_id
- Update Payment Accounts to include the destination client_id

Plugins Tables if the plugin is installed

- Update Tickets/Replies to include the destination client_id
- Update Client Documents to include the destination client_id
- Update Downloads Manager to include the destination client_id

After changing/migrate all to the destination client account, we will make the old client account as inactive.

 

is this all for the blesta system, or there are some other think we should think about ?

 

Share this post


Link to post
Share on other sites
On 4/25/2018 at 9:53 AM, Chris van der Westhuizen said:

Hi Blesta Addons

Do you have a solution already, or should I write something to accomplish that according to your steps? 

Because we also sits with a lot of duplicate clients after importing from some whmcs systems which is messing up our billing and invoicing. 

We are still waiting the staff answer if what we have put are enough or exist something else we have not included ?

it great if you can do something and always i'm ready to help you finish the work .

Share this post


Link to post
Share on other sites

Hi All, I hope the following can help you.

What I do in the meantime is moving the services etc over to the new client and close the old client.  In my case we don't need to move everything.  The services are important for us to be on the new client.  Thus I used the code below, although it is not a complete script moving everything you might want to move, it did it for us, and can maybe help others.  I commented the update queries with -- for me not to run it by accident 🙂 .  find below:

--blesta move services from one client to another. (the values entered was for my testing)
/*Step 01*/
use billing;
set @var_id_value_from=1195; /*from client code, not id*/
set @var_id_value_to=1205; /*to client code, not id*/

set @var_id_from=0;
SELECT `id` into @var_id_from FROM `clients` where `id_value` = @var_id_value_from limit 1;
set @var_id_to=0;
SELECT `id` into @var_id_to FROM `clients` where `id_value` = @var_id_value_to limit 1;

select @var_id_value_from, @var_id_value_to, @var_id_from, @var_id_to;

SELECT * FROM `clients` where `id` = @var_id_from;
SELECT * FROM `clients` where `id` = @var_id_to;
SELECT * FROM `services` where `client_id` = @var_id_from;
SELECT * FROM `services` where `client_id` = @var_id_to;
--update services set `client_id` = @var_id_to where client_id = @var_id_from;

/*skip this only for testing*/
select * from service_fields where service_id in(324); /* no action needed */
select * from service_changes where service_id in(324); /* no action needed */
select * from service_options where service_id in(324); /* no action needed */
select * from log_services where service_id in(324); /* no action needed */
select * from package_pricing where pricing_id in(33); /* no action needed */
select * from packages where id in(26); /* no action needed */
select * from pricings where id in(33); /* no action needed */

/*Step 02*/
select * from client_packages where client_id = @var_id_from; /*change client_id from to */
--update client_packages set `client_id` = @var_id_to where client_id = @var_id_from;

/* assuming that the clients's currency, and tax status is the same, and you want to move all services from one client to another
then you can do the invoices and transactions as well */

/*Step 03*/
select * from support_tickets where client_id = @var_id_from; /* service_id in(324); *//*code = 9462313;*/  /* change client_id from old client id to new client id (not client_id_value) */
/* the support_tickets are linked to support_replies which are linked to contacts that are linked to the current client.
if you want to move the support tickets related to the service_id, I will suggest moving the contact first to the new client.
but the contact is linked to a lot of tables.  For my purpose I deside to leave the support_tickets at the old client and just make the support_tickets.service_id = null
*/
--update support_tickets set client_id = @var_id_to where client_id = @var_id_from; /* should I or not, in regards to the comment above? */
select * from support_replies where ticket_id in(select ticket_id from support_tickets where service_id in(324)); /* no action needed if not moving the tickets with services to new client*/

/*Step 04*/
select * from service_invoices where service_id in(324); /* no action needed */
select * from invoices where client_id = @var_id_from; /* id in (37106,38781);*/ /* change client_id from old client id to new client id (not client_id_value) */
-- update invoices set client_id = @var_id_to where client_id = @var_id_from;
select * from invoice_lines where invoice_id in(37106,38781); /* no action needed */

select * from invoices_recur where `client_id` = @var_id_from; /* change cleint_id fom old client to new client */
--update invoices_recur set client_id = @var_id_to where client_id = @var_id_from;

/*Step 05*/
select * from transaction_applied where transaction_id in(select id from transactions where client_id = @var_id_from); /* no action needed */
select * from transactions where client_id = @var_id_from; /* change old client_id to new client_id. make transactions.account_id = null if account not account will not also be moved to new client, like in my case */
--update transactions set transactions.account_id = null, client_id = @var_id_to where client_id = @var_id_from;

Then I closed the old duplicated client in Blesta.


have fun! 🙂

Share this post


Link to post
Share on other sites
On 4/12/2018 at 4:37 AM, Blesta Addons said:

Update Contacts to include the destination client_id

Not sure you could do that if you wanted to keep the client and simply make them inactive.  A client without a contact seems problematic.

On 4/26/2018 at 8:26 AM, Blesta Addons said:

We are still waiting the staff answer if what we have put are enough or exist something else we have not included ?

If you are looking for relations on the client_id then there are some others.

  • module_client_meta - Blesta Core
  • client_notes - Blesta Core
  • client_packages - Blesta Core
  • client_settings - Blesta Core
  • client_values - Blesta Core
  • reseller_pricing_clients - Reseller Pricing
  • support_tickets - Support Manager

You should also be concerned with tables related on the contact_id

  • accounts_cc, accounts_ach - Blesta Core
  • contact_numbers - Blesta Core
  • contact_permissions - Blesta Core
  • log_contacts - Blesta Core
  • support_replies - Support Manager
  • download_logs - Download Manager
  • mass_mailer_tasks - Mass Mailer

I think that covers everything you missed.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×