Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Inventory Report v1.0 beta


insomniac2

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
Link to comment
Share on other sites

  • 1 month later...
  • 2 weeks later...

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 ?

Link to comment
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.

Link to comment
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 !!!!

Link to comment
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.

Link to comment
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.

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

  • 5 months later...

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!

Link to comment
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!

Link to comment
Share on other sites

  • 3 weeks later...

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?

Link to comment
Share on other sites

  • 6 months later...

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...