Jump to content
olsonsp4c

Package Tracking with Email Notification Mods

Recommended Posts

Hello,

 

I am getting the following SQL errors in my site.

This is on the admin side when I go to Customers=>Orders=> Edit:

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'and ups_track_num, fedex_freight_track_num, dhl_track_num, curr

 

select customers_name, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, comments, fedex_track_num, ups_track_num, and ups_track_num, fedex_freight_track_num, dhl_track_num, currency, currency_value, date_purchased, orders_status, last_modified from orders where orders_id = '67'

 

[TEP STOP]

 

And I get this on the catalog side when going to Account Information=>View Order:

1054 - Unknown column 'comments' in 'field list'

 

select customers_name, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, comments, fedex_track_num, ups_track_num, usps_track_num, fedex_freight_track_num, dhl_track_num, currency, currency_value, date_purchased, orders_status, last_modified from orders where orders_id = '65'

 

[TEP STOP]

 

I checked my database after uploading the SQL file and everything seems to be correct, but not according to the error in the admin side. I double checked all the installation steps.

 

Please help.

 

Rod

Share this post


Link to post
Share on other sites

I figured out the catalog side error....somehow the comments filed got dropped.

 

But I still cannot figure out the admin side error.....I dropped the fields and ran the SQL file again with the same results. I do not see where they are finding the "and" in the text.

 

Help,

Rod

Share this post


Link to post
Share on other sites
select customers_name, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, comments, fedex_track_num, ups_track_num, and ups_track_num, fedex_freight_track_num, dhl_track_num, currency, currency_value, date_purchased, orders_status, last_modified from orders where orders_id = '67'

 

Now do you see it?

 

After the first ups_track_num

Share this post


Link to post
Share on other sites
Now do you see it?

 

After the first ups_track_num

 

 

Yes...I see that in the error message, but cannot find it in the sql data. Everything on my database is the way it's supposed to be...I cannot find where it is puling that "and ups_track_num" from...

 

Rod

Share this post


Link to post
Share on other sites
I figured out the catalog side error....somehow the comments filed got dropped.

 

But I still cannot figure out the admin side error.....I dropped the fields and ran the SQL file again with the same results. I do not see where they are finding the "and" in the text.

 

Help,

Rod

 

If I understand you correctly your looking for an error when you create the fields in the database?

 

This is an error in the query in your php-file. Look around line 38 in your admin/orders.php file

 

As for the comments field you don't have one in the orders table.

Either add it (though there is no point to do so unless you somwhere add code to put something in that field)

or remove comment from the query. This is in catalog/includes/classes/orders.php

Look around line 35.

Share this post


Link to post
Share on other sites
If I understand you correctly your looking for an error when you create the fields in the database?

 

This is an error in the query in your php-file. Look around line 38 in your admin/orders.php file

 

As for the comments field you don't have one in the orders table.

Either add it (though there is no point to do so unless you somwhere add code to put something in that field)

or remove comment from the query. This is in catalog/includes/classes/orders.php

Look around line 35.

 

 

Here is my code from admin/orders.php:

 

