--Таблица смет
create table info.Estimate
(estimateID		int identity	primary key		not null,
estimateNomber	int								not null,
estimateDate	datetime	default getdate()	not null,
estimateOwner varbinary( 85 ) DEFAULT SUSER_SID()	not null,
estimateName	varchar( 255 )					not null,
estimateNik		varchar( 50 )						not null,
inProject			bit	default  0 				not null,
blocked				bit default  0 				not null,
ObjectChargeID	int								null
foreign key references	info.ObjectCharges(ObjectChargeID),
ContractID		int								not null
foreign key references info.Contracts(ContractID),
constraint Estimate_ContractID_estimateNomber unique(ContractID,estimateNomber),
constraint Estimate_ContractID_estimateName unique(ContractID,estimateName),
constraint Estimate_ContractID_estimateNik unique(ContractID,estimateNik)
)
go
--разделы смет
create table info.Parts
(partID			int identity	primary key		not null,
PartNomber		int								not null,
PartName		varchar( 255 )					not null,
ObjectChargeID	int								null
foreign key references	info.ObjectCharges(ObjectChargeID),
estimateID		int								not null
foreign key references	info.Estimate(estimateID),
constraint Parts_estimateID_PartNomber unique (estimateID,PartNomber),
constraint Parts_estimateID_PartName unique (estimateID,PartName)
)
go
--наборы работ сметы
create table info.Works
(WorkID				int identity		primary key			not null,
WorkNomber			int										not null,
WorkName			varchar( 255 )							not null,
WorkED				varchar( 50 )								not null,
WorkValue			decimal		default  0 					not null,
WorkCost			money		default  0 					not null,
WorkSumma			as WorkValue*WorkCost,
WorkFixedSumma		money		default  0 					not null,
WorkTZ				decimal		default  0 					not null,
WorkTzsumma			as WorkTZ*WorkValue,
WorkNotes			varchar( 255 )							null,
Subcontractor		bit default  0 							not null,
WorkKey				as WorkName + '_' + WorkED,
partID				int										not null
foreign key references info.Parts(partID),
constraint Works_WorkCost_WorkFixedSumma		check((WorkCost>= 0  and WorkFixedSumma= 0 ) or
(WorkCost> 0  and WorkFixedSumma> 0 )),
constraint Works_WorkTZ							check(WorkTZ>= 0 ),
constraint Works_WorkNomber						check(WorkNomber> 0 ),
constraint Works_WorkValue						check(WorkValue>= 0 ),
constraint Works_WorkNomber_partID				unique(partID,WorkNomber),
)
go
--материалы в составе работ сметы
---postavschik - поставщик. принимает 3 варианта значений:
--1 Отдел снабжения
--2 Прорабы
--3 Заказчик
create table info.Materials
(MaterialID				int identity	primary key		not null,
MaterialName			varchar( 255 )					not null,
MaterialMarka			varchar( 50 )						not null,
MaterialED				varchar( 50 )						not null,
MaterialValue			decimal	default  0 				not null,
MaterialCost			money	default  0 				not null,
MaterialSumma			as MaterialCost*MaterialValue,
MaterialFixedSumma		money		default  0 			not null,
MaterialNotes			varchar( 255 )					null,
MaterialKey1			as MaterialName + '_' + MaterialMarka + '_' + MaterialED,
MaterialKey2			as MaterialName + '_' + MaterialMarka + '_' + MaterialED  + '_' + isnull(MaterialNotes,' '),
WorkID				int								not null		
foreign key references info.Works(WorkID),
postavschik			tinyint	default  1 				not null
constraint	Materials_postavschik check(postavschik= 1  or
postavschik= 2  or postavschik= 3 ),
constraint Materials_MaterialCost_MaterialFixedSumma		check((MaterialCost>= 0  and MaterialFixedSumma= 0 ) or
(MaterialCost> 0  and MaterialFixedSumma> 0 ))
)
go

--Заявки на материалы
create table building.ZvMaterials
(
zvMaterialID		int identity	primary key		not null,
zvMaterialNomber	int								not null,
zvMaterialDate		datetime	default getdate()	not null,
zvMaterialOwner varbinary( 85 ) DEFAULT SUSER_SID()	not null,
zvYear				as year(zvMaterialDate),
estimateID			int								not null
foreign key references	info.Estimate(estimateID),
constraint ZvMaterials_zvMaterialNomber_Year unique(zvMaterialNomber,zvYear)
)
--Записи заявки на материалы
create table building.ZvRecMaterials
(zvRecMaterialID				int identity	primary key		not null,
zvRecMaterialName			varchar( 255 )					not null,
zvRecMaterialMarka			varchar( 50 )						not null,
zvRecMaterialED				varchar( 50 )						not null,
zvRecMaterialValue			decimal	default  0 				not null,
zvRecMaterialCost			money	default  0 				not null,
zvRecMaterialSumma			as zvRecMaterialCost*zvRecMaterialValue,
zvRecMaterialNotes			varchar( 255 )					null,
zvRecMaterialKey1			as zvRecMaterialName + '_' + zvRecMaterialMarka + '_' + zvRecMaterialED,
zvRecMaterialKey2			as zvRecMaterialName + '_' + zvRecMaterialMarka + '_' + zvRecMaterialED  + '_' + 
isnull(zvRecMaterialNotes,' '),
zvMaterialID				int								not null		
foreign key references building.ZvMaterials(zvMaterialID)
)
go

--Межтабличное ограничение: нельзя заказать материал в количестве, превышающем остаток
CREATE ASSERTION zayavka_ne_mojet_bit_bolshe_ostatka
AS CHECK (
(SELECT SUM(info.Materials.MaterialValue) AS [Объем остатка] 
FROM info.Materials, info.Works, info.Parts, building.ZvMaterials, building.ZvRecMaterials
WHERE	info.Materials.WorkID=info.Works.WorkID 
		AND info.Works.partID=info.Parts.partID 
		AND info.Parts.estimateID=building.ZvMaterials.estimateID
		AND building.ZvMaterials.zvMaterialID=building.ZvMaterials.zvMaterialID
)
<=(...)
)