Latest News: (loading..)
Gergely

Products Monitor

13 posts in this topic

Products Monitor Pro

 

(Main Page)

 

Version: Pro

for osCommerce V3alfa5

 

 

Inspired by http://forums.oscomm...useful-queries/

Thanks to pyramids

Author: Gergely

http://oscom.hu

 

 

 

Products monitor analise the products.

 

 

- Non sensitive for statuses

- Only products! (variants non grouped)

 

Display icon error for:

 

1. Empty images

2. Non exist weights

3. Empty Tax class

4. Low price value

 

Icon titles give more information about field data (value). Admin can edit errors alternatively and delete products.

 

The low stock statistical module exist, so I dont implemented low stock yet.

 

 

Products monitor use only main page for critical products. This is more

effective solution, than make separated queries on each page. In the future

we can edit and delete products directly.

 

products_monitor_pro.jpg

Share this post


Link to post
Share on other sites

Products Monitor Pro V1.0

 

Add some new fields and info icon.

 

  • Products Categories
  • Last Modification Date
  • Status Edit Icon Demo

This version presents only critical products data on page sheet.

 

Known Bugs:

Master products status not presented. I think the original products page have to display this information.

If variants product status is off the titles missed. I used original core product class and will be better to build self class for this page.

 

Function:

Displaying only products have critical data

Show problems in icons and text information in icon title

Direct link to product editor

 

Plans:

Bugs editions

Delete products directly

Edit critical value directly

Add admin products list page with normal values

 

products_monitor_pro_v1_0.jpg

Share this post


Link to post
Share on other sites

 

Thanks burt,

 

I managed my live rc store :rolleyes: by your mod.

 

I copy one page navigation on top and insert critical data filter by this way.

<?php
/*
$Id: stats_products_monitor.php

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

Copyright (c) 2010 osCommerce
Copyright (c) 2010 Club osCommerce www.clubosc.com

Released under the GNU General Public License
*/

require('includes/application_top.php');
?>
<!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">
<script language="javascript" src="includes/general.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
<tr>
<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
</table></td>
<!-- body_text //-->
<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">
	<tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
	<tr>
	<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
	<td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
	</tr>
	</table></td>
	</tr>
	<tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
<?php
if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;
$rows = 0;
$products_query_raw = "select p.products_id, p.products_image, p.products_status, p.products_weight, p.products_tax_class_id, p.products_price, pd.products_name, l.name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LANGUAGES . " l where p.products_id = pd.products_id and l.languages_id = pd.language_id and p.products_status = 1 and (p.products_weight = 0 or p.products_price = 0 or p.products_image = '' or p.products_tax_class_id = 0) order by pd.products_name, l.languages_id, p.products_image, p.products_price, p.products_weight";
$products_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $products_query_raw, $products_query_numrows);
$products_query = tep_db_query($products_query_raw);
// while ($products = tep_db_fetch_array($products_query)) {
?>
	<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, $HTTP_GET_VARS['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, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page']); ?></td>
	</tr>
	</table></td>
	</tr>
	<tr>
	<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
	<tr class="dataTableHeadingRow">
	<td class="dataTableHeadingContent" width="70"><?php echo TABLE_HEADING_PRODUCTS_ID; ?></td>
	<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS; ?></td>
	<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS_TAX_CLASS; ?></td>
	<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_IMAGE; ?></td>
	<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRICE; ?></td>
	<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_WEIGHT; ?></td>
	<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_STATUS; ?></td>
	<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_DELETE; ?></td>
	</tr>
