Upload Email List and Add to CMSms FEU Database

My environment is running CMS Made Simple and its Front End Users Made Simple extension. I want to build a custom module that can be added to CMSms, that has three elements: 1. Admin interface to register new file types (see below Admin Interface) 2. Email List Transformation tool (see below Email Transformation) 3. FEU Loader that will add/modify data into the FEU database, based on the Email Transformation -- note this utility already exists, and you may simply tell me to use it rather than building your own :) Right Freelancer has good knowledge of CMSms, Opening Email Messages from batch, detaching attachments, converting worksheets into CSV using OpenOffice or other freely available scripts/tools ... 1. Admin Interface ================== The following form would be needed: ----------------------------------------------------------------- Add Email Translation Rule ----------------------------------------------------------------- Rule/Source Name: _______________________ Rule Type: (drop down) Match Sender Match Sender & Subject Match Sender & Subject & FileName Match Sender & Partial Subject & Partial FileName Ajax Fields: Sender: _______________ Subject: _______________ (only displayed for #2 and #3) Partial Subject: __________ (only displayed for #4) File Name: ______________ (only displayed for #3) Partial File Name: _________ (only displayed for #4) ----------------------------------------------------------------- Translation Setup: First Data Row : ___ First Name Column # : ___ Last Name Column # : ____ Email Column # : ____ . . Note lots of fields . User Defined #1 Column # : ___ . . Up to 10 User Def Fields . User Defined #10 Column # : ___ ----------------------------------------------------------------- These inputs will be added into a table, and later used by the Transformation Engine, depending on which rule, the rules to match how this email will be translated comes from matching (1) sender+partial subject+partial filename and if not found then (2) matching sender+subject+filename and if not found then (3) sender+subject and if not found then (4) matching sender only The other setup fields are used once you open the attachment as translation rules (which row do I start reading from, which column contains the first name, which column contains the last name, etc) 2. Email Transformation ======================= So, we're building a CRON job (or something) that is opening a mailbox and determining if there are any new email messages. If any are found, the attachment(s) need to be pulled from the email and transformed. If the attachment is an XLS or XLSX file, it needs to be converted to a CSV. If the XLS or XLSX file has multiple workbooks, only the last workbook needs to be converted. My thoughts from a pseudo code perspective: Open the Admin Database Select * from Admin Database Build an array (Sender, Subject, Filename, firstrec_position, fname_position, lname_position .(lots of fields)..email_position, user1_position, user2_position ..lots of fields.. user10_position, source_name)Close Admin Database Open Output File Open Report Writer Open the mailbox Do until new_messages = 0 Next Message Set Sender = sender() Trim "re:" and "fw:" etc from Subject Set Subject = subject() Do until attachments = 0 Extract attachment (if excel with multiple worksheets, open only last worksheet) Search admin_array for match to sender and partial subject and partial filename Else Search admin_array for match to sender and subject and filename Else Search Admin_array for match to sender and subject Else Search Admin_array for match to sender Else Trigger Error Message "Sender=sender, subject=subject, filename=filename -- no array match" For x=1 to firstrec Read attachment row to ignore junk/header End Do until eof Read attachment row fname = csv column that matches Admin_array fname_position lname = csv column that matches Admin_array lname_position . . . email = csv column that matches Admin_array email_position user1 = csv column that matches Admin_array user1_position . . . user10 = csv column that matches Admin_array user10_position if Admin_array lname_positon = 0 then assume we have to split fname into fname||blank||lname Write Output File source_name, fname, lname, ... email, user1 ... user10 Write message to report writer End End End Close the mailbox Close the output file Close the report writer and send as email to admin Exit