using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Spreadsheet;
using Infrastructure;
using Infrastructure.Extensions;
using Irony;
using Microsoft.Extensions.Options;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using Newtonsoft.Json.Linq;
using NPOI.OpenXmlFormats.Dml;
using OpenAuth.App.BaseApp;
using OpenAuth.App.BaseApp.Base;
using OpenAuth.App.BaseApp.Shape;
using OpenAuth.App.BasicQueryService;
using OpenAuth.App.FormScheme.FormHelpers;
using OpenAuth.App.Interface;
using OpenAuth.App.ServiceApp.DroneCaseInfoTaskManage.Request;
using OpenAuth.App.ServiceApp.DroneCaseInfoTaskManage.Response;
using OpenAuth.App.ServiceApp.DroneCloudQueryManage.Request;
using OpenAuth.App.ServiceApp.Response;
using OpenAuth.Repository;
using OpenAuth.Repository.Domain;
using Org.BouncyCastle.Ocsp;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO.Compression;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static ICSharpCode.SharpZipLib.Zip.ExtendedUnixData;

namespace OpenAuth.App.ServiceApp.DroneCaseInfoTaskManage
{
    public class DroneCaseInfoTaskApp : SqlSugarBaseApp<DroneCaseInfoTask, SugarDbContext>
    {
        ISqlSugarClient client;
        private ImMsgApp _immsgApp;
        private ShpLayerSourceApp _shplayerapp;
        private readonly string _filePath;
        CommonDataManager _commonDataManager;
        public DroneCaseInfoTaskApp(ISugarUnitOfWork<SugarDbContext> unitWork, ISimpleClient<DroneCaseInfoTask> repository, IOptions<AppSetting> setOptions, ISqlSugarClient sqlSugarClient, IAuth auth, ImMsgApp immsgApp, ShpLayerSourceApp shplayersource, CommonDataManager commonDataManager) : base(unitWork, repository, auth)
        {
            _filePath = setOptions.Value.UploadPath;
            if (string.IsNullOrEmpty(_filePath))
            {
                _filePath = AppContext.BaseDirectory;
            }

            client = sqlSugarClient;
            _immsgApp = immsgApp;
            _shplayerapp = shplayersource;
            _commonDataManager = commonDataManager;
        }

        #region 添加查询任务信息
        //添加任务
        public Response<bool> AddCaseInfoTask(string caseids, string shplayerids, DateTime qianshixiang, DateTime houshixiang, string picihao)
        {
            var user = _auth.GetCurrentUser().User;
            //初始化查询结果
            DroneCaseInfoTask caseinfotask = new DroneCaseInfoTask()
            {
                Id = Guid.NewGuid().ToString(),
                CreateTime = DateTime.Now,
                CreateUser = user?.Id.ToString() ?? "",
                picihao = picihao,
                State = 0,
                qianshixiang = qianshixiang,
                houshixiang = houshixiang,
                shplayerids = shplayerids,
                caseinfoids = caseids,
                rate=0
            };
            var data = client.Queryable<DroneCaseInfoTask>().Where(r => r.picihao == picihao).ToList();
            AddCaseinfoTaskRes rs = new AddCaseinfoTaskRes();
            rs.id = caseinfotask.Id;
            if (data.Count > 0)
            {
                throw new Exception("批次号重复，任务已存在");
            }
            else
            {
                //添加数据
                var flag = client.Ado.UseTran(() =>
                {
                    client.Insertable(caseinfotask).ExecuteCommand();
                });
                if (flag.IsSuccess)
                {
                    rs.flag=true;
                    return new Response<bool> { Result = true };
                }
                else
                {
                    rs.flag = false;
                    return new Response<bool> { Result = false, Message = "操作失败" };
                }
            }
        }

        //查询任务
        public async Task<Response<List<DroneCaseInfoTask>>> LoadDroneCaseInfoTask()
        {
            //查询任务数据
            var data = await client.Queryable<DroneCaseInfoTask>().Where(r => r.State != 2)
                   .OrderByDescending(a => a.CreateTime).ToListAsync();

            //获取之后，需要把这些任务标记为正在查询
            foreach (var item in data)
            {
                item.State = 1;
            }
            await client.Updateable<DroneCaseInfoTask>(data).ExecuteCommandAsync();
            return new Response<List<DroneCaseInfoTask>>
            {
                Result = data
            };
        }

        //根据任务id查询任务信息
        public async Task<Response<DroneCaseInfoTask>> LoadDroneCaseInfobyid(string id)
        {
            var data =await  client.Queryable<DroneCaseInfoTask>().Where(r => r.Id == id).FirstAsync();
            Response<DroneCaseInfoTask> ts = new Response<DroneCaseInfoTask>();
            ts.Result=data;
            return ts;
        }
        //更新任务信息
        public Response<bool> UpdateDroneCaseInfoTask(string id,int rate)
        {
            var flag =  client.Updateable<DroneCaseInfoTask>().SetColumns(r => new DroneCaseInfoTask
            {
                rate = rate
            }).Where(r => r.Id == id).ExecuteCommand();
            Response<bool> ts = new Response<bool>();
            if (flag > 0)
            {
                ts.Result = true;
            }
            else
            {
                ts.Result = false;
            }
            
            return ts;
        }

        //查询任务
        public async Task<Response<List<DroneCaseInfoTask>>> LoadDroneCaseInfoTaskByUser()
        {
            var user = _auth.GetCurrentUser().User;
            //查询任务数据
            var data = await client.Queryable<DroneCaseInfoTask>().Where(r => r.CreateUser==user.Id.ToString())
                   .OrderByDescending(a => a.CreateTime).ToListAsync();

            return new Response<List<DroneCaseInfoTask>>
            {
                Result = data
            };
        }
        #endregion

        #region 查询图层信息
        //查询任务
        public async Task<Response<List<DroneLayerInfo>>> LoadLayerInfo()
        {
            //查询任务数据
            var data = await client.Queryable<DroneLayerInfo>().ToListAsync();

            return new Response<List<DroneLayerInfo>>
            {
                Result = data
            };
        }
        #endregion

