qlhb/Ropin.Inspection.Repository/ISP/TispRecordItemRepository.cs

using Ropin.Inspection.Common.Helper;
using Ropin.Inspection.Model.Entities;
using Ropin.Inspection.Model.SearchModel;
using Ropin.Inspection.Model.ViewModel;
using Ropin.Inspection.Repository.Interface;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository
{
    public class TispRecordItemRepository : RepositoryBase<TISP_RecordItem, Guid>, ITispRecordItemRepository
    {
        public TispRecordItemRepository(InspectionDbContext dbContext) : base(dbContext)
        {
           
        }
        public Task<int> GetContentAlarmCout(Guid contentId, Guid spotId)
        {
            //var query = from a in DbContext.TISP_Record
            //            join b in DbContext.TISP_RecordItem 
            //            on a.C_ID equals b.C_RecordCode
            //            where a.C_SpotCode == spotId && b.C_ContentCode == contentId && b.C_Status == "3"
            //            select b.C_ID;
            ////var lambda = query.OrderByDescending(p => p)
            //var v = query.ToList();
            MySqlConnector.MySqlParameter[] parameters = new[] { 
                new MySqlConnector.MySqlParameter("contentId", contentId),
            new MySqlConnector.MySqlParameter("spotId", spotId)};
            string sql = @"SELECT B.* FROM TISP_Record A
INNER JOIN TISP_RecordItem B
ON A.C_ID = B.C_RecordCode
WHERE A.C_SpotCode =@spotId AND B.C_ContentCode =@contentId
ORDER BY B.D_CreateOn DESC
";
            IEnumerable<TISP_RecordItem> recordItemlist = EntityFrameworkCoreExtensions.GetList<TISP_RecordItem>(DbContext.Database, sql, parameters);
            TISP_RecordItem item = recordItemlist.FirstOrDefault();
            if (item != null && (item.C_Status == "0" || item.C_Status == "3"))
            {
                return Task.FromResult(1);
            }
            return Task.FromResult(0);

        }
        public Task<IEnumerable<TispRecordItemDetailViewModel>> GetRecordsConditionAsync(Guid recordId)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("recordId", recordId) };
            string sql = "SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID WHERE A.C_RecordCode = @recordId";
            sql = @"SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,D.C_Name AS ReportUserName
FROM TISP_RecordItem A 
LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID 
LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID
LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy
WHERE A.C_RecordCode = @recordId";
            IEnumerable<TispRecordItemWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TispRecordItemWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                IEnumerable<TispRecordItemDetailViewModel> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
            var q = from b in recordItemlist
                    group b by b.C_ID into g
                    select new TispRecordItemDetailViewModel
                    {
                        C_ID = g.Key,
                        C_RecordCode = g.First().C_RecordCode,
                        C_ContentCode = g.First().C_ContentCode,
                        C_SpotContentCode = g.First().C_SpotContentCode,
                        C_InspectionContent = g.First().C_InspectionContent,
                        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 select new RecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus }).ToList<RecordImage>(),
                    };

            return Task.FromResult(q);
        }

        public Task<List<List<TispRecordItemDetailViewModel>>> GetRecordItemsByRecordIdAsync(Guid recordId)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("recordId", recordId) };
            string sql = @"SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,D.C_Name AS ReportUserName
FROM TISP_RecordItem A 
LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID 
LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID
LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy
WHERE A.C_RecordCode = @recordId  ORDER BY A.D_CreateOn ASC ";
            IEnumerable<TispRecordItemWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TispRecordItemWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                List<List<TispRecordItemDetailViewModel>> 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 recordContentItemlist)
            {
                var q = from b in item
                        group b by new { b.C_ID}  into g
                        select new TispRecordItemDetailViewModel
                        {
                            C_ID = g.Key.C_ID,
                            C_RecordCode = g.First().C_RecordCode,
                            C_ContentCode = g.First().C_ContentCode,
                            C_SpotContentCode = g.First().C_SpotContentCode,
                            C_InspectionContent = g.First().C_InspectionContent,
                            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 select new RecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus }).ToList<RecordImage>(),
                        };
                recordContentItems.Add(q.ToList());
            }
           

            return Task.FromResult(recordContentItems);
        }
        /// <summary>
        /// 异常报警记录
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public Task<PageData<TispRecordItemDetailViewModel>> GetAlarmRecordsAsync(TispRecordAlarmSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("AreaCode", searchModel.C_AreaCode),
                new MySqlConnector.MySqlParameter("start", searchModel.Start), 
                new MySqlConnector.MySqlParameter("end", searchModel.End),
                new MySqlConnector.MySqlParameter("spotId", searchModel.SpotId),
                new MySqlConnector.MySqlParameter("createUserId ", searchModel.CreateUserId)};
            string sql = "SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,F.C_Name AS ReportUserName FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy   WHERE (A.C_Status = '0' or A.C_Status = '3') AND E.C_Status = '1' AND E.C_StoreCode = @StoreCode ";
            sql = @"SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,F.C_Name AS ReportUserName 
FROM TISP_RecordItem A 
LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID 
LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy   WHERE A.C_Status = '0'  AND A.C_LastUpdatedBy IS NULL  AND E.C_Status = '1' AND E.C_StoreCode = @StoreCode ";
            if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
            {
                sql += " AND E.C_AreaCode =@AreaCode ";
            }
            if (!string.IsNullOrEmpty(searchModel.SpotId))
            {
                sql += " AND D.C_SpotCode =@spotId ";
            }
            if (!string.IsNullOrEmpty(searchModel.CreateUserId))
            {
                sql += " AND D.C_CreateBy =@createUserId ";
            }
            //if (!string.IsNullOrEmpty(searchModel.UpdateUserId))
            //{
            //    sql += " AND D.C_LastUpdatedBy =@updateUserId ";
            //}
            if (searchModel.Start!= Convert.ToDateTime("0001-01-01 00:00:00")&& searchModel.End != Convert.ToDateTime("0001-01-01 00:00:00"))
            {
                sql += " AND D.D_CreateOn between @start and @end";
            }
            sql += " ORDER BY A.D_CreateOn DESC";

            IEnumerable<TispRecordItemWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TispRecordItemWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                PageData<TispRecordItemDetailViewModel> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
            var q = from b in recordItemlist
                    group b by b.C_ID into g
                    select new TispRecordItemDetailViewModel
                    {
                        C_ID = g.Key,
                        C_RecordCode = g.First().C_RecordCode,
                        C_ContentCode = g.First().C_ContentCode,
                        C_SpotContentCode = g.First().C_SpotContentCode,
                        C_InspectionContent = g.First().C_InspectionContent,
                        C_Remark = g.First().C_Remark,
                        C_Status = g.First().C_Status,
                        C_AlarmLevel = g.First().C_AlarmLevel,
                        C_Name = g.First().C_Name,
                        SpotName = g.First().SpotName,
                        SpotNumber = g.First().SpotNumber,
                        ReportUserName = g.First().ReportUserName,
                        D_CreateOn = g.First().D_CreateOn,
                        RecordImageList = (from c in g select new RecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus }).ToList<RecordImage>(),
                    };
            PageData<TispRecordItemDetailViewModel> pageData = new PageData<TispRecordItemDetailViewModel>
            {
                Totals = q.ToList().Count,
                Rows = searchModel.IsPagination ? q.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize).ToList() : q.ToList()

            };
            return Task.FromResult(pageData);
        }
        /// <summary>
        /// 为后台管理显示所有异常上报记录
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public Task<PageData<TispRecordItemDetailViewModel>> GetAlarmRecordListAsync(TispRecordAlarmSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("AreaCode", searchModel.C_AreaCode),
                new MySqlConnector.MySqlParameter("start", searchModel.Start),
                new MySqlConnector.MySqlParameter("end", searchModel.End),
                new MySqlConnector.MySqlParameter("spotId", searchModel.SpotId),
                new MySqlConnector.MySqlParameter("createUserId ", searchModel.CreateUserId)};
            string sql = "SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,F.C_Name AS ReportUserName FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy   WHERE (A.C_Status = '0' or A.C_Status = '3') AND E.C_Status = '1' AND E.C_StoreCode = @StoreCode ";
            sql = @"SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,G.C_Name AS SpotAreaName,F.C_Name AS ReportUserName 
