modifying stats_low_stock.php

[Supertex]

This file provides a list of products with low stock numbers, and allows edits when you click on the item in the list.  The problem I had with it, is that edits  are done with categories.php, and it wouldn't return to the list after the edit.  Instead, it just goes back to to categories.php with no product selected, and you have to hit "back" twice to get to the next item that requires a stock quantity changed.   When you have several items to update, this requirement gets tedious.  The behaviour was explained to me in another thread.  Also, suggestions were made towards an addon that accomplishes something similar.  However this seems like a fairly simple task that would be a good learning experience for me.

 

I used some of the code found in the "product sort order" addon to add a "shipment" field for each row, and a set of action buttons.  Fields are disabled until you press the 'unlock' button, then the fields become editable, and the buttons change to 'back' and 'update'.  I added an entry to functions.php that -should- increase products_quantity by the value of $shipment.

 

I've currently got these changes up in a development environment.   Problem is, when I click 'update' the list doesn't add to existing quantities - It does SOMETHING, but I'm not exactly sure what.  I just get the OSC logo at the top and the body of the page is blank.  I'm not sure I've included the $shipment variable correctly.  Perhaps I need to specify in the code an 'int' type, as I'm not sure how it will work if it gets a NULL from field data.  For all I know...what I've done may be entirely wrong, so don't be shocked if you see foolish things here.  I'm learning as I go.  

 

I'm not hoping for someone to do this for me, but I would certainly appreciate guidance.

 

Here are the edits:

 

First, something to check whether or not the form should be editable, and supply a function that will update the db field.

 

Lines 15-47

//add quantity update action switch for "unlock" and "update" buttons
 if (tep_not_null($action)){
switch ($action){
case 'unlock':
   $qty_edit = true;
break;

case 'update':
        for ($i=0, $n=sizeof($HTTP_POST_VARS['products_id']); $i<$n; $i++) {
            tep_add_product_shipment($HTTP_POST_VARS['products_id'][$i], $HTTP_POST_VARS['shipment'][$i]);
          }
$qty_edit = false;
        tep_redirect(tep_href_link(FILENAME_STATS_LOW_STOCK));
        break;
}
  }
// end quantity update switch edit
?>

<!--This function was added to functions.php for quantity updates

function tep_add_product_shipment($products_id, $shipment) {
   return tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = products_quantity + " . $shipment . " where products_id = '" . (int)$products_id . "'");
}
-->

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

Then an entry for the list's header:

 

<!--quantity update header start-->
                 <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_NEW_SHIPMENT_QTY; ?></td>
<!--quantity update header end-->

An entry for the form field:

 

<!--quantity update field start-->
<td align="center">
                 <?php
                 if ($qty_edit == false){
echo tep_draw_input_field('shipment[]', $shipment, 'SIZE=5 Disabled') . '</td>';
                 }else{
                    echo tep_draw_input_field('shipment[]', $shipment, 'SIZE=5') . tep_draw_hidden_field('products_id[]', $products['products_id']). '</td>';
                }
                 ?>
<!--quantity update field end-->

And finally, an entry for the buttons and their actions:

 

<!--start quantity update action buttons-->
<td align="center" width="15%" valign="top">
<table bgcolor="#cccccc" width="100%" height="150px">
<tr>
<td class="smallText"><center>Once unlocked you may enter new shipment quantities to be added to existing quantities.  Then "update" to save and continue.</center></td>
</tr>
<tr>
<td align="center">
<?php
if ($qty_edit == false){
echo '<a href="' . tep_href_link(FILENAME_STATS_LOW_STOCK, 'cpath=' . $cpath . '&action=unlock') . '">' . tep_image_button('button_unlock.gif', IMAGE_UNLOCK) . '</a>'; /*enable field input*/
} else {
echo  '<a href="' . tep_href_link(FILENAME_STATS_LOW_STOCK, $cPath_back . 'cPath=' . $cPath) . '">' . tep_image_button('button_back.gif', IMAGE_BACK) . '</a>'; /* return to field-disabled state */
echo  '<a href="' . tep_href_link(FILENAME_STATS_LOW_STOCK, 'cpath=' . $cpath . '&action=update') . '">' . tep_image_button('button_update.gif', IMAGE_UPDATE) . '</a>'; /*run query*/
}
?>
</td>
</tr>
</table>
</td>
<!--end quantity update action buttons-->
</tr>
</table>
<?php
  require(DIR_WS_INCLUDES . 'template_bottom.php');
  require(DIR_WS_INCLUDES . 'application_bottom.php');
?>

 

I'm also attaching the file

[Supertex]

Didn't get the file attached, so here it is.

stats_low_stock.php

[phpdeveloper1]

I'm not sure about the quantity updating part but for correctly redirecting maybe you could do something like

