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, ITispSpotRepository { public TispSpotRepository(InspectionDbContext dbContext) : base(dbContext) { } public List 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 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(DbContext.Database, sql, parameters).FirstOrDefault(); return Task.FromResult(model); } public Task 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(DbContext.Database, sql, parameters).FirstOrDefault(); return Task.FromResult(model); } public Task 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(DbContext.Database, sql, null).FirstOrDefault(); return Task.FromResult(model); } public Task> 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(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(), C_GPS = g.First().C_GPS, C_Name = g.FirstOrDefault().C_Name, C_Number = g.FirstOrDefault().C_Number, C_Position = g.FirstOrDefault().C_Position, C_QRCode = g.FirstOrDefault().C_QRCode, C_Remark = g.FirstOrDefault().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(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(), // C_GPS = g.FirstOrDefault().C_GPS, // C_Name = g.FirstOrDefault().C_Name, // C_Number = g.FirstOrDefault().C_Number, // C_Position = g.FirstOrDefault().C_Position, // C_QRCode = g.FirstOrDefault().C_QRCode, // C_Remark = g.FirstOrDefault().C_Remark // }; //return Task.FromResult(q); } } }