Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18
  1. 14 Dec 2007, 01:23 PM #11
    Stanley Krute
    Stanley Krute is offline New Zenner
    Join Date
    Feb 2007
    Posts
    13
    Plugin Contributions
    0

    Default Re: Inventory Report

    I had a problem in that the Inventory Report link in the Admin Reports would report a page error 500.
    The web server (Abyss) log showed a parsing error:
    PHP Parse error: syntax error, unexpected '}' in \admin\stats_inventory_report.php on line 191

    Line 191 is purely this

    <?php } ?>

    I commented it out and it now works "fine".
    I know the code is/was ok as it works on another machine, so anyone have an idea why the webserver would not like this?
    It might be because line 182 starts out as

    <?

    rather than as

    <?php

    So you might try changing that line and see how things go.

    -- stan
    Reply With Quote Reply With Quote
  2. 14 Dec 2007, 02:04 PM #12
    torvista's Avatar
    torvista
    torvista is offline Totally Zenned
    Join Date
    Aug 2007
    Location
    Gijón, Asturias, Spain
    Posts
    2,831
    Plugin Contributions
    31

    Default Re: Inventory Report

    Hi Stan,
    yes, the missing "php" on line 182 was causing the problem.

    Thanks very much.

    regards
    Steve
    Reply With Quote Reply With Quote
  3. 18 Dec 2007, 07:48 PM #13
    damonp
    damonp is offline New Zenner
    Join Date
    Mar 2004
    Posts
    60
    Plugin Contributions
    0

    Default Re: Inventory Report

    I have added the products_model field to the listing output. If you’d like to test it out, PM me your email address and I’ll reply with the code. Once it has been successfully installed on several sites without issue, I’ll prepare a new general release.
    damonparker.org
    freelance web app dev
    ZenCart Sites: pimpcostumes.com | platinumcostumes.com | costumeshoes.com
    Reply With Quote Reply With Quote
  4. 1 Jan 2008, 06:42 PM #14
    sam_m
    sam_m is offline New Zenner
    Join Date
    Nov 2007
    Posts
    71
    Plugin Contributions
    0

    Default Re: Inventory Report

    Just want to say thank you for this Contribution, exactly what I was looking for
    Reply With Quote Reply With Quote
  5. 21 Jul 2009, 02:21 AM #15
    javaman
    javaman is offline New Zenner
    Join Date
    Jul 2009
    Posts
    7
    Plugin Contributions
    1

    Default Re: Inventory Report

    I updated the inventory report if anyone is interested (just the main file: stats_inventory_report.php) to work with products with attributes inventory. I also added the full product category breadcrumb and model as well. Also made subcategories work recursively.

    Code:
    <?php
//
// +----------------------------------------------------------------------+
// |zen-cart Open Source E-commerce                                       |
// +----------------------------------------------------------------------+
// | Copyright (c) 2003 The zen-cart developers                           |
// |                                                                      |
// | http://www.zen-cart.com/index.php                                    |
// |                                                                      |
// | Portions Copyright (c) 2003 osCommerce                               |
// +----------------------------------------------------------------------+
// | This source file is subject to version 2.0 of the GPL license,       |
// | that is bundled with this package in the file LICENSE, and is        |
// | available through the world-wide-web at the following url:           |
// | http://www.zen-cart.com/license/2_0.txt.                             |
// | If you did not receive a copy of the zen-cart license and are unable |
// | to obtain it through the world-wide-web, please send a note to       |
// | [email protected] so we can mail you a copy immediately.          |
// +----------------------------------------------------------------------+
//  $Id: stats_inventory_report.php 13 2007-03-14 00:53:35Z damonp $
//

/*
** $Id: stats_inventory_report.php 13 2007-03-14 00:53:35Z damonp $
**
** ZenCart Inventory Report Copyright (c) 2007 damonparker.org <[email protected]>
**
*/

