qlhb/Ropin.Inspection.Repository/TmtnDevOpsRecordRepository.cs

using FluentEmail.Core;
using Newtonsoft.Json;
using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Entities;
using Ropin.Inspection.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository
{
    public class TmtnDevOpsRepository : RepositoryBase<TMTN_DevOps, string>, ITmtnDevOpsRepository
    {
        public TmtnDevOpsRepository(InspectionDbContext DbContext) : base(DbContext)
        {

        }

        public Task<IEnumerable<TispRecord30DaysStatistics>> GetRecords30DaysStatisticsAsync(string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("storeCode", storeCode)};
            string sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, SUM(if(C_Status='4',1,0)) AS Normal, SUM(if(C_Status='3',1,0)) AS Abnormal, count(C_ID) as Total   from(
SELECT
    DATE_SUB(CURDATE(), INTERVAL 31 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 30 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 29 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 28 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 27 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 26 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 25 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 24 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 23 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 22 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 21 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 20 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 19 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 18 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 17 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 16 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 15 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 14 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 13 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 12 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 11 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 10 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 9 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 8 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 7 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 6 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 5 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 4 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 3 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 2 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 1 day) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date
) A LEFT JOIN (
SELECT B.* FROM TISP_Spot A
INNER JOIN TMTN_DevOps B ON B.C_SpotCode = A.C_Code WHERE A.C_StoreCode = @storeCode AND A.C_Status = '1'
) B ON  DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')";
            IEnumerable<TispRecord30DaysStatistics> recordlist;
            recordlist = EntityFrameworkCoreExtensions.GetList<TispRecord30DaysStatistics>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordlist);
        }
        public Task<IEnumerable<DevOpsFullScreenRecord>> GetDevOpsFullScreenByDevIdAsync(DevOpsItemSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("devStoreCode", searchModel.C_DevStoreCode)};
            string sql = @"SELECT 2 as ""Type"",store.C_Name as StoreName,ops.D_CreateOn as RecordTime,us.C_Name UserName,CAST(ops.C_Status as SIGNED) Status,ops.C_ID Id 
FROM TMTN_DevOps ops  -- 维保工单
-- inner JOIN TMTN_DevOpsRecord record on ops.C_ID =record.C_DevOpsCode  -- 维保记录 
LEFT JOIN TDEV_DevSpot spot on spot.C_SpotCode = ops.C_SpotCode -- 业主设备运维点表
LEFT JOIN TDEV_DevStore store on store .C_ID =spot.C_DevStoreCode -- 业主设备表
LEFT JOIN TSYS_User us on us .C_UserID = ops.C_CreateBy -- 用户表
WHERE 1=1";
            if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode))
            {
                sql += " AND  spot.C_DevStoreCode = @devStoreCode";
            }
            sql += " AND ops.C_Status <> '0' ORDER BY ops.D_LastUpdatedOn DESC";

            var recordlist = EntityFrameworkCoreExtensions.GetList<DevOpsFullScreenRecord>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordlist);
        }
        public Task<IEnumerable<DevOpsFullScreenRecord>> GetDevRepairFullScreenByDevIdAsync(DevOpsItemSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("devStoreCode", searchModel.C_DevStoreCode)};
            string sql = @"  SELECT 3 as ""Type"",b.C_Name as StoreName,a.D_CreateOn as RecordTime,f.C_Name UserName,CAST(a.C_Status as SIGNED) Status,a.C_ID Id 
 FROM TMTN_RepairOrder a
 LEFT JOIN TDEV_DevStore b on a.C_DevStoreCode =b.C_ID 
 LEFT JOIN TSYS_User f on f.C_UserID = a.C_LastUpdatedBy
 WHERE 1=1 ";
            if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode))
            {
                sql += " AND  a.C_DevStoreCode = @devStoreCode";
            }
            sql += " ORDER BY a.D_LastUpdatedOn DESC";

            var recordlist = EntityFrameworkCoreExtensions.GetList<DevOpsFullScreenRecord>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordlist);
            //searchModel.TotalCount = recordlist.First() != null ? recordlist.ToList().Count : 0;
            //return Task.FromResult(searchModel.IsPagination ? recordlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : recordlist);
        }

        public Task<IEnumerable<DevOpsFullScreenRecord>> GetISPRecordAsync(DevOpsItemSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("devStoreCode", searchModel.C_DevStoreCode)};
            string sql = @"SELECT 1 as ""Type"",store.C_Name as StoreName,record.D_CreateOn as RecordTime,f.C_Name UserName,CAST(record.C_Status as SIGNED) Status,CAST(record.C_ID as char) Id 
