/// <summary>
/// This method will get row(s) from the database using the value of the field specified
/// along with the details of the child table.
/// </summary>
///
/// <param name="pk" type="AdvancedImportJobPrimaryKey">Primary Key information based on which data is to be fetched.</param>
///
/// <returns>object of class AdvancedFieldMaps</returns>
///
/// <remarks>
///
/// <RevisionHistory>
/// Author Date Description
/// DLGenerator 7/4/2012 2:40:13 PM Created function
///
/// </RevisionHistory>
///
/// </remarks>
///
public static AdvancedFieldMaps SelectAllByForeignKeyFromAdvancedImportJob(AdvancedImportJobPrimaryKey pk)
{
DatabaseHelper oDatabaseHelper = new DatabaseHelper();
bool ExecutionState = false;
AdvancedFieldMaps obj = null;
// Pass the values of all key parameters to the stored procedure.
System.Collections.Specialized.NameValueCollection nvc = pk.GetKeysAndValues();
foreach (string key in nvc.Keys)
{
oDatabaseHelper.AddParameter("@" + key,nvc[key] );
}
// The parameter '@ErrorCode' will contain the status after execution of the stored procedure.
oDatabaseHelper.AddParameter("@ErrorCode", -1, System.Data.ParameterDirection.Output);
IDataReader dr=oDatabaseHelper.ExecuteReader("sp_AdvancedFieldMap_SelectAllByForeignKeyAdvancedImportJob", ref ExecutionState);
obj = new AdvancedFieldMaps();
obj = AdvancedFieldMap.PopulateObjectsFromReaderWithCheckingReader(dr, oDatabaseHelper);
dr.Close();
oDatabaseHelper.Dispose();
return obj;
}
/// <summary>
/// This method will get row(s) from the database using the value of the field specified
/// along with the details of the child table.
/// </summary>
///
/// <param name="pk" type="AdvancedImportJobPrimaryKey">Primary Key information based on which data is to be fetched.</param>
///
/// <returns>object of class AdvancedImportJob</returns>
///
/// <remarks>
///
/// <RevisionHistory>
/// Author Date Description
/// DLGenerator 7/4/2012 2:42:12 PM Created function
///
/// </RevisionHistory>
///
/// </remarks>
///
public static AdvancedImportJob SelectOneWithAdvancedFieldMapUsingImportJobID(AdvancedImportJobPrimaryKey pk, string ConnectionString)
{
DatabaseHelper oDatabaseHelper = new DatabaseHelper();
bool ExecutionState = false;
AdvancedImportJob obj=null;
// Pass the values of all key parameters to the stored procedure.
System.Collections.Specialized.NameValueCollection nvc = pk.GetKeysAndValues();
foreach (string key in nvc.Keys)
{
oDatabaseHelper.AddParameter("@" + key,nvc[key] );
}
// The parameter '@ErrorCode' will contain the status after execution of the stored procedure.
oDatabaseHelper.AddParameter("@ErrorCode", -1, System.Data.ParameterDirection.Output);
IDataReader dr=oDatabaseHelper.ExecuteReader("sp_AdvancedImportJob_SelectOneWithAdvancedFieldMapUsingImportJobID", ref ExecutionState);
if (dr.Read())
{
obj = new AdvancedImportJob(ConnectionString);
PopulateObjectFromReader(obj,dr);
dr.NextResult();
//Get the child records.
obj.AdvancedFieldMaps=AdvancedFieldMap.PopulateObjectsFromReader(dr);
}
dr.Close();
oDatabaseHelper.Dispose();
return obj;
}
public void ProcessSingleAdvancedImportRecipients(DataRow dr)
{
bool isFirstRowHeader = false;
bool isSendNotificationEmail = false;
char[] delimiterChar = null;
DataTable dtFileData = null;
string[] strFileData = null;
StreamReader sr = null;
int JobID = 0;
try
{
Logger.logdata(logforimportrecipients, "************** in ProcessSingleAdvancedImportRecipients(..) method **************");
Logger.logdata(logforimportrecipients, string.Format("ProcessSingleAdvancedImportRecipients(..) has been started at {0}", DateTime.Now.ToString()));
Logger.logdata(logforimportrecipients, string.Format("Import process satarted for job with ID: {0} at {1}", dr["ID"].ToString(), DateTime.Now.ToString()));
//Get the filename of imported file.
string filename = dr["GuidFilename"].ToString();
ImportJobID = dr["ID"].ToString();
DateTime importStartTime = DateTime.Now;
if (File.Exists(Path.Combine(importFilePath, filename)))
{
//update import job status
AdvancedImportJobPrimaryKey objAdvancedImportJobPrimaryKey = new AdvancedImportJobPrimaryKey(Convert.ToInt64(ImportJobID));
AdvancedImportJob objAdvancedImportJob = AdvancedImportJob.SelectOne(objAdvancedImportJobPrimaryKey, connectionString);
objAdvancedImportJob.Status = Common.ImportStatus.InProcess.ToString();
//AdvancedImportJob obj = new AdvancedImportJob(connectionString);
//obj.Update();
objAdvancedImportJob.Update();
//Insert Import History
ReplyPositiveBL.AdvancedImportHistory objImportHistory = new ReplyPositiveBL.AdvancedImportHistory(connectionString);
objImportHistory.ImportJobID = Convert.ToInt64(ImportJobID);
objImportHistory.Staus = "Processing";
objImportHistory.StatusMessage = "File import is in processing.";
//objImportHistory.UpdatedCount = updatedCount;
//objImportHistory.InsertedCount = insertedCount;
objImportHistory.StartTime = importStartTime;
//objImportHistory.EndTime = DateTime.Now;
objImportHistory.InsertandReturnID(ref JobID);
isSendNotificationEmail = Convert.ToBoolean(dr["Notification"].ToString());
isFirstRowHeader = Convert.ToBoolean(dr["IsFileHasHeader"].ToString());
delimiterChar = getSeparatorChar(dr["FileType"].ToString());
Logger.logdata(logforimportrecipients, string.Format("import filepath: {0}", Path.Combine(importFilePath, filename)));
//Read the import file content.
FileStream Stream = new FileStream(Path.Combine(importFilePath, filename), FileMode.Open, FileAccess.Read);
Logger.logdata(logforimportrecipients, string.Format("stream length: {0}", Stream.Length));
//convert file stream to datatable.
if (dr["FileType"].ToString().Equals("Excel(.xls, .xlsx)"))
{
//Commented By swaroop June7-12
dtFileData = getDataTableOfExcel(Stream, filename, false);
//dtFileData = getDataTableOfExcel(Stream, filename, isFirstRowHeader);
}
else
{
sr = new StreamReader(Stream);
strFileData = sr.ReadToEnd().Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
dtFileData = convertFileToDatatable(strFileData, isFirstRowHeader, delimiterChar);
}
//remove header from datatable if first row is header.
if (isFirstRowHeader)
{
dtFileData.Rows.RemoveAt(0);
}
//get fieldmap
sqlQry = "select * from AdvancedFieldMap where ImportJobID =" + ImportJobID;
ds = getDataset(sqlQry);
DataTable dtFieldMap = ds.Tables[0];
//The following code is for removing Unmapped columns while importing a file to List
if (dtFileData.Columns.Count != dtFieldMap.Rows.Count)
{
string[] ActualColumns = (from dc in dtFileData.Columns.Cast<DataColumn>() select dc.ColumnName).ToArray();
string[] MappedColumns = dtFieldMap.AsEnumerable().Select(row => row.Field<string>("SourceField")).ToArray();
string[] UnMappedColumns = ActualColumns.Except(MappedColumns).ToArray();
foreach (string clm in UnMappedColumns)
{
if (dtFileData.Columns.Contains(clm))
dtFileData.Columns.Remove(clm);
}
}
// process file data
if (dtFileData.Rows.Count > 0)
{
Logger.logdata(logforimportrecipients, string.Format("total number of records found in imported file: {0}", dtFileData.Rows.Count));
string emailAddressColumn = dtFieldMap.Select("DestinationField='EmailAddress'")[0]["SourceField"].ToString();
string purlColumn =dtFieldMap.Select("DestinationField='Purl'")[0]["SourceField"].ToString();
dtFileData = RemoveDuplicateRows(dtFileData,purlColumn, dtFieldMap);
Logger.logdata(logforimportrecipients, string.Format("valid recipients count in selected file: {0}", dtFileData.Rows.Count));
int totalRecipientsFound = dtFileData.Rows.Count;
int validRecipientsFound = dtFileData.Rows.Count;
int inValidRecipientsFound = totalRecipientsFound - validRecipientsFound;
int insertedCount = 0;
int updatedCount = 0;
if (dtFileData.Rows.Count == 0)
{
//throw new Exception("Please select proper file to import.");
}
//upsert into RecipientMaster and listcontactmaster based on importlimit.
var AllRecords = from item in dtFileData.AsEnumerable() select item;
Logger.logdata(logforimportrecipients, string.Format("all records count in selected file: {0}", AllRecords.Count()));
int nextRecords = 0;
int importLimit = Convert.ToInt32(ConfigurationManager.AppSettings["ImportRecipientsLimit"].ToString());
Logger.logdata(logforimportrecipients, string.Format("Import limit: {0}", importLimit));
while (true)
{
Logger.logdata(logforimportrecipients, string.Format("looping records: {0}", nextRecords));
var top1000 = AllRecords.Skip(nextRecords).Take(importLimit);
if (top1000.Count() == 0)
{
break;
}
else
{
DataTable dtContacts = dtFileData.Clone();
top1000.CopyToDataTable(dtContacts, LoadOption.PreserveChanges);
DataTable dtListContactMasterResult = ImportRecordsToRecipientMaster(dtContacts, dtFieldMap, dr);
insertedCount += Convert.ToInt32(dtListContactMasterResult.Compute("Count(Status)", "Status='Inserted' and ListMasterID=" + Convert.ToInt64(dr["ListID"].ToString())));
updatedCount += Convert.ToInt32(dtListContactMasterResult.Compute("Count(Status)", "Status='Updated' and ListMasterID=" + Convert.ToInt64(dr["ListID"].ToString())));
nextRecords = nextRecords + importLimit;
}
}
Logger.logdata(logforimportrecipients, string.Format("total number of records inserted: {0}", insertedCount));
Logger.logdata(logforimportrecipients, string.Format("total number of records found updated: {0}", updatedCount));
//build html string to send notification email.
//get listname of listid``````````````
AdvanceListMasterPrimaryKey objListMasterPrimaryKey = new AdvanceListMasterPrimaryKey(Convert.ToInt64(dr["ListID"]));
AdvanceListMaster objListmaster = AdvanceListMaster.SelectOne(objListMasterPrimaryKey, connectionString);
StringBuilder objStringBuilder = new StringBuilder();
objStringBuilder.Append("<table width=100%>");
objStringBuilder.Append("<tr><td>" + string.Format("Imported file: <b>{0}</b>.", dr["Filename"].ToString()) + "</td></tr>");
objStringBuilder.Append("<tr><td>" + string.Format("Total number of recipients found: <b>{0}</b>.", totalRecipientsFound) + "</td></tr>");
objStringBuilder.Append("<tr><td>" + string.Format("Number of valid recipients found: <b>{0}</b>.", validRecipientsFound) + "</td></tr>");
objStringBuilder.Append("<tr><td>" + string.Format("Number of InValid recipients found: <b>{0}</b>.", inValidRecipientsFound) + "</td></tr>");
objStringBuilder.Append("<br>");
objStringBuilder.Append("<br>");
objStringBuilder.Append("<tr><td><b>ListName</b></td><td><b>Inserted</b></td><td><b>Updated</b></td>");
objStringBuilder.Append("<tr><td colspan=3><hr></td></tr>");
objStringBuilder.Append("<tr><td>" + objListmaster.ListName + "</td><td>" + insertedCount + "</td><td>" + updatedCount + "</td></tr>");
//update import job status
//ImportJobPrimaryKey
objAdvancedImportJobPrimaryKey = new AdvancedImportJobPrimaryKey(Convert.ToInt64(ImportJobID));
//ImportJob
objAdvancedImportJob = AdvancedImportJob.SelectOne(objAdvancedImportJobPrimaryKey, connectionString);
objAdvancedImportJob.Status = Common.ImportStatus.Success.ToString();
objAdvancedImportJob.Update();
//Insert Import History
//ReplyPositiveBL.ImportHistory
objImportHistory = new ReplyPositiveBL.AdvancedImportHistory(connectionString);
objImportHistory.ImportJobID = Convert.ToInt64(ImportJobID);
objImportHistory.Staus = "SUCCESS";
objImportHistory.StatusMessage = "File import process completed successfully.";
objImportHistory.UpdatedCount = updatedCount;
objImportHistory.InsertedCount = insertedCount;
objImportHistory.InvalidCount = inValidRecipientsFound;
objImportHistory.StartTime = importStartTime;
objImportHistory.ID = JobID;
objImportHistory.EndTime = DateTime.Now;
objImportHistory.Update();
//send notification email.
if (isSendNotificationEmail)
sendNotificationMail(objStringBuilder.ToString(), dr["NotificationEmailIDs"].ToString(), filename);
}
}
Logger.logdata(logforimportrecipients, string.Format("Import process ended for job with ID: {0} at {1}", dr["ID"].ToString(), DateTime.Now.ToString()));
Logger.logdata(logforimportrecipients, string.Format("ProcessSingleAdvancedImportRecipients(..) method has been ended at {0}", DateTime.Now.ToString()));
Logger.logdata(logforimportrecipients, "************** Exit ProcessSingleImportRecipients(..) method **************");
}
catch (Exception ex)
{
//update import job status
ImportJobPrimaryKey objImportJobPrimaryKey = new ImportJobPrimaryKey(Convert.ToInt64(ImportJobID));
ImportJob objImportJob = ImportJob.SelectOne(objImportJobPrimaryKey, connectionString);
objImportJob.Status = Common.ImportStatus.Error.ToString();
objImportJob.Update();
throw ex;
}
}
/// <summary>
/// This method will Delete one row from the database using the primary key information
/// </summary>
///
/// <param name="pk" type="AdvancedImportJobPrimaryKey">Primary Key information based on which data is to be fetched.</param>
///
/// <returns>True if succeeded</returns>
///
/// <remarks>
///
/// <RevisionHistory>
/// Author Date Description
/// DLGenerator 7/4/2012 2:42:12 PM Created function
///
/// </RevisionHistory>
///
/// </remarks>
///
public static bool Delete(AdvancedImportJobPrimaryKey pk, string ConnectionString)
{
DatabaseHelper oDatabaseHelper = new DatabaseHelper(ConnectionString);
bool ExecutionState = false;
// Pass the values of all key parameters to the stored procedure.
System.Collections.Specialized.NameValueCollection nvc = pk.GetKeysAndValues();
foreach (string key in nvc.Keys)
{
oDatabaseHelper.AddParameter("@" + key,nvc[key] );
}
// The parameter '@ErrorCode' will contain the status after execution of the stored procedure.
oDatabaseHelper.AddParameter("@ErrorCode", -1, System.Data.ParameterDirection.Output);
oDatabaseHelper.ExecuteScalar("sp_AdvancedImportJob_Delete", ref ExecutionState);
oDatabaseHelper.Dispose();
return ExecutionState;
}