/*
	History:
	07/20/09 19:00 Richard Sandoz
	integrated with attributes with inventory model
	added full breadcrumb for products category
	added product model
	when selecting a category, includes subcategories in report result set
	
	03/13/07 15:28
	switched to master_categories_id for category filter
	added master category column
	added url parameters to paginate link

	03/08/07 17:55
	Initial release

*/

	require('includes/application_top.php');
	require(DIR_WS_CLASSES . 'currencies.php');
	$currencies = new currencies();


  function get_attribute_names($attributes_ids) {
    global $db;
    if ($attributes_ids == '')
      return '';
    $query = "select pov.products_options_values_name
                   , pa.options_values_id
              from products_attributes pa
                   inner join products_options_values pov on (pov.products_options_values_id = pa.options_values_id)
              where pa.products_attributes_id in (".$attributes_ids.")";
    $result_set = $db->Execute($query);
    $result = '';
    while (!$result_set->EOF) {
      $options_values_id = $result_set->fields['products_options_values_name'];
      $result .= ($result==''?'':',').$options_values_id;
      $result_set->MoveNext();
    }
    return $result;
  }

  function get_all_children_categories($categories_id) {
    global $db;
    $query = "select categories_id from categories where parent_id = ".$categories_id;
    $result_set = $db->Execute($query);
    $child_set = $categories_id;
    while (!$result_set->EOF) {
      $child_categories_id = $result_set->fields['categories_id'];
      $child_set .= ','.get_all_children_categories($child_categories_id);
      $result_set->MoveNext();
    }
    $children_categories_ids = "'".$categories_id."'";
    return $child_set;
  }


	$cat	= $_GET['cat'];
	$dir 	= $_GET['dir'];
	$sort 	= $_GET['sort'];

	$cat = $cat == '0' ? '':$cat;

	if($cat != '')	{
		$db_category_where = " WHERE master_categories_id in (".get_all_children_categories($cat).") ";
	}

	$dir 		= $dir == '' ? 'ASC':$dir;
	$op_dir	= $dir == 'ASC' ? 'DESC':'ASC';
	$sort 		= $sort == '' ? 'cd.categories_name':$sort;

	$dir_id = $dir_model = $dir_name = $dir_type = $dir_category = $dir_price = $dir_quantity = $dir_subquantity = $dir_total = 'DESC';

	switch($sort)	{
	case('p.products_id'):
		$dir_id = $op_dir;
		break;
	case('products_model'):
		$dir_model = $op_dir;
		break;
	case('products_name'):
		$dir_name = $op_dir;
		break;
	case('products_type'):
		$dir_type = $op_dir;
		break;
	case('products_price'):
		$dir_price = $op_dir;
		break;
	case('products_quantity'):
		$dir_quantity = $op_dir;
		break;
	case('products_subquantity'):
		$dir_subquantity = $op_dir;
		break;
	case('cd.categories_name'):
		$dir_category = $op_dir;
		break;
	case('total'):
		$dir_total = $op_dir;
		break;
	}


?>
<!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; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script type="text/javascript">
  <!--
  function init()
  {
    cssjsmenu('navbar');
    if (document.getElementById)
    {
      var kill = document.getElementById('hoverJS');
      kill.disabled = true;
    }
  }
  // -->
</script>
</head>
<body onload="init()">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <tr>
<!-- 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 zen_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td><a href="./stats_inventory_report.php?page=all&sort=<?php echo $sort; ?>&dir=<?php echo $dir; ?>&cat=<?php echo $cat; ?>">Show All</a>&nbsp;&nbsp;|&nbsp;&nbsp;<a href="./stats_inventory_report.php?sort=<?php echo $sort; ?>&dir=<?php echo $dir; ?>&cat=<?php echo $cat; ?>">Paginate</a></td>
        <td align="right"><form method="get" action="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=<?php echo $sort; ?>&dir=<?php echo $dir; ?>&cat=<?php echo $cat; ?>"><?php echo zen_draw_pull_down_menu('cat', zen_get_category_tree(), $cat, 'onChange="this.form.submit();"'); ?></form></td>
      </tr>
      <tr>
        <td colspan="2"><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr class="dataTableHeadingRow">
                <td class="dataTableHeadingContent" align="center"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=p.products_id&dir=<?php echo $dir_id; ?>&cat=<?php echo $cat; ?>"><b><?php echo TABLE_HEADING_NUMBER; ?></b></a></td>
                <td class="dataTableHeadingContent"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_model&dir=<?php echo $dir_model; ?>&cat=<?php echo $cat; ?>"><b><?php echo Model; ?></b></a></td>
                <td class="dataTableHeadingContent"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_name&dir=<?php echo $dir_name; ?>&cat=<?php echo $cat; ?>"><b><?php echo TABLE_HEADING_PRODUCTS; ?></b></a></td>
                <td class="dataTableHeadingContent"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_type&dir=<?php echo $dir_type; ?>&cat=<?php echo $cat; ?>"><b><?php echo Type; ?></b></a></td>
                <td class="dataTableHeadingContent" align="center"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=cd.categories_name&dir=<?php echo $dir_category; ?>&cat=<?php echo $cat; ?>"><b>Master Category</b></a></td>
                <td class="dataTableHeadingContent" align="center"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_quantity&dir=<?php echo $dir_quantity; ?>&cat=<?php echo $cat; ?>"><b><?php echo TABLE_HEADING_QUANTITY; ?></b></a></td>
                <td class="dataTableHeadingContent" align="center"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_subquantity&dir=<?php echo $dir_subquantity; ?>&cat=<?php echo $cat; ?>"><b><?php echo Subquantity; ?></b></a></td>
                <td class="dataTableHeadingContent" align="right"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_price&dir=<?php echo $dir_price; ?>&cat=<?php echo $cat; ?>"><b><?php echo TABLE_HEADING_PRICE; ?></b></a></td>
                <td class="dataTableHeadingContent" align="right"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=total&dir=<?php echo $dir_total; ?>&cat=<?php echo $cat; ?>"><b><?php echo TABLE_HEADING_TOTAL; ?></b></a></td>
              </tr>
