296 lines
15 KiB
C#
296 lines
15 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Configuration;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using HZQR.Common;
|
||
|
||
namespace WebAPiDemo.GeneralMethod
|
||
{
|
||
public static class AuditHelper
|
||
{
|
||
/// <summary>
|
||
/// 创建<see cref="OracleHelper"/>实例（使用配置文件里的"db"连接串）
|
||
/// </summary>
|
||
private static Common.OracleHelper CreateOracleHelper() => new Common.OracleHelper(
|
||
ConfigurationManager.ConnectionStrings["db"].ConnectionString);
|
||
|
||
/// <summary>
|
||
/// 获取稽核指标数据
|
||
/// </summary>
|
||
/// <param name="StatisticsMonth">统计月份</param>
|
||
/// <param name="ProvinceId">省份内码</param>
|
||
/// <param name="ServerpartId">服务区内码</param>
|
||
/// <param name="BusinessType">经营模式</param>
|
||
/// <param name="BusinessTrade">经营业态</param>
|
||
public static Model.AuditSummaryModel GetAuditSummary(string StatisticsMonth,
|
||
string ProvinceId = null,
|
||
string ServerpartId = null,
|
||
string BusinessType = null,
|
||
string BusinessTrade = null)
|
||
{
|
||
var sql =
|
||
$@"select
|
||
audit_days,
|
||
statistics_days,
|
||
auditcash_amount,
|
||
cashpay_amount,
|
||
abnormalaudit_count,
|
||
audit_count,
|
||
audit_checkcount,
|
||
commandaudit_dealcount,
|
||
commandaudit_count
|
||
from platform_dashboard.t_auditmonthly where statistics_month = {StatisticsMonth}";
|
||
|
||
if (!string.IsNullOrEmpty(ProvinceId))
|
||
{
|
||
sql += $" and province_id = {ProvinceId}";
|
||
}
|
||
if (!string.IsNullOrEmpty(ServerpartId))
|
||
{
|
||
sql += $" and serverpart_id = {ServerpartId}";
|
||
}
|
||
if (!string.IsNullOrEmpty(BusinessType))
|
||
{
|
||
sql += $" and business_type = {BusinessType}";
|
||
}
|
||
if (!string.IsNullOrEmpty(BusinessTrade))
|
||
{
|
||
sql += $" and regexp_like(business_trade, '(^|,){BusinessTrade}(,|$)')";
|
||
}
|
||
|
||
var dsAuditData = CreateOracleHelper().ExcuteSqlGetDataSet(sql);
|
||
|
||
// 查到数据
|
||
if (dsAuditData.Tables.Count > 0 && dsAuditData.Tables[0].Rows.Count > 0)
|
||
{
|
||
// 指数：稽核完成度=稽核天数/本月天数*指数（指数通过配置文件控制）
|
||
var audit_rate = ConfigurationManager.AppSettings["audit_rate"].TryParseToDecimal();
|
||
|
||
decimal sum_audit_complete_ratio = 0, // 求和（稽核天数/本月天数*指数）
|
||
sum_auditcash_amount = 0, // 求和（稽查现金）
|
||
sum_cashpay_amount = 0; // 求和（现金支付）
|
||
int sum_abnormalaudit_count = 0, // 求和（异常稽核次数）
|
||
sum_audit_count = 0, // 求和（稽核次数）
|
||
sum_audit_checkcount = 0, // 求和（现场稽核查看次数）
|
||
sum_commandaudit_dealcount = 0, // 求和（指令稽核异常次数）
|
||
sum_commandaudit_count = 0; // 求和（指令稽核次数）
|
||
|
||
foreach (DataRow row in dsAuditData.Tables[0].Rows)
|
||
{
|
||
// 获取从DB取到的数据
|
||
var audit_days = row.Read<int>("audit_days"); // 稽核天数
|
||
var statistics_days = row.Read<int>("statistics_days"); // 本月天数
|
||
var auditcash_amount = row.Read<decimal>("auditcash_amount"); // 稽查现金
|
||
var cashpay_amount = row.Read<decimal>("cashpay_amount"); // 现金支付
|
||
var abnormalaudit_count = row.Read<int>("abnormalaudit_count"); // 异常稽核次数
|
||
var audit_count = row.Read<int>("audit_count"); // 稽核次数
|
||
var audit_checkcount = row.Read<int>("audit_checkcount"); // 现场稽核查看次数
|
||
var commandaudit_dealcount = row.Read<int>("commandaudit_dealcount"); // 指令稽核异常次数
|
||
var commandaudit_count = row.Read<int>("commandaudit_count"); // 指令稽核次数
|
||
|
||
// 统计数据（求和）
|
||
sum_audit_complete_ratio += audit_rate * audit_days / statistics_days;
|
||
sum_auditcash_amount += auditcash_amount;
|
||
sum_cashpay_amount += cashpay_amount;
|
||
sum_abnormalaudit_count += abnormalaudit_count;
|
||
sum_audit_count += audit_count;
|
||
sum_audit_checkcount += audit_checkcount;
|
||
sum_commandaudit_dealcount += commandaudit_dealcount;
|
||
sum_commandaudit_count += commandaudit_count;
|
||
}
|
||
|
||
return new Model.AuditSummaryModel
|
||
{
|
||
// 稽核完成度=平均值（稽核天数/本月天数*指数，指数通过配置文件控制）
|
||
AuditCompleteRatio = (double)Math.Round(100 * sum_audit_complete_ratio / dsAuditData.Tables[0].Rows.Count, 2, MidpointRounding.AwayFromZero),
|
||
// 稽核误差率 = 求和（稽查现金）/ 求和（现金支付）
|
||
AuditInaccuracyRatio = sum_auditcash_amount == 0 || sum_cashpay_amount == 0
|
||
? 100
|
||
: (double)Math.Round(100 * sum_auditcash_amount / sum_cashpay_amount, 2, MidpointRounding.AwayFromZero),
|
||
// 异常稽核次数占比=求和（异常稽核次数）/求和（稽核次数）
|
||
AbnormalAuditRatio = sum_audit_count == 0
|
||
? 0
|
||
: (double)Math.Round(100d * sum_abnormalaudit_count / sum_audit_count, 2, MidpointRounding.AwayFromZero),
|
||
// 稽核视频查看率=求和（现场稽核查看次数）/求和（异常稽核次数）
|
||
AuditCheckcountRatio = sum_abnormalaudit_count == 0
|
||
? 0
|
||
: (double)Math.Round(100d * sum_audit_checkcount / sum_abnormalaudit_count, 2, MidpointRounding.AwayFromZero),
|
||
// 指令稽核异常占比=求和（指令稽核异常次数）/求和（指令稽核次数）
|
||
CommandAuditDealCountRatio = sum_commandaudit_count == 0
|
||
? 0
|
||
: (double)Math.Round(100d * sum_commandaudit_dealcount / sum_commandaudit_count, 2, MidpointRounding.AwayFromZero)
|
||
};
|
||
}
|
||
|
||
// 如果没有从DB查询到任何数据
|
||
return null;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取片区异常日结占比
|
||
/// </summary>
|
||
/// <param name="StatisticsMonth">统计月份</param>
|
||
/// <param name="SpregiontypeId">片区内码</param>
|
||
/// <param name="OrderFields">排序字段：支持对返回结果中"异常日结数量,异常日结占比,异常日结长短款额"的字段进行排序</param>
|
||
public static List<Model.AbnormalRatioModel> GetAbnormalRatioBySpregiontype(string StatisticsMonth,
|
||
string SpregiontypeId = null,
|
||
string OrderFields = null)
|
||
{
|
||
var sql = $@"
|
||
with region as (
|
||
select distinct
|
||
spregiontype_id,
|
||
spregiontype_name
|
||
from
|
||
highway_storage.t_serverpart
|
||
where
|
||
spregiontype_id is not null
|
||
), abnormalaudit as (
|
||
select
|
||
spregiontype_id,
|
||
count(distinct endaccount_id) as abnormal_endaccount_id_count, --异常日结数量：现场稽核异常表中不同日结账单内码的记录条数
|
||
sum(different_price) as abnormal_different_price --异常日结长短款额：统计现场稽核异常表的长短款额，数值求和
|
||
from
|
||
highway_storage.t_serverpart t,
|
||
highway_selldata.t_abnormalaudit a
|
||
where
|
||
t.serverpart_id = a.serverpart_id
|
||
and erase_type in (1,2)
|
||
and check_enddate like '{StatisticsMonth}%'
|
||
group by
|
||
spregiontype_id
|
||
), revenuemonthly as (
|
||
select
|
||
spregiontype_id,
|
||
sum(endaccount_count) as sum_endaccount_count --求和（日结账单数量）
|
||
from
|
||
highway_storage.t_serverpart t,
|
||
platform_dashboard.t_revenuemonthly r
|
||
where
|
||
t.serverpart_id = r.serverpart_id
|
||
and statistics_month = {StatisticsMonth}
|
||
group by
|
||
spregiontype_id
|
||
)
|
||
select
|
||
t.spregiontype_id as id,
|
||
t.spregiontype_name as name,
|
||
nvl(a.abnormal_endaccount_id_count, 0) as abnormal_endaccount_id_count,
|
||
nvl(round(100 * a.abnormal_endaccount_id_count / r.sum_endaccount_count, 2), 0) as abnormal_endaccount_id_ratio, --异常日结占比=求和（异常日结数量）/求和（日结账单数量）
|
||
nvl(a.abnormal_different_price, 0) as abnormal_different_price
|
||
from
|
||
region t
|
||
left join abnormalaudit a on t.spregiontype_id = a.spregiontype_id
|
||
left join revenuemonthly r on t.spregiontype_id = r.spregiontype_id";
|
||
|
||
return QueryAbnormalRatio(sql, SpregiontypeId, OrderFields);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取服务区异常日结占比
|
||
/// </summary>
|
||
/// <param name="StatisticsMonth">统计月份</param>
|
||
/// <param name="SpregiontypeId">片区内码</param>
|
||
/// <param name="OrderFields">排序字段：支持对返回结果中"异常日结数量,异常日结占比,异常日结长短款额"的字段进行排序</param>
|
||
public static List<Model.AbnormalRatioModel> GetAbnormalRatioByServerpart(string StatisticsMonth,
|
||
string SpregiontypeId = null,
|
||
string OrderFields = null)
|
||
{
|
||
var sql = $@"
|
||
with abnormalaudit as (
|
||
select
|
||
serverpart_id,
|
||
count(distinct endaccount_id) as abnormal_endaccount_id_count, --异常日结数量：现场稽核异常表中不同日结账单内码的记录条数
|
||
sum(different_price) as abnormal_different_price --异常日结长短款额：统计现场稽核异常表的长短款额，数值求和
|
||
from
|
||
highway_selldata.t_abnormalaudit
|
||
where
|
||
erase_type in (1,2)
|
||
and check_enddate like '{StatisticsMonth}%'
|
||
group by
|
||
serverpart_id
|
||
), revenuemonthly as (
|
||
select
|
||
serverpart_id,
|
||
sum(endaccount_count) as sum_endaccount_count --求和（日结账单数量）
|
||
from
|
||
platform_dashboard.t_revenuemonthly
|
||
where
|
||
statistics_month = {StatisticsMonth}
|
||
group by
|
||
serverpart_id
|
||
)
|
||
select
|
||
t.serverpart_id as id,
|
||
t.serverpart_name as name,
|
||
nvl(a.abnormal_endaccount_id_count, 0) as abnormal_endaccount_id_count,
|
||
nvl(round(100 * a.abnormal_endaccount_id_count / r.sum_endaccount_count, 2), 0) as abnormal_endaccount_id_ratio, --异常日结占比=求和（异常日结数量）/求和（日结账单数量）
|
||
nvl(a.abnormal_different_price, 0) as abnormal_different_price
|
||
from
|
||
highway_storage.t_serverpart t
|
||
left join abnormalaudit a on t.serverpart_id = a.serverpart_id
|
||
left join revenuemonthly r on t.serverpart_id = r.serverpart_id";
|
||
|
||
return QueryAbnormalRatio(sql, SpregiontypeId, OrderFields);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 查询异常日结占比
|
||
/// </summary>
|
||
/// <param name="sql">查询SQL</param>
|
||
/// <param name="SpregiontypeId">片区内码</param>
|
||
/// <param name="OrderFields">排序字段：支持对返回结果中"异常日结数量,异常日结占比,异常日结长短款额"的字段进行排序</param>
|
||
private static List<Model.AbnormalRatioModel> QueryAbnormalRatio(string sql,
|
||
string SpregiontypeId = null,
|
||
string OrderFields = null)
|
||
{
|
||
if (!string.IsNullOrEmpty(SpregiontypeId))
|
||
{
|
||
sql += $" where t.spregiontype_id = {SpregiontypeId}";
|
||
}
|
||
if (!string.IsNullOrEmpty(OrderFields))
|
||
{
|
||
sql += $" order by {GetSqlOrderFields(OrderFields)}";
|
||
}
|
||
|
||
var dsRatioData = CreateOracleHelper().ExcuteSqlGetDataSet(sql);
|
||
|
||
// 查到数据
|
||
if (dsRatioData.Tables.Count > 0 && dsRatioData.Tables[0].Rows.Count > 0)
|
||
{
|
||
return dsRatioData.Tables[0].AsEnumerable().Select(row => new Model.AbnormalRatioModel
|
||
{
|
||
// 服务区/片区 内码
|
||
Id = row.Read<string>("id"),
|
||
// 服务区/片区 名称
|
||
Name = row.Read<string>("name"),
|
||
// 异常日结数量：现场稽核异常表中不同日结账单内码的记录条数
|
||
AbnormalEndAccountIdCount = row.Read<int>("abnormal_endaccount_id_count"),
|
||
// 异常日结占比=求和（异常日结数量）/求和（日结账单数量）
|
||
AbnormalEndAccountIdRatio = row.Read<double>("abnormal_endaccount_id_ratio"),
|
||
// 异常日结长短款额：统计现场稽核异常表的长短款额，数值求和
|
||
AbnormalDifferentPrice = row.Read<double>("abnormal_different_price")
|
||
}).ToList();
|
||
}
|
||
|
||
// 如果没有从DB查询到任何数据
|
||
return null;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 转换排序字段为SQL字段
|
||
/// </summary>
|
||
/// <param name="OrderFields">排序字段</param>
|
||
private static string GetSqlOrderFields(string OrderFields)
|
||
{
|
||
return OrderFields.Replace("，", ",")
|
||
.Replace("异常日结数量", "abnormal_endaccount_id_count")
|
||
.Replace("异常日结占比", "abnormal_endaccount_id_ratio")
|
||
.Replace("异常日结长短款额", "abnormal_different_price")
|
||
.Replace(nameof(Model.AbnormalRatioModel.AbnormalEndAccountIdCount), "abnormal_endaccount_id_count")
|
||
.Replace(nameof(Model.AbnormalRatioModel.AbnormalEndAccountIdRatio), "abnormal_endaccount_id_ratio")
|
||
.Replace(nameof(Model.AbnormalRatioModel.AbnormalDifferentPrice), "abnormal_different_price");
|
||
}
|
||
}
|
||
} |