public IHttpActionResult GetList([FromUri] PersonReq req)
{
if (null == req)
{
req = new PersonReq();
}
if (!req.ICCardNo.IsBlank())
{
var persons = cardService.GetCardListByCardNo(req.ICCardNo);
req.PersonIds = persons;
}
if (!req.IDCardInternalNO.IsBlank())
{
var persons = cardService.GetCardListByCardNo(req.IDCardInternalNO);
req.PersonIds = req.PersonIds.Union(persons).ToList <string>();
}
var table = personService.GetList(req);
return(Ok(new
{
code = 0,
msg = "success",
data = table
}));
}
/// <summary>
/// 取消无用
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
//public TableData GetList222(PersonReq req)
//{
// var query = _personRepository.Table.Where(a => !a.Deleted);
// if (req.AreaUUID.IsNotBlank())
// {
// if (req.AreaUUID == "uncheckin")
// {
// // 未入住
// query = query.Where(a => a.IsLived==0 && a.PersonType != 273);
// }
// else if (req.AreaUUID == "managecard")
// {
// query = query.Where(a => a.PersonType == 273);
// }
// else
// {
// var area = _areaRepository.GetById(req.AreaUUID);
// if (area != null)
// {
// query = from p in _personRepository.Table
// join ru in _roomUserRepository.Table on p.PersonUUID equals ru.PersonUUID
// join r in _roomRepository.Table on ru.RoomUUID equals r.RoomUUID
// join ar in _areaRepository.Table on r.AreaUUID equals ar.AreaUUID
// where !p.Deleted && !ru.Deleted
// && ar.Code.StartsWith(area.Code) && ru.Status == 1
// orderby p.CreateTime descending
// select p;
// }
// }
// }
// if (!req.PersonName.IsBlank())
// {
// query = query.Where(a => a.PersonName.Contains(req.PersonName));
// }
// if (req.BirthDay.HasValue && req.BirthDay.Value != DateTime.MinValue)
// {
// query = query.Where(b => b.Birthday == req.BirthDay.Value);
// }
// if (!req.IDCardNo.IsBlank())
// {
// query = query.Where(a => a.IDCardNo.Contains(req.IDCardNo));
// }
// if (req.Sex.HasValue)
// {
// query = query.Where(a => a.Sex == req.Sex.Value);
// }
// if (!req.PhoneNo.IsBlank())
// {
// query = query.Where(a => a.PhoneNo.Contains(req.PhoneNo));
// }
// if (!req.RegAddress.IsBlank())
// {
// query = query.Where(a => a.RegAddress.Contains(req.RegAddress));
// }
// if (!req.IDCardInternalNO.IsBlank() || !req.ICCardNo.IsBlank())
// {
// if (req.PersonIds != null && req.PersonIds.Count > 0)
// {
// query = query.Where(a => req.PersonIds.Contains(a.PersonUUID));
// }
// else
// {
// query = query.Where(a => a.PersonUUID.Contains("unknow"));
// }
// }
// if (req.IsRenter)
// query = query.Where(a => a.IsRenter);
// if (!req.communityId.IsBlank())
// {
// query = query.Where(a => a.CommunityUUID == req.communityId);
// }
// if (req.DateFrom.HasValue)
// query = query.Where(b => req.DateFrom.Value <= b.CreateTime);
// if (req.DateTo.HasValue)
// query = query.Where(b => req.DateTo.Value >= b.CreateTime);
// query = query.OrderByDescending(o => o.CreateTime);
// var pagedList = query.ToPagedList(req.page, req.pageSize);
// var personList = new List<PersonListDto>();
// foreach (var item in pagedList)
// {
// var dto = new PersonListDto();
// dto.PersonUUID = item.PersonUUID;
// dto.PersonName = item.PersonName;
// dto.IDCardNo = item.IDCardNo;
// dto.PhoneNo = item.PhoneNo;
// dto.SexName = item.SexDict?.DictName ?? string.Empty;
// dto.NationName = item.NationDict?.DictName ?? string.Empty;
// dto.BirthdayVal = item.Birthday.ToString("yyyy-MM-dd");
// dto.ValidFromVal = item.ValidFrom.HasValue ? item.ValidFrom.Value.ToString("yyyy-MM-dd") : string.Empty;
// dto.ValidToVal = item.ValidTo.HasValue ? item.ValidTo.Value.ToString("yyyy-MM-dd") : string.Empty;
// dto.RegAddress = item.RegAddress;
// dto.IsLocalVal = item.IsLocal == 1 ? "是" : "否";
// dto.IsRenterVal = item.IsRenter ? "是" : "否";
// dto.CreateTimeVal = item.CreateTime.ToString("yyyy-MM-dd");
// var community = _areaRepository.GetById(item.CommunityUUID);
// dto.CommunityName = community != null ? community.ChineseName : "";
// personList.Add(dto);
// }
// return new TableData
// {
// currPage = req.page,
// pageSize = req.pageSize,
// pageTotal = pagedList.TotalPageCount,
// totalCount = pagedList.TotalItemCount,
// list = personList
// };
//}
public TableData GetList(PersonReq req)
{
var areaCode = "";
var listType = "";
if (req.AreaUUID.IsNotBlank())
{
if (req.AreaUUID == "uncheckin")
{
// 未入住
listType = "uncheckin";
}
if (req.AreaUUID == "isLeave")
{
// 未入住
listType = "isLeave";
}
else if (req.AreaUUID == "managecard")
{
listType = "managecard";
}
else
{
var area = _areaRepository.GetById(req.AreaUUID);
if (area != null)
{
areaCode = area.Code;
}
}
}
string showField = "p.PersonUUID,p.PersonName,p.IDCardNo,p.Sex,p.Nation,p.BirthDay,p.ValidFrom,p.ValidTo,p.RegAddress,p.IsLocal,";
showField += "p.IsRenter, p.CommunityUUID,p.CreateTime, p.PhoneNo,r.InsurCompany,";
showField += "ru.FamilyRelation,room.RoomName";
var querySql = @"select {0} from biz_Person p "
+ " left join biz_Renter r on p.personuuid = r.personuuid"
+ " left join biz_RoomUser ru on p.PersonUUID = ru.PersonUUID"
+ " left join biz_Room room on room.RoomUUID = ru.RoomUUID"
+ " left join biz_Area area on room.AreaUUID = area.AreaUUID"
+ " where p.Deleted = 0 and(room.Deleted = 0 or room.Deleted is null) ";
if (!string.IsNullOrEmpty(areaCode))
{
querySql += " and area.Code like '" + areaCode + "%'";
}
if (listType == "uncheckin")
{
querySql += " and p.IsLived =0 and (p.PersonType != 273 or P.PersonType IS NULL)";
}
if (listType == "isLeave")
{
querySql += " and p.IsLived =2";
}
else
{
querySql += " and (ru.Status = 1 or ru.Status is null)";
}
if (listType == "managecard")
{
querySql += " and p.PersonType = 273";
}
if (!req.PersonName.IsBlank())
{
querySql += string.Format(" and p.personName like '%{0}%'", req.PersonName);
}
if (req.BirthDay.HasValue && req.BirthDay.Value != DateTime.MinValue)
{
querySql += string.Format("and p.Birthday='{0}'", req.BirthDay.Value);
}
if (!req.IDCardNo.IsBlank())
{
querySql += string.Format(" and p.IDCardNo like '%{0}%'", req.IDCardNo);
}
if (req.Sex.HasValue)
{
querySql += string.Format("and p.sex={0}", req.Sex.Value);
}
if (!req.PhoneNo.IsBlank())
{
querySql += string.Format(" and p.PhoneNo like '%{0}%'", req.PhoneNo);
}
if (!req.RegAddress.IsBlank())
{
querySql += string.Format(" and p.RegAddress like '%{0}%'", req.RegAddress);
}
if (!req.IDCardInternalNO.IsBlank() || !req.ICCardNo.IsBlank())
{
// query = query.Where(a => req.PersonIds.Contains(a.PersonUUID));
//if (req.PersonIds != null && req.PersonIds.Count > 0)
//{
querySql += string.Format(" and p.PersonUUID IN ({0})", Utils.Array2Strin(req.PersonIds));
//}
}
if (req.IsRenter)
{
querySql += " and p.IsRenter=1";
}
if (!req.communityId.IsBlank())
{
querySql += string.Format(" and p.CommunityUUID='{0}'", req.communityId);
}
if (req.DateFrom.HasValue)
{
querySql += string.Format(" and p.CreateTime>='{0}'", req.DateFrom.Value);
}
if (req.DateTo.HasValue)
{
querySql += string.Format(" and p.CreateTime<='{0}'", req.DateTo.Value);
}
querySql += " order by p.CreateTime desc";
var query = _dbContext.SqlQuery <PersonListDto>(string.Format(querySql, showField)).ToList();
var pagedList = new PagedList <PersonListDto>(query, req.page, req.pageSize);
//var personList = new List<PersonListDto>();
foreach (var item in pagedList)
{
var SexDict = _dictRepository.GetById(item.Sex);
var NationDict = _dictRepository.GetById(item.Nation);
var FamilyRelationDict = _dictRepository.GetById(item.FamilyRelation);
item.SexName = SexDict?.DictName ?? string.Empty;
item.NationName = NationDict?.DictName ?? string.Empty;
item.FamilyRelationName = FamilyRelationDict?.DictName ?? string.Empty;
item.BirthdayVal = item.Birthday.ToString("yyyy-MM-dd");
item.ValidFromVal = item.ValidFrom.HasValue ? item.ValidFrom.Value.ToString("yyyy-MM-dd") : string.Empty;
item.ValidToVal = item.ValidTo.HasValue ? item.ValidTo.Value.ToString("yyyy-MM-dd") : string.Empty;
item.IsLocalVal = item.IsLocal == 1 ? "是" : "否";
item.IsRenterVal = item.IsRenter ? "是" : "否";
item.CreateTimeVal = item.CreateTime.ToString("yyyy-MM-dd");
var community = _areaRepository.GetById(item.CommunityUUID);
item.CommunityName = community != null ? community.ChineseName : "";
}
return(new TableData
{
currPage = req.page,
pageSize = req.pageSize,
pageTotal = pagedList.TotalPageCount,
totalCount = pagedList.TotalItemCount,
list = pagedList
});
}