/// <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> /// 充电站监视点设置 /// </summary> public int SetView(int zhanid, int sort) { Common_DAl comDAL = new Common_DAl(); string sql = "update dev_chargstation set ismonitor={0} where zhan_bh={1}"; List <object> list = new List <object>(); list.Add(sort); list.Add(zhanid); return(comDAL.ExecuteSQL(sql, list)); }
/// <summary> /// 充电站监视点查询 /// </summary> public DataTable GetChargStationQuery(int page, int rows, ref int total) { // string strSql = @" // SELECT // dev_chargstation.zhan_bh, // dev_chargstation.zhuan_mc, // dev_chargstation.ismonitor, // case when // dev_chargstation.ismonitor=1 then 'checked' when dev_chargstation.ismonitor=2 then '' end as ismonitornew // FROM dev_chargstation"; string strSql = @" SELECT dev_chargstation.zhan_bh, dev_chargstation.zhuan_mc, nvl(dev_chargstation.ismonitor,0) as ismonitor, nvl(dev_chargstation.ismonitor,0) as ismonitornew FROM dev_chargstation"; DataTable dt = new DataTable(); List <object> list = new List <object>(); Common_DAl comDAL = new Common_DAl(); return(comDAL.GetQueryPage(strSql, list, page, rows, ref total)); }
/// <summary> /// 充电信息统计 /// </summary> public DataTable GetDataTableTotal(string zhanid, DateTime sdt, DateTime edt, int page, int rows, ref int total) { string strSql = @" select A.zhuan_mc, A.ZHUANGXING_H, --A.zhuan_id, A.zhuanglei_x, A.YUNXING_BH, A.CHARGE_TIME_MIN, A.charge_number, A.gat_data_record, round(B.RUNRAT,2) as RUNRAT, round(B.USERAT,2) as USERAT 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.GetQueryPage(strSql, list, page, rows, ref total)); }
/// <summary> /// 充电信息查询 /// </summary> public DataTable GetDataTableQuery(string cardno, DateTime sdt, DateTime edt, string zhanid, int page, int rows, ref int total) { string strSql = @" SELECT dev_chargstation.zhuan_mc, gat_data_record.id, --gat_data_record.zhuan_id, dev_chargpile.YUNXING_BH, gat_data_record.terminal_no, gat_data_record.checkout_no, gat_data_record.creditcarddt, gat_data_record.startdt, gat_data_record.enddt, gat_data_record.card_no, gat_data_record.card_start_money, gat_data_record.card_end_money, gat_data_record.power_high, gat_data_record.money_high, round(gat_data_record.value_high/100,2) value_high, gat_data_record.power_low, gat_data_record.money_low, round(gat_data_record.value_low/100,2) value_low, gat_data_record.power_tip, gat_data_record.money_tip, round(gat_data_record.value_tip/100,2) value_tip, gat_data_record.power_normal, gat_data_record.money_normal, round(gat_data_record.value_normal/100,2) value_normal, gat_data_record.charge_power, round(gat_data_record.charge_money/100,2) charge_money, gat_data_record.charge_time_hour, gat_data_record.charge_time_min, gat_data_record.stop_type, gat_data_record.createdt, gat_data_record.updatedt 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.GetQueryPage(strSql, list, page, rows, ref total)); }