reclassification.yaml

dataset_type: reclassification
target_dataset: reclassification
title: Proactive Publication - Position Reclassification
shortname: Position Reclassification
notes: Access, upload and modify the position reclassification reports for your organization

template_version: 3
template_updated: 2020-09-15

portal_type: info
collection: pd

resources:
- title: Proactive Publication - Position Reclassification
  resource_name: reclassification

  published_resource_id: bdaa5515-3782-4e5c-9d44-c25e032addb7

  create_form: true
  edit_form: true
  fields:

  - datastore_id: ref_number
    datastore_type: text
    label:
      en: Reference Number
      fr: Numéro de référence
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    excel_column_width: 30
    form_attrs:
      size: 60

  - datastore_id: job_number
    datastore_type: text
    label:
      en: Job Number
      fr: Numéro d'emploi
    format_type: Free text
    excel_column_width: 30
    form_attrs:
      size: 60

  - datastore_id: pos_number
    datastore_type: text
    label:
      en: Position Number
      fr: Numéro du poste
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    excel_column_width: 30
    form_attrs:
      size: 60

  - datastore_id: date
    datastore_type: date
    label:
      en: Date
      fr: Date
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Date (Please format the data as YYYY-MM-DD)
    excel_column_width: 20
    extract_date_year: true
    extract_date_month: true
    extract_date_clean: true
    form_snippet: scheming/form_snippets/date.html

  - datastore_id: pos_title_en
    datastore_type: text
    label:
      en: Position Title (English)
      fr: Titre du poste (anglais)
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    excel_column_width: 30
    form_attrs:
      size: 60

  - datastore_id: pos_title_fr
    datastore_type: text
    label:
      en: Position Title (French)
      fr: Titre du poste (français)
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    excel_column_width: 30
    form_attrs:
      size: 60

  - datastore_id: old_class_group_code
    datastore_type: text
    label:
      en: Previous Position Classification Group
      fr: Groupe de classification du poste antérieur
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Controlled List
    validation: This field must not be empty
    excel_full_text_choices: True
    form_choices_prefix_code: True
    choices_file: choices/classification_group.yaml
    excel_error_formula: 'FALSE'

  - datastore_id: old_class_level
    datastore_type: text
    label:
      en: Previous Position Classification Level Number
      fr: Numéro de niveau de la classification du poste antérieur
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Controlled List
    validation: This field must not be empty
    excel_full_text_choices: False
    choices_file: choices/level_number.yaml
    excel_error_formula: 'FALSE'

  - datastore_id: new_class_group_code
    datastore_type: text
    label:
      en: Reclassified Position Classification Group
      fr: Groupe de classification du poste reclassifié
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Controlled List
    validation: This field must not be empty
    excel_full_text_choices: True
    form_choices_prefix_code: True
    choices_file: choices/classification_group.yaml
    excel_error_formula: 'FALSE'

  - datastore_id: new_class_level
    datastore_type: text
    label:
      en: Reclassified Position Classification Level Number
      fr: Numéro de niveau de la classification du poste reclassifié
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Controlled List
    validation: This field must not be empty
    excel_full_text_choices: False
    choices_file: choices/level_number.yaml
    excel_error_formula: 'FALSE'

  - datastore_id: old_differential
    datastore_type: text
    label:
      en: Previous Supervisory Differential
      fr: Ancienne prime de surveillance
    format_type: Free Text
    form_attrs:
      size: 60

  - datastore_id: new_differential
    datastore_type: text
    label:
      en: Reclassified Supervisory Differential
      fr: Prime de surveillance précédente
    format_type: Free Text
    form_attrs:
      size: 60

  - datastore_id: reason_en
    datastore_type: text
    label:
      en: Reason for Reclassification (English)
      fr: Raison de la decision en anglais
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Free Text
    validation: This field must not be empty
    form_snippet: scheming/form_snippets/textarea.html
    form_attrs:
      style: "width: 100%; display: block"

  - datastore_id: reason_fr
    datastore_type: text
    label:
      en: Reason for Reclassification (French)
      fr: Raison de la decision en français
    obligation: Mandatory
    excel_required: true
    form_required: true
    format_type: Free Text
    validation: This field must not be empty
    form_snippet: scheming/form_snippets/textarea.html
    form_attrs:
      style: "width: 100%; display: block"

  - datastore_id: record_created
    label: Record Creation Time
    import_template_include: false
    visible_to_public: false
    datastore_type: timestamp
    preview_class: bg-info

  - datastore_id: record_modified
    label: Last Record Modification Time
    import_template_include: false
    visible_to_public: false
    datastore_type: timestamp
    preview_class: bg-info

  - datastore_id: user_modified
    label: User Last Modified Record
    import_template_include: false
    visible_to_public: false
    datastore_type: text
    preview_class: bg-info

  solr_static_fields:
    report_type_en: Position Reclassification
    report_type_fr: Reclassifications de postes

  datastore_primary_key: [ref_number]
  datastore_indexes: ""

  triggers:
    - reclassification_trigger: |
        DECLARE
          errors text[][] := '{{}}';
          crval RECORD;
        BEGIN
          errors := errors || required_error(NEW.ref_number, 'ref_number');
          IF NEW.ref_number LIKE '%,%' THEN
            errors := errors || ARRAY[['ref_number', {ref_number_error}]];
          END IF;
          errors := errors || required_error(NEW.pos_number, 'pos_number');
          errors := errors || required_error(NEW.date, 'date');
          errors := errors || required_error(NEW.pos_title_en, 'pos_title_en');
          errors := errors || required_error(NEW.pos_title_fr, 'pos_title_fr');
          errors := errors || required_error(NEW.old_class_group_code, 'old_class_group_code');
          errors := errors || choice_error(NEW.old_class_group_code, {old_class_group_code}, 'old_class_group_code');
          errors := errors || required_error(NEW.new_class_group_code, 'new_class_group_code');
          errors := errors || choice_error(NEW.new_class_group_code, {new_class_group_code}, 'new_class_group_code');
          errors := errors || required_error(NEW.old_class_level, 'old_class_level');
          errors := errors || required_error(NEW.new_class_level, 'new_class_level');
          NEW.old_class_level := lpad(NEW.old_class_level::text, 2, '0');
          errors := errors || choice_error(NEW.old_class_level, {old_class_level}, 'old_class_level');
          NEW.new_class_level := lpad(NEW.new_class_level::text, 2, '0');
          errors := errors || choice_error(NEW.new_class_level, {new_class_level}, 'new_class_level');
          errors := errors || required_error(NEW.reason_en, 'reason_en');
          errors := errors || required_error(NEW.reason_fr, 'reason_fr');
          IF errors = '{{}}' THEN
            RETURN NEW;
          END IF;
          RAISE EXCEPTION E'TAB-DELIMITED\t%', array_to_string(errors, E'\t');
        END;
    - update_record_modified_created_trigger

  trigger_strings:  # yaml | not supported
    ref_number_error: Comma is not allowed in Reference Number field

  examples:
    record:
      ref_number: "PR-2019-Q1-00005"
      pos_number: "14999"
      date: "2019-03-01"
      pos_title_en: "Chief, Facilities Services Management"
      pos_title_fr: "Chef, Gestion des services aux immeubles"
      old_class_group_code: "AS"
      old_class_level: "03"
      new_class_group_code: "AS"
      new_class_level: "04"
      reason_en: "Change of Duties"
      reason_fr: "Changements des les fonctions de travail"
    filters:
      pos_number: "14999"
    filter_one:
      ref_number: "PR-2019-Q1-00005"
    sort: date desc


