/// <summary>
/// Executes scalar command
/// Uses PrimeDataAccess transaction if present but does not commit nor rollback
/// </summary>
/// <param name="dbCommandWrapper">command to execute</param>
/// <returns></returns>
protected object ExecuteScalar(DBCommandWrapper command)
{
object retVal = null;
try
{
LogCommand(command);
retVal = db.ExecuteScalar(command);
if (retVal == null)
{
MGRELog.Write("Returned: NULL");
}
else
{
MGRELog.Write("Returned: " + retVal.ToString());
}
}
catch (Exception ex)
{
HandleException(ex);
}
return(retVal);
}
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);
}
protected ApplicationException HandleException(Exception ex)
{
MGRELog.Write(ex);
if (ex is System.Data.DBConcurrencyException)
{
return(new ApplicationException(ex.Message, ex));
}
else
{
#if DEBUG //in debug mode return raw sql error
ApplicationException replaceEx = new ApplicationException(ex.Message, ex);
replaceEx.Source = "DataAccess";
#else
string msg = "A data error has occurred. Please report to the Service Desk";
if (ex.Message.StartsWith("Timeout"))
{
msg = "Timeout";
}
ApplicationException replaceEx = new ApplicationException(msg);
replaceEx.Source = "DataAccess";
#endif
return(replaceEx);
}
}
protected int UpdateDataSet(DataSet ds, string tableName, DBCommandWrapper inCommand, DBCommandWrapper upCommand, DBCommandWrapper deCommand)
{
int rowsAffected = 0;
try
{
rowsAffected = db.UpdateDataSet(ds, tableName, inCommand, upCommand, deCommand, UpdateBehavior.Transactional);
MGRELog.Write("Rows affected: " + rowsAffected.ToString());
}
catch (Exception ex)
{
HandleException(ex);
}
return(rowsAffected);
}
protected void LogCommand(DBCommandWrapper cmd)
{
string logMessage = "";
foreach (IDataParameter param in cmd.Command.Parameters)
{
if (param.Value == null)
{
logMessage += (logMessage == "" ? "" : ",") + "Null";
}
else
{
logMessage += (logMessage == "" ? "" : ",") + param.Value.ToString();
}
}
logMessage = "Calling " + cmd.Command.CommandText + " " + logMessage;
MGRELog.Write(logMessage);
}
protected void LogCommand(string spName, params object[] parameters)
{
string logMessage = "";
foreach (object obj in parameters)
{
if (obj == null)
{
logMessage += (logMessage == "" ? "" : ",") + "Null";
}
else
{
logMessage += (logMessage == "" ? "" : ",") + obj.ToString();
}
}
logMessage = "Calling " + spName + " " + logMessage;
MGRELog.Write(logMessage);
}
public DualAppShortcutSetting GetSettings(int appID)
{
try
{
MGRE.ETL.Data.Contracts.IETLDAL etlDAL = new Data.ETLData();
DualAppBO bo = new DualAppBO(etlDAL);
return(bo.GetSettings(appID));
}
catch (MGREException vex)
{
MGRELog.Write(vex);
throw new FaultException <MGREExceptionData>(vex.AppError, new FaultReason(vex.Message));
}
catch (Exception ex)
{
MGRELog.Write(ex);
throw;
}
}
public void RunImportNowByGUID(Guid importGUID)
{
try
{
MGRE.ETL.Data.Contracts.IETLDAL etlDAL = new Data.ETLData();
ETLImportBO bo = new ETLImportBO(etlDAL);
bo.RunImportNow(importGUID, System.ServiceModel.ServiceSecurityContext.Current.WindowsIdentity.Name);
}
catch (MGREException vex)
{
MGRELog.Write(vex);
throw new FaultException <MGREExceptionData>(vex.AppError, new FaultReason(vex.Message));
}
catch (Exception ex)
{
MGRELog.Write(ex);
throw;
}
}
public List <vw_FundsAndProperties> GetFundsAndProperties()
{
try
{
MGRE.ETL.Data.Contracts.IETLDAL etlDAL = new Data.ETLData();
DualAppBO bo = new DualAppBO(etlDAL);
return(bo.GetFundsAndProperties());
}
catch (MGREException vex)
{
MGRELog.Write(vex);
throw new FaultException <MGREExceptionData>(vex.AppError, new FaultReason(vex.Message));
}
catch (Exception ex)
{
MGRELog.Write(ex);
throw;
}
}
protected void HandleException(Exception ex)
{
MGRELog.Write(ex);
string msg = "A data error has occurred. Please report to the Service Desk";
if (ex.Message.StartsWith("Timeout"))
{
msg = "Timeout";
}
MGREException replaceEx = MGRELog.GetDerivedException(ex, msg);
replaceEx.Source = "DataAccess";
#if DEBUG //in debug mode return raw sql error
throw ex;
#else
throw replaceEx;
#endif
}
/// <summary>
/// Execute a given stored proc - with params - as a non-query
/// Uses PrimeDataAccess transaction if present but does not commit nor rollback
/// </summary>
protected int ExecuteStoredProcAsNonQuery(string spName, params object[] parameters)
{
int retVal = 0;
try
{
LogCommand(spName, parameters);
DBCommandWrapper command = db.GetStoredProcCommandWrapper(spName, parameters);
db.ExecuteNonQuery(command);
retVal = command.RowsAffected;
MGRELog.Write("Rows Affected: " + retVal.ToString());
}
catch (Exception ex)
{
HandleException(ex);
}
return(retVal);
}
protected BaseData()
{
try
{
if (connectionString == "")
{//use the enterprise library config to get a connection string
MGRELog.Write("Setting connection string");
Database db = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase("HUB");
System.Data.IDbConnection cnn = db.GetConnection();
connectionString = db.GetConnection().ConnectionString;
cnn.Dispose();
MGRELog.Write("Connection string set");
}
EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
//Set the provider name.
entityBuilder.Provider = "System.Data.SqlClient";
// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = connectionString;
// Set the Metadata location.
entityBuilder.Metadata = @"res://*/ETL.csdl|
res://*/ETL.ssdl|
res://*/ETL.msl";
entConn = new EntityConnection(entityBuilder.ToString());
dataContext = new ETLEntities(entConn);
}
catch (Exception ex)
{
HandleException(ex);
}
}
/// <summary>
/// Load a given dataset, using the supplied stored proc (with parameters) and use the list of table names to dictate the order in wchi the proc supplies the data for the tables
/// Uses PrimeDataAccess transaction if present but does not commit nor rollback
/// Throws exception without
/// </summary>
protected void LoadDataSetFromSql(string sql, DataSet ds, string[] tableNames)
{
try
{
MGRELog.Write("Sql:" + sql);
DBCommandWrapper command = db.GetSqlStringCommandWrapper(sql);
db.LoadDataSet(command, ds, tableNames);
string logMessage = "";
foreach (DataTable tbl in ds.Tables)
{
logMessage += tbl.TableName + " rows:" + tbl.Rows.Count.ToString() + Environment.NewLine;
}
MGRELog.Write(logMessage);
}
catch (Exception ex)
{
HandleException(ex);
}
}
private void button2_Click(object sender, RoutedEventArgs e)
{
try
{
MGRE.ETL.Data.Contracts.IETLDAL etlDAL = new Data.ETLData();
//ETLExportBO bo = new ETLExportBO(etlDAL);
//bo.RunExportNow(importName, "deanc");
DualAppBO bo = new DualAppBO(etlDAL);
DualAppShortcutSetting settings = bo.GetSettings(127);
}
catch (MGREException vex)
{
MGRELog.Write(vex);
//throw new FaultException<MGREExceptionData>(vex.AppError, new FaultReason(vex.Message));
}
catch (Exception ex)
{
MGRELog.Write(ex);
throw;
}
}
private void RunExportNowByName(string importName)
{
try
{
MGRE.ETL.Data.Contracts.IETLDAL etlDAL = new Data.ETLData();
ETLExportBO bo = new ETLExportBO(etlDAL);
bo.RunExportNow(importName, "deanc");
//ETLImportBO boImp = new ETLImportBO(etlDAL);
//boImp.RunImportNow("CADFinTxn", "deanc");
}
catch (MGREException vex)
{
MGRELog.Write(vex);
//throw new FaultException<MGREExceptionData>(vex.AppError, new FaultReason(vex.Message));
}
catch (Exception ex)
{
MGRELog.Write(ex);
throw;
}
}
/// <summary>
/// Load a given dataset, using the supplied stored proc (with parameters) and use the list of table names to dictate the order in wchi the proc supplies the data for the tables
/// Uses PrimeDataAccess transaction if present but does not commit nor rollback
/// Throws exception without
/// </summary>
protected void LoadDataSet(string spName, DataSet ds, string[] tableNames, params object[] parameters)
{
try
{
LogCommand(spName, parameters);
DBCommandWrapper command = db.GetStoredProcCommandWrapper(spName, parameters);
db.LoadDataSet(command, ds, tableNames);
string logMessage = "";
foreach (DataTable tbl in ds.Tables)
{
logMessage += tbl.TableName + " rows:" + tbl.Rows.Count.ToString() + Environment.NewLine;
}
MGRELog.Write(logMessage);
}
catch (Exception ex)
{
HandleException(ex);
}
}
public ETLData(string applicationDatabase)
: base(applicationDatabase)
{
MGRELog.Write("Connected to " + applicationDatabase + " database", "HubData.Constructor");
}
public bool ImportETLFile(ETLImportDefinition importDefinition, string importFileLocation, string userName)
{
ETLData data = new ETLData("HUB");
DateTime startDateTime = DateTime.Now;
int lineNo = 1;
List <string> tempDataValues = new List <string>();
bool dataHeldOverMultipleLines = false;
try
{
foreach (ETLImportTablesDefinition table in importDefinition.TableDefinitions)
{
//Locate csv file
string filePath = importFileLocation + @"\" + importDefinition.ImportSubDirectory + @"\" + table.ETLFileName;
string sFileContents = "";
using (StreamReader oStreamReader = new StreamReader(File.OpenRead(filePath)))
{
sFileContents = oStreamReader.ReadToEnd();
}
//Replace any carriage Returns conatined within "s.
sFileContents = Regex.Replace(sFileContents,
@"\r\n(?=[^""]*""(?:[^""]*""[^""]*"")*[^""]*$)",
String.Empty);
DataSet tableToPopulate = new DataSet();
tableToPopulate = data.GetTableDataSet(table.TableName, table.SelectProcedureName);
//List<CSVData> csvList = new List<CSVData>();
string[] fileLines = sFileContents.Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
List <string> columnNames = new List <string>();
int dataStartLineNo = table.LineDataStarts;
int footerLineCount = table.FooterLineCount;
foreach (string line in fileLines)
{
//Could be a case (especially decimal values) whereby a comma (thousand delimeter) can be in the file
//e.g. "-28,000" - In these cases remove any commas that exist between "s.
string fileLine = Regex.Replace(line,
@",(?=[^""]*""(?:[^""]*""[^""]*"")*[^""]*$)",
String.Empty);
if (lineNo == 1)
{
//Check header is correct
if (fileLine.Replace(",", "").Trim().ToUpper() != table.ETLHeaderName.ToUpper())
{
throw new MGREException("ETL file header is incorrect : " + fileLine.Trim().ToUpper());
}
}
else if (lineNo == dataStartLineNo)
{
//Retrieve column names and hold in array
columnNames = fileLine.Split(",".ToCharArray()).ToList <string>();
}
else if (lineNo == fileLines.Count() - footerLineCount)
{
//End of file exit loop
break;
}
else if (lineNo > dataStartLineNo)
{
List <string> dataValues = new List <string>();
//Build up CSVData list recording column Name, Data Value and row number
if (!dataHeldOverMultipleLines)
{
tempDataValues.Clear();
dataValues = fileLine.Split(",".ToCharArray()).ToList <string>();
}
else
{
int j = 0;
foreach (string l in fileLine.Split(",".ToCharArray()))
{
if (j == 0)
{
tempDataValues[tempDataValues.Count - 1] = tempDataValues[tempDataValues.Count - 1] + " " + l;
}
else
{
tempDataValues.Add(l);
}
j++;
}
}
if (columnNames.Count != tableToPopulate.Tables[table.TableName].Columns.Count)
{
throw new MGREException("Number of columns in ETL file do not match number of columns in database table : " + table.TableName);
}
if (dataValues.Count != columnNames.Count && tempDataValues.Count != columnNames.Count)
{
//We have an issue where data is held over multiple lines (by carriage returns), most likely candidate is notes
//In this case we need to cache the current datavalues and append subsequent data until we get the complete line of data.
if (!dataHeldOverMultipleLines)
{
tempDataValues = dataValues;
}
dataHeldOverMultipleLines = true;
}
else
{
if (dataHeldOverMultipleLines)
{
dataValues = tempDataValues;
}
int columnNo = 0;
DataRow row = tableToPopulate.Tables[table.TableName].NewRow();
foreach (string dataValue in dataValues)
{
DataColumn col = tableToPopulate.Tables[table.TableName].Columns[columnNames[columnNo].Trim()];
if (col == null)
{
throw new MGREException("Possible ETL Schema Change, column not found : " + columnNames[columnNo].Trim());
}
row[columnNames[columnNo].Trim()] = SetValue(dataValue, col.DataType);
columnNo++;
}
tableToPopulate.Tables[table.TableName].Rows.Add(row);
dataHeldOverMultipleLines = false;
}
}
lineNo++;
}
if (table.DeleteProcedureName.Length > 0)
{
//Clear out table before populating
data.DeleteData(table.TableName, table.DeleteProcedureName);
}
data.InsertImportData(tableToPopulate, table.TableName, table.InsertProcedureName);
}
//Record Import History
data.RecordHistory(importDefinition.ETLImportGUID, userName, startDateTime, true);
//Move file to Success Folder
foreach (ETLImportTablesDefinition table in importDefinition.TableDefinitions)
{
string filePath = importFileLocation + @"\" + importDefinition.ImportSubDirectory + @"\" + table.ETLFileName;
string successPath = importFileLocation + @"\" + importDefinition.ImportSubDirectory + @"\Success\" + table.ETLFileName.Replace(".csv", startDateTime.Year.ToString() + startDateTime.Month.ToString() + startDateTime.Day.ToString() + startDateTime.Hour.ToString() + startDateTime.Minute.ToString() + ".csv");
System.IO.File.Move(filePath, successPath);
}
return(true);
}
catch (Exception ex)
{
MGRELog.Write(ex);
//Record Import History
data.RecordHistory(importDefinition.ETLImportGUID, userName, startDateTime, false);
//Move file to Fail Folder
foreach (ETLImportTablesDefinition table in importDefinition.TableDefinitions)
{
string filePath = importFileLocation + @"\" + importDefinition.ImportSubDirectory + @"\" + table.ETLFileName;
string failPath = importFileLocation + @"\" + importDefinition.ImportSubDirectory + @"\Fail\" + table.ETLFileName.Replace(".csv", startDateTime.Year.ToString() + startDateTime.Month.ToString() + startDateTime.Day.ToString() + startDateTime.Hour.ToString() + startDateTime.Minute.ToString() + ".csv");
System.IO.File.Move(filePath, failPath);
}
throw ex;
}
}
public AuthorisedService()
{
MGREServerLog.AttachToLoggingHandler();
MGRELog.WriteMethodStart("AuthorisedService()");
try
{
userName = System.ServiceModel.ServiceSecurityContext.Current.WindowsIdentity.Name;
MGRELog.Write("UserName:"******"";
object cacheUser;
System.Data.DataSet ds;
string[] segments = userName.Split('\\');
if (segments.Length > 1)
{
userName = segments[1];
}
userDsKey = "UserDs" + appId.ToString() + userName;
MGRELog.Write("userDsKey:" + userDsKey);
cacheUser = MemoryCache.Default.Get(userDsKey);
if (cacheUser != null)
{
MGRELog.Write("User in cache");
userDs = (PruPIM.Common.User.DataSets.UserDataSet)cacheUser;
}
else
{
MGRELog.Write("Querying user from ic");
PruPIM.Common.User.Business.Rules.UserBusinessRule user = new PruPIM.Common.User.Business.Rules.UserBusinessRule(TraceLevel.Error);
ds = user.GetUser(userName, appId);
MGRELog.WriteDataSet(ds);
userDs = new PruPIM.Common.User.DataSets.UserDataSet();
userDs.Merge(ds);
CacheItemPolicy cachePolicy = new CacheItemPolicy();
cachePolicy.AbsoluteExpiration = DateTime.Now.AddMinutes(1);
cachePolicy.Priority = System.Runtime.Caching.CacheItemPriority.Default;
MemoryCache.Default.Add(userDsKey, userDs, cachePolicy);
}
if (userDs.Users.Count == 0)
{
throw new System.Security.SecurityException("No valid user authenticated. Impersonation may not be setup on the web server.");
}
if (userDs.AllowedScreens.Count == 0)
{
throw new System.Security.SecurityException("No access to EWatch");
}
}
catch (Exception ex)
{
MGRELog.Write(ex);
throw;
}
}
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);
}
