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.*,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<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,
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_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,
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);
}
}
}