        #region 接收查询结果
        public async Task<Response<bool>> UpdateDroneCaseInfo(DroneCaseInfoTaskRes req)
        {
            using (var uow = base.UnitWork.CreateContext())
            {
                //导入案件信息
                var data = req.importlist;
                #region 导入案件信息
                int i = 0;
                List<DroneCaseInfoSingle> datalist = new List<DroneCaseInfoSingle>();
                List<DroneShpData> shplist = new List<DroneShpData>();
                StringBuilder geomSql = new StringBuilder();
                StringBuilder sql = new StringBuilder();
                foreach (var item in data)
                {
                    DroneCaseInfoSingle cinfo = new DroneCaseInfoSingle();
                    cinfo.Id = item.Id;
                    cinfo.createtime = DateTime.Now;
                    cinfo.createuser = item.createuser;
                    cinfo.createusername = item.createusername;
                    cinfo.is_illegal = 1;
                    cinfo.is_intact = 1;
                    cinfo.synchronoustime = DateTime.Now;
                    cinfo.handle_status_id = 0;
                    cinfo.handle_status_name = "待接收";
                    cinfo.is_jieshou = 0;
                    cinfo.is_closed = 0;
                    cinfo.is_delete = 0;
                    cinfo.case_description = item.case_description;
                    cinfo.area = item.area;
                    cinfo.tubanlaiyuan = item.tubanlaiyuan;
                    cinfo.typename = item.typename;
                    //类型id
                    var type = client.Queryable<SysDataItemDetail>().Where(r => r.ItemName == cinfo.typename && r.ItemCode == "DRONE_CASE_TYPE").First();
                    if (type != null)
                    {
                        cinfo.typeid = type.ItemDetailId;
                    }

                    cinfo.lng = item.lng;
                    cinfo.lat = item.lat;
                    JObject xzqh = GetOrgAreaByPoint((decimal)cinfo.lng, (decimal)cinfo.lat);
                    cinfo.countyid = xzqh["countyid"].ToString();
                    cinfo.countyname = xzqh["countyname"].ToString();
                    cinfo.streetid = xzqh["streetid"].ToString();
                    cinfo.streetname = xzqh["streetname"].ToString();
                    cinfo.communityid = xzqh["communityid"].ToString();
                    cinfo.communityname = xzqh["communityname"].ToString();
                    cinfo.address = cinfo.countyname + " " + cinfo.streetname + " " + cinfo.communityname;
                    cinfo.case_no = item.case_no;
                    cinfo.casepic = item.casepic;
                    cinfo.nongyongdi_area = item.nongyongdi_area;
                    cinfo.yongjiujibennongtian_area = item.yongjiujibennongtian_area;
                    cinfo.gengdi_area = item.gengdi_area;
                    cinfo.guotukongjianguihua_area = item.guotukongjianguihua_area;
                    cinfo.shengtaibaohuhongxian_area = item.shengtaibaohuhongxian_area;
                    cinfo.zhongdianquyu_area = item.zhongdianquyu_area;

                    //图斑数据drone_shp_data
                    DroneShpData model = new DroneShpData();
                    model.relid = cinfo.Id;
                    model.createtime = DateTime.Now;
                    model.createuser = item.createusername;
                    model.area = item.shparea;
                    //获取主键
                    string _gid = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
                    model.gid = int.Parse(_gid) + i;//转为数字类型
                    i++;
                    cinfo.geomid = model.gid.ToString();

                    geomSql.AppendFormat($" update drone_shp_data set geom = '{item.geom}' where gid = '{model.gid}';");
                    sql.AppendFormat($" update drone_shp_data set area = st_area(st_transform(ST_SetSRID(geom,4326), 4527)) where gid = '{model.gid}';");
                    shplist.Add(model);

                    datalist.Add(cinfo);
                }
                string errornums = "";
                switch (data[0].subjectname)
                {
                    case "巡察审计":
                        var dataxunchashenji = datalist.MapTo<List<DroneCaseInfoXcsj>>();
                        //判断是否有重复数据
                        List<string> casenos = dataxunchashenji?.Select(r => r.case_no).ToList();
                        var caseinfo = client.Queryable<DroneCaseInfoXcsj>().Where(r => casenos.Contains(r.case_no)).ToList();
                        if (caseinfo.Count>0)
                        {
                            var nums = caseinfo.Select(r => r.case_no).ToList();
                            var dataxuncha = dataxunchashenji.Where(r => !nums.Contains(r.case_no)).ToList();
                            //保存重复数据案件编号
                            errornums= string.Join(",", nums);
                            //添加未重复数据
                            await uow.DroneCaseInfoXcsj.InsertRangeAsync(dataxuncha);
                        }
                        else
                        {
                            await uow.DroneCaseInfoXcsj.InsertRangeAsync(dataxunchashenji);
                        }
                        break;
                    case "耕地非粮化":
                        var datafeilianghua = datalist.MapTo<List<DroneCaseInfoFLH>>();
                        List<string> casenof = datafeilianghua?.Select(r => r.case_no).ToList();
                        var caseinfof = client.Queryable<DroneCaseInfoFLH>().Where(r => casenof.Contains(r.case_no)).ToList();
                        if (caseinfof.Count>0)
                        {
                            var nums = caseinfof.Select(r => r.case_no).ToList();
                            var dataxuncha = datafeilianghua.Where(r => !nums.Contains(r.case_no)).ToList();
                            //保存重复数据案件编号
                            errornums = string.Join(",", nums);
                            //添加未重复数据
                            await uow.DroneCaseInfoFLH.InsertRangeAsync(dataxuncha);
                        }
                        else
                        {
                            await uow.DroneCaseInfoFLH.InsertRangeAsync(datafeilianghua);
                        }
                        break;
                    case "生态保护红线监管":
                        var datastbh = datalist.MapTo<List<DroneCaseInfoSTHX>>();
                        List<string> casenox = datastbh?.Select(r => r.case_no).ToList();
                        var caseinfox = client.Queryable<DroneCaseInfoSTHX>().Where(r => casenox.Contains(r.case_no)).ToList();
                        if (caseinfox.Count > 0)
                        {
                            var nums = caseinfox.Select(r => r.case_no).ToList();
                            var dataxuncha = datastbh.Where(r => !nums.Contains(r.case_no)).ToList();
                            //保存重复数据案件编号
                            errornums = string.Join(",", nums);
                            //添加未重复数据
                            await uow.DroneCaseInfoSTHX.InsertRangeAsync(dataxuncha);
                        }
                        else
                        {
                            await uow.DroneCaseInfoSTHX.InsertRangeAsync(datastbh);
                        }
                        break;
                    case "生态修复":
                        var datastxiufu = datalist.MapTo<List<DroneCaseInfoSTHX>>();
                        List<string> casenoh = datastxiufu?.Select(r => r.case_no).ToList();
                        var caseinfot = client.Queryable<DroneCaseInfoSTHX>().Where(r => casenoh.Contains(r.case_no)).ToList();
                        if (caseinfot.Count > 0)
                        {
                            var nums = caseinfot.Select(r => r.case_no).ToList();
                            var dataxuncha = datastxiufu.Where(r => !nums.Contains(r.case_no)).ToList();
                            //保存重复数据案件编号
                            errornums = string.Join(",", nums);
                            //添加未重复数据
                            await uow.DroneCaseInfoSTHX.InsertRangeAsync(dataxuncha);
                        }
                        else
                        {
                            await uow.DroneCaseInfoSTHX.InsertRangeAsync(datastxiufu);
                        }
                        break;
                    case "违法用地":
                        List<string> casenog = datalist?.Select(r => r.case_no).ToList();
                        var caseinfos = client.Queryable<DroneCaseInfoSingle>().Where(r => casenog.Contains(r.case_no)).ToList();
                        if (caseinfos.Count>0)
                        {
                            var nums = caseinfos.Select(r => r.case_no).ToList();
                            var dataxuncha = datalist.Where(r => !nums.Contains(r.case_no)).ToList();
                            //保存重复数据案件编号
                            errornums = string.Join(",", nums);
                            //添加未重复数据
                            await uow.DroneCaseInfoSingle.InsertRangeAsync(dataxuncha);
                        }
                        else
                        {
                            await uow.DroneCaseInfoSingle.InsertRangeAsync(datalist);
                        }
                        break;
                    case "重点问题I类":
                        var zhongdianwenti1 = datalist.MapTo<List<DroneCaseInfoZdwt1>>();
                        List<string> casenot = zhongdianwenti1?.Select(r => r.case_no).ToList();
                        var caseinfoz = client.Queryable<DroneCaseInfoZdwt1>().Where(r => casenot.Contains(r.case_no)).ToList();
                        if (caseinfoz.Count>0)
                        {
                            var nums = caseinfoz.Select(r => r.case_no).ToList();
                            var dataxuncha = zhongdianwenti1.Where(r => !nums.Contains(r.case_no)).ToList();
                            //保存重复数据案件编号
                            errornums = string.Join(",", nums);
                            //添加未重复数据
                            await uow.DroneCaseInfoZdwt1.InsertRangeAsync(dataxuncha);
                        }
                        else
                        {
                            await uow.DroneCaseInfoZdwt1.InsertRangeAsync(zhongdianwenti1);
                        }
                        break;
                    case "重点问题II类":
                        var zhongdianwenti2 = datalist.MapTo<List<DroneCaseInfoZdwt2>>();
                        List<string> casenow = zhongdianwenti2?.Select(r => r.case_no).ToList();
                        var caseinfow = client.Queryable<DroneCaseInfoZdwt2>().Where(r => casenow.Contains(r.case_no)).ToList();
                        if (caseinfow.Count>0)
                        {
                            var nums = caseinfow.Select(r => r.case_no).ToList();
                            var dataxuncha = zhongdianwenti2.Where(r => !nums.Contains(r.case_no)).ToList();
                            //保存重复数据案件编号
                            errornums = string.Join(",", nums);
                            //添加未重复数据
                            await uow.DroneCaseInfoZdwt2.InsertRangeAsync(dataxuncha);
                        }
                        else
                        {
                            await uow.DroneCaseInfoZdwt2.InsertRangeAsync(zhongdianwenti2);
                        }
                        break;
                    case "卫片下发":
                        var weipianxiafa = datalist.MapTo<List<DroneCaseInfoSatellite>>();
                        List<string> casenoa = weipianxiafa?.Select(r => r.case_no).ToList();
                        var caseinfoa = client.Queryable<DroneCaseInfoSatellite>().Where(r => casenoa.Contains(r.case_no)).ToList();
                        if (caseinfoa.Count>0)
                        {
                            var nums = caseinfoa.Select(r => r.case_no).ToList();
                            var dataxuncha = weipianxiafa.Where(r => !nums.Contains(r.case_no)).ToList();
                            //保存重复数据案件编号
                            errornums = string.Join(",", nums);
                            //添加未重复数据
                            await uow.DroneCaseInfoSatellite.InsertRangeAsync(dataxuncha);
                        }
                        else
                        {
                            await uow.DroneCaseInfoSatellite.InsertRangeAsync(weipianxiafa);
                        }
                        break;
                }
                //更新任务
                var task = uow.DroneCaseInfoTask.GetById(req.task.Id);
                if (task != null)
                {
                    //更新记录
                    await uow.DroneCaseInfoTask.UpdateAsync(u => new DroneCaseInfoTask
                    {
                        State = 2
                    }, u => u.Id == task.Id);
                }
                await uow.DroneShpData.InsertRangeAsync(shplist);
                await uow.Db.Ado.ExecuteCommandAsync(geomSql.ToString());
                await uow.Db.Ado.ExecuteCommandAsync(sql.ToString());
                #endregion
                //InsertCaseInfoData(infos,uow);
                var flag = uow.Commit();

                var content = new
                {
                    casetaskid = task.Id,
                    picihao = task == null ? "" : task.picihao,
                    errmessage="图斑编号重复："+errornums.ToString(),
                };
                var contents = Json.ToJson(content);

                //添加推送数据的用户
                List<string> users = new List<string>();
                if (task != null)
                {
                    users.Add(task.CreateUser);
                }

                //推送数据
                if (flag == true)
                {
                    try
                    {
                        await _immsgApp.SendMsg("CaseInfoTask", users, contents, "4", task.Id);
                    }
                    catch
                    {
                        return new Response<bool>
                        {
                            Result = flag,
                            Message = "数据导入成功，推送失败"
                        };
                    }
                }
                //返回值
                return new Response<bool>
                {
                    Result = flag,
                    Message = flag == true ? "导入数据成功" : "导入数据失败"
                };
            }
        }
        public bool CaseInfoJudge(List<string> caseno,string subjectname)
        {
            bool flag = false;
            switch (subjectname)
            {
                case "巡察审计":
                    var caseinfo = client.Queryable<DroneCaseInfoXcsj>().Where(r => caseno.Contains(r.case_no)).ToList();
                    if (caseinfo.Count > 0)
                    {
                        flag= true;
                    }
                    break;
                case "耕地非粮化":
                    var caseinfoflh = client.Queryable<DroneCaseInfoFLH>().Where(r => caseno.Contains(r.case_no)).ToList();
                    if (caseinfoflh.Count > 0)
                    {
                        flag = true;
                    }
                    break;
                case "违法用地":
                    var caseinfosig = client.Queryable<DroneCaseInfoSingle>().Where(r => caseno.Contains(r.case_no)).ToList();
                    if (caseinfosig.Count > 0)
                    {
                        flag = true;
                    }
                    break;
                case "重点问题I类":
                    var caseinfozt1 = client.Queryable<DroneCaseInfoZdwt1>().Where(r => caseno.Contains(r.case_no)).ToList();
                    if (caseinfozt1.Count > 0)
                    {
                        flag = true;
                    }
                    break;
                case "重点问题II类":
                    var caseinfozt2 = client.Queryable<DroneCaseInfoZdwt2>().Where(r => caseno.Contains(r.case_no)).ToList();
                    if (caseinfozt2.Count > 0)
                    {
                        flag = true;
                    }
                    break;
                case "卫片下发":
                    var caseinfosatellite = client.Queryable<DroneCaseInfoSatellite>().Where(r => caseno.Contains(r.case_no)).ToList();
                    if (caseinfosatellite.Count > 0)
                    {
                        flag = true;
                    }
                    break;
            }
            return flag;
        }

