Capital Trials at the Old Bailey

The items in the legend appear in the opposite order that the associated colour blocks appear in the actual chart, so I wanted to reverse the order of the items in the legend. http://stackoverflow.com/questions/15812217/how-to-reverse-legend-labels-in-flot-stacked-bar-chart Turns out the version of flot we're using (0.7) doesn't support that, but version 0.8 does by adding 'sorted:"reverse"' to the end of the attributes for the legend: just after the specification of the colors: . Tried installing v0.8 of the library and calling it from the chart.php page, but no chart appears, nor do any error statements. I don't have the time right now to figure this out just to get the legend items to appear in reverse order, but I'll look into it when I have some time on my hands.

Some of the colours in the output chart are difficult to distinguish from each other. In jquery.flot.min.js I found this snippet specifying colours: colors:["#edc240","#afd8f8","#cb4b4b","#4da74d","#9440ed"] which is yellow, light blue, red, green, purple. I replaced it with the following, which specifies up to ten colours before repeating (there are 9 categories at most in the display, that's for age-brackets) colors:["#FF8080","#78BEF0","#DED16F","#CC66C9","#5DBAAC","#F2A279","#7182E3","#92D169","#BF607C","#7CDDF7"] based on colours used to distinguish players on different teams in online games Then I tweaked it to this: colors:["#FF8080","#78BEF0","#DED16F","#CC66C9","#5DBAAC","#F2A279","#7182E3","#92D169","#CCCCCC","#7CDDF7"] so the 9th colour would be a light grey (only the age-groups has 9 categories and the 9th is "unknown").

Miraculously seem to have figured out how to get the age_group to be used as a sorting field in the chart display, and to get the text value rather than the id value for the age ranges to show up in the Legend. Have it working in the dev instance. Will test further and then add it to the production instance tomorrow.

classes/Search/Single.php
1) modify
array('c.criminal_id', 'c.surname', 'c.given_name', 'c.age')

to
array('c.criminal_id', 'c.surname', 'c.given_name', 'c.age', 'c.age_group_id_fk')

classes/Search.php
1) modify
public $allowedGroups = array(
'age' => 'age',
'crime' => 'crime_group_text',
'gender' => 'gender_text',
'outcome' => 'outcome_group_text',
'respite' => 'respite_normalized_text'
);

to
public $allowedGroups = array(
'crime' => 'crime_group_text',
'gender' => 'gender_text',
'outcome' => 'outcome_group_text',
'respite' => 'respite_normalized_text',
'age_group' => 'age_group_text'
);

the first argument seems to have to be 'age_group', but I don't know why
the second argument has to match a field name in the selection generated by this object (e.g. if 'age_group_text' is replaced with 'age_group_id' then I get the ids rather than the text values)

2) added to function _prepareCriminals()

$this->_select->joinLeft(
array('ag' => 'age_groups'),
'ag.age_group_id = c.age_group_id_fk',
array('ag.age_group_text')
);

this adds the age_group_text to the generated results, allowing it to be referred to in the line of code that specifies the allowed_groups.

Have modified code in dev site to read the age_group_id_fk value in (int between 0 and 7). That piece of information is only of use in the chart view, where I hope to use it instead of the raw age to group trials. Have yet to figure out: - what I really want isn't the id_fk value, but the value in the age_groups table. Not sure how to do that in the context (i.e. for use by the plotting library Jamie selected). - how to work with the library code. It's proving to be a lot more opaque than I thought it would be.

In dev instance:
Modified
- import/classes/Importer.php (importOutcomes function)
- includes/classes/Search/Single.php (to include corpse_treatment fields)
- includes/ajax/record.php (to display corpse_treatment fields)

Added two fields to the outcomes table to accommodate corpse_treatment info

Truncated all the tables that are populated from the data (crimes, criminals, trial_files, trials, outcomes, respites, mercy_appeals) and reset auto-incrementer on each to 1

Uploaded small test data file.

Once that was working, made same changes to production instance, except I uploaded all the data files into the production site rather than just one little test file.

Got list of age grouping from SD at my request. (<14, 15-19, 20-24, 25-29, 30-34, 35-9, 40-9, 50-9, 60+) They will be used in the visualization tool which currently gives every possible age a different colour value (so it's pretty well useless).
Simplest approach seems to be to add an age_group element to the XML which contains what amounts to the foreign-key-lookup value for the sql table. Then read that as a field into the criminals sql table. Add an age_groups table which relates the integer foreign-key value with the text value to display to the user. Done all that to the dev and production sites.

Next I have to modify the import code to include that age_group_id value (and a couple of other values we've added like corpse_treatment). Once I've got the database populating correctly, then add the code that queries and reports from the db to make use of that information in the visualization. Started that on dev site.

SD asked to have "Housebreaking" added to crime_normalized list. There is already an item "Housebreak" in that list and one instance of it in the data files. I asked him which form of the word he wants.

Raised larger question of whether any of the code processing the xml into sql data needed to be modified. It looks like all that code grabs values from the database, so I'll need to compare the values in the db against the values in the schema and make sure they're in sync.

SD sent me the 1750s data. I've added empty <OBPO_ref> and <judge_respite_OBPO_ref> elements to each trial for the 1730s, 1740s and 1750s. I've also updated the rng file to bailey_2013-11 which requires the presence of those elements and that their contents be either empty or a regular expression t\d{8}-\d{1,2} (for example t17380914-4). I've sent him back an xml file containing the data for all three decades for him to add the OBPO references.

SD has begun a thorough proofing of all the data files. I've created a v2 folder in the data folder to hold the revised data files from him. Once I have them all, I'll then have to clean out the relevant tables in the database and repopulate them, using the already established procedure. So far we've done a couple of decades and I've had to modify the petty-treason crime and add a couple of judges.

SD wants to add a reference to the BaileyOnline site to records in our dataset. Most likely candidates now are
the trial element (in case there is more than one trial in a trial-file and each trial links to a different reference in BaileyOnline)
the judges_respite element (the reasoning for a judges respite is often found in a different Bailey Online reference than the documentation of the original trial).

I've asked SD to confirm that for each trial or judges_respite, there is only one reference to Bailey Online. If that's right, then I'll proceed to update the data files, the rng schema, the mysql tables and the code that displays values in query results.