Freelancer

Description

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