Stock Levels

[timon_zed]

Hello good citizens,
Am trying to implement a simple web based shopping system, however I want to find out if there is a way in which i can reduce the quantity of goods in stock after customer buys just by using servlets without me having to go to the database to change the stock levels.

Any feedback would be appreciated.

[peter_budo]

In similar fashion as you query database to see what is available in stock you can link your customer purchase order action(on any website seen as button to "Confirm" after you provided delivery address and payment details) to a method in servlet to take data stored in order form to construct UPDATE query. Query will decrement you stock by certain amount, this would need to be done with sub-queries (query inside query) plus in same time you should also create new entry for new purchase order in different set of tables.
If you are more experienced with database you can even set up procedure for this sort of task.

[kvprajapati]

Don't update or subtract values. Use aggregate sql functions.
For example,
1. Calculate the sum of quantity of a specific product in your purchase
table.
2. Calculate the sum of quantity of a specific product in your sales table.
3. Current stock = Sum of qty of purchase - Sum of qty of sales.

[peter_budo]

Don't update or subtract values. Use aggregate sql functions.
For example,
1. Calculate the sum of quantity of a specific product in your purchase
table.
2. Calculate the sum of quantity of a specific product in your sales table.
3. Current stock = Sum of qty of purchase - Sum of qty of sales.

Can you provide clearer explanation as I do understand that aggregate functions are

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

and in my understanding these are used for calculation and not changing existing value in the table.

[kvprajapati]

I apologize for inconvenience.
What I said,
Stock = Sum of quantity of purchased item - Sum of quantity of sold item.

EDIT: I suggest OP that don't update (add or subtract) total quantity.

[timon_zed]

I apologize for inconvenience.
What I said,
Stock = Sum of quantity of purchased item - Sum of quantity of sold item.

EDIT: I suggest OP that don't update (add or subtract) total quantity.

I get the jist of what you're saying adatapost, so would this mean that i would have to add an update query within the same servlet that is processing the purchases made by customers?

I love to learn but I hate being taught - Winston Churchill

[timon_zed]

Here's something i came up with....

<form name="purchase" action="Purchase" method="POST">
            <table border="1">
                <thead>
                    <tr>
                        <th></th>
                        <th></th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td>Product Number</td>
                        <td><input type="text" name="ProdNum" value="" maxlength="7" /></td>
                    </tr>
                    <tr>
                        <td>Quantity</td>
                        <td><input type="text" name="quantity" value="" /></td>
                    </tr>
                    <tr>
                        <td><input type="reset" value="Clear" name="clear" /></td>
                        <td><input type="submit" value="Purchase" name="purchase" /></td>
                    </tr>
                </tbody>
            </table>

        </form>

The associated servlet code sample...

String ProdID = null;
        String ProdName = null;
        String Price1 = null;
        String Num = null;
        int cost,amt,value;
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:Concept","admin","");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery ("SELECT ProductID, PName, Price FROM Stock");
            
            while(rs.next())
            {
                ProdID = rs.getString("ProductID");
                if(ProdID.equals(request.getParameter("ProdNum")))
                {
                    ProdName = rs.getString("PName");
                    Price1 = rs.getString("Price");
                    value = Integer.parseInt(Price1);
                    Num = request.getParameter("quantity");
                    amt = Integer.parseInt(Num);
                    cost = amt*value;
                    out.print(ProdID +'\t');
                    out.print(ProdName + '\t');
                    out.print(Num + '\t');
                    out.print("K "+ cost);
                    out.println('\t'+"<a href = purchase.jsp>Back</a>");
                }
            }
            con.close();
            rs.close ();
            st.close ();
        }
        catch(Exception e)
        {
            System.out.print("Unable to find DB");
        }
    }

What i need now is to find a way in which i can update the DB table by reducing the stocks of the purchased comodity just using this same servlet. Or do i need to write another servlet that would do that for me?