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 TispSpotUserRepository : RepositoryBase, ITispSpotUserRepository { public TispSpotUserRepository(InspectionDbContext dbContext) : base(dbContext) { } public Task> GetBySpotIdAsync(Guid spotId) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("Id", spotId)}; var contentlist = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, "SELECT A.*,B.C_Name AS C_UserName,C.C_Name AS C_SpotName FROM TISP_SpotUser A,TSYS_User B,TISP_Spot C WHERE A.C_UserCode = B.C_UserID AND A.C_SpotCode = C.C_Code AND A.C_SpotCode = @Id", parameters); return Task.FromResult(contentlist.AsEnumerable()); } public Task> GetByUserIdAsync(Guid userId) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("Id", userId) }; string sql = @"SELECT A.*,B.C_Name AS C_UserName,C.C_Name AS C_SpotName ,D.C_Name AS StoreName,D.C_Code AS StoreCode FROM TISP_SpotUser A,TSYS_User B,TISP_Spot C , TPNT_Store D WHERE A.C_UserCode = B.C_UserID AND A.C_SpotCode = C.C_Code AND D.C_Code = C.C_StoreCode AND A.C_UserCode = @Id"; var contentlist = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters); var q = from b in contentlist group b by b.StoreCode into g select new TispUserStoreRouteSpotViewModel { StoreCode = g.Key, StoreName = g.FirstOrDefault().StoreName, SpotUserList = (from c in g select new SimSpot { C_SpotCode = c.C_SpotCode, C_SpotName = c.C_SpotName }).ToList() }; return Task.FromResult(q); } //public Task> GetStoreRouteSpotByUserIdAsync(Guid userId) //{ // var query = from a in DbContext.TBDM_Prov // join b in DbContext.TBDM_City // on a.C_Code equals b.C_ProvCode // join c in DbContext.TBDM_Area // on b.C_Code equals c.C_CityCode // group a by a.C_Code into g // select new TbdmProvViewModel // { // C_Code = g.First().C_Code, // C_Name = g.First().C_Name, // I_Sort = g.First().I_Sort, // C_Status = g.First().C_Status, // Citys = (from h in b group g by g into m // select new TbdmCity // { // C_Code = m.First().C_Code, // C_Name = m.First().C_Name, // I_Sort = m.First().I_Sort, // C_Status = m.First().C_Status, // }).ToList(), // }; // var v = query.ToList(); //} public Task> GetAsyncByPage(int pageSize, int pageIndex) { string sql = "SELECT A.*,B.C_Name AS C_UserName,C.C_Name AS C_SpotName FROM TISP_SpotUser A,TSYS_User B,TISP_Spot C WHERE A.C_UserCode = B.C_UserID AND A.C_SpotCode = C.C_Code"; var contentlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql,null,pageSize,pageIndex); return Task.FromResult(contentlist.AsEnumerable()); } public Task> GetGroupBySpotAsyncByPage(int pageSize, int pageIndex) { string sql = "SELECT C.C_UserID,C.C_Name AS C_UserName, B.C_Remark,A.C_Code AS C_SpotID,A.C_Name AS C_SpotName,A.C_Number,A.C_GPS,A.C_Position,A.C_QRCode From TISP_Spot A LEFT JOIN TISP_SpotUser B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C on B.C_UserCode = C.C_UserID"; var contentlist = EntityFrameworkCoreExtensions.GetList(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 where c.C_UserID != Guid.Empty select new SpotUser { C_UserID = c.C_UserID, C_UserName = c.C_UserName, }).ToList(), C_GPS = g.FirstOrDefault().C_GPS, C_SpotName = g.FirstOrDefault().C_SpotName, 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); } public Task> GetGroupBySpotAsyncByPage(TispSpotUserSearchModel searchViewModel) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("spotId", searchViewModel.SpotId), new MySqlConnector.MySqlParameter("userId", searchViewModel.UserId) }; string sql = "SELECT C.C_UserID,C.C_Name AS C_UserName, B.C_Remark,A.C_Code AS C_SpotID,A.C_Name AS C_SpotName,A.C_Number,A.C_GPS,A.C_Position,A.C_QRCode From TISP_Spot A LEFT JOIN TISP_SpotUser B ON A.C_Code = B.C_SpotCode LEFT JOIN TSYS_User C on B.C_UserCode = C.C_UserID WHERE A.C_Status ='1' "; //if (Guid.Empty != searchViewModel.SpotId) //{ // sql += "AND A.C_Code =@spotId "; //} //if (Guid.Empty != searchViewModel.UserId) //{ // sql += " AND C.C_UserID =@userId"; //} var contentlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null); 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.UserId)) { contentlist = contentlist.Where(x => x.C_UserID.Equals(Guid.Parse(searchViewModel.UserId))); } 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 where c.C_UserID != Guid.Empty select new SpotUser { C_UserID = c.C_UserID, C_UserName = c.C_UserName, }).ToList(), C_GPS = g.FirstOrDefault().C_GPS, C_SpotName = g.FirstOrDefault().C_SpotName, C_Number = g.FirstOrDefault().C_Number, C_Position = g.FirstOrDefault().C_Position, C_QRCode = g.FirstOrDefault().C_QRCode, C_Remark = g.FirstOrDefault().C_Remark }; 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 DeleteBySpotIdAsync(Guid spotId) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("Id", spotId) }; int result = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, "DELETE from TISP_SpotUser WHERE C_SpotCode = @Id", parameters); return Task.FromResult(result); } public Task DeleteByUserIdAsync(Guid userId) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("Id", userId) }; int result = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, "DELETE from TISP_SpotUser WHERE C_UserCode = @Id", parameters); return Task.FromResult(result); } public Task GetBySpotIdAndUserIdAsync(Guid spotId, Guid userId) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("spotId", spotId), new MySqlConnector.MySqlParameter("userId", userId) }; string sql = "Select count(*) from TISP_SpotUser WHERE C_SpotCode = @spotId And C_UserCode = @userId"; //string sql = "Select count(*) from TISP_SpotUser WHERE C_SpotCode = '08d8ff14-f250-4ba2-8499-8b96ac8fa407' And C_UserCode = '2812fd35-f276-4eed-822b-36a85d119437'"; //int result = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, "Select count(*) from TISP_SpotUser WHERE C_SpotCode = @spotId And C_UserCode = @userId", parameters); //return Task.FromResult(result); object result = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).Rows[0][0]; return Task.FromResult(int.Parse(result.ToString())); } } }