yili-onemap/api/QM.KJGH.CGGL/QM.KJGH.CGGL.Repository/GhbzGhxmCgmlRepository.cs

using QM.KJGH.CGGL.IRepository;
using QM.KJGH.CGGL.Model.Cggl;
using QM.KJGH.CGGL.Model.Ghxms;
using QM.KJGH.Model.KJGH;
using QM.OrmSqlSugar;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WS.AutoMapper;
using WS.Helper;

namespace QM.KJGH.CGGL.Repository
{
    public class GhbzGhxmCgmlRepository : SqlSugarRepository<GhbzGhxmCgml>, IGhbzGhxmCgmlRepository
    {
        public async Task<List<GhbzGhxmCgmlView>> QueryList()
        {
            var result = (await GetListAsync()).MapTo<GhbzGhxmCgml, GhbzGhxmCgmlView>();
            return result;
        }

        /// <summary>
        /// 规划类型
        /// </summary>
        /// <returns></returns>
        public async Task<List<CgglGhcgxqListView>> XQQueryListType(string search)
        {

            var db = DbClient();

            //查询所有成果数据
            var list = await db.Queryable<CgglGhcgxq>().WhereIF(!string.IsNullOrEmpty(search), i => i.CGMC.Contains(search)).ToListAsync();

            //分组得到有数据的分类
            var data = list.GroupBy(it => new { it.GHLX }).Select(it => new CgglGhcgxqListView
            {
                GHLX = it.Key.GHLX
            }).ToList();

            //分组得到行政区
            var result = list.GroupBy(a => new { a.XZQDM, a.XZQMC, a.GHLX }).Select((a) => new CgmlCityListView
            {
                CGGHLX = a.Key.GHLX,
                XZQMC = a.Key.XZQMC,
                XZQDM = a.Key.XZQDM
            }).ToList();

            //分组得到行政区下目录
            var cgmldata = list.GroupBy(a => new { a.XZQDM, a.XZQMC, a.GHLX, a.MLBSM, a.MLMC })
                .OrderBy(u => u.Key.XZQDM).ThenBy(u => u.Key.MLMC).Select((a) => new CgmlListView
            {
                XZQMC = a.Key.XZQMC,
                XZQDM = a.Key.XZQDM,
                MLBSM = a.Key.MLBSM,
                MLMC = a.Key.MLMC,
                GHLX = a.Key.GHLX
            }).ToList();

            #region 构建Json格式数据

            cgmldata.ForEach(it =>
            {
                it.cglistViews = list.Where(i => i.XZQDM == it.XZQDM && i.MLBSM == it.MLBSM && i.GHLX == it.GHLX).Select(i => new CgListView
                {
                    CGBSM = i.BSM,
                    CGMC = i.CGMC,
                    CGBG = i.CGBG,
                    CGLX = i.CGLX,
                    CGLJ = i.CGLJ,
                    CGKZM = i.CGKZM,
                }).ToList();
            });
            result.ForEach(it =>
            {
                it.listViews = cgmldata.Where(i => i.XZQDM == it.XZQDM && i.GHLX == it.CGGHLX).ToList();
            });

            data.ForEach(it =>
            {
                it.CgmlCityLists = result.Where(i => it.GHLX == i.CGGHLX).ToList();
            });
            #endregion

            return data;
        }

        /// <summary>
        /// 规划行政区
        /// </summary>
        /// <returns></returns>
        public async Task<List<NewCgglGhcgxqListView>> XQQueryList(string search)
        {
            var db = DbClient();

            //查询所有成果数据
            var list = await db.Queryable<CgglGhcgxq>().WhereIF(!string.IsNullOrEmpty(search), i => i.CGMC.Contains(search)).ToListAsync();

            //分组得到有数据的分类
            var data = list.GroupBy(it => new { it.XZQDM, it.XZQMC }).Select(it => new NewCgglGhcgxqListView
            {
                XZQDM = it.Key.XZQDM,
                XZQMC = it.Key.XZQMC
            }).ToList();

            //分组得到类型
            var result = list.GroupBy(a => new { a.XZQDM, a.XZQMC, a.GHLX }).Select((a) => new NewCgmlCityListView
            {
                GHLX = a.Key.GHLX,
                XZQMC = a.Key.XZQMC,
                XZQDM = a.Key.XZQDM
            }).ToList();

            //分组得到行政区下目录
            var cgmldata = list.GroupBy(a => new { a.XZQDM, a.XZQMC, a.GHLX, a.MLBSM, a.MLMC })
                .OrderBy(u=>u.Key.XZQDM).ThenBy(u=>u.Key.MLMC).Select((a) => new NewCgmlListView
            {
                XZQMC = a.Key.XZQMC,
                XZQDM = a.Key.XZQDM,
                MLBSM = a.Key.MLBSM,
                MLMC = a.Key.MLMC,
                GHLX = a.Key.GHLX
            }).ToList();

            #region 构建Json格式数据

            cgmldata.ForEach(it =>
            {
                it.cglistViews = list.Where(i => i.XZQDM == it.XZQDM && i.MLBSM == it.MLBSM && i.GHLX == it.GHLX).Select(i => new NewCgListView
                {
                    CGBSM = i.BSM,
                    CGMC = i.CGMC,
                    CGBG = i.CGBG,
                    CGLX = i.CGLX,
                    CGLJ = i.CGLJ
                }).ToList();
            });
            result.ForEach(it =>
            {
                it.listViews = cgmldata.Where(i => i.XZQDM == it.XZQDM && i.GHLX == it.GHLX).ToList();
            });

            data.ForEach(it =>
            {
                it.CgmlCityLists = result.Where(i => i.XZQDM == it.XZQDM).ToList();
            });
            #endregion

            return data;
        }

        /// <summary>
        /// 查询表格数据
        /// </summary>
        /// <param name="xzqdm">行政区代码</param>
        /// <param name="tablename">要查询的表</param>
        /// <param name="cglx">成果表格</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <returns></returns>
        public async Task<CgglGhbgsxView> ListDetail(string xzqdm, string tablename, string cglx, int pageIndex, int pageSize)
        {

            var db = DbClient();
            var tname = string.Empty;
            var key = new List<string>();
            var value = new List<string>();
            if (cglx == "2")
            {
                tname = db.Queryable<CgglGhbg>().Where(i => i.BSM == tablename).First().SJKYH;
                var data = await db.Queryable<CgglGhbgsx>().Where(u => u.BG_BSM == tablename).ToListAsync();
                data.ForEach(a =>
                {
                    key.Add(a.ZDDM);
                    value.Add(a.ZDMC);
                });
            }
            else
            {
                tname = db.Queryable<CgglSlsj>().Where(i => i.BSM == tablename).First().SJKYH;
                var data = await db.Queryable<CgglSlsjsx>().Where(u => u.BG_BSM == tablename).ToListAsync();
                data.ForEach(a =>
                {
                    key.Add(a.ZDDM);
                    value.Add(a.ZDMC);
                });
            }
            var xzqWhere = "";
            if (!string.IsNullOrWhiteSpace(xzqdm))
            {
                xzqWhere = $" and XZQDM={xzqdm} ";
            }
            var table = $"{tname}.{tablename}";
            var TableSelect = ConfigHelper.Configuration["SQL:TableSelect"];
            var sql = string.Format(TableSelect, string.Join(",", key), table, xzqWhere,pageSize*(pageIndex-1), pageSize*pageIndex);
            WS.Log.RunLog.Debug("SQL=>" + sql);
            var result = DbClient().Ado.SqlQuery<dynamic>(sql);
            sql = $"select count(*) as count from {tname}.{tablename} where 1=1 {xzqWhere}";
            WS.Log.RunLog.Debug("SQL=>" + sql);
            var count = DbClient().Ado.SqlQuery<int>(sql).FirstOrDefault();

            return new CgglGhbgsxView
            {
                Key = string.Join(",", key),
                Value = string.Join(",", value),
                TotalCount = count,
                dynamics = result
            };
        }
    }
}