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<TISP_SpotUser, Guid>, ITispSpotUserRepository
{
public TispSpotUserRepository(InspectionDbContext dbContext) : base(dbContext)
{
}
public Task<IEnumerable<TispSpotUserViewModel>> GetBySpotIdAsync(Guid spotId)
{
MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("Id", spotId)};
var contentlist = EntityFrameworkCoreExtensions.SqlQuery<TispSpotUserViewModel>(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<IEnumerable<TispUserStoreRouteSpotViewModel>> 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<TispSpotUserViewModel>(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<TispSpotUserViewModel>().StoreName,
SpotUserList = (from c in g select new SimSpot { C_SpotCode = c.C_SpotCode, C_SpotName = c.C_SpotName }).ToList<SimSpot>()
};
return Task.FromResult(q);
}
//public Task<IEnumerable<TispUserStoreRouteSpotViewModel>> 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<TbdmCity>(),
// };
// var v = query.ToList();
//}
public Task<IEnumerable<TispSpotUserViewModel>> 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<TispSpotUserViewModel>(DbContext.Database, sql,null,pageSize,pageIndex);
return Task.FromResult(contentlist.AsEnumerable());
}
public Task<IEnumerable<TispSpotUsersViewModel>> 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<TispSpotsUsersViewModel>(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<SpotUser>(),
C_GPS = g.FirstOrDefault<TispSpotsUsersViewModel>().C_GPS,
C_SpotName = g.FirstOrDefault<TispSpotsUsersViewModel>().C_SpotName,
C_Number = g.FirstOrDefault<TispSpotsUsersViewModel>().C_Number,
C_Position = g.FirstOrDefault<TispSpotsUsersViewModel>().C_Position,
C_QRCode = g.FirstOrDefault<TispSpotsUsersViewModel>().C_QRCode,
C_Remark = g.FirstOrDefault<TispSpotsUsersViewModel>().C_Remark
};
return Task.FromResult(q);
}
public Task<PageData<TispSpotUsersViewModel>> 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<TispSpotsUsersViewModel>(DbContext.Database, sql, null);
if (null == contentlist || contentlist.First() == null)
{
PageData<TispSpotUsersViewModel> 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<SpotUser>(),
C_GPS = g.FirstOrDefault<TispSpotsUsersViewModel>().C_GPS,
C_SpotName = g.FirstOrDefault<TispSpotsUsersViewModel>().C_SpotName,
C_Number = g.FirstOrDefault<TispSpotsUsersViewModel>().C_Number,
C_Position = g.FirstOrDefault<TispSpotsUsersViewModel>().C_Position,
C_QRCode = g.FirstOrDefault<TispSpotsUsersViewModel>().C_QRCode,
C_Remark = g.FirstOrDefault<TispSpotsUsersViewModel>().C_Remark
};
PageData<TispSpotUsersViewModel> pageData = new PageData<TispSpotUsersViewModel>
{
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<int> 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<int> 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<int> 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()));
}
}
}