Invoice modification

[quicklyshop]

Hi

I sell computer hardware and on every sale i have to pass serial number of products to the invoice. So i need to be able to modify the invoices. Is there any contribution that will help.

 

My second problem is Telephone orders or email orders. Simply I need a POS which will let me enter orders of customers even if they dont have an account on the site.

[netguru]

So why not ask them create an account?

[quicklyshop]

Okey we solved the pos problem. but i still couldnt solve the problem on entering serial numbers of items to invoices

[lleahu]

ok.

 

I have arrived at the same position you are at quicklyshop, and i will write the mod.

 

so we need to display serial #s on the invoice.

 

where do you imagine you will want to enter the serial # into the system at? on what screen?

 

my initial thought is to have a 'enter serial #' or 'edit serial #' link next to each item on the order review page under the customer section, in the admin site.

 

you would click on 'enter serial #' if no serial # exists and click on 'edit serial #' if there is already a serial # for that product.

 

when you print out the invoice and/or packing slip, the links wont show up.

 

the serial # will appear some like this in the product attributes section:

" -- product serial number: xxxxxxxxxxx "

 

did you have something else in mind?

[quicklyshop]

Hi

That was what i had in mind. But implementing it to invoice looks like hell.

 

We can add an extra column for each line on the invoice in mysql database. And if this column value is null, invoice will show a link next to each to product " Add Serial# ". If the column value is not null it will show serial# next to the product on each line in invoice.

I assume this contribution will make so many OSC Citizen Happy if someone puts it to action

[lleahu]

okey dokey then.

 

give a few - im gonna start on this now.

 

btw, im using the 20030203 daily snapshot as my codebase - hope thats not going to cause problems for you.

[quicklyshop]

ILEHU is working really hard for this community so he deserved dozen of King Size Jam Donuts (hmmm yummie :P )

[Ajeh]

*scratch, scratch, scratch*

 

Take that off the things to do list ... :onfire:

 

Bless you lleahu!

[lleahu]

This module been posted to the oscommerce download site.

 

Please test and send all feedback back to me so I can keep this a top-notch mod.

 

 

8)

 

 

http://www.oscommerce.com/community/contributions,946

[strictlypc]

Hi Everyone,

 

I found an error on the Serial Number Contribution that I would like to share with everybody. In adminorders.php

 

The first change to this file should be:

// BEGIN - Product Serial Numbers

   case 'update_serial':

     $oID = tep_db_prepare_input($HTTP_GET_VARS['oID']);

     $serial_number = tep_db_prepare_input($HTTP_POST_VARS['serial_number']);

     $serial = tep_db_prepare_input($HTTP_GET_VARS['serial']);



     tep_db_query("update " . TABLE_ORDERS_PRODUCTS . " set products_serial_number = '" . tep_db_input($serial_number) . "' where orders_id = '" . tep_db_input($oID) . "'");



      $messageStack->add_session(SUCCESS_ORDER_UPDATED, 'success');



     tep_redirect(tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('action')) . 'action=edit&serial_updated=1'));

     break;

// END - Product Serial Numbers

 

AND NOT:

// BEGIN - Product Serial Numbers

   case 'update_serial':

     $oID = tep_db_prepare_input($HTTP_GET_VARS['oID']);

     $serial_number = tep_db_prepare_input($HTTP_POST_VARS['serial_number']);

     $serial = tep_db_prepare_input($HTTP_GET_VARS['serial']);



     tep_db_query("update " . TABLE_ORDERS_PRODUCTS . " set products_serial_number = '" . tep_db_input($serial_number) . "' where orders_id = '" . tep_db_input($oID) . "' and products_id=" . tep_db_input($serial));



      $messageStack->add_session(SUCCESS_ORDER_UPDATED, 'success');



     tep_redirect(tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('action')) . 'action=edit&serial_updated=1'));

     break;

// END - Product Serial Numbers

 

Notice the difference in the two codes? The original code gave me an error, so i modified it and now it works!

 

Thanks for a great contribution lleahu,

 

David

[lleahu]

could you be more specific about the error you recieved.

 

i will go check my code again, but from a fast glance, your update would change the serial numbers for all the prodcuts in the orders, not a specific product in that order.

[strictlypc]

Hi lleahu,

 

The error I got with the original code was:

 

1064 - You have an error in your SQL syntax near '' at line 1



