April 1, 2014 at 6:01 am
Using a self developed a highly specialized shopping cart in (VB.Net) with SQL Server 2008.
“Cart” data ([CartID],[CartNumber], [Status], [StatusDate]) is stored in the CART Table.
“Item Order” ([ItemID],[CartNumber], [Item Description], [PickStatus], [OrderStatus]) is stored in the ORDERS Table. Common Key between two tables is is [Cart Number].
Le’ts assume that a Shopping Cart is started (Status=1 Open) and 3 items are added.
CART has 1 entry and ORDERS has 3 entires, one for each item ordered.
CART:
001, 1, 1, 04/04/2014
ORDERS:
001, 1, Erasers, 4, 1
002, 1, Pencils, 4, 1
002, 1, Chalk, 4, 1
As each item is reviewed by staff picking the order, the item [Status] is updated it is assigned one of several status’
1= Item Picked
2= Item BackOrdered
3= Item Out for Delivery
4= Item Added to Cart
A column in the ORDERS table (PickStatus) is assigned a status for each item, intially 4 when the item is added, but changed to:
After Item 1 is reviewed it may be assigned status 1
After Item 2 is reviewed it may be assigned status 2
After Item 3 is reviewed it may be assigned status 1
SO, the CART and ORDERS table look like:
CART:
001, 1, 1, 04/04/2014
ORDERS:
001, 1, Erasers, 1, 1
002, 1, Pencils, 2, 1
002, 1, Chalk, 1, 1
At This point, technically this Cart is still OPEN, and when I do a search by Cart for all Open carts, all carts with status 1 (Open) will display.
At a later point Item 2 (the Back-Ordered Item) has benn received, is now reviewed and it is assigned Status = 1 (Item Picked).
It is at this time that I want to change the status of the CART from 1=Open to 2=Closed. So its only after the last item assigned to that CART is completed is when I want to change its status. The cart size is unlimited, so I cant count the number of items and when the last is reached, make the Status Change.
I hope I have explained this problem sufficiently …Is there a way to handle this scenario… or a better way ?
Thanks!
April 1, 2014 at 7:49 am
You could handle this two ways: 1) Create a trigger AFTER UPDATE to update the record as needed, or 2) Handle the change via your VB.Net code.
I'd probably opt for option 2 as I tend to steer away from triggers when possible
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 1, 2014 at 9:04 am
Thank you for responding.
Based on your reponse I gave it some more thought.
I ended up creating another action option = "Picked - Order Complete... an action that the user has to trigger. Then, in the stored procedure I check the incoming status and depending on which is submitted, an appropriate update command is executed and the status of the cart assigned.
Thanks for the "Trigger"!
April 2, 2014 at 8:23 am
You could do something like the following to update the Cart status after each update of an order item.
update Cart
set Status = OverallStatus
from Cart inner join
(Select Cart2.CartNumber,
case when sum(Orders.PickStatus) = count(Orders.PickStatus) then 1 else 2 end as OverallStatus
from Cart as Cart2 inner join Orders on Cart2.CartNumber = Orders.CartNumber
group by Cart2.CartNumber) as ItemList
on Cart.CartNumber = ItemList.CartNumber
I'm fairly sure the above could be tidied up considerably, but it worked with the test data from your original post.
April 4, 2014 at 5:37 am
Thank you Chris. I will try that!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply