How to show customers zip code/postcode in orders

[♥14steve14]

I am often told that there is no such thing as  a silly question. I believe that I have found that silly question.

For what ever reason I am trying to add a tracking number from Royal Mail and a couriers into the comments box when updating orders in admin. That bit is easy using Gary's 'Canned Comments' code , and it all works fine and as I hoped it would. The canned comments are added in the  orders page after you have clicked to see the order. I want to show the customers zip/post code on the main orders page or front screen of the orders to make it easy which orders need the tracking number listed. The post office receipt matches the tracking code to the postcode, which cant be seen until the order is actually opened and viewed. That means I have to click on every order to find the postcode to find the tracking number, when it is only needed on certain orders. In my view that's a waste of time with 40+ orders a day.

I hope this is all making some sort of sense to someone.

On the main orders page things like customers, order total, order date and order status are shown. I have added customers IP and order number there so I have a basic idea on how to alter that page to show a heading and can figure out how to show the postcode I hope, but have no idea on how to incorporate the order and a section of the delivery address to get the data needed to show relative to the customers order. This is where I need help. I need to know the sql statement to get that info please and where it would be best placed in the orders page code.

Any help greatly appreciated.

 

[♥raiwa]

Hello Steve,

Have a look in this part of orders.php quite at the end:

    if (isset($_GET['cID'])) {
      $cID = tep_db_prepare_input($_GET['cID']);
      $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";
    } elseif (isset($_GET['status']) && is_numeric($_GET['status']) && ($_GET['status'] > 0)) {
      $status = tep_db_prepare_input($_GET['status']);
      $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by o.orders_id DESC";
    } else {
      $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
    }

Add o.delivery_postcode  to all queries.

Then in the table section:

                <td class="dataTableContent"><?php echo '<a href="' . tep_href_link('orders.php', tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $orders['orders_id'] . '&action=edit') . '">' . tep_image('images/icons/preview.gif', ICON_PREVIEW) . '</a>&nbsp;' . $orders['customers_name']; ?></td>
                <td class="dataTableContent" align="right"><?php echo strip_tags($orders['order_total']); ?></td>
                <td class="dataTableContent" align="center"><?php echo tep_datetime_short($orders['date_purchased']); ?></td>
                <td class="dataTableContent" align="right"><?php echo $orders['orders_status_name']; ?></td>
                <td class="dataTableContent" align="right"><?php if (isset($oInfo) && is_object($oInfo) && ($orders['orders_id'] == $oInfo->orders_id)) { echo tep_image('images/icon_arrow_right.gif', ''); } else { echo '<a href="' . tep_href_link('orders.php', tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '">' . tep_image('images/icon_info.gif', IMAGE_ICON_INFO) . '</a>'; } ?>&nbsp;</td>

add a new column with: $orders['delivery_postcode'].

This should show it for each order.

Add also a heading title in this section to align the table correct:

                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ORDER_TOTAL; ?></td>
                <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_DATE_PURCHASED; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_STATUS; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACTION; ?>&nbsp;</td>

 

[♥14steve14]

Thanks Rainer  @raiwa I will take a look later when time allows. Sounds really simple now.

[♥14steve14]

Found some time to try this and everything works as I hoped it would. Tomorrow will be the test to see whether it makes entering the tracking numbers easier which I hope it will. It took ages today without the postcode being visible. Thanks Rainer.

[Dan Cole]

Are you entering the tracking numbers by hand Steve?  We did that for years until one day....when the team let me near the shipping station, I realized I had the tracking number in my hand standing next to a bar code reader.  I now scan the label bar codes and send out the YOUR ORDER HAD SHIPPED emails as the labels are being printed.   I use a modified batch report to do the actual work.  It has been a tremendous help.

Dan

[♥14steve14]

Hi Dan @Dan Cole

I do still manually enter the tracking numbers as I take all my post to the local post office and pay over the counter. Not the cheapest way I know. I would be interested in seeing or hearing more about how you do this. Its something that is so labour intensive, although not so much now.

I am currently waiting for a call back from Royal Mail about their click and drop service, when I may have to do something different. This has only come about since I had two customers claim non arrival of orders after the 30 days that I keep the certificate of Postings for.

[Dan Cole]

We generate the shipping labels using the shippers software.  In my case it is either Canada Post or Fedex.  The labels have the bar coded tracking numbers on them so it is just a matter of scanning that bar code as the label is being produced.  I then modified one of the osC batch report contributions to accept the tracking number and send out the usual emails and update the order status etc.  It took a task that was getting very time consuming and made it easy, pesy.  Sometimes customers now have the tracking information before we can even get the label on the box.

Dan

[MrPhil]

4 hours ago, Dan Cole said:

Sometimes customers now have the tracking information before we can even get the label on the box.

Email 5 minutes later: "Hey, where's my package??!" 

[Dan Cole]

1 minute ago, MrPhil said:

Email 5 minutes later: "Hey, where's my package??!" 

Haven't had that one asked yet but I have been asked why the shipper doesn't have a record of the shipment.   It is probably an answer that needs to be added to my shipping notification email.

Dan 

[♥14steve14]

I only started thinking about this after I had a customer ask where his order was 3 months after I posted it. No proof of posting or anything as after 30 days I bin them if I have heard nothing from the customer.

Saying that I have had one phone call this week asking where an order is and the order was never made. Items were still in the customers cart and he had not even started the checkout process apparently. This is the trouble I have when my customers are on the older generation.

May be we should start a thread somewhere about silly customer experiences to bring a bit of light relief.

[burt]

4 minutes ago, 14steve14 said:

May be we should start a thread somewhere about silly customer experiences to bring a bit of light relief.

YES!  I have some funny tales...

[Dan Cole]

6 minutes ago, 14steve14 said:

No proof of posting or anything as after 30 days I bin them if I have heard nothing from the customer.

The system I use also records the tracking number with in the order status comments so I always have a record of it.

Dan

[MrPhil]

31 minutes ago, 14steve14 said:

as after 30 days I bin them if I have heard nothing from the customer.

30 days might be a little quick on the draw. I wonder if there is legal precedent to say that's too soon to discard records? I would think that 6 to 12 months' retention of shipping information would stand up in court if someone sued you for non-delivery. Other information on the order should probably be retained for at least 12 months, perhaps longer (especially for warranty periods, not to mention financial records and tax filings). At some point you should be able to reasonably say, "Enough! I've held on to this information for long enough."

[♥14steve14]

1 hour ago, MrPhil said:

30 days might be a little quick on the draw. I wonder if there is legal precedent to say that's too soon to discard records? I would think that 6 to 12 months' retention of shipping information would stand up in court if someone sued you for non-delivery. Other information on the order should probably be retained for at least 12 months, perhaps longer (especially for warranty periods, not to mention financial records and tax filings). At some point you should be able to reasonably say, "Enough! I've held on to this information for long enough."

It's only a certificate of posting. Not needed once the item has been delivered. 

[♥14steve14]

1 hour ago, Dan Cole said:

The system I use also records the tracking number with in the order status comments so I always have a record of it.

Dan

That is what I am doing now. Hopefully having delivery information will also stop those people that will say an item has not arrived and hope to get a refund. 

[♥14steve14]

Best 15 minutes of my time making this change. Already it has saved that when entering tracking numbers. Thanks for all the help and advice.