Jump to content



Photo

Products Monitor


  • Please log in to reply
12 replies to this topic

#1   Gergely

Gergely

    Action Hero

  • Community Team
  • 1,229 posts
  • Real Name:Gergely Tóth
  • Gender:Male
  • Location:Budapest

Posted 01 September 2010 - 19:36

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.

[img]http://oscom.hu/images/products_monitor/products_monitor_pro.jpg[/img]

some rewrites :-


#2   Gergely

Gergely

    Action Hero

  • Community Team
  • 1,229 posts
  • Real Name:Gergely Tóth
  • Gender:Male
  • Location:Budapest

Posted 02 September 2010 - 18:15

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

[img]http://oscom.hu/images/products_monitor/products_monitor_pro_v1_0.jpg[/img]

some rewrites :-


#3   evanmugford

evanmugford
  • Members
  • 33 posts
  • Real Name:evan

Posted 06 September 2010 - 15:50

will you be doing a version for rc? or just alpha5?

#4 ONLINE   burt

burt

    Vanquisher of Demons

  • Community Team
  • 9,738 posts
  • Real Name:G Burton
  • Gender:Male
  • Location:UK/DEV/on

Posted 06 September 2010 - 17:00

Nice mod. I might port this to 2.x series.
IF YOU MAKE A POST REQUESTING HELP...please state the exact version of osCommerce that you are using. THANKS
 
Responsive osCommerce, join in:
http://forums.oscomm...rom-the-get-go/

--
Making your shop better, one osCommerce module at a time - get in touch, or get my newsletter every 1st of the month.
 
Big Bang Templates for 2.3.4

#5 ONLINE   burt

burt

    Vanquisher of Demons

  • Community Team
  • 9,738 posts
  • Real Name:G Burton
  • Gender:Male
  • Location:UK/DEV/on

Posted 07 September 2010 - 11:39

2.x version.

[img]http://www.clubosc.com/test/2.x.jpg[/img]

http://addons.oscommerce.com/info/7539
IF YOU MAKE A POST REQUESTING HELP...please state the exact version of osCommerce that you are using. THANKS
 
Responsive osCommerce, join in:
http://forums.oscomm...rom-the-get-go/

--
Making your shop better, one osCommerce module at a time - get in touch, or get my newsletter every 1st of the month.
 
Big Bang Templates for 2.3.4

#6   Gergely

Gergely

    Action Hero

  • Community Team
  • 1,229 posts
  • Real Name:Gergely Tóth
  • Gender:Male
  • Location:Budapest

Posted 07 September 2010 - 19:56

2.x version.

http://addons.oscommerce.com/info/7539


Thanks burt,

I managed my live rc store /rolleyes.gif' class='bbc_emoticon' alt=':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

some rewrites :-


#7   wert

wert
  • Members
  • 2 posts
  • Real Name:Dmitriy
  • Gender:Male

Posted 19 September 2010 - 15:50

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.

#8   Gergely

Gergely

    Action Hero

  • Community Team
  • 1,229 posts
  • Real Name:Gergely Tóth
  • Gender:Male
  • Location:Budapest

Posted 01 October 2010 - 08:17

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

some rewrites :-


#9   wert

wert
  • Members
  • 2 posts
  • Real Name:Dmitriy
  • Gender:Male

Posted 09 October 2010 - 14:33

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....rvice-pack-1-5/)

i tried your change code on main.php

but error (1054) remained

Edited by wert, 09 October 2010 - 14:40.


#10   plasmaboy

plasmaboy
  • Members
  • 4 posts
  • Real Name:Lucky
  • Gender:Male
  • Location:London

Posted 29 December 2010 - 23:34

2.x version.

http://addons.oscommerce.com/info/7539



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.

[img]http://www.nufound.co.uk/Clipboard02.jpg[/img]

#11   plasmaboy

plasmaboy
  • Members
  • 4 posts
  • Real Name:Lucky
  • Gender:Male
  • Location:London

Posted 03 January 2011 - 22:10

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:
[img]http://www.nufound.co.uk/01.jpg[/img]

Editing / Adding new product:
[img]http://www.nufound.co.uk/02.jpg[/img]

#12   Gergely

Gergely

    Action Hero

  • Community Team
  • 1,229 posts
  • Real Name:Gergely Tóth
  • Gender:Male
  • Location:Budapest

Posted 05 January 2011 - 11:47

Soooo couple evenings of playing around and i think i get what i want /smile.gif' class='bbc_emoticon' alt=':)' />
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 /smile.gif' class='bbc_emoticon' alt=':)' />
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.

some rewrites :-


#13   newburns

newburns
  • Members
  • 182 posts
  • Real Name:Ray Burns
  • Gender:Male
  • Location:Texas

Posted 13 April 2013 - 18:20

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)