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

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 TsecRecordItemRepository : RepositoryBase<TISP_SecurityItem, Guid>, ITsecRecordItemRepository
    {
        public TsecRecordItemRepository(InspectionDbContext dbContext) : base(dbContext)
        {

        }

        public Task<IEnumerable<TsecRecordItemDetailViewModel>> GetSecurityRecordItemsAsync(TsecRecordItemsSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("start", searchModel.Start),
                new MySqlConnector.MySqlParameter("end", searchModel.End),
                new MySqlConnector.MySqlParameter("ispUserId", searchModel.IspUserId),
                new MySqlConnector.MySqlParameter("secUserId ", searchModel.SecUserId),
            new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode),
            new MySqlConnector.MySqlParameter("AreaCode", searchModel.C_AreaCode)};
            //new MySqlConnector.MySqlParameter("organizeCode", searchModel.OrganizeCode)
            //SELECT A.*,B.C_InspectionContent AS InspectionContent,B.C_Remark AS InspectionRemark,B.C_Status AS InspectionStatus,G.C_Name AS SecurityCreateByName,H.C_Name AS InspectionUserName,D.C_AlarmLevel AS ContentAlarmLevel,D.C_Name AS ContentName,F.C_Name AS SpotName,F.C_ImageUrl AS SpotImage, F.C_Number AS SpotNumber  From TSEC_RecordItem A LEFT JOIN TISP_RecordItem B ON B.C_ID = A.C_RecordItemCode LEFT JOIN TISP_Record C ON C.C_ID = B.C_RecordCode LEFT JOIN TISP_Content D ON B.C_ContentCode = D.C_ID LEFT JOIN TISP_RecordImage E ON E.C_RecordItemCode = B.C_ID LEFT JOIN TISP_Spot F ON F.C_Code = C.C_SpotCode LEFT JOIN TSYS_User G ON G.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User H ON H.C_UserID = B.C_CreateBy ORDER BY A.D_CreateOn ASC
            //MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("QRCode", QRCode) };
            //string sql = "SELECT A.*,B.C_InspectionContent AS InspectionContent,B.C_Remark AS InspectionRemark,B.C_Status AS InspectionStatus,G.C_Name AS SecurityCreateByName,H.C_Name AS InspectionUserName,D.C_AlarmLevel AS ContentAlarmLevel,D.C_Name AS ContentName,F.C_Name AS SpotName,F.C_ImageUrl AS SpotImage, F.C_Number AS SpotNumber  From TSEC_RecordItem A LEFT JOIN TISP_RecordItem B ON B.C_ID = A.C_RecordItemCode LEFT JOIN TISP_Record C ON C.C_ID = B.C_RecordCode LEFT JOIN TISP_Content D ON B.C_ContentCode = D.C_ID LEFT JOIN TISP_RecordImage E ON E.C_RecordItemCode = B.C_ID LEFT JOIN TISP_Spot F ON F.C_Code = C.C_SpotCode LEFT JOIN TSYS_User G ON G.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User H ON H.C_UserID = B.C_CreateBy ORDER BY A.D_CreateOn ASC";
            //string sql = "SELECT A.*, B.C_InspectionContent AS InspectionContent,B.C_Remark AS InspectionRemark,B.C_Status AS InspectionStatus,G.C_Name AS SecurityCreateByName,H.C_Name AS InspectionUserName,D.C_AlarmLevel AS ContentAlarmLevel,D.C_Name AS ContentName,F.C_Name AS SpotName,F.C_ImageUrl AS SpotImage, F.C_Number AS SpotNumber  From TISP_RecordItem B LEFT JOIN (SELECT A0.* From (SELECT * From TSEC_RecordItem ORDER BY TSEC_RecordItem.D_CreateOn DESC) A0 GROUP BY A0.C_ID) A ON B.C_ID = A.C_RecordItemCode LEFT JOIN TISP_Record C ON C.C_ID = B.C_RecordCode LEFT JOIN TISP_Content D ON B.C_ContentCode = D.C_ID LEFT JOIN TISP_RecordImage E ON E.C_RecordItemCode = B.C_ID LEFT JOIN TISP_Spot F ON F.C_Code = C.C_SpotCode LEFT JOIN TSYS_User G ON G.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User H ON H.C_UserID = B.C_CreateBy WHERE A.C_ID is not NULL GROUP BY B.C_ID ORDER BY A.D_CreateOn ASC";
            //string sql = "SELECT A.C_ID AS RecordId, G.C_Name AS SecurityCreateByName, D.C_Name AS InspectionUserName, GROUP_CONCAT(F.C_Status SEPARATOR ',') as C_Status,F.*, E.C_InspectionContent AS InspectionContent,E.C_Remark AS InspectionRemark,E.C_Status AS InspectionStatus,B.C_Name AS SpotName,B.C_ImageUrl AS SpotImage, B.C_Number AS SpotNumber  FROM (SELECT * FROM TISP_Record GROUP BY C_SpotCode ORDER BY D_CreateOn desc) 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 TISP_RecordItem E ON E.C_RecordCode =  A.C_ID LEFT JOIN TSEC_RecordItem F ON F.C_RecordItemCode = E.C_ID LEFT JOIN TSYS_User G ON G.C_UserID = F.C_CreateBy WHERE F.C_ID IS NOT NULL GROUP BY A.C_ID ORDER BY A.D_CreateOn DESC";
            string sql = "SELECT A.C_ID AS RecordId,A.D_CreateOn AS InspectionTime, G.C_Name AS SecurityCreateByName, D.C_Name AS InspectionUserName, GROUP_CONCAT(F.C_Status SEPARATOR ',') as C_Status,F.*, E.C_InspectionContent AS InspectionContent,E.C_Remark AS InspectionRemark,E.C_Status AS InspectionStatus,B.C_Name AS SpotName,B.C_ImageUrl AS SpotImage, B.C_Number AS SpotNumber  FROM (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) 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 TISP_RecordItem E ON E.C_RecordCode =  A.C_ID LEFT JOIN TISP_SecurityItem F ON F.C_RecordItemCode = E.C_ID LEFT JOIN TSYS_User G ON G.C_UserID = F.C_CreateBy WHERE F.C_ID IS NOT NULL";
            sql = @"SELECT A.C_ID AS RecordId,A.D_CreateOn AS InspectionTime, G.C_Name AS SecurityCreateByName, D.C_Name AS InspectionUserName, GROUP_CONCAT(F.C_Status SEPARATOR ',') as C_Status,F.*, E.C_InspectionContent AS InspectionContent,E.C_Remark AS InspectionRemark,E.C_Status AS InspectionStatus,B.C_Name AS SpotName,B.C_ImageUrl AS SpotImage, B.C_Number AS SpotNumber  
