Is there any way to make use of variable names as part of a class structure

[jimctr]

Here I have an assignment statement:

ThisYear.Item(j).Jan.Wkx_HrBlk1 = GetHrBlocksPerMonth(HRBLK1)

Jan corresponds to the month January. Is there any way to insert a variable in place of Jan, which can also assume Feb, Mar, Apr, etc. if assigned to do so?


Class Structure Below

Public Class Year

'Generate Data For Each Year
Public CurrentYear As UInt16 'Holds Current Year Value
Public TotalImages As UInt16 'Total Images collected on a yearly basis

Public JanTotals As UInt16 'Image Totals for the Month Of Jan
Public FebTotals As UInt16 ' Etc
Public MarTotals As UInt16
Public AprTotals As UInt16
Public MayTotals As UInt16
Public JunTotals As UInt16
Public JulTotals As UInt16
Public AugTotals As UInt16
Public SepTotals As UInt16
Public OctTotals As UInt16
Public NovTotals As UInt16
Public DecTotals As UInt16

'Subclass of Year are Months
Public Jan As New Month
Public Feb As New Month
Public Mar As New Month
Public Apr As New Month
Public May As New Month
Public Jun As New Month
Public Jul As New Month
Public Aug As New Month
Public Sep As New Month
Public Oct As New Month
Public Nov As New Month
Public Dec As New Month


Public Class Month

Public Wk1 As New Week
Public Wk2 As New Week
Public Wk3 As New Week
Public Wk4 As New Week
Public Wk5 As New Week


Public Wkx_HrBlk1 As UInt16
Public Wkx_HrBlk2 As UInt16
Public Wkx_HrBlk3 As UInt16
Public Wkx_HrBlk4 As UInt16
Public Wkx_HrBlk5 As UInt16
Public Wkx_HrBlk6 As UInt16
Public Wkx_HrBlk7 As UInt16
Public Wkx_HrBlk8 As UInt16
Public Wkx_HrBlk9 As UInt16
Public Wkx_HrBlk10 As UInt16
Public Wkx_HrBlk11 As UInt16
Public Wkx_HrBlk12 As UInt16


Public Class Week
Public HrBlk1 As UInt16
Public HrBlk2 As UInt16
Public HrBlk3 As UInt16
Public HrBlk4 As UInt16
Public HrBlk5 As UInt16
Public HrBlk6 As UInt16
Public HrBlk7 As UInt16
Public HrBlk8 As UInt16
Public HrBlk9 As UInt16
Public HrBlk10 As UInt16
Public HrBlk11 As UInt16
Public HrBlk12 As UInt16
End Class
End Class
End Class

***
Also, I also have an enumeration which mirrors the structure for month. Is there any way to leverage the Enumeration?

Public Enum MonthEnum

Dec = 0
Jan = 1
Feb = 2
Mar = 3
Apr = 4
May = 5
Jun = 6
Jul = 7
Aug = 8
Sep = 9
Oct = 10
Nov = 11

End Enum

Here is the context for the assignment statement. Note that k variable references the Month so I would like to use this variable to point to the certain class structure.



'Generate Totals for Each Month
For k = 0 To 11 'k refers to Months
WM = k
GetMonths(k) = (From Row As DataRow In dt.Rows Where Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year = WY _
Where Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Month = WM _
Select Row.Field(Of UInteger)("HRBlk1")).Count 'Can Refer to a random Column. Will count all rows irrespective of value

For p = 0 To 11 'p refers to Hour Blocks
TempString = "HrBlk" & CStr(p + 1)
GetHrBlocksPerMonth(p) = (From Row As DataRow In dt.Rows Where Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year = WY _
Where Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Month = WM _
Select Row.Field(Of UInteger)(TempString)).Sum(Function(ThisRow) ThisRow) 'Break down of Hour Blocks by Year.
Next

Select Case k

'Assigments mapping k to Class identifier would go here

End Select



ThisYear.Item(j).Jan.Wkx_HrBlk1 = GetHrBlocksPerMonth(HrBlkEnum.HrBlk1)

Next

 

[Herman]

Nope, this is not possible... Hence why I suggested you use a datatable instead when you asked earlier, which you seem to have done, but only halfway...

 

[jimctr]

Back to the datatable. I still need to assign data to variables (I believe), because ultimately these variables will be fed into an app such as fusion charts (converted to XML first) to chart the data. What am I missing? What the datatable bought me is not having to build functions into the class structure.

Thanks

 

[Herman]

Well think about what you are doing now... What all this code does. All the code you have posted up to now is to do just one thing, establish a data structure. All of this is done for you when you use a proper data object. Why reinvent the wheel? All you really need to do is establish what data you need to store, what you might want to cross reference, what result you want to obtain. Once that is done, you establish your structure from the top down. All the rest will just be about writing one liner queries to get the information you want.