        /// <summary>
        /// 矿产接收查询结果
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        public async Task<Response<bool>> UpdateDroneCaseInfoMinerals(DroneCaseInfoTaskRes req)
        {
            using (var uow = base.UnitWork.CreateContext())
            {
                #region 案件信息处理
                var data = req.importlist;
                int i = 0;
                List<DroneCaseInfoMinerals> datalist = new List<DroneCaseInfoMinerals>();
                List<DroneShpData> shplist = new List<DroneShpData>();
                StringBuilder geomSql = new StringBuilder();
                StringBuilder sql = new StringBuilder();
                foreach (var item in data)
                {
                    DroneCaseInfoMinerals cinfo = new DroneCaseInfoMinerals();
                    cinfo.Id = item.Id;
                    cinfo.createtime = DateTime.Now;
                    cinfo.createuser = item.createuser;
                    cinfo.is_illegal = 1;
                    //cinfo.xiafatime_base = DateTime.Now;
                    cinfo.handle_status_id = 0;
                    cinfo.is_closed = 0;
                    cinfo.case_description = item.case_description;
                    cinfo.weifaarea_base = item.area;
                    cinfo.tubanlaiyuan = item.tubanlaiyuan;
                    cinfo.typename = item.typename;
                    cinfo.centerlat_base = item.lng.ToString();
                    cinfo.centerlng_base = item.lat.ToString();
                    JObject xzqh = GetOrgAreaByPoint((decimal)item.lng, (decimal)item.lat);
                    cinfo.countyid = xzqh["countyid"].ToString();
                    cinfo.countyname = xzqh["countyname"].ToString();
                    cinfo.streetid = xzqh["streetid"].ToString();
                    cinfo.streetname = xzqh["streetname"].ToString();
                    cinfo.communityid = xzqh["communityid"].ToString();
                    cinfo.communityname = xzqh["communityname"].ToString();
                    //cinfo.address = cinfo.countyname + " " + cinfo.streetname + " " + cinfo.communityname;
                    cinfo.case_no = item.case_no;
                    cinfo.tubanpic_base = item.casepic;
                    cinfo.jbntarea_base = item.yongjiujibennongtian_area;
                    cinfo.gengdi_area = item.gengdi_area;
                    cinfo.stbhhxarea_base = item.shengtaibaohuhongxian_area;
                    cinfo.cskfbjarea_base = item.cskfbjarea_base;
                    cinfo.weifazhandiarea_base = item.weifazhandiarea_base;
                    cinfo.weifakaicaiarea_base = item.weifakaicaiarea_base;

                    //图斑数据drone_shp_data
                    DroneShpData model = new DroneShpData();
                    model.relid = cinfo.Id;
                    model.createtime = DateTime.Now;
                    model.createuser = item.createusername;
                    model.area = item.shparea;
                    //获取主键
                    string _gid = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
                    model.gid = int.Parse(_gid) + i;//转为数字类型
                    i++;
                    cinfo.geomid = model.gid.ToString();

                    geomSql.AppendFormat($" update drone_shp_data set geom = '{item.geom}' where gid = '{model.gid}';");
                    sql.AppendFormat($" update drone_shp_data set area = st_area(st_transform(ST_SetSRID(geom,4326), 4527)) where gid = '{model.gid}';");
                    shplist.Add(model);

                    datalist.Add(cinfo);
                }
                var caseno = datalist.Select(r => r.case_no).ToList();
                var caseinfo = client.Queryable<DroneCaseInfoMinerals>().Where(r => caseno.Contains(r.case_no)).ToList();
                string errornums = "";
                if (caseinfo.Count > 0)
                {
                    var nums = caseinfo.Select(r => r.case_no).ToList();
                    var dataxuncha = datalist.Where(r => !nums.Contains(r.case_no)).ToList();
                    //保存重复数据案件编号
                    errornums = string.Join(",", nums);
                    //添加未重复数据
                    await uow.DroneCaseInfoMinerals.InsertRangeAsync(dataxuncha);

                }
                else
                {
                    await uow.DroneCaseInfoMinerals.InsertRangeAsync(datalist);
                }

                //更新任务
                var task = client.Queryable<DroneCaseInfoTask>().Where(r => r.Id == req.task.Id).First();
                if (task != null)
                {
                    //更新记录
                    await uow.DroneCaseInfoTask.UpdateAsync(u => new DroneCaseInfoTask
                    {
                        State = 2
                    }, u => u.Id == task.Id);
                }
                await uow.DroneShpData.InsertRangeAsync(shplist);
                await uow.Db.Ado.ExecuteCommandAsync(geomSql.ToString());
                await uow.Db.Ado.ExecuteCommandAsync(sql.ToString());

                var flag = uow.Commit();
                #endregion
                var content = new
                {
                    casetaskid = task.Id,
                    picihao = task == null ? "" : task.picihao,
                    errmessage = "图斑编号重复：" + errornums.ToString()
                };
                var contents = Json.ToJson(content);

                //添加推送数据的用户
                List<string> users = new List<string>();
                if (task != null)
                {
                    users.Add(task.CreateUser);
                }

                //推送数据
                if (flag == true)
                {
                    try
                    {
                        await _immsgApp.SendMsg("CaseInfoTask", users, contents, "4", task.Id);
                    }
                    catch
                    {
                        return new Response<bool>
                        {
                            Result = flag,
                            Message = "数据导入成功，推送失败"
                        };
                    }
                }
                //返回值
                return new Response<bool>
                {
                    Result = flag,
                    Message = flag == true ? "导入数据成功" : "导入数据失败"
                };
            }
        }

        /// <summary> 
        /// 根据经纬度获取组织机构
        /// </summary>
        /// <param name="lng"></param>
        /// <param name="lat"></param>
        /// <returns></returns>
        public JObject GetOrgAreaByPoint(decimal lng, decimal lat)
        {
            //县id
            string countyid = "";
            string countyname = "";
            //镇id
            string streetid = "";
            string streetname = "";
            //村id
            string communityid = "";
            string communityname = "";

            //SqlSugar实例
            using (var db = Repository.AsSugarClient())
            {
                //查询坐标属于哪个县
                StringBuilder sql = new StringBuilder();
                sql.AppendFormat($" SELECT xzqdm,xzqmc FROM \"shp_drone_county\" where ST_Within(st_geomfromtext('POINT({lng} {lat})',4326), ST_SetSRID(geom,4326))");
                var countyList = db.SqlQueryable<dynamic>(sql.ToString()).ToList();
                if (countyList.Count > 0)
                {
                    //县名称
                    countyname = countyList[0].xzqmc;
                    //县id
                    countyid = countyList[0].xzqdm;
                }

                //查询坐标属于哪个镇
                sql = new StringBuilder();
                sql.AppendFormat($" SELECT xzqdm,xzqmc FROM \"shp_drone_town\" where ST_Within(st_geomfromtext('POINT({lng} {lat})',4326), ST_SetSRID(geom,4326))");
                var streetList = db.SqlQueryable<dynamic>(sql.ToString()).ToList();
                if (streetList.Count > 0)
                {
                    //镇名称
                    streetname = streetList[0].xzqmc;
                    //镇id
                    streetid = streetList[0].xzqdm;
                }

                if (!string.IsNullOrEmpty(streetid))
                {
                    //查询组织机构属于哪个村
                    sql = new StringBuilder();
                    sql.AppendFormat($" SELECT zldwdm,zldwmc FROM \"shp_drone_community\" where ST_Within(st_geomfromtext('POINT({lng} {lat})',4326), ST_SetSRID(geom,4326))");
                    var communityidList = db.SqlQueryable<dynamic>(sql.ToString()).ToList();
                    if (communityidList.Count > 0)
                    {
                        //村名称
                        communityname = communityidList[0].zldwmc;
                        //村id
                        communityid = communityidList[0].zldwdm;
                    }
                }

                //拼接json数据，返回到前台
                JObject obj = new JObject();

                obj.Add("countyid", countyid);
                obj.Add("countyname", countyname);
                obj.Add("streetid", streetid);
                obj.Add("streetname", streetname);
                obj.Add("communityid", communityid);
                obj.Add("communityname", communityname);

                return obj;
            }
        }
        #endregion

