How can I update the record in one table with value entry of another table as soon as the value is entered?

[mostwanted]

I have a website where I am selling items, what I desperately want to achieve is to be able to show the buyers the remaining number of each item. I have 2 tables, the table that has all the items being sold & has a field amount which is the current number of items in stock and the other table which records sales as customers buy and has a field quantity which is the quantity of items purchased. After a customer has made a purchase I want to be able to subtract quantity from amount and have difference update and be the new value for amount.

I wrote some controller code which is not achieving this, it is instead updating the amount field for all items with the same figure.

THE VIEW

{{extend 'layout.html'}}

    <div class="row">
{{for p in products:}}
        {{if p.product_type=="Earrings":}}
<div class="clothes">
    <h4 style="color: #ff69b4;">{{=p.name}}</h4>
    <h7 style="color: #ff69b4;">{{=p.amount}} available in stock</h7>
    <h5>
        <span style="color: aqua; font-weight: bold;">{{=MoneyFormat(p.price)}}</span>
    </h5>
    <img class="magnify" src="{{=URL('download',args=p.image)}}" height="200px"/>
    <br />
    <span id="{{='item%s'%p.id}}" style="font-weight: bold; color: red;">{{=session.cart.get(p.id,0)}}</span> in cart - {{=A('add to cart',callback=URL('cart_callback',vars=dict(id=p.id,action='add')),target='item%s'%p.id,_class='button pill')}}
    <br />
    <span style="font-size:12px;font-weight: bold; color: #ff69b4;">Click the image to enlarge</span>
<br />
</div>
{{pass}}

{{pass}}
        </div>

THE CART_CALLBACK FUNCTION

def cart_callback():
    id = int(request.vars.id)
    if request.vars.action == 'add':
        session.cart[id]=session.cart.get(id,0)+1
    if request.vars.action == 'sub':
        session.cart[id]=max(0,session.cart.get(id,0)-1)
    return str(session.cart[id])

MY FUNCTION FOR UPDATING AFTER PURCHASES

def index():
    if not auth.user:
        response.flash=T('PLEASE LOG IN FIRST TO BE ABLE TO GET THE MENU AND BUY')
    products = db(db.product).select(orderby=db.product.name)
    num=db(db.sale).select()
    for n in num:
        quantity=n.quantity
        if quantity is None:
            quantity=0
        for p in products:
            amount1=p.amount-quantity
            db(db.product.amount).update(amount=amount1)
    return locals()

What should happen is that every time a customer buys whatever number of items a new figure showing reduction in number of items should be displayed.

Regards;

Mostwanted

[Dave S]

index() looks like it spends a great deal of time subtracting everything in sales from everything in products.

I think you are recording the product id in the cart.  Are you then (on check-out, I presume) recording the product id in db.sales?

if so , then you don't need the select() on all products.
Instead, use the product id in each row of db.sales to select the product entry to update.

You probably want a way to identify which rows in db.sales have already been processed (that is, which quantities have already been subtracted from the the available in the corresponding product entry).

/dps

 

[mostwanted]

Everything you are saying i understand but I cant put into code, every attempt I have made all day today just went up on traceback-flames! I am recording sales made into the sales db through the buy function:

def buy():
    if not session.cart:
        session.flash = 'Add something to shopping cart'
        redirect(URL('index'))
    invoice = session.invoiceNo
    total = sum(db.product(id).price*qty for id,qty in session.cart.items())
    for session.key, session.value in session.cart.items():
        db.sales.insert(invoice=invoice,buyer=auth.user.id,product = session.key,quantity = session.value,price = db.product(session.key).price)
       
    session.cart.clear()
    session.flash = 'Sale Complete'
    redirect(URL('invoice',args=invoice))
    return dict(cart=session.cart,form=form,total=total)

My cart doest really do alot,

