public ActionResult IndexAjax(WorkerSearchViewModel searchViewModel, [DataSourceRequest] DataSourceRequest dsRequest)
{
if (dsRequest.Filters == null)
{
dsRequest.Filters = new List <IFilterDescriptor>();
}
DataSourceResult result = workerRepository.Search(searchViewModel, dsRequest);
foreach (WorkerListViewModel o in result.Data)
{
o.WorkerInRoleList = workerinroleRepository.FindAllByWorkerID(o.ID).AsEnumerable().Select(item => new WorkerInRole()
{
ID = item.ID, WorkerRoleName = item.WorkerRole.Name, ProgramName = item.Program.Name, RegionName = item.Region.Name, EffectiveFrom = item.EffectiveFrom, EffectiveTo = item.EffectiveTo
}).ToList();
if (o.WorkerInRoleList != null)
{
foreach (WorkerInRole role in o.WorkerInRoleList)
{
List <WorkerSubProgram> subProgramList = workersubprogramRepository.FindAllByWorkerInRoleID(role.ID);
if (subProgramList != null)
{
foreach (WorkerSubProgram subProgram in subProgramList)
{
role.SubProgramNames = role.SubProgramNames.Concate(',', subProgram.SubProgram.Name);
}
}
}
}
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
private void SearchOrders(WorkerSearchViewModel model)
{
var searchModel = new WorkerSearchModel
{
PageIndex = model.PageIndex,
PageSize = model.PageSize,
KeyWord = model.KeyWord
};
model.ViewList = _workerService.Search(searchModel);
}
public ActionResult Index()
{
try
{
var model = new WorkerSearchViewModel();
SearchOrders(model);
return(View(model));
}
catch (Exception ex)
{
ErrorNotification(ex);
return(View());
}
}
public ActionResult IndexAjax(WorkerSearchViewModel searchViewModel, [DataSourceRequest] DataSourceRequest dsRequest)
{
if (dsRequest.Filters == null)
{
dsRequest.Filters = new List <IFilterDescriptor>();
}
DataSourceResult result = workerRepository.Search(searchViewModel, dsRequest);
foreach (WorkerListViewModel o in result.Data)
{
o.WorkerInRoleNewList = workerinrolenewRepository.FindAllByWorkerID(o.ID).AsEnumerable().Select(item => new WorkerInRoleNew()
{
ID = item.ID, WorkerRoleName = item.WorkerRole.Name, EffectiveFrom = item.EffectiveFrom, EffectiveTo = item.EffectiveTo
}).ToList();
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
//public string FindWorkerNameByCaseWorkerID(int caseWorkerID)
//{
// return context.CaseWorker.Join(context.Worker, left => left.WorkerID, right => right.ID, (left, right) => new { left, right }).Where(item => item.left.ID == caseWorkerID).Select(item =>item.right.FirstName +" "+item.right.LastName).FirstOrDefault();
//}
public DataSourceResult Search(WorkerSearchViewModel searchParameters, DataSourceRequest paramDSRequest)
{
DataSourceRequest dsRequest = paramDSRequest;
if (dsRequest == null)
{
dsRequest = new DataSourceRequest();
}
if (dsRequest.Filters == null || (dsRequest.Filters != null && dsRequest.Filters.Count == 0))
{
if (dsRequest.Filters == null)
{
dsRequest.Filters = new List <IFilterDescriptor>();
}
}
if (dsRequest.Sorts == null || (dsRequest.Sorts != null && dsRequest.Sorts.Count == 0))
{
if (dsRequest.Sorts == null)
{
dsRequest.Sorts = new List <SortDescriptor>();
}
SortDescriptor defaultSortExpression = new SortDescriptor("LastUpdateDate", System.ComponentModel.ListSortDirection.Descending);
dsRequest.Sorts.Add(defaultSortExpression);
}
if (dsRequest.PageSize == 0)
{
dsRequest.PageSize = Constants.CommonConstants.DefaultPageSize;
}
StringBuilder sqlQuery = new StringBuilder(@"SELECT
*,
[W].[FirstName]+' '+[W].[LastName] [Name]
FROM Worker [W]
left join WorkerInRole WIR on W.ID = WIR.WorkerID
WHERE [W].[ID]>0");
if (searchParameters.RegionID > 0)
{
searchParameters.RegionID = searchParameters.RegionID;
sqlQuery.Append(" AND [WIR].[RegionID] =" + searchParameters.RegionID + "");
}
else if (IsCurrentLoggedInWorkerRegionalAdministrator)
{
searchParameters.RegionID = searchParameters.RegionID;
sqlQuery.Append(" AND [WIR].[RegionID] = (Select RegionID from WorkerInRole WIR where WorkerRoleID=" + SiteConfigurationReader.RegionalAdministratorRoleID + " and WorkerID=" + CurrentLoggedInWorker.ID + ")");
sqlQuery.Append(" AND [W].[ID] !=1");
}
if (searchParameters.FirstName.IsNotNullOrEmpty())
{
searchParameters.FirstName = searchParameters.FirstName.Trim();
sqlQuery.Append(" AND [W].[FirstName] LIKE '%" + searchParameters.FirstName + "%'");
}
if (searchParameters.LastName.IsNotNullOrEmpty())
{
searchParameters.LastName = searchParameters.LastName.Trim();
sqlQuery.Append(" AND [W].[LastName] LIKE '%" + searchParameters.LastName + "%'");
}
if (searchParameters.RoleID > 0)
{
sqlQuery.Append(" AND [W].[ID] IN (SELECT [WorkerID] FROM WorkerInRole WHERE WorkerRoleID=" + searchParameters.RoleID + ")");
}
if (searchParameters.ProgramID > 0)
{
sqlQuery.Append(" AND [WIR].[ProgramID] =" + searchParameters.ProgramID + "");
}
//if (IsCurrentLoggedInWorkerRegionalAdministrator)
//{
// sqlQuery.Append(" AND [W].[CreatedByworkerID] = " + CurrentLoggedInWorker.ID.ToString());
//}
DataSourceResult dataSourceResult = context.Database.SqlQuery <WorkerListViewModel>(sqlQuery.ToString()).AsEnumerable().GroupBy(m => m.ID).Select(m => m.First()).ToDataSourceResult(dsRequest);
DataSourceRequest dsRequestTotalCountQuery = new DataSourceRequest();
dsRequestTotalCountQuery.Filters = dsRequest.Filters;
dataSourceResult.Total = context.Database.SqlQuery <WorkerListViewModel>(sqlQuery.ToString()).AsEnumerable().ToDataSourceResult(dsRequestTotalCountQuery).Data.AsQueryable().Count();
return(dataSourceResult);
}
//public string FindWorkerNameByCaseWorkerID(int caseWorkerID)
//{
// return context.CaseWorker.Join(context.Worker, left => left.WorkerID, right => right.ID, (left, right) => new { left, right }).Where(item => item.left.ID == caseWorkerID).Select(item =>item.right.FirstName +" "+item.right.LastName).FirstOrDefault();
//}
// public DataSourceResult Search(WorkerSearchViewModel searchParameters, DataSourceRequest paramDSRequest)
// {
// bool IsCurrentUserNotAdminRole = (CurrentLoggedInWorkerRoleIDs.IndexOf(1) == -1);
// string hasEditPermission = (workerroleactionpermissionnewRepository.HasPermission(CurrentLoggedInWorkerRoleIDs, Constants.Areas.WorkerManagement, Constants.Controllers.Worker, Constants.Actions.Edit, true)).ToDisplayStyle();
// string hasDeletePermission = (workerroleactionpermissionnewRepository.HasPermission(CurrentLoggedInWorkerRoleIDs, Constants.Areas.WorkerManagement, Constants.Controllers.Worker, Constants.Actions.Delete, true)).ToDisplayStyle();
// DataSourceRequest dsRequest = paramDSRequest;
// if (dsRequest == null)
// {
// dsRequest = new DataSourceRequest();
// }
// if (dsRequest.Filters == null || (dsRequest.Filters != null && dsRequest.Filters.Count == 0))
// {
// if (dsRequest.Filters == null)
// {
// dsRequest.Filters = new List<IFilterDescriptor>();
// }
// }
// if (dsRequest.Sorts == null || (dsRequest.Sorts != null && dsRequest.Sorts.Count == 0))
// {
// if (dsRequest.Sorts == null)
// {
// dsRequest.Sorts = new List<SortDescriptor>();
// }
// SortDescriptor defaultSortExpression = new SortDescriptor("LastUpdateDate", System.ComponentModel.ListSortDirection.Descending);
// dsRequest.Sorts.Add(defaultSortExpression);
// }
// if (dsRequest.PageSize == 0)
// {
// dsRequest.PageSize = Constants.CommonConstants.DefaultPageSize;
// }
// StringBuilder sqlQuery = new StringBuilder(@"SELECT DISTINCT
// W.ID,W.EmailAddress,W.IsActive, [W].[FirstName] + ' ' + [W].[LastName] [Name],
// '" + hasDeletePermission + @"' [HasPermissionToDelete],
// '" + hasEditPermission + @"' [HasPermissionToEdit]
// FROM Worker AS [W]
// LEFT JOIN WorkerInRoleNew WIR on W.ID = WIR.WorkerID
// LEFT JOIN WorkerRolePermissionNew WRP on WIR.WorkerRoleID = WRP.WorkerRoleID
// LEFT JOIN Permission AS P ON WRP.PermissionID = P.ID
// LEFT JOIN PermissionRegion AS PR ON P.ID = PR.PermissionID
// LEFT JOIN PermissionSubProgram AS PSP ON PR.ID = PSP.PermissionRegionID
// LEFT JOIN PermissionJamatkhana AS PJK ON PR.ID = PJK.PermissionRegionID
// WHERE [W].[ID] > 0 ");
// if (IsCurrentUserNotAdminRole)
// {
// sqlQuery.Append(" AND [W].[ID] <> " + CurrentLoggedInWorker.ID + " ");
// }
// if (searchParameters.RegionID > 0)
// {
// searchParameters.RegionID = searchParameters.RegionID;
// sqlQuery.Append(" AND [PR].[RegionID] =" + searchParameters.RegionID + "");
// }
// //else if (IsCurrentLoggedInWorkerRegionalAdministrator) //<JL:Comment:06/11/2017>
// else if (IsCurrentUserNotAdminRole) //<JL:Add:06/11/2017>
// {
// StringBuilder regionquery = new StringBuilder();
// regionquery.Append("SELECT DISTINCT PR.RegionID ");
// regionquery.Append("FROM Worker AS [W] ");
// regionquery.Append("LEFT JOIN WorkerInRoleNew WIR on W.ID = WIR.WorkerID ");
// regionquery.Append("LEFT JOIN WorkerRolePermissionNew WRP on WIR.WorkerRoleID = WRP.WorkerRoleID ");
// regionquery.Append("LEFT JOIN Permission AS P ON WRP.PermissionID = P.ID ");
// regionquery.Append("LEFT JOIN PermissionRegion AS PR ON P.ID = PR.PermissionID ");
// //regionquery.Append("WHERE [W].ID = " + CurrentLoggedInWorker.ID + " AND WIR.WorkerRoleID = " + SiteConfigurationReader.RegionalAdministratorRoleID); //<JL:Comment:06/11/2017>
// regionquery.Append("WHERE [W].ID = " + CurrentLoggedInWorker.ID); //<JL:Add:06/11/2017>
// searchParameters.RegionID = searchParameters.RegionID;
// //sqlQuery.Append(" AND [PR].[RegionID] = (SELECT RegionID FROM WorkerInRoleNew WIR WHERE WorkerRoleID = " + SiteConfigurationReader.RegionalAdministratorRoleID + " AND WorkerID = " + CurrentLoggedInWorker.ID + ")");
// sqlQuery.Append(" AND [PR].[RegionID] IN (" + regionquery.ToString() + ")");
// sqlQuery.Append(" AND [W].[ID] !=1");
// }
// if (searchParameters.FirstName.IsNotNullOrEmpty())
// {
// searchParameters.FirstName = searchParameters.FirstName.Trim();
// sqlQuery.Append(" AND [W].[FirstName] LIKE '%" + searchParameters.FirstName + "%'");
// }
// if (searchParameters.LastName.IsNotNullOrEmpty())
// {
// searchParameters.LastName = searchParameters.LastName.Trim();
// sqlQuery.Append(" AND [W].[LastName] LIKE '%" + searchParameters.LastName + "%'");
// }
// if (searchParameters.RoleID > 0)
// {
// sqlQuery.Append(" AND [W].[ID] IN (SELECT [WorkerID] FROM WorkerInRoleNew WHERE WorkerRoleID = " + searchParameters.RoleID + ")");
// }
// if (searchParameters.ProgramID > 0)
// {
// sqlQuery.Append(" AND [PR].[ProgramID] =" + searchParameters.ProgramID + "");
// }
// else if (IsCurrentUserNotAdminRole) //<JL:Add:06/11/2017>
// {
// StringBuilder programquery = new StringBuilder();
// programquery.Append("SELECT DISTINCT PR.ProgramID ");
// programquery.Append("FROM Worker AS [W] ");
// programquery.Append("INNER JOIN WorkerInRoleNew WIR on W.ID = WIR.WorkerID ");
// programquery.Append("INNER JOIN WorkerRolePermissionNew WRP on WIR.WorkerRoleID = WRP.WorkerRoleID ");
// programquery.Append("INNER JOIN Permission AS P ON WRP.PermissionID = P.ID ");
// programquery.Append("INNER JOIN PermissionRegion AS PR ON P.ID = PR.PermissionID ");
// programquery.Append("WHERE [W].ID = " + CurrentLoggedInWorker.ID);
// searchParameters.ProgramID = searchParameters.ProgramID;
// sqlQuery.Append(" AND [PR].[ProgramID] IN (" + programquery.ToString() + ")");
// sqlQuery.Append(" AND [W].[ID] !=1");
// }
// if (IsCurrentUserNotAdminRole) //<JL:Add:06/11/2017>
// {
// StringBuilder subprogramquery = new StringBuilder();
// subprogramquery.Append("SELECT DISTINCT PSP.SubProgramID ");
// subprogramquery.Append("FROM Worker AS [W] ");
// subprogramquery.Append("INNER JOIN WorkerInRoleNew WIR on W.ID = WIR.WorkerID ");
// subprogramquery.Append("INNER JOIN WorkerRolePermissionNew WRP on WIR.WorkerRoleID = WRP.WorkerRoleID ");
// subprogramquery.Append("INNER JOIN Permission AS P ON WRP.PermissionID = P.ID ");
// subprogramquery.Append("INNER JOIN PermissionRegion AS PR ON P.ID = PR.PermissionID ");
// subprogramquery.Append("INNER JOIN PermissionSubProgram AS PSP ON PR.ID = PSP.PermissionRegionID ");
// subprogramquery.Append("WHERE [W].ID = " + CurrentLoggedInWorker.ID);
// sqlQuery.Append(" AND [PSP].[SubProgramID] IN (" + subprogramquery.ToString() + ")");
// sqlQuery.Append(" AND [W].[ID] !=1");
// StringBuilder jkquery = new StringBuilder();
// jkquery.Append("SELECT DISTINCT PJK.JamatkhanaID ");
// jkquery.Append("FROM Worker AS [W] ");
// jkquery.Append("INNER JOIN WorkerInRoleNew WIR on W.ID = WIR.WorkerID ");
// jkquery.Append("INNER JOIN WorkerRolePermissionNew WRP on WIR.WorkerRoleID = WRP.WorkerRoleID ");
// jkquery.Append("INNER JOIN Permission AS P ON WRP.PermissionID = P.ID ");
// jkquery.Append("INNER JOIN PermissionRegion AS PR ON P.ID = PR.PermissionID ");
// jkquery.Append("LEFT JOIN PermissionJamatkhana AS PJK ON PR.ID = PJK.PermissionRegionID ");
// jkquery.Append("WHERE [W].ID = " + CurrentLoggedInWorker.ID);
// sqlQuery.Append(" AND [PJK].[JamatkhanaID] IN (" + jkquery.ToString() + ")");
// }
// DataSourceResult dataSourceResult = context.Database.SqlQuery<WorkerListViewModel>(sqlQuery.ToString()).AsEnumerable().GroupBy(m=>m.ID).Select(m=>m.First()).ToDataSourceResult(dsRequest);
// DataSourceRequest dsRequestTotalCountQuery = new DataSourceRequest();
// dsRequestTotalCountQuery.Filters = dsRequest.Filters;
// dataSourceResult.Total = context.Database.SqlQuery<WorkerListViewModel>(sqlQuery.ToString()).AsEnumerable().ToDataSourceResult(dsRequestTotalCountQuery).Data.AsQueryable().Count();
// return dataSourceResult;
// }
public DataSourceResult Search(WorkerSearchViewModel searchParameters, DataSourceRequest paramDSRequest)
{
bool IsCurrentUserNotAdminRole = (CurrentLoggedInWorkerRoleIDs.IndexOf(1) == -1);
string hasEditPermission = (workerroleactionpermissionnewRepository.HasPermission(CurrentLoggedInWorkerRoleIDs, Constants.Areas.WorkerManagement, Constants.Controllers.Worker, Constants.Actions.Edit, true)).ToDisplayStyle();
string hasDeletePermission = (workerroleactionpermissionnewRepository.HasPermission(CurrentLoggedInWorkerRoleIDs, Constants.Areas.WorkerManagement, Constants.Controllers.Worker, Constants.Actions.Delete, true)).ToDisplayStyle();
DataSourceRequest dsRequest = paramDSRequest;
if (dsRequest == null)
{
dsRequest = new DataSourceRequest();
}
if (dsRequest.Filters == null || (dsRequest.Filters != null && dsRequest.Filters.Count == 0))
{
if (dsRequest.Filters == null)
{
dsRequest.Filters = new List <IFilterDescriptor>();
}
}
if (dsRequest.Sorts == null || (dsRequest.Sorts != null && dsRequest.Sorts.Count == 0))
{
if (dsRequest.Sorts == null)
{
dsRequest.Sorts = new List <SortDescriptor>();
}
SortDescriptor defaultSortExpression = new SortDescriptor("LastUpdateDate", System.ComponentModel.ListSortDirection.Descending);
dsRequest.Sorts.Add(defaultSortExpression);
}
if (dsRequest.PageSize == 0)
{
dsRequest.PageSize = Constants.CommonConstants.DefaultPageSize;
}
StringBuilder sqlQuery;
sqlQuery = new StringBuilder(@"SELECT DISTINCT
W.ID,W.EmailAddress,W.IsActive, [W].[FirstName] + ' ' + [W].[LastName] [Name],
'" + hasDeletePermission + @"' [HasPermissionToDelete],
'" + hasEditPermission + @"' [HasPermissionToEdit]
FROM Worker AS [W] ");
if (IsCurrentUserNotAdminRole)
{
sqlQuery.Append(" INNER JOIN WorkerInRoleNew WIR on W.ID = WIR.WorkerID");
sqlQuery.Append(" INNER JOIN WorkerRolePermissionNew WRP on WIR.WorkerRoleID = WRP.WorkerRoleID");
if (searchParameters.RegionID > 0 || searchParameters.ProgramID > 0)
{
sqlQuery.Append(" INNER JOIN PermissionRegion AS PR ON WRP.PermissionID = PR.PermissionID");
if (searchParameters.RegionID > 0)
{
sqlQuery.Append(" AND [PR].[RegionID] =" + searchParameters.RegionID + "");
}
if (searchParameters.ProgramID > 0)
{
sqlQuery.Append(" AND [PR].[ProgramID] =" + searchParameters.ProgramID + "");
}
}
sqlQuery.Append(" INNER JOIN (");
sqlQuery.Append(" SELECT WRP.PermissionID");
sqlQuery.Append(" FROM WorkerInRoleNew AS WIR ");
sqlQuery.Append(" INNER JOIN WorkerRolePermissionNew WRP ON WIR.WorkerRoleID = WRP.WorkerRoleID");
sqlQuery.Append(" WHERE [WIR].WorkerID = " + CurrentLoggedInWorker.ID + " ");
sqlQuery.Append(" ) AS CW ON [WRP].[PermissionID] = CW.PermissionID");
}
else
{
sqlQuery.Append(" LEFT JOIN WorkerInRoleNew WIR on W.ID = WIR.WorkerID");
sqlQuery.Append(" LEFT JOIN WorkerRolePermissionNew WRP on WIR.WorkerRoleID = WRP.WorkerRoleID");
if (searchParameters.RegionID > 0 || searchParameters.ProgramID > 0)
{
sqlQuery.Append(" LEFT JOIN PermissionRegion AS PR ON WRP.PermissionID = PR.PermissionID");
if (searchParameters.RegionID > 0)
{
sqlQuery.Append(" AND [PR].[RegionID] =" + searchParameters.RegionID + "");
}
if (searchParameters.ProgramID > 0)
{
sqlQuery.Append(" AND [PR].[ProgramID] =" + searchParameters.ProgramID + "");
}
}
}
sqlQuery.Append(" WHERE [W].[ID] > 0 ");
if (IsCurrentUserNotAdminRole)
{
sqlQuery.Append(" AND [W].[ID] NOT IN (" + CurrentLoggedInWorker.ID + ",1) ");
}
if (searchParameters.FirstName.IsNotNullOrEmpty())
{
searchParameters.FirstName = searchParameters.FirstName.Trim();
sqlQuery.Append(" AND [W].[FirstName] LIKE '%" + searchParameters.FirstName + "%'");
}
if (searchParameters.LastName.IsNotNullOrEmpty())
{
searchParameters.LastName = searchParameters.LastName.Trim();
sqlQuery.Append(" AND [W].[LastName] LIKE '%" + searchParameters.LastName + "%'");
}
if (searchParameters.RoleID > 0)
{
sqlQuery.Append(" AND [W].[ID] IN (SELECT [WorkerID] FROM WorkerInRoleNew WHERE WorkerRoleID = " + searchParameters.RoleID + ")");
}
DataSourceResult dataSourceResult = context.Database.SqlQuery <WorkerListViewModel>(sqlQuery.ToString()).AsEnumerable().GroupBy(m => m.ID).Select(m => m.First()).ToDataSourceResult(dsRequest);
DataSourceRequest dsRequestTotalCountQuery = new DataSourceRequest();
dsRequestTotalCountQuery.Filters = dsRequest.Filters;
dataSourceResult.Total = context.Database.SqlQuery <WorkerListViewModel>(sqlQuery.ToString()).AsEnumerable().ToDataSourceResult(dsRequestTotalCountQuery).Data.AsQueryable().Count();
return(dataSourceResult);
}
