/// <summary>
/// 充电桩死锁数据查询
/// </summary>
public DataTable GetPileLockQuery(int page, int rows, ref int total, string strUrl)
{
string strSql = @"
select
c.zhuan_mc zhuanmc,
--t.powerpileno,
a.YUNXING_BH,
a.powerpilename,
t.logdesc
from oth_pilestates t
join dev_chargpile a on a.dev_chargpile=t.powerpileno
join dev_branch b on b.branchno=a.box_id
join dev_chargstation c on c.zhan_bh=b.zhuan_bh
where t.logdesc like '%死锁%'
";
DataTable dt = new DataTable();
MemeryDbDaoClient bll = new MemeryDbDaoClient();
bll.RpcUrl = strUrl;
var jq = bll.GetPages(strSql.ToUpper(), page, rows);
total = jq.Total;
dt = jq.Rows as DataTable;
return(dt);
}
/// <summary>
/// 充电桩遥测数据查询
/// </summary>
public DataTable GetPileTelemetryQuery(string pileno, DateTime sdt, DateTime edt, string itemname, int page, int rows, ref int total, string strUrl)
{
string strSql = @"
select
c.zhuan_mc,
--(select dc.yunxing_bh from dev_chargpile dc where dc.dev_chargpile= t.zhuan_id) zhuan_id,
--t.zhuan_id 充电桩编号,
p.yunxing_bh,
p.ZONGXIAN_DZ,
t.gatitemid,
i.ITEMNAME,
i.M_UNITS,
i.ITEMDESC,
strftime('@dt',t.gatdt) GATDT,
t.m_value || '' m_value
from GAT_DATA_YC t
join Gat_Item i on t.GATITEMID=i.ITEMNO
join dev_chargpile p on p.DEV_CHARGPILE=t.zhuan_id
join dev_branch b on b.branchno=p.box_id
join DEV_CHARGSTATION c on c.zhan_bh=b.zhuan_bh
@where
order by t.gatdt desc
";
string strWhere = "where 1=1 ";
if (!string.IsNullOrEmpty(pileno))
{
strWhere += " and (t.zhuan_id='" + pileno + "' or p.ZONGXIAN_DZ='" + pileno + "' or p.yunxing_bh='" + pileno + "')";
}
if (sdt != new DateTime())
{
strWhere += " and t.gatdt>='" + sdt.ToString("yyyy-MM-dd") + " 00:00:00'";
}
if (edt != new DateTime())
{
strWhere += " and t.gatdt<'" + edt.AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00'";
}
if (!string.IsNullOrEmpty(itemname))
{
strWhere += " and i.ITEMNAME like '%" + itemname + "%'";
}
strSql = strSql.Replace("@where", strWhere);
strSql = strSql.ToUpper();
strSql = strSql.Replace("@DT", "%Y-%m-%d %H:%M:%S");
DataTable dt = new DataTable();
MemeryDbDaoClient bll = new MemeryDbDaoClient();
bll.RpcUrl = strUrl;
var jq = bll.GetPages(strSql, page, rows);
total = jq.Total;
dt = jq.Rows as DataTable;
return(dt);
}
/// <summary>
/// 遥测明细数据查询
/// </summary>
public DataTable PileTelemetryQueryOutExcel(string pileno, DateTime sdt, DateTime edt, string itemname, string strUrl)
{
string strSql = @"
select
c.zhuan_mc 站名称,
--(select dc.yunxing_bh from dev_chargpile dc where dc.dev_chargpile= t.zhuan_id) zhuan_id,
--t.zhuan_id 充电桩编号,
p.yunxing_bh 桩运行编号,
p.ZONGXIAN_DZ 桩总线地址,
t.gatitemid 采集项标识,
i.ITEMNAME 名称,
i.M_UNITS 单位,
i.ITEMDESC 描述,
strftime('@dt',t.gatdt) 采集时间,
t.m_value || '' 值
from GAT_DATA_YC t
join Gat_Item i on t.GATITEMID=i.ITEMNO
join dev_chargpile p on p.DEV_CHARGPILE=t.zhuan_id
join dev_branch b on b.branchno=p.box_id
join DEV_CHARGSTATION c on c.zhan_bh=b.zhuan_bh
@where
order by t.gatdt desc
";
string strWhere = "where 1=1 ";
if (!string.IsNullOrEmpty(pileno))
{
strWhere += " and (t.zhuan_id='" + pileno + "' or p.ZONGXIAN_DZ='" + pileno + "' or p.yunxing_bh='" + pileno + "')";
}
if (sdt != new DateTime())
{
strWhere += " and t.gatdt>='" + sdt.ToString("yyyy-MM-dd") + " 00:00:00'";
}
if (edt != new DateTime())
{
strWhere += " and t.gatdt<'" + edt.AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00'";
}
if (!string.IsNullOrEmpty(itemname))
{
strWhere += " and i.ITEMNAME like '%" + itemname + "%'";
}
strSql = strSql.Replace("@where", strWhere);
strSql = strSql.ToUpper();
strSql = strSql.Replace("@DT", "%Y-%m-%d %H:%M:%S");
DataTable dt = new DataTable();
MemeryDbDaoClient bll = new MemeryDbDaoClient();
bll.RpcUrl = strUrl;
dt = bll.QueryDt(strSql);
return(dt);
}
/// <summary>
/// 导出充电桩死锁数据Excel
/// </summary>
public DataTable PileLockQueryOutExcel(string strUrl)
{
string strSql = @"
select
c.zhuan_mc 充电场站名称,
--t.powerpileno 充电桩编号,
a.YUNXING_BH 桩运行编号,
a.powerpilename 充电桩名称,
t.logdesc 异常说明
from oth_pilestates t
join dev_chargpile a on a.dev_chargpile=t.powerpileno
join dev_branch b on b.branchno=a.box_id
join dev_chargstation c on c.zhan_bh=b.zhuan_bh
where t.logdesc like '%死锁%'
";
DataTable dt = new DataTable();
MemeryDbDaoClient bll = new MemeryDbDaoClient();
bll.RpcUrl = strUrl;
dt = bll.QueryDt(strSql.ToUpper());
return(dt);
}
