using Mall.Model.Entity.BaseSetUp;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mall.Model.Extend.BaseSetUp;
using Mall.Common.Enum;

namespace Mall.Repository.BaseSetUp
{
    /// <summary>
    /// 地区表仓储层
    /// </summary>
    public partial class Rb_destinationRepository : RepositoryBase<RB_Destination>
    {
        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName { get { return nameof(RB_Destination); } }

        /// <summary>
        /// 获取地区分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="where"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<RB_Destination_Extend> GetPageList(int pageIndex, int pageSize, RB_Destination_Extend where, out long count)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" SELECT * FROM {0} WHERE 1=1 ", TableName);
            sb.AppendFormat(" AND {0}={1} ", nameof(RB_Destination_Extend.Status), (int)Common.Enum.DateStateEnum.Normal);
            if (where != null)
            {
                if (where.Name != null && !string.IsNullOrEmpty(where.Name.Trim()))
                {
                    sb.AppendFormat(" AND {0} LIKE '%{1}%' ", nameof(RB_Destination_Extend.Name), where.Name.Trim());
                }
                var QArea = Common.ConvertHelper.ConvertInt(where.QArea);
                var QCountry = Common.ConvertHelper.ConvertInt(where.QCountry);
                var QProvince = Common.ConvertHelper.ConvertInt(where.QProvince);
                var QCity = Common.ConvertHelper.ConvertInt(where.QCity);
                var QDistrict = Common.ConvertHelper.ConvertInt(where.QDistrict);
                //区
                if (QDistrict > 0)
                {
                    sb.AppendFormat(" AND {0}={1} ", nameof(RB_Destination_Extend.ID), QDistrict);
                }
                if (QCity > 0 && QDistrict <= 0)
                {
                    sb.AppendFormat(" AND ({0}={1} OR {2}={3}) ", nameof(RB_Destination_Extend.ParentID), QCity, nameof(RB_Destination_Extend.ID), QCity);
                }
                if (QProvince > 0 && QCity <= 0 && QDistrict <= 0)
                {
                    sb.AppendFormat(" AND (({0}={1} AND {2}={3}) OR ({4}={5}) ) ", nameof(RB_Destination_Extend.ParentID), QProvince, nameof(RB_Destination_Extend.CodeLevel), (int)Common.Enum.AreaRegionEnum.CityEnum, nameof(RB_Destination_Extend.ID), QProvince);
                }
                if (QCountry > 0 && QProvince <= 0 && QCity <= 0 && QDistrict <= 0)
                {
                    sb.AppendFormat(" AND (({0}={1} AND {2}={3}) OR ({4}={5})) ", nameof(RB_Destination_Extend.ParentID), QCountry, nameof(RB_Destination_Extend.CodeLevel), (int)Common.Enum.AreaRegionEnum.ProvinceEnum, nameof(RB_Destination_Extend.ID), QCountry);
                }
                if (QArea > 0 && QCountry <= 0 && QProvince <= 0 && QCity <= 0 && QDistrict <= 0)
                {
                    sb.AppendFormat(" AND FIND_IN_SET({0},{1}) AND {2}={3} ", QArea, nameof(RB_Destination_Extend.Area), nameof(RB_Destination_Extend.CodeLevel), (int)Common.Enum.AreaRegionEnum.CountryEnum);
                }
            }
            sb.AppendFormat(" ORDER BY {0} ASC ", nameof(RB_Destination_Extend.ID));
            return GetPage<RB_Destination_Extend>(pageIndex, pageSize, out count, sb.ToString()).ToList();
        }