if ($action != 'update')
{
  require(DIR_WS_INCLUDES . 'template_top.php');
  }

[Dan Cole]

I'm no coder and haven't messed around with your code at all but this line looks a little suspect to me...line 39.

 

return tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = products_quantity + " . $shipment . " where products_id = '" . (int)$products_id . "'");

 

Does it actually do what you want?

[Supertex]

Thank you for the replies. 

 

 

I'm not sure about the quantity updating part but for correctly redirecting maybe you could do something like

if ($action != 'update')
{
  require(DIR_WS_INCLUDES . 'template_top.php');
  }

 I'll add that in and try it again.

 

 

 

I'm no coder and haven't messed around with your code at all but this line looks a little suspect to me...line 39.

return tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = products_quantity + " . $shipment . " where products_id = '" . (int)$products_id . "'");

Does it actually do what you want?

 

It doesn't alter the db at all, actually.  I click submit, the body of the page turns white, and nothing else happens.  I'd be happy if it goofed the table data up, even.  At least then I could see what it did and start figuring out how to fix it.

 

However, when I use that in phpmyadmin, it does seem to work correctly.

 

Right now, the only things that "work" are that the forms are disabled by default, and they do become active when I click the 'unlock' button, the 'back' button returns them to disabled, and the 'update' does initiate an action...it's just that the action seems to get hung up somewhere.

[Dan Cole]

I know you want to figure this out yourself so I'll just ask questions...things I might try...if that is okay with you?

 

If it is...is anything returned by that function?  What happens if you put the db_query above the return?

[Supertex]

Been tied up for a while...but back on this.

 

In case I hadn't mentioned it - I'm a dummy with code.  I know just enough to wreck something.  

 

I picked that function out of general.php (not functions.php like it mentions in the snippet) because it seemed to behave similarly to what I'm after.  I just modified which fields it works with and what it does to them.  As to whether or not it needs to return anything, no it doesn't.  

 

I'll remove the return entirely and see what happens.

[Supertex]

I've been tinkering with this again in my slow times, but with no luck.  And I mean luck, as I'm way out of my understanding.  My thoughts here were that Geoffrey Walton's Product Sort Order addon does something similar, and so I looked it over carefully and tried to duplicate the method he used.  

 

One major difference I see, is that his code was intended to run in categories.php, in which each line can be 'selected' and so there's a cpath entry in each line detailing button action.  But the page I'm using this on does not have the option to select a line.  This page just checks whether or not a product is active, and whether the corresponding products_quantity is below a given threshold.

 

So I don't know if there are crucial elements missing for what I'm trying to do.

 

Removing the return provided no change at all.  Still just a white page, void of product lines after a "submit."

[♥bruyndoncx]

a post form was missing

submit button at the bottom

and then a way to distinguish when good shipment data was entered

I included the function on top of this file and had to change the reorder field related sql as it doesn't exist in my database, I hardcoded 6 in stead

so that section you will have to change back to your original values

 

<?php
/*
  $Id$

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

  Copyright (c) 2010 osCommerce

  Released under the GNU General Public License
*/

  require('includes/application_top.php');

  require(DIR_WS_INCLUDES . 'template_top.php');
 
  $action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : '');
 
    function tep_add_product_shipment($products_id, $shipment) {
          $sql = "update " . TABLE_PRODUCTS . " set products_quantity = products_quantity + " . $shipment . " where products_id = '" . (int)$products_id . "'";
echo $sql ."\n";
          return tep_db_query($sql);
  }

 
//add quantity update action switch for "unlock" and "update" buttons  
 if (tep_not_null($action)){
    switch ($action){
        case 'unlock':
          $qty_edit = true;
        break;
    }
 }
//print_r($_POST);

 if (isset($HTTP_POST_VARS) && !empty($HTTP_POST_VARS)) {
 // 'update':
        for ($i=0, $n=sizeof($HTTP_POST_VARS['products_id']); $i<$n; $i++) {
            if ( isset($HTTP_POST_VARS['shipment'][$i]) && $HTTP_POST_VARS['shipment'][$i]<> 0 )
            tep_add_product_shipment($HTTP_POST_VARS['products_id'][$i], $HTTP_POST_VARS['shipment'][$i]);
          }
            $qty_edit = false;
        tep_redirect(tep_href_link($PHP_SELF));
        break;
 }
 
// end quantity update switch edit
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
    <tr>
        <td valign="top">
            <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_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
                </tr>
            </table>
        </td>
    </tr>
    <tr>
        <td>
        
