/*
   سه شنبه, 25 آذر 139903:19:46 ب.ظ
   User: 
   Server: TAHERIAN
   Database: ATNDB
   Application: 
*/
Use ATNDBTest
Go
/*=============================================================================================================*/
Create Table dbo.PartCluster (
	Serial Bigint Identity(1,1) Not Null, 
	Code Bigint Null, --------------------
	PartClusterRef Bigint Null, ----------
	FADescription Varchar(50) Null, ------
	ENDescription Varchar(50) Null, ------
	Comment Varchar(300) Null, -----------
	RecorderRef Int Null, ----------------
	RecordDate char(10) Null, ------------
	EditorRef Int Null, ------------------
	EditDate char(10) Null,	--------------
	OfficeDetailRef Int Null, ------------
	Tag Varchar(50) Null) ----------------
Go
/*=============================================================================================================*/
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
If Exists(SELECT * FROM sys.objects WHERE name ='FK_Parts_UnitWeight')
Begin
	ALTER TABLE dbo.Part DROP CONSTRAINT FK_Parts_UnitWeight
End
GO

ALTER TABLE dbo.UnitWeight SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_Parts_Unit')
Begin
	ALTER TABLE dbo.Part DROP CONSTRAINT FK_Parts_Unit
End
GO

ALTER TABLE dbo.Units SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_Parts_Storages')
Begin
	ALTER TABLE dbo.Part DROP CONSTRAINT FK_Parts_Storages
End
GO

ALTER TABLE dbo.Storages SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_Parts_PartPriceKind')
Begin
	ALTER TABLE dbo.Part DROP CONSTRAINT FK_Parts_PartPriceKind
End
GO

ALTER TABLE dbo.PartBuyPriceKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_Parts_PartKind')
Begin
	ALTER TABLE dbo.Part DROP CONSTRAINT FK_Parts_PartKind
End
GO

ALTER TABLE dbo.PartKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_Parts_Groups')
Begin
	ALTER TABLE dbo.Part DROP CONSTRAINT FK_Parts_Groups
End
GO

ALTER TABLE dbo.Groups SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_Part_PartSalePriceScope')
Begin
	ALTER TABLE dbo.Part DROP CONSTRAINT FK_Part_PartSalePriceScope
End
GO

ALTER TABLE dbo.PartSalePriceScope SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_Part_PartProfitPercentKind')
Begin
	ALTER TABLE dbo.Part DROP CONSTRAINT FK_Part_PartProfitPercentKind
End
GO

