/// <summary>
/// 查询日志列表
/// </summary>
/// <param name="filters"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <returns></returns>
public Pag <kh_examinesModel> GetMechExamList(List <Filter> filters, int start, int limit, int year, int month)
{
using (Entities db = new Entities())
{
string sql = @"SELECT exams.*, compan.companyname, contra.contractname, IFNULL(SUM(score.deduct),0) sumscore, users.displayname
FROM kh_examines exams LEFT JOIN yh_companys compan ON exams.companyid = compan.companyid LEFT JOIN yh_contracts contra ON exams.contractid = contra.contractid LEFT JOIN kh_scores score ON exams.examineid = score.examineid LEFT JOIN base_users users ON exams.createuserid = users.id
where YEAR(exams.examinedate)=" + year + " and MONTH(exams.examinedate)=" + month + " GROUP BY exams.examineid";
IEnumerable <kh_examinesModel> queryable = db.Database.SqlQuery <kh_examinesModel>(sql);
if (filters != null && filters.Count > 0)
{
foreach (Filter filter in filters)
{
string value = filter.value;
switch (filter.property)
{
case "companyname":
if (!string.IsNullOrEmpty(value))
{
queryable = queryable.Where(t => t.companyname == value);
}
break;
case "starttime":
if (!string.IsNullOrEmpty(value))
{
DateTime starttime = DateTime.Parse(value);
DateTime Etime = starttime.AddDays(1);
queryable = queryable.Where(t => t.examinedate >= starttime && t.examinedate < Etime);
}
break;
case "endtime":
if (!string.IsNullOrEmpty(value))
{
DateTime endtime = DateTime.Parse(value);
DateTime Etime = endtime.AddDays(1);
queryable = queryable.Where(t => t.examinedate >= endtime && t.examinedate < Etime);
}
break;
case "contractname":
if (!string.IsNullOrEmpty(value))
{
queryable = queryable.Where(t => t.contractname.Contains(value));
}
break;
}
}
}
IOrderedEnumerable <kh_examinesModel> temp = queryable.OrderByDescending(a => a.createtime);
Pag <kh_examinesModel> rst = PagHelper.CreatPagList(temp, start, limit);
return(rst);
}
}
public Pag <MonitorListModel> GetMonitorTableList(List <Filter> filters, int start, int limit, int unitid, string path)
{
using (Entities db = new Entities())
{
string sql = @"SELECT spe.unitid,
unit.unitname,
cam.cameraid,
cam.cameraname,
camt.cameratypename,
cam.cameratypeid,
spe.seq,
unit.path,
unit.parentid
FROM
fi_specialcameras spe
left JOIN fi_specialunits unit on spe.unitid=unit.unitid
LEFT JOIN fi_cameras cam ON spe.cameraid = cam.cameraid
LEFT JOIN fi_cameratypes camt ON cam.cameratypeid = camt.cameratypeid";
string[] pathlist = path.Split('/');
if (pathlist.Length == 3)
{
if (pathlist[1] == "0")
{
sql += " where 1=1";
}
else
{
sql += " where unit.parentid=" + pathlist[1];
}
}
else if (unitid != 0)
{
sql += " where spe.unitid=" + unitid;
}
else
{
sql += " where 1=1";
}
IEnumerable <MonitorListModel> queryable = db.Database.SqlQuery <MonitorListModel>(sql);
if (filters != null && filters.Count > 0)
{
foreach (Filter filter in filters)
{
string value = filter.value;
}
}
IOrderedEnumerable <MonitorListModel> temp = queryable.OrderByDescending(a => a.seq);
Pag <MonitorListModel> rst = PagHelper.CreatPagList(temp, start, limit);
return(rst);
}
}
public Pag <kh_scoresModel> getScoreList(int examineid, int start, int limit)
{
using (Entities db = new Entities())
{
IEnumerable <kh_scoresModel> queryable = from a in db.kh_scores
join b in db.base_users on a.deductuserid equals b.id
where a.examineid == examineid
select new kh_scoresModel
{
scoreid = a.scoreid,
deail = a.deail,
deduct = a.deduct,
deductuserid = a.deductuserid,
examinetime = a.examinetime,
examineid = a.examineid,
deductusername = b.displayname,
};
IOrderedEnumerable <kh_scoresModel> temp = queryable.OrderByDescending(a => a.examineid);
Pag <kh_scoresModel> rst = PagHelper.CreatPagList(temp, start, limit);
return(rst);
}
}
public Pag <basicinfoModel> GetBaseInfoList(List <Filter> filters, int start, int limit)
{
Pag <basicinfoModel> list = new Pag <basicinfoModel>();
using (Entities db = new Entities())
{
string sql = string.Format(@"select * from fi_uvas t where t.isdelete=0");
IEnumerable <basicinfoModel> queryable = db.Database.SqlQuery <basicinfoModel>(sql);
if (filters != null && filters.Count > 0)
{
foreach (Filter filter in filters)
{
string value = filter.value;
switch (filter.property)
{
case "ovanum":
if (!string.IsNullOrEmpty(value))
{
queryable = queryable.Where(t => t.ovanum.Contains(value));
}
break;
case "ovaname":
if (!string.IsNullOrEmpty(value))
{
queryable = queryable.Where(t => t.ovaname.Contains(value));
}
break;
}
;
}
}
IOrderedEnumerable <basicinfoModel> temp = queryable.OrderByDescending(a => a.ovaid);
list = PagHelper.CreatPagList(temp, start, limit);
}
return(list);
}
/// <summary>
/// 获取行政许可审批全部列表
/// </summary>
/// <param name="filter"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <returns></returns>
public Pag <LicenseModel> GetAllCaseSourcesList(List <Filter> filters, int start, int limit)
{
List <LicenseModel> list = new List <LicenseModel>();
using (Entities db = new Entities())
{
string sql = string.Format(@"select lic.*,zds.zd_name splxname,file.filepath firstImgUrl
from xz_licensings lic
left join base_zds zds on lic.splx=zds.zd_id and zds.zd_type='type_splx'
left join (select * from xzj_files fi where fi.source=2 group by fi.sourceid) as file on lic.licensingid=file.sourceid");
IEnumerable <LicenseModel> queryable = db.Database.SqlQuery <LicenseModel>(sql);
#region linq
//IQueryable<LicenseModel> queryable = from a in db.xz_licensings
// join b_join in db.xzj_files.Where(t => t.source == 2) on a.licensingid equals b_join.sourceid into btmp
// from b in btmp.DefaultIfEmpty()
// orderby a.licensingid descending
// select new LicenseModel
// {
// licensingid = a.licensingid,
// sph = a.sph,
// xksx = a.xksx,
// splx = a.splx,
// b_address = a.b_address,
// sxmx = a.sxmx,
// sqr = a.sqr,
// cardtype = a.cardtype,
// card = a.card,
// contactphone = a.contactphone,
// s_address = a.s_address,
// processtime_start = a.processtime_start,
// processtime_end = a.processtime_end,
// processcontent = a.processcontent,
// processaddress = a.processaddress,
// geography = a.geography,
// createuserid = a.createuserid,
// createtime = a.createtime,
// shresult = a.shresult,
// shopinion = a.shopinion,
// shuser = a.shuser,
// issh = a.issh,
// shtime = a.shtime,
// firstImgUrl = b.filename,
// };
#endregion
foreach (LicenseModel model in queryable)
{
if (model != null && model.firstImgUrl != null)
{
List <string> filelist = new List <string>();
filelist.Add(model.firstImgUrl);
model.imgUrl = filelist;
}
}
if (filters != null && filters.Count > 0)
{
foreach (Filter filter in filters)
{
string value = filter.value;
switch (filter.property)
{
case "splx":
if (!string.IsNullOrEmpty(value))
{
queryable = queryable.Where(t => t.splx.Contains(value));
}
break;
case "sqr":
if (!string.IsNullOrEmpty(value))
{
queryable = queryable.Where(t => t.sqr.Contains(value));
}
break;
case "xksx":
if (!string.IsNullOrEmpty(value))
{
queryable = queryable.Where(t => t.xksx.Contains(value));
}
break;
case "sph":
if (!string.IsNullOrEmpty(value))
{
queryable = queryable.Where(t => t.sph.ToString().Contains(value));
}
break;
case "createtime":
if (!string.IsNullOrEmpty(value))
{
DateTime createtime = Convert.ToDateTime(value);
queryable = queryable.Where(t => t.createtime == createtime);
}
break;
}
}
}
var temp = queryable.OrderByDescending(a => a.licensingid);
var rst = PagHelper.CreatPagList(temp, start, limit);
return(rst);
//list = queryable.OrderByDescending(a => a.licensingid).Skip(start).Take(limit).ToList();
}
// return list;
}
/// <summary>
/// 获取行政许可待审批列表
/// </summary>
/// <param name="filter"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <returns></returns>
public Pag <LicenseModel> GetPendingCaseSourcesList(List <Filter> filters, int start, int limit, int userid)
{
Pag <LicenseModel> list = new Pag <LicenseModel>();
using (Entities db = new Entities())
{
#region linq
//IQueryable<LicenseModel> queryable = from a in db.xz_licensings
// join b_join in db.xzj_files.Where(t => t.source == 2) on a.licensingid equals b_join.sourceid into btmp
// from b in btmp.DefaultIfEmpty()
// where a.issh == 0
// orderby a.licensingid descending
// select new LicenseModel
// {
// licensingid = a.licensingid,
// sph = a.sph,
// xksx = a.xksx,
// splx = a.splx,
// b_address = a.b_address,
// sxmx = a.sxmx,
// sqr = a.sqr,
// cardtype = a.cardtype,
// card = a.card,
// contactphone = a.contactphone,
// s_address = a.s_address,
// processtime_start = a.processtime_start,
// processtime_end = a.processtime_end,
// processcontent = a.processcontent,
// processaddress = a.processaddress,
// geography = a.geography,
// createuserid = a.createuserid,
// createtime = a.createtime,
// shresult = a.shresult,
// shopinion = a.shopinion,
// shuser = a.shuser,
// issh = a.issh,
// shtime = a.shtime,
// firstImgUrl = b.filename,
// };
#endregion
string sql = string.Format(@"SELECT d.name, lic.*, zds.zd_name AS splxname, file.filepath AS firstImgUrl
FROM xz_licensings lic
LEFT JOIN (SELECT *
FROM xzj_files fi
WHERE fi.source = 2
GROUP BY fi.sourceid
) file ON lic.licensingid = file.sourceid
LEFT JOIN base_zds zds ON lic.splx = zds.zd_id
AND zds.zd_type = 'type_splx'
RIGHT JOIN (SELECT b.id, b.usertypeid, c.unitid, e.`name`
FROM base_users b
RIGHT JOIN (SELECT a.id, a.unitid
FROM base_users a
WHERE a.id = {0}
) c ON b.unitid = c.unitid
AND c.unitid BETWEEN 10 AND 17
LEFT JOIN base_units e ON c.unitid = e.id
) d ON lic.createuserid = d.id
WHERE lic.issh = 0", userid);
//判断是否是队员
string strsql = string.Format(@"select (case when Find_IN_SET (3,a.roleid)>0 then 1 else null end)mark from
(select users.id,users.displayname,GROUP_CONCAT(usroles.roleid) roleid
FROM base_users users LEFT JOIN base_userroles usroles ON users.id = usroles.userid
WHERE users.id={0}) a;
", userid);
IEnumerable <LicenseModel> sqlQueryable = db.Database.SqlQuery <LicenseModel>(sql);
List <string> sqltest = db.Database.SqlQuery <string>(strsql).ToList();
if (sqltest.FirstOrDefault() == null)
{
return(list);
}
else
{
foreach (LicenseModel model in sqlQueryable)
{
if (model != null && model.firstImgUrl != null)
{
List <string> filelist = new List <string>();
filelist.Add(model.firstImgUrl);
model.imgUrl = filelist;
}
}
if (filters != null && filters.Count > 0)
{
foreach (Filter filter in filters)
{
string value = filter.value;
switch (filter.property)
{
case "splx":
if (!string.IsNullOrEmpty(value))
{
sqlQueryable = sqlQueryable.Where(t => t.splx.Contains(value));
}
break;
case "sqr":
if (!string.IsNullOrEmpty(value))
{
sqlQueryable = sqlQueryable.Where(t => t.sqr.Contains(value));
}
break;
case "xksx":
if (!string.IsNullOrEmpty(value))
{
sqlQueryable = sqlQueryable.Where(t => t.xksx.Contains(value));
}
break;
case "sph":
if (!string.IsNullOrEmpty(value))
{
sqlQueryable = sqlQueryable.Where(t => t.sph.ToString().Contains(value));
}
break;
case "createtime":
if (!string.IsNullOrEmpty(value))
{
DateTime createtime = Convert.ToDateTime(value);
sqlQueryable = sqlQueryable.Where(t => t.createtime == createtime);
}
break;
}
}
}
IOrderedEnumerable <LicenseModel> temp = sqlQueryable.OrderByDescending(a => a.createtime);
list = PagHelper.CreatPagList(temp, start, limit);
}
}
return(list);
}
