/// <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;
        }