Jump to content
AlexandrZuyev

Customers IP and Geo Country info with order_confirmation email

Recommended Posts

 

I made a small addon to the checkout_process.php so when I get an email from my store I see order info + Customer's IP and Geo Country. Maybe it could be usefull for someone else...

MYSQL:

ALTER TABLE orders
  ADD customers_ip varchar(25) NOT NULL;

Edit file checkout_process.php find line 92
$payment_modules->before_process();

Add after:

// Get and set customer's Geo and IP into order's email
function ip_visitor_country()
{

    $client  = @$_SERVER['HTTP_CLIENT_IP'];
    $forward = @$_SERVER['HTTP_X_FORWARDED_FOR'];
    $remote  = $_SERVER['REMOTE_ADDR'];
    $country  = "Unknown";

    if(filter_var($client, FILTER_VALIDATE_IP))
    {
        $ip = $client;
    }
    elseif(filter_var($forward, FILTER_VALIDATE_IP))
    {
        $ip = $forward;
    }
    else
    {
        $ip = $remote;
    }
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, "http://www.geoplugin.net/json.gp?ip=".$ip);
    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
    $ip_data_in = curl_exec($ch); // string
    curl_close($ch);

    $ip_data = json_decode($ip_data_in,true);
    $ip_data = str_replace('"', '"', $ip_data); // for PHP 5.2 see stackoverflow.com/questions/3110487/

    if($ip_data && $ip_data['geoplugin_countryName'] != null) {
        $country = $ip_data['geoplugin_countryName'];
    }

    return $ip.', '.$country;
}

$customers_ip = ip_visitor_country(); // output Coutry name
-----

Find 
$sql_data_array = array('customers_id' => $customer_id,

add after
'customers_ip' => $customers_ip,
-----

Find 
// lets start with the email confirmation
   $email_order = STORE_NAME . "\n" .

Add after:
IP . ' ' . $customers_ip . "\n" . 

-----
//END

post-285481-0-41451900-1483557778_thumb.jpg

Share this post


Link to post
Share on other sites

@@AlexandrZuyev I love your idea.... Although I think I've seen it around here before.

 

I added it to the orders.php page in place of the email.

 

However, in the thought of going forward with OsCommerce - the question really becomes how to accomplish adding to the orders table without modifing checkout_process.php?

 

I have made this up into a quick and easy checkout_confirmation content module, however have no idea how you could write to the database without involving checkout_process.

 

Any ideas?

Share this post


Link to post
Share on other sites

See the IP logging addons. They don't record the country name, as far as I know, as the code by @@AlexandrZuyev does. But his code requires a third party package be installed and that is not available on all servers.

Share this post


Link to post
Share on other sites

@@AlexandrZuyev I love your idea.... Although I think I've seen it around here before.

 

I added it to the orders.php page in place of the email.

 

However, in the thought of going forward with OsCommerce - the question really becomes how to accomplish adding to the orders table without modifing checkout_process.php?

 

I have made this up into a quick and easy checkout_confirmation content module, however have no idea how you could write to the database without involving checkout_process.

 

Any ideas?

 

checkout_success module?

 

 

Example of Such: https://github.com/gburton/Responsive-osCommerce/blob/master/includes/modules/content/checkout_success/cm_cs_redirect_old_order.php#L38

 

Of course this really depends upon the buyer hitting the checkout_success page, but I think most orders do that?


This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites

@@Jack_mcs thanks Jack... yes I have seen that one... The great thing about the outside connection (which I didn't actually use - I deliver our site on cloudflare SDN which has a built in function available by using HTTP_CF_IPCOUNTRY;

	$location = $_SERVER["HTTP_CF_IPCOUNTRY"];

it returns countries_iso_code_2 which I then look up in the DB to get the full country name.... But for those (most if not everyone) that don't use cloudflare you can use the geoplugin address (not ideal of course).

 

The great thing about getting the country with the IP is it allows a quick easy check (by entry level staff) to confirm the customers "actual" location to their delivery address - without turning on Address Verification (if you have it) in your credit card processing (payment) module..... I've had huge issues losing orders with AV turned on.... every 10th order had an issue with 2nd address lines, postal code input (spaces, dash etc) wrong State/Province (Quebec as an example can go  by QC or PQ)....

 

@@burt

 

thank you for the hint....

 

Now to turn

        $check_query = tep_db_query("select 1 from " . TABLE_ORDERS . " where orders_id = '" . (int)$order_id . "' and date_purchased < date_sub(now(), interval '" . (int)MODULE_CONTENT_CHECKOUT_SUCCESS_REDIRECT_OLD_ORDER_MINUTES . "' minute)");

Into something like (not tested)

        $query = tep_db_query("insert info " . TABLE_ORDERS . " where orders_id = '" . (int)$order_id . "' and date_purchased, '$customers_ip')");

Am I in the right direction????

Share this post


Link to post
Share on other sites

Something more like (pertinent parts here only):

 

$customers_ip = tep_db_prepare_input(ip_visitor_country());

tep_db_query("update orders set customers_ip = '" . tep_db_input($customers_ip) . "' where orders_id = '" . (int)$order_id . "')");
The code posted by OP is far from ideal - none of that should be used. If it was me...I would simply be storing IP address, and then (if needed) lookup from within admin on an as-needed basis.

 

In which case:

 

$customers_ip = tep_db_prepare_input(tep_get_ip_address());

tep_db_query("update orders set customers_ip = '" . tep_db_input($customers_ip) . "' where orders_id = '" . (int)$order_id . "')");
Assumes "orders" table has a column called customers_ip !

This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites

@@burt that should be a good start... thank you very kindly.

 

It should be easy to create the required column in the orders table on install of the module...

 

 

The code posted by OP is far from ideal - none of that should be used. If it was me...I would simply be storing IP address, and then (if needed) lookup from within admin on an as-needed basis.

I can certainly understand why you  wouldn't like the outside curl.... but I do you see anything wrong with;

    $client  = @$_SERVER['HTTP_CLIENT_IP'];
    $forward = @$_SERVER['HTTP_X_FORWARDED_FOR'];
    $remote  = $_SERVER['REMOTE_ADDR'];
 
    if(filter_var($client, FILTER_VALIDATE_IP))
    {
        $ip = $client;
    }
    elseif(filter_var($forward, FILTER_VALIDATE_IP))
    {
        $ip = $forward;
    }
    else
    {
        $ip = $remote;
    }

To get the IP.... thoughts?

Share this post


Link to post
Share on other sites

Ok, now.... how to display the IP "somewhere" in orders.php without a core change?????

 

I will post the content mod here for someone to test.....

 

@@burt.... someone with some skill ( ;) ) could probably use call to google to find the customers location from the IP and compare to the delivery address to "flag" a store owner something is up.... That would be valuable ;) ;) ;) ;) ;)

