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); }