SpecialProcessing(AddKLADRInfo)
GO
SpecialProcessing(SetDefaultLocality) -- она должна выставить @@@DefaultLocalityID
GO
--
alter table Addresses alter column Note nvarchar(200)
--
update Характеристики_полей set Используемое_поле = 'Name' where Код_характеристики = 471
GO
alter table Организация alter column Наименование nvarchar(200) not null
GO
-- Вынесем адрес в LegalEntity.
alter table LegalEntities add AddressID int
alter table LegalEntities add Address nvarchar(200)
GO
alter table LegalEntities add foreign key (AddressID) references Addresses
GO
update LegalEntities set LegalEntities.AddressID = Individuals.AddressID, LegalEntities.Address = Individuals.Address
  FROM Individuals WHERE LegalEntities.LegalEntityID = Individuals.IndividualID
update LegalEntities set LegalEntities.AddressID = Организация.Код_фактического_адреса, LegalEntities.Address = Организация.Фактический_адрес
  FROM Организация WHERE LegalEntities.LegalEntityID = Организация.Код_организации
GO
declare @table_name nvarchar(50)
declare @strSQL nvarchar(1024)

set @table_name=N'Individuals'
set @strSQL=''
select @strSQL = @strSQL + ' ALTER TABLE ' + @table_name + ' DROP CONSTRAINT '+ object_name(FK_Tab.id) 
from sysobjects FK_Tab 
where (xtype = 'F') AND object_id(@table_name)=parent_obj 

if @strSQL<>''  exec (@strSQL)

set @table_name=N'Организация'
set @strSQL=''
select @strSQL = @strSQL + ' ALTER TABLE ' + @table_name + ' DROP CONSTRAINT '+ object_name(FK_Tab.id) 
from sysobjects FK_Tab 
where (xtype = 'F') AND object_id(@table_name)=parent_obj 

if @strSQL<>''  exec (@strSQL)
GO
alter table Individuals drop column AddressID
alter table Организация drop column Код_фактического_адреса
alter table Организация drop column Фактический_адрес
alter table Individuals drop column Address
GO
--
alter table Delineations add SigningCompanyDate datetime
alter table Delineations add SigningApplicantDate datetime
alter table Delineations add SigningThirdDate datetime
GO
update Delineations set Delineations.SigningCompanyDate = TCActs.DelineationSigningCompanyDate 
  FROM TCActs 
  inner join TCApplications on TCActs.ActID = TCApplications.TCApplicationID
  inner join ConsumingObjects on ConsumingObjects.ID = TCApplications.ConsumingObjectID
  inner join Delineations on ConsumingObjects.DelineationID = Delineations.DelineationID

update Delineations set Delineations.SigningApplicantDate = TCActs.DelineationSigningApplicantDate 
  FROM TCActs 
  inner join TCApplications on TCActs.ActID = TCApplications.TCApplicationID
  inner join ConsumingObjects on ConsumingObjects.ID = TCApplications.ConsumingObjectID
  inner join Delineations on ConsumingObjects.DelineationID = Delineations.DelineationID
GO
alter table TCApplications add ShortSpec bit
alter table TCApplications add NoContract bit
alter table TCApplications add NoActual bit
alter table TCActs drop column DelineationDate
alter table TCActs drop column DelineationSigningCompanyDate
alter table TCActs drop column DelineationSigningApplicantDate
alter table TCActs drop column DelineationSigningThirdDate
GO
alter table TCContractOutgoings add CompanyPercent int
GO
update TCContractOutgoings Set CompanyPercent = 100
GO
  -- удаляем связи у объектов, работающих только с полным адресом
declare @table_name nvarchar(50)
declare @strSQL nvarchar(1024)

set @table_name=N'ConsumingObjects'
set @strSQL=''
select @strSQL = @strSQL + ' ALTER TABLE ' + @table_name + ' DROP CONSTRAINT '+ object_name(FK_Tab.id) 
from sysobjects FK_Tab 
where (xtype = 'F') AND object_id(@table_name)=parent_obj 

if @strSQL<>''  exec (@strSQL)

set @table_name=N'LegalEntities'
set @strSQL=''
select @strSQL = @strSQL + ' ALTER TABLE ' + @table_name + ' DROP CONSTRAINT '+ object_name(FK_Tab.id) 
from sysobjects FK_Tab 
where (xtype = 'F') AND object_id(@table_name)=parent_obj 

