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, ITispRecordItemRepository { public TispRecordItemRepository(InspectionDbContext dbContext) : base(dbContext) { } public Task 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(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> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { IEnumerable 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(), }; return Task.FromResult(q); } public Task>> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { List> recordItemDetail = null; return Task.FromResult(recordItemDetail); } var recordContentItemlist = from s in recordItemlist group s by s.C_ContentCode into g select g; List> recordContentItems = new List>() { }; 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(), }; recordContentItems.Add(q.ToList()); } return Task.FromResult(recordContentItems); } /// /// 异常报警记录 /// /// /// public Task> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { PageData 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(), }; PageData pageData = new PageData { 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> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { PageData 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(), }; PageData pageData = new PageData { 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> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { PageData 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(), }; PageData pageData = new PageData { 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 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 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> GetRecordItemByIdAsync(Guid id) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("id", id) }; 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_ID = @id"; IEnumerable recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { IEnumerable 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, RecordImageList = (from c in g select new RecordImage { C_RecordImageId = c.C_RecordImageId, C_ImageURL = c.C_ImageURL, RecordImageStatus = c.RecordImageStatus }).ToList(), }; return Task.FromResult(q); } public Task> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { IEnumerable 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(), }; return Task.FromResult(q); } public Task> 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_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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { IEnumerable 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, 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(), }; return Task.FromResult(q); } public Task> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { IEnumerable recordItemDetail = new List() { }; 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(), }; return Task.FromResult(q); } /// /// 防火检查记录表(周) /// /// /// /// public Task> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList2(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { IEnumerable recordItemDetail = new List() { }; 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 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; } /// /// 消防设施每周检查记录 /// /// /// /// public Task> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList2(DbContext.Database, sql, parameters); if (null == recordItemlist || recordItemlist.First() == null) { IEnumerable recordItemDetail = new List() { }; 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 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(); } /// /// 防火巡查记录表 /// /// /// /// public Task> 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 recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); return Task.FromResult(recordItemlist); //IEnumerable recordItemlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); //if (null == recordItemlist || recordItemlist.First() == null) //{ // IEnumerable recordItemDetail = new List() { }; // 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 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(); //} /// /// 防患整改验收单 /// /// /// /// public Task 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 recordItemlist = EntityFrameworkCoreExtensions.GetList2(DbContext.Database, sql, parameters); HiddenDangerRectificationAcceptanceFormViewModel record = new HiddenDangerRectificationAcceptanceFormViewModel() { }; if (null == recordItemlist || recordItemlist.First() == null) { return Task.FromResult(record); } record.RectificationContent = new List(); record.Implementation = new List(); //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); } } }