/// <summary> /// 根据单位返回市县乡优化后的统计 /// </summary> /// <param name="sw">参见模型</param> /// <returns>DT</returns> public static DataTable getDTByOrgNo(PatrolRouteStat_SW sw) { StringBuilder sb = new StringBuilder(); if (PublicCls.OrgIsShi(sw.TopORGNO)) { sb.AppendFormat(" select count(substring(BYORGNO,1,6)+'000000000') as C,sum(PATROLCOUNT) as C1,sum(PATROLYESCOUNT) as C2,sum(PATROLNOCOUNT) as C3,substring(BYORGNO,1,6)+'000000000' as BYORGNO"); sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID"); sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" group by substring(BYORGNO,1,6)+'000000000'"); } else if (PublicCls.OrgIsXian(sw.TopORGNO)) { sb.AppendFormat(" select count(BYORGNO) as C,sum(PATROLCOUNT) as C1,sum(PATROLYESCOUNT) as C2,sum(PATROLNOCOUNT) as C3, BYORGNO"); sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID"); sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" and substring(BYORGNO,1,6)+'000000000'='{0}'", sw.TopORGNO); sb.AppendFormat(" group by BYORGNO"); } else { sb.AppendFormat("select count(a.USERID) as C,sum(PATROLCOUNT) as C1,sum(PATROLYESCOUNT) as C2,sum(PATROLNOCOUNT) as C3,a.USERID as BYORGNO"); sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID"); sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" and BYORGNO='{0}'", sw.TopORGNO); sb.AppendFormat(" group by a.USERID"); } return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 返回各县护林员个数 /// </summary> /// <param name="sw">参见模型</param> /// <returns></returns> public static DataTable getDTByOrgSum(T_IPSFR_USER_SW sw) { StringBuilder sb = new StringBuilder(); if (PublicCls.OrgIsShi(sw.BYORGNO)) { sb.AppendFormat(" select count(substring(BYORGNO,1,6)+'000000000') as C,substring(BYORGNO,1,6)+'000000000' as BYORGNO"); sb.AppendFormat(" FROM T_IPSFR_USER"); sb.AppendFormat(" where ISENABLE = '1'"); sb.AppendFormat(" group by substring(BYORGNO,1,6)+'000000000'"); } else if (PublicCls.OrgIsXian(sw.BYORGNO)) { sb.AppendFormat(" select count(BYORGNO) as C, BYORGNO"); sb.AppendFormat(" FROM T_IPSFR_USER"); sb.AppendFormat(" where ISENABLE = '1'"); sb.AppendFormat(" and substring(BYORGNO,1,6)+'000000000'='{0}'", sw.BYORGNO); sb.AppendFormat(" group by BYORGNO"); } else { sb.AppendFormat(" select hid,hname"); sb.AppendFormat(" FROM T_IPSFR_USER"); sb.AppendFormat(" where ISENABLE = '1'"); sb.AppendFormat(" and BYORGNO='{0}'", sw.BYORGNO); } DataSet ds = DataBaseClass.FullDataSet(sb.ToString()); return(ds.Tables[0]); }
/// <summary> /// 根据单位返回市县乡优化后的统计 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static DataTable getDTByOrgNo(PatrolRouteStat_SW sw) { StringBuilder sb = new StringBuilder(); if (PublicCls.OrgIsShi(sw.TopORGNO)) { sb.AppendFormat(" select count(substring(BYORGNO,1,6)+'000000000') as C,a.ROUTESTATE,substring(BYORGNO,1,6)+'000000000' as BYORGNO"); sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_PATROL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID"); sb.AppendFormat(" WHERE (ROUTEDATE >= '{0}') AND (ROUTEDATE <= '{1}')", sw.DateBegin + " 00:00:00", sw.DateEnd + " 23:59:59"); sb.AppendFormat(" group by ROUTESTATE,substring(BYORGNO,1,6)+'000000000'"); } else if (PublicCls.OrgIsXian(sw.TopORGNO)) { sb.AppendFormat(" select count(BYORGNO) as C,a.ROUTESTATE, BYORGNO"); sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_PATROL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID"); sb.AppendFormat(" WHERE (ROUTEDATE >= '{0}') AND (ROUTEDATE <= '{1}')", sw.DateBegin + " 00:00:00", sw.DateEnd + " 23:59:59"); sb.AppendFormat(" and substring(BYORGNO,1,6)+'000000000'='{0}'", sw.TopORGNO); sb.AppendFormat(" group by ROUTESTATE,BYORGNO"); } else { sb.AppendFormat("select count(a.hid) as C,a.ROUTESTATE,a.hid as BYORGNO"); sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_PATROL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID"); sb.AppendFormat(" WHERE (ROUTEDATE >= '{0}') AND (ROUTEDATE <= '{1}')", sw.DateBegin + " 00:00:00", sw.DateEnd + " 23:59:59"); sb.AppendFormat(" and BYORGNO='{0}'", sw.TopORGNO); sb.AppendFormat(" group by ROUTESTATE,a.hid"); } return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 根据单位返回市县乡及各日期后的统计 /// </summary> /// <param name="sw">参见模型</param> /// <returns>dt</returns> public static DataTable getDTByOrgNoToDate(OutRaiLCount_SW sw) { sw.DateBegin = sw.DateBegin + " 00:00:00"; sw.DateEnd = sw.DateEnd + " 23:59:59"; StringBuilder sb = new StringBuilder(); if (PublicCls.OrgIsShi(sw.TopORGNO)) { sb.AppendFormat(" select count(substring(BYORGNO,1,6)+'000000000') as C,substring(BYORGNO,1,6)+'000000000' as BYORGNO,cast(CONVERT(varchar(100), sbtime, 111) as date) as SBDATE"); sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_RAIL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID"); sb.AppendFormat(" WHERE (sbtime >= '{0}') AND (sbtime <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" group by substring(BYORGNO,1,6)+'000000000',cast(CONVERT(varchar(100), sbtime, 111) as date)"); } else if (PublicCls.OrgIsXian(sw.TopORGNO)) { sb.AppendFormat(" select count(BYORGNO) as C, BYORGNO,cast(CONVERT(varchar(100), sbtime, 111) as date) as SBDATE"); sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_RAIL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID"); sb.AppendFormat(" WHERE (sbtime >= '{0}') AND (sbtime <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" and substring(BYORGNO,1,6)+'000000000'='{0}'", sw.TopORGNO); sb.AppendFormat(" group by BYORGNO,cast(CONVERT(varchar(100), sbtime, 111) as date)"); } else { sb.AppendFormat(" select count(a.HID) as C,a.HID as BYORGNO,cast(CONVERT(varchar(100), sbtime, 111) as date) as SBDATE"); sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_RAIL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID"); sb.AppendFormat(" WHERE (sbtime >= '{0}') AND (sbtime <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" and BYORGNO='{0}'", sw.TopORGNO); sb.AppendFormat(" group by a.HID,cast(CONVERT(varchar(100), sbtime, 111) as date)"); } return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 统计队伍人数 /// </summary> /// <param name="dt">dt</param> /// <param name="orgNo">orgNo</param> /// <param name="DICTVALUE">DICTVALUE</param> /// <returns></returns> public static string getMCount(DataTable dt, string orgNo, string DICTVALUE) { if (string.IsNullOrEmpty(orgNo))//返回所有记录个数 return dt.Rows.Count.ToString(); if (PublicCls.OrgIsShi(orgNo))//市 { if (string.IsNullOrEmpty(DICTVALUE)) return dt.Compute("Sum(ARMYMEMBERCOUNT)", "substring(BYORGNO,1,4)='" + PublicCls.getShiIncOrgNo(orgNo) + "'").ToString(); else return dt.Compute("Sum(ARMYMEMBERCOUNT)", "substring(BYORGNO,1,4)='" + PublicCls.getShiIncOrgNo(orgNo) + "' and ARMYTYPE='" + DICTVALUE + "'").ToString(); } else if (PublicCls.OrgIsXian(orgNo))//县 { if (string.IsNullOrEmpty(DICTVALUE)) return dt.Compute("Sum(ARMYMEMBERCOUNT)", "substring(BYORGNO,1,6)='" + PublicCls.getXianIncOrgNo(orgNo) + "'").ToString(); else return dt.Compute("Sum(ARMYMEMBERCOUNT)", "substring(BYORGNO,1,6)='" + PublicCls.getXianIncOrgNo(orgNo) + "' and ARMYTYPE='" + DICTVALUE + "'").ToString(); } else if (PublicCls.OrgIsZhen(orgNo)) { if (string.IsNullOrEmpty(DICTVALUE)) return dt.Compute("Sum(ARMYMEMBERCOUNT)", "substring(BYORGNO,1,9)='" + PublicCls.getZhenIncOrgNo(orgNo) + "'").ToString(); else return dt.Compute("Sum(ARMYMEMBERCOUNT)", "substring(BYORGNO,1,9)='" + PublicCls.getZhenIncOrgNo(orgNo) + "' and ARMYTYPE='" + DICTVALUE + "'").ToString(); } else //机构编码可能不正确 return ""; }
/// <summary> /// 获取待审核(反馈)个数 /// </summary> /// <returns></returns> public JsonResult getSHFKCount() { Message ms = null; int count = 0; var curog = SystemCls.getCurUserOrgNo(); var bo = PublicCls.OrgIsShi(curog); //市 var bb = PublicCls.OrgIsXian(curog); //县 var bx = PublicCls.OrgIsZhen(curog); //镇 if (bo) { var list = JC_FIRECls.GetListModel(new JC_FIRE_SW { MANSTATESTR = "11,15" }); count = list.Count(); } else if (bb) { var list = JC_FIRECls.GetListModel(new JC_FIRE_SW { MANSTATESTR = "31,32,33,34" }); count = list.Count(); } else if (bx) { var list = JC_FIRECls.GetListModel(new JC_FIRE_SW { MANSTATESTR = "0,51" }); count = list.Count(); } ms = new Message(true, count.ToString(), ""); return(Json(ms)); }
/// <summary> /// 获取数据 /// </summary> /// <returns>参见模型</returns> public static DataTable getDT(JC_PERALARM_SW sw, out int total) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" FROM JC_PERALARM a"); sb.AppendFormat(" WHERE 1=1"); if (string.IsNullOrEmpty(sw.PERALARMID) == false) { sb.AppendFormat(" AND PERALARMID = '{0}'", ClsSql.EncodeSql(sw.PERALARMID)); } if (string.IsNullOrEmpty(sw.PERALARMPHONE) == false) { sb.AppendFormat(" AND PERALARMPHONE like '%{0}%'", ClsSql.EncodeSql(sw.PERALARMPHONE)); } if (string.IsNullOrEmpty(sw.PERALARMNAME) == false) { sb.AppendFormat(" AND PERALARMNAME like '%{0}%'", ClsSql.EncodeSql(sw.PERALARMNAME)); } if (!string.IsNullOrEmpty(sw.DateBegin)) { sb.AppendFormat(" AND PERALARMTIME>='{0} 00:00:00'", sw.DateBegin); } if (!string.IsNullOrEmpty(sw.DateEnd)) { sb.AppendFormat(" AND PERALARMTIME<='{0} 23:59:59'", sw.DateEnd); } if (string.IsNullOrEmpty(sw.MANSTATE) == false) { sb.AppendFormat(" AND MANSTATE = '{0}'", ClsSql.EncodeSql(sw.MANSTATE)); } if (string.IsNullOrEmpty(sw.BYORGNO) == false) { if (PublicCls.OrgIsShi(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.BYORGNO)); } else if (PublicCls.OrgIsXian(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.BYORGNO)); } else { sb.AppendFormat(" and BYORGNO = '{0}'", PublicCls.getZhenIncOrgNo(sw.BYORGNO)); } } string sql = "SELECT PERALARMID,BYORGNO, FIRENAME,PERALARMPHONE, PERALARMNAME, PERALARMTIME, PERALARMADDRESS, PERALARMCONTENT, MANSTATE, MANRESULT, MANTIME, MANUSERID,JD, WD, PEARLARMPRE, PEARLARMISSUED" + sb.ToString() + " order by PERALARMTIME DESC"; string sqlC = "select count(1) " + sb.ToString(); total = int.Parse(DataBaseClass.ReturnSqlField(sqlC)); sw.curPage = PagerCls.getCurPage(new PagerSW { curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total }); DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a"); return(ds.Tables[0]); }
/// <summary> /// 获取记录 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static DataTable getDT(JC_FIRE_PLAN_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" FROM JC_FIRE_PLAN"); sb.AppendFormat(" WHERE 1=1"); if (string.IsNullOrEmpty(sw.JC_FIRE_PLANID) == false) { sb.AppendFormat(" AND JC_FIRE_PLANID = '{0}'", ClsSql.EncodeSql(sw.JC_FIRE_PLANID)); } if (string.IsNullOrEmpty(sw.FIRELEVEL) == false)//火险等级 { sb.AppendFormat(" AND FIRELEVEL = '{0}'", ClsSql.EncodeSql(sw.FIRELEVEL)); } if (string.IsNullOrEmpty(sw.PLANTITLE) == false) { sb.AppendFormat(" AND PLANTITLE like '%{0}%'", ClsSql.EncodeSql(sw.PLANTITLE)); } if (string.IsNullOrEmpty(sw.BYORGNO) == false) { if (PublicCls.OrgIsShi(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.BYORGNO)); } else if (PublicCls.OrgIsXian(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.BYORGNO)); } else { sb.AppendFormat(" and BYORGNO = '{0}'", PublicCls.getZhenIncOrgNo(sw.BYORGNO)); } } if (string.IsNullOrEmpty(sw.searchOrgNo) == false)//根据当前单位编码,反查上级单位预案,主要用于调度指挥 { if (PublicCls.OrgIsShi(sw.searchOrgNo)) { sb.AppendFormat(" and BYORGNO = '{0}'", PublicCls.getShiIncOrgNo(sw.searchOrgNo) + "00000"); } else if (PublicCls.OrgIsXian(sw.searchOrgNo)) { sb.AppendFormat(" and BYORGNO in ('{0}','{1}')", PublicCls.getXianIncOrgNo(sw.searchOrgNo) + "000", PublicCls.getShiIncOrgNo(sw.searchOrgNo) + "00000"); } else { sb.AppendFormat(" and BYORGNO in ('{0}','{1}','{2}')", PublicCls.getXianIncOrgNo(sw.searchOrgNo) + "000", PublicCls.getShiIncOrgNo(sw.searchOrgNo) + "00000", sw.searchOrgNo); } } string sql = "SELECT JC_FIRE_PLANID, BYORGNO, FIRELEVEL, PLANTITLE, PLANCONTENT, PLANFILENAME" + sb.ToString() + " order by BYORGNO DESC, FIRELEVEL"; DataSet ds = DataBaseClass.FullDataSet(sql); return(ds.Tables[0]); }
/// <summary> /// 页面公用ViewBag /// </summary> /// <param name="PageCode">页面编码(菜单编码)</param> /// <param name="RightCode">权限编码 用于页面级权限验证</param> /// <param name="PageTitle">页面标题 标题为空自动获取页面标题及上级页面标题</param> public void pubViewBag(string PageCode, string RightCode, string PageTitle) { CookieModel cookieInfo = SystemCls.getCookieInfo(); ViewBag.getPageMenuStr = getPageMenuStr(PageCode); ViewBag.PageCode = PageCode; string curORGNo = SystemCls.getCurUserOrgNo(); if (PublicCls.OrgIsShi(curORGNo)) { curORGNo = ConfigCls.getConfigValue("ProvincialCapital");//州府所在地行政区划编码 } //获取火险等级 ViewBag.fireLevel = YJ_DANGERCLASSCls.getLevelByOrgNo(new YJ_DANGERCLASS_SW { BYORGNO = curORGNo }); //获取滚动信息 if (T_SYS_PARAMETERCls.getValueByFlag(new T_SYS_PARAMETER_SW { PARAMFLAG = "LoginInfo" }).ToString() == "0") { ViewBag.marqueeSysInfo = T_SYS_PARAMETERCls.getValueByFlag(new T_SYS_PARAMETER_SW { PARAMFLAG = "marqueeIndexInfo" }).ToString(); } else { ViewBag.marqueeSysInfo = YJ_WEATHERCls.getWeather(new YJ_WEATHER_SW { BYORGNO = curORGNo }); } ViewBag.PageLeftMenu = getPageLeftMenu(PageCode); //左侧菜单 ViewBag.isPageRight = SystemCls.isRight(RightCode); //判断页面是否有权限 ViewBag.SystemName = ConfigCls.getSystemName(); //系统名称 ViewBag.noticeRefreshTimeInterval = ConfigCls.noticeRefreshTimeInterval(); //菜单自动刷新间隔 ViewBag.PageTitle = PageTitle; //如果页面标题为空,则自动通过页面编码从系统菜单表中获取菜单名称及上级菜单名称 if (string.IsNullOrEmpty(PageTitle)) { ViewBag.PageTitle = T_SYS_MENUCls.getModel(new T_SYS_MENU_SW { MENUCODE = PageCode, SYSFLAG = ConfigCls.getSystemFlag() }).MENUNAME; } ViewBag.Title = ConfigCls.getSystemName() + "-" + ViewBag.PageTitle; //Title名称 ViewBag.SystemLogo = ConfigCls.getSystemLogo(); //获取系统logo图片 ViewBag.trueName = cookieInfo.trueName; //当前登录用户姓名 ViewBag.TopFireAlarm = ConfigCls.getTopFireAlarm(); //是否火情报警 ViewBag.TopFireLevel = ConfigCls.getTopFireLevel(); //是否显示火险等级 ViewBag.depName = StateSwitch.GetOrgNameByOrgNO(SystemCls.getCurUserOrgNo()); //部门名称 string systemFlag = ConfigCls.getSystemFlag(); //系统标识 如Springer ViewBag.T_UrlReferrer = Request.UrlReferrer; }
/// <summary> /// 获取分页列表 /// </summary> /// <param name="sw"></param> /// <param name="total"></param> /// <returns></returns> public static DataTable getDT(JC_FIRE_PLAN_SW sw, out int total) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" FROM JC_FIRE_PLAN"); sb.AppendFormat(" WHERE 1=1"); if (string.IsNullOrEmpty(sw.JC_FIRE_PLANID) == false) { sb.AppendFormat(" AND JC_FIRE_PLANID = '{0}'", ClsSql.EncodeSql(sw.JC_FIRE_PLANID)); } if (string.IsNullOrEmpty(sw.FIRELEVEL) == false) { sb.AppendFormat(" AND FIRELEVEL = '{0}'", ClsSql.EncodeSql(sw.FIRELEVEL)); } if (string.IsNullOrEmpty(sw.PLANTITLE) == false) { sb.AppendFormat(" AND PLANTITLE like '%{0}%'", ClsSql.EncodeSql(sw.PLANTITLE)); } if (string.IsNullOrEmpty(sw.BYORGNO) == false) { if (PublicCls.OrgIsShi(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.BYORGNO)); } else if (PublicCls.OrgIsXian(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.BYORGNO)); } else if (PublicCls.OrgIsZhen(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(sw.BYORGNO)); } else { sb.AppendFormat(" and BYORGNO = '{0}'", sw.BYORGNO); } } string sql = "SELECT JC_FIRE_PLANID, BYORGNO, FIRELEVEL, PLANTITLE, PLANCONTENT, PLANFILENAME" + sb.ToString() + " order by BYORGNO ,FIRELEVEL"; string sqlC = "select count(1) " + sb.ToString(); total = int.Parse(DataBaseClass.ReturnSqlField(sqlC)); sw.curPage = PagerCls.getCurPage(new PagerSW { curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total }); DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a"); return(ds.Tables[0]); }
/// <summary> /// 签收状态(仅限签收由于签收无顺序性 所以不能用最新时间的记录的状态来判断) /// </summary> /// <param name="orgno"></param> /// <param name="state"></param> /// <returns></returns> public static string QSStateNewList(string orgno, List <string> statelist) { string str = ""; bool bo = PublicCls.OrgIsShi(orgno);//是否为市 if (bo) { if (statelist.Contains("1") || statelist.Contains("4")) { str = "已签收"; } else { str = "未签收"; } } else { bool bb = PublicCls.OrgIsXian(orgno);//是否为县 if (bb) { if (statelist.Contains("2") || statelist.Contains("32")) { str = "已签收"; } else { str = "未签收"; } } else { bool bc = PublicCls.OrgIsZhen(orgno);//是否为乡镇 if (bc) { if (statelist.Contains("3")) { str = "已签收"; } else { str = "未签收"; } } } } return(str); }
/// <summary> /// 获取分页数据 /// </summary> /// <returns>参见模型</returns> public static DataTable getDT(JC_INFRAREDCAMERA_BASICINFO_SW sw, out int total) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" FROM JC_INFRAREDCAMERA_BASICINFO a"); sb.AppendFormat(" WHERE 1=1"); if (string.IsNullOrEmpty(sw.INFRAREDCAMERAID) == false) { sb.AppendFormat(" AND INFRAREDCAMERAID = '{0}'", ClsSql.EncodeSql(sw.INFRAREDCAMERAID)); } if (string.IsNullOrEmpty(sw.PHONE) == false) { sb.AppendFormat(" AND PHONE = '{0}'", ClsSql.EncodeSql(sw.PHONE)); } if (string.IsNullOrEmpty(sw.BYORGNO) == false) { if (PublicCls.OrgIsShi(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.BYORGNO)); } else if (PublicCls.OrgIsXian(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.BYORGNO)); } else if (PublicCls.OrgIsZhen(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(sw.BYORGNO)); } else { sb.AppendFormat(" and BYORGNO = '{0}'", sw.BYORGNO); } } string sql = "SELECT INFRAREDCAMERAID,BYORGNO,INFRAREDCAMERANAME,PHONE,JD,WD,GC,ADDRESS" + sb.ToString() + " order by BYORGNO"; string sqlC = "select count(1) " + sb.ToString(); total = int.Parse(DataBaseClass.ReturnSqlField(sqlC)); sw.curPage = PagerCls.getCurPage(new PagerSW { curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total }); DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a"); return(ds.Tables[0]); }
/// <summary> /// 根据单位返回市县乡及各日期后的统计 /// </summary> /// <param name="sw">参见模型</param> /// <returns>dt</returns> public static DataTable getDTByOrgNoToDate(PatrolRouteStat_SW sw) { StringBuilder sb = new StringBuilder(); if (PublicCls.OrgIsShi(sw.TopORGNO)) { /* * select count(substring(BYORGNO,1,6)+'000') as C,substring(BYORGNO,1,6)+'000' as BYORGNO,CONVERT(varchar(100),SBDATE, 111) as DBDATE * FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID * WHERE (SBDATE >= '2017-4-1') AND (SBDATE <= '2017-5-6') * --and substring(BYORGNO,1,6)+'000'='532501000' * group by substring(BYORGNO,1,6)+'000',CONVERT(varchar(100),SBDATE, 111) */ sb.AppendFormat(" select count(substring(BYORGNO,1,6)+'000000000') as C,substring(BYORGNO,1,6)+'000000000' as BYORGNO,SBDATE"); sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID"); sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" group by substring(BYORGNO,1,6)+'000000000',SBDATE"); } else if (PublicCls.OrgIsXian(sw.TopORGNO)) { // select count(BYORGNO) as C, BYORGNO,CONVERT(varchar(100),SBDATE, 111) as DBDATE //FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID //WHERE (SBDATE >= '2017-4-1') AND (SBDATE <= '2017-5-6') //and substring(BYORGNO,1,6)+'000'='532501000'--", sw.TopORGNO); //group by BYORGNO,CONVERT(varchar(100),SBDATE, 111) sb.AppendFormat(" select count(BYORGNO) as C, BYORGNO,SBDATE"); sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID"); sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" and substring(BYORGNO,1,6)+'000000000'='{0}'", sw.TopORGNO); sb.AppendFormat(" group by BYORGNO,SBDATE"); } else { //select count(a.USERID) as C,a.USERID as BYORGNO,CONVERT(varchar(100),SBDATE, 111) as DBDATE // FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID //WHERE (SBDATE >= '2017-4-1') AND (SBDATE <= '2017-5-6') //and BYORGNO='532501001'--", sw.TopORGNO); //group by a.USERID,CONVERT(varchar(100),SBDATE, 111) sb.AppendFormat(" select count(a.USERID) as C,a.USERID as BYORGNO,SBDATE"); sb.AppendFormat(" FROM T_IPS_REALDATATEMPORARY a LEFT OUTER JOIN T_IPSFR_USER b ON a.USERID = b.HID"); sb.AppendFormat(" WHERE (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" and BYORGNO='{0}'", sw.TopORGNO); sb.AppendFormat(" group by a.USERID,SBDATE"); } return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 根据DataTable和OrgNo和瞭望台类型判断记录个数 /// </summary> /// <param name="dt">DataTable</param> /// <param name="orgNo">单位编码</param> /// <param name="DICTVALUE">结构类型值</param> /// <returns>记录个数</returns> public static string getCountOVERWATCHByOrgNo(DataTable dt, string orgNo, string DICTVALUE) { if (string.IsNullOrEmpty(orgNo))//返回所有记录个数 { return(dt.Rows.Count.ToString()); } if (PublicCls.OrgIsShi(orgNo))//市 { if (string.IsNullOrEmpty(DICTVALUE)) { return(dt.Compute("count(DC_UTILITY_OVERWATCH_ID)", "substring(ORGNOS,1,4)='" + PublicCls.getShiIncOrgNo(orgNo) + "'").ToString()); } else { return(dt.Compute("count(DC_UTILITY_OVERWATCH_ID)", "substring(ORGNOS,1,4)='" + PublicCls.getShiIncOrgNo(orgNo) + "' and STRUCTURETYPE='" + DICTVALUE + "'").ToString()); } } else if (PublicCls.OrgIsXian(orgNo))//县 { if (string.IsNullOrEmpty(DICTVALUE)) { return(dt.Compute("count(DC_UTILITY_OVERWATCH_ID)", "substring(ORGNOS,1,6)='" + PublicCls.getXianIncOrgNo(orgNo) + "'").ToString()); } else { return(dt.Compute("count(DC_UTILITY_OVERWATCH_ID)", "substring(ORGNOS,1,6)='" + PublicCls.getXianIncOrgNo(orgNo) + "' and STRUCTURETYPE='" + DICTVALUE + "'").ToString()); } } else if (PublicCls.OrgIsZhen(orgNo)) { if (string.IsNullOrEmpty(DICTVALUE)) { return(dt.Compute("count(DC_UTILITY_OVERWATCH_ID)", "substring(ORGNOS,1,9)='" + PublicCls.getZhenIncOrgNo(orgNo) + "'").ToString()); } else { return(dt.Compute("count(DC_UTILITY_OVERWATCH_ID)", "substring(ORGNOS,1,9)='" + PublicCls.getZhenIncOrgNo(orgNo) + "' and STRUCTURETYPE='" + DICTVALUE + "'").ToString()); } } else //机构编码可能不正确 { return(""); } }
/// <summary> /// 判断护林员当前是否在线 /// </summary> /// <example> /// 传递参数: /// sw.USERID 护林员ID列表,多用户以逗号分隔 /// sw.SearchTime 查询时间,空默认取当前时间 /// sw.ORGNO 组织机构编码,非空取该组织机构下的所有护林员 /// </example> /// <param name="sw">参见模型</param> /// <returns>参见模型</returns> public static DataTable getOnLineDtByOrgno(T_IPS_REALDATATEMPORARYSW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" SELECT a.BYORGNO,a.HNAME,a.PHONE,a.ONSTATE,a.HID,b.SBTIME,b.ISOUTRAIL"); sb.AppendFormat(" FROM T_IPSFR_USER a left outer join T_IPS_REALDATATEMPORARY b on a.HID=b.USERID"); sb.AppendFormat(" where a.ISENABLE=1");//护林员启用状态 if (string.IsNullOrEmpty(sw.USERID) == false) { sb.AppendFormat(" and a.HID in({0})", sw.USERID); } if (string.IsNullOrEmpty(sw.SearchTime)) { sb.AppendFormat(" and DATEDIFF(mi,SBTIME,'{1}') <= '{0}' ", ConfigCls.inLineTimeInterval().ToString(), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } else { sb.AppendFormat(" and DATEDIFF(mi,SBTIME,'{1}') >= '{0}'", ConfigCls.inLineTimeInterval().ToString(), sw.SearchTime); } if (string.IsNullOrEmpty(sw.ORGNO) == false) { if (PublicCls.OrgIsShi(sw.ORGNO)) { sb.AppendFormat(" and a.BYORGNO like'{0}%'", PublicCls.getShiIncOrgNo(sw.ORGNO)); } else if (PublicCls.OrgIsXian(sw.ORGNO)) { sb.AppendFormat(" and a.BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.ORGNO)); } else if (PublicCls.OrgIsZhen(sw.ORGNO)) { sb.AppendFormat(" and a.BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(sw.ORGNO)); } else if (PublicCls.OrgIsCun(sw.ORGNO)) { sb.AppendFormat(" and a.BYORGNO like '{0}'", PublicCls.getCunIncOrgNo(sw.ORGNO)); } } if (string.IsNullOrEmpty(sw.PhoneHname) == false) { sb.AppendFormat(" AND (a.PHONE like '%{0}%' or a.HNAME like '%{0}%')", ClsSql.EncodeSql(sw.PhoneHname)); } return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 根据DataTable、orgNo和SBDATE判断记录个数 /// </summary> /// <param name="dt">DataTable</param> /// <param name="orgNo">单位编码</param> /// <param name="SBDATE">上报日期</param> /// <returns>记录个数</returns> public static string getCountByOrgNoSBDATE(DataTable dt, string orgNo, string SBDATE) { if (PublicCls.OrgIsShi(orgNo))//市 { return(dt.Compute("count(HID)", "substring(BYORGNO,1,4)='" + PublicCls.getShiIncOrgNo(orgNo) + "' and SBDATE='" + SBDATE + "'").ToString()); } else if (PublicCls.OrgIsXian(orgNo))//县 { return(dt.Compute("count(HID)", "substring(BYORGNO,1,6)='" + PublicCls.getXianIncOrgNo(orgNo) + "' and SBDATE='" + SBDATE + "'").ToString()); } else if (PublicCls.OrgIsZhen(orgNo)) { return(dt.Compute("count(HID)", "BYORGNO='" + PublicCls.getZhenIncOrgNo(orgNo) + "' and SBDATE='" + SBDATE + "'").ToString()); } else //机构编码可能不正确 { return(""); } }
/// <summary> /// 正常的 /// </summary> /// <param name="dt"></param> /// <param name="orgNo"></param> /// <param name="LengthError"></param> /// <returns>参见模型</returns> public static string getCountByPatrolLengthError(DataTable dt, string orgNo, float LengthError) { if (PublicCls.OrgIsShi(orgNo))//市 { return(dt.Compute("count(HID)", "substring(BYORGNO,1,4)='" + PublicCls.getShiIncOrgNo(orgNo) + "' and PatrolLenError>=" + LengthError.ToString()).ToString()); } else if (PublicCls.OrgIsXian(orgNo))//县 { return(dt.Compute("count(HID)", "substring(BYORGNO,1,6)='" + PublicCls.getXianIncOrgNo(orgNo) + "' and PatrolLenError>=" + LengthError.ToString()).ToString()); } else if (PublicCls.OrgIsZhen(orgNo)) { return(dt.Compute("count(HID)", "BYORGNO='" + PublicCls.getZhenIncOrgNo(orgNo) + "' and PatrolLenError>=" + LengthError.ToString()).ToString()); } else //机构编码可能不正确 { return(""); } }
/// <summary> /// 获取DataTable /// </summary> /// <param name="sw">参见模型</param> /// <returns>返回DataTable</returns> public static DataTable getDT(T_IPSFR_ROUTERAIL_PATROL_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" SELECT a.HID, a.ROUTEDATE, a.LONGITUDE, a.ROADID,a.LATITUDE, a.ROUTESTATE,b.BYORGNO,b.PHONE,b.HNAME"); sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_PATROL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID"); sb.AppendFormat(" where 1=1");//护林员启用状态 if (string.IsNullOrEmpty(sw.HID) == false) { sb.AppendFormat(" and a.HID='{0}'", sw.HID); } if (string.IsNullOrEmpty(sw.DateBegin) == false) { sb.AppendFormat(" and ROUTEDATE >= '{0}'", sw.DateBegin); } if (string.IsNullOrEmpty(sw.DateEnd) == false) { sb.AppendFormat(" and ROUTEDATE <= '{0}'", sw.DateEnd); } if (string.IsNullOrEmpty(sw.orgNo) == false) { if (PublicCls.OrgIsShi(sw.orgNo)) { sb.AppendFormat(" and b.BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.orgNo)); } else if (PublicCls.OrgIsXian(sw.orgNo)) { sb.AppendFormat(" and b.BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.orgNo)); } else { sb.AppendFormat(" and b.BYORGNO='{0}'", PublicCls.getZhenIncOrgNo(sw.orgNo)); } } if (string.IsNullOrEmpty(sw.PhoneHname) == false) { sb.AppendFormat(" AND (b.PHONE like '%{0}%' or b.HNAME like '%{0}%')", ClsSql.EncodeSql(sw.PhoneHname)); } sb.AppendFormat(" order by a.HID,a.ROUTEDATE DESC,a.ROUTESTATE desc"); return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 获取数据 /// </summary> /// <returns>参见模型</returns> public static DataTable getDT(JC_INFRAREDCAMERA_BASICINFO_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" FROM JC_INFRAREDCAMERA_BASICINFO a"); sb.AppendFormat(" WHERE 1=1"); if (string.IsNullOrEmpty(sw.INFRAREDCAMERAID) == false) { sb.AppendFormat(" AND INFRAREDCAMERAID = '{0}'", ClsSql.EncodeSql(sw.INFRAREDCAMERAID)); } if (string.IsNullOrEmpty(sw.PHONE) == false) { sb.AppendFormat(" AND PHONE = '{0}'", ClsSql.EncodeSql(sw.PHONE)); } if (string.IsNullOrEmpty(sw.BYORGNO) == false) { if (PublicCls.OrgIsShi(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.BYORGNO)); } else if (PublicCls.OrgIsXian(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.BYORGNO)); } else if (PublicCls.OrgIsZhen(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(sw.BYORGNO)); } else { sb.AppendFormat(" and BYORGNO = '{0}'", sw.BYORGNO); } } string sql = "SELECT INFRAREDCAMERAID,BYORGNO,INFRAREDCAMERANAME,PHONE,JD,WD,GC,ADDRESS" + sb.ToString() + " order by BYORGNO"; DataSet ds = DataBaseClass.FullDataSet(sql); return(ds.Tables[0]); }
/// <summary> /// 获取数据 /// </summary> /// <returns>参见模型</returns> public static DataTable getDT(JC_MONITOR_BASICINFO_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" FROM JC_MONITOR_BASICINFO a"); sb.AppendFormat(" WHERE 1=1"); if (string.IsNullOrEmpty(sw.EMID) == false) { sb.AppendFormat(" AND EMID = '{0}'", ClsSql.EncodeSql(sw.EMID)); } if (string.IsNullOrEmpty(sw.TTBH) == false) { sb.AppendFormat(" AND TTBH like '%{0}%'", ClsSql.EncodeSql(sw.TTBH)); } if (string.IsNullOrEmpty(sw.BYORGNO) == false) { if (PublicCls.OrgIsShi(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.BYORGNO)); } else if (PublicCls.OrgIsXian(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.BYORGNO)); } else if (PublicCls.OrgIsZhen(sw.BYORGNO)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(sw.BYORGNO)); } else { //sb.AppendFormat(" and BYORGNO = '{0}'", PublicCls.getZhenIncOrgNo(sw.BYORGNO)); } } string sql = "SELECT EMID, TTBH, EMNAME, BYORGNO, JD, WD, GC, ADDRESS, IP, LOGINUSERNAME, USERPWD, XH, PP, GD, JCJL,OBJID,TEMPLATEDID,PORT,TYPE" + sb.ToString() + " order by BYORGNO"; DataSet ds = DataBaseClass.FullDataSet(sql); return(ds.Tables[0]); }
/// <summary> /// 获取火情来源数据 /// </summary> /// <returns></returns> public JsonResult GetFireSourceData() { var result = new List <OutData>(); MessageListObject ms = new MessageListObject(false, null); var sw = new JC_FIRE_SW(); var curogr = SystemCls.getCurUserOrgNo(); if (!string.IsNullOrEmpty(curogr)) { var bo = PublicCls.OrgIsShi(curogr); var bb = PublicCls.OrgIsXian(curogr); var bx = PublicCls.OrgIsZhen(curogr); if (!bo) { sw.BYORGNO = curogr; } sw.isCountIndex = "1"; //遍历火情来源 Array ary = Enum.GetValues(typeof(EnumType)); //array是数组的基类, 无法实例化 var list = JC_FIRECls.GetListModel(sw); foreach (int item in ary) { string hotsum = "0"; var fireData = new OutData(); if (item.ToString() != "1")//排除红外相机 { if (list.Any()) { var firelist = list.Where(p => p.FIREFROM == item.ToString() && !string.IsNullOrEmpty(p.FIREFROM) && p.ISOUTFIRE != "1" && p.MANSTATE != "19" && p.MANSTATE != "18"); //筛选热点类型 排除已灭的 && ((p.ISOUTFIRE.Trim() == "1" && p.MANSTATE.Trim() != "4") || p.ISOUTFIRE.Trim() != "1") 已在程序中处理 hotsum = firelist.Count().ToString(); //热点个数 } fireData.name = Enum.GetName(typeof(EnumType), item); fireData.value = hotsum; result.Add(fireData); } } ms = new MessageListObject(true, result); } return(Json(ms)); }
/// <summary> /// 获取DataTable /// </summary> /// <param name="sw">参见模型</param> /// <returns>DataTable</returns> public static DataTable getDT(OutRaiLCount_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" SELECT a.HID,a.LONGITUDE,a.LATITUDE,a.SBTIME,b.HNAME,b.PHONE,b.BYORGNO"); sb.AppendFormat(" FROM T_IPSFR_ROUTERAIL_RAIL a LEFT OUTER JOIN T_IPSFR_USER b ON a.HID = b.HID"); sb.AppendFormat(" where 1=1");//护林员启用状态 if (string.IsNullOrEmpty(sw.DateBegin) == false) { sb.AppendFormat(" and a.SBTIME >= '{0} 00:00:00'", sw.DateBegin); } if (string.IsNullOrEmpty(sw.DateEnd) == false) { sb.AppendFormat(" and a.SBTIME <= '{0} 23:59:59'", sw.DateEnd); } if (string.IsNullOrEmpty(sw.orgNo) == false) { if (PublicCls.OrgIsShi(sw.orgNo)) { sb.AppendFormat(" and left(b.BYORGNO,4)='{0}'", PublicCls.getShiIncOrgNo(sw.orgNo)); } else if (PublicCls.OrgIsXian(sw.orgNo)) { sb.AppendFormat(" and left(b.BYORGNO,6)='{0}'", PublicCls.getXianIncOrgNo(sw.orgNo)); } else { sb.AppendFormat(" and b.BYORGNO='{0}'", PublicCls.getZhenIncOrgNo(sw.orgNo)); } } if (string.IsNullOrEmpty(sw.PhoneHname) == false) { sb.AppendFormat(" AND (b.PHONE like '%{0}%' or b.HNAME like '%{0}%')", ClsSql.EncodeSql(sw.PhoneHname)); } //sb.AppendFormat(" order by a.HID,a.SBTIME desc"); return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 根据DataTable和OrgNo判断记录个数 /// </summary> /// <param name="dt">DataTable</param> /// <param name="orgNo">单位编码</param> /// <returns>记录个数</returns> public static string getCountByOrgNo(DataTable dt, string orgNo) { if (string.IsNullOrEmpty(orgNo))//返回所有记录个数 { return(dt.Rows.Count.ToString()); } if (PublicCls.OrgIsShi(orgNo))//市 { return(dt.Compute("count(HID)", "substring(BYORGNO,1,4)='" + PublicCls.getShiIncOrgNo(orgNo) + "'").ToString()); } else if (PublicCls.OrgIsXian(orgNo))//县 { return(dt.Compute("count(HID)", "substring(BYORGNO,1,6)='" + PublicCls.getXianIncOrgNo(orgNo) + "'").ToString()); } else if (PublicCls.OrgIsZhen(orgNo)) { return(dt.Compute("count(HID)", "BYORGNO='" + PublicCls.getZhenIncOrgNo(orgNo) + "'").ToString()); } else //机构编码可能不正确 { return(""); } }
/// <summary> /// 获取数据,获取所有角色 /// </summary> /// <returns>参见模型</returns> public static DataTable getDT(T_SYSSEC_ROLE_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("SELECT ROLEID, ROLENAME, ROLENOTE, SYSFLAG,ROLELEVEL, ORDERBY"); sb.AppendFormat(" FROM T_SYSSEC_ROLE"); sb.AppendFormat(" WHERE 1=1"); if (string.IsNullOrEmpty(sw.ROLEID) == false) { sb.AppendFormat(" AND ROLEID = '{0}'", ClsSql.EncodeSql(sw.ROLEID)); } if (string.IsNullOrEmpty(sw.SYSFLAG) == false) { sb.AppendFormat(" AND SYSFLAG = '{0}'", ClsSql.EncodeSql(sw.SYSFLAG)); } //if (string.IsNullOrEmpty(sw.USERID) == false) // sb.AppendFormat(" AND USERID = '{0}'", ClsSql.EncodeSql(sw.USERID)); string curOrg = SystemCls.getCurUserOrgNo(); if (PublicCls.OrgIsShi(curOrg)) { sb.AppendFormat(" AND ROLELEVEL>=1"); } else if (PublicCls.OrgIsXian(curOrg)) { sb.AppendFormat(" AND ROLELEVEL>=2"); } else { sb.AppendFormat(" AND ROLELEVEL>=3"); } sb.AppendFormat(" ORDER BY ORDERBY "); DataSet ds = DataBaseClass.FullDataSet(sb.ToString()); return(ds.Tables[0]); }
/// <summary> /// 获取上报与护林员、单位组合数量统计 /// </summary> /// <param name="dt">DataTable</param> /// <param name="ORGNO">单位编码</param> /// <param name="SYSTYPEVALUE">上报类型ID</param> /// <returns>采集数量</returns> public static string getCountByOrgHUse(DataTable dt, string ORGNO, string SYSTYPEVALUE) { if (PublicCls.OrgIsShi(ORGNO))//市 { if (string.IsNullOrEmpty(SYSTYPEVALUE)) { return(dt.Compute("count(HID)", "substring(ORGNO,1,4)='" + PublicCls.getShiIncOrgNo(ORGNO) + "'").ToString()); } else { return(dt.Compute("count(HID)", "substring(ORGNO,1,4)='" + PublicCls.getShiIncOrgNo(ORGNO) + "' and SYSTYPEVALUE='" + SYSTYPEVALUE + "'").ToString()); } } else if (PublicCls.OrgIsXian(ORGNO))//县 { if (string.IsNullOrEmpty(SYSTYPEVALUE)) { return(dt.Compute("count(HID)", "substring(ORGNO,1,6)='" + PublicCls.getXianIncOrgNo(ORGNO) + "'").ToString()); } else { return(dt.Compute("count(HID)", "substring(ORGNO,1,6)='" + PublicCls.getXianIncOrgNo(ORGNO) + "' and SYSTYPEVALUE='" + SYSTYPEVALUE + "'").ToString()); } } else { if (string.IsNullOrEmpty(SYSTYPEVALUE)) { return(dt.Compute("count(HID)", "substring(ORGNO,1,9)='" + PublicCls.getZhenIncOrgNo(ORGNO) + "'").ToString()); } else { return(dt.Compute("count(HID)", "substring(ORGNO,1,9)='" + PublicCls.getZhenIncOrgNo(ORGNO) + "' and SYSTYPEVALUE='" + SYSTYPEVALUE + "'").ToString()); } } //return str; }
/// <summary> /// 根据DataTable和OrgNo判断记录个数 /// </summary> /// <param name="dt">DataTable</param> /// <param name="orgNo">单位编码</param> /// <param name="state">状态</param> /// <returns>记录个数</returns> public static string getCountByOrgNo(DataTable dt, string orgNo, string state) { if (string.IsNullOrEmpty(orgNo))//返回所有记录个数 { return(dt.Rows.Count.ToString()); } string sqlStr = ""; if (string.IsNullOrEmpty(state) == false) { if (state == "0") //未检 { sqlStr = " AND ROUTESTATE=0"; } else { sqlStr = " AND ROUTESTATE=1"; } } if (PublicCls.OrgIsShi(orgNo))//市 { return(dt.Compute("count(HID)", "substring(BYORGNO,1,4)='" + PublicCls.getShiIncOrgNo(orgNo) + "'" + sqlStr + "").ToString()); } else if (PublicCls.OrgIsXian(orgNo))//县 { return(dt.Compute("count(HID)", "substring(BYORGNO,1,6)='" + PublicCls.getXianIncOrgNo(orgNo) + "'" + sqlStr + "").ToString()); } else if (PublicCls.OrgIsZhen(orgNo)) { return(dt.Compute("count(HID)", "BYORGNO='" + PublicCls.getZhenIncOrgNo(orgNo) + "'" + sqlStr + "").ToString()); } else //机构编码可能不正确 { return(""); } }
/// <summary> /// 护林员与数据上传中间表组合查询 /// </summary> /// <param name="sw">参见模型</param> /// <returns>参见模型</returns> public static DataTable getHRAndRealDataDT(T_IPS_REALDATATEMPORARYSW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(" SELECT a.BYORGNO,a.HNAME,a.HID,a.PHONE,b.USERID, b.SBDATE,(b.PATROLLENGTH-a.PATROLLENGTH/1000) as PatrolLenError,a.PATROLLENGTH/1000,b.PATROLLENGTH as RealPATROLLENGTH"); sb.AppendFormat(" FROM T_IPSFR_USER a left outer join T_IPS_REALDATATEMPORARY b on a.HID=b.USERID"); sb.AppendFormat(" where a.ISENABLE=1 and b.USERID is not null");//护林员启用状态 if (string.IsNullOrEmpty(sw.DateBegin) == false) { sb.AppendFormat(" and SBDATE >= '{0}'", sw.DateBegin); } if (string.IsNullOrEmpty(sw.DateEnd) == false) { sb.AppendFormat(" and SBDATE <= '{0}'", sw.DateEnd); } if (string.IsNullOrEmpty(sw.ORGNO) == false) { if (PublicCls.OrgIsShi(sw.ORGNO)) { sb.AppendFormat(" and a.BYORGNO like'{0}%'", PublicCls.getShiIncOrgNo(sw.ORGNO)); } else if (PublicCls.OrgIsXian(sw.ORGNO)) { sb.AppendFormat(" and a.BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.ORGNO)); } else { sb.AppendFormat(" and a.BYORGNO = '{0}'", PublicCls.getZhenIncOrgNo(sw.ORGNO)); } } if (string.IsNullOrEmpty(sw.PhoneHname) == false) { sb.AppendFormat(" AND (a.PHONE like '%{0}%' or a.HNAME like '%{0}%')", ClsSql.EncodeSql(sw.PhoneHname)); } return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 怠工 /// </summary> /// <param name="sw">参见模型</param> /// <returns>DT</returns> public static DataTable getDTSabotageByOrgNo(SabotageCount_SW sw) { string LengthError = (ConfigCls.getPatrolLengthError() / 1000).ToString();//巡检距离误差 StringBuilder sb = new StringBuilder(); if (PublicCls.OrgIsShi(sw.TopORGNO)) { sb.AppendFormat(" select count(substring(BYORGNO,1,6)+'000000000') as C,substring(BYORGNO,1,6)+'000000000' as BYORGNO,SBDATE,"); sb.AppendFormat(" case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end as PatrolLenErro", LengthError); sb.AppendFormat(" FROM T_IPSFR_USER a left outer join T_IPS_REALDATATEMPORARY b on a.HID=b.USERID"); sb.AppendFormat(" where a.ISENABLE=1 and b.USERID is not null and a.PATROLLENGTH/1000>0"); sb.AppendFormat(" and (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" group by SBDATE,substring(BYORGNO,1,6)+'000000000',case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end", LengthError); } else if (PublicCls.OrgIsXian(sw.TopORGNO)) { sb.AppendFormat(" select count(BYORGNO) as C,BYORGNO,SBDATE,"); sb.AppendFormat(" case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end as PatrolLenErro", LengthError); sb.AppendFormat(" FROM T_IPSFR_USER a left outer join T_IPS_REALDATATEMPORARY b on a.HID=b.USERID"); sb.AppendFormat(" where a.ISENABLE=1 and b.USERID is not null and a.PATROLLENGTH/1000>0"); sb.AppendFormat(" and (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" and substring(BYORGNO,1,6)+'000000000'='{0}'", sw.TopORGNO); sb.AppendFormat(" group by SBDATE,BYORGNO,case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end", LengthError); } else { sb.AppendFormat(" select count(b.USERID) as C,b.USERID as BYORGNO,SBDATE,"); sb.AppendFormat(" case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end as PatrolLenErro", LengthError); sb.AppendFormat(" FROM T_IPSFR_USER a left outer join T_IPS_REALDATATEMPORARY b on a.HID=b.USERID"); sb.AppendFormat(" where a.ISENABLE=1 and b.USERID is not null and a.PATROLLENGTH/1000>0"); sb.AppendFormat(" and (SBDATE >= '{0}') AND (SBDATE <= '{1}')", sw.DateBegin, sw.DateEnd); sb.AppendFormat(" and BYORGNO='{0}'", sw.TopORGNO); sb.AppendFormat(" group by SBDATE,b.USERID,case when(a.PATROLLENGTH/1000-b.PATROLLENGTH)<{0} then 1 else 0 end", LengthError); } return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 获取周末人员值班信息 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static DataTable getWeekDT(OD_USER_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("SELECT "); sb.AppendFormat(" OD_TYPEID,ODUID, ONDUTYDATE, BYORGNO, ONDUTYUSERTYPE, ONDUTYUSERID, ISATTENDED,ATTENDEDTIME"); sb.AppendFormat(" FROM OD_USER"); sb.AppendFormat(" WHERE 1=1"); sb.AppendFormat(" AND ONDUTYDATE in(SELECT ONDUTYDATE FROM OD_DATE where WEEK in('星期六','星期日'))"); if (string.IsNullOrEmpty(sw.DTBegin) == false) { sb.AppendFormat(" AND ONDUTYDATE>='{0}'", sw.DTBegin); } if (string.IsNullOrEmpty(sw.DTEnd) == false) { sb.AppendFormat(" AND ONDUTYDATE<='{0}'", sw.DTEnd); } if (string.IsNullOrEmpty(sw.curOrgNo) == false) { if (PublicCls.OrgIsShi(sw.curOrgNo)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.curOrgNo)); } else if (PublicCls.OrgIsXian(sw.curOrgNo)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.curOrgNo)); } else { sb.AppendFormat(" and BYORGNO = '{0}'", PublicCls.getZhenIncOrgNo(sw.curOrgNo)); } } sb.AppendFormat(" order by ONDUTYDATE"); return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 获取周末人员值班信息 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static DataTable getWeekDT(DUTY_USER_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("SELECT "); sb.AppendFormat(" DUID, DUTYDATE, BYORGNO, DUTYUSERTYPE, DUTYUSERID, ISATTENDED,ATTENDEDTIME"); sb.AppendFormat(" FROM DUTY_USER"); sb.AppendFormat(" WHERE 1=1"); sb.AppendFormat(" AND DUTYDATE in(SELECT DISTINCT DUTYDATE FROM DUTY_USER where datepart(weekday,DUTYDATE)=1 or datepart(weekday,DUTYDATE)=7)"); if (string.IsNullOrEmpty(sw.DTBegin) == false) { sb.AppendFormat(" AND DUTYDATE>='{0}'", sw.DTBegin); } if (string.IsNullOrEmpty(sw.DTEnd) == false) { sb.AppendFormat(" AND DUTYDATE<='{0}'", sw.DTEnd); } if (string.IsNullOrEmpty(sw.curOrgNo) == false) { if (PublicCls.OrgIsShi(sw.curOrgNo)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.curOrgNo)); } else if (PublicCls.OrgIsXian(sw.curOrgNo)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.curOrgNo)); } else { sb.AppendFormat(" and BYORGNO = '{0}'", PublicCls.getZhenIncOrgNo(sw.curOrgNo)); } } sb.AppendFormat(" order by DUTYDATE"); return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }