automated email editing l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Automated Email Editing PowerPoint Presentation
Download Presentation
Automated Email Editing

Loading in 2 Seconds...

play fullscreen
1 / 25

Automated Email Editing - PowerPoint PPT Presentation


  • 375 Views
  • Uploaded on

Automated Email Editing. Electronic Reporting (ER) Steve Kocsis, NOAA Fisheries Juneau, Alaska. Automate the reporting of the North Pacific groundfish and prohibited species catch. Electronic Reporting in Alaska. Receiving and sending daily and weekly reports

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Automated Email Editing' - salena


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
automated email editing

Automated Email Editing

Electronic Reporting (ER)

Steve Kocsis, NOAA Fisheries

Juneau, Alaska

electronic reporting in alaska

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
perl as an e r tool
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

e r sequence
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

e r in ak region 1998 now
E.R. in AK Region 1998-Now

*

* 2005

extrapolated

e r in ak region 1998 now6
E.R. In AK Region 1998 - Now
  • 98% coverage of groundfish
  • shoreside processors
  • Increasing coverage of processing vessels
client raw data
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

table fields
Table fields

...

...

...

...

production tables shoreside

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

report types
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)
timing sequence 1
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

timing sequence 2
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

timing sequence 3
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

perl dbi module
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

perl mime multipurpose internet mail extensions
Perl MIME (Multipurpose Internet Mail Extensions)

1

UNIX mail

MIME Decoding

Perl MIME module

Perl DBI

Attachments

1

Unique filename

by ER type

nmfs mailbox
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

perl regular expressions regex
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

perl regex
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

working directory

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

validation
Validation

Validation

package

Working tables

Production tables

errors

client

client

Load history

Generate RRT

Email

program flow 1
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

program flow 2
Program flow 2

sql*ldr

archive

Working tables

bad records

log records

errors

load history

validation

Production tables

program flow 3
Program flow 3

Loadhistory

Production tables

Generate return receipt

archive

Find client email

Email

Process with client s/w

summary electronic reporting
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