Month: June 2014

Keyser Söze, Code and the World Cup

world-cup-fifa_1401359010

It seems appropriate given the name of this site and 2014 being a world cup year that I might post something on code that I found a while back now related to establishing fixtures in a league coded by, for me, a mythical character. Back then I was taking part in a squash league with some friends and we needed to figure out some way of organising the matches for everyone. Seemed simple enough everyone plays everyone else on consecutive weekends. Turns out its not quite so easy and there’s a whole branch of mathematics called combinatronics that specifically looks at the way of optimising this kind of problem. In that really humble syntax of mathematicians I heard this phrase being banded about – a non trivial problem. As ever I turned to the internet and found some code by someone called Dev Ashish. Now I don’t know about you but prior to the internet I never had access to this kinds of expert and the power of the code really blew me away. The code very neatly creates the required number of matches in a table and allows me to organise matches for individuals to play each other and from there I was able to keep scores on everyone. It was in a word a bit of genius coding.

That was approximately 2005 and come 2014 and my blog the world cup reminded me of this amazing piece of coding.
I have my suspicions of where Dev Ashish is now but I can’t tell for sure.

Keyser Söze / Woland / Dev Ashish – they’re out there…

Joking aside many thanks to Dev Ashish for posting an amazing piece of code…

Option Compare Database
Option Explicit

Public intLeagueno As Integer
Public strLeaguenme As String


Function CalculateFixtures(ByVal Age As Integer, ByVal startdate As Date, ByVal EndDate As Date) As Integer

'**************************************************
' Set Database connections and Recordsets Variables
' Coded by Dev Ashish
'**************************************************
Dim cnn As ADODB.Connection
Dim rstTeams As ADODB.Recordset
Dim rstFixtures As ADODB.Recordset
    
'****************************************
' Create Integer Variables
'****************************************
Dim NumberofFixtures As Integer     ' Number of Fixtures between teams
Dim NumberofMatches As Integer      ' Number of Matches to be played
Dim NumberofTeams As Integer        ' Number of Teams
Dim Week As Integer                 ' Week Number for Fixtures

Dim FirstTeam As Integer
Dim LastTeam As Integer

Dim StartPosition As Integer

    Dim strtdate As String
    Dim intMsgbox As Integer
    strtdate = InputBox("Enter the date you want the league to start", "Question?")
    If (strtdate = "") Then
    intMsgbox = MsgBox("Thanks anyway")
    startdate = 3500
    
    Else
    startdate = strtdate
    intMsgbox = MsgBox("Calculating the fixtures starting" & " " & startdate, vbOKOnly, "Result")
    
End If

Dim iCounter As Integer

'****************************************
' Create Player String Variables
'****************************************

Dim Player1 As String
Dim Player2 As String

'****************************************
' Create Team/GameSequence Variables based on Number of Teams
'****************************************
Dim Team(50) As String
Dim GameSequence(50) As String
Dim TeamNames(1 To 50) As String

Set cnn = CurrentProject.Connection
Set rstTeams = New ADODB.Recordset
Set rstFixtures = New ADODB.Recordset



    '*********************************************************
    'Open the Tables Teams and Fixtures
    '*********************************************************
    rstTeams.Open "SELECT * FROM tblTeams Where leagueno = " & intLeagueno & "", cnn, adOpenKeyset, adLockOptimistic
    'Where AgeGroup = 'u" & Age & "'"
    
    rstFixtures.Open "tblFixtures", cnn, adOpenKeyset, adLockOptimistic
    
    '****************************************************
    ' Read the Team Names into an Array
    '****************************************************
    iCounter = 1
    
    Do While Not rstTeams.EOF

        TeamNames(iCounter) = rstTeams.Fields("Team")
        iCounter = iCounter + 1
        rstTeams.MoveNext
    
    Loop
    
    '*****************************
    'Set Main constants
    '*****************************
    NumberofTeams = iCounter - 1
    NumberofFixtures = NumberofTeams - 1
    NumberofMatches = NumberofTeams / 2
    
    '*****************************************************
    ' Clear the Game Sequence Array
    '*****************************************************
    For iCounter = 1 To NumberofFixtures
        GameSequence(iCounter) = ""
    Next iCounter
    
    '*****************************************************
    ' Clear the Teams Array
    '*****************************************************
    For iCounter = 1 To NumberofTeams
        Team(iCounter) = iCounter
    Next iCounter
    
    FirstTeam = 0
    
    '*****************************************************
    ' Create the Game Sequence ready for the fixtures
    '*****************************************************
    For Week = 1 To NumberofFixtures
        FirstTeam = FirstTeam + 1
        
        For iCounter = FirstTeam To FirstTeam + NumberofFixtures - 1
            If iCounter > (NumberofFixtures) Then
                LastTeam = iCounter - NumberofFixtures
            Else
                LastTeam = iCounter
            End If
            GameSequence(Week) = GameSequence(Week) & " " & Format(Team(LastTeam), "00")
        Next iCounter
        GameSequence(Week) = Trim(GameSequence(Week)) + " " & Format(Team(NumberofTeams), "00")
    Next Week
    
    '***************************************************
    'Insert the new fixtures into the Table
    '***************************************************
    For Week = 1 To NumberofFixtures
        StartPosition = 1
        'Debug.Print "Week " & Week
        For iCounter = 1 To NumberofMatches
            Player1 = Mid(GameSequence(Week), StartPosition, 2)
            Player2 = Left(Right(GameSequence(Week), (StartPosition) + 1), 2)
            StartPosition = StartPosition + 3
            
            rstFixtures.AddNew
            rstFixtures.Fields("WeekNo") = Week
            'rstFixtures.Fields("HomeTeam") = TeamNames(HomeTeam)
            rstFixtures.Fields("Player1") = TeamNames(Player1)
            'rstFixtures.Fields("AwayTeam") = TeamNames(AwayTeam)
            rstFixtures.Fields("Player2") = TeamNames(Player2)
            'rstFixtures.Fields("Age") = Age
            rstFixtures.Fields("FixDate") = startdate
            rstFixtures.Fields("Leagueno") = intLeagueno
            rstFixtures.Update
        
        Next iCounter
    startdate = startdate + 7
    If startdate > EndDate Then Week = NumberofFixtures + 1
    Next Week

