//this method is used to insert the recipients of a single ImportJob into ListContactMaster and ContactMaster table
public void ProcessSingleImportRecipients(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 ProcessSingleImportRecipients(..) method **************");
Logger.logdata(logforimportrecipients, string.Format("ProcessSingleImportRecipients(..) 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
ImportJobPrimaryKey objImportJobPrimaryKey = new ImportJobPrimaryKey(Convert.ToInt64(ImportJobID));
ImportJob objImportJob = ImportJob.SelectOne(objImportJobPrimaryKey, connectionString);
objImportJob.Status = Common.ImportStatus.InProcess.ToString();
objImportJob.Update();
//Insert Import History
ReplyPositiveBL.ImportHistory objImportHistory = new ReplyPositiveBL.ImportHistory(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)"))
{
if (filename.EndsWith(".xls"))
dtFileData = getDataTableOfExcel(Stream, filename, false);
else
{
string path = Path.Combine(importFilePath, filename);
System.Data.DataTable Contacts = new System.Data.DataTable();
ReadXlsx.Application xlApp;
ReadXlsx.Workbook xlWorkBook;
ReadXlsx.Worksheet xlWorkSheet;
xlApp = new ReadXlsx.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, ReadXlsx.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (ReadXlsx.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int r;
int c;
int intRows;
int intCols;
ReadXlsx.Range excelCell = xlWorkSheet.UsedRange;
Object[,] values = (Object[,])excelCell.Value2;
intRows = values.GetLength(0);
if (intRows != 0)
{
intCols = values.GetLength(1);
if (intCols != 0)
{
for (c = 1; c <= intCols; c++)
{
Contacts.Columns.Add(new DataColumn("Column" + c));
}
for (r = 1; r <= intRows; r++)
{
DataRow tblRow = Contacts.NewRow();
for (c = 0; c < Contacts.Columns.Count; c++)
{
tblRow[c] = (values[r, (c + 1)] == null) ? string.Empty : values[r, (c + 1)];
}
Contacts.Rows.Add(tblRow);
}
}
}
dtFileData = Contacts;
xlWorkSheet = null;
xlWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
xlWorkBook = null;
xlApp.Quit();
xlApp = null;
}
}
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 Fieldmap 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);
}
}
//Bulk insert the import file content in to temp table.
//insertImportingContactsInToTemp(dtFileData, dtFieldMap, ImportJobID);
//Logger.logdata(logforimportrecipients, string.Format("imported file records are bulk inserted to temp table: {0}", dtFileData.Rows.Count));
// 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();
var v = from item in dtFieldMap.AsEnumerable() where item["DestinationField"].ToString() == "IsActive" select item;
foreach (var obj in v)
{
IsActiveField = dtFieldMap.Select("DestinationField='IsActive'")[0]["SourceField"].ToString();
}
DataTable dtValidEmailData = checkInValidEmailAddresses(dtFileData, emailAddressColumn, IsActiveField);
//to select distinct rows
//dtValidEmailData = dtValidEmailData.DefaultView.ToTable(true, Columns);
dtValidEmailData = RemoveDuplicateRows(dtValidEmailData, emailAddressColumn, dtFieldMap);
Logger.logdata(logforimportrecipients, string.Format("valid recipients count in selected file: {0}", dtValidEmailData.Rows.Count));
int totalRecipientsFound = dtFileData.Rows.Count;
int validRecipientsFound = dtValidEmailData.Rows.Count;
int inValidRecipientsFound = totalRecipientsFound - validRecipientsFound;
int insertedCount = 0;
int updatedCount = 0;
if (dtValidEmailData.Rows.Count == 0)
{
//throw new Exception("Please select proper file to import.");
}
//upsert into contactmaster and listcontactmaster based on importlimit.
var AllRecords = from item in dtValidEmailData.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 = dtValidEmailData.Clone();
top1000.CopyToDataTable(dtContacts, LoadOption.PreserveChanges);
DataTable dtListContactMasterResult = ImportRecordsToContactMaster(dtContacts, dtFieldMap, dr);
insertedCount += Convert.ToInt32(dtListContactMasterResult.Compute("Count(Status)", "Status='INSERT' and ListMasterID=" + Convert.ToInt64(dr["ListID"].ToString())));
updatedCount += Convert.ToInt32(dtListContactMasterResult.Compute("Count(Status)", "Status='UPDATE' 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``````````````
ListMasterPrimaryKey objListMasterPrimaryKey = new ListMasterPrimaryKey(Convert.ToInt64(dr["ListID"]));
ListMaster objListmaster = ListMaster.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
objImportJobPrimaryKey = new ImportJobPrimaryKey(Convert.ToInt64(ImportJobID));
//ImportJob
objImportJob = ImportJob.SelectOne(objImportJobPrimaryKey, connectionString);
objImportJob.Status = Common.ImportStatus.Success.ToString();
objImportJob.Update();
//Insert Import History
//ReplyPositiveBL.ImportHistory
objImportHistory = new ReplyPositiveBL.ImportHistory(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("ProcessSingleImportRecipients(..) 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>
/// Populates the fields for multiple objects from the columns found in an open reader.
/// </summary>
///
/// <param name="rdr" type="IDataReader">An object that implements the IDataReader interface</param>
///
/// <returns>Object of ImportHistories</returns>
///
/// <remarks>
///
/// <RevisionHistory>
/// Author Date Description
/// DLGenerator 11/21/2009 4:19:25 PM Created function
///
/// </RevisionHistory>
///
/// </remarks>
///
internal static ImportHistories PopulateObjectsFromReader(IDataReader rdr, string ConnectionString)
{
ImportHistories list = new ImportHistories();
while (rdr.Read())
{
ImportHistory obj = new ImportHistory(ConnectionString);
PopulateObjectFromReader(obj,rdr);
list.Add(obj);
}
return list;
}
/// <summary>
/// Populates the fields for multiple objects from the columns found in an open reader.
/// </summary>
///
/// <param name="rdr" type="IDataReader">An object that implements the IDataReader interface</param>
///
/// <returns>Object of ImportHistories</returns>
///
/// <remarks>
///
/// <RevisionHistory>
/// Author Date Description
/// DLGenerator 11/21/2009 4:19:25 PM Created function
///
/// </RevisionHistory>
///
/// </remarks>
///
internal static ImportHistories PopulateObjectsFromReaderWithCheckingReader(IDataReader rdr, DatabaseHelper oDatabaseHelper, string ConnectionString)
{
ImportHistories list = new ImportHistories();
if (rdr.Read())
{
ImportHistory obj = new ImportHistory(ConnectionString);
PopulateObjectFromReader(obj, rdr);
list.Add(obj);
while (rdr.Read())
{
obj = new ImportHistory(ConnectionString);
PopulateObjectFromReader(obj, rdr);
list.Add(obj);
}
oDatabaseHelper.Dispose();
return list;
}
else
{
oDatabaseHelper.Dispose();
return null;
}
}
/// <summary>
/// This method will return an object representing the record matching the primary key information specified.
/// </summary>
///
/// <param name="pk" type="ImportHistoryPrimaryKey">Primary Key information based on which data is to be fetched.</param>
///
/// <returns>object of class ImportHistory</returns>
///
/// <remarks>
///
/// <RevisionHistory>
/// Author Date Description
/// DLGenerator 11/21/2009 4:19:25 PM Created function
///
/// </RevisionHistory>
///
/// </remarks>
///
public static ImportHistory SelectOne(ImportHistoryPrimaryKey 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);
IDataReader dr=oDatabaseHelper.ExecuteReader("sp_ImportHistory_SelectbyPrimaryKey", ref ExecutionState);
if (dr.Read())
{
ImportHistory obj=new ImportHistory(ConnectionString);
PopulateObjectFromReader(obj,dr);
dr.Close();
oDatabaseHelper.Dispose();
return obj;
}
else
{
dr.Close();
oDatabaseHelper.Dispose();
return null;
}
}