FROM TISP_RecordItem A 
LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID 
LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy  
LEFT JOIN TPNT_Area G ON G.C_Code = E.C_AreaCode
WHERE A.C_Status = '0' AND E.C_Status = '1' AND E.C_StoreCode = @StoreCode ";
            if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
            {
                sql += " AND E.C_AreaCode =@AreaCode ";
            }
            if (!string.IsNullOrEmpty(searchModel.SpotId))
            {
                sql += " AND D.C_SpotCode =@spotId ";
            }
            if (!string.IsNullOrEmpty(searchModel.CreateUserId))
            {
                sql += " AND D.C_CreateBy =@createUserId ";
            }
            //if (!string.IsNullOrEmpty(searchModel.UpdateUserId))
            //{
            //    sql += " AND D.C_LastUpdatedBy =@updateUserId ";
            //}
            if (searchModel.Start != Convert.ToDateTime("0001-01-01 00:00:00") && searchModel.End != Convert.ToDateTime("0001-01-01 00:00:00"))
            {
                sql += " AND D.D_CreateOn between @start and @end";
            }
            sql += " ORDER BY A.D_CreateOn DESC";

            IEnumerable<TispRecordItemWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TispRecordItemWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                PageData<TispRecordItemDetailViewModel> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
            var q = from b in recordItemlist
                    group b by b.C_ID into g
                    select new TispRecordItemDetailViewModel
                    {
                        C_ID = g.Key,
                        C_RecordCode = g.First().C_RecordCode,
                        C_ContentCode = g.First().C_ContentCode,
                        C_SpotContentCode = g.First().C_SpotContentCode,
                        C_InspectionContent = g.First().C_InspectionContent,
                        C_Remark = g.First().C_Remark,
                        C_Status = g.First().C_Status,
                        C_AlarmLevel = g.First().C_AlarmLevel,
                        C_Name = g.First().C_Name,
                        SpotName = g.First().SpotName,
                        SpotNumber = g.First().SpotNumber,
                        ReportUserName = g.First().ReportUserName,
                        D_CreateOn = g.First().D_CreateOn,
                        SpotAreaName = g.First().SpotAreaName,
                        RecordImageList = (from c in g select new RecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus }).ToList<RecordImage>(),
                    };
            PageData<TispRecordItemDetailViewModel> pageData = new PageData<TispRecordItemDetailViewModel>
            {
                Totals = q.ToList().Count,
                Rows = searchModel.IsPagination ? q.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize).ToList() : q.ToList()

            };
            return Task.FromResult(pageData);
        }
        /// <summary>
        /// 异常报警恢复记录
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public Task<PageData<TispRecordItemDetailViewModel>> GetAlarmRecoveryRecordsAsync(TispRecordAlarmSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("AreaCode", searchModel.C_AreaCode),
                new MySqlConnector.MySqlParameter("start", searchModel.Start),
                new MySqlConnector.MySqlParameter("end", searchModel.End),
                new MySqlConnector.MySqlParameter("spotId", searchModel.SpotId),
                new MySqlConnector.MySqlParameter("createUserId ", searchModel.CreateUserId),
                new MySqlConnector.MySqlParameter("updateUserId  ", searchModel.UpdateUserId )};
            //string sql = "SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID WHERE A.C_Status = '2'";
            string sql = "SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,F.C_Name AS ReportUserName ,G.C_Name AS RecordUserName FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User G ON G.C_UserID = A.C_LastUpdatedBy  WHERE A.C_Status = '2' AND E.C_Status = '1'  AND E.C_StoreCode =@StoreCode ";
            sql = @"SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,E.C_AreaCode AS SpotAreaCode,H.C_Name AS SpotAreaName,F.C_Name AS ReportUserName ,G.C_Name AS RecordUserName
FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy 
LEFT JOIN TSYS_User G ON G.C_UserID = A.C_CreateBy 
LEFT JOIN TPNT_Area H ON H.C_Code = E.C_AreaCode 
WHERE (A.C_Status = '2' || A.C_Status = '3' || A.C_Status = '4') AND E.C_Status = '1'  AND E.C_StoreCode =@StoreCode ";
            if (!string.IsNullOrEmpty(searchModel.SpotId))
            {
                sql += " AND D.C_SpotCode =@spotId ";
            }
            if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
            {
                sql += " AND E.C_AreaCode =@AreaCode ";
            }
            if (!string.IsNullOrEmpty(searchModel.CreateUserId))
            {
                sql += " AND A.C_CreateBy =@createUserId ";
            }
            if (!string.IsNullOrEmpty(searchModel.UpdateUserId))
            {
                sql += " AND A.C_CreateBy =@updateUserId ";
            }
            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 <TispRecordItemWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TispRecordItemWithImageViewModel>(DbContext.Database, sql, parameters);

            if (null == recordItemlist || recordItemlist.First() == null)
            {
                PageData<TispRecordItemDetailViewModel> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
             var q = from b in recordItemlist
                     group b by b.C_ID into g
                    select new TispRecordItemDetailViewModel
                    {
                        C_ID = g.Key,
                        C_RecordCode = g.First().C_RecordCode,
                        C_ContentCode = g.First().C_ContentCode,
                        C_SpotContentCode = g.First().C_SpotContentCode,
                        C_InspectionContent = g.First().C_InspectionContent,
                        C_Remark = g.First().C_Remark,
                        C_Status = g.First().C_Status,
                        C_AlarmLevel = g.First().C_AlarmLevel,
                        C_Name = g.First().C_Name,
                        D_CreateOn = g.First().D_CreateOn,
                        D_LastUpdatedOn = g.First().D_LastUpdatedOn,
                        C_CreateBy = g.First().C_CreateBy,
                        C_LastUpdatedBy = g.First().C_LastUpdatedBy,
                        SpotName = g.First().SpotName,
                        SpotNumber = g.First().SpotNumber,
                        ReportUserName = g.First().ReportUserName,
                        RecordUserName = g.First().RecordUserName,
                        SpotAreaName = g.First().SpotAreaName,
                        RecordImageList = (from c in g select new RecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus }).ToList<RecordImage>(),
                    };
            PageData<TispRecordItemDetailViewModel> pageData = new PageData<TispRecordItemDetailViewModel>
            {
                Totals = q.ToList().Count,
                Rows = searchModel.IsPagination ? q.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize).ToList() : q.ToList()

            };
            return Task.FromResult(pageData);
        }

        public Task<int> GetAlarmRecordsCountAsync(TispRecordAlarmSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("AreaCode", searchModel.C_AreaCode),
                new MySqlConnector.MySqlParameter("start", searchModel.Start),
                new MySqlConnector.MySqlParameter("end", searchModel.End),
                new MySqlConnector.MySqlParameter("spotId", searchModel.SpotId),
                new MySqlConnector.MySqlParameter("createUserId ", searchModel.CreateUserId)};
            string sql = "SELECT COUNT(C_ID) FROM TISP_RecordItem  WHERE C_Status = '0' or C_Status = '3'";
            sql = @"SELECT COUNT(A.C_ID) FROM TISP_RecordItem A 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
WHERE (A.C_Status = '0' or A.C_Status = '3') AND E.C_Status = '1' AND E.C_StoreCode =@StoreCode";

            sql = @"SELECT count(A.C_ID) 
