Stock control

[warrenphillips25336771]

Can someonel please give an easy explanation on how to do a very simple stock control using ecart, can it be hand coded or not? just a simple on is needed.

[Ray Borduin]

There are a couple of possible techniques. My favorite is to add an inventory field to your products database and store the insert date or update date when the inventory number was set.

Then make sure on your checkout that you are properly inserting an order date in the orders table and inserting the product details in the order details table.

With that done, you have enough information to do inventory control on your results and details pages.

With a little SQL you can subtract the number sold after the date the inventory was last updated and retrieve the new available quantity.

The only hand-coding necessary is to handle making sure people don't update the cart to a higher quantity if you allow quantity update, or to allow purchases up to that quantity and not more for items available in limited quantities. The same code can also be used to make sure the quantities are still available at checkout and that someone else didn't beat the user to the sale of a limited quantity item.

We will be releasing a store solution dealing with this concept built with eCart to help serve as a functional example.

[warrenphillips25336771]

There are a couple of possible techniques. My favorite is to add an inventory field to your products database and store the insert date or update date when the inventory number was set.

Then make sure on your checkout that you are properly inserting an order date in the orders table and inserting the product details in the order details table.

With that done, you have enough information to do inventory control on your results and details pages.

With a little SQL you can subtract the number sold after the date the inventory was last updated and retrieve the new available quantity.

The only hand-coding necessary is to handle making sure people don't update the cart to a higher quantity if you allow quantity update, or to allow purchases up to that quantity and not more for items available in limited quantities. The same code can also be used to make sure the quantities are still available at checkout and that someone else didn't beat the user to the sale of a limited quantity item.

We will be releasing a store solution dealing with this concept built with eCart to help serve as a functional example.

Thanks ray

The problem is I spent alot of money on the web assist super suite, now your bringing something out which in my view ecart should have included, so you now want more money off me just for this, come on be fair and release it with an updated working version of ecart and give us our moneys worth,

[Ray Borduin]

The distinction is between the tools and the solution... You argue that since you built the tools, you should get the solution. That is like an architect arguing that when he bought the wood and nails and hammers he should get a completed house.

With the super suilte you got the equivalent of unlimited building materials and heavy equipment.... you still need to build the house.

The store solution will be our application of all of the tools in the suite to build our own version of a store. We will be the architect. It will not be THE solution, but A solution.

Some people may want to buy the site without needing to learn how to use the tools... they might not even own the tools or DW. Some might want the site and the tools so they can make additions. Some might just want the tools.

People buy the super suite for many applications, not just physical goods stores with inventory control. Perhaps we should include all solutions in the super suite? We could do that, but we would have to charge more for it.

Maybe we should have an Ultra-Suite that includes a copy of all solutions, but I think some people would still only want to pay for the suite since they don't need that solution.

One way or the other, if we put a lot of work into new functionality, we will need to charge for it, otherwise we wouldn't be able to do the work in the first place.

[warrenphillips25336771]

Ray

I understand what your saying, and I agree totally with what your trying to get at, but ecart is that, it is a shopping cart that allows us to put products on to sell, whether it be one item or 50, all i was trying to say is, can it not be included? I am still new to php mysql and things like that, but i am learning, I was only asking can it not be included. I would most probably still buy the darn thing as it would make my life easier,

[Ray Borduin]

I don't necessariliy disagree with you either. The store will not just be a great example of eCart. It will be a great example of Validation Toolkit, Security Assist, Dynamic Web Charts, Universal Email, Google Maps, and Data Assist all working together.

Because it is a whole new paradigm for us to start integrating our products into solutions, it really didn't fit into the existing suites, and we plan on doing quite a few different solutions and didn't want to have to raise the tools cost by including pre-packaged solutions people might not need.

We are listening, so we appreciate the feedback, and we will continue to work to make your life easier.

[warrenphillips25336771]

I am just running through ecart and on the pdf it shows sending an email receipt, but i am not getting this option, (before you ask yes i have the full suite installed). any ideas why?

[Ray Borduin]

