/// <summary>
/// 充电站数据查询导出Excel
/// </summary>
public DataTable CardQueryOutExcel(string cardno, DateTime sdt, DateTime edt, string zhanid)
{
string strSql = @"
SELECT
gat_data_record.card_no as 充值卡,
dev_chargstation.zhuan_mc as 充电场站,
--gat_data_record.zhuan_id as 桩系统编号,
dev_chargpile.yunxing_bh as 桩运行编号,
gat_data_record.startdt as 充电开始时间,
gat_data_record.enddt as 充电结束时间,
round(gat_data_record.value_high/100,2) as 电价_峰,
round(gat_data_record.value_normal/100,2) as 电价_平,
round(gat_data_record.value_low/100,2) as 电价_谷,
round(gat_data_record.value_tip/100,2) as 电价_尖,
gat_data_record.power_high as 电量_分,
gat_data_record.power_normal as 电量_平,
gat_data_record.power_low as 电量_谷,
gat_data_record.money_tip as 电量_尖,
gat_data_record.charge_power as 电量_总,
round(gat_data_record.charge_money/100,2) as 充电总金额
FROM gat_data_record
join dev_chargpile on dev_chargpile.DEV_CHARGPILE=gat_data_record.zhuan_id
join DEV_BRANCH on DEV_BRANCH.BRANCHNO=dev_chargpile.BOX_ID
join DEV_CHARGSTATION on DEV_CHARGSTATION.ZHAN_BH=DEV_BRANCH.ZHUAN_BH
where 1=1 @where order by gat_data_record.STARTDT desc
";
DataTable dt = new DataTable();
var i = -1;
List <object> list = new List <object>();
string strWhere = "";
if (!string.IsNullOrEmpty(cardno))
{
strWhere += " and gat_data_record.CARD_NO={" + ++i + "} ";
list.Add(cardno);
}
if (sdt != new DateTime())
{
strWhere += " and gat_data_record.STARTDT>={" + ++i + "} ";
list.Add(sdt);
}
if (edt != new DateTime())
{
strWhere += " and gat_data_record.STARTDT<{" + ++i + "} ";
list.Add(edt);
}
if (!string.IsNullOrEmpty(zhanid))
{
strWhere += " and DEV_CHARGSTATION.ZHAN_BH={" + ++i + "} ";
list.Add(zhanid);
}
strSql = strSql.Replace("@where", strWhere);
Common_DAl comDAL = new Common_DAl();
return(comDAL.GetQuerySql(strSql, list));
}
/// <summary>
/// 充电站数据统计导出Excel
/// </summary>
public DataTable CardTotalOutExcel(string zhanid, DateTime sdt, DateTime edt)
{
string strSql = @"
select
A.zhuan_mc as 充电场站,
--A.zhuan_id as 桩编号,
A.ZHUANGXING_H as 桩型号,
A.zhuanglei_x as 桩类型,
A.YUNXING_BH as 桩运行编号,
A.CHARGE_TIME_MIN as 累计充电时长,
A.charge_number as 累计充电次数,
A.gat_data_record as 累计充电电量,
round(B.RUNRAT,2) as 运行率,
round(B.USERAT,2) as 使用率
from
(
SELECT
dev_chargstation.zhuan_mc,
gat_data_record.zhuan_id,
dev_powerpiletypes.ZHUANGXING_H,
dev_powerpiletypes.zhuanglei_x,
dev_chargpile.YUNXING_BH,
sum(gat_data_record.CHARGE_TIME_MIN) as CHARGE_TIME_MIN,
count(*) as charge_number,
sum(gat_data_record.CHARGE_POWER) as gat_data_record
FROM gat_data_record
join dev_chargpile on dev_chargpile.DEV_CHARGPILE=gat_data_record.zhuan_id
join dev_powerpiletypes on dev_powerpiletypes.PARSERKEY=dev_chargpile.PILETYPEID
join DEV_BRANCH on DEV_BRANCH.BRANCHNO=dev_chargpile.BOX_ID
join DEV_CHARGSTATION on DEV_CHARGSTATION.ZHAN_BH=DEV_BRANCH.ZHUAN_BH
where 1=1 @where1
group by
dev_chargstation.zhuan_mc,
gat_data_record.zhuan_id,
dev_powerpiletypes.ZHUANGXING_H,
dev_powerpiletypes.zhuanglei_x,
dev_chargpile.YUNXING_BH
) A
left join
(
select
powerpileno,
avg(RUNRAT) as RUNRAT,
avg(USERAT) as USERAT
from RPT_POWERPILEUSE
where 1=1 and RPTTYPE='day' @where2
group by powerpileno
) B on B.POWERPILENO=A.zhuan_id";
DataTable dt = new DataTable();
var i = -1;
List <object> list = new List <object>();
string strWhere1 = "";
string strWhere2 = "";
if (!string.IsNullOrEmpty(zhanid))
{
strWhere1 += " and DEV_CHARGSTATION.ZHAN_BH={" + ++i + "} ";
list.Add(zhanid);
}
if (sdt != new DateTime())
{
strWhere1 += " and gat_data_record.STARTDT>={" + ++i + "} ";
list.Add(sdt);
strWhere2 += " and RPT_POWERPILEUSE.RPTDATE>={" + ++i + "} ";
list.Add(sdt);
}
if (edt != new DateTime())
{
strWhere1 += " and gat_data_record.STARTDT<{" + ++i + "} ";
list.Add(edt);
strWhere2 += " and RPT_POWERPILEUSE.RPTDATE<{" + ++i + "} ";
list.Add(edt);
}
strSql = strSql.Replace("@where1", strWhere1);
strSql = strSql.Replace("@where2", strWhere2);
Common_DAl comDAL = new Common_DAl();
return(comDAL.GetQuerySql(strSql, list));
}