public ActionResult Save(string datapointValues, string date, string campaignID, string lobID, string siteID)
{
//string[] splitDatapointIds = datapointIds.TrimEnd( ',' ).Split( ',' );
string[] splitDatapointValues = datapointValues.TrimEnd(',').Split(',');
string message = string.Empty;
#region Create Data set
//DataSet dataset = new DataSet();
//dataset.Locale = CultureInfo.InvariantCulture;
DataTable datatable = new DataTable();
//dataset.Tables.Add( "AHCData" );
datatable.Columns.Add("DatapointID", typeof(long));
datatable.Columns.Add("SiteID", typeof(long));
datatable.Columns.Add("CampaignID", typeof(long));
datatable.Columns.Add("LoBID", typeof(long));
datatable.Columns.Add("Date", typeof(DateTime));
//datatable.Columns.Add( "Date", typeof( string ) );
datatable.Columns.Add("DataValue", typeof(string));
datatable.Columns.Add("UserName", typeof(string));
datatable.Columns.Add("DateModified", typeof(DateTime));
string datemodified = DateTime.Now.ToString();
string user = User.Identity.GetUserName();
DataRow datarow;
#endregion
try
{
foreach (var datapointValue in splitDatapointValues)
{
long id = 0;
string dt = string.Empty;
string value = string.Empty;
string[] splitDatapointValue = datapointValue.Split(':');
string[] splitIdDate = splitDatapointValue[0].Split('_');
id = Convert.ToInt64(splitIdDate[1]);
dt = splitIdDate[0].Replace("x", "-");
value = splitDatapointValue[1].ToString().Replace("%", "").Trim();
datarow = datatable.NewRow();
datarow["DatapointID"] = id;
datarow["SiteID"] = siteID;
datarow["CampaignID"] = campaignID;
datarow["LoBID"] = lobID;
datarow["Date"] = dt;
datarow["DataValue"] = value;
datarow["UserName"] = user;
datarow["DateModified"] = datemodified;
datatable.Rows.Add(datarow);
}
_ahcService.Save(datatable);
message = "Data saved.";
}
catch (Exception ex)
{
message = "Unexpected error encountered. Please contact your system administrator.";
string auditEntry = string.Empty;
auditEntry += @"Error saving Assumption and Headcount. \n";
auditEntry += string.Format("ErrorMessage: {0} \n", ex.Message);
auditEntry += string.Format("TargetName: {0} \n", ex.TargetSite.Name);
auditEntry += string.Format("StackTrace: {0} \n", ex.StackTrace);
AuditTrailViewModel audit = new AuditTrailViewModel()
{
AuditEntry = auditEntry,
CreatedBy = User.Identity.GetUserName(),
DateCreated = DateTime.Now,
DateModified = null
};
_auditTrailService.Create(audit);
}
var model = new Model.Web.CommonView()
{
Message = message
};
return(Json(model, JsonRequestBehavior.AllowGet));
}
public bool ExcelRead(string fileName, string column, int columns)
{
//check excel reader if null
if (_exr != null)
{
//dispose excel reader
_exr.Dispose();
//set excel reader to null
_exr = null;
}
//create new instance of excel reader
_exr = new ExcelReader();
//set excel filename
_exr.ExcelFilename = fileName;
//read headers
_exr.Headers = false;
_exr.MixedData = false;
//get set sheet Name to string array
//_exr = new ExcelReader();
string[] sheetNames = _exr.GetExcelSheetNames();
StringBuilder sb = new StringBuilder();
foreach (string sheet in sheetNames)
{
if (sheet.Length < 50)
{
//replace invalid character from sheetname
newSheet = sheet.Replace("$", "")
.Replace("'", "");
#region PCP
//check if data table is null
if (_xlsTable == null)
{
//set new instance for datatable
_xlsTable = new DataTable();
}
//set excel reader connection open
_exr.KeepConnectionOpen = true;
//set excel sheet Name to read
_exr.SheetName = newSheet.ToString();
//set excel sheet range
//_exr.SheetRange = Settings.Column + ":" + Settings.Row;
_exr.SheetRange = string.Format("{0}:{1}", ConfigurationManager.AppSettings["WFMStartColumn"]
, ConfigurationManager.AppSettings["WFMEndColumn"]);
var lastColumn = new String(ConfigurationManager.AppSettings["WFMEndColumn"].Where(Char.IsLetter).ToArray());
var colIndex = this.ExcelColumnNameToNumber(lastColumn) - 3;
//get record from excel sheet
_xlsTable = _exr.GetTable();
int count = _xlsTable.Rows.Count;
DataRow dateDataRow = _xlsTable.Rows[0];
List <DateTime> dates = new List <DateTime>();
#region setup SiteID,CampaignID,LoBID
string[] splittedSheet = newSheet.Split('>');
long siteID = long.Parse(splittedSheet[0]);
long campaignID = long.Parse(splittedSheet[1]);
long lobID = long.Parse(splittedSheet[2]);
#endregion setup SiteID,CampaignID,LoBID
#region Truncate table first
string qryDelete = string.Format("DELETE FROM StagingWeeklyAHDatapoint WHERE SiteID={0} AND CampaignID={1} AND LoBID={2}", siteID, campaignID, lobID);
_context.ExecuteTSQL(qryDelete);
#endregion Truncate table first
#region Setup Excel data
for (int i = 1; i < count; i++)
{
DataRow row = _xlsTable.Rows[i];
#region Loop Column
for (int j = 0; j <= colIndex; j++)
{
string dtString = this.FormattedDateString(dateDataRow[j].ToString());
DateTime date = DateTime.Parse(dtString);
if (date.DayOfWeek == DayOfWeek.Monday)
{
//insert data
string data = row[j].ToString().Trim();
if (string.IsNullOrEmpty(data))
{
data = "0";
}
data = data.Replace("%", "").Replace(" ", "").Replace(",", "");
int week = 0;
week = this.GetWeekOfYear(date);
dates.Add(date);
var model = new Model.WeeklyDatapointViewModel()
{
SiteID = siteID,
CampaignID = campaignID,
LoBID = lobID,
DatapointID = i,
Week = week,
Data = data,
Date = date,
CreatedBy = "WFM PCP Uploader",
DateCreated = DateTime.Now
};
_stagingWeeklyDatapointService.Save(model);
}
}
#endregion Loop Column
}
#endregion Setup Excel data
#region Save to WeeklyAHDatapoint
foreach (var dt in dates.OrderBy(x => x.Date).Distinct())
{
#region Create Dataset
//DataTable datatable = this.WeeklyDtpDatatable;
DataTable datatable = new DataTable();
//dataset.Tables.Add( "AHCData" );
datatable.Columns.Add("DatapointID", typeof(long));
datatable.Columns.Add("SiteID", typeof(long));
datatable.Columns.Add("CampaignID", typeof(long));
datatable.Columns.Add("LoBID", typeof(long));
datatable.Columns.Add("Date", typeof(DateTime));
//datatable.Columns.Add( "Date", typeof( string ) );
datatable.Columns.Add("DataValue", typeof(string));
datatable.Columns.Add("UserName", typeof(string));
datatable.Columns.Add("DateModified", typeof(DateTime));
DataRow datarow;
#endregion Create Dataset
string b = dt.ToString("yyyy-MM-dd");
//get staging data
var stagingDatapoints = _context.AsQueryable <StagingWeeklyDatapoint>()
.Where(x => x.Date == dt && x.SiteID == siteID && x.CampaignID == campaignID && x.LoBID == lobID)
.ToList();
foreach (var sDatapoint in stagingDatapoints)
{
string value = sDatapoint.Data.Replace("%", "")
.Replace("#VALUE!", "")
.Replace("-", "").Trim();
datarow = datatable.NewRow();
datarow["DatapointID"] = sDatapoint.DatapointID;
datarow["SiteID"] = sDatapoint.SiteID;
datarow["CampaignID"] = sDatapoint.CampaignID;
datarow["LoBID"] = sDatapoint.LoBID;
datarow["Date"] = dt;
datarow["DataValue"] = value;
datarow["UserName"] = sDatapoint.CreatedBy;
datarow["DateModified"] = sDatapoint.DateCreated;
datatable.Rows.Add(datarow);
var bb = datatable;
}
_achService.Save(datatable);
}
#endregion Save to WeeklyAHDatapoint
isSuccess = true;
#endregion PCP
#region Log
this.WriteLogs(ref sb, fileName, siteID.ToString(), campaignID.ToString(), lobID.ToString());
//sb.AppendLine( WriteLogs( fileName, siteID.ToString(), campaignID.ToString(), lobID.ToString() ) );
//sb.AppendLine( );
#endregion Log
}
}
if (sb != null)
{
string filename = string.Format("Success_{0}.txt", (DateTime.Now).ToString("MM.dd.yyyy.HH.mm"));
string filelocation = ""; // AppSettings.Setting<string>("WFMUpload.Logs") + filename;
File.WriteAllText(filelocation, sb.ToString());
}
return(true);
}