private DataTable ReadExcelFile(string ext, string path, ref string phoneCol)
{
DataTable excelDt = new DataTable();
FileStream stream = System.IO.File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = null;
if (ext == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
int fieldcount = excelReader.FieldCount;
int rowcount = excelReader.RowCount;
DataRow row;
DataTable dt_ = new DataTable();
dt_ = excelReader.AsDataSet().Tables[0];
for (int i = 0; i < dt_.Columns.Count; i++)
{
var thisCol = dt_.Rows[0][i].ToString();
excelDt.Columns.Add(thisCol);
if (filterCols.Split(',').ToList().Contains(thisCol))
{
phoneCol = thisCol;
}
}
int rowcounter = 0;
for (int row_ = 1; row_ < dt_.Rows.Count; row_++)
{
row = excelDt.NewRow();
for (int col = 0; col < dt_.Columns.Count; col++)
{
row[col] = LeadsHelpers.ProcessNumber(dt_.Rows[row_][col].ToString());
rowcounter++;
}
excelDt.Rows.Add(row);
}
excelReader.Close();
excelReader.Dispose();
return(excelDt);
}
private DataTable ReadCsvFile(string path, ref string phoneCol)
{
string empty = string.Empty;
DataTable dtCsv = new DataTable();
string Fulltext;
using (StreamReader sr = new StreamReader(path))
{
while (!sr.EndOfStream)
{
Fulltext = sr.ReadToEnd().ToString(); //read full file text
string[] rows = Fulltext.Split('\n'); //split full file text into rows
for (int i = 0; i < rows.Count() - 1; i++)
{
string[] rowValues = rows[i].Split(','); //split each row with comma to get individual values
{
if (i == 0)
{
for (int j = 0; j < rowValues.Count(); j++)
{
var thisCol = rowValues[j];
dtCsv.Columns.Add(thisCol.Replace("\r", "")); //add headers
if (filterCols.Split(',').ToList().Contains(thisCol.Replace("\r", "")) || filterCols.Split(',').ToList().Contains(thisCol.Replace("\r", "")))
{
phoneCol = thisCol.Replace("\r", "");
}
}
}
else
{
DataRow dr = dtCsv.NewRow();
for (int k = 0; k < rowValues.Count(); k++)
{
dr[k] = LeadsHelpers.ProcessNumber(rowValues[k].Replace("\r", "").ToString());
}
dtCsv.Rows.Add(dr); //add other rows
}
}
}
}
}
return(dtCsv);
}
public ActionResult PerformUserScrub(FormCollection formCollection, string PhoneNos, string SelectedLeads)
{
bool isError = false;
try
{
var used = userScrubService.GetScrubsByUserId(this.CurrentLoggedInUser.Id).Sum(x => x.ScrubCredits);
Stopwatch sw = Stopwatch.StartNew();
List <int> selectedLeads = JsonConvert.DeserializeObject <List <DropDownModel> >(SelectedLeads).Select(x => x.Id).ToList();
if (string.IsNullOrEmpty(PhoneNos))
{
HttpPostedFileBase file = Request.Files["ScrubFile"];
if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))
{
string ext = Path.GetExtension(file.FileName);
string newFileName = Guid.NewGuid().ToString();
string path = Path.Combine(Server.MapPath("~/Content/DataLoads/"), newFileName + ext);
file.SaveAs(path);
List <long> UserScrubPhonesList = new List <long>();
DataTable excelDt = new DataTable();
string phoneCol = string.Empty;
List <DropDownModel> dt = new List <DropDownModel>();
if (ext.ToLower() == ".csv")
{
excelDt = ReadCsvFile(path, ref phoneCol);
dt = FilterDatatableColoumn(excelDt.AsEnumerable(), excelDt, phoneCol);
}
else
{
excelDt = ReadExcelFile(ext, path, ref phoneCol);
dt = FilterDatatableColoumn(excelDt.AsEnumerable(), excelDt, phoneCol);
}
foreach (var r in dt)
{
long number;
bool isSuccess = Int64.TryParse(r.Name, out number);
if (isSuccess)
{
UserScrubPhonesList.Add(number);
}
}
if (UserScrubPhonesList.Count > 0)
{
if ((this.CurrentLoggedInUser.CreditScore - used) >= UserScrubPhonesList.Count())
{
var matchedList = leadService.ScrubPhoneNos(selectedLeads, UserScrubPhonesList).ToList();
var unmatchedCount = UserScrubPhonesList.Except(matchedList).ToList();
var dtList = excelDt.AsEnumerable();
var matchedDt = GetFilteredPhones(dtList, matchedList, phoneCol);
var unMatchedDt = GetFilteredPhones(dtList, unmatchedCount, phoneCol);
sw.Stop();
// Matched File Create
string matchedFileName = Guid.NewGuid().ToString();
CreatedDataTableCSV(matchedDt, matchedFileName);
// UnMatched File Create
string unMatchedFileName = Guid.NewGuid().ToString();
CreatedDataTableCSV(unMatchedDt, unMatchedFileName);
userScrubService.SaveUserScrub(UserScrubPhonesList.Count(), this.CurrentLoggedInUser.Id, SelectedLeads, matchedList.Count(), unmatchedCount.Count(), matchedFileName, unMatchedFileName, newFileName + ext, sw.Elapsed.Seconds);
}
else
{
ViewBag.NoCredits = true;
ViewBag.IsFileError = false;
ViewBag.CurrentUser = this.CurrentLoggedInUser;
ViewBag.LeadTypesList = leadService.GetLeadTypes().Where(x => x.IsActive == true).ToList().Select(x => new DropDownModel()
{
Name = x.Name,
Id = x.Id
}).OrderBy(x => x.Name).ToList();
var usedCredits1 = userScrubService.GetScrubsByUserId(this.CurrentLoggedInUser.Id).Sum(x => x.ScrubCredits);
var userTotalCredits1 = this.CurrentLoggedInUser.CreditScore;
ViewBag.remainingCredits = LeadsHelpers.ToUsNumberFormat(userTotalCredits1 - usedCredits1);
ViewBag.totalCredits = LeadsHelpers.ToUsNumberFormat(userTotalCredits1);
return(View("Scrubber"));
}
}
}
}
else
{
List <string> inputList = PhoneNos.Split(',').ToList();
List <long> UserScrubPhonesList = new List <long>();
foreach (var i in inputList)
{
long number;
bool isSuccess = Int64.TryParse(LeadsHelpers.ProcessNumber(i), out number);
if (isSuccess)
{
UserScrubPhonesList.Add(number);
}
}
if (UserScrubPhonesList.Count > 0)
{
if ((this.CurrentLoggedInUser.CreditScore - used) >= UserScrubPhonesList.Count())
{
var matchedList = leadService.ScrubPhoneNos(selectedLeads, UserScrubPhonesList).ToList();
var unmatchedCount = UserScrubPhonesList.Except(matchedList).ToList();
sw.Stop();
//InputFile Create
string inputFileName = Guid.NewGuid().ToString();
CreateSaveCsvFile(inputFileName, UserScrubPhonesList);
//Matched File Create
string matchedFileName = Guid.NewGuid().ToString();
CreateSaveCsvFile(matchedFileName, matchedList);
// UnMatched File Create
string unMatchedFileName = Guid.NewGuid().ToString();
CreateSaveCsvFile(unMatchedFileName, unmatchedCount);
userScrubService.SaveUserScrub(UserScrubPhonesList.Count(), this.CurrentLoggedInUser.Id, SelectedLeads, matchedList.Count(), unmatchedCount.Count(), matchedFileName, unMatchedFileName, inputFileName + ".csv", sw.Elapsed.Seconds);
}
else
{
ViewBag.NoCredits = true;
ViewBag.IsFileError = false;
ViewBag.CurrentUser = this.CurrentLoggedInUser;
ViewBag.LeadTypesList = leadService.GetLeadTypes().Where(x => x.IsActive == true).ToList().Select(x => new DropDownModel()
{
Name = x.Name,
Id = x.Id
}).OrderBy(x => x.Name).ToList();
var usedCredits1 = userScrubService.GetScrubsByUserId(this.CurrentLoggedInUser.Id).Sum(x => x.ScrubCredits);
var userTotalCredits1 = this.CurrentLoggedInUser.CreditScore;
ViewBag.remainingCredits = LeadsHelpers.ToUsNumberFormat(userTotalCredits1 - usedCredits1);
ViewBag.totalCredits = LeadsHelpers.ToUsNumberFormat(userTotalCredits1);
return(View("Scrubber"));
}
}
}
}
catch (Exception ex)
{
isError = true;
}
ViewBag.NoCredits = false;
ViewBag.IsFileError = isError;
ViewBag.CurrentUser = this.CurrentLoggedInUser;
ViewBag.LeadTypesList = leadService.GetLeadTypes().Where(x => x.IsActive == true).ToList().Select(x => new DropDownModel()
{
Name = x.Name,
Id = x.Id
}).OrderBy(x => x.Name).ToList();
var usedCredits = userScrubService.GetScrubsByUserId(this.CurrentLoggedInUser.Id).Sum(x => x.ScrubCredits);
var userTotalCredits = this.CurrentLoggedInUser.CreditScore;
ViewBag.remainingCredits = LeadsHelpers.ToUsNumberFormat(userTotalCredits - usedCredits);
ViewBag.totalCredits = LeadsHelpers.ToUsNumberFormat(userTotalCredits);
return(View("Scrubber"));
}