Jump to content
Sign in to follow this  
insomniac2

Inventory Report v1.0 beta

Recommended Posts

Here is a redesigned Stock and Backorders system for your store. I've spent 2 days messing around to get it where it is. I think it's actually pretty cool. Would like you to try it and give me your input .. or add to it.

 

Thanx.

 

Contribution Link: http://www.oscommerce.com/community/contributions,3101

Edited by insomniac2

Share this post


Link to post
Share on other sites

Could you expand on that? ... and tell us where the problem is.

 

For example ... The instructions were confusing ... or ... Installed and did not work etc. etc.

 

That way maybe we could help you out.

 

Thanx.

Share this post


Link to post
Share on other sites

Hi,

 

Just trying to add the contrib but have ran into a couple of snags.

 

At instruction 3. I cannot find a file called

/admin/includes/boxes/statistics.php
, should there have been one in the download ?

 

At instruction 5. in the admin/categories.php file I cannot find the

PRODUCT_QUANTITY line
to insert the section that follows.

 

Also the next part of instruction 5. in the admin/categories.php file about adding the fields

products_reorder, products_reorder_to
to the queries, I cannot find any similar code where this could be added.

 

I've got some other contribs loaded (e.g. HTC, Ultimate SEO URL's, Dynamic Sitemap...) but cant spot if they have amended the code I'm looking for.

 

If you could give me any pointers I'd appreciate it ?

Share this post


Link to post
Share on other sites

There are a few typos /errors in the install instrutions.

 

For step 3 you can add the link to admin in any file you want or have ... for example admin/includes/boxes/repots.php

 

 

For Step 5 should be TEXT_PRODUCTS_QUANTITY ... or you can place the code anywhere you would prefer it to show up in categories.php

 

 

and for the adding the products_reorder, products_reorder_to fields to the queries ...

 

Look for

 

tep_db_query("insert into " . TABLE_PRODUCTS_TO_CATEGORIES . " (products_id, categories_id) values ('" . (int)$products_id . "', '" . (int)$current_category_id . "')");

 

and ADD this query below

 

tep_db_query("update products set products_quantity = '" . $HTTP_POST_VARS['products_quantity'] . "', products_reorder = '" . $HTTP_POST_VARS['products_reorder'] . "', products_reorder_to = '" . $HTTP_POST_VARS['products_reorder_to'] . "', products_model = '" . $HTTP_POST_VARS['products_model'] . "', products_image = '" . $HTTP_POST_VARS['products_image'] . "', products_price = '" . $HTTP_POST_VARS['products_price'] . "', products_weight = '" . $HTTP_POST_VARS['products_weight'] . "', products_tax_class_id = '" . $HTTP_POST_VARS['products_tax_class_id'] . "', products_status = '" . $HTTP_POST_VARS['products_status'] . "' where products_id = '" . $HTTP_GET_VARS['pID'] . "'");

 

FIND this line

 

$sql_data_array = array('products_quantity' => tep_db_prepare_input($HTTP_POST_VARS['products_quantity']),

 

ADD these line after that

 

'products_reorder' => tep_db_prepare_input($HTTP_POST_VARS['products_reorder']),

'products_reorder_to' => tep_db_prepare_input($HTTP_POST_VARS['products_reorder_to']),

 

 

FIND

 

} elseif ($HTTP_POST_VARS['copy_as'] == 'duplicate') {

$product_query = tep_db_query("select ......

 

ADD products_reorder, products_reorder_to, to the $product_query

 

JUST BELOW FIND

tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, .....

 

ADD products_reorder, products_reorder_to, to the insert query

 

SHOULD LOOK SOMETHING LIKE

 

tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_reorder, products_reorder_to, products_model, vendors_prod_id, products_upc, products_image, products_pdfupload, products_inventory_cost, vendors_product_price, products_retail_price, products_price, vendors_product_price, vendors_prod_comments, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, vendors_id, manufacturers_id, minorder, products_geo_zones, products_sort_order) values ('" . tep_db_input($product['products_quantity']) . "', '" . (int)$product['products_reorder'] . "', '" . (int)$product['products_reorder_to'] . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_retail_price']) . "', '" . tep_db_input($product['products_price']) . "', '" . tep_db_input($product['vendors_prod_comments']) . "', now(), '" . (empty($product['products_date_available']) ? "null" : "'" . tep_db_input($product['products_date_available']) . "'") . ", '" . tep_db_input($product['products_date_expire']) . "', '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['vendors_id'] . "', '" . (int)$product['manufacturers_id'] . "', '" . (int)$product['products_geo_zones'] . "', '" . (int)$product['products_sort_order'] . "')");

 