update orders_products set products_serial_number = '123456789' where orders_id = '10' and products_id=



[TEP STOP]

 

Thanks,

David

[johnglobal]

Not sure what this mod does

Does this allow you to have a part number (sku) for each product ?

I am guessing that the part number is then displayed on the email invoice

?

 

Anyone and demo ?

Thanks

John

[lleahu]

Not sure what this mod does

Does this allow you to have a part number (sku) for each product ?

I am guessing that the part number is then displayed on the email invoice

?

 

Anyone and demo ?

Thanks

John

 

 

 

if you sell a piece of hardware such as a computer firewall or software or something, you can enter the serial # for that product right on the invoice before you send it out.

[lleahu]

Hi lleahu,

 

The error I got with the original code was:

 

1064 - You have an error in your SQL syntax near '' at line 1



update orders_products set products_serial_number = '123456789' where orders_id = '10' and products_id=



[TEP STOP]

 

Thanks,

David

 

what snapshot date are you running?

 

please check to make sure you have these things in your store:

 

1. on or around line #270 in admin/orders.php you should see:

 

     	 tep_draw_form('serial_form', FILENAME_ORDERS, 'action=update_serial&oID=' . $oID . '&serial=' . $order->products[$i]['id'], 'post', '') .

 

2. on or around line #95 in admin/includes/classes/order.php you should see:

 

                                        'id' => $orders_products['products_id'],

 

3. on or around line #83 in admin/includes/classes/order.php you should see:

 

      $orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price, products_serial_number from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . tep_db_input($order_id) . "'");

 

4. check the table 'orders_products' in your database to make sure there really is values in the field 'products_id'.

 

[email protected]

[johnglobal]

Thanks Lee

[strictlypc]

Hi lleahu,

 

Thanks, That did the trick. I was missing "product_id" in admin/includes/classes/order.php.

 

Thanks for your help,

David

[Guest]

Help. I tried adding this and can not get it to work. I have a snapshot from Feb 6, 2003

 

I edited all the files like described and re-uploaded them to my server.

 

When I click on an order from admin I get this error:

 

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

 

select customers_name, customers_company, 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_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, cc_type, cc_owner, cc_number, cc_expires, comments, currency, currency_value, date_purchased, orders_status, last_modified from orders where orders_id = '6'

 

[TEP STOP]

 

Help. This looks like a real useful feature.

[krs]

It's great.

 

But then i'm thinking that in 6 month someone calls and say he has a RMA request for a product he bought. How do i find the right invoice again, so i can check that he really bought it on my shop, and that it is still under garanti. Need some search methode in there also.

 

Have you done some for this already or ?

 

Kristian in DK

[Guest]

Hi

 

Great contribution, this one will really help me out, thanks to all those involved, I've installed the mod and it appears to be working fully, however I get the following error code in the orders page in admin

 

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

 

select new_value, old_value, date_added, customer_notified from orders_status_history where orders_id = '2' order by orders_status_history_id desc

 

[TEP STOP]

 

 

I'm running it on loaded 5

 

Anyone got any ideas on why this error code is showing up :?:

 

Thanks

 

Andy

[Guest]

Hi

 

found a possible problem, I don't know if anyone else has experienced this but when I click on the link from a confirmed order email it trys to take you to the order as it should and it returns this error

 

Parse error: parse error, unexpected T_ELSE in /home/black-th/public_html/online/account_history_info.php on line 150

 

I have the RMA feature installed and running fine on Loaded 5, however this could be a conflict between the two scripts perhaps?

 

my account_history_info.php looks like this - Appologies in advance for the long post

 

<?php

