Esempio n. 1
0
        private string GetValue(GkhExcelCell[] data, string field)
        {
            var result = string.Empty;

            if (this.headersDict.ContainsKey(field))
            {
                var index = this.headersDict[field];
                if (data.Length > index && index > -1)
                {
                    result = data[index].Value;
                }
            }

            return result.Trim();
        }
Esempio n. 2
0
        private string InitHeader(GkhExcelCell[] data)
        {
            this.headersDict["ID_DOMA"] = -1;
            this.headersDict["MU"] = -1;
            this.headersDict["TYPE_CITY"] = -1;
            this.headersDict["CITY"] = -1;
            this.headersDict["TYPE_STREET"] = -1;
            this.headersDict["KLADR"] = -1;
            this.headersDict["STREET"] = -1;
            this.headersDict["HOUSE_NUM"] = -1;
            this.headersDict["LITER"] = -1;
            this.headersDict["KORPUS"] = -1;
            this.headersDict["BUILDING"] = -1;

            this.headersDict["ID_COM"] = -1;
            this.headersDict["TYPE_COM"] = -1;
            this.headersDict["NAME_COM"] = -1;
            this.headersDict["INN"] = -1;
            this.headersDict["KPP"] = -1;
            this.headersDict["OGRN"] = -1;
            this.headersDict["DATE_REG"] = -1;
            this.headersDict["TYPE_LAW_FORM"] = -1;
            this.headersDict["MR_COM"] = -1;
            this.headersDict["MU_COM"] = -1;
            this.headersDict["TYPE_CITY_COM"] = -1;
            this.headersDict["CITY_COM"] = -1;
            this.headersDict["TYPE_STREET_COM"] = -1;
            this.headersDict["STREET_COM"] = -1;
            this.headersDict["KLARD_COM"] = -1;
            this.headersDict["HOUSE_NUM_COM"] = -1;
            this.headersDict["LITER_COM"] = -1;
            this.headersDict["KORPUS_COM"] = -1;
            this.headersDict["BUILDING_COM"] = -1;

            this.headersDict["DATE_START_CON"] = -1;
            this.headersDict["TYPE_CON"] = -1;
            this.headersDict["NUM_DOG"] = -1;
            this.headersDict["DATE_DOG"] = -1;

            for (var index = 0; index < data.Length; ++index)
            {
                var header = data[index].Value.ToUpper();
                if (this.headersDict.ContainsKey(header))
                {
                    this.headersDict[header] = index;
                }
            }

            var requiredFields = new List<string> { "TYPE_COM" };

            var absentColumns = requiredFields.Where(x => !this.headersDict.ContainsKey(x) || this.headersDict[x] == -1).ToList();

            if (absentColumns.Any())
            {
                return "Отсутствуют обязательные столбцы: " + string.Join(", ", absentColumns);
            }

            return string.Empty;
        }