FROM TISP_RecordItem A 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy   WHERE A.C_Status = '0'  AND A.C_LastUpdatedBy IS NULL  AND E.C_Status = '1'";
            if (!string.IsNullOrEmpty(searchModel.C_StoreCode))
            {
                sql += " AND E.C_StoreCode =@StoreCode ";
            }
            if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
            {
                sql += " AND E.C_AreaCode =@AreaCode ";
            }
            if (!string.IsNullOrEmpty(searchModel.SpotId))
            {
                sql += " AND D.C_SpotCode =@spotId ";
            }
            if (!string.IsNullOrEmpty(searchModel.CreateUserId))
            {
                sql += " AND D.C_CreateBy =@createUserId ";
            }
            //if (!string.IsNullOrEmpty(searchModel.UpdateUserId))
            //{
            //    sql += " AND D.C_LastUpdatedBy =@updateUserId ";
            //}
            if (searchModel.Start != Convert.ToDateTime("0001-01-01 00:00:00") && searchModel.End != Convert.ToDateTime("0001-01-01 00:00:00"))
            {
                sql += " AND D.D_CreateOn between @start and @end";
            }
            object result = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).Rows[0][0];
            return Task.FromResult(int.Parse(result.ToString()));
        }
        public Task<int> GetAlarmRecoveryRecordsCountAsync(TispRecordAlarmSearchModel searchModel)
        {
            //            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode) };
            //            string sql = "SELECT COUNT(C_ID) FROM TISP_RecordItem  WHERE C_Status = '2'";
            //            sql = @"SELECT COUNT(A.C_ID)
            //FROM TISP_RecordItem A 
            //LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode 
            //LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
            //WHERE A.C_Status = '2'  AND E.C_Status = '1' AND E.C_StoreCode =@StoreCode";
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
                new MySqlConnector.MySqlParameter("AreaCode", searchModel.C_AreaCode),
                new MySqlConnector.MySqlParameter("start", searchModel.Start),
                new MySqlConnector.MySqlParameter("end", searchModel.End),
                new MySqlConnector.MySqlParameter("spotId", searchModel.SpotId),
                new MySqlConnector.MySqlParameter("createUserId ", searchModel.CreateUserId),
                new MySqlConnector.MySqlParameter("updateUserId  ", searchModel.UpdateUserId )};
            //string sql = "SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID WHERE A.C_Status = '2'";
            string sql = "SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,F.C_Name AS ReportUserName ,G.C_Name AS RecordUserName FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User G ON G.C_UserID = A.C_LastUpdatedBy  WHERE A.C_Status = '2' AND E.C_Status = '1'  AND E.C_StoreCode =@StoreCode ";
            sql = @"SELECT COUNT(A.C_ID) 
FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy 
LEFT JOIN TSYS_User G ON G.C_UserID = A.C_CreateBy 
WHERE (A.C_Status = '2' || A.C_Status = '3' || A.C_Status = '4') AND E.C_Status = '1'  AND E.C_StoreCode =@StoreCode ";
            if (!string.IsNullOrEmpty(searchModel.SpotId))
            {
                sql += " AND D.C_SpotCode =@spotId ";
            }
            if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
            {
                sql += " AND E.C_AreaCode =@AreaCode ";
            }
            if (!string.IsNullOrEmpty(searchModel.CreateUserId))
            {
                sql += " AND A.C_CreateBy =@createUserId ";
            }
            if (!string.IsNullOrEmpty(searchModel.UpdateUserId))
            {
                sql += " AND A.C_CreateBy =@updateUserId ";
            }
            if (searchModel.Start != DateTime.MinValue && searchModel.End != DateTime.MinValue)
            {
                sql += " AND A.D_CreateOn between @start and @end";
            }
            object result = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).Rows[0][0];
            return Task.FromResult(int.Parse(result.ToString()));
        }
        public Task<IEnumerable<TispRecordItemDetailViewModel>> GetRecordItemByIdAsync(Guid id)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("id", id) };
            string sql = @"SELECT A.*,S.C_Name as SpotName,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus FROM TISP_RecordItem A 
LEFT JOIN TISP_Record R on (A.C_RecordCode=R.C_ID)
LEFT JOIN TISP_Spot S on (R.C_SpotCode=S.C_Code)
LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID 
LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID 
WHERE A.C_ID = @id";
            IEnumerable<TispRecordItemWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TispRecordItemWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                IEnumerable<TispRecordItemDetailViewModel> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
            var q = from b in recordItemlist
                    group b by b.C_ID into g
                    select new TispRecordItemDetailViewModel
                    {
                        C_ID = g.Key,
                        C_RecordCode = g.First().C_RecordCode,
                        SpotName=g.First().SpotName,
                        C_ContentCode = g.First().C_ContentCode,
                        C_SpotContentCode = g.First().C_SpotContentCode,
                        C_InspectionContent = g.First().C_InspectionContent,
                        C_Remark = g.First().C_Remark,
                        C_Status = g.First().C_Status,
                        C_AlarmLevel = g.First().C_AlarmLevel,
                        C_Name = g.First().C_Name,
                        D_CreateOn = g.First().D_CreateOn,
                        D_LastUpdatedOn = g.First().D_LastUpdatedOn,
                        RecordImageList = (from c in g select new RecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus }).ToList<RecordImage>(),
                    };

            return Task.FromResult(q);
        }
        public Task<IEnumerable<TispRecordItemAlarmDetailViewModel>> GetAlarmRecordsByQRCodeAsync(string QRCode, string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("QRCode", QRCode), new MySqlConnector.MySqlParameter("storeCode", storeCode) };
            string sql = "SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,E.C_Position AS SpotPosition ,F.C_Name AS ReportUserName FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy WHERE A.C_Status = '0' AND E.C_QRCode = @QRCode AND E.C_StoreCode =@storeCode ORDER BY A.D_CreateOn ASC";
            sql = @"SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,E.C_Position AS SpotPosition ,F.C_Name AS ReportUserName 
FROM TISP_RecordItem A 
LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID 
LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy 
WHERE A.C_Status = '0' AND A.C_LastUpdatedBy IS NULL AND E.C_QRCode = @QRCode AND E.C_StoreCode =@storeCode ORDER BY A.D_CreateOn ASC";
            sql = @"SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,E.C_Position AS SpotPosition ,F.C_Name AS ReportUserName 