/*

 $Id: account_history_info.php,v 1.91 2003/01/09 15:55:00 hpdl Exp $



 osCommerce, Open Source E-Commerce Solutions

 http://www.oscommerce.com



 Copyright (c) 2002 osCommerce



 Released under the GNU General Public License

*/



 require('includes/application_top.php');



 if (!tep_session_is_registered('customer_id')) {

   $navigation->set_snapshot();

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

 }



 $customer_number_query = tep_db_query("select customers_id from " . TABLE_ORDERS . " where orders_id = '". tep_db_input(tep_db_prepare_input($HTTP_GET_VARS['order_id'])) . "'");

 $customer_number = tep_db_fetch_array($customer_number_query);

 if ($customer_number['customers_id'] != $customer_id) {

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

 }



 require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_ACCOUNT_HISTORY_INFO);



 $breadcrumb->add(NAVBAR_TITLE_1, tep_href_link(FILENAME_ACCOUNT, '', 'SSL'));

 $breadcrumb->add(NAVBAR_TITLE_2, tep_href_link(FILENAME_ACCOUNT_HISTORY, '', 'SSL'));

 $breadcrumb->add(NAVBAR_TITLE_3, tep_href_link(FILENAME_ACCOUNT_HISTORY_INFO, 'order_id=' . $HTTP_GET_VARS['order_id'], 'SSL'));



 require(DIR_WS_CLASSES . 'order.php');

 $order = new order($HTTP_GET_VARS['order_id']);

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">

<html <?php echo HTML_PARAMS; ?>>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">

<?php

// BOF: WebMakers.com Changed: Header Tag Controller v1.0

// Replaced by header_tags.php

if ( file_exists(DIR_WS_INCLUDES . 'header_tags.php') ) {

 require(DIR_WS_INCLUDES . 'header_tags.php');

} else {

?> 

<title><?php echo TITLE; ?></title>

<?php

}

// EOF: WebMakers.com Changed: Header Tag Controller v1.0

?>

<base href="<?php echo (getenv('HTTPS') == 'on' ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>">

<link rel="stylesheet" type="text/css" href="stylesheet.css">

</head>

<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0">

<!-- header //-->

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<!-- header_eof //-->



<!-- body //-->

<table border="0" width="100%" cellspacing="3" cellpadding="3">

 <tr>

   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">

<!-- left_navigation //-->

<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>

<!-- left_navigation_eof //-->

   </table></td>

<!-- body_text //-->

   <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

     <tr>

       <td><table border="0" width="100%" cellspacing="0" cellpadding="0">

         <tr>

           <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>

           <td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . 'table_background_history.gif', HEADING_TITLE, HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>

         </tr>

       </table></td>

     </tr>

     <tr>

       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

     </tr>

     <tr>

       <td><table border="0" width="100%" cellspacing="0" cellpadding="2">

         <tr>

           <td class="main" colspan="2"><b><?php echo sprintf(HEADING_ORDER_NUMBER, $HTTP_GET_VARS['order_id']) . ' <small>(' . $order->info['orders_status'] . ')</small>'; ?></b></td>

         </tr>

         <tr>

           <td class="smallText"><?php echo HEADING_ORDER_DATE . ' ' . tep_date_long($order->info['date_purchased']); ?></td>

           <td class="smallText" align="right"><?php echo HEADING_ORDER_TOTAL . ' ' . $order->info['total']; ?></td>

         </tr>

       </table></td>

     </tr>

     <tr>

       <td><table border="0" width="100%" cellspacing="1" cellpadding="2" class="infoBox">

         <tr class="infoBoxContents">

<?php

 if ($order->delivery != false) {

?>

           <td width="30%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">

             <tr>

               <td class="main"><b><?php echo HEADING_DELIVERY_ADDRESS; ?></b></td>

             </tr>

             <tr>

               <td class="main"><?php echo tep_address_format($order->delivery['format_id'], $order->delivery, 1, ' ', '<br>'); ?></td>

             </tr>

<?php

   if ($order->info['shipping_method']) {

?>

             <tr>

               <td class="main"><b><?php echo HEADING_SHIPPING_METHOD; ?></b></td>

             </tr>

             <tr>

               <td class="main"><?php echo $order->info['shipping_method']; ?></td>

             </tr>

<?php

   }

?>

           </table></td>

<?php

 }

?>

           <td width="<?php echo (($order->delivery != false) ? '70%' : '100%'); ?>" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

             <tr>

               <td><table border="0" width="100%" cellspacing="0" cellpadding="2">

