Live Results?

[Bryan]

I usually use Open Office to fill in the spreadsheet, and haven't had any issues. But Ton's seems to do enough advanced stuff that OpenOffice doesn't work.

Does anyone else use OpenOffice in their competitions?

 

[Ton]

I use the new short cuts for the name of the sheet

e.g You have a 2x2 sheet , I have 222 , the same name used in the WCA registration page. might this be the problem?

My results sheet has orginal much more in it, it was merged with the idea of Clement and Ron

So the sheets get the name directly of the registration sheet

My WCA sheet has the followings specs
-Results per event are automated filled in , when the event start you have to copy/ paste special the names as value to remove the formulas (else you can not sort)
-Make grouping list
-print scoresheet per event + the group
-Print Certifactes with the name and result, for this you need just prepared certifacates without the name and result
-Print result of all events per competitor, when uses it takes at least 60 min for 100 competitors to print, so I can not use this most of the times

Yeah, I've been looking at your spreadsheet for a little while now... it is very ingenious. I may try using some of what you have done for future spreadsheets of mine.

The problem with the script is actually in the excel reader code... its former maintainer never bothered to handle some of the data. Fortunately, about a month ago, it looks like a new maintainer took over, and has (as of last month) released new code fixing this bug.

I've updated my script to incorporate this new version, which should fix the problem you were having. Same links as before^^

Oke try than the full excel sheet (I removed the date of birth)

http://www.speedcubing.com/events/Czech2009/CzechOpen2009results.xls

 

[Ton]

I use the new short cuts for the name of the sheet

e.g You have a 2x2 sheet , I have 222 , the same name used in the WCA registration page. might this be the problem?

My results sheet has orginal much more in it, it was merged with the idea of Clement and Ron

So the sheets get the name directly of the registration sheet

My WCA sheet has the followings specs
-Results per event are automated filled in , when the event start you have to copy/ paste special the names as value to remove the formulas (else you can not sort)
-Make grouping list
-print scoresheet per event + the group
-Print Certifactes with the name and result, for this you need just prepared certifacates without the name and result
-Print result of all events per competitor, when uses it takes at least 60 min for 100 competitors to print, so I can not use this most of the times

Yeah, I've been looking at your spreadsheet for a little while now... it is very ingenious. I may try using some of what you have done for future spreadsheets of mine.

The problem with the script is actually in the excel reader code... its former maintainer never bothered to handle some of the data. Fortunately, about a month ago, it looks like a new maintainer took over, and has (as of last month) released new code fixing this bug.

I've updated my script to incorporate this new version, which should fix the problem you were having. Same links as before^^

Thanks looks promising!

Question: why are the names on two lines
http://www.speedcubing.com/events/Czech2009/results/




Any why this is a very elegant/simple solution to post live results

 

[JBCM627]

Thanks looks promising!

Question: why are the names on two lines
http://www.speedcubing.com/events/Czech2009/results/

Any why this is a very elegant/simple solution to post live results

Thanks for testing The script is more refined now, and should do a better job displaying info.

The names appeared on two lines because the spreadsheet had space reserved for data in many blank cells, and the script was outputting them as empty table cells, effectively shrinking the html table cell sizes. It shouldn't do this any more though.

It should also be more careful about displaying sheets... any sheets without data in the "A1" cell won't be displayed. The script also expects the first sheet to be the registration sheet so it has a list of competitors to look up data for. It looks like there was a small bug in the excelreader script, which is why the Sq1 and other sheets aren't being displayed... I've corrected for this now though, so they should show up. Another tweak is that it no longer relies on the sheets to specify what format the times are in, but grabs that information from the cells.

The changes have been uploaded again, so you can redownload if you like.


That was a huge workbook too, btw... I was only intending this script to be used for smaller amounts of data. Perhaps I'll add in something to let it cache processed data...

 

[Ton]

Thanks looks promising!

Question: why are the names on two lines
http://www.speedcubing.com/events/Czech2009/results/

Any why this is a very elegant/simple solution to post live results

Thanks for testing The script is more refined now, and should do a better job displaying info.

