Automated Email Editing

1
Automated Email Editing

• Electronic Reporting (ER)
• Steve Kocsis, NOAA Fisheries
• Juneau, Alaska

2
Electronic Reporting in Alaska

• Automate the reporting of the
• North Pacific groundfish and
• prohibited species catch

• Receiving and sending daily and weekly reports
• from/to shoreside plants and vessels

• Decision-making for closing fisheries

• Computing discard and by-catch rates

3
Perl as an E.R. Tool
Practical Extraction and Report Language, 88,
Wall
Free, GNU GPL, FSF, CPAN
DBI and Oracle DBD modules for dynamic SQL
MIME module for Email parsing and sending
Regular expression string manipulation
4
E.R. Sequence
NMFS SERVER
Form
Data
Client
NMFS Server
Email
Email
Validate Data
1) Get files 2) Unique ID
Load Host
Generate RRT
Bad files Validation errors Load history
Archive
5
E.R. in AK Region 1998-Now

2005 extrapolated
6
E.R. In AK Region 1998 - Now

• 98 coverage of groundfish
• shoreside processors

• Increasing coverage of processing vessels

7
Client data form
8
Client raw data

• 00,1234,411
• 03,1234,08/31/2001,N,N,2,N
• 04,1234,08/31/2001,BSAI,270,P,30,350752.,N
• 05,1234,08/31/2001,67890,08/29/2001,F1234,JNU,N,,,
  Y,,N
• 06,1234,08/31/2001,67890,08/29/2001,G00029549,,PTR
  ,47,189,N
• 07,1234,08/31/2001,67890,08/29/2001,G00029549,5,65
  5430,110,41,,517,226.,,,N
• 07,1234,08/31/2001,67890,08/29/2001,G00029549,4,65
  5430,121,41,,517,171.,,,N
• 07,1234,08/31/2001,67890,08/29/2001,G00029549,3,65
  5430,122,41,,517,5.,,,N
• 07,1234,08/31/2001,67890,08/29/2001,G00029549,6,65
  5430,141,41,,517,9.,,,N
• 07,1234,08/31/2001,67890,08/29/2001,G00029549,2,65
  5430,270,41,,517,3994.,,,N
• 99

Prefix identifies table 00- sl_rpt 03 -
sl_daily_activity 06- sl_delivery_fish_ticket 07
- sl_delivery_product
9
Table fields
...
...
...
...
10
Production tables - shoreside
01
sl_buying_station
transmitted_data_file
05
sl_delivery
sl_rpt
00
sl_daily_activity
sl_delivery_fish_ticket
03
06
sl_daily_product
sl_delivery_product
04
07
11
Report types

• Six types currently supported

• Identified by file extension
• (.era, .vwp, .slg, .cat, .dev, .vlr)

• Extension couples types to specific functions

• Modular software gives flexibility for
• expansion to new types (design feature)

12
Timing Sequence 1
Application
Server
Client
Internet
LAN
User sends data by Email
Read Email
Parse ER files
Query Database
Perl DBI
Use query to create Sqlldr script for loading
ER Data into working tables
13
Timing Sequence 2
LAN
Server
Oracle Application
Perl DBI
Send sqlldr command
Run sqlldr script to populate working tables
Return Error messages
Read and parse error messages
Load error tables Archive log and ER files
Run sql script and Unix archive commands
14
Timing Sequence 3
Server
Oracle Application
Client
LAN
Internet
Run ER validation Package
Send Sql
Move data in working tables to production tables
Return Error Messages
Send Sql
Generate error report
Send email with error report to client
Save report
Archive with FTP
15
Perl DBI module
.
E R L S C R I P T

D B I S W I T C H
Oracle Database
P E R L AP I
P E R L S C R I P T
ORACLEDBD
Dynamic SQL
.
File handles, connect/disconnect
16
Perl MIME (Multipurpose Internet Mail Extensions)
1
UNIX mail
MIME Decoding
Perl MIME module
Perl DBI
Attachments
1
Unique filename by ER type
17
NMFS mailbox
Client Email

• Time scheduled

Working directory
Parse decode attachments

• Queue ER files
• for processing

Identify ER files
Assign unique ID
00019546.slg
00019547.slg
Time stamp

00019548.slg
18
Perl regular expressions (regex)
Pattern matched to a string Textual analysis
and manipulation
/(.)\.(.)/ file 1 ext 2
8128.slg file 8128 ext slg
subject /Subject (.)/
Subject NOAATECH2004 subject NOAATECH2004
19
Perl regex
/From\s.\s\w3\s\w3\s1,2\d1,2\s\d2\d2
\d2\sAKSD\s\d4/
From Steve.Kocsis_at_noaa.gov Fri Oct 21 151538
AKD 2003
s/load/mod/
s/ltdatafilegt/fn/
slgload.ctl -gt slgmod.ctl
ltdatafilegt -gt 0008128.slg
s/extpattern/\.bad/
s/extpattern//
111.era-gt111.bad 222.slg-gt222.bad
111.era-gt111 222.vlr-gt222
20
Working directory
00021348.slg
Hfdsihfjkhjkhsfh Fdsjhlkjlfkjsfkjf Dsfhkjfhhsfd Fs
dhjsjfdfjsfd Fdjkjgkjgjfgkd Dhfdhffjfhjksf
Type??
ER Data files
Time scheduled
.slg
run sqlldr script
Load errors
archive
Load history
Load working tables
21
Validation
Validation package
Working tables
Production tables
errors
client
client
Load history
Generate RRT
Email
22
Program Flow 1
NMFS mailbox
Time scheduled
Data file
email
Get ER attachments
Assign unique file name
Ifjkjjjfdsklf Jffkjfkjfksl Fdhfhfh Fshfh fdsjfhfs
Fetch ER profile
Time scheduled
sqlldr ctl file by ER type
Client form
Import into working tables
23
Program flow 2
sqlldr
archive
Working tables
bad records
log records
errors
load history
validation
Production tables
24
Program flow 3
Load history
Production tables
Generate return receipt
archive
Find client email
Email
Process with client s/w
25
Summary Electronic Reporting

• In operation since 1998 using Perl/UNIX

• Improved In-season fisheries management
• - goals of increased timeliness and reliability
• have been met

• Decreased time and cost of reporting
• for NMFS and industry
• - widely accepted by industry
• - NMFS AK region success
• - Mandatory for many shoreside processors
• Cooperation with other agencies