BV Commerce Forum
»
BV Commerce Support
»
Development
»
Help with omitting sale items from offers
Rank: Member
Joined: 4/6/2006(UTC)
Posts: 27
|
Was wondering if anyone knows how this could be accomplished. I have a custom offer set up which includes a set of products and a minimum order quantity must be met for the offer to apply. I'd like to have it so that if one of the items specified in an offer is in a sale that is currently running, the offer would not use that item to meet the minimum quantity. I set something up that seemed to work but had performance issues probably due to too many nested for loops. Any help is much appreciated. Thanks.
|
|
|
|
|
|
Rank: Member
Joined: 8/17/2006(UTC)
Posts: 681
|
Williams,
I'm not sure I understand what you try to do but if you post/send your code I'll try to optimize it.
Regards,
Corneliu. |
|
|
|
|
|
|
Rank: Member
Joined: 4/6/2006(UTC)
Posts: 27
|
Thanks for the response Corneliu. Here's what I got so far: Code:
[color=#0000ff]End[/color][color=#0000ff]Function
End[/color][color=#000000] [/color][color=#0000ff]Class
[/color]
Thanks
|
|
|
|
|
|
Rank: Member
Joined: 4/10/2006(UTC)
Posts: 462
|
I definitely see places where optimizations can be made.
My first suggestion would be replace all your "custom" sql code with Bvc business objects. While this won't necessarily make the task run any faster as behind the scenes, the Bvc core is basically running the same stuff, it will make your efforts much cleaer and easier to optimize.
For example, the whole section you programmed on retrieving the currently running sales can be collapsed to call the
Sale.GetAllValidSales function which returns an array of current sales. You can then use the length of the array to determin if any sales exists and if so loop through the Sale array business object. |
Netriplex Corporation<br /> |
|
|
|
|
|
Rank: Member
Joined: 8/17/2006(UTC)
Posts: 681
|
Williams,
Try this one. I haven't tested it but it should work ;)
Public Overrides Function Execute(ByVal context As BVSoftware.Bvc5.Core.BusinessRules.OrderTaskContext) As BVSoftware.Bvc5.Core.Marketing.DiscountQueue
Dim discounts As New DiscountQueue
' This offer is only good if the minimum and maximum quantities are met.
' This code gets the mix and match product count.
Dim productcount As Integer = 0
' it seems we check agains the context.Order.Items.ProductId several times to see if it exists
Dim orderItemsDictionary As New Generic.Dictionary(Of String, LineItem)
For Each lineitem As LineItem In context.Order.Items
orderItemsDictionary.Add(lineitem.ProductId, lineitem)
Next
Dim li As LineItem
For Each setting As ComponentSettingListItem In _products
If orderItemsDictionary.TryGetValue(setting.Setting1, li) Then
productcount += li.Quantity
End If
Next
' Looks for which items are currently on sale
Dim MyConnection As SqlConnection
Dim MyCommand As SqlCommand
Dim dtrReader As SqlDataReader
Dim connString As String
Dim prodCount As Integer
Dim n As Integer
prodCount = 0
connString = ConfigurationSettings.AppSettings("ConnectionString")
'MyConnection = New SqlConnection(connString)
'MyCommand = New SqlCommand("select count(productid) as ProductCount from bvc_salexproduct where saleid in (select bvin from bvc_sale where enddate > getdate() and startdate < getdate())", MyConnection)
'MyConnection.open()
'dtrReader = MyCommand.ExecuteReader()
'If dtrReader.HasRows() Then
' dtrReader.Read()
' prodCount = dtrReader("ProductCount")
'End If
'dtrReader.Close()
' If there are items on sale, store them in an array and then loop through decrementing productcount
Dim products As New Collection(Of String)
MyCommand.CommandText = "select productid from bvc_salexproduct where saleid in (select bvin from bvc_sale where enddate > getdate() and startdate < getdate())"
dtrReader = MyCommand.ExecuteReader()
While dtrReader.Read()
products.Add(dtrReader("productid"))
End While
dtrReader.Close()
If products.Count > 0 Then
' this contins a list of the products part of the order (what used to be part of the two for/each in the second half of this statement
Dim productsInOrder As New Generic.Dictionary(Of String, LineItem)
For Each productid As String In products
If orderItemsDictionary.TryGetValue(productid, li) Then
productcount -= li.Quantity
productsInOrder.Add(productid, li)
End If
Next
' If the minimum and maximum order amount is met, then mark each discounted item
' in the order by adding a discount up to the maximum quantity.
If productcount >= _minimumQuantity And productcount <= _maximumQuantity Then
For Each setting As ComponentSettingListItem In _products
If productsInOrder.TryGetValue(setting.Setting1, li) Then
discounts.AddDiscount(li.Bvin, _discountAmount, DiscountQueueItemType.LineItem)
End If
Next
End If
Else
If productcount >= _minimumQuantity And productcount <= _maximumQuantity Then
For Each setting As ComponentSettingListItem In _products
If orderItemsDictionary.TryGetValue(productid, li) Then
discounts.AddDiscount(li.Bvin, _discountAmount, DiscountQueueItemType.LineItem)
End If
Next
End If
End If
Return discounts
End Function
Regards,
Corneliu. |
|
|
|
|
|
|
Rank: Member
Joined: 4/10/2006(UTC)
Posts: 462
|
Hi,
This was not meant to be competitive, but I had some time today and did not realize Corneliu already posted a response. But you may want to try this as your execute function. As I do not have any data to test it on, I'm unsure if this was overoptimized for what you are trying to accomplish but I heavily commented it to make sure I got your intentions right.
Public Overrides Function Execute(ByVal context As BVSoftware.Bvc5.Core.BusinessRules.OrderTaskContext) As BVSoftware.Bvc5.Core.Marketing.DiscountQueue
Dim discounts As New DiscountQueue
' This code gets the mix and match product count.
Dim productcount As Integer = 0
' Looks for which items are currently on sale
Dim curSales As Sale() = Sale.GetAllValidSales()
' Loop each lineitem in the order
For Each lineitem As LineItem In context.Order.Items
' Loop each product available in the Mix and match promotion
For Each setting As ComponentSettingListItem In _products
' Check that the current product associated with the line item is
' part of the mix and match promotion by comparing to the productid in
' the mix and match promotion setting list
If String.Compare(setting.Setting1, lineitem.ProductId, True) = 0 Then
' If the product is part of the mix and match promotion we need to verify
' whether the product is on sale as sale items are excluded from the mix
' and match promotion
For Each curSale As Sale In curSales
' BVC5 GetValid Sales SP currently does not verify whether a promotion
' has started yet, so we manually check here
If curSale.StartDate < DateTime.Now Then
' If the current sale in the all valid sale loops applys to the
' current product, we skip the product
If Not curSale.IsApplicableToProduct(lineitem.ProductId) Then
' otherwise we add the lineitem to the total product count
' to keep track of whether enough product was ordered to qualify
' for the promotion
productcount += lineitem.Quantity
' We also apply the discount to the discount queue
discounts.AddDiscount(lineitem.Bvin, _discountAmount, DiscountQueueItemType.LineItem)
End If
End If
Next
End If
Next
Next
' This offer is only good if the minimum and maximum quantities are met.
If productcount >= _minimumQuantity And productcount <= _maximumQuantity Then
Return discounts
Else
Return New DiscountQueue
End If
End Function |
Netriplex Corporation<br /> |
|
|
|
|
|
Rank: Member
Joined: 4/6/2006(UTC)
Posts: 27
|
Thanks so much for the responses. I tried each of the code that was posted and there was a little problem with Corneliu's code which gave this error:
<H2>Compilation Error </H2>
[color=#ffffcc>
[tr ][td ][/tr][tr ]<TD><CODE>
Line 124: If productcount >= _minimumQuantity And productcount <= _maximumQuantity Then
Line 125: For Each setting As ComponentSettingListItem In _products
[color=red>Line][/color]Line 127: discounts.AddDiscount(li.Bvin, _discountAmount, DiscountQueueItemType.LineItem)
Line 128: End If</PRE>[/code][/td][/tr]</TABLE>
The code from bvuser seems to work well except that when there is no valid sale running it will not apply the offer. I believe this is because the curSale variable is null when no sales qualify which kind of messes things up when it is referenced in the For loop. "If products.Count > 0 Then" accounts for the lack of sales in Corneliu's code but I'm not exactly sure how to implement this in bvcuser's code. As you can tell I'm a fairly novice coder so this help is very much appreciated in that it helps me to get the functionality I need as well as to better understand how to code within BVC5.
|
|
|
|
|
|
Rank: Member
Joined: 4/10/2006(UTC)
Posts: 462
|
Sorry about that, this updated code should do the trick
Public Overrides Function Execute(ByVal context As BVSoftware.Bvc5.Core.BusinessRules.OrderTaskContext) As BVSoftware.Bvc5.Core.Marketing.DiscountQueue
Dim discounts As New DiscountQueue
' This code gets the mix and match product count.
Dim productcount As Integer = 0
' Looks for which items are currently on sale
Dim curSales As Sale() = Sale.GetAllValidSales()
' Loop each lineitem in the order
For Each lineitem As LineItem In context.Order.Items
' Loop each product available in the Mix and match promotion
For Each setting As ComponentSettingListItem In _products
' Check that the current product associated with the line item is
' part of the mix and match promotion by comparing to the productid in
' the mix and match promotion setting list
If String.Compare(setting.Setting1, lineitem.ProductId, True) = 0 Then
' Verify whether any sales currently exist
If curSales.Length > 0 Then
' If the product is part of the mix and match promotion we need to verify
' whether the product is on sale as sale items are excluded from the mix
' and match promotion
For Each curSale As Sale In curSales
' BVC5 GetValid Sales SP currently does not verify whether a promotion
' has started yet, so we manually check here
If curSale.StartDate < DateTime.Now Then
' If the current sale in the all valid sale loops applys to the
' current product, we skip the product
If Not curSale.IsApplicableToProduct(lineitem.ProductId) Then
' otherwise we add the lineitem to the total product count
' to keep track of whether enough product was ordered to qualify
' for the promotion
productcount += lineitem.Quantity
' We also apply the discount to the discount queue
discounts.AddDiscount(lineitem.Bvin, _discountAmount, DiscountQueueItemType.LineItem)
End If
End If
Next
End If
Else
' No Sales are currently running so we add the lineitem to the total product count
' to ensure the customer qualifies for the minimums and maximums
productcount += lineitem.Quantity
' We also apply the discount to the discount queue
discounts.AddDiscount(lineitem.Bvin, _discountAmount, DiscountQueueItemType.LineItem)
End If
Next
Next
' This offer is only good if the minimum and maximum quantities are met.
If productcount >= _minimumQuantity And productcount <= _maximumQuantity Then
Return discounts
Else
Return New DiscountQueue
End If
End Function |
Netriplex Corporation<br /> |
|
|
|
|
|
Rank: Member
Joined: 4/6/2006(UTC)
Posts: 27
|
I found a scenario where bvuser's code has a bit of trouble. If there are two valid sales, items can be double discounted and sale items can also sneak into the offer receiving an unjustified discount. I did my best to try to fix the problem and I think I came up with a solution. Code:
[color=#0000ff]End[/color][color=#0000ff]Function
[/color]
Again, thank you bvuser for all the time and effort you have put into helping me out. I truly appreciate it.
|
|
|
|
|
|
Rank: Member
Joined: 4/10/2006(UTC)
Posts: 462
|
Your code should work fine, this might optimize it a bit (definitely not noticably unless there are a bunch of products/sales)
Public Overrides Function Execute(ByVal context As BVSoftware.Bvc5.Core.BusinessRules.OrderTaskContext) As BVSoftware.Bvc5.Core.Marketing.DiscountQueue
Dim discounts As New DiscountQueue
' This code gets the mix and match product count.
Dim productcount As Integer = 0
' Looks for which items are currently on sale
Dim curSales As Sale() = Sale.GetAllValidSales()
' Loop each lineitem in the order
For Each lineitem As LineItem In context.Order.Items
' Loop each product available in the Mix and match promotion
For Each setting As ComponentSettingListItem In _products
' Initialize saleExists flag to false
Dim saleExists As Boolean = False
' Check that the current product associated with the line item is
' part of the mix and match promotion by comparing to the productid in
' the mix and match promotion setting list
If String.Compare(setting.Setting1, lineitem.ProductId, True) = 0 Then
' If the product is part of the mix and match promotion we need to verify
' whether the product is on sale as sale items are excluded from the mix
' and match promotion
For Each curSale As Sale In curSales
' BVC5 GetValid Sales SP currently does not verify whether a promotion
' has started yet, so we manually check here
If curSale.StartDate < DateTime.Now Then
' If the current sale in the all valid sale loops applys to the
' current product, set the saleExists flag to true and exit the
' for loop to prevent unneccessary processing
If curSale.IsApplicableToProduct(lineitem.ProductId) Then
saleExists = True
Exit For
End If
End If
Next
' If a sale for the current product does not exist...
If Not saleExists Then
' we add the lineitem to the total product count
' to keep track of whether enough product was ordered to qualify
' for the promotion
productcount += lineitem.Quantity
' We also apply the discount to the discount queue
discounts.AddDiscount(lineitem.Bvin, _discountAmount, DiscountQueueItemType.LineItem)
End If
End If
Next
Next
' This offer is only good if the minimum and maximum quantities are met.
If productcount >= _minimumQuantity And productcount <= _maximumQuantity Then
Return discounts
Else
Return New DiscountQueue
End If
End Function
If you need anything else e-mail is welcome info (at) caplinkbdc [dot] com |
Netriplex Corporation<br /> |
|
|
|
|
|
Rank: Member
Joined: 8/17/2006(UTC)
Posts: 681
|
I always knew that I can't compile code in notepad but I'm to lazy to open VS sometimes :) Sorry for my mistake. If you still plan to use it the fix is to change productid to setting.
Regards,
Corneliu. |
|
|
|
|
|
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.