It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Accounts Receivable → 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.
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.
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> </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 \"> ⇒ </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 \"> <input type=\"submit\" formaction=\"..\" value=\" Cancel \" > </center>";
// <input type=\"submit\" formaction=\"..\" value=\" Cancel \" >
echo "</form><br /><br /><br />";
end_page();As much as I can see, it works fine. Any Comments?
@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.
To avoid sql injection where we know the 2 POST variables are integers, just use:
$deletecustomer = $_POST['deletecustomer']+0;
$keepcustomer = $_POST['keepcustomer']+0;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?
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.
This is good feature if it comes within core , as like clone, we need an option to 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
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.
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?
Can we have something similar to this like Merge GL Accounts?
While Merging Customers I got following error
DATABASE ERROR : An error occured
error code : 1054
error message : Unknown column 'type' in 'where clause'
sql that failed was : DELETE FROM 0_crm_persons WHERE type = 'customer' AND id = (SELECT person_id from 0_crm_contacts WHERE type = 'customer' AND entity_id = 4 LIMIT 1)
@boxygen: nice catch.
Committed fix in my repo.
@Apmuthu I have checked it. It is not giving error but Once merged it shall give some message that customers are now merged etc.
Still I would request to have GL Accounts Merge routine
There are many GL Accounts and Types and any merge will affect a different subset of tables.
The location redirection does not yet seem to work for the success status message to be displayed and the customer lists to be updated.
thanks for this, seems to have worked for me.
For the next person figuring out how to use it; unpack the zip file so that the merge_customers.php file is in your sales directory, then call it from your browser like so:
http://url.foryour.fa/sales/merge_customers.php
FrontAccounting forum → Accounts Receivable → How Can I Merge Customers?
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.