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 TispRecordRepository : RepositoryBase, ITispRecordRepository { public TispRecordRepository(InspectionDbContext dbContext) : base(dbContext) { } public Task> GetRecordsConditionAsync(TispRecordSearchModel searchModel) { if (searchModel.End != DateTime.MinValue) { TimeSpan timeToAdd = new TimeSpan(0, 23, 59, 59); searchModel.End = searchModel.End + timeToAdd; } MySqlConnector.MySqlParameter[] parameters = new[] { //new MySqlConnector.MySqlParameter("start", searchModel.Start), //new MySqlConnector.MySqlParameter("end", searchModel.End), new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode), new MySqlConnector.MySqlParameter("spotCode", searchModel.C_SpotID), new MySqlConnector.MySqlParameter("userCode", searchModel.C_UserID), new MySqlConnector.MySqlParameter("areaCode", searchModel.C_AreaCode), new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode), new MySqlConnector.MySqlParameter("Start", searchModel.Start), new MySqlConnector.MySqlParameter("End", searchModel.End)}; //SELECT A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark FROM TISP_Record A LEFT JOIN TISP_Spot B ON A.C_SpotCode = B.C_Code //string sql = "SELECT A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark,B.C_ImageUrl, D.C_Name AS C_CreateByName,E.C_Name AS C_LastUpdatedByName FROM TISP_Record A LEFT JOIN TISP_Spot B ON A.C_SpotCode = B.C_Code LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User E ON E.C_UserID = A.C_LastUpdatedBy LEFT JOIN TISP_SpotRoute F ON F.C_SpotCode = A.C_SpotCode WHERE 1=1 AND A.D_CreateOn between @start and @end"; string sql = @"SELECT A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark,B.C_ImageUrl, D.C_Name AS C_CreateByName,E.C_Name AS C_LastUpdatedByName FROM TISP_Record A LEFT JOIN TISP_Spot B ON A.C_SpotCode = B.C_Code LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User E ON E.C_UserID = A.C_LastUpdatedBy LEFT JOIN TDEV_DevSpot P ON P.C_SpotCode = B.C_Code WHERE 1=1"; if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode)) { sql += " AND P.C_DevStoreCode = @DevStoreCode"; } if (!string.IsNullOrEmpty(searchModel.C_StoreCode)) { sql += " AND B.C_StoreCode = @StoreCode"; } if (!string.IsNullOrEmpty(searchModel.C_SpotID)) { sql += " AND A.C_SpotCode = @spotCode"; } //if (searchModel.Abnormal) //{ // sql += " AND A.C_LastUpdatedBy IS NOT NULL"; //} if (!string.IsNullOrEmpty(searchModel.C_AreaCode)) { sql += " AND A.C_AreaCode = @areaCode"; } if (searchModel.Start != DateTime.MinValue && searchModel.End != DateTime.MinValue) { sql += " AND A.D_CreateOn BETWEEN @Start AND @End"; } sql += " ORDER BY A.D_CreateOn DESC"; IEnumerable spotlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); searchModel.TotalCount = spotlist.First() != null ? spotlist.ToList().Count : 0; return Task.FromResult(searchModel.IsPagination ? spotlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : spotlist); } public Task GetRecordAsync(Guid id) { //string sql = "SELECT B.*,A.*,C.*,D.*,E.* FROM TISP_RecordItem B LEFT JOIN TISP_Record A ON A.C_ID = B.C_SpotContentCode LEFT JOIN TISP_RecordImage C ON B.C_ID = C.C_RecordItemCode LEFT JOIN TISP_Spot D ON D.C_Code = A.C_SpotCode LEFT JOIN TISP_Content E ON E.C_ID = B.C_SpotContentCode"; //var contentlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null, pageSize, pageIndex); //var q = from b in contentlist // group b by b.C_SpotID into g // select new TispSpotUsersViewModel // { // C_SpotID = g.Key, // SpotUserList = (from c in g select new SpotUser { C_UserID = c.C_UserID, C_UserName = c.C_UserName, }).ToList(), // C_GPS = g.FirstOrDefault().C_GPS, // C_SpotName = g.FirstOrDefault().C_SpotName, // C_Number = g.FirstOrDefault().C_Number, // C_Position = g.FirstOrDefault().C_Position, // C_QRCode = g.FirstOrDefault().C_QRCode, // C_Remark = g.FirstOrDefault().C_Remark // }; //return Task.FromResult(q); return null; } public Task> GetAllSpotRecordAsync(AllSpotRecordSearchModel searchModel) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode), new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode), new MySqlConnector.MySqlParameter("AreaCode", searchModel.C_AreaCode) }; string sql; // if (!searchModel.bMapShow) // { // //sql = "SELECT A.* ,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM TISP_Spot A LEFT JOIN (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) order by D_CreateOn desc limit 1) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy"; // //if (!string.IsNullOrEmpty(searchModel.C_AreaCode)) // //{ // // sql += " AND A.C_AreaCode = @AreaCode"; // //} // sql = @"SELECT A.* ,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName //FROM TISP_Spot A //LEFT JOIN (SELECT C_SpotCode, ProductMapX,ProductMapY,DevDataConfigName,DevDataValue,DevDataTime,DevName,DevMachineCode //FROM TISP_Spot A //LEFT JOIN (SELECT * FROM( //SELECT A.C_SpotCode,A.F_Map_X AS ProductMapX ,A.F_Map_Y AS ProductMapY,D.C_Name AS DevDataConfigName,D.C_Code AS DevDataConfigCode, C.C_Value AS DevDataValue,C.D_CreateOn AS DevDataTime,B.C_Name AS DevName,B.C_MachineCode AS DevMachineCode FROM TPRD_Product A //LEFT JOIN TDEV_Device B ON B.C_Code = A.C_DeviceCode //INNER JOIN TDEV_DevData C ON C.C_DeviceCode = B.C_Code //INNER JOIN TDEV_DevDataConfig D ON D.C_Code = C.C_ConfigCode //WHERE B.C_Status = '1' AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) ORDER BY C.D_CreateOn desc //)A GROUP BY A.DevDataConfigCode) K ON K.C_SpotCode = A.C_Code) K ON K.C_SpotCode = A.C_Code //LEFT JOIN (SELECT * FROM(SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) ORDER BY D_CreateOn DESC)A GROUP BY A.C_SpotCode) B ON A.C_Code = B.C_SpotCode //LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy "; // } // else // { // sql = @"SELECT A.* ,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM TISP_Spot A LEFT JOIN ( SELECT * FROM (SELECT * FROM TISP_Record WHERE C_Status = '1' AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C_SpotCode ORDER BY D_CreateOn desc) U0 //UNION //SELECT * FROM TISP_Record WHERE C_Status = '2' OR C_Status = '3' //) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE A.C_Status ='1' GROUP BY A.C_Code ORDER BY RecordDateTime desc"; // sql = @"SELECT A.* ,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM TISP_Spot A LEFT JOIN ( SELECT * FROM (SELECT * FROM TISP_Record WHERE C_Status = '1' AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C_SpotCode ORDER BY D_CreateOn desc) U0 //UNION //SELECT * FROM (SELECT * FROM TISP_Record WHERE C_Status = '2' OR C_Status = '3' AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C_SpotCode ORDER BY D_CreateOn desc) U2 //) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE A.C_Status ='1' AND A.C_StoreCode =@StoreCode GROUP BY A.C_Code ORDER BY RecordDateTime desc"; // sql = @"SELECT A.* ,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName,K.C_DeviceCode,K.C_ConfigCode,K.C_Value,K.D_CreateOn //FROM TISP_Spot A //LEFT JOIN (SELECT * from TDEV_DevData where D_CreateOn = (SELECT max(D_CreateOn) FROM TDEV_DevData) AND C_ConfigCode = 'DDC00001') K ON K.C_DeviceCode = A.C_Code //LEFT JOIN ( SELECT * FROM (SELECT * FROM TISP_Record WHERE C_Status = '1' AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C_SpotCode ORDER BY D_CreateOn desc) U0 //UNION //SELECT * FROM (SELECT * FROM TISP_Record WHERE C_Status = '2' OR C_Status = '3' AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C_SpotCode ORDER BY D_CreateOn desc) U2 //) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE A.C_Status ='1' AND A.C_StoreCode =@StoreCode AND K.D_CreateOn GROUP BY A.C_Code ORDER BY RecordDateTime desc"; // sql = @"SELECT A.* ,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName,K.* //FROM TISP_Spot A //LEFT JOIN (SELECT C_SpotCode, ProductMapX,ProductMapY,DevDataConfigName,DevDataValue,DevDataTime,DevName,DevMachineCode //FROM TISP_Spot A //LEFT JOIN (SELECT A.C_SpotCode,A.F_Map_X AS ProductMapX ,A.F_Map_Y AS ProductMapY,D.C_Name AS DevDataConfigName,C.C_Value AS DevDataValue,C.D_CreateOn AS DevDataTime,B.C_Name AS DevName,B.C_MachineCode AS DevMachineCode FROM TPRD_Product A //LEFT JOIN TDEV_Device B ON B.C_Code = A.C_DeviceCode //INNER JOIN TDEV_DevData C ON C.C_DeviceCode = B.C_Code //INNER JOIN TDEV_DevDataConfig D ON D.C_Code = C.C_ConfigCode //WHERE B.C_Status = '1' AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY D.C_Code ORDER BY C.D_CreateOn desc) K ON K.C_SpotCode = A.C_Code) K ON K.C_SpotCode = A.C_Code //LEFT JOIN ( SELECT * FROM (SELECT * FROM TISP_Record WHERE C_Status = '1' AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C_SpotCode ORDER BY D_CreateOn desc) U0 //UNION //SELECT * FROM (SELECT * FROM TISP_Record WHERE C_Status = '2' OR C_Status = '3' AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C_SpotCode ORDER BY D_CreateOn desc) U2 //) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE A.C_Status ='1' AND A.C_StoreCode =@StoreCode "; // if (!string.IsNullOrEmpty(searchModel.C_AreaCode)) // { // sql += " AND A.C_AreaCode = @AreaCode"; // } // sql += " GROUP BY A.C_Code ORDER BY RecordDateTime desc"; // sql = @"SELECT A.* ,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName,K.* //FROM TISP_Spot A //LEFT JOIN (SELECT C_SpotCode, ProductMapX,ProductMapY,DevDataConfigName,DevDataValue,DevDataTime,DevName,DevMachineCode //FROM TISP_Spot A //LEFT JOIN (SELECT A.C_SpotCode,A.F_Map_X AS ProductMapX ,A.F_Map_Y AS ProductMapY,D.C_Name AS DevDataConfigName,C.C_Value AS DevDataValue,C.D_CreateOn AS DevDataTime,B.C_Name AS DevName,B.C_MachineCode AS DevMachineCode FROM TPRD_Product A //LEFT JOIN TDEV_Device B ON B.C_Code = A.C_DeviceCode //INNER JOIN TDEV_DevData C ON C.C_DeviceCode = B.C_Code //INNER JOIN TDEV_DevDataConfig D ON D.C_Code = C.C_ConfigCode //WHERE B.C_Status = '1' AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY D.C_Code ORDER BY C.D_CreateOn desc) K ON K.C_SpotCode = A.C_Code) K ON K.C_SpotCode = A.C_Code //LEFT JOIN ( SELECT * FROM (SELECT * FROM TISP_Record WHERE C_Status = '1' AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C_SpotCode ORDER BY D_CreateOn desc) U0 //UNION //SELECT * FROM (SELECT * FROM TISP_Record WHERE C_Status = '2' OR C_Status = '3' AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C_SpotCode ORDER BY D_CreateOn desc) U2 //) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE A.C_Status ='1' GROUP BY A.C_Code ORDER BY RecordDateTime desc"; // } sql = @"SELECT A.* ,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM TISP_Spot A LEFT JOIN (SELECT C_SpotCode, ProductMapX,ProductMapY,DevDataConfigName,DevDataValue,DevDataTime,DevName,DevMachineCode FROM TISP_Spot A LEFT JOIN (SELECT * FROM( SELECT A.C_SpotCode,A.F_Map_X AS ProductMapX ,A.F_Map_Y AS ProductMapY,D.C_Name AS DevDataConfigName,D.C_Code AS DevDataConfigCode, C.C_Value AS DevDataValue,C.D_CreateOn AS DevDataTime,B.C_Name AS DevName,B.C_MachineCode AS DevMachineCode FROM TPRD_Product A LEFT JOIN TDEV_Device B ON B.C_Code = A.C_DeviceCode INNER JOIN TDEV_DevData C ON C.C_DeviceCode = B.C_Code INNER JOIN TDEV_DevDataConfig D ON D.C_Code = C.C_ConfigCode WHERE B.C_Status = '1' AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) ORDER BY C.D_CreateOn desc )A GROUP BY A.DevDataConfigCode) K ON K.C_SpotCode = A.C_Code) K ON K.C_SpotCode = A.C_Code LEFT JOIN (SELECT A.* FROM TISP_Record A, ( SELECT C_SpotCode, max( D_CreateOn ) max_dateline FROM TISP_Record R WHERE DATE_FORMAT(R.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) || R.C_Status = '2' || R.C_Status = '3' GROUP BY R.C_SpotCode )B WHERE A.C_SpotCode = B.C_SpotCode AND A.D_CreateOn = B.max_dateline) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE C_StoreCode = @StoreCode "; sql = @" SELECT P.C_DevStoreCode,A.* ,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName,D.C_Status AS DevOspStatus FROM TISP_Spot A LEFT JOIN (SELECT C_SpotCode, ProductMapX,ProductMapY,DevDataConfigName,DevDataValue,DevDataTime,DevName,DevMachineCode FROM TISP_Spot A LEFT JOIN (SELECT * FROM( SELECT A.C_SpotCode,A.F_Map_X AS ProductMapX ,A.F_Map_Y AS ProductMapY,D.C_Name AS DevDataConfigName,D.C_Code AS DevDataConfigCode, C.C_Value AS DevDataValue,C.D_CreateOn AS DevDataTime,B.C_Name AS DevName,B.C_MachineCode AS DevMachineCode FROM TPRD_Product A LEFT JOIN TDEV_Device B ON B.C_Code = A.C_DeviceCode INNER JOIN TDEV_DevData C ON C.C_DeviceCode = B.C_Code INNER JOIN TDEV_DevDataConfig D ON D.C_Code = C.C_ConfigCode WHERE B.C_Status = '1' AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) ORDER BY C.D_CreateOn desc )A GROUP BY A.DevDataConfigCode) K ON K.C_SpotCode = A.C_Code) K ON K.C_SpotCode = A.C_Code LEFT JOIN (SELECT A.* FROM TISP_Record A, ( SELECT C_SpotCode, max( D_CreateOn ) max_dateline FROM TISP_Record R WHERE DATE_FORMAT(R.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) || R.C_Status = '2' || R.C_Status = '3' GROUP BY R.C_SpotCode )B WHERE A.C_SpotCode = B.C_SpotCode AND A.D_CreateOn = B.max_dateline) B ON A.C_Code = B.C_SpotCode LEFT JOIN (SELECT A.* FROM TMTN_DevOps A, ( SELECT C_SpotCode, max( D_CreateOn ) max_dateline FROM TMTN_DevOps R WHERE DATE_FORMAT(R.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) || R.C_Status = '1' || R.C_Status = '2'| R.C_Status = '3' || R.C_Status = '4' GROUP BY R.C_SpotCode )C WHERE A.C_SpotCode = C.C_SpotCode AND A.D_CreateOn = C.max_dateline) D ON A.C_Code = D.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy LEFT JOIN TDEV_DevSpot P ON P.C_SpotCode = A.C_Code WHERE 1=1 "; sql = @" SELECT * from ( SELECT A.* ,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName,D.C_Status AS DevOspStatus FROM TISP_Spot A LEFT JOIN (SELECT C_SpotCode, ProductMapX,ProductMapY,DevDataConfigName,DevDataValue,DevDataTime,DevName,DevMachineCode FROM TISP_Spot A LEFT JOIN (SELECT * FROM( SELECT A.C_SpotCode,A.F_Map_X AS ProductMapX ,A.F_Map_Y AS ProductMapY,D.C_Name AS DevDataConfigName,D.C_Code AS DevDataConfigCode, C.C_Value AS DevDataValue,C.D_CreateOn AS DevDataTime,B.C_Name AS DevName,B.C_MachineCode AS DevMachineCode FROM TPRD_Product A LEFT JOIN TDEV_Device B ON B.C_Code = A.C_DeviceCode INNER JOIN TDEV_DevData C ON C.C_DeviceCode = B.C_Code INNER JOIN TDEV_DevDataConfig D ON D.C_Code = C.C_ConfigCode WHERE B.C_Status = '1' AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) ORDER BY C.D_CreateOn desc )A GROUP BY A.DevDataConfigCode) K ON K.C_SpotCode = A.C_Code) K ON K.C_SpotCode = A.C_Code LEFT JOIN (SELECT A.* FROM TISP_Record A, ( SELECT C_SpotCode, max( D_CreateOn ) max_dateline FROM TISP_Record R WHERE DATE_FORMAT(R.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) || R.C_Status = '2' || R.C_Status = '3' GROUP BY R.C_SpotCode )B WHERE A.C_SpotCode = B.C_SpotCode AND A.D_CreateOn = B.max_dateline) B ON A.C_Code = B.C_SpotCode LEFT JOIN (SELECT A.* FROM TMTN_DevOps A, ( SELECT C_SpotCode, max( D_CreateOn ) max_dateline FROM TMTN_DevOps R WHERE DATE_FORMAT(R.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) || R.C_Status = '1' || R.C_Status = '2'| R.C_Status = '3' || R.C_Status = '4' GROUP BY R.C_SpotCode )C WHERE A.C_SpotCode = C.C_SpotCode AND A.D_CreateOn = C.max_dateline) D ON A.C_Code = D.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy )AA INNER JOIN TDEV_DevSpot P ON P.C_SpotCode = AA.C_Code WHERE 1=1"; if (!string.IsNullOrEmpty(searchModel.C_StoreCode)) { sql += " AND AA.C_StoreCode = @StoreCode"; } if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode)) { sql += " AND P.C_DevStoreCode = @DevStoreCode"; } if (!string.IsNullOrEmpty(searchModel.C_AreaCode)) { sql += " AND AA.C_AreaCode = @AreaCode"; } IEnumerable spotRecordlist; if (!searchModel.IsPagination) { spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); } else { spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters, searchModel.PageSize, searchModel.PageIndex); } //IEnumerable spotRecordlist; //if (!searchModel.IsPagination) //{ // if (!string.IsNullOrEmpty(searchModel.C_AreaCode)) // { // spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null).Where(c => c.C_StoreCode == searchModel.C_StoreCode && c.C_AreaCode == searchModel.C_AreaCode); // } // else // { // spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null).Where(c => c.C_StoreCode == searchModel.C_StoreCode); // } //} //else //{ // if (!string.IsNullOrEmpty(searchModel.C_AreaCode)) // { // spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null, searchModel.PageSize, searchModel.PageIndex).Where(c => c.C_StoreCode == searchModel.C_StoreCode && c.C_AreaCode == searchModel.C_AreaCode); // } // else // { // spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null, searchModel.PageSize, searchModel.PageIndex).Where(c => c.C_StoreCode == searchModel.C_StoreCode); // } //} if (null == spotRecordlist || null == spotRecordlist.First() || !spotRecordlist.Any()) { IEnumerable recordItemDetail = null; return Task.FromResult(recordItemDetail); } var q = from b in spotRecordlist group b by b.C_Code into g select new AllSpotRecordWithDevViewModel { C_Code = g.First().C_Code, C_StoreCode = g.First().C_StoreCode, C_AreaCode = g.First().C_AreaCode, F_Map_X = g.First().F_Map_X, F_Map_Y = g.First().F_Map_Y, C_Number = g.First().C_Number, C_Name = g.First().C_Name, C_Position = g.First().C_Position, C_QRCode = g.First().C_QRCode, C_GPS = g.First().C_GPS, C_Remark = g.First().C_Remark, C_ImageUrl = g.First().C_ImageUrl, C_CreateBy = g.First().C_CreateBy, D_CreateOn = g.First().D_CreateOn, C_LastUpdatedBy = g.First().C_LastUpdatedBy, D_LastUpdatedOn = g.First().D_LastUpdatedOn, C_Status = g.First().C_Status, RecordCode = g.First().RecordCode, RecordDateTime = g.First().RecordDateTime, RecordStatus = g.First().RecordStatus, RecordUserName = g.First().RecordUserName, ProductDevs = (from c in g select new ProductDev { ProductMapX = c.ProductMapX, ProductMapY = c.ProductMapY, DevDataConfigName = c.DevDataConfigName, DevDataValue = c.DevDataValue, DevDataTime = c.DevDataTime, DevName = c.DevName, DevMachineCode = c.DevMachineCode, }), DevOspStatus = g.First().DevOspStatus, }; return Task.FromResult(q); } public Task> GetUserSpotRecordAsync(UserSpotRecordSearchModel searchModel) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("userId", searchModel.userId) , new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode) }; //string sql = "SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId) A LEFT JOIN (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) order by D_CreateOn desc limit 1) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy"; string sql = "SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) AND C_CreateBy = @userId order by D_CreateOn desc limit 1) B LEFT JOIN (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId AND A1.C_StoreCode =@StoreCode) A ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy"; IEnumerable spotRecordlist; if (!searchModel.IsPagination) { spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); } else { spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters, searchModel.PageSize, searchModel.PageIndex); } return Task.FromResult(spotRecordlist); } public Task GetUserTodaySpotRecordCountAsync(Guid id, string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("userId", id), new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = "SELECT COUNT(*) FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId) A LEFT JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE B.C_ID IS NOT NULL) AA GROUP BY C_Code) BB"; object result = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).Rows[0][0]; return Task.FromResult(int.Parse(result.ToString())); } //public Task GetAllTodaySpotRecordCountAsync() //{ //} public Task GetUserTodayNotSpotRecordCountAsync(Guid id, string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("userId", id), new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = "SELECT Count(*) - COUNT(RecordCode) FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId) A LEFT JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB"; object result = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).Rows[0][0]; return Task.FromResult(Convert.ToInt32(result)); } public Task GetAllTodayNotSpotRecordCountAsync() { //string sql = "SELECT COUNT(*) - (SELECT count(DISTINCT C_SpotCode ) FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) From TISP_Spot"; string sql = "SELECT count(C_Code) FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code) A LEFT JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB WHERE RecordCode IS NULL"; object result = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, null).Rows[0][0]; return Task.FromResult(int.Parse(result.ToString())); } //public Task> GetUserTodaySpotRecordsAsync(Guid id, string storeCode) //{ // MySqlConnector.MySqlParameter[] parameters = new[] { // new MySqlConnector.MySqlParameter("userId", id), // new MySqlConnector.MySqlParameter("storeCode", storeCode)}; // string sql = string.Empty; // //string sql = "SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId) A LEFT JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) order by D_CreateOn desc limit 1) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE B.C_ID IS NOT NULL"; // if (id == Guid.Empty) // { // sql = "SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT * FROM TISP_Spot) A LEFT JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE B.C_ID IS NOT NULL order by A.D_CreateOn desc) AA GROUP BY C_Code ORDER BY D_CreateOn DESC"; // } // else // { // sql = "SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId) A LEFT JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE B.C_ID IS NOT NULL order by A.D_CreateOn desc) AA GROUP BY C_Code ORDER BY D_CreateOn DESC"; // } // IEnumerable spotRecordlist; // spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); // return Task.FromResult(spotRecordlist); //} /// /// 管理员获取所有信息 /// /// public Task> GetUsersTodaySpotRecordsAsync() { //MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("userId", id) }; string sql = "SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code) A LEFT JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE B.C_ID IS NOT NULL order by A.D_CreateOn desc "; IEnumerable spotRecordlist; spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null); return Task.FromResult(spotRecordlist); } //public Task> GetUserTodayNotSpotRecordsAsync(Guid id, string storeCode) //{ // MySqlConnector.MySqlParameter[] parameters = new[] { // new MySqlConnector.MySqlParameter("userId", id), // new MySqlConnector.MySqlParameter("storeCode", storeCode)}; // string sql = string.Empty; // if (id == Guid.Empty) // { // sql = "SELECT * FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code) A LEFT JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB WHERE RecordCode IS NULL"; // } // else // { // sql = "SELECT * FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId) A LEFT JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB WHERE RecordCode IS NULL"; // } // IEnumerable spotRecordlist; // spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); // return Task.FromResult(spotRecordlist); //} public Task> GetRecords30DaysStatisticsAsync() { //string sql = "select date_format(D_CreateOn,'%Y%m%d') as SpecificDay, SUM(if(C_Status='1',1,0)) AS Normal, SUM(if(C_Status='2',1,0)) AS Abnormal, count(C_ID) as Total from TISP_Record WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= D_CreateOn GROUP BY DATE_FORMAT(D_CreateOn, '%Y%m%d')"; string sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, SUM(if(C_Status='1',1,0)) AS Normal, SUM(if(C_Status='2',1,0)) AS Abnormal, count(C_ID) as Total from( SELECT DATE_SUB(CURDATE(), INTERVAL 31 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 30 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 29 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 28 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 27 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 26 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 25 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 24 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 23 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 22 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 21 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 20 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 19 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 18 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 17 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 16 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 15 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 14 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 13 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 12 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 11 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 10 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 9 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 8 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 7 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date ) A LEFT JOIN TISP_Record B ON DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')"; sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, SUM(if(C_Status='1',1,0)) AS Normal, SUM(if(C_Status='2',1,0)) AS Abnormal, count(C_ID) as Total from( SELECT DATE_SUB(CURDATE(), INTERVAL 31 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 30 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 29 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 28 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 27 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 26 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 25 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 24 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 23 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 22 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 21 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 20 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 19 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 18 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 17 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 16 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 15 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 14 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 13 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 12 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 11 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 10 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 9 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 8 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 7 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date ) A LEFT JOIN ( SELECT B.* FROM TISP_Spot A INNER JOIN TISP_Record B ON B.C_SpotCode = A.C_Code WHERE A.C_StoreCode = '51031240-4c88-43e1-a18b-63bc03e70a8c' AND A.C_Status = '1' ) B ON DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')"; IEnumerable recordlist; recordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null); return Task.FromResult(recordlist); } public Task> GetAlarmRecovery7DaysStatisticsAsync() { string sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, SUM(if(C_Status='0',1,0)) AS Alarm, SUM(if(C_Status='2',1,0)) AS Recovery From( SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 0 DAY) AS click_date ) A LEFT JOIN TISP_RecordItem B ON DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')"; IEnumerable recordlist; recordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null); return Task.FromResult(recordlist); } public Task GetTodaySpotStatisticsAsync() { string sql = "SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Normal,CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Abnormal FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn, '%Y%m%d') = DATE_FORMAT(CURDATE(), '%Y%m%d')"; TodaySpotStatistics record; record = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, null).FirstOrDefault(); return Task.FromResult(record); } public Task GetTodaySpotContentStatisticsAsync() { string sql = "SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Normal,CASE WHEN ISNULL(SUM(if(C_Status='0',1,0))) THEN 0 ELSE SUM(if(C_Status='0',1,0)) END AS Abnormal ,CASE WHEN ISNULL(SUM(if(C_Status='3',1,0))) THEN 0 ELSE SUM(if(C_Status='3',1,0)) END AS ConfirmAbnormal ,CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Recovery FROM TISP_RecordItem WHERE DATE_FORMAT(D_CreateOn, '%Y%m%d') = DATE_FORMAT(CURDATE(), '%Y%m%d')"; TodaySpotContentStatistics record; record = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, null).FirstOrDefault(); return Task.FromResult(record); } public Task> GetRecord12MonthStatisticsAsync() { string sql = @"SELECT date_format(click_date,'%Y%m') AS SpecificMonth, SUM(if(ISNULL(C_Status) ,0,1)) AS RecordCount from( SELECT DATE_SUB(CURDATE(), INTERVAL 12 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 11 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 10 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 9 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 8 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 7 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 0 month) AS click_date ) A LEFT JOIN TISP_Record B ON DATE_FORMAT(A.click_date, '%Y-%m') = DATE_FORMAT(B.D_CreateOn, '%Y-%m') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m')"; IEnumerable recordlist; recordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null); return Task.FromResult(recordlist); } public Task GetAllTodaySpotRecordCountAsync(string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = "SELECT count(DISTINCT B.C_SpotCode ) FROM TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) "; object result = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).Rows[0][0]; return Task.FromResult(int.Parse(result.ToString())); } public Task GetAllTodayNotSpotRecordCountAsync(string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = "SELECT count(C_Code) FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code) A LEFT JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB WHERE RecordCode IS NULL"; sql = @"SELECT count(C_Code) FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code) A INNER JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB WHERE RecordCode IS NULL"; sql = @"SELECT count(C_Code) FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code) A INNER JOIN (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C.C_Code,B.C_Status) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB WHERE RecordCode IS NULL"; object result = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).Rows[0][0]; return Task.FromResult(int.Parse(result.ToString())); } public Task> GetUserTodaySpotRecordsAsync(Guid id, string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("userId", id), new MySqlConnector.MySqlParameter("storeCode", storeCode) }; string sql = string.Empty; //string sql = "SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId) A LEFT JOIN (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) order by D_CreateOn desc limit 1) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE B.C_ID IS NOT NULL"; if (id == Guid.Empty) { sql = "SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT * FROM TISP_Spot) A LEFT JOIN (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE B.C_ID IS NOT NULL order by A.D_CreateOn desc) AA WHERE C_StoreCode = @storeCode GROUP BY C_Code ORDER BY D_CreateOn DESC"; sql = @"SELECT * FROM ( SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B LEFT JOIN (SELECT * FROM TISP_Spot WHERE C_StoreCode = @storeCode AND C_Status = '1') A ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy order by B.D_CreateOn desc) AA GROUP BY C_Code ORDER BY RecordDateTime DESC"; sql = @" SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B LEFT JOIN (SELECT * FROM TISP_Spot WHERE C_StoreCode = @storeCode AND C_Status = '1') A ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy order by B.D_CreateOn desc"; } else { sql = "SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId) A LEFT JOIN (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy WHERE B.C_ID IS NOT NULL order by A.D_CreateOn desc) AA WHERE C_StoreCode = @storeCode GROUP BY C_Code ORDER BY D_CreateOn DESC"; sql = @"SELECT * FROM ( SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT * FROM TISP_Record WHERE C_CreateBy = @userId AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B LEFT JOIN (SELECT * FROM TISP_Spot WHERE C_StoreCode = @storeCode AND C_Status = '1') A ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy order by B.D_CreateOn desc) AA GROUP BY C_Code ORDER BY RecordDateTime DESC"; sql = @" SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT * FROM TISP_Record WHERE C_CreateBy = @userId AND DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B INNER JOIN (SELECT * FROM TISP_Spot WHERE C_StoreCode = @storeCode AND C_Status = '1') A ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy order by B.D_CreateOn desc"; } IEnumerable spotRecordlist; spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); return Task.FromResult(spotRecordlist); } public Task> GetUserTodayNotSpotRecordsAsync(Guid id, string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("userId", id), new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = string.Empty; if (id == Guid.Empty) { sql = "SELECT * FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code) A LEFT JOIN (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB WHERE RecordCode IS NULL AND C_StoreCode = @storeCode"; sql = @"SELECT * FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT p.* FROM TISP_SpotContent c LEFT JOIN TISP_Spot p on (p.C_Code=c.C_SpotCode) WHERE p.C_StoreCode =@storeCode AND p.C_Status = '1') A LEFT JOIN (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB WHERE RecordCode IS NULL "; } else { sql = "SELECT * FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId) A LEFT JOIN (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB WHERE RecordCode IS NULL AND C_StoreCode = @storeCode"; sql = @"SELECT * FROM (SELECT * FROM (SELECT A.*,B.C_ID AS RecordCode,B.D_CreateOn AS RecordDateTime,B.C_Status AS RecordStatus,C.C_Name AS RecordUserName FROM (SELECT A1.* FROM TISP_SpotUser A0,TISP_Spot A1 WHERE A0.C_SpotCode = A1.C_Code AND A0.C_UserCode = @userId AND A1.C_StoreCode = @storeCode AND A1.C_Status = '1') A LEFT JOIN (SELECT * FROM TISP_Record WHERE DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY)) B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C ON C.C_UserID = B.C_CreateBy) AA GROUP BY C_Code) BB WHERE RecordCode IS NULL "; } IEnumerable spotRecordlist; spotRecordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); return Task.FromResult(spotRecordlist); } public Task> GetRecords30DaysStatisticsAsync(string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, SUM(if(C_Status='1',1,0)) AS Normal, SUM(if(C_Status='2',1,0)) AS Abnormal, count(C_ID) as Total from( SELECT DATE_SUB(CURDATE(), INTERVAL 31 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 30 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 29 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 28 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 27 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 26 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 25 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 24 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 23 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 22 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 21 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 20 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 19 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 18 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 17 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 16 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 15 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 14 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 13 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 12 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 11 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 10 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 9 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 8 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 7 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date ) A LEFT JOIN (SELECT B.D_CreateOn,B.C_Status,B.C_ID from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode) B ON DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d') "; sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, SUM(if(C_Status='1',1,0)) AS Normal, SUM(if(C_Status='2',1,0)) AS Abnormal, count(C_ID) as Total from( SELECT DATE_SUB(CURDATE(), INTERVAL 31 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 30 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 29 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 28 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 27 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 26 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 25 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 24 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 23 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 22 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 21 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 20 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 19 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 18 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 17 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 16 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 15 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 14 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 13 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 12 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 11 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 10 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 9 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 8 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 7 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date ) A LEFT JOIN ( SELECT B.* FROM TISP_Spot A INNER JOIN TISP_Record B ON B.C_SpotCode = A.C_Code WHERE A.C_StoreCode = @storeCode AND A.C_Status = '1' ) B ON DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')"; IEnumerable recordlist; recordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); return Task.FromResult(recordlist); } public Task> GetAlarmRecovery7DaysStatisticsAsync(string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, SUM(if(C_Status='0',1,0)) AS Alarm, SUM(if(C_Status='2',1,0)) AS Recovery From( SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 0 DAY) AS click_date ) A LEFT JOIN (SELECT A.D_CreateOn,A.C_Status FROM TISP_RecordItem A LEFT JOIN TISP_Record B ON B.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode = @storeCode AND C.C_Status = '1' ) B ON DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')"; IEnumerable recordlist; recordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); return Task.FromResult(recordlist); } public Task GetTodaySpotStatisticsAsync(string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = "SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Normal,CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Abnormal FROM (SELECT B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1') A WHERE DATE_FORMAT(D_CreateOn, '%Y%m%d') = DATE_FORMAT(CURDATE(), '%Y%m%d')"; sql = @"SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Normal, CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Abnormal FROM (SELECT C.C_Code, B.* from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(B.D_CreateOn, '%Y%m%d') = DATE_FORMAT(CURDATE(), '%Y%m%d') GROUP BY C.C_Code, B.C_Status) A "; TodaySpotStatistics record; record = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).FirstOrDefault(); return Task.FromResult(record); } public Task GetTodaySpotContentStatisticsAsync(string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = "SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Normal,CASE WHEN ISNULL(SUM(if(C_Status='0',1,0))) THEN 0 ELSE SUM(if(C_Status='0',1,0)) END AS Abnormal ,CASE WHEN ISNULL(SUM(if(C_Status='3',1,0))) THEN 0 ELSE SUM(if(C_Status='3',1,0)) END AS ConfirmAbnormal ,CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Recovery FROM (SELECT A.D_CreateOn,A.C_Status FROM TISP_RecordItem A LEFT JOIN TISP_Record B ON B.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1')A WHERE DATE_FORMAT(D_CreateOn, '%Y%m%d') = DATE_FORMAT(CURDATE(), '%Y%m%d')"; sql = @"SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Normal, CASE WHEN ISNULL(SUM(if(C_Status='0',1,0))) THEN 0 ELSE SUM(if(C_Status='0',1,0)) END AS Abnormal , CASE WHEN ISNULL(SUM(if(C_Status='3',1,0))) THEN 0 ELSE SUM(if(C_Status='3',1,0)) END AS ConfirmAbnormal , CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Recovery FROM ( SELECT A.D_CreateOn,A.C_Status FROM TISP_RecordItem A LEFT JOIN TISP_Record B ON B.C_ID = A.C_RecordCode LEFT JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1' AND DATE_FORMAT(A.D_CreateOn, '%Y%m%d') = DATE_FORMAT(CURDATE(), '%Y%m%d') )A "; TodaySpotContentStatistics record; record = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).FirstOrDefault(); return Task.FromResult(record); } public Task> GetRecord12MonthStatisticsAsync(string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = @"SELECT date_format(click_date,'%m') AS SpecificMonth, SUM(if(ISNULL(C_Status) ,0,1)) AS RecordCount from( SELECT DATE_SUB(CURDATE(), INTERVAL 12 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 11 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 10 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 9 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 8 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 7 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 0 month) AS click_date ) A LEFT JOIN (SELECT B.D_CreateOn,B.C_Status,B.C_ID from TISP_Record B INNER JOIN TISP_Spot C ON C.C_Code = B.C_SpotCode WHERE C.C_StoreCode =@storeCode AND C.C_Status = '1') B ON DATE_FORMAT(A.click_date, '%Y-%m') = DATE_FORMAT(B.D_CreateOn, '%Y-%m') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m')"; IEnumerable recordlist; recordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); return Task.FromResult(recordlist); } } }