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

using Ropin.Inspection.Model.Entities;
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 TispSpotRepository : RepositoryBase<TISP_Spot, Guid>, ITispSpotRepository
    {
        public TispSpotRepository(InspectionDbContext dbContext) : base(dbContext)
        {

        }

        public List<TispSpotViewModel> GetTispSpotJoinContent()
        {
            return DbContext.TISP_Spot.Join(DbContext.TISP_SpotContent, spot => spot.C_Code, spotContent => spotContent.C_SpotCode, (spot, spotContent) => new TispSpotViewModel
            {
                C_Code = spot.C_Code,

        //C_Number = spot.C_Number,

        //C_Name = spot.C_Number,

        //        C_Position = spot.C_Number,


        //        C_QRCode = spot.C_Number,

        //        C_GPS = spot.C_Number,

        //        C_Remark = spot.C_Number,

                //ContentList = new TispContentViewModel { C_ID = spotContent.C_ID },

                C_CreateBy = spot.C_CreateBy,
                D_CreateOn = spot.D_CreateOn,

                //C_LastUpdatedBy = spot.C_LastUpdatedBy,
                //D_LastUpdatedOn = spot.D_LastUpdatedOn,

                //C_Status = spot.C_Status,
            }).ToList();
        }

        public Task<TispSpotViewModel> GetSpotByQRCodeAsync(string qRCode,string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("qRCode", qRCode), new MySqlConnector.MySqlParameter("storeCode", storeCode) };
            string sql = "SELECT C_Code,C_Number,C_Name,C_Position,C_QRCode,C_GPS,C_Remark,I_Offset From TISP_Spot WHERE C_QRCode =@qRCode AND C_StoreCode=@storeCode";
            TispSpotViewModel model = EntityFrameworkCoreExtensions.SqlQuery<TispSpotViewModel>(DbContext.Database, sql, parameters).FirstOrDefault();
            return Task.FromResult(model);
        }
        public Task<TispSpotDevStoreViewModel> GetDevStoreByQRCodeAsync(string qRCode, string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("qRCode", qRCode), new MySqlConnector.MySqlParameter("storeCode", storeCode) };
            string sql = @"SELECT A.C_Code,A.C_Number,A.C_Name,A.C_Position,A.C_QRCode,A.C_GPS,A.C_Remark,A.I_Offset,B.C_DevStoreCode,C.C_Name AS C_DevStoreName,C.C_NumberCode AS C_DevNumberCode From TISP_Spot A
