public DataTable QueryBySjlx(string sjlx, string xh, string sjx, int page, int rows, ref int count) { Log.Debug("QueryBySjlx方法参数为:sjlx:" + sjlx + ",page:" + page + ",rows:" + rows + ", ref count:" + count); var sqlTable = new StringBuilder(@"select gp.*, (select changjia from dev_powerpiletypes dpt where dpt.parserkey=gp.piletypeid) Zhuangcj, (select zhuanglei_x from dev_powerpiletypes dpt where dpt.parserkey=gp.piletypeid) ZhuangLeiX, (select itemname from gat_item t where t.itemno=gp.gatitemid) gatitemname from gat_pointcfg gp where 1=1 "); var sqlCount = new StringBuilder("select count(*) from gat_pointcfg gp where 1=1 "); var sqlWhere = new StringBuilder(); var list = new List <object>(); var i = -1; if (!string.IsNullOrEmpty(sjlx)) { sqlWhere.Append(" and exists (select 1 from gat_item gi where gi.itemno=gp.gatitemid and gi.datatype={" + ++i + "}) "); list.Add(sjlx); } if (!string.IsNullOrEmpty(xh)) { sqlWhere.Append(" and gp.piletypeid like {" + ++i + "} "); list.Add("%" + xh + "%"); } if (!string.IsNullOrEmpty(sjx)) { sqlWhere.Append(" and exists (select 1 from gat_item gi where gi.itemno=gp.gatitemid and gi.itemname like {" + ++i + "}) "); list.Add("%" + sjx + "%"); } //DataTable dt = Oop.GetDataTableByPage(sqlTable.Append(sqlWhere).Append(" order by createdt desc").ToString(), (page - 1) * rows, rows, list.ToArray()); DataTable dt = Oop.GetDataTableByPage(sqlTable.Append(sqlWhere).Append(" order by Zhuangcj,ZhuangLeiX,gatitemname").ToString(), (page - 1) * rows, rows, list.ToArray()); count = int.Parse(Oop.GetScalar(sqlCount.Append(sqlWhere).ToString(), list.ToArray()).ToString()); return(dt); }
/// <summary> /// 查询异常告警 /// </summary> /// <returns></returns> public DataTable FindByTelesignallingWarn(TelesignallingParam telesignallingParam, int page, int rows, ref int count) { Log.Debug("FindByTelesignallingWarn方法没有参数"); var sql = new StringBuilder(); var sqlWhere = new StringBuilder(); var sqlCount = new StringBuilder("select count(*) from oth_warnrec owr inner join dev_chargpile dcp2 on dcp2.dev_chargpile=owr.targetdev "); var getpage = new GetPage(); sql.Append("select owr.id,owr.targetdev,owr.dataitemid, "); sql.Append("(select sc.codename from sys_code sc where sc.parentid='5077B15C-4F5E-49B2-9E32-E7C1E2F1E74D' and owr.logtype=sc.code) codename, "); sql.Append("to_char(owr.occurdt,'yyyy-mm-dd hh24:mi:ss') occurdt,"); sql.Append("(select dcs.zhan_jc from dev_chargstation dcs "); sql.Append("inner join dev_branch db on db.zhuan_bh=dcs.zhan_bh "); sql.Append("inner join dev_chargpile dcp on dcp.box_id=db.branchno "); sql.Append("where dcp.dev_chargpile=owr.targetdev) zhanjc,"); sql.Append("(select dcp.yunxing_bh from dev_chargpile dcp where dcp.dev_chargpile=owr.targetdev) yunxing_bh,"); sql.Append("(select gi.Itemname from gat_item gi where gi.itemno=owr.dataitemid) itemname, "); sql.Append("owr.logdesc,owr.processflag "); sql.Append("from oth_warnrec owr "); sql.Append("inner join dev_chargpile dcp2 on dcp2.dev_chargpile=owr.targetdev "); sqlWhere.Append("where owr.dataitemid!='CardExceptionState' and owr.dataitemid!='DTUStatus' and owr.dataitemid!='PowerStopState' "); if (!string.IsNullOrEmpty(telesignallingParam.WarnType)) { sqlWhere.Append("and owr.logtype='" + telesignallingParam.WarnType + "' "); } if (!string.IsNullOrEmpty(telesignallingParam.Zhanbh)) { sqlWhere.Append("and owr.targetdev like '" + telesignallingParam.Zhanbh + "%' "); } if (!string.IsNullOrEmpty(telesignallingParam.ZhanYxBh)) { sqlWhere.Append("and dcp2.yunxing_bh='" + telesignallingParam.ZhanYxBh + "' "); } if (!string.IsNullOrEmpty(telesignallingParam.Kssj) && !string.IsNullOrEmpty(telesignallingParam.Jssj)) { sqlWhere.Append("and owr.occurdt between to_date('" + telesignallingParam.Kssj + "','yyyy-mm-dd') and to_date('" + telesignallingParam.Jssj + "','yyyy-mm-dd')+1 "); } if (!string.IsNullOrEmpty(telesignallingParam.Clfs)) { sqlWhere.Append("and owr.processflag='" + telesignallingParam.Clfs + "' "); } sqlWhere.Append("order by owr.occurdt desc"); count = int.Parse(Oop.GetScalar(sqlCount.Append(sqlWhere).ToString()).ToString()); return(Oop.GetDataTableByPage(sql.Append(sqlWhere).ToString(), (page - 1) * rows, rows)); }
/// <summary> /// 根据给点的条件查询桩状态 /// </summary> /// <param name="cdcz">场站编号</param> /// <param name="zbh">桩编号</param> /// <param name="dateBegin">起始日期</param> /// <param name="dateEnd">结束日期</param> /// <param name="page">页码</param> /// <param name="rows">行数</param> /// <param name="count">总计</param> /// <returns></returns> public DataTable FindByChargingPileInfo(string cdcz, string zbh, DateTime dateBegin, DateTime dateEnd, int page, int rows, ref int count) { var i = -1; var list = new List <object>(); Log.Debug("FindByChargingPileInfo(重载):方法参数:cdcz=" + cdcz + ",zbh=" + zbh + ",dateBegin=" + dateBegin.ToString("yyyy-MM-dd") + ",dateEnd=" + dateEnd.ToString("yyyy-MM-dd")); var sqlCount = new StringBuilder("select count(*) "); var sql = new StringBuilder("select (select dcs.zhan_jc from dev_chargstation dcs where dcs.zhan_bh = substr(dcp.dev_chargpile,0,3)) zhanjc, "); sql.Append("dcp.yunxing_bh YunxingBh,gdr.charge_power chargepower,gdr.charge_money/100 chargemoney,gdr.card_no cardno, "); sql.Append("to_char(gdr.startdt,'yyyy-mm-dd hh24:mi:ss') startdt, "); sql.Append("to_char(gdr.enddt,'yyyy-mm-dd hh24:mi:ss') enddt "); var sqlWhere = new StringBuilder(""); sqlWhere.Append("from gat_data_record gdr inner join dev_chargpile dcp on gdr.zhuan_id=dcp.dev_chargpile where 1=1 "); if (zbh.Length > 0)//桩编号如果存在 { sqlWhere.Append(" and gdr.zhuan_id={" + ++i + "} "); list.Add(zbh); } else if (cdcz.Length > 0) { sqlWhere.Append(" and gdr.zhuan_id like {" + ++i + "} "); list.Add(cdcz + "%"); } if (dateBegin != DateTime.MinValue) { sqlWhere.Append(" and gdr.startdt>to_date({" + ++i + "},'yyyy-mm-dd') "); list.Add(dateBegin.AddDays(-1).ToString("yyyy-MM-dd")); } if (dateEnd != DateTime.MinValue) { sqlWhere.Append(" and gdr.enddt<to_date({" + ++i + "},'yyyy-mm-dd') "); list.Add(dateEnd.AddDays(1).ToString("yyyy-MM-dd")); } count = int.Parse(Oop.GetScalar(sqlCount.Append(sqlWhere).ToString(), list.ToArray()).ToString()); return(Oop.GetDataTableByPage( sql.Append(sqlWhere).Append(" order by gdr.startdt desc,gdr.enddt desc ").ToString(), (page - 1) * rows, rows, list.ToArray())); }
/// <summary> /// 根据异常类型查询未处理异常 /// </summary> /// <param name="type">异常类型</param> /// <param name="page"></param> /// <param name="rows"></param> /// <returns></returns> public DataTable QueryByType(string type, int page, int rows, ref int count) { Log.Debug("Query方法参数:type:" + type); const string sql = @"select ow.id, (select dcs.zhuan_mc from dev_chargstation dcs where dcs.zhan_bh = (select db.zhuan_bh from DEV_BRANCH db where db.branchno=dcp.box_id)) zhanmc,dppt.parserkey,dppt.zhuanglei_x zhuangleix, ow.targetdev,(select gi.itemname from gat_item gi where gi.itemno=ow.dataitemid) itemname,gp.yxstates, gp.yxeff,gp.yxwarn,gp.limitmin,gp.limitmax,gp.eff_min,gp.eff_max ,ow.m_value mvalue,ow.logdesc, ow.processflag as processflag from oth_warnrec ow left join dev_chargpile dcp on ow.targetdev=dcp.dev_chargpile left join DEV_POWERPILETYPES dppt on dppt.parserkey=dcp.piletypeid left join gat_pointcfg gp on gp.gatitemid=ow.dataitemid and gp.piletypeid=dppt.parserkey where ow.processflag=0 and ow.logtype={0} order by ow.createdt desc"; Log.Debug("SQL语句:" + sql); const string sqlCount = @"select count(*) from oth_warnrec ow where ow.logtype={0} and ow.processflag=0 "; var o = Oop.GetScalar(sqlCount, type); count = int.Parse(o.ToString()); return(Oop.GetDataTableByPage(sql, (page - 1) * rows, rows, type)); }
/// <summary> /// 异常数据查询 /// </summary> /// <param name="yclx">异常类型</param> /// <param name="dateBegin">起始时间</param> /// <param name="dateEnd">结束时间</param> /// <param name="page">页码</param> /// <param name="rows">每页行数</param> /// <param name="count">总行数</param> /// <returns></returns> public DataTable QueryByPage(string yclx, DateTime dateBegin, DateTime dateEnd, int page, int rows, ref int count) { Log.Debug("QueryByPage方法参数:yclx:" + yclx + " dateBegin:" + dateBegin.ToString("yyyy-MM-dd") + ",dateEnd:" + dateEnd.ToString("yyyy-MM-dd") + ",page:" + page + ",rows:" + rows); const string sql = @"select (select dcs.zhuan_mc from dev_chargstation dcs where dcs.zhan_bh = (select db.zhuan_bh from DEV_BRANCH db where db.branchno=dcp.box_id)) zhanmc,dppt.parserkey,dppt.zhuanglei_x zhuangleix, (select dc.yunxing_bh from dev_chargpile dc where dc.dev_chargpile=ow.targetdev) targetdev, (select gi.itemname from gat_item gi where gi.itemno=ow.dataitemid) itemname,gp.yxstates,ow.processeep, gp.yxeff,gp.yxwarn,gp.limitmin,gp.limitmax,gp.eff_min,gp.eff_max ,ow.m_value mvalue,ow.processflag,ow.logdesc,ow.processdt from oth_warnrec ow left join dev_chargpile dcp on ow.targetdev=dcp.dev_chargpile left join DEV_POWERPILETYPES dppt on dppt.parserkey=dcp.piletypeid left join gat_pointcfg gp on gp.gatitemid=ow.dataitemid and gp.piletypeid=dppt.parserkey where ow.logtype={0} and ow.createdt between {1} and {2} order by ow.createdt"; Log.Debug("SQL语句:" + sql); const string sqlCount = @"select count(*) from oth_warnrec ow where ow.logtype={0} and ow.createdt between {1} and {2} "; var o = Oop.GetScalar(sqlCount, yclx, dateBegin, dateEnd); count = int.Parse(o.ToString()); return(Oop.GetDataTableByPage(sql, (page - 1) * rows, rows, yclx, dateBegin, dateEnd)); }