Results 1 to 23 of 23

Thread: linq query to sum total items

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    linq query to sum total items

    I,i have a little program that retrieves railway reservations for groups.

    The object for the program is to know :
    a) how many people load and unload that train,selecting a particular station (ex rome,11 loaded,89 unloaded)
    b) how many people load a specific car (ex car 5-10 passenger,car 7-11 passenger)
    c) how many destinations and people selecting a particular station (ex from rome,11 passenger to florence,18 to milan,etc)
    in another thread i had linq suggestion, so i create a datatable filling it by a json response from a website.
    made this code to retrieve all stations served by the train :
    Code:
    With treno1
    	.Columns.Add("Partenza", System.Type.GetType("System.String"))
    	.Columns.Add("Arrivo", System.Type.GetType("System.String"))
    	.Columns.Add("PNR", System.Type.GetType("System.String"))
    	.Columns.Add("Classe", System.Type.GetType("System.String"))
    	.Columns.Add("Cognome", System.Type.GetType("System.String"))
    	.Columns.Add("Carrozza", System.Type.GetType("System.String"))
    	.Columns.Add("Posto", System.Type.GetType("System.String"))
    	.Columns.Add("Nome", System.Type.GetType("System.String"))
    	.Columns.Add("Treno", System.Type.GetType("System.String"))
    	.Columns.Add("ID prenotazione", System.Type.GetType("System.String"))
    End With
    Dim stazioni As New List(Of String)
    
    For Each item As JProperty In data
        item.CreateReader()
    
        For Each msg As JObject In item.Values
            treno1.Rows.Add(msg("departureLocationName"), msg("arrivalLocationName"), msg("pnrCode"), msg("serviceLevel"), msg("lastName"), msg("wagon"), msg("seat"), msg("firstName"), msg("transportMeanName"), msg("Id"))
        Next
    Next
    
    Dim partenzaStations = From n In treno1.AsEnumerable()
                           Where n.Field(Of String)("Partenza") IsNot Nothing
                           Select n.Field(Of String)("Partenza")
    
    Dim arrivoStations = From n In treno1.AsEnumerable()
                         Where n.Field(Of String)("Arrivo") IsNot Nothing
                         Select n.Field(Of String)("Arrivo")
    
    Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
    
    ComboBox1.DataSource = stations.ToArray
    ComboBox1.SelectedIndex = -1
    but now how to know how many people is loading a station ? and how many people in CERTAIN station are going to a destination ? (for example in rome : 10 to florence,10 to bologna ,5 to milan)
    i tried this code but there's a logic error :


    Code:
    Dim destinazione = From n In treno1.AsEnumerable()
                       Where n.Field(Of String)("Partenza") = ComboBox1.SelectedValue
                       Select n.Field(Of String)("Arrivo")
    
    Dim destinazioni = (destinazione.Distinct).ToList
    
    Dim totalepax = From n In treno1.AsEnumerable()
                    Where n.Field(Of String)("Partenza") = ComboBox1.SelectedValue
    Aggregate destinazioni
    which is the right code ?
    Last edited by dday9; Jul 24th, 2020 at 12:04 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: linq query to sum total items

    Leaving a huge wad of leading whitespace on your except for the first line is a great way to make it harder for us to read. I see so many people do this and they obviously select the text from the first character in the first line and ignore the fact that this selects all the whitespace in every other line. In VS, you can hold down the Alt key while you drag to select and it will select an arbitrary rectangle, which means that you can make our lives easier when we read your code but you don't have to make any real effort to do it.

  3. #3
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: linq query to sum total items

    Edited the post to trim the leading whitespace from the code snippets.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: linq query to sum total items

    Rather than doing this:
    vb.net Code:
    1. .Columns.Add("Partenza", System.Type.GetType("System.String"))
    do this:
    vb.net Code:
    1. .Columns.Add("Partenza", GetType(String))
    The Type.getType method should only be used if you are getting a String containing the type name from somewhere else. If you're hardcoding that String then don't. Instead, use the GetType operator and hardcode an actual data type. That way, you get compile-time checking for a valid type. If you misspell your String then you won't find out until run time.

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: linq query to sum total items

    Personally, I tend to use function syntax for LINQ queries unless they are a bit complex and query syntax makes them easier to read. If you want to sum a column:
    vb.net Code:
    1. Dim sum = myTable.AsEnumerable().Sum(Function(row) row.Field(Of Integer)("MyColumn"))
    Given that you have no numeric columns in your DataTable though, I assume that what you actually want is a count rather than a sum. You can filter using Where and then call Count:
    vb.net Code:
    1. Dim count = myTable.AsEnumerable().Where(Function(row) row.Field(Of String)("MyColumn") = someValue).Count())
    but it is better to use Count to filter as well:
    vb.net Code:
    1. Dim count = myTable.AsEnumerable().Count(Function(row) row.Field(Of String)("MyColumn") = someValue))
    Not sure whether this addresses everything you want to know but you could have explained your table schema a little better, given that your column names aren't English.

  6. #6
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: linq query to sum total items

    Based on your code, I can infer that the incoming JSON looks like the following:
    Code:
    {
        Values: [
            {
                "departureLocationName": "...",
                "arrivalLocationName": "...",
                "pnrCode": "...",
                "serviceLevel": "...",
                "lastName": "...",
                "wagon": "...",
                "seat": "...",
                "firstName": "...",
                "transportMeanName": "...",
                "Id": "..."
            }
        ]
    }
    What I would suggest doing is setting up a POCO (or I guess it'd be called a POVBO, but that just sounds weird) representation of the incoming data. In this class, you could even rename the property names if you wanted to just so long as you used the JsonProperty attribute:
    Code:
    Imports Newtonsoft.Json
    Public Class Train
    
        <JsonProperty("departureLocationName")>
        Public Property Partenza As String
    
        <JsonProperty("arrivalLocationName")>
        Public Property Arrivo As String
    
        <JsonProperty("pnrCode")>
        Public Property PNR As String
    
        <JsonProperty("serviceLevel")>
        Public Property Classe As String
    
        <JsonProperty("lastName")>
        Public Property Cognome As String
    
        <JsonProperty("wagon")>
        Public Property Carrozza As String
    
        <JsonProperty("seat")>
        Public Property Poso As String
    
        <JsonProperty("firstName")>
        Public Property Nome As String
    
        <JsonProperty("transportMeanName")>
        Public Property Treno As String
    
        <JsonProperty("Id")>
        Public Property Id As String
    
    End Class
    Now that you have a class representation of the data you can do the following:
    1. Create a List(Of Train)
    2. Assign the value of the list equal to the deserialized data using the JsonConvert::DeserializeObject(Of T) method
    3. Create a new BindingSource
    4. Set the DataSource of the BindingSource equal to the List(Of Train)
    5. Bind your DataGridView to the BindingSource
    6. Get the data for your ComboBox using the data from the List(Of Train)


    That would look something like this:
    Code:
    'TODO: Not sure where your JSON literal is, so I'm just assuming its declared somewhere like this: Dim json As String = "...."
    
    ' Convert the JSON to a JObject
    Dim jsonObject = JsonConvert.DeserializeObject(json)
    
    ' Create a new List(Of Train) by deserializing the JSON
    Dim trains As List(Of Train) = JsonConvert.DeserializeObject(Of List(Of Train))(jsonObject.Item("Values").ToString())
    
    ' Bind the DataGridView to the List by setting up a BindingSource
    Dim bs As BindingSource = New BindingSource() With {
        .DataSource = trains
    }
    treno1.DataSource = bs
    
    ' Set the DataSource of the ComboBox to hold all the departureLocationNames and arrivalLocationNames
    Dim partenzaStations = From train In trains
                           Where Not String.IsNullOrWhitespace(train.Partenza)
                           Select train.Partenza
    
    Dim arrivoStations = From train In trains
                           Where Not String.IsNullOrWhitespace(train.Arrivo)
                           Select train.Arrivo
    
    Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
    With ComboBox1
        .DataSource = stations
        .SelectedIndex = -1
    End With
    Now you can apply a filter on the BindingSource on the arrivalLocationName based on the new selected item in your ComboBox by handling the SelectedIndexChanged event:
    Code:
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        If (ComboBox1.SelectedIndex < 0) Then
            bs.RemoveFilter()
            Return
        End If
    
        bs.Filter = $"Partenza = '{ComboBox1.Text}'"
    End Sub
    Update
    So apparently when you set up the BindingSource like this, it does not support filtering so you're not able to filter the underlying data. Instead, since it looks like you don't care about filtering the data in the DataGridView, but instead just want to a count of all records where Partenza or Arrivo is the value in the ComboBox, you could just change the SelectedIndexChanged event to be more like this:
    Code:
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        'TODO: Assumes you have a label named LabelTotal on your form
        If (Combobox1.SelectedIndex < 0) Then
            Return
        End If
    
        Dim bs = DirectCast(treno1.DataSource, BindingSource)
        Dim trains = DirectCast(bs.List, List(Of Train))
    
        Dim partenzaTotal = trains.Where(Function(train) train.Partenza = combobox1.Text).Count()
        Dim arrivoTotal = trains.Where(Function(train) train.Arrivo = combobox1.Text).Count()
        Dim aggregateTotal = (partenzaTotal + arrivoTotal)
        LabelTotal.Text = $"Partenza Total: {partenzaTotal} Arrivo Total: {arrivoTotal} Aggregate Total: {aggregateTotal}"
    End Sub
    Here's a screenshot of a working example:
    Name:  snippet.jpg
Views: 258
Size:  22.9 KB
    Last edited by dday9; Jul 24th, 2020 at 01:11 AM.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: linq query to sum total items

    Quote Originally Posted by dday9 View Post
    What I would suggest doing is setting up a POCO (or I guess it'd be called a POVBO, but that just sounds weird) representation of the incoming data.
    The C in POCO stands for CLR so it doesn't matter what .NET language you implement a POCO in.

  8. #8
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: linq query to sum total items

    Hmm, I always thought it stood for "plain ole C# object".
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  9. #9

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    Re: linq query to sum total items

    @ jmcilhinney
    well you were right...i need the count function.
    the thing is i need to count how many passengers are booked for a specific destination,from a specific departure.
    At first i fill the datatable,then i load combobox1 with this code :
    Code:
    Dim partenzaStations = From n In treno1.AsEnumerable()
    				Where n.Field(Of String)("Partenza") IsNot Nothing
    				 Select n.Field(Of String)("Partenza")
    
    Dim arrivoStations = From n In treno1.AsEnumerable()
    			       Where n.Field(Of String)("Arrivo") IsNot Nothing
    				 Select n.Field(Of String)("Arrivo")
    
    Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
    				ComboBox1.DataSource = stations.ToArray
    				ComboBox1.SelectedIndex = -1
    and now i have in the combobox ALL stations served by the train.
    then,chosing a station from combobox1 (for instance : ROME),i want to have all the arrival stations for people leaving Rome ,with total passenger for single destination
    so i must make a count,from treni1 (my datatable),where Partenza(means departure) is chosen from combobox1,BUT i also need several destination like the attachment image

    so count from treni1,where partenza = combobox1.selected value.....but it only returns how many people are loading the station....
    from departure-to destination,total people
    from departure-to other destination-total people
    and so on..Name:  info.jpg
Views: 250
Size:  118.4 KB
    Last edited by eurostar_italia; Jul 24th, 2020 at 01:23 PM.

  10. #10
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: linq query to sum total items

    Quote Originally Posted by eurostar_italia View Post
    I,i have a little program that retrieves railway reservations for groups.


    in another thread i had linq suggestion, so i create a datatable filling it by a json response from a website.
    like I(and other people) asked in the other Thread why don't you show us that, or supply some Data to work with

    also what happens to the Data when your finished ? do you store the Data or do you delete it ?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  11. #11

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    Re: linq query to sum total items

    Quote Originally Posted by ChrisE View Post
    like I(and other people) asked in the other Thread why don't you show us that, or supply some Data to work with

    also what happens to the Data when your finished ? do you store the Data or do you delete it ?
    I just show the data without storing it.
    A json copy would ben ok ?
    i 'm working around my program because i also don't want to use newtonsoft library to deserialize the json (but this is another point).

  12. #12
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: linq query to sum total items

    I built a library that converts JSON to an XDocument if that's more along the lines of what you want to do.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  13. #13

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    Re: linq query to sum total items

    Quote Originally Posted by dday9 View Post
    I built a library that converts JSON to an XDocument if that's more along the lines of what you want to do.
    thanks but i'm trying to dismiss libraries..

  14. #14

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    Re: linq query to sum total items

    this i a json file with some data i need to show...763reservation.txt

  15. #15

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    Re: linq query to sum total items

    data attached,thanks @ChrisE

  16. #16
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: linq query to sum total items

    Quote Originally Posted by eurostar_italia View Post
    data attached,thanks @ChrisE
    that wasn't what I meant, you fire a Webrequest to get the Data
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  17. #17

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    Re: linq query to sum total items

    Quote Originally Posted by ChrisE View Post
    that wasn't what I meant, you fire a Webrequest to get the Data
    the webrequest is available only in my company intranet.this is the json file i get from the web request,which i use to fill the datatable.
    where
    json ("departureLocationName")=datatable column ("Partenza")
    json ("arrivalLocationName")=datatable column ("Arrivo")
    json ("pnrCode")=datatable column ("PNR")
    json ("serviceLevel")=datatable column ("Classe")
    json ("lastName")=datatable column ("Cognome")
    json ("wagon")=datatable column ("Carrozza")
    json ("seat")=datatable column ("Posto")
    json ("firstName")=datatable column ("Nome")
    json ("transportMeanName"=datatable column ("Treno")
    json ("Id")=datatable column ("ID prenotazione")

  18. #18
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: linq query to sum total items

    got not much going on Today so......

    Ok so I took the Data from the Text file you gave.

    like I said you can create a .mdb Database without installing Access and use it just like any other File(.txt,.xlsx,.docx etc...)

    here a sample to create the .mdb and a Table for the Train data
    I created a Folder E:\Train for this sample

    open a new Project and add 2 Forms
    Code:
    'this is for Form1
    Imports System.Data.OleDb
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            'Add the COM Reference, "Microsoft ADO Ext. 6.0 for DLL and Security"
            ' create the empty DB file
            Dim cat As New ADOX.Catalog()
            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Train\TrainData.mdb")
            cat = Nothing
    
            'now create the Train Table in the Database
            MakeTrainTable()
          
        End Sub
        Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                      ByVal sSQL As String, _
                                      Optional ByRef ErrMessage As String = Nothing, _
                                      Optional ByVal TransAction As  _
                                      OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    
        Public Sub MakeTrainTable()
            Dim sSQL As String
    
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Train\TrainData.mdb")
    
            'here you create your Table in the Database
            con.Open()
            'create your Table tbl_Train
            sSQL = " Create Table tbl_Train"
            sSQL &= "( [TR_ID] AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY"
            sSQL &= ", [TR_arrivalLocationName] varChar(50)"
            sSQL &= ", [TR_pnrCode] varChar(50)"
            sSQL &= ", [TR_serviceLevel] varChar(50)"
            sSQL &= ", [TR_lastName] varChar(50)"
            sSQL &= ", [TR_firstName] varChar(50)"
            sSQL &= ", [TR_wagon] INT"
            sSQL &= ", [TR_seat] INT"
            sSQL &= ", [TR_transportMeanName] varChar(50)"
            sSQL &= ", [TR_couponId] varChar(50)"
            sSQL &= ")"
            ExecuteSQL(con, sSQL)
        End Sub
    End Class
    this is for Form2
    Code:
    Imports System.Data.OleDb
    
    
    Public Class Form2
        Private objConnection As OleDbConnection
        Private objCommand As OleDbCommand
        Private objDataAdapter As OleDbDataAdapter
        Private objDataTable As DataTable
        Private objDataSet As DataSet
        '//Access Database::
        Private strConnectionString As String = _
               "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=E:\Train\TrainData.mdb;"
        '--------------------------------------------------------
        Private Sub Form2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            'show all Data from Train Table
            objConnection = New OleDbConnection(strConnectionString)
            Dim sSQL As String = "Select * From tbl_Train "
            Dim sWhere As String = Nothing
            sSQL &= sWhere & "Order by TR_arrivalLocationName"
            objCommand = New OleDbCommand(sSQL, objConnection)
            objDataAdapter = New OleDbDataAdapter
            objDataAdapter.SelectCommand = objCommand
            objDataTable = New DataTable
            objDataAdapter.Fill(objDataTable)
            DataGridView1.DataSource = objDataTable
        End Sub
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            objConnection = New OleDbConnection(strConnectionString)
    
            'Fill Combobox with Distinct Station names
            objCommand = New OleDbCommand("SELECT Distinct TR_arrivalLocationName " & _
               "FROM [tbl_Train]", objConnection)
            objDataAdapter = New OleDbDataAdapter
            objDataAdapter.SelectCommand = objCommand
            objDataTable = New DataTable
            objDataAdapter.Fill(objDataTable)
            DataGridView1.DataSource = objDataTable
            'Bind the DataTable to the ComboBox
            ComboBox1.DataSource = objDataTable
            ComboBox1.DisplayMember = "TR_arrivalLocationName"
            ComboBox1.ValueMember = "TR_arrivalLocationName"
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
            objConnection = New OleDbConnection(strConnectionString)
            'filter the Datagridview with Station name
            Dim sSQL As String = "Select * From tbl_Train "
            Dim sWhere As String = Nothing
    
            If ComboBox1.Text <> Nothing Then
                sWhere &= "And (TR_arrivalLocationName Like '" & ComboBox1.Text & "%') "
            End If
    
            If sWhere <> Nothing Then
                sWhere = "Where " & sWhere.Substring(4)
            End If
            sSQL &= sWhere & "Order by TR_arrivalLocationName"
            objCommand = New OleDbCommand(sSQL, objConnection)
            objDataAdapter = New OleDbDataAdapter
            objDataAdapter.SelectCommand = objCommand
            objDataTable = New DataTable
            objDataAdapter.Fill(objDataTable)
            DataGridView1.DataSource = objDataTable
            'Clean up
            objDataAdapter.Dispose()
            objDataAdapter = Nothing
            objCommand.Dispose()
            objCommand = Nothing
            objConnection.Dispose()
            objConnection = Nothing
    
        End Sub
    End Class
    here a Image Form2
    Name:  Form2Train.jpg
Views: 167
Size:  49.6 KB



    I used you Text file and Imported it to a .csv File and then added it to the
    created Database
    this uses Powershell to create and send the Data to the .mdb

    start Powershell ISE(x86) included in Windows and add this...
    Code:
    $json = @"
    {"listReservationReport": [
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1397102440",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Posto a sedere",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "2",
    			"seat": "114",
    			"pnrCode": "ABXX",
    			"departureLocationName": "BOLZANO BOZEN",
    			"arrivalLocationName": "BOLOGNA",
    			"cpCode": "777777",
    			"offerName": "Super Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "11,90",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "RESERVATION OFFICE",
    			"gender": "Non disponibile",
    			"saleSystem": "WEB",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1399710523",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Posto a sedere",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "1",
    			"seat": "12",
    			"pnrCode": "ABXX",
    			"departureLocationName": "BOLOGNA",
    			"arrivalLocationName": "ROMA TERMINI",
    			"cpCode": "777777",
    			"offerName": "Super Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "13,90",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "POSTAZIONE 1 (32786 -1)",
    			"gender": "Non disponibile",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1400690501",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Posto a sedere",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "1",
    			"seat": "73",
    			"pnrCode": "ABXX",
    			"departureLocationName": "BOLZANO BOZEN",
    			"arrivalLocationName": "BOLOGNA",
    			"cpCode": "777777",
    			"offerName": "Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "32,90",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "RESERVATION OFFICE",
    			"gender": "Non disponibile",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1401864174",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Posto a sedere",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "1",
    			"seat": "14",
    			"pnrCode": "ABXX",
    			"departureLocationName": "VERONA PORTA NUOVA",
    			"arrivalLocationName": "BOLOGNA",
    			"cpCode": "777777",
    			"offerName": "Super Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "11,10",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "INTERNET",
    			"gender": "Non disponibile",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1401864173",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Posto a sedere",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "1",
    			"seat": "15",
    			"pnrCode": "ABXX",
    			"departureLocationName": "VERONA PORTA NUOVA",
    			"arrivalLocationName": "BOLOGNA",
    			"cpCode": "777777",
    			"offerName": "Super Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "11,10",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "INTERNET",
    			"gender": "Non disponibile",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1402460318",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Posto a sedere",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "1",
    			"seat": "52",
    			"pnrCode": "ABXX",
    			"departureLocationName": "BOLZANO BOZEN",
    			"arrivalLocationName": "BOLOGNA",
    			"cpCode": "777777",
    			"offerName": "Super Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "15,30",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "RESERVATION OFFICE",
    			"gender": "Non disponibile",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1402463650",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Posto a sedere",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "1",
    			"seat": "23",
    			"pnrCode": "ABXX",
    			"departureLocationName": "VERONA PORTA NUOVA",
    			"arrivalLocationName": "ORTE",
    			"cpCode": "777777",
    			"offerName": "Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "21,90",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "MOBILE",
    			"gender": "Non disponibile",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1402853359",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Notte",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "4",
    			"seat": "41",
    			"pnrCode": "ABXX",
    			"departureLocationName": "TRENTO",
    			"arrivalLocationName": "ROMA TERMINI",
    			"cpCode": "777777",
    			"offerName": "Super Economy",
    			"serviceLevel": "1° Classe",
    			"amount": "79,90",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "RESERVATION OFFICE",
    			"gender": "Promiscuo, Intero, Famiglia",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1408311297",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Notte",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "3",
    			"seat": "51",
    			"pnrCode": "ABXX",
    			"departureLocationName": "FIRENZE",
    			"arrivalLocationName": "ROMA TERMINI",
    			"cpCode": "777777",
    			"offerName": "Super Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "49,90",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "RESERVATION OFFICE",
    			"gender": "Promiscuo, Intero, Famiglia",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1408311298",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Notte",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "3",
    			"seat": "52",
    			"pnrCode": "ABXX",
    			"departureLocationName": "BOLOGNA",
    			"arrivalLocationName": "FIRENZE",
    			"cpCode": "777777",
    			"offerName": "Super Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "49,90",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "RESERVATION OFFICE",
    			"gender": "Promiscuo, Intero, Famiglia",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1408557722",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Posto a sedere",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "1",
    			"seat": "24",
    			"pnrCode": "ABXX",
    			"departureLocationName": "VERONA PORTA NUOVA",
    			"arrivalLocationName": "ORTE",
    			"cpCode": "777777",
    			"offerName": "Super Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "16,90",
    			"adults": 1,
    			"teens": 0,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "RESERVATION OFFICE",
    			"gender": "Non disponibile",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		},
    		{
    			"validationType": "INIZIALE",
    			"bookingId": "1409970574",
    			"travelId": null,
    			"travelSolutionId": null,
    			"couponId": "134299999",
    			"transportMeanName": "763",
    			"transportClassification": "NI",
    			"transportMeanDate": "25-07-2020",
    			"couponServiceType": "Posto a sedere",
    			"materializationType": "NON MATERIALIZZATO",
    			"wagon": "1",
    			"seat": "54",
    			"pnrCode": "ABXX",
    			"departureLocationName": "BOLOGNA",
    			"arrivalLocationName": "ORTE",
    			"cpCode": "777777",
    			"offerName": "Economy",
    			"serviceLevel": "2° Classe",
    			"amount": "16,00",
    			"adults": 0,
    			"teens": 1,
    			"alreadyPaied": "--",
    			"alreadyCached": "--",
    			"ci204": null,
    			"firstName": "JOHN",
    			"lastName": "SMITH",
    			"channelName": "POSTAZIONE 1 (32778 - 1)",
    			"gender": "Non disponibile",
    			"saleSystem": "GALILEO",
    			"travellerInfo": null,
    			"validation": null
    		}
    	],
    	"validatorList": [],
    	"materializedStatus": [
    		{
    			"onboard": 0,
    			"absent": 0,
    			"none": 0,
    			"defect": 0,
    			"undo": 0
    		}
    	],
    	"notMaterializedStatus": [
    		{
    			"onboard": 0,
    			"absent": 0,
    			"none": 30,
    			"defect": 0,
    			"undo": 0
    		}
    	]
    }
    "@ | ConvertFrom-Json | Select-Object -Expand listReservationReport 
    
    $json | Select arrivalLocationName,
                    pnrCode,
                    serviceLevel,
                    lastName,
                    wagon,
                    seat,
                    firstName,
                    transportMeanName,
                    couponId |
    #saved Train Information to csv
    Export-Csv -Path "E:\Train\Train_file.csv" -NoTypeInformation  -Encoding UTF8 -Delimiter ";"
    
    #now import csv to Access TrainData.mdb
    
    $connectstring = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\Train\TrainData.mdb"
    $conn = New-Object System.Data.OleDb.OleDbConnection($connectstring)
    $conn.Open()
    $nFiles = Import-Csv -Delimiter ";" -Path "E:\Train\Train_file.csv" 
    foreach ($File in $nFiles) 
    { 
    $c1 = $File.arrivalLocationName
    $c2 = $File.pnrCode
    $c3 = $File.serviceLevel
    $c4 = $File.lastName
    $c5 = $File.firstName
    $c6 = $File.wagon
    $c7 = $File.seat
    $c8 = $File.transportMeanName
    $c9 = $File.couponId
    
    
    $cmd = $conn.CreateCommand()
    $cmd.CommandText="INSERT INTO tbl_Train(TR_arrivalLocationName,
                                           TR_pnrCode,
                                           TR_serviceLevel,
                                           TR_lastName,
                                           TR_firstName,
                                           TR_wagon,
                                           TR_seat,
                                           TR_transportMeanName,
                                           TR_couponId) 
                                           VALUES('$c1','$c2','$c3', '$c4','$c5','$c6','$c7','$c8','$c9')"
    $cmd.ExecuteNonQuery()
    }
    $conn.Close()
    here a Image of the created .csv
    Name:  csvTrain.jpg
Views: 170
Size:  42.3 KB


    a bit much to take in at first


    have fun
    Last edited by ChrisE; Jul 28th, 2020 at 10:51 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  19. #19

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    Re: linq query to sum total items

    @Chris i very say thank you for your patience ,but that way it's not the one i could take.
    I'm trying so simplify the code but without having external db...(i'm stil trying to avoid the newtosoft dll) due....well you know operators.
    i guess i must study better dictonaries perhaphs they will help me with matrix...i don't know...i'm going crazy.
    thanks a lot

  20. #20

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    Re: linq query to sum total items

    Code:
    	Dim destinazione = From n In treno1.AsEnumerable()
    	 Where n.Field(Of String)("Partenza") = ComboBox1.SelectedValue
    	  Select n.Field(Of String)("Arrivo")
    
    
    	Dim destinazioni = (destinazione.Distinct).ToList
    
    
            For Each element In destinazioni
    	Dim valore = From n In treno1.AsEnumerable()
    	 Where n.Field(Of String)("Partenza") = ComboBox1.SelectedValue And n.Field(Of String)("Arrivo") = destinazioni (here the problem)....

  21. #21
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: linq query to sum total items

    Quote Originally Posted by eurostar_italia View Post
    @Chris i very say thank you for your patience ,but that way it's not the one i could take.
    I'm trying so simplify the code but without having external db...(i'm stil trying to avoid the newtosoft dll) due....well you know operators.
    i guess i must study better dictonaries perhaphs they will help me with matrix...i don't know...i'm going crazy.
    thanks a lot
    working with a Access Database und the use of SQL for your querys would make you life
    much easier, but that's up to you

    good luck
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  22. #22

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    Re: linq query to sum total items

    Quote Originally Posted by ChrisE View Post
    working with a Access Database und the use of SQL for your querys would make you life
    much easier, but that's up to you

    good luck
    i believe you and i know your's the best way,but i know my colleagues and even my boss : "it would be strange for them a db...."
    it's like a war.thanks a lot anyway...i can't say how much i appreciate your help.i wish i could.

  23. #23

    Thread Starter
    Member
    Join Date
    Jul 2020
    Posts
    51

    Re: linq query to sum total items

    Code:
    		Dim destinazione = From n In treno1.AsEnumerable()
    						   Where n.Field(Of String)("Partenza") = ComboBox1.SelectedValue
    						   Select n.Field(Of String)("Arrivo")
    
    
    
    		Dim destinazioni = (destinazione.Distinct).ToList
    		destinazioni.Sort()
    
    		For Each element In destinazioni
    			Dim count = treno1.AsEnumerable().Count(Function(row) (row.Field(Of String)("Partenza") = ComboBox1.SelectedValue))
    
    
    			ListBox1.Items.Add(element.ToString & Count.ToString)
    		Next
    the output is "destination1-10 people,destination2-10 people,destination3-10 people,etc but it's wrong
    it should be :"destination1-5people,destination2-3 people,destination3-2 people
    what's wrong in the code ?? i guess 10 it's the total count of the people boarding the station selected in the combobox1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width