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

using Ropin.Inspection.Model.Entities;
using Ropin.Inspection.Model.SearchModel;
using Ropin.Inspection.Model.ViewModel;
using Ropin.Inspection.Repository.Interface;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository
{
    public class 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);
        }
    }
}