FROM TISP_RecordItem A 
LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID 
LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy 
WHERE A.C_Status = '3' AND A.C_LastUpdatedBy IS NULL AND E.C_QRCode = @QRCode AND E.C_StoreCode =@storeCode ORDER BY A.D_CreateOn ASC";
            IEnumerable<TispRecordItemWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TispRecordItemWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                IEnumerable<TispRecordItemAlarmDetailViewModel> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
            var q = from b in recordItemlist
                    group b by b.C_ID into g
                    select new TispRecordItemAlarmDetailViewModel
                    {
                        C_ID = g.Key,
                        C_RecordCode = g.First().C_RecordCode,
                        C_ContentCode = g.First().C_ContentCode,
                        C_SpotContentCode = g.First().C_SpotContentCode,
                        C_InspectionContent = g.First().C_InspectionContent,
                        C_Remark = g.First().C_Remark,
                        C_Status = g.First().C_Status,
                        C_AlarmLevel = g.First().C_AlarmLevel,
                        C_Name = g.First().C_Name,
                        D_CreateOn = g.First().D_CreateOn,
                        SpotName = g.First().SpotName,
                        SpotNumber = g.First().SpotNumber,
                        SpotPosition = g.First().SpotPosition,
                        ReportUserName = g.First().ReportUserName,
                        RecordImageList = (from c in g select new RecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus }).ToList<RecordImage>(),
                    };

            return Task.FromResult(q);
        }
        public Task<IEnumerable<TispRecordItemAlarmDetailViewModel>> GetAlarmRecordsByRecordItemIdAsync(Guid itemId)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("ItemId", itemId) };
            string sql = "SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_StoreCode,E.C_QRCode,E.C_Number AS SpotNumber,E.C_Position AS SpotPosition ,F.C_Name AS ReportUserName,F.C_Mobile AS ReportUserMobile FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy WHERE A.C_ID  = @ItemId ";
            IEnumerable<TispRecordItemWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TispRecordItemWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                IEnumerable<TispRecordItemAlarmDetailViewModel> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
            var q = from b in recordItemlist
                    group b by b.C_ID into g
                    select new TispRecordItemAlarmDetailViewModel
                    {
                        C_ID = g.Key,
                        C_RecordCode = g.First().C_RecordCode,
                        C_ContentCode = g.First().C_ContentCode,
                        C_SpotContentCode = g.First().C_SpotContentCode,
                        C_InspectionContent = g.First().C_InspectionContent,
                        C_Remark = g.First().C_Remark,
                        C_Status = g.First().C_Status,
                        C_AlarmLevel = g.First().C_AlarmLevel,
                        C_Name = g.First().C_Name,
                        D_CreateOn = g.First().D_CreateOn,
                        SpotName = g.First().SpotName,
                        C_StoreCode = g.First().C_StoreCode,
                        C_QRCode= g.First().C_QRCode,
                        SpotNumber = g.First().SpotNumber,
                        SpotPosition = g.First().SpotPosition,
                        ReportUserName = g.First().ReportUserName,
                        ReportUserMobile = g.First().ReportUserMobile,
                        RecordImageList = (from c in g select new RecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus }).ToList<RecordImage>(),
                    };

            return Task.FromResult(q);
        }
        public Task<IEnumerable<TispRecordItemAlarmDetailViewModel>> GetNewRecordByQRCodeAsync(string QRCode, string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("QRCode", QRCode), new MySqlConnector.MySqlParameter("storeCode", storeCode) };
            //string sql = "SELECT * From (SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,E.C_Position AS SpotPosition ,F.C_Name AS ReportUserName,D.D_CreateOn AS ReportCreateOn,G.C_Status AS SecurityStatus  FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy LEFT JOIN TSEC_RecordItem G ON G.C_RecordItemCode = A.C_ID  WHERE E.C_QRCode = @QRCode order by D.D_CreateOn desc )R GROUP BY R.C_RecordCode";
            //string sql = "SELECT * FROM (SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,E.C_Position AS SpotPosition ,F.C_Name AS ReportUserName,D.D_CreateOn AS ReportCreateOn,G.C_Status AS SecurityStatus  FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID LEFT JOIN (SELECT * From TISP_Record ORDER BY D_CreateOn DESC limit 1) D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy LEFT JOIN TSEC_RecordItem G ON G.C_RecordItemCode = A.C_ID  WHERE E.C_QRCode = @QRCode ORDER BY A.D_CreateOn DESC )AA  ";
            string sql = "SELECT * FROM (SELECT  (@i:=@i+1) as pm , A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,E.C_Position AS SpotPosition ,F.C_Name AS ReportUserName,D.D_CreateOn AS ReportCreateOn,G.C_Status AS SecurityStatus  FROM (SELECT @i:=0) AS i, TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID INNER JOIN (SELECT A.* FROM TISP_Record A,(SELECT C_SpotCode, max(D_CreateOn) maxTime FROM TISP_Record GROUP BY C_SpotCode) B WHERE A.C_SpotCode = B.C_SpotCode AND A.D_CreateOn = B.maxTime)D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy LEFT JOIN TISP_SecurityItem G ON G.C_RecordItemCode = A.C_ID WHERE E.C_QRCode =@QRCode AND E.C_StoreCode =@storeCode )AA GROUP BY IFNULL(AA.C_RecordImageId,UUID())";
            //string sql = "SELECT * FROM (SELECT A.*,B.C_AlarmLevel,B.C_Name,C.C_ID AS C_RecordImageId ,C.C_ImageURL,C.C_Status AS RecordImageStatus,E.C_Name AS SpotName,E.C_Number AS SpotNumber,E.C_Position AS SpotPosition ,F.C_Name AS ReportUserName,D.D_CreateOn AS ReportCreateOn,G.C_Status AS SecurityStatus  FROM TISP_RecordItem A LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID LEFT JOIN TISP_RecordImage C ON C.C_RecordItemCode = A.C_ID INNER JOIN (SELECT A.* FROM TISP_Record A,(SELECT C_SpotCode, max(D_CreateOn) maxTime FROM TISP_Record GROUP BY C_SpotCode) B WHERE A.C_SpotCode = B.C_SpotCode AND A.D_CreateOn = B.maxTime)D ON D.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = A.C_CreateBy LEFT JOIN TSEC_RecordItem G ON G.C_RecordItemCode = A.C_ID WHERE E.C_QRCode =@QRCode)AA";
            IEnumerable<TispRecordItemWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TispRecordItemWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                IEnumerable<TispRecordItemAlarmDetailViewModel> recordItemDetail = new List<TispRecordItemAlarmDetailViewModel>() { };
                return Task.FromResult(recordItemDetail);
            }
            var q = from b in recordItemlist
                    group b by b.C_ID into g
                    select new TispRecordItemAlarmDetailViewModel
                    {
                        C_ID = g.First().C_ID,
                        C_RecordCode = g.First().C_RecordCode,
                        C_ContentCode = g.First().C_ContentCode,
                        C_SpotContentCode = g.First().C_SpotContentCode,
                        C_InspectionContent = g.First().C_InspectionContent,
                        C_Remark = g.First().C_Remark,
                        C_Status = g.First().C_Status,
                        C_AlarmLevel = g.First().C_AlarmLevel,
                        C_Name = g.First().C_Name,
                        D_CreateOn = g.First().D_CreateOn,
                        D_LastUpdatedOn = g.First().D_LastUpdatedOn,
                        C_CreateBy = g.First().C_CreateBy,
                        C_LastUpdatedBy = g.First().C_LastUpdatedBy,
                        SpotName = g.First().SpotName,
                        SpotNumber = g.First().SpotNumber,
                        SpotPosition = g.First().SpotPosition,
                        ReportUserName = g.First().ReportUserName,
                        SecurityStatus = g.First().SecurityStatus,
                        RecordImageList = (from c in g select new RecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus }).ToList<RecordImage>(),
                    };
             return Task.FromResult(q);
        }

        /// <summary>
        /// 防火检查记录表（周）
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public Task<IEnumerable<FirePreventionWeekRecordViewModel>> GetFirePreventionWeekRecordAsync(DateTime start,DateTime end, string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("start", start), new MySqlConnector.MySqlParameter("end", end), new MySqlConnector.MySqlParameter("storeCode", storeCode) };
            //string sql = "SELECT A.G_ID,group_concat(C.C_Status) AS CheckSituation, group_concat(CASE C.C_Status when '3' THEN  CONCAT(E.C_Number ,':', E.C_Name,'异常未处理',C.C_InspectionContent) WHEN '2' THEN CONCAT(E.C_Number ,':', E.C_Name,'已处理',C.C_InspectionContent) END) AS Result ,A.C_Name FROM TISP_ContentGroup A LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID LEFT JOIN TISP_RecordItem C ON C.C_ContentCode = B.G_ContentCode LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ContentCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode WHERE A.C_Status = '1' AND E.C_Number is NOT NULL AND E.C_Name IS NOT NULL AND C.D_CreateOn BETWEEN  @start and @end  GROUP BY IFNULL(A.G_ID,UUID())";            
            //string sql = "SELECT A.G_ID , C.C_Status ,E.C_Number ,E.C_Name AS SpotName,C.C_InspectionContent,A.C_Name FROM TISP_ContentGroup A LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID LEFT JOIN TISP_RecordItem C ON C.C_ContentCode = B.G_ContentCode LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ContentCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode WHERE A.C_Status = '1' AND E.C_Number is NOT NULL AND E.C_Name IS NOT NULL AND C.D_CreateOn BETWEEN  @start and @end ";
            string sql = @"SELECT A.G_ID,A.C_Name, G.C_Status ,E.C_Number ,E.C_Name AS SpotName,C.C_Name AS C_InspectionContent FROM TISP_ContentGroup A 
LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID 
LEFT JOIN TISP_Content C ON C.C_ID = B.G_ContentCode
LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ID 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TISP_Record F ON F.C_SpotCode = E.C_Code
LEFT JOIN TISP_RecordItem G ON G.C_RecordCode = F.C_ID 
WHERE A.C_Status = '1' AND A.I_Type = 1 AND (G.D_CreateOn BETWEEN  @start and @end OR G.C_Status IS NULL)
";
            sql = @"SELECT A.G_ID,A.C_Name, G.C_Status ,E.C_Number ,E.C_Name AS SpotName,C.C_Name AS C_InspectionContent FROM TISP_ContentGroup A 
LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID 
LEFT JOIN TISP_Content C ON C.C_ID = B.G_ContentCode
LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ID 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TISP_Record F ON F.C_SpotCode = E.C_Code
LEFT JOIN TISP_RecordItem G ON G.C_RecordCode = F.C_ID 
WHERE A.C_Status = '1' AND A.I_Type = 1 AND (G.D_CreateOn BETWEEN  @start and @end)
";
            sql = @"SELECT A.G_ID,A.C_Name, M.C_Status ,M.C_Number , SpotName,H.C_Name AS C_InspectionContent 
FROM TISP_ContentGroup A 
LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID
LEFT JOIN TISP_Content H ON H.C_ID = B.G_ContentCode
LEFT JOIN
(
SELECT B.C_ID,A.C_Status ,A.D_CreateOn,A.C_InspectionContent AS AbnormalContent,A.C_Remark,E.C_Number ,E.C_Name AS SpotName,H.C_Name AS UserName
FROM TISP_RecordItem A 
LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode
LEFT JOIN TSYS_User H ON H.C_UserID = A.C_CreateBy
WHERE A.D_CreateOn BETWEEN  @start and @end AND E.C_StoreCode =@storeCode
)M
ON M.C_ID = H.C_ID
WHERE A.C_Status = '1' AND A.I_Type = 1 AND A.C_StoreCode =@storeCode ";
            IEnumerable <FirePreventionWeekRecordDto> recordItemlist = EntityFrameworkCoreExtensions.GetList2<FirePreventionWeekRecordDto>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                IEnumerable<FirePreventionWeekRecordViewModel> recordItemDetail = new List<FirePreventionWeekRecordViewModel>() { };
                return Task.FromResult(recordItemDetail);
            }
            else
            {
                var q = from b in recordItemlist
                        group b by b.G_ID into g
                        select new FirePreventionWeekRecordViewModel
                        {
                            G_ID = g.First().G_ID,
                            CheckSituation = g.Any(i=>i.C_Status =="3" || i.C_Status == "2" ) ?"异常":"正常", 
                            C_Name = g.First().C_Name,
                            Result = MergeExcResult(from c in g where (c.C_Status == "3" || c.C_Status == "2") select c), 
                        };


                return Task.FromResult(q);
            }   
        }
        private string MergeExcResult(IEnumerable<FirePreventionWeekRecordDto> iArray)
        {
            //StringBuilder sb = new StringBuilder();
            string sb = string.Empty;
            foreach (var v in iArray)
            {
                //sb.Append(v.C_Number);
                //sb.Append(":");
                //sb.Append(v.C_Name);
                //sb.Append(v.C_InspectionContent);
                //sb.Append(" ");
                if (v.C_Status == "3")
                {
                    sb = "未处理";
                    break;
                }
                else if(v.C_Status == "2")
                {
                    sb = "已处理";
                }
            }
            return sb;
        }

        /// <summary>
        /// 消防设施每周检查记录
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public Task<IEnumerable<FireFightingFacilitiesWeekRecordViewModel>> GetFireFightingFacilitiesAsync(DateTime start, DateTime end, string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("start", start), new MySqlConnector.MySqlParameter("end", end), new MySqlConnector.MySqlParameter("storeCode", storeCode) };
            //string sql = "SELECT A.G_ID , A.C_PatrolItem,A.C_Name, C.C_Status ,E.C_Number ,E.C_Name AS SpotName,C.C_InspectionContent,C.C_Remark FROM TISP_ContentGroup A LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID LEFT JOIN TISP_RecordItem C ON C.C_ContentCode = B.G_ContentCode LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ContentCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode WHERE A.C_Status = '1' AND A.I_Type = 3 AND C.D_CreateOn BETWEEN  @start and @end ";
            string sql = @"SELECT A.G_ID,A.C_PatrolItem,A.C_Name, G.C_Status ,E.C_Number ,E.C_Name AS SpotName,C.C_Name AS C_InspectionContent,C.C_Remark FROM TISP_ContentGroup A 
LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID 
LEFT JOIN TISP_Content C ON C.C_ID = B.G_ContentCode
LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ID 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TISP_Record F ON F.C_SpotCode = E.C_Code
LEFT JOIN TISP_RecordItem G ON G.C_RecordCode = F.C_ID 
WHERE A.C_Status = '1' AND A.I_Type = 3 AND (G.D_CreateOn BETWEEN  @start and @end OR G.C_Status IS NULL)
";
            sql = @"SELECT A.G_ID,A.C_PatrolItem,A.C_Name, G.C_Status ,E.C_Number ,E.C_Name AS SpotName,C.C_Name AS C_InspectionContent,C.C_Remark FROM TISP_ContentGroup A 
LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID 
LEFT JOIN TISP_Content C ON C.C_ID = B.G_ContentCode
LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ID 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TISP_Record F ON F.C_SpotCode = E.C_Code
LEFT JOIN TISP_RecordItem G ON G.C_RecordCode = F.C_ID 
WHERE A.C_Status = '1' AND A.I_Type = 3 AND (G.D_CreateOn BETWEEN  @start and @end)
";
            sql = @"SELECT A.G_ID,A.C_PatrolItem,A.C_Name, M.C_Status ,M.C_Number ,M.SpotName,H.C_Name AS C_InspectionContent,H.C_Remark
