using Application.Shared.Dtos.Units.Imports;
using Domain.Models.Units;
using EntityFramework;
using Infrastructure.Abstructions;
using Infrastructure.Converts;
using Infrastructure.Excel;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace BackGround.Hangfire.Jobs.Units
{
    public class ImportUnitsJob : ITransientDependency, IImportUnitsJob
    {
        private readonly IParser _excelParser;
        private readonly FireStationDbContext _context;
        private readonly IConverter _converter;
        public ImportUnitsJob(IParser parser, FireStationDbContext context, IConverter converter)
        {
            _excelParser = parser;
            _context = context;
            _converter = converter;
        }
        public async Task Execute(Guid fileId)
        {
            var file =await _context.UploadedFiles.Where(u => u.Id == fileId).FirstOrDefaultAsync();
            if (file == null)
            {
                throw new Exception($"没有找到id为{fileId}的文件");
            }
            var fileUnit = await _context.FileUnits.FirstOrDefaultAsync(f => f.FileId == fileId);
            if (fileUnit == null)
            {
                throw new Exception($"没有找到文件{fileId}的单位关联记录");
            }
            var unitId = fileUnit.UnitId;
            int uniacid = fileUnit.Uniacid;
            int groupId = fileUnit.GroupId;
            var fileName = file.Path;
            var workBook = _excelParser.ReadExcel(fileName);
            var industrialEnterprises = _excelParser.ParseExcel<IndustrialEnterprise>(workBook);
            var tallBuildings = _excelParser.ParseExcel<TallBuilding>(workBook);
            var underGrunds = _excelParser.ParseExcel<UnderGround>(workBook);
            var hotels = _excelParser.ParseExcel<Hotel>(workBook);
            var others = _excelParser.ParseExcel<Other>(workBook);
            var welfareInstitutions = _excelParser.ParseExcel<WelfareInstitution>(workBook);
            var stateOrgans = _excelParser.ParseExcel<StateOrgan>(workBook);
            var bPCs = _excelParser.ParseExcel<BPC>(workBook);
            var schools = _excelParser.ParseExcel<School>(workBook);
            var medicalInstitutions = _excelParser.ParseExcel<MedicalInstitution>(workBook);
            var publicEntertainments = _excelParser.ParseExcel<PublicEntertainment>(workBook);
            var markets = _excelParser.ParseExcel<Market>(workBook);
            var inflammableAndExplosiveProductionUnits = _excelParser.ParseExcel<InflammableAndExplosiveProductionUnit>(workBook);
            var inflammableAndExplosiveStores = _excelParser.ParseExcel<InflammableAndExplosiveStore>(workBook);
            var gasStations = _excelParser.ParseExcel<GasStation>(workBook);
            var liquefiedGasStations = _excelParser.ParseExcel<LiquefiedGasStation>(workBook);
            var storages = _excelParser.ParseExcel<Storage>(workBook);
            workBook.Close();
            await SaveIndustrialEnterprises(industrialEnterprises, uniacid, groupId);
            await SaveTallBuildings(tallBuildings, uniacid, groupId);
            await SaveUnderGrounds(underGrunds, uniacid, groupId);
            await SaveHotels(hotels, uniacid, groupId);
            await SaveOthers(others, uniacid, groupId);
            await SaveWelfareInstitution(welfareInstitutions, uniacid, groupId);
            await SaveStateOrgans(stateOrgans, uniacid, groupId);
            await SavePBCs(bPCs, uniacid, groupId);
            await SaveSchools(schools, uniacid, groupId);
            await SaveMedicalInstitutions(medicalInstitutions, uniacid, groupId);
            await SavePublicEntertainments(publicEntertainments, uniacid, groupId);
            await SaveMarkets(markets, uniacid, groupId);
            await SaveInflammableAndExplosiveProductionUnits(inflammableAndExplosiveProductionUnits, uniacid, groupId);
            await SaveInflammableAndExplosiveStores(inflammableAndExplosiveStores, uniacid, groupId);
            await SaveGasStation(gasStations, uniacid, groupId);
            await SaveLiquefiedGasStations(liquefiedGasStations, uniacid, groupId);
            await SaveStorages(storages, uniacid, groupId);
            await _context.SaveChangesAsync();
        }

        private async Task SaveStorages(List<Storage> storages, int uniacid, int groupId)
        {
            var errors = new List<string>();
            var existCategory = await GetOrAddCategory(typeof(Storage),groupId,uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var storage in storages)
            {
                var existUnit = await unitQuery.Where(u => u.Name == storage.Name && u.LocationName == storage.Area
                && u.Uniacid==uniacid && u.GroupId==groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = storage.Area,
                        Name = storage.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(storage.RegulatoryLevel),
                        PrincipalName = storage.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = storage.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea=ConvertAreaSizeToInt(storage.Survey.Area),
                        UpdateTime = DateTime.Now,
                        Longitude = ParseGeoPoints(storage.Longitude),
                        Latitude = ParseGeoPoints(storage.Latitude),
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = storage.Area;
                    existUnit.Name = storage.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(storage.RegulatoryLevel);
                    existUnit.PrincipalName = storage.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = storage.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(storage.Survey.Area);
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(storage.Longitude);
                    existUnit.Latitude = ParseGeoPoints(storage.Latitude);
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveLiquefiedGasStations(List<LiquefiedGasStation> gasStations, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(LiquefiedGasStation), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var gasStation in gasStations)
            {
                var existUnit = await unitQuery.Where(u => u.Name == gasStation.Name && u.LocationName == gasStation.Area
                && u.Uniacid==uniacid && u.GroupId==groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = gasStation.Area,
                        Name = gasStation.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(gasStation.RegulatoryLevel),
                        PrincipalName = gasStation.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = gasStation.PersonInChargeOfSafetyInfo?.Phone,
                        UpdateTime = DateTime.Now,
                        Longitude = ParseGeoPoints(gasStation.Longitude),
                        Latitude = ParseGeoPoints(gasStation.Latitude),
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = gasStation.Area;
                    existUnit.Name = gasStation.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(gasStation.RegulatoryLevel);
                    existUnit.PrincipalName = gasStation.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = gasStation.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(gasStation.Longitude);
                    existUnit.Latitude = ParseGeoPoints(gasStation.Latitude);
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveGasStation(List<GasStation> gasStations, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(GasStation), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var gasStation in gasStations)
            {
                var existUnit = await unitQuery.Where(u => u.Name == gasStation.Name && u.LocationName == gasStation.Area
                && u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = gasStation.Area,
                        Name = gasStation.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(gasStation.RegulatoryLevel),
                        PrincipalName = gasStation.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = gasStation.PersonInChargeOfSafetyInfo?.Phone,
                        Longitude = ParseGeoPoints(gasStation.Longitude),
                        Latitude = ParseGeoPoints(gasStation.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = gasStation.Area;
                    existUnit.Name = gasStation.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(gasStation.RegulatoryLevel);
                    existUnit.PrincipalName = gasStation.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = gasStation.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(gasStation.Longitude);
                    existUnit.Latitude = ParseGeoPoints(gasStation.Latitude);
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveInflammableAndExplosiveStores(List<InflammableAndExplosiveStore> inflammableAndExplosiveStores, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(InflammableAndExplosiveStore), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var inflammableAndExplosiveStore in inflammableAndExplosiveStores)
            {
                var existUnit = await unitQuery.Where(u => u.Name == inflammableAndExplosiveStore.Name && u.LocationName == inflammableAndExplosiveStore.Area
                && u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = inflammableAndExplosiveStore.Area,
                        Name = inflammableAndExplosiveStore.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(inflammableAndExplosiveStore.RegulatoryLevel),
                        PrincipalName = inflammableAndExplosiveStore.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = inflammableAndExplosiveStore.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(inflammableAndExplosiveStore.Survey.Area),
                        Longitude = ParseGeoPoints(inflammableAndExplosiveStore.Longitude),
                        Latitude = ParseGeoPoints(inflammableAndExplosiveStore.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = inflammableAndExplosiveStore.Area;
                    existUnit.Name = inflammableAndExplosiveStore.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(inflammableAndExplosiveStore.RegulatoryLevel);
                    existUnit.PrincipalName = inflammableAndExplosiveStore.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = inflammableAndExplosiveStore.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(inflammableAndExplosiveStore.Survey.Area);
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(inflammableAndExplosiveStore.Longitude);
                    existUnit.Latitude = ParseGeoPoints(inflammableAndExplosiveStore.Latitude);
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveInflammableAndExplosiveProductionUnits(List<InflammableAndExplosiveProductionUnit> inflammableAndExplosiveProductionUnits, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(InflammableAndExplosiveProductionUnit), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var inflammableAndExplosiveProductionUnit in inflammableAndExplosiveProductionUnits)
            {
                var existUnit = await unitQuery.Where(u => u.Name == inflammableAndExplosiveProductionUnit.Name && u.LocationName == inflammableAndExplosiveProductionUnit.Area 
                && u.Uniacid==uniacid && u.GroupId==groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = inflammableAndExplosiveProductionUnit.Area,
                        Name = inflammableAndExplosiveProductionUnit.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(inflammableAndExplosiveProductionUnit.RegulatoryLevel),
                        PrincipalName = inflammableAndExplosiveProductionUnit.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = inflammableAndExplosiveProductionUnit.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(inflammableAndExplosiveProductionUnit.Survey.Area),
                        Longitude = ParseGeoPoints(inflammableAndExplosiveProductionUnit.Longitude),
                        Latitude = ParseGeoPoints(inflammableAndExplosiveProductionUnit.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = inflammableAndExplosiveProductionUnit.Area;
                    existUnit.Name = inflammableAndExplosiveProductionUnit.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(inflammableAndExplosiveProductionUnit.RegulatoryLevel);
                    existUnit.PrincipalName = inflammableAndExplosiveProductionUnit.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = inflammableAndExplosiveProductionUnit.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(inflammableAndExplosiveProductionUnit.Survey.Area);
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(inflammableAndExplosiveProductionUnit.Longitude);
                    existUnit.Latitude = ParseGeoPoints(inflammableAndExplosiveProductionUnit.Latitude);
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveMarkets(List<Market> markets, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(Market), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var market in markets)
            {
                var existUnit = await unitQuery.Where(u => u.Name == market.Name && u.LocationName == market.Area
                && u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = market.Area,
                        Name = market.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(market.RegulatoryLevel),
                        PrincipalName = market.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = market.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(market.Survey.Area),
                        Longitude = ParseGeoPoints(market.Longitude),
                        Latitude = ParseGeoPoints(market.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = market.Area;
                    existUnit.Name = market.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(market.RegulatoryLevel);
                    existUnit.PrincipalName = market.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = market.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(market.Survey.Area);
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(market.Longitude);
                    existUnit.Latitude = ParseGeoPoints(market.Latitude);
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SavePublicEntertainments(List<PublicEntertainment> publicEntertainments, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(PublicEntertainment), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var publicEntertainment in publicEntertainments)
            {
                var existUnit = await unitQuery.Where(u => u.Name == publicEntertainment.Name && u.LocationName == publicEntertainment.Area
                && u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = publicEntertainment.Area,
                        Name = publicEntertainment.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(publicEntertainment.RegulatoryLevel),
                        PrincipalName = publicEntertainment.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = publicEntertainment.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(publicEntertainment.Survey.Area),
                        Longitude = ParseGeoPoints(publicEntertainment.Longitude),
                        Latitude = ParseGeoPoints(publicEntertainment.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = publicEntertainment.Area;
                    existUnit.Name = publicEntertainment.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(publicEntertainment.RegulatoryLevel);
                    existUnit.PrincipalName = publicEntertainment.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = publicEntertainment.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(publicEntertainment.Survey.Area);
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(publicEntertainment.Longitude);
                    existUnit.Latitude = ParseGeoPoints(publicEntertainment.Latitude);
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveMedicalInstitutions(List<MedicalInstitution> medicalInstitutions, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(MedicalInstitution), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var medicalInstitution in medicalInstitutions)
            {
                var existUnit = await unitQuery.Where(u => u.Name == medicalInstitution.Name && u.LocationName == medicalInstitution.Area
                && u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = medicalInstitution.Area,
                        Name = medicalInstitution.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(medicalInstitution.RegulatoryLevel),
                        PrincipalName = medicalInstitution.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = medicalInstitution.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(medicalInstitution.Survey.Area),
                        UpdateTime = DateTime.Now,
                        Longitude = ParseGeoPoints(medicalInstitution.Longitude),
                        Latitude = ParseGeoPoints(medicalInstitution.Latitude),
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = medicalInstitution.Area;
                    existUnit.Name = medicalInstitution.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(medicalInstitution.RegulatoryLevel);
                    existUnit.PrincipalName = medicalInstitution.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = medicalInstitution.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(medicalInstitution.Survey.Area);
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(medicalInstitution.Longitude);
                    existUnit.Latitude = ParseGeoPoints(medicalInstitution.Latitude);
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveSchools(List<School> schools, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(School), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var school in schools)
            {
                var existUnit = await unitQuery.Where(u => u.Name == school.Name && u.LocationName == school.Area
                && u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid = uniacid,
                        GroupId = groupId,
                        CategoryId = existCategory.Id,
                        LocationName = school.Area,
                        Name = school.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(school.RegulatoryLevel),
                        PrincipalName = school.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = school.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(school.Survey.Area),
                        PeopleNum = ConvertPeopleCount(school.Survey.StudentCount),
                        Longitude = ParseGeoPoints(school.Longitude),
                        Latitude = ParseGeoPoints(school.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = school.Area;
                    existUnit.Name = school.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(school.RegulatoryLevel);
                    existUnit.PrincipalName = school.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = school.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(school.Survey.Area);
                    existUnit.PeopleNum = ConvertPeopleCount(school.Survey.StudentCount);
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(school.Longitude);
                    existUnit.Latitude = ParseGeoPoints(school.Latitude);
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SavePBCs(List<BPC> bPCs, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(BPC), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var bpc in bPCs)
            {
                var existUnit = await unitQuery.Where(u => u.Name == bpc.Name && u.LocationName == bpc.Area && u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = bpc.Area,
                        Name = bpc.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(bpc.RegulatoryLevel),
                        PrincipalName = bpc.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = bpc.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(bpc.Survey.Area),
                        Longitude = ParseGeoPoints(bpc.Longitude),
                        Latitude = ParseGeoPoints(bpc.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = bpc.Area;
                    existUnit.Name = bpc.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(bpc.RegulatoryLevel);
                    existUnit.PrincipalName = bpc.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = bpc.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(bpc.Survey.Area);
                    existUnit.Longitude = ParseGeoPoints(bpc.Longitude);
                    existUnit.Latitude = ParseGeoPoints(bpc.Latitude);
                    existUnit.UpdateTime = DateTime.Now;
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveStateOrgans(List<StateOrgan> stateOrgans, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(StateOrgan), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var stateOrgan in stateOrgans)
            {
                var existUnit = await unitQuery.Where(u => u.Name == stateOrgan.Name && u.LocationName == stateOrgan.Area
                && u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = stateOrgan.Area,
                        Name = stateOrgan.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(stateOrgan.RegulatoryLevel),
                        PrincipalName = stateOrgan.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = stateOrgan.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(stateOrgan.Survey.Area),
                        Longitude = ParseGeoPoints(stateOrgan.Longitude),
                        Latitude = ParseGeoPoints(stateOrgan.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = stateOrgan.Area;
                    existUnit.Name = stateOrgan.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(stateOrgan.RegulatoryLevel);
                    existUnit.PrincipalName = stateOrgan.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = stateOrgan.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(stateOrgan.Survey.Area);
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(stateOrgan.Longitude);
                    existUnit.Latitude = ParseGeoPoints(stateOrgan.Latitude);
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveWelfareInstitution(List<WelfareInstitution> welfareInstitutions, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(WelfareInstitution), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var welfareInstitution in welfareInstitutions)
            {
                var existUnit = await unitQuery.Where(u => u.Name == welfareInstitution.Name && u.LocationName == welfareInstitution.Area
                && u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid = uniacid,
                        GroupId = groupId,
                        CategoryId = existCategory.Id,
                        LocationName = welfareInstitution.Area,
                        Name = welfareInstitution.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(welfareInstitution.RegulatoryLevel),
                        PrincipalName = welfareInstitution.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = welfareInstitution.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(welfareInstitution.Survey.Area),
                        Longitude = ParseGeoPoints(welfareInstitution.Longitude),
                        Latitude = ParseGeoPoints(welfareInstitution.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = welfareInstitution.Area;
                    existUnit.Name = welfareInstitution.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(welfareInstitution.RegulatoryLevel);
                    existUnit.PrincipalName = welfareInstitution.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = welfareInstitution.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(welfareInstitution.Survey.Area);        
                    existUnit.UpdateTime = DateTime.Now;
                    existUnit.Longitude = ParseGeoPoints(welfareInstitution.Longitude);
                    existUnit.Latitude = ParseGeoPoints(welfareInstitution.Latitude);
                    _context.Units.Update(existUnit);
                }
                try
                {
                    await _context.SaveChangesAsync();
                }
                catch (Exception e)
                {

                }
            }
        }
        private async Task SaveOthers(List<Other> others, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(Other), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var other in others)
            {
                var existUnit = await unitQuery.Where(u => u.Name == other.Name && u.LocationName == other.Area &&
                u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid = uniacid,
                        GroupId = groupId,
                        CategoryId = existCategory.Id,
                        LocationName = other.Area,
                        Name = other.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(other.RegulatoryLevel),
                        PrincipalName = other.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = other.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(other.Survey.Area),
                        PeopleNum = ConvertPeopleCount(other.Survey.EmployeeCount),
                        Longitude = ParseGeoPoints(other.Longitude),
                        Latitude = ParseGeoPoints(other.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = other.Area;
                    existUnit.Name = other.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(other.RegulatoryLevel);
                    existUnit.PrincipalName = other.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = other.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(other.Survey.Area);
                    existUnit.PeopleNum = ConvertPeopleCount(other.Survey.EmployeeCount);
                    existUnit.Longitude = ParseGeoPoints(other.Longitude);
                    existUnit.Latitude = ParseGeoPoints(other.Latitude);
                    existUnit.UpdateTime = DateTime.Now;
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveHotels(List<Hotel> hotels, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(Hotel), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var hotel in hotels)
            {
                var existUnit = await unitQuery.Where(u => u.Name == hotel.Name && u.LocationName == hotel.Area && u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = hotel.Area,
                        Name = hotel.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(hotel.RegulatoryLevel),
                        PrincipalName = hotel.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = hotel.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(hotel.Survey.Area),
                        PeopleNum = ConvertPeopleCount(hotel.Survey.EmployeeCount),
                        Longitude = ParseGeoPoints(hotel.Longitude),
                        Latitude = ParseGeoPoints(hotel.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = hotel.Area;
                    existUnit.Name = hotel.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(hotel.RegulatoryLevel);
                    existUnit.PrincipalName = hotel.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = hotel.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(hotel.Survey.Area);
                    existUnit.PeopleNum = ConvertPeopleCount(hotel.Survey.EmployeeCount);
                    existUnit.Longitude = ParseGeoPoints(hotel.Longitude);
                    existUnit.Latitude = ParseGeoPoints(hotel.Latitude);
                    existUnit.UpdateTime = DateTime.Now;
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveUnderGrounds(List<UnderGround> underGrounds, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(UnderGround), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var underGround in underGrounds)
            {
                var existUnit = await unitQuery.Where(u => u.Name == underGround.Name && u.LocationName == underGround.Area &&
                u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = underGround.Area,
                        Name = underGround.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(underGround.RegulatoryLevel),
                        PrincipalName = underGround.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = underGround.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(underGround.Survey.Area),
                        Longitude = ParseGeoPoints(underGround.Longitude),
                        Latitude = ParseGeoPoints(underGround.Latitude),
                        UpdateTime =DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = underGround.Area;
                    existUnit.Name = underGround.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(underGround.RegulatoryLevel);
                    existUnit.PrincipalName = underGround.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = underGround.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(underGround.Survey.Area);
                    existUnit.Longitude = ParseGeoPoints(underGround.Longitude);
                    existUnit.Latitude = ParseGeoPoints(underGround.Latitude);
                    existUnit.UpdateTime = DateTime.Now;
                    _context.Units.Update(existUnit);
                }
                   await _context.SaveChangesAsync();
            }
        }
        private async Task SaveTallBuildings(List<TallBuilding> tallBuildings, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(TallBuilding), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var tallBuilding in tallBuildings)
            {
                var existUnit = await unitQuery.Where(u => u.Name == tallBuilding.Name && u.LocationName == tallBuilding.Area &&
                u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = tallBuilding.Area,
                        Name = tallBuilding.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(tallBuilding.RegulatoryLevel),
                        PrincipalName = tallBuilding.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = tallBuilding.PersonInChargeOfSafetyInfo?.Phone,
                        UseArea = ConvertAreaSizeToInt(tallBuilding.Survey.Area),
                        Longitude = ParseGeoPoints(tallBuilding.Longitude),
                        Latitude = ParseGeoPoints(tallBuilding.Latitude),
                        UpdateTime = DateTime.Now,
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = tallBuilding.Area;
                    existUnit.Name = tallBuilding.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(tallBuilding.RegulatoryLevel);
                    existUnit.PrincipalName = tallBuilding.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = tallBuilding.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.UseArea = ConvertAreaSizeToInt(tallBuilding.Survey.Area);
                    existUnit.Longitude = ParseGeoPoints(tallBuilding.Longitude);
                    existUnit.Latitude = ParseGeoPoints(tallBuilding.Latitude);
                    existUnit.UpdateTime = DateTime.Now;
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();
            }
        }
        private async Task SaveIndustrialEnterprises(List<IndustrialEnterprise> industrialEnterprises, int uniacid, int groupId)
        {
            var existCategory = await GetOrAddCategory(typeof(IndustrialEnterprise), groupId, uniacid);
            var unitQuery = _context.Units.AsQueryable();
            foreach (var industrialEnterprise in industrialEnterprises)
            {
                var existUnit = await unitQuery.Where(u => u.Name == industrialEnterprise.Name && u.LocationName == industrialEnterprise.Area &&
                u.Uniacid == uniacid && u.GroupId == groupId).FirstOrDefaultAsync();
                if (existUnit == null)
                {
                    var unit = new Unit
                    {
                        Uniacid=uniacid,
                        GroupId=groupId,
                        CategoryId = existCategory.Id,
                        LocationName = industrialEnterprise.Area,
                        Name = industrialEnterprise.Name,
                        SuperviseLevel = ConvertRegulatoryLevelToInt(industrialEnterprise.RegulatoryLevel),
                        PrincipalName = industrialEnterprise.PersonInChargeOfSafetyInfo?.Name,
                        PrincipalPhoneNum = industrialEnterprise.PersonInChargeOfSafetyInfo?.Phone,
                        CoverArea = ConvertAreaSizeToInt(industrialEnterprise.Survey.AreaCovered),
                        UseArea = ConvertAreaSizeToInt(industrialEnterprise.Survey.BuildUpArea),
                        Longitude= ParseGeoPoints(industrialEnterprise.Longitude),
                        Latitude= ParseGeoPoints(industrialEnterprise.Latitude),
                        UpdateTime = DateTime.Now
                    };
                    await _context.Units.AddAsync(unit);
                }
                else
                {
                    existUnit.CategoryId = existCategory.Id;
                    existUnit.LocationName = industrialEnterprise.Area;
                    existUnit.Name = industrialEnterprise.Name;
                    existUnit.SuperviseLevel = ConvertRegulatoryLevelToInt(industrialEnterprise.RegulatoryLevel);
                    existUnit.PrincipalName = industrialEnterprise.PersonInChargeOfSafetyInfo?.Name;
                    existUnit.PrincipalPhoneNum = industrialEnterprise.PersonInChargeOfSafetyInfo?.Phone;
                    existUnit.CoverArea = ConvertAreaSizeToInt(industrialEnterprise.Survey.AreaCovered);
                    existUnit.UseArea = ConvertAreaSizeToInt(industrialEnterprise.Survey.BuildUpArea);
                    existUnit.Longitude = ParseGeoPoints(industrialEnterprise.Longitude);
                    existUnit.Latitude = ParseGeoPoints(industrialEnterprise.Latitude);
                    existUnit.UpdateTime = DateTime.Now;
                    _context.Units.Update(existUnit);
                }
                await _context.SaveChangesAsync();

            }
        }

        private short ConvertPeopleCount(string peopleCount)
        {
            peopleCount = peopleCount.Replace("人", "").Trim();
            return Int16.Parse(peopleCount);
        }
        private Int16 ConvertRegulatoryLevelToInt(string regulatoryLevel)
        {
            var strValue = regulatoryLevel.Replace("级", "").Trim();
            try
            {
                return (Int16)_converter.ParseCnToInt(strValue);
            }
            catch
            {
                throw new Exception($"错误级值:{regulatoryLevel}");
            }

        }
        private async Task<UnitCategory> GetOrAddCategory(Type type,int groupId,int uniacid)
        {
            var unitCategoryQuery = _context.UnitCategories.AsQueryable();
            var sheetName = _excelParser.GetSheetName(type).Trim();
            var existCategory = await unitCategoryQuery.FirstOrDefaultAsync(c => c.Name == sheetName && c.GroupId==groupId && c.Uniacid==uniacid);
            if (existCategory == null)
            {
                var maxSort = await unitCategoryQuery.MaxAsync(c => (int?)c.Sort);
                maxSort = maxSort.HasValue ? maxSort.Value : 0;
                var unitCategory = new UnitCategory
                {
                    Sort = maxSort.Value + 1,
                    Name = sheetName,
                    GroupId=groupId,
                    Uniacid=uniacid,
                    UpdateTime = DateTime.Now
                };
                var entity = await _context.UnitCategories.AddAsync(unitCategory);
                await _context.SaveChangesAsync();
                existCategory = entity.Entity;
            }
            return existCategory;
        }

        private float ParseGeoPoints(string geo)
        {
            float.TryParse(geo, out var value);
            return value;
        }
        private int ConvertAreaSizeToInt(string area)
        {
            area = area.Trim();
            var pattern = "^\\d+\\.?\\d*";
            var match = Regex.Match(area, pattern);
            var value = 0.0;
            if (match.Success)
            {
                value = double.Parse(match.Value);
            }
            Converter.AreaUnits sourceUnit;
            if (area.Contains(Converter.AreaUnits.m2.ToString()) || area.Contains("㎡")||area.Contains("平方米"))
            {
                sourceUnit = Converter.AreaUnits.m2;
            }
            else if (area.Contains(Converter.AreaUnits.km2.ToString()) || area.Contains("k㎡")||area.Contains("平方公里"))
                {
                sourceUnit = Converter.AreaUnits.km2;
            }
            else if (area.Contains(Converter.AreaUnits.分.ToString()))
            {
                sourceUnit = Converter.AreaUnits.分;
            }
            else if (area.Contains(Converter.AreaUnits.亩.ToString()))
            {
                sourceUnit = Converter.AreaUnits.亩;
            }
            else if (area.Contains(Converter.AreaUnits.公亩.ToString()))
            {
                sourceUnit = Converter.AreaUnits.公亩;
            }
            else if (area.Contains(Converter.AreaUnits.公顷.ToString()))
            {
                sourceUnit = Converter.AreaUnits.公顷;
            }
            else if(string.IsNullOrEmpty(area.Replace(match.Value, "").Trim()) )
            {
                sourceUnit = Converter.AreaUnits.m2;
            }
            else
            {
                throw new Exception($"非法面积值{area}");
            }
            var result = _converter.ConvertArea(value, sourceUnit, Converter.AreaUnits.m2);
            return (int)result;
        }
    }
}