        #region 根据批次号查询案件信息
        public async Task<Response<PageInfo<List<DroneCaseInfoImport>>>> LoadCaseImportInfoBypicihao(string picihao,  string laiyuan, DateTime? begintime,DateTime? endtime, int pageindex, int pagesize)
        {
            var user = _auth.GetCurrentUser();
            //定义且实例化分页数据
            RefAsync<int> totalCount = 0;
            var caseinfolist = await client.SqlQueryable<DroneCaseInfoImport>("SELECT \"Id\",case_no,case_description,lng,lat,typename,area,tubanlaiyuan,ST_AsText(geom) AS geom,picihao,createtime,createuser,createusername,subjectname,shparea,casepic,nongyongdi_area,  gengdi_area,yongjiujibennongtian_area,zhongdianquyu_area,shengtaibaohuhongxian_area,guotukongjianguihua_area FROM drone_caseinfo_import")
                .WhereIF(!string.IsNullOrEmpty(picihao), r => picihao == r.picihao)
                .WhereIF(!string.IsNullOrEmpty(laiyuan), r => r.tubanlaiyuan.Contains(laiyuan))
                .WhereIF(begintime!=null&&endtime!=null,r=>r.createtime>=begintime&&r.createtime< endtime)
                .WhereIF(user!=null,r=>r.createuser==user.User.Id.ToString())
                .ToPageListAsync(pageindex, pagesize, totalCount); 

            return new Response<PageInfo<List<DroneCaseInfoImport>>>
            {
                Result = new PageInfo<List<DroneCaseInfoImport>>
                {
                    Items = caseinfolist,
                    Total = totalCount
                }
            };
        }

        /// <summary>
        /// 查询最后一次导入数据
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public async Task<Response<string>> LoadCaseImportInfoLast(string subjectname,int pageindex, int pagesize)
        {
            var user = _auth.GetCurrentUser().User;
            string picihao = "";
            //定义且实例化分页数据
            RefAsync<int> totalCount = 0;
            var data = await client.Queryable<DroneCaseInfoImport>()
                .WhereIF(!string.IsNullOrEmpty(subjectname), r => r.subjectname == subjectname)
                .Where(r => r.createuser == user.Id.ToString())
                .OrderByDescending(r => r.createtime).FirstAsync();
            if (data != null)
            {
                picihao = data.picihao;
            }

            return new Response<string>
            {
                Result = picihao
            };
        }
        #endregion

        #region 导入shp数据新版
        /// <summary>
        /// shp导入
        /// </summary>
        /// <param name="zipFilePath"></param>
        /// <param name="srid"></param>
        /// <returns></returns>
        /// <exception cref="FileNotFoundException"></exception>
        /// <exception cref="Exception"></exception>
        public async Task<Response<string>> ImportCaseInfoShpData(string zipFilePath, string srid, string subjectname)
        {
            var _user = _auth.GetCurrentUser().User;
            using var db = Repository.AsSugarClient();

            // 开启事务
            await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
            // 取得文件完全路径
            zipFilePath = System.IO.Path.Combine(_filePath, zipFilePath);
            // 确保ZIP文件存在
            if (!File.Exists(zipFilePath))
            {
                throw new FileNotFoundException("ZIP文件未找到。");
            }

            // 打开ZIP存档 取除后缀之前的字符串
            var extractPath = zipFilePath.Substring(0, zipFilePath.LastIndexOf(".", StringComparison.Ordinal)) +
                              "extract";
            // 解压文件
            await UnZip(zipFilePath, extractPath);
            var searchPattern = "*.shp"; // 设置你想要遍历的文件后缀名
            var fileName = Directory.GetFiles(extractPath, searchPattern, SearchOption.AllDirectories);
            if (fileName.IsEmpty())
            {
                throw new Exception("压缩文件中无shp文件");
            }

            var shpFileName = fileName[0];
            var shpFile = System.IO.Path.Combine(extractPath, shpFileName);

            List<DroneCaseInfoImport> datalist = new List<DroneCaseInfoImport>();
            List<DroneShpData> shplist = new List<DroneShpData>();
            StringBuilder geomSql = new StringBuilder();
            //批量更新面积
            StringBuilder sql = new StringBuilder();
            Random random = new Random();
            //批次号
            var pici = DateTime.Now.ToString("yyyyMMssHHmmss") + random.Next(1000, 9999).ToString();

            using (var dataReader = new ShapefileDataReader(shpFile, GeometryFactory.Default))
            {
                while (dataReader.Read())
                {
                    string countyid = "";
                    DroneCaseInfoImport cinfo = new DroneCaseInfoImport();
                    cinfo.Id = Guid.NewGuid().ToString();
                    cinfo.createtime = DateTime.Now;
                    cinfo.createuser = _user.Id.ToString();
                    cinfo.createusername = _user.Name.ToString();
                    cinfo.case_description = dataReader.GetValue(2).ToString();
                    cinfo.subjectname = subjectname;
                    cinfo.picihao = pici;
                    if (dataReader.IsDBNull(6))
                    {
                        throw new Exception("图斑面积不能为空");
                    }
                    else
                    {
                        cinfo.area = dataReader.GetValue(6).ToString();
                    }
                    if (dataReader.IsDBNull(7))
                    {
                        throw new Exception("图斑来源不能为空");
                    }
                    else
                    {
                        cinfo.tubanlaiyuan = dataReader.GetValue(7).ToString();
                    }
                    if (dataReader.IsDBNull(8))
                    {
                        cinfo.nongyongdi_area = "0.00";
                    }
                    else
                    {
                        cinfo.nongyongdi_area = dataReader.GetValue(8).ToString();
                    }
                    if (dataReader.IsDBNull(9))
                    {
                        cinfo.gengdi_area = "0.00";
                    }
                    else
                    {
                        cinfo.gengdi_area = dataReader.GetValue(9).ToString();
                    }
                    if (dataReader.IsDBNull(10))
                    {
                        cinfo.yongjiujibennongtian_area = "0.00";
                    }
                    else
                    {
                        cinfo.yongjiujibennongtian_area = dataReader.GetValue(10).ToString();
                    }
                    if (dataReader.IsDBNull(11))
                    {
                        cinfo.zhongdianquyu_area = "0.00";
                    }
                    else
                    {
                        cinfo.zhongdianquyu_area = dataReader.GetValue(11).ToString();
                    }
                    if (dataReader.IsDBNull(12))
                    {
                        cinfo.shengtaibaohuhongxian_area = "0.00";
                    }
                    else
                    {
                        cinfo.shengtaibaohuhongxian_area = dataReader.GetValue(12).ToString();
                    }
                    if (dataReader.IsDBNull(13))
                    {
                        cinfo.guotukongjianguihua_area = "0.00";
                    }
                    else
                    {
                        cinfo.guotukongjianguihua_area = dataReader.GetValue(13).ToString();
                    }

                    cinfo.typename = dataReader.GetValue(5).ToString();
                    //类型id
                    var type = db.Queryable<SysDataItemDetail>().Where(r => r.ItemName == cinfo.typename && r.ItemCode == "DRONE_CASE_TYPE").First();
                    //坐标及行政区划
                    if (dataReader.IsDBNull(3) || dataReader.IsDBNull(4))
                    {
                        throw new Exception("坐标不能为空");
                    }
                    else
                    {
                        decimal lng = Convert.ToDecimal(dataReader.GetValue(3).ToString());
                        decimal lat = Convert.ToDecimal(dataReader.GetValue(4).ToString());
                        cinfo.lng = lng;
                        cinfo.lat = lat;
                        JObject xzqh = GetOrgAreaByPoint(lng, lat);
                        countyid = xzqh["countyid"].ToString();
                    }
                    //案件编号
                    if (!dataReader.IsDBNull(1))
                    {
                        var caseinfo = client.Queryable<DroneCaseInfoImport>().Where(r => r.case_no == dataReader.GetValue(1).ToString()).ToList();
                        var caseoinfo=client.Queryable<ViewDroneCaseinfoTotal>().Where(r => r.case_no == dataReader.GetValue(1).ToString()).ToList();
                        if (caseinfo.Count > 0|| caseoinfo.Count>0)
                        {
                            throw new Exception("图斑编号" + dataReader.GetValue(1).ToString() + "已存在，请重新整理数据");
                        }
                        else
                        {
                            cinfo.case_no = dataReader.GetValue(1).ToString();
                        }
                    }
                    else
                    {
                        bool isUnique;
                        var prefix = type?.Prefix;
                        do
                        {
                            var caseno = prefix + countyid + DateTime.Now.ToString("yyyyMMdd") + random.Next(1000, 9999).ToString();
                            var count = db.Queryable<DroneCaseInfoImport>().Where(c => c.case_no == caseno).Count();
                            isUnique = count == 0;

                        } while (!isUnique);
                    }

                    //geom
                    if (!dataReader.IsDBNull(0))
                    {
                        var geometry = (Geometry)dataReader.GetValue(0);
                        if (!geometry.IsValid)
                        {
                            throw new Exception("图斑" + cinfo.case_no + "未闭合，请重新整理数据");
                        }
                        var geometryForWgs84 = GeometryFactory.Default.WithSRID(int.Parse(srid))
                            .CreateGeometry(geometry);
                        geomSql.AppendFormat($" update drone_caseinfo_import set geom = '{geometryForWgs84.AsText()}' where \"Id\" = '{cinfo.Id}';");
                    }
                    datalist.Add(cinfo);
                }
            }

            using (var uow = base.UnitWork.CreateContext())
            {
                await uow.DroneCaseInfoImport.InsertRangeAsync(datalist);
                await uow.Db.Ado.ExecuteCommandAsync(geomSql.ToString());
                var flag = uow.Commit();
                if (flag == true)
                {
                    return new Response<string>()
                    {
                        Result = pici,
                        Message = "导入成功"
                    };
                }
                else
                {
                    return new Response<string>()
                    {
                        Result = "false",
                        Message = "导入失败"
                    };
                }

            }
        }

