-
Jul 23rd, 2020, 08:23 PM
#1
Thread Starter
Member
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.
-
Jul 23rd, 2020, 08:51 PM
#2
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.
-
Jul 24th, 2020, 12:00 AM
#3
Re: linq query to sum total items
Edited the post to trim the leading whitespace from the code snippets.
-
Jul 24th, 2020, 12:07 AM
#4
Re: linq query to sum total items
Rather than doing this:
vb.net Code:
.Columns.Add("Partenza", System.Type.GetType("System.String"))
do this:
vb.net Code:
.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.
-
Jul 24th, 2020, 12:15 AM
#5
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:
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:
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:
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.
-
Jul 24th, 2020, 12:34 AM
#6
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:
- Create a List(Of Train)
- Assign the value of the list equal to the deserialized data using the JsonConvert::DeserializeObject(Of T) method
- Create a new BindingSource
- Set the DataSource of the BindingSource equal to the List(Of Train)
- Bind your DataGridView to the BindingSource
- 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:
Last edited by dday9; Jul 24th, 2020 at 01:11 AM.
-
Jul 24th, 2020, 02:23 AM
#7
Re: linq query to sum total items
Originally Posted by dday9
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.
-
Jul 24th, 2020, 08:22 AM
#8
Re: linq query to sum total items
Hmm, I always thought it stood for "plain ole C# object".
-
Jul 24th, 2020, 01:11 PM
#9
Thread Starter
Member
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..
Last edited by eurostar_italia; Jul 24th, 2020 at 01:23 PM.
-
Jul 25th, 2020, 04:25 AM
#10
Re: linq query to sum total items
Originally Posted by eurostar_italia
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.
-
Jul 25th, 2020, 04:47 AM
#11
Thread Starter
Member
Re: linq query to sum total items
Originally Posted by ChrisE
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).
-
Jul 25th, 2020, 01:12 PM
#12
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.
-
Jul 27th, 2020, 05:20 PM
#13
Thread Starter
Member
Re: linq query to sum total items
Originally Posted by dday9
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..
-
Jul 27th, 2020, 05:32 PM
#14
Thread Starter
Member
Re: linq query to sum total items
this i a json file with some data i need to show...763reservation.txt
-
Jul 27th, 2020, 05:33 PM
#15
Thread Starter
Member
Re: linq query to sum total items
data attached,thanks @ChrisE
-
Jul 28th, 2020, 04:57 AM
#16
Re: linq query to sum total items
Originally Posted by eurostar_italia
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.
-
Jul 28th, 2020, 05:08 AM
#17
Thread Starter
Member
Re: linq query to sum total items
Originally Posted by ChrisE
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")
-
Jul 28th, 2020, 08:51 AM
#18
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
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
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.
-
Aug 2nd, 2020, 06:46 PM
#19
Thread Starter
Member
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
-
Aug 2nd, 2020, 07:01 PM
#20
Thread Starter
Member
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)....
-
Aug 3rd, 2020, 08:41 AM
#21
Re: linq query to sum total items
Originally Posted by eurostar_italia
@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.
-
Aug 3rd, 2020, 02:28 PM
#22
Thread Starter
Member
Re: linq query to sum total items
Originally Posted by ChrisE
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.
-
Aug 3rd, 2020, 04:38 PM
#23
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|