'****************************************
'Close the tables
'****************************************
rstTeams.Close
Set rstTeams = Nothing
rstFixtures.Close
Set rstFixtures = Nothing

End Function

Complex Event Processing (How Cool)

complexEventProcessing2

As part of my timing software solution I had been interested in seeing if there was a better solution to the constant pulling of information from the timing boxes. It would for instance be far better to have some sort of push mechanism where calculations were only made when new information was received.

Turns out that with the advent of big data (which to my mind is being driven by sensors) it seems to be a bit of a hot topic. Doubtless brought on by the myriad number of devices and sensors which are in the market at the moment. Microsoft have a framework called StreamInSight which is free to those that already have SQL Server 2012 licence (not sure which level)

I still have a lot of questions.
Leading on from the post I did on designing my own timing software I consider that it would be useful as a single time could come in and it could be linked up with its partner times and a lap number could be calculated that would allow the relatively easy display via pivot. A process that could occur as the information came in rather than being pulled and batched which is the way my system does it at the moment.

Not sure how I can get my hands on the framework though… Would love to try it out.

Resources here.

StreamInsight: More than Just an API

Step through forms and alter properties.

A nice patch of code that will allow you to cycle through a series of forms and make them read only. Useful if you don’t have immediate access to make changes to the backend, SQL Server or active directory. If you have any programmatic save record commands you will have to deprecate those lines.

Public Sub turnOffFormProps()
Dim strForm As String, db As DAO.Database
Dim doc As DAO.Document
Set db = CurrentDb

For Each doc In db.Containers("Forms").Documents
strForm = doc.Name
DoCmd.OpenForm strForm, acDesign
Debug.Print Forms(strForm).Properties("AllowAdditions")
Forms(strForm).Properties("AllowAdditions") = False
Debug.Print Forms(strForm).Properties("AllowDeletions")
Forms(strForm).Properties("AllowDeletions") = False
DoCmd.Close acForm, strForm, acSaveYes
Next doc

Set doc = Nothing
db.Close
Set db = Nothing
End Sub

Attaching Databases to SQL Server 08R2 Express

jigsaw2
It should be noted that the following although the easiest way to get a new database into an instance it should not be used in a production environment. In fact doing so may get you sacked. If experimenting though this method should be fine. If in doubt seek help as in a production environment you would want to look through all the code before attaching anything into an instance.

Go to SQL server management studio and on the Databases tree
Right click and select
Attach…..

Attach database window should appear which will allow you to use the Add… button to navigate to the
\Data\ subdirectory where all the sql server databases are held.

IMPORTANT – prior to loading a file in the database will have needed to have been DETACHED and you should always move anything mdf file that you are wanting to put into a database into the data subdirectory.
This ordinarily is done by going to database in question scrolling down to the database and right clicking on the database

Tasks > – Detach…

WARNING
If a database has not been detached properly it may NOT be possible to re-attach the database this is of course a security feature. So experimenting with simple moving files about will not work…

The listed code below does it at command line but the above works in SSMS

The default location for databases in SQL 08R2 Express is
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

The default location for database in SQL 2012 is
C:\Program Files\Microsoft SQL Server\MSSQL11.[InstanceName\MSSQL\DATA

Using VBA to open URL in chrome

Haven’t tried this out but could be useful. I have a digital mapping web application that I link to from a database and it has issues with IE but works perfectly in Chrome…

shell("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url http:google.ca")

Update : 24 June 2014 – just tested this out on a windows 8.1 device and seems to work

%localappdata%\google

Scope of variables – And Getting Confused

Note to self

If you are wanting to pass parameter values between forms ensure that you place public variables in a module outside of the form.

IMPORTANT – additionally ensure that the same variable names are NOT also listed in the function on the form. If you don’t remove the local variables of the same name. Parameters will appear to be set to the public variables but when you try and call them subsequent to the local scope closure they will be blank.

This must mean that variables are set consecutively and transferred into a memory address. If two variables of the identical name are set the first gets one memory address and the second another. Thus they may appear the same but reference different locations. For clarity be careful with your variable definitions!!!!

slide-1-638