public IActionResult PageList([FromQuery] UserInfoPagedDto param)
{
var userData = new UserInfoDataBase();
var resultData = userData.pageList(param);
var rows = resultData.Values.FirstOrDefault();
var total = resultData.Keys.FirstOrDefault();
return(Json(new { total = total, rows = rows, index = param.offset }));
}
public Dictionary <int, List <UserInfoDto> > pageList(UserInfoPagedDto parm)
{
string sqls = string.Format(@"select a.Id,a.Address,a.UserName, a.Phone, a.Address,a.MakeRoomTime ,a.SellingPoint,a.RoomNumber,a.Price,a.AllPrice,a.Areas,a.Apartment,a.Remark
,(select Url from img where UserInfoId = a.id order by CreateTime desc limit 1 offset 0) as url from UserInfo as a where a.Id is not null");
string sqlCount = "select count(*) from UserInfo where Id is not null";
string sqlwhere = null;
//if (strRegionIds != null)
//{
// strsql = strRegionIds + strsql;
// sqlCount = strRegionIds + sqlCount;
// sqlwhere += " and exists(select RegionId from RegionIds where RegionId =c.RegionId)";
//}
if (!string.IsNullOrWhiteSpace(parm.Address))
{
sqlwhere += $" and Address like '" + parm.Address + "%'";
}
if (!string.IsNullOrWhiteSpace(parm.UserName))
{
sqlwhere += $" and UserName like '" + parm.UserName + "%'";
}
if (!string.IsNullOrWhiteSpace(parm.Apartment))
{
sqlwhere += " and Apartment like '" + parm.Apartment + "%'";
}
if (!string.IsNullOrWhiteSpace(parm.Keywords))
{
sqlwhere += $" and (SellingPoint like '" + parm.Keywords + "%' or Apartment like '" + parm.Keywords + "%' )";
}
string sqlsize = "";
sqlCount += sqlwhere;
sqls += sqlwhere;
if (parm.limit != 0 && parm.offset > 0)
{
var skipCount = parm.limit * (parm.offset - 1);
sqlsize = string.Format(" order by CreateTime asc limit {0} offset {1}", parm.limit, skipCount);
sqls += sqlsize;
}
var dt = SQLiteHelper.ExecuteDataTable(sqls);
var userData = new List <UserInfoDto>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var item = new UserInfoDto
{
Id = Guid.Parse(dr["Id"].ToString()),
UserName = dr["UserName"].ToString(),
Phone = dr["Phone"].ToString(),
Address = dr["Address"].ToString(),
AllPrice = dr["AllPrice"].ToString(),
Price = dr["Price"].ToString(),
Areas = dr["Areas"].ToString(),
Apartment = dr["Apartment"].ToString(),
RoomNumber = dr["RoomNumber"].ToString(),
SellingPoint = dr["SellingPoint"].ToString(),
MakeRoomTime = dr["MakeRoomTime"].ToString(),
Url = dr["url"].ToString()
};
userData.Add(item);
}
}
var count = SQLiteHelper.ExecuteScalar(sqlCount);
Dictionary <int, List <UserInfoDto> > result = new Dictionary <int, List <UserInfoDto> >();
result.Add(Convert.ToInt16(count), userData);
return(result);
}