Post by allenshea » Mon May 26, 2014 12:52 pm

How can I have countries of customer in admin page?

Appreciated for your comments. Thanks!

Allen

I know nothing about PHP and SQL, but I still try my best to understand it.


Active Member

Posts

Joined
Mon Dec 14, 2009 10:01 pm

Post by uNeverNo » Tue May 27, 2014 2:20 am

Where do you want to show them and how?

Post in the forum yet no one's willing to help? Contact me for free opencart coding or configuration assistance. Reach me at uneverno1986#gmail.com(replace # with @). But no free extension development request. Sorry about that :( It could take a few days to make a complicated mod.

If you feel I have helped, a donation is optional and appreciated. Image


User avatar
Active Member

Posts

Joined
Sun Sep 23, 2012 10:49 am

Post by allenshea » Tue May 27, 2014 5:40 am

uNeverNo wrote:Where do you want to show them and how?
Admin->Sales->Customer

I want list the customer countries in Customer List. Can you kindly help? Thanks!

I know nothing about PHP and SQL, but I still try my best to understand it.


Active Member

Posts

Joined
Mon Dec 14, 2009 10:01 pm

Post by uNeverNo » Tue May 27, 2014 7:16 pm

Try this:
open admin\controller\sale\customer.php

add

Code: Select all

$this->data['column_country'] = $this->language->get('column_country');
after

Code: Select all

$this->data['column_action'] = $this->language->get('column_action');	

add

Code: Select all

'country'             => $result['country'],
after

Code: Select all

'ip'             => $result['ip'],
------------------------------------------------------------------------------------------------------------------------------------------

open admin\language\english\sale\customer.php

add

Code: Select all

$_['column_country']         = 'Country';
after

Code: Select all

$_['column_action']         = 'Action';
------------------------------------------------------------------------------------------------------------------------------------------

open admin\view\template\sale\customer_list.tpl

add

Code: Select all

<td class="left"><?php echo $column_country; ?></td>
before

Code: Select all

<td class="left"><?php echo $column_login; ?></td>


add

Code: Select all

<td class="left"><?php echo $customer['country']; ?></td>
after

Code: Select all

<td class="left"><?php echo $customer['date_added']; ?></td>
------------------------------------------------------------------------------------------------------------------
Open admin\model\sale\customer.php

replace

Code: Select all

WHERE cgd.language_id
with

Code: Select all

LEFT JOIN " . DB_PREFIX . "country co ON (co.country_id = (SELECT country_id FROM " . DB_PREFIX . "address WHERE address_id = c.address_id)) WHERE cgd.language_id
replace

Code: Select all

cgd.name AS customer_group
with

Code: Select all

cgd.name AS customer_group, co.name AS country

Post in the forum yet no one's willing to help? Contact me for free opencart coding or configuration assistance. Reach me at uneverno1986#gmail.com(replace # with @). But no free extension development request. Sorry about that :( It could take a few days to make a complicated mod.

If you feel I have helped, a donation is optional and appreciated. Image


User avatar
Active Member

Posts

Joined
Sun Sep 23, 2012 10:49 am

Post by allenshea » Tue May 27, 2014 8:20 pm

Dear uNeverNo,

Thanks for your kindly help, but anyhow, I got errors when open customer list.
Notice: Error: Column 'name' in order clause is ambiguous
Error No: 1052
SELECT *, CONCAT(c.firstname, ' ', c.lastname) AS name, cgd.name AS customer_group, co.name AS country FROM customer c LEFT JOIN customer_group_description cgd ON (c.customer_group_id = cgd.customer_group_id) LEFT JOIN country co ON (co.country_id = (SELECT country_id FROM address WHERE address_id = c.address_id)) WHERE cgd.language_id = '1' ORDER BY name ASC LIMIT 0,20 in htdocs\online\system\database\mysql.php on line 49
Count you kindly check where I have done wrong? Thanks again.

Allen
uNeverNo wrote:Try this:
open admin\controller\sale\customer.php

add

Code: Select all

$this->data['column_country'] = $this->language->get('column_country');
after

Code: Select all

$this->data['column_action'] = $this->language->get('column_action');	

add

Code: Select all

'country'             => $result['country'],
after

Code: Select all

'ip'             => $result['ip'],
------------------------------------------------------------------------------------------------------------------------------------------

open admin\language\english\sale\customer.php

add

Code: Select all

$_['column_country']         = 'Country';
after

Code: Select all

$_['column_action']         = 'Action';
------------------------------------------------------------------------------------------------------------------------------------------

open admin\view\template\sale\customer_list.tpl

add

Code: Select all

<td class="left"><?php echo $column_country; ?></td>
before

Code: Select all

<td class="left"><?php echo $column_login; ?></td>


add

Code: Select all

<td class="left"><?php echo $customer['country']; ?></td>
after

Code: Select all

<td class="left"><?php echo $customer['date_added']; ?></td>
------------------------------------------------------------------------------------------------------------------
Open admin\model\sale\customer.php

replace

Code: Select all

WHERE cgd.language_id
with

Code: Select all

LEFT JOIN " . DB_PREFIX . "country co ON (co.country_id = (SELECT country_id FROM " . DB_PREFIX . "address WHERE address_id = c.address_id)) WHERE cgd.language_id
replace

Code: Select all

cgd.name AS customer_group
with

Code: Select all