        /// <summary>
        /// 矿产shp导入
        /// </summary>
        /// <param name="zipFilePath"></param>
        /// <param name="srid"></param>
        /// <returns></returns>
        /// <exception cref="FileNotFoundException"></exception>
        /// <exception cref="Exception"></exception>
        public async Task<Response<string>> ImportMineralShpData(string zipFilePath, string srid, string subjectname)
        {
            var _user = _auth.GetCurrentUser().User;
            using var db = Repository.AsSugarClient();

            // 开启事务
            await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
            // 取得文件完全路径
            zipFilePath = System.IO.Path.Combine(_filePath, zipFilePath);
            // 确保ZIP文件存在
            if (!File.Exists(zipFilePath))
            {
                throw new FileNotFoundException("ZIP文件未找到。");
            }

            // 打开ZIP存档 取除后缀之前的字符串
            var extractPath = zipFilePath.Substring(0, zipFilePath.LastIndexOf(".", StringComparison.Ordinal)) +
                              "extract";
            // 解压文件
            await UnZip(zipFilePath, extractPath);
            var searchPattern = "*.shp"; // 设置你想要遍历的文件后缀名
            var fileName = Directory.GetFiles(extractPath, searchPattern, SearchOption.AllDirectories);
            if (fileName.IsEmpty())
            {
                throw new Exception("压缩文件中无shp文件");
            }

            var shpFileName = fileName[0];
            var shpFile = System.IO.Path.Combine(extractPath, shpFileName);

            List<DroneCaseInfoImport> datalist = new List<DroneCaseInfoImport>();
            List<DroneShpData> shplist = new List<DroneShpData>();
            StringBuilder geomSql = new StringBuilder();
            //批量更新面积
            StringBuilder sql = new StringBuilder();
            Random random = new Random();
            //批次号
            var pici = DateTime.Now.ToString("yyyyMMssHHmmss") + random.Next(1000, 9999).ToString();

            using (var dataReader = new ShapefileDataReader(shpFile, GeometryFactory.Default))
            {
                while (dataReader.Read())
                {
                    string countyid = "";
                    DroneCaseInfoImport cinfo = new DroneCaseInfoImport();
                    cinfo.Id = Guid.NewGuid().ToString();
                    cinfo.createtime = DateTime.Now;
                    cinfo.createuser = _user.Id.ToString();
                    cinfo.createusername = _user.Name.ToString();
                    cinfo.case_description = dataReader.GetValue(2).ToString();
                    cinfo.subjectname = subjectname;
                    cinfo.picihao = pici;
                    if (dataReader.IsDBNull(6))
                    {
                        throw new Exception("违法面积不能为空");
                    }
                    else
                    {
                        cinfo.area = dataReader.GetValue(6).ToString();
                    }
                    if (dataReader.IsDBNull(7))
                    {
                        throw new Exception("图斑来源不能为空");
                    }
                    else
                    {
                        cinfo.tubanlaiyuan = dataReader.GetValue(7).ToString();
                    }
                    if (dataReader.IsDBNull(8))
                    {
                        cinfo.weifakaicaiarea_base = "0.00";
                    }
                    else
                    {
                        cinfo.weifakaicaiarea_base = dataReader.GetValue(8).ToString();
                    }
                    if (dataReader.IsDBNull(9))
                    {
                        cinfo.weifazhandiarea_base = "0.00";
                    }
                    else
                    {
                        cinfo.weifazhandiarea_base = dataReader.GetValue(9).ToString();
                    }
                    if (dataReader.IsDBNull(10))
                    {
                        cinfo.gengdi_area = "0.00";
                    }
                    else
                    {
                        cinfo.gengdi_area = dataReader.GetValue(10).ToString();
                    }
                    if (dataReader.IsDBNull(11))
                    {
                        cinfo.yongjiujibennongtian_area = "0.00";
                    }
                    else
                    {
                        cinfo.yongjiujibennongtian_area = dataReader.GetValue(11).ToString();
                    }
                    if (dataReader.IsDBNull(12))
                    {
                        cinfo.shengtaibaohuhongxian_area = "0.00";
                    }
                    else
                    {
                        cinfo.shengtaibaohuhongxian_area = dataReader.GetValue(12).ToString();
                    }
                    if (dataReader.IsDBNull(13))
                    {
                        cinfo.cskfbjarea_base = "0.00";
                    }
                    else
                    {
                        cinfo.guotukongjianguihua_area = dataReader.GetValue(13).ToString();
                    }

                    cinfo.typename = dataReader.GetValue(5).ToString();

                    //坐标及行政区划
                    if (dataReader.IsDBNull(3) || dataReader.IsDBNull(4))
                    {
                        throw new Exception("坐标不能为空");
                    }
                    else
                    {
                        decimal lng = Convert.ToDecimal(dataReader.GetValue(3).ToString());
                        decimal lat = Convert.ToDecimal(dataReader.GetValue(4).ToString());
                        cinfo.lng = lng;
                        cinfo.lat = lat;
                        JObject xzqh = GetOrgAreaByPoint(lng, lat);
                        countyid = xzqh["countyid"].ToString();
                    }
                    //案件编号
                    if (!dataReader.IsDBNull(1))
                    {
                        var caseinfo = client.Queryable<DroneCaseInfoImport>().Where(r => r.case_no == dataReader.GetValue(1).ToString()).ToList();
                        var caseoinfo = client.Queryable<DroneCaseInfoMinerals>().Where(r => r.case_no == dataReader.GetValue(1).ToString()).ToList();
                        if (caseinfo.Count > 0|| caseoinfo.Count>0)
                        {
                            throw new Exception("图斑编号" + dataReader.GetValue(1).ToString() + "已存在，请重新整理数据");
                        }
                        else
                        {
                            cinfo.case_no = dataReader.GetValue(1).ToString();
                        }
                    }
                    else
                    {
                        bool isUnique;
                        var prefix = "KC";
                        do
                        {
                            var caseno = prefix + countyid + DateTime.Now.ToString("yyyyMMdd") + random.Next(1000, 9999).ToString();
                            var count = db.Queryable<DroneCaseInfoImport>().Where(c => c.case_no == caseno).Count();
                            isUnique = count == 0;

                        } while (!isUnique);
                    }

                    //geom
                    if (!dataReader.IsDBNull(0))
                    {
                        var geometry = (Geometry)dataReader.GetValue(0);
                        if (!geometry.IsValid)
                        {
                            throw new Exception("图斑" + cinfo.case_no + "未闭合，请重新整理数据");
                        }
                        var geometryForWgs84 = GeometryFactory.Default.WithSRID(int.Parse(srid))
                            .CreateGeometry(geometry);
                        geomSql.AppendFormat($" update drone_caseinfo_import set geom = '{geometryForWgs84.AsText()}' where \"Id\" = '{cinfo.Id}';");
                    }
                    datalist.Add(cinfo);
                }
            }

            using (var uow = base.UnitWork.CreateContext())
            {
                await uow.DroneCaseInfoImport.InsertRangeAsync(datalist);
                await uow.Db.Ado.ExecuteCommandAsync(geomSql.ToString());
                var flag = uow.Commit();
                if (flag == true)
                {
                    return new Response<string>()
                    {
                        Result = pici,
                        Message = "导入成功"
                    };
                }
                else
                {
                    return new Response<string>()
                    {
                        Result = "false",
                        Message = "导入失败"
                    };
                }

            }
        }



