/// <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 }));
}
