Versions Compared

Old Version 10

changes.mady.by.user Alexandre Bédard

Saved on

compared with

New Version Current

changes.mady.by.user Noreen Dertinger

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

...

...

...

System Profile

System Full Name

CAWIS Electronic Distribution Module

System Full Name (French)

SWIMN Electronic Distribution Module

Business Administrator

Sylvie BlaisLauren McMahon

Business Owner

Marc CaouetteLauren McMahon

DirectorDavid

TurnbullPhilippe Ngassam

Subject Matter Expert

Alexandre Bédard

Division

National Aircraft Certification

NTARS Code

CA60(?)

Source Location

Source Code Location

http://tfsprod:8080/tfs/civav/CAWIS-WAAU/_dashboards

Migration to DevOps

  •  

New Source Code Location (DevOps)

https://dev.azure.com/transport-canada/DSD-CIVAV Support/_git/CAWIS-EDM

Technology Assessment

Platform Type

Console

Database Platform and Version

Oracle 18c

Development Language and Framework

VB.NET

Operating System and Version

Windows Server 2016

Additional Dependencies

TC Mailer, Mailbee, RightFax

Authentication

None

Environment Access Information

ENV

UNC

WWWFILES

URL

DB

DEV

\\ncrws514\d$\Scripts\CivAv\CAWIS-EDM (Dev)

CAWISD

PREACC

PREACC_CA

ACC

CAWISA

PROD

CAWISP

TRAINING

System Overview

View file
nameEDM Process.vsdx

Good To Know

<--->

How-To and Fixes

Doing a run of EDM (Notice or overdue)

  1. PRIOR TO EXECUTION - ask the client if the AAIR-fleets are up to date. Fleet reporters are not to be included in the EDM. The FLEET report is available from the CAWIS reports menu.

  2. [Optional] Ask the client if they have new templates (see below for more details on how to update them).

  3. When the scheduled time for the run approaches (10 minutes), go to the EDM folder (See Dev UNC in table above).

  4. Open the “CAWIS_EDM.exe.config“ file. Make sure that the configuration “ClientEmailAddress” has your email address to receive the reports at the end of each steps.

  5. In the “Scripts” folder, open the file “LaunchEDM.bat” file.

  6. Remove the “rem” at the start of the file. This will un-comment the actual execution of EDM.

  7. Depending of which type of run, some settings will need to be changed.

    1. For “-TYPE”, if the run is for notices, put “AAIR_NOTICE” after the equal sign. For overdues, put “AAIR_OVERDUE” instead.

    2. For “-STEP”, put the following value after the equal sign depending on which part of the run you are at (listed in the normal order): “EMAIL”, “EMAIL_PARSING”, “FAX”, “FAX_PARSING”, “FTP”.

  8. [Optional] You may want to change the name of the log file. Any name is fine.

  9. Note: To monitor the execution, you can open the log file in a text editor and reload it whenever you want an update. (I suggest Notepad++ since it makes reloading the file easier)

  10. When the step is finished, put back the “rem” at the beginning to the batch file.

  11. Send the confirmation email received from EDM to the client.

  12. Return to step 4 until all the steps have been done (usually the FTP step). The recommended time between the steps is at least 3 days.

  13. Important: Make sure that “rem” is at the beginning of the batch file to avoid that the program execute itself on other months.

Updating the notice or overdue email template (For developers)

  1. Using Toad or SQL Developer, on the CAWISP database, download the template by looking at the “ATTACHMENT_NAME_NM” column in the table “CAWIS.A33_CAWIS_ATTACHMENTS”. The notice template is named “AAIR_NOTICE-1-en.txt” and the overdue template, “AAIR_OVERDUE-1-en.txt”. The data is located in ATTACHMENT_RAW.
    Note: even if the attachment name says .txt, this is actually an HTML file.

  2. Open the file in an HTML editor, such as Notepad++.

  3. Update the template using the client provided template.

  4. Replace any wildcard value with the placeholders below.

  5. Save the template and upload it to the same location as step 1.

  6. Open CAWIS_EDM.exe.config and make sure that your email is in the “SmokeTestEmailInternal” setting.

  7. Execute CAWIS-EDM using the following command using either the command prompt or Powershell: .\CAWIS_EDM.exe -TESTMODE

  8. Select the option “1 – Internal Email Test” and wait for the email to arrive in your inbox.

  9. Check the received email for any discrepancies with the client provided template.

  10. Send the received email to the client for approval.

