FrontAccounting forum — How Can I Merge Customers?

Re: How Can I Merge Customers?

null@example.com (bobloblian) — Fri, 05 Jul 2019 19:15:59 +0000

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

Re: How Can I Merge Customers?

null@example.com (apmuthu) — Fri, 29 Sep 2017 06:46:21 +0000

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.

Re: How Can I Merge Customers?

null@example.com (boxygen) — Fri, 29 Sep 2017 03:17:28 +0000

@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

Re: How Can I Merge Customers?

null@example.com (apmuthu) — Thu, 28 Sep 2017 13:20:40 +0000

@boxygen: nice catch.

Committed fix in my repo.

Re: How Can I Merge Customers?

null@example.com (boxygen) — Thu, 28 Sep 2017 05:47:10 +0000

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)

Re: How Can I Merge Customers?

null@example.com (boxygen) — Thu, 28 Sep 2017 05:38:36 +0000

Can we have something similar to this like Merge GL Accounts?

Re: How Can I Merge Customers?

null@example.com (apmuthu) — Thu, 21 Sep 2017 05:01:44 +0000

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?

Re: How Can I Merge Customers?

null@example.com (apmuthu) — Thu, 21 Sep 2017 02:18:27 +0000

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?

null@example.com (itronics) — Wed, 20 Sep 2017 17:54:48 +0000

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?

null@example.com (kvvaradha) — Sun, 17 Sep 2017 07:50:28 +0000

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?

null@example.com (apmuthu) — Sun, 17 Sep 2017 03:44:24 +0000

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.

Re: How Can I Merge Customers?

null@example.com (davidkumi) — Wed, 13 Sep 2017 11:33:08 +0000

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?

null@example.com (apmuthu) — Tue, 12 Sep 2017 16:21:15 +0000

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?

null@example.com (cambell) — Tue, 12 Sep 2017 15:24:57 +0000

@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.

Re: How Can I Merge Customers?

null@example.com (davidkumi) — Tue, 12 Sep 2017 14:29:02 +0000

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:

You chose the same customer in both selections. Cannot merge a customer with himself/herself. 

Reload


");
        $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 "";
echo "

NOTE: THIS ACTION IS IRREVOCABLE

";


echo "    Merge & Delete this customer                     Keep this customer    
           ⇒          ";

echo "


      ";
//    
echo "



";

end_page();

Merge & Delete this customer		Keep this customer
	⇒

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