FROM TISP_ContentGroup A 
LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID
LEFT JOIN TISP_Content H ON H.C_ID = B.G_ContentCode
LEFT JOIN
(
SELECT B.C_ID,A.C_Status,E.C_Number ,E.C_Name AS SpotName
FROM TISP_RecordItem A 
LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode
WHERE (A.C_Status = '2' OR A.C_Status = '3') AND E.C_Status = '1' AND E.C_StoreCode =@storeCode  AND (A.D_CreateOn BETWEEN  @start and @end )
)M
ON M.C_ID = H.C_ID
WHERE A.C_Status = '1' AND A.I_Type = 3 AND A.C_StoreCode = @storeCode ";
            IEnumerable<FireFightingFacilitiesWeekRecordDto> recordItemlist = EntityFrameworkCoreExtensions.GetList2<FireFightingFacilitiesWeekRecordDto>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                IEnumerable<FireFightingFacilitiesWeekRecordViewModel> recordItemDetail = new List<FireFightingFacilitiesWeekRecordViewModel>() { };
                return Task.FromResult(recordItemDetail);
            }
            else
            {
                //var q = from b in recordItemlist
                //        group b by b.G_ID into g
                //        select new FireFightingFacilitiesWeekRecordViewModel
                //        {
                //            C_PatrolItem = g.First().C_PatrolItem,
                //            FireFightingFacilitiesItem = from d in g group d by d.C_PatrolItem into f select new FireFightingFacilitiesItem
                //            {
                //                G_ID = f.First().G_ID,
                //                CheckSituation = f.Any(i => i.C_Status == "3") ? "异常" : "正常",
                //                C_Name = f.First().C_Name,
                //                C_PatrolItem = f.First().C_PatrolItem,
                //                Result = MergeExcResult(from c in f where c.C_Status == "3" select c),
                //            }
                //        };

                var q = from b in recordItemlist
                        group b by b.C_PatrolItem into g
                        select new FireFightingFacilitiesWeekRecordViewModel
                        {
                            C_PatrolItem = g.First().C_PatrolItem,
                            FireFightingFacilitiesItem = from d in g
                                                         group d by d.G_ID into f
                                                         select new FireFightingFacilitiesItem
                                                         {
                                                             G_ID = f.First().G_ID,
                                                             CheckSituation = f.Any(i => i.C_Status == "3") ? "异常" : "正常",
                                                             C_Name = f.First().C_Name,
                                                             C_PatrolItem = f.First().C_PatrolItem,
                                                             Result = MergeExcResult(from c in f where (c.C_Status == "3" || c.C_Status == "2") select c),
                                                         }
                        };

                return Task.FromResult(q);
            }
        }

        private string MergeExcResult(IEnumerable<FireFightingFacilitiesWeekRecordDto> iArray)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var v in iArray)
            {
                if (v.C_Status == "3")
                {
                    sb.Append(v.C_Number);
                    sb.Append(":");
                    sb.Append(v.C_Name);
                    sb.Append(v.C_InspectionContent);
                    sb.Append(v.C_Remark);
                    sb.Append(",未处理");
                    sb.Append("; \r\n");
                }
                if (v.C_Status == "2")
                {
                    sb.Append(v.C_Number);
                    sb.Append(":");
                    sb.Append(v.C_Name);
                    sb.Append(v.C_InspectionContent);
                    sb.Append(v.C_Remark);
                    sb.Append(",已处理");
                    sb.Append("; \r\n");
                }
            }
            return sb.ToString();
        }
        /// <summary>
        /// 防火巡查记录表
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public Task<IEnumerable<FireInspectionRecordViewModel>> GetFireInspectionRecordAsync(DateTime start, DateTime end, string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("start", start), new MySqlConnector.MySqlParameter("end", end), new MySqlConnector.MySqlParameter("storeCode", storeCode) };

            //string sql = "SELECT A.G_ID , A.C_PatrolItem,A.C_Name, C.C_Status ,C.D_CreateOn,E.C_Number ,E.C_Name AS SpotName,C.C_InspectionContent,C.C_Remark,F.C_Name AS UserName FROM TISP_ContentGroup A LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID LEFT JOIN TISP_RecordItem C ON C.C_ContentCode = B.G_ContentCode LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ContentCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = C.C_CreateBy WHERE A.C_Status = '1' AND A.I_Type = 4 AND C.D_CreateOn BETWEEN  @start and @end ORDER BY C.D_CreateOn ASC ";
            //            string sql = @"SELECT 
            //C_Name,D_CreateOn,UserName,sx AS CreateDate,group_concat(C_InspectionContent,',') AS GroupInspectionContent,group_concat(C_Remark,',') AS GroupRemark,
            //GROUP_CONCAT(fhm)  as 'Fhm',
            //GROUP_CONCAT(fhmZt)  as 'FhmZt',
            //GROUP_CONCAT(bzbs)  as 'Bzbs',
            //GROUP_CONCAT(bzbsZt)  as 'BzbsZt',
            //GROUP_CONCAT(zdbw)  as 'Zdbw',
            //GROUP_CONCAT(zdbwZt)  as 'ZdbwZt',
            //GROUP_CONCAT(sdwz)  as 'Sdwz',
            //GROUP_CONCAT(sdwzZt)  as 'SdwzZt',
            //GROUP_CONCAT(sstd)  as 'Sstd',
            //GROUP_CONCAT(sstdZt)  as 'SstdZt',
            //GROUP_CONCAT(xfqc)  as 'Xfqc',
            //GROUP_CONCAT(xfqcZt)  as 'XfqcZt',
            //GROUP_CONCAT(aqck)  as 'Aqck',
            //GROUP_CONCAT(aqckZt)  as 'AqckZt'

            //FROM

            //(
            //SELECT 
            //   C_Name,C_Status,D_CreateOn,UserName,C_InspectionContent,C_Remark,
            //LOWER(DATE_FORMAT(D_CreateOn,'%Y-%m-%d %p')) as sx,
            //CASE WHEN C_Name ='防火门' THEN C_Name ELSE NULL END AS fhm,
            //CASE WHEN C_Name ='防火门' THEN C_Status ELSE NULL END AS fhmZt,
            //CASE WHEN C_Name ='标识标志' THEN C_Name ELSE NULL END AS bzbs,
            //CASE WHEN C_Name ='标识标志' THEN C_Status ELSE NULL END AS bzbsZt,
            //CASE WHEN C_Name ='重点部位' THEN C_Name ELSE NULL END AS zdbw,
            //CASE WHEN C_Name ='重点部位' THEN C_Status ELSE NULL END AS zdbwZt,
            //CASE WHEN C_Name ='水电违章' THEN C_Name ELSE NULL END AS sdwz,
            //CASE WHEN C_Name ='水电违章' THEN C_Status ELSE NULL END AS sdwzZt,
            //CASE WHEN C_Name ='安全出口' THEN C_Name ELSE NULL END AS aqck,
            //CASE WHEN C_Name ='安全出口' THEN C_Status ELSE NULL END AS aqckZt,
            //CASE WHEN C_Name ='疏散通道' THEN C_Name ELSE NULL END AS sstd,
            //CASE WHEN C_Name ='疏散通道' THEN C_Status ELSE NULL END AS sstdZt,
            //CASE WHEN C_Name ='消防器材' THEN C_Name ELSE NULL END AS xfqc,
            //CASE WHEN C_Name ='消防器材' THEN C_Status ELSE NULL END AS xfqcZt
            //from
            //(
            //SELECT A.G_ID , A.C_PatrolItem,A.C_Name, C.C_Status ,C.D_CreateOn,E.C_Number ,E.C_Name AS SpotName,C.C_InspectionContent,C.C_Remark,F.C_Name AS UserName FROM TISP_ContentGroup A LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID LEFT JOIN TISP_RecordItem C ON C.C_ContentCode = B.G_ContentCode LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ContentCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = C.C_CreateBy WHERE A.C_Status = '1' AND A.I_Type = 4 
            //)TableA GROUP BY LOWER(DATE_FORMAT(D_CreateOn,'%Y-%m-%d %p')),C_Name ORDER BY D_CreateOn DESC
            //) TableB GROUP BY sx";
            string sql = @"SELECT 
C_Name,D_CreateOn,UserName,sx AS CreateDate,group_concat(C_InspectionContent,',') AS GroupInspectionContent,group_concat(C_Remark,',') AS GroupRemark,
GROUP_CONCAT(fhm)  as 'Fhm',
GROUP_CONCAT(fhmZt)  as 'FhmZt',
GROUP_CONCAT(bzbs)  as 'Bzbs',
GROUP_CONCAT(bzbsZt)  as 'BzbsZt',
GROUP_CONCAT(zdbw)  as 'Zdbw',
GROUP_CONCAT(zdbwZt)  as 'ZdbwZt',
GROUP_CONCAT(sdwz)  as 'Sdwz',
GROUP_CONCAT(sdwzZt)  as 'SdwzZt',
GROUP_CONCAT(sstd)  as 'Sstd',
GROUP_CONCAT(sstdZt)  as 'SstdZt',
GROUP_CONCAT(xfqc)  as 'Xfqc',
GROUP_CONCAT(xfqcZt)  as 'XfqcZt',
GROUP_CONCAT(aqck)  as 'Aqck',
GROUP_CONCAT(aqckZt)  as 'AqckZt'

FROM

(
SELECT 
   C_Name,C_Status,D_CreateOn,UserName,C_InspectionContent,C_Remark,
LOWER(DATE_FORMAT(D_CreateOn,'%Y-%m-%d %p')) as sx,
CASE WHEN C_Name ='防火门' THEN C_Name ELSE NULL END AS fhm,
CASE WHEN C_Name ='防火门' THEN C_Status ELSE NULL END AS fhmZt,
CASE WHEN C_Name ='标识标志' THEN C_Name ELSE NULL END AS bzbs,
CASE WHEN C_Name ='标识标志' THEN C_Status ELSE NULL END AS bzbsZt,
CASE WHEN C_Name ='重点部位' THEN C_Name ELSE NULL END AS zdbw,
CASE WHEN C_Name ='重点部位' THEN C_Status ELSE NULL END AS zdbwZt,
CASE WHEN C_Name ='水电违章' THEN C_Name ELSE NULL END AS sdwz,
CASE WHEN C_Name ='水电违章' THEN C_Status ELSE NULL END AS sdwzZt,
CASE WHEN C_Name ='安全出口' THEN C_Name ELSE NULL END AS aqck,
CASE WHEN C_Name ='安全出口' THEN C_Status ELSE NULL END AS aqckZt,
CASE WHEN C_Name ='疏散通道' THEN C_Name ELSE NULL END AS sstd,
CASE WHEN C_Name ='疏散通道' THEN C_Status ELSE NULL END AS sstdZt,
CASE WHEN C_Name ='消防器材' THEN C_Name ELSE NULL END AS xfqc,
CASE WHEN C_Name ='消防器材' THEN C_Status ELSE NULL END AS xfqcZt
from
(
SELECT A.G_ID,A.C_PatrolItem,A.C_Name, G.C_Status ,G.D_CreateOn,E.C_Number ,E.C_Name AS SpotName,C.C_Name AS C_InspectionContent,C.C_Remark,H.C_Name AS UserName FROM TISP_ContentGroup A 
LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID 
LEFT JOIN TISP_Content C ON C.C_ID = B.G_ContentCode
LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ID 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TISP_Record F ON F.C_SpotCode = E.C_Code
LEFT JOIN TISP_RecordItem G ON G.C_RecordCode = F.C_ID 
LEFT JOIN TSYS_User H ON H.C_UserID = G.C_CreateBy WHERE A.C_Status = '1' AND A.C_StoreCode =@storeCode AND A.I_Type = 4 AND G.D_CreateOn BETWEEN  @start and @end 
)TableA GROUP BY LOWER(DATE_FORMAT(D_CreateOn,'%Y-%m-%d %p')),C_Name ORDER BY D_CreateOn DESC
) TableB GROUP BY sx";
//            sql = @"SELECT 
//C_Name,D_CreateOn,UserName,sx AS CreateDate,group_concat(C_InspectionContent, ',') AS GroupInspectionContent, group_concat(C_Remark, ',') AS GroupRemark, SpotNumber, SpotName,

