Results 1 to 10 of 18

Hybrid View

  1. 1 Jan 2008, 06:42 PM #1
    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
  2. 21 Jul 2009, 02:21 AM #2
    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
  3. 4 Aug 2009, 04:05 PM #3
    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
  4. 31 Jan 2011, 10:51 PM #4
    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
 

 
« 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