/procedures/meta/comments

Parliamentary procedure model.

Table and model renamed from the source schema because Rails reserves the name 'procedure'.

 3 class ParliamentaryProcedure < ActiveRecord::Base

The procedure_routes table joins parliamentary_procedures to their routes, allowing a route to appear in many procedures and a procedure to have many routes.

 6   has_many :procedure_routes

A procedure may have many routes, through the procedure_routes table.

 9   has_many :routes, :through => 'procedure_routes'

We create an association to the work packages subject to a procedure.

Many work packages may be subject to the same procedure.

 13   has_many :work_packages

Method to return an array of start steps in a procedure.

New tables have been added to the database to reflect what we plan to happen with the step collections work: these place steps into a collection of start steps for a given procedure. Until this work happens, we'll need to hardcode an array.

This method returns an array of start steps and the name of the type of each step, to save on querying for this later.

 18   def start_steps
 19     Step.all.select('s.*, st.name as step_type_name' ).joins( 'as s, step_collections as sc, step_collection_types as sct, step_types as st' ).where( 's.id = sc.step_id' ).where( 'sc.step_collection_type_id = sct.id' ).where( 'sct.name = ?', 'Start steps' ).where( 'sc.parliamentary_procedure_id =?', self ).where( 's.step_type_id = st.id' )
 20   end

Method to return all routes which appear in a procedure, together with the name and type of the source step of each route and the name and type of the target step of each route. This saves us having to query for these later.

 23   def routes_with_steps
 24     Route.all.select( 'r.*, ss.name as source_step_name, ts.name as target_step_name, sst.name as source_step_type, tst.name as target_step_type' ).joins( 'as r, procedure_routes as pr, steps as ss, steps as ts, step_types as sst, step_types as tst' ).where( 'r.id = pr.route_id' ).where( 'pr.parliamentary_procedure_id = ?', self ).where( 'r.from_step_id = ss.id' ).where( 'r.to_step_id = ts.id' ).where( 'ss.step_type_id = sst.id' ).where( 'ts.step_type_id = tst.id' )
 25   end

Method to return all steps connected to routes in a procedure, each step having:

 32   def steps_with_actualisations_in_work_package( work_package )
 33     Step.find_by_sql(
 34       "
 35         SELECT
 36           s.*,
 37           SUM(commons_step.is_commons) AS is_in_commons, 
 38           SUM(lords_step.is_lords) AS is_in_lords,
 39           SUM(actualisations_has_happened.is_actualised_has_happened) AS is_actualised_has_happened,
 40           COUNT(actualisations_has_happened.actualised_as_happened_count) as actualised_as_happened_count,
 41           SUM(actualisations.is_actualised) AS is_actualised
 42         FROM steps s
 44         /* We know that steps appear in a procedure by virtue of being attached to routes in that procedure, so we join to the routes table ... */
 45         INNER JOIN routes r
 47         /* We know that all steps in a procedure have an inbound route and that some don't have an outbound route, so we only bind the step to the to_step_id of a route. */
 48           ON r.to_step_id = s.id
 50         /* We join to the procedure_routes table, using the route_id ... */
 51         INNER JOIN procedure_routes pr
 52         	ON pr.route_id = r.id
 54           /* ... ensuring we only get routes in this procedure. */
 55         	AND pr.parliamentary_procedure_id = #{self.id}
 57         /* We know that a step may be in one or both Houses - or none - via the house_steps table, so we left join to the house_steps table twice. */
 58         /* The left join ensures that the outer step query returns records for steps that are not in the House we're querying for: ... */
 60         /* ... once to check if the step is in the Commons */
 61         LEFT JOIN
 62           (
 63             SELECT 1 as is_commons, hs.step_id
 64             FROM house_steps hs
 65             WHERE hs.house_id = 1 -- 1 being the ID of the Commons.
 66             GROUP BY hs.id
 67           ) commons_step
 68           ON s.id = commons_step.step_id
 70         /* ... and once to check if the step is in the Lords. */
 71         LEFT JOIN
 72           (
 73             SELECT 1 as is_lords, hs.step_id
 74             FROM house_steps hs
 75             WHERE hs.house_id = 2 -- 2 being the ID of the Lords.
 76             GROUP BY hs.id
 77           ) lords_step
 78           ON s.id = lords_step.step_id
 80           /* We know that a step may be actualised in a work package by one or more business items having a date in the past, or of today, or having no date. A step may be within a work package, but not actualised. */
 81         /* The left join ensures that the outer step query returns records for steps that have not been actualised with a business item with a date in the past, or of today. */
 82         LEFT JOIN
 83           (
 84             SELECT 1 as is_actualised_has_happened, COUNT(a.id) as actualised_as_happened_count, a.step_id
 85             FROM business_items bi, actualisations a
 86             WHERE bi.id = a.business_item_id
 88             /* We select business items with a date in the past or of today. */
 89             AND bi.date <= CURRENT_DATE
 91             /* We select business items within the specified work package. */
 92             AND bi.work_package_id = #{work_package.id}
 94             /* We group by the ID of the step being actualised. */
 95             GROUP BY a.step_id
 97           ) actualisations_has_happened
 98           ON s.id = actualisations_has_happened.step_id
 100         /* The left join ensures that the outer step query returns records for steps that have not been actualised with a business item, regardless of the date of that business item. */
 101         LEFT JOIN
 102           (
 103             SELECT 1 as is_actualised, a.step_id
 104             FROM business_items bi, actualisations a
 105             WHERE bi.id = a.business_item_id
 107             /* We select business items within the specified work package. */
 108             AND bi.work_package_id = #{work_package.id}
 110             /* We group by the ID of the actualisation. */
 111             GROUP BY a.id
 113           ) actualisations
 114           ON s.id = actualisations.step_id
 116           /* We group by the step ID because the same step may be the target step of many routes and we only want to include each step once. */
 117           GROUP BY s.id;
 118       "
 119     )
 120   end

A method to add an ellipsis to a description of a procedure, if the description text is longer than 255 characters.

 123   def description_massaged
 124     description.length < 256 ? description : description + " ..."
 125   end
 126 end