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, ITmtnDevOperateRecordRepository { public TmtnDevOperateRecordRepository(InspectionDbContext DbContext) : base(DbContext) { } public Task 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(DbContext.Database, sql, parameters).FirstOrDefault(); return Task.FromResult(record); } } }