public IEnumerable <MemberListViewModel> GetMembers(MemberFilterModel filter, GridState gridState)
{
IEnumerable <MemberListViewModel> result = null;
result = this.MemberRepository.GetMembers(filter, gridState);
return(result);
}
public ActionResult Edit(MemberFilterModel filter)
{
var result = MemberWorkflow.GetMemberDetail(filter.Id);
ViewData.Model = result;
return(View());
}
// GET: Demo
public ActionResult Index(MemberFilterModel filter, GridState gridstate)
{
gridstate.PageSize = 10;
var querable = MemberWorkflow.GetMembers(filter, gridstate);
ViewData.Model = querable;
return(View());
}
public GetMembersModel GetMembers([FromUri] MemberFilterModel value)
{
if (ModelState.IsValid)
{
var val = value;
return(this._member.GetMembers(value));
}
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.BadRequest, new { Message = ModelState.GetErrorModelState() }));
}
public List <MemberDataModel> QueryUserData(MemberFilterModel arg)
{
string sql = @"SELECT U.ACCOUNT AS Account
,PASSWORD AS Password
,EMAIL AS Email
,PHONE AS Phone
,CONVERT(varchar(100), BIRTHDAY, 23) As Birthday
,G.GENDER_CODE AS GenderCode
,G.GENDER_TEXT As GenderText
,U.USER_ID AS UserId
,U.E_NAME AS EName
,U.C_NAME AS CName
,U.PER_SERIL_NO AS PerSerilNo
,T.TITLE_NAME AS TitleName
,T.TITLE_CODE AS TitleCode
FROM USERS AS U
LEFT JOIN GENDER_CODE AS G
ON U.GENDER_CODE = G.GENDER_CODE
LEFT JOIN TITLE_CODE AS T
ON U.TITLE_CODE = T.TITLE_CODE
WHERE U.IS_OUT <> 1 ";
if (arg == null)
{
arg = new MemberFilterModel();
}
if (!string.IsNullOrEmpty(arg.Account))
{
sql += "AND ACCOUNT LIKE @Account ";
}
object parameters = new
{
Account = arg.Account == null ? string.Empty : '%' + arg.Account + '%'
};
using (var connection = new SqlConnection(this.GetDbConnectionString()))
{
List <MemberDataModel> result = connection.Query <MemberDataModel>(sql, parameters).ToList();
return(result);
}
}
private IQueryable <MemberListViewModel> ApplyFilter(MemberFilterModel filter, IQueryable <MemberListViewModel> queryable)
{
if (filter.WorkId.HasValue)
{
queryable = queryable.Where(p => p.WorkId == filter.WorkId);
}
if (!string.IsNullOrWhiteSpace(filter.CompanyName))
{
queryable = queryable.Where(p => p.CompanyName.Contains(filter.CompanyName));
}
if (!string.IsNullOrWhiteSpace(filter.Name))
{
queryable = queryable.Where(p => p.Name == filter.Name);
}
if (!string.IsNullOrWhiteSpace(filter.Email))
{
queryable = queryable.Where(p => p.Email == filter.Email);
}
if (filter.Admin.HasValue)
{
queryable = queryable.Where(p => p.Admin == filter.Admin);
}
if (filter.Member.HasValue)
{
queryable = queryable.Where(p => p.Member == filter.Member);
}
if (filter.Lock.HasValue)
{
queryable = queryable.Where(p => p.Lock == filter.Lock);
}
return(queryable);
}
public IEnumerable <MemberListViewModel> GetMembers(MemberFilterModel filter, GridState gridState)
{
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
IEnumerable <MemberListViewModel> results = null;
using (var dbcontext = MemberDbContext.CreateContext())
{
var queryable = this.ApplyJoin(dbcontext.Member, dbcontext.Identity, dbcontext.Role,
dbcontext.CompanyConfing, dbcontext.MemberActivity);
queryable = this.ApplyFilter(filter, queryable);
queryable = this.ApplyPaging(gridState, queryable);
results = queryable.AsNoTracking().ToList();
}
return(results);
}
public IHttpActionResult QueryUserData(MemberFilterModel arg)
{
ApiResult apiResult = new ApiResult();
MemberDataModel user = this.GetUser();
if (user == null || !MemberService.IsAdmin(user))
{
apiResult.Message = "非法使用";
apiResult.Status = Models.Enum.ApiStatus.CustomerError;
return Ok(apiResult);
}
try
{
apiResult.Data = this.MemberService.QueryUserData(arg);
apiResult.Message = "查詢成功";
apiResult.Status = Models.Enum.ApiStatus.Success;
}
catch (Exception e)
{
apiResult.Message = "系統錯誤，請聯絡系統管理員";
apiResult.Status = Models.Enum.ApiStatus.Fail;
}
return Ok(apiResult);
}
public static ApiTableResponseModel <MemberModel> GetFilteredMembers_Dapper(MemberFilterModel filter)
{
ApiTableResponseModel <MemberModel> response = new ApiTableResponseModel <MemberModel>();
var q =
new Query("Members")
.LeftJoin("Lookup_AgeCategories", "Members.AgeCategoryID", "Lookup_AgeCategories.ID")
.LeftJoin("ContactData", "Members.ContactDataID", "ContactData.ContactDataID")
.Select("MemberID", "FirstName", "LastName", "IsActive", "IsCompetitor", "JMBG", "BirthDate", "BirthPlace", "AgeCategoryID", "Note")
.SelectRaw("FirstName + ' ' + LastName as FullName")
.SelectRaw("Lookup_AgeCategories.Name as AgeCategory")
.SelectRaw("dbo.fnGetMemberDanceGroups(MemberID) as DanceGroups")
.SelectRaw("'' as Split")
.SelectRaw("ContactData.Address, ContactData.Email, ContactData.Phone1, ContactData.Phone2, ContactData.Phone3");
if (filter != null)
{
bool excludeNonActive = true;
if (filter.ExcludeNonActive.HasValue)
{
excludeNonActive = (bool)filter.ExcludeNonActive;
}
if (excludeNonActive)
{
q = q.WhereRaw("IsActive = 1");
}
if (!string.IsNullOrEmpty(filter.FullName))
{
q = q.WhereRaw("lower(FirstName + ' ' + LastName) like '%" + filter.FullName.ToLower() + "%'");
}
if (!string.IsNullOrEmpty(filter.JMBG))
{
q = q.WhereRaw("lower(JMBG) like '" + filter.JMBG.ToLower() + "%'");
}
if (filter.DanceGroupID.HasValue)
{
q = q.WhereRaw(filter.DanceGroupID + " in (select DanceGroupID from fnGetMemberDanceGroupsAsTable(MemberID))");
}
// paging & sorting
if (string.IsNullOrEmpty(filter.OrderByClause))
{
// default order
filter.OrderByClause = "FullName";
}
if (filter.PageNo < 1)
{
filter.PageNo = 1;
}
if (filter.RecordsPerPage < 1)
{
// unlimited
filter.RecordsPerPage = 1000000;
}
}
var compiler = new SqlServerCompiler();
SqlResult result = compiler.Compile(q);
string sql = result.Sql;
string connectionString = DALHelper.GetSqlConnectionStringFromEF();
List <MemberModel> members = new List <MemberModel>();
using (var connection = new SqlConnection(connectionString))
{
// Total
response.Total = connection.Query <MemberModel>(sql).Count();
// Data
members = connection.Query <MemberModel, ContactDataModel, MemberModel>(
sql,
(member, contactData) => {
member.ContactData = contactData;
return(member);
},
splitOn: "Split"
)
.OrderBy(filter.OrderByClause)
.Skip((filter.PageNo - 1) * filter.RecordsPerPage)
.Take(filter.RecordsPerPage)
.AsList();
}
response.Data = members;
return(response);
}
public ApiTableResponseModel <MemberModel> GetFilteredMembers_v2(MemberFilterModel filter)
{
return(DB.Members.GetFilteredMembers_Dapper_v2(filter));
}
public List <MemberDataModel> QueryUserData(MemberFilterModel arg)
{
return(this.MemberDao.QueryUserData(arg));
}
public GetMembersModel GetMembers(MemberFilterModel filters)
{
if (!string.IsNullOrEmpty(filters.searchType) && filters.searchType.Equals("updated"))
{
var param = HttpContext.Current.Request.Params;
var fromDate = param.Get("searchText[from]").Replace(" GMT+0700 (Indochina Time)", "");
var toDate = param.Get("searchText[to]").Replace(" GMT+0700 (Indochina Time)", "");
filters.searchText = $"{fromDate},{toDate}";
}
var members = this.Members.Select(m => new GetMember
{
id = m.id,
email = m.email,
firstname = m.firstname,
lastname = m.lastname,
position = m.position,
role = m.role,
updated = m.updated
}).OrderByDescending(m => m.updated);
var memberFilter = new GetMembersModel
{
items = members.Skip((filters.startPage - 1) * filters.limitPage)
.Take(filters.limitPage)
.ToArray(),
totalItems = members.Count()
};
if (!string.IsNullOrEmpty(filters.searchType) && !string.IsNullOrEmpty(filters.searchText))
{
string searchText = filters.searchText;
string searchType = filters.searchType;
IEnumerable <GetMember> searchMembers = new GetMember[] { };
switch (searchType)
{
case "updated":
var dates = searchText.Split(',');
DateTime fromDate = DateTime.Parse(dates[0]);
DateTime toDate = DateTime.Parse(dates[1]);
searchMembers = from m in members
where m.updated >= fromDate && m.updated <= toDate
select m;
break;
case "role":
searchText = Enum.Parse(typeof(RoleAccount), searchText).ToString();
searchMembers = from m in members
where m.GetType().
GetProperty(filters.searchType).
GetValue(m).
ToString().
Contains(searchText)
select m;
break;
default:
searchMembers = from m in members
where m.GetType().
GetProperty(filters.searchType).
GetValue(m).
ToString().
ToLower().
Contains(searchText.ToLower())
select m;
break;
}
//if (filters.searchType.Equals("role"))
//{
// searchText = Enum.Parse(typeof(RoleAccount), searchText).ToString().ToLower();
//}
memberFilter.items = searchMembers.Skip((filters.startPage - 1) * filters.limitPage)
.Take(filters.limitPage)
.ToArray();
memberFilter.totalItems = searchMembers.Count();
}
return(memberFilter);
}