FIND

 

} elseif ($action == 'new_product_preview') {

if (tep_not_null($HTTP_POST_VARS)) {

$pInfo = new objectInfo($HTTP_POST_VARS);

$products_name = $HTTP_POST_VARS['products_name'];

$products_description = $HTTP_POST_VARS['products_description'];

$products_url = $HTTP_POST_VARS['products_url'];

} else {

$product_query = tep_db_query("select .........

 

ADD p.products_reorder, p.products_reorder_to, to the $product_query shown above

 

FIND

 

$parameters = array('products_name' => '',

 

ADD to the array

 

'products_reorder' => '',

'products_reorder_to' => '',

 

SHOULD LOOK SOMETHING LIKE

 

if ($action == 'new_product') {

$parameters = array('products_name' => '',

'products_description' => '',

'products_url' => '',

'products_id' => '',

'products_quantity' => '',

'products_reorder' => '', // added

'products_reorder_to' => '', // added

'products_model' => '',

'products_image' => '',

'products_price' => '',

'products_weight' => '',

'products_date_added' => '',

'products_last_modified' => '',

'products_date_available' => '',

'products_status' => '',

'products_tax_class_id' => '',

'manufacturers_id' => '');

 

JUST AFTER THE ABOVE FIND

 

if (isset($HTTP_GET_VARS['pID']) && empty($HTTP_POST_VARS)) {

$product_query = tep_db_query("select ......

 

ADD products_reorder, products_reorder_to, to the $product_query

 

 

NOW SKIP the part

 

$products_count = 0

 

I don't know where that came from

 

 

I think thats it. categories,php is the worst file to help people with lol.

 

Hope that works for you.

Share this post


Link to post
Share on other sites

Hi Don,

 

Thanks for the speedy reply.................. and the instructions were bang on !

 

I've now got it loaded and showing correctly in the Admin panel with the warning of "Products to Restock:1".

 

Clicking into Reports it's also showing correctly under there with the "Re-Order level" warning at the top of the screen.

 

When I click into the report the product is not listed but I'll check and double check the amendments I've made and get back to you - I reckon I've just misplaced a character somewhere.

 

Once again - Thanks !!!!

Share this post


Link to post
Share on other sites

Found it !

 

Must pay more attention to my cut & paste................................

 

Thanks very much for the help in spelling it out for me.

 

Regards

 

Bob

Share this post


Link to post
Share on other sites

Glad to hear you got it going. I will try to find time to update the package instructions and repost it. They were quickly thrown together. I was trying to find time to get rid of the table that still shows even when no items require restock or are out of stock but my mind has been frazzled with other things lol.

Share this post


Link to post
Share on other sites

I realize there is still a glitch with the separation of the tables. One for only Low Stock Items and the other for Out of Stock (ie. backorders)

 

Here is a quick fix for when Low Stock Items table does not show up for low items.

 

OPEN admin/stats_low_stock.php

 

 

FIND LINE 275 ... unset($cPath_array); unset($p_category_array);

 

COMMENT IT OUT LIKE THIS ... //unset($cPath_array); unset($p_category_array);

 

 

FIND LINE 283 ... if ($display == n) {

 

CHANGE IT TO THIS ... if ( ($display == n) || ($products['products_quantity'] >= $products['products_reorder']) ) {

 

This will temporarily solve the problem of Low Stock Items not showing up.

 

 

Right now the 2 tables are working off some of the same queries. This is where the problems are in separating the two tables for Low Stock Items and the other for Out of Stock (ie. backorders)

 

I have spent hours trying to revamp the file to no effect. It does need a total overhaul and a few if and }else{ statements lol to separate the two tables properly. If any php Wiz has the time or wants to improve on it that would be great.

Share this post


Link to post
Share on other sites

I have 77 products that need reording, but there is no way to navigate between the page splits. Here is what you need to add to the code if you ever want to see what's on the next page (beyond what is set for MAX_DISPLAY_SEARCH_RESULTS):

 

Look for the following block of code (beginning at line 431 in the original code):

 

<tr>

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

<tr>

<td class="pageHeading" align="left"> </td>

<td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>

<td align="right">

<?php echo tep_image_submit('button_update.gif', IMAGE_UPDATE) . ' ' . '<a href="' . tep_href_link(FILENAME_STATS_LOW_STOCK) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>'; ?>

</td>

</tr>

Add the following code directly above it:

 

<tr>

<td><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_MANUFACTURERS); ?></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><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>

</tr>

 

 

Hope this helps some folks! You might want to add this into your next release.

 

Mark

Share this post


Link to post
Share on other sites

I'm getting a long list with items that are not in stock (0 avail.).

I finally found out what is wrong.

I have a contribution 'enable/disable' categories.

When an item is in a category that is disabled, but the item itself is active (but because the category is disabled, it's not showing up in the shop), I get that item in the reorder list.

 

Has anyone with the contribution enable/disable categories also this inventory report contrib?

 

If so, have you found a sollution?

I know where the problem is

 

  $products_query_raw = "select p.products_id, p.products_quantity, p.products_reorder, p.products_reorder_to, p.products_model, pd.products_name, p.products_price, p.products_weight, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.products_id = pd.products_id and p.manufacturers_id = m.manufacturers_id and pd.language_id = '" . (int)$languages_id . "' and p.products_quantity <= p.products_reorder group by pd.products_id order by $db_orderby $sorted";

 

This piece of code also needs another join with the 'catagories'-table, and look if categories_status equals 1. If not, don't show the item.

 

But since my knowledge of writing SQL-statements is limited, esp. with (inner) joins, I request the help of the others here.

 

Thanks!

Share this post


Link to post
Share on other sites

OK here is a lager query that includes categories_status capability. Can someone check if this is SQL 5 compatible. I still don't understand the rules for joining tables etc for SQL 5

 

New query for $products_query_raw_bb =

 

$products_query_raw_bb = "select p.products_id, p.products_quantity, p.products_reorder, p.products_reorder_to, p.products_model, pd.products_name, p.products_price, p.products_weight, p.products_date_added, m.manufacturers_other, p.manufacturers_id, p.products_retail_price, if(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, if(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_CATEGORIES . " c on c.categories_id = p2c.categories_id where c.categories_status = '1' and p.products_status = '1' and p.products_id = pd.products_id and p.products_quantity <= p.products_reorder and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' group by pd.products_id order by $order";

 

 

I'm getting a long list with items that are not in stock (0 avail.).

I finally found out what is wrong.

I have a contribution 'enable/disable' categories.

When an item is in a category that is disabled, but the item itself is active (but because the category is disabled, it's not showing up in the shop), I get that item in the reorder list.

 

Has anyone with the contribution enable/disable categories also this inventory report contrib?

 

If so, have you found a sollution?

I know where the problem is

 

  $products_query_raw = "select p.products_id, p.products_quantity, p.products_reorder, p.products_reorder_to, p.products_model, pd.products_name, p.products_price, p.products_weight, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.products_id = pd.products_id and p.manufacturers_id = m.manufacturers_id and pd.language_id = '" . (int)$languages_id . "' and p.products_quantity <= p.products_reorder group by pd.products_id order by $db_orderby $sorted";

 

This piece of code also needs another join with the 'catagories'-table, and look if categories_status equals 1. If not, don't show the item.

 

But since my knowledge of writing SQL-statements is limited, esp. with (inner) joins, I request the help of the others here.

 

Thanks!

Share this post


Link to post
Share on other sites

Hi

 

I am stuck with step 5. This has got me confused. Sorry for not understanding.

 

5. Update the file "admin/categories.php" with fields for products_reorder and products_reorder_to.

 

Insert the following after the PRODUCT_QUANTITY line in the new_product section:

 

<tr>

<td nowrap><font face="<? echo TEXT_FONT_FACE; ?>" size="<? echo TEXT_FONT_SIZE; ?>" color="<? echo TEXT_FONT_COLOR; ?>"> <? echo TEXT_PRODUCTS_REORDER; ?> </font></td>

<td nowrap><font face="<? echo TEXT_FONT_FACE; ?>" size="<? echo TEXT_FONT_SIZE; ?>" color="<? echo TEXT_FONT_COLOR; ?>"> <input type="text" name="products_reorder" value="<? echo @$pInfo->reorder; ?>"> </font></td>

</tr>

<tr>

<td nowrap><font face="<? echo TEXT_FONT_FACE; ?>" size="<? echo TEXT_FONT_SIZE; ?>" color="<? echo TEXT_FONT_COLOR; ?>"> <? echo TEXT_PRODUCTS_REORDER_TO; ?> </font></td>

<td nowrap><font face="<? echo TEXT_FONT_FACE; ?>" size="<? echo TEXT_FONT_SIZE; ?>" color="<? echo TEXT_FONT_COLOR; ?>"> <input type="text" name="products_reorder_to" value="<? echo @$pInfo->reorder_to; ?>"> </font></td>

</tr>

 

Change the sql statements to support the two new fields.

 

OR: just add the fields products_reorder, products_reorder_to, to each of these queries.

 

At the action == insert_product section:

if (tep_db_query("insert into products (products_name, products_description, products_quantity, products_reorder, products_reorder_to, products_model, products_image, products_url, products_price, products_date_added, products_weight, products_status, products_tax_class_id) values ('" . $HTTP_POST_VARS['products_name'] . "', '" . $HTTP_POST_VARS['products_description'] . "', '" . $HTTP_POST_VARS['products_quantity'] . "', '" . $HTTP_POST_VARS['products_reorder'] . "', '" . $HTTP_POST_VARS['products_reorder_to'] . "', '" . $HTTP_POST_VARS['products_model'] . "', '" . $HTTP_POST_VARS['products_image'] . "', '" . $HTTP_POST_VARS['products_url'] . "', '" . $HTTP_POST_VARS['products_price'] . "', '" . $HTTP_POST_VARS['products_date_added'] . "', '" . $HTTP_POST_VARS['products_weight'] . "', '" . $HTTP_POST_VARS['products_status'] . "', '" . $HTTP_POST_VARS['products_tax_class_id'] . "')")) {

 

At the action == update_product:

tep_db_query("update products set products_name = '" . $HTTP_POST_VARS['products_name'] . "', products_description = '" . $HTTP_POST_VARS['products_description'] . "', products_quantity = '" . $HTTP_POST_VARS['products_quantity'] . "', products_reorder = '" . $HTTP_POST_VARS['products_reorder'] . "', products_reorder_to = '" . $HTTP_POST_VARS['products_reorder_to'] . "', products_model = '" . $HTTP_POST_VARS['products_model'] . "', products_image = '" . $HTTP_POST_VARS['products_image'] . "', products_url = '" . $HTTP_POST_VARS['products_url'] . "', products_price = '" . $HTTP_POST_VARS['products_price'] . "', products_weight = '" . $HTTP_POST_VARS['products_weight'] . "', products_tax_class_id = '" . $HTTP_POST_VARS['products_tax_class_id'] . "', products_status = '" . $HTTP_POST_VARS['products_status'] . "' where products_id = '" . $HTTP_GET_VARS['pID'] . "'");

 

At the action == new_product:

$product_query = tep_db_query("select products_name, products_description, products_quantity, products_reorder, products_reorder_to, products_model, products_image, products_url, products_price, products_weight, products_date_added, products_status, products_tax_class_id, manufacturers_id from products where products_id = '" . $HTTP_GET_VARS['pID'] . "'");

 

At the else part of action == new_product_preview:

$product_query = tep_db_query("select p.products_name, p.products_description, p.products_quantity, p.products_reorder, p.products_reorder_to, p.products_model, p.products_image, p.products_url, p.products_price, p.products_weight, p.products_date_added, p.products_status, p.manufacturers_id, m.manufacturers_name, m.manufacturers_image from products p, manufacturers m where p.products_id = '" . $HTTP_GET_VARS['pID'] . "' and p.manufacturers_id = m.manufacturers_id");

 

 

AFTER:

$products_count = 0;

// $rows = 0; // this shouldnt be reset

 

ADD LINES:

$products_query = tep_db_query("select p.products_id, p.products_name, p.products_quantity, p.products_reorder, p.products_reorder_to, p.products_image, p.products_price, p.products_date_added, p.products_status from products p, products_to_categories p2c where p.products_id = p2c.products_id and p2c.categories_id = '" . $current_category_id . "' order by products_name");

 

 

Any suggestions?

Share this post


Link to post
Share on other sites

I followed all the steps in this and it worked good! Still that step 3 was a little off and had to tweak the code. I think a simple instruction upgrade on the instructions will make it great!

 

Thanx

 

2dogrc

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
Sign in to follow this  

×