/* 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 TABLE dbo.AccDocItem
	DROP CONSTRAINT FK_AccDocItem_AccDetail
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT FK_AccDocItem_AccDetail1
GO
COMMIT

BEGIN TRANSACTION
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT FK_AccDocItem_AccTotal
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT FK_AccDocItem_AccDocHeader
GO
COMMIT

 BEGIN TRANSACTION
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT FK_AccDocItem_AccControl
GO
COMMIT

BEGIN TRANSACTION
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT DF_AccDocItem_Credit
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT DF_AccDocItem_Debit
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT DF_AccDocItem_ShowOrder
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT DF_AccDocItem_RelationID
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT DF_AccDocItem_UpdateDate
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT DF_AccDocItem_Updater
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT DF_AccDocItem_RelationTable
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT DF_AccDocItem_RelationTableID
GO
ALTER TABLE dbo.AccDocItem
	DROP CONSTRAINT DF_AccDocItem_Confirmed
GO
CREATE TABLE dbo.Tmp_AccDocItem
	(
	ID bigint NOT NULL IDENTITY (1, 1),
	HeaderID bigint NOT NULL,
	TotalID bigint NOT NULL,
	ControlID bigint NULL,
	DetailID1 bigint NULL,
	DetailID2 bigint NULL,
	Credit decimal(28, 5) NOT NULL,
	Debit decimal(28, 5) NOT NULL,
	Description nvarchar(3000) NULL,
	ShowOrder int NOT NULL,
	RelationID int NOT NULL,
	UpdateDate datetime NOT NULL,
	Updater bigint NULL,
	RelationTable int NOT NULL,
	RelationTableID bigint NOT NULL,
	Confirmed bigint NULL,
	FormData nvarchar(MAX) NULL,
	CostCenter bigint NULL,
	Project bigint NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_AccDocItem ADD CONSTRAINT
	DF_AccDocItem_Credit DEFAULT ((0)) FOR Credit
GO
ALTER TABLE dbo.Tmp_AccDocItem ADD CONSTRAINT
	DF_AccDocItem_Debit DEFAULT ((0)) FOR Debit
GO
ALTER TABLE dbo.Tmp_AccDocItem ADD CONSTRAINT
	DF_AccDocItem_ShowOrder DEFAULT ((1)) FOR ShowOrder
GO
ALTER TABLE dbo.Tmp_AccDocItem ADD CONSTRAINT
	DF_AccDocItem_RelationID DEFAULT ((0)) FOR RelationID
GO
ALTER TABLE dbo.Tmp_AccDocItem ADD CONSTRAINT
	DF_AccDocItem_UpdateDate DEFAULT (getdate()) FOR UpdateDate
GO
ALTER TABLE dbo.Tmp_AccDocItem ADD CONSTRAINT
	DF_AccDocItem_Updater DEFAULT ((0)) FOR Updater
GO
ALTER TABLE dbo.Tmp_AccDocItem ADD CONSTRAINT
	DF_AccDocItem_RelationTable DEFAULT ((0)) FOR RelationTable
GO
ALTER TABLE dbo.Tmp_AccDocItem ADD CONSTRAINT
	DF_AccDocItem_RelationTableID DEFAULT ((0)) FOR RelationTableID
GO
ALTER TABLE dbo.Tmp_AccDocItem ADD CONSTRAINT
	DF_AccDocItem_Confirmed DEFAULT ((0)) FOR Confirmed
GO
SET IDENTITY_INSERT dbo.Tmp_AccDocItem ON
GO
IF EXISTS(SELECT * FROM dbo.AccDocItem)
	 EXEC('INSERT INTO dbo.Tmp_AccDocItem (ID, HeaderID, TotalID, ControlID, DetailID1, DetailID2, Credit, Debit, Description, ShowOrder, RelationID, UpdateDate, Updater, RelationTable, RelationTableID, Confirmed, FormData, CostCenter, Project)
		SELECT ID, HeaderID, TotalID, ControlID, DetailID1, DetailID2, Credit, Debit, Description, ShowOrder, RelationID, UpdateDate, Updater, RelationTable, RelationTableID, CONVERT(bigint, Confirmed), FormData, CostCenter, Project FROM dbo.AccDocItem WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_AccDocItem OFF
GO
ALTER TABLE dbo.AccSellFactorTrucks
	DROP CONSTRAINT FK_AccSellFactorTrucks_AccDocItem
GO
ALTER TABLE dbo.AccChequeOutItemInfo
	DROP CONSTRAINT FK_AccChequeOutItemInfo_AccDocItem
GO
ALTER TABLE dbo.AccChequeOut
	DROP CONSTRAINT FK_AccChequeOut_AccDocItem
GO
ALTER TABLE dbo.AccChequeIn
	DROP CONSTRAINT FK_AccChequeIn_AccDocItem
GO
ALTER TABLE dbo.AccChequeIn
	DROP CONSTRAINT FK_AccChequeIn_AccDocItem1
GO
ALTER TABLE dbo.AccChequeFree
	DROP CONSTRAINT FK_AccChequeFree_AccDocItem
GO
DROP TABLE dbo.AccDocItem
GO
EXECUTE sp_rename N'dbo.Tmp_AccDocItem', N'AccDocItem', 'OBJECT' 
GO
ALTER TABLE dbo.AccDocItem ADD CONSTRAINT
	PK_AccDocItem PRIMARY KEY CLUSTERED 
	(
	ID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX_AccDocItem_Debit ON dbo.AccDocItem
	(
	Debit
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_AccDocItem_Credit ON dbo.AccDocItem
	(
	Credit
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_AccDocItem_RelationTBLID ON dbo.AccDocItem
	(
	RelationTableID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_AccDocItem_RelationTBL ON dbo.AccDocItem
	(
	RelationTable
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_AccDocItem_Value ON dbo.AccDocItem
	(
	Debit,
	Credit
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_HeaderID ON dbo.AccDocItem
	(
	HeaderID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX AccDocItem_HeaderID_ID_RelationID ON dbo.AccDocItem
	(
	HeaderID,
	ID,
	RelationID
	) INCLUDE (TotalID, ControlID, DetailID1, Updater, RelationTable, RelationTableID, Confirmed, DetailID2, Credit, Debit, Description, ShowOrder, UpdateDate) 
 WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.AccDocItem WITH NOCHECK ADD CONSTRAINT
	FK_AccDocItem_AccControl FOREIGN KEY
	(
	ControlID
	) REFERENCES dbo.AccControl
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.AccDocItem
	NOCHECK CONSTRAINT FK_AccDocItem_AccControl
GO
ALTER TABLE dbo.AccDocItem ADD CONSTRAINT
	FK_AccDocItem_AccDocHeader FOREIGN KEY
	(
	HeaderID
	) REFERENCES dbo.AccDocHeader
	(
	ID
	) ON UPDATE  CASCADE 
	 ON DELETE  CASCADE 
	
GO
ALTER TABLE dbo.AccDocItem WITH NOCHECK ADD CONSTRAINT
	FK_AccDocItem_AccTotal FOREIGN KEY
	(
	TotalID
	) REFERENCES dbo.AccTotal
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.AccDocItem
	NOCHECK CONSTRAINT FK_AccDocItem_AccTotal
GO
ALTER TABLE dbo.AccDocItem WITH NOCHECK ADD CONSTRAINT
	FK_AccDocItem_AccDetail FOREIGN KEY
	(
	DetailID1
	) REFERENCES dbo.AccDetail
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.AccDocItem
	NOCHECK CONSTRAINT FK_AccDocItem_AccDetail
GO
ALTER TABLE dbo.AccDocItem WITH NOCHECK ADD CONSTRAINT
	FK_AccDocItem_AccDetail1 FOREIGN KEY
	(
	DetailID2
	) REFERENCES dbo.AccDetail
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.AccDocItem
	NOCHECK CONSTRAINT FK_AccDocItem_AccDetail1
GO
CREATE TRIGGER AccDocItem_LastUpdate
ON dbo.AccDocItem FOR UPDATE 
	AS
	if not (UPDATE(UpdateDate))
		update AccDocItem set UpdateDate=getdate() where id in (select id from inserted)

     	--update AppSetting set TheValue = dbo.StrDate(getdate()) where [Name]='AccDocLastUpdate'
	return
GO
CREATE     TRIGGER AccDocItem_Delete
ON dbo.AccDocItem for DELETE 
	AS
		insert dbo.AccDocItemTemp  select * from deleted
                --Delete from dbo.AccDocHeader where id in (select ID from dbo.AccDocHeader where not ID in (select distinct HeaderID from dbo.AccDocItem) )
              	--update AppSetting set TheValue = dbo.StrDate(getdate()) where [Name]='AccDocLastUpdate'
	return
GO
CREATE TRIGGER AccDocItem_Insert
ON dbo.AccDocItem FOR Insert
	AS
     	--update AppSetting set TheValue = dbo.StrDate(getdate()) where [Name]='AccDocLastUpdate'
	return
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.AccChequeFree ADD CONSTRAINT
	FK_AccChequeFree_AccDocItem FOREIGN KEY
	(
	DocItemID
	) REFERENCES dbo.AccDocItem
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.AccChequeIn ADD CONSTRAINT
	FK_AccChequeIn_AccDocItem FOREIGN KEY
	(
	DocItemID
	) REFERENCES dbo.AccDocItem
	(
	ID
	) ON UPDATE  CASCADE 
	 ON DELETE  CASCADE 
	
GO
ALTER TABLE dbo.AccChequeIn WITH NOCHECK ADD CONSTRAINT
	FK_AccChequeIn_AccDocItem1 FOREIGN KEY
	(
	CashDocItemID
	) REFERENCES dbo.AccDocItem
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.AccChequeIn
	NOCHECK CONSTRAINT FK_AccChequeIn_AccDocItem1
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.AccChequeOut ADD CONSTRAINT
	FK_AccChequeOut_AccDocItem FOREIGN KEY
	(
	DocItemID
	) REFERENCES dbo.AccDocItem
	(
	ID
	) ON UPDATE  CASCADE 
	 ON DELETE  CASCADE 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.AccChequeOutItemInfo ADD CONSTRAINT
	FK_AccChequeOutItemInfo_AccDocItem FOREIGN KEY
	(
	CashDocItemID
	) REFERENCES dbo.AccDocItem
	(
	ID
	) ON UPDATE  CASCADE 
	 ON DELETE  CASCADE 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.AccSellFactorTrucks ADD CONSTRAINT
	FK_AccSellFactorTrucks_AccDocItem FOREIGN KEY
	(
	DocItemID
	) REFERENCES dbo.AccDocItem
	(
	ID
	) ON UPDATE  CASCADE 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
