qlhb/Ropin.Inspection.Repository/TmtnDevOperateRecordRepository.cs

using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository
{
    public class TmtnDevOperateRecordRepository : RepositoryBase<TMTN_DevOperateRecord, Guid>, ITmtnDevOperateRecordRepository
    {
        public TmtnDevOperateRecordRepository(InspectionDbContext DbContext) : base(DbContext)
        {

        }

        public Task<RepairStatistics> GetDevOpsStatisticsAsync(string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("storeCode", storeCode)};
            string sql = "";
            sql = @"SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Approval,
CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Confirm ,
CASE WHEN ISNULL(SUM(if(C_Status='3',1,0))) THEN 0 ELSE SUM(if(C_Status='3',1,0)) END AS RepairOn ,
CASE WHEN ISNULL(SUM(if(C_Status='4',1,0))) THEN 0 ELSE SUM(if(C_Status='4',1,0)) END AS Complete,
CASE WHEN ISNULL(SUM(if(C_Status='5',1,0))) THEN 0 ELSE SUM(if(C_Status='5',1,0)) END AS Cancel,
CASE WHEN ISNULL(SUM(if(C_Status='6',1,0))) THEN 0 ELSE SUM(if(C_Status='6',1,0)) END AS RepairRework,  
CASE WHEN ISNULL(SUM(if(C_Status='7',1,0))) THEN 0 ELSE SUM(if(C_Status='7',1,0)) END AS RepairCompleted  
FROM (
SELECT A.D_CreateOn,A.C_Status FROM TMTN_DevOps A 
INNER JOIN TISP_Spot C ON C.C_Code = A.C_SpotCode  WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND A.D_CreateOn >= date_sub(NOW(),INTERVAL 6 MONTH)
)D
";
            RepairStatistics record;
            record = EntityFrameworkCoreExtensions.SqlQuery<RepairStatistics>(DbContext.Database, sql, parameters).FirstOrDefault();
            return Task.FromResult(record);
        }

    }
}