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.
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 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.