INNER JOIN TDEV_DevSpot B ON B.C_SpotCode = A.C_Code
INNER JOIN TDEV_DevStore C ON C.C_ID = B.C_DevStoreCode
WHERE (A.C_QRCode =@qRCode OR C.C_NumberCode = @qRCode) And A.C_StoreCode = @storeCode And A.C_Status = '1' AND C.C_Status = '1'";
            TispSpotDevStoreViewModel model = EntityFrameworkCoreExtensions.SqlQuery<TispSpotDevStoreViewModel>(DbContext.Database, sql, parameters).FirstOrDefault();
            return Task.FromResult(model);
        }

        public Task<TispSpotViewModel> GetAllSpotBySqlAsync()
        {
            //MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("qRCode", qRCode) };
            string sql = "SELECT C_Code,C_Number,C_Name,C_Position,C_QRCode,C_GPS,C_Remark,I_Offset From TISP_Spot WHERE C_Status = '1' ORDER BY D_CreateOn ASC";
            TispSpotViewModel model = EntityFrameworkCoreExtensions.SqlQuery<TispSpotViewModel>(DbContext.Database, sql, null).FirstOrDefault();
            return Task.FromResult(model);
        }


        public Task<IEnumerable<TispSpotContentsViewModel>> GetSpotContentsByQRCodeAsync(string qRCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("qRCode", qRCode) };
            //string sql = "SELECT A.*,B.C_ContentCode AS C_ContentId,C.C_AlarmLevel AS C_ContentAlarmLevel,C.C_Name AS C_ContentName From TISP_Spot A LEFT JOIN TISP_SpotContent B ON A.C_Code = B.C_SpotCode LEFT JOIN TISP_Content C ON C.C_ID = B.C_ContentCode";
            string sql = "SELECT A.*,B.C_ContentCode AS C_ContentId,C.C_AlarmLevel AS C_ContentAlarmLevel,C.C_Name AS C_ContentName From TISP_Content C LEFT JOIN TISP_SpotContent B ON C.C_ID = B.C_ContentCode  LEFT OUTER  JOIN  TISP_Spot A ON A.C_Code = B.C_SpotCode WHERE A.C_QRCode ='6973361880083'";
            var contentlist = EntityFrameworkCoreExtensions.GetList<TispSpotsContentsViewModel>(DbContext.Database, sql, parameters);
            //var spotContentList = contentlist.Where(i => ("6973361880083").Equals(i.C_QRCode));
            if (null == contentlist || contentlist.First() == null)
                throw new Exception("没有数据记录");
            var q = from b in contentlist
                    group b by b.C_Code into g
                    select new TispSpotContentsViewModel
                    {
                        C_Code = g.Key,
                        SpotContentList = (from c in g select new SpotContent { C_ContentId = c.C_ContentId, C_ContentAlarmLevel = c.C_ContentAlarmLevel, C_ContentName = c.C_ContentName }).ToList<SpotContent>(),
                        C_GPS = g.First().C_GPS,
                        C_Name = g.FirstOrDefault<TispSpotsContentsViewModel>().C_Name,
                        C_Number = g.FirstOrDefault<TispSpotsContentsViewModel>().C_Number,
                        C_Position = g.FirstOrDefault<TispSpotsContentsViewModel>().C_Position,
                        C_QRCode = g.FirstOrDefault<TispSpotsContentsViewModel>().C_QRCode,
                        C_Remark = g.FirstOrDefault<TispSpotsContentsViewModel>().C_Remark
                    };

            //TISP_Spot spot = DbContext.TISP_Spot.Find(DbContext.TISP_SpotContent, spot => spot.C_Code,  (spot) => new SpotContent
            //{
                
            //}).ToList();

            return Task.FromResult(q);




            //MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("qRCode", qRCode) };
            //string sql = "SELECT A.*,B.C_ContentCode AS C_ContentId,C.C_AlarmLevel AS C_ContentAlarmLevel,C.C_Name AS C_ContentName From TISP_Spot A LEFT JOIN TISP_SpotContent B ON A.C_Code = B.C_SpotCode LEFT JOIN TISP_Content C ON C.C_ID = B.C_ContentCode WHERE A.C_QRCode = '6973361880083'";
            //var contentlist = EntityFrameworkCoreExtensions.GetList<TispSpotsContentsViewModel>(DbContext.Database, sql, parameters);
            //var q = from b in contentlist
            //        group b by b.C_Code into g
            //        select new TispSpotContentsViewModel
            //        {
            //            C_Code = g.Key,
            //            SpotContentList = (from c in g select new SpotContent { C_ContentId = c.C_ContentId, C_ContentAlarmLevel = c.C_ContentAlarmLevel, C_ContentName = c.C_ContentName }).ToList<SpotContent>(),
            //            C_GPS = g.FirstOrDefault<TispSpotsContentsViewModel>().C_GPS,
            //            C_Name = g.FirstOrDefault<TispSpotsContentsViewModel>().C_Name,
            //            C_Number = g.FirstOrDefault<TispSpotsContentsViewModel>().C_Number,
            //            C_Position = g.FirstOrDefault<TispSpotsContentsViewModel>().C_Position,
            //            C_QRCode = g.FirstOrDefault<TispSpotsContentsViewModel>().C_QRCode,
            //            C_Remark = g.FirstOrDefault<TispSpotsContentsViewModel>().C_Remark
            //        };
            //return Task.FromResult(q);
        }

    }
}