/// <summary>
///
/// </summary>
/// <param name="userId"></param>
public DataTable QueryExistParam(string userId)
{
string sql = string.Format(@"SELECT QCSCQY,CLXH,RLLX,
CASE WHEN MAX(ISNULL(BSQXS,0))='MT' THEN 'MT' ELSE 'OT' END BSQXS,
CASE WHEN MAX(CAST(ISNULL(ZWPS,0) AS INT))<3 THEN 2 ELSE 3 END ZWPS,
MIN(CAST(ISNULL(ZCZBZL,0) AS INT)) ZCZBZL,
MAX(CAST(ISNULL(ZHGKRLXHL,0) AS DECIMAL(10, 1))) ZHGKRLXHL,
MAX(CAST(ISNULL(ZHGKRLXHL,0) AS DECIMAL(10, 1))) ZHGKRLXHL,
SUM(SL) SL
FROM HS_JHB
WHERE CLZL='乘用车（M1）' AND USER_ID='{0}'
GROUP BY QCSCQY,CLXH,RLLX
ORDER BY QCSCQY,CLXH,RLLX,BSQXS,ZWPS,ZCZBZL,ZHGKRLXHL" , userId);
CalcDBHelper dbUtil = new CalcDBHelper();
DataTable dtBasic = new DataTable();
try
{
dtBasic = dbUtil.QuerySingleDT(sql);
}
catch (Exception)
{
}
return(dtBasic);
}
/// <summary>
/// 根据条件查询数据平均油耗聚合参数
/// </summary>
/// <param name="userId">用户名</param>
/// <param name="clxh">车辆型号</param>
/// <param name="rllx">燃料类型</param>
/// <param name="clzl">车辆种类</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">截止时间</param>
/// <param name="timeType">时间类型（制造日期or上传日期）</param>
/// <returns></returns>
public DataSet QueryFuelCalData(string userId, string clxh, string rllx, string clzl, string startTime, string endTime, string timeType)
{
CalcDBHelper dbUtil = new CalcDBHelper();
string sqlCondition = string.Format("CLZL='乘用车（M1）' and USER_ID='{0}'", userId);
if (!string.IsNullOrEmpty(clxh))
{
sqlCondition += " AND CLXH LIKE '%" + clxh.Trim() + "%'";
}
if (!string.IsNullOrEmpty(rllx))
{
sqlCondition += " AND RLLX='" + rllx.Trim() + "'";
}
// 默认搜索上报日期，若timeType为CLZZRQ则搜索车辆制造日期
string timeField = "CLZZRQ";
if (timeType == "CREATETIME")
{
timeField = "CLZZRQ";
}
if (!string.IsNullOrEmpty(startTime))
{
sqlCondition += " AND " + timeField + ">='" + startTime.Trim() + "'";
}
if (!string.IsNullOrEmpty(endTime))
{
sqlCondition += " AND " + timeField + "<'" + Convert.ToDateTime(endTime.Trim()).Add(new TimeSpan(24, 0, 0)).ToString() + "'";
}
string sqlBasic = string.Empty;
sqlBasic = string.Format(@"SELECT QCSCQY,USER_ID,CLZL,RLLX,CLXH,
MAX(CAST(ISNULL(ZWPS,0) AS INT)) ZWPS,
MIN(CAST(ISNULL(ZCZBZL,0) AS INT)) ZCZBZL,
MAX(CAST(ISNULL(ZHGKRLXHL,0) AS DECIMAL(10, 1))) ZHGKRLXHL,
CAST(SUM(SL) AS INT) SL
FROM HS_JHB WHERE {0}
GROUP BY QCSCQY,USER_ID,CLZL,RLLX,CLXH
ORDER BY QCSCQY,USER_ID,CLZL,RLLX,CLXH,ZWPS,ZCZBZL,ZHGKRLXHL", sqlCondition);
DataSet dsBasic = new DataSet();
try
{
dsBasic = dbUtil.QuerySingleDS(sqlBasic);
}
catch (Exception)
{
}
return(dsBasic);
}
/// <summary>
/// 平均油耗
/// </summary>
/// <param name="userId"></param>
/// <param name="clxh"></param>
/// <param name="rllx"></param>
/// <param name="clzl"></param>
/// <param name="zczbzl"></param>
/// <param name="bsqxs"></param>
/// <param name="bsqdws"></param>
/// <param name="zhgkxhl"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public DataSet QueryForeCastCAFC()
{
CalcDBHelper dbUtil = new CalcDBHelper();
string sqlBasic = string.Empty;
sqlBasic = string.Format(@"SELECT T.QCSCQY,SUM(T.SL) AS SL,
SUM(T.P_ACT) / SUM(T.SL) AS CAFC,
SUM(T.P_TGT) / SUM(T.SL) AS TCAFC,
(SUM(T.P_TGT) / SUM(T.SL))*1.06 AS TCAFC106,
(SUM(T.P_TGT) / SUM(T.SL))*1.09 AS TCAFC109
FROM
(
SELECT T1.QCSCQY,T1.CLXH,T1.ZWPS,T1.BSQXS, T1.ACT_ZHGKRLXHL,T2.TGT_ZHKGRLXHL,
SUM(T1.SL) AS SL,
MAX(CAST(ISNULL(T1.ACT_ZHGKRLXHL,0) AS decimal(10, 1))) * SUM(SL) AS P_ACT,
MAX(CAST(ISNULL(T2.TGT_ZHKGRLXHL,0) AS decimal(10, 1))) * SUM(SL) AS P_TGT
FROM
(
SELECT QCSCQY,CLXH,
CASE WHEN MAX(ISNULL(BSQXS,0))='MT' THEN 'MT' ELSE 'OT' END BSQXS,
CASE WHEN MAX(CAST(ISNULL(ZWPS,0) AS INT))<3 THEN 2 ELSE 3 END ZWPS,
MIN(CAST(ISNULL(ZCZBZL,0) AS INT)) ZCZBZL,
MAX(CAST(ISNULL(ZHGKRLXHL,0) AS decimal(10, 1))) ACT_ZHGKRLXHL,SUM(JKSL) SL
FROM HS_FORECAST
WHERE (RLLX='汽油' OR RLLX='柴油' OR RLLX='双燃料' OR RLLX='两用燃料' OR RLLX='非插电式混合动力')
GROUP BY QCSCQY,CLXH
) T1,
(
SELECT [ZWPS],[BSQXS],[MIN_ZCZBZL],[MAX_ZCZBZL],[TGT_ZHKGRLXHL]
FROM TARGET_FUEL
) T2
WHERE T1.ZWPS=T2.[ZWPS] AND T2.BSQXS=T1.BSQXS AND T1.ZCZBZL>T2.[MIN_ZCZBZL] AND T1.ZCZBZL<T2.[MAX_ZCZBZL]
GROUP BY T1.QCSCQY,T1.CLXH,T1.ZWPS,T1.BSQXS, T1.ACT_ZHGKRLXHL,T2.TGT_ZHKGRLXHL
) T
GROUP BY QCSCQY" );
DataSet dsBasic = new DataSet();
try
{
dsBasic = dbUtil.QuerySingleDS(sqlBasic);
}
catch (Exception)
{
}
return(dsBasic);
}
/// <summary>
/// 根据条件查询数据上报统计
/// </summary>
/// <param name="userId">用户名</param>
/// <param name="clxh">车辆型号</param>
/// <param name="rllx">燃料类型</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">截止时间</param>
/// <param name="timeType">时间类型（制造日期or上传日期）</param>
/// <returns></returns>
public DataSet QueryStatisticsData(string userId, string clxh, string rllx, string startTime, string endTime)
{
CalcDBHelper dbUtil = new CalcDBHelper();
string sqlCondition = string.Format("CLZL='乘用车（M1）' and USER_ID='{0}'", userId);
if (!string.IsNullOrEmpty(clxh))
{
sqlCondition += " AND CLXH LIKE '%" + clxh.Trim() + "%'";
}
if (!string.IsNullOrEmpty(rllx))
{
sqlCondition += " AND RLLX='" + rllx.Trim() + "'";
}
if (!string.IsNullOrEmpty(startTime))
{
sqlCondition += " AND CLZZRQ>='" + startTime.Trim() + "'";
}
if (!string.IsNullOrEmpty(endTime))
{
sqlCondition += " AND CLZZRQ<'" + Convert.ToDateTime(endTime.Trim()).Add(new TimeSpan(24, 0, 0)).ToString() + "'";
}
string sqlBasic = string.Empty;
sqlBasic = string.Format(@"SELECT T1.QCSCQY,T1.QYLX,T1.USER_ID,T1.CLXH,CAST(T1.CLZZRQ AS NVARCHAR) CLZZRQ,CAST(SUM(SL) AS INT) SL FROM
(
SELECT QCSCQY,USER_ID,CLXH,
CASE SUBSTRING(QYID,1,1) WHEN 'F' THEN '进口' ELSE '国产' END QYLX,
CONVERT(NVARCHAR(7),CLZZRQ,23) CLZZRQ,SL
FROM HS_JHB NOLOCK
WHERE {0}
) T1
GROUP BY QCSCQY,CLXH,CLZZRQ,QYLX,USER_ID
ORDER BY CLXH,CLZZRQ", sqlCondition);
DataSet dsBasic = new DataSet();
try
{
dsBasic = dbUtil.QuerySingleDS(sqlBasic);
}
catch (Exception ex)
{
}
return(dsBasic);
}
public DataTable QueryForecastParam()
{
CalcDBHelper dbUtil = new CalcDBHelper();
string sqlBasic = string.Empty;
sqlBasic = string.Format(@"select * from HS_FORECAST");
DataTable dsBasic = new DataTable();
try
{
dsBasic = dbUtil.QuerySingleDT(sqlBasic);
}
catch (Exception)
{
}
return(dsBasic);
}
public int SaveForecastData(string calcId, string clxh, string rllx, string bsqxs, int zwps, int zczbzl, int jksl, double zhgkrlxhl)
{
int count = 0;
string sql = string.Format(@"INSERT INTO HS_FORECAST
(
[CALC_ID],[QCSCQY],[CLXH]
,[RLLX],[BSQXS],[ZWPS]
,[ZCZBZL],[ZHGKRLXHL],[JKSL]
)
VALUES('{0}','{1}','{2}','{3}','{4}',{5},{6},{7},{8})
", calcId, Utils.qymc, clxh, rllx, bsqxs, zwps, zczbzl, zhgkrlxhl, jksl);
CalcDBHelper dbUtil = new CalcDBHelper();
try
{
count = dbUtil.ExecuteSql(sql);
}
catch (Exception)
{
}
return(count);
}
/// <summary>
/// 平均油耗
/// </summary>
/// <param name="userId"></param>
/// <param name="clxh"></param>
/// <param name="rllx"></param>
/// <param name="clzl"></param>
/// <param name="zczbzl"></param>
/// <param name="bsqxs"></param>
/// <param name="bsqdws"></param>
/// <param name="zhgkxhl"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public DataSet QueryCAFC(string userId, string rllx, string clxh, string zczbzl, string zwps, string bsqxs, string zhgkxhl, string startTime, string endTime)
{
CalcDBHelper dbUtil = new CalcDBHelper();
string sqlCondition = string.Format(" And USER_ID='{0}'", userId);
if (!string.IsNullOrEmpty(startTime))
{
sqlCondition += " AND CLZZRQ>='" + startTime.Trim() + "'";
}
if (!string.IsNullOrEmpty(endTime))
{
sqlCondition += " AND CLZZRQ<'" + Convert.ToDateTime(endTime.Trim()).Add(new TimeSpan(24, 0, 0)).ToString() + "'";
}
string sqlBasic = string.Empty;
sqlBasic = string.Format(@"SELECT T.QCSCQY AS 汽车企业名称, T.QYLX AS 企业类型,
SUM(T.SL) AS 产量,
SUM(T.P_ACT) / SUM(T.SL) AS CAFC,
SUM(T.P_TGT) / SUM(T.SL) AS TCAFC,
(SUM(T.P_TGT) / SUM(T.SL))*1.06 AS TCAFC106,
(SUM(T.P_TGT) / SUM(T.SL))*1.09 AS TCAFC109
FROM
(
SELECT T1.QCSCQY, CASE SUBSTRING(QYID,1,1) WHEN 'F' THEN '进口'ELSE '国产'END QYLX,
T1.CLXH,T1.ZWPS,T1.BSQXS, T1.ACT_ZHGKRLXHL,T2.TGT_ZHKGRLXHL,
SUM(T1.SL) AS SL,
MAX(CAST(ISNULL(T1.ACT_ZHGKRLXHL,0) AS decimal(10, 1))) * SUM(SL) AS P_ACT,
MAX(CAST(ISNULL(T2.TGT_ZHKGRLXHL,0) AS decimal(10, 1))) * SUM(SL) AS P_TGT
FROM
(
SELECT QCSCQY,CLXH,QYID,
CASE WHEN MAX(ISNULL(BSQXS,0))='MT' THEN 'MT' ELSE 'OT' END BSQXS,
CASE WHEN MAX(CAST(ISNULL(ZWPS,0) AS INT))<3 THEN 2 ELSE 3 END ZWPS,
MIN(CAST(ISNULL(ZCZBZL,0) AS INT)) ZCZBZL,
MAX(CAST(ISNULL(ZHGKRLXHL,0) AS decimal(10, 1))) ACT_ZHGKRLXHL,SUM(SL) SL
FROM HS_JHB
WHERE CLZL='乘用车（M1）' AND (RLLX='汽油' OR RLLX='柴油' OR RLLX='双燃料' OR RLLX='两用燃料' OR RLLX='非插电式混合动力') {0}
GROUP BY QYID,QCSCQY,CLXH
) T1,
(
SELECT [ZWPS],[BSQXS],[MIN_ZCZBZL],[MAX_ZCZBZL],[TGT_ZHKGRLXHL]
FROM TARGET_FUEL
) T2
WHERE T1.ZWPS=T2.[ZWPS] AND T2.BSQXS=T1.BSQXS AND T1.ZCZBZL>T2.[MIN_ZCZBZL] AND T1.ZCZBZL<T2.[MAX_ZCZBZL]
GROUP BY T1.CLXH,T1.QCSCQY,QYID,T1.ZWPS,T1.BSQXS, T1.ACT_ZHGKRLXHL,T2.TGT_ZHKGRLXHL
) T
GROUP BY T.QCSCQY,T.QYLX
ORDER BY T.QYLX,T.QCSCQY", sqlCondition);
DataSet dsBasic = new DataSet();
try
{
dsBasic = dbUtil.QuerySingleDS(sqlBasic);
}
catch (Exception)
{
}
return(dsBasic);
}
/// <summary>
/// 平均油耗及参与核算的参数明细
/// </summary>
/// <param name="userId"></param>
/// <param name="clxh"></param>
/// <param name="rllx"></param>
/// <param name="clzl"></param>
/// <param name="zczbzl"></param>
/// <param name="bsqxs"></param>
/// <param name="bsqdws"></param>
/// <param name="zhgkxhl"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public DataSet QueryFuelCalDetails(string userId, string rllx, string clxh, string zczbzl, string zwps, string bsqxs, string zhgkxhl, string startTime, string endTime)
{
CalcDBHelper dbUtil = new CalcDBHelper();
string sqlCondition = string.Format("CLZL='乘用车（M1）' and USER_ID='{0}'", userId);
if (!string.IsNullOrEmpty(clxh))
{
sqlCondition += " AND CLXH LIKE '%" + clxh.Trim() + "%'";
}
if (!string.IsNullOrEmpty(rllx))
{
sqlCondition += " AND RLLX='" + rllx.Trim() + "'";
}
if (!string.IsNullOrEmpty(startTime))
{
sqlCondition += " AND CLZZRQ>='" + startTime.Trim() + "'";
}
if (!string.IsNullOrEmpty(endTime))
{
sqlCondition += " AND CLZZRQ<'" + Convert.ToDateTime(endTime.Trim()).Add(new TimeSpan(24, 0, 0)).ToString() + "'";
}
string sqlBasic = string.Empty;
sqlBasic = string.Format(@"SELECT QCSCQY,CLXH,QYID,T1.ZCZBZL,CASE T1.BSQXS WHEN 'OT' then '非手动' else T1.BSQXS end as BSQXS,T1.ZWPS,T1.ZHGKXSLC,
T1.ZHGKRLXHL_ACT,
T1.ZHKGRLXHL_TGT,
SUM(SL_ACT) SL_ACT,
SUM(SL_TGT) SL_TGT,
T1.ZHGKRLXHL_ACT * SUM(SL_ACT) AS P_ACT,
T1.ZHKGRLXHL_TGT * SUM(SL_TGT) AS P_TGT
FROM
(
SELECT T2.QCSCQY,T2.CLXH,T2.QYID,T2.ZCZBZL,T2.BSQXS,T2.ZWPS,T2.ZHGKXSLC,
CASE WHEN (T2.RLLX='纯电动' OR T2.RLLX='燃料电池' OR
(T2.RLLX='插电式混合动力' AND T2.ZHGKXSLC>=50)) THEN 0.0
ELSE ZHGKRLXHL END ZHGKRLXHL_ACT,
T3.TGT_ZHKGRLXHL ZHKGRLXHL_TGT,
CASE WHEN (T2.RLLX='纯电动' OR T2.RLLX='燃料电池' OR
(T2.RLLX='插电式混合动力' AND T2.ZHGKXSLC>=50)) THEN SUM(T2.SL)*5
WHEN (T2.RLLX='汽油' OR T2.RLLX='柴油' OR T2.RLLX='柴油' OR T2.RLLX='两用燃料' OR T2.RLLX='双燃料' OR T2.RLLX='非插电式混合动力')
AND T2.ZHGKRLXHL<=2.8 THEN SUM(T2.SL)*3
ELSE SUM(T2.SL) END SL_ACT,
SUM(T2.SL) AS SL_TGT
FROM
(
SELECT QCSCQY,CLXH,QYID,RLLX,
CASE WHEN MAX(ISNULL(BSQXS,0))='MT' THEN 'MT' ELSE 'OT' END BSQXS,
CASE WHEN MAX(CAST(ISNULL(ZWPS,0) AS INT))<3 THEN 2 ELSE 3 END ZWPS,
MIN(CAST(ISNULL(ZCZBZL,0) AS INT)) ZCZBZL,
MAX(CAST(ISNULL(ZHGKXSLC,0) AS DECIMAL(10, 1))) ZHGKXSLC,
MAX(CAST(ISNULL(ZHGKRLXHL,0) AS DECIMAL(10, 1))) ZHGKRLXHL,
SUM(SL) SL
FROM HS_JHB
WHERE CLZL='乘用车（M1）' AND {0}
GROUP BY QYID,QCSCQY,CLXH,RLLX
) T2,
(
SELECT [ZWPS],[BSQXS],[MIN_ZCZBZL],[MAX_ZCZBZL],[TGT_ZHKGRLXHL]
FROM TARGET_FUEL
) T3
WHERE T2.ZWPS=T3.[ZWPS] AND T2.BSQXS=T3.BSQXS AND T2.ZCZBZL>T3.[MIN_ZCZBZL] AND T2.ZCZBZL<T3.[MAX_ZCZBZL]
GROUP BY T2.QYID,T2.QCSCQY,T2.CLXH,T2.RLLX,T2.ZCZBZL,T2.BSQXS,T2.ZWPS,T2.ZHGKXSLC,T2.ZHGKRLXHL,T3.TGT_ZHKGRLXHL
) T1
GROUP BY QYID,QCSCQY,CLXH,T1.ZCZBZL,T1.BSQXS,T1.ZWPS,T1.ZHGKXSLC,T1.ZHGKRLXHL_ACT,T1.ZHKGRLXHL_TGT " , sqlCondition);
DataSet dsBasic = new DataSet();
try
{
dsBasic = dbUtil.QuerySingleDS(sqlBasic);
}
catch (Exception)
{
}
return(dsBasic);
}