-- 1.1.10 Power.TechnologicalConnection
-- Добавляем "Примечание" в объекты электроснабжения.

alter table ConsumingObjects add Note nvarchar(max)
GO
EXEC sp_rename 'SealAccounts', 'SealAccountTasks'
GO
CREATE TABLE SealAccounts
(
	SealAccountID		int  identity(1,1),	
	ActNumber			nvarchar(50),
	ActDate				datetime,
	ActExecutorID		int,
	SealAccountReasonID int,
	OtherSealPlace		nvarchar(300),
	ActNote				nvarchar(300),
	WitnessId			int,
	AttachId			int,		
	Primary Key (SealAccountID)
) 
GO
SET IDENTITY_INSERT SealAccounts ON
INSERT INTO SealAccounts(SealAccountID, ActNumber,	ActDate, ActExecutorID,	SealAccountReasonID, OtherSealPlace, ActNote, WitnessId, AttachId)
	SELECT SealAccountID, ActNumber,ActDate, ActExecutorID,	SealAccountReasonID, OtherSealPlace, ActNote, WitnessId, AttachId
	FROM SealAccountTasks
SET IDENTITY_INSERT SealAccounts OFF
GO
declare @table_name nvarchar(50)
declare @strSQL nvarchar(1024)

set @table_name=N'SealAccountTasks'
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 SealAccountTasks WITH NOCHECK
   ADD CONSTRAINT SealAccount_fk FOREIGN KEY (SealAccountID)
      REFERENCES SealAccounts(SealAccountID) 
      ON DELETE CASCADE
GO
alter table SealAccountTasks drop column ActNumber
alter table SealAccountTasks drop column ActDate
alter table SealAccountTasks drop column ActExecutorID
alter table SealAccountTasks drop column SealAccountReasonID
alter table SealAccountTasks drop column OtherSealPlace
alter table SealAccountTasks drop column ActNote
alter table SealAccountTasks drop column WitnessId
alter table SealAccountTasks drop column AttachId
GO
alter table SealAccountTasks add ActID int
GO
update SealAccountTasks set ActID = SealAccountID
GO
alter table SealAccountTasks alter column ActID int not null
GO
ALTER TABLE SealAccounts add ConsumingObjectID int
GO 
Update SealAccounts set SealAccounts.ConsumingObjectID = TCApplications.ConsumingObjectID
From TCApplications
where TCApplicationID = SealAccountID
Go
alter table SealAccounts alter column ConsumingObjectID int not null
GO
ALTER TABLE SealAccounts
	ADD CONSTRAINT ConsumingObject_fk FOREIGN KEY (ConsumingObjectID)
      REFERENCES ConsumingObjects(ID) 
      ON DELETE CASCADE
GO
ALTER TABLE SealAccountTasks
	ADD CONSTRAINT TCAct_fk FOREIGN KEY (ActID)
	REFERENCES TCActs(ActID)
	ON DELETE CASCADE
        ON UPDATE CASCADE
        
ALTER TABLE SealAccountTasks 
	ADD CONSTRAINT Executor_fk FOREIGN KEY(ExecutorID) 
	REFERENCES Персонал(Код_работника)
GO
ALTER TABLE SealAccounts
	ADD CONSTRAINT ActExecutor_fk FOREIGN KEY (ActExecutorID) 
		REFERENCES Персонал (Код_работника)
		
ALTER TABLE SealAccounts
	ADD CONSTRAINT SealReasont_fk FOREIGN KEY (SealAccountReasonID)
		REFERENCES SealAccountReasons (ReasonID)

ALTER TABLE SealAccounts
	ADD CONSTRAINT Attach_fk FOREIGN KEY (AttachId) 
		REFERENCES StoreDocuments (DocumentID)
		ON DELETE CASCADE
GO
-- Перебрасываем связь со счетчиками с SealAccountList на SealAccounts
declare @table_name nvarchar(50)
declare @strSQL nvarchar(1024)

set @table_name=N'SealAccountList'
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 SealAccountList 
       ADD CONSTRAINT Counter_fk FOREIGN KEY (CounterID)
       REFERENCES Измерительный_прибор(Код_прибора)
       ON DELETE CASCADE
       
ALTER TABLE SealAccountList 
       ADD CONSTRAINT SealAccountList_fk FOREIGN KEY (SealAccountID)
       REFERENCES SealAccounts (SealAccountID)
       ON DELETE CASCADE
GO
-- Добавим связь "Абонент" в Акта приемки электроучетов.
alter table SealAccounts add AbonentID int
GO
ALTER TABLE SealAccounts
       ADD CONSTRAINT SealAccountAbonent_fk FOREIGN KEY (AbonentID)
       REFERENCES LegalEntities (LegalEntityID)       