<form action="<?php echo $PHP_SELF;?>?action=update" method="post">
 
            <table border="0" width="100%" cellspacing="0" cellpadding="2">
                  <tr>
                    <td valign="top">         
                        <table border="0" width="100%" cellspacing="0" cellpadding="2">
                              <tr class="dataTableHeadingRow" width="80%">
                              <?php
                                  switch ($_GET['listing'])  {
                                    case "name":
                                    $order = "pd.products_name";
                                    break;
                                    case "name-dsc":
                                    $order = "pd.products_name DESC";
                                    break;
                                      case "manuf":
                                      $order = "m.manufacturers_name, p.products_model";
                                      break;
                                      case "manuf-dsc":
                                      $order = "m.manufacturers_name, p.products_model DESC";
                                      break;
                                      case "qty":
                                      $order = "p.products_quantity, p.products_model";
                                      break;
                                      case "qty-dsc":
                                      $order = "p.products_quantity, p.products_model DESC";
                                      break;
                                    case "model-dsc":
                                    $order = "p.products_model DESC";
                                    break;
                                     default:
                                      $order = "p.products_model";
                                 }
                             ?>
                                <td class="dataTableHeadingContent" align="left"><a href="<?php echo tep_href_link(FILENAME_STATS_LOW_STOCK, tep_get_all_get_params(array('listing')) . 'listing=name'); ?>"><?php echo tep_image(DIR_WS_IMAGES . 'icon_up.gif', SORT_BY_PRODUCTS_NAME); ?></a> <a href="<?php echo tep_href_link(FILENAME_STATS_LOW_STOCK, tep_get_all_get_params(array('listing')) . 'listing=name-dsc'); ?>"><?php echo tep_image(DIR_WS_IMAGES . 'icon_down.gif', SORT_BY_PRODUCTS_NAME_DESC); ?></a><?php echo TABLE_HEADING_PRODUCTS; ?></td>
                                <td class="dataTableHeadingContent" align="center"><a href="<?php echo tep_href_link(FILENAME_STATS_LOW_STOCK, tep_get_all_get_params(array('listing')) . 'listing=manuf'); ?>"><?php echo tep_image(DIR_WS_IMAGES . 'icon_up.gif', SORT_BY_PRODUCTS_MANUFACTURER); ?></a> <a href="<?php echo tep_href_link(FILENAME_STATS_LOW_STOCK, tep_get_all_get_params(array('listing')) . 'listing=manuf-dsc'); ?>"><?php echo tep_image(DIR_WS_IMAGES . 'icon_down.gif', SORT_BY_PRODUCTS_MANUFACTURER_DESC); ?></a><?php echo TABLE_HEADING_MANUFACTURER; ?></td>
                                <td class="dataTableHeadingContent" align="center"><a href="<?php echo tep_href_link(FILENAME_STATS_LOW_STOCK, tep_get_all_get_params(array('listing')) . 'listing=model'); ?>"><?php echo tep_image(DIR_WS_IMAGES . 'icon_up.gif', SORT_BY_PRODUCTS_MODEL); ?></a> <a href="<?php echo tep_href_link(FILENAME_STATS_LOW_STOCK, tep_get_all_get_params(array('listing')) . 'listing=model-dsc'); ?>"><?php echo tep_image(DIR_WS_IMAGES . 'icon_down.gif', SORT_BY_PRODUCTS_MODEL_DESC); ?></a><?php echo TABLE_HEADING_MODEL; ?></td>
                                <td class="dataTableHeadingContent" align="center"><a href="<?php echo tep_href_link(FILENAME_STATS_LOW_STOCK, tep_get_all_get_params(array('listing')) . 'listing=qty'); ?>"><?php echo tep_image(DIR_WS_IMAGES . 'icon_up.gif', SORT_BY_PRODUCTS_QUANTITY); ?></a> <a href="<?php echo tep_href_link(FILENAME_STATS_LOW_STOCK, tep_get_all_get_params(array('listing')) . 'listing=qty-dsc'); ?>"><?php echo tep_image(DIR_WS_IMAGES . 'icon_down.gif', SORT_BY_PRODUCTS_QUANTITY_DESC); ?></a><?php echo TABLE_HEADING_QTY_LEFT; ?> </td>
<!--quantity update header start-->
                                <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_NEW_SHIPMENT_QTY; ?></td>
<!--quantity update header end-->
                              </tr>
