/// <summary> /// 获取首页缴费 /// </summary> /// <param name="StartTime">开始时间</param> /// <param name="EndTime">结束时间</param> /// <returns></returns> public DataTable GetHomePay(string StartTime, string EndTime) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Project, this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Clear(); strSql.Append("select a.Log_id,a.CDate,a.Co_id,a.Module_id,a.ModuleAddr,c.ModuleName,b.CoStrcName,b.CoName,b.CoFullName,"); strSql.Append("a.PayAmt,a.PayVal,a.PayType,a.IsWrong,a.IsPay,a.Trade_no,case when a.IsPay=1 then s1.CrmName else s2.UName end as Create_by,"); strSql.Append("a.Create_dt,a.ErrCode,a.EndTime,a.ErrTxt"); strSql.Append(" from v4_pay_log as a"); if (string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" left join vp_mdinfo as c on a.Ledger=c.Ledger and a.Module_id=c.Module_id and a.ModuleAddr=c.ModuleAddr"); } else { strSql.Append(" inner join vp_mdinfo as c on a.Ledger=c.Ledger and a.Module_id=c.Module_id and a.ModuleAddr=c.ModuleAddr and FIND_IN_SET(c.IsDefine,@MdItems)"); } strSql.Append(" left join vp_coinfo as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id"); strSql.Append(" left join v3_user as s1 on a.Ledger=s1.Ledger and a.Create_by=s1.Crm_id"); strSql.Append(" left join sys_user as s2 on a.Ledger=s2.Ledger and a.Create_by=s2.Uid"); strSql.Append(" where a.Ledger=@Ledger and a.CDate>=@Start and a.CDate<=@End and a.ErrCode=1"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" and a.PayType=1");/*充值*/ strSql.Append(" order by a.Log_id desc"); return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, Start = StartTime, End = EndTime, MdItems = WHoleDAL.MdItems })); }
/// <summary> /// 获取参数设置列表 /// </summary> /// <param name="coName"></param> /// <param name="moduleAddr"></param> /// <returns></returns> public DataTable GetYdParamsOfList(string CoStrcName, string CoName) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); object params01 = new { Ledger = this.Ledger, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", AreaPowerStr = AreaPowerStr }; StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Module_id,a.ModuleName,a.ModuleAddr,a.Fun_id,a.FunType,a.FunName,b.DataValue,b.`Status`,a.Co_id,c1.CoStrcName,c1.CoName"); strSql.Append(" from vp_funinfo as a "); strSql.Append(" inner join syscont as s1 on a.Ledger=s1.Ledger"); strSql.Append(" inner join vp_mdinfo as m1 on a.Ledger=m1.Ledger and a.Module_id=m1.Module_id"); strSql.Append(" left join v1_map as b on a.Ledger=b.Ledger and a.Module_id=b.Module_id and a.Fun_id=b.Fun_id"); strSql.Append(" left join vp_coinfo as c1 on a.Ledger=c1.Ledger and a.Co_id=c1.Co_id"); strSql.Append(" where a.Ledger=@Ledger and a.Action=1"); strSql.Append(" and m1.ModuleAddr=case when s1.Zb=0 then m1.MeterAddr else m1.ModuleAddr end"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" and ifnull(c1.CoStrcName,'') like @CoStrcName"); strSql.Append(" and ifnull(c1.CoName,'') like @CoName "); return(SQLHelper.Query(strSql.ToString(), params01)); }
public DataTable GetTemperatureAndHumidity(string CoStrcName, string CoName) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); object params01 = new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", }; StringBuilder strSql = new StringBuilder(); strSql.Append("select c.Co_id,c.CoName,c.CoFullName,c.CoStrcName,a.ModuleAddr,a.Module_id,CONCAT(a.Module_id,'.',a.ModuleAddr,'.')as LpszDbVar,"); strSql.Append("max(case when b.FunType like 'Leak%' then b.Scale else 0 end)as LeakScale,"); strSql.Append("max(case when b.FunType like 'Temp%' then b.Scale else 0 end)as TempScale"); strSql.Append(" from vp_mdinfo a INNER JOIN v0_fun b on a.Ledger=b.Ledger and a.Mm_id=b.Mm_id and (b.FunType like 'Leak%' or b.FunType like 'Temp%')"); strSql.Append(" left JOIN vp_coinfo c on a.Ledger=c.Ledger and a.Co_id=c.Co_id"); strSql.Append(" where a.Ledger=@Ledger and a.ModuleType='YDH20D'"); strSql.Append(" and ifnull(c.CoStrcName,'') like @CoStrcName "); strSql.Append(" and ifnull(c.CoName,'') like @CoName "); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" group by a.Module_id"); return(SQLHelper.Query(strSql.ToString(), params01)); }
public DataTable GetYdMontionOnList(string CoStrcName, string CoName) { if (string.IsNullOrEmpty(CoStrcName) || CoStrcName == "{StrcName}" || CoStrcName == "null") { CoStrcName = string.Empty; } if (string.IsNullOrEmpty(CoName) || CoName == "{CoName}" || CoName == "null") { CoName = string.Empty; } string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Clear(); /*先找基站*/ strSql.Append("select a.Module_id,a.ModuleAddr,a.ModuleName,a.Parent_id,a.Co_id,a.Multiply,a.FullStruc,vf.Fun_id,c1.CoName,c1.CoStrcName,vf.Fun_id,vf.FunName,vf.FunType,vf.Scale,vf.OrdNo"); strSql.Append(" from vp_mdinfo as a inner join vp_coinfo as c1 on a.Ledger=c1.Ledger and a.Co_id=c1.Co_id"); strSql.Append(" inner join v0_fun as vf on a.Ledger=vf.Ledger and a.Mm_id=vf.Mm_id and vf.FunType='E'"); strSql.Append(" where a.Ledger=@Ledger and not FIND_IN_SET(a.ModuleType,@SplitException)"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" and ifnull(vf.Disabled,0)=0 and ifnull(c1.CoStrcName,'') like @CoStrcName "); strSql.Append(" and ifnull(c1.CoName,'') like @CoName "); if (!string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" and FIND_IN_SET(a.IsDefine,@MdItems)"); } strSql.Append(" order by a.Module_id,vf.OrdNo,vf.Fun_id"); return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", SplitException = WHoleDAL.splitException, MdItems = WHoleDAL.MdItems })); }
public DataTable GetYdAlarmOfCmdList(string CoStrcName, string CoName, DateTime Start, DateTime End) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); object params01 = new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", Start = Start.ToString("yyyy-MM-dd"), End = End.ToString("yyyy-MM-dd"), MdItems = WHoleDAL.MdItems, }; StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Log_id,a.Co_id,a.Module_id,a.ModuleAddr,b.ModuleName,a.Fun_id,a.FunType,a.DataValue,"); strSql.Append("case when ifnull(a.ErrCode,0)=0 then a.Create_dt else a.EndTime end as Create_dt,a.ErrCode,a.ErrTxt,b.HandledBY,c.CoStrcName,c.CoName,s2.UName as Create_by"); strSql.Append(" from v2_command as a inner join vp_mdinfo as b on a.Ledger=b.Ledger and a.Module_id=b.Module_id"); strSql.Append(" left join vp_coinfo as c on a.Ledger=c.Ledger and a.Co_id=c.Co_id"); strSql.Append(" left join sys_user as s2 on a.Ledger=s2.Ledger and a.Create_by=s2.Uid"); strSql.Append(" where a.Ledger=@Ledger and a.CDate>=@Start and a.CDate<=@End and a.ErrCode<=0"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" and ifnull(c.CoStrcName,'') like @CoStrcName "); strSql.Append(" and ifnull(c.CoName,'') like @CoName "); if (!string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" and FIND_IN_SET(b.IsDefine,@MdItems)"); } strSql.Append(" order by a.Log_id desc"); return(SQLHelper.Query(strSql.ToString(), params01)); }
public DataTable GetModuleListByOrg(string CoStrcName, string CoName, params string[] funTypeParams) { if (string.IsNullOrEmpty(CoStrcName) || CoStrcName == "{StrcName}" || CoStrcName == "null") { CoStrcName = string.Empty; } if (string.IsNullOrEmpty(CoName) || CoName == "{CoName}" || CoName == "null") { CoName = string.Empty; } string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Clear(); strSql.Append("select c.Co_id,c.CoName,c.CoFullName,c.CoStrcName,a.ModuleAddr,a.ModuleName,a.Module_id,a.Multiply,b.Fun_id,b.FunType"); strSql.Append(" from vp_mdinfo a INNER JOIN v0_fun b on a.Ledger=b.Ledger and a.Mm_id=b.Mm_id and a.MdDisabled=0 and b.Disabled=0"); strSql.Append(" INNER JOIN vp_coinfo c on a.Ledger=c.Ledger and a.Co_id=c.Co_id and c.Disabled=0"); strSql.Append(" where a.Ledger=@Ledger and not FIND_IN_SET(a.ModuleType,@SplitException)"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } if (funTypeParams != null && funTypeParams.Length > 0) { strSql.Append(" and ( "); for (int i = 0; i < funTypeParams.Length; i++) { if (i > 0) { strSql.Append(" or "); } strSql.Append(" b.FunType='" + funTypeParams[i] + "'"); } strSql.Append(" ) "); } if (!string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" and FIND_IN_SET(a.IsDefine,@MdItems)"); } if (!string.IsNullOrEmpty(CoStrcName)) { strSql.Append(" and ifnull(c.CoStrcName,'') like @CoStrcName "); } if (!string.IsNullOrEmpty(CoName)) { strSql.Append(" and ifnull(c.CoName,'') like @CoName "); } strSql.Append(" GROUP BY c.Co_id,c.CoName,c.CoFullName,c.CoStrcName,a.ModuleAddr,a.Module_id,b.FunType "); strSql.Append(" order by a.Module_id,b.Fun_id"); return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", SplitException = WHoleDAL.splitException, MdItems = WHoleDAL.MdItems })); }
public DataTable GetViewCellCombox(int parent_id) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Co_id,a.CoName from vp_coinfo as a"); strSql.Append(" where a.Ledger=@Ledger and a.Parent_id=@Parent_id"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, Parent_id = parent_id, AreaPowerStr = AreaPowerStr })); }
/// <summary> /// 月用电量 /// </summary> /// <param name="Co_id"></param> /// <param name="Module_id"></param> /// <param name="ModuleAddr"></param> /// <returns></returns> public decimal GetYdMonitorOnMthUseVal(int Co_id, int Module_id, string ModuleAddr) { DateTime TagTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); decimal useVal = 0; DataTable dtUse = WHoleDAL.GetCoreQueryData(this.Ledger, Module_id.ToString(), TagTime, TagTime, "month", "E"); foreach (DataRow dr in dtUse.Rows) { if (CommFunc.ConvertDBNullToInt32(dr["Co_id"]) == Co_id) { useVal = useVal + ((CommFunc.ConvertDBNullToDecimal(dr["LastVal"]) - CommFunc.ConvertDBNullToDecimal(dr["FirstVal"])) * CommFunc.ConvertDBNullToDecimal(dr["Multiply"])); } } return(useVal); }
/// <summary> /// 获取电气或者列表 /// </summary> /// <returns></returns> public DataTable GetDqhzList() { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Module_id,a.Meter_id,a.ModuleName,a.MeterName,b.Fun_id,b.FunType,b.FunName,b.Scale,b.LpszDbVarName"); strSql.Append(" from vp_mdinfo as a inner join vp_funinfo as b on a.Ledger = b.Ledger and a.Module_id = b.Module_id"); strSql.Append(" where a.Ledger=@Ledger and a.IsDefine = 200"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, AreaPowerStr })); }
public DataTable GetViewList(int co_id) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Module_id,a.ModuleAddr,a.ModuleName,a.Fun_id,a.FunName,a.LpszDbVarName as TagName,a.Co_id,a.Inst_loc,b.CoName,b.Path from vp_funinfo as a"); strSql.Append(" left join vp_coinfo as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id"); strSql.Append(" where a.Ledger=@Ledger and a.Co_id=@Co_id and a.Action=0 and ifnull(a.TagName,'')!=''"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, Co_id = co_id, AreaPowerStr = AreaPowerStr })); }
/// <summary> /// 工单列表 /// </summary> /// <returns></returns> public DataTable GetSolutionList(string CoStrcName, string CoName, int status, DateTime Start, DateTime End) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); object params01 = new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", Start = Start.ToString("yyyy-MM-dd"), End = End.ToString("yyyy-MM-dd"), Status = status, SysUid = this.SysUid, }; StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Log_id,a.Src_id,a.SolutionNo,a.Status,b.Create_dt,a.SolutionTime,b.Co_id,b.Module_id,b.ModuleAddr,b.Fun_id,b.FunType,b.FunName,a.SolutionImage,a.Content,b.CollectValue,b.CollectTime,b.Appr_dt,"); strSql.Append("b.Create_dt as AlarmTime,c1.CoStrcName,c1.CoName,m1.ModuleName,u1.UName as SolutionName,a.SolutionImage"); strSql.Append(" from v2_solution_log as a inner join v2_alarm_log as b on a.Src_id=b.Log_id"); strSql.Append(" inner JOIN vp_coinfo as c1 on b.Ledger=c1.Ledger and b.Co_id=c1.Co_id"); strSql.Append(" inner join vp_mdinfo as m1 on b.Ledger=m1.Ledger and b.Module_id=m1.Module_id and b.ModuleAddr=m1.ModuleAddr and b.Co_id=m1.Co_id"); strSql.Append(" left join vp_userinfo as u1 on a.Ledger=u1.Ledger and a.SolutionBy=u1.Uid"); strSql.Append(" where b.Ledger=@Ledger"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(b.Co_id,@AreaPowerStr)"); } if (!"{strcName}".Equals(CoStrcName)) { strSql.Append(" and ifnull(c1.CoStrcName,'') like @CoStrcName "); } if (!"{coName}".Equals(CoName)) { strSql.Append(" and ifnull(c1.CoName,'') like @CoName "); } if (status >= 0) { strSql.Append(" and a.Status=@Status"); } if (SysUid != 1) //管理员可以查看全部 { strSql.Append(" and a.SolutionBy=@SysUid"); } strSql.Append(" ORDER BY a.Log_id DESC"); return(SQLHelper.Query(strSql.ToString(), params01)); }
public DataTable GetAlarmCompare() { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Log_id,a.CDate,a.Module_id,a.Content"); strSql.Append(" from v2_alarm_log as a "); strSql.Append(" inner join vp_funinfo as b on a.Ledger=b.Ledger and a.Module_id=b.Module_id and a.Fun_id=b.Fun_id"); strSql.Append(" where a.CDate>=@CDate and a.Ledger=@Ledger"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, CDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1), AreaPowerStr = AreaPowerStr })); }
public DataTable GetYdAlarmOfChargeList(string CoStrcName, string CoName, string IsPay, DateTime Start, DateTime End) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); object params01 = new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", IsPay = IsPay + "%", Start = Start.ToString("yyyy-MM-dd"), End = End.ToString("yyyy-MM-dd"), MdItems = WHoleDAL.MdItems, }; StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Log_id,a.CDate,a.Co_id,a.Module_id,a.ModuleAddr,c.ModuleName,b.CoStrcName,b.CoName,b.CoFullName,"); strSql.Append("a.PayAmt,a.PayVal,a.PayType,a.IsWrong,a.IsPay,a.Trade_no,case when a.IsPay=1 then s1.CrmName else s2.UName end as Create_by,"); strSql.Append("a.Create_dt,a.ErrCode,a.EndTime,a.ErrTxt"); strSql.Append(" from v4_pay_log as a"); if (string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" left join vp_mdinfo as c on a.Ledger=c.Ledger and a.Module_id=c.Module_id and a.ModuleAddr=c.ModuleAddr"); } else { strSql.Append(" inner join vp_mdinfo as c on a.Ledger=c.Ledger and a.Module_id=c.Module_id and a.ModuleAddr=c.ModuleAddr and FIND_IN_SET(c.IsDefine,@MdItems)"); } strSql.Append(" left join vp_coinfo as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id"); strSql.Append(" left join v3_user as s1 on a.Ledger=s1.Ledger and a.Create_by=s1.Crm_id"); strSql.Append(" left join sys_user as s2 on a.Ledger=s2.Ledger and a.Create_by=s2.Uid"); strSql.Append(" where a.Ledger=@Ledger and a.CDate>=@Start and a.CDate<=@End and a.ErrCode!=1"); strSql.Append(" and ifnull(b.CoStrcName,'') like @CoStrcName "); strSql.Append(" and ifnull(b.CoName,'') like @CoName "); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } if (!string.IsNullOrEmpty(IsPay)) { strSql.Append(" and ifnull(a.IsPay,0) like @IsPay");/*现金支付*/ } strSql.Append(" order by a.Log_id desc"); return(SQLHelper.Query(strSql.ToString(), params01)); }
public DataTable GetViewBuildCombox() { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); //strSql.Append("select b.Co_id,b.CoName from vp_coinfo as a inner join v1_cust as b on a.Ledger=b.Ledger and a.Parent_id=b.Co_id"); //strSql.Append(" where a.Ledger=@Ledger and a.Attrib=@Attrib and a.IsDefine=1"); //strSql.Append(" group by b.Co_id"); strSql.Append("select a.Co_id,a.CoName from vp_coinfo as a"); strSql.Append(" where a.Ledger=@Ledger and a.Attrib=@Attrib"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, Attrib = CoAttribV2_1.Project, AreaPowerStr = AreaPowerStr })); }
/// <summary> /// 获取最新一条告警 /// </summary> /// <returns></returns> public DataTable GetAlarmFisrt() { //1.U 越高限,地点:8101房间10N表,当前值:230V string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Log_id,a.Content,a.CollectValue,b.MeterName from v2_alarm_log as a left join vp_mdinfo as b on a.Ledger=b.Ledger and a.Module_id=b.Module_id"); strSql.Append(" where a.Ledger=@Ledger "); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" order by a.Log_id desc limit 1"); return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr })); // object obj = SQLHelper.ExecuteScalar(strSql.ToString(), new { Ledger = this.Ledger }); //return CommFunc.ConvertDBNullToString(obj); }
/// <summary> /// 获取全部客户信息 /// </summary> /// <returns></returns> public DataTable GetYdCustOnCoInfoList() { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Co_id,a.CoNo,a.CoName,a.Disabled,a.Parent_id,a.Attrib,"); strSql.Append("b.CustAddr,b.Office_tel,b.Mobile,b.Email,b.IsDefine,m1.CoName as PCoName"); strSql.Append(" from v1_cust as a left join v1_custinfo as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id"); strSql.Append(" left join v1_cust as m1 on a.Ledger=m1.Ledger and a.Parent_id=m1.Co_id"); strSql.Append(" where a.Ledger=@Ledger"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, AreaPowerStr })); }
public DataTable GetIdxList(DateTime startTime) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Co_id,a.CoName as CellName,b.CoName as ProjectName,c.CoName as UnitName,d.CoName as AreaName,ix.Idx"); strSql.Append(" from v1_cust as a left join v1_cust as b on a.Ledger=b.Ledger and a.Parent_id=b.Co_id"); strSql.Append(" left join v1_cust as c on b.Ledger=c.Ledger and b.Parent_id=c.Co_id"); strSql.Append(" left join v1_cust as d on c.Ledger=d.Ledger and c.Parent_id=d.Co_id"); strSql.Append(" left join v1_cust_idx as ix on a.Ledger=ix.Ledger and a.Co_id=ix.Co_id and ix.Month=@Month"); strSql.Append(" where a.Ledger=@Ledger and a.Attrib=@Attrib"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" order by ix.Idx,a.Co_id"); return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, Attrib = (int)CoAttribV2_1.Cell, Month = new DateTime(startTime.Year, startTime.Month, 1), AreaPowerStr = AreaPowerStr })); }
/// <summary> /// 隐患列表 /// </summary> /// <returns></returns> public DataTable GetYdAlarmList(string CoStrcName, string CoName, int status, DateTime Start, DateTime End) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); object params01 = new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", Start = Start.ToString("yyyy-MM-dd"), End = End.ToString("yyyy-MM-dd"), Status = status, }; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT a.Log_id,a.Co_id,a.Module_id,a.ModuleAddr,a.Fun_id,a.FunType,a.FunName,a.Content,a.CollectTime,a.CollectValue,a.Create_dt,a.Status,"); strSql.Append("b.CoStrcName,b.CoName,m1.ModuleName,a.Appr_by,vf.FunName as CollectName"); strSql.Append(" FROM v2_alarm_log as a"); strSql.Append(" inner JOIN vp_coinfo as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id"); strSql.Append(" inner join vp_mdinfo as m1 on a.Ledger=m1.Ledger and a.Module_id=m1.Module_id and a.ModuleAddr=m1.ModuleAddr and a.Co_id=m1.Co_id"); strSql.Append(" left join v0_fun as vf on a.Ledger=vf.Ledger and a.Fun_id=vf.Fun_id"); strSql.Append(" where a.Ledger=@Ledger and a.CDate>=@Start and a.CDate<=@End"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } if (!string.IsNullOrEmpty(CoStrcName)) { strSql.Append(" and ifnull(b.CoStrcName,'') like @CoStrcName "); } if (!string.IsNullOrEmpty(CoName)) { strSql.Append(" and ifnull(b.CoName,'') like @CoName "); } if (status >= 0) { strSql.Append(" and a.Status=@Status"); } strSql.Append(" ORDER BY a.Log_id DESC"); return(SQLHelper.Query(strSql.ToString(), params01)); }
/// <summary> /// 获取用电单元信息 /// </summary> /// <param name="attrib"></param> /// <returns></returns> public DataTable GetVp_coinfo(CoAttribV2_1 attrib) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Co_id,a.CoNo,a.CoName,a.CustAddr,a.Mobile,a.Parent_id,a.Remark,a.CoStrcName,a.Disabled,b.CoName as ParentName"); strSql.Append(" from vp_coinfo as a"); strSql.Append(" left join v1_cust as b on a.Ledger=b.Ledger and a.Parent_id=b.Co_id"); strSql.Append(" where a.Ledger=@Ledger and a.Attrib=@Attrib"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } else { strSql.Append(" and FIND_IN_SET(a.Co_id,'-1')");//给-1:是无效值 为false时无权限 } return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, Attrib = (int)attrib, AreaPowerStr = AreaPowerStr, SysUid = this.SysUid })); }
public DataTable GetYdAlarmOfValList(string CoStrcName, string CoName, decimal AlarmVal) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); object params01 = new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", AlarmVal = AlarmVal, MdItems = WHoleDAL.MdItems, }; StringBuilder strSql = new StringBuilder(); strSql.Append("select a.Co_id,a.PayVal,a.UseVal,a.ShareVal,a.DebtTime,s1.ChrgType,m1.Module_id,m1.ModuleAddr,m1.ModuleName,b.CoStrcName,b.CoName,"); strSql.Append("case when s1.ChrgType<=1 then a.SyVal else ifnull(c.RdVal,0)*m1.Multiply end as SyVal"); strSql.Append(" from vp_cobill as a inner join syscont as s1 on a.Ledger=s1.Ledger"); strSql.Append(" inner join vp_coinfo as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id"); strSql.Append(" inner join v0_fun as vf on a.Ledger = vf.Ledger and a.Fun_id = vf.Fun_id and vf.FunType='E'"); strSql.Append(" left join vp_mdinfo as m1 on a.Ledger=m1.Ledger and a.Co_id=m1.Co_id and vf.Mm_id=m1.Mm_id and m1.Module_id=case when s1.ChrgType<=1 then -9991 else m1.Module_id END"); strSql.Append(" left join vp_v2info as c on a.Ledger=c.Ledger and a.Co_id=c.Co_id and a.Fun_id=c.Fun_id and m1.Module_id=c.Module_id and m1.ModuleAddr=c.ModuleAddr and m1.Mm_id=c.Mm_id"); strSql.Append(" left join v1_gateway_esp_module_info as d on a.Ledger=d.Ledger and m1.Module_id=d.Module_id"); strSql.Append(" where a.Ledger=@Ledger"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" and ifnull(b.CoStrcName,'') like @CoStrcName"); strSql.Append(" and ifnull(b.CoName,'') like @CoName"); //strSql.Append(" and case when s1.ChrgType<=1 then a.SyVal else ifnull(c.RdVal,0) end<= @AlarmVal"); strSql.Append(" and case when s1.ChrgType<=1 then a.SyVal else ifnull(c.RdVal,0)*m1.Multiply end <= case when s1.ChrgType<=1 or ifnull(d.AlarmVal,0)=0 then @AlarmVal else d.AlarmVal end"); strSql.Append(" and m1.ModuleAddr=case when s1.Zb=0 then m1.MeterAddr else m1.ModuleAddr end"); if (!string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" and exists(select * from vp_mdinfo as md where md.Ledger=a.Ledger and md.Co_id=a.Co_id and FIND_IN_SET(m1.IsDefine,@MdItems))"); } return(SQLHelper.Query(strSql.ToString(), params01)); }
/// <summary> /// 采集异常告警 /// </summary> /// <param name="CoStrcName"></param> /// <param name="CoName"></param> /// <param name="Start"></param> /// <param name="End"></param> /// <returns></returns> public DataTable GetYdAlarmOfUnusualList(string CoStrcName, string CoName, DateTime Start, DateTime End) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); object params01 = new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", Start = Start.ToString("yyyy-MM-dd"), End = End.ToString("yyyy-MM-dd"), }; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT a.Log_id,b.CoStrcName,b.CoName,a.ModuleAddr,d.ModuleName,a.AType,a.Content,a.CollectTime,"); strSql.Append("CASE WHEN a.ErrCode = '1' THEN '是' else '否' END as ErrCode,c.Co_id,c.Module_id,c.Fun_id,c.FirstVal,c.LastVal,c.LastTime"); strSql.Append(" FROM v2_alarm_log as a"); strSql.Append(" inner JOIN vp_coinfo as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id"); strSql.Append(" inner JOIN v2_info as c on a.Ledger=c.Ledger and a.Co_id=c.Co_id and a.Module_id=c.Module_id and a.ModuleAddr=c.ModuleAddr and a.Fun_id=c.Fun_id"); strSql.Append(" inner join vp_mdinfo as d on a.Ledger=d.Ledger and a.Module_id=d.Module_id and a.ModuleAddr=d.ModuleAddr and a.Co_id=d.Co_id"); strSql.Append(" where a.Ledger=@Ledger and a.CDate>=@Start and a.CDate<=@End and a.AType like 'Ez_%'"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } if (!string.IsNullOrEmpty(CoStrcName)) { strSql.Append(" and ifnull(b.CoStrcName,'') like @CoStrcName "); } if (!string.IsNullOrEmpty(CoName)) { strSql.Append(" and ifnull(b.CoName,'') like @CoName "); } strSql.Append(" GROUP BY a.Log_id ORDER BY a.Log_id DESC"); return(SQLHelper.Query(strSql.ToString(), params01)); }
public DataTable GetYdAlarmOfLoadList(string CoStrcName, string CoName, DateTime Start, DateTime End) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); object params01 = new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", Start = Start.ToString("yyyy-MM-dd"), End = End.ToString("yyyy-MM-dd"), MdItems = WHoleDAL.MdItems, }; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT a.Log_id,a.CDate,b.CoFullName,b.CoStrcName,b.CoName,a.ModuleAddr,c.ModuleName,a.AType,a.Content,a.CTime,a.CollectTime"); strSql.Append(" FROM v2_alarm_log as a"); strSql.Append(" left JOIN vp_coinfo as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id "); strSql.Append(" left join vp_mdinfo as c on a.Ledger=c.Ledger and a.Module_id=c.Module_id"); strSql.Append(" where a.Ledger=@Ledger and a.CDate>=@Start and a.CDate<=@End and a.AType = 'EventWpf' and a.ErrCode=1 and Content!=''"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } if (!string.IsNullOrEmpty(CoStrcName)) { strSql.Append(" and ifnull(b.CoStrcName,'') like @CoStrcName "); } if (!string.IsNullOrEmpty(CoName)) { strSql.Append(" and ifnull(b.CoName,'') like @CoName "); } strSql.Append(" ORDER BY a.Log_id DESC"); return(SQLHelper.Query(strSql.ToString(), params01)); }
public DataTable GetYdAlarmOfGwList(string CoStrcName, string CoName, string AType, DateTime Start, DateTime End) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); object params01 = new { Ledger = this.Ledger, AType = AType, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", Start = Start.ToString("yyyy-MM-dd"), End = End.ToString("yyyy-MM-dd"), MdItems = WHoleDAL.MdItems, }; StringBuilder strSql = new StringBuilder(); strSql.Append("select a.AType,a.Co_id,a.Module_id,a.ModuleAddr,b.ModuleName,a.Fun_id,cast(CONCAT(a.CDate,' ',a.CTime) as char(30))as Create_dt,a.CollectTime,a.Content,a.ErrCode,a.ErrTxt,b.HandledBY,f1.FunType,f1.FunName,c.CoStrcName,c.CoName"); strSql.Append(" from v2_alarm_log as a inner join vp_mdinfo as b on a.Ledger=b.Ledger and a.Module_id=b.Module_id"); strSql.Append(" left join v0_fun as f1 on a.Ledger=f1.Ledger and a.Fun_id=f1.Fun_id"); strSql.Append(" left join vp_coinfo as c on a.Ledger=c.Ledger and a.Co_id=c.Co_id"); strSql.Append(" where a.Ledger=@Ledger and a.CDate>=@Start and a.CDate<=@End and a.AType=@AType"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" and ifnull(c.CoStrcName,'') like @CoStrcName "); strSql.Append(" and ifnull(c.CoName,'') like @CoName "); if (!string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" and FIND_IN_SET(b.IsDefine,@MdItems)"); } strSql.Append(" order by a.Log_id desc"); return(SQLHelper.Query(strSql.ToString(), params01)); }
public DataTable GetYdCollectData(string coStrcName, string coName, string funType, int isDefine) { if (string.IsNullOrEmpty(coStrcName) || coStrcName == "{StrcName}" || coStrcName == "null") { coStrcName = string.Empty; } if (string.IsNullOrEmpty(coName) || coName == "{CoName}" || coName == "null") { coName = string.Empty; } string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Clear(); strSql.Append("select a.Module_id,a.ModuleAddr,a.ModuleName,a.Multiply,a.Co_id,b.Fun_id,b.Scale,b.FunType,c.CoName,c.CoFullName,c.CoStrcName"); strSql.Append(" from vp_mdinfo a inner join vp_funinfo b on a.Ledger=b.Ledger and a.Module_id=b.Module_id"); strSql.Append(" left join vp_coinfo c on a.Ledger=c.Ledger and a.Co_id=c.Co_id"); strSql.Append(" where a.Ledger=@Ledger and b.FunType=@FunType and not FIND_IN_SET(a.ModuleType,@SplitException)"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } if (isDefine != 0) { strSql.Append(" and c.IsDefine=@IsDefine"); } if (!string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" and FIND_IN_SET(a.IsDefine,@MdItems)"); } strSql.Append(" and ifnull(c.CoName,'') like @CoName and ifnull(c.CoStrcName,'') like @CoStrcName"); return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, FunType = funType, IsDefine = isDefine, CoStrcName = "%" + coStrcName + "%", CoName = "%" + coName + "%", SplitException = WHoleDAL.splitException, MdItems = WHoleDAL.MdItems })); }
public DataTable GetYdSsrOfCmd(string CoStrcName, string CoName, DateTime Start, DateTime End) { if (string.IsNullOrEmpty(CoStrcName) || CoStrcName == "{StrcName}" || CoStrcName == "null") { CoStrcName = string.Empty; } if (string.IsNullOrEmpty(CoName) || CoName == "{CoName}" || CoName == "null") { CoName = string.Empty; } string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Clear(); strSql.Append("select a.Log_id,a.Co_id,a.Module_id,a.ModuleAddr,b.ModuleName,a.FunType,a.Create_dt,c.CoStrcName,c.CoName,s2.UName as Create_by,a.DataValue"); strSql.Append(" from v2_command as a inner join vp_mdinfo as b on a.Ledger=b.Ledger and a.Module_id=b.Module_id"); strSql.Append(" inner join vp_coinfo as c on a.Ledger=c.Ledger and a.Co_id=c.Co_id"); strSql.Append(" left join sys_user as s2 on a.Ledger=s2.Ledger and a.Create_by=s2.Uid"); strSql.Append(" where a.Ledger=@Ledger and a.CDate>=@Start and a.CDate<=@End and a.ErrCode=1"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" and a.FunType in('Ssr0','Ssr1','Ssr') and LENGTH(a.DataValue)>0 "); strSql.Append(" and ifnull(c.CoStrcName,'') like @CoStrcName "); strSql.Append(" and ifnull(c.CoName,'') like @CoName "); if (!string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" and FIND_IN_SET(b.IsDefine,@MdItems)"); } strSql.Append(" order by a.Log_id desc"); return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", Start = Start.ToString("yyyy-MM-dd"), End = End.ToString("yyyy-MM-dd"), MdItems = WHoleDAL.MdItems })); }
/// <summary> /// 获取设备预付费信息 /// </summary> /// <param name="CoStrcName"></param> /// <param name="CoName"></param> /// <returns></returns> public DataTable GetYdPrePayInMdOnList(int Module_id, string CoStrcName, string CoName, int co_id) { string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); // StringBuilder strSql = new StringBuilder(); string ids = ""; if ("{strcName}".Equals(CoStrcName)) { CoStrcName = ""; } if ("{coName}".Equals(CoName)) { CoName = ""; } if (co_id != 0) { strSql.Clear(); strSql.Append("select GetCoChildList(@Ledger,@Co_id)as ids"); object obj = SQLHelper.ExecuteScalar(strSql.ToString(), new { Ledger = this.Ledger, Co_id = co_id }); ids = CommFunc.ConvertDBNullToString(obj); } // object params01 = new { Ledger = this.Ledger, Module_id = Module_id, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", MdItems = WHoleDAL.MdItems, AreaPowerStr = AreaPowerStr, Ids = ids, }; strSql.Clear(); //strSql.Append("select a.Module_id,a.ModuleAddr,a.Co_id,a.Multiply,b.LastVal as CurVal,"); //strSql.Append("round(b.ChrgVal * a.Multiply,2)as ChrgVal,round((b.LastVal-b.FirstVal),2) * a.Multiply as UseVal,"); //strSql.Append("round(b.RdVal * a.Multiply,2) as RdVal,c1.CoStrcName,c1.CoName,s1.Price,a.IsCharg"); //strSql.Append(" from vp_mdinfo as a"); //strSql.Append(" left join vp_v2info as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id and a.Module_id=b.Module_id and a.ModuleAddr=b.ModuleAddr and a.Mm_id=b.Mm_id and b.FunType='E'"); //strSql.Append(" left join vp_coinfo as c1 on a.Ledger=c1.Ledger and a.Co_id=c1.Co_id"); //strSql.Append(" left join syscont as s1 on a.Ledger=s1.Ledger"); //strSql.Append(" where a.Ledger=@Ledger and a.ModuleType!='YDH20D'"); strSql.Append("select a.Module_id,a.MeterName,vc.Fun_id,a.ModuleAddr,a.Co_id,a.Multiply,b.LastVal as CurVal,round(b.ChargVal * a.Multiply,2)as ChrgVal,"); strSql.Append("round((b.LastVal-b.FirstVal),2) * a.Multiply as UseVal,round(b.RdVal * a.Multiply,2) as RdVal,"); strSql.Append("c1.CoStrcName,c1.CoName,round(IF(ifnull(d.price,0)>0,d.price,e.price),3) Price,a.IsCharg,"); strSql.Append("round(b.ZsVal * a.Multiply,2) as ZsVal,round(b.SyZsVal * a.Multiply,2)as SyZsVal"); strSql.Append(" from vp_mdinfo as a"); strSql.Append(" inner join syscont e on a.Ledger=e.Ledger"); strSql.Append(" inner join vp_funinfo as vc on a.Ledger=vc.Ledger and a.Module_id=vc.Module_id and vc.FunType='E'"); strSql.Append(" left join vp_v2info as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id and a.Module_id=b.Module_id and a.ModuleAddr=b.ModuleAddr and a.Mm_id=b.Mm_id and vc.Fun_id=b.Fun_id and b.Hist_id=0"); strSql.Append(" left join vp_coinfo as c1 on a.Ledger=c1.Ledger and a.Co_id=c1.Co_id"); strSql.Append(" left join v1_gateway_esp_module_info as d on a.Ledger=d.Ledger and a.Module_id=d.Module_id"); strSql.Append(" where a.Ledger=@Ledger and a.ModuleType!='YDH20D'"); strSql.Append(" and a.ModuleAddr=case when e.Zb=0 then a.MeterAddr else a.ModuleAddr end"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } if (Module_id != 0) { strSql.Append(" and a.Module_id=@Module_id"); } strSql.Append(" and ifnull(c1.CoStrcName,'') like @CoStrcName "); strSql.Append(" and ifnull(c1.CoName,'') like @CoName "); if (!string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" and FIND_IN_SET(a.IsDefine,@MdItems)"); } if (!string.IsNullOrEmpty(ids)) { strSql.Append(" and FIND_IN_SET(a.Co_id,@Ids)"); } return(SQLHelper.Query(strSql.ToString(), params01)); }
/// <summary> /// 获取数据列表 /// </summary> /// <param name="CoStrcName"></param> /// <param name="CoName"></param> /// <param name="Ssr"></param> /// <returns></returns> public DataTable GetYdMonitorOnList(int module_id, string CoStrcName, string CoName, string Ssr, int co_id) { StringBuilder strSql = new StringBuilder(); if (string.IsNullOrEmpty(CoStrcName) || CoStrcName == "{strcName}" || CoStrcName == "null") { CoStrcName = string.Empty; } if (string.IsNullOrEmpty(CoName) || CoName == "{coName}" || CoName == "null") { CoName = string.Empty; } if (string.IsNullOrEmpty(Ssr) || Ssr == "{ssr}" || Ssr == "null") { Ssr = string.Empty; } string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); string ids = ""; if (co_id != 0) { strSql.Clear(); strSql.Append("select GetCoChildList(@Ledger,@Co_id)as ids"); object obj = SQLHelper.ExecuteScalar(strSql.ToString(), new { Ledger = this.Ledger, Co_id = co_id }); ids = CommFunc.ConvertDBNullToString(obj); } object params01 = new { Ledger = this.Ledger, Module_id = module_id, CoStrcName = "%" + CoStrcName + "%", CoName = "%" + CoName + "%", Ssr = Ssr, SplitException = WHoleDAL.splitException, AreaPowerStr = AreaPowerStr, MdItems = WHoleDAL.MdItems, Ids = ids, }; strSql.Clear(); strSql.Append("select a.Ledger,a.Module_id,a.ModuleAddr,a.ModuleName,a.Co_id,a.Multiply,b.LastVal as CurVal,"); strSql.Append("round(b.RdVal * a.Multiply,b.Scale)as RdVal,round(b.SyZsVal * a.Multiply,b.Scale)as SyZsVal,"); strSql.Append("round(b.LastVal * a.Multiply,b.Scale)as LastVal,a.ModuleType,"); strSql.Append("b.LastTime,c1.CoName,c1.CoStrcName,''as Ssr,s1.FrMd,b.MemcachKey,a.HandledBY,"); strSql.Append("case when TIMESTAMPDIFF(MINUTE,b.LastTime,NOW()) < s1.FrMd then 1 else 0 end as `OnLine`,mp.IsAlarm,"); strSql.Append("case when ifnull(mp.Price,0)=0 then s1.Price else mp.Price end as Price,fp.LpszDbVarName as CachComm"); strSql.Append(" from vp_mdinfo as a"); strSql.Append(" left join vp_v2info as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id and a.Module_id=b.Module_id and a.ModuleAddr=b.ModuleAddr and a.Mm_id=b.Mm_id and b.FunType='E' and b.Hist_id=0"); strSql.Append(" left join vp_coinfo as c1 on a.Ledger=c1.Ledger and a.Co_id=c1.Co_id"); strSql.Append(" left join syscont as s1 on a.Ledger=s1.Ledger"); strSql.Append(" left join v1_gateway_esp_module_info as mp on a.Ledger=mp.Ledger and a.Module_id=mp.Module_id"); strSql.Append(" left join vp_funinfo as fp on a.Ledger=fp.Ledger and a.Module_id=fp.Module_id and a.Mm_id=fp.Mm_id and fp.FunType='Comm' and fp.Disabled=0");/*在线状态*/ strSql.Append(" where a.Ledger=@Ledger and not FIND_IN_SET(a.ModuleType,@SplitException)"); if (module_id != 0) { strSql.Append(" and a.Module_id=@Module_id"); } if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } if (!string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" and FIND_IN_SET(a.IsDefine,@MdItems)"); } //strSql.Append(string.Format(" and a.IsDefine in ({0})", WHoleDAL.MdItems)); strSql.Append(" and ifnull(c1.CoStrcName,'') like @CoStrcName "); strSql.Append(" and ifnull(c1.CoName,'') like @CoName "); if (!string.IsNullOrEmpty(ids)) { strSql.Append(" and FIND_IN_SET(a.Co_id,@Ids)"); } return(SQLHelper.Query(strSql.ToString(), params01)); }
public DataTable GetYdPayLogOnList(string StrcName, string CoName, int d_ways, string Create_by, DateTime StartTime, DateTime EndTime) { if (string.IsNullOrEmpty(StrcName) || StrcName == "{StrcName}" || StrcName == "null") { StrcName = string.Empty; } if (string.IsNullOrEmpty(CoName) || CoName == "{CoName}" || CoName == "null") { CoName = string.Empty; } if (string.IsNullOrEmpty(Create_by) || Create_by == "{Create_by}" || Create_by == "null") { Create_by = string.Empty; } string AreaPowerStr = ""; bool IsCheckAreaPower = WHoleDAL.GetAreaPower(this.Ledger, this.SysUid, out AreaPowerStr); StringBuilder strSql = new StringBuilder(); strSql.Clear(); strSql.Append("select a.Log_id,a.CDate,a.Co_id,a.Module_id,a.ModuleAddr,c.ModuleName,b.CoStrcName,b.CoName,b.CoFullName,"); strSql.Append("a.PayAmt,a.PayVal,a.PayType,a.IsWrong,a.IsPay,a.Trade_no,s1.UName as Create_by,"); strSql.Append("a.Create_dt,a.ErrCode,a.EndTime,a.ErrTxt"); strSql.Append(" from v4_pay_log as a"); if (string.IsNullOrEmpty(WHoleDAL.MdItems)) { strSql.Append(" left join vp_mdinfo as c on a.Ledger=c.Ledger and a.Module_id=c.Module_id and a.ModuleAddr=c.ModuleAddr"); } else { strSql.Append(" inner join vp_mdinfo as c on a.Ledger=c.Ledger and a.Module_id=c.Module_id and a.ModuleAddr=c.ModuleAddr and FIND_IN_SET(c.IsDefine,@MdItems)"); } strSql.Append(" left join vp_coinfo as b on a.Ledger=b.Ledger and a.Co_id=b.Co_id"); strSql.Append(" left join vp_userinfo as s1 on a.Ledger=s1.Ledger and a.Create_by=s1.Uid"); //strSql.Append(" left join sys_user as s2 on a.Ledger=s2.Ledger and a.Create_by=s2.Uid"); strSql.Append(" where a.Ledger=@Ledger and a.CDate>=@Start and a.CDate<=@End and a.ErrCode=1"); if (IsCheckAreaPower == true) { strSql.Append(" and FIND_IN_SET(a.Co_id,@AreaPowerStr)"); } strSql.Append(" and ifnull(b.CoStrcName,'') like @CoStrcName "); strSql.Append(" and ifnull(b.CoName,'') like @CoName "); if (!string.IsNullOrEmpty(Create_by)) { strSql.Append(" and ifnull(s1.UName,'') like @Create_by"); } if (d_ways == 1) { strSql.Append(" and a.PayType=1");/*充值*/ } else if (d_ways == 2) { strSql.Append(" and a.PayType=2");/*退费*/ } else if (d_ways == 3) { strSql.Append(" and a.PayType=3");/*赠送电*/ } else if (d_ways == 4) { strSql.Append(" and a.PayType=4");/*退赠送电*/ } else if (d_ways != 0) { strSql.Append(" and 1=2");/*没有记录*/ } strSql.Append(" order by a.Log_id desc"); return(SQLHelper.Query(strSql.ToString(), new { Ledger = this.Ledger, AreaPowerStr = AreaPowerStr, CoStrcName = "%" + StrcName + "%", CoName = "%" + CoName + "%", Create_by = "%" + Create_by + "%", Start = StartTime.ToString("yyyy-MM-dd"), End = EndTime.ToString("yyyy-MM-dd"), MdItems = WHoleDAL.MdItems })); }