FROM TISP_Record record
LEFT JOIN TISP_Spot spot ON record.C_SpotCode =spot.C_Code
LEFT JOIN TDEV_DevSpot devSpot on devSpot.C_SpotCode = spot.C_Code  -- 业主设备运维点表
LEFT JOIN TDEV_DevStore store on store .C_ID =devSpot.C_DevStoreCode -- 业主设备表
LEFT JOIN TSYS_User f on f.C_UserID = record .C_CreateBy 
WHERE 1=1 ";
            if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode))
            {
                sql += " AND  devSpot.C_DevStoreCode = @devStoreCode";
            }
            sql += " ORDER BY record.C_CreateBy DESC";
            var recordlist = EntityFrameworkCoreExtensions.GetList<DevOpsFullScreenRecord>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordlist);
        }

        public Task<IEnumerable<Record12MonthStatistics>> GetRecord12MonthStatisticsAsync(string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
            new MySqlConnector.MySqlParameter("storeCode", storeCode)};
            string sql = @"SELECT date_format(click_date,'%m') AS SpecificMonth, SUM(CASE C_Status WHEN 4 THEN 1 ELSE 0 END) AS RecordCount   from(
SELECT
    DATE_SUB(CURDATE(), INTERVAL 12 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 11 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 10 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 9 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 8 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 7 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 6 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 5 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 4 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 3 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 2 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 1 month) AS click_date UNION ALL
SELECT
    DATE_SUB(CURDATE(), INTERVAL 0 month) AS click_date
) A LEFT JOIN (SELECT B.D_CreateOn,B.C_Status,B.C_ID from TMTN_DevOps B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode  WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1') B ON  DATE_FORMAT(A.click_date, '%Y-%m') = DATE_FORMAT(B.D_CreateOn, '%Y-%m') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m')";
            IEnumerable<Record12MonthStatistics> recordlist;
            recordlist = EntityFrameworkCoreExtensions.GetList<Record12MonthStatistics>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordlist);
        }

        public Task<IEnumerable<TmtnDevOpsWithImageViewModel>> GetDevOpsWithImageAsync(TmtnDevOpsOrderSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode),
                new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("Status", searchModel.C_Status)};
            //            string sql = @"SELECT H.C_ID, A.*,B.C_Url
            //FROM TMTN_DevOps A
            //LEFT JOIN TMTN_DevOpsRecordApp B ON B.C_DevOpsContentCode = A.C_ID
            //LEFT JOIN TISP_Spot C ON C.C_Code = A.C_SpotCode
            //LEFT JOIN TDEV_DevSpot D ON D.C_SpotCode = C.C_Code 
            //LEFT JOIN TDEV_DevStore H ON H.C_ID = D.C_DevStoreCode 
            //WHERE 1=1";
            string sql = @"SELECT A.*,H.C_ID AS DevStoreCode,H.C_Name AS DevStoreName,H.C_NumberCode AS DevStoreNumberCode,C.C_Name AS SpotName,G.C_Name AS UserName,H.C_Url AS DevUrl
