Archi's EHM 2007 Data Editing Blog

Post by **archibalduk** » Wed Nov 09, 2011 5:43 pm

This blog is all about my work editing the Eastside Hockey Manager 2007 database in order to making updating EHM easier and less time consuming. My overall aim is to enable users to work more collaboratively in updating the database. Keeping the database up to date is currently a massive task and involves a lot of duplication because we have researchers putting the data into spreadsheets and then the database leader (such as Manimal) manually making those changes using the Pre Game Editor.

Originally, I had planned on creating a multi-user editor (a database editor that could be accessed by many users at the same time) however on reflection I don't think this is the best solution to the problem. Instead, I want to write a utility that processes researcher's spreadsheets and automatically makes the changes to the database. The name of this utility will be the EHM Updater.

In addition to the EHM Updater I'm also working on various other tools for editing/updating EHM as well as changing the EHM start date (the resultant Custom Start Date Patch can be downloaded here). My main priority is to support Manimal's roster updates and Nino's 1974 database as well as Bobmcgoo's EHM Realism Patch.

As I have written quite a range of tools and have a number of projects ongoing, I will use this first post of my blog to list the completed projects, those in progress and those I plan working on. I'm quite open to taking suggestions/requests from users on tools that might help edit EHM. If you have any suggestions/feedback, please do contact me via PM or the Data Editing Forum. That's not to say I'll write something on request, but I'm keen to get feedback and see what might make editing EHM easier.

Here is a list of my projects (I have moved the original post from here to the post below):

Last Updated: 10 December 2012

Ongoing Projects

EHM Updater (formerly known as the Staff Updater)
URL: http://www.ehmtheblueline.com/forums/vi ... 60#p142560
Status: In progress (high priority). This is a large project and will take quite a length of time. Beta versions for testing will be available from time to time.
Description: This will convert spreadsheets of research and apply them to the database. This was previously known as the Staff Updater but will now cover additional areas (such as competition history updating). Currently I have the following features planned:

Contract details (done)
Player attributes (done)
Staff career history (done)
Competition history (done)
Player awards history (done)
Remove player attributes for non-players (done)
Incorporate the Date of First Pro Contract fix (done)
Creating new players/staff (done)
Ability to remove/delete all retired and invalid staff from the database (done)
Possibly incorporate the player info generator with a Date of First Pro Contract generator (done)
Fix the bug with the attribute_errors.csv file where successfully applied attributes are listed as "UNKNOWN ERROR". (done)
Ability to retire/invalidate players (done)
Fix a bug whereby the Updater translates 'ů' as 'z'.
Name validator - remove unused names from the namesets (it will need to take into account the fact that some players do not have a nationality assigned)
Draft history and/or team rights
Incorporate the Retired Numbers Updater
Incorporate the Nations Updater/Extractor
Non-player attribute updating
Ability to change a non-player's job type using the contract updating spreadsheet

Mass Attribute Editor
URL: http://www.ehmtheblueline.com/forums/vi ... 110&t=8909
Status: Version 2.0 is 75% complete
Description: An improved version which will make things a little tidier and will also make it much easier to add additional attributes at a later date. Made mass adjustments to a range of attributes and filter according to player position and CA.

DB unCSD
URL: N/A
Status: 95% complete but needs be rewritten because it could be made more efficient and easier to maintain.
Description: Adjust all dates in the EHM 2007 by user defined value. E.g. this can be used to change a CSD Patched database for use with the hardcoded 2006/07 start date.

EHM C++ Libraries
URL: http://www.ehmtheblueline.com/forums/vi ... 110&t=8593
Status: In progress. No ETA at the moment.
Description: A collection of various common functions for editing/updating EHM written in C/C++. This will be open source. The aim is to help make it easier should anybody in the future want to write utilities for EHM.

EHM Memory Patch
URL: N/A - but check out a related project by Alessandro: http://www.ehmtheblueline.com/forums/vi ... =13&t=8739
Status: 50% complete
Description: This will allow you to make alterations to the game in a similar way to the CSD Patch. This could, for example, be used to modify league rules, etc.

Planned Projects

Custom Start Date Patch v2.0
Description: I plan on rewriting the Patch so that it is easier to maintain and also without the need for the .NET Framework being installed. I will also look at a couple of minor bugs reported e.g. in relation to using the fantasy draft and also the many NHL records news items that seem to appear.

Free Agent Utility
Description: The previous Free Agent Utility by mne2 unfortunately doesn't work with Windows 7. I'll therefore write a new version to work with Windows 7. I'll also look into adding a random CA/PA generator as an optional extra.

Released Projects

1974 Attribute Editor
Download: Probably only of interest to Nino, but I'll happily supply this on request
Status: Complete
Description: Mass edit certain staff attributes (mostly reputation settings) in order to help encourage Europeans to stay out of North America (in order to make the game more like the hockey world in 1974 where players rarely moved from Europe to North America).

Birth Year Fix
Download: http://www.ehmtheblueline.com/forums/vi ... =88&t=8683
Status: Version 1.0
Description: The Birth Year Fix tool sets the Birth Year field for each player/staff in EHM so that it is the same as their Date of Birth. Within EHM, there are two data fields which store the player/staff year of birth. One field is the Date of Birth and the other is the Year of Birth. If the Year of Birth does not match the Date of Birth then issues can be encountered in game with regards to junior player eligibilty for the NHL draft (i.e. players can be signed without having to first be drafted).

Contract Expirary Mass Editor
Download: http://www.ehmtheblueline.com/forums/vi ... =88&t=8337
Status: Version 2.0
Description: The Contract Expiry Mass Editor allows you to make adjustments to all of the player and staff contract expiry years in the EHM 2007 database. This tool may be particularly useful for those wanting to make a database compatible with the Custom Start Date Patch (such as a year-adjusted database - e.g. Lidas/Manimal Rosters v1.0 through to v3.2 - or a set of retro rosters) or those making a fantasy database.

Custom Start Date Patch
Download: http://www.ehmtheblueline.com/csdpatch
Status: Version 1.2
Description: Change the start date in EHM for any date after 2006/07. Unfortunately it is not possible to set a date before 2006/07.

Custom Start Date Checker
Download: https://skydrive.live.com/redir.aspx?ci ... 29C5C5!280
Status: Complete, but slightly out of date.
Description: Useful in helping to diagnose compatibility issues with the Custom Start Date Patch. This is probably of less relevance as the Patch has moved on and it seems it works on all Windows systems now.

Date of First Pro Contract Fix
Download: N/A - Will be included in the EHM Updater (but is also available on request)
Status: Version 1.0
Description: Repair and update the Date of First Pro Contract data. This resolves a problem whereby undrafted players can be signed without first going through the NHL entry draft.

Date Joined NHL Quick Fix
Download: N/A - Available on request
Status: Version 1.0
Description: The Date Joined Year Mass Editor allows a single year to be set for the Date Joined field for all players contracted to an NHL team.

Draft Year Mass Editor
Download: http://www.ehmtheblueline.com/forums/vi ... =88&t=8348
Status: Version 1.0
Description: Batch modify all draft years (i.e. the year a player was drafted) in the EHM 2007 database. This can also convert older Lidas/Manimal databases for use with the CSD Patch because of the way post 2006/07 draft picks were added to the database.

History Editor
Download: N/A
Status: On hiatus. This will form part of the EHM Updater instead.
Description: Edit the player career history and team competition history using Excel and Access. Enables a large-scale history updating project (this is how we updated a huge chunk of the 2006/07-2010/11 career history for Manimal v4.x). Although the editor was very successful, I think things could be done better and without the need for the somewhat unreliable Access file. All of the editing functions will therefore be developed for the EHM Updater instead.

Index.dat Generator
Download: http://www.ehmtheblueline.com/forums/vi ... =88&t=8689
Status: Version 1.0
Description: This tool will update your index.dat file in your EHM 2007 rosters/database. Whenever any records/data are added or removed from a .dat file, the index.dat file must be updated to reflect the new number of records in that file. Utilities such as the Pre-Game Editor and History Editor update the index.dat file automatically, but if you use another tool or hex edit the data then you must update the index.dat file yourself. By using this tool, it will update your index.dat file automatically.

Names Extractor
Download: N/A - Available on request
Status: Version 0.1
Description: Export the names from EHM, edit them in Excel and reimport them. This was written in order to fix some naming issues with the 1974 database.

Nation Extractor
Download: http://www.ehmtheblueline.com/forums/vi ... =88&t=9171
Status: Version 1.0
Description: Export certain nation data from EHM, edit them in Excel and reimport them. Editable data includes IIHF rankings, EU membership, reputation and importance.

Player Info Generator
Download: http://www.ehmtheblueline.com/forums/vi ... =88&t=9213
Status: Version 1.1
Description: The Player Info Generator creates a new player_info.dat file based upon the player career histories stored in the game’s database. The data stored in this file can be found under the ‘Player Info’ tab in the Pre Game Editor. It is important to keep the Player Info up to date because it affects aspects of the game such as rookie eligibility, RFA/UFA eligibility, some player records and eligibility for the NCAA. Therefore, each time you add/edit/delete player career histories or if you move players to a new team, you should use this tool to update the Player Info.

Retired Numbers Updater
Download: Available on request. This tool will eventually be incorporated into the EHM Updater.
Status: 100%
Description: Export the retired numbers from the database, edit it in Excel, and reimport it. I am also going to post this in the C++ Discussion Thread as an example on how data can be exported/imported from/to the database.

Team History Extractor
Download: N/A - Available on request
Status: Working but abandoned
Description: Allows team history (i.e. season-by-season league data) to be exported and updated. This has no impact on the game because it seems the game does not use this data. Therefore there seems no point in updating this.

Post by **archibalduk** » Sun Nov 13, 2011 11:40 am

Originally posted 9 November 2011:

Seeing as I've been doing a lot of work behind the scenes with regards to data editing, I thought I'd start a blog to chart my progress. Now that the CSD Patch is finalised (although I'd like to revisit it at some point to see if I can make it work with the CD-ROM version), I've been looking at data editing in order to bring EHM truly up to date in light of the fact that we're no longer restricted by the hardcoded 2006 start date.

Before I go any further I just want to emphasise that I'm no C++ expert. In fact I'm a complete novice. The CSD Patch is the first thing I've ever programmed and it was basically done by piecing together information from online tutorials/forums/articles. So anything I say in here is based only on what I've learned thus far and is certainly not any voice of authority or expertise.

Anyway, the first tasks I wanted to carry out were two-fold:

1) A contract expiry editor to update the expiries of every player and staff member en masse.
2) A draft year editor so that the post 2006 drafts in the Manimal rosters could be converted to their correct draft years. Also, it would act as a quick fix for other rosters so that draft rights are not lost when using a custom start year.

I've fairly recently completed both of these tasks and the resultant tools can be downloaded from the Utilities category of the EHM 2007 Downloads section. At the time of writing these tools, I didn't have access to the EHM 2007 database structure (thankfully Mne2 has now recovered the information from the SI Forums - and this can also be downloaded from the Downloads section). Thus I had to rely on trial and error in order to figure out the length of each record in the relevant roster .dat files and then figure out which bytes stored the information I needed to change. It took a fair bit of experimenting, but I got there relatively quickly (and thankfully kept my sanity in the process!).

Now that we have the EHM 2007 database structure, I'm really interested in creating some form of pre-game editor. This isn't because I'm in any way unhappy with Mne2's editor, it's just that I want to see if somehow we could allow multiple users to work on the database simultaneously. Also, I want to edit things like club records and history which cannot currently cannot be edited. Having now been playing around with the structure and C++ for a little while, I must say that I have the utmost respect for Mne2 and his editor. It is a real feat and we're incredibly lucky to have had somebody who has enabled us all to continue enjoying EHM since the game was shelved by SI. My attempt of creating an editor will not be anywhere near as advanced a Mne2's; instead, I'll be looking to harness the power of MS Access / LibreOffice Base so that it will do a lot of the work for me.

Before I jump head first into creating a full-blown editor, I want to look specifically at updating the player and competition histories. Using the CSD Patch with a start date of 2011/12, we're missing five years' worth of history and records. It would be many, many hours of work to bring the database up to date if only one person could do this. Thus I'm currently working on a project to enable multiple users to compile the data which I would then merge and import into the latest version of Manimal's rosters.

The key place to start is to export the relevant .dat files into a format that is easily accessible. I've decided that using the .csv format is the way to go. These files can easily be viewed/edited/imported using a variety of software such as Excel, Access and MySQL. It is actually quite simple to write a script to export the data as .csv. Here's an example of how the club_comp.dat file looks when exported to CSV:

The next essential part is to be able to convert the .csv file back into the .dat file. There's no point working on an Access database, etc to manipulate the data if you can't reimport it back into EHM's database. This took a little longer to figure out but once I tried a new method, I have apparently now achieved this. I still need to test it further, but I've been able to convert index.dat (I chose this file because it holds a small amount of data and is therefore a little easier to experiment with) to a .csv file and then reimport the .csv back into the .dat file. I've tested it in EHM and it seemed to work fine.

The reason I want to test it more is because my script handles blank bytes differently when converting back to .dat. E.g. if you have a field within the database that has a length of 51 characters (=51 bytes) and you enter text into it that is less than 51 characters long, the remaining bytes in the field are stored as blanks. The blank bytes outputted by my script seem to be different to those that are normally in the .dat files. I still haven't determined whether this is an issue, but my initial test worked flawlessly. I'm going to do a little more testing before I move on to the history data editor itself.

Once I've done some more work, I'll try and find time to post an update. I can't promise to update this blog on a regular basis. Just like Dabo's news updates, it's better to spend more time working on the project itself rather than spending lots of time posting regular posts. I'll actually be briefly pausing on this project to create a tool for bobmcgoo which will adjust en masse a number of attributes in the hope this may bring the NHL scoring to a more realistic level (the graphs bobmcgoo has shown me from his manual editing thus far show an extremely impressive improvement).

Next time, I'll post some info about my work on bobmacgoo's tool and also about which type of database I'll use for the history editing.

If anybody has any suggestions/questions/comments then please feel free to contact me via the Editing & Game Addons Forum.

One final point: Although it is my aim to produce some form of data editor, I'm not going to promise one at this stage. This blog is as much about my investigation into the viability of a multi-user data editor as it is about actually creating one. If it is viable then I will do my best. Otherwise there's no point as I'll simply be duplicating what the Pre Game Editor can already do.

Originally posted 13 November 2011:

Nino33 recently asked if it was possible to remove all draft rights from the database without having to go through each player individually in the Pre Game Editor. It turns out that this is possible and it's rather simple to do:

1) Delete/backup drafted_players.dat (this is the file that stores the details of each draft pick).
2) Create a new blank text file (e.g. right-click on your desktop -> New -> Text Document. Name the file drafted_players.dat and put it in your database folder.
3) Update the relevant section index.dat file that relates to drafted_players.dat to reflect that there are zero records.

I've tried this using the latest version of Nino33's 1974 rosters and, as hoped, there are no draft rights recorded in game:

Additionally, when looking at the players eligible for the next draft, there are nearly 11,000 players listed (compared with approx 5,000 in an early version of Manimal v4.0):

I'm currently running a one year sim to make sure all is working okay in the game following the change. Another thing worth checking is whether the Pre Game Editor is happy with zero records in the drafted_players.dat. I would hope it would be, but this is all new territory for me and is worth double-checking.

Whilst I think of it, it might be helpful if I explain a little about index.dat as this is a file I'll be regularly using throughout my work on the database. The purpose of index.dat is to keep track of the number of records stored in each of the .dat files. Whenever any .dat files are modified (i.e. records are added or removed), the index.dat must be updated otherwise you may well end up with difficulties in game (e.g. new data might not appear or you may get errors/crashes). Thus index.dat is going to be used a great deal during my work on the database.

UPDATE: Following the one year sim, everything worked fine with the removal of all the draft data.

Post by **archibalduk** » Sun Nov 13, 2011 4:04 pm

I mentioned in my first post that I'd say a bit more about bobmcgoo's tool and which type of database I'll be using for the history editing. Here it is:

Mass Attribute Editor
I've finished writing the Attribute Editor requested by bobmcgoo and it sounds like it doesn't everything he wanted. The Editor allows him to adjust a variety of attributes by either a value increase/decrease or by a percentage. Filters can be created so that the changes can be applied to players within a certain range of CA and/or PA. Also, different adjustments can be applied according to the players position. All of these settings are controlled via an Excel csv file:

I don't intend on adding this editor to the TBL Downloads Section at the moment. However, if it is of any use to anybody then I'll happy send you the download link. Additional attributes can easily be added to the editor. Perhaps at some point in the future I'll add all of the other player attributes to the editor and at that point I think I'd upload it to the Downloads Section.