For example, and because I am not sure what exactly you are designing here I will just take a simple example. A music album database with one table for albums, another for songs, and another for artists.

The 'Albums' table might have fields like those, in addition to a primary key called AlbumID: AlbumName, ISBNNumber, ExArtistId

The 'Songs' table might have those: SongId, SongName, WriterName, ExAlbumId, ExArtistId

And finally the Artists table: ArtistId, ArtistName, ITunesURL

Now let's say you want to know the AlbumName, WriterName, and ArtistName for a given SongName:

SELECT a.AlbumName, s.WriterName, b.ArtistName  -- Choose the fields we want in our resulting table
FROM Songs s -- From this base table, which we alias 's'...
INNER JOIN Albums a ON s.ExArtistId = a.ExArtistId -- And this secondary table a...
INNER JOIN Artists b ON s.ExArtistId = b.ArtistId -- And also from this table b, and only give records where s.ExArtistId = a.ExArtistId = b.ArtistId
WHERE s.SongName LIKE @varSongName -- And only those records where the song name matches what the user entered.

You just run that query, or its Linq equivalent, and the beauty of it all is that you don't have to write anything more than a variation of that query to get a completely different yet accurate result. If you need to total an integer column for all records dated between dateX and dateY, you just "SELECT SUM(IntColumn) WHERE DateCol >= @dateX AND DateCol <= @dateY" . I guess the point is trying to reinvent the whole thing is pointless, and a waste of time in the end. All you would possibly need to be doing is already implemented for you.

 

[jimctr]

Thanks Herman for your well thought out post.

The data table I produced (though not entirely complete yet) contains all the essential data for producing these graphs, though some of the data is not in a form which is usable as is. What I believe I hear you saying is that instead of creating a class structure to organize all of the data in a state which is appropriate for graphing, I should instead spawn new tables (or alternately use multidimensional variables) to hold the data directly for graphing purposes. Really, the first table I generated is of the raw type and is only an intermediary step to the final solution. The data needs to be messaged to get it in the correct form where columns can simply be read to be placed into graphical format, similar to the way graphs are produced from spreadsheets in excel.

 

[Herman]

Well describe for instance one graph you might want to render.

 

[jimctr]

I know I am being stubborn in my thinking and I know exactly what you are talking about. For some reason psychologically I am contented by the fact that all the completed data is stored somewhere (not virtual where it can be derived on the fly). I think they call that anal ;o). It's even more wasteful given how I was going to structure things. I'll explain. With Fusion Charts or some similar program, you can drill down through the various graphs you generate.

The very first chart a person would see is a bar chart of year mapped against frequency (this in the event that the camera images stored span into a different calendar year). If they click on the 2012 bar, say, this takes them to the next level for that year (notice in this case, 2011 data isn't really even required unless they decide to back up and drill down on that year). The next chart will be for a specific calendar year, mapping month against frequency. Click on a desired month, and you drill down to the next level where we subdivide months into blocks of hours (default: 2 hour increments through the 24 hour military time). At this point I would superimpose Temperature on one axis and Pressure on the other, and a mouse over will give frequency total. At this level I can also get into averages, standard deviations and correlations if I want. Click on a given hour block for a given month in a given year, and this brings me to a month subdivided into weeks broken down into the 12 hour blocks per week (as opposed to per month as above).
(P.S., this is where I am with my structure definition and I can clearly see how onerous and wasteful this is with the massive Nested Select Case I need to generate). Clicking on hour block at this level references back to the images for that particular time slot.

As a general overview this is the game I am trying to play. So I need to store some information for accessing data at the low levels (which year, which month, which week) but certainly not everything.

 

[Herman]

Ok so your main data source could be a table with fields ID(PK), ImagePath (string), TimeStamp (datetime), Latitude (float), Longitude (float), Temperature (float), Pressure (float), MoonPhase (??). Just dump all your thousands of picture data in the table like this.

Now let's take your first graph. You want year vs frequency. A frequency is essentially just a count over a period of time:

SELECT DATEPART(YEAR, TimeStamp) AS ThatYear, COUNT(*) AS ImageCount FROM MyTable GROUP BY ThatYear

That will return a table with two fields, one called ThatYear containing the year, and another ImageCount containing the total number of records for that year.

Your second query (the first drill-down) would be like this:

SELECT DATENAME(mm, TimeStamp) AS MonthName, COUNT(*) AS ImageCount FROM MyTable WHERE DATEPART(YEAR, TimeStamp) = @ChosenYear GROUP BY MonthName

That will return a table with 2 fields, and over a year 12 rows, containing the name of the month in one column and the count of occurences in each month in the other. Once your data is in place, you NEVER have to mess with the structure, just write a query to get just the part of info you need. To generate the graph, you only need to assign each field to an axis.