<?php

 if (sizeof($order->info['tax_groups']) > 1) {

?>

                 <tr>

                   <td class="main" colspan="2"><b><?php echo HEADING_PRODUCTS; ?></b></td>

                   <td class="smallText" align="right"><b><?php echo HEADING_TAX; ?></b></td>

                   <td class="smallText" align="right"><b><?php echo HEADING_TOTAL; ?></b></td>

                 </tr>

<?php

 } else {

?>

                 <tr>

                   <td class="main" colspan="3"><b><?php echo HEADING_PRODUCTS; ?></b></td>

                 </tr>

<?php

 }



 $size = sizeof($order->products);

 for ($i=0; $i<$size; $i++) {

$rma_query_one = tep_db_query("SELECT returns_id FROM " . TABLE_RETURNS_PRODUCTS_DATA . " where products_id = '" . $order->products[$i]['id'] . "' and order_id = '" . $HTTP_GET_VARS['order_id'] . "'");

$rma_query = tep_db_fetch_array($rma_query_one);

$rma_number_query = tep_db_query("SELECT rma_value FROM " . TABLE_RETURNS . " where returns_id = '" . $rma_query['returns_id'] . "'");

$rma_result = tep_db_fetch_array($rma_number_query);



$return_link = '<font color=blue><b>Return # <a href="returns_track.php?action=returns_show&rma=' . $rma_result['rma_value'] . '">' . $rma_result['rma_value'] . '</a>';





} else {

$return_link = '<a href="' . tep_href_link(FILENAME_RETURN, 'order_id=' . $HTTP_GET_VARS['order_id'] . '&products_id=' . ($order->products[$i]['id']), 'SSL') . '"><font color=red><b>Return this product</a></b></font>';

}

   echo '          <tr>' . "n" .

        '            <td class="main" align="right" valign="top" width="30">' . $order->products[$i]['qty'] . ' x</td>' . "n" .

        '            <td class="main" valign="top">' . $order->products[$i]['name'];



// BEGIN - Product Serial Numbers

  if (tep_not_null($order->products[$i]['serial_number'])) echo '  (Serial #: ' . $order->products[$i]['serial_number'] . ')';

// END - Product Serial Numbers



   if ( (isset($order->products[$i]['attributes'])) && (sizeof($order->products[$i]['attributes']) > 0) ) {

     $sizea = sizeof($order->products[$i]['attributes']);

     for ($j=0; $j<$sizea; $j++) {

       echo '<br><nobr><small> <i> - ' . $order->products[$i]['attributes'][$j]['option'] . ': ' . $order->products[$i]['attributes'][$j]['value'] . '</i></small></nobr>';

     }

   }



   echo '</td>' . "n";



   if (sizeof($order->info['tax_groups']) > 1) echo '            <td class="main" valign="top" align="right">' . tep_display_tax_value($order->products[$i]['tax']) . '%</td>' . "n";



   echo '            <td class="main" align="right" valign="top">' . $currencies->format(tep_add_tax($order->products[$i]['final_price'], $order->products[$i]['tax']) * $order->products[$i]['qty'], true, $order->info['currency'], $order->info['currency_value']) . '</td>' . "n" .

        '          </tr>' . "n";

 }

?>

               </table></td>

             </tr>

           </table></td>

         </tr>

       </table></td>

     </tr>

     <tr>

       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

     </tr>

     <tr>

       <td class="main"><b><?php echo HEADING_BILLING_INFORMATION; ?></b></td>

     </tr>

     <tr>

       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

     </tr>

     <tr>

       <td><table border="0" width="100%" cellspacing="1" cellpadding="2" class="infoBox">

         <tr class="infoBoxContents">

           <td width="30%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">

             <tr>

               <td class="main"><b><?php echo HEADING_BILLING_ADDRESS; ?></b></td>

             </tr>

             <tr>

               <td class="main"><?php echo tep_address_format($order->billing['format_id'], $order->billing, 1, ' ', '<br>'); ?></td>

             </tr>

             <tr>

               <td class="main"><b><?php echo HEADING_PAYMENT_METHOD; ?></b></td>

             </tr>

             <tr>

               <td class="main"><?php echo $order->info['payment_method']; ?></td>

             </tr>

           </table></td>

           <td width="70%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">

<?php

 $size = sizeof($order->totals);

 for ($i=0; $i<$size; $i++) {

   echo '              <tr>' . "n" .

        '                <td class="main" align="right" width="100%">' . $order->totals[$i]['title'] . '</td>' . "n" .

        '                <td class="main" align="right">' . $order->totals[$i]['text'] . '</td>' . "n" .

        '              </tr>' . "n";

 }