The names appeared on two lines because the spreadsheet had space reserved for data in many blank cells, and the script was outputting them as empty table cells, effectively shrinking the html table cell sizes. It shouldn't do this any more though.

It should also be more careful about displaying sheets... any sheets without data in the "A1" cell won't be displayed. The script also expects the first sheet to be the registration sheet so it has a list of competitors to look up data for. It looks like there was a small bug in the excelreader script, which is why the Sq1 and other sheets aren't being displayed... I've corrected for this now though, so they should show up. Another tweak is that it no longer relies on the sheets to specify what format the times are in, but grabs that information from the cells.

The changes have been uploaded again, so you can redownload if you like.


That was a huge workbook too, btw... I was only intending this script to be used for smaller amounts of data. Perhaps I'll add in something to let it cache processed data...

works great, take 1 to 2 min total to upload per event,

http://www.speedcubing.com/events/Indonesian2009/Live%20results.html

 

[tim]

Nice job, guys. I can't wait watching the live results .

 

[JBCM627]

Glad it works

I'm working on making a web interface for it, so you can upload the .xls and change settings in a browser. I may have that done by the end of this weekend, but we'll see.

 

[Ton]

Thanks looks promising!

Question: why are the names on two lines
http://www.speedcubing.com/events/Czech2009/results/

Any why this is a very elegant/simple solution to post live results

Thanks for testing The script is more refined now, and should do a better job displaying info.

The names appeared on two lines because the spreadsheet had space reserved for data in many blank cells, and the script was outputting them as empty table cells, effectively shrinking the html table cell sizes. It shouldn't do this any more though.

It should also be more careful about displaying sheets... any sheets without data in the "A1" cell won't be displayed. The script also expects the first sheet to be the registration sheet so it has a list of competitors to look up data for. It looks like there was a small bug in the excelreader script, which is why the Sq1 and other sheets aren't being displayed... I've corrected for this now though, so they should show up. Another tweak is that it no longer relies on the sheets to specify what format the times are in, but grabs that information from the cells.

The changes have been uploaded again, so you can redownload if you like.


That was a huge workbook too, btw... I was only intending this script to be used for smaller amounts of data. Perhaps I'll add in something to let it cache processed data...

I found this visual basic macro to cleanup my sheet

Use
Macro -> Visual Basic and make a module , you now have a nice cleanup macro "DeleteHiddenRows"

This is also use full after you delete line , your end page marker will still be there, afrer clear the cells , can can use the macro to reset the end page marker
btw use Ctrl Home and Ctrl End, to see where your page end is

Sub DeleteHiddenRows()
Dim iRow As Long

With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False

With ActiveSheet.UsedRange
For iRow = .Row + .Rows.Count - 1 To .Row Step -1
If Rows(iRow).Hidden Then Rows(iRow).Delete
Next iRow
End With

.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

 

[Ton]

Glad it works

I'm working on making a web interface for it, so you can upload the .xls and change settings in a browser. I may have that done by the end of this weekend, but we'll see.

Small question
http://www.speedcubing.com/events/Czech2009/results/competitors.php?p=Ton%20Dennenbroek

What must I do to correct the display per person, now the first round results appears twice
For me it is a great addition, compared post directly into the database, which needs more delicate handling

 

[JBCM627]

Small question
http://www.speedcubing.com/events/Czech2009/results/competitors.php?p=Ton%20Dennenbroek

What must I do to correct the display per person, now the first round results appears twice
For me it is a great addition, compared post directly into the database, which needs more delicate handling

Huh, that is odd. It seems to be matching your name with the first blank cell at the bottom of your sheet containing a formula; I'll have to look into that more. For now, you can force it to only display your name once per spreadsheet... put a "break;" in on line 94, or near the end of the script right after the echo "</tr>";.

 

[Ton]

Huh, that is odd. It seems to be matching your name with the first blank cell at the bottom of your sheet containing a formula; I'll have to look into that more. For now, you can force it to only display your name once per spreadsheet... put a "break;" in on line 94, or near the end of the script right after the echo "</tr>";.

Done

I added a time stamp (local time of competion) of the last time
of the scores

http://www.speedcubing.com/events/Indonesian2009/Live%20results.html


I changed in live.inc.php