Keyword

Description

%AAIR_YEAR%

Last Year (From EDM run date)

%TAIL_MARK%

Aircraft tailmark

%LAST_YEAR%

Last Year (From today)

%TODAY_PLUS1_MONTH%

One month from today

%CAWIS_URL%

CAWIS homepage URL

%HELP_URL%

CAWIS help URL

%HELP_URL_FRA%

CAWIS french help URL

%SENT_MONTH_ENG%

Month on which the AAIR notice was issued (Fullname)

%SENT_MONTH_FRA%

French month on which the AAIR notice was issued (Fullname)

%DUE_DATE%

AAIR due date (Calculated from EDM run date)

 Updating the notice or overdue fax template

  1. Using Toad or SQL Developer, on the CAWISP database, download the template by looking at the “ATTACHMENT_NAME_NM” column in the table “CAWIS.A33_CAWIS_ATTACHMENTS”. The notice template is named “AAIR_NOTICE-2-en.txt” and the overdue template, “AAIR_OVERDUE-2-en.txt”. The data is located in ATTACHMENT_RAW.
    Note: even if the attachment name says .txt, this is actually an HTML file.

  2. Open the file in an HTML editor, such as Notepad++.

  3. Update the template using the client provided template.

  4. Replace any wildcard value with the placeholders below.

  5. Save the template and upload it to the same location as step 1.

  6. Open CAWIS_EDM.exe.config and make sure that your fax number is in the “SmokeTestFax” setting.

  7. Execute CAWIS-EDM using the following command using either the command prompt or Powershell: .\CAWIS_EDM.exe -TESTMODE

  8. Select the option “3 - Fax Test” and wait for the fax to arrive.

  9. Check the received fax for any discrepancies with the client provided template.

  10. Either re-run the fax test using the client fax number or send a scan of the received fax to the client for approval.

Keyword

Description

@@AAIR_ACCESS_CODE

Aircraft access code (Confidential information)

@@CAWIS_URL

CAWIS homepage URL

@@AAIR_ISSUE_DATE

AAIR issue date

@@AAIR_DUE_DATE

AAIR due date

@@TAILMARK

Aircraft tailmark

@@AIRCRAFT_TATC

Aircraft TACT code

@@AUTHORITY

Aircraft type of authority

@@PURPOSE

Aircraft purpose

@@BASE_COUNTRY

Aircraft country

@@BASE_PROVINCE

Aircraft province

@@BASE_MUNICIPALITY

Aircraft municipality

@@BASE_AIRPORT

Aircraft airport

@@BASE_OTHER

Aircraft other location

@@BASE_REGION

Aircraft region

@@REPORTING_YEAR

Year before issue date (Format: JAN 1 TO DEC 31, {REPORTING_YEAR})

@@OWNER_NAME

Owner name

@@ADDRESS_1

Owner primary address

@@CITY_PROV

Owner city and province

@@POSTAL_CODE

Owner postal code

@@AIRCRAFT_MAKE

Aircraft manufacturer

@@AIRCRAFT_MODEL

Aircraft model

@@AIRCRAFT_SERIAL

Aircraft serial number

@@ENGINE_MAKE

Engine(s) manufacturer

@@ENGINE1_MODEL

First engine model

@@ENGINE1_SERIAL

First engine serial number

@@ENGINE2_MODEL

Second engine model

@@ENGINE2_SERIAL

Second engine serial number

@@ENGINE3_MODEL

Third engine model

@@ENGINE3_SERIAL

Third engine serial number

@@ENGINE4_MODEL

Fourth engine model

@@ENGINE4_SERIAL

Fourth engine serial number

@@PROP_MAKE

Propeller(s) manufacturer

@@PROP1_MODEL

First propeller model

@@PROP1_SERIAL

First propeller serial number

@@PROP2_MODEL

Second propeller model

@@PROP2_SERIAL

Second propeller serial number

@@PROP3_MODEL

Third propeller model

@@PROP3_SERIAL

Third propeller serial number

@@PROP4_MODEL

Fourth propeller model

@@PROP4_SERIAL

Fourth propeller serial number

@@SKI_MAKE

Ski manufacturer

@@SKI_MODEL

