Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vb.net help

Posted on 2013-02-05
5
Medium Priority
?
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38856317
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
ID: 38858649
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
ID: 38858895
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 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 total points
ID: 38859292
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
ID: 38867443
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Starting up a Project

715 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