//	echo "<a name='results'></a><h5><u>".$data->val(1,1,$enum)." - Results</u></h5>";
To
echo "<a name='results'></a><h5><u>".$data->val(1,1,$enum)." - Results Last"," modified on: " . date("d-m-y H:i:s", filemtime($results_data['spreadsheet_filepath'])+7200+43200)," WIB (UTC+07)</u></h5>";

 

[Ton]

Huh, that is odd. It seems to be matching your name with the first blank cell at the bottom of your sheet containing a formula; I'll have to look into that more. For now, you can force it to only display your name once per spreadsheet... put a "break;" in on line 94, or near the end of the script right after the echo "</tr>";.

Done

I added a time stamp (local time of competion) of the last time
of the scores

http://www.speedcubing.com/events/Indonesian2009/Live%20results.html


I changed in live.inc.php

//	echo "<a name='results'></a><h5><u>".$data->val(1,1,$enum)." - Results</u></h5>";
To
echo "<a name='results'></a><h5><u>".$data->val(1,1,$enum)." - Results Last"," modified on: " . date("d-m-y H:i:s", filemtime($results_data['spreadsheet_filepath'])+7200+43200)," WIB (UTC+07)</u></h5>";

The +7200 was need to correct the timezone of our server (speedcubing.com UTC -3)

 

[Ton]

Huh, that is odd. It seems to be matching your name with the first blank cell at the bottom of your sheet containing a formula; I'll have to look into that more. For now, you can force it to only display your name once per spreadsheet... put a "break;" in on line 94, or near the end of the script right after the echo "</tr>";.

Done

I added a time stamp (local time of competion) of the last time
of the scores

http://www.speedcubing.com/events/Indonesian2009/Live%20results.html


I changed in live.inc.php

//	echo "<a name='results'></a><h5><u>".$data->val(1,1,$enum)." - Results</u></h5>";
To
echo "<a name='results'></a><h5><u>".$data->val(1,1,$enum)." - Results Last"," modified on: " . date("d-m-y H:i:s", filemtime($results_data['spreadsheet_filepath'])+7200+43200)," WIB (UTC+07)</u></h5>";

The +7200 was need to correct the timezone of our server (speedcubing.com GMT -2)

 

[JBCM627]

I added a time stamp (local time of competion) of the last time
of the scores

http://www.speedcubing.com/events/Indonesian2009/Live%20results.html


I changed in live.inc.php

//	echo "<a name='results'></a><h5><u>".$data->val(1,1,$enum)." - Results</u></h5>";
To
echo "<a name='results'></a><h5><u>".$data->val(1,1,$enum)." - Results Last"," modified on: " . date("d-m-y H:i:s", filemtime($results_data['spreadsheet_filepath'])+7200+43200)," WIB (UTC+07)</u></h5>";

The +7200 was need to correct the timezone of our server (speedcubing.com GMT -2)

Very nice addition. I'll make that change to the version I have soon.

 

[Dave Campbell]

It is a nice idea. What is the intent for availability? It seems a little silly for every organizer to have to download and install the script to use it on their own. Why not centralize it somewhere? I see it is on speedcubing.com, but i am assuming that is because Ton is involved.

Is there any thought to making it a feature available to all organizers for his or her own competition on speedcubing.com/worldcubeassociation.org? You could have some sort of public upload with a validation key that must be entered so we can ensure there are no rogue files uploaded to corrupt the data (or, rather, minimize the likelihood).

Keeping it public allows everyone to have access to the same version, any updates are immediately available to everyone. No need to propagate the changes. It also allows the users to find live results easier. No need to look for competition X's server. Just set up a simple index page that all can hit to see any live results available.

I can see bandwidth being a possible reason to not go this route. The biggest hit most likely being the constant upload of the file to the server and its processing. One could also argue disc space as an issue. But you could remove the data and just send the user to the official results page on the WCA site after the competition results have been finalized.

Finally, a curiosity question. If it takes 1-2 minutes to upload per event how does the script handle the update? Is there downtime during those 1-2 minutes while it deletes old data and adds new data?

 

[Ton]

