hospitalityq.yaml

dataset_type: hospitalityq
target_dataset: hospitalityq

title: Proactive Publication - Hospitality Expenses
shortname: Hospitality Expenses
notes: Access, upload and modify the quarterly hospitality expenses for your organization

template_version: 3
template_updated: 2019-07-10

portal_type: info
collection: pd


resources:
- title: Proactive Publication - Hospitality Expenses
  resource_name: hospitalityq

  published_resource_id: 7b301f1a-2a7a-48bd-9ea9-e0ac4a5313ed

  create_form: true
  edit_form: true
  fields:

  # 3.1
  - datastore_id: ref_number
    datastore_type: text
    label:
      en: Reference Number
      fr: Numéro de référence
    description:
      en: This field is populated by the organization. It is a unique reference number given to each line item in the spreadsheet. Having a unique identifier for each item will allow users locate a specific item in the registry should they need to modify or delete.
      fr: Cette zone est remplie par chaque organisation. Un identificateur unique est attribué à chaque poste dans le tableur. Un identificateur unique pour chaque poste permettra aux utilisateurs de repérer un article en particulier s’ils doivent le modifier ou le supprimer.
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type:
      en: "H-2019-P1-00001 – this sequence will continue for each line item (i.e.: H-2019-P1-00001, H-2019-P1-00002, H-2019-P1-00003, etc.)"
      fr: H-2016-P1-00001 – cette séquence se poursuivra pour chaque poste (c.-à-d. H-2016-Q1-00001, H-2016-P1-00002, H -2016-P1-00003….)

  # 3.2
  - datastore_id: disclosure_group
    datastore_type: text
    label:
      en: Disclosure Group
      fr: Groupe de divulgation
    description:
      en: This field will display the group to which the individual belongs.
      fr: Ce champ indique le groupe auquel appartient l’individu.
    obligation: Optional
    format_type: Controlled List
    choices:
      MPSES:
        en: Minister/Ministerial advisor / Ministerial Staff / Parliamentary Secretary/Exempt Staff
        fr: Ministre / Conseiller ministériel / Personnel ministériel / Secrétaire parlementaire / Personnel exonéré
      SLE:
        en: Senior officer or employees
        fr: Cadre supérieur ou employé

  # 3.3
  - datastore_id: title_en
    datastore_type: text
    label:
      en: Title (English)
      fr: Titre (anglais)
    description:
      en: This field will display, in English, the position title of the individual who incurred the hospitality expenses (the hospitality expenses were charged to their responsibility centre).
      fr: Cette zone indique, en anglais, le titre du poste de la personne qui a engagé les dépenses d’accueil (les dépenses d’accueil ont été imputées à leur centre de responsabilité).
    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

  # 3.4
  - datastore_id: title_fr
    datastore_type: text
    label:
      en: Title (French)
      fr: Titre (français)
    description:
      en: This field will display, in French, the position title of the individual who incurred the hospitality expenses (the hospitality expenses were charged to their responsibility centre).
      fr: Cette zone indique, en français, le titre du poste de la personne qui a engagé les dépenses d’accueil (les dépenses d’accueil ont été imputées à leur centre de responsabilité).
    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

  # 3.5
  - datastore_id: name
    datastore_type: text
    label:
      en: Name
      fr: Nom
    description:
      en: This field will display the name of the individual who incurred the hospitality expenses (the hospitality expenses were charged to their responsibility centre).
      fr: Ce champ affiche le nom de la personne qui a engagé les dépenses d’accueil (les dépenses d’accueil ont été imputées à leur centre de responsabilité.
    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

  # 3.6
  - datastore_id: description_en
    datastore_type: text
    label:
      en: Purpose of hospitality activity (English)
      fr: But de l’activité d’accueil (anglais)
    description:
      en: This will cover both the forms (for example, breakfast, refreshment, lunch, reception, dinner and other forms of hospitality) and circumstances (the purpose) of the hospitality, in English.
      fr: Cette zone comprend la forme d’accueil (par exemple, déjeuner, rafraîchissement, dîner, réception, souper, et autres formes d’accueil) et les circonstances (le but) de l’accueil, en anglais.
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    form_snippet: scheming/form_snippets/textarea.html
    form_attrs:
      style: "width: 100%; display: block"

  # 3.7
  - datastore_id: description_fr
    datastore_type: text
    label:
      en: Purpose of hospitality activity (French)
      fr: But de l’activité d’accueil (français)
    description:
      en: This will cover both the forms (for example, breakfast, refreshment, lunch, reception, dinner and other forms of hospitality) and circumstances (the purpose) of the hospitality, in French.
      fr: Cette zone comprend la forme d’accueil (par exemple, déjeuner, rafraîchissement, dîner, réception, souper, et autres formes d’accueil) et les circonstances (le but) de l’accueil, en français.
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    form_snippet: scheming/form_snippets/textarea.html
    form_attrs:
      style: "width: 100%; display: block"

  # 3.8
  - datastore_id: start_date
    datastore_type: date
    label:
      en: Start Date
      fr: Date de début
    description:
      en: The start date on which the hospitality was provided.
      fr: La date du début auxquelles les activités d’accueil ont eu lieu.
    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

  # 3.9
  - datastore_id: end_date
    datastore_type: date
    label:
      en: End Date
      fr: Date de fin
    description:
      en: The end date on which the hospitality was provided. (can be the same as start date)
      fr: La date de fin auxquelles les activités d’accueil ont eu lieu. (peut être la même date du début)
    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
    form_snippet: scheming/form_snippets/date.html

  # 3.10
  - datastore_id: location_en
    datastore_type: text
    label:
      en: Municipality where the hospitality activity took place (English)
      fr: Municipalité où l’activité d’accueil a eu lieu (anglais)
    excel_heading:
      en: |
        Municipality where the hospitality
        activity took place (English)
      fr: |
        Municipalité où l’activité d’accueil
        a eu lieu (anglais)
    description:
      en: Must include the Municipality where hospitality was provided, in English
      fr: Comprend la municipalité où les services d’accueil ont été fournis, en anglais
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    form_attrs:
      size: 60

  # 3.11
  - datastore_id: location_fr
    datastore_type: text
    label:
      en: Municipality where the hospitality activity took place (French)
      fr: Municipalité où l’activité d’accueil a eu lieu (français)
    excel_heading:
      en: |
        Municipality where the hospitality
        activity took place (French)
      fr: |
        Municipalité où l’activité d’accueil
        a eu lieu (français)
    description:
      en: Must include the Municipality where hospitality was provided, in French.
      fr: Comprend la municipalité où les services d’accueil ont été fournis, en français.
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    form_attrs:
      size: 60

  # 3.12
  - datastore_id: vendor_en
    datastore_type: text
    label:
      en: Name of commercial establishment or vendor involved in the hospitality activity  (English)
      fr: Nom des établissements commerciaux ou des fournisseurs concernés (anglais)
    excel_heading:
      en: |
        Name of commercial establishment or vendor
        involved in the hospitality activity (English)
      fr: |
        Nom des établissements commerciaux ou des
        fournisseurs concernés (anglais)
    description:
      en: Must include the name of the commercial establishment or vendor that provided the hospitality (for example, restaurant, hotel or other location) and/or vendor (for example, a caterer), in English
      fr: Comprend le nom de l’établissement commercial qui a fourni l’accueil (par exemple, restaurant, hôtel ou autre lieu) et/ou du fournisseur (par exemple, un traiteur), en anglais.
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    form_attrs:
      size: 60

  # 3.13
  - datastore_id: vendor_fr
    datastore_type: text
    label:
      en: Name of commercial establishment or vendor involved in the hospitality activity  (French)
      fr: Nom des établissements commerciaux ou des fournisseurs concernés (français)
    excel_heading:
      en: |
        Name of commercial establishment or vendor
        involved in the hospitality activity (French)
      fr: |
        Nom des établissements commerciaux ou
        des fournisseurs concernés (français)
    description:
      en: Must include the name of the commercial establishment or vendor that provided the hospitality (for example, restaurant, hotel or other location) and/or vendor (for example, a caterer), in French.
      fr: Comprend le nom de l’établissement commercial qui a fourni l’accueil (par exemple, restaurant, hôtel ou autre lieu) et/ou du fournisseur (par exemple, un traiteur), en français.
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    form_attrs:
      size: 60

  # 3.14
  - datastore_id: employee_attendees
    datastore_type: int
    label:
      en: Attendees (Government of Canada Officials)
      fr: Participants (Nombre de représentants du gouvernement du Canada)
    excel_heading:
      en: |
        Attendees (Government
        of Canada Officials)
      fr: |
        Participants (Nombre de représentants
        du gouvernement du Canada)
    description:
      en: The total number of attendees (Government of Canada Officials)
      fr: Nombre total de participants (représentants du gouvernement du Canada).
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    form_attrs:
      size: 40

  # 3.15
  - datastore_id: guest_attendees
    datastore_type: int
    label:
      en: Attendees (Guests)
      fr: Participants (Nombre d’invités)
    description:
      en: The total number of attendees (Guests)
      fr: Nombre total de participants (invités)
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type: Free text
    form_attrs:
      size: 40

  # 3.16
  - datastore_id: total
    datastore_type: money
    label:
      en: Total cost
      fr: Total des coûts
    description:
      en: Total Amount of the expenses for the hospitality activity
      fr: Montant total des dépenses pour l’activité d’accueil
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    format_type:
      en: Numerical – Note, do not include dollar signs ($), all amounts include taxes.
      fr: Texte libre
    extract_double_sortable: true
    excel_column_width: 20
    form_attrs:
      size: 40
    solr_dollar_range_facet:
     - 0
     - 250
     - 500
     - 1000
     - 5000
     - 25000

  # 3.17
  - datastore_id: additional_comments_en
    datastore_type: text
    label:
      en: Additional comments (English)
      fr: Commentaires additionnels (anglais)
    description:
      en: This field may be populated with additional explanatory comments, in English.
      fr: Ce champ peut indiquer des commentaires explicatifs additionnels, en anglais.
    obligation: Optional
    format_type: Free text
    form_snippet: scheming/form_snippets/textarea.html
    form_attrs:
      style: "width: 100%; display: block"

    # 3.18
  - datastore_id: additional_comments_fr
    datastore_type: text
    label:
      en: Additional comments (French)
      fr: Commentaires additionnels (français)
    description:
      en: This field may be populated with additional explanatory comments, in French.
      fr: Ce champ peut indiquer des commentaires explicatifs additionnels, en français.
    obligation: Optional
    format_type: Free text
    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: Hospitality Expenses
    report_type_fr: Dépenses d'accueil

  datastore_primary_key: [ref_number]
  datastore_indexes: ""

  excel_example_height: 50

  triggers:
  - hospitalityq_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.start_date, 'start_date');
        IF NEW.start_date >= '2019-06-21'::date THEN
          errors := errors || choice_error(NEW.disclosure_group, {disclosure_group}, 'disclosure_group');
          errors := errors || required_error(NEW.title_en, 'title_en');
          errors := errors || required_error(NEW.title_fr, 'title_fr');
          errors := errors || required_error(NEW.name, 'name');
          errors := errors || required_error(NEW.description_en, 'description_en');
          errors := errors || required_error(NEW.description_fr, 'description_fr');
          errors := errors || required_error(NEW.end_date, 'end_date');
          errors := errors || required_error(NEW.location_en, 'location_en');
          errors := errors || required_error(NEW.location_fr, 'location_fr');
          errors := errors || required_error(NEW.vendor_en, 'vendor_en');
          errors := errors || required_error(NEW.vendor_fr, 'vendor_fr');
          errors := errors || required_error(NEW.employee_attendees, 'employee_attendees');
          errors := errors || required_error(NEW.guest_attendees, 'guest_attendees');
          errors := errors || required_error(NEW.total, 'total');
        END IF;
        NEW.total := round(NEW.total, 2);
        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: H-2019-P3-001
      disclosure_group: SLE
      title_en: Vice-Chairperson, Deputy Minister, Parliamentary Secretary, Assistant Deputy Minister, Programs Branch
      title_fr: Vice-président, sous-ministre, secrétaire parlementaire, sous-ministre adjoint, Direction générale des programmes
      name: Smith, John
      description_en: "Refreshments: information management and policies committee meeting; reception: Maltese delegation, etc"
      description_fr: "Rafraîchissements : Réunion du Comité de gestion de l’information et des politiques, Réception : Délégation maltaise, etc"
      start_date: "2019-06-25"
      end_date: "2019-06-25"
      location_en: Montreal, Quebec, Canada
      location_fr: Montréal, Québec, Canada
      vendor_en: Le Centre Sheraton
      vendor_fr: Le Centre Sheraton
      employee_attendees: 150
      guest_attendees: 4
      total: "7710.55"
      additional_comments_en: "Provide additional explanatory comments as required."
      additional_comments_fr: "Fournir des commentaires explicatifs additionnels au besoin."
    filters:
      name: Jane Smith
    filter_one:
      ref_number: "1980198"
    sort: date desc

  solr_org_fields:
  - ati_email


