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();
}
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;
}
/// <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;
}