The file is locked for about 20 seconds during the time of the upload, you can not get results , in stead you get this message

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 16 bytes) in /home/u6/speedcubing/html/events/Czech2009/results/include/reader.php on line 69

I guess we can change the error code is , please wait while file is refreshed

 

[JBCM627]

It is a nice idea. What is the intent for availability? It seems a little silly for every organizer to have to download and install the script to use it on their own. Why not centralize it somewhere? I see it is on speedcubing.com, but i am assuming that is because Ton is involved.

Is there any thought to making it a feature available to all organizers for his or her own competition on speedcubing.com/worldcubeassociation.org? You could have some sort of public upload with a validation key that must be entered so we can ensure there are no rogue files uploaded to corrupt the data (or, rather, minimize the likelihood).

Keeping it public allows everyone to have access to the same version, any updates are immediately available to everyone. No need to propagate the changes. It also allows the users to find live results easier. No need to look for competition X's server. Just set up a simple index page that all can hit to see any live results available.

I can see bandwidth being a possible reason to not go this route. The biggest hit most likely being the constant upload of the file to the server and its processing. One could also argue disc space as an issue. But you could remove the data and just send the user to the official results page on the WCA site after the competition results have been finalized.

Finally, a curiosity question. If it takes 1-2 minutes to upload per event how does the script handle the update? Is there downtime during those 1-2 minutes while it deletes old data and adds new data?

Actually public uploading is something I have done already:
http://koii.cubingusa.com/cm/results/admin.php

I've added in a bit to 'cache' the file, so when it is uploaded, it is broken apart into a few few easy-to-read files. This makes the down time significantly less, since it only locks the individual event files for a very short time. There is also a noticeable difference when displaying larger spreadsheets like Ton's... the cached results tax the server far less.

Right now the script doesn't really handle errors or do much validation, and can therefore be broken pretty easily.

Keeping it public does make sense, although it would limit customization. Perhaps someone wants to change the css or include some code that alters the display somehow... this wouldn't be possible if it were too centralized. I agree that bandwidth and memory usage are both issues to consider, though.

 

[JBCM627]

What better to do on a Saturday night than code. I could even cube, but no...

I've improved the live results script some more now since last posting in here. A few new things:
* grabs and displays styles from spreadsheet (well, for the most part...)
* included Ton's addition to display time of last update
* cleaner individual results display (Tim's suggestion)
* public upload / filesystem checks
* more organized/clean filesystem structure
* notices for events that are done/in progress/not begun
* should be a bit easier to customize
* added in some javascript to handle data
* touched up css some (just don't use IE < 6, although it still isn't bad even in that...)

I really debated adding in the httprequests, since now it won't work at all in some browsers, and providing links to specific results becomes harder - almost as bad as using frames .

Sample is here:
http://koii.cubingusa.com/cm/results/
Downloads here:
http://koii.cubingusa.com/cm/results/results.zip
http://koii.cubingusa.com/cm/results/results.7z

Things I'd still like to do/improve:
* Check for small errors in spreadsheets and alert the uploader so they can correct it - to make Bob and Ron's lives easier
* Store spreadsheet styles more efficiently
* cleaner/ better code in general
* sort results by specified column
* clean up css some more
* use hashes instead of query strings - better for those URLs. Then I'll be happy about using javascript.

It is a nice idea. What is the intent for availability? It seems a little silly for every organizer to have to download and install the script to use it on their own. Why not centralize it somewhere?

I've been thinking about this, and have written the script to make handling more than one competition fairly straightforward. I'm not sure if this is a direction I want to head in though, since the WCA results system will amass all official results regardless of any other attempt at centralization - so why duplicate what has already been done? Also, if more people toy with the code, that will probably lead to better code and more features. With that being said, I wouldn't mind having something set up for people who don't want to mess with setting up a system on their own.

 

[JBCM627]

My first triple post. That hash thing wasn't as hard as I thought it would be. The last * on the to-do list can be crossed off then...

^^Source files for download should be updated.

 

[Carson]

It looks a lot cleaner than it did the last time I saw it, and having the styles to carry over will be very nice. I am sure the list of completed/in progress events will clear up some confusion as well.

I especially look forward to being able to sort by column.

GREAT work Jim!!!