<?php
while ($products = tep_db_fetch_array($products_query)) {
?>
	<tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href='<?php echo tep_href_link(FILENAME_CATEGORIES, 'action=new_product_preview&read=only&pID=' . $products['products_id'], 'NONSSL'); ?>'">
	<td class="dataTableContent" width="70"><?php echo $products['products_id']; ?></td>
	<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'action=new_product&pID=' . $products['products_id'], 'SSL') . '">' . $products['products_name'] . '</a> (' . $products['name'] . ')'; ?></td>
	<td class="dataTableContent"><?php echo ($products['products_tax_class_id'] > 0) ? tep_image(DIR_WS_ICONS . 'tick.gif') : tep_image(DIR_WS_ICONS . 'cross.gif'); ?></td>
	<td class="dataTableContent"><?php echo (tep_not_null($products['products_image'])) ? tep_image(DIR_WS_ICONS . 'tick.gif') : tep_image(DIR_WS_ICONS . 'cross.gif'); ?></td>
	<td class="dataTableContent"><?php echo ($products['products_price'] > 0) ? tep_image(DIR_WS_ICONS . 'tick.gif') : tep_image(DIR_WS_ICONS . 'cross.gif'); ?></td>
	<td class="dataTableContent"><?php echo ($products['products_weight'] > 0) ? tep_image(DIR_WS_ICONS . 'tick.gif') : tep_image(DIR_WS_ICONS . 'cross.gif'); ?></td>
	<td class="dataTableContent"><?php echo ($products['products_status'] > 0) ? tep_image(DIR_WS_ICONS . 'tick.gif') : tep_image(DIR_WS_ICONS . 'cross.gif'); ?></td>
	<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'action=new_product&pID=' . $products['products_id'], 'SSL') . '">' . tep_image(DIR_WS_ICONS . 'preview.gif') . '</a> <a href="' . tep_href_link(FILENAME_CATEGORIES, 'action=delete_product&pID=' . $products['products_id'], 'SSL') . '">' . tep_image(DIR_WS_ICONS . 'delete.gif') . '</a>'; ?></td>
	</tr>
<?php
}
?>
	</table></td>
	</tr>
	<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, $HTTP_GET_VARS['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, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page']); ?></td>
	</tr>
	</table></td>
	</tr>
	</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'); ?>

 

My query request was better for me, but this is personal.

 

You know, would be better to build field checkbox filter in the Products Monitor series. I tested on more than 6000 products and need more filter switch to do faster edit.

 

ie:

Filter by only one field where

- status

- products name (I fond empty names! This problem is only RC. V3 use names everything)

- weight

- tax_class

- price

- image

 

0 or empty.

 

After it will be very usefull mod.

 

Gergely

Share this post


Link to post
Share on other sites

i install this addon, but i see only warning:

