public List <OverAllExcessDeficitSummaryViewModel> GetCampaignSummary(DateTime start, DateTime end) { List <OverAllExcessDeficitSummaryViewModel> ret = new List <OverAllExcessDeficitSummaryViewModel>(); _context.AsQueryable <WeeklyStaffDatapoint>() .Where(x => (x.Date >= start && x.Date <= end) && x.DatapointID == 23 && x.Site.Active == true && x.Campaign.Active == true && x.LoB.Active == true) .Select(x => new OverAllExcessDeficitSummaryViewModel() { Name = x.Campaign.Name, Data = x.Data }).ToList() .ForEach(a => ret.Add(new OverAllExcessDeficitSummaryViewModel() { ExcessDeficitTotal = Convert.ToDouble(a.Data), Name = a.Name }) ); if (ret != null) { if (ret.Count() > 0) { ret = ret.GroupBy(g => g.Name) .Select(x => new OverAllExcessDeficitSummaryViewModel { Name = x.Key, ExcessDeficitTotal = Math.Round(x.Average(i => i.ExcessDeficitTotal), 0) }) .OrderBy(x => x.Name) .ToList(); } } return(ret); }
public IQueryable <UserRoleViewModel> GetAll() { var data = _context.AsQueryable <UserRole>() .Select(x => new UserRoleViewModel() { ID = x.ID, RoleID = x.RoleID, NTLogin = x.NTLogin, EmployeeID = x.EmployeeID, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, Active = x.Active, #region RoleVM RoleVM = new RoleViewModel() { ID = x.Role.ID, Name = x.Role.Name, Code = x.Role.Code, Description = x.Role.Description, CreatedBy = x.Role.CreatedBy, ModifiedBy = x.Role.ModifiedBy, DateCreated = x.Role.DateCreated, DateModified = x.Role.DateModified, Active = x.Role.Active } #endregion RoleVM }); return(data); }
public List <OverAllHiringOutlookSummaryViewModel> GetCampaignSummary(DateTime start, DateTime end) { List <OverAllHiringOutlookSummaryViewModel> ret = new List <OverAllHiringOutlookSummaryViewModel>(); _context.AsQueryable <WeeklyHiringDatapoint>() .Where(x => (x.Date >= start && x.Date <= end) && x.Site.Active == true && x.Campaign.Active == true && x.LoB.Active == true) .Select(x => new OverAllHiringOutlookSummaryViewModel() { Name = x.Campaign.Name, Data = x.Data }).ToList() .ForEach(a => ret.Add(new OverAllHiringOutlookSummaryViewModel() { HiringTotal = Convert.ToInt32(a.Data), Name = a.Name }) ); if (ret != null) { if (ret.Count() > 0) { ret = ret.GroupBy(g => g.Name) .Select(x => new OverAllHiringOutlookSummaryViewModel { Name = x.Key, HiringTotal = x.Sum(i => i.HiringTotal) }) .OrderBy(x => x.Name) .ToList(); } } return(ret); }
public IQueryable <SiteCampaignLobFormulaViewModel> GetAll() { var data = _context.AsQueryable <SiteCampaignLobFormula>() .Select(x => new SiteCampaignLobFormulaViewModel() { ID = x.ID, SiteID = x.SiteID, CampaignID = x.CampaignID, LoBID = x.LoBID, DynamicFormulaID = x.DynamicFormulaID, Active = x.Active, #region SiteVM SiteVM = new SiteViewModel() { ID = x.SiteID, Name = x.Site.Name, Code = x.Site.Code }, #endregion SiteVM #region CampaignVM CampaignVM = new CampaignViewModel() { ID = x.CampaignID, Name = x.Campaign.Name, Code = x.Campaign.Code }, #endregion CampaignVM #region LobVM LoBVM = new LoBViewModel() { ID = x.LoBID, Name = x.LoB.Name, Code = x.LoB.Code }, #endregion LobVM #region DynamicVM DynamicFormulaVM = new DynamicFormulaViewModel() { ID = x.DynamicFormulaID, Name = x.DynamicFormula.Name } #endregion DynamicVM }).AsQueryable(); return(data); }
public IQueryable <LoBViewModel> GetAll() { var data = _context.AsQueryable <LoB>() .Select(x => new LoBViewModel() { ID = x.ID, CampaignID = x.CampaignID, Name = x.Name, Code = x.Code, Description = x.Description, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, Active = x.Active, #region CampaignVM //CampaignVM = x.CampaignID==0 ? new CampaignViewModel() : new CampaignViewModel() //{ // ID = x.Campaign.ID, // SiteID = x.Campaign.SiteID, // Name = x.Campaign.Name, // Code = x.Campaign.Code, // Description = x.Campaign.Description, // CreatedBy = x.Campaign.CreatedBy, // ModifiedBy = x.Campaign.ModifiedBy, // DateCreated = x.Campaign.DateCreated, // DateModified = x.Campaign.DateModified, // Active = x.Campaign.Active, // #region SiteVM // SiteVM = new SiteViewModel() // { // ID = x.Campaign.Site.ID, // Name = x.Campaign.Site.Name, // Code = x.Campaign.Site.Code, // Description = x.Campaign.Site.Description, // CreatedBy = x.Campaign.Site.CreatedBy, // ModifiedBy = x.Campaign.Site.ModifiedBy, // DateCreated = x.Campaign.Site.DateCreated, // DateModified = x.Campaign.Site.DateModified, // Active = x.Campaign.Site.Active // } // #endregion //} #endregion CampaignVM }).AsQueryable(); return(data); }
public IQueryable <SiteCampaignLoBViewModel> GetAll() { var data = _context.AsQueryable <SiteCampaignLoB>() .Select(x => new SiteCampaignLoBViewModel() { ID = x.ID, SiteID = x.SiteID, CampaignID = x.CampaignID, LobID = x.LoBID, Active = x.Active, SiteVM = new SiteViewModel() { ID = x.SiteID, Name = x.Site.Name, Code = x.Site.Code, Description = x.Site.Description, CreatedBy = x.Site.CreatedBy, ModifiedBy = x.Site.ModifiedBy, DateCreated = x.Site.DateCreated, DateModified = x.Site.DateModified, Active = x.Site.Active }, CampaignVM = new CampaignViewModel() { ID = x.CampaignID, Name = x.Campaign.Name, Code = x.Campaign.Code, Description = x.Campaign.Description, CreatedBy = x.Campaign.CreatedBy, ModifiedBy = x.Campaign.ModifiedBy, DateCreated = x.Campaign.DateCreated, DateModified = x.Campaign.DateModified, Active = x.Campaign.Active }, LobVM = new LoBViewModel() { ID = x.LoBID, Name = x.LoB.Name, Code = x.LoB.Code, Description = x.LoB.Description, CreatedBy = x.LoB.CreatedBy, ModifiedBy = x.LoB.ModifiedBy, DateCreated = x.LoB.DateCreated, DateModified = x.LoB.DateModified, Active = x.LoB.Active } }).AsQueryable(); return(data); }
public IQueryable <DynamicFormulaViewModel> GetAll() { var data = _context.AsQueryable <DynamicFormula>() .Select(x => new DynamicFormulaViewModel() { ID = x.ID, Name = x.Name, Description = x.Description, DateCreated = x.DateCreated, DateModified = x.DateModified, Active = x.Active }).AsQueryable(); return(data); }
public IQueryable <PermissionViewModel> GetAll() { var data = _context.AsQueryable <Permission>() .Select(x => new PermissionViewModel() { ID = x.ID, Name = x.Name, Description = x.Description, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, Active = x.Active }).AsQueryable(); return(data); }
public IQueryable <AuditTrailViewModel> GetAll() { var data = _context.AsQueryable <AuditTrail>() .Select(x => new AuditTrailViewModel() { ID = x.ID, AuditEntry = x.AuditEntry, CreatedBy = x.CreatedBy, DateCreated = x.DateCreated, DateModified = x.DateModified }).AsQueryable(); return(data); }
public IEnumerable <Model.SegmentCategory> GetAllSegmentCategories() { var segmentCats = _context.AsQueryable <Model.SegmentCategory>() .Select(x => new Model.SegmentCategory() { ID = x.ID, Name = x.Name, SortOrder = x.SortOrder, Visible = x.Visible, Active = x.Active, #region Segments Segments = x.Segments.OrderBy(s => s.SortOrder).Select(s => new Model.Segment() { ID = s.ID, Name = s.Name, SortOrder = s.SortOrder, Visible = s.Visible, Active = s.Active, SegmentCategoryID = s.ID, #region Datapoints Datapoints = s.Datapoints.OrderBy(d => d.SortOrder).Select(d => new Model.Datapoint() { ID = d.ID, Name = d.Name, //ReferenceID = d.ReferenceID, SortOrder = d.SortOrder, Visible = d.Visible, Active = d.Active, Datatype = d.Datatype }) #endregion }) #endregion }); return(segmentCats); }
public IQueryable <DatapointViewModel> GetAll() { var data = _context.AsQueryable <Data.Entities.Datapoint>() .Select(x => new DatapointViewModel() { ID = x.ID, SegmentID = x.SegmentID, //ReferenceID = x.ReferenceID, Name = x.Name, Datatype = x.Datatype, SortOrder = x.SortOrder, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, Active = x.Active, Visible = x.Visible, #region DatapointCategoryVM SegmentVM = new SegmentViewModel() { ID = x.Segment.ID, SegmentCategoryID = (int)x.Segment.SegmentCategoryID, Name = x.Segment.Name, SortOrder = x.Segment.SortOrder, CreatedBy = x.Segment.CreatedBy, ModifiedBy = x.Segment.ModifiedBy, DateCreated = x.Segment.DateCreated, DateModified = x.Segment.DateModified, Active = x.Segment.Active, Visible = x.Segment.Visible, SegmentCategoryVM = new SegmentCategoryViewModel() { ID = x.Segment.SegmentCategory.ID, Name = x.Segment.SegmentCategory.Name, SortOrder = x.Segment.SegmentCategory.SortOrder, CreatedBy = x.Segment.SegmentCategory.CreatedBy, ModifiedBy = x.Segment.SegmentCategory.ModifiedBy, DateCreated = x.Segment.SegmentCategory.DateCreated, DateModified = x.Segment.SegmentCategory.DateModified, Active = x.Segment.SegmentCategory.Active, Visible = x.Segment.SegmentCategory.Visible, } } #endregion DatapointCategoryVM }).AsQueryable(); return(data); }
public IQueryable <StaffSegmentViewModel> GetAll() { var data = _context.AsQueryable <Data.Entities.StaffSegment>() .Select(x => new StaffSegmentViewModel() { ID = x.ID, SegmentCategoryID = (int)x.SegmentCategoryID, Name = x.Name, SortOrder = x.SortOrder, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, Active = x.Active, Visible = x.Visible }).AsQueryable(); return(data); }
public IQueryable <HiringDatapointViewModel> GetAll() { var data = _context.AsQueryable <Data.Entities.HiringDatapoint>() .Select(x => new HiringDatapointViewModel() { ID = x.ID, SegmentID = x.SegmentID, ReferenceID = x.ReferenceID, Name = x.Name, Datatype = x.Datatype, SortOrder = x.SortOrder, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, Active = x.Active, Visible = x.Visible }).AsQueryable(); return(data); }
public IQueryable <SegmentViewModel> GetAll() { var data = _context.AsQueryable <Data.Entities.Segment>() .Select(x => new SegmentViewModel() { ID = x.ID, SegmentCategoryID = (int)x.SegmentCategoryID, Name = x.Name, SortOrder = x.SortOrder, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, Active = x.Active, Visible = x.Visible, #region SegmentCategoryVM SegmentCategoryVM = x.SegmentCategoryID == null ? null : new SegmentCategoryViewModel() { ID = x.SegmentCategory.ID, Name = x.SegmentCategory.Name, SortOrder = x.SegmentCategory.SortOrder, CreatedBy = x.SegmentCategory.CreatedBy, ModifiedBy = x.SegmentCategory.ModifiedBy, DateCreated = x.SegmentCategory.DateCreated, DateModified = x.SegmentCategory.DateModified, Active = x.SegmentCategory.Active, Visible = x.SegmentCategory.Visible } #endregion SegmentCategoryVM }).AsQueryable(); return(data); }
public IQueryable <ModuleViewModel> GetAll() { var data = _context.AsQueryable <Module>() .Select(x => new ModuleViewModel() { ID = x.ID, ParentID = x.ParentID, Name = x.Name, Route = x.Route, MenuIcon = x.MenuIcon, FontAwesome = x.FontAwesome, SortOrder = x.SortOrder, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, Active = x.Active }).AsQueryable(); return(data); //List<ModuleViewModel> modules = null; //foreach(var module in _context.AsQueryable<Module>().Where(x=>x.ParentID==0) ) //{ // ModuleViewModel model = new ModuleViewModel(); // model.ID = module.ID; // model.ParentID = module.ParentID; // model.Name = module.Name; // model.Route = module.Route; // model.SortOrder = module.SortOrder; // model.CreatedBy = module.CreatedBy; // model.ModifiedBy = module.ModifiedBy; // model.DateCreated = module.DateCreated; // model.DateModified = module.DateModified; // model.Active = module.Active; // //model.Children = new List<ModuleViewModel> ();//this.CreateChildren( module.ID ); // modules.Add( model ); //} //return modules.AsQueryable(); }
public IQueryable <ModuleRolePermissionViewModel> GetAll() { var data = _context.AsQueryable <ModuleRolePermission>() .Select(x => new ModuleRolePermissionViewModel() { ID = x.ID, ModuleID = x.ModuleID, RoleID = x.RoleID, PermissionID = x.PermissionID, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, Active = x.Active, #region ModuleVM ModuleVM = new ModuleViewModel() { ID = x.Module.ID, ParentID = x.Module.ParentID, Name = x.Module.Name, Route = x.Module.Route, MenuIcon = x.Module.MenuIcon, FontAwesome = x.Module.FontAwesome, SortOrder = x.Module.SortOrder, CreatedBy = x.Module.CreatedBy, ModifiedBy = x.Module.ModifiedBy, DateCreated = x.Module.DateCreated, DateModified = x.Module.DateModified, Active = x.Module.Active }, #endregion ModuleVM #region RoleVM RoleVM = new RoleViewModel() { ID = x.Role.ID, Name = x.Role.Name, Code = x.Role.Code, Description = x.Role.Description, CreatedBy = x.Role.CreatedBy, ModifiedBy = x.Role.ModifiedBy, DateCreated = x.Role.DateCreated, DateModified = x.Role.DateModified, Active = x.Role.Active }, #endregion RoleVM #region PermissionVM PermissionVM = new PermissionViewModel() { ID = x.Permission.ID, Name = x.Permission.Name, Description = x.Permission.Description, CreatedBy = x.Permission.CreatedBy, ModifiedBy = x.Permission.ModifiedBy, DateCreated = x.Permission.DateCreated, DateModified = x.Permission.DateModified, Active = x.Permission.Active } #endregion PermissionVM }).AsQueryable(); return(data); }
public IQueryable <WeeklyDatapointViewModel> GetAll() { var data = _context.AsQueryable <WeeklyDatapoint>() .Select(x => new WeeklyDatapointViewModel() { ID = x.ID, CampaignID = x.CampaignID, LoBID = x.LoBID, DatapointID = x.DatapointID, Week = x.Week, Data = x.Data, //Datatype = x.Datatype, Date = x.Date, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, #region CampaignVM CampaignVM = x.CampaignID == null ? null : new CampaignViewModel() { ID = x.Campaign.ID, SiteID = x.Campaign.SiteID, Name = x.Campaign.Name, Code = x.Campaign.Code, Description = x.Campaign.Description, CreatedBy = x.Campaign.CreatedBy, ModifiedBy = x.Campaign.ModifiedBy, DateCreated = x.Campaign.DateCreated, DateModified = x.Campaign.DateModified, Active = x.Campaign.Active, #region SiteVM SiteVM = new SiteViewModel() { ID = x.Campaign.Site.ID, Name = x.Campaign.Site.Name, Code = x.Campaign.Site.Code, Description = x.Campaign.Site.Description, CreatedBy = x.Campaign.Site.CreatedBy, ModifiedBy = x.Campaign.Site.ModifiedBy, DateCreated = x.Campaign.Site.DateCreated, DateModified = x.Campaign.Site.DateModified, Active = x.Campaign.Site.Active } #endregion SiteVM }, #endregion CampaignVM #region LobVM LoBVM = x.LoBID == null ? null : new LoBViewModel() { ID = x.LoB.ID, CampaignID = x.LoB.CampaignID, Name = x.LoB.Name, Code = x.LoB.Code, Description = x.LoB.Description, CreatedBy = x.LoB.CreatedBy, ModifiedBy = x.LoB.ModifiedBy, DateCreated = x.LoB.DateCreated, DateModified = x.LoB.DateModified, Active = x.LoB.Active, #region CampaignVM CampaignVM = new CampaignViewModel() { ID = x.LoB.Campaign.ID, SiteID = x.LoB.Campaign.SiteID, Name = x.LoB.Campaign.Name, Code = x.LoB.Campaign.Code, Description = x.LoB.Campaign.Description, CreatedBy = x.LoB.Campaign.CreatedBy, ModifiedBy = x.LoB.Campaign.ModifiedBy, DateCreated = x.LoB.Campaign.DateCreated, DateModified = x.LoB.Campaign.DateModified, Active = x.LoB.Campaign.Active, #region SiteVM SiteVM = new SiteViewModel() { ID = x.LoB.Campaign.Site.ID, Name = x.LoB.Campaign.Site.Name, Code = x.LoB.Campaign.Site.Code, Description = x.LoB.Campaign.Site.Description, CreatedBy = x.LoB.Campaign.Site.CreatedBy, ModifiedBy = x.LoB.Campaign.Site.ModifiedBy, DateCreated = x.LoB.Campaign.Site.DateCreated, DateModified = x.LoB.Campaign.Site.DateModified, Active = x.LoB.Campaign.Site.Active } #endregion SiteVM } #endregion CampaignVM }, #endregion LobVM #region DatapointVM DatapointVM = new DatapointViewModel() { ID = x.Datapoint.ID, SegmentID = x.Datapoint.SegmentID, Name = x.Datapoint.Name, SortOrder = x.Datapoint.SortOrder, CreatedBy = x.Datapoint.CreatedBy, ModifiedBy = x.Datapoint.ModifiedBy, DateCreated = x.Datapoint.DateCreated, DateModified = x.Datapoint.DateModified, Active = x.Datapoint.Active, Visible = x.Datapoint.Visible, #region DatapointCategoryVM SegmentVM = new SegmentViewModel() { ID = x.Datapoint.Segment.ID, Name = x.Datapoint.Segment.Name, SortOrder = x.Datapoint.Segment.SortOrder, CreatedBy = x.Datapoint.Segment.CreatedBy, ModifiedBy = x.Datapoint.Segment.ModifiedBy, DateCreated = x.Datapoint.Segment.DateCreated, DateModified = x.Datapoint.Segment.DateModified, Active = x.Datapoint.Segment.Active, Visible = x.Datapoint.Segment.Visible } #endregion DatapointCategoryVM } #endregion DatapointVM }).AsQueryable(); return(data); }
public IQueryable <WeeklyDatapointViewModel> GetAll() { var data = _context.AsQueryable <StagingWeeklyDatapoint>() .Select(x => new WeeklyDatapointViewModel() { ID = x.ID, CampaignID = x.CampaignID, LoBID = x.LoBID, DatapointID = x.DatapointID, Week = x.Week, Data = x.Data, //Datatype = x.Datatype, Date = x.Date, CreatedBy = x.CreatedBy, ModifiedBy = x.ModifiedBy, DateCreated = x.DateCreated, DateModified = x.DateModified, //#region CampaignVM //CampaignVM = x.CampaignID == null ? null : new CampaignViewModel () //{ // ID = x.Campaign.ID, // SiteID = x.Campaign.SiteID, // Name = x.Campaign.Name, // Code = x.Campaign.Code, // Description = x.Campaign.Description, // CreatedBy = x.Campaign.CreatedBy, // ModifiedBy = x.Campaign.ModifiedBy, // DateCreated = x.Campaign.DateCreated, // DateModified = x.Campaign.DateModified, // Active = x.Campaign.Active, // #region SiteVM // SiteVM = new SiteViewModel() // { // ID = x.Campaign.Site.ID, // Name = x.Campaign.Site.Name, // Code = x.Campaign.Site.Code, // Description = x.Campaign.Site.Description, // CreatedBy = x.Campaign.Site.CreatedBy, // ModifiedBy = x.Campaign.Site.ModifiedBy, // DateCreated = x.Campaign.Site.DateCreated, // DateModified = x.Campaign.Site.DateModified, // Active = x.Campaign.Site.Active // } // #endregion //}, //#endregion //#region LobVM //LoBVM = x.LoBID == null ? null : new LoBViewModel() //{ // ID = x.LoB.ID, // CampaignID = x.LoB.CampaignID, // Name = x.LoB.Name, // Code = x.LoB.Code, // Description = x.LoB.Description, // CreatedBy = x.LoB.CreatedBy, // ModifiedBy = x.LoB.ModifiedBy, // DateCreated = x.LoB.DateCreated, // DateModified = x.LoB.DateModified, // Active = x.LoB.Active, // #region CampaignVM // CampaignVM = new CampaignViewModel() // { // ID = x.LoB.Campaign.ID, // SiteID = x.LoB.Campaign.SiteID, // Name = x.LoB.Campaign.Name, // Code = x.LoB.Campaign.Code, // Description = x.LoB.Campaign.Description, // CreatedBy = x.LoB.Campaign.CreatedBy, // ModifiedBy = x.LoB.Campaign.ModifiedBy, // DateCreated = x.LoB.Campaign.DateCreated, // DateModified = x.LoB.Campaign.DateModified, // Active = x.LoB.Campaign.Active, // #region SiteVM // SiteVM = new SiteViewModel() // { // ID = x.LoB.Campaign.Site.ID, // Name = x.LoB.Campaign.Site.Name, // Code = x.LoB.Campaign.Site.Code, // Description = x.LoB.Campaign.Site.Description, // CreatedBy = x.LoB.Campaign.Site.CreatedBy, // ModifiedBy = x.LoB.Campaign.Site.ModifiedBy, // DateCreated = x.LoB.Campaign.Site.DateCreated, // DateModified = x.LoB.Campaign.Site.DateModified, // Active = x.LoB.Campaign.Site.Active // } // #endregion // } // #endregion //}, //#endregion //#region DatapointVM //DatapointVM = new DatapointViewModel() //{ // ID = x.Datapoint.ID, // SegmentID = x.Datapoint.SegmentID, // Name = x.Datapoint.Name, // SortOrder = x.Datapoint.SortOrder, // CreatedBy = x.Datapoint.CreatedBy, // ModifiedBy = x.Datapoint.ModifiedBy, // DateCreated = x.Datapoint.DateCreated, // DateModified = x.Datapoint.DateModified, // Active = x.Datapoint.Active, // Visible = x.Datapoint.Visible, // #region DatapointCategoryVM // SegmentVM = new SegmentViewModel() // { // ID = x.Datapoint.Segment.ID, // Name = x.Datapoint.Segment.Name, // SortOrder = x.Datapoint.Segment.SortOrder, // CreatedBy = x.Datapoint.Segment.CreatedBy, // ModifiedBy = x.Datapoint.Segment.ModifiedBy, // DateCreated = x.Datapoint.Segment.DateCreated, // DateModified = x.Datapoint.Segment.DateModified, // Active = x.Datapoint.Segment.Active, // Visible = x.Datapoint.Segment.Visible // } // #endregion //} //#endregion }).AsQueryable(); return(data); }
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); }