public object Get([FromUri] VisitorSearchModel pager = null)
{
if (pager == null)
{
pager = new VisitorSearchModel();
}
var query = _context.People;
var totalCount = query.Count();
var pred = PredicateBuilder.True <Person>();
if (!string.IsNullOrWhiteSpace(pager.Firstname))
{
pred = pred.And(p => p.Firstname.Contains(pager.Firstname));
}
if (!string.IsNullOrWhiteSpace(pager.Lastname))
{
pred = pred.And(p => p.Lastname.Contains(pager.Lastname));
}
if (!string.IsNullOrWhiteSpace(pager.EmailAddress))
{
pred = pred.And(p => p.EmailAddress.Contains(pager.EmailAddress));
}
if (!string.IsNullOrWhiteSpace(pager.Zipcode))
{
pred = pred.And(p => p.Zipcode.Contains(pager.Zipcode));
}
if (pager.FuzzyMatchRange != null && pager.FuzzyMatchRange.Length == 1)
{
var min = pager.FuzzyMatchRange[0].ToDecimal();
pred = pred.And(p => p.FuzzyMatchValue >= min);
}
if (pager.FuzzyMatchRange != null && pager.FuzzyMatchRange.Length == 2)
{
var min = pager.FuzzyMatchRange[0].ToDecimal();
var max = pager.FuzzyMatchRange[1].ToDecimal();
pred = pred.And(p => p.FuzzyMatchValue >= min && p.FuzzyMatchValue <= max);
}
var greaterThanDate = DateTime.Now.AddDays(pager.DaysOld * -1);
if (greaterThanDate.Date != DateTime.Today.Date)
{
pred = pred.And(p => p.DateCreated >= greaterThanDate);
}
var filteredQuery = query.Where(pred);
var pagerCount = filteredQuery.Count();
var results = filteredQuery
.Order(pager.OrderBy, pager.OrderDirection == "desc" ? SortDirection.Descending : SortDirection.Ascending)
.ThenByDescending(m => m.DateCreated)
.Skip(pager.PageSize * (pager.Page - 1) ?? 0)
.Take(pager.PageSize ?? PAGE_SIZE)
.ToList();
var totalPages = Math.Ceiling((double)pagerCount / pager.PageSize ?? PAGE_SIZE);
pager.TotalCount = totalCount;
pager.FilteredCount = pagerCount;
pager.TotalPages = totalPages;
pager.Results = results;
return(Ok(pager));
}
/// <summary>
/// flag (time:按照时间查询 top:按照次数查询)
/// </summary>
public string GetListByTimeAndTop(VisitorSearchModel v)
{
string Time = DataHelper.IsNullReturnLine(v.Time);
string VCertificateNumber = DataHelper.IsNullReturnLine(v.VCertificateNumber);
string Top = DataHelper.IsNullReturnLine(v.Top);
string Flag = DataHelper.IsNullReturnLine(v.Flag);
string sql = "";
SqlParameter[] pms = null;
if (string.IsNullOrEmpty(VCertificateNumber))
{
return(ConvertHelper.resultJson(0, "证件号不能为空"));
}
if (Flag == "time")
{
if (string.IsNullOrEmpty(Time))
{
return(ConvertHelper.resultJson(0, "请选择时间"));
}
string timeStart = Time.Split(' ')[0] + " 00:00:00";
string timeEnd = Time.Split(' ')[1] + " 23:59:59";
pms = new SqlParameter[] {
new SqlParameter("@timeStart", SqlDbType.NVarChar)
{
Value = (timeStart)
},
new SqlParameter("@timeEnd", SqlDbType.NVarChar)
{
Value = (timeEnd)
},
new SqlParameter("@VCertificateNumber", SqlDbType.NVarChar)
{
Value = (VCertificateNumber)
}
};
sql += "select v.VId,v.VName,v.VSex,v.VNation,v.VBirthDate,v.VAddress,v.VIssuingAuthority,v.VExpiryDate,v.VCertificateType,v.VCertificateNumber,v.VType,v.VFromCourtId,v.VInTime,v.VOutTime,v.VInPost,v.VOutPost,v.VInDoorkeeper,v.VOutDoorkeeper,v.VVisitingReason,v.VIntervieweeDept,v.VInterviewee,v.VOffice,v.VOfficePhone,v.VExtensionPhone,v.VMobilePhone,v.VRemark,c.CName from T_VisitorAccessInf v ";
sql += "left join T_CourtInf c ";
sql += "on v.VFromCourtId = c.CNumber ";
sql += "where v.VInTime between @timeStart and @timeEnd ";
sql += "and v.VCertificateNumber=@VCertificateNumber ";
sql += "order by v.VId desc";
}
else if (Flag == "top")
{
if (string.IsNullOrEmpty(Top.ToString()))
{
return(ConvertHelper.resultJson(0, "次数不能为空"));
}
pms = new SqlParameter[] {
new SqlParameter("@Top", SqlDbType.Int)
{
Value = (Convert.ToInt32(Top))
},
new SqlParameter("@VCertificateNumber", SqlDbType.NVarChar)
{
Value = (VCertificateNumber)
}
};
sql += "select top (@Top) v.VId,v.VName,v.VSex,v.VNation,v.VBirthDate,v.VAddress,v.VIssuingAuthority,v.VExpiryDate,v.VCertificateType,v.VCertificateNumber,v.VType,v.VFromCourtId,v.VInTime,v.VOutTime,v.VInPost,v.VOutPost,v.VInDoorkeeper,v.VOutDoorkeeper,v.VVisitingReason,v.VIntervieweeDept,v.VInterviewee,v.VOffice,v.VOfficePhone,v.VExtensionPhone,v.VMobilePhone,v.VRemark,c.CName from T_VisitorAccessInf v ";
sql += "left join T_CourtInf c ";
sql += "on v.VFromCourtId = c.CNumber ";
sql += "where v.VCertificateNumber=@VCertificateNumber ";
sql += "order by v.VId desc";
}
DataTable dt;
try
{
dt = SQLHelper.ExecuteDataTable(sql, CommandType.Text, pms);
return("{\"code\":1,\"data\":" + ConvertHelper.DataTableToJson(dt) + "}");
//return ConvertHelper.DataTableToJson(dt);
//return dt;
}
catch (Exception e)
{
//在webapi中要想抛出异常必须这样抛出，否则只抛出一个默认500的异常
var resp = new HttpResponseMessage(HttpStatusCode.InternalServerError)
{
Content = new StringContent(e.ToString()),
ReasonPhrase = "error"
};
throw new HttpResponseException(resp);
}
}