cgd.name AS customer_group, co.name AS country

I know nothing about PHP and SQL, but I still try my best to understand it.


Active Member

Posts

Joined
Mon Dec 14, 2009 10:01 pm

Post by OnNets » Fri Jan 16, 2015 5:06 pm

Hi allenshea,

Do you have any update how to work on this?

Regards,
Stenno

New member

Posts

Joined
Wed Jan 06, 2010 5:53 pm

Post by ashino » Mon May 18, 2015 4:48 pm

Hello Guys,

I found the solution if you want it:

1- Do exactly what it given to you by uNeverNo
2- Here is the extra thing you need:
Notice: Error: Column 'name' in order clause is ambiguous
Error No: 1052
SELECT *, CONCAT(c.firstname, ' ', c.lastname) AS name, cgd.name AS customer_group, co.name AS country FROM customer c LEFT JOIN customer_group_description cgd ON (c.customer_group_id = cgd.customer_group_id) LEFT JOIN country co ON (co.country_id = (SELECT country_id FROM address WHERE address_id = c.address_id)) WHERE cgd.language_id = '1' ORDER BY name ASC LIMIT 0,20 in htdocs\online\system\database\mysql.php on line 49
The error means that you are trying to order by a column name that is used in more than one table, in this case the column name is "Name".

************************************************************

To solve this in "Open admin\model\sale\customer.php":

add:

Code: Select all

$sort_data = array(
			'co.name',
			'c.email',
			'customer_group',
			'c.status',
			'c.approved',
			'c.ip',
			'c.date_added'
		);	
instead of:

Code: Select all

$sort_data = array(
			'name',
			'c.email',
			'customer_group',
			'c.status',
			'c.approved',
			'c.ip',
			'c.date_added'
		);	
and add:

Code: Select all

$sql .= " ORDER BY co.name";
instead of:

Code: Select all

$sql .= " ORDER BY name";
********************************************************************

all the errors in the admin page will be removed and the country of each user will show up.

I hope this works for you O0

Newbie

Posts

Joined
Mon May 18, 2015 4:38 pm

Post by OnNets » Sat Jun 06, 2015 1:00 pm

Thanks ashino. This help!

New member

Posts

Joined
Wed Jan 06, 2010 5:53 pm

Post by ndim_pro » Mon Oct 22, 2018 6:32 pm

Very helpful topic.

I am trying to do the same but for Orders list in admin.

I followed all the steps but I missed it in the sql query thing. Can you tell me what query should I replace or add. I managed to create/show a new column named country but it is empty due to the sql query I don't know.

Below is my vqmod so far for OC 2.3.0.2
Could you give it a look and see what is missing or what is wrong?

Thank you.

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<modification>
   <id>Show Country in Orders List by ndimos</id>
   <version>1.0</version>
   <vqmver>2.X</vqmver>
   <author>ndimos</author>
    <file name="admin/controller/sale/order.php">
      <operation>
       <search><![CDATA[$data['column_action'] = $this->language->get('column_action');]]></search>
       <add position="after"><![CDATA[$data['column_country'] = $this->language->get('column_country');]]></add>
      </operation> 
      <operation>
       <search><![CDATA['customer'      => $result['customer'],]]></search>
       <add position="after"><![CDATA['country'             => $result['country'],]]></add>
      </operation>    
    </file>
    <!--<file name="admin/language/el-gr/sale/order.php">
      <operation>
       <search><![CDATA[$_['column_weight']              = 'Βάρος προϊόντος';]]></search>
       <add position="after"><![CDATA[$_['column_country']         = 'Χώρα';]]></add>
      </operation>     
    </file>-->
    <file name="admin/language/en-gb/sale/order.php">
      <operation>
       <search><![CDATA[$_['column_weight']              = 'Product Weight';]]></search>
       <add position="after"><![CDATA[$_['column_country']         = 'Country';]]></add>
      </operation>     
    </file>
    <file name="admin/model/sale/order.php">
      <operation>
       <search><![CDATA['o.order_id',]]></search>
       <add position="after"><![CDATA['o.country',]]></add>
      </operation>
      <operation>
       <search><![CDATA[$sql .= " ORDER BY o.order_id";]]></search>
       <add position="replace"><![CDATA[$sql .= " ORDER BY o.country";]]></add>
      </operation> 
    </file>
    <file name="admin/view/template/sale/order_list.tpl">
      <operation>
       <search><![CDATA[<a href="<?php echo $sort_order; ?>"><?php echo $column_order_id; ?></a]]></search>
       <add position="after" offset="1"><![CDATA[<td class="text-right"><?php if ($sort == 'o.country') { ?>
                    <a href="<?php echo $sort_order; ?>" class="<?php echo strtolower($order); ?>"><?php echo $column_country; ?></a>
                    <?php } else { ?>
                    <a href="<?php echo $sort_order; ?>"><?php echo $column_country; ?></a>
                    <?php } ?></td>]]></add>
      </operation> 
      <operation>
       <search><![CDATA[<td class="text-right"><?php echo $order['order_id']; ?></td>]]></search>
       <add position="after"><![CDATA[<td class="text-right"><?php echo $order['shipping_country']; ?></td>]]></add>
      </operation>
    </file>
</modification>

User avatar
New member

Posts

Joined
Fri Nov 04, 2016 4:49 pm
Who is online

Users browsing this forum: Bing [Bot] and 43 guests