//   GROUP_CONCAT(fhm)  as 'Fhm',
//GROUP_CONCAT(fhmZt) as 'FhmZt',
//GROUP_CONCAT(bzbs) as 'Bzbs',
//GROUP_CONCAT(bzbsZt) as 'BzbsZt',
//GROUP_CONCAT(zdbw) as 'Zdbw',
//GROUP_CONCAT(zdbwZt) as 'ZdbwZt',
//GROUP_CONCAT(sdwz) as 'Sdwz',
//GROUP_CONCAT(sdwzZt) as 'SdwzZt',
//GROUP_CONCAT(sstd) as 'Sstd',
//GROUP_CONCAT(sstdZt) as 'SstdZt',
//GROUP_CONCAT(xfqc) as 'Xfqc',
//GROUP_CONCAT(xfqcZt) as 'XfqcZt',
//GROUP_CONCAT(aqck) as 'Aqck',
//GROUP_CONCAT(aqckZt) as 'AqckZt'

//FROM

//(
//SELECT
//   C_Name, C_Status, D_CreateOn, UserName, C_InspectionContent, C_Remark, SpotNumber, SpotName, SpotInspection, SpotRemark,
//LOWER(DATE_FORMAT(D_CreateOn, '%Y-%m-%d %p')) as sx,
//CASE WHEN C_Name = '防火门' THEN C_Name ELSE NULL END AS fhm,
//CASE WHEN C_Name = '防火门' THEN C_Status ELSE NULL END AS fhmZt,
//CASE WHEN C_Name = '标识标志' THEN C_Name ELSE NULL END AS bzbs,
//CASE WHEN C_Name = '标识标志' THEN C_Status ELSE NULL END AS bzbsZt,
//CASE WHEN C_Name = '重点部位' THEN C_Name ELSE NULL END AS zdbw,
//CASE WHEN C_Name = '重点部位' THEN C_Status ELSE NULL END AS zdbwZt,
//CASE WHEN C_Name = '水电违章' THEN C_Name ELSE NULL END AS sdwz,
//CASE WHEN C_Name = '水电违章' THEN C_Status ELSE NULL END AS sdwzZt,
//CASE WHEN C_Name = '安全出口' THEN C_Name ELSE NULL END AS aqck,
//CASE WHEN C_Name = '安全出口' THEN C_Status ELSE NULL END AS aqckZt,
//CASE WHEN C_Name = '疏散通道' THEN C_Name ELSE NULL END AS sstd,
//CASE WHEN C_Name = '疏散通道' THEN C_Status ELSE NULL END AS sstdZt,
//CASE WHEN C_Name = '消防器材' THEN C_Name ELSE NULL END AS xfqc,
//CASE WHEN C_Name = '消防器材' THEN C_Status ELSE NULL END AS xfqcZt
//from
//(
//SELECT A.G_ID, A.C_PatrolItem, A.C_Name, G.C_Status, G.D_CreateOn, E.C_Number, E.C_Number AS SpotNumber, E.C_Name AS SpotName, C.C_Name AS SpotInspection, G.C_Remark AS SpotRemark, C.C_Name AS C_InspectionContent, C.C_Remark, H.C_Name AS UserName FROM TISP_ContentGroup A
//LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID
//LEFT JOIN TISP_Content C ON C.C_ID = B.G_ContentCode
//LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ID
//LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode
//LEFT JOIN TISP_Record F ON F.C_SpotCode = E.C_Code
//LEFT JOIN TISP_RecordItem G ON G.C_RecordCode = F.C_ID
//LEFT JOIN TSYS_User H ON H.C_UserID = G.C_CreateBy WHERE A.C_Status = '1' AND A.I_Type = 4 AND(G.C_Status = '2' OR G.C_Status = '3' OR G.C_Status = '1') AND G.D_CreateOn BETWEEN  '2021-11-01' and '2021-12-11' AND H.C_UserID IN('6a3f5fbc-e568-4bd0-a1ff-2ad7f33c773d', '4447b5b0-6777-474b-9e6b-bf02af864829', 'dc81542d-1b98-4b94-836a-74167189fb5d', '6e4b713b-82cd-491e-8b80-bcdec08f33cb')
//)TableA GROUP BY LOWER(DATE_FORMAT(D_CreateOn, '%Y-%m-%d %p')), C_Name ORDER BY D_CreateOn DESC
//) TableB GROUP BY sx";
            IEnumerable<FireInspectionRecordViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<FireInspectionRecordViewModel>(DbContext.Database, sql, parameters);
            return Task.FromResult(recordItemlist);
            //IEnumerable<FireInspectionRecordDto> recordItemlist = EntityFrameworkCoreExtensions.GetList<FireInspectionRecordDto>(DbContext.Database, sql, parameters);

            //if (null == recordItemlist || recordItemlist.First() == null)
            //{
            //    IEnumerable<FireInspectionRecordViewModel> recordItemDetail = new List<FireInspectionRecordViewModel>() { };
            //    return Task.FromResult(recordItemDetail);
            //}
            //else
            //{
            //    var q = from b in recordItemlist
            //            group b by b.G_ID into g
            //            select new FireInspectionRecordViewModel
            //            {
            //                G_ID = g.First().G_ID,
            //                CheckSituation = g.Any(i => i.C_Status == "2") ? "异常" : "正常",
            //                UserName = g.First().UserName,
            //                D_CreateOn = g.First().D_CreateOn,
            //                Result = MergeExcResult(from c in g where c.C_Status == "2" select c),
            //            };


            //    return Task.FromResult(q);
            //}
        }

        //private string MergeExcResult(IEnumerable<FireInspectionRecordDto> iArray)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    foreach (var v in iArray)
        //    {
        //        sb.Append(v.C_Number);
        //        sb.Append(":");
        //        sb.Append(v.C_Name);
        //        sb.Append(v.C_InspectionContent);
        //        sb.Append(v.C_Remark);
        //        sb.Append(" ");
        //    }
        //    return sb.ToString();
        //}

        /// <summary>
        /// 防患整改验收单
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public Task<HiddenDangerRectificationAcceptanceFormViewModel> HiddenDangerRectificationAcceptanceForm(DateTime start, DateTime end, string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("start", start), new MySqlConnector.MySqlParameter("end", end), new MySqlConnector.MySqlParameter("storeCode", storeCode) };

            //string sql = "SELECT A.G_ID , A.C_PatrolItem,A.C_Name, C.C_Status ,C.D_CreateOn,E.C_Number ,E.C_Name AS SpotName,C.C_InspectionContent,C.C_Remark,F.C_Name AS UserName FROM TISP_ContentGroup A LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID LEFT JOIN TISP_RecordItem C ON C.C_ContentCode = B.G_ContentCode LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ContentCode LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode LEFT JOIN TSYS_User F ON F.C_UserID = C.C_CreateBy WHERE A.C_Status = '1' AND A.I_Type = 2 AND C.C_Status = '2' AND C.D_CreateOn BETWEEN  @start and @end ";
            string sql = @"SELECT A.G_ID,A.C_PatrolItem,A.C_Name, G.C_Status ,G.D_CreateOn,E.C_Number ,E.C_Name AS SpotName,C.C_Name AS C_InspectionContent,G.C_InspectionContent AS AbnormalContent,G.C_Remark,H.C_Name AS UserName FROM TISP_ContentGroup A 
LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID 
LEFT JOIN TISP_Content C ON C.C_ID = B.G_ContentCode
LEFT JOIN TISP_SpotContent D ON D.C_ContentCode = C.C_ID 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode 
LEFT JOIN TISP_Record F ON F.C_SpotCode = E.C_Code
LEFT JOIN TISP_RecordItem G ON G.C_RecordCode = F.C_ID 
LEFT JOIN TSYS_User H ON H.C_UserID = G.C_CreateBy
WHERE A.C_Status = '1' AND A.I_Type = 2 AND G.C_Status = '2' OR G.C_Status = '3' AND (G.D_CreateOn BETWEEN  @start and @end OR G.C_Status IS NULL)
";
            sql = @"SELECT A.G_ID,A.C_PatrolItem,A.C_Name, M.C_Status ,M.D_CreateOn,M.C_Number ,SpotName,H.C_Name AS C_InspectionContent,M.AbnormalContent,M.C_Remark,M.UserName
FROM TISP_ContentGroup A 
LEFT JOIN TISP_ContentGroupItem B ON B.G_ContentGroupCode = A.G_ID
LEFT JOIN TISP_Content H ON H.C_ID = B.G_ContentCode
LEFT JOIN
(
SELECT B.C_ID,A.C_Status ,A.D_CreateOn,A.C_InspectionContent AS AbnormalContent,A.C_Remark,E.C_Number ,E.C_Name AS SpotName,H.C_Name AS UserName
FROM TISP_RecordItem A 
LEFT JOIN TISP_Content B ON A.C_ContentCode = B.C_ID 
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode
LEFT JOIN TSYS_User H ON H.C_UserID = A.C_CreateBy
WHERE A.C_Status = '2' OR A.C_Status = '3' AND (A.D_CreateOn BETWEEN  @start and @end )AND B.C_StoreCode =@storeCode
)M
ON M.C_ID = H.C_ID
WHERE A.C_Status = '1' AND A.I_Type = 2 AND A.C_StoreCode =@storeCode";
            IEnumerable<HiddenDangerRectificationAcceptanceFormDto> recordItemlist = EntityFrameworkCoreExtensions.GetList2<HiddenDangerRectificationAcceptanceFormDto>(DbContext.Database, sql, parameters);
            HiddenDangerRectificationAcceptanceFormViewModel record = new HiddenDangerRectificationAcceptanceFormViewModel() { };
            if (null == recordItemlist || recordItemlist.First() == null)
            {   
                return Task.FromResult(record);
            }
            record.RectificationContent = new List<string>();
            record.Implementation = new List<string>();
            //record.RectificationContent = recordItemlist.Select (i => i.C_Number + " "+ i.SpotName + " " + i.C_InspectionContent + " " + i.AbnormalContent).ToArray();
            //record.Implementation = recordItemlist.Select(i => i.C_Number + " " + i.SpotName + " " + i.C_InspectionContent + " " + i.C_Remark).ToArray();
            //return Task.FromResult(record);

            foreach (var item in recordItemlist)
            {
                if (item.C_Status == "3" || item.C_Status == "2")
                {
                    string rectification = item.C_Number + " " + item.SpotName + " " + item.C_InspectionContent + " " + item.AbnormalContent;
                    record.RectificationContent.Add(rectification);
                    //IEnumerableExtensions.Add(record.RectificationContent, rectification);
                    string Implementation = rectification + " " + (item.C_Status == "2"?"已整改":"未整改");
                    //IEnumerableExtensions.Add(record.Implementation, Implementation);
                    record.Implementation.Add(Implementation);
                }
            }

            return Task.FromResult(record);
        }

        /// <summary>
        /// 点检工单表记录-type=5
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public Task<IEnumerable<InspectionWorkOrderModel>> GetInspectionWorkOrderAsync(DateTime start, DateTime end, string storeCode, string devCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode), new MySqlConnector.MySqlParameter("devCode", devCode) };
            string sql = @"select G.G_ID,G.C_Name,G.C_Remark,G.C_PatrolItem,H.C_ID as ContentId,H.C_Name as ContentName,H.C_Remark as ContentRemark 
from TISP_ContentGroup G
LEFT JOIN TISP_ContentGroupItem  B ON B.G_ContentGroupCode = G.G_ID
LEFT JOIN TISP_Content H ON H.C_ID = B.G_ContentCode
where G.I_Type=5 and G.C_Status='1' and G.C_StoreCode=@storeCode";
            if (!string.IsNullOrEmpty(devCode))
            {
                sql += " and G.C_DevStoreCode=@devCode  ";
            }
            sql += "  order by G.I_Sort asc";
            IEnumerable<ReportContentGroupModel> recordItemlist = EntityFrameworkCoreExtensions.GetList2<ReportContentGroupModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                IEnumerable<InspectionWorkOrderModel> recordItemDetail = new List<InspectionWorkOrderModel>() { };
                return Task.FromResult(recordItemDetail);
            }
            else
            {
                try
                {
                    var q = from b in recordItemlist
                            group b by b.C_PatrolItem into g
                            select new InspectionWorkOrderModel
                            {
                                PatrolItem = g.First().C_PatrolItem,
                                Remark = string.Join("；", g.Select(x => x.C_Remark).Distinct()),
                                ContentRecordList = GetContentRecord(g.Select(x => x.ContentId).Distinct().ToList(), start, end, storeCode)
                            };

                    return Task.FromResult(q);
                }
                catch (Exception ex)
                {
                    return null;
                }

            }
        }
        private List<InspectionWorkOrderContentRecord> GetContentRecord(List<Guid> ContentId, DateTime start, DateTime end, string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("start", start), new MySqlConnector.MySqlParameter("end", end), new MySqlConnector.MySqlParameter("storeCode", storeCode) };
           string sql = $@"select C.C_ID as ContentId,C.C_Name as ContentName,C.C_Remark as ContentRemark,
T.C_Status,T.MXID,T.D_CreateOn,T.UserName
from  TISP_Content C
LEFT JOIN (SELECT A.C_Status,A.C_ID AS MXID,A.D_CreateOn,A.C_ContentCode,
U.C_Name as UserName 
FROM (((TISP_RecordItem A
LEFT JOIN TISP_Record D ON D.C_ID = A.C_RecordCode) 
LEFT JOIN TISP_Spot E ON E.C_Code = D.C_SpotCode)
LEFT JOIN TSYS_User U ON (A.C_CreateBy=U.C_UserID))
WHERE E.C_Status = '1' AND E.C_StoreCode =@storeCode 
AND (A.D_CreateOn BETWEEN  @start and @end )) T on (C.C_ID=T.C_ContentCode)
where C.C_Status='1' and C.C_ID in ('{string.Join("','", ContentId)}')
order by C.I_Sort asc";
            IEnumerable<ReportContentGroupModel> recordItemlist = EntityFrameworkCoreExtensions.GetList2<ReportContentGroupModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                return null;
            }
            else
            {
                var q = from b in recordItemlist
                        group b by b.ContentName into g
                        select new InspectionWorkOrderContentRecord
                        {
                            Name = g.First().ContentName,
                            Remark=g.First().ContentRemark,
                            CheckStatus=g.OrderByDescending(x=>x.D_CreateOn).First()?.C_Status,
                            UserName= g.Where(x=>!string.IsNullOrEmpty(x.UserName)).Select(x => x.UserName).Distinct().ToList(),
                        };
                return q.ToList();
            }
        }
    }
}