FROM TMTN_DevOps A
LEFT JOIN TISP_Spot C ON C.C_Code = A.C_SpotCode
LEFT JOIN TDEV_DevSpot D ON D.C_SpotCode = C.C_Code 
LEFT JOIN TDEV_DevStore H ON H.C_ID = D.C_DevStoreCode
LEFT JOIN TSYS_User G ON G.C_UserID = A.C_CreateBy
WHERE 1=1";
            if (!string.IsNullOrEmpty(searchModel.C_StoreCode))
            {
                sql += " AND  H.C_StoreCode = @StoreCode";
            }
            if (!string.IsNullOrEmpty(searchModel.C_Status))
            {
                sql += " AND  A.C_Status = @Status";
            }
            if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode))
            {
                sql += " AND H.C_ID = @DevStoreCode";
            }

            //if (searchModel.Start != DateTime.MinValue && searchModel.End != DateTime.MinValue)
            //{
            //    sql += " AND A.D_CreateOn BETWEEN @Start AND @End";
            //}

            sql += " ORDER BY A.D_CreateOn DESC";
            IEnumerable<TmtnDevOpsWithImageViewModel> spotlist = EntityFrameworkCoreExtensions.GetList<TmtnDevOpsWithImageViewModel>(DbContext.Database, sql, parameters);
            searchModel.TotalCount = spotlist.First() != null ? spotlist.ToList().Count : 0;
            return Task.FromResult(searchModel.IsPagination ? spotlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : spotlist);
        }



    }
    public class TmtnDevOpsRecordRepository : RepositoryBase<TMTN_DevOpsRecord, Guid>, ITmtnDevOpsRecordRepository
    {
        public TmtnDevOpsRecordRepository(InspectionDbContext DbContext) : base(DbContext)
        {

        }

        public Task<IEnumerable<TmtnDevOpsDetailViewModel>> GetDevOpsAsync(TmtnDevOpsDetailSearchModel searchModel)
        {
                MySqlConnector.MySqlParameter[] parameters = new[] { 
                new MySqlConnector.MySqlParameter("spotCode", searchModel.C_SpotID),
                new MySqlConnector.MySqlParameter("userCode", searchModel.C_UserID),
                new MySqlConnector.MySqlParameter("areaCode", searchModel.C_AreaCode),
                new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode),
                new MySqlConnector.MySqlParameter("Status", searchModel.C_Status),
                new MySqlConnector.MySqlParameter("DevOpsCode", "%" +searchModel.C_DevOpsCode+"%"),
            new MySqlConnector.MySqlParameter("Start", searchModel.Start),
            new MySqlConnector.MySqlParameter("End", searchModel.End),
            new MySqlConnector.MySqlParameter("devOpsStatus", searchModel.DevOpsStatus),
                new MySqlConnector.MySqlParameter("name", "%" +searchModel.C_Name+"%")
            };

//            string sql = @"SELECT C.C_DevStoreCode,H.C_Name AS DevStoreName,H.C_NumberCode AS DevStoreNumberCode, A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark,B.C_ImageUrl, D.C_Name AS C_CreateByName,E.C_Name AS C_LastUpdatedByName 
//FROM TMTN_DevOpsRecord A 
//LEFT JOIN TMTN_SpotDevOpsContent F ON F.C_DevOpsContentCode = A.C_SpotDevOpsContentCode
//LEFT JOIN TISP_Spot B ON B.C_Code = F.C_SpotCode
//LEFT JOIN TDEV_DevSpot C ON C.C_SpotCode = B.C_Code 
//LEFT JOIN TDEV_DevStore H ON H.C_ID = C.C_DevStoreCode 
//LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy 
//LEFT JOIN TSYS_User E ON E.C_UserID = A.C_LastUpdatedBy  
//WHERE B.C_StoreCode = @StoreCode";
            string sql = @"SELECT G.C_ID AS C_DevOpsCode,G.C_Name AS DevOpsName,G.C_Status AS DevOpsStatus, C.C_DevStoreCode,H.C_Name AS DevStoreName,H.C_NumberCode AS DevStoreNumberCode,H.C_OpsTempCode,H.C_PlanTempCode,H.C_RepairTempCode,H.C_RunTempCode, A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark,B.C_ImageUrl, D.C_Name AS C_CreateByName,E.C_Name AS C_LastUpdatedByName 
FROM TMTN_DevOpsRecord A 
LEFT JOIN TMTN_SpotDevOpsContent F ON F.C_DevOpsContentCode = A.C_SpotDevOpsContentCode
LEFT JOIN TISP_Spot B ON B.C_Code = F.C_SpotCode
LEFT JOIN TDEV_DevSpot C ON C.C_SpotCode = B.C_Code 
LEFT JOIN TDEV_DevStore H ON H.C_ID = C.C_DevStoreCode 
LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy 
LEFT JOIN TSYS_User E ON E.C_UserID = A.C_LastUpdatedBy  
INNER JOIN TMTN_DevOps G ON G.C_ID = A.C_DevOpsCode 
WHERE 1=1";
            if (!string.IsNullOrEmpty(searchModel.C_StoreCode))
            {
                sql += " AND B.C_StoreCode = @StoreCode";
            }
            if (!string.IsNullOrEmpty(searchModel.C_Status))
            {
                sql += " AND A.C_Status = @Status";
            }
            if (!string.IsNullOrEmpty(searchModel.DevOpsStatus))
            {
                if (searchModel.DevOpsStatus=="!1")
                {
                    sql += " AND G.C_Status!=1 ";
                }
                else
                {
                    sql += " AND G.C_Status=@devOpsStatus ";
                }
            }
            if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode))
            {
                //sql += " AND C.C_DevStoreCode = @DevStoreCode";
                sql += " AND (B.C_QRCode = @DevStoreCode OR H.C_ID = @DevStoreCode)";//可能扫到设备码或维保点的二维码
            }
            if (!string.IsNullOrEmpty(searchModel.C_SpotID))
            {
                sql += " AND (B.C_Code = @spotCode OR H.C_ID = @spotCode)";
            }
            //if (searchModel.Abnormal)
            //{
            //    sql += " AND A.C_LastUpdatedBy IS NOT NULL";
            //}
            if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
            {
                sql += " AND B.C_AreaCode = @areaCode";
            }

            if (searchModel.Start != DateTime.MinValue && searchModel.End != DateTime.MinValue)
            {
                sql += " AND A.D_CreateOn BETWEEN @Start AND @End";
            }
            if (!string.IsNullOrEmpty(searchModel.C_DevOpsCode))
            {
                sql += " And G.C_ID Like @DevOpsCode";
            }
            if (!string.IsNullOrEmpty(searchModel.C_Name))
            {
                sql += " And B.C_Name Like @name";
            }
            sql += " GROUP BY A.C_DevOpsCode ORDER BY A.D_CreateOn DESC";
            IEnumerable<TmtnDevOpsDetailViewModel> spotlist = EntityFrameworkCoreExtensions.GetList<TmtnDevOpsDetailViewModel>(DbContext.Database, sql, parameters);
            searchModel.TotalCount = spotlist.First() != null ? spotlist.ToList().Count : 0;
            return Task.FromResult(searchModel.IsPagination ? spotlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : spotlist);

        }

        public Task<IEnumerable<TmtnDevOpsRecordDetailViewModel>> GetRecordsConditionAsync(TmtnDevOpsRecordDetailSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { 
                //new MySqlConnector.MySqlParameter("start", searchModel.Start),
                //new MySqlConnector.MySqlParameter("end", searchModel.End),
                new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode),
                new MySqlConnector.MySqlParameter("DevOpsCode", searchModel.C_DevOpsCode),
                new MySqlConnector.MySqlParameter("spotCode", searchModel.C_SpotID),
                new MySqlConnector.MySqlParameter("userCode", searchModel.C_UserID),
                new MySqlConnector.MySqlParameter("areaCode", searchModel.C_AreaCode),
                new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
            new MySqlConnector.MySqlParameter("Start", searchModel.Start),
            new MySqlConnector.MySqlParameter("End", searchModel.End)};
            //SELECT A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark FROM TISP_Record A LEFT JOIN TISP_Spot B ON A.C_SpotCode = B.C_Code
            //string sql = "SELECT A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark,B.C_ImageUrl, D.C_Name AS C_CreateByName,E.C_Name AS C_LastUpdatedByName FROM TISP_Record A LEFT JOIN TISP_Spot B ON A.C_SpotCode = B.C_Code LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User E ON E.C_UserID = A.C_LastUpdatedBy LEFT JOIN TISP_SpotRoute F ON F.C_SpotCode = A.C_SpotCode WHERE 1=1 AND A.D_CreateOn between @start and @end";
            string sql = @"SELECT C.C_DevStoreCode,H.C_Name AS DevStoreName,H.C_NumberCode AS DevStoreNumberCode, A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark,B.C_ImageUrl, D.C_Name AS C_CreateByName,E.C_Name AS C_LastUpdatedByName 