Which database to use?
Before working any further on the History Editor, I need to figure out what sort of database I'm going to use. Possible options I've considered are:

Flat file database (e.g. Excel);
SQL database hosted online;
Microsoft Access / LibreOffice Base.

1) Flat file database
I've only really included this in the list for the sake of completeness.

Based upon my fairly limited understanding of databases, EHM uses a relational database. This is where the database is made up of a number of tables and they are all joined together via relationships.

The difficulty with a flat file database (a Microsoft Excel spreadsheet is an example of a flat file database) is that it doesn't really allow for relationships to be created and managed. If you export one of the .dat files to Excel then you'll end up with a spreadsheet of lots of seemingly meaningless numbers. Relational database software such as MS Access is needed in order to link up these various numbers (such as ClubID, LeagueID, StaffID, etc) and display the information in a way that can be easily understood.

2) SQL database hosted online

In an ideal world, an SQL database online would be my preferred solution. The reason being that I could import the EHM data into TBL's server database and then allow users to edit the data via their web browser. It would be a great way of enabling multiple users to edit the database at the same time. I would fully integrate it into the current site and enable permissions so that only identified users could access and modify the data - I could even allow different levels of permissions to different users.

However there are some catches that reduces the viability of this solution:

Firstly, there's the bandwidth usage involved. The EHM database is usually roughly 30mb in size and so there is the potential for a lot of data to be frequently downloaded by users as they view the data online. For example, if a user was working on updating a particular league, they would need a page that downloads and displays all of the player and team data for the whole league. They would probably visit this page numerous times over the course of their work updating and so it'll inevitably involve a great deal of data being downloaded from the site. As a site, I try to keep bandwidth down as far as possible (in order to keep running costs down and to help site stability). The amount of additional bandwidth that would potentially be involved makes this solution seem much less attractive.

Secondly, there's the complexity of coding the web pages (I'd be using php and html) to display and modify the data. Setting up the relationships between data tables would be complex and I'm sure writing the various SQL queries would be complex too.

Thirdy, the editor could only be used with the Manimal rosters. There simply wouldn't be the server space or bandwidth to host other roster projects. Also, the import and export of the data would have to be done myself via the server's backend software. The whole system would have been written specifically for the customised version of the phpBB forum software we use; it wouldn't easily be ported to other websites.

3) Microsoft Access / LibreOffice Base

I think this is the best way forward for any data editor I create. I've used MS Access quite a bit in the past and find that it is very easy to create relationships, queries, etc (well, it's what Access is designed for after all!

). Access does a lot of the work for you and has some useful import and export functions too. I think this will be the most straightforward solution - and one that others will find accessible for their own roster projects (whereas the SQL method could only be used for the Manimal rosters).

There are a couple of drawbacks that spring to mind however. Firstly, not everybody has MS Access (I only have it because I get staff discount on MS Office Professional). Thus it'll need to be compatible with LibreOffice Base (a freeware equivalent of MS Access). I've never used Base before and I've no idea how easy it'll be to make something that will be compatible with the both of them. Secondly, it's not as multi-user friendly as an online database would be (such as SQL above). However, I think we should be able to get some basic multi-user editing functions out of it.

Access does allow for online sharing of the database (by using front-end forms and a back-end SQL/Access database) which would allow the same level of multi-user editing as a web-based editor. However, the same issues arise with regards to server space and bandwidth. I'm not all that sure about how secure it is, either.

Thus Access/Base looks like the best of the three listed above. I hope to start work on the database next week. In the mean time, if anybody has a better suggestion with regards to what sort of database to use then drop me a PM.

Post by **archibalduk** » Sun Nov 13, 2011 5:27 pm

I wasn't really planning on posting in here quite so regularly, but I thought I'd share my latest progress update:

As mentioned in my first post, I've written a script that can export .dat files to .csv and then back to .dat. I've been adapting this script for my history editing project. The script exports the staff history to .csv and then reimports it to the .dat file and updates the index.dat. Rather than jumping in head first, I thought I'd add some post 2005/06 history for one player (I chose Tuuka Rask) using Excel and then import it back into EHM.

So here's what it looks like in Excel:

The first column in the screenshot above is the unique HistoryID identifier (each record in the table must have a unique identifier number). For the test data I added for Rask, I entered "1" for each HistoryID. This is because the script automatically generates and corrects the HistoryID when it converts the .csv back to .dat. This will be useful if lots of users have completed their own research in different spreadsheets/Access databases - you just paste the data into one spreadsheet and let the script do the rest (i.e. you don't have to worry about correcting the 19,000+ HistoryIDs).

And here's what happens when I use the script to import into EHM (as you'll see, I just entered some random numbers into Rask's history for the sake of testing):

In other words, it seems to work perfectly!

So the next step is to work on the Access/LibreOffice database itself and then I will finalise the import/export script. Once this is done, I/we can get to work on collating the post 2005/06 player histories.

Post by **archibalduk** » Sat Nov 19, 2011 5:57 pm

As I mentioned previously, one of my top priorities for developing the History Editor is that it has to work on anybody's computer (using Windows) without the need for purchasing any software. Hence I was looking at LibreOffice (because it's freeware). Unfortunately it turns out the LibreOffice Base isn't really suitable for my purposes. It's all a bit awkward to use and the import functions don't work correctly (importing and exporting functions are essential for the Editor). In fact, it appears as though Base isn't actively developed at the moment. Thus it was a dead-end and means I have no choice but to use Microsoft Office.

Thankfully, I've found a much better solution; Microsoft has produced an viewer for Access 2010 files. The Access 2010 Runtime can be downloaded for free and allows you to view and edit data in Access files. It doesn't have any of the functions that Access has; instead you have to programme these yourself using MS Access 2010. So it'll involve a little more work on my part when designing the Editor (e.g. I'll have to design a full user interface and write my own import/export functions). This is probably a good thing because it forces me to integrate all of the functions as buttons in my database rather than just leaving users to navigate Access' somewhat intimidating menus.

In a nut-shell, if you don't have Access 2007 (or newer) then you can download the free runtime in order to use my History Editor.

Progress on the Editor has been slow because it's been seven or eight years since I've used Access. Also, I'm no Visual Basic programmer (Visual Basic is needed in order to programme the functions). However, I have made a decent amount of progress and have almost finished the basic career history editing features. Here are two screenshots of the early design (note that the layout is not complete yet and will be tidied up before being finalised):

1. This is the player/staff select screen where you find which player you want to edit:

2. This is the editing screen where you can add/remove/edit career history for the selected player:

Post by **archibalduk** » Sun Nov 20, 2011 6:24 pm

I'm now making really good progress on the History Editor and have completed almost all of the main features. Data can be very easily imported and exported from the Editor. In order to set up the editor to be shared with other users for multi-user editing (or just to use on your own) you simply click on the "Import Basic Data" button (this imports some csv files which my C++ script exports from the EHM database) and it's ready to go. If you want to edit the existing career history then you can also import this with the use of a different button.

Once users have completed adding new histories, they click on the "Export data" button and this can then by shared with the Head Researcher. The Head Researcher can either import it straight into EHM using my C++ script or they can first import it into their own copy of the History Editor (thereby using their copy of the Editor as the master database).

All that remains in terms of career history editing is to tidy up the user interface and to generally make everything as user-friendly as I can. An example of how things have already been made user-friendly is that when you add a new entry into the career history for a player, the Team and League fields automatically default to the player's current team and league (this can then be changed by the user if necessary). You can see this in the second screenshot in my previous post.

Once I've finalised it all, I'll move on to the competition history editing functions. Hopefully this will all be done in advance of Manimal v4.0 being released (so that we can get started on updating the histories as soon as the rosters are released). I'll then work on the C++ side of things - i.e. finalising the import/export tool that will transfer the data to/from Access (csv files) from/to EHM (and update the index.dat file).

Post by **archibalduk** » Sun Nov 20, 2011 8:37 pm

The career history editing functions are now complete!

Time to get to work on the league history section...

Unfortunately I'm running into a known crash in Access 2010 Service Pack 1. The database can crash when running Visual Basic code (such as clicking on buttons). Thankfully there is a work-around (by creating a short-cut to the database and using the /decompile switch) and Microsoft has recently released a "hotfix" which can either be downloaded individually or, alternatively, will feature in a future Windows Update (apparently it will appear in Office 2010 Service Pack 2).

Update: It seems the current hotfix has mixed reviews. Perhaps the /decompile switch is the best solution for the time being.