        /// <summary>
        /// 获取所有的地区数据
        /// </summary>
        /// <returns></returns>
        public List<RB_Destination_Extend> GetListExt(RB_Destination_Extend where)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT * FROM {0} WHERE 1=1 ", TableName);
            sb.AppendFormat(" AND {0}={1} ", nameof(RB_Destination_Extend.Status), (int)Common.Enum.DateStateEnum.Normal);
            if (where != null)
            {
                if (where.CodeLevel > 0)
                {
                    sb.AppendFormat(" AND {0}={1}", nameof(RB_Destination_Extend.CodeLevel), (int)where.CodeLevel);
                }
                if (where.ParentID > 0)
                {
                    sb.AppendFormat(" AND {0}={1} ", nameof(RB_Destination_Extend.ParentID), where.ParentID);
                }
                if (where.QArea != null && !string.IsNullOrEmpty(where.QArea))
                {
                    var QArea = Common.ConvertHelper.ConvertInt(where.QArea);
                    if (QArea > 0)
                    {
                        sb.AppendFormat(" AND FIND_IN_SET({0},{1})  ", QArea, nameof(RB_Destination_Extend.Area));
                    }
                }
                if (where.Name != null && !string.IsNullOrEmpty(where.Name.Trim()))
                {
                    sb.AppendFormat(" AND {0} LIKE '%{1}%' ", nameof(RB_Destination_Extend.Name), where.Name.Trim());
                }
                if (!string.IsNullOrEmpty(where.NameStr)) {
                    sb.AppendFormat(" AND {0} in({1})", nameof(RB_Destination_Extend.Name), where.NameStr);
                }

            }
            return Get<RB_Destination_Extend>(sb.ToString()).ToList();
        }

        /// <summary>
        /// 获取所有的地区数据
        /// </summary>
        /// <returns></returns>
        public List<RB_Destination_Extend> GetListByCountryId(RB_Destination_Extend where)
        {
            string whereStr = string.Format(" AND {0}={1} ", nameof(RB_Destination_Extend.Status), (int)Common.Enum.DateStateEnum.Normal);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"
SELECT *   FROM {0} WHERE ParentID IN
(
   SELECT ID  FROM {0} WHERE ParentID IN({1}) {2} 
) {2}
", TableName, where.QIds, whereStr);
            return Get<RB_Destination_Extend>(sb.ToString()).ToList();
        }

        /// <summary>
        /// 获取所有的省份和城市数据
        /// </summary>
        /// <returns></returns>
        public List<RB_Destination_Extend> GetProviceAndCityList(bool isGetArea = false)
        {
            string whereStr = " AND CodeLevel IN(2,3)";
            if (isGetArea)
            {
                whereStr = " AND CodeLevel IN(2,3,4) ";
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"
SELECT *   FROM {0} WHERE  1=1 AND ParentID<>2 {1}
 ", TableName, whereStr);
            sb.AppendFormat(" AND {0}={1} ", nameof(RB_Destination_Extend.Status), (int)Common.Enum.DateStateEnum.Normal);
            return Get<RB_Destination_Extend>(sb.ToString()).ToList();
        }

        /// <summary>
        /// 根据条件获取单个地区
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public RB_Destination_Extend GetEntityExt(RB_Destination_Extend where)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT * FROM {0} WHERE 1=1 ", TableName);

            if (where != null)
            {
                if (where.Name != null && !string.IsNullOrEmpty(where.Name.Trim()))
                {
                    sb.AppendFormat(" AND {0}='{1}'", nameof(RB_Destination_Extend.Name), where.Name.Trim());
                }
                if (where.ParentID != null && where.ParentID > 0)
                {
                    sb.AppendFormat(" AND {0}={1} ", nameof(RB_Destination_Extend.ID), where.ParentID);
                }
            }
            sb.Append(" LIMIT 1;");
            return Get<RB_Destination_Extend>(sb.ToString()).ToList().FirstOrDefault();
        }

        /// <summary>
        /// 根据地区名称获取地区列表
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public List<RB_Destination_Extend> GetListByNames(string[] where)
        {
            StringBuilder sbWhere = new StringBuilder();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT * FROM {0} WHERE 1=1 ", TableName);
            sb.AppendFormat(" AND {0}<>{1} ", nameof(RB_Destination_Extend.CodeLevel), (int)AreaRegionEnum.DistrictEnum);
            if (where != null && where.Length > 0)
            {
                var index = 0;
                foreach (var item in where)
                {
                    if (!string.IsNullOrEmpty(item.Trim()))
                    {
                        if (index == 0)
                        {
                            sbWhere.AppendFormat(" {0} LIKE '{1}%' ", nameof(RB_Destination_Extend.Name), item.Trim());
                        }
                        else
                        {
                            sbWhere.AppendFormat("  OR {0} LIKE '{1}%' ", nameof(RB_Destination_Extend.Name), item.Trim());
                        }
                        index++;
                    }
                }
            }
            if (!string.IsNullOrEmpty(sbWhere.ToString()))
            {
                sb.AppendFormat(" AND ({0}) ", sbWhere.ToString());
            }
            return Get<RB_Destination_Extend>(sb.ToString()).ToList();
        }

        /// <summary>
        /// 获取地区列表
        /// </summary>
        /// <returns></returns>
        public List<Mall.Common.Data.Node> GetAllList()
        {
            List<Common.Data.Node> queryList = new List<Common.Data.Node>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT * FROM {0} WHERE 1=1 ", TableName);
            sb.AppendFormat(" AND {0}={1} ", nameof(RB_Destination_Extend.Status), (int)Common.Enum.DateStateEnum.Normal);
            List<RB_Destination_Extend> list = Get<RB_Destination_Extend>(sb.ToString()).ToList();
            foreach (var item in list)
            {
                Common.Data.Node model = new Common.Data.Node()
                {
                    I = item.ID,
                    N = item.Name,
                    E = !string.IsNullOrWhiteSpace(item.EnName) ? item.EnName : "",
                    C = !string.IsNullOrWhiteSpace(item.ThreeCode) ? item.ThreeCode : "",
                    L = Convert.ToInt32(item.CodeLevel),
                    P = Convert.ToInt32(item.ParentID),
                    S = (int)item.Status
                };
                queryList.Add(model);
            }
            return queryList;
        }


        /// <summary>
        /// 获取地区列表
        /// </summary>
        /// <returns></returns>
        public List<RB_Destination_Extend> GetAllDestinationList()
        {
            List<Common.Data.Node> queryList = new List<Common.Data.Node>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT * FROM {0} WHERE 1=1 ", TableName);
            sb.AppendFormat(" AND {0}={1} ", nameof(RB_Destination_Extend.Status), (int)Common.Enum.DateStateEnum.Normal);
            List<RB_Destination_Extend> list = Get<RB_Destination_Extend>(sb.ToString()).ToList();
          
            return list;
        }

        /// <summary>
        /// 根据Ids获取地区名称
        /// </summary>
        /// <param name="Ids">地区编号</param>
        /// <returns></returns>
        public string GetNames(string Ids)
        {
            if (!string.IsNullOrEmpty(Ids))
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("SELECT GROUP_CONCAT(Name) AS Name FROM {0}  ", TableName);
                sb.Append(" WHERE 1=1 ");
                sb.AppendFormat(" AND {0}={1} ", nameof(RB_Destination_Extend.Status), (int)Common.Enum.DateStateEnum.Normal);
                sb.AppendFormat(" AND Id in({0})", Ids.TrimStart(',').TrimEnd(','));
                var obj = base.ExecuteScalar(sb.ToString());
                if (obj != null)
                {
                    return obj.ToString();
                }
                return "";
            }
            return "";
        }

        /// <summary>
        /// 根据id 获取所有数据
        /// </summary>
        /// <param name="Ids"></param>
        /// <returns></returns>
        public List<RB_Destination> GetDictvalueListForIds(string Ids)
        {
            if (!string.IsNullOrEmpty(Ids))
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("SELECT * FROM {0}  ", TableName);
                sb.Append(" WHERE 1=1 ");
                sb.AppendFormat(" AND {0}={1} ", nameof(RB_Destination_Extend.Status), (int)Common.Enum.DateStateEnum.Normal);
                sb.AppendFormat(" AND Id in({0})", Ids.TrimStart(',').TrimEnd(','));
                return base.Get(sb.ToString()).ToList();
            }
            return new List<RB_Destination>();
        }

        /// <summary>
        /// 获取中国所有城市
        /// </summary>
        /// <returns></returns>
        public List<RB_Destination> GetAllStartCity(int CountryId = 2)
        {
            string where = " 1=1";
            if (CountryId > 0)
            {
                where += $@" and dpp.ID={CountryId}";
            }
            string sql = $@"SELECT d.* FROM rb_destination d
LEFT JOIN rb_destination dp ON d.ParentID=dp.ID
LEFT JOIN rb_destination dpp ON dp.ParentID=dpp.ID
WHERE {where} AND d.CodeLevel=3 AND d.`Status`=0";
            return Get(sql).ToList();
        }


        /// <summary>
        /// 获取中国所有城市
        /// </summary>
        /// <returns></returns>
        public List<RB_Destination> GetAllStartCityTemp(int CountryId = 2)
        {
            string sql = $@"SELECT d.* FROM rb_destination d
LEFT JOIN rb_destination dp ON d.ParentID=dp.ID
LEFT JOIN rb_destination dpp ON dp.ParentID=dpp.ID  ";
            return Get(sql).ToList();
        }
    }
}