It should be based on WA Universal Email... what version of Universal Email do you have installed?

[warrenphillips25336771]

I have the latest version ray, as my pc crashed and i had to re-install all the extensions, I have done the cart now, would it be possible to add this feature?

[warrenphillips25336771]

my mistake, not been activated yet, still can i add this feature after doing the cart?/#

[warrenphillips25336771]

now i get this messaage when going to confirm.php

Unknown column '3c25b3d246fbd0ff137371bbeec89dc2' in 'where clause'

any ideas?

[Ray Borduin]

This most likely means that a field is marked as a number field when it is actually a text field in the store cart summary in database server behavior on that page.

[warrenphillips25336771]

blimey, how does one who does not know php check this? :)

[warrenphillips25336771]

checked it and all is how it should be

[Ray Borduin]

You may need to post a support incident. Someone will need to look at the code on the page to debug it.

[Art]

When will this "solution" be available to download/purchase?

[Ray Borduin]

It looks like probably tomorrow... We are making final preparations now.

[warrenphillips25336771]

see the store thing is out, looks good, but pricey, if i spend anymore money i will be bankrupt :) but i still feel stock control should be part of ecart

[Ray Borduin]

Stock control is done in your catelog and administration. I understand your point, and we may eventually update eCart to handle catelog and administrative controls, but for now those things are handled in other tools like DataAssist and require a bit of database design and SQL statement writing to accomplish.

Really all of the functionality is there... the PowerStore is an example of that fact since it was built with eCart along with the other tools in the super suite.

[warrenphillips25336771]

I know the functionality is there but the reason i came to WA is because you claimed beginners with no programming experience could use it and i agree with you on that, but how does a person who does not know php get stock control implemented without clogging up the forums?

[Jon Gibson]

Warren:

I have a lynda.com account and I strongly recommend purchasing a one month pass ($25) and working through the Kevin Skoglund video series. I would be more than willing to help you if you get stuck in the video series, as I got stuck in three or four spots. Going through that series gave me a solid understanding of how PHP and MySQL work together.

The fact of the matter is these products enable people like me (part-time designer) to do back-end work without having to pay an expert $100-$200 an hour. Add to that the help of Ray and others on this forum and I have successfully completed my first e-commerce site in less than two weeks.

I think your question is a good one (inventory control) and I will look into as well, because I know that I may need that down the line too. I say keep the thread going and allow others to chime in so that we can work together to find a solution.

Best wishes,
Jon

For MySQL questions: http://www.mysql.com

[Ray Borduin]

Since eCart already inserts the sold inventory into the orderdetails table, all you need to do is store the inventory on hand as well as the date when that inventory number was updated... then you can account for the number sold from that date in your catalog.

The query would looks something like:
SELECT *, (ProductStock - (SELECT Coalesce(Sum(DetailQuantity),0) FROM orderdetails INNER JOIN orders ON OrderID = DetailOrderID WHERE DetailProductID = ProductID AND OrderDate > ProductUpdateDate)) AS NumLeft FROM products LEFT OUTER JOIN productcategories ON ProductCategoryID = CategoryID WHERE ProductLive <> 0

At that point you could display or use the number left or use that information to optionally show an out of stock message.

[warrenphillips25336771]

Hi jon and ray

thanks to you both, I have used lynda.com but due to my job (i drive truck here in the uk) i only part time design so getting to watche the darn videos is a pain, I got the stock control done for $20 by a coder, so all is not lost but ray, where was that answer a long time ago? :) I know i need to learn it, i wish i had the time for someone to sit down and teach me php so that i can just code like i do with html/css. but i agree lets keep the thing going for now, this is the code used on my cart.php page

