public bool CreateETLFiles(ETLExportDefinition exportDefinition, string exportFileLocation, string userName)
{
ETLData data = new ETLData(exportDefinition.ApplicationDatabase);
DateTime startDateTime = DateTime.Now;
try
{
foreach (ETLExportTablesDefinition table in exportDefinition.TableDefinitions)
{
//Retrieve DataSet for particular ETL Table
DataSet ds = new DataSet();
ds = data.GetETLData(table.ProcedureName, table.TableName);
if (ds != null)
{
//Save all files into temp area (in case one or more files fail)
string exportETLFileLocation = exportFileLocation + @"\" +
exportDefinition.ExportSubDirectory + @"\Temp\" +
table.ETLFileName.Replace(".csv", DateTimeSuffix());
CreateCSVFile(ds, exportETLFileLocation, table.ETLHeaderName);
}
}
//Move all files from temp area into pick-up area
foreach (string etlFile in Directory.GetFiles(exportFileLocation + @"\" +
exportDefinition.ExportSubDirectory + @"\Temp\"))
{
System.IO.File.Move(etlFile, etlFile.Replace(@"Temp\", ""));
}
////Now mark ETL as processed! - update _Status column of record
//data.UpdateETLDataStatus(exportDefinition.StatusProcedureName, (int)Common.Enums.ETLStatus.Success);
//Status update is done in Email method below!
//Email notification that file has been created....
Email.EmailETL email = new Email.EmailETL();
email.Email(exportDefinition, "");
//Record Import History
ETLData hubData = new ETLData("HUB");
hubData.RecordHistory(exportDefinition.ETLExportGUID, userName, startDateTime, true);
}
catch (Exception ex)
{
MGRELog.Write(ex);
//Now mark ETL as failed! - update _Status column of record
data.UpdateETLDataStatus(exportDefinition.StatusProcedureName, (int)Common.Enums.ETLStatus.Failed);
//Record Import History
ETLData hubData = new ETLData("HUB");
hubData.RecordHistory(exportDefinition.ETLExportGUID, userName, startDateTime, false);
throw ex;
}
return(true);
}
/// <summary>
/// Runs ETL Export Immediately for particular export name
/// </summary>
public void RunExportNow(string exportName)
{
if (exportName.Length == 0)
{
throw new ArgumentNullException("exportName");
}
//Retrieve GUID
ETLExportDefinition export = etlDAL.GetETLExportByExportName(exportName);
if (export == null)
{
throw new Exception("ETL Export does not exist - " + exportName);
}
}
/// <summary>
/// Runs ETL Export Immediately for particular export guid
/// </summary>
public void RunExportNow(Guid exportGUID)
{
if (exportGUID == Guid.Empty)
{
throw new ArgumentNullException("exportGUID");
}
//Retrieve GUID
ETLExportDefinition export = etlDAL.GetETLExportByExportGUID(exportGUID);
if (export == null)
{
throw new Exception("ETL Export does not exist - " + exportGUID.ToString());
}
RunExportNow(export);
}
/// <summary>
/// Runs ETL Export Immediately for particular export
/// </summary>
public void RunExportNow(ETLExportDefinition exportDefinition, string userName)
{
if (exportDefinition.ExportType == (int)MGRE.ETL.Common.Enums.ExportType.ETL)
{
Export.ETLCreate export = new Export.ETLCreate();
export.CreateETLFiles(exportDefinition, config.ETLExportDirectoryLocation, userName);
}
else if (exportDefinition.ExportType == (int)MGRE.ETL.Common.Enums.ExportType.Email)
{
Email.EmailETL email = new Email.EmailETL();
email.Email(exportDefinition, userName);
}
else if (exportDefinition.ExportType == (int)MGRE.ETL.Common.Enums.ExportType.EmailETL)
{
Export.ETLCreate export = new Export.ETLCreate();
export.CreateETLFilesForEmail(exportDefinition, config.ETLExportDirectoryLocation, userName);
}
}
public bool Email(ETLExportDefinition exportDefinition, string etlDirectory)
{
//Hold emails in collection so that we email all or nothing if an error is encountered.
List <MailMessageSender> emails = new List <MailMessageSender>();
ETLData data = new ETLData(exportDefinition.ApplicationDatabase);
try
{
foreach (ETLExportEmail email in exportDefinition.Emails)
{
MailMessageSender msg = new MailMessageSender();
msg.AppId = AppSettingsWrapper.applicationIdSetting.Value;
msg.FromAddress = email.FromAddress;
msg.Subject = email.Subject;
if (!email.EmailIsDataSpecific)
{
if (email.ToAssignees != null)
{
string[] tos = email.ToAssignees.Split(',');
foreach (string to in tos)
{
msg.Recipients.AddTO(to);
}
}
if (email.CCAssignees != null)
{
string[] ccs = email.CCAssignees.Split(',');
foreach (string cc in ccs)
{
msg.Recipients.AddCC(cc);
}
}
if (email.BCCAssignees != null)
{
string[] bccs = email.BCCAssignees.Split(',');
foreach (string bcc in bccs)
{
msg.Recipients.AddBCC(bcc);
}
}
}
if (etlDirectory.Length == 0)
{
//Data is explicitly listed in the email for someone to manually type in the data into Yardi Voyager
DataSet ds = new DataSet();
ds = data.GetETLData(email.ProcedureName, "EmailData");
if (ds != null)
{
//Send one email per row
//This only works for a flat structure table (not implemented a solution that will allow Parent - child relationship)
//In the case of Property and Job Cost from AIMS this can be explained in a flat structure table.
//An example that doesn't follow this is Capval Property Valuation and multiple unit ERV records.
//At present CapVal is not requesting an email solution, hopefully capval will only ever use an ETL solution that
//does not experience this issue. Not sure how an email solution would work anyway with regards an email solution -
//potentially a lot of data to manually key in!
foreach (DataRow row in ds.Tables[0].Rows)
{
if (email.EmailIsDataSpecific)
{
string[] tos = row["_EmailRecipients"].ToString().Split(',');
foreach (string to in tos)
{
msg.Recipients.AddTO(to);
}
//May need to add CCs here as well if developer adds a cc column?
}
msg.Body = ExamineData(row, email.Body);
msg.IsHtml = true;
emails.Add(msg);
}
}
}
else
{
//Attach ETL files to Email for someone to manually import the data via the ETL screens in Voyager
foreach (string etlFile in Directory.GetFiles(etlDirectory))
{
FileInfo file = new FileInfo(etlFile);
Attachment attach = new Attachment(etlDirectory, file.Name);
msg.Attachments.Add(attach);
}
msg.Body = email.Body;
msg.IsHtml = true;
emails.Add(msg);
}
}
foreach (MailMessageSender email in emails)
{
email.SendMail();
}
//Now mark ETL row as processed! - update _Status column of record
data.UpdateETLDataStatus(exportDefinition.StatusProcedureName, (int)Common.Enums.ETLStatus.Success);
}
catch (Exception ex)
{
MGRELog.Write(ex);
//Now mark ETL row as failed! - update _Status column of record
data.UpdateETLDataStatus(exportDefinition.StatusProcedureName, (int)Common.Enums.ETLStatus.Failed);
}
return(true);
}
public bool Email(ETLExportDefinition exportDefinition)
{
return(Email(exportDefinition, ""));
}
/// <summary>
/// Runs ETL Export Immediately for particular export
/// </summary>
public void RunExportNow(ETLExportDefinition exportDefinition)
{
Export.ETLCreate export = new Export.ETLCreate();
bool result = export.CreateETLFiles(exportDefinition);
}
