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