        private static async Task UnZip(string zipFilePath, string extractPath)
        {
            await Task.Run(() =>
            {
                Directory.CreateDirectory(extractPath);
                // 设置字符编码
                Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
                var gbk = Encoding.GetEncoding("utf-8");
                using (var archive = ZipFile.OpenRead(zipFilePath))
                {
                    // 列举ZIP文件中的条目
                    foreach (var entry in archive.Entries)
                    {
                        var xxx = gbk.GetString(Encoding.Default.GetBytes(entry.FullName));
                        Console.WriteLine(xxx);
                    }

                    // 提取ZIP文件中的所有文件到指定目录

                    foreach (var entry in archive.Entries)
                    {
                        if (entry.Name != string.Empty)
                        {
                            // 确保完整路径存在  entry.FullName 是否可以编码
                            var fixedEntryName = entry.FullName.Replace("/", "");
                            var destinationPath = System.IO.Path.GetFullPath(System.IO.Path.Combine(extractPath, fixedEntryName));
                            Console.WriteLine("解压文件路径：" + destinationPath);
                            if (!destinationPath.StartsWith(System.IO.Path.GetFullPath(extractPath) + System.IO.Path.DirectorySeparatorChar))
                            {
                                throw new UnauthorizedAccessException("试图提取的文件超出了目标文件夹的路径边界。");
                            }

                            // 提取条目到目标路径
                            entry.ExtractToFile(destinationPath, overwrite: true);
                        }
                    }
                }

                // 遍历解压目录，是否有shp后缀的文件
                if (!Directory.Exists(extractPath))
                {
                    throw new Exception("文件解压失败");
                }
            });
        }

        /// <summary>
        /// 河东设施农业监管shp导入
        /// </summary>
        /// <param name="zipFilePath"></param>
        /// <param name="srid"></param>
        /// <returns></returns>
        /// <exception cref="FileNotFoundException"></exception>
        /// <exception cref="Exception"></exception>
        public async Task<Response<bool>> ImportCaseInfoShpDataSsny(string zipFilePath, string srid)
        {
            var _user = _auth.GetCurrentUser().User;
            using var db = Repository.AsSugarClient();

            // 开启事务
            await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
            // 取得文件完全路径
            zipFilePath = System.IO.Path.Combine(_filePath, zipFilePath);
            // 确保ZIP文件存在
            if (!File.Exists(zipFilePath))
            {
                throw new FileNotFoundException("ZIP文件未找到。");
            }

            // 打开ZIP存档 取除后缀之前的字符串
            var extractPath = zipFilePath.Substring(0, zipFilePath.LastIndexOf(".", StringComparison.Ordinal)) +
                              "extract";
            // 解压文件
            await UnZip(zipFilePath, extractPath);
            var searchPattern = "*.shp"; // 设置你想要遍历的文件后缀名
            var fileName = Directory.GetFiles(extractPath, searchPattern, SearchOption.AllDirectories);
            if (fileName.IsEmpty())
            {
                throw new Exception("压缩文件中无shp文件");
            }

            var shpFileName = fileName[0];
            var shpFile = System.IO.Path.Combine(extractPath, shpFileName);

            List<DroneSsnyd> datalist = new List<DroneSsnyd>();
            List<DroneShpData> shplist = new List<DroneShpData>();
            StringBuilder geomSql = new StringBuilder();
            //批量更新面积
            StringBuilder sql = new StringBuilder();
            Random random = new Random();
            //批次号
            //var pici = DateTime.Now.ToString("yyyyMMssHHmmss") + random.Next(1000, 9999).ToString();
            int i = 0;
            using (var dataReader = new ShapefileDataReader(shpFile, GeometryFactory.Default))
            {
                while (dataReader.Read())
                {
                    DroneSsnyd cinfo = new DroneSsnyd();
                    cinfo.Id = Guid.NewGuid().ToString();
                    cinfo.createuser = _user.Id.ToString();

                    //案件编号
                    if (!dataReader.IsDBNull(1))
                    {
                        var caseinfo = client.Queryable<DroneSsnyd>().Where(r => r.xiangmu_no == dataReader.GetValue(1).ToString()).ToList();
                        if (caseinfo.Count > 0)
                        {
                            throw new Exception("图斑编号" + dataReader.GetValue(1).ToString() + "已存在，请重新整理数据");
                        }
                        else
                        {
                            cinfo.xiangmu_no = dataReader.GetValue(1).ToString();
                        }
                    }
                    else
                    {
                        throw new Exception("图斑编号不能为空，请重新整理数据");
                    }

                    if (dataReader.IsDBNull(2))
                    {
                        cinfo.xiangmu_name = "";
                    }
                    else
                    {
                        cinfo.xiangmu_name = dataReader.GetValue(2).ToString();
                    }

                    if (dataReader.IsDBNull(3))
                    {
                        cinfo.xiangmu_yt = "";
                    }
                    else
                    {
                        cinfo.xiangmu_yt = dataReader.GetValue(3).ToString();
                    }

                    if (dataReader.IsDBNull(4))
                    {
                        cinfo.xingzhengquhua = "";
                    }
                    else
                    {
                        cinfo.xingzhengquhua = dataReader.GetValue(4).ToString();
                    }


                    if (dataReader.IsDBNull(5))
                    {
                        cinfo.shenqing_area = 0;
                    }
                    else
                    {
                        var aa = dataReader.GetValue(5);
                        cinfo.shenqing_area = Convert.ToDecimal(dataReader.GetValue(5).ToString());
                    }
                    if (dataReader.IsDBNull(6))
                    {
                        cinfo.beian_no = "";
                    }
                    else
                    {
                        cinfo.beian_no = dataReader.GetValue(6).ToString();
                    }

                    if (dataReader.IsDBNull(7))
                    {
                        cinfo.beianriqi = null;
                    }
                    else
                    {
                        var value = dataReader.GetValue(7);
                        cinfo.beianriqi = value as DateTime? ?? Convert.ToDateTime(value);
                    }

                    if (dataReader.IsDBNull(8))
                    {
                        cinfo.quanliren = "";
                    }
                    else
                    {
                        cinfo.quanliren = dataReader.GetValue(8).ToString();
                    }

                    if (dataReader.IsDBNull(9))
                    {
                        cinfo.start_time = null;
                    }
                    else
                    {
                        var value = dataReader.GetValue(9);
                        cinfo.start_time = value as DateTime? ?? Convert.ToDateTime(value);
                    }

                    if (dataReader.IsDBNull(10))
                    {
                        cinfo.end_time = null;
                    }
                    else
                    {
                        var value = dataReader.GetValue(10);
                        cinfo.end_time = value as DateTime? ?? Convert.ToDateTime(value);
                    }

                    if (dataReader.IsDBNull(11))
                    {
                        cinfo.jianzhujiegou = "";
                    }
                    else
                    {
                        cinfo.jianzhujiegou = dataReader.GetValue(11).ToString();
                    }

                    if (dataReader.IsDBNull(12))
                    {
                        cinfo.shengchan_area = 0;
                    }
                    else
                    {
                        cinfo.shengchan_area = Convert.ToDecimal(dataReader.GetValue(12).ToString());
                    }
                    if (dataReader.IsDBNull(13))
                    {
                        cinfo.fuzhu_area = 0;
                    }
                    else
                    {
                        cinfo.fuzhu_area = Convert.ToDecimal(dataReader.GetValue(13).ToString());
                    }

                    string countyid = "";
                    string streetid = "";

                    if (dataReader.IsDBNull(14))
                    {
                        cinfo.countyname = "";
                    }
                    else
                    {
                        cinfo.countyname =dataReader.GetValue(14).ToString();
                        var org = client.Queryable<SysOrg>().Where(r => r.Name == cinfo.countyname).First();
                        if (org != null)
                        {
                            countyid = org.Id.ToString();
                            cinfo.countyid = org.Id.ToString();
                        }
                    }

                    if (dataReader.IsDBNull(15))
                    {
                        cinfo.streetname = "";
                    }
                    else
                    {
                        cinfo.streetname = dataReader.GetValue(15).ToString();
                        var org = client.Queryable<SysOrg>()
                            .Where(r => r.Name == cinfo.streetname)
                            .WhereIF(!string.IsNullOrEmpty(countyid),r=>r.ParentId.ToString() == countyid)
                            .First();
                        if (org != null)
                        {
                            streetid = org.Id.ToString();
                            cinfo.streetid = org.Id.ToString();
                        }
                    }

                    if (dataReader.IsDBNull(16))
                    {
                        cinfo.communityname = "";
                    }
                    else
                    {
                        cinfo.communityname = dataReader.GetValue(16).ToString();
                        var org = client.Queryable<SysOrg>()
                            .Where(r => r.Name == cinfo.communityname)
                            .WhereIF(!string.IsNullOrEmpty(streetid), r => r.ParentId.ToString() == streetid)
                            .First();
                        if (org != null)
                        {
                            cinfo.communityid = org.Id.ToString();
                        }
                    }

                    //图斑数据drone_shp_data
                    DroneShpData model = new DroneShpData();
                    model.relid = cinfo.Id;
                    model.createtime = DateTime.Now;
                    model.createuser = _user.Name;
                    //获取主键
                    string _gid = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
                    model.gid = int.Parse(_gid) + i;//转为数字类型
                    i++;
                    cinfo.geomid = model.gid.ToString();

                    cinfo.handle_status_id = 0;


                    //geom
                    if (!dataReader.IsDBNull(0))
                    {
                        var geometry = (Geometry)dataReader.GetValue(0);
                        if (!geometry.IsValid)
                        {
                            throw new Exception("图斑" + cinfo.xiangmu_no + "未闭合，请重新整理数据");
                        }
                        var geometryForWgs84 = GeometryFactory.Default.WithSRID(int.Parse(srid))
                            .CreateGeometry(geometry);
                        geomSql.AppendFormat($" update drone_shp_data set geom = '{geometryForWgs84.AsText()}' where gid = '{model.gid}';");
                        sql.AppendFormat($" update drone_shp_data set area = st_area(st_transform(ST_SetSRID(geom,4326), 4527)) where gid = '{model.gid}';");
                    }
                    datalist.Add(cinfo);
                    shplist.Add(model);
                }
            }

            using (var uow = base.UnitWork.CreateContext())
            {
                await uow.DroneSsnyd.InsertRangeAsync(datalist);
                await uow.DroneShpData.InsertRangeAsync(shplist);
                await uow.Db.Ado.ExecuteCommandAsync(geomSql.ToString());
                await uow.Db.Ado.ExecuteCommandAsync(sql.ToString());
                var flag = uow.Commit();
                if (flag == true)
                {
                    return new Response<bool>()
                    {
                        Result = flag,
                        Message = "导入成功"
                    };
                }
                else
                {
                    return new Response<bool>()
                    {
                        Result = false,
                        Message = "导入失败"
                    };
                }

            }
        }

