public ServiceResult <object> GridView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tblUser = Util.GetSqlServerTableName <Users>();
var tblOrganization = Util.GetSqlServerTableName <DataLayer.Model.Core.Organization.Organization>();
var tblRole = Util.GetSqlServerTableName <DataLayer.Model.Core.Role.Role>();
var tblEntity = Util.GetSqlServerTableName <DataLayer.Model.Core.EntityState.EntityState>();
var queryString =
$"select entityId,tableName,tblEntityId,organizationName,userRoleRoleName, userLockerFirstName,userLockerLastName,fullTitle,deleted,createBy,accessKey from (select EntityId as entityId,FullTitle as fullTitle ,Deleted as deleted,CreateBy as createBy,AccessKey as accessKey,TableName as tableName,TableEntityId as tblEntityId,mainOrganization.orgName as organizationName,mainRole.roleName as userRoleRoleName,mainUser.userFirstName as userLockerFirstName,mainUser.lastName as userLockerLastName from {tblEntity} as mainData left join (select EntityId as roleEntityId,RoleName as roleName from {tblRole}) as mainRole on mainRole.roleEntityId = mainData.RoleId left join (select EntityId as OrganizationEntityId ,Name as orgName from {tblOrganization}) as mainOrganization on mainOrganization.OrganizationEntityId = mainData.OrganizationId left join (select EntityId as userEntityId,FirstName as userFirstName,LastName as lastName from {tblUser}) as mainUser on mainUser.userEntityId = mainData.UserId ) e " +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
queryString = QueryUtil.SetPaging(bp.Clause.PageNo, bp.Clause.PageSize, queryString);
using (var unitOfWork = new UnitOfWork())
{
var entityStateList = unitOfWork.EntityState.CreateNativeQuery(queryString, x => new[]
{
x[0] != null ? Convert.ToInt32(x[0]) : (object)null,
x[1] != null ? Convert.ToInt64(x[1]) : (object)null,
x[2]?.ToString(),
x[3]?.ToString(),
x[4]?.ToString(),
x[5]?.ToString(),
x[6]?.ToString(),
x[7]?.ToString()
});
if (entityStateList.Count == 0)
{
return(new ServiceResult <object>(new List <EntityStateDto>(), 0));
}
var list = new List <object>();
var headerTitle = new object[]
{
"entityId", "tblName", "tblEntityId", "organizationName", "userRoleRoleName",
"userLockerFirstName", "userLockerLastName"
};
list.Add(headerTitle);
list.AddRange(entityStateList);
return(new ServiceResult <object>(list, entityStateList.Count));
}
}
catch (Exception e)
{
return(ExceptionUtil.ExceptionHandler(e, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
public ServiceResult <object> GridView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <Users>();
var queryString =
$"select entityId,firstName,lastName,username,attempt,lastVisit,deleted,fullTitle,createBy,accessKey from (select EntityId as entityId, FirstName as firstName,LastName as lastName,Username as username,Attempt as attempt,LastVisit as lastVisit,FullTitle as fullTitle,Deleted as deleted,CreateBy as createBy,AccessKey as accessKey from {tableName} where code <> 'ADMIN') e " +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
queryString = QueryUtil.SetPaging(bp.Clause.PageNo, bp.Clause.PageSize, queryString);
using (var unitOfWork = new UnitOfWork())
{
var usersList = unitOfWork.Users.CreateNativeQuery(queryString, x => new[]
{
x[0] != null ? Convert.ToInt32(x[0]) : (object)null,
x[1] != null ? Convert.ToInt32(x[1]) : (object)null,
x[2]?.ToString(),
x[3]?.ToString(),
x[4]?.ToString(),
x[5]?.ToString(),
Util.GetTimeStamp(string.IsNullOrEmpty(x[6]?.ToString())
? (DateTime?)null
: Convert.ToDateTime(x[6].ToString()))
});
if (usersList.Count == 0)
{
return(new ServiceResult <object>(new List <PersonDto>(), 0));
}
var list = new List <object>();
var headerTitle = new object[]
{ "entityId", "firstName", "lastName", "username", "attempt", "lastVisit" };
list.Add(headerTitle);
list.AddRange(usersList);
return(new ServiceResult <object>(list, usersList.Count));
}
}
catch (Exception e)
{
return(ExceptionUtil.ExceptionHandler(e, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
public ServiceResult <object> GridView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.Person.Person>();
var queryString =
$"select * from (select entityId,firstName,lastName,NationalCode,fatherName,birthDate,deleted,fullTitle,createBy,accessKey from {tableName} where Code <> 'ADMIN') e " +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
queryString = QueryUtil.SetPaging(bp.Clause.PageNo, bp.Clause.PageSize, queryString);
using (var unitOfWork = new UnitOfWork())
{
var personList = unitOfWork.Person.CreateNativeQuery(queryString, x => new[]
{
x[0] != null ? Convert.ToInt32(x[0]) : (object)null,
x[1] != null ? Convert.ToInt64(x[1]) : (object)null,
x[2]?.ToString(),
x[3]?.ToString(),
x[4]?.ToString(),
x[5]?.ToString(),
Util.GetTimeStamp(string.IsNullOrEmpty(x[6]?.ToString())
? (DateTime?)null
: Convert.ToDateTime(x[6].ToString()))
});
if (personList.Count == 0)
{
return(new ServiceResult <object>(new List <PersonDto>(), 0));
}
var list = new List <object>();
var headerTitle = new object[]
{ "entityId", "firstName", "lastName", "nationalCode", "fatherName", "birthDate" };
list.Add(headerTitle);
list.AddRange(personList);
return(new ServiceResult <object>(list, personList.Count));
}
}
catch (Exception e)
{
return(ExceptionUtil.ExceptionHandler(e, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
public ServiceResult <object> GridView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.ComboVal.ComboVal>();
var queryString =
"select entityId,name,value,code,parentId,fullTitle,createBy,accessKey from (select data.EntityId as entityId,data.Name as name,data.Value as value,data.Code as code,parent.Name as parentId,data.FullTitle as fullTitle,data.Deleted as deleted,data.CreateBy as createBy,data.AccessKey as accessKey from (select EntityId,Name,Value,Code,ParentId,FullTitle,Deleted,AccessKey,CreateBy from " +
tableName + " where Deleted =0) data left join(select EntityId, Name from " + tableName +
" where Deleted = 0) parent on data.ParentId = parent.EntityId ) e " +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
queryString = QueryUtil.SetPaging(bp.Clause.PageNo, bp.Clause.PageSize, queryString);
using (var unitOfWork = new UnitOfWork())
{
var comboList = unitOfWork.ComboVal.CreateNativeQuery(queryString, x => new[]
{
x[0] != null ? Convert.ToInt32(x[0]) : (object)null,
x[1] != null ? Convert.ToInt64(x[1]) : (object)null,
x[2]?.ToString(),
x[3]?.ToString(),
x[4]?.ToString(),
x[5]?.ToString()
});
if (comboList.Count == 0)
{
return(new ServiceResult <object>(new List <ComboValDto>(), 1));
}
var list = new List <object>();
var headerTitle = new object[] { "entityId", "name", "value", "code", "parentName" };
list.Add(headerTitle);
list.AddRange(comboList);
return(new ServiceResult <object>(list, comboList.Count));
}
}
catch (Exception e)
{
return(ExceptionUtil.ExceptionHandler(e, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
public ServiceResult <object> GridView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.Menu.Menu>();
var tblUseCase = Util.GetSqlServerTableName <DataLayer.Model.Core.UseCase.UseCase>();
var queryString =
$"select entityId,useCaseName,name,title,orderNode,parentName,useCase,parentId,deleted,createBy,fullTitle,accessKey from ( select EntityId as entityId, Name as name,Title as title, OrderNode as orderNode,useCaseTarget.useCaseName as useCaseName ,parentMenu.parentName as parentName, ParentId as parentId, UseCase as useCase,Deleted as deleted , CreateBy as createBy , AccessKey as accessKey, FullTitle as fullTitle from {tableName} as mainData left join (select EntityId as useCaseEntityId,UseCaseName as useCaseName from {tblUseCase}) as useCaseTarget on useCaseTarget.useCaseEntityId = mainData.UseCase left join (select EntityId as parentEntityId , Name as parentName from {tableName}) as parentMenu on parentMenu.parentEntityId = mainData.ParentId ) e " +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
queryString = QueryUtil.SetPaging(bp.Clause.PageNo, bp.Clause.PageSize, queryString);
using (var unitOfWork = new UnitOfWork())
{
var lstMenu = unitOfWork.Menu.CreateNativeQuery(queryString, x => new[]
{
x[0] != null ? Convert.ToInt32(x[0]) : (object)null,
x[1] != null ? Convert.ToInt64(x[1]) : (object)null,
x[2]?.ToString(),
x[3]?.ToString(),
x[4]?.ToString(),
x[5]?.ToString(),
x[6]?.ToString()
});
if (lstMenu.Count == 0)
{
return(new ServiceResult <object>(new List <MenuDto>(), 0));
}
var list = new List <object>();
var headerTitle = new object[] { "entityId", "useCase", "name", "title", "orderNode", "parent" };
list.Add(headerTitle);
list.AddRange(lstMenu);
return(new ServiceResult <object>(list, lstMenu.Count));
}
}
catch (Exception e)
{
return(ExceptionUtil.ExceptionHandler(e, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
public ServiceResult <object> GridView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.UseCase.UseCase>();
var queryString =
$"select entityId,useCaseName,clazz,tableName,uName as parent,ParentId,createBy,accessKey,fullTitle,deleted from (select * from (select EntityId as entityId, UseCaseName as useCaseName, Clazz as clazz, TableName as tableName, ParentId, CreateBy as createBy, FullTitle as fullTitle, AccessKey As accessKey, Deleted as deleted from {tableName} where code <> 'ADMIN') as currentUseCase left join(select EntityId as eId, UseCaseName as uName from {tableName}) as parentUseCase on currentUseCase.ParentId = parentUseCase.eId) e" +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
queryString = QueryUtil.SetPaging(bp.Clause.PageNo, bp.Clause.PageSize, queryString);
using (var unitOfWork = new UnitOfWork())
{
var useCaseList = unitOfWork.UseCase.CreateNativeQuery(queryString, x => new[]
{
x[0] != null ? Convert.ToInt32(x[0]) : (object)null,
x[1] != null ? Convert.ToInt64(x[1]) : (object)null,
x[2]?.ToString(),
x[3]?.ToString(),
x[4]?.ToString(),
x[5]?.ToString()
});
if (useCaseList.Count == 0)
{
return(new ServiceResult <object>(new List <UseCaseDto>(), 0));
}
var list = new List <object>();
var headerTitle = new object[] { "entityId", "useCaseName", "clazz", "tableName", "parent" };
list.Add(headerTitle);
list.AddRange(useCaseList);
return(new ServiceResult <object>(list, useCaseList.Count));
}
}
catch (Exception e)
{
return(ExceptionUtil.ExceptionHandler(e, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
public ServiceResult <object> GridView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.Location.Location>();
var tableNameComboVal = Util.GetSqlServerTableName <DataLayer.Model.Core.ComboVal.ComboVal>();
var queryString =
$"select entityId,name,typeName,type,parentName,parentId,fullTitle,deleted,createBy,accessKey from ( select combo.comboName as typeName,parent.parentName as parentName, EntityId as entityId, Name as name,Type as type,ParentId as parentId,FullTitle as fullTitle,Deleted as deleted,CreateBy as createBy,AccessKey as accessKey from {tableName} as mainLocation left join (select EntityId as comboId,Name as comboName from {tableNameComboVal}) as combo on combo.comboId = mainLocation.type left join (select EntityId as parentEntityId,Name as parentName from {tableName}) as parent on parent.parentEntityId = mainLocation.ParentId ) e " +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
queryString = QueryUtil.SetPaging(bp.Clause.PageNo, bp.Clause.PageSize, queryString);
using (var unitOfWork = new UnitOfWork())
{
var locationList = unitOfWork.Location.CreateNativeQuery(queryString, x => new[]
{
x[0] != null ? Convert.ToInt32(x[0]) : (object)null,
x[1] != null ? Convert.ToInt64(x[1]) : (object)null,
x[2]?.ToString(),
x[3]?.ToString(),
x[5]?.ToString()
});
if (locationList.Count == 0)
{
return(new ServiceResult <object>(new List <LocationDto>(), 0));
}
var list = new List <object>();
var headerTitle = new object[] { "entityId", "name", "type", "parent" };
list.Add(headerTitle);
list.AddRange(locationList);
return(new ServiceResult <object>(list, locationList.Count));
}
}
catch (Exception e)
{
return(ExceptionUtil.ExceptionHandler(e, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
public ServiceResult <object> GridView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.Action.Action>();
var queryString =
$"select * from (select EntityId as entityId,ActionName as actionName,ActionEnName as actionEnName,FullTitle as fullTitle,Deleted as deleted,AccessKey as accessKey,CreateBy as createBy from {tableName}) e " +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
queryString = QueryUtil.SetPaging(bp.Clause.PageNo, bp.Clause.PageSize, queryString);
using (var unitOfWork = new UnitOfWork())
{
var actionList = unitOfWork.Action.CreateNativeQuery(queryString, x => new[]
{
x[0] != null ? Convert.ToInt32(x[0]) : (object)null,
x[1] != null ? Convert.ToInt64(x[1]) : (object)null,
x[2]?.ToString(),
x[3]?.ToString()
});
if (actionList.Count == 0)
{
return(new ServiceResult <object>(new List <ActionDto>(), 0));
}
var list = new List <object>();
var headerTitle = new object[] { "entityId", "actionName", "actionEnName" };
list.Add(headerTitle);
list.AddRange(actionList);
return(new ServiceResult <object>(list, actionList.Count));
}
}
catch (Exception e)
{
return(ExceptionUtil.ExceptionHandler(e, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
