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