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