FROM TMTN_DevOpsRecord A 
LEFT JOIN TMTN_SpotDevOpsContent F ON F.C_DevOpsContentCode = A.C_SpotDevOpsContentCode
LEFT JOIN TISP_Spot B ON B.C_Code = F.C_SpotCode
LEFT JOIN TDEV_DevSpot C ON C.C_SpotCode = B.C_Code 
LEFT JOIN TDEV_DevStore H ON H.C_ID = C.C_DevStoreCode 
LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy 
LEFT JOIN TSYS_User E ON E.C_UserID = A.C_LastUpdatedBy  
WHERE 1=1";
            //if (searchModel.bSolidWaste)
            //{
            //    sql += "AND  A.C_SolidWaste IS NOT NULL";
            //}
            if (!string.IsNullOrEmpty(searchModel.C_DevOpsCode))
            {
                sql += " AND  A.C_DevOpsCode = @DevOpsCode";
            }
            if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode))
            {
                sql += " AND  C.C_DevStoreCode = @DevStoreCode";
            }
            if (!string.IsNullOrEmpty(searchModel.C_SpotID))
            {
                sql += " AND  B.C_SpotCode = @spotCode";
            }
            //if (searchModel.Abnormal)
            //{
            //    sql += " AND A.C_LastUpdatedBy IS NOT NULL";
            //}
            if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
            {
                sql += " AND B.C_AreaCode = @areaCode";
            }

            if (searchModel.Start != DateTime.MinValue && searchModel.End != DateTime.MinValue)
            {
                sql += " AND A.D_CreateOn BETWEEN @Start AND @End";
            }

            sql += " ORDER BY A.D_CreateOn DESC";
            IEnumerable<TmtnDevOpsRecordDetailViewModel> spotlist = EntityFrameworkCoreExtensions.GetList<TmtnDevOpsRecordDetailViewModel>(DbContext.Database, sql, parameters);
            searchModel.TotalCount = spotlist.First() != null ? spotlist.ToList().Count : 0;
            return Task.FromResult(searchModel.IsPagination ? spotlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : spotlist);

        }

        public Task<IEnumerable<TmtnDevOpsRecordDetailWithImageViewModel>> GetRecordItemsByRecordIdAsync(string recordId)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("recordId", recordId) };
            string sql = @"SELECT A.*,E.C_AlarmLevel,E.C_Name,C.C_ID AS C_RecordImageId ,C.C_Url AS C_ImageURL,C.C_Status AS RecordImageStatus,D.C_Name AS ReportUserName
