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<TISP_Record, Guid>, ITispRecordRepository
{
public TispRecordRepository(InspectionDbContext dbContext) : base(dbContext)
{
}
public Task<IEnumerable<TispRecordDetailViewModel>> 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<TispRecordDetailViewModel> spotlist = EntityFrameworkCoreExtensions.GetList<TispRecordDetailViewModel>(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<TispRecordViewModel> 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<TispRecordViewModel>(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<SpotUser>(),
// C_GPS = g.FirstOrDefault<TispSpotsUsersViewModel>().C_GPS,
// C_SpotName = g.FirstOrDefault<TispSpotsUsersViewModel>().C_SpotName,
// C_Number = g.FirstOrDefault<TispSpotsUsersViewModel>().C_Number,
// C_Position = g.FirstOrDefault<TispSpotsUsersViewModel>().C_Position,
// C_QRCode = g.FirstOrDefault<TispSpotsUsersViewModel>().C_QRCode,
// C_Remark = g.FirstOrDefault<TispSpotsUsersViewModel>().C_Remark
// };
//return Task.FromResult(q);
return null;
}
public Task<IEnumerable<AllSpotRecordWithDevViewModel>> 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<AllSpotRecordViewModel> spotRecordlist;
if (!searchModel.IsPagination)
{
spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, parameters);
}
else
{
spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, parameters, searchModel.PageSize, searchModel.PageIndex);
}
//IEnumerable<AllSpotRecordViewModel> spotRecordlist;
//if (!searchModel.IsPagination)
//{
// if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
// {
// spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, null).Where(c => c.C_StoreCode == searchModel.C_StoreCode && c.C_AreaCode == searchModel.C_AreaCode);
// }
// else
// {
// spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, null).Where(c => c.C_StoreCode == searchModel.C_StoreCode);
// }
//}
//else
//{
// if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
// {
// spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(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<AllSpotRecordViewModel>(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<AllSpotRecordWithDevViewModel> 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<IEnumerable<AllSpotRecordViewModel>> 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<AllSpotRecordViewModel> spotRecordlist;
if (!searchModel.IsPagination)
{
spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, parameters);
}
else
{
spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, parameters, searchModel.PageSize, searchModel.PageIndex);
}
return Task.FromResult(spotRecordlist);
}
public Task<int> 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<int> GetAllTodaySpotRecordCountAsync()
//{
//}
public Task<int> 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<int> 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<IEnumerable<AllSpotRecordViewModel>> 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<AllSpotRecordViewModel> spotRecordlist;
// spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, parameters);
// return Task.FromResult(spotRecordlist);
//}
/// <summary>
/// 管理员获取所有信息
/// </summary>
/// <returns></returns>
public Task<IEnumerable<AllSpotRecordViewModel>> 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<AllSpotRecordViewModel> spotRecordlist;
spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, null);
return Task.FromResult(spotRecordlist);
}
//public Task<IEnumerable<AllSpotRecordViewModel>> 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<AllSpotRecordViewModel> spotRecordlist;
// spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, parameters);
// return Task.FromResult(spotRecordlist);
//}
public Task<IEnumerable<TispRecord30DaysStatistics>> 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<TispRecord30DaysStatistics> recordlist;
recordlist = EntityFrameworkCoreExtensions.GetList<TispRecord30DaysStatistics>(DbContext.Database, sql, null);
return Task.FromResult(recordlist);
}
public Task<IEnumerable<AlarmRecovery7DaysStatistics>> 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<AlarmRecovery7DaysStatistics> recordlist;
recordlist = EntityFrameworkCoreExtensions.GetList<AlarmRecovery7DaysStatistics>(DbContext.Database, sql, null);
return Task.FromResult(recordlist);
}
public Task<TodaySpotStatistics> 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<TodaySpotStatistics>(DbContext.Database, sql, null).FirstOrDefault();
return Task.FromResult(record);
}
public Task<TodaySpotContentStatistics> 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<TodaySpotContentStatistics>(DbContext.Database, sql, null).FirstOrDefault();
return Task.FromResult(record);
}
public Task<IEnumerable<Record12MonthStatistics>> 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<Record12MonthStatistics> recordlist;
recordlist = EntityFrameworkCoreExtensions.GetList<Record12MonthStatistics>(DbContext.Database, sql, null);
return Task.FromResult(recordlist);
}
public Task<int> 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<int> 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<IEnumerable<AllSpotRecordViewModel>> 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<AllSpotRecordViewModel> spotRecordlist;
spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, parameters);
return Task.FromResult(spotRecordlist);
}
public Task<IEnumerable<AllSpotRecordViewModel>> 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<AllSpotRecordViewModel> spotRecordlist;
spotRecordlist = EntityFrameworkCoreExtensions.GetList<AllSpotRecordViewModel>(DbContext.Database, sql, parameters);
return Task.FromResult(spotRecordlist);
}
public Task<IEnumerable<TispRecord30DaysStatistics>> 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<TispRecord30DaysStatistics> recordlist;
recordlist = EntityFrameworkCoreExtensions.GetList<TispRecord30DaysStatistics>(DbContext.Database, sql, parameters);
return Task.FromResult(recordlist);
}
public Task<IEnumerable<AlarmRecovery7DaysStatistics>> 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<AlarmRecovery7DaysStatistics> recordlist;
recordlist = EntityFrameworkCoreExtensions.GetList<AlarmRecovery7DaysStatistics>(DbContext.Database, sql, parameters);
return Task.FromResult(recordlist);
}
public Task<TodaySpotStatistics> 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<TodaySpotStatistics>(DbContext.Database, sql, parameters).FirstOrDefault();
return Task.FromResult(record);
}
public Task<TodaySpotContentStatistics> 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<TodaySpotContentStatistics>(DbContext.Database, sql, parameters).FirstOrDefault();
return Task.FromResult(record);
}
public Task<IEnumerable<Record12MonthStatistics>> 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<Record12MonthStatistics> recordlist;
recordlist = EntityFrameworkCoreExtensions.GetList<Record12MonthStatistics>(DbContext.Database, sql, parameters);
return Task.FromResult(recordlist);
}
}
}