- title: Proactive Publication - Hospitality Nothing to Report
  resource_name: hospitalityq-nil

  published_resource_id: 36a3b6cc-4f45-4081-8dbd-2340ca487041

  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
    description:
      en: "This tab / field in the template is only populated if there are no hospitality expenses for the reporting period. This field should be populated with the year of the reporting period."
      fr: "Cet onglet/champ du modèle n’est rempli que s'il n'y a pas de frais d'accueil pour la période d’établissement de rapports. Ce champ doit être rempli avec l’année de la période d’établissement de rapports."
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    excel_column_width: 10
    extract_date_year: true

  - datastore_id: month
    datastore_type: text
    label:
      en: Month
      fr: mois
    description:
      en: "This tab / field in the template is only populated if there are no hospitality expenses for the reporting period. This field should be populated with the month of the reporting period."
      fr: "Cet onglet/champ du modèle n’est rempli que s'il n'y a pas de frais d'accueil pour la période d’établissement de rapports. Ce champ doit être rempli avec le mois de la période d’établissement de rapports."
    obligation: Mandatory
    excel_required: true
    form_required: true
    validation: This field must not be empty
    excel_full_text_choices: true
    choices:
      P01:
        en: April
        fr: avril
      P02:
        en: May
        fr: mai
      P03:
        en: June
        fr: juin
      P04:
        en: July
        fr: juillet
      P05:
        en: August
        fr: août
      P06:
        en: September
        fr: septembre
      P07:
        en: October
        fr: octobre
      P08:
        en: November
        fr: novembre
      P09:
        en: December
        fr: décembre
      P10:
        en: January
        fr: janvier
      P11:
        en: February
        fr: février
      P12:
        en: March
        fr: 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
    datastore_type: timestamp
    visible_to_public: false
    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:
    nothing_to_report_en: Nothing to report for this period
    nothing_to_report_fr: Rien à signaler pour cette période
    report_type_en: Nothing to report
    report_type_fr: Rien à signaler

  datastore_primary_key: [year, month]
  datastore_indexes: ""

  triggers:
    - hospitalityq_nil_trigger: |
        DECLARE
          errors text[][] := '{{}}';
          crval RECORD;
        BEGIN
          errors := errors || required_error(NEW.year, 'year');
          errors := errors || required_error(NEW.month, 'month');
          errors := errors || choice_error(NEW.month, {month}, 'month');
          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: 2019
      month: P01
    filters:
      year: 2019
    filter_one:
      year: 2019
      month: P01
    sort: year desc, month desc

excel_edge_style:
  PatternFill:
    patternType: solid
    fgColor: FF5d535e
excel_header_style:
  PatternFill:
    patternType: solid
    fgColor: FF9a9eab
excel_column_heading_style:
  PatternFill:
    patternType: solid
    fgColor: FF9a9eab