if @strSQL<>''  exec (@strSQL)
GO
-- изменим модель адреса - разобъем на части
EXEC sp_rename 'Addresses', 'AddressBaseParts'
GO 
CREATE TABLE Addresses
(
	AddressID	int NOT NULL,
	ExtraPartID	int NOT NULL, 
	PRIMARY KEY(AddressID)		
)
GO
CREATE TABLE AddressExtraParts
(
	ExtraPartID	int identity(1,1),
	Flat		nvarchar(50),
	Entrance        nvarchar(50),
	PRIMARY KEY(ExtraPartID)		
)
GO
alter table Addresses add foreign key (AddressID) references AddressBaseParts on delete cascade
alter table Addresses add foreign key (ExtraPartID) references AddressExtraParts
GO
set identity_insert AddressExtraParts on
insert into AddressExtraParts(ExtraPartID,Flat,Entrance)
  select AddressID,Flat,Entrance from AddressBaseParts
set identity_insert AddressExtraParts off
GO
insert into Addresses(AddressID,ExtraPartID)
  select AddressID,AddressID from AddressBaseParts
GO
alter table AddressBaseParts drop column Flat
alter table AddressBaseParts drop column Entrance
GO
delete from Addresses where AddressID in (Select AddressId from Группа_ячейки)
delete from Addresses where AddressID in (Select AddressId from Линия)
delete from Addresses where AddressID in (Select AddressId from Подстанция)
delete from Addresses where AddressID in (Select AddressId from Склад)
delete from AddressExtraParts where ExtraPartID in (Select AddressId from Группа_ячейки)
delete from AddressExtraParts where ExtraPartID in (Select AddressId from Линия)
delete from AddressExtraParts where ExtraPartID in (Select AddressId from Подстанция)
delete from AddressExtraParts where ExtraPartID in (Select AddressId from Склад)
GO
--удалим все строковые адреса.. сначала добавив для непустых адреса с локацией по-дефолту и строковым адресом в примечании
alter table AddressBaseParts add ObjID int
GO
-- Группа_ячейки.Адрес
insert into AddressBaseParts (LocalityID, ObjID, Note) 
  select @@@DefaultLocalityID, Код_группы, Адрес
  from Группа_ячейки
  where AddressID is null AND Адрес is not NULL
update Группа_ячейки set Группа_ячейки.AddressID = AddressBaseParts.AddressID
   from AddressBaseParts where ObjID = Код_группы
update AddressBaseParts set ObjID = NULL
alter table Группа_ячейки drop column Адрес
GO
-- Линия.Адрес
insert into AddressBaseParts (LocalityID, ObjID, Note) 
  select @@@DefaultLocalityID, Код_линии, Адрес
  from Линия
  where AddressID is null AND Адрес is not NULL
update Линия set Линия.AddressID = AddressBaseParts.AddressID 
  from AddressBaseParts where ObjID = Код_линии
update AddressBaseParts set ObjID = NULL
alter table Линия drop column Адрес
GO
-- Подстанция.Адрес
insert into AddressBaseParts (LocalityID, ObjID, Note) 
  select @@@DefaultLocalityID, Код_подстанции, Адрес
  from Подстанция
  where AddressID is null AND Адрес is not NULL
update Подстанция set Подстанция.AddressID = AddressBaseParts.AddressID
  from AddressBaseParts where ObjID = Код_подстанции
update AddressBaseParts set ObjID = NULL
alter table Подстанция drop column Адрес
GO
-- Склад.Адрес
insert into AddressBaseParts (LocalityID, ObjID, Note) 
  select @@@DefaultLocalityID, Код_склада, Адрес
  from Склад
  where AddressID is null AND Адрес is not NULL
update Склад set Склад.AddressID = AddressBaseParts.AddressID
  from AddressBaseParts where ObjID = Код_склада
update AddressBaseParts set ObjID = NULL
alter table Склад drop column Адрес
GO
alter table AddressExtraParts add ObjID int
alter table Addresses add ObjID int
GO
-- Individuals.RegistrationAddress
insert into AddressBaseParts (LocalityID, ObjID, Note) 
  select @@@DefaultLocalityID, IndividualID, RegistrationAddress
  from Individuals
  where RegistrationAddressID is null AND RegistrationAddress is not NULL
insert into AddressExtraParts (ObjID)  
  select ObjID from AddressBaseParts
  where ObjID is not null
insert into Addresses(AddressID, ExtraPartID, ObjID)
  select AddressID, ExtraPartID, BP.ObjID 
  from AddressBaseParts BP
  inner join AddressExtraParts EP on EP.ObjID = BP.ObjID
update Individuals set RegistrationAddressID = AddressID
  from Addresses where ObjID = IndividualID
alter table Individuals drop column RegistrationAddress
update AddressBaseParts set ObjID = NULL
update AddressExtraParts set ObjID = NULL
update Addresses set ObjID = NULL
GO
-- Организация.Юридический_адрес
insert into AddressBaseParts (LocalityID, ObjID, Note) 
  select @@@DefaultLocalityID, Код_организации, Юридический_адрес
  from Организация
  where Код_юридического_адреса is null AND Юридический_адрес is not NULL
insert into AddressExtraParts (ObjID)  
  select ObjID from AddressBaseParts
  where ObjID is not null
insert into Addresses(AddressID, ExtraPartID, ObjID)
  select AddressID, ExtraPartID, BP.ObjID 
  from AddressBaseParts BP
  inner join AddressExtraParts EP on EP.ObjID = BP.ObjID
update Организация set Код_юридического_адреса = AddressID
  from Addresses where ObjID = Код_организации
alter table Организация drop column Юридический_адрес
update AddressBaseParts set ObjID = NULL
update AddressExtraParts set ObjID = NULL
update Addresses set ObjID = NULL
GO
-- LegalEntities.Address
insert into AddressBaseParts (LocalityID, ObjID, Note) 
  select @@@DefaultLocalityID, LegalEntityID, Address
  from LegalEntities
  where AddressID is null AND Address is not NULL
insert into AddressExtraParts (ObjID)  
  select ObjID from AddressBaseParts
  where ObjID is not null
insert into Addresses(AddressID, ExtraPartID, ObjID)
  select AddressID, ExtraPartID, BP.ObjID 
  from AddressBaseParts BP
  inner join AddressExtraParts EP on EP.ObjID = BP.ObjID
update LegalEntities set LegalEntities.AddressID = Addresses.AddressID
  from Addresses where ObjID = LegalEntityID 
alter table LegalEntities drop column Address
update AddressBaseParts set ObjID = NULL
update AddressExtraParts set ObjID = NULL
update Addresses set ObjID = NULL
GO
-- ConsumingObjects.Address
insert into AddressBaseParts (LocalityID, ObjID, Note) 
  select @@@DefaultLocalityID, ID, Address
  from ConsumingObjects
  where AddressID is null
insert into AddressExtraParts (ObjID)  
  select ObjID from AddressBaseParts
  where ObjID is not null
insert into Addresses(AddressID, ExtraPartID, ObjID)
  select AddressID, ExtraPartID, BP.ObjID 
  from AddressBaseParts BP
  inner join AddressExtraParts EP on EP.ObjID = BP.ObjID
update ConsumingObjects set ConsumingObjects.AddressID = Addresses.AddressID
  from Addresses where ObjID = ID
alter table ConsumingObjects drop column Address
alter table ConsumingObjects alter column AddressID int not null
GO
alter table AddressBaseParts drop column ObjID
alter table AddressExtraParts drop column ObjID
alter table Addresses drop column ObjID
GO
-- Убрала каскадность удаления Заявок, связанных с Объектом электроснабжения
declare @table_name nvarchar(50)
declare @strSQL nvarchar(1024)

set @table_name=N'TCApplications'
set @strSQL=''
select @strSQL = @strSQL + ' ALTER TABLE ' + @table_name + ' DROP CONSTRAINT '+ object_name(FK_Tab.id) 
from sysobjects FK_Tab 
where (xtype = 'F') AND object_id(@table_name)=parent_obj 

if @strSQL<>''  exec (@strSQL)

ALTER TABLE TCApplications
	ADD FOREIGN KEY (TCApplicationCauseID)
	REFERENCES TCApplicationCauses  (TCApplicationCauseID)

ALTER TABLE TCApplications
	ADD FOREIGN KEY (ConsumingObjectID)
	REFERENCES ConsumingObjects(ID)

ALTER TABLE TCApplications
	ADD FOREIGN KEY (LegalEntityID)
	REFERENCES LegalEntities(LegalEntityID)

ALTER TABLE TCApplications
	ADD FOREIGN KEY (ParentApplicationID)
	REFERENCES TCApplications
-- Добавим в Характеристики_полей флаг ReadOnly для поля, чтобы заблокировать редактирование
alter table Характеристики_полей add ReadOnly bit 
GO
-- Улицы
SET IDENTITY_INSERT Характеристики_полей ON

INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения, ReadOnly) 
	VALUES (472,'Streets','LocalityID','Name','Localities','Адм.-терр. единица', NULL, NULL, NULL)
INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения, ReadOnly) 
	VALUES (473,'Streets','StreetCode',NULL,NULL,'Код в КЛАДР', NULL, NULL, 1)
INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения, ReadOnly) 
	VALUES (474,'Streets','Name',NULL,NULL,'Наименование', NULL, NULL, NULL)
INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения, ReadOnly) 
	VALUES (475,'Streets','TypeID','Name','StreetTypes','Тип', NULL, NULL, NULL)
INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения, ReadOnly) 
	VALUES (476,'Streets','Synonyms',NULL,NULL,'Синонимы', NULL, NULL, NULL)

-- Адреса
INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения, ReadOnly) 
	VALUES (477,'Организация','Код_юридического_адреса',NULL,'Addresses','Юридический адрес', NULL, NULL, NULL)

INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения, ReadOnly) 
	VALUES (478,'Организация','AddressID',NULL,'Addresses','Фактический адрес', NULL, NULL, NULL)

INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения, ReadOnly) 
	VALUES (479,'Individuals','RegistrationAddressID',NULL,'Addresses','Адрес регистрации', NULL, NULL, NULL)

INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения, ReadOnly) 
	VALUES (480,'Individuals','AddressID',NULL,'Addresses','Адрес', NULL, NULL, NULL)

SET IDENTITY_INSERT Характеристики_полей OFF
GO
-- форенкеи
alter table LegalEntities add foreign key (AddressID) references Addresses
ALTER TABLE Individuals WITH NOCHECK
        ADD FOREIGN KEY (IndividualID)
                              REFERENCES LegalEntities(LegalEntityID) ON DELETE CASCADE

alter table Individuals add foreign key (RegistrationAddressID) references Addresses
ALTER TABLE Организация WITH NOCHECK
        ADD FOREIGN KEY (Код_организации)
                              REFERENCES LegalEntities(LegalEntityID) ON DELETE CASCADE

ALTER TABLE Организация
        ADD FOREIGN KEY (Код_организационно_правовой_формы)
                              REFERENCES Справочник_организационно_правовых_форм

alter table Организация add foreign key (Код_юридического_адреса) references Addresses
alter table ConsumingObjects 
	add foreign key (ParentID)
	references ConsumingObjects(ID)

alter table ConsumingObjects 
	add foreign key (TypeID)
	references ConsumingObjectTypes(TypeID)

alter table ConsumingObjects add foreign key (AddressID) references Addresses

alter table ConsumingObjects 
	add foreign key (DelineationID)
	references Delineations(DelineationID)

GO
-- добавим процедуры и триггеры
---- ConsumingObjects - AFTER UPDATE,DELETE ----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OnConsumingObjectsDelete]') and xtype = N'TR')
drop trigger [dbo].[OnConsumingObjectsDelete]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterConsumingObjectsUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterConsumingObjectsUpdate]
GO
CREATE TRIGGER AfterConsumingObjectsUpdate ON [dbo].[ConsumingObjects] 
AFTER UPDATE, DELETE
AS
-- потом переписать на удаление из AddressExtraParts
  if (SELECT Count(AddressID) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select AddressID from DELETED)
      AND ((select count(AddressID) from inserted) = 0 OR   
      AddressID not in (select AddressID from INSERTED))  
GO
---- Individuals - AFTER UPDATE,DELETE ----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OnIndividualsDelete]') and xtype = N'TR')
drop trigger [dbo].[OnIndividualsDelete]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterIndividualsUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterIndividualsUpdate]
GO
CREATE TRIGGER AfterIndividualsUpdate ON [dbo].[Individuals] 
AFTER UPDATE,DELETE
AS
  if (SELECT Count(RegistrationAddressID) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select RegistrationAddressID from DELETED)
      AND ((select count(RegistrationAddressID) from inserted) = 0 OR   
      AddressID not in (select RegistrationAddressID from INSERTED))  
GO
---- LegalEntities - AFTER UPDATE,DELETE ----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OnLegalEntitiesDelete]') and xtype = N'TR')
drop trigger [dbo].[OnLegalEntitiesDelete]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterLegalEntitiesUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterLegalEntitiesUpdate]
GO
CREATE TRIGGER AfterLegalEntitiesUpdate ON [dbo].[LegalEntities] 
AFTER UPDATE,DELETE
AS
  if (SELECT Count(AddressID) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select AddressID from DELETED)
      AND ((select count(AddressID) from inserted) = 0 OR   
      AddressID not in (select AddressID from INSERTED))  
GO
---- Группа_ячейки - AFTER UPDATE,DELETE ----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OnГруппа_ячейкиDelete]') and xtype = N'TR')
drop trigger [dbo].[OnГруппа_ячейкиDelete]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterГруппа_ячейкиUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterГруппа_ячейкиUpdate]
GO
CREATE TRIGGER AfterГруппа_ячейкиUpdate ON [dbo].[Группа_ячейки] 
AFTER UPDATE,DELETE
AS
  if (SELECT Count(AddressID) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select AddressID from DELETED)
      AND ((select count(AddressID) from inserted) = 0 OR   
      AddressID not in (select AddressID from INSERTED))  
GO
---- Линия - AFTER UPDATE,DELETE ----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OnЛинияDelete]') and xtype = N'TR')
drop trigger [dbo].[OnЛинияDelete]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterЛинияUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterЛинияUpdate]
GO
CREATE TRIGGER AfterЛинияUpdate ON [dbo].[Линия] 
AFTER UPDATE,DELETE
AS
  if (SELECT Count(AddressID) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select AddressID from DELETED)
      AND ((select count(AddressID) from inserted) = 0 OR   
      AddressID not in (select AddressID from INSERTED))  
GO
---- Организация - AFTER UPDATE,DELETE ----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OnОрганизацияDelete]') and xtype = N'TR')
drop trigger [dbo].[OnОрганизацияDelete]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterОрганизацияUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterОрганизацияUpdate]
GO
CREATE TRIGGER AfterОрганизацияUpdate ON [dbo].[Организация] 
AFTER UPDATE,DELETE
AS
  if (SELECT Count(Код_юридического_адреса) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select Код_юридического_адреса from DELETED)
      AND ((select count(Код_юридического_адреса) from inserted) = 0 OR   
      AddressID not in (select Код_юридического_адреса from INSERTED))  
GO
---- Подстанция - AFTER UPDATE,DELETE ----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OnПодстанцияDelete]') and xtype = N'TR')
drop trigger [dbo].[OnПодстанцияDelete]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterПодстанцияUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterПодстанцияUpdate]
GO
CREATE TRIGGER AfterПодстанцияUpdate ON [dbo].[Подстанция] 
AFTER UPDATE,DELETE
AS
  if (SELECT Count(AddressID) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select AddressID from DELETED)
      AND ((select count(AddressID) from inserted) = 0 OR   
      AddressID not in (select AddressID from INSERTED))  
GO
---- Склад - AFTER UPDATE,DELETE ----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OnСкладDelete]') and xtype = N'TR')
drop trigger [dbo].[OnСкладDelete]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterСкладUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterСкладUpdate]
GO
CREATE TRIGGER AfterСкладUpdate ON [dbo].[Склад] 
AFTER UPDATE,DELETE
AS
  if (SELECT Count(AddressID) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select AddressID from DELETED)
      AND ((select count(AddressID) from inserted) = 0 OR   
      AddressID not in (select AddressID from INSERTED))  
GO
-- получаем полное строковое представление адреса по коду
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAddressFullView]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetAddressFullView]
GO
create function GetAddressFullView(@AddressID integer)
returns nvarchar(4000)
as
begin
  declare @res nvarchar(4000)
  set @res = ''
  declare @FStreetID int
  declare @FLocality nvarchar(50)
  declare @FStreet nvarchar(50)
  declare @FEstate nvarchar(50)
  declare @FEstateType nvarchar(50)
  declare @FFraction nvarchar(50)
  declare @FHousing nvarchar(50)
  declare @FBuilding nvarchar(50)
  declare @FFlat nvarchar(50)
  declare @FEntrance nvarchar(50) 
  declare @FNote nvarchar(100) 
  select 
    @FStreetID = BP.StreetID,
    @FLocality = LT.Abbr + '. ' + L.Name,
    @FStreet = ST.Abbr + '. ' + S.Name,
    @FEstate = BP.Estate,
    @FEstateType = case BP.EstateType 
      when 0 then 'д. '
      when 1 then 'вл. '
      when 2 then 'уч. '
    end,
    @FFraction = BP.Fraction,
    @FHousing = BP.Housing,
    @FBuilding = BP.Building,
    @FNote = BP.Note, 
    @FFlat = EP.Flat,
    @FEntrance = EP.Entrance
    from Addresses A
    inner join AddressBaseParts BP on BP.AddressID = A.AddressID
    inner join AddressExtraParts EP on EP.ExtraPartID = A.ExtraPartID
    inner join Localities L 
      inner join LocalityTypes LT on LT.TypeID = L.TypeID
      on L.LocalityID = BP.LocalityID    
    left join Streets S 
      inner join StreetTypes ST on ST.TypeID = S.TypeID
      on S.StreetID = BP.StreetID
    where A.AddressID = @AddressID

  if @FEstate <> ''
  begin
    set @res = @FEstateType + @FEstate
    if @FFraction <> ''
      set @res = @res + '/' + @FFraction
  end
  if @FHousing <> ''
    set @res = @res + ' корпус ' +  @FHousing
  if @FBuilding <> ''
    set @res = @res + ' строение ' +  @FBuilding
  if @FFlat <> ''
    set @res = @res + ' кв. ' + @FFlat
  if @FEntrance <> ''
    set @res = @res + ' п. ' + @FEntrance

  if (IsNull(@FStreetID,0) > 0)
    if @res <> ''
      set @res = @FStreet + ', ' + @res
    else
      set @res = @FStreet

  if @FNote <> '' 
    set @res = @res + ' (' +  @FNote + ')'

  return @res
end
GO
-- получаем полное строковое представление адреса по коду
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetBaseAddressFullView]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetBaseAddressFullView]
GO
create function GetBaseAddressFullView(@AddressID integer)
returns nvarchar(4000)
as
begin
  declare @res nvarchar(4000)
  set @res = ''
  declare @FStreetID int
  declare @FLocality nvarchar(50)
  declare @FStreet nvarchar(50)
  declare @FEstate nvarchar(50)
  declare @FEstateType nvarchar(50)
  declare @FFraction nvarchar(50)
  declare @FHousing nvarchar(50)
  declare @FBuilding nvarchar(50)
  declare @FNote nvarchar(100) 
  select 
    @FStreetID = BP.StreetID,
    @FLocality = LT.Abbr + '. ' + L.Name,
    @FStreet = ST.Abbr + '. ' + S.Name,
    @FEstate = BP.Estate,
    @FEstateType = case BP.EstateType 
      when 0 then 'д. '
      when 1 then 'вл. '
      when 2 then 'уч. '
    end,
    @FFraction = BP.Fraction,
    @FHousing = BP.Housing,
    @FBuilding = BP.Building,
    @FNote = BP.Note 
    from AddressBaseParts BP
    inner join Localities L 
      inner join LocalityTypes LT on LT.TypeID = L.TypeID
      on L.LocalityID = BP.LocalityID    
    left join Streets S 
      inner join StreetTypes ST on ST.TypeID = S.TypeID
      on S.StreetID = BP.StreetID
    where BP.AddressID = @AddressID

  if @FEstate <> ''
  begin
    set @res = @FEstateType + @FEstate
    if @FFraction <> ''
      set @res = @res + '/' + @FFraction
  end
  if @FHousing <> ''
    set @res = @res + ' корпус ' +  @FHousing
  if @FBuilding <> ''
    set @res = @res + ' строение ' +  @FBuilding

  if (IsNull(@FStreetID,0) > 0)
    if @res <> ''
      set @res = @FStreet + ', ' + @res
    else
      set @res = @FStreet

  if @FNote <> '' 
    set @res = @res + ' (' +  @FNote + ')'

  return @res
end
GO
---- Addresses - FOR DELETE ----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OnAddressesDelete]') and xtype = N'TR')
drop trigger [dbo].[OnAddressesDelete]
GO
CREATE TRIGGER OnAddressesDelete ON [dbo].[Addresses] 
FOR DELETE 
AS
  if (SELECT Count(*) FROM DELETED) > 0 
    DELETE FROM AddressExtraParts WHERE ExtraPartID IN (SELECT ExtraPartID FROM DELETED)