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>
/// <param name="bean"></param>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <returns></returns>
public override DataTable QueryByPage(SmsOutBox bean, int page, int rows)
{
var sql = new StringBuilder();
sql.Append("select * from msg_outbox where 1=1 ");
var list = new List <object>();
var i = 0;
if (!string.IsNullOrEmpty(bean.Msgid.ToString(CultureInfo.InvariantCulture)))
{
sql.Append(" and Msgid={" + i++ + "}");
list.Add(bean.Msgid);
}
if (!string.IsNullOrEmpty(bean.Expresslevel.ToString(CultureInfo.InvariantCulture)))
{
sql.Append(" and Expresslevel={" + i++ + "}");
list.Add(bean.Expresslevel);
}
if (!string.IsNullOrEmpty(bean.Sender))
{
sql.Append(" and Sender={" + i++ + "}");
list.Add(bean.Sender);
}
if (!string.IsNullOrEmpty(bean.Receiver))
{
sql.Append(" and Receiver={" + i++ + "}");
list.Add(bean.Receiver);
}
if (!string.IsNullOrEmpty(bean.Msgtype.ToString(CultureInfo.InvariantCulture)))
{
sql.Append(" and Msgtype={" + i++ + "}");
list.Add(bean.Msgtype);
}
if (!string.IsNullOrEmpty(bean.Msgtitle))
{
sql.Append(" and Msgtitle={" + i++ + "}");
list.Add(bean.Msgtitle);
}
if (!string.IsNullOrEmpty(bean.Mmscontentlocation))
{
sql.Append(" and Mmscontentlocation={" + i++ + "}");
list.Add(bean.Mmscontentlocation);
}
if (!string.IsNullOrEmpty(bean.Sendtime.ToString()))
{
sql.Append(" and Sendtime={" + i++ + "}");
list.Add(bean.Sendtime);
}
if (!string.IsNullOrEmpty(bean.Commport.ToString(CultureInfo.InvariantCulture)))
{
sql.Append(" and Commport={" + i + "}");
list.Add(bean.Commport);
}
return(Oop.GetDataTableByPage(sql.ToString(), (page - 1) * rows, rows, list.ToArray()));
}
/// <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="bean"></param>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <returns></returns>
public override DataTable QueryByPage(Employer bean, int page, int rows)
{
Log.Debug("Query方法参数：" + bean.ToString());
var sql = new StringBuilder();
sql.Append("select * from t_employer where 1=1 ");
var list = new List <object>();
var i = 0;
if (!string.IsNullOrEmpty(bean.Id))
{
sql.Append(" and id={" + ++i + "}");
list.Add(bean.Id);
}
if (!string.IsNullOrEmpty(bean.WorkNum))
{
sql.Append(" and WorkNum={" + ++i + "}");
list.Add(bean.WorkNum);
}
if (!string.IsNullOrEmpty(bean.Name))
{
sql.Append(" and name={" + ++i + "}");
list.Add(bean.Name);
}
if (!string.IsNullOrEmpty(bean.DeptId))
{
sql.Append(" and deptid={" + ++i + "}");
list.Add(bean.DeptId);
}
if (!string.IsNullOrEmpty(bean.PhoneNum))
{
sql.Append(" and PhoneNum={" + ++i + "}");
list.Add(bean.PhoneNum);
}
if (!string.IsNullOrEmpty(bean.Email))
{
sql.Append(" and email={" + ++i + "}");
list.Add(bean.Email);
}
return(Oop.GetDataTableByPage(sql.ToString(), page * rows, rows, list.ToArray()));
}
/// <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));
}
