249 lines
11 KiB
C#
249 lines
11 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Web;
|
||
using WebAPiDemo.GeneralMethod.Common;
|
||
using WebAPiDemo.Models;
|
||
using WebAPiDemo.Models.Common;
|
||
|
||
namespace WebAPiDemo.GeneralMethod.Services
|
||
{
|
||
/// <summary>
|
||
/// 车流量数据分析业务层
|
||
/// </summary>
|
||
public class CartCountAnalysisHelper
|
||
{
|
||
/// <summary>
|
||
/// Oracle 查询初始化帮助类
|
||
/// </summary>
|
||
private readonly OracleHelper _oracleHelper;
|
||
|
||
/// <summary>
|
||
/// 默认初始化表【卡口车辆进出月度汇总表】
|
||
/// </summary>
|
||
public CartCountAnalysisHelper()
|
||
{
|
||
_oracleHelper = new OracleHelper(OracleConfigHelper.Server, OracleConfigHelper.Name, OracleConfigHelper.UserName_PLATFORM_DASHBOARD, OracleConfigHelper.UserPwd);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 月度车辆类型占比分页查询
|
||
/// </summary>
|
||
/// <param name="pageResultDto">查询参数</param>
|
||
/// <returns></returns>
|
||
public List<CartCountAnalysisModel> MonthCartTypeList(PageCartCountAnalysisRequestDto pageResultDto)
|
||
{
|
||
StringBuilder sbSql = new StringBuilder(); //查询Sql
|
||
StringBuilder sbWhere = new StringBuilder($" where INOUT_TYPE={pageResultDto.InoutType}"); //条件Sql，默认 进出类型【INOUT_TYPE】=0
|
||
StringBuilder groupWhere = new StringBuilder(); //分组Sql
|
||
StringBuilder orderBy = new StringBuilder(pageResultDto.SortStr); //排序Sql
|
||
|
||
//判断月度是否为空
|
||
if(!string.IsNullOrWhiteSpace(pageResultDto.StatisticsMonth))
|
||
{
|
||
//查询月度字段
|
||
sbWhere.Append($" AND STATISTICS_MONTH='{pageResultDto.StatisticsMonth}'");
|
||
}
|
||
|
||
//判断服务区内码是否为空,以，分组
|
||
if (!string.IsNullOrWhiteSpace(pageResultDto.ServerpartIds))
|
||
{
|
||
var ids = pageResultDto.ServerpartIds.Split(',').ToArray();
|
||
if(ids!=null && ids.Any())
|
||
{
|
||
//查询服务区内码
|
||
sbWhere.Append($" AND SERVERPART_ID in( {string.Join(",", ids)})");
|
||
}
|
||
}
|
||
|
||
//判断服务区方位是否为空
|
||
if (!string.IsNullOrWhiteSpace(pageResultDto.ServerpartRegion))
|
||
{
|
||
//查询服务区方位
|
||
sbWhere.Append($" AND SERVERPART_REGION='{pageResultDto.ServerpartRegion}'");
|
||
}
|
||
|
||
//从【卡口车辆进出月度汇总表】 查询月度车辆类型占比数据
|
||
//统计字段：车辆类型【VEHICLE_TYPE】,车流量【VEHICLE_COUNT】，停留时长(s)【STAY_TIMES】,记录条数（停留时长）【STAY_TIMESCOUNT】
|
||
sbSql.Append("select VEHICLE_TYPE,sum(STAY_TIMES) STAY_TIMES,sum(VEHICLE_COUNT)VEHICLE_COUNT,sum(STAY_TIMESCOUNT)STAY_TIMESCOUNT from T_BAYONETMONTHLY_AH");
|
||
|
||
//车辆类型【VEHICLE_TYPE】分组
|
||
groupWhere.Append(" Group by VEHICLE_TYPE");
|
||
sbSql.Append(sbWhere).Append(groupWhere).Append(orderBy);
|
||
|
||
var result = _oracleHelper.ExcuteSqlGetDataSet(sbSql.ToString());
|
||
//查询不为空，则转成List列表返回
|
||
if (result != null)
|
||
{
|
||
var list = (IList<CartCountAnalysisModel>)DataTableHelper.DataTableToIList<CartCountAnalysisModel>(result.Tables[0]);
|
||
if(list!=null && list.Any())
|
||
{
|
||
//遍历list,停留时长=求和（停留时长）/求和（停留时长记录条数）,余2位小数
|
||
foreach (var item in list)
|
||
{
|
||
item.STAY_TIMES = Math.Round(item.STAY_TIMES / item.STAY_TIMESCOUNT, 2);
|
||
}
|
||
}
|
||
|
||
return list.ToList();
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
/// 获取月度车辆归属地排行
|
||
/// <param name="StatisticsMonth">统计月份</param>
|
||
/// <param name="ServerpartIds">服务区内码集合，多个用,隔开</param>
|
||
/// <param name="ServerpartRegion">服务区方位</param>
|
||
/// <param name="CartType">车辆类型</param>
|
||
/// <returns></returns>
|
||
public CartCountAnalysisRankModel MonthCartRank(string StatisticsMonth,string ServerpartIds,string ServerpartRegion,string CartType)
|
||
{
|
||
StringBuilder sbSql = new StringBuilder(); //Sql
|
||
StringBuilder sbWhere = new StringBuilder($" where 1=1"); //条件Sql
|
||
|
||
//判断月度是否为空
|
||
if (!string.IsNullOrWhiteSpace(StatisticsMonth))
|
||
{
|
||
//查询月度字段
|
||
sbWhere.Append($" AND STATISTICS_MONTH='{StatisticsMonth}'");
|
||
}
|
||
|
||
//判断服务区内码是否为空,以，分组
|
||
if (!string.IsNullOrWhiteSpace(ServerpartIds))
|
||
{
|
||
var ids = ServerpartIds.Split(',').ToArray();
|
||
if (ids != null && ids.Any())
|
||
{
|
||
//查询服务区内码
|
||
sbWhere.Append($" AND SERVERPART_ID in( {string.Join(",", ids)})");
|
||
}
|
||
}
|
||
|
||
//判断服务区方位是否为空
|
||
if (!string.IsNullOrWhiteSpace(ServerpartRegion))
|
||
{
|
||
//查询服务区方位
|
||
sbWhere.Append($" AND SERVERPART_REGION='{ServerpartRegion}'");
|
||
}
|
||
|
||
//判断车辆类型是否为空
|
||
if (!string.IsNullOrWhiteSpace(CartType))
|
||
{
|
||
//查询车辆类型
|
||
sbWhere.Append($" AND VEHICLE_TYPE='{CartType}'");
|
||
}
|
||
|
||
//从【卡口车辆进出月度汇总表】 查询月度车辆归属地排行
|
||
//统计字段：省内车流量【PROVINCE_COUNT】,省外车流量【OUTPROVINCE_COUNT】，总车流量【Ttoal_COUNT】
|
||
sbSql.Append("select sum(PROVINCE_COUNT) PROVINCE_COUNT,sum(OUTPROVINCE_COUNT)OUTPROVINCE_COUNT from T_BAYONETMONTHLY_AH");
|
||
sbSql.Append(sbWhere);
|
||
|
||
var result = _oracleHelper.ExcuteSqlGetDataSet(sbSql.ToString());
|
||
|
||
//返回是否为空
|
||
if (result != null)
|
||
{
|
||
var model = DataTableHelper.DataTableToModel<CartCountAnalysisRankModel>(result.Tables[0]);
|
||
|
||
//车辆归属地列表
|
||
var plateList = GetLicensePlateModelList(StatisticsMonth,ServerpartIds, ServerpartRegion, CartType);
|
||
|
||
if (plateList != null && plateList.Any())
|
||
{
|
||
//计算列表内 车流量占比 【VEHICLE_COUNT_RATIO】=车流量【VEHICLE_COUNT】/总车流量【Ttoal_COUNT】,并取4位余数
|
||
foreach (var item in plateList)
|
||
{
|
||
item.VEHICLE_COUNT_RATIO = Math.Round(item.VEHICLE_COUNT / model.Ttoal_COUNT, 4) * 100;
|
||
}
|
||
}
|
||
|
||
//将车辆归属地列表添加入 model
|
||
model.LicensePlateList.AddRange(plateList);
|
||
|
||
return model;
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
////车辆归属地列表
|
||
/// <param name="StatisticsMonth">统计月份</param>
|
||
/// <param name="ServerpartIds">服务区内码集合，多个用,隔开</param>
|
||
/// <param name="ServerpartRegion">服务区方位</param>
|
||
/// <param name="CartType">车辆类型</param>
|
||
/// <returns></returns>
|
||
public List<LicensePlateModel> GetLicensePlateModelList(string StatisticsMonth, string ServerpartIds, string ServerpartRegion, string CartType)
|
||
{
|
||
StringBuilder sbSql = new StringBuilder(); //sql
|
||
StringBuilder sbWhere = new StringBuilder($" where 1=1"); //条件Sql
|
||
StringBuilder groupWhere = new StringBuilder(); //分组Sql
|
||
|
||
//判断月度是否为空
|
||
if (!string.IsNullOrWhiteSpace(StatisticsMonth))
|
||
{
|
||
//查询月度字段
|
||
sbWhere.Append($" AND STATISTICS_MONTH='{StatisticsMonth}'");
|
||
}
|
||
|
||
//判断服务区内码是否为空,以，分组
|
||
if (!string.IsNullOrWhiteSpace(ServerpartIds))
|
||
{
|
||
var ids = ServerpartIds.Split(',').ToArray();
|
||
if (ids != null && ids.Any())
|
||
{
|
||
//查询服务区内码
|
||
sbWhere.Append($" AND SERVERPART_ID in( {string.Join(",", ids)})");
|
||
}
|
||
}
|
||
|
||
//判断服务区方位是否为空
|
||
if (!string.IsNullOrWhiteSpace(ServerpartRegion))
|
||
{
|
||
//查询服务区方位
|
||
sbWhere.Append($" AND SERVERPART_REGION='{ServerpartRegion}'");
|
||
}
|
||
|
||
//判断车辆类型是否为空
|
||
if (!string.IsNullOrWhiteSpace(CartType))
|
||
{
|
||
//查询车辆类型
|
||
sbWhere.Append($" AND VEHICLE_TYPE='{CartType}'");
|
||
}
|
||
|
||
//从【卡口车辆归属月度统计表】 查询车辆归属地列表
|
||
//统计字段：车牌归属地【LICENSE_PLATE】,停留时长【STAY_TIMES】，车流量【VEHICLE_COUNT】,记录条数（停留时长）【STAY_TIMESCOUNT】
|
||
sbSql.Append("select LICENSE_PLATE,sum(STAY_TIMES)STAY_TIMES,sum(VEHICLE_COUNT)VEHICLE_COUNT,sum(STAY_TIMESCOUNT)STAY_TIMESCOUNT from T_BAYONETOWNERMONTH_AH");
|
||
//根据 车牌归属地【LICENSE_PLATE】 分组
|
||
groupWhere.Append("Group by LICENSE_PLATE");
|
||
|
||
sbSql.Append(sbWhere).Append(groupWhere);
|
||
|
||
//Sql查询帮助类
|
||
OracleHelper oracleHelper = new OracleHelper(OracleConfigHelper.Server, OracleConfigHelper.Name, OracleConfigHelper.UserName_HIGHWAY_SELLDATA, OracleConfigHelper.UserPwd);
|
||
|
||
var result = oracleHelper.ExcuteSqlGetDataSet(sbSql.ToString());
|
||
|
||
//判断返回是否为空
|
||
if (result != null)
|
||
{
|
||
//实例化成List
|
||
var list = (List<LicensePlateModel>)DataTableHelper.DataTableToIList<LicensePlateModel>(result.Tables[0]);
|
||
|
||
if (list != null && list.Any())
|
||
{
|
||
//遍历list,停留时长=求和（停留时长）/求和（停留时长记录条数）,余2位小数
|
||
foreach (var item in list)
|
||
{
|
||
item.STAY_TIMES = Math.Round(item.STAY_TIMES / item.STAY_TIMESCOUNT, 2);
|
||
}
|
||
}
|
||
return list;
|
||
}
|
||
|
||
return new List<LicensePlateModel>();
|
||
}
|
||
}
|
||
} |