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