Overview of Requirements
INPUT - VERSION 1:
-  Setting up the ftp upload facility
-  Polling the directory where ftp'd files will be uploaded;
-  Loading the data into mysql tables (possibly a temporary table prior to running diagnostics)
-  Run basic diagnostics (Bill will supply a list of business rules including both Errors and Warning conditions):
-  Email the user a notification that (1) file was received; (2) File was run through diagnostics (and report on errors and warnings)
 
OUTPUT v1:
- Basic Detailed Output: 
--- Produce a link to a tab-delimited file of all records on an html page that authorized users (AFP, Urban, others?) can download.
- Summary Reports
--- Processing Summary: Number of records received, by whom, on what dates  (Purpose: Alert admins that data coming in too slowly or that we are missing data from key partners)
 
INPUT VERSION 2:
- Bill: We need to think through how errors will be dealt with. Options include:
--- Fix and resubmit data
--- Let user send email explaining/fixing errors
--- Automatically fix some types of errors.  (??)
 
Since most of data SHOULD be automatically generated from admin. systems, I would expect very few errors, but I may be mistaken.  (Data collected through AFP survey is another matter.)
 
 
OUTPUT v2:
- More sophisticated Summary Reports -- pass my SQL Report Writer Tool queries to HTML generate tables.
 
Communication Protocol
Use SFTP on port 21 or 22?
Since the data is all at the organization level, there are NO names of specific donors so security really shouldn't be a big deal.
I was envisioning that they would have write-only access to a single directory.  (They do NOT get read or list access.)  Each software company would have its own ID that they would use in conjunction with a timestamp to identify their files.  Some of the companies want their clients to upload directly to us.  For these, the software company would be responsible for providing a client ID that would be automatically inserted into the file that their software creates and drops off for us.
Our server would be set up to poll the directory and process any new files it sees.  It will also email a 'receipt' acknowledging that the file was received.
1 - We will have a write-only directory available on one of our servers where files can be ftp'd.  It WILL be password-protected.  A script will be running continuously or, say, every 30 minutes to look for new files.
2 - Set up a single FTP account and make it available to all FEP participants.  For those building the upload capability into their applications, the account and password would be included in the application code or configuration file (so it can be customized should the account or url change).
File Headers
3 - File names will need to be unique.  How do we ensure this?  Here are a couple of options.  Let me know what you think works best:
- We could use organizations' EINs (federal employer ID numbers UNLESS several chapters or branches share the same EIN but provide separate uploads)
- Do you all have client IDs?  The files could contain the software developer name/ID + client ID + date + version (if multiple versions on same day -- if mistakes are made and data needs to be re-uploaded).  Thus, Blackbaud client # 100 might submit a file with a name like: BLKBD-0100-20070223-a.txt. Your software would need to handle the creation of the unique file name including the version#.
- If the file is directly from a software developer and containing records from multiple clients, then the client number would be set to '0000'.
4 - We should send an acknowledgement email to the client (and to the developer???). To do so, we need the clients email.  We could get this in one of several ways:
- You provide us with a list (difficult to manage since probably requires constant updating).
- Have users include a 2nd "header" file with client information in a standardized format. Each file could have the same root name but a 'INF' extension.
- Elaine (etapestry, 3/5): Add another row in the exported file that could include Contact Name, Contact Title, Contact Email, and Date Exported? This is something we could easily program on our end to include in the exported file. That way when you receive it on your end you can simply get our contact information in the file. The new row could be the first row in the exported file.
-
    filename: BLKBD-0100-20070223-a.txt
    CONTENTS
    ID:         0100
    name:    ABC Homeless Shelter
    email:    joe@abcHomeless.org
 
- "DataSourceType" field.  (Bill 3/6)
"D" - from software firm (i.e., nonprofit to software provider to FEP/NCCS)
"I" - from individual nonprofit using software from software provider.
"S" - from AFP's Websurveyor option completed online by nonprofit. 
 
- Require users to enter information on a web page. (Some will forget. Probably not a good choice.)
5 - Background script searches for new uploads. If found, it sends email to confirm receipt of file; checks data format; and loads data into database.  It could also generate summary tables or other output if necessary.