        #endregion


        #region 添加查询前时像后时像
        //添加时像后时像
        public Response<bool> AddTubanshixiang(List<DroneTuBanshixiang> req)
        {
            //var user = _auth.GetCurrentUser().User;
            //添加数据
            var flag = client.Ado.UseTran(() =>
            {
                var data = client.Queryable<DroneTuBanshixiang>().ToList();
                client.Deleteable(data).ExecuteCommand();
                client.Insertable(req).ExecuteCommand();
            });
            if (flag.IsSuccess)
            {
                return new Response<bool> { Result = true };
            }
            else
            {
                return new Response<bool> { Result = false, Message = "操作失败" };
            }
        }

        //查询图斑前时像后时像
        public async Task<Response<List<DroneTuBanshixiang>>> LoadTuBanshixiang()
        {
            //查询任务数据
            var data = await client.Queryable<DroneTuBanshixiang>().ToListAsync();
            return new Response<List<DroneTuBanshixiang>>
            {
                Result = data
            };
        }
        #endregion

        #region 不查询截图 直接导入
        public async Task<Response<bool>> UpdateDroneCaseInfoT(string picihao)
        {
            var data = client.Queryable<DroneCaseInfoImport>().Where(r => r.picihao==picihao).ToList();
            if (data.Count > 0)
            {
                using (var uow = base.UnitWork.CreateContext())
                {
                    #region 导入案件信息
                    int i = 0;
                    List<DroneCaseInfoSingle> datalist = new List<DroneCaseInfoSingle>();
                    List<DroneShpData> shplist = new List<DroneShpData>();
                    StringBuilder geomSql = new StringBuilder();
                    StringBuilder sql = new StringBuilder();
                    foreach (var item in data)
                    {
                        DroneCaseInfoSingle cinfo = new DroneCaseInfoSingle();
                        cinfo.Id = item.Id;
                        cinfo.createtime = DateTime.Now;
                        cinfo.createuser = item.createuser;
                        cinfo.createusername = item.createusername;
                        cinfo.is_illegal = 1;
                        cinfo.is_intact = 1;
                        cinfo.synchronoustime = DateTime.Now;
                        cinfo.handle_status_id = 0;
                        cinfo.handle_status_name = "待接收";
                        cinfo.is_jieshou = 0;
                        cinfo.is_closed = 0;
                        cinfo.is_delete = 0;
                        cinfo.case_description = item.case_description;
                        cinfo.area = item.area;
                        cinfo.tubanlaiyuan = item.tubanlaiyuan;
                        cinfo.typename = item.typename;
                        //类型id
                        var type = client.Queryable<SysDataItemDetail>().Where(r => r.ItemName == cinfo.typename && r.ItemCode == "DRONE_CASE_TYPE").First();
                        if (type != null)
                        {
                            cinfo.typeid = type.ItemDetailId;
                        }

                        cinfo.lng = item.lng;
                        cinfo.lat = item.lat;
                        JObject xzqh = GetOrgAreaByPoint((decimal)cinfo.lng, (decimal)cinfo.lat);
                        cinfo.countyid = xzqh["countyid"].ToString();
                        cinfo.countyname = xzqh["countyname"].ToString();
                        cinfo.streetid = xzqh["streetid"].ToString();
                        cinfo.streetname = xzqh["streetname"].ToString();
                        cinfo.communityid = xzqh["communityid"].ToString();
                        cinfo.communityname = xzqh["communityname"].ToString();
                        cinfo.address = cinfo.countyname + " " + cinfo.streetname + " " + cinfo.communityname;
                        cinfo.case_no = item.case_no;
                        cinfo.casepic = item.casepic;
                        cinfo.nongyongdi_area = item.nongyongdi_area;
                        cinfo.yongjiujibennongtian_area = item.yongjiujibennongtian_area;
                        cinfo.gengdi_area = item.gengdi_area;
                        cinfo.guotukongjianguihua_area = item.guotukongjianguihua_area;
                        cinfo.shengtaibaohuhongxian_area = item.shengtaibaohuhongxian_area;
                        cinfo.zhongdianquyu_area = item.zhongdianquyu_area;

                        //图斑数据drone_shp_data
                        DroneShpData model = new DroneShpData();
                        model.relid = cinfo.Id;
                        model.createtime = DateTime.Now;
                        model.createuser = item.createusername;
                        model.area = item.shparea;
                        //获取主键
                        string _gid = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
                        model.gid = int.Parse(_gid) + i;//转为数字类型
                        i++;
                        cinfo.geomid = model.gid.ToString();

                        geomSql.AppendFormat($" update drone_shp_data set geom = '{item.geom}' where gid = '{model.gid}';");
                        sql.AppendFormat($" update drone_shp_data set area = st_area(st_transform(ST_SetSRID(geom,4326), 4527)) where gid = '{model.gid}';");
                        shplist.Add(model);

                        datalist.Add(cinfo);
                    }
                    string errornums = "";
                    switch (data[0].subjectname)
                    {
                        case "巡察审计":
                            var dataxunchashenji = datalist.MapTo<List<DroneCaseInfoXcsj>>();
                            //判断是否有重复数据
                            List<string> casenos = dataxunchashenji?.Select(r => r.case_no).ToList();
                            var caseinfo = client.Queryable<DroneCaseInfoXcsj>().Where(r => casenos.Contains(r.case_no)).ToList();
                            if (caseinfo.Count > 0)
                            {
                                var nums = caseinfo.Select(r => r.case_no).ToList();
                                var dataxuncha = dataxunchashenji.Where(r => !nums.Contains(r.case_no)).ToList();
                                //保存重复数据案件编号
                                errornums = string.Join(",", nums);
                                //添加未重复数据
                                await uow.DroneCaseInfoXcsj.InsertRangeAsync(dataxuncha);
                            }
                            else
                            {
                                await uow.DroneCaseInfoXcsj.InsertRangeAsync(dataxunchashenji);
                            }
                            break;
                        case "耕地非粮化":
                            var datafeilianghua = datalist.MapTo<List<DroneCaseInfoFLH>>();
                            List<string> casenof = datafeilianghua?.Select(r => r.case_no).ToList();
                            var caseinfof = client.Queryable<DroneCaseInfoFLH>().Where(r => casenof.Contains(r.case_no)).ToList();
                            if (caseinfof.Count > 0)
                            {
                                var nums = caseinfof.Select(r => r.case_no).ToList();
                                var dataxuncha = datafeilianghua.Where(r => !nums.Contains(r.case_no)).ToList();
                                //保存重复数据案件编号
                                errornums = string.Join(",", nums);
                                //添加未重复数据
                                await uow.DroneCaseInfoFLH.InsertRangeAsync(dataxuncha);
                            }
                            else
                            {
                                await uow.DroneCaseInfoFLH.InsertRangeAsync(datafeilianghua);
                            }
                            break;
                        case "违法用地":
                            List<string> casenog = datalist?.Select(r => r.case_no).ToList();
                            var caseinfos = client.Queryable<DroneCaseInfoSingle>().Where(r => casenog.Contains(r.case_no)).ToList();
                            if (caseinfos.Count > 0)
                            {
                                var nums = caseinfos.Select(r => r.case_no).ToList();
                                var dataxuncha = datalist.Where(r => !nums.Contains(r.case_no)).ToList();
                                //保存重复数据案件编号
                                errornums = string.Join(",", nums);
                                //添加未重复数据
                                await uow.DroneCaseInfoSingle.InsertRangeAsync(dataxuncha);
                            }
                            else
                            {
                                await uow.DroneCaseInfoSingle.InsertRangeAsync(datalist);
                            }
                            break;
                        case "重点问题I类":
                            var zhongdianwenti1 = datalist.MapTo<List<DroneCaseInfoZdwt1>>();
                            List<string> casenot = zhongdianwenti1?.Select(r => r.case_no).ToList();
                            var caseinfoz = client.Queryable<DroneCaseInfoZdwt1>().Where(r => casenot.Contains(r.case_no)).ToList();
                            if (caseinfoz.Count > 0)
                            {
                                var nums = caseinfoz.Select(r => r.case_no).ToList();
                                var dataxuncha = zhongdianwenti1.Where(r => !nums.Contains(r.case_no)).ToList();
                                //保存重复数据案件编号
                                errornums = string.Join(",", nums);
                                //添加未重复数据
                                await uow.DroneCaseInfoZdwt1.InsertRangeAsync(dataxuncha);
                            }
                            else
                            {
                                await uow.DroneCaseInfoZdwt1.InsertRangeAsync(zhongdianwenti1);
                            }
                            break;
                        case "重点问题II类":
                            var zhongdianwenti2 = datalist.MapTo<List<DroneCaseInfoZdwt2>>();
                            List<string> casenow = zhongdianwenti2?.Select(r => r.case_no).ToList();
                            var caseinfow = client.Queryable<DroneCaseInfoZdwt2>().Where(r => casenow.Contains(r.case_no)).ToList();
                            if (caseinfow.Count > 0)
                            {
                                var nums = caseinfow.Select(r => r.case_no).ToList();
                                var dataxuncha = zhongdianwenti2.Where(r => !nums.Contains(r.case_no)).ToList();
                                //保存重复数据案件编号
                                errornums = string.Join(",", nums);
                                //添加未重复数据
                                await uow.DroneCaseInfoZdwt2.InsertRangeAsync(dataxuncha);
                            }
                            else
                            {
                                await uow.DroneCaseInfoZdwt2.InsertRangeAsync(zhongdianwenti2);
                            }
                             break;
                        case "卫片下发":
                            var weipianxiafa = datalist.MapTo<List<DroneCaseInfoSatellite>>();
                            List<string> casenoa = weipianxiafa?.Select(r => r.case_no).ToList();
                            var caseinfoa = client.Queryable<DroneCaseInfoSatellite>().Where(r => casenoa.Contains(r.case_no)).ToList();
                            if (caseinfoa.Count > 0)
                            {
                                var nums = caseinfoa.Select(r => r.case_no).ToList();
                                var dataxuncha = weipianxiafa.Where(r => !nums.Contains(r.case_no)).ToList();
                                //保存重复数据案件编号
                                errornums = string.Join(",", nums);
                                //添加未重复数据
                                await uow.DroneCaseInfoSatellite.InsertRangeAsync(dataxuncha);
                            }
                            else
                            {
                                await uow.DroneCaseInfoSatellite.InsertRangeAsync(weipianxiafa);
                            }
                            break;
                    }
                    await uow.DroneShpData.InsertRangeAsync(shplist);
                    await uow.Db.Ado.ExecuteCommandAsync(geomSql.ToString());
                    await uow.Db.Ado.ExecuteCommandAsync(sql.ToString());

                    var flag = uow.Commit();

                    //返回值
                    var res = new Response<bool>
                    {
                        Result = flag,
                        Message = flag == true ? "导入数据成功" : "导入数据失败"
                    };

                    if (flag == true)
                    {
                        if (!string.IsNullOrEmpty(errornums))
                        {
                            res.Message = "重复编号：" + errornums + "未导入";
                        }
                    }
                    //返回值
                    return res;
                    #endregion
                }

            }
            else
            {
                throw new Exception("未找到该批次数据");
            }
        }