Share this post


Link to post
Share on other sites

@@AlexandrZuyev

 

Here is a simplified version of your modification that would work in 2.3.4 BS or above... It removes the outside CURL call but still gives you the IP.

 

No core changes.... it adds (and removes on un-install) the required column to the orders table (there is no way that I know of to do a check (if exists) on a column... so the only way I can see adding the column is to remove the column on un-install... thereby REMOVING/DELETING the IP data in the column.

 

Displaying the IP without a core change would still be on the to do list....

 

for includes/modules/content/checkout_success/cm_customers_location.php

<?php
/*
  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

  class cm_customers_location {
    var $code;
    var $group;
    var $title;
    var $description;
    var $sort_order;
    var $enabled = false;

    function cm_customers_location() {
      $this->code = get_class($this);
      $this->group = basename(dirname(__FILE__));

      $this->title = MODULE_CONTENT_CUSTOMER_LOCATION_TITLE;
      $this->description = MODULE_CONTENT_CUSTOMER_LOCATION_DESCRIPTION;
      $this->description .= '<div class="secWarning">' . MODULE_CONTENT_BOOTSTRAP_ROW_DESCRIPTION . '</div>';

      if ( defined('MODULE_CONTENT_CUSTOMER_LOCATION_STATUS') ) {
        $this->sort_order = MODULE_CONTENT_CUSTOMER_LOCATION_SORT_ORDER;
        $this->enabled = (MODULE_CONTENT_CUSTOMER_LOCATION_STATUS == 'True');
      }
    }

    function execute() {
      global $oscTemplate;
      
    $content_width = (int)MODULE_CONTENT_CUSTOMER_LOCATION_CONTENT_WIDTH;
	
   $customers_ip = tep_db_prepare_input(tep_get_ip_address());

   tep_db_query("update orders set customers_ip = '" . tep_db_input($customers_ip) . "' where orders_id = '" . (int)$order_id . "' ");	
      
      ob_start();
      include(DIR_WS_MODULES . 'content/' . $this->group . '/templates/customer_location.php');
      $template = ob_get_clean();

      $oscTemplate->addContent($template, $this->group);
    }

    function isEnabled() {
      return $this->enabled;
    }

    function check() {
      return defined('MODULE_CONTENT_CUSTOMER_LOCATION_STATUS');
    }

    function install() {
      tep_db_query("insert into configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) values ('Enable Customers Location Module', 'MODULE_CONTENT_CUSTOMER_LOCATION_STATUS', 'True', 'Do you want to enable this module?', '6', '1', 'tep_cfg_select_option(array(\'True\', \'False\'), ', now())");
      tep_db_query("insert into configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) values ('Content Width', 'MODULE_CONTENT_CUSTOMER_LOCATION_CONTENT_WIDTH', '4', 'What width container should the content be shown in?', '6', '1', 'tep_cfg_select_option(array(\'12\', \'11\', \'10\', \'9\', \'8\', \'7\', \'6\', \'5\', \'4\', \'3\', \'2\', \'1\'), ', now())");
      tep_db_query("insert into configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) values ('Sort Order', 'MODULE_CONTENT_CUSTOMER_LOCATION_SORT_ORDER', '0', 'Sort order of display. Lowest is displayed first.', '6', '0', now())");
	  tep_db_query("ALTER TABLE orders ADD customers_ip varchar(25) NOT NULL");
    }

    function remove() {
      tep_db_query("delete from configuration where configuration_key in ('" . implode("', '", $this->keys()) . "')");
	  tep_db_query("ALTER TABLE orders DROP customers_ip");	  
    }

    function keys() {
      return array('MODULE_CONTENT_CUSTOMER_LOCATION_STATUS', 'MODULE_CONTENT_CUSTOMER_LOCATION_CONTENT_WIDTH', 'MODULE_CONTENT_CUSTOMER_LOCATION_SORT_ORDER');
    }
  }


for includes/modules/content/checkout_success/templates/customer_location.php

<div class="col-sm-<?php echo $content_width; ?> text-center">  
  <div class="panel panel-danger">
    <div class="panel-heading"> 
      <?php echo MODULE_CONTENT_CUSTOMER_LOCATION_PUBLIC_TITLE; ?>
    </div>
    <div class="panel-body panel-large">
      <?php 
      // $happy is the number of customer
      echo sprintf(MODULE_CONTENT_CUSTOMER_LOCATION_PUBLIC_TEXT, $customers_ip); 
      ?>
    </div>    
  </div>
</div>

And for a language file....

  <?php

  define('MODULE_CONTENT_CUSTOMER_LOCATION_TITLE', 'Customers Location');
  define('MODULE_CONTENT_CUSTOMER_LOCATION_DESCRIPTION', 'Shows Customers Location');
  
  define('MODULE_CONTENT_CUSTOMER_LOCATION_PUBLIC_TITLE', 'Location and IP Address');
  define('MODULE_CONTENT_CUSTOMER_LOCATION_PUBLIC_TEXT', '<p>%s</p>');

Share this post


Link to post
Share on other sites

@@greasemonkey

 

 

I already have an ip recorder module for the checkout success page, want it?


Let's make things easier for new osCommerce users http://forums.oscommerce.com/topic/402638-discussion-about-hard-coded-database-tables/?p=1718900  Getting there with osCommerce 2.4! :thumbsup:

Share this post


Link to post
Share on other sites

Displaying the IP without a core change would still be on the to do list....

Hook into the Orders page - good learning experience :D ;)

This would end up with the IP details getting its own Tab & Content on this page.

And no core changes whatsoever.

 

This idea needs shopowners to use the Tabbed Order page (this is built into up-to-date Edge IIRC).

Also anyone who fully installed the Paypal App also has this Tabbed Orders page...


This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites

@@burt I see I made a mistake in my code above... not giving a global to $order_id

    function execute() {
      global $oscTemplate;

Needs to be:

    function execute() {
      global $oscTemplate, $order_id;

Now... I'm a little confused on how to make an admin (or any) hook... I'm trying to deconstruct the paypal hook.

Questions... the file /includes/hooks/admin/orders/paypal.php (.... I will call the new file locations.php)

 

Is this the only file that would be required (and a language file?)? The paypal hook looks to point to the app... with         include(DIR_FS_CATALOG . 'includes/apps/paypal/hooks/admin/orders/action.php');......  because I'm not making a app I presume it would go something like (not tested):

  class hook_admin_orders_location {

    function listen_orderTab() {
	global $orders;
	  
          $tab_title = addslashes($this->_app->getDef('tab_title'));
          $tab_link = substr(tep_href_link('orders.php', tep_get_all_get_params()), strlen($base_url)) . '#section_location_content';

          $output = <<<EOD
<script>
$(function() {
  $('#orderTabs ul').append('<li><a href="{$tab_link}">{$tab_title}</a></li>');
});
</script>

<div id="section_location_content" style="padding: 10px;">
 <p><strong>Customers Location: </strong><?php echo $order->customer['customers_ip'];?></p>	
</div>
EOD;
      $hook = new location_hook_admin_orders_tab();

      return $hook->execute();
    }
  }
Edited by greasemonkey

Share this post


Link to post
Share on other sites

Ok.... I get (got it) it.....

<?php
/*
  $Id$

  osCommerce, Open Source E-Commerce Solutions
  http://www.oscommerce.com

  Copyright (c) 2014 osCommerce

  Released under the GNU General Public License
*/

  class hook_admin_orders_location {

    function listen_orderTab() {
	    global $order;
	  
	      $customers_ip = $order->customer['customers_ip'];
          $tab_title = Location;
          $tab_link = substr(tep_href_link('orders.php', tep_get_all_get_params()), strlen($base_url)) . '#section_location_content';

          $output = <<<EOD
<script>
$(function() {
  $('#orderTabs ul').append('<li><a href="{$tab_link}">{$tab_title}</a></li>');
});
</script>

<div id="section_location_content" style="padding: 10px;">

 <p><strong>Customers Location: </strong>{$customers_ip}</p>	
</div>
EOD;

      return $output;
    }
  }
?>

Share this post


Link to post
Share on other sites

@@greasemonkey good work!


This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites

@@burt thanks Gary.

 

I'll put it together into and addon once I've had a chance to double check it and test...

 

Now the original idea was to supply the IP address AND the country - do you have any ideas on how to supply the country data for the IP in the cleanest possible way? In a perfect world a map... but I would be happy with just the country.

 

It looks like the Google API is out... geolocation is only via W3C.

 

IP2location is do-able... they provide a free country database I think (but I think you have to register.... and the DB has to be downloaded and maintained)

 

There are mutiple free API's but they are all small....

Share this post


Link to post
Share on other sites

@@burt any issues with something like this? Geoplugin seems reputable...

          $geo = unserialize(file_get_contents("http://www.geoplugin.net/php.gp?ip=$customers_ip"));

          $country = $geo["geoplugin_countryName"];
          $city = $geo["geoplugin_city"];
          $region = $geo["geoplugin_regionName"];
          $latitude = $geo["geoplugin_latitude"];
          $longitude = $geo["geoplugin_longitude"];

And then output like...

 <p><strong>Customers Location: </strong>{$customers_ip}<br><br>{$country}<br>{$city}<br>{$region}<br>{$latitude}<br>{$longitude}</p>

Now... I could set up a Google Map api on the city, or even the lat/long...... they have lots of examples.... here

 

But then you would be required to get a Google map API key.

Edited by greasemonkey

Share this post


Link to post
Share on other sites

Throwing out ideas;

Why not (in the module) offer an input for Google API Key.  If that is filled in...show the map; it not, not.

 

You could also grab the customers (delivery) address, and compare the parts of it against:

 

$country = $geo["geoplugin_countryName"];   >>>>>>>>>>>>>>>>>>>>> delivery_country
$city = $geo["geoplugin_city"] >>>>>>>>>>>>>>>>>>>>> delivery_city
$region = $geo["geoplugin_regionName"]; >>>>>>>>>>>>>>>> delivery_state

 

and have it set out in a table with little ticks for matches or crosses for not matching.
 

 

But remember always:  it's too easy to over-engineer...Keep it simple. and let other people do the hard work if they want something more.  It used to be that I felt that doing everything at the start was correct.  It took me a long time to understand that doing the bare bones allows other to flesh out as needed.

Edited by burt

This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites

@@burt good point about keeping it simple... and letting the user modify as necessary.

 

Any option on removing the column on install? Seems logical to me... but someone may be upset if they find out after these IP data is gone...

 

All I need is a tester (on BS EDGE or Gold with PayPal app) any takers?

Share this post


Link to post
Share on other sites

@@greasemonkey

 

Any option on removing the column on install? Seems logical to me... but someone may be upset if they find out after these IP data is gone...

 

Scott,

 

There have been discussions on:

 

1) How to test if a column already exists in a table when installing an addon

2) How to ask if the column should be deleted or saved when un-installing an addon

 

I have the necessary code in this addon ...

 

http://addons.oscommerce.com/info/9318

 

HTH

 

Malcolm


Get the latest Responsive osCommerce CE (community edition) here .

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

×