public bool ExtractCSV(string filelocation, string ntLogin)
{
bool retValue = false;
try
{
string filename = Path.GetFileNameWithoutExtension(filelocation);
using (StreamReader sr = new StreamReader(filelocation))
{
string currentLine;
#region Setup Site, Campaign and LOB Id
string[] siteCampaignLobIds = filename.Split('.');
int siteID = Convert.ToInt32(siteCampaignLobIds[0]);
int campaignID = Convert.ToInt32(siteCampaignLobIds[1]);
int lobID = Convert.ToInt32(siteCampaignLobIds[2]);
#endregion
#region Method Variable(s)
string segmentName = string.Empty;
int col = 0;
int row = 0;
int ctr = 0;
// currentLine will be null when the StreamReader reaches the end of file
Regex regx = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
#endregion
#region Deactivate existing Raw data in DB
string procName = "wfmpcp_ActivateDeactiviateCSV_sp";
string[] parameters = { "Active", "SiteID", "CampaignID", "LoBID" };
object[] paramValues = { false, siteID, campaignID, lobID };
_context.Execute(procName, parameters, paramValues);
#endregion
#region Read CSV
while ((currentLine = sr.ReadLine()) != null)
{
string[] splitted = regx.Split(currentLine);
#region Iterate CSV Values
foreach (var data in splitted)
{
col++;
ctr++;
string value = data.Replace("%", "").Replace("#VALUE!", "").Replace("#DIV/0!", "")
.Replace("-", "").Trim();
#region Setup Date
if (row == 1 && col > 2)
{
//Header
string[] splittedDate = data.Trim().Split('/');
string mm = splittedDate[0].ToString().Trim().Length == 1 ? ("0" + splittedDate[0].ToString()) : splittedDate[0].ToString().Trim();
string dd = splittedDate[1].ToString().Trim().Length == 1 ? ("0" + splittedDate[1].ToString()) : splittedDate[1].ToString().Trim();
string yyyy = splittedDate[2].ToString();
value = string.Format("{0}-{1}-{2}", yyyy, mm, dd);
}
#endregion
#region Just to add value to segment column
if (col == 1)
{
if (!string.IsNullOrEmpty(value))
{
segmentName = value;
}
value = segmentName;
row++;
}
#endregion
if (string.IsNullOrEmpty(value))
{
value = "0";
}
#region Insert New Record
procName = "wfmpcp_CreateCSVRawData_sp";
parameters = new string[] { "Data", "CreatedBy", "RowNumber", "ColumnNumber", "SiteID", "CampaignID", "LoBID", "Filename" };
paramValues = new object[] { value.Replace(",", "").Replace("\"", "").Replace("`", "").Trim(),
ntLogin,
row,
col,
siteID,
campaignID,
lobID,
string.Format("{0}.csv", filename) };
_context.Execute(procName, parameters, paramValues);
#endregion
if (col == 46)
{
col = 0; //reset col value
}
}
#endregion
}
#endregion
#region Setup Values to save
//delete first to staging
procName = "wfmpcp_DeleteStagingAHWeeklyDatapoint_sp";
parameters = new string[] { "SiteID", "CampaignID", "LobID" };
paramValues = new object[] { siteID, campaignID, lobID };
_context.Execute(procName, parameters, paramValues);
//get data from csv
#endregion Create Dataset
#region Insert data to Staging table
procName = "wfmpcp_CreateStagingWeeklyAHDatapoint_sp";
parameters = new string[] { "SiteID", "CampaignID", "LoBID", "Active" };
paramValues = new object[] { siteID, campaignID, lobID, true };
_context.Execute(procName, parameters, paramValues);
#endregion
#region Save data to WeeklyAHDatapoint and It's corresponding tables
procName = "wfmpcp_SaveAHCStagingToActual_sp";
parameters = new string[] { "SiteID", "CampaignID", "LoBID" };
paramValues = new object[] { siteID, campaignID, lobID };
_context.Execute(procName, parameters, paramValues);
#endregion
#region Log to Audit Trail
string entry = string.Empty;
entry = string.Format("UploadAHCServce.ExtractCSV({0},{1});SiteID:{2};Campaign:{3};LoBID:{4};SUCCESS;", filelocation, ntLogin, siteID, campaignID, lobID);
AuditTrail model = new AuditTrail()
{
AuditEntry = entry,
CreatedBy = "System Generated",
DateCreated = DateTime.Now,
DateModified = DateTime.Now
};
_context.Add <AuditTrail>(model);
_context.SaveChanges();
#endregion
retValue = true;
sr.Dispose();
}
}
catch (Exception ex)
{
retValue = false;
string entry = string.Empty;
entry = string.Format("UploadAHCServce.ExtractCSV({0},{1});Error:{2};FAIL;", filelocation, ntLogin, ex.Message);
AuditTrail model = new AuditTrail()
{
AuditEntry = entry,
CreatedBy = "System Generated",
DateCreated = DateTime.Now,
DateModified = DateTime.Now
};
_context.Add <AuditTrail>(model);
_context.SaveChanges();
}
return(retValue);
}