Solved

vb.net help

Posted on 2013-02-05
5
155 Views
Last Modified: 2013-02-20
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
       
 
	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

Open in new window

0
Comment
Question by:charlie324
  • 2
  • 2
5 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
With LINQ, you could make this work in short order. For example:

Imports System.IO
Imports System.Linq

Module Module1
    Sub Main()
        Dim query = From line In File.ReadAllLines("file.csv")
                    Let columns = line.Split(New String() {" "}, StringSplitOptions.RemoveEmptyEntries)
                    Let orderId = columns(0)
                    Let custName = columns(1)
                    Let prodId = columns(2)
                    Group By orderId, custName, prodId Into Group
                    Select New With
                    {
                        .OrderID = orderId,
                        .ProdQty = Group.Count(),
                        .OrderProdId = prodId
                    }

        For Each item In query
            Console.WriteLine("{0}    {1}    {2}", item.OrderID, item.ProdQty, item.OrderProdId)
        Next
    End Sub
End Module

Open in new window


Screenshot
Do note:  Part of this logic is dependent on the customer name not containing any spaces. This could be accounted for, though.
0
 
LVL 6

Author Comment

by:charlie324
Comment Utility
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
0
 
LVL 16

Expert Comment

by:Rose Babu
Comment Utility
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

Open in new window

Do you need it in the code level itself?
0
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
Comment Utility
Conventional it is...

Imports System.IO

Module Module1
    Sub Main()
        Dim grouping As New Dictionary(Of String, Integer)()

        For Each line As String In File.ReadAllLines("file.csv")
            Dim columns() As String = line.Split(New String() {" "}, StringSplitOptions.RemoveEmptyEntries)
            Dim key As String = columns(0) + "-" + columns(1)

            If Not grouping.ContainsKey(key) Then grouping.Add(key, 0)

            grouping(key) += 1
        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)
        Next
    End Sub
End Module

Open in new window

0
 
LVL 6

Author Comment

by:charlie324
Comment Utility
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?

  
'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

Open in new window


Your help is greatly appreciated.

Many Thanks
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
A short article about problems I had with the new location API and permissions in Marshmallow
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now