public Paging <List <InheritCaseSourceModel> > GetFreeDiscretionList(List <Filter> filters, int start, int limit)
{
EntitiesORCL db = new EntitiesORCL();
Paging <List <InheritCaseSourceModel> > paging = new Paging <List <InheritCaseSourceModel> >();
string sql = string.Format(@"select power2.powerid,power2.code,power2.powername,law2.name as flfg,law2.content as clyj, standard2.wfqx,standardp2.result as cf ,law2.createtime from (select power.*, row_number() over(partition by power.powerid order by power.DATAVERVISON desc ) rn1
from fds_power power) power2
left join (select law.*, row_number() over(partition by law.powerid order by law.DATAVERVISON desc ) rn2
from fds_law law) law2 on power2.powerid = law2.powerid and rn2=1
left join (select standard.*, row_number() over(partition by standard.lawid order by standard.DATAVERVISON desc ) rn3
from fds_standard standard) standard2 on law2.lawid = standard2.lawid and rn3=1
left join (select standardp.*, row_number() over(partition by standardp.standardid order by standardp.DATAVERVISON desc ) rn4
from fds_standard_punish standardp) standardp2 on standard2.standardid = standardp2.standardid and rn4=1
where rn1=1");
IEnumerable <InheritCaseSourceModel> list = db.Database.SqlQuery <InheritCaseSourceModel>(sql);
if (filters != null && filters.Count > 0)
{
foreach (Filter filter in filters)
{
string value = filter.value;
switch (filter.property)
{
case "powername":
if (!string.IsNullOrEmpty(value))
{
list = list.Where(t => t.powername.Contains(value));
}
break;
case "flfg":
if (!string.IsNullOrEmpty(value))
{
list = list.Where(t => t.flfg != null && !string.IsNullOrEmpty(t.flfg) && t.flfg.Contains(value));
}
break;
}
}
}
paging.Items = list.Skip(start).Take(limit).ToList();
paging.Total = list.Count();
return(paging);
}
public InheritCaseSourceModel GetFreeDiscretionModel(string powerid)
{
InheritCaseSourceModel model = new InheritCaseSourceModel();
using (EntitiesORCL db = new EntitiesORCL())
{
string sql = string.Format(@"select power2.powerid,power2.code,power2.powername,law2.name as flfg,law2.content as clyj, standard2.wfqx,standardp2.result as cf ,law2.createtime from (select power.*, row_number() over(partition by power.powerid order by power.DATAVERVISON desc ) rn1
from fds_power power) power2
left join (select law.*, row_number() over(partition by law.powerid order by law.DATAVERVISON desc ) rn2
from fds_law law) law2 on power2.powerid = law2.powerid and rn2=1
left join (select standard.*, row_number() over(partition by standard.lawid order by standard.DATAVERVISON desc ) rn3
from fds_standard standard) standard2 on law2.lawid = standard2.lawid and rn3=1
left join (select standardp.*, row_number() over(partition by standardp.standardid order by standardp.DATAVERVISON desc ) rn4
from fds_standard_punish standardp) standardp2 on standard2.standardid = standardp2.standardid and rn4=1
where rn1=1");
IEnumerable <InheritCaseSourceModel> list = db.Database.SqlQuery <InheritCaseSourceModel>(sql);
model = list.FirstOrDefault(a => a.powerid == powerid);
}
return(model);
}
public List <int> GetMenuCount()
{
List <int> list = new List <int>();
DateTime time = DateTime.Now;
DateTime dt1 = Convert.ToDateTime("0001/01/01");
DateTime dt2 = Convert.ToDateTime("0001/01/01");
dt1 = Convert.ToDateTime(DateTime.Now.ToString("yyyy/MM/dd"));
dt2 = dt1.AddDays(1);
DateTime dt3 = Convert.ToDateTime(DateTime.Now.ToString("yyyy/01/01"));
DateTime dt4 = dt3.AddYears(1);
using (Entities db = new Entities())
{
//人员
var ry = from a in db.base_users
join b_join in db.base_units on a.unitid equals b_join.id into bTmp
from b in bTmp.DefaultIfEmpty()
where b.unittypeid != 1
select new UserModel
{
ID = a.id
};
int rycount = ry.Count();
list.Add(rycount);
//车辆
var cl = from a in db.qw_cars
where a.status == 0
select new QW_CarsModel
{
carid = a.carid,
};
int clcount = cl.Count();
list.Add(clcount);
//监控
var jk = from a in db.fi_cameras
select new FI_CamerasModel
{
cameraid = a.cameraid,
};
int jkcount = jk.Count();
list.Add(jkcount);
//部件
int bjcount = db.bj_part.Count();
list.Add(bjcount);
//养护
int yhcount = db.yh_contracts.Where(a => a.createtime >= dt3 && a.createtime <dt4 && a.contactendtime> time).Count();
list.Add(yhcount);
//整治
int zzcount = db.zxzz_tasks.Where(a => a.createtime >= dt3 && a.createtime < dt4).Count();
list.Add(zzcount);
//审批
EntitiesORCL orcldb = new EntitiesORCL();
int Year = DateTime.Now.Year;
string spsql = string.Format(@"select * from audit_project_w t
where t.windowname='区综合行政执法局'
and t.status=90
and to_char(t.banjiedate,'yyyy')>=2017 and to_char(t.banjiedate,'yyyy')={0}
order by t.banjiedate desc ", Year);
int spcount = orcldb.Database.SqlQuery <Audit_project_wModel>(spsql).Count();
list.Add(spcount);
//违拆
int cqcount = db.wj_cqxms.Where(a => a.createtime >= dt3 && a.createtime < dt4).Count();
string sql = string.Format(@"select * from (select * from wj_wzjzs a where a.createtime>=str_to_date('{0}','%Y/%m/%d %H:%i:%s') and a.createtime<str_to_date('{1}','%Y/%m/%d %H:%i:%s') and a.parentid in (select a.wjid from wj_wzjzs a where a.parentid is null) order by a.createtime desc) tab1 GROUP BY tab1.parentid", dt3, dt4);
int wjcount = db.Database.SqlQuery <WJ_WzjzsModel>(sql).Count();
int wccount = cqcount + wjcount;
list.Add(wccount);
}
return(list);
}
