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

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()));
        }
    }
}