-- 1.0.36 Power.TechnologicalConnection
-- Добавим для хранения некоторых констант
CREATE TABLE IntConsts
(
	ConstID	int identity(1,1),
	Description nvarchar(100),
	Name	nvarchar(50) NOT NULL,
	Value	int NOT NULL,
	PRIMARY KEY (ConstID)
)
-- Переделываем "Выполнение заявок"
CREATE TABLE ApplicationExecuteWorks
(
	ApplicationID					int NOT NULL,

		ProjectNeed					bit,
        ProjectNumber				nvarchar(50),
		ProjectName					nvarchar(500),
		ProjectGiveTask				datetime,
		ProjectExecutorID			int,        
		ProjectPlanDate				datetime,
        ProjectDate 				datetime,
		ProjectAttachID				int,
		ProjectCost					decimal(18,2),
		ProjectSMRCost				decimal(18,2),
		ProjectTechWatchNeed		bit,
		ProjectTechWatchGive		datetime,
		ProjectTechWatchDate		datetime,
		ProjectTechWatchActAttachID	int,		
		ProjectTabEditorID			int,

		ProjectApplicantNeed		bit,
		ProjectApplicantNumber		nvarchar(50),
		ProjectApplicantName		nvarchar(250),
		ProjectApplicantDateReady	datetime,
		ProjectApplicantDateAgreement	datetime,
		ProjectApplicantDateNotAgreement	datetime,
		ProjectApplicantGiving		datetime,
		ProjectRefuzedID			int,
		ProjectApplicantOrganizationID 		int,
		ProjectApplicantTabEditorID	int,		

		SMRNeed					bit,
        SMRStartDate 				datetime,
        SMRPlanDate				datetime,
        SMREndDate 				datetime,
        SMRLegalEntityID			int,
        SMRExecutorID				int,
        SMRContractNumber			nvarchar(50),
		SMRTechWatchDate			datetime,
		SMRTabEditorID			int,	        

	PRIMARY KEY (ApplicationID)	
)
GO
insert into ApplicationExecuteWorks (ApplicationID,
		ProjectNeed,
        ProjectNumber,
		ProjectName,
		ProjectGiveTask,
		ProjectExecutorID,        
		ProjectPlanDate,
        ProjectDate,
		ProjectCost,
		ProjectSMRCost,
		ProjectTechWatchNeed,
		ProjectTechWatchGive,
		ProjectTechWatchDate,
		ProjectTabEditorID,

		ProjectApplicantNeed,
		ProjectApplicantNumber,
		ProjectApplicantName,
		ProjectApplicantDateReady,
		ProjectApplicantDateAgreement,
		ProjectApplicantDateNotAgreement,
		ProjectApplicantGiving,
		ProjectRefuzedID,
		ProjectApplicantOrganizationID,
		ProjectApplicantTabEditorID,		

		SMRNeed,
        SMRStartDate,
        SMRPlanDate,
        SMREndDate,
        SMRLegalEntityID,
        SMRExecutorID,
        SMRContractNumber,
		SMRTechWatchDate,
		SMRTabEditorID)
		
		SELECT ActID,
		ProjectNeed,
        ProjectNumber,
		ProjectName,
		ProjectGiveTask,
		ProjectExecutorID,        
		ProjectPlanDate,
        ProjectDate,
		ProjectCost,
		ProjectSMRCost,
		ProjectTechWatchNeed,
		ProjectTechWatchGive,
		ProjectTechWatchDate,
		ProjectTabEditorID,

		ProjectApplicantNeed,
		ProjectApplicantNumber,
		ProjectApplicantName,
		ProjectApplicantDateReady,
		ProjectApplicantDateAgreement,
		ProjectApplicantDateNotAgreement,
		ProjectApplicantGiving,
		ProjectRefuzedID,
		ProjectApplicantOrganizationID,
		ProjectApplicantTabEditorID,		

		WorksNeed,
        WorksStartDate,
        WorksPlanDate,
        WorksEndDate,
        WorksLegalEntityID,
        WorksExecutorID,
        WorksContractNumber,
		WorksTechWatchDate,
		WorksTabEditorID
		FROM TCActs
GO
ALTER TABLE ApplicationExecuteWorks
	ADD FOREIGN KEY (ApplicationID)
	REFERENCES TCApplications(TCApplicationID)
	ON DELETE CASCADE
        ON UPDATE CASCADE

alter table ApplicationExecuteWorks add foreign key(ProjectApplicantOrganizationID) references LegalEntities(LegalEntityID)
alter table ApplicationExecuteWorks add foreign key(ProjectExecutorID) references Персонал(Код_работника)
alter table ApplicationExecuteWorks add foreign key(SMRLegalEntityID) references LegalEntities(LegalEntityID)
alter table ApplicationExecuteWorks add foreign key(SMRExecutorID) references Персонал(Код_работника)
alter table ApplicationExecuteWorks add foreign key(ProjectTabEditorID) references Персонал(Код_работника)		
alter table ApplicationExecuteWorks add foreign key(ProjectApplicantTabEditorID) references Персонал(Код_работника)
alter table ApplicationExecuteWorks add foreign key(SMRTabEditorID) references Персонал(Код_работника)
alter table ApplicationExecuteWorks add foreign key(ProjectRefuzedID)references TCActProjectRefuzed  (TCActProjectRefuzedID)
GO
declare @table_name nvarchar(50)
declare @strSQL nvarchar(1024)

set @table_name=N'SMRWorksExecutors'
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 SMRWorksExecutors
	ADD FOREIGN KEY (ExecutorID)
	REFERENCES Персонал (Код_работника)
	ON DELETE CASCADE
	
ALTER TABLE SMRWorksExecutors
	ADD FOREIGN KEY (ApplicationID)
	REFERENCES ApplicationExecuteWorks (ApplicationID)
	ON DELETE CASCADE
GO
-- Таблица "Заявка на подключение"
CREATE TABLE ConnectionApplications
(
	ApplicationID int NOT NULL,
	CheckupEPU				bit,
	AcceptanceUchet			bit,
	PresentingPower			bit,
	ReadyForInspectionAppL	datetime,
	ReadyForInspectionAppG	datetime,
	ReadyForInspectionOrgL	datetime,
	ReadyForInspectionOrgG	datetime,	
	ReadyForPresentingPower	datetime,
	WaitingPowerDays		int,
	GiveDocumentsDate		datetime,
	ConnectionType			int not null,
	ExecutorID				int,
	PRIMARY KEY (ApplicationID)
)
ALTER TABLE ConnectionApplications
	ADD FOREIGN KEY (ApplicationID)
	REFERENCES TCApplications(TCApplicationID)
	ON DELETE CASCADE
        ON UPDATE CASCADE

ALTER TABLE ConnectionApplications 
	ADD FOREIGN KEY(ExecutorID) 
		REFERENCES Персонал(Код_работника)
GO
insert into ConnectionApplications(ApplicationID, ConnectionType)
	SELECT (ApplicationID), 0 FROM ApplicationExecuteWorks
GO
-- Переделываем TCActs.
declare @table_name nvarchar(50)
declare @strSQL nvarchar(1024)

set @table_name=N'TCActs'
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
update Delineations set Delineations.PmaxFromContract = TCActs.PmaxFromContract,  
						Delineations.PmaxFromNet = TCActs.PmaxFromNet, 
						Delineations.PmaxFromSpec = TCActs.PmaxFromSpec 
  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 TCActs drop column ProjectNeed
ALTER TABLE TCActs drop column ProjectNumber
ALTER TABLE TCActs drop column ProjectName	
ALTER TABLE TCActs drop column ProjectGiveTask
ALTER TABLE TCActs drop column ProjectExecutorID
ALTER TABLE TCActs drop column ProjectPlanDate
ALTER TABLE TCActs drop column ProjectDate
ALTER TABLE TCActs drop column ProjectAttach
ALTER TABLE TCActs drop column ProjectCost
ALTER TABLE TCActs drop column ProjectSMRCost
ALTER TABLE TCActs drop column ProjectTechWatchNeed
ALTER TABLE TCActs drop column ProjectTechWatchGive
ALTER TABLE TCActs drop column ProjectTechWatchDate
ALTER TABLE TCActs drop column ProjectTechWatchActAttach
ALTER TABLE TCActs drop column ProjectTabEditorID

ALTER TABLE TCActs drop column ProjectApplicantNeed
ALTER TABLE TCActs drop column ProjectApplicantNumber
ALTER TABLE TCActs drop column ProjectApplicantName
ALTER TABLE TCActs drop column ProjectApplicantDateReady
ALTER TABLE TCActs drop column ProjectApplicantDateAgreement
ALTER TABLE TCActs drop column ProjectApplicantDateNotAgreement
ALTER TABLE TCActs drop column ProjectApplicantGiving
ALTER TABLE TCActs drop column ProjectRefuzedID
ALTER TABLE TCActs drop column ProjectApplicantOrganizationID
ALTER TABLE TCActs drop column ProjectApplicantTabEditorID

ALTER TABLE TCActs drop column WorksNeed
ALTER TABLE TCActs drop column WorksStartDate
ALTER TABLE TCActs drop column WorksPlanDate
ALTER TABLE TCActs drop column WorksEndDate
ALTER TABLE TCActs drop column WorksLegalEntityID
ALTER TABLE TCActs drop column WorksExecutorID
ALTER TABLE TCActs drop column WorksContractNumber
ALTER TABLE TCActs drop column WorksTechWatchDate
ALTER TABLE TCActs drop column WorksTabEditorID

ALTER TABLE TCActs drop column PmaxFromContract
ALTER TABLE TCActs drop column PmaxFromNet
ALTER TABLE TCActs drop column PmaxFromSpec

ALTER TABLE TCActs drop column UnitInspectionJobDate
ALTER TABLE TCActs drop column UnitInspectionDate
ALTER TABLE TCActs drop column UnitInspectionExecutor
ALTER TABLE TCActs drop column CompanyCommissioningActNumber
ALTER TABLE TCActs drop column CompanyCommissioningDate
ALTER TABLE TCActs drop column ActualConnectionApplicationFilingDate
ALTER TABLE TCActs drop column ActualConnectionDate
ALTER TABLE TCActs drop column ActualConnectionExecutorID
ALTER TABLE TCActs drop column SupplyApplicationFilingDate

alter table TCActs add ExecuteDiagramB		bit
alter table TCActs add ProjectOuterPowerB	bit
alter table TCActs add LicenseProjectB		bit
alter table TCActs add LicenseWorksB		bit
alter table TCActs add ActHideWorksB		bit

GO
ALTER TABLE TCActs
	ADD FOREIGN KEY (ActID)
	REFERENCES TCApplications(TCApplicationID)
	ON DELETE CASCADE
        ON UPDATE CASCADE

alter table TCActs add foreign key(SpecFulfilmentExecutorID) references Персонал(Код_работника)
alter table TCActs add foreign key(SupplyExecutorID) references Персонал(Код_работника)
GO
-- Переделка, не будем хранить промежуточные таблицы для хранения документов. 
-- Тип документа будем задавать в столбце DocumentType
CREATE TABLE StoreDocumentTypes
(
	StoreDocumentTypeID int NOT NULL,
	Description			nvarchar(100),
	Primary Key (StoreDocumentTypeID)
)
ALTER TABLE StoreDocuments add DocumentTypeID int
GO
ALTER TABLE StoreDocuments
	ADD FOREIGN KEY (DocumentTypeID)
	REFERENCES StoreDocumentTypes(StoreDocumentTypeID)
GO
INSERT INTO StoreDocumentTypes(StoreDocumentTypeID, Description) Values(0, 'Any')
INSERT INTO StoreDocumentTypes(StoreDocumentTypeID, Description) Values(1, 'Delineation')
INSERT INTO StoreDocumentTypes(StoreDocumentTypeID, Description) Values(2, 'TCSpec')
INSERT INTO StoreDocumentTypes(StoreDocumentTypeID, Description) Values(3, 'SingleLineDiagram')
INSERT INTO StoreDocumentTypes(StoreDocumentTypeID, Description) Values(4, 'RusTechWatchAct')
INSERT INTO StoreDocumentTypes(StoreDocumentTypeID, Description) Values(5, 'ProjectAttach')
INSERT INTO StoreDocumentTypes(StoreDocumentTypeID, Description) Values(6, 'ProjectRusTechWatchAttach')
GO
update StoreDocuments Set DocumentTypeID = 1
	where DocumentID in (select DocumentID From DelineationDocuments)
update StoreDocuments Set DocumentTypeID = 2
	where DocumentID in (select DocumentID From TCSpecDocuments)
GO
alter table StoreDocuments alter column DocumentTypeID int not null
GO
alter table Delineations add DelineationDocumentID int
GO
update Delineations set DelineationDocumentID = DelineationDocuments.DocumentID
From Delineations inner join DelineationDocuments on Delineations.DelineationID = DelineationDocuments.DelineationID
GO
ALTER TABLE Delineations
	ADD FOREIGN KEY (DelineationDocumentID)
	REFERENCES StoreDocuments(DocumentID)	 
GO
drop table DelineationDocuments
GO
alter table TCSpecs add TCSpecDocumentID int
GO
update TCSpecs set TCSpecDocumentID = TCSpecDocuments.DocumentID 
From TCSpecs inner join TCSpecDocuments on TCSpecs.SpecID = TCSpecDocuments.TCSpecID
GO
ALTER TABLE TCSpecs
	ADD FOREIGN KEY (TCSpecDocumentID)
	REFERENCES StoreDocuments(DocumentID)	 
GO
DROP TABLE TCSpecDocuments
GO
ALTER TABLE ConsumingObjects add SingleLineDiagramID int
GO
ALTER TABLE ConsumingObjects
	ADD FOREIGN KEY (SingleLineDiagramID)
	REFERENCES StoreDocuments(DocumentID)	 
GO
alter table TCActs Add RusTechWatchID int
GO
ALTER TABLE TCActs
	ADD FOREIGN KEY (RusTechWatchID)
	REFERENCES StoreDocuments(DocumentID)	 
GO
-- Проекты приложения для ПИР
ALTER TABLE ApplicationExecuteWorks
	ADD FOREIGN KEY (ProjectAttachID)
		REFERENCES StoreDocuments(DocumentID)	 
GO
ALTER TABLE ApplicationExecuteWorks
	ADD FOREIGN KEY (ProjectTechWatchActAttachID)
		REFERENCES StoreDocuments(DocumentID)	
GO
-- Осмотр ЭПУ потребителя
CREATE TABLE InspectionEPU
(
	InspectionEPU_ID	int not null,
	ExecutorID			int not null,
	StartDate			datetime,
	FinishDate			datetime,
	Primary Key (InspectionEPU_ID)
) 
GO
ALTER TABLE InspectionEPU
	ADD FOREIGN KEY (InspectionEPU_ID)
	REFERENCES TCActs(ActID)
	ON DELETE CASCADE
        ON UPDATE CASCADE
ALTER TABLE InspectionEPU 
	ADD FOREIGN KEY(ExecutorID) 
	REFERENCES Персонал(Код_работника)
GO
-- Опломбировка учетов
CREATE TABLE SealAccounts
(
	SealAccountID		int not null,
	ExecutorID			int not null,
	StartDate			datetime,
	FinishDate			datetime,
	Primary Key (SealAccountID)
) 
GO
ALTER TABLE SealAccounts
	ADD FOREIGN KEY (SealAccountID)
	REFERENCES TCActs(ActID)
	ON DELETE CASCADE
        ON UPDATE CASCADE
ALTER TABLE SealAccounts 
	ADD FOREIGN KEY(ExecutorID) 
	REFERENCES Персонал(Код_работника)
GO
-- Установка ограничений мощности.
CREATE TABLE SetMaximumPower
(
	SetMaximumPowerID	int not null,
	ExecutorID			int not null,
	StartDate			datetime,
	FinishDate			datetime,
	Primary Key (SetMaximumPowerID)
) 
GO
ALTER TABLE SetMaximumPower
	ADD FOREIGN KEY (SetMaximumPowerID)
	REFERENCES TCActs(ActID)
	ON DELETE CASCADE
        ON UPDATE CASCADE
ALTER TABLE SetMaximumPower 
	ADD FOREIGN KEY(ExecutorID) 
	REFERENCES Персонал(Код_работника)
GO
-- Фактическое присоединение
CREATE TABLE RealConnection
(
	RealConnectionID	int not null,
	ExecutorID			int not null,
	StartDate			datetime,
	FinishDate			datetime,
	Primary Key (RealConnectionID)
) 
GO
ALTER TABLE RealConnection
	ADD FOREIGN KEY (RealConnectionID)
	REFERENCES TCActs(ActID)
	ON DELETE CASCADE
        ON UPDATE CASCADE
ALTER TABLE RealConnection 
	ADD FOREIGN KEY(ExecutorID) 
	REFERENCES Персонал(Код_работника)
GO
-- Подача напряжения
CREATE TABLE GivePower
(
	GivePowerID	int not null,
	ExecutorID			int not null,
	StartDate			datetime,
	FinishDate			datetime,
	Primary Key (GivePowerID)
) 
GO
ALTER TABLE GivePower
	ADD FOREIGN KEY (GivePowerID)
	REFERENCES TCActs(ActID)
	ON DELETE CASCADE
        ON UPDATE CASCADE
ALTER TABLE GivePower 
	ADD FOREIGN KEY(ExecutorID) 
	REFERENCES Персонал(Код_работника)
GO