Esempio n. 3
0
        /// <summary>
        /// Обработка строки импорта
        /// </summary>
        private Record ProcessLine(GkhExcelCell[] data, int rowNumber)
        {
            var record = new Record { isValidRecord = false, RowNumber = rowNumber };

            if (data.Length <= 1)
            {
                return record;
            }

            var organizationType = this.GetValue(data, "TYPE_COM");
            if (string.IsNullOrEmpty(organizationType))
            {
                this.AddLog(record.RowNumber, "Не задан тип организации.", false);
                return record;
            }
            else
            {
                record.OrganizationType = this.GetOrganizationType(organizationType);

                if (record.OrganizationType == 0)
                {
                    this.AddLog(record.RowNumber, "Неизвестный тип организации: " + organizationType, false);
                    return record;
                }
            }

            record.ImportOrganizationId = this.GetValue(data, "ID_COM").ToInt();

            if (record.ImportOrganizationId > 0)
            {
                record.OrganizationId = this.GetOrganizationId(record.OrganizationType, record.ImportOrganizationId);
            }

            if (record.OrganizationId == 0)
            {
                record.Inn = this.GetValue(data, "INN");
                record.Kpp = this.GetValue(data, "KPP");

                var mixedKey = string.Format("{0}#{1}", record.Inn, record.Kpp);

                var contragentExists = contragentsDict.ContainsKey(mixedKey);

                if (contragentExists)
                {
                    var contragentsCount = contragentsDict[mixedKey].Count;
                    if (contragentsCount > 1)
                    {
                        this.AddLog(record.RowNumber, "Неоднозначная ситуация. Соответствующих данному ИНН/КПП контрагентов найдено: " + contragentsCount, false);
                        return record;
                    }
                }
                else
                {
                    // Контрагента не нашли, собираем инфу для создания контрагента

                    if (!(Utils.Utils.VerifyInn(record.Inn, false) || Utils.Utils.VerifyInn(record.Inn, true)))
                    {
                        this.AddLog(record.RowNumber, "Некорректный ИНН: " + record.Inn, false);
                        return record;
                    }

                    record.Ogrn = this.GetValue(data, "OGRN");

                    if (!(Utils.Utils.VerifyOgrn(record.Ogrn, false) || Utils.Utils.VerifyOgrn(record.Ogrn, true)))
                    {
                        this.AddLog(record.RowNumber, "Некорректный ОГРН: " + record.Ogrn, false);
                        return record;
                    }

                    record.OrganizationName = this.GetValue(data, "NAME_COM");

                    if (string.IsNullOrEmpty(record.OrganizationName))
                    {
                        this.AddLog(record.RowNumber, "Не задано Наименование юр лица.", false);
                        return record;
                    }

                    record.OrgStreetKladrCode = this.GetValue(data, "KLADR_COM");
                    record.OrgMunicipalityName = this.GetValue(data, "MU_COM");

                    if (string.IsNullOrEmpty(record.OrgMunicipalityName))
                    {
                        record.OrgMunicipalityName = this.GetValue(data, "MR_COM");

                        if (string.IsNullOrEmpty(record.OrgMunicipalityName))
                        {
                            this.AddLog(record.RowNumber, "Не задан мун.район и мун.образование организации.", false);
                            return record;
                        }
                    }

                    if (!this.municipalitiesDict.ContainsKey(record.OrgMunicipalityName.ToLower()))
                    {
                        this.AddLog(record.RowNumber, "Указанный мун.район/мун.образование не найден в системе.", false);
                        return record;
                    }

                    var municipalityProxy = this.municipalitiesDict[record.OrgMunicipalityName.ToLower()];

                    record.ContragentMunicipalityId = municipalityProxy.Key;
                    record.ContragentMunicipalityFiasId = municipalityProxy.Value;

                    record.OrgLocalityName = Simplified(this.GetValue(data, "CITY_COM") + " " + this.GetValue(data, "TYPE_CITY_COM"));
                    record.OrgStreetName = Simplified(this.GetValue(data, "STREET_COM") + " " + this.GetValue(data, "TYPE_STREET_COM"));
                    record.OrgHouse = this.GetValue(data, "HOUSE_NUM_COM");

                    if (string.IsNullOrWhiteSpace(record.OrgHouse))
                    {
                        this.AddLog(record.RowNumber, "Не задан номер дома организации.", false);
                        return record;
                    }

                    record.OrgLetter = this.GetValue(data, "LITER_COM");
                    record.OrgHousing = this.GetValue(data, "KORPUS_COM");
                    record.OrgBuilding = this.GetValue(data, "BUILDING_COM");

                    var organizationForm = this.GetValue(data, "TYPE_LAW_FORM");
                    if (string.IsNullOrEmpty(organizationForm))
                    {
                        this.AddLog(record.RowNumber, "Не задана Организационно-правовая форма.", false);
                        return record;
                    }
                    else
                    {
                        if (!this.organizationFormDict.ContainsKey(organizationForm.ToLower()))
                        {
                            this.AddLog(record.RowNumber, "Неизвестная Организационно-правовая форма: " + organizationForm, false);
                            return record;
                        }

                        record.OrganizationForm = new OrganizationForm { Id = this.organizationFormDict[organizationForm.ToLower()] };
                    }

                    var dateRegistration = this.GetValue(data, "DATE_REG");

                    if (string.IsNullOrWhiteSpace(dateRegistration))
                    {
                        this.AddLog(record.RowNumber, "Не задана Дата регистрации организации.", false);
                        return record;
                    }
                    else
                    {
                        DateTime date;

                        if (DateTime.TryParseExact(dateRegistration, "dd.MM.yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out date))
                        {
                            record.DateRegistration = date;
                        }
                        else
                        {
                            this.AddLog(record.RowNumber, "Некорректная дата в поле 'DATE_REG': " + dateRegistration + "'. Дата ожидается в формате 'дд.мм.гггг'", false);
                            return record;
                        }
                    }
                }

                if (record.OrganizationType == OrgType.ManagingOrganization)
                {
                    var managementType = this.GetValue(data, "TYPE_CON");
                    if (string.IsNullOrEmpty(managementType))
                    {
                        this.AddLog(record.RowNumber, "Не задан тип управления.", false);
                        return record;
                    }
                    else
                    {
                        switch (managementType.ToLower())
                        {
                            case "ук":
                                record.TypeManagement = TypeManagementManOrg.UK;
                                break;

                            case "тсж":
                                record.TypeManagement = TypeManagementManOrg.TSJ;
                                break;

                            case "жск":
                                record.TypeManagement = TypeManagementManOrg.JSK;
                                break;

                            default:
                                this.AddLog(
                                    record.RowNumber, "Неизвестный тип управления: " + managementType, false);
                                return record;
                        }
                    }
                }
            }

            var accountCreateDate = this.GetValue(data, "DATE_START_CON");

            if (string.IsNullOrWhiteSpace(accountCreateDate))
            {
                this.AddLog(record.RowNumber, "Не задана Дата начала управления/обслуживания домом (а).", false);
                return record;
            }
            else
            {
                DateTime date;

                if (DateTime.TryParseExact(accountCreateDate, "dd.MM.yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out date))
                {
                    record.ContractStartDate = date;
                }
                else
                {
                    this.AddLog(record.RowNumber, "Некорректная дата в поле 'DATE_START_CON': " + accountCreateDate + "'. Дата ожидается в формате 'дд.мм.гггг'", false);
                    return record;
                }
            }

            record.DocumentNumber = this.GetValue(data, "NUM_DOG");

            var documentDate = this.GetValue(data, "DATE_DOG");

            if (string.IsNullOrWhiteSpace(documentDate))
            {
                this.AddLog(record.RowNumber, "Не задана Дата договора управления/обслуживания.", false);
                return record;
            }
            else
            {
                DateTime date;

                if (DateTime.TryParseExact(documentDate, "dd.MM.yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out date))
                {
                    record.DocumentDate = date;
                }
                else
                {
                    this.AddLog(record.RowNumber, "Некорректная дата в поле 'DATE_DOG': " + accountCreateDate + "'. Дата ожидается в формате 'дд.мм.гггг'", false);
                    return record;
                }
            }

            //// 1. Если указан идентификатор дома, пытаемся найти его среди существующих
            record.ImportRealtyObjectId = this.GetValue(data, "ID_DOMA").ToInt();
            if (record.ImportRealtyObjectId > 0 && this.existingRealtyObjectIdList.Contains(record.ImportRealtyObjectId))
            {
                record.RealtyObjectId = record.ImportRealtyObjectId;
            }

            //// 2. Если дом еще не найден, ищем по связке Код КЛАДР + номер дома + литер + корпус + строение
            if (record.RealtyObjectId == 0)
            {
                record.House = this.GetValue(data, "HOUSE_NUM");

                if (string.IsNullOrWhiteSpace(record.House))
                {
                    this.AddLog(record.RowNumber, "Не задан номер дома.", false);
                    return record;
                }

                record.Letter = this.GetValue(data, "LITER");
                record.Housing = this.GetValue(data, "HOUSING");
                record.Building = this.GetValue(data, "BUILDING");
                record.StreetKladrCode = this.GetValue(data, "KLADR");

                if (!string.IsNullOrWhiteSpace(record.StreetKladrCode))
                {
                    var realtyObjectsOnStreet = realtyObjectsByKladrCodeDict[record.StreetKladrCode].Where(x => x.House == record.House).ToList();

                    realtyObjectsOnStreet = string.IsNullOrWhiteSpace(record.Letter)
                        ? realtyObjectsOnStreet.Where(x => string.IsNullOrWhiteSpace(x.Letter)).ToList()
                        : realtyObjectsOnStreet.Where(x => x.Letter == record.Letter).ToList();

                    realtyObjectsOnStreet = string.IsNullOrWhiteSpace(record.Housing)
                        ? realtyObjectsOnStreet.Where(x => string.IsNullOrWhiteSpace(x.Housing)).ToList()
                        : realtyObjectsOnStreet.Where(x => x.Housing == record.Housing).ToList();

                    realtyObjectsOnStreet = string.IsNullOrWhiteSpace(record.Building)
                        ? realtyObjectsOnStreet.Where(x => string.IsNullOrWhiteSpace(x.Building)).ToList()
                        : realtyObjectsOnStreet.Where(x => x.Building == record.Building).ToList();

                    if (realtyObjectsOnStreet.Count > 1)
                    {
                        this.AddLog(record.RowNumber, "Неоднозначный дом. Соответствующих данному набору адресных параметров домов найдено: " + realtyObjectsOnStreet.Count, false);
                        return record;
                    }

                    if (realtyObjectsOnStreet.Count == 1)
                    {
                        record.RealtyObjectId = realtyObjectsOnStreet.First().roId;
                    }
                }
            }

            //// 3. Если дом все еще не найден, ищем по текстовым значениям: МО + Нас.пункт + улица + номер дома + литер + корпус + строение
            if (record.RealtyObjectId == 0)
            {
                record.MunicipalityName = this.GetValue(data, "MU");
                if (string.IsNullOrWhiteSpace(record.MunicipalityName))
                {
                    this.AddLog(record.RowNumber, "Не задано муниципальное образование.", false);
                    return record;
                }

                record.LocalityName = Simplified(this.GetValue(data, "CITY") + " " + this.GetValue(data, "TYPE_CITY"));

                if (string.IsNullOrWhiteSpace(record.LocalityName))
                {
                    this.AddLog(record.RowNumber, "Не задан населенный пункт.", false);
                    return record;
                }

                record.StreetName = Simplified(this.GetValue(data, "STREET") + " " + this.GetValue(data, "TYPE_STREET"));

                if (string.IsNullOrWhiteSpace(record.StreetName))
                {
                    this.AddLog(record.RowNumber, "Не задана улица.", false);
                    return record;
                }

                if (!this.realtyObjectsByAddressDict.ContainsKey(record.MunicipalityName.ToLower()))
                {
                    this.AddLog(record.RowNumber, "Не найдены дома в муниципальном образовании: " + record.MunicipalityName, false);
                    return record;
                }

                var municipalityRealtyObjectsDict = this.realtyObjectsByAddressDict[record.MunicipalityName.ToLower()];

                if (!municipalityRealtyObjectsDict.ContainsKey(record.LocalityName.ToLower()))
                {
                    this.AddLog(record.RowNumber, "В указанном МО не найдены дома в населенном пунтке: " + record.LocalityName, false);
                    return record;
                }

                var localityRealtyObjectsDict = municipalityRealtyObjectsDict[record.LocalityName.ToLower()];

                if (!localityRealtyObjectsDict.ContainsKey(record.StreetName.ToLower()))
                {
                    this.AddLog(record.RowNumber, "В указанном населенном пунтк не найдены дома на улице: " + record.StreetName, false);
                    return record;
                }

                var realtyObjectsOnStreet = localityRealtyObjectsDict[record.StreetName.ToLower()].Where(x => x.House == record.House).ToList();

                realtyObjectsOnStreet = string.IsNullOrWhiteSpace(record.Letter)
                    ? realtyObjectsOnStreet.Where(x => string.IsNullOrWhiteSpace(x.Letter)).ToList()
                    : realtyObjectsOnStreet.Where(x => x.Letter == record.Letter).ToList();

                realtyObjectsOnStreet = string.IsNullOrWhiteSpace(record.Housing)
                    ? realtyObjectsOnStreet.Where(x => string.IsNullOrWhiteSpace(x.Housing)).ToList()
                    : realtyObjectsOnStreet.Where(x => x.Housing == record.Housing).ToList();

                realtyObjectsOnStreet = string.IsNullOrWhiteSpace(record.Building)
                    ? realtyObjectsOnStreet.Where(x => string.IsNullOrWhiteSpace(x.Building)).ToList()
                    : realtyObjectsOnStreet.Where(x => x.Building == record.Building).ToList();

                if (realtyObjectsOnStreet.Count == 0)
                {
                    this.AddLog(record.RowNumber, "Дом не найден в системе", false);
                    return record;
                }

                if (realtyObjectsOnStreet.Count > 1)
                {
                    this.AddLog(record.RowNumber, "Неоднозначный дом. Соответствующих данному набору адресных параметров домов найдено: " + realtyObjectsOnStreet.Count, false);
                    return record;
                }

                record.RealtyObjectId = realtyObjectsOnStreet.First().roId;
            }

            record.isValidRecord = true;

            return record;
        }