<?php
  if ($HTTP_GET_VARS['page'] > 1) $rows = $HTTP_GET_VARS['page'] * 20 - 20;
  $products_query_raw = strtolower(
    "select p.products_id, p.products_quantity, 6 as products_reorder, p.products_model, p.manufacturers_id, pd.products_name, m.manufacturers_id, m.manufacturers_name ".
    " from ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS." p JOIN " .TABLE_MANUFACTURERS." m ON (p.manufacturers_id=m.manufacturers_id) " .
    " where p.products_id = pd.products_id ".
    "   and p.products_status=1 " . //edit to include non-active products.
    "   and pd.language_id = '".$languages_id."'".
    "   and ((p.products_quantity <= '" . STOCK_REORDER_LEVEL ."')".
    "   or (2 > p.products_quantity )) and products_model like 'ADE %' ".
    " group by pd.products_id".
    " order by $order");
  $products_split = new splitPageResults($HTTP_GET_VARS['page'], 1000, $products_query_raw, $products_query_numrows);
  $products_query = tep_db_query($products_query_raw);
  while ($products = tep_db_fetch_array($products_query)) {
    $rows++;

    if (strlen($rows) < 2) {
      $rows = '0' . $rows;
    }

    $pq=$products['products_quantity'];
    $the_link=tep_href_link(FILENAME_CATEGORIES, 'action=new_product&pID=' . $products['products_id'], 'NONSSL');
    $the_row_class=$pq <= 0 ? "messageStackError" : "messageStackWarning";
    
?>          
                              <tr class="<?php echo $the_row_class; ?>" onmouseover="this.className='datatableRowOver';this.style.cursor='hand'" onmouseout="this.className='<?php echo $the_row_class; ?>'"><!-- onclick="document.location.href='<?php echo $the_link; ?>'"-->
                                <td class="smallText dataTableContent" align="left"> <?php echo '<a href="' . $the_link . '" class="blacklink">' . $products['products_name'] . '</a>'; ?> </td>
                                <td class="smallText dataTableContent" align="center"> <?php echo $products['manufacturers_name']; ?> </td>
                                <td class="smallText dataTableContent" align="center"> <?php echo '<a href="' . $the_link . '" class="blacklink">' . $products['products_model'] . '</a>'; ?> </td>
                                <td class="smallText dataTableContent" align="center"> <?php echo  $pq; ?> </td>
<!--quantity update field start-->
                                <td align="center">
                                <?php
                                    if ($qty_edit == false){
                                    echo tep_draw_input_field('shipment[]', $shipment, 'SIZE=5 Disabled') . '</td>';
                                }else{
                                       echo tep_draw_input_field('shipment[]', $shipment, 'SIZE=5') . tep_draw_hidden_field('products_id[]', $products['products_id']). '</td>';
                                   }
                                ?>
<!--quantity update field end-->
                              </tr>
                                <?php
                                  }
                                ?>
                          </table>
                      </td>
                </tr>
                <tr>
                    <td colspan="3" align="right">
                 <input type="submit" value="Submit">

                        <table border="0" width="100%" cellspacing="0" cellpadding="2">
                              <tr>
                                <td class="smallText" valign="top"><?php echo $products_split->display_count($products_query_numrows, 1000, $HTTP_GET_VARS['page'], TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
                                <td class="smallText" align="right"><?php echo $products_split->display_links($products_query_numrows, 1000, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page']); ?> </td>
                              </tr>
                        </table>
                    </td>
                  </tr>
            </table>
        </td>
<!--start quantity update action buttons-->
        <td align="center" width="15%" valign="top">
            <table bgcolor="#cccccc" width="100%" height="150px">
                <tr>
                    <td class="smallText"><center>Once unlocked you may enter new shipment quantities to be added to existing quantities.  Then "update" to save and continue.</center></td>
                </tr>
                <tr>
                    <td align="center">
                    <?php
                        if ($qty_edit == false){
                            echo '<a href="' . tep_href_link($PHP_SELF, 'cpath=' . $cpath . '&action=unlock') . '">' . tep_image_button('button_unlock.gif', IMAGE_UNLOCK) . '</a>'; /*enable field input*/
                        } else {
                            echo  '<a href="' . tep_href_link($PHP_SELF, $cPath_back . 'cPath=' . $cPath) . '">' . tep_image_button('button_back.gif', IMAGE_BACK) . '</a>'; /* return to field-disabled state */
                            echo  '<a href="' . tep_href_link($PHP_SELF, 'cpath=' . $cpath . '&action=update') . '">' . tep_image_button('button_update.gif', IMAGE_UPDATE) . '</a>'; /*run query*/
                        }
                    ?>
                    </td>
                </tr>
            </table>
      
      </form>
        </td>
<!--end quantity update action buttons-->
    </tr>
</table>
<?php
  require(DIR_WS_INCLUDES . 'template_bottom.php');
  require(DIR_WS_INCLUDES . 'application_bottom.php');
?>
  

[Supertex]

Thank you, Carine.  You've saved what little hair I had left ;)  

 

So must the submit button directly follow the form?  I assumed that my "Update" button would handle that.

 

And should I move the function to general.php, or is it ok to leave it as is? 

[♥bruyndoncx]

the submit button needs to be within  the form tags

your update button is just a link with some parameters, it doesn't do a form submission

 

the function, you can just leave it in here , unless you use it on other pages too