qlhb/Ropin.Inspection.Repository/PRD/TprdProductRepository.cs

using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Entities;
using Ropin.Inspection.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository
{
    public class TprdProductRepository : RepositoryBase<TPRD_Product, Guid>, ITprdProductRepository
    {
        public TprdProductRepository(InspectionDbContext dbContext) : base(dbContext)
        {

        }
        public Task<IEnumerable<AllProductWithDevViewModel>> GetProductWithDataByAsync(TprdProductWithDataSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode), new MySqlConnector.MySqlParameter("AreaCode", searchModel.C_AreaCode) };
            string sql = @"SELECT A.*,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 AND A.C_StoreCode =@StoreCode ";

            sql = @"SELECT A.*,C.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
LEFT JOIN (
SELECT C.*,D.C_Name AS DevDataConfigName ,D.C_Code AS DevDataConfigCode FROM  TDEV_DevDataConfig D 
LEFT JOIN TDEV_DevData C ON D.C_Code = C.C_ConfigCode AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C.C_DeviceCode , C.C_ConfigCode ORDER BY C.D_CreateOn desc
)C ON A.C_DeviceCode = C.C_DeviceCode
WHERE B.C_Status = '1' "; //AND A.C_StoreCode =@StoreCode 
            //if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
            //{
            //    sql += " AND A.C_AreaCode =@AreaCode";
            //}

            sql = @"SELECT A.*,C.DevDataConfigName,C.C_Value AS DevDataValue,C.D_CreateOn AS DevDataTime,B.C_Name AS DevName,B.C_MachineCode AS DevMachineCode, D.DevAlertConfigName,D.C_Value AS DevAlertValue,D.D_CreateOn AS DevAlertTime
FROM TPRD_Product A 
LEFT JOIN TDEV_Device B ON B.C_Code = A.C_DeviceCode AND B.C_Status = '1'
LEFT JOIN (
SELECT C.*,D.C_Name AS DevDataConfigName ,D.C_Code AS DevDataConfigCode FROM  TDEV_DevDataConfig D 
LEFT JOIN TDEV_DevData C ON D.C_Code = C.C_ConfigCode AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C.C_DeviceCode , C.C_ConfigCode ORDER BY C.D_CreateOn desc
)C ON A.C_DeviceCode = C.C_DeviceCode
LEFT JOIN (
SELECT C.*,D.C_Name AS DevAlertConfigName ,D.C_Code AS DevAlertConfigCode FROM  TDEV_DevAlertConfig D 
LEFT JOIN TDEV_DevAlert C ON D.C_Code = C.C_ConfigCode AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C.C_DeviceCode , C.C_ConfigCode ORDER BY C.D_CreateOn desc
)D ON A.C_DeviceCode = D.C_DeviceCode";

            sql = @"SELECT A.*,C.DevDataConfigName,C.C_Value AS DevDataValue,C.D_CreateOn AS DevDataTime,B.C_Name AS DevName,B.C_MachineCode AS DevMachineCode, D.DevAlertConfigName,D.C_Value AS DevAlertValue,D.D_CreateOn AS DevAlertTime,
E.C_Name,
E.C_Spec,
E.C_Brand,
E.C_Vender,
E.C_ProdArea
FROM TPRD_Product A 
LEFT JOIN TDEV_Device B ON B.C_Code = A.C_DeviceCode AND B.C_Status = '1'
LEFT JOIN (
SELECT C.*,D.C_Name AS DevDataConfigName ,D.C_Code AS DevDataConfigCode FROM  TDEV_DevDataConfig D 
LEFT JOIN TDEV_DevData C ON D.C_Code = C.C_ConfigCode AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C.C_DeviceCode , C.C_ConfigCode ORDER BY C.D_CreateOn desc
)C ON A.C_DeviceCode = C.C_DeviceCode
LEFT JOIN (
SELECT C.*,D.C_Name AS DevAlertConfigName ,D.C_Code AS DevAlertConfigCode FROM  TDEV_DevAlertConfig D 
LEFT JOIN TDEV_DevAlert C ON D.C_Code = C.C_ConfigCode AND DATE_FORMAT(C.D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C.C_DeviceCode , C.C_ConfigCode ORDER BY C.D_CreateOn desc
)D ON A.C_DeviceCode = D.C_DeviceCode
LEFT JOIN TPRD_ProdSKU E
ON E.C_SKUCode = A.C_SKUCode";
            IEnumerable<TprdProductWithDataViewModel> productRecordlist;
            if (!searchModel.IsPagination)
            {
                if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
                {
                    productRecordlist = EntityFrameworkCoreExtensions.GetList<TprdProductWithDataViewModel>(DbContext.Database, sql, null).Where(c => c.C_StoreCode == searchModel.C_StoreCode && c.C_AreaCode == searchModel.C_AreaCode);
                }
                else
                {
                    productRecordlist = EntityFrameworkCoreExtensions.GetList<TprdProductWithDataViewModel>(DbContext.Database, sql, null).Where(c => c.C_StoreCode == searchModel.C_StoreCode);
                }
                

            }
            else
            {
                if (!string.IsNullOrEmpty(searchModel.C_AreaCode))
                {
                    productRecordlist = EntityFrameworkCoreExtensions.GetList<TprdProductWithDataViewModel>(DbContext.Database, sql, null, searchModel.PageSize, searchModel.PageIndex).Where(c => c.C_StoreCode == searchModel.C_StoreCode && c.C_AreaCode == searchModel.C_AreaCode);
                }
                else
                {
                    productRecordlist = EntityFrameworkCoreExtensions.GetList<TprdProductWithDataViewModel>(DbContext.Database, sql, null, searchModel.PageSize, searchModel.PageIndex).Where(c => c.C_StoreCode == searchModel.C_StoreCode);
                }
                
            }
            if (null == productRecordlist || !productRecordlist.Any())
            {
                IEnumerable<AllProductWithDevViewModel> recordItemDetail = null;
                return Task.FromResult(recordItemDetail);
            }
            var q = from b in productRecordlist
                    group b by b.C_Code into g
                    select new AllProductWithDevViewModel
                    {
                        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_SKUCode = g.First().C_StoreCode,
                        C_DeviceCode = g.First().C_DeviceCode,
                        C_SpotCode = g.First().C_SpotCode,
                        D_ProdDate = g.First().D_ProdDate,
                        D_ValiDate = g.First().D_ValiDate,
                        C_ImageUrl = g.First().C_ImageUrl,
                        D_LastISP = g.First().D_LastISP,
                        I_IsAlarm = g.First().I_IsAlarm,
                        C_AlarmMsg = g.First().C_AlarmMsg,
                        I_Sort = g.First().I_Sort,

                        C_QRCode = g.First().C_QRCode,

                        C_Remark = g.First().C_Remark,

                        C_CreateBy = g.First().C_CreateBy,
                        D_CreateOn = g.First().D_CreateOn,

                        C_LastUpdatedBy = g.First().C_LastUpdatedBy,
                        D_LastUpdatedOn = g.First().D_LastUpdatedOn,
                        I_Status = g.First().I_Status,
                        C_Name = g.First().C_Name,
                        C_Spec = g.First().C_Spec,
                        C_Brand = g.First().C_Brand,
                        C_Vender = g.First().C_Vender,
                        C_ProdArea = g.First().C_ProdArea,

                        DevDatas = (from c in g
                                       select new DevData
                                       {
                                           DevDataConfigName = c.DevDataConfigName,
                                           DevDataValue = c.DevDataValue,
                                           DevDataTime = c.DevDataTime,
                                           DevName = c.DevName,
                                           DevMachineCode = c.DevMachineCode,
                                           DevAlertConfigName = c.DevAlertConfigName,
                                           DevAlertValue = c.DevAlertValue,
                                           DevAlertTime = c.DevAlertTime,
                                       }),
                    };

            return Task.FromResult(q);
        }
        public Task<IEnumerable<TprdProductViewModel>> GetAlertProductsByAsync(string storeCode)
        {
            Expression<Func<TPRD_Product, bool>> ex = i => (i.I_Status == 2 || i.I_Status == 1) && i.D_ValiDate != null && i.D_ValiDate > DateTime.Now && i.C_StoreCode == storeCode;
            var query = from a in DbContext.TPRD_Product
                        join b in DbContext.TPRD_ProdSKU
                        on a.C_SKUCode equals b.C_SKUCode
                        join c in DbContext.TPNT_Store
                        on a.C_StoreCode equals c.C_Code
                        join d in DbContext.TDEV_Device
                        on a.C_DeviceCode equals d.C_Code
                        join e in DbContext.TISP_Spot
                        on a.C_SpotCode equals e.C_Code
                        join f in DbContext.TPNT_Area
                        on a.C_AreaCode equals f.C_Code
                        where a.C_StoreCode == storeCode && (a.I_Status == 2 || a.I_Status == 1) && a.D_ValiDate != null && a.D_ValiDate > DateTime.Now
                        select new TprdProductViewModel
                        {
                            C_Code = a.C_Code,
                            C_QRCode = a.C_QRCode,
                            C_SKUCode = a.C_SKUCode,
                            C_StoreCode = a.C_StoreCode,
                            C_DeviceCode = a.C_DeviceCode,
                            C_SpotCode = a.C_SpotCode,
                            C_AreaCode = a.C_AreaCode,
                            F_Map_X = a.F_Map_X,
                            F_Map_Y = a.F_Map_Y,
                            D_ProdDate = a.D_ProdDate,
                            D_ValiDate = a.D_ValiDate,
                            C_ImageUrl = a.C_ImageUrl,
                            D_LastISP = a.D_LastISP,
                            I_IsAlarm = a.I_IsAlarm,
                            C_AlarmMsg = a.C_AlarmMsg,
                            I_Sort = a.I_Sort,
                            C_Remark = a.C_Remark,
                            C_CreateBy = a.C_CreateBy,
                            D_CreateOn = a.D_CreateOn,
                            C_LastUpdatedBy = a.C_LastUpdatedBy,
                            D_LastUpdatedOn = a.D_LastUpdatedOn,
                            I_Status = a.I_Status,
                            SpotName = e.C_Name,
                            AreaName = f.C_Name,
                            StoreName = c.C_Name,
                            DeviceName = d.C_Name,
                            SKUName = b.C_Name,
                        };
            return Task.FromResult(query.AsEnumerable());
        }
        public Task<TprdProductViewModel> GetAlertProductByCodeAsync(Guid code)
        {
            
            var query = from a in DbContext.TPRD_Product
                        join b in DbContext.TPRD_ProdSKU
                        on a.C_SKUCode equals b.C_SKUCode
                        join c in DbContext.TPNT_Store
                        on a.C_StoreCode equals c.C_Code
                        join d in DbContext.TDEV_Device
                        on a.C_DeviceCode equals d.C_Code
                        join e in DbContext.TISP_Spot
                        on a.C_SpotCode equals e.C_Code
                        join f in DbContext.TPNT_Area
                        on a.C_AreaCode equals f.C_Code
                        where a.C_Code == code
                        select new TprdProductViewModel
                        {
                            C_Code = a.C_Code,
                            C_QRCode = a.C_QRCode,
                            C_SKUCode = a.C_SKUCode,
                            C_StoreCode = a.C_StoreCode,
                            C_DeviceCode = a.C_DeviceCode,
                            C_SpotCode = a.C_SpotCode,
                            C_AreaCode = a.C_AreaCode,
                            F_Map_X = a.F_Map_X,
                            F_Map_Y = a.F_Map_Y,
                            D_ProdDate = a.D_ProdDate,
                            D_ValiDate = a.D_ValiDate,
                            C_ImageUrl = a.C_ImageUrl,
                            D_LastISP = a.D_LastISP,
                            I_IsAlarm = a.I_IsAlarm,
                            C_AlarmMsg = a.C_AlarmMsg,
                            I_Sort = a.I_Sort,
                            C_Remark = a.C_Remark,
                            C_CreateBy = a.C_CreateBy,
                            D_CreateOn = a.D_CreateOn,
                            C_LastUpdatedBy = a.C_LastUpdatedBy,
                            D_LastUpdatedOn = a.D_LastUpdatedOn,
                            I_Status = a.I_Status,
                            SpotName = e.C_Name,
                            AreaName = f.C_Name,
                            StoreName = c.C_Name,
                            DeviceName = d.C_Name,
                            SKUName = b.C_Name,
                        };
            return Task.FromResult(query.FirstOrDefault());
        }
        public Task<IEnumerable<TprdProductViewModel>> GetValiDateProductsByAsync(string storeCode)
        {
            Expression<Func<TPRD_Product, bool>> ex = i => (i.I_Status == 2 || i.I_Status == 1) && i.I_IsAlarm == 0 && i.C_StoreCode == storeCode;
            var query = from a in DbContext.TPRD_Product
                        join b in DbContext.TPRD_ProdSKU
                        on a.C_SKUCode equals b.C_SKUCode
                        join c in DbContext.TPNT_Store
                        on a.C_StoreCode equals c.C_Code
                        join d in DbContext.TDEV_Device
                        on a.C_DeviceCode equals d.C_Code
                        join e in DbContext.TISP_Spot
                        on a.C_SpotCode equals e.C_Code
                        join f in DbContext.TPNT_Area
                        on a.C_AreaCode equals f.C_Code
                        where a.C_StoreCode == storeCode && (a.I_Status == 2 || a.I_Status == 1) && a.I_IsAlarm == 0
                        select new TprdProductViewModel
                        {
                            C_Code = a.C_Code,
                            C_QRCode = a.C_QRCode,
                            C_SKUCode = a.C_SKUCode,
                            C_StoreCode = a.C_StoreCode,
                            C_DeviceCode = a.C_DeviceCode,
                            C_SpotCode = a.C_SpotCode,
                            C_AreaCode = a.C_AreaCode,
                            F_Map_X = a.F_Map_X,
                            F_Map_Y = a.F_Map_Y,
                            D_ProdDate = a.D_ProdDate,
                            D_ValiDate = a.D_ValiDate,
                            C_ImageUrl = a.C_ImageUrl,
                            D_LastISP = a.D_LastISP,
                            I_IsAlarm = a.I_IsAlarm,
                            C_AlarmMsg = a.C_AlarmMsg,
                            I_Sort = a.I_Sort,
                            C_Remark = a.C_Remark,
                            C_CreateBy = a.C_CreateBy,
                            D_CreateOn = a.D_CreateOn,
                            C_LastUpdatedBy = a.C_LastUpdatedBy,
                            D_LastUpdatedOn = a.D_LastUpdatedOn,
                            I_Status = a.I_Status,
                            SpotName = e.C_Name,
                            AreaName = f.C_Name,
                            StoreName = c.C_Name,
                            DeviceName = d.C_Name,
                            SKUName = b.C_Name,
                        };
            return Task.FromResult(query.AsEnumerable());
        }
        public Task<TprdProductViewModel> GetValiDateProductByCodeAsync(Guid code)
        {
            var query = from a in DbContext.TPRD_Product
                        join b in DbContext.TPRD_ProdSKU
                        on a.C_SKUCode equals b.C_SKUCode
                        join c in DbContext.TPNT_Store
                        on a.C_StoreCode equals c.C_Code
                        join d in DbContext.TDEV_Device
                        on a.C_DeviceCode equals d.C_Code
                        join e in DbContext.TISP_Spot
                        on a.C_SpotCode equals e.C_Code
                        join f in DbContext.TPNT_Area
                        on a.C_AreaCode equals f.C_Code
                        where a.C_Code == code
                        select new TprdProductViewModel
                        {
                            C_Code = a.C_Code,
                            C_QRCode = a.C_QRCode,
                            C_SKUCode = a.C_SKUCode,
                            C_StoreCode = a.C_StoreCode,
                            C_DeviceCode = a.C_DeviceCode,
                            C_SpotCode = a.C_SpotCode,
                            C_AreaCode = a.C_AreaCode,
                            F_Map_X = a.F_Map_X,
                            F_Map_Y = a.F_Map_Y,
                            D_ProdDate = a.D_ProdDate,
                            D_ValiDate = a.D_ValiDate,
                            C_ImageUrl = a.C_ImageUrl,
                            D_LastISP = a.D_LastISP,
                            I_IsAlarm = a.I_IsAlarm,
                            C_AlarmMsg = a.C_AlarmMsg,
                            I_Sort = a.I_Sort,
                            C_Remark = a.C_Remark,
                            C_CreateBy = a.C_CreateBy,
                            D_CreateOn = a.D_CreateOn,
                            C_LastUpdatedBy = a.C_LastUpdatedBy,
                            D_LastUpdatedOn = a.D_LastUpdatedOn,
                            I_Status = a.I_Status,
                            SpotName = e.C_Name,
                            AreaName = f.C_Name,
                            StoreName = c.C_Name,
                            DeviceName = d.C_Name,
                            SKUName = b.C_Name,
                        };
            return Task.FromResult(query.FirstOrDefault());
        }
        public IEnumerable<TprdDeviceByAreaViewModel> GetDeviceByAreaCode(TprdDeviceByAreaSearchModel searchModel)
        {
            var query = from b in DbContext.TDEV_Device
                        join a in DbContext.TPRD_Product
                        on b.C_Code equals a.C_DeviceCode
                        where a.C_StoreCode == searchModel.C_StoreCode && (string.IsNullOrEmpty(searchModel.C_AreaCode) ? 1 == 1 : a.C_AreaCode == searchModel.C_AreaCode)
                        //group a by a.C_DeviceCode into g
                        select new TprdDeviceByAreaViewModel
                        {
                            DeviceCode = a.C_DeviceCode,
                            I_Status = a.I_Status,
                            DeviceName = b.C_Name,
                            DeviceMachineCode = b.C_MachineCode,
                            DeviceStatus = b.C_Status,
                        }
                        ;
            //var q = from a in query
            //        group a by a.DeviceCode into g
            //        select new TprdDeviceByAreaViewModel
            //        {
            //            DeviceCode = g.First().DeviceCode,
            //            I_Status = g.First().I_Status,
            //            DeviceName = g.First().DeviceName,
            //            DeviceMachineCode = g.First().DeviceMachineCode,
            //            DeviceStatus = g.First().DeviceStatus,
            //        };
            var list = query.ToList().GroupBy(c => c.DeviceCode);
            return query.ToList().GroupBy(c=>c.DeviceCode).Select(h=> 
            new TprdDeviceByAreaViewModel 
            { 
                DeviceCode = h.First().DeviceCode,
                I_Status = h.First().I_Status,
                DeviceName = h.First().DeviceName,
                DeviceMachineCode = h.First().DeviceMachineCode,
                DeviceStatus = h.First().DeviceStatus,
            }
                ).AsEnumerable();
        }

        public Task<TprdProductViewModel> GetProductByQRCodeAsync(string QRCode, string storeCode)
        {
            var query = from a in DbContext.TPRD_Product
                        join b in DbContext.TPRD_ProdSKU
                        on a.C_SKUCode equals b.C_SKUCode
                        join c in DbContext.TPNT_Store
                        on a.C_StoreCode equals c.C_Code
                        join d in DbContext.TDEV_Device
                        on a.C_DeviceCode equals d.C_Code into temp
                        from tt in temp.DefaultIfEmpty()
                        join e in DbContext.TISP_Spot
                        on a.C_SpotCode equals e.C_Code
                        join f in DbContext.TPNT_Area
                        on a.C_AreaCode equals f.C_Code
                        where a.C_QRCode == QRCode && a.C_StoreCode == storeCode
                        select new TprdProductViewModel
                        {
                            C_Code = a.C_Code,
                            C_QRCode = a.C_QRCode,
                            C_SKUCode = a.C_SKUCode,
                            C_StoreCode = a.C_StoreCode,
                            C_DeviceCode = a.C_DeviceCode,
                            C_SpotCode = a.C_SpotCode,
                            C_AreaCode = a.C_AreaCode,
                            F_Map_X = a.F_Map_X,
                            F_Map_Y = a.F_Map_Y,
                            D_ProdDate = a.D_ProdDate,
                            D_ValiDate = a.D_ValiDate,
                            C_ImageUrl = a.C_ImageUrl,
                            D_LastISP = a.D_LastISP,
                            I_IsAlarm = a.I_IsAlarm,
                            C_AlarmMsg = a.C_AlarmMsg,
                            I_Sort = a.I_Sort,
                            C_Remark = a.C_Remark,
                            C_CreateBy = a.C_CreateBy,
                            D_CreateOn = a.D_CreateOn,
                            C_LastUpdatedBy = a.C_LastUpdatedBy,
                            D_LastUpdatedOn = a.D_LastUpdatedOn,
                            I_Status = a.I_Status,
                            SpotName = e.C_Name,
                            AreaName = f.C_Name,
                            StoreName = c.C_Name,
                            DeviceName = tt == null ? "" : tt.C_Name,
                            SKUName = b.C_Name,
                        };
            return Task.FromResult(query.FirstOrDefault());
        }
    }
}