/*
   Friday, March 30, 20121:42:17 PM
   User: 
   Server: .
   Database: ATNDBTest
   Application: 
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO


ALTER VIEW [dbo].[FactorHeaderAndDetailAbstractList]
 
AS
SELECT	dbo.FactorDetail.Serial AS FactorDetailSerial, 
		dbo.FactorHeader.Serial AS FactorHeaderSerial, 
		dbo.FactorHeader.Code AS FactorHeaderCode, 
        dbo.FactorKind.Serial AS FactorKindSerial,
        dbo.FactorKind.Description AS FactorKindDescription, 
        dbo.FactorKind.Stream AS FactorKindStream, 
        dbo.FactorKind.Priority,
		dbo.FactorHeader.ControlNo, 
		dbo.FactorHeader.EffectiveDate, 
		dbo.FactorHeader.RecordDate, 
		dbo.FactorHeader.EditDate, 
		dbo.FactorHeader.VerificationDate, 
		dbo.FactorHeader.UpdatingDate, 
		dbo.FactorHeader.Cost1th, 
		dbo.FactorHeader.Cost2th, 
		dbo.FactorHeader.Rebate, 
		dbo.FactorHeader.PayDate, 
		dbo.FactorHeader.Mechanize, 
		dbo.FactorHeader.Description, 
		dbo.Part.Serial as PartSerial, 
		dbo.Part.PartBuyPriceKindRef as PartBuyPriceKindSerial, 
		dbo.FactorDetail.Quantity, 
		dbo.FactorDetail.UnitPrice, 
		dbo.FactorDetail.TotalPrice,
		dbo.FactorDetail.BuyUnitPriceInPartTable, 
		dbo.FactorHeader.OfficeDetailRef, 
		dbo.FactorHeader.OfficePeriodRef
FROM dbo.FactorHeader INNER JOIN
	 dbo.FactorKind ON dbo.FactorHeader.FactorKindRef = dbo.FactorKind.Serial INNER JOIN
	 dbo.FactorDetail ON dbo.FactorHeader.Serial = dbo.FactorDetail.FactorHeaderRef INNER JOIN
	 dbo.Part ON dbo.FactorDetail.PartRef = dbo.Part.Serial


GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Items_Reserve
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Items_Remain
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Parts_MaxRemain
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Parts_MinRemain
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Items_PriceBuy
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Part_SaleProfitPercentKind
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Items_PriceSale
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Parts_SaleUnitPrice2
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Parts_SaleUnitPrice3
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Parts_SaleUnitPrice4
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Parts_SaleProfitPercent1
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Parts_SaleProfitPercent2
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Parts_SaleProfitPercent3
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT DF_Parts_SaleProfitPercent4
GO
CREATE TABLE dbo.Tmp_Part
	(
	Serial bigint NOT NULL,
	Code bigint NOT NULL,
	Name varchar(50) NULL,
	StorageRef bigint NULL,
	GroupRef bigint NULL,
	UnitRef bigint NULL,
	OEM varchar(25) NULL,
	Active bit NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	EditorRef int NULL,
	EditDate char(10) NULL,
	PartKindRef smallint NULL,
	Reserve float(53) NULL,
	Remain float(53) NULL,
	MaxRemain bigint NULL,
	MinRemain bigint NULL,
	ShelfNo varchar(15) NULL,
	PartBuyPriceKindRef smallint NULL,
	BuyUnitPrice bigint NULL,
	BuyUnitPriceInForeignCurrency money NULL,
	SaleDependToBuy bit NULL,
	PartProfitPercentKindRef smallint NULL,
	PartSalePriceMinimumRef smallint NULL,
	ForeignCurrencyForBuy bit NULL,
	ForeignCurrencyForSale bit NULL,
	SaleUnitPrice1 bigint NULL,
	SaleUnitPrice2 bigint NULL,
	SaleUnitPrice3 bigint NULL,
	SaleUnitPrice4 bigint NULL,
	SaleProfitPercent1 float(53) NULL,
	SaleProfitPercent2 float(53) NULL,
	SaleProfitPercent3 float(53) NULL,
	SaleProfitPercent4 float(53) NULL,
	Comment varchar(300) NULL,
	OfficeDetailRef int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Part SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Items_Reserve DEFAULT ((0)) FOR Reserve
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Items_Remain DEFAULT ((0)) FOR Remain
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Parts_MaxRemain DEFAULT ((0)) FOR MaxRemain
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Parts_MinRemain DEFAULT ((0)) FOR MinRemain
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Items_PriceBuy DEFAULT ((0)) FOR BuyUnitPrice
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Part_SaleProfitPercentKind DEFAULT ((1)) FOR PartProfitPercentKindRef
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Items_PriceSale DEFAULT ((0)) FOR SaleUnitPrice1
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Parts_SaleUnitPrice2 DEFAULT ((0)) FOR SaleUnitPrice2
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Parts_SaleUnitPrice3 DEFAULT ((0)) FOR SaleUnitPrice3
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Parts_SaleUnitPrice4 DEFAULT ((0)) FOR SaleUnitPrice4
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Parts_SaleProfitPercent1 DEFAULT ((0)) FOR SaleProfitPercent1
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Parts_SaleProfitPercent2 DEFAULT ((0)) FOR SaleProfitPercent2
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Parts_SaleProfitPercent3 DEFAULT ((0)) FOR SaleProfitPercent3
GO
ALTER TABLE dbo.Tmp_Part ADD CONSTRAINT
	DF_Parts_SaleProfitPercent4 DEFAULT ((0)) FOR SaleProfitPercent4
GO
IF EXISTS(SELECT * FROM dbo.Part)
	 EXEC('INSERT INTO dbo.Tmp_Part (Serial, Code, Name, StorageRef, GroupRef, UnitRef, OEM, Active, RecorderRef, RecordDate, EditorRef, EditDate, PartKindRef, Reserve, Remain, MaxRemain, MinRemain, ShelfNo, PartBuyPriceKindRef, BuyUnitPrice, SaleDependToBuy, PartProfitPercentKindRef, PartSalePriceMinimumRef, SaleUnitPrice1, SaleUnitPrice2, SaleUnitPrice3, SaleUnitPrice4, SaleProfitPercent1, SaleProfitPercent2, SaleProfitPercent3, SaleProfitPercent4, Comment, OfficeDetailRef)
		SELECT Serial, Code, Name, StorageRef, GroupRef, UnitRef, OEM, Active, RecorderRef, RecordDate, EditorRef, EditDate, PartKindRef, Reserve, Remain, MaxRemain, MinRemain, ShelfNo, PartBuyPriceKindRef, BuyUnitPrice, SaleDependToBuy, PartProfitPercentKindRef, PartSalePriceMinimumRef, SaleUnitPrice1, SaleUnitPrice2, SaleUnitPrice3, SaleUnitPrice4, SaleProfitPercent1, SaleProfitPercent2, SaleProfitPercent3, SaleProfitPercent4, Comment, OfficeDetailRef FROM dbo.Part WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.PartProductRawMaterialHeader
	DROP CONSTRAINT FK_PartProductRawMaterialHeader_Part
GO
ALTER TABLE dbo.StorageCountDetail
	DROP CONSTRAINT FK_StorageCountDetail_Part
GO
ALTER TABLE dbo.PartProductRawMaterialDetail
	DROP CONSTRAINT FK_PartProductRawMaterial_Part
GO
ALTER TABLE dbo.FactorDetail
	DROP CONSTRAINT FK_FactorsDetail_Parts
GO
ALTER TABLE dbo.PartWarehouseBridge
	DROP CONSTRAINT FK_PartWarehouseBridge_Part
GO
DROP TABLE dbo.Part
GO
EXECUTE sp_rename N'dbo.Tmp_Part', N'Part', 'OBJECT' 
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	PK_Parts_Storage PRIMARY KEY CLUSTERED 
	(
	Serial
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IDX_Part_OfficeDetailRef ON dbo.Part
	(
	OfficeDetailRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_Remain ON dbo.Part
	(
	Remain
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Create TRIGGER [dbo].[ComputeWarehouseRemain] ON dbo.Part 
After INSERT, UPDATE, DELETE
AS

Delete From PartWarehouseBridge
Where PartRef in (SELECT Serial FROM Inserted) Or PartRef in (SELECT Serial FROM Deleted)

Insert Into PartWarehouseBridge
			Select PartSerial as PartRef,
				   WarehouseSerial as WarehouseRef, 
				   Isnull(Sum(In_put-Out_put),0) as Remain, 
				   Isnull(Sum(Out_put_Reserve),0) as Reserve 
			From (Select PartSerial,
						 WarehouseSerial,
					     Case When VerificationDate Is Not Null And FactorKindStream = 'I' Then Quantity Else 0 End as In_put,
					     Case When FactorKindStream = 'O' Then Quantity Else 0 End as Out_put,
					     Case When VerificationDate Is Null And FactorKindStream = 'O' Then Quantity Else 0 End as Out_put_Reserve
				  From FactorHeaderAndDetailList
				  Where PartSerial in (SELECT Serial FROM Inserted) Or PartSerial in (SELECT Serial FROM Deleted)) Tbl1	
			Group By PartSerial,WarehouseSerial
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PartWarehouseBridge ADD CONSTRAINT
	FK_PartWarehouseBridge_Part FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.PartWarehouseBridge SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.FactorDetail WITH NOCHECK ADD CONSTRAINT
	FK_FactorsDetail_Parts FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.FactorDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PartProductRawMaterialDetail ADD CONSTRAINT
	FK_PartProductRawMaterial_Part FOREIGN KEY
	(
	RawMaterialRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.PartProductRawMaterialDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.StorageCountDetail ADD CONSTRAINT
	FK_StorageCountDetail_Part FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.StorageCountDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PartProductRawMaterialHeader ADD CONSTRAINT
	FK_PartProductRawMaterialHeader_Part FOREIGN KEY
	(
	ProductRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.PartProductRawMaterialHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