?>

           </table></td>

         </tr>

       </table></td>

     </tr>

     <tr>

       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

     </tr>

     <tr>

       <td class="main"><b><?php echo HEADING_ORDER_STATUS; ?></b></td>

     </tr>

     <tr>

       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

     </tr>

     <tr>

       <td><table border="0" width="100%" cellspacing="1" cellpadding="2" class="infoBox">

         <tr class="infoBoxContents">

           <td width="30%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">

<?php

 $statuses_query = tep_db_query("select os.orders_status_name, osh.date_added from " . TABLE_ORDERS_STATUS . " os, " . TABLE_ORDERS_STATUS_HISTORY . " osh where osh.orders_id = '" . $HTTP_GET_VARS['order_id'] . "' and osh.new_value = os.orders_status_id and os.language_id = '" . $languages_id . "' order by osh.date_added desc");

 while ($statuses = tep_db_fetch_array($statuses_query)) {

   echo '              <tr>' . "n" .

        '                <td class="main">' . tep_date_short($statuses['date_added']) . '</td>' . "n" .

        '                <td class="main">' . $statuses['orders_status_name'] . '</td>' . "n" .

        '              </tr>' . "n";

 }

?>

           </table></td>

           <td width="70%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">

             <tr>

               <td class="main"><b><?php echo HEADING_COMMENT; ?></b></td>

             </tr>

             <tr>

               <td class="main"><?php echo ((strlen($order->info['comments']) > 0) ? nl2br($order->info['comments']) : '<i>' . TEXT_NO_COMMENTS_AVAILABLE . '</i>'); ?></td>

             </tr>

           </table></td>

         </tr>

       </table></td>

     </tr>

<?php

 if (DOWNLOAD_ENABLED == 'true') include(DIR_WS_MODULES . 'downloads.php');

?>

     <tr>

       <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

     </tr>

     <?php

 $check_status_query = tep_db_query("select orders_status from " . TABLE_ORDERS . " where orders_id = '" . tep_db_input($order_id) . "'");

 $check_status = tep_db_fetch_array($check_status_query);

 if ($check_status['orders_status'] <= ORDER_CANCEL_CHANGE_ID) {

?>

         <tr>

         <td class="main" align="right"><?php echo '<a href="' . tep_href_link(FILENAME_RESTORE_ORDER, 'oID=' . $HTTP_GET_VARS['order_id']) . '">' . tep_image_button('button_restore_order.gif', 'Change/Cancel') . '</a>'; ?>    <?php echo '<a href="' . tep_href_link(FILENAME_ACCOUNT_HISTORY, tep_get_all_get_params(array('order_id')), 'SSL') . '">' . tep_image_button('button_back.gif', IMAGE_BUTTON_BACK) . '</a>'; ?></td>

         </tr>

<?php

} else {

?>



     <tr>

       <td align="right" class="main"><?php echo '<a href="' . tep_href_link(FILENAME_ACCOUNT_HISTORY, tep_get_all_get_params(array('order_id')), 'SSL') . '">' . tep_image_button('button_back.gif', IMAGE_BUTTON_BACK) . '</a>'; ?></td>

     </tr>

<?php

}

?>

   </table></td>

<!-- body_text_eof //-->

   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">

<!-- right_navigation //-->

<?php require(DIR_WS_INCLUDES . 'column_right.php'); ?>

<!-- right_navigation_eof //-->

   </table></td>

 </tr>

</table>

<!-- body_eof //-->



<!-- footer //-->

<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>

<!-- footer_eof //-->

<br>

</body>

</html>

<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

 

As I said, appologies for the long post, I'm not all that great with php and wasn't sure which part would be the most relevant, if I cut a bit of the page scripting out it could be this part thats the problem.

 

Any help on this would be greatly appreciated as this is a great addon and would add a great deal of functionality to the store

 

Best Regards

 

Andy

[tails2k]

Hello... installed the mod for ms2.... got the following error

 

 

Parse error: parse error in /var/www/osc/admin/orders.php on line 90

 

 

I just dropped the files in....

[Laurent]

@tails2k

 

is there a

 

}

 

Delete it and than it works fine.

[extreme12]

hi!

 

I have installed the mod and updated the database, but nothing is shown on the invoice!! How do I type in the serial number on the invoice?

[Guest]

I have the same problem. Just installed the contribution and I can't find a way to add the serial number unless I add it manually into the database.

 

I'm using OSC 2.2 MS2

 

Can anyone help?

 

Thanks

Rob