public override DataTable QueryByPage(JobsDetails bean, int page, int rows, ref int count)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from cmd_jobsdetails where 1=1 ");
if (string.IsNullOrEmpty(bean.Id))
{
sql.Append(" and Id={" + bean.Id + "}");
}
if (bean.PowerpileId != null)
{
sql.Append(" and Id={" + bean.PowerpileId + "}");
}
if (!string.IsNullOrEmpty(bean.TaskId))
{
sql.Append(" and Id={" + bean.TaskId + "}");
}
if (bean.CreateDT != null)
{
sql.Append(" and CreateDT={" + bean.CreateDT + "}");
}
if (bean.UpdateDT != null)
{
sql.Append(" and UpdateDT={" + bean.UpdateDT + "}");
}
Log.Debug("SQL :" + sql);
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref count));
}
public override DataTable QueryByPage(MenuPower bean, int page, int rows, ref int count)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from t_menu_power where 1=1 ");
if (!string.IsNullOrEmpty(bean.Id))
{
sql.Append(" and Id={" + bean.Id + "}");
}
if (!string.IsNullOrEmpty(bean.MenuId))
{
sql.Append(" and MenuId={" + bean.MenuId + "}");
}
if (!string.IsNullOrEmpty(bean.PowerId))
{
sql.Append(" and PowerId={" + bean.PowerId + "}");
}
Log.Debug("SQL :" + sql);
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref count));
}
/// <summary>
///
/// </summary>
/// <param name="bean"></param>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="count"></param>
/// <returns></returns>
public override DataTable QueryByPage(SmsOutBox bean, int page, int rows, ref int count)
{
var getpage = new GetPage();
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(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref count));
}
public override DataTable QueryByPage(ChargPrice bean, int page, int rows, ref int count)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from sm_useroles where 1=1 ");
if (!string.IsNullOrEmpty(bean.Id))
{
sql.Append(" and Id={" + bean.Id + "}");
}
if (!string.IsNullOrEmpty(bean.Name))
{
sql.Append(" and Name={" + bean.Name + "}");
}
if (!string.IsNullOrEmpty(bean.PriceType))
{
sql.Append(" and PriceType={" + bean.PriceType + "}");
}
if (bean.Version != null)
{
sql.Append(" and Version={" + bean.Version + "}");
}
if (bean.BeginDT != null)
{
sql.Append(" and BeginDT={" + bean.BeginDT + "}");
}
if (bean.EndDT != null)
{
sql.Append(" and EndDT={" + bean.EndDT + "}");
}
if (bean.Price != null)
{
sql.Append(" and Price={" + bean.Price + "}");
}
if (bean.CreateDT != null)
{
sql.Append(" and CreateDT={" + bean.CreateDT + "}");
}
if (bean.UpdateDT != null)
{
sql.Append(" and UpdateDT={" + bean.UpdateDT + "}");
}
Log.Debug("SQL :" + sql);
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref count));
}
public override DataTable QueryByPage(Branch bean, int page, int rows, ref int count)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from dev_branch where 1=1 ");
if (bean.BranchNo != null)
{
sql.Append(" and BranchNo={" + bean.BranchNo + "}");
}
if (bean.ZhuanBh != null)
{
sql.Append(" and ZhuanBh={" + bean.ZhuanBh + "}");
}
if (!string.IsNullOrEmpty(bean.ChanQuanGx))
{
sql.Append(" and ChanQuanGx={" + bean.ChanQuanGx + "}");
}
if (!string.IsNullOrEmpty(bean.YunWeiDw))
{
sql.Append(" and YunWeiDw={" + bean.YunWeiDw + "}");
}
if (!string.IsNullOrEmpty(bean.ChangJia))
{
sql.Append(" and ChangJia={" + bean.ChangJia + "}");
}
if (!string.IsNullOrEmpty(bean.FenZhiXlx))
{
sql.Append(" and FenZhiXlx={" + bean.FenZhiXlx + "}");
}
if (bean.Createdt != null)
{
sql.Append(" and Createdt={" + bean.Createdt + "}");
}
if (bean.Updatedt != null)
{
sql.Append(" and Updatedt={" + bean.Updatedt + "}");
}
if (!string.IsNullOrEmpty(bean.ZhiChanBh))
{
sql.Append(" and ZhiChanBh={" + bean.ZhiChanBh + "}");
}
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref count));
}
/// <summary>
/// 结合码表统计计划任务
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="count"></param>
/// <param name="cmdtype"></param>
/// <returns></returns>
public DataTable FindBy(int page, int rows, ref int count, string cmdtype)
{
Log.Debug("FindBy方法参数：任务类型" + cmdtype);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select csj.id,csj.taskname,to_char(csj.runatdate,'yyyy-mm-dd HH24:mi:ss') runatdate,csj.remark,");
sql.Append("(select codename from sys_code sc where csj.taskstate=sc.code and sc.parentid in('CD2F3398-3EC7-401C-BF49-A706AE4529D8')) taskstate ");
sql.Append("from cmd_schedulejobs csj ");
sql.Append("where csj.cmdtype='" + cmdtype + "' ");
sql.Append("order by runatdate desc");
return(getpage.GetPageByProcedure(rows, page, sql.ToString(), ref count));
}
public override DataTable QueryByPage(Role bean, int page, int rows, ref int recordcount)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from sm_role where 1=1 ");
if (!string.IsNullOrEmpty(bean.RoleId))
{
sql.Append(" and RoleId={" + bean.RoleId + "}");
}
if (!string.IsNullOrEmpty(bean.AppId))
{
sql.Append(" and AppId={" + bean.AppId + "}");
}
if (!string.IsNullOrEmpty(bean.RoleName))
{
sql.Append(" and RoleName={" + bean.RoleName + "}");
}
if (!string.IsNullOrEmpty(bean.RoleDesc))
{
sql.Append(" and RoleDesc={" + bean.RoleDesc + "}");
}
if (bean.RoleNo != null)
{
sql.Append(" and RoleNo={" + bean.RoleNo + "}");
}
if (bean.StaGrade != null)
{
sql.Append(" and StaGrade={" + bean.StaGrade + "}");
}
if (bean.CreateDT != null)
{
sql.Append(" and CreateDT={" + bean.CreateDT + "}");
}
if (bean.UpdateDT != null)
{
sql.Append(" and UpdateDT={" + bean.UpdateDT + "}");
}
Log.Debug("SQL :" + sql);
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref recordcount));
}
public override DataTable QueryByPage(ScheduleLog bean, int page, int rows, ref int count)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from cmd_schedulelog where 1=1 ");
if (string.IsNullOrEmpty(bean.Id))
{
sql.Append(" and Id={" + bean.Id + "}");
}
if (!string.IsNullOrEmpty(bean.TaskId))
{
sql.Append(" and TaskId={" + bean.TaskId + "}");
}
if (bean.BeginDT != null)
{
sql.Append(" and BeginDT={" + bean.BeginDT + "}");
}
if (!string.IsNullOrEmpty(bean.Result))
{
sql.Append(" and Result={" + bean.Result + "}");
}
if (!string.IsNullOrEmpty(bean.Remark))
{
sql.Append(" and Remark={" + bean.Remark + "}");
}
if (bean.EndDT != null)
{
sql.Append(" and EndDT={" + bean.EndDT + "}");
}
if (bean.CreateDT != null)
{
sql.Append(" and CreateDT={" + bean.CreateDT + "}");
}
if (bean.UpdateDT != null)
{
sql.Append(" and UpdateDT={" + bean.UpdateDT + "}");
}
Log.Debug("SQL :" + sql);
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref count));
}
/// <summary>
/// 查询本月排行卡消费总额
/// </summary>
/// <returns></returns>
public DataTable FindByRankCardZje(string kssj, string jssj, int page, int rows, ref int count)
{
Log.Debug("FindByRankCardZje:方法参数：");
var sql = new StringBuilder();
var getpage = new GetPage();
sql.Append("select rownum,t.* from( ");
sql.Append("select gdr.card_no cardno,sum(gdr.charge_money)/100 zje ");
sql.Append("from gat_data_record gdr ");
sql.Append("where gdr.charge_money is not null and gdr.enddt between to_date('" + kssj + "','yyyy-mm-dd') and to_date('" + DateTime.Parse(jssj).AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ");
sql.Append("group by gdr.card_no ");
sql.Append("order by zje desc ");
sql.Append(")t ");
return(getpage.GetPageByProcedure(rows, page, sql.ToString(), ref count));
}
/// <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));
}
public override DataTable QueryByPage(ResMenu bean, int page, int rows, ref int count)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from sm_resmenu where 1=1 ");
if (!string.IsNullOrEmpty(bean.ResId))
{
sql.Append(" and ResId={" + bean.ResId + "}");
}
if (!string.IsNullOrEmpty(bean.ParentId))
{
sql.Append(" and ParentId={" + bean.ParentId + "}");
}
if (!string.IsNullOrEmpty(bean.Caption))
{
sql.Append(" and Caption={" + bean.Caption + "}");
}
if (!string.IsNullOrEmpty(bean.Href))
{
sql.Append(" and Href={" + bean.Href + "}");
}
if (bean.SortNo != null)
{
sql.Append(" and SortNo={" + bean.SortNo + "}");
}
if (bean.CreateDT != null)
{
sql.Append(" and CreateDT={" + bean.CreateDT + "}");
}
if (bean.UpdateDT != null)
{
sql.Append(" and UpdateDT={" + bean.UpdateDT + "}");
}
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref count));
}
/// <summary>
/// 查询本月排行充电量
/// </summary>
/// <returns></returns>
public DataTable FindByRankZdl(string kssj, string jssj, int page, int rows, ref int count)
{
Log.Debug("FindByRankZdl:方法参数：");
var sql = new StringBuilder();
var getpage = new GetPage();
sql.Append("select rownum,t.* from( ");
sql.Append("select dcs.zhan_jc zhanjc,sum(gdr.charge_power)zdl ");
sql.Append("from gat_data_record gdr ");
sql.Append("right join dev_chargpile dcp on dcp.dev_chargpile=gdr.zhuan_id ");
sql.Append("right join dev_branch db on db.branchno=dcp.box_id ");
sql.Append("right join dev_chargstation dcs on dcs.zhan_bh=db.zhuan_bh ");
sql.Append("where dcp.deleteflag is null and gdr.charge_money is not null and gdr.enddt between to_date('" + kssj + "','yyyy-mm-dd') and to_date('" + DateTime.Parse(jssj).AddDays(1).ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ");
sql.Append("group by dcs.zhan_jc ");
sql.Append("order by zdl desc nulls last ");
sql.Append(") t ");
return(getpage.GetPageByProcedure(rows, page, sql.ToString(), ref count));
}
public override DataTable QueryByPage(UseRoles bean, int page, int rows, ref int recordcount)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from sm_useroles where 1=1 ");
if (!string.IsNullOrEmpty(bean.RoleId))
{
sql.Append(" and RoleId={" + bean.RoleId + "}");
}
if (!string.IsNullOrEmpty(bean.LoginName))
{
sql.Append(" and LoginName={" + bean.LoginName + "}");
}
Log.Debug("SQL :" + sql);
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref recordcount));
}
public override DataTable QueryByPage(Warn bean, int page, int rows, ref int count)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from gat_warn where 1=1 ");
if (!string.IsNullOrEmpty(bean.Id))
{
sql.Append(" and Id={" + bean.Id + "}");
}
if (!string.IsNullOrEmpty(bean.WarnTarget))
{
sql.Append(" and WarnTarget={" + bean.WarnTarget + "}");
}
if (!string.IsNullOrEmpty(bean.WarnType))
{
sql.Append(" and WarnType={" + bean.WarnType + "}");
}
if (!string.IsNullOrEmpty(bean.WarnContext))
{
sql.Append(" and WarnContext={" + bean.WarnContext + "}");
}
if (bean.CreateDT != null)
{
sql.Append(" and CreateDT={" + bean.CreateDT + "}");
}
if (bean.UpdateDT != null)
{
sql.Append(" and UpdateDT={" + bean.UpdateDT + "}");
}
Log.Debug("SQL :" + sql);
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref count));
}
public override DataTable QueryByPage(ChargStation bean, int page, int rows, ref int count)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from sm_useroles where 1=1 ");
if (bean.ZhanBh != null)
{
sql.Append(" and ZhanBh={" + bean.ZhanBh + "}");
}
if (!string.IsNullOrEmpty(bean.ZhuanMc))
{
sql.Append(" and ZhuanMc={" + bean.ZhuanMc + "}");
}
if (!string.IsNullOrEmpty(bean.YeZhuDw))
{
sql.Append(" and YeZhuDw={" + bean.YeZhuDw + "}");
}
if (!string.IsNullOrEmpty(bean.LianXiR))
{
sql.Append(" and LianXiR={" + bean.LianXiR + "}");
}
if (!string.IsNullOrEmpty(bean.LianXiDh))
{
sql.Append(" and LianXiDh={" + bean.LianXiDh + "}");
}
if (!string.IsNullOrEmpty(bean.ZhuangLeiX))
{
sql.Append(" and ZhuangLeiX={" + bean.ZhuangLeiX + "}");
}
if (!string.IsNullOrEmpty(bean.ZhuangChangJ))
{
sql.Append(" and ZhuangChangJ={" + bean.ZhuangChangJ + "}");
}
if (!string.IsNullOrEmpty(bean.XiangXiDz))
{
sql.Append(" and XiangXiDz={" + bean.XiangXiDz + "}");
}
if (bean.Longtude != null)
{
sql.Append(" and Longtude={" + bean.Longtude + "}");
}
if (bean.Latitude != null)
{
sql.Append(" and Latitude={" + bean.Latitude + "}");
}
if (bean.CreateDT != null)
{
sql.Append(" and CreateDT={" + bean.CreateDT + "}");
}
if (bean.TouYun_Sj != null)
{
sql.Append(" and UpdateDT={" + bean.TouYun_Sj + "}");
}
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref count));
}
/// <summary>
/// 分页查询计划任务
/// </summary>
/// <param name="bean"></param>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="count"></param>
/// <returns></returns>
public override DataTable QueryByPage(ScheduleJobs bean, int page, int rows, ref int count)
{
Log.Debug("QueryByPage方法参数：" + bean);
var getpage = new GetPage();
var sql = new StringBuilder();
sql.Append("select * from cmd_schedulejobs where 1=1 ");
if (!string.IsNullOrEmpty(bean.Id))
{
sql.Append(" and Id={" + bean.Id + "}");
}
if (!string.IsNullOrEmpty(bean.TaskName))
{
sql.Append(" and TaskName={" + bean.TaskName + "}");
}
if (bean.PowerpileId != null)
{
sql.Append(" and PowerpileId={" + bean.PowerpileId + "}");
}
if (!string.IsNullOrEmpty(bean.Interval))
{
sql.Append(" and Interval={" + bean.Interval + "}");
}
if (!string.IsNullOrEmpty(bean.CmdType))
{
sql.Append(" and CmdType={" + bean.CmdType + "}");
}
if (!string.IsNullOrEmpty(bean.TaskState))
{
sql.Append(" and TaskState={" + bean.TaskState + "}");
}
if (bean.JobMonth != null)
{
sql.Append(" and JobMonth={" + bean.JobMonth + "}");
}
if (bean.JobWeek != null)
{
sql.Append(" and JobWeek={" + bean.JobWeek + "}");
}
if (bean.JobDay != null)
{
sql.Append(" and JobDay={" + bean.JobDay + "}");
}
if (bean.JobHour != null)
{
sql.Append(" and JobHour={" + bean.JobHour + "}");
}
if (bean.JobMinute != null)
{
sql.Append(" and JobMinute={" + bean.JobMinute + "}");
}
if (bean.JobSecond != null)
{
sql.Append(" and JobSecond={" + bean.JobSecond + "}");
}
if (bean.RunatDate != null)
{
sql.Append(" and RunatDate={" + bean.RunatDate + "}");
}
if (!string.IsNullOrEmpty(bean.Remark))
{
sql.Append(" and Remark={" + bean.Remark + "}");
}
if (!string.IsNullOrEmpty(bean.RefId))
{
sql.Append(" and RefId={" + bean.RefId + "}");
}
if (!string.IsNullOrEmpty(bean.Refenity))
{
sql.Append(" and Refenity={" + bean.Refenity + "}");
}
if (bean.CreateDT != null)
{
sql.Append(" and CreateDT={" + bean.CreateDT + "}");
}
if (bean.UpdateDT != null)
{
sql.Append(" and UpdateDT={" + bean.UpdateDT + "}");
}
Log.Debug("SQL :" + sql);
return(getpage.GetPageByProcedure(page, rows, sql.ToString(), ref count));
}