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