[MYSQL] Unknown column 'p.parent_id' in 'on clause' (1054): [QUERY] select SQL_CALC_FOUND_ROWS p.products_id, if(p.parent_id <> 0, p.parent_id, p.products_id) as master_id, pd.products_name, tc.tax_class_title, p.products_tax_class_id, p.products_weight, p.products_price, p.products_status, p.products_last_modified, i.image, pd.language_id, count(p2c.categories_id) as categories_id from osc_products p, osc_products_description pd left join osc_products_images i on(if(p.parent_id <> 0, p.parent_id = i.products_id, p.products_id = i.products_id) and i.default_flag = 1) left join osc_tax_class tc on(p.products_tax_class_id = tc.tax_class_id) left join osc_products_to_categories p2c on(if(p.parent_id <> 0, p.parent_id = p2c.products_id, p.products_id = p2c.products_id)) where if(p.parent_id <> 0, p.parent_id = pd.products_id, p.products_id = pd.products_id) and p.has_children = 0 and pd.language_id = 1 and (i.image = "" or p.products_weight = 0 or p.products_price <1 or p.products_tax_class_id = 0 or p.products_s in Z:\home\localhost\www\osc\includes\classes\database.php on line 72

 

please help.

Share this post


Link to post
Share on other sites

i install this addon, but i see only warning:

[MYSQL] Unknown column 'p.parent_id' in 'on clause' (1054): [QUERY] select SQL_CALC_FOUND_ROWS p.products_id, if(p.parent_id <> 0, p.parent_id, p.products_id) as master_id, pd.products_name, tc.tax_class_title, p.products_tax_class_id, p.products_weight, p.products_price, p.products_status, p.products_last_modified, i.image, pd.language_id, count(p2c.categories_id) as categories_id from osc_products p, osc_products_description pd left join osc_products_images i on(if(p.parent_id <> 0, p.parent_id = i.products_id, p.products_id = i.products_id) and i.default_flag = 1) left join osc_tax_class tc on(p.products_tax_class_id = tc.tax_class_id) left join osc_products_to_categories p2c on(if(p.parent_id <> 0, p.parent_id = p2c.products_id, p.products_id = p2c.products_id)) where if(p.parent_id <> 0, p.parent_id = pd.products_id, p.products_id = pd.products_id) and p.has_children = 0 and pd.language_id = 1 and (i.image = "" or p.products_weight = 0 or p.products_price <1 or p.products_tax_class_id = 0 or p.products_s in Z:\home\localhost\www\osc\includes\classes\database.php on line 72

 

please help.

 

Hi,

 

I used MySQL 5.0.32 version and you?

 

Maybe something wrong NULL value in products table. The p.parent_id must exist in products table. I think there will be wrong with "if" conditions for this reason. Try to use reverse conditions in query script like "p.parent_id is null" in catalog/admin/includes/application/products_monitor/classes/main.php.

 

 $Qpdemo = $osC_Database->query('select SQL_CALC_FOUND_ROWS p.products_id, if(p.parent_id is null, p.products_id, p.parent_id) as master_id, pd.products_name, tc.tax_class_title, p.products_tax_class_id, p.products_weight, p.products_price, p.products_status, p.products_last_modified, i.image, pd.language_id, count(p2c.categories_id) as categories_id from :table_products p, :table_products_description pd left join :table_products_images i on(if(p.parent_id is null, p.products_id = i.products_id, p.parent_id = i.products_id) and i.default_flag = 1) left join :table_tax_class tc on(p.products_tax_class_id = tc.tax_class_id) left join :table_products_to_categories p2c on(if(p.parent_id is null, p.products_id = p2c.products_id, p.parent_id = p2c.products_id)) where if(p.parent_id is null, p.products_id = pd.products_id, p.parent_id = pd.products_id) and p.has_children = 0 and pd.language_id = :languages_id and (i.image = "" or p.products_weight = 0 or p.products_price <1 or p.products_tax_class_id = 0 or p.products_status < 1 or p2c.categories_id = 0)');

 

Gergely

Share this post


Link to post
Share on other sites

thanks for you reply.

 

i used local: MySQL 5.0.89

and used on my hosting provider: MySQL 5.1.40-community

 

on both server identical error.

 

i tried oscommerce 3.0A5 (this server) and oscommerce 3.0A5 + Russian SP1.5 (http://oscommerce-3.spb.ru/service-pack-1-5/)

 

i tried your change code on main.php

 

but error (1054) remained

Edited by wert

Share this post


Link to post
Share on other sites

 

 

Hi there.

 

I just installed this contribution on my osc2.3.1 and i already added extra field which shows when product stock quantity is low(lower than quantity set directly in stats_products_monitor.php file). I just added extra code which extracts quantity information from DB and then if it's more than 5 shows green icon, if less than 5 shows red icon...

My knowledge in php and mysql is...lets say verry bad :)

So maybe someone knows how we can set low stock quantity for each product separately? Then it will be possible to see which product going low.

 

Thanks in advance.

 

Clipboard02.jpg

Share this post


Link to post
Share on other sites

Soooo couple evenings of playing around and i think i get what i want :)

I upgraded this contribution, so now i can see what i have in stock for every single product, i can see what is low stock level for that product and if stock level < low stock level then it shows red cross :)

Also when editing or adding new product i have extra field, where i can specify low stock level for that product(that will be stored in data base).

 

Also, i think, it will be great to show category names on it. Like in this contribution http://addons.oscommerce.com/info/6551

And to have ability edit product's info in pop-up window. Yeap.... it's still lot to learn...

 

Anyway... if anyone will be interested i can try to collect everything to one place(now will be difficult to remember what exactly i changed...) and share, maybe someone can extend it more than me.

 

Product Monitor Pro 2.x:

01.jpg

 

Editing / Adding new product:

02.jpg

Gergely likes this

Share this post


Link to post
Share on other sites

Soooo couple evenings of playing around and i think i get what i want :)

I upgraded this contribution, so now i can see what i have in stock for every single product, i can see what is low stock level for that product and if stock level < low stock level then it shows red cross :)

Also when editing or adding new product i have extra field, where i can specify low stock level for that product(that will be stored in data base).

 

Also, i think, it will be great to show category names on it. Like in this contribution http://addons.oscommerce.com/info/6551

And to have ability edit product's info in pop-up window. Yeap.... it's still lot to learn...

 

Anyway... if anyone will be interested i can try to collect everything to one place(now will be difficult to remember what exactly i changed...) and share, maybe someone can extend it more than me.

 

Product Monitor Pro 2.x:

 

 

Editing / Adding new product:

 

 

Usefull.

Share this post


Link to post
Share on other sites

How do you code which categories are needed?

For my installation I need to add:

Product Description has any values or characters (yes or no)

Product Tab 4 has any values or characters (yes or no)

Product has specific attribute; downloadable attribute (yes or no)

Product has specific attribute2; hard copy attribute (yes or no)

Product is on sale (just yes or check)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now