<?php
  if (isset($_GET['page']) && ($_GET['page'] > 1)) $rows = $_GET['page'] * MAX_DISPLAY_SEARCH_RESULTS_REPORTS - MAX_DISPLAY_SEARCH_RESULTS_REPORTS;
  $rows = 0;
  $lang_id = $_SESSION['languages_id'] != '' ? intval($_SESSION['languages_id']):1;

  $products_query_raw ="select p.products_id
                            , pwas.stock_id
                            , p.products_model
                            , p.products_quantity
                            , pwas.quantity products_subquantity
                            , pd.products_name
                            , pwas.stock_attributes products_type
                            , p.products_price
                            , (ifnull(pwas.quantity,p.products_quantity) * p.products_price) as total
                            , concat(ifnull(concat(cd9.categories_name,' :: '),'')
                                    ,ifnull(concat(cd8.categories_name,' :: '),'')
                                    ,ifnull(concat(cd7.categories_name,' :: '),'')
                                    ,ifnull(concat(cd6.categories_name,' :: '),'')
                                    ,ifnull(concat(cd5.categories_name,' :: '),'')
                                    ,ifnull(concat(cd4.categories_name,' :: '),'')
                                    ,ifnull(concat(cd3.categories_name,' :: '),'')
                                    ,ifnull(concat(cd2.categories_name,' :: '),'')
                                    ,cd.categories_name) as categories_name
                       from ".TABLE_PRODUCTS." p
                            left join products_with_attributes_stock pwas on (pwas.products_id = p.products_id)
                            left join ".TABLE_PRODUCTS_DESCRIPTION." pd on (pd.products_id = p.products_id)
                            left join ".TABLE_CATEGORIES." c on (c.categories_id = p.master_categories_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd on (cd.categories_id = c.categories_id AND cd.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c2 on (c2.categories_id = c.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd2 on (cd2.categories_id = c2.categories_id AND cd2.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c3 on (c3.categories_id = c2.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd3 on (cd3.categories_id = c3.categories_id AND cd3.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c4 on (c4.categories_id = c3.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd4 on (cd4.categories_id = c4.categories_id AND cd4.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c5 on (c5.categories_id = c4.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd5 on (cd5.categories_id = c5.categories_id AND cd5.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c6 on (c6.categories_id = c5.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd6 on (cd6.categories_id = c6.categories_id AND cd6.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c7 on (c7.categories_id = c6.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd7 on (cd7.categories_id = c7.categories_id AND cd7.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c8 on (c8.categories_id = c7.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd8 on (cd8.categories_id = c8.categories_id AND cd8.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c9 on (c9.categories_id = c8.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd9 on (cd9.categories_id = c9.categories_id AND cd9.language_id = '$lang_id')
                       $db_category_where
                       group by p.products_id, pwas.stock_id
                       order by ".($sort=="cd.categories_name"?"categories_name,pd.products_name":$sort)." $dir";
//print "<pre>";
//print $products_query_raw;
//print "</pre>";
//
  if($_GET['page'] != 'all')	$products_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $products_query_raw, $products_query_numrows);
  $products = $db->Execute($products_query_raw);
  while (!$products->EOF) {

// only show low stock on products that can be added to the cart
    if ($zc_products->get_allow_add_to_cart($products->fields['products_id']) == 'Y') {
      $rows++;

      if (strlen($rows) < 2) {
        $rows = '0' . $rows;
      }
      $cPath = zen_get_product_path($products->fields['products_id']);
      $total += $products->fields['total']
?>
              <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href='<?php echo zen_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products->fields['products_id']); ?>'">
              	<td class="dataTableContent" align="center"><?php echo $products->fields['products_id']; ?>&nbsp;</td>
                <td class="dataTableContent"><?php echo '<a href="' . zen_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products->fields['products_id']) . '">' . $products->fields['products_model'] . '</a> '; ?></td>
                <td class="dataTableContent"><?php echo '<a href="' . zen_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products->fields['products_id']) . '">' . $products->fields['products_name'] . '</a> '; ?></td>
                <td class="dataTableContent"><?php echo get_attribute_names($products->fields['products_type']); ?>&nbsp;</td>
                <td class="dataTableContent" align="left"><?php echo $products->fields['categories_name']; ?>&nbsp;</td>
                <td class="dataTableContent" align="right"><?php echo ($products->fields['products_subquantity']==''?$products->fields['products_quantity']:'['.$products->fields['products_quantity'].']'); ?>&nbsp;</td>
                <td class="dataTableContent" align="right"><?php echo $products->fields['products_subquantity']; ?>&nbsp;</td>
                <td class="dataTableContent" align="right"><?php echo $currencies->format($products->fields['products_price']); ?>&nbsp;</td>
                <td class="dataTableContent" align="right"><?php echo $currencies->format($products->fields['total']); ?>&nbsp;</td>
              </tr>
<?php
    }
    $products->MoveNext();
  }
?>
              <tr class="dataTableHeadingRow">
                <td colspan="8">&nbsp;</td>
                <td class="dataTableHeadingContent" align="right"><?php echo $currencies->format($total); ?></td>
              </tr>
            </table></td>
          </tr>
<?php if(is_object($products_split))	{?>
          <tr>
            <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr>
                <td class="smallText" valign="top"><?php echo $products_split->display_count($products_query_numrows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $_GET['page'], TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
                <td class="smallText" align="right"><?php echo $products_split->display_links($products_query_numrows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, MAX_DISPLAY_PAGE_LINKS, $_GET['page'], "sort=$sort&dir=$dir&cat=$cat"); ?></td>
              </tr>
            </table></td>
          </tr>
<?php }	?>
        </table></td>
      </tr>
    </table></td>
<!-- body_text_eof //-->
  </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
    Reply With Quote Reply With Quote
  6. 4 Aug 2009, 04:05 PM #16
    DickTurpin's Avatar
    DickTurpin
    DickTurpin is offline New Zenner
    Join Date
    Oct 2006
    Location
    UK
    Posts
    27
    Plugin Contributions
    0

    Default Re: Inventory Report

    I like the idea of this mod but it appears to include gift vouchers aswell. I have many items which are tailor made for the individual so I have a stock number of say 100 as suggested when setting up. My question is is there a way of adding categories or item numbers to be ignored in the inventory? If not may I please request this feature in the next version.

    Other than that this is just what I've been looking for!
    When the going gets tough, the tough take a coffee break.

    visit my store at: http://www.mysticminds.co.uk/store
    Reply With Quote Reply With Quote
  7. 29 Dec 2010, 07:29 PM #17
    Danielle
    Danielle is offline Totally Zenned
    Join Date
    Oct 2004
    Posts
    1,045
    Plugin Contributions
    0

    Default Re: Inventory Report

    I've gotten this mod to display manufacturer ID in the report, but I'd really like it to show the manufacturer name instead. Does anyone know how I can achieve this? Here is the relevant code so far:

    $products_query_raw = "select manufacturers_id, products_model, p.products_id, products_quantity, pd.products_name, categories_name from ".TABLE_PRODUCTS." p left join ".TABLE_PRODUCTS_DESCRIPTION." pd using(products_id) LEFT JOIN ".TABLE_CATEGORIES_DESCRIPTION." cd ON(cd.categories_id = p.master_categories_id AND cd.language_id = '$lang_id') $db_category_where group by p.products_id order by $sort $dir";
    <td class="dataTableContent" align="center"><?php echo $products->fields['manufacturers_id']; ?>&nbsp;</td>
    Or, even better, I'd LOVE to have a dropdown manufacturer filter at the top of the report. Is that possible?

    Thanks!
    Danielle
    Reply With Quote Reply With Quote
  8. 31 Jan 2011, 10:51 PM #18
    supersnow's Avatar
    supersnow
    supersnow is offline Zen Follower
    Join Date
    Apr 2007
    Location
    California, USA
    Posts
    233
    Plugin Contributions
    0

    Default Re: Inventory Report

    Quote Originally Posted by javaman View Post
    I updated the inventory report if anyone is interested (just the main file: stats_inventory_report.php) to work with products with attributes inventory. I also added the full product category breadcrumb and model as well. Also made subcategories work recursively.

    Code:
    <?php
//
// +----------------------------------------------------------------------+
// |zen-cart Open Source E-commerce                                       |
// +----------------------------------------------------------------------+
// | Copyright (c) 2003 The zen-cart developers                           |
// |                                                                      |
// | http://www.zen-cart.com/index.php                                    |
// |                                                                      |
// | Portions Copyright (c) 2003 osCommerce                               |
// +----------------------------------------------------------------------+
// | This source file is subject to version 2.0 of the GPL license,       |
// | that is bundled with this package in the file LICENSE, and is        |
// | available through the world-wide-web at the following url:           |
// | http://www.zen-cart.com/license/2_0.txt.                             |
// | If you did not receive a copy of the zen-cart license and are unable |
// | to obtain it through the world-wide-web, please send a note to       |
// | [email protected] so we can mail you a copy immediately.          |
// +----------------------------------------------------------------------+
//  $Id: stats_inventory_report.php 13 2007-03-14 00:53:35Z damonp $
//

/*
** $Id: stats_inventory_report.php 13 2007-03-14 00:53:35Z damonp $
**
** ZenCart Inventory Report Copyright (c) 2007 damonparker.org <[email protected]>
**
*/

/*
    History:
    07/20/09 19:00 Richard Sandoz
    integrated with attributes with inventory model
    added full breadcrumb for products category
    added product model
    when selecting a category, includes subcategories in report result set
    
    03/13/07 15:28
    switched to master_categories_id for category filter
    added master category column
    added url parameters to paginate link

    03/08/07 17:55
    Initial release

*/

    require('includes/application_top.php');
    require(DIR_WS_CLASSES . 'currencies.php');
    $currencies = new currencies();


  function get_attribute_names($attributes_ids) {
    global $db;
    if ($attributes_ids == '')
      return '';
    $query = "select pov.products_options_values_name
                   , pa.options_values_id
              from products_attributes pa
                   inner join products_options_values pov on (pov.products_options_values_id = pa.options_values_id)
              where pa.products_attributes_id in (".$attributes_ids.")";
    $result_set = $db->Execute($query);
    $result = '';
    while (!$result_set->EOF) {
      $options_values_id = $result_set->fields['products_options_values_name'];
      $result .= ($result==''?'':',').$options_values_id;
      $result_set->MoveNext();
    }
    return $result;
  }

  function get_all_children_categories($categories_id) {
    global $db;
    $query = "select categories_id from categories where parent_id = ".$categories_id;
    $result_set = $db->Execute($query);
    $child_set = $categories_id;
    while (!$result_set->EOF) {
      $child_categories_id = $result_set->fields['categories_id'];
      $child_set .= ','.get_all_children_categories($child_categories_id);
      $result_set->MoveNext();
    }
    $children_categories_ids = "'".$categories_id."'";
    return $child_set;
  }


    $cat    = $_GET['cat'];
    $dir     = $_GET['dir'];
    $sort     = $_GET['sort'];

    $cat = $cat == '0' ? '':$cat;

    if($cat != '')    {
        $db_category_where = " WHERE master_categories_id in (".get_all_children_categories($cat).") ";
    }

    $dir         = $dir == '' ? 'ASC':$dir;
    $op_dir    = $dir == 'ASC' ? 'DESC':'ASC';
    $sort         = $sort == '' ? 'cd.categories_name':$sort;

    $dir_id = $dir_model = $dir_name = $dir_type = $dir_category = $dir_price = $dir_quantity = $dir_subquantity = $dir_total = 'DESC';

    switch($sort)    {
    case('p.products_id'):
        $dir_id = $op_dir;
        break;
    case('products_model'):
        $dir_model = $op_dir;
        break;
    case('products_name'):
        $dir_name = $op_dir;
        break;
    case('products_type'):
        $dir_type = $op_dir;
        break;
    case('products_price'):
        $dir_price = $op_dir;
        break;
    case('products_quantity'):
        $dir_quantity = $op_dir;
        break;
    case('products_subquantity'):
        $dir_subquantity = $op_dir;
        break;
    case('cd.categories_name'):
        $dir_category = $op_dir;
        break;
    case('total'):
        $dir_total = $op_dir;
        break;
    }


?>
<!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; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script type="text/javascript">
  <!--
  function init()
  {
    cssjsmenu('navbar');
    if (document.getElementById)
    {
      var kill = document.getElementById('hoverJS');
      kill.disabled = true;
    }
  }
  // -->
</script>
</head>
<body onload="init()">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <tr>
<!-- 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 zen_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td><a href="./stats_inventory_report.php?page=all&sort=<?php echo $sort; ?>&dir=<?php echo $dir; ?>&cat=<?php echo $cat; ?>">Show All</a>&nbsp;&nbsp;|&nbsp;&nbsp;<a href="./stats_inventory_report.php?sort=<?php echo $sort; ?>&dir=<?php echo $dir; ?>&cat=<?php echo $cat; ?>">Paginate</a></td>
        <td align="right"><form method="get" action="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=<?php echo $sort; ?>&dir=<?php echo $dir; ?>&cat=<?php echo $cat; ?>"><?php echo zen_draw_pull_down_menu('cat', zen_get_category_tree(), $cat, 'onChange="this.form.submit();"'); ?></form></td>
      </tr>
      <tr>
        <td colspan="2"><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr class="dataTableHeadingRow">
                <td class="dataTableHeadingContent" align="center"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=p.products_id&dir=<?php echo $dir_id; ?>&cat=<?php echo $cat; ?>"><b><?php echo TABLE_HEADING_NUMBER; ?></b></a></td>
                <td class="dataTableHeadingContent"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_model&dir=<?php echo $dir_model; ?>&cat=<?php echo $cat; ?>"><b><?php echo Model; ?></b></a></td>
                <td class="dataTableHeadingContent"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_name&dir=<?php echo $dir_name; ?>&cat=<?php echo $cat; ?>"><b><?php echo TABLE_HEADING_PRODUCTS; ?></b></a></td>
                <td class="dataTableHeadingContent"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_type&dir=<?php echo $dir_type; ?>&cat=<?php echo $cat; ?>"><b><?php echo Type; ?></b></a></td>
                <td class="dataTableHeadingContent" align="center"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=cd.categories_name&dir=<?php echo $dir_category; ?>&cat=<?php echo $cat; ?>"><b>Master Category</b></a></td>
                <td class="dataTableHeadingContent" align="center"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_quantity&dir=<?php echo $dir_quantity; ?>&cat=<?php echo $cat; ?>"><b><?php echo TABLE_HEADING_QUANTITY; ?></b></a></td>
                <td class="dataTableHeadingContent" align="center"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_subquantity&dir=<?php echo $dir_subquantity; ?>&cat=<?php echo $cat; ?>"><b><?php echo Subquantity; ?></b></a></td>
                <td class="dataTableHeadingContent" align="right"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=products_price&dir=<?php echo $dir_price; ?>&cat=<?php echo $cat; ?>"><b><?php echo TABLE_HEADING_PRICE; ?></b></a></td>
                <td class="dataTableHeadingContent" align="right"><a href="./stats_inventory_report.php?page=<?php echo $_GET['page']; ?>&sort=total&dir=<?php echo $dir_total; ?>&cat=<?php echo $cat; ?>"><b><?php echo TABLE_HEADING_TOTAL; ?></b></a></td>
              </tr>
<?php
  if (isset($_GET['page']) && ($_GET['page'] > 1)) $rows = $_GET['page'] * MAX_DISPLAY_SEARCH_RESULTS_REPORTS - MAX_DISPLAY_SEARCH_RESULTS_REPORTS;
  $rows = 0;
  $lang_id = $_SESSION['languages_id'] != '' ? intval($_SESSION['languages_id']):1;

  $products_query_raw ="select p.products_id
                            , pwas.stock_id
                            , p.products_model
                            , p.products_quantity
                            , pwas.quantity products_subquantity
                            , pd.products_name
                            , pwas.stock_attributes products_type
                            , p.products_price
                            , (ifnull(pwas.quantity,p.products_quantity) * p.products_price) as total
                            , concat(ifnull(concat(cd9.categories_name,' :: '),'')
                                    ,ifnull(concat(cd8.categories_name,' :: '),'')
                                    ,ifnull(concat(cd7.categories_name,' :: '),'')
                                    ,ifnull(concat(cd6.categories_name,' :: '),'')
                                    ,ifnull(concat(cd5.categories_name,' :: '),'')
                                    ,ifnull(concat(cd4.categories_name,' :: '),'')
                                    ,ifnull(concat(cd3.categories_name,' :: '),'')
                                    ,ifnull(concat(cd2.categories_name,' :: '),'')
                                    ,cd.categories_name) as categories_name
                       from ".TABLE_PRODUCTS." p
                            left join products_with_attributes_stock pwas on (pwas.products_id = p.products_id)
                            left join ".TABLE_PRODUCTS_DESCRIPTION." pd on (pd.products_id = p.products_id)
                            left join ".TABLE_CATEGORIES." c on (c.categories_id = p.master_categories_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd on (cd.categories_id = c.categories_id AND cd.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c2 on (c2.categories_id = c.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd2 on (cd2.categories_id = c2.categories_id AND cd2.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c3 on (c3.categories_id = c2.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd3 on (cd3.categories_id = c3.categories_id AND cd3.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c4 on (c4.categories_id = c3.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd4 on (cd4.categories_id = c4.categories_id AND cd4.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c5 on (c5.categories_id = c4.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd5 on (cd5.categories_id = c5.categories_id AND cd5.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c6 on (c6.categories_id = c5.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd6 on (cd6.categories_id = c6.categories_id AND cd6.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c7 on (c7.categories_id = c6.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd7 on (cd7.categories_id = c7.categories_id AND cd7.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c8 on (c8.categories_id = c7.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd8 on (cd8.categories_id = c8.categories_id AND cd8.language_id = '$lang_id')
                            left join ".TABLE_CATEGORIES." c9 on (c9.categories_id = c8.parent_id)
                            left join ".TABLE_CATEGORIES_DESCRIPTION." cd9 on (cd9.categories_id = c9.categories_id AND cd9.language_id = '$lang_id')
                       $db_category_where
                       group by p.products_id, pwas.stock_id
                       order by ".($sort=="cd.categories_name"?"categories_name,pd.products_name":$sort)." $dir";
//print "<pre>";
//print $products_query_raw;
//print "</pre>";
//
  if($_GET['page'] != 'all')    $products_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $products_query_raw, $products_query_numrows);
  $products = $db->Execute($products_query_raw);
  while (!$products->EOF) {

// only show low stock on products that can be added to the cart
    if ($zc_products->get_allow_add_to_cart($products->fields['products_id']) == 'Y') {
      $rows++;

      if (strlen($rows) < 2) {
        $rows = '0' . $rows;
      }
      $cPath = zen_get_product_path($products->fields['products_id']);
      $total += $products->fields['total']
?>
              <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href='<?php echo zen_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products->fields['products_id']); ?>'">
                  <td class="dataTableContent" align="center"><?php echo $products->fields['products_id']; ?>&nbsp;</td>
                <td class="dataTableContent"><?php echo '<a href="' . zen_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products->fields['products_id']) . '">' . $products->fields['products_model'] . '</a> '; ?></td>
                <td class="dataTableContent"><?php echo '<a href="' . zen_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products->fields['products_id']) . '">' . $products->fields['products_name'] . '</a> '; ?></td>
                <td class="dataTableContent"><?php echo get_attribute_names($products->fields['products_type']); ?>&nbsp;</td>
                <td class="dataTableContent" align="left"><?php echo $products->fields['categories_name']; ?>&nbsp;</td>
                <td class="dataTableContent" align="right"><?php echo ($products->fields['products_subquantity']==''?$products->fields['products_quantity']:'['.$products->fields['products_quantity'].']'); ?>&nbsp;</td>
                <td class="dataTableContent" align="right"><?php echo $products->fields['products_subquantity']; ?>&nbsp;</td>
                <td class="dataTableContent" align="right"><?php echo $currencies->format($products->fields['products_price']); ?>&nbsp;</td>
                <td class="dataTableContent" align="right"><?php echo $currencies->format($products->fields['total']); ?>&nbsp;</td>
              </tr>
<?php
    }
    $products->MoveNext();
  }
?>
              <tr class="dataTableHeadingRow">
                <td colspan="8">&nbsp;</td>
                <td class="dataTableHeadingContent" align="right"><?php echo $currencies->format($total); ?></td>
              </tr>
            </table></td>
          </tr>
<?php if(is_object($products_split))    {?>
          <tr>
            <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr>
                <td class="smallText" valign="top"><?php echo $products_split->display_count($products_query_numrows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $_GET['page'], TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
                <td class="smallText" align="right"><?php echo $products_split->display_links($products_query_numrows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, MAX_DISPLAY_PAGE_LINKS, $_GET['page'], "sort=$sort&dir=$dir&cat=$cat"); ?></td>
              </tr>
            </table></td>
          </tr>
<?php }    ?>
        </table></td>
      </tr>
    </table></td>
<!-- body_text_eof //-->
  </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
    I tried this modification so I can see the inventory with stock by attributes but i just get the following error:

    Code:
    1146 Table '***_****.products_with_attributes_stock' doesn't exist
in:
[select  count(*) as total  from zen_products p                             left  join products_with_attributes_stock pwas on (pwas.products_id =  p.products_id)                             left join  zen_products_description pd on (pd.products_id = p.products_id)                              left join zen_categories c on (c.categories_id =  p.master_categories_id)                             left join  zen_categories_description cd on (cd.categories_id = c.categories_id AND  cd.language_id = '1')                             left join  zen_categories c2 on (c2.categories_id = c.parent_id)                              left join zen_categories_description cd2 on (cd2.categories_id  = c2.categories_id AND cd2.language_id = '1')                              left join zen_categories c3 on (c3.categories_id = c2.parent_id)                              left join zen_categories_description cd3 on  (cd3.categories_id = c3.categories_id AND cd3.language_id = '1')                              left join zen_categories c4 on (c4.categories_id =  c3.parent_id)                             left join  zen_categories_description cd4 on (cd4.categories_id = c4.categories_id  AND cd4.language_id = '1')                             left join  zen_categories c5 on (c5.categories_id = c4.parent_id)                              left join zen_categories_description cd5 on  (cd5.categories_id = c5.categories_id AND cd5.language_id = '1')                              left join zen_categories c6 on (c6.categories_id =  c5.parent_id)                             left join  zen_categories_description cd6 on (cd6.categories_id = c6.categories_id  AND cd6.language_id = '1')                             left join  zen_categories c7 on (c7.categories_id = c6.parent_id)                              left join zen_categories_description cd7 on  (cd7.categories_id = c7.categories_id AND cd7.language_id = '1')                              left join zen_categories c8 on (c8.categories_id =  c7.parent_id)                             left join  zen_categories_description cd8 on (cd8.categories_id = c8.categories_id  AND cd8.language_id = '1')                             left join  zen_categories c9 on (c9.categories_id = c8.parent_id)                              left join zen_categories_description cd9 on  (cd9.categories_id = c9.categories_id AND cd9.language_id = '1')                                                ]
If you were entering  information, press the BACK button in your browser and re-check the  information you had entered to be sure you left no blank fields.
    anyone have any idea? I am using zencart. 1.3.9h

    Thanks
    Thanks
    Supersnow
    Reply With Quote Reply With Quote
 

 
Page 2 of 2 FirstFirst 12
« Previous Thread | Next Thread »

Similar Threads

  1. v150 ZC Inventory Report Module
    By mprough in forum All Other Contributions/Addons
    Replies: 74
    Last Post: 15 Feb 2026, 05:34 PM
  2. v154 SBA Inventory Report
    By catsma_97504 in forum All Other Contributions/Addons
    Replies: 1
    Last Post: 6 May 2016, 04:16 PM
  3. Store inventory report?
    By RocketFoot in forum General Questions
    Replies: 4
    Last Post: 14 May 2009, 01:15 PM
  4. Inventory Order Report
    By j1l2i3p4k5i6n7g8 in forum General Questions
    Replies: 4
    Last Post: 5 Jan 2008, 05:55 PM
  5. Inventory Report with Attributes
    By brad512 in forum General Questions
    Replies: 6
    Last Post: 31 Dec 2006, 09:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

disjunctive-egg
Content and Graphics Copyright (c) 2003 - 2026 Zen Ventures, LLC - all rights reserved
Zen Cart® is a Registered Trademark of Zen Ventures, LLC