public HttpResponseMessage getPersonels(DTOFilterGetPersonelRequest request)
{
using (var db = new CRMEntities())
{
var filter = request.getFilter();
filter.fieldFilters.Add(new DTOFieldFilter {
fieldName = "deleted", value = 0, op = 2
});
var countSql = filter.getCountSQL();
var rowCount = db.Database.SqlQuery <int>(countSql).First();
var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage);
var res = db.personel.SqlQuery(querySql).ToList();
DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo
{
pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage),
pageNo = request.pageNo,
rowsPerPage = request.rowsPerPage,
totalRowCount = rowCount
};
return(Request.CreateResponse(HttpStatusCode.OK,
new DTOPagedResponse(DTOResponseError.NoError(), res.Where(r => r.deleted == false).Select(r => r.toDTO()).ToList(), paginginfo, querySql)
, "application/json"));
}
}
public HttpResponseMessage getPersonels(DTOFilterGetPersonelRequest request)
{
using (var db = new KOCSAMADLSEntities())
{
var filter = request.getFilter();
filter.fieldFilters.Add(new DTOFieldFilter {
fieldName = "deleted", value = 0, op = 2
});
var countSql = filter.getCountSQL();
var rowCount = db.Database.SqlQuery <int>(countSql).First();
var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage);
var res = db.personel.SqlQuery(querySql).ToList();
var ilIds = res.Select(s => s.ilKimlikNo).Distinct().ToList();
var ils = db.il.Where(s => ilIds.Contains(s.kimlikNo)).ToList();
var ilceIds = res.Select(s => s.ilceKimlikNo).Distinct().ToList();
var ilces = db.ilce.Where(s => ilceIds.Contains(s.kimlikNo)).ToList();
var relatedPersonelIds = res.Select(s => s.relatedpersonelid).Distinct().ToList();
var relatedPersonels = db.personel.Where(s => relatedPersonelIds.Contains(s.personelid)).ToList();
var kurulumPersonelIds = res.Select(s => s.kurulumpersonelid).Distinct().ToList();
var kurulumPersonels = db.personel.Where(s => kurulumPersonelIds.Contains(s.personelid)).ToList();
res.ForEach(s => {
s.ilce = ilces.Where(i => i.kimlikNo == s.ilceKimlikNo).FirstOrDefault();
s.il = ils.Where(i => i.kimlikNo == s.ilKimlikNo).FirstOrDefault();
s.relatedpersonel = relatedPersonels.Where(p => p.personelid == s.relatedpersonelid).FirstOrDefault();
s.kurulumpersonel = kurulumPersonels.Where(p => p.personelid == s.kurulumpersonelid).FirstOrDefault();
});
DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo
{
pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage),
pageNo = request.pageNo,
rowsPerPage = request.rowsPerPage,
totalRowCount = rowCount
};
return(Request.CreateResponse(HttpStatusCode.OK,
new DTOPagedResponse(DTOResponseError.NoError(), res.Where(r => r.deleted == false).Select(r => r.toDTO()).ToList(), paginginfo, querySql)
, "application/json"));
}
}