- title: Proactive Publication - Position Reclassification Nothing to Report
  resource_name: reclassification-nil

  published_resource_id: 1e955e4d-df35-4441-bf38-b7086192ece2

  create_form: true
  edit_form: false #edit not possible for nil reports since only stored data is primary key
  fields:

  - datastore_id: year
    datastore_type: year
    label:
      en: Year
      fr: Année
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    excel_column_width: 10
    extract_date_year: true
    excel_error_formula: '{cell}>YEAR(TODAY())'

  - datastore_id: quarter
    datastore_type: text
    label:
      en: Quarter
      fr: Trimestre
    excel_column_width: 10
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    excel_full_text_choices: true
    choices:
      Q1:
        en: April 1 - June 30
        fr: 1 avril - 30 juin
      Q2:
        en: July 1 - September 30
        fr: 1 juillet - 30 septembre
      Q3:
        en: October 1 - December 31
        fr: 1 octobre - 31 décembre
      Q4:
        en: January 1 - March 31
        fr: 1 janvier - 31 mars

  - datastore_id: record_created
    label: Record Creation Time
    import_template_include: false
    visible_to_public: false
    datastore_type: timestamp
    preview_class: bg-info

  - datastore_id: record_modified
    label: Last Record Modification Time
    import_template_include: false
    visible_to_public: false
    datastore_type: timestamp
    preview_class: bg-info

  - datastore_id: user_modified
    label: User Last Modified Record
    import_template_include: false
    visible_to_public: false
    datastore_type: text
    preview_class: bg-info

  solr_static_fields:
    report_type_en: Nothing to report
    report_type_fr: Rien à signaler

  datastore_primary_key: [year, quarter]
  datastore_indexes: ""

  triggers:
  - reclassification_nil_trigger: |
      DECLARE
        errors text[][] := '{{}}';
        crval RECORD;
      BEGIN
        errors := errors || required_error(NEW.year, 'year');
        errors := errors || required_error(NEW.quarter, 'quarter');
        errors := errors || choice_error(NEW.quarter, {quarter}, 'quarter');
        IF NEW.year > date_part('year', CURRENT_DATE) THEN
          errors := errors || ARRAY[['year', {year_error}]];
        END IF;

        IF errors = '{{}}' THEN
          RETURN NEW;
        END IF;
        RAISE EXCEPTION E'TAB-DELIMITED\t%', array_to_string(errors, E'\t');
      END;
  - update_record_modified_created_trigger

  trigger_strings:  # yaml | not supported
    year_error: This must list the year you are reporting on (not the fiscal year).

  examples:
    record:
      year: 2016
      quarter: Q2
    filters:
      year: 2016
    filter_one:
      year: 2016
      quarter: Q2
    sort: year desc, quarter desc

# https://design.gccollab.ca/component/colour "Green and Blue" colours 2, 3
excel_edge_style:
  PatternFill:
    patternType: solid
    fgColor: FF4e4741
  Font:
    color: FFFFFF
    size: 14
excel_header_style:
  PatternFill:
    patternType: solid
    fgColor: FF83c3f2
excel_column_heading_style:
  PatternFill:
    patternType: solid
    fgColor: FF83c3f2