On Fri, May 7, 2010 at 10:18 AM, C. Hatton Humphrey <[email protected]>wrote:
>
> Okay folks, I've got an interesting Friday thought challenge for you all -
>
> We have a manual process that I need to work on automating. It
> involves the following:
> 1. Identify clients that have invoices posted in a week. If a client
> has a contact email, proceed.
> 2. Run between 2 and 4 different reports and save them in Excel using
> a distinct file name.
> 3. For some clients, convert some of the files from Excel to CSV.
> 4. Attach the files to an email and send to the contact email listed.
>
> Here are the mitigating factors:
> * Some clients need reports on different days.
> * Some clients only have invoices every two weeks.
>
> I already have step 1 covered with a query. The reports indicated in
> step 2 exist as SQL Reporting Services reports. My original plan was
> to have scheduled reports fire off based on a query every Friday that
> ID'd the clients with invoices that week and email an Excel file.
> Unfortunately, not every client gets every report.
>
> I'm simply trying to figure out how to architect this thing. Any
> suggestions?
>
>
I'd do something like this myself, may be a cleaner way but i think this
gets over many of the hurdles, of course I'm not sure about the converting
the excel into .cvs I would proboly <cfsqwtch>between file creation scripts
based on clients.rptFileType when creating the file.
tblClients:
clientID, email, rptFileType
tblRptSchedule:
clientID, runDayOfWeek, runFrequency, LastRunDate
tblReportTypes
reportId, ReportName
tblReportTypeToClient
Reportid, clientID
1. Select clients who have a runDate of today, where frequency matches up to
what the difference is between lastRunDate
2. Run a second query here in case you need to combine multiple reports in
the same invoice.
3. Create Rpt based on file extension from Clients table
4. Update RptSchedule.LastRunDate = NOW() where clientID = clients.clientID
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-community/message.cfm/messageid:317689
Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