Ski model

@@FLOAT_MAKE

Float manufacturer

@@FLOAT_MODEL

Float model

@@TC_LINE1

Formatted tailmark (Format: TRANSPORT CANADA {Tailmark})

@@TC_LINE2

TC address (First line)

@@TC_LINE3

TC address (Second line)

@@TC_LINE4

TC city and province (Format: {City} {Province})

@@TC_LINE5

TC postal code

@@EMPTY_ACTUAL

Aircraft actual empty weight

@@EMPTY_AMENDED

Aircraft amended empty weight

@@MAX_WEIGHT

Aitcraft maximum takeoff weight

@@ACTUAL_DATE

Last updated date of aircraft actual weight

@@AMENDED_DATE

Last updated date of aircraft amended weight

@@EMAIL_ADDRESS

Aircraft email address

@@FAX_NPA

Numbering plan area of the owner fax number

@@FAX_NXX

Central office code of the owner fax number

@@FAX_NUM

Line number of the owner fax number

@@AW

Checked if actual landing configuration is 'WHEELS' (Format: X)

@@AF

Checked if actual landing configuration is 'FLOATS' (Format: X)

@@AS

Checked if actual landing configuration is 'SKIS' (Format: X)

@@AMW

Checked if amended landing configuration is 'WHEELS' (Format: X)

@@AMF

Checked if amended landing configuration is 'FLOATS' (Format: X)

@@AMS

Checked if amended landing configuration is 'SKIS' (Format: X)

@@TKOFFW

Checked if takeoff configuration is 'WHEELS' (Format: X)

@@TKOFFF

Checked if takeoff configuration is 'FLOATS' (Format: X)

@@TKOFFS

Checked if takeoff configuration is 'SKIS' (Format: X)

EDM General Help

Sends notice to aircraft owners about upcoming AAIR due date.

...

In case of program failure the program is designed to allow a re-run of a step any number of times.

Editing CAWIS EDM Template for notice and overdue notification (for CAWIS user)

Requires: Basic knowledge of HTML, CAWIS administrator account, Access to the internal version of CAWIS

Retrieving the HTML template

  1. Login into CAWIS using credentials that have the AU role (administrative privilege).

  2. Go to “Code Table Admin”, then “General” and then “CAWIS Attachments”.

  3. Click on the template to edit and save it.

  4. Rename the file extension from “.txt” to “.htm”.

Editing the HTML template

  1. Open the file in a text editor.

  2. [Optional] Open the file in a browser. This will let you see the changes you make when you save them and refresh the page in the browser.

  3. Edit the file with the needed changes. All valid placeholders are located in “Code Table Admin” > “EDM” > “EDM Keywords”.
    Note: Everything must resides within the HTML file. No external resource (such as images) are to be included, except for hyperlinks. Images can be included if encoded using Base64 directly into the HTML code.

  4. Save the file.

  5. [Optional] Refresh the browser page previously opened and check for any problem.

Upload the HTML template

  1. Go back to the “CAWIS Attachments” page.

  2. Select the template to edit and click on “Upload”.

  3. Upload the file using the “Browse” button. DO NOT check the “Update filename” checkbox.

  4. Click on “Save”.

Validate the HTML template

  1. Submit a ticket to the CAWIS development team to execute a test run for email or fax templates.

  2. Wait for the test run to be complete. You will receive an email with placeholders replaced by test values.

POST EDM - Parsing the data

A week or so, after an EDM run, we want to identify any email addresses where we received bounce-backs, so that the Publications unit can eliminate these from CAWIS. To accomplish this, we run the -PARSEMAIL function.

Note :  A query like this, will let you know if the data has been parsed properly

Basically, we're searching AA003 for failures ( notification status = 3 )  where the client had a legitimate looking email.  This success/fail info will be available to CAWIS admin, on the AAIRDATA report, next day

Yes, it is possible that there were NO bad emails in the EDM run, but this would be a very rare occurrence.

SELECT * FROM AA003_NOTIFICATION_ATTEMPT

WHERE PROCESS_YEAR_MONTH_NUM = 'YYYYMM'

AND NOTIFICATION_TARGET_LBL NOT IN ('Blank Email Address',

'Failure due to notification preference' ,

'failure due to SMTP Error' )

AND NOTIFICATION_STATUS_CD = '3' ;