/* 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.AccChequeIn
	DROP CONSTRAINT FK_AccChequeIn_AccDocItem
GO
ALTER TABLE dbo.AccChequeIn
	DROP CONSTRAINT FK_AccChequeIn_AccDocItem1
GO
COMMIT

BEGIN TRANSACTION
GO
ALTER TABLE dbo.AccChequeIn
	DROP CONSTRAINT DF_AccChequeIn_Cashed
GO
ALTER TABLE dbo.AccChequeIn
	DROP CONSTRAINT DF_AccChequeIn_UpdateDate
GO
ALTER TABLE dbo.AccChequeIn
	DROP CONSTRAINT DF_AccChequeIn_Updater
GO
CREATE TABLE dbo.Tmp_AccChequeIn
	(
	ID bigint NOT NULL IDENTITY (1, 1),
	DebitTotalID bigint NOT NULL,
	DebitControlID bigint NOT NULL,
	DebitDetailID1 bigint NOT NULL,
	DebitDetailID2 bigint NOT NULL,
	CreditTotalID bigint NOT NULL,
	CreditControlID bigint NOT NULL,
	CreditDetailID1 bigint NOT NULL,
	CreditDetailID2 bigint NOT NULL,
	OwnerTotalID bigint NULL,
	OwnerControlID bigint NULL,
	OwnerDetailID1 bigint NULL,
	OwnerDetailID2 bigint NULL,
	DocItemID bigint NULL,
	DocItemRelationID int NULL,
	BankName nvarchar(100) NOT NULL,
	BankBranch nvarchar(100) NOT NULL,
	ChequeNo bigint NOT NULL,
	ChequeDate datetime NOT NULL,
	ChequeValue decimal(28, 5) NOT NULL,
	Description nvarchar(3000) NULL,
	Status int NOT NULL,
	CashDate datetime NOT NULL,
	CashDocItemID bigint NULL,
	CashItemRelationID int NULL,
	UpdateDate datetime NOT NULL,
	Updater bigint NOT NULL,
	PrintCount int NULL,
	Flags int NULL,
	ReturnDate datetime NULL,
	ReturnDocItemID bigint NULL,
	ReturnItemRelationID int NULL,
	ValueDivStatus int NULL,
	Account nvarchar(20) NULL,
	CostCenter bigint NULL,
	Project bigint NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_AccChequeIn ADD CONSTRAINT
	DF_AccChequeIn_Cashed DEFAULT ((0)) FOR Status
GO
ALTER TABLE dbo.Tmp_AccChequeIn ADD CONSTRAINT
	DF_AccChequeIn_UpdateDate DEFAULT (getdate()) FOR UpdateDate
GO
ALTER TABLE dbo.Tmp_AccChequeIn ADD CONSTRAINT
	DF_AccChequeIn_Updater DEFAULT ((0)) FOR Updater
GO
SET IDENTITY_INSERT dbo.Tmp_AccChequeIn ON
GO
IF EXISTS(SELECT * FROM dbo.AccChequeIn)
	 EXEC('INSERT INTO dbo.Tmp_AccChequeIn (ID, DebitTotalID, DebitControlID, DebitDetailID1, DebitDetailID2, CreditTotalID, CreditControlID, CreditDetailID1, CreditDetailID2, OwnerTotalID, OwnerControlID, OwnerDetailID1, OwnerDetailID2, DocItemID, DocItemRelationID, BankName, BankBranch, ChequeNo, ChequeDate, ChequeValue, Description, Status, CashDate, CashDocItemID, CashItemRelationID, UpdateDate, Updater, PrintCount, Flags, ReturnDate, ReturnDocItemID, ReturnItemRelationID, ValueDivStatus, Account, CostCenter, Project)
		SELECT ID, DebitTotalID, DebitControlID, DebitDetailID1, DebitDetailID2, CreditTotalID, CreditControlID, CreditDetailID1, CreditDetailID2, OwnerTotalID, OwnerControlID, OwnerDetailID1, OwnerDetailID2, DocItemID, DocItemRelationID, BankName, BankBranch, ChequeNo, ChequeDate, ChequeValue, Description, Status, CashDate, CashDocItemID, CashItemRelationID, UpdateDate, Updater, PrintCount, Flags, ReturnDate, ReturnDocItemID, ReturnItemRelationID, ValueDivStatus, Account, CostCenter, Project FROM dbo.AccChequeIn WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_AccChequeIn OFF
GO
DROP TABLE dbo.AccChequeIn
GO
EXECUTE sp_rename N'dbo.Tmp_AccChequeIn', N'AccChequeIn', 'OBJECT' 
GO
ALTER TABLE dbo.AccChequeIn ADD CONSTRAINT
	PK_AccChequeIn 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_AccChequeIn_ReturnID ON dbo.AccChequeIn
	(
	ReturnDocItemID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_AccChequeIn_DocID ON dbo.AccChequeIn
	(
	DocItemID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_AccChequeIn_CashID ON dbo.AccChequeIn
	(
	CashDocItemID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
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
CREATE  TRIGGER AccChequeIn_Delete 
ON dbo.AccChequeIn For DELETE 
	AS
		insert AccChequeInTemp select * from  deleted
      --update AppSetting set TheValue = dbo.StrDate(getdate()) where [Name]='AccChequeInLastUpdate'
	return
GO
CREATE   TRIGGER AccChequeIn_LastUpdate
ON dbo.AccChequeIn FOR UPDATE 
	AS
	if not (UPDATE(UpdateDate))
		update AccChequeIn set UpdateDate=getdate() where id in (select id from inserted)

        --update AppSetting set TheValue = dbo.StrDate(getdate()) where [Name]='AccChequeInLastUpdate'
   return
GO
CREATE   TRIGGER AccChequeIn_Insert
ON dbo.AccChequeIn FOR Insert
	AS
        --update AppSetting set TheValue = dbo.StrDate(getdate()) where [Name]='AccChequeInLastUpdate'
	return
GO
COMMIT
