charlie324
asked on
vb.net help
Hello Experts
I am having a small problem and I wonder if someone can assist?
Attached is the code snippet which I am using to download the CSV order file, read all the rows and save order in the database.
I store the order header in EDIOrder table and details ex: ProductID, ProductQty in EDIOrderDetails table
The problem I am having is with the order details, because is no quantity on the orders report.
This is because each individual line on the report is one item.
Therefore, if one customer buys 2 of one item, 3 of another, I will have 5 lines in your repor
ex:
OrderId OrderCustName OrderProdID
-------------------------- ---------- ---------- ---------- ---------- ----------
1234 ABC STAR
1234 ABC STAR
2345 XYZ TREE
2345 XYZ TREE
2345 XYZ TREE
While saving the order details, I wanted to group similar items qty and save it some like this
EDIOrderDetails
=================
OrderID ProdQty Product.ProdID
-------------------------- ---------- ---------- ---------- ---------- -----
1234 2 STAR
2345 3 TREE
I hope you understand what I am trying to say?
Please can someone help?
Thanks
Charlie
I am having a small problem and I wonder if someone can assist?
Attached is the code snippet which I am using to download the CSV order file, read all the rows and save order in the database.
I store the order header in EDIOrder table and details ex: ProductID, ProductQty in EDIOrderDetails table
The problem I am having is with the order details, because is no quantity on the orders report.
This is because each individual line on the report is one item.
Therefore, if one customer buys 2 of one item, 3 of another, I will have 5 lines in your repor
ex:
OrderId OrderCustName OrderProdID
--------------------------
1234 ABC STAR
1234 ABC STAR
2345 XYZ TREE
2345 XYZ TREE
2345 XYZ TREE
While saving the order details, I wanted to group similar items qty and save it some like this
EDIOrderDetails
=================
OrderID ProdQty Product.ProdID
--------------------------
1234 2 STAR
2345 3 TREE
I hope you understand what I am trying to say?
Please can someone help?
Thanks
Charlie
Try
' Download the CSV order report
DownloadReports(URL, USERPWD, POSTFIELDS, ReportName, ResultXml)
ResultContent = Marshal.PtrToStringAnsi(ResultXml)
'Split the rows
OrderRows = ResultContent.Split(vbCr)
Dim OrderCurrentID As Integer = 0
Dim ProdInternalID As String = String.Empty
Dim ProdPrevInternalID As String = String.Empty
Dim NumOrders As DataSet
Dim ProdQty As Integer = 1
Dim PrevOrderId As Integer = 0
'Parse the data
For Each Row As String In OrderRows
If (OrderCurrentID = 0) Then
OrderCurrentID += 1
Continue For
End If
If (Row.Trim() <> String.Empty) Then
Dim Fields() As String = Row.Trim().Split(vbTab)
If (Fields.Length = 15) Then
If (Fields(0) = "Sold") Then
If Not (_OrderIDs.Contains(Fields(1))) Then
_OrderIDs.Add(Fields(1))
End If
NumOrders = DropShipDAL.IsRetailerOrderIDExist(Fields(1))
If NumOrders.Tables(0).Rows.Count > 0 Then
CommonFunctions.SendErrorEmail("EDI - Duplicate Order", "OrderID = " & OrderID)
Continue For
End If
'Saving Order Header
If (OrderCurrentID = 1) Then
Adapter.AddDropShipOrder(OrderID, _
Fields(1), _
1, _
"1st Class", _
"1", _
Fields(7), _
Fields(8).Split(" ")(0), _
Fields(8).Split(" ")(Fields(8).Split(" ").Length - 1), _
Fields(9), _
Fields(10), _
"", _
Fields(11), _
"", _
Fields(12), _
"238", _
"", _
"")
End If
ProdInternalID = Trim(Fields(4))
'Fetch ProductID from database
Product = ProductBLL.GetProductByPlayProdID(ProdInternalID)
If (Product.ProdID Is Nothing) Or (Product.ProdID = "") Or (Not Product.ProdCanDropShip) Then
CommonFunctions.SendErrorEmail("EDI - Product Not On Their List", "OrderID = " & OrderID & " --- InternalID=" + Fields(4))
Continue For
End If
If OrderID = PrevOrderId AndAlso ProdInternalID = ProdPrevInternalID Then
ProdQty = ProdQty + 1
End If
' Saving Order Details
' I need to group similar items qty and pass it here
Adapter.AddDropShipOrderDetail(OrderID, ProdQty, Product.ProdName, Product.ProdID, OrderCurrentID)
OrderCurrentID += 1
PrevOrderId = OrderID
ProdPrevInternalID = ProdInternalID
End If
End If
End If
Next
Catch ex As Exception
CommonFunctions.SendErrorEmail("EDI - Error saving an order", ex.Message + " --- ResultContent=" + ResultContent)
Finally
FreeHGlobal(ReportName)
FreeHGlobal(ResultXml)
End Try
ASKER
Hi kaufmed
Thank you for your input.
Is there any other way to do this using conventional vb.net code please?
I am not really that familiar with LINQ yet.
Many Thanks
Thank you for your input.
Is there any other way to do this using conventional vb.net code please?
I am not really that familiar with LINQ yet.
Many Thanks
After populating the orders data into table (using your 'Saving Order Details' process) you can query the result to the required format like this
[OrdersTemp] Table
OrderId OrderCustName OrderProdID
------------------------------------------------------------------------------
1234 ABC STAR
1234 ABC STAR
2345 XYZ TREE
2345 XYZ TREE
2345 XYZ TREE
SELECT
OrderId,
COUNT(*) As ProdQty,
OrderProdID As [Product.ProdID]
FROM OrdersTemp
GROUP BY OrderId, OrderProdID
Output Result:
OrderID ProdQty Product.ProdID
-------------------------------------------------------------------------
1234 2 STAR
2345 3 TREE
Do you need it in the code level itself?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi srosebabu
Thanks for your comments. Yes, I must validate this before saving in the DB.
Hello kaufmed
Many thanks for your example, its looking great, I really hope this work. I will get back to you shortly on this.
Please can you advise if I have more columns to add then I would group them as well?
Something like this?
Your help is greatly appreciated.
Many Thanks
Thanks for your comments. Yes, I must validate this before saving in the DB.
Hello kaufmed
Many thanks for your example, its looking great, I really hope this work. I will get back to you shortly on this.
Please can you advise if I have more columns to add then I would group them as well?
Something like this?
'Field1 = OrderID
'Field4 = SKU ex: STAR
Dim grouping As New Dictionary(Of String, Integer)()
For Each OrderLine As String In OrderRows
Dim columns() As String = OrderLine.Split(New String() {vbTab}, StringSplitOptions.RemoveEmptyEntries)
If (Fields(0) = "Sold") Then
Dim key As String = Fields(1).ToString + "-" + Fields(4).ToString + "-" + Product.ProdID.ToString + "-" + Product.ProdID.ToString + "-" + OrderCurrentID.ToString
If Not grouping.ContainsKey(key) Then grouping.Add(key, 0)
grouping(key) += 1
End If
Next
For Each item As KeyValuePair(Of String, Integer) In grouping
Dim keyParts() As String = item.Key.Split("-"c)
'Console.WriteLine("{0} {1} {2}", keyParts(0), keyParts(1), item.Value)
'Adapter.AddDropShipOrderDetail(OrderID, ProdQty, Product.ProdName, Product.ProdID, OrderCurrentID)
Adapter.AddDropShipOrderDetail(OrderID, item.Value, Product.ProdName, Product.ProdID, OrderCurrentID)
Next
Your help is greatly appreciated.
Many Thanks
Open in new window
Do note: Part of this logic is dependent on the customer name not containing any spaces. This could be accounted for, though.