FROM (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) 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 TISP_RecordItem E ON E.C_RecordCode =  A.C_ID 
LEFT JOIN TISP_SecurityItem F ON F.C_RecordItemCode = E.C_ID 
LEFT JOIN TSYS_User G ON G.C_UserID = F.C_CreateBy 
WHERE B.C_StoreCode = @StoreCode ";
            sql = @"SELECT A.C_ID AS RecordId,A.D_CreateOn AS InspectionTime, G.C_Name AS SecurityCreateByName, D.C_Name AS InspectionUserName, GROUP_CONCAT(F.C_Status SEPARATOR ',') as C_Status,F.*, E.C_InspectionContent AS InspectionContent,E.C_Remark AS InspectionRemark,E.C_Status AS InspectionStatus,B.C_Name AS SpotName,B.C_ImageUrl AS SpotImage, B.C_Number AS SpotNumber  
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 TISP_RecordItem E ON E.C_RecordCode =  A.C_ID 
INNER JOIN TISP_SecurityItem F ON F.C_RecordItemCode = E.C_ID 
LEFT JOIN TSYS_User G ON G.C_UserID = F.C_CreateBy
WHERE B.C_StoreCode = @StoreCode ";
            if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
            {
                sql += " AND B.C_AreaCode =@AreaCode ";
            }
            if (!string.IsNullOrEmpty(searchModel.IspUserId))
            {
                sql += " AND A.C_CreateBy =@ispUserId ";
            }
            if (!string.IsNullOrEmpty(searchModel.SecUserId))
            {
                sql += " AND E.C_CreateBy =@secUserId ";
            }
            if (searchModel.Start != Convert.ToDateTime("0001-01-01 00:00:00") && searchModel.End != Convert.ToDateTime("0001-01-01 00:00:00"))
            {
                sql += " AND A.D_CreateOn BETWEEN @start and @end";
            }
            //sql += " AND B.G_OrganizeCode = @organizeCode";
            sql += " GROUP BY A.C_ID ORDER BY A.D_CreateOn DESC";
            IEnumerable<TsecRecordItemDetailViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TsecRecordItemDetailViewModel>(DbContext.Database, sql, parameters);
            searchModel.TotalCount = recordItemlist.First() != null ? recordItemlist.ToList().Count : 0;
            return Task.FromResult(searchModel.IsPagination ? recordItemlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : recordItemlist);
        }

        public Task<IEnumerable<TsecRecordItemWithImagesViewModel>> GetSecurityRecordItemByIdAsync(Guid recordId)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("recordId", recordId) };
            //string sql = "SELECT A.*,B.C_ImageURL,B.C_ID AS C_RecordImageId,B.C_Status AS RecordImageStatus From TSEC_RecordItem A LEFT JOIN TSEC_RecordImage B ON B.C_SecurityRecordCode = A.C_ID WHERE A.C_RecordItemCode =@recordId";
            //string sql = "SELECT A.*,B.C_ImageURL,B.C_ID AS C_RecordImageId,B.C_Status AS RecordImageStatus From (SELECT * From TSEC_RecordItem ORDER BY D_CreateOn DESC LIMIT 1) A LEFT JOIN TSEC_RecordImage B ON B.C_SecurityRecordCode = A.C_ID WHERE A.C_RecordItemCode =@recordId";
            //string sql = "SELECT MAX(A.D_CreateOn), A.*,B.C_ImageURL,B.C_ID AS C_RecordImageId,B.C_Status AS RecordImageStatus From TSEC_RecordItem A LEFT JOIN TSEC_RecordImage B ON B.C_SecurityRecordCode = A.C_ID WHERE A.C_RecordItemCode =@recordId";
            string sql = "SELECT  A.*,B.C_ImageURL,B.C_ID AS C_RecordImageId,B.C_Status AS RecordImageStatus From TISP_SecurityItem A LEFT JOIN TISP_SecurityImage B ON B.C_SecurityRecordCode = A.C_ID INNER JOIN (SELECT * From TISP_SecurityItem WHERE C_RecordItemCode =@recordId ORDER BY D_CreateOn DESC LIMIT 1 )C ON C.D_CreateOn = A.D_CreateOn";
            sql = @"SELECT A.*,B.C_ImageURL,B.C_ID AS C_RecordImageId,B.C_Status AS RecordImageStatus  FROM
(
(SELECT * From TISP_SecurityItem WHERE C_RecordItemCode =@recordId ORDER BY D_CreateOn DESC LIMIT 1 ) A
 LEFT JOIN TISP_SecurityImage B ON B.C_SecurityRecordCode = A.C_ID 
) ";
            IEnumerable<TsecRecordItemWithImageViewModel> recordItemlist = EntityFrameworkCoreExtensions.GetList<TsecRecordItemWithImageViewModel>(DbContext.Database, sql, parameters);
            if (null == recordItemlist || recordItemlist.First() == null)
            {
                IEnumerable<TsecRecordItemWithImagesViewModel> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
            var q = from b in recordItemlist
                    group b by b.C_ID into g
                    select new TsecRecordItemWithImagesViewModel
                    {
                        //C_ID = g.Key,
                        C_SecurityContent = g.First().C_SecurityContent,
                        C_Remark = g.First().C_Remark,
                        C_Status = g.First().C_Status,
                        C_CreateBy = g.First().C_CreateBy,
                        D_CreateOn = g.First().D_CreateOn,
                        C_LastUpdatedBy = g.First().C_LastUpdatedBy,
                        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);
        }
    }
}