def cart():
    if not session.cart:
        session.flash = 'Add something to shopping basket'
        redirect(URL('index'))
    total = sum(db.product(id).price*qty for id,qty in session.cart.items())
    vat=round(total*0, 2)
    totalPrice=total+vat
    session.invoiceNo="".join([random.choice(string.ascii_uppercase)\
                   for i in range(4)])+"-"+"".join([random.choice(string.digits)\
                                                    for i in range(4)])+"-"+"".join([random.choice(string.ascii_uppercase)\
                                                                                     for i in range(4)])+"-"+"".join([random.choice(string.digits)\
                   for i in range(4)])
   
    #CONVERT TO INTEGER TO BE ABLE TO UTILIZE {{=MoneyFormat()}} FUNCTION
    text=request.vars.name
    if text is None:
        text=int(0)
    if text:
        text=int(request.vars.name)
        change=float(text)-totalPrice
    else:
        change=int(0)
    return dict(cart=session.cart, total=total, totalPrice=totalPrice, vat=vat, text=text, change=change, invoiceNo=session.invoiceNo)

From what you said I felt if i could identify the sold items in the sale table and match them against the items in the product table and then from there update those that have the same id. As a concept in my head its easy but implementing it goes side ways:

Mostwanted

[isi_jca]

Hi!!!,

When you made a sale, only must to update the products sold.

For example:

         db(db.product.amount.id = product_id ).update(amount=amount1)

[villas]

Sorry to hear you are struggling with this.

vat=round(total*0, 2)

The above line would always produce: vat == 0.0

which doesn't seem intentional.

text=int(request.vars.name)

To make your program more robust, I suggest using try...except to catch any errors in the above line.

[mostwanted]

I figured if I could update the amount field in db.product within the buy functiontion just around the same time i'm saving inside the db.sale table when purchasing then it should be easy but I am having a really hard time identifying only the purchased items by their ids & subtracting their quantities from similar items in db.product!

The code below is able to update the amount in db.product but it updates for all products which is not what i want

CODE SAMPLE 1:

for item1 in item1:
            diff=item1.amount-value
            db(db.product.amount).update(amount=diff)

Then i made another desperate attempt with the code below but it is not updating anything at all!

for item1 in item1:
            for id, k in session.cart.items():
                if item1.id==k:
                    diff=item1.amount-value
                    db(db.product.amount).update(amount=diff)

Anyone who can do this better please help.

Regards;

Mostwanted

On Thursday, September 5, 2019 at 8:21:56 AM UTC+2, Dave S wrote:

[Dave S]

On Tuesday, September 10, 2019 at 2:54:43 AM UTC-7, mostwanted wrote:

I figured if I could update the amount field in db.product within the buy functiontion just around the same time i'm saving inside the db.sale table when purchasing then it should be easy but I am having a really hard time identifying only the purchased items by their ids & subtracting their quantities from similar items in db.product!

Don't you record the id of the item in your shopping cart?

 

The code below is able to update the amount in db.product but it updates for all products which is not what i want

CODE SAMPLE 1:

for item1 in item1:
            diff=item1.amount-value

I don't think you want this line:   

            db(db.product.amount).update(amount=diff)

I think it is selecting everything in db.product that has an amount.

Once you figure out how to get the id from your shopping cart, the line should probably be

                db(db.product.id == cart_item.id).update(amount=diff)

 

Then i made another desperate attempt with the code below but it is not updating anything at all!

for item1 in item1:
            for id, k in session.cart.items():
                if item1.id==k:
                    diff=item1.amount-value
                    db(db.product.amount).update(amount=diff)

Anyone who can do this better please help.

Regards;

Mostwanted

/dps

[villas]

for item1 in item1:


I mentioned this to you before -- please do not do this! 

It is a really bad practice which will cause terrible problems for you.

For example, do this instead:

for i in item1:


Best wishes for your program.

[mostwanted]

Thank you everyone for your input, i was finally able to solve my problem, i added these two lines & erased everything else (smile) & it worked like a charm.


diff=db.product(key).amount-value
db(db.product.id==db.product(key).id).update(amount=diff)




The whole code:

total = sum(db.product(id).price*qty for id,qty in session.cart.items())

for key, value in session.cart.items():
        diff=db.product(key).amount-value
        db(db.product.id==db.product(key).id).update(amount=diff)
        db.sale.insert(invoice=invoice,buyer=auth.user.id,product = key,quantity = value,price = db.product(key).price)

Regards;




Mostwanted

[mostwanted]

Sorry @villas its just a bad habit that i have somehow over time gotten used to but i will heed your advice, thank you.

Mostwanted

[villas]

Well done for getting it working...

We're all learning from each other here!  :)