On my cart.php page it is like this (just to make sure they can't add anything if bought before checkout and passed to paypal,)

$avail_stock = $_POST['txtstockQty'];
mysql_select_db($database_beads2, $beads2);
$query_RSclasses = "SELECT * FROM content WHERE ContentID = 2";
$RSclasses = mysql_query($query_RSclasses, $beads2) or die(mysql_error());
$row_RSclasses = mysql_fetch_assoc($RSclasses);
$totalRows_RSclasses = mysql_num_rows($RSclasses);

<script type="text/JavaScript">
function checkstock(v1,name1){
var value1=v1;
var x=name1.length;
var pnum = name1.substring(15,name1.length);
var cname = "qty" + pnum;

var avail_stock=document.getElementById(cname).value;
var pur_stock = document.getElementById(name1).value;
if(parseFloat(avail_stock)<parseFloat(pur_stock)){
alert("The available stock is "+avail_stock);
for (var i=0;i < document.cartfrm.elements.length; i++) {
if (document.cartfrm.elements[i].type=="text" && document.cartfrm.elements[i].name==name1) {
document.cartfrm.elements[i].value="";
document.cartfrm.elements[i].focus();
return false;
}
}
}

}
</script> (feel free to use if needed)

and also this

<?php
while (!$Beads->EOF()) {
$item_id = $Beads->DisplayInfo("ID");
mysql_select_db($database_beads2, $beads2);
$sql = "SELECT * FROM items WHERE ItemID=".$item_id;
$Result = mysql_query($sql, $beads2) or die(mysql_error());
$rs = mysql_fetch_assoc($Result);
$stock_qty=$rs['ItemAv'];

?>

ray please correct me if you feel anything like this is wrong. Hope this helps people out there,

[Mark Kujoy]

I personally think stock control should be part of eCart as well. I've been using eCart since version 1 after moving from UltraCart which did have stock control as standard. I'm sorry to say that between version 1 and 4 theres not been many new features added. Sure you get more payment gateways and improved code etc and a few more wizards, but ecommerce has moved on along way since version 1 was released and its time customers were listened to and a more rounded extension was delivered. I think the big feature in version 4 was it was all CSS based, but the standard of the layouts and images was poor compared to the 3.

You do market this as a e-commerce solution. Its time it really was a beginning to end product.

[Ray Borduin]

Our powerstore solution has inventory control and uses eCart. eCart has the capacity for inventory control now, but inventory and control of it is a back end concept and eCart doesn't really go there at all. Having said that, we agree and we plan on working toward that direction.

The problem with inventory control and options, two of the largest feature requests for ecart, is that they are so dependent on the database. The database queries, database structure, and administrative back end become more important and difficult to create. In fact, the most difficult aspects to inventory control are writing your SQL queries, conceptualizing the database, and creating the administrative interface and have nothing to do with the eCart or any of its functionality.

Currently our solution is to use DataAssist for creating the Administrative back and and the Catalog. Since that is where the difficulty in inventory control exists, we would probably have to account for it there, and since the most difficult task is writing the SQL statements, it is kind of hard to actually include this as a feature at all.

Really it becomes an education task to help people understand the database design and queries necessary to make it work with our current tools.

Another approach that we may take in the long run is to include DataAssist with eCart and create a catalog builder and product admin builder. If you could build a catalog with inventory control and an administrative section to update inventory, eCart functionality really isn't and wouldn't be the problem.

Our eventual solution will probably involve a combination of dataAssist, siteAssist, and eCart as well as all of our other products, so that they can work together to create an entire store. Currently the powerstore is an example of what we hope that wizard might create. It will continue to evolve and we will continue to work toward creating wizards so that users can more easily create these complex solutions on their own.

These are the problems we are working on. The problem isn't convincing us that we want inventory control or options to be easy for our users, we all agree those are features that would be great to have. The problem is how to implement them and figuring out what exactly the missing pieces are.

In my opinion the most difficult task in inventory control is writing the SQL query in the DW Recordset. If we want to make that easier outside of education then we may need to rewrite the Recordset... anyway it is a complex problem, and one that we are very educated on and aware of.

We need to put all of our tools together to make a beginning to end product. We plan on making that happen, but it will still take years before that vision is complete.

[glmcps295424]

Ray,

While we are waiting for all of this perhaps a series of video tutorials that deals with the coding concepts involved would be a big help to many of us. Realizing that that can be time consuming perhaps you could create a way for third parties to create and post videos and let the community vote on the usefulness of their efforts. Those with very good ratings might recieve some price breaks on future products.

This COULD provide the needed assistance without taxing your resources.

Just a thought,

Gregg

[Mark Kujoy]

I can see your point. E-commerce is such a varied market. What with different products and shipping requirements and the rest of a long list. I know the headaches well, a more encompassing expanded product in the future would be great.

Is powerstore site available as a downloadable demo so i could look over the code? For the CMS backend stuff i use ADDT so dont have siteassist or dataassist.

TIA Mark.

[Mark Kujoy]

Just had a look at your main site again. I didn't realise you sold powerstore as an product.

:)

[Ray Borduin]

You can view a live demo of the code and how it all works. You have access to the back end and reports and can process fake transactions to see the customer workflow.

http://www.powerstore-demo.com/

It is built using SecurityAssist, DataAssist, Dynamic Web Charts, eCart, Cookies Toolkit, Validation Toolkit, CSS Sculptor, CSS Menu Writer, Google Maps Pro, Universal Email, and other products. It includes the Contact Form Solution, and the User Registration Solution rolled into a web site with eCart. It is a relatively simple and effective example of putting together our tools to create an application.

[Sades]

You can view a live demo of the code and how it all works. You have access to the back end and reports and can process fake transactions to see the customer workflow.

http://www.powerstore-demo.com/

It is built using SecurityAssist, DataAssist, Dynamic Web Charts, eCart, Cookies Toolkit, Validation Toolkit, CSS Sculptor, CSS Menu Writer, Google Maps Pro, Universal Email, and other products. It includes the Contact Form Solution, and the User Registration Solution rolled into a web site with eCart. It is a relatively simple and effective example of putting together our tools to create an application.

We really really don't want to spend more money on that, i already spent a lot with the super suite, thinking the ecommerce solution on it did what its suppose, which should include inventory control, i feel like i was scam, now that im needing to discount the amount of products sold from the stock i cant and now i need to learn php to do it, the power store is nice but we are suppose to build something like that without programming knowledge with ecart or the super suite, the worse part is that i thought it did that until i got a call from a client asking me why the stocks where not been discounted, if this feature is not included in ecart some day sooon then we have been scamed on a poor solution or partial solution for ecommerce been force to buy other products, or at least a recipie guide on how we can do stock control like in the powerstore if it was really made using only webassist products we need a guide on this!!!!!

[Ray Borduin]

Building the powerstore by hand, even with our tools, takes a lot of knowledge of php, mySQL, and mastery of our product line. An assumption that you could create any possible solution without any knowledge of the underlying code or products once you purchase the super suite is not accurate, so I apologize you were under that assumption.

Discounts can be easily added with eCart to the powerstore or a store you build on your own. There is existing eCart documentation and tutorials on how to add discounts. Inventory control is a little trickier, since, at least in powerstore, some techniques require writing complex SQL to deduct inventory and check inventory levels as items are sold. You need to understand your database and how to reference data from it in order to be successful and unfortunately we don't have a tutorial on this specific subject available at this time, but it is implemented in the powerstore solution.

[virtualbr]

Ok, let me expose what i think.
Stock control, although can really be trickier, in my point of view, is as crucial for a store as is the "add to cart" functionality.

I know this can be rather easily implemented usiing other extensions and probably simple coding, probably not a big deal, BUT I do believe it would be very good if WA would have the courtesy of developing a Recipie ou tutorial adresssing this subject.

I do understand you can point costomers to buy a new package with the pre built store, wich is very considerate, but I dont see this as a real solution for eCart costomers.

I would truely like to see more interest of WA in providing solutions in this direction for eCart.

But I do understand that there are millions of things that can change in this enviroment that can affect the aplication of a single solution. Still, would be great to have an easy WA style solution for this that could be built by users, instead of having to buy a new ecommerce solution.

Thanks for the oportunity to have my opinion expressed.

[Ray Borduin]

We appreciate the feedback and suggestions. Keep them coming and we'll do our best to put effort toward what people need the most.