Automated Email Editing

1. Automated Email Editing Electronic Reporting (ER) Steve Kocsis, NOAA Fisheries Juneau, Alaska

2. Automate the reporting of the • North Pacific groundfish and • prohibited species catch Electronic Reporting in Alaska • 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 Generate RRT Load Host 1) Get files 2) Unique ID 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,655430,110,41,,517,226.,,,N 07,1234,08/31/2001,67890,08/29/2001,G00029549,4,655430,121,41,,517,171.,,,N 07,1234,08/31/2001,67890,08/29/2001,G00029549,3,655430,122,41,,517,5.,,,N 07,1234,08/31/2001,67890,08/29/2001,G00029549,6,655430,141,41,,517,9.,,,N 07,1234,08/31/2001,67890,08/29/2001,G00029549,2,655430,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. 01 sl_buying_station transmitted_data_file Production tables - shoreside 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 Sql*ldr script for loading ER Data into working tables

13. Timing Sequence 2 Server LAN Oracle Application Perl DBI Send sql*ldr command Run sql*ldr 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 Client Oracle Application Internet LAN 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 S C R I P T P E R L AP I 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. NMFSmailbox 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\w{3}\s\w{3}\s{1,2}\d{1,2}\s\d{2}:\d{2}:\d{2}\sAK[SD]\s\d{4}$/ From Steve.Kocsis@noaa.gov Fri Oct 21 15:15:38 AKD 2003 s/load/mod/; s/<datafile>/$fn/; slgload.ctl -> slgmod.ctl <datafile> -> 0008128.slg s/$extpattern$/\.bad/ s/$extpattern$// 111.era->111.bad 222.slg->222.bad 111.era->111 222.vlr->222

20. 00021348.slg Working directory Hfdsihfjkhjkhsfh Fdsjhlkjlfkjsfkjf Dsfhkjfhhsfd Fsdhjsjfdfjsfd Fdjkjgkjgjfgkd Dhfdhffjfhjksf … Type?? ER Data files Time scheduled *.slg run sql*ldr 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 Data file Time scheduled email Get ER attachments Assign unique file name Ifjkjjjfdsklf Jffkjfkjfksl Fdhfhfh Fshfh fdsjfhfs Fetch ER profile Time scheduled sql*ldr ctl file by ER type Client form Import into working tables

23. Program flow 2 sql*ldr archive Working tables bad records log records errors load history validation Production tables

24. Program flow 3 Loadhistory 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