qlhb/Ropin.Inspection.Repository/ISP/TispSpotRouteRepository.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 TispSpotRouteRepository : RepositoryBase<TISP_SpotRoute, Guid>, ITispSpotRouteRepository
    {
        public TispSpotRouteRepository(InspectionDbContext dbContext) : base(dbContext)
        {

        }
        public Task<int> 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<PageData<TispSpotsRouteViewModel>> 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<TispSpotsRoutesViewModel>(DbContext.Database, sql, parameters);
            if (null == contentlist || contentlist.First() == null)
            {
                PageData<TispSpotsRouteViewModel> 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<RouteSpot>(),
                        C_RouteName = g.FirstOrDefault<TispSpotsRoutesViewModel>().C_RouteName,
                        C_RouteImageUrl = g.FirstOrDefault<TispSpotsRoutesViewModel>().C_RouteImageUrl,
                        D_CreateOn = g.FirstOrDefault<TispSpotsRoutesViewModel>().D_CreateOn,
                        C_UserName = g.FirstOrDefault<TispSpotsRoutesViewModel>().C_UserName
                    };
            PageData<TispSpotsRouteViewModel> pageData = new PageData<TispSpotsRouteViewModel>
            {
                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<PageData<TispSpotsRouteViewModel>> 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<TispSpotsRoutesViewModel>(DbContext.Database, sql, parameters);
            if (null == contentlist || contentlist.First() == null)
            {
                PageData<TispSpotsRouteViewModel> 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<RouteSpot>(),
                        C_RouteName = g.FirstOrDefault<TispSpotsRoutesViewModel>().C_RouteName,
                        C_RouteImageUrl = g.FirstOrDefault<TispSpotsRoutesViewModel>().C_RouteImageUrl,
                        D_CreateOn = g.FirstOrDefault<TispSpotsRoutesViewModel>().D_CreateOn,
                        C_UserName = g.FirstOrDefault<TispSpotsRoutesViewModel>().C_UserName,
                        C_Remark = g.FirstOrDefault<TispSpotsRoutesViewModel>().C_Remark,
                        C_Status = g.FirstOrDefault<TispSpotsRoutesViewModel>().C_Status
                    };
            PageData<TispSpotsRouteViewModel> pageData = new PageData<TispSpotsRouteViewModel>
            {
                Totals = q.ToList().Count,
                Rows = searchViewModel.IsPagination ? q.Skip((searchViewModel.PageIndex - 1) * searchViewModel.PageSize).Take(searchViewModel.PageSize).ToList() : q.ToList()

            };
            return Task.FromResult(pageData);

        }
    }
}