Post by **archibalduk** » Sat Nov 26, 2011 7:24 pm

Firstly, a BIG THANK YOU to Nino33 and Kabbott50 who kindly helped testing an early version of the Editor.

Thanks to their help I was able to ensure that the Editor will work on other users' computers. There was a worrying day or two at the beginning of this week because it wouldn't load on their computers. It eventually transpired that the issue I'd described in my previous post was the culprit. Now that I've installed the hotfix and recompiled the database, it works on others' computers. We also found out that users will need Access 2010 (with Service Pack 1) in order to run the Editor (Access 2007 isn't sufficient). If you don't have Access 2010 then you can use the Runtime with the Service Pack 1. So everybody will be able to use the Editor one way or the other.

I've now completed the basic Editor. It can import staff career histories, club competition histories and international competition histories. Once in the Editor, you can add/edit/delete as you wish. These can then been exported. Once exported, they can either be sent to another user (so you can have several users editing the histories and then merge them together) or imported into EHM's database.

There just remain a few tasks:

1) I need to finalise the way in which data is exported. According to the database structure info posted by SI, the data needs to be in a particular order. I need to ensure that the data is exported and grouped in the correct order.

2) I need to complete the utility which imports/exports the data between the database and the Editor / CSV files. I've completed most of this.

3) Finally, I need to decide what to do with player_info.dat. This file stores each players' total club and league stats (I've posted below a screenshot from the Pre Game Editor of the stats I'm talking about). Laz has kindly explained that this data is used for calculating RFA status and also milestones / club records etc. It's hard to tell, but it looks like this data hasn't really been updated in previous roster updates. I thought perhaps I could write something to calculate these stats based upon the staff histories. However, I don't think it's that simple because, if I understand correctly (if I don't understand it correctly please post in this thread to correct me) we're looking at the player's stats for his current contracted team and the league he's currently playing it. It might be beyond my novice programming skills to write a script to calculate this (because a player can play for multiple teams per season). I'll need to have a think - perhaps I could use the club contracted data from staff.dat.

Seeing as the player_info.dat hasn't been updated in the past, I don't see a problem initially skipping this for our planned history updating project (again, correct me if I'm wrong). If I can't figure out a script then perhaps we can revisit this once we've updated the histories.

Here's the screenshot from the Pre Game Editor which shows what the player_info.dat data stores:

Here are a couple of screenshots of how the Editor is currently looking. This will give you a better idea of how it works and what it does:

Post by **archibalduk** » Sun Nov 27, 2011 8:28 am

I thought I'd post a good question I received today from AdamOates (and my reply):

AdamOates wrote:Since you are creating a way to have multiple people edit the player histories, do you think that we will eventually be able to do the same thing with roster updating?

I would really like to create something similar for roster updating, but I'm really not sure whether it would be practical. The history editing is quite straightforward because the data is stored in just a single file (I mean one file for career histories, one for club competition histories and one for national competition histories). The player/staff data is spread over many files and also almost every single file relates somehow to players/staff. So if you add/delete a player then you have to update virtually every single file in the database. So it would be a big, complex task.

Having said that, it would be more straightforward to implement an editor that would allow multiple users to edit player attributes and contract details. You wouldn't be able to add/delete players, but you could at least update the existing players. As with the History Editor, you wouldn't be able to delete any players/teams/competitions using the Pre Game Editor until the data from the Access database is imported back into the main database. I guess it depends on whether the benefits of multiple users editing the database like this would outweigh the disadvantage of not being able to delete players/teams/competitions.

I think (and I will be checking this shortly) that it will be okay to continue adding players/teams/competitions in the Pre Game Editor whilst other users work on the Access Editor.

What do you think of that?

Post by **archibalduk** » Thu Dec 01, 2011 9:40 pm

One of the pains with updating/editing the database outside of the Pre Game Editor is the need to update the index.dat file each time. Index.dat keeps track of how many records are stored in each of the .dat files. So if you add or remove records, you have to update the appropriate part of index.dat.

I've been working on a simple script to update index.dat automatically. So you can make your changes to the .dat files and then just run the script. I'm going to give it a full test this weekend and, assuming all works well, I'd hope to have it uploaded this weekend or early next week.

I'll be adding this script to the History Editor so that it will update index.dat when you export the histories back into EHM.

Post by **archibalduk** » Sun Dec 04, 2011 2:18 pm

Just a very quick progress report on the History Extractor (this is the tool that transfers the data to and from the History Editor and the EHM database):

The basic functions of the Extractor are all now complete. It will export the relevant data from the EHM database for use with the Editor and can then import the data from the Editor back into the EHM database. It also renumbers the records correctly and updates Index.dat - so everything is done automatically for you now (there's no need to do any additional hex editing etc). I just need to tidy up the script code and then it'll be ready for when we get started on updating the histories.

Here's a screenshot following importing Lazion's updated histories using the Extractor:

Post by **archibalduk** » Tue Dec 13, 2011 10:06 pm

In order to make it easier to maintain two versions of future Manimal Roster updates (i.e. one version for use with the CSD Patch and one for without the Patch) I've been working on a converter to do everything for us.

Another more important reason for the need for a converter is in order to ensure that everybody participating in the Challenges are playing on a level playing field. The patched rosters and the non-patched 2006 rosters are different in certain ways - for example the post 2006 drafts have to be stored as additional rounds in the 2006 draft whereas there isn't such a problem for the patched rosters. Also, it only seems right that both sets of rosters are identical in the interests of fairness.

Today I've finished the first version of the converter and have been giving it a test to check everything works as hoped - and it does! I've posted a couple of screenshots of how the rosters look using the standard 2006 rosters (the screenshots are from the end of the first season in game). As you'll see from the screenshots, by pushing all the dates back by five years it will allow those unable to use the patch to benefit from the post 2005/06 career and competition histories.

All that remains is to make the converter update the extra_config.cfg file. This is a little more complex but is nonetheless perfectly feasible.

Hopefully this will ensure that even those unable to use the Patch can continue to enjoy the Manimal v4.0+ Rosters and the planned 2012/12 UUDB Rosters.

Player career histories pushed back five years - so the real life 2010/11 histories can appear as 2005/06 histories in game

Competition histories pushed back five years - so the real life 2010/11 histories can appear as 2005/06 histories in game

Draft histories pushed back five years - so the real life 2011 draft can appear as 2006 draft in game

Post by **archibalduk** » Wed Dec 14, 2011 9:59 pm

Firstly, season's greetings to anybody who bothers to read my rather long and dull blog posts!

Here's what I hope to be working on over the coming weeks/months (listed in no particular order). Some of these projects (e.g. the History Converter and Staff Editor) will be more complex tasks than what I've worked on thus far. Obviously there are no guarantees as of yet as to whether I will be successful with all of these:

History Updating
With the generous help of large number of users, we're well under way in researching the 2006/07+ histories for many of the playable teams in the game. Once Manimal gives the okay, we'll start adding our research to the History Editor and then I'll collate it all and import it into the database. Competition histories have also been updated (thanks to a great deal of help from Lazion).

History Editor
Once the histories have been updated, I plan on adding some additional features to the Editor such as editing the national team rankings (which I presume/hope will allow for a change in the WC rankings).

History Converter
Some of the users working on the UUDB have kindly been helping in researching the career histories. In return I will be providing them with a spreadsheet of all of the 2006/07+ histories so they can start updating the histories in the UUDB. I hope to take this further by writing a converter so that the staff.dat file in the Manimal Rosters can be converted for use with the UUDB Rosters. Seeing as both projects are aiming to achieve similar things, it would be great if we could work together as it would halve the work for everybody. The converter would also mean that we could continue researching the histories whilst Manimal & Co continue to work on the database using the Pre Game Editor. Currently, in the absence of a converter, it isn't possible to add/delete/rename players/competitions/teams whilst the histories are being edited. The converter will resolve this.

Player/Staff Editor / Multi-User Editor
Assuming I am able to successfully write a History Converter, I will be able to adapt this work (and the work I've been doing on the History Editor) in order to create a Multi-User Editor. This would allow lots of users to edit the player/staff data all at the same time. The users will then be able to send their changes to Manimal / Alessandro who will then be able to import these changes into the database. Users would be able to edit things such as contract details and attributes for all players/staff existing in the database.

Currently the researchers for Manimal project have to list all of their research in a spreadsheet and then email it to Manimal. Manimal then has to manually enter all of this data in the Pre Game Editor. My planned Multi-User Editor will allow the users to make the changes themselves and these can be quickly and easily imported into the database. This would make the updating process a lot quicker and would allow more of the database to be updated each season. Assuming of course that Manimal / Alessandro would be interested in using my tool!

The MU Editor would allow a user to continue editing the database like normal in the Pre Game Editor whilst other users use the MU Editor. The MU Editor would import the data by matching up the player's name and date of birth. Assuming there are no players/staff in the game with the same name and date of birth then it'll work fine...

This would also allow data/research to be shared between projects, should the leaders of such projects wish to do so. For example, the UUDB guys could research some leagues whilst the Manimal guys work on others. All of this data could then be imported to both databases - assuming of course that player names are spelled exactly the same (this is probably only a difficulty for Cyrillic names, e.g. Russian names, as these can be spelled in more than one way using the English alphabet).

Player Info Updater
Once the player histories have been updated, I would hope to write something that regenerates the Player Info based upon the histories. This is probably only of use to the North American leagues, but it would be helpful nonetheless to have all of this up to date without having to do it manually.

Memory Patch
I've half-written an extension to the CSD Patch which allows custom changes to be made to the game (e.g. so users can make their own league rules/structure patches). I need to fix a bug in it and then I'll be able to upload it to the site.

CSD -> 2006 Database Convertor
As mentioned in my previous post, I've been working on a converter which is 99% complete. All I need to do now is get it to update the extra_config.cfg file (and ideally the records config file too).

Post by **archibalduk** » Thu Dec 29, 2011 11:03 am

PLEASE NOTE THAT THE INSTRUCTIONS POSTED BELOW ONLY APPLY TO THE MANIMAL V4.0 HISTORY UPDATING PROJECT. They could be used for other projects but you would first need to generate the correct team and player lists (I will add this feature to a future version of the Editor before public release).

The History Updating Project continues at full steam ahead with roughly 17% of the NHL complete along with the entire Swedish Elitserien (thanks to Reave's excellent work), a couple of Russian, AHL and ECHL teams and all competition histories. Hopefully once we are all back from the holidays, we will have even more teams complete! As I have said many times before, the response from the community has been fantastic - I never imagined so many people would volunteer.

Generally speaking, the History Editor has been a success - both for the History Project and also for Nino33's 1974 DB Project (if you don't know about this project then you really should check out this thread - the work Nino is putting in is absolutely phenomenal). In an ideal world, I would have written a dedicated application rather than using Access but this is beyond my skills and, even if I had the knowledge, would have taken a great deal longer to write and design. My approach does have its drawbacks (i.e. you have to install the rather large Runtime package) and frustratingly it has not worked on everybody's computer (although judging from the feedback I have received thus far, it seems to be working for roughly 85-90% of users).

For those users unable to use the Editor, there is an alternative. In fact, even for those that can use the Editor, the alternative method may be more attractive. The alternative method is to use an Excel spreadsheet rather than Access. On the plus side, I find it quicker (and I personally use this method rather than the Editor for data entry). On the down side, it does leave more margin for error than the Editor (and I have first hand experience of the mistakes that can be made with Excel when completing my Winnipeg research - and this took a great deal of work to fix - but I have since updated the spreadsheet template I use which should prevent the mistake I made from happening again) - so you have to be very careful when entering.

It might sound strange that I, the author of the Editor, prefer to use Excel to enter data rather than my own Editor! However, the Editor does a lot more than just data entry - most importantly it manages and processes the data so that I can easily import other researchers' data, check it and correctly format and export it for use in game. The Editor is a significantly more reliable method of entering data but, for those unable to use the Editor or for those who are confident with raw data (like myself), Excel is a very viable alternative.

I posted the instructions recently in this thread but I thought I'd re-post them here because it will easily get lost in the original thread:

archibalduk wrote:As one or two users are having difficulty running the Editor, I thought I would share an alternative way of editing the data without using the Editor. I must point out that this method makes it easier to make mistakes - it is essential that you check the ID numbers carefully as you enter the data.

If you have already put your research into a spreadsheet then you might find the following method quicker than using the Editor. But please note my warning about being careful when entering the data. I personally use this method rather than the Editor because I find it quicker and easier.

Although it looks like there is a lot of text below, it is very simple to use the spreadsheet. As usual, I just wanted to make sure everything was explained as clearly as I could.

You will need to download History Spreadsheets - Manimal v4.1.zip from here. Extract the files somewhere on your computer.

Here is how to use the spreadsheets included with the zip file:

1) Open Player_list.xls. The column headings have small arrows next to them which allow you to filter the list. Click on the drop-down arrow next to Team Name and select the team you are researching. If you are using Excel 2007/2010 then click on the 'Select all' option from the menu to deselect all the teams and then select the team you want by putting a tick next to it. If you struggle to use the filter then you can simply scroll down the window until you find your team they're in alphabetical order.

You should research career histories for all of the players listed for the team you select.

2) Open Career_history.xls. This is the spreadsheet you should enter your stats into. Ignore the TeamID and CompetitionID columns for the time being (we will deal with these later below).

Enter each new row of career history as a new row in the spreadsheet.

It doesn't matter what you enter for the Player Name and Team Name columns. These are just for your reference and are not used in the game. You could just enter the players' surnames, for example. For the team names, you could write anything you want (as well as the league name if your really want). The purpose of these two columns is just so that we can easily see what each career history refers to.

For the Playoffs and On Loan columns you should enter a 0 or a 1. You will see that I have explained above each column title what a 0 and 1 means.

You must enter a stat for each column in the spreadsheet - even if it is just a zero. For example, for all skaters you should enter a zero for each goalie stat column. There cannot be any blanks in the spreadsheet.

For the StaffID column you should enter the player's StaffID number. You can find this number from Player_list.xls. You should enter the StaffID number that corresponds to that player. If you enter the wrong StaffID then the career stats will appear for the wrong player in game.

3) Once you have entered all of the career stats, you should just have the TeamID and CompID columns to complete. To make it as quick and easy to complete as possible, you can sort the data by Team Name (so you can group the rows together by team). To do this, click on the drop-down arrow in the Team Name column and click on 'Sort A-Z' or 'Sort Ascending' depending on what version of Excel you have.

