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 TispSpotRouteRepository : RepositoryBase, ITispSpotRouteRepository { public TispSpotRouteRepository(InspectionDbContext dbContext) : base(dbContext) { } public Task DeleteByRouteIdAsync(Guid routeId) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("Id", routeId) }; int result = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, "DELETE from TISP_SpotRoute WHERE C_RouteCode = @Id", parameters); return Task.FromResult(result); } public Task> GetSpotRoutesAsyncByPage(TispSpotRouteSearchModel searchViewModel) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("spotId", searchViewModel.SpotId), new MySqlConnector.MySqlParameter("routeId", searchViewModel.RouteId) }; string sql = "SELECT B.C_ID AS C_RouteID, B.C_Name AS C_RouteName,B.C_ImageUrl AS C_RouteImageUrl,D.C_Code AS C_SpotID,D.C_Name AS C_SpotName,C.C_Name AS C_UserName,A.D_CreateOn From TISP_SpotRoute A LEFT JOIN TISP_Route B ON A.C_RouteCode = B.C_ID LEFT JOIN TSYS_User C on C.C_UserID = A.C_CreateBy LEFT JOIN TISP_Spot D ON D.C_Code = A.C_SpotCode WHERE B.C_Status ='1' AND D.C_Code IS NOT NULL "; if (!string.IsNullOrEmpty(searchViewModel.SpotId)) { sql += "AND A.C_SpotCode =@spotId "; } if (!string.IsNullOrEmpty(searchViewModel.RouteId)) { sql += " AND A.C_RouteCode =@routeId"; } var contentlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == contentlist || contentlist.First() == null) { PageData contentlistDetail = null; return Task.FromResult(contentlistDetail); } if (!string.IsNullOrEmpty(searchViewModel.SpotId)) { contentlist = contentlist.Where(x => x.C_SpotID.Equals(Guid.Parse(searchViewModel.SpotId))); } if (!string.IsNullOrEmpty(searchViewModel.RouteId)) { contentlist = contentlist.Where(x => x.C_RouteID.Equals(Guid.Parse(searchViewModel.RouteId))); } var q = from b in contentlist group b by b.C_RouteID into g select new TispSpotsRouteViewModel { C_RouteID = g.Key, RouteSpotList = (from c in g where c.C_SpotID != Guid.Empty select new RouteSpot { C_SpotID = c.C_SpotID, C_SpotName = c.C_SpotName, }).ToList(), C_RouteName = g.FirstOrDefault().C_RouteName, C_RouteImageUrl = g.FirstOrDefault().C_RouteImageUrl, D_CreateOn = g.FirstOrDefault().D_CreateOn, C_UserName = g.FirstOrDefault().C_UserName }; PageData pageData = new PageData { Totals = q.ToList().Count, Rows = searchViewModel.IsPagination ? q.Skip((searchViewModel.PageIndex - 1) * searchViewModel.PageSize).Take(searchViewModel.PageSize).ToList() : q.ToList() }; return Task.FromResult(pageData); } public Task> GetSpotsRouteAsyncByPage(TispSpotRouteSearchModel searchViewModel) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("spotId", searchViewModel.SpotId), new MySqlConnector.MySqlParameter("StoreCode", searchViewModel.C_StoreCode), new MySqlConnector.MySqlParameter("routeId", searchViewModel.RouteId) }; string sql = "SELECT B.C_Status,B.C_Remark, B.C_ID AS C_RouteID, B.C_Name AS C_RouteName,B.C_ImageUrl AS C_RouteImageUrl,D.C_Code AS C_SpotID,D.C_Name AS C_SpotName,C.C_Name AS C_UserName,A.D_CreateOn From TISP_Route B LEFT JOIN TISP_SpotRoute A ON A.C_RouteCode = B.C_ID LEFT JOIN TSYS_User C on C.C_UserID = A.C_CreateBy LEFT JOIN TISP_Spot D ON D.C_Code = A.C_SpotCode WHERE B.C_Status ='1' ";//AND D.C_Code IS NOT NULL sql = @"SELECT B.C_Status,B.C_Remark, B.C_ID AS C_RouteID, B.C_Name AS C_RouteName,B.C_ImageUrl AS C_RouteImageUrl,D.C_Code AS C_SpotID,D.C_StoreCode AS C_StoreCode,D.C_Name AS C_SpotName,C.C_Name AS C_UserName,A.D_CreateOn From TISP_Route B LEFT JOIN TISP_SpotRoute A ON A.C_RouteCode = B.C_ID LEFT JOIN TSYS_User C on C.C_UserID = A.C_CreateBy LEFT JOIN TISP_Spot D ON D.C_Code = A.C_SpotCode WHERE B.C_StoreCode =@StoreCode "; if (!string.IsNullOrEmpty(searchViewModel.SpotId)) { sql += "AND A.C_SpotCode =@spotId "; } if (!string.IsNullOrEmpty(searchViewModel.RouteId)) { sql += " AND A.C_RouteCode =@routeId"; } sql += " ORDER BY A.D_CreateOn DESC "; var contentlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); if (null == contentlist || contentlist.First() == null) { PageData contentlistDetail = null; return Task.FromResult(contentlistDetail); } if (!string.IsNullOrEmpty(searchViewModel.SpotId)) { contentlist = contentlist.Where(x => x.C_SpotID.Equals(Guid.Parse(searchViewModel.SpotId))); } if (!string.IsNullOrEmpty(searchViewModel.RouteId)) { contentlist = contentlist.Where(x => x.C_RouteID.Equals(Guid.Parse(searchViewModel.RouteId))); } if (!string.IsNullOrEmpty(searchViewModel.RouteName)) { contentlist = contentlist.Where(x => x.C_RouteName.Contains(searchViewModel.RouteName)); } var q = from b in contentlist group b by b.C_RouteID into g select new TispSpotsRouteViewModel { C_RouteID = g.Key, RouteSpotList = (from c in g where c.C_SpotID != Guid.Empty && c !=null select new RouteSpot { C_SpotID = c.C_SpotID, C_SpotName = c.C_SpotName, }).ToList(), C_RouteName = g.FirstOrDefault().C_RouteName, C_RouteImageUrl = g.FirstOrDefault().C_RouteImageUrl, D_CreateOn = g.FirstOrDefault().D_CreateOn, C_UserName = g.FirstOrDefault().C_UserName, C_Remark = g.FirstOrDefault().C_Remark, C_Status = g.FirstOrDefault().C_Status }; PageData pageData = new PageData { Totals = q.ToList().Count, Rows = searchViewModel.IsPagination ? q.Skip((searchViewModel.PageIndex - 1) * searchViewModel.PageSize).Take(searchViewModel.PageSize).ToList() : q.ToList() }; return Task.FromResult(pageData); } } }