StableVersion4.3/HL_FristAidPlatform_DataBase/Base/T_Base_CountyDB.cs

using HL_FristAidPlatform_Help;
using HL_FristAidPlatform_IDataBase;
using HL_FristAidPlatform_Models;
using Newtonsoft.Json;
using SqlSugar;
using System.Collections.Generic;
using System.Data;

namespace HL_FristAidPlatform_DataBase
{
    public class T_Base_CountyDB : BaseDB, IT_Base_County
    {
        public SqlSugarClient db = GetClient();
        public bool Add(T_Base_County city)
        {
            return db.Insertable(city).IgnoreColumns(ignoreNullColumn: true).ExecuteCommand() == 1 ? true : false;
        }

        public bool Dels(dynamic[] guids)
        {
            return db.Deleteable<T_Base_County>().In(new dynamic[] { guids }).ExecuteCommand() == 1 ? true : false;
        }

        public bool Update(T_Base_County city)
        {
            return db.Insertable(city).IgnoreColumns(ignoreNullColumn: true).ExecuteCommand() == 1 ? true : false;
        }

        public T_Base_County Get(string guid)
        {
            return db.Queryable<T_Base_County>().First(it => it.GUID == guid);
        }

        public TableModel<T_Base_County> GetPageList(int pageIndex, int pageSize)
        {
            int total = 0;
            List<T_Base_County> data = db.Queryable<T_Base_County>().ToPageList(pageIndex, pageSize, ref total);
            TableModel<T_Base_County> t = new TableModel<T_Base_County>();
            t.Code = 0;
            t.PageCount = data.Count;
            t.TotalNumber = total;
            t.Data = data;
            t.Msg = "成功";
            return t;
        }

        public ProvinceCityCounty GetAllList()
        {
            string SqlStr = string.Format(@"select p.ProvinceCode,p.ProvinceName,c.CityCode,c.CityName,c.ProvinceCode as ProvinceCodeToCity, y.CityCode as CityCodeToCounty,y.CountyCode,y.CountyName from  T_Base_Province p join  T_Base_City c on p.ProvinceCode=c.ProvinceCode join T_Base_County y on y.CityCode=c.CityCode");

            DataTable dt = db.Ado.GetDataTable(SqlStr);
            ProvinceCityCounty pcc = new ProvinceCityCounty();
            List<Province> provinces = new List<Province>();

            var provinceDT = dt.DefaultView.ToTable(true, new string[] { "ProvinceCode", "ProvinceName" });
            foreach (DataRow item in provinceDT.Rows)
            {
                List<City> citys = new List<City>();
                Province province = new Province();
                province.code = item["ProvinceCode"] + "";
                province.name = item["ProvinceName"] + "";
                var cityDT = dt.DefaultView.ToTable(true, new string[] { "CityCode", "ProvinceCodeToCity", "CityName" });
                for (int i = 0; i < cityDT.Rows.Count; i++)
                {
                    if (cityDT.Rows[i]["ProvinceCodeToCity"] + "" == province.code + "")
                    {
                        List<County> countys = new List<County>();
                        City city = new City();
                        city.ProvinceCode = cityDT.Rows[i]["ProvinceCodeToCity"] + "";
                        city.code = cityDT.Rows[i]["CityCode"] + "";
                        city.name = cityDT.Rows[i]["CityName"] + "";
                        for (int j = 0; j < dt.Rows.Count; j++)
                        {
                            if (dt.Rows[j]["CityCodeToCounty"] + "" == city.code + "")
                            {
                                County county = new County();
                                county.CityCode = dt.Rows[j]["CityCodeToCounty"] + "";
                                county.code = dt.Rows[j]["CountyCode"] + "";
                                county.name = dt.Rows[j]["CountyName"] + "";
                                countys.Add(county);
                                city.children = countys;
                            }
                        }
                        citys.Add(city);
                        province.children = citys;
                    }
                }
                provinces.Add(province);
            }
            pcc.province = provinces;
            return pcc;
        }

        /// <summary>
        /// 获取列表-无分页
        /// </summary>
        /// <param name="cityCode">所属市级代码</param>
        /// <returns></returns>
        public TableModel<T_Base_County> GetList(string cityCode)
        {
            TableModel<T_Base_County> t = new TableModel<T_Base_County>();

            var listMode = db.Queryable<T_Base_County>().Where(it => it.CityCode == cityCode).OrderBy(it => it.OrderBy).ToList();

            t.Code = 0;
            t.PageCount = listMode.Count;
            t.TotalNumber = listMode.Count;
            t.Data = listMode;
            t.Msg = "成功";
            return t;
        }

        /// <summary>
        /// 获取当前辖区下的县/区
        /// </summary>
        /// <param name="pageIndex">起始页</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="cityCode">所属市级代码</param>
        /// <param name="keyWord">关键词</param>
        /// <returns></returns>
        public TableModel<T_Base_CountyModel> GetPageListByCityCode(int pageIndex, int pageSize, string cityCode, string keyWord)
        {
            int TotalNumber = 0;
            TableModel<T_Base_CountyModel> t = new TableModel<T_Base_CountyModel>();

            var listMode = db.Queryable<T_Base_County, T_Base_City, T_Base_Province, T_SYS_SystemModule>((County, City, Province, SystemModule) => new object[] {
                JoinType.Left,County.CityCode==City.CityCode,
            JoinType.Left,City.ProvinceCode==Province.ProvinceCode,
            JoinType.Left,Province.SystemModuleID==SystemModule.ID,
            })
            .Where((County, City, Province, SystemModule) => County.CityCode == cityCode)
            .WhereIF(!string.IsNullOrEmpty(keyWord), (County, City, Province, SystemModule) => County.Abbreviation.Contains(keyWord) || County.Initials.Contains(keyWord) || County.PymCode.Contains(keyWord) || County.WbmCode.Contains(keyWord) || County.CountyCode.Contains(keyWord) || County.CountyName.Contains(keyWord) || County.CityCode.Contains(keyWord))
             .Select((County, City, Province, SystemModule) => new T_Base_CountyModel
             {
                 GUID = County.GUID,
                 CountyCode = County.CountyCode,
                 CountyName = County.CountyName,
                 Abbreviation = County.Abbreviation,
                 Initials = County.Initials,
                 PymCode = County.PymCode,
                 WbmCode = County.WbmCode,
                 CityCode = County.CityCode,
                 OrderBy = County.OrderBy,

                 SystemModuleID = SystemModule.ID,
                 SystemName = SystemModule.ShortName,
                 ProvinceCode = Province.ProvinceCode,
                 ProvinceName = Province.ProvinceName,
                 CityName = City.CityName,
             }).OrderBy((County) => County.OrderBy).ToPageList(pageIndex, pageSize, ref TotalNumber);

            t.Code = 0;
            t.PageCount = listMode.Count;
            t.TotalNumber = TotalNumber;
            t.Data = listMode;
            t.Msg = "成功";
            return t;
        }
    }
}