Open Competition_list.xls and Team_list.xls. For each team listed in your career history, you need to lookup the corresponding TeamID and CompID numbers. Then enter these numbers into your spreadsheet.

For example, for the Atlanta Thrashers, you will see that their TeamID is 411 and the TeamID of the NHL is 0. So these would be the ID numbers you would need to add if you were adding a row of history for a season at the Thrashers.

Note that the Memorial Cup has its own seperate CompID (33) to the OHL (7), QMJHL (8) and WHL (6). For other playoff competitions, you simply enter the ID for the league (e.g. NHL = 0) and then enter '1' in the Playoffs column.

As with the StaffID, if you accidentally enter the wrong TeamID or CompID then the career history will display the wrong team or competition in game. Hence why it is so important to double-check that you are entering the correct number. It won't cause the game to crash however - it's just that the data will be wrong in game.

4) Once you are done, email the Career_history.xls file to me (archibalduk AT gmail DOT com). Put in your email a list of which rosters you have researched and what your TBL username is.

Finally, here is an example of how the completed spreadsheet should look like:

Post by **archibalduk** » Sat Mar 03, 2012 12:52 pm

Heh it seems this is my first post for just over two months! As has been well documented on the forums, this has been because I have been busy working with a small group of users to update the player career histories. I won't say much about it here because it has already been discussed at length in the forums, but overall the project and the History Editor was a success*. I didn't imagine there would be so many volunteers offering to help out and was amazed how many leagues we managed to complete.

I've decided not to release the History Editor publicly quite yet. Following on from our history researching, there are a few minor bugs/glitches that I want to first iron out (such as being able to add _BLANK to the competition field in the staff histories as well as the ability to add international histories). There are some additional areas of histories/records that I'd like to add to the History Editor, such as editing team records. I have actually written something that allows the team records to be edited in Excel and reimported into the database (I have used it to update some of the NHL team records for the upcoming Manimal v4.1 and Nino has been using it for his 1974 database) but I think it would make things easier if it could also be done centrally within the History Editor.

*The only really big issue that appeared with the History Editor is the Access database's compatibility with some users' systems. This is really frustrating because there is not much I can do about it but thankfully it didn't affect very many users and I was able to offer an alternative method using Excel (see my previous blog post above).

Anyway, I've decided to put the History Editor on hold for the moment because there are some other tasks I would like to complete (and are of higher priority IMO). Here's what I've been doing:

Nations Updater
As I mentioned here, I've been taking a look at the various IIHF Ranking Score data in the database. It seems much of the data isn't used in game, but it is possible to update the IIHF Ranking Scores. Unfortunately the teams playing in the first season of the international competitions appear to be hardcoded and so updating the IIHF Scores does not change the team selection. However, it does influence which teams compete from the second season onwards.

Once I have made the user interface tidier (well, it's only going to be a command prompt window, but it's not very user friendly at the moment), I'll upload the tool to TBL. It will be possible for users to create their own nations updates in Excel and share these with other users to import into their own databases. For example, I will upload a file with the 2011 rankings and this could be added to any EHM 2007 database using my tool.

Converters
In order to allow staff career histories to be shared with any EHM database and to allow users to edit the database whilst the histories are updated, I need to write a converter to update the ID numbers for the players, teams and competitions (because the ID numbers can change slightly each time you edit a database). A converter is also going to be a key part for any multi-user editor. I posted a bit about my idea for a multi-user editor here, but in essence the idea is to allow users to list all of the contract and attribute changes in Excel and these can then be imported into any database (thereby allowing users to share their research with all the difference database updating projects such as Manimal and UUDB - should they choose to do so).

I've made a small start on figuring out the converters, but not much progress has been made yet (I've had a crazy busy past four weeks at work and have lacked both energy and time).

Player Info Updater
I started work on this last night and this morning and have made a great deal of progress on this. My aim is to create a tool that will automatically update the player_info.dat file based upon the player career histories. Updating all of this data manually is far too large a task and so I'm writing a tool that can update it in a matter of seconds.

The data I'm talking about can be found on the Player Info tab in the Pre Game Editor:

So far, my tool will calculate all of the Club Stats and League Stats sections (the lower two sections in the screenshot above - i.e. GP, G, A, PIM, Wins, SO) and also the NHL Games number in the Professional Games section. I haven't yet added the ability to update the player_info.dat file itself (but this will be really easy to add) because I'm still in the testing stages. At the moment, it calculates the total for each player and saves this in an Excel spreadsheet (this allows me to check that the tool is working correctly etc). Once I have finished all of the calculation functions, I'll add the ability to update the player_info.dat file itself.

The most challenging part of this project is trying to figure out what exactly each of the fields covers. I'm looking at an old database which only has the pre-2006/07 histories so that I can how SI calculated each of the fields. Having looked through the database, I can see that the Club and League stats and the NHL Games stat only cover regular season games and so this is how I have programmed my tool to calculate these.

The remaining issues I need to investigate are as follows:

NHL Seasons - Does this only count regular seasons? E.g. if a player only plays in the playoffs in a season (i.e. he doesn't play any regular season NHL games), is this counted as a season?
Professional Games Played - I need to make a list of every league in the game that is regarded as a professional league
Canadian Junior Games - Which leagues does this cover? I know it covers the BCHL, OHL, QMJHL and WHL, but are there any others? I know for a fact it doesn't cover the Memorial Cup.
Played in North America as a Junior - Which leagues does this cover?
Has played in College - Which leagues does this cover? Presumably this covers the NCAA leagues (e.g. Hockey East, CCHA, WCHA, etc) but are there other college leagues in the US and does it also cover any Canadian college leagues?
North American Major Junior Games - Which leagues does this cover?
With regards to all of the above, does it cover just regular season or does it also include playoffs?

If anybody has any suggestions to make with regards to the above list, or if you would like to help figure this out, please let me know here. It would be great to have some help on this!

Post by **archibalduk** » Thu Mar 22, 2012 10:09 pm

Now that the Player Info Generator is complete and I've finished making some updates to the Attribute Editor for bobmcgoo's EHM Realism Patch (pending feedback / further requests from bobmcgoo), I'm working on the converters.

The word "converter" is probably a bit of a loose term, but what I mean is that data (such as contract details, attributes and histories) can be added to any database without the need to worry about player ID's, etc. The first tool I've been working on is a staff contract updater. The idea is that users can enter updated contract details in a spreadsheet and these can then be imported into any database. Here's an example of how the spreadsheet looks:

And here's proof that an early version of my tool actually works (i.e. it loads the spreadsheet and updates the database accordingly):

It's still rough around the edges and there is still some work to be done, but the main code is up and running very well indeed. Bits I need to add to the tool include:

Update the 'club size' field in club.dat. This seems to keep track of the number of players on the roster of each team - or it might be the number of players contracted. I need to check this out. Edit: It seems I'm mistaken - there isn't such a field (I got it muddled with the TEAM_SZ enum which is unrelated). This makes things much easier!
Allow users to update as many or as few of the fields as they wish.
See if it is possible to make the spreadsheet case insensitive.

The general idea behind the tool is that you can list contractual changes and/or trades in a spreadsheet and these can be imported straight into the database. The tool updates the database by matching the player's name and date of birth (hence why you have to include the player's DOB in the spreadsheet). So there's no need to worry about any ID numbers. Hopefully this will make it a lot quicker and easier to import research into the database. Ordinarily, a user has to manually enter other users' research into the database by using the Pre Game Editor. My tool will speed things up in that the research can be imported into the database without having to manually implement the changes using the Editor. It will also allow users to share research across different databases if they choose.

Once I've finished the contract update function, I'm going to look at doing something very similar with the player attributes. I.e. a spreadsheet where users can enter attribute changes for players which can then be directly imported into the database.

I think something similar could be done with the player histories rather than having to use the History Editor. I'm going to see if career history could be listed in a spreadsheet and directly imported (rather than having to copy and paste the data into the History Editor player by player). This might make the player career history research quicker and easier.

Post by **archibalduk** » Sat Mar 24, 2012 6:01 pm

I've made huge progress with regards to the Staff Updater. In fact, I think the first part of it is now complete (i.e. the Staff Contract Updater). Here's a run down of the progress:

You don't need to enter info in every cell. The Updater will only update the data you enter. Any cells left blank will be skipped (so the original data will be left intact). I've posted a little bit more about this further below.
It is case insensitive. It doesn't matter whether you enter staff/team names with upper or lower case letters (or any combination thereof). It also doesn't matter whether or not you add the 'TM' trademark symbol to the end of any team names.
As the csv file is ASCII, it cannot cope with letters with a caron. Therefore, you can enter these letters without the caron (e.g. you enter 'A' rather than 'Ǎ'). Andyjayp is going to try and resolve this, but I think it's probably easier for most people just to enter the "normal" letter anyway.
For any records that cannot be matched to a player/team, they are summarised in a spreadsheet along with details of why the player/team couldn't be matched.
There are various error checking functions in place with regards to dates. This helps prevent certain erroneous dates or if you inadvertently enter only a partial date.

I've been doing some tests in the Pre Game Editor and EHM, and it seems the Updater works perfectly. I want to carry out the usual single season sim to check for any other issues (but I don't expect there will be - I think a crash/error during the game start up would have been more likely had there been any issues). Surprisingly, the updating process is very fast. I've only been using a very small number of players (roughly 8 or 9) but the Updater loads and processes instantaneously. I would have thought that the comparison/conversion process would take some time, but clearly (and happily) not.

As I mentioned in my first bullet point, you don't have to enter data in all of the cells. The Updater will just update the data you enter. Here's an example:

So in the example above, Getzlaf will be moved to Boston Bruins (and will be placed on the Providence roster) but his Date Joined, Contract Expiry Date, Wages and Value will remain as they already are in the database. For Mark Olver and Devin Setoguchi, the only aspect of their contract details that is updated are their Contract Expiry Dates. For Brad Winchester, the only aspect updated will be his Wages.

Hopefully the above example shows the way in which things could be updated. For a researcher researching a league, they can list all of the roster moves and contract changes in the spreadsheet. This spreadsheet can then be emailed to Manimal/Alessandro/etc and he can instantly import the data using the Updater (and of course he can first have a look through the spreadsheet to make sure he is happy with everything). This will save so much time - there will no longer be any need to manually make the researched changes using the Pre-Game Editor.

Now that the contract updating aspect is complete, I'll now be looking at an attribute updating function and also some additional features to help make things as easy as possible.

EDIT: Just to clarify that players can also be set to UFA using this tool.

Post by **archibalduk** » Sun Mar 25, 2012 10:54 am

In order to make things a little easier when using the Staff Updater, I've added a function to create reference spreadsheets listing every player and non-player in the database. There are three main reasons behind my idea:

It is a quick and easy way of looking up a player's date of birth and spelling of the name (this will help ensure that you don't make any mistakes when completing the Updater spreadsheet).
If you want to update the contract details of an entire team roster or league, you can filter through the reference spreadsheet accordingly. You could do a similar thing with the attribute updater (my next task) - e.g. if you want to update attributes for all players in a particular team/league then you can filter through the reference spreadsheet so that you have a pre-made list to work from.
The names and date of birth data can be copied and pasted into the contract update / attribute update spreadsheets, making the updating process quicker (and ensuring you don't miss any players on a roster/league).

The Updater generates two spreadsheets. The first spreadsheet lists every player (i.e. players and player/coaches) and the second spreadsheet lists all of those who are non-players (e.g. coaches, GMs, presidents, etc). Here's how they look:

PLAYERS SPREADSHEET:

NON-PLAYERS SPREADSHEET:

Post by **archibalduk** » Sun Mar 25, 2012 6:15 pm

archibalduk wrote:Update the 'club size' field in club.dat. This seems to keep track of the number of players on the roster of each team - or it might be the number of players contracted. I need to check this out. Edit: It seems I'm mistaken - there isn't such a field (I got it muddled with the TEAM_SZ enum which is unrelated). This makes things much easier!

Heh it seems I was right the first time around (well, almost)!

The database does keep track of the squad sizes after all. However, it's not quite as straightforward as that. The database doesn't just count the number of players on each roster, it tracks the Staff ID number of every single player and staff member for each team. So for each team, there is a list in the database of which staff are contracted as the chairman, coaches, GMs, Assistant GMs, players, etc (as well as team captains and the two assistant/alternate captains - I don't even know if this can be edited in the Pre Game Editor!?!).

This poses a big headache for me because each time you move a player/non-player to another team (or even to an affiliate), all of the information about who is on each roster has to be updated. So this afternoon I've had some spare time to work on this. It has been driving me crazy (not because of errors/crashes, but because there's a lot to get my head around!), but I think I'm almost there. I just need to finalise the way it saves the updated player/staff lists to the database and check that each player type is accounted for (I particularly need to check staff classified as Presidents/Directors).

I think this aspect of the Updater is going to need a lot of testing. If I don't get it right then players won't appear in their team's rosters (well they appear contracted to the correct team when you view their player profile, but they don't appear on the roster screen).

Quite a lot more work is going to be needed before it is all ready for use.

Post by **archibalduk** » Sun Apr 01, 2012 7:48 pm

Thanks to Nino33, Nikki Nine and Alessandro for helping to test the Staff Updater. Their valuable feedback has helped me pick up a couple of bugs and make the Updater more flexible.

Version 0.2.2 beta can be downloaded from the Staff Updater Thread. Since version 0.2.2, I have added some further flexibility relating to how the information in the contract update spreadsheet can be organised. Rather than just having a large list, it is now possible to add blank rows and rows with headings to help break things up and categorise the changes. Here's an example of how the spreadsheet could be organised (note that you can add as many blank rows and headings as you like):

I'm now working on the next feature of the Staff Editor - updating the player attributes. Thus far I have set up the player attributes spreadsheet (the spreadsheet where you will be able to enter all of the changes you want to make) and the Updater can read and process the changes (processing = looking up the player in the database, checking for blank cells, converting text to the relevant number, ensuring attributes cannot be out of range, etc). In the same way as the contract updating spreadsheet, you will be able to organise it using blank rows and headings.

The next task is to use the loaded data to update the player attributes in the database and to add some final error/mistake checking (i.e. so that a mistake in the spreadsheet cannot make its way into the database).

At present, the Updater covers all 64 player attributes in the Player Details and Player Attributes tabs of the Pre Game Editor. I'll try and extend it to the eight attributes listed on the Staff Attributes tab too.

Interestingly, I've come across two attributes that aren't visible/editable in the Pre Game Editor: International Jersey Number and Morale. The jersey number is self explanatory, but I'm not sure what effect the morale attribute has. Perhaps it affects the player's morale at the beginning of the game. Both of these attributes will be editable using the Updater and so we'll be able to experiment to see what effect the Morale attribute has.

Hopefully I'll have a beta of the attribute updating function ready within the next 7-10 days (probably closer to ten days as I'm at the EIHL Playoff semis and final next weekend

).

Post by **archibalduk** » Wed Apr 04, 2012 10:20 pm

I'm slowly working my way through the 72 player attributes which my tool will update.

I've managed to short-cut some of it into a nice small piece of code, but other parts are getting kind of long (2,100 lines of code and counting!). Although it's time consuming, it's less error prone (and ultimately quicker for me to write) than trying to short-cut/optimise the whole lot at once (which I'll look at later). Anyway, it's proving to be a good learning curve.

The contract updating portion is now complete as far as I can tell. I've finished all of the checks to try and prevent mistakes (or at least prevent mistakes from causing any damage to the database or any crashes in the Updater).

One issue that has cropped up during testing (thanks to Manimal, Nino, Nikki Nine, Alessandro and Reave for their invaluable feedback) is that there is a regional variation in the format of csv files. For countries that use a period/full stop as a decimal point, the column separator used in csv files is a comma. However, for countries that use a comma as the decimal point (e.g. many parts of continental Europe), a semicolon is used as a separator. This causes difficulty because those in different parts of the world will be using different formats. To fix this, I have done three things:

1) The Updater detects the format of the csv updater files. E.g. if the Head Researcher (such as Manimal) receives different formats of csv updater files from other researchers, the Updater will handle the two different formats of csv automatically.

2) The Updater detects your Windows system settings and outputs csv files in the correct format (e.g. the error and list files will be output in the correct format). As I haven't released a beta with this change, I cannot confirm this works quite yet - but it works with my Windows decimal settings.

3) The Updater is able to convert the related csv files from one format to the other. E.g. if the Head Researcher receives a file from a researcher that is in the wrong format (by "wrong format" I mean the other format to that which the Head Researcher uses) then the Updater can convert it to the other format so that it can be viewed correctly in Excel on the Head Researcher's system.

So now the csv format issue is resolved (pending testing), I'll work on finalising the attribute updating function. Hopefully I'll have something ready to test shortly before or after the weekend.

Post by **archibalduk** » Sat Apr 14, 2012 10:45 am

The most recent beta version of the Staff Updater appears to be fully working without any issues!

It is now able to import both updated contract details and updated attributes. Hopefully this will help make the process of updating rosters a bit quicker.

As mentioned previously, the code behind the Staff Updater has really grown and become somewhat of a mess (because I've been concentrating on getting it working rather than making it "pretty"). I have been working on optimising things and making it as easy to understand and maintain as possible. Having recently learnt the basics about C++ classes and object-oriented programming, I am rewriting my Updater to make it more modular with the aim to make it as easy as possible to add new features. Originally I had planned to create one Staff Updater (for staff-related updating) and one Team/Competition Updater (for team records, competition history, etc). However, by following my modular approach, I now intend on creating just one updater which will do everything - I will call the EHM Updater. It will be much easier for me to maintain a single Updater than having to maintain two.

This is definitely a long-term project and there is a lot to be done: Firstly, I need to finish rewriting the exiting features. Then I will move on to each new function/module. I need to sit down and decide on what modules to add, but I know that the first module to add after the contract and attribute updating will be a player career history updating module. I will also look into creating some sort of GUI (i.e. a proper window with buttons rather than the existing text command line / DOS window). As the Updater grows, it will become somewhat overwhelming if the user interface is still a text command line because there will be so many options (a window with categorised buttons would be much easier to use). Adding a window does make things a little more complex for me (because I'm a novice) but I want to give it a shot at some point.

The EHM Updater takes me back to the first post in my blog:

archibalduk wrote:Now that we have the EHM 2007 database structure, I'm really interested in creating some form of pre-game editor. This isn't because I'm in any way unhappy with Mne2's editor, it's just that I want to see if somehow we could allow multiple users to work on the database simultaneously.

I hope that eventually the EHM Updater will achieve the target I had originally set myself - i.e. it will allow lots of users to edit/update large parts of the database simultaneously.

On to a different topic; I will also this week be looking at two additional tasks:

1) Fixing the overwritten players bug
There seems to be some sort of bug meaning that if you replace an existing player with a new player using the Pre Game Editor, they can be signed by teams without first going through the Entry Draft. There must be some artefact from the previous player in the database which makes them exempt from the usual rule. I want to hunt down this rogue data and fix it. Manimal tells me this bug is a real pain for him.

2) EHM Memory/League Patch
This is something that I was working on some time ago but I never managed to get working properly. Having acquired a better understanding of programming, I'm going to revisit this in order to try and get it working. The basic idea is that a patch file can be created in Excel/OpenOffice based upon ArtMoney (such as Alessandro's League Patch) and this can then be applied to EHM without the need for the end user to use ArtMoney. I'm aware ArtMoney isn't always successful on all versions of Windows.

