insomniac2 Posted February 28, 2006 Posted February 28, 2006 (edited) 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 February 28, 2006 by insomniac2 Quote
insomniac2 Posted April 9, 2006 Author Posted April 9, 2006 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. Quote
vox Posted April 21, 2006 Posted April 21, 2006 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 ? Quote
insomniac2 Posted April 21, 2006 Author Posted April 21, 2006 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. Quote
vox Posted April 22, 2006 Posted April 22, 2006 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 !!!! Quote
vox Posted April 22, 2006 Posted April 22, 2006 Found it ! Must pay more attention to my cut & paste................................ Thanks very much for the help in spelling it out for me. Regards Bob Quote
insomniac2 Posted April 22, 2006 Author Posted April 22, 2006 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. Quote
insomniac2 Posted April 22, 2006 Author Posted April 22, 2006 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. Quote
astronomad Posted May 2, 2006 Posted May 2, 2006 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 Quote
insomniac2 Posted May 2, 2006 Author Posted May 2, 2006 Thanks astronomad .. I never even thout about that. I will add it to the next release if I get time. Quote
ygeffens Posted October 18, 2006 Posted October 18, 2006 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! Quote
insomniac2 Posted October 18, 2006 Author Posted October 18, 2006 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! Quote
cdamianou Posted November 3, 2006 Posted November 3, 2006 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? Quote
Guest Posted May 22, 2007 Posted May 22, 2007 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 Quote
New 
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.