if (tep_not_null($action)) {
switch ($action) {
  case 'update_order':
	$oID = tep_db_prepare_input($HTTP_GET_VARS['oID']);
	$status = tep_db_prepare_input($HTTP_POST_VARS['status']);
	$comments = tep_db_prepare_input($HTTP_POST_VARS['comments']);
	//Tracking contribution begin
  $ups_track_num = tep_db_prepare_input($HTTP_POST_VARS['ups_track_num']);
  $usps_track_num = tep_db_prepare_input($HTTP_POST_VARS['usps_track_num']);
  $fedex_track_num = tep_db_prepare_input($HTTP_POST_VARS['fedex_track_num']);
  $fedex_freight_track_num = tep_db_prepare_input($HTTP_POST_VARS['fedex_freight_track_num']);
  $dhl_track_num = tep_db_prepare_input($HTTP_POST_VARS['dhl_track_num']);
   //Tracking contribution end

 

 

I do not see the dreaded"and ups_track_num" What am I missing?

Rod

Share this post


Link to post
Share on other sites
Here is my code from admin/orders.php:

 

if (tep_not_null($action)) {
switch ($action) {
  case 'update_order':
	$oID = tep_db_prepare_input($HTTP_GET_VARS['oID']);
	$status = tep_db_prepare_input($HTTP_POST_VARS['status']);
	$comments = tep_db_prepare_input($HTTP_POST_VARS['comments']);
	//Tracking contribution begin
  $ups_track_num = tep_db_prepare_input($HTTP_POST_VARS['ups_track_num']);
  $usps_track_num = tep_db_prepare_input($HTTP_POST_VARS['usps_track_num']);
  $fedex_track_num = tep_db_prepare_input($HTTP_POST_VARS['fedex_track_num']);
  $fedex_freight_track_num = tep_db_prepare_input($HTTP_POST_VARS['fedex_freight_track_num']);
  $dhl_track_num = tep_db_prepare_input($HTTP_POST_VARS['dhl_track_num']);
   //Tracking contribution end

I do not see the dreaded"and ups_track_num" What am I missing?

Rod

 

No, no. It's in the select statement. It look something like this:

 
$check_status_query = tep_db_query("select customers_name, customers_email_address, orders_status,	   fedex_track_num, ups_track_num, usps_track_num,norpost_track_num, date_purchased from " . TABLE_ORDERS . " where orders_id = '" . tep_db_input($oID) . "'");

Share this post


Link to post
Share on other sites

Here is what I have there...cannot see an error:

 

//Tracking contribution begin
	$check_status_query = tep_db_query("select customers_name, customers_email_address, orders_status, ups_track_num, usps_track_num, fedex_track_num, fedex_freight_track_num, dhl_track_num, date_purchased from " . TABLE_ORDERS . " where orders_id = '" . tep_db_input($oID) . "'");
   //Tracking contribution end

 

?

 

Rod - Still looking

Share this post


Link to post
Share on other sites
Here is what I have there...cannot see an error:

 

//Tracking contribution begin
	$check_status_query = tep_db_query("select customers_name, customers_email_address, orders_status, ups_track_num, usps_track_num, fedex_track_num, fedex_freight_track_num, dhl_track_num, date_purchased from " . TABLE_ORDERS . " where orders_id = '" . tep_db_input($oID) . "'");
   //Tracking contribution end

 

?

 

Rod - Still looking

 

Sorry some times I'm blind. Neither your nor my select statement look anything like the one in the error statement. It is (probably) the one in admin/includes/classes/order.php. Around line 27

Share this post


Link to post
Share on other sites
Sorry some times I'm blind. Neither your nor my select statement look anything like the one in the error statement. It is (probably) the one in admin/includes/classes/order.php. Around line 27

 

BINGO!! Thanks for your lengthy help. Very much appreciated!! Here is the line of code from the installation read me file. "and ups_track_num" should be changed to usps_track_num.

 

Thanks.

 

From Read Me Install:

***Step 8***

 

catalog/admin/includes/classes/order.php

 

Find this:

 

$order_query = tep_db_query("select customers_name, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, comments, currency, currency_value, date_purchased, orders_status, last_modified from " . TABLE_ORDERS . " where orders_id = '" . tep_db_input($order_id) . "'");

 

And replace it with this:

 

//Tracking contribution begin
  $order_query = tep_db_query("select customers_name, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, comments, fedex_track_num, ups_track_num, and ups_track_num, fedex_freight_track_num, dhl_track_num, currency, currency_value, date_purchased, orders_status, last_modified from " . TABLE_ORDERS . " where orders_id = '" . tep_db_input($order_id) . "'");
//Tracking contribution end

***Note: the only change was the addition of fedex_track_num, ups_track_num, and ups_track_num, fedex_freight_track_num, dhl_track_num, to the query.

Share this post


Link to post
Share on other sites

wow, got exactly every body's errors on this install so I would like to summarize these here:

 

first error: admin > Customers > orders gives

Parse error: parse error in ***/admin/orders.php on line 180 // which is case 'deleteconfirm':

thanks to kloienyc, placed a " } before case 'deleteconfirm':

 

Second error: admin > orders > customers > orders > edit gives

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and ups_track_num, fedex_freight_track_num, dhl_track_num, currency, currency_va' at line 1

 

change "and ups_track_num" to usps_track_num in catalog/admin/includes/classes/order.php

 

third error: catalog > account > order > view gives

1054 - Unknown column 'comments' in 'field list'

go to phpmyadmin into table orders and add field comments VARCHAR length 256 Null after cc_expires

Share this post


Link to post
Share on other sites

on my first attempt to test admin > customers > orders >

 

I inserted a tracking number to an existing real order and pressed update. admin gave error and deleted the order completely. I looked in phpmyadmin order table and sure enough, that order is gone.

1. Is there a way to recover this order back into the database?

 

Also in admin > customers > orders > when testing all three UPS, Fedex, and USPS tracking numbers, when I delete the UPS number and add a Fedex number, the UPS box refills with the last UPS tracking number. Also, in catalog > account > order > view you see all three tracking numbers.

 

2. In a real order situation, if one made a mistake and sent a UPS number and then updated with a USPS tracking number, how can I make sure the UPS box remains blank in both admin and catolog > account > order > views so as not to confuse customer?

 

Finally the email sent is MIME-Version: 1.0 however there are no links , see below

Detailed Invoice: /account_history_info.php?order_id=36
Date Ordered: Wednesday 26 July, 2006


Package tracking numbers (click "invoice" link to track):
Fedex: 092051532518205

3. How do I turn these into links?

Share this post


Link to post
Share on other sites

First off I want to say this contribution meets perfectly a necessity for shop owners.

 

I am having some trouble though.

 

I have placed a test order, and have been changing the status. The basics work fine, I can enter the tracking number can link to the tracking information on the UPS website or etc.

 

The trouble is that when I change the status of the order, 2 emails are sent to the customer.

 

The first sent email is perfect. It has the right title and the link to the account account_history.php is fine. However, the customer receives a second email which is titled EMAIL_TEXT_SUBJECT inside it is the exact same email as the first one, except the link is https://https://server119.tchmachines.com/~....php?order_id=1

 

which as you can see has two https:// on it and two // at the end of my secure server address.

 

Does anybody know how to eliminate this second email?

 

My shop will not be live for a while but this is really throwing a curve ball at me so I thought I'd ask for help instead of wasting hours figuring it out.

Share this post


Link to post
Share on other sites
on my first attempt to test admin > customers > orders >

 

I inserted a tracking number to an existing real order and pressed update. admin gave error and deleted the order completely. I looked in phpmyadmin order table and sure enough, that order is gone.

1. Is there a way to recover this order back into the database?

 

Also in admin > customers > orders > when testing all three UPS, Fedex, and USPS tracking numbers, when I delete the UPS number and add a Fedex number, the UPS box refills with the last UPS tracking number. Also, in catalog > account > order > view you see all three tracking numbers.

 

2. In a real order situation, if one made a mistake and sent a UPS number and then updated with a USPS tracking number, how can I make sure the UPS box remains blank in both admin and catolog > account > order > views so as not to confuse customer?

 

Finally the email sent is MIME-Version: 1.0 however there are no links , see below

Detailed Invoice: /account_history_info.php?order_id=36
Date Ordered: Wednesday 26 July, 2006
Package tracking numbers (click "invoice" link to track):
Fedex: 092051532518205

3. How do I turn these into links?

 

1. Check with your db-host if they have a backup. It's always good to have a test server for the testing rather than doing ot your live server.

 

2.

Find this code in admin/order.php

if (tep_not_null($ups_track_num)) {
	tep_db_query("update " . TABLE_ORDERS . " set ups_track_num = '" . tep_db_input($ups_track_num) . "' where orders_id = '" . tep_db_input($oID) . "'");
	$order_updated = true;
  }

  if (tep_not_null($usps_track_num)) {
	tep_db_query("update " . TABLE_ORDERS . " set usps_track_num = '" . tep_db_input($usps_track_num) . "' where orders_id = '" . tep_db_input($oID) . "'");
	$order_updated = true;
  }

  if (tep_not_null($fedex_track_num)) {
	tep_db_query("update " . TABLE_ORDERS . " set fedex_track_num = '" . tep_db_input($fedex_track_num) . "' where orders_id = '" . tep_db_input($oID) . "'");
	$order_updated = true;
  }

 

change to:

 

		tep_db_query("update " . TABLE_ORDERS . " set ups_track_num = '" . tep_db_input($ups_track_num) . "' where orders_id = '" . tep_db_input($oID) . "'");
	$order_updated = true;

	tep_db_query("update " . TABLE_ORDERS . " set usps_track_num = '" . tep_db_input($usps_track_num) . "' where orders_id = '" . tep_db_input($oID) . "'");
	$order_updated = true;

	tep_db_query("update " . TABLE_ORDERS . " set fedex_track_num = '" . tep_db_input($fedex_track_num) . "' where orders_id = '" . tep_db_input($oID) . "'");
	$order_updated = true;

 

Basically remove the check for inserting blank, because you have to to remove the tracking number

 

3.

 

Check admin/includes/configure.php and catalog/includes/configure.php

 

These three need to have values to create the links:

 

define('HTTP_SERVER', 'http://www.yoursite.com/');

define('HTTP_CATALOG_SERVER', 'http://www.yoursite.com/');

define('HTTPS_CATALOG_SERVER', 'https://www.yoursite.com/')

 

Hope it helps.

Share this post


Link to post
Share on other sites

This is the original author - my plate has been really full, so I've not been able to update this contrib like I'd have liked or support it well (as I am not really a coder as those of you who are probably figured out by the way I wrote the hacks).

 

I just wanted to say thank you from the bottom of my heart to those who are making my contribution better and are actively supporting it here on this thread.

 

Appreciate you.

 

Scott

Share this post


Link to post
Share on other sites

Hey great contrib, I ran into one problem though. When I update an order and dont notify the customer it works perfect, it updates the order history and everything. Now when I click notify customer and then update after a few seconds I get a page not found error, but the email has been sent. The problem is that the order history dont get recorded into the database. I figure it is a error in the email part of the code, since it works fine when no email is being sent.

Share this post


Link to post
Share on other sites

1054 - Unknown column 'customers_company' in 'field list'

 

That's the error I get. When I try to confirm the order. I don't know what to do. I do not require customer to give their company name either.

 

I found "Customers_company" in catalog/checkout_process.php

and catalog/include/classes/order.php

and catalog/admin/includes/classes/order.php

 

I have read all the posts. The closest one was about "comment" .

 

Could somebody please help me.

 

Thank you

Share this post


Link to post
Share on other sites
1054 - Unknown column 'customers_company' in 'field list'

 

That's the error I get. When I try to confirm the order. I don't know what to do. I do not require customer to give their company name either.

 

I found "Customers_company" in catalog/checkout_process.php

and catalog/include/classes/order.php

and catalog/admin/includes/classes/order.php

 

I have read all the posts. The closest one was about "comment" .

 

Could somebody please help me.

 

Thank you

I hope I finally figured it out after reading and searching more. This is what I did.

 

I added these files to mysql:

 

customers_company varchar(32) default NULL,

delivery_company varchar(32) default NULL,

billing_company varchar(32) default NULL,

billing_street_address varchar(64) NOT NULL default '',

billing_suburb varchar(32) default NULL,

billing_city varchar(32) NOT NULL default '',

billing_postcode varchar(10) NOT NULL default '',

billing_state varchar(32) default NULL,

billing_country varchar(32) NOT NULL default '',

 

I hope it will work now.

Share this post


Link to post
Share on other sites

 

Great Job Claus and thanks for updating the contribution. I would have posted earlier but I missed the topic notification email.

 

1. I contacted the buyer and requested a copy of the email sent to them to rebuild order. I see 7 tables related to orders in phpmyadmin. what sql query string should I issue so as not to miss a required field to insert missing order?

 

2. Basically remove the if statement check for inserting blank, because you have to to remove the tracking number. worked Great both in admin and catalog

 

3.Check admin/includes/configure.php and catalog/includes/configure.php

These three need to have values to create the links:

 

define('HTTP_SERVER', 'http://www.yoursite.com/');

define('HTTP_CATALOG_SERVER', 'http://www.yoursite.com/'); // added to catalog/includes/configure.php

define('HTTPS_CATALOG_SERVER', 'https://www.yoursite.com/') // catalog/includes/configure.php

 

but still sending HTML emails but no links, see below. any other clues?

 

Detailed Invoice: http://www.domain.com/account_history_info.php?order_id=36
...
Package tracking numbers (click "invoice" link to track):
Fedex: 092051532518205

Share this post


Link to post
Share on other sites
Hey great contrib, I ran into one problem though. When I update an order and dont notify the customer it works perfect, it updates the order history and everything. Now when I click notify customer and then update after a few seconds I get a page not found error, but the email has been sent. The problem is that the order history dont get recorded into the database. I figure it is a error in the email part of the code, since it works fine when no email is being sent.

 

Fixed my own problem, I took out the check to remove tracking number part and now it works perfect.

Share this post


Link to post
Share on other sites

Hi, thanks for the contribution . I've managed to sort out most problems , with the help to the people in this thread. All the admin and email side of things are working great. But now somehow ive managed to change something so you cant confirm the order, however this did work yesterday before i deleted the 'tracking contribution begin & end' stuff - so im guessing i managed to remove something else . I went back over the readme and re-replaced the right parts but it still wont work.

 

Now this is what the problem is -

 

When i test out buying an order i can get through all the main pages of the procedure but when i click the 'confirm order' button i get this error message -

 

Parse error: parse error, unexpected $ in /home/fhlinux186/p/****/user/htdocs/catalog/checkout_process.php on line 281

 

now ive checked the file it points too 'checkout_process.php' on the line 281, but there is nothing there but the ?> thing no $ & it's the very last line of the code. Im not clued up on this php lark so hope that someone with the knowledge can help me out . I'll include the code of the file at hand..

 

cheers.

 

<?php
/*
 $Id: checkout_process.php,v 1.128 2003/05/28 18:00:29 hpdl Exp $

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

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 include('includes/application_top.php');

// if the customer is not logged on, redirect them to the login page
 if (!tep_session_is_registered('customer_id')) {
$navigation->set_snapshot(array('mode' => 'SSL', 'page' => FILENAME_CHECKOUT_PAYMENT));
tep_redirect(tep_href_link(FILENAME_LOGIN, '', 'SSL'));
 }

 if (!tep_session_is_registered('sendto')) {
tep_redirect(tep_href_link(FILENAME_CHECKOUT_PAYMENT, '', 'SSL'));
 }

 if ( (tep_not_null(MODULE_PAYMENT_INSTALLED)) && (!tep_session_is_registered('payment')) ) {
tep_redirect(tep_href_link(FILENAME_CHECKOUT_PAYMENT, '', 'SSL'));
}

// avoid hack attempts during the checkout procedure by checking the internal cartID
 if (isset($cart->cartID) && tep_session_is_registered('cartID')) {
if ($cart->cartID != $cartID) {
  tep_redirect(tep_href_link(FILENAME_CHECKOUT_SHIPPING, '', 'SSL'));
}
 }

 include(DIR_WS_LANGUAGES . $language . '/' . FILENAME_CHECKOUT_PROCESS);

// load selected payment module
 require(DIR_WS_CLASSES . 'payment.php');
 $payment_modules = new payment($payment);

// load the selected shipping module
 require(DIR_WS_CLASSES . 'shipping.php');
 $shipping_modules = new shipping($shipping);

 require(DIR_WS_CLASSES . 'order.php');
 $order = new order;

// load the before_process function from the payment modules
 $payment_modules->before_process();

 require(DIR_WS_CLASSES . 'order_total.php');
 $order_total_modules = new order_total;

 $order_totals = $order_total_modules->process();

 $sql_data_array = array('customers_id' => $customer_id,
					  'customers_name' => $order->customer['firstname'] . ' ' . $order->customer['lastname'],
					  'customers_company' => $order->customer['company'],
					  'customers_street_address' => $order->customer['street_address'],
					  'customers_suburb' => $order->customer['suburb'],
					  'customers_city' => $order->customer['city'],
					  'customers_postcode' => $order->customer['postcode'], 
					  'customers_state' => $order->customer['state'], 
					  'customers_country' => $order->customer['country']['title'], 
					  'customers_telephone' => $order->customer['telephone'], 
					  'customers_email_address' => $order->customer['email_address'],
					  'customers_address_format_id' => $order->customer['format_id'], 
					  'delivery_name' => $order->delivery['firstname'] . ' ' . $order->delivery['lastname'], 
					  'delivery_company' => $order->delivery['company'],
					  'delivery_street_address' => $order->delivery['street_address'], 
					  'delivery_suburb' => $order->delivery['suburb'], 
					  'delivery_city' => $order->delivery['city'], 
					  'delivery_postcode' => $order->delivery['postcode'], 
					  'delivery_state' => $order->delivery['state'], 
					  'delivery_country' => $order->delivery['country']['title'], 
					  'delivery_address_format_id' => $order->delivery['format_id'], 
					  'billing_name' => $order->billing['firstname'] . ' ' . $order->billing['lastname'], 
					  'billing_company' => $order->billing['company'],
					  'billing_street_address' => $order->billing['street_address'], 
					  'billing_suburb' => $order->billing['suburb'], 
					  'billing_city' => $order->billing['city'], 
					  'billing_postcode' => $order->billing['postcode'], 
					  'billing_state' => $order->billing['state'], 
					  'billing_country' => $order->billing['country']['title'], 
					  'billing_address_format_id' => $order->billing['format_id'], 
					  'payment_method' => $order->info['payment_method'], 
					  'cc_type' => $order->info['cc_type'], 
					  'cc_owner' => $order->info['cc_owner'], 
					  'cc_number' => $order->info['cc_number'], 
					  'cc_expires' => $order->info['cc_expires'], 
					  'date_purchased' => 'now()', 
					  'orders_status' => $order->info['order_status'], 
					  'currency' => $order->info['currency'], 
					  'currency_value' => $order->info['currency_value']);
 tep_db_perform(TABLE_ORDERS, $sql_data_array);
 $insert_id = tep_db_insert_id();
 for ($i=0, $n=sizeof($order_totals); $i<$n; $i++) {
$sql_data_array = array('orders_id' => $insert_id,
						'title' => $order_totals[$i]['title'],
						'text' => $order_totals[$i]['text'],
						'value' => $order_totals[$i]['value'], 
						'class' => $order_totals[$i]['code'], 
						'sort_order' => $order_totals[$i]['sort_order']);
tep_db_perform(TABLE_ORDERS_TOTAL, $sql_data_array);
 }

 $customer_notification = (SEND_EMAILS == 'true') ? '1' : '0';
 $sql_data_array = array('orders_id' => $insert_id, 
					  'orders_status_id' => $order->info['order_status'], 
					  'date_added' => 'now()', 
					  'customer_notified' => $customer_notification,
					  'comments' => $order->info['comments']);
 tep_db_perform(TABLE_ORDERS_STATUS_HISTORY, $sql_data_array);

// initialized for the email confirmation
 $products_ordered = '';
 $subtotal = 0;
 $total_tax = 0;

 for ($i=0, $n=sizeof($order->products); $i<$n; $i++) {
// Stock Update - Joao Correia
if (STOCK_LIMITED == 'true') {
  if (DOWNLOAD_ENABLED == 'true') {
	$stock_query_raw = "SELECT products_quantity, pad.products_attributes_filename 
						FROM " . TABLE_PRODUCTS . " p
						LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " pa
						 ON p.products_id=pa.products_id
						LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad
						 ON pa.products_attributes_id=pad.products_attributes_id
						WHERE p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "'";
// Will work with only one option for downloadable products
// otherwise, we have to build the query dynamically with a loop
	$products_attributes = $order->products[$i]['attributes'];
	if (is_array($products_attributes)) {
	  $stock_query_raw .= " AND pa.options_id = '" . $products_attributes[0]['option_id'] . "' AND pa.options_values_id = '" . $products_attributes[0]['value_id'] . "'";
	}
	$stock_query = tep_db_query($stock_query_raw);
  } else {
	$stock_query = tep_db_query("select products_quantity from " . TABLE_PRODUCTS . " where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
  }
  if (tep_db_num_rows($stock_query) > 0) {
	$stock_values = tep_db_fetch_array($stock_query);
// do not decrement quantities if products_attributes_filename exists
	if ((DOWNLOAD_ENABLED != 'true') || (!$stock_values['products_attributes_filename'])) {
	  $stock_left = $stock_values['products_quantity'] - $order->products[$i]['qty'];
	} else {
	  $stock_left = $stock_values['products_quantity'];
	}
	tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = '" . $stock_left . "' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
	if ( ($stock_left < 1) && (STOCK_ALLOW_CHECKOUT == 'false') ) {
	  tep_db_query("update " . TABLE_PRODUCTS . " set products_status = '0' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");
	}
  }
}

// Update products_ordered (for bestsellers list)
tep_db_query("update " . TABLE_PRODUCTS . " set products_ordered = products_ordered + " . sprintf('%d', $order->products[$i]['qty']) . " where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'");

$sql_data_array = array('orders_id' => $insert_id, 
						'products_id' => tep_get_prid($order->products[$i]['id']), 
						'products_model' => $order->products[$i]['model'], 
						'products_name' => $order->products[$i]['name'], 
						'products_price' => $order->products[$i]['price'], 
						'final_price' => $order->products[$i]['final_price'], 
						'products_tax' => $order->products[$i]['tax'], 
						'products_quantity' => $order->products[$i]['qty']);
tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);
$order_products_id = tep_db_insert_id();

//------insert customer choosen option to order--------
$attributes_exist = '0';
$products_ordered_attributes = '';
if (isset($order->products[$i]['attributes'])) {
  $attributes_exist = '1';
  for ($j=0, $n2=sizeof($order->products[$i]['attributes']); $j<$n2; $j++) {
	if (DOWNLOAD_ENABLED == 'true') {
	  $attributes_query = "select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix, pad.products_attributes_maxdays, pad.products_attributes_maxcount , pad.products_attributes_filename 
						   from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa 
						   left join " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad
							on pa.products_attributes_id=pad.products_attributes_id
						   where pa.products_id = '" . $order->products[$i]['id'] . "' 
							and pa.options_id = '" . $order->products[$i]['attributes'][$j]['option_id'] . "' 
							and pa.options_id = popt.products_options_id 
							and pa.options_values_id = '" . $order->products[$i]['attributes'][$j]['value_id'] . "' 
							and pa.options_values_id = poval.products_options_values_id 
							and popt.language_id = '" . $languages_id . "' 
							and poval.language_id = '" . $languages_id . "'";
	  $attributes = tep_db_query($attributes_query);
	} else {
	  $attributes = tep_db_query("select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa where pa.products_id = '" . $order->products[$i]['id'] . "' and pa.options_id = '" . $order->products[$i]['attributes'][$j]['option_id'] . "' and pa.options_id = popt.products_options_id and pa.options_values_id = '" . $order->products[$i]['attributes'][$j]['value_id'] . "' and pa.options_values_id = poval.products_options_values_id and popt.language_id = '" . $languages_id . "' and poval.language_id = '" . $languages_id . "'");
	}
	$attributes_values = tep_db_fetch_array($attributes);

	$sql_data_array = array('orders_id' => $insert_id, 
							'orders_products_id' => $order_products_id, 
							'products_options' => $attributes_values['products_options_name'],
							'products_options_values' => $attributes_values['products_options_values_name'], 
							'options_values_price' => $attributes_values['options_values_price'], 
							'price_prefix' => $attributes_values['price_prefix']);
	tep_db_perform(TABLE_ORDERS_PRODUCTS_ATTRIBUTES, $sql_data_array);

	if ((DOWNLOAD_ENABLED == 'true') && isset($attributes_values['products_attributes_filename']) && tep_not_null($attributes_values['products_attributes_filename'])) {
	  $sql_data_array = array('orders_id' => $insert_id, 
							  'orders_products_id' => $order_products_id, 
							  'orders_products_filename' => $attributes_values['products_attributes_filename'], 
							  'download_maxdays' => $attributes_values['products_attributes_maxdays'], 
							  'download_count' => $attributes_values['products_attributes_maxcount']);
	  tep_db_perform(TABLE_ORDERS_PRODUCTS_DOWNLOAD, $sql_data_array);
	}
	$products_ordered_attributes .= "\n\t" . $attributes_values['products_options_name'] . ' ' . $attributes_values['products_options_values_name'];
  }
}
//------insert customer choosen option eof ----
$total_weight += ($order->products[$i]['qty'] * $order->products[$i]['weight']);
$total_tax += tep_calculate_tax($total_products_price, $products_tax) * $order->products[$i]['qty'];
$total_cost += $total_products_price;

$products_ordered .= $order->products[$i]['qty'] . ' x ' . $order->products[$i]['name'] . ' (' . $order->products[$i]['model'] . ') = ' . $currencies->display_price($order->products[$i]['final_price'], $order->products[$i]['tax'], $order->products[$i]['qty']) . $products_ordered_attributes . "\n";
 }

// lets start with the email confirmation
 $email_order = STORE_NAME . "\n" . 
			 EMAIL_SEPARATOR . "\n" . 
			 EMAIL_TEXT_ORDER_NUMBER . ' ' . $insert_id . "\n" .
			 EMAIL_TEXT_INVOICE_URL . ' ' . tep_href_link(FILENAME_ACCOUNT_HISTORY_INFO, 'order_id=' . $insert_id, 'SSL', false) . "\n" .
			 EMAIL_TEXT_DATE_ORDERED . ' ' . strftime(DATE_FORMAT_LONG) . "\n\n";
 if ($order->info['comments']) {
$email_order .= tep_db_output($order->info['comments']) . "\n\n";
 }
 $email_order .= EMAIL_TEXT_PRODUCTS . "\n" . 
			  EMAIL_SEPARATOR . "\n" . 
			  $products_ordered . 
			  EMAIL_SEPARATOR . "\n";

 for ($i=0, $n=sizeof($order_totals); $i<$n; $i++) {
$email_order .= strip_tags($order_totals[$i]['title']) . ' ' . strip_tags($order_totals[$i]['text']) . "\n";
 }

 if ($order->content_type != 'virtual') {
$email_order .= "\n" . EMAIL_TEXT_DELIVERY_ADDRESS . "\n" . 
				EMAIL_SEPARATOR . "\n" .
				tep_address_label($customer_id, $sendto, 0, '', "\n") . "\n";
 }

 $email_order .= "\n" . EMAIL_TEXT_BILLING_ADDRESS . "\n" .
			  EMAIL_SEPARATOR . "\n" .
			  tep_address_label($customer_id, $billto, 0, '', "\n") . "\n\n";
 if (is_object($$payment)) {
$email_order .= EMAIL_TEXT_PAYMENT_METHOD . "\n" . 
				EMAIL_SEPARATOR . "\n";
$payment_class = $$payment;
$email_order .= $payment_class->title . "\n\n";
if ($payment_class->email_footer) { 
  $email_order .= $payment_class->email_footer . "\n\n";
}
  tep_mail($order->customer['firstname'] . ' ' . $order->customer['lastname'], $order->customer['email_address'], EMAIL_TEXT_SUBJECT_1 . ' ' . $insert_id . ' ' .EMAIL_TEXT_SUBJECT_2 , $email_order, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS);

// send emails to other people
 if (SEND_EXTRA_ORDER_EMAILS_TO != '')
  {
tep_mail('', SEND_EXTRA_ORDER_EMAILS_TO, EMAIL_TEXT_SUBJECT_1 . ' ' . $insert_id . ' ' .EMAIL_TEXT_SUBJECT_2, $email_order, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS);
 }

// load the after_process function from the payment modules
 $payment_modules->after_process();

 $cart->reset(true);

// unregister session variables used during checkout
 tep_session_unregister('sendto');
 tep_session_unregister('billto');
 tep_session_unregister('shipping');
 tep_session_unregister('payment');
 tep_session_unregister('comments');

 tep_redirect(tep_href_link(FILENAME_CHECKOUT_SUCCESS, '', 'SSL'));

 require(DIR_WS_INCLUDES . 'application_bottom.php');

 ?>

Share this post


Link to post
Share on other sites

Jackk, comparing your code to mine, I see some differences starting line 256. below is what the code looks like starting line 254. Copy and paste and test it.

 if ($payment_class->email_footer) {
  $email_order .= $payment_class->email_footer . "\n\n";
}
 }	 
  tep_mail($order->customer['firstname'] . ' ' . $order->customer['lastname'], $order->customer['email_address'], EMAIL_TEXT_SUBJECT_1 . ' ' . $insert_id . ' ' .EMAIL_TEXT_SUBJECT_2 , $email_order, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS);

// send emails to other people
 if (SEND_EXTRA_ORDER_EMAILS_TO != '') {
tep_mail('', SEND_EXTRA_ORDER_EMAILS_TO, EMAIL_TEXT_SUBJECT, $email_order, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS);



 }

// load the after_process function from the payment modules
 $payment_modules->after_process();

 $cart->reset(true);

// unregister session variables used during checkout
 tep_session_unregister('sendto');
 tep_session_unregister('billto');
 tep_session_unregister('shipping');
 tep_session_unregister('payment');
 tep_session_unregister('comments');

 tep_redirect(tep_href_link(FILENAME_CHECKOUT_SUCCESS, '', 'SSL'));

 require(DIR_WS_INCLUDES . 'application_bottom.php');

 ?>

Share this post


Link to post
Share on other sites

hey thanks for that avail1now works perfectly now, must remember to be more careful in future.

 

cheers

Share this post


Link to post
Share on other sites

originally asked this on July 27, 2006. emails go out as HTML but tracking numbers and urls are not links or linkable.

 

admin/includes/configure.php and catalog/includes/configure.php contain definitions below to create links:

define('HTTP_SERVER', 'http://www.yoursite.com/');

define('HTTP_CATALOG_SERVER', 'http://www.yoursite.com/');

define('HTTPS_CATALOG_SERVER', 'https://www.yoursite.com/')

 

yet HTML emails contain:

Detailed Invoice: http://www.domain.com/account_history_info.php?order_id=36
...
Package tracking numbers (click "invoice" link to track):
Fedex: 092051532518205

 

Any Clues?

Edited by avail1now

Share this post


Link to post
Share on other sites

1054 - Unknown column 'comments' in 'field list'

select customers_name, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, comments, fedex_track_num, ups_track_num, usps_track_num, fedex_freight_track_num, dhl_track_num, currency, currency_value, date_purchased, orders_status, last_modified from orders where orders_id = '134'

 

hi im still having problem with this, and i dont know much about mysql..

 

SELECT * FROM `orders` WHERE 1 insert comments varchar(256) null

 

alter table `orders` insert comments varchar(256) null

 

??? sorry i dont know how! :( can someone teach me?

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

×