Post by **archibalduk** » Sun Apr 15, 2012 10:27 am

Finding the source of the overwritten players bug: The hunt begins!

The post below doesn't really say much other than to explain what the bug is and how I'm going to go about trying to find its source. The reason I'm posting all of this is in case anybody feels I've missed something or if they have some useful information/suggestions. This task is going to be a bit of a hunt for a needle in a haystack and so if anybody knows something I don't, I'd love to hear from you!

What is the bug?
Perhaps before I explain how I'm going to go about hunting for the bug, I'll explain my understanding of what it is: When a player retires from hockey in real life (and they don't become a coach, scout, etc), they need to be deleted from the next Manimal roster update. The Pre Game Editor doesn't have a 'delete' function and so instead the players have to be either set to 'retired' or have their data replaced with a new player. The difficulty with setting players to 'retired' is that you start filling up the database with lots of players/data that have no use in the game (and they won't even appear in game AFAIK). This makes the database file size larger, makes it harder to manage (in terms of navigating through the list of players/staff in the Editor) and may well increase the risk of corruption. This is also a pain for retro and fantasy databases (such as Nino's epic 1974 database) because you cannot overwrite existing players with young undrafted players.

The bug that occurs with overwritten players is that they can be signed in game by NHL teams even though they are undrafted (as they are eligible for the NHL Entry Draft, you shouldn't be able to sign them). Ordinarily, you would encounter this message in game if you tried to sign an undrafted prospect:

However, for some reason, you don't get this message if you try to sign an undrafted prospect that was created in the Editor by overwriting a retired player. The game just lets you go ahead and sign them. Therefore there must be something in the database relating to the overwritten player that hasn't been updated/reset when the player was overwritten. For ease of reference, I'll refer to this as "an artefact from the previous player" - or simply an "artefact". The aim of my hunt is to identify this artefact.

The hunt is afoot!
The first place to start is to identify some test data so that I can start going through the database and comparing "working" players with "faulty" players (i.e. those not suffering from the bug and those that are suffering). Interestingly, I tried overwriting Niklas Lidstrom (by clearing all of his contract, draft, player info and history data and by making him 17 years old) and, when I started the game, it all worked fine (i.e he didn't suffer from the bug)!!

So instead, Manimal has given me two faulty Swedish players to go by. In addition to this, I have identified two working players to compare them with:

Faulty Players

Oliver Bohm (14/04/1992) - Almtuna
Henrik Messfeldt (31/03/1993) - Brynas J18

Working Players

Fredrik Svantesson (08/10/1993) - UFA
Jonas Wiklund (03/04/1991) - Brynas J20

Something I noticed straightaway is that both Bohm and Messfeld have NHL release clauses. However this seems to have no bearing because Henrik Lundberg (21/08/1991 - Frolunda J20) also has such a clause but is not faulty.

So the best place to start is probably to have a look at their contract and draft data in the database. I'll do this by writing a simple programme to extract the database files to the usual CSV format. Then I'll compare the two sets of players in Excel.

Post by **archibalduk** » Sun Apr 15, 2012 11:38 am

Remember Oliver Bohm, one of the players I listed as faulty in my previous post? Take a look at this following some tweaking:

It seems this was very easy to identify and fix!!

The cause of the problem relates to a field in the staff.dat file that you cannot edit in the Pre Game Editor. The field is called "Date of First Pro Contract". When you edit or overwrite a player in the Pre Game Editor, the Date of First Pro Contract does not get updated (because it is not visible anywhere in the Editor). So if you overwrite a player with a new player, he will inherit the previous player's Date of First Pro Contract. For example, Bohm had inherited the previous player's date of June 1994. When I looked at Svantesson and Wiklund, I could see that both players had their Date of First Pro Contract set as 01/02/1900. When the Pre Game Editor creates a new player, it obviously sets the date as 01/02/1900. By setting Bohm's Date as 01/02/1900, the bug was fixed.

As a quick fix to this problem, I will write a tool which will allow you to put a list of faulty/overwritten players in a CSV file. The tool will then change the Date of First Contract for each of those players to 01/02/1900. I'll also add an option to set every staff member in the database to 01/02/1900 (this might be useful for Nino). I don't really know what the Date of First Contract field does and so I don't know what side effects there will be from setting every player in the database to 01/02/1900 - but I'll add this feature as it may be of use for Nino's 1974 database.

I'll also add this function to my list of things to add for the EHM Updater. Perhaps I could calculate a player's Date of First Pro Contract by his career history (e.g. if the career history lists his first season with a pro team as 2002/03 then the Updater could set the Date of First Pro Contract as 1st Sept 2002) - this would make it easy to ensure that there are no faulty players in the database.

Post by **archibalduk** » Mon Apr 23, 2012 10:29 pm

Unfortunately progress has been slow for the past week or so. I've been really busy doing other things (sometimes I have a life

- and my everything was temporarily thrown into chaos thanks to a water leak). However, progress is starting to pick up once more (this update is more about coding rather than EHM):

1) EHM Updater & The EHM Libraries
As mentioned previously, I am working on rewriting the Staff Updater and have renamed it the EHM Updater. The plan is to make the code easier to maintain and develop.

About two weeks ago, I set about rewriting the code, but it really didn't go very well. I decided about a third of the way through the rewrite that I hadn't made the Updater anywhere near as modular as I'd hoped. However, it wasn't a total disaster as I did manage to improve/streamline some parts.

So now I've started a second rewrite. This time, it is going a heck of a lot better. Things are simpler and more efficient. This will make it much easier to add additional functions to the Updater in the future (such as history updating, etc). It is still going to be some time until I can add new features, however, because it is taking a great deal of time to rewrite (but it will ultimately save me a huge amount of time in the future). Unfortunately it is going to be later than planned before I can add new features to the Updater (or even release a public version).

Whilst writing the Updater, I've come up with the idea of creating an EHM Library. The library will be a collection of common functions that can be used with the database. I doubt this will be of any interest to anybody, but should anybody ever become interested in using C++ to modify the EHM database, it will give them a head start. The library will be an open source .cpp and .h file that can be added to any C++ project. As I write the Updater, I will develop the library and, once it is in a decent state, I'll upload it to TBL. Functions will include things such as updating the index.dat file and calculating/updating the roster size data. Thus others can just use my library rather than having to write these functions themselves.

2) Date of First Pro Contract
More bad news I'm afraid: Having done some experimentation, I'm unable to apply my fix without EHM crashing at start-up. However, I don't think my fix is the cause, but rather EHM itself. Even if I replace the modified file with the original, the game still crashes at start-up. I think EHM has somehow gotten in a muddle with databases (I've seen other users previously encounter crashes with databases with no apparent cause). I wonder perhaps if it relates to the ghost copy of files that the Windows 7 UAC operates.

Once I can solve the crashes, I'll be able to finalise the fix tool. I'm pretty confident that the fix is fine - it's just my copy of EHM.

3) The Custom Start Date Patch
I've started looking into an alternative way of creating GUI applications (applications that use a window rather than a command line interface (CLI)). Most of the applications/tools I write are CLI (i.e. just a text DOS prompt) but this probably isn't the best way of going about things for the EHM Updater. It would be much simpler to have some sort of GUI than using text-based interface because there will eventually be many options/functions.

So what does this have to do with the CSD? If I can successfully get my head around a new GUI language (such as wxWidgets) then I'll rewrite the CSD Patch using this. The drawback with the current CSD Patch is that it needs the user to install the .NET Framework and the Microsoft C++ Redistributable package. If I can use an alternative GUI then the user won't need to install these packages - it'll work out of the box. There is also a very slight possibility that this might make it possible for those running Mac/Linux (via something like Crossover, WINE or Winetricks) to use the Patch.

4) C++ Discussion Thread / Code Snippets
I promised some time back that I'd post a sample code snippet on how to import CSV files into the database. However, I put this on hold because I wanted to prioritise other projects and it seems there isn't any demand for it. However, I have been writing a snippet and I'll post it as soon as it is complete.

Archi's EHM 2007 Data Editing Blog

Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog

Re: Archi's EHM 2007 Data Editing Blog