using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Common.Standard;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard.Channel;
using Dapper;
namespace XYY.Data.Standard.Channel
{
    public interface ILogistics_PublicChannelCategoryRepository : IBaseRepository<Logistics_PublicChannelCategory>
    {
        Task<IEnumerable<Logistics_PublicChannelCategoryDto>> GetDtos(QueryModel queryModel);
    }

    public class Logistics_PublicChannelCategoryRepository : BaseRepository<Logistics_PublicChannelCategory>, ILogistics_PublicChannelCategoryRepository
    {
        public Logistics_PublicChannelCategoryRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
        {

        }

        public async Task<IEnumerable<Logistics_PublicChannelCategoryDto>> GetDtos(QueryModel queryModel)
        {
            var strWhere = " ";
            if (queryModel.QueryParamer != null && queryModel.QueryParamer.Count > 0)
                strWhere = GetWhere(queryModel.QueryParamer);
            string sql = $@"
                            SELECT DISTINCT
                                RootId,PublicId,p3.Name as RootName,p4.Name as PublicName,CountryCodes = STUFF((
		                        SELECT ',' + CountryCode
				                        FROM Logistics_PublicChannelCategory AS p1
				                        WHERE p1.RootId = p2.RootId and p1.PublicId = p2.PublicId
				                        FOR XML PATH('')
				                        ), 1, 1, '') 
                            FROM Logistics_PublicChannelCategory as p2
                               left join Logistics_Public p3 on p3.Id = p2.RootId
                               left join Logistics_Public p4 on p4.Id = p2.PublicId
	                        where 1=1 {strWhere}
	                        group by RootId,PublicId,p3.Name,p4.Name";
            return await _unitOfWork.Connection.QueryAsync<Logistics_PublicChannelCategoryDto>(sql, null, _unitOfWork.Transaction);
        }
    }
}