FROM TMTN_DevOpsRecord A 
LEFT JOIN TMTN_SpotDevOpsContent F ON F.C_DevOpsContentCode = A.C_SpotDevOpsContentCode
LEFT JOIN TMTN_DevOpsContent E ON E.C_ID = F.C_DevOpsContentCode 
LEFT JOIN TISP_Spot B ON B.C_Code = F.C_SpotCode 
LEFT JOIN TMTN_DevOpsRecordApp C ON C.C_DevOpsContentCode = A.C_ID
LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy
WHERE A.C_DevOpsCode = @recordId  ORDER BY A.D_CreateOn ASC ";
            IEnumerable<TmtnDevOpsRecordWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TmtnDevOpsRecordWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                IEnumerable<TmtnDevOpsRecordDetailWithImageViewModel> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
            //var recordContentItemlist = from s in recordItemlist
            //                            group s by s.C_ContentCode into g
            //                            select g;
            //List<List<TispRecordItemDetailViewModel>> recordContentItems = new List<List<TispRecordItemDetailViewModel>>() { };

            //foreach (var item in recordItemlist)
            //{
            var q = from b in recordItemlist
                    group b by b.C_SpotDevOpsContentCode into g
                    select new TmtnDevOpsRecordDetailWithImageViewModel
                    {
                        C_ID = g.Key,
                        //C_RecordCode = g.First().C_RecordCode,
                        //C_ContentCode = g.First().C_ContentCode,
                        C_SpotDevOpsContentCode = g.First().C_SpotDevOpsContentCode,
                        C_Record = g.First().C_Record,
                        D_CreateOn = g.First().D_CreateOn,
                        C_Remark = g.First().C_Remark,
                        C_Status = g.First().C_Status,
                        C_AlarmLevel = g.First().C_AlarmLevel,
                        C_Name = g.First().C_Name,
                        ReportUserName = g.First().ReportUserName,
                        RecordImageList = (from c in g where c.C_RecordImageId != null select new MtnRecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus, RecordImageName = c.RecordImageName })?.ToList<MtnRecordImage>()?.Distinct(new Compare())?.ToList(),
                    };
            //}


            return Task.FromResult(q);
        }

        public Task<List<List<TmtnDevOpsRecordDetailWithImageViewModel>>> GetRecordItemsGroupByRecordIdAsync(string recordId)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("recordId", recordId) };
            string sql = @"SELECT A.*,E.C_AlarmLevel,E.C_Name,C.C_ID AS C_RecordImageId ,C.C_Url AS C_ImageURL,C.C_Status AS RecordImageStatus,C.C_Name AS RecordImageName,D.C_Name AS ReportUserName
FROM TMTN_DevOpsRecord A 
LEFT JOIN TMTN_SpotDevOpsContent F ON F.C_DevOpsContentCode = A.C_SpotDevOpsContentCode
LEFT JOIN TMTN_DevOpsContent E ON E.C_ID = F.C_DevOpsContentCode 
LEFT JOIN TISP_Spot B ON B.C_Code = F.C_SpotCode 
LEFT JOIN TMTN_DevOpsRecordApp C ON C.C_DevOpsContentCode = A.C_ID
LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy
WHERE A.C_DevOpsCode = @recordId  ORDER BY A.D_CreateOn ASC ";
            IEnumerable<TmtnDevOpsRecordWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TmtnDevOpsRecordWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                List<List<TmtnDevOpsRecordDetailWithImageViewModel>> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
            var recordContentItemlist = from s in recordItemlist
                                        group s by s.C_SpotDevOpsContentCode into g
                                        select g;
            List<List<TmtnDevOpsRecordDetailWithImageViewModel>> recordContentItems = new List<List<TmtnDevOpsRecordDetailWithImageViewModel>>() { };

            foreach (var item in recordContentItemlist)
            {
                var q = from b in item
                        group b by new{ b.C_Status,b.C_ID }  into g
                        select new TmtnDevOpsRecordDetailWithImageViewModel
                        {
                            C_ID = g.First().C_ID,//g.Key,
                            //C_RecordCode = g.First().C_RecordCode,
                            //C_ContentCode = g.First().C_ContentCode,
                            C_SpotDevOpsContentCode = g.First().C_SpotDevOpsContentCode,
                            C_Record = g.First().C_Record,
                            D_CreateOn = g.First().D_CreateOn,
                            C_Remark = g.First().C_Remark,
                            C_Status = g.First().C_Status,
                            C_AlarmLevel = g.First().C_AlarmLevel,
                            C_Name = g.First().C_Name,
                            ReportUserName = g.First().ReportUserName,
                            DevOpsRecordSolidWaste = string.IsNullOrWhiteSpace(g.First().C_SolidWaste)?null:JsonConvert.DeserializeObject<SolidWaste>(g.First().C_SolidWaste),
                            RecordImageList = (from c in g where c.C_RecordImageId != null select new MtnRecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus, RecordImageName = c.RecordImageName })?.ToList<MtnRecordImage>()?.Distinct(new Compare())?.ToList()
,
                        };
                recordContentItems.Add(q.ToList());
            }


            return Task.FromResult(recordContentItems);
        }

        public Task<IEnumerable<TMTN_DevOps>> GetDevOpsList(TmtnDevOpsDetailSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("spotCode", searchModel.C_SpotID),
                new MySqlConnector.MySqlParameter("userCode", searchModel.C_UserID),
                new MySqlConnector.MySqlParameter("areaCode", searchModel.C_AreaCode),
                new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode),
                new MySqlConnector.MySqlParameter("Status", searchModel.C_Status),
                new MySqlConnector.MySqlParameter("DevOpsCode", "%" +searchModel.C_DevOpsCode+"%"),
            new MySqlConnector.MySqlParameter("Start", searchModel.Start),
            new MySqlConnector.MySqlParameter("End", searchModel.End),
            new MySqlConnector.MySqlParameter("devOpsStatus", searchModel.DevOpsStatus),
                new MySqlConnector.MySqlParameter("name", "%" +searchModel.C_Name+"%")
            };
            string sql = @"select G.*,C.C_DevStoreCode from TMTN_DevOps G
LEFT JOIN TDEV_DevSpot C ON G.C_SpotCode = C.C_SpotCode 
where 1=1";
            if (!string.IsNullOrEmpty(searchModel.C_Status))
            {
                sql += "  and G.C_Status=@Status ";
            }
            if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode))
            {
                sql += " and C.C_DevStoreCode=@DevStoreCode ";
            }
            IEnumerable<TMTN_DevOps> spotlist = EntityFrameworkCoreExtensions.GetList<TMTN_DevOps>(DbContext.Database, sql, parameters);
            return Task.FromResult(spotlist);
        }
    }

    public class Compare : IEqualityComparer<MtnRecordImage>
    {
        public bool Equals(MtnRecordImage x, MtnRecordImage y)
        {
            return x.C_RecordImageId == y.C_RecordImageId;
        }
        public int GetHashCode(MtnRecordImage obj)
        {
            return obj.C_RecordImageId.GetHashCode();
        }
    }
}