Topic: How Can I Merge Customers?

Hi all,

I have several customers duplicated. For example, XYZ Ltd, XYZ Company Ltd and XYZ all have transactions (payments, invoices, deliveries, orders, etc) but this is just the same customer. How can I merge them so that all their transactions are under one name instead of several?

Advance thanks.

Re: How Can I Merge Customers?

Study the schema / ERD for your version of FA and then replace all discardable customer ids with the preferred customer id.
The FA 2.3.x schema is available in the Wiki and FA v2.4.x isn't too different.

Re: How Can I Merge Customers?

I looked at how it's done in Quickbooks on youtube and I didn't like it - copying name of customer you want and pasting over the one you want to merge and delete. So I did it differently. I created the file sales/merge.php and the code as follows:

<?php

$path_to_root = "..";
$page_security = 'SA_CUSTOMER';

include_once($path_to_root . "/sales/includes/cart_class.inc");
include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/sales/includes/sales_ui.inc");
include_once($path_to_root . "/sales/includes/ui/sales_order_ui.inc");
include_once($path_to_root . "/sales/includes/sales_db.inc");
include_once($path_to_root . "/sales/includes/db/sales_types_db.inc");
include_once($path_to_root . "/reporting/includes/reporting.inc");
include_once($path_to_root . "/includes/ui/ui_controls.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/sales/includes/db/customers_db.inc");

$js = '';

page("Merge Customers", false, false, "", $js);

        $deletecustomer = $_POST['deletecustomer'];
        $keepcustomer = $_POST['keepcustomer'];
        
        
        if ($keepcustomer  && $deletecustomer){                
        
        if ($keepcustomer == $deletecustomer) 
            die("<center>You chose the same customer in both selections. Cannot merge a customer with himself/herself. <br /><br /><a href=\"merge.php\">Reload</a><br /><br /><br />");
        $sql = "UPDATE 0_debtor_trans SET `debtor_no` = $keepcustomer, branch_code = (select branch_code from 0_cust_branch where debtor_no = $keepcustomer limit 1) WHERE `debtor_no` = $deletecustomer";
        $result = db_query($sql, "An error occured");

        $sql = "UPDATE 0_sales_orders SET `debtor_no` = $keepcustomer, delivery_address = '', contact_phone = '', deliver_to = '', branch_code = (select branch_code from 0_cust_branch where debtor_no = $keepcustomer limit 1) WHERE `debtor_no` = $deletecustomer";
        $result = db_query($sql, "An error occured");

        $sql = "UPDATE 0_cust_allocations SET `person_id` = $keepcustomer WHERE `person_id` = $deletecustomer";
        $result = db_query($sql, "An error occured");

        $sql = "DELETE FROM 0_debtors_master where debtor_no = $deletecustomer"; 
        $result = db_query($sql, "An error occured");

        $sql = "DELETE FROM 0_cust_branch where debtor_no = $deletecustomer"; 
        $result = db_query($sql, "An error occured");

        $sql = "DELETE FROM 0_crm_contacts where type = 'customer' AND entity_id = $deletecustomer"; 
        $result = db_query($sql, "An error occured");

        $sql = "DELETE FROM 0_crm_contacts where type = 'cust_branch' AND entity_id = (select branch_code from 0_cust_branch where debtor_no = $deletecustomer)"; 
        $result = db_query($sql, "An error occured");

        $sql = "DELETE FROM 0_crm_persons where type = 'customer' AND id = (select person_id from 0_crm_contacts where type = 'customer' AND entity_id = $deletecustomer limit 1)"; 
        $result = db_query($sql, "An error occured");
        
    header("Location: ..");
        }

    $sql = "SELECT  debtor_no,  name FROM  `0_debtors_master`  ORDER BY 'name' ASC ";
    $result = db_query($sql, "An error occured");
echo "<form method=\"post\">";
echo "<center><br /><br /><b class=\"headingtext\">NOTE: THIS ACTION IS IRREVOCABLE</b><br /><br /><table style= \"width:70% \">  <tr>  <td colspan= \"6 \">Merge & Delete this customer</td>  <th> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  </th>  <td colspan= \"6 \">Keep this customer</td>   </tr>  <tr>  <td colspan= \"6 \"><select id= \"deletecustomer\" name= \"deletecustomer\" class= \"combo2 \" title= \"Merge & Delete customer \" _last= \"14 \">";

    while ($myrow = db_fetch($result))
    {
        echo "<option value= \"" . $myrow['debtor_no'] . " \">" . $myrow['name'] . " </option>"; 
    }

    echo "</select></td>  <td colspan= \"2 \"><font size= \"6 \"> &nbsp; &nbsp; &rArr; &nbsp; &nbsp; &nbsp; </font> </td><td colspan= \"6 \"><select id= \"keepcustomer\" name= \"keepcustomer\" class= \"combo2 \" title= \"Merge & keep customer \" _last= \"14 \">";
    
    
    $sql = "SELECT  debtor_no,  name FROM  `0_debtors_master`  ORDER BY 'name' ASC ";
    $result = db_query($sql, "An error occured");
        while ($myrow = db_fetch($result))
    {
        //echo $myrow['debtor_no'] . " " . $myrow['name'] . "<br />";
        echo "<option value= \"" . $myrow['debtor_no'] . " \">" . $myrow['name'] . " </option>"; 
    }
    
    echo "</select></td>  </tr>";


echo "</table>";

echo "<br /><br /><br /><input type=\"submit\" value=\" Merge Customers \"> &nbsp; &nbsp; <input type=\"submit\" formaction=\"..\" value=\" Cancel \" > </center>";
//&nbsp; &nbsp; <input type=\"submit\" formaction=\"..\" value=\" Cancel \" >
echo "</form><br /><br /><br />";

end_page();

As much as I can see, it works fine. Any Comments?

Re: How Can I Merge Customers?

@davidkumi Well done. :-)  Thanks for sharing.  Here's some comments that would make it even better:

- Instead of "if $keep && $delete" you could do "if !$keep error if !$delete error"  That keeps the error handling consistent and near the top of the module effectively saying 'if the input is bad, error out early before proceeding'.

- There's no checking on the input data, so you can easily do sql injection here.  But, for a one of use, not to worry about that.

- $result isn't used in the UPDATE or DELETE queries, so you don't need it.  Just call db_query(...).

Its not too far off a plugin.  Just need to add the menu location and a bit of plugin boiler plate and it would be a plugin.

Cambell https://github.com/cambell-prince

Re: How Can I Merge Customers?

To avoid sql injection where we know the 2 POST variables are integers, just use:

        $deletecustomer = $_POST['deletecustomer']+0;
        $keepcustomer = $_POST['keepcustomer']+0;

Re: How Can I Merge Customers?

Thanks @apmuthu and @cambell. I'll update and repost.

I notice that Journal Entries with 1200 Account Receivables, the customer must be selected. When this customer is merged into another, the transaction is correctly transferred but clicking the ref # of that journal Entry in the new customer, the customer name doesn't display. Any ideas?

Re: How Can I Merge Customers?

The sales/merge_customers.php file has been updated with TB_PREF and sql_injection protection.

@joe: can include in core with appropriate menu entry and role permission.

The same can be done for suppliers and items as well.

Post's attachments

MergeCustomers.zip 18.56 kb, 3 downloads since 2017-09-17 

You don't have the permssions to download the attachments of this post.

Re: How Can I Merge Customers?

This is good feature if it comes within core , as like clone, we need an option to merge customers.

Re: How Can I Merge Customers?

The file cannot be included into sources in current form for a couple of reasons:

1. Some fields in database are not updated leaving it in inconsistent state:
recurrent_invoices.debtor_no
sales_orders.debtor_no, sales_orders.branch_code
gl_trans.person_id - for some transaction where person_type_id is set to PT_CUSTOMER

2. For security reasons additional access to this feature should be restricted to users with special rights (company admin), so additional access level have to be defined in access_levels.inc and included in sql files. If the file is to be distributed as extension module, this can be defined in module.

3. Due to potentially disastrous effects, the warning displayed on the page is not enough. Additional confirmation step should be used before customer merging is done. this mechanism is used e.g. in Setup|Void Transaction page, and can be reused here.

4. The code should be at least roughly consistent with those used in the rest of application (e.g. using defined output helpers instead of raw html echo, or sanitizing sql with db_escape() function). These requirements have kept the code maintainable so far, and we want to continue this in near future.

Beside that, the feature looks interesting indeed, especially for novice users or in multi-salesman setup, where multiply customer records are not so rare.

Janusz

Re: How Can I Merge Customers?

To the extent possible, I have cleaned up the code and attached it in the 7th post in this thread.

@itronics: please provide the additions to cover the fields stated.

As far as the raw echo statements are concerned, it was only a means to illustrate the actual intent by following the KISS principle.

If there are any security considerations for access levels, then it can be exclusively allotted to the Company Administrator for keeping it in the core.

Re: How Can I Merge Customers?

Revision 2 of the sales/merge_customers.php is attached herewith and committed to my FA24mods repo.

Addresses the missing fields listed in 1 by @itronics except the field gl_trans.person_id - for some transaction where person_type_id is set to PT_CUSTOMER (2).

Security escalated to Company Admin.

All echo statements replaced by wrappers.

On Merging Customers, the page does not re-compute the select boxes though the merge has been successful - purge cache?

The additional confirmation message is just displayed since there is no separate select window as in Void Transactions page. Someone can enhance it if necessary.

Anyone game to make it into a module?

Post's attachments

MergeCustomers2.zip 11.71 kb, 1 downloads since 2017-09-21 

You don't have the permssions to download the attachments of this post.