主营分析列表接口

WX

2023-11-21 babd76ba1c7fb0706f6dea718e3dcb29214b1e85

主营分析列表接口

 GasolineBlend/Controllers/RcyController.cs

@@ -84,5 +84,15 @@
            var res = await _rcyService.GetGsxqList();
            return SuccessNoShow(data: res);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<ActionResult> GetChainIncomeCostPage(int pageIndex, int pageSize, string exchangeCode)
        {
            var res = await _rcyService.GetChainIncomeCostPage(pageIndex,pageSize, exchangeCode);
            return SuccessNoShow(data: res);
        }
    }
}

 RiskControl.NewService/Entity/RCY/ChainIncomeCost.cs

New file
@@ -0,0 +1,52 @@
using SqlSugar;

namespace RiskControl.NewService.Entity.RCY
{
    /// <summary>
    /// 
    ///</summary>
    [SugarTable("ChainIncomeCost")]
    public class ChainIncomeCost
    {
        /// <summary>
        ///  
        ///</summary>
        [SugarColumn(ColumnName = "Id", IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        /// <summary>
        ///  
        ///</summary>
        [SugarColumn(ColumnName = "CompanyName")]
        public string CompanyName { get; set; }
        /// <summary>
        ///  
        ///</summary>
        [SugarColumn(ColumnName = "ExchangeCode")]
        public string ExchangeCode { get; set; }
        /// <summary>
        ///  
        ///</summary>
        [SugarColumn(ColumnName = "ExchangeName")]
        public string ExchangeName { get; set; }
        /// <summary>
        ///  
        ///</summary>
        [SugarColumn(ColumnName = "Income")]
        public decimal? Income { get; set; }
        /// <summary>
        ///  
        ///</summary>
        [SugarColumn(ColumnName = "Cost")]
        public decimal? Cost { get; set; }
        /// <summary>
        ///  
        ///</summary>
        [SugarColumn(ColumnName = "GrossMargin")]
        public decimal? GrossMargin { get; set; }
        /// <summary>
        ///  
        ///</summary>
        [SugarColumn(ColumnName = "ReportType")]
        public string ReportType { get; set; }
    }
}

 RiskControl.NewService/RiskControl.NewService.csproj

@@ -159,6 +159,7 @@
    <Compile Include="Entity\CreditRatingReportSelf.cs" />
    <Compile Include="Entity\CreditRatingReportStar.cs" />
    <Compile Include="Entity\QccDbEntity\SysCompanyOriginalName.cs" />
    <Compile Include="Entity\RCY\ChainIncomeCost.cs" />
    <Compile Include="Entity\RCY\RcyGsxq.cs" />
    <Compile Include="Entity\RCY\RcyJqjz.cs" />
    <Compile Include="Entity\RCY\RcySsdw.cs" />

 RiskControl.NewService/Service/RcyService.cs

@@ -1,4 +1,5 @@
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using RiskControl.NewService.Entity.RCY;
using RiskControl.NewService.Extension;
@@ -93,5 +94,47 @@
            return res;
        }

        public async Task<Page<object>> GetChainIncomeCostPage(int pageIndex,int pageSize,string exchangeCode)
        {
            var pageCompany = await RiskControlRRDb.Queryable<ChainIncomeCost>()
                .Select(a => a.CompanyName)
                .Distinct()
                .ToPageAsync(pageIndex, pageSize);

            var reportTypes = await RiskControlRRDb.Queryable<ChainIncomeCost>()
                .Select(a => a.ReportType)
                .Distinct()
                .ToListAsync();

            var colSql = string.Empty;
            foreach (var reportType in reportTypes.OrderByDescending(a=>a))
            {
                colSql += $@"
    MAX(CASE WHEN ReportType = '{reportType}' THEN Income ELSE 0 END)  AS Income{reportType},
    MAX(CASE WHEN ReportType = '{reportType}' THEN Cost ELSE 0 END) AS Cost{reportType},
    MAX(CASE WHEN ReportType = '{reportType}' THEN GrossMargin ELSE 0 END) AS GrossMargin{reportType},";
            }
            var sql = $@"SELECT 
    CompanyName,
    ExchangeCode,
    ExchangeName,{colSql.TrimEnd(',')}
FROM ChainIncomeCost
Where CompanyName in ('{string.Join("','", pageCompany.Items)}') 
{(string.IsNullOrWhiteSpace(exchangeCode)?"": $"And ExchangeCode='{exchangeCode}'")}
GROUP BY CompanyName, ExchangeCode, ExchangeName;";

            var data =  RiskControlRRDb.Ado.SqlQuery<object>(sql);


            var res = new Page<object>()
            {
                CurrentPage = pageCompany.CurrentPage,
                PageSize = pageCompany.PageSize,
                TotalItems = pageCompany.TotalItems,
                Items = data
            };
            return res;
        }

    }
}