ALTER TABLE dbo.PartProfitPercentKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
If Exists(Select * From sys.Views Where name = 'FactorHeaderAndDetailAbstractList') Drop View dbo.FactorHeaderAndDetailAbstractList
GO
CREATE TABLE dbo.Tmp_Part
	(
	Serial bigint NOT NULL,
	Code varchar(25) NOT NULL,
	Name varchar(200) NULL,
	ENName varchar(200) NULL,
	StorageRef bigint NULL,
	PartClusterRef bigint NULL,
	GroupRef bigint NULL,
	UnitRef bigint NULL,
	UnitWeight decimal(35, 4) NULL,
	UnitWeightRef bigint NULL,
	OEM varchar(25) NULL,
	Barcode nvarchar(200) NULL,
	BarcodeImg varbinary(MAX) NULL,
	PartImg varbinary(MAX) NULL,
	AddedTaxInBuy bit NULL,
	AddedTaxInSales bit 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,
	Orders float(53) NULL,
	MaxRemain bigint NULL,
	MinRemain bigint NULL,
	ShelfNo varchar(50) NULL,
	PartBuyPriceKindRef smallint NULL,
	BuyUnitPrice decimal(35, 15) NULL,
	BuyUnitPriceInForeignCurrency money NULL,
	SaleDependToBuy bit NULL,
	PartProfitPercentKindRef smallint NULL,
	PartSalePriceScopeRef smallint NULL,
	ForeignCurrencyForBuy bit NULL,
	ForeignCurrencyForSale bit NULL,
	Comment varchar(300) NULL,
	OfficeDetailRef int NULL,
	Tag varchar(50) NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Part SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Part)
	 EXEC('INSERT INTO dbo.Tmp_Part (Serial, Code, Name, ENName, StorageRef, GroupRef, UnitRef, UnitWeight, UnitWeightRef, OEM, Barcode, BarcodeImg, PartImg, AddedTaxInBuy, AddedTaxInSales, Active, RecorderRef, RecordDate, EditorRef, EditDate, PartKindRef, Reserve, Remain, Orders, MaxRemain, MinRemain, ShelfNo, PartBuyPriceKindRef, BuyUnitPrice, BuyUnitPriceInForeignCurrency, SaleDependToBuy, PartProfitPercentKindRef, PartSalePriceScopeRef, ForeignCurrencyForBuy, ForeignCurrencyForSale, Comment, OfficeDetailRef, Tag)
		SELECT Serial, Code, Name, ENName, StorageRef, GroupRef, UnitRef, UnitWeight, UnitWeightRef, OEM, Barcode, BarcodeImg, PartImg, AddedTaxInBuy, AddedTaxInSales, Active, RecorderRef, RecordDate, EditorRef, EditDate, PartKindRef, Reserve, Remain, Orders, MaxRemain, MinRemain, ShelfNo, PartBuyPriceKindRef, BuyUnitPrice, BuyUnitPriceInForeignCurrency, SaleDependToBuy, PartProfitPercentKindRef, PartSalePriceScopeRef, ForeignCurrencyForBuy, ForeignCurrencyForSale, Comment, OfficeDetailRef, Tag FROM dbo.Part WITH (HOLDLOCK TABLOCKX)')
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_FactorDetail_Part')
Begin
	ALTER TABLE dbo.FactorDetail DROP CONSTRAINT FK_FactorDetail_Part
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PartWarehouseBridge_Part')
Begin
	ALTER TABLE dbo.PartWarehouseBridge DROP CONSTRAINT FK_PartWarehouseBridge_Part
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PartGroupsBridge_Part')
Begin
	ALTER TABLE dbo.PartGroupsBridge DROP CONSTRAINT FK_PartGroupsBridge_Part
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_FactorsDetail_Parts')
Begin
	ALTER TABLE dbo.FactorDetail DROP CONSTRAINT FK_FactorsDetail_Parts
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PartSalePrice_Part')
Begin
	ALTER TABLE dbo.PartSalePrice DROP CONSTRAINT FK_PartSalePrice_Part
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PartProductRawMaterialHeader_Part')
Begin
	ALTER TABLE dbo.PartProductRawMaterialHeader DROP CONSTRAINT FK_PartProductRawMaterialHeader_Part
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PartProductRawMaterial_Part')
Begin
	ALTER TABLE dbo.PartProductRawMaterialDetail DROP CONSTRAINT FK_PartProductRawMaterial_Part
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_StorageCountDetail_Part')
Begin
	ALTER TABLE dbo.StorageCountDetail DROP CONSTRAINT FK_StorageCountDetail_Part
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_SystemAccCodeDefaultPerPart_Part')
Begin
	ALTER TABLE dbo.SystemAccCodeDefaultPerPart DROP CONSTRAINT FK_SystemAccCodeDefaultPerPart_Part
End
GO

DROP TABLE dbo.Part
GO
EXECUTE sp_rename N'dbo.Tmp_Part', N'Part', 'OBJECT' 
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	PK_Part 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_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 NONCLUSTERED INDEX IDX_Part_Code ON dbo.Part
	(
	Code
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_Part_Barcode ON dbo.Part
	(
	Barcode
	) 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_Part_PartKindRef ON dbo.Part
	(
	PartKindRef
	) INCLUDE (Serial, Remain) 
 WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_Part_PartProfitPercentKindRef ON dbo.Part
	(
	PartProfitPercentKindRef
	) INCLUDE (Serial, Code, Name, StorageRef, GroupRef, BuyUnitPriceInForeignCurrency, PartSalePriceScopeRef, ForeignCurrencyForBuy, UnitRef, OEM, PartKindRef, ShelfNo, PartBuyPriceKindRef, BuyUnitPrice) 
 WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Part_PartProfitPercentKind FOREIGN KEY
	(
	PartProfitPercentKindRef
	) REFERENCES dbo.PartProfitPercentKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Part_PartSalePriceScope FOREIGN KEY
	(
	PartSalePriceScopeRef
	) REFERENCES dbo.PartSalePriceScope
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_Groups FOREIGN KEY
	(
	GroupRef
	) REFERENCES dbo.Groups
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_PartKind FOREIGN KEY
	(
	PartKindRef
	) REFERENCES dbo.PartKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_PartPriceKind FOREIGN KEY
	(
	PartBuyPriceKindRef
	) REFERENCES dbo.PartBuyPriceKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_Storages FOREIGN KEY
	(
	StorageRef
	) REFERENCES dbo.Storages
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_Unit FOREIGN KEY
	(
	UnitRef
	) REFERENCES dbo.Units
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO

If Not Exists(SELECT * FROM sys.objects WHERE name ='PK_UnitWeight')
Begin
	Alter Table dbo.UnitWeight Add Constraint PK_UnitWeight Primary Key Clustered (Serial)
End
GO

ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_UnitWeight FOREIGN KEY
	(
	UnitWeightRef
	) REFERENCES dbo.UnitWeight
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SystemAccCodeDefaultPerPart ADD CONSTRAINT
	FK_SystemAccCodeDefaultPerPart_Part FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.SystemAccCodeDefaultPerPart 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.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.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
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PartSalePrice ADD CONSTRAINT
	FK_PartSalePrice_Part FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.PartSalePrice SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PartGroupsBridge ADD CONSTRAINT
	FK_PartGroupsBridge_Part FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.PartGroupsBridge SET (LOCK_ESCALATION = TABLE)
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 ADD CONSTRAINT
	FK_FactorDetail_Part 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