        /// <summary>
        /// 导入矿产信息--不截图
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        public async Task<Response<bool>> UpdateDroneCaseInfoMineralsT(string picihao)
        {
            var data = client.Queryable<DroneCaseInfoImport>().Where(r => r.picihao == picihao).ToList();
            if(data.Count > 0)
            {
                using (var uow = base.UnitWork.CreateContext())
                {
                    #region 案件信息处理
                    int i = 0;
                    List<DroneCaseInfoMinerals> datalist = new List<DroneCaseInfoMinerals>();
                    List<DroneShpData> shplist = new List<DroneShpData>();
                    StringBuilder geomSql = new StringBuilder();
                    StringBuilder sql = new StringBuilder();
                    foreach (var item in data)
                    {
                        DroneCaseInfoMinerals cinfo = new DroneCaseInfoMinerals();
                        cinfo.Id = item.Id;
                        cinfo.createtime = DateTime.Now;
                        cinfo.createuser = item.createuser;
                        cinfo.is_illegal = 1;
                        //cinfo.xiafatime_base = DateTime.Now;
                        cinfo.handle_status_id = 0;
                        cinfo.is_closed = 0;
                        cinfo.case_description = item.case_description;
                        cinfo.weifaarea_base = item.area;
                        cinfo.tubanlaiyuan = item.tubanlaiyuan;
                        cinfo.typename = item.typename;
                        cinfo.centerlat_base = item.lng.ToString();
                        cinfo.centerlng_base = item.lat.ToString();
                        JObject xzqh = GetOrgAreaByPoint((decimal)item.lng, (decimal)item.lat);
                        cinfo.countyid = xzqh["countyid"].ToString();
                        cinfo.countyname = xzqh["countyname"].ToString();
                        cinfo.streetid = xzqh["streetid"].ToString();
                        cinfo.streetname = xzqh["streetname"].ToString();
                        cinfo.communityid = xzqh["communityid"].ToString();
                        cinfo.communityname = xzqh["communityname"].ToString();
                        //cinfo.address = cinfo.countyname + " " + cinfo.streetname + " " + cinfo.communityname;
                        cinfo.case_no = item.case_no;
                        cinfo.tubanpic_base = item.casepic;
                        cinfo.jbntarea_base = item.yongjiujibennongtian_area;
                        cinfo.gengdi_area = item.gengdi_area;
                        cinfo.stbhhxarea_base = item.shengtaibaohuhongxian_area;
                        cinfo.cskfbjarea_base = item.cskfbjarea_base;
                        cinfo.weifazhandiarea_base = item.weifazhandiarea_base;
                        cinfo.weifakaicaiarea_base = item.weifakaicaiarea_base;

                        //图斑数据drone_shp_data
                        DroneShpData model = new DroneShpData();
                        model.relid = cinfo.Id;
                        model.createtime = DateTime.Now;
                        model.createuser = item.createusername;
                        model.area = item.shparea;
                        //获取主键
                        string _gid = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
                        model.gid = int.Parse(_gid) + i;//转为数字类型
                        i++;
                        cinfo.geomid = model.gid.ToString();

                        geomSql.AppendFormat($" update drone_shp_data set geom = '{item.geom}' where gid = '{model.gid}';");
                        sql.AppendFormat($" update drone_shp_data set area = st_area(st_transform(ST_SetSRID(geom,4326), 4527)) where gid = '{model.gid}';");
                        shplist.Add(model);

                        datalist.Add(cinfo);
                    }
                    var caseno = datalist.Select(r => r.case_no).ToList();
                    var caseinfo = client.Queryable<DroneCaseInfoMinerals>().Where(r => caseno.Contains(r.case_no)).ToList();
                    string errornums = "";
                    if (caseinfo.Count > 0)
                    {
                        var nums = caseinfo.Select(r => r.case_no).ToList();
                        var dataxuncha = datalist.Where(r => !nums.Contains(r.case_no)).ToList();
                        //保存重复数据案件编号
                        errornums = string.Join(",", nums);
                        //添加未重复数据
                        await uow.DroneCaseInfoMinerals.InsertRangeAsync(dataxuncha);

                    }
                    else
                    {
                        await uow.DroneCaseInfoMinerals.InsertRangeAsync(datalist);
                    }

                    await uow.DroneShpData.InsertRangeAsync(shplist);
                    await uow.Db.Ado.ExecuteCommandAsync(geomSql.ToString());
                    await uow.Db.Ado.ExecuteCommandAsync(sql.ToString());

                    var flag = uow.Commit();
                    #endregion
                    var res=new Response<bool>
                    {
                        Result = flag,
                        Message = flag == true ? "导入数据成功" : "导入数据失败"
                    };

                    if(flag == true)
                    {
                        if (!string.IsNullOrEmpty(errornums))
                        {
                            res.Message = "重复编号：" + errornums + "未导入";
                        }
                    }
                    //返回值
                    return res;
                }
            }
            else
            {
                throw new Exception("未找到该批次数据");
            }
           
        }
        #endregion
    }
}