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

        }
        public Task<int> DeleteByRegionIdAsync(Guid RegionId)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("Id", RegionId) };
            int result = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, "DELETE from TISP_SpotRegion WHERE C_RegionCode = @Id", parameters);
            return Task.FromResult(result);
        }
        public Task<PageData<TispSpotsRegionViewModel>> GetSpotRegionsAsyncByPage(TispSpotRegionSearchModel searchViewModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("spotId", searchViewModel.SpotId),
                new MySqlConnector.MySqlParameter("RegionId", searchViewModel.RegionId) };
            string sql = "SELECT B.C_ID AS C_RegionID, B.C_Name AS C_RegionName,B.C_ImageUrl AS C_RegionImageUrl,D.C_Code AS C_SpotID,D.C_Name AS C_SpotName,D.C_GPS AS C_SpotGPS,C.C_Name AS C_UserName,A.D_CreateOn From TISP_SpotRegion  A  LEFT JOIN TISP_Region B  ON A.C_RegionCode = 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'";
            if (!string.IsNullOrEmpty(searchViewModel.SpotId))
            {
                sql += "AND A.C_SpotCode =@spotId ";
            }
            if (!string.IsNullOrEmpty(searchViewModel.RegionId))
            {
                sql += " AND A.C_RegionCode =@RegionId";
            }
            var contentlist = EntityFrameworkCoreExtensions.GetList<TispSpotsRegionsViewModel>(DbContext.Database, sql, parameters);
            if (null == contentlist || contentlist.First() == null)
            {
                PageData<TispSpotsRegionViewModel> 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.RegionId))
            {
                contentlist = contentlist.Where(x => x.C_RegionID.Equals(Guid.Parse(searchViewModel.RegionId)));
            }
            var q = from b in contentlist
                    group b by b.C_RegionID into g
                    select new TispSpotsRegionViewModel
                    {
                        C_RegionID = g.Key,
                        RegionSpotList = (from c in g where c.C_SpotID != Guid.Empty select new RegionSpot { C_SpotID = c.C_SpotID, C_SpotName = c.C_SpotName,C_SpotGPS = c.C_SpotGPS }).ToList<RegionSpot>(),
                        C_RegionName = g.FirstOrDefault<TispSpotsRegionsViewModel>().C_RegionName,
                        C_RegionImageUrl = g.FirstOrDefault<TispSpotsRegionsViewModel>().C_RegionImageUrl,
                        D_CreateOn = g.FirstOrDefault<TispSpotsRegionsViewModel>().D_CreateOn,
                        C_UserName = g.FirstOrDefault<TispSpotsRegionsViewModel>().C_UserName
                    };
            PageData<TispSpotsRegionViewModel> pageData = new PageData<TispSpotsRegionViewModel>
            {
                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<TispSpotsRegionViewModel>> GetSpotsRegionAsyncByPage(TispSpotRegionSearchModel searchViewModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("spotId", searchViewModel.SpotId),
                new MySqlConnector.MySqlParameter("RegionId", searchViewModel.RegionId) };
            //string sql = "SELECT B.C_Status,B.C_Remark, B.C_ID AS C_RegionID, B.C_Name AS C_RegionName,B.C_ImageUrl AS C_RegionImageUrl,D.C_Code AS C_SpotID,D.C_Name AS C_SpotName,D.C_GPS AS C_SpotGPS,C.C_Name AS C_UserName,A.D_CreateOn From TISP_Region B LEFT JOIN TISP_SpotRegion A  ON A.C_RegionCode = 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'";
            string sql = "SELECT B.C_Status,B.C_Remark, B.C_ID AS C_RegionID, B.C_Name AS C_RegionName,B.C_ImageUrl AS C_RegionImageUrl,D.C_Code AS C_SpotID,D.C_Name AS C_SpotName,D.C_GPS AS C_SpotGPS, E.C_Status AS C_SpotStatus,  C.C_Name AS C_UserName,A.D_CreateOn From TISP_Region B LEFT JOIN TISP_SpotRegion A  ON A.C_RegionCode = 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 LEFT JOIN  (SELECT * FROM TISP_Record WHERE  DATE_FORMAT(D_CreateOn,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 0 DAY) GROUP BY C_SpotCode ORDER BY D_CreateOn desc ) E ON E.C_SpotCode = D.C_Code  WHERE B.C_Status ='1' ";
            if (!string.IsNullOrEmpty(searchViewModel.SpotId))
            {
                sql += "AND A.C_SpotCode =@spotId ";
            }
            if (!string.IsNullOrEmpty(searchViewModel.RegionId))
            {
                sql += " AND B.C_ID =@RegionId";
            }
            var contentlist = EntityFrameworkCoreExtensions.GetList<TispSpotsRegionsViewModel>(DbContext.Database, sql, parameters);
            if (null == contentlist || contentlist.First() == null)
            {
                PageData<TispSpotsRegionViewModel> 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.RegionId))
            {
                contentlist = contentlist.Where(x => x.C_RegionID.Equals(Guid.Parse(searchViewModel.RegionId)));
            }
            var q = from b in contentlist
                    group b by b.C_RegionID into g
                    select new TispSpotsRegionViewModel
                    {
                        C_RegionID = g.Key,
                        RegionSpotList = (from c in g where c.C_SpotID != Guid.Empty select new RegionSpot { C_SpotID = c.C_SpotID, C_SpotName = c.C_SpotName,C_SpotGPS = c.C_SpotGPS,C_SpotStatus = c.C_SpotStatus }).ToList<RegionSpot>(),
                        C_RegionName = g.FirstOrDefault<TispSpotsRegionsViewModel>().C_RegionName,
                        C_RegionImageUrl = g.FirstOrDefault<TispSpotsRegionsViewModel>().C_RegionImageUrl,
                        D_CreateOn = g.FirstOrDefault<TispSpotsRegionsViewModel>().D_CreateOn,
                        C_UserName = g.FirstOrDefault<TispSpotsRegionsViewModel>().C_UserName,
                        C_Remark = g.FirstOrDefault<TispSpotsRegionsViewModel>().C_Remark,
                        C_Status = g.FirstOrDefault<TispSpotsRegionsViewModel>().C_Status
                    };
            PageData<TispSpotsRegionViewModel> pageData = new PageData<TispSpotsRegionViewModel>
            {
                Totals = q.ToList().Count,
                Rows = searchViewModel.IsPagination ? q.Skip((searchViewModel.PageIndex - 1) * searchViewModel.PageSize).Take(searchViewModel.PageSize).ToList() : q.ToList()

            };
            return Task.FromResult(pageData);

        }
    }
}