/*
   شنبه, 12 اردیبهشت 140512:40:47 ب.ظ
   User: 
   Server: TEST2\SQL28
   Database: kamel dan 2015
   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 TABLE dbo.AccFactor
	DROP CONSTRAINT DF_AccFactor_UpdateDate
GO
ALTER TABLE dbo.AccFactor
	DROP CONSTRAINT DF_AccFactor_Updater
GO
ALTER TABLE dbo.AccFactor
	DROP CONSTRAINT DF_AccFactor_IsPermanent
GO
ALTER TABLE dbo.AccFactor
	DROP CONSTRAINT DF_AccFactor_IsFalsi
GO
ALTER TABLE dbo.AccFactor
	DROP CONSTRAINT DF_AccFactor_Zir5Darsad
GO
ALTER TABLE dbo.AccFactor
	DROP CONSTRAINT DF_AccFactor_HaghAmalKari
GO
ALTER TABLE dbo.AccFactor
	DROP CONSTRAINT DF_AccFactor_TaxIns
GO
CREATE TABLE dbo.Tmp_AccFactor
	(
	ID bigint NOT NULL IDENTITY (1, 1),
	Type smallint NULL,
	Number bigint NOT NULL,
	UserNumber bigint NOT NULL,
	Date datetime NOT NULL,
	SeriallID bigint NOT NULL,
	PersonTotalID bigint NOT NULL,
	PersonControlID bigint NOT NULL,
	PersonDetailID1 bigint NOT NULL,
	PersonDetailID2 bigint NOT NULL,
	PersonAddress nvarchar(500) NULL,
	PersonFinancialCode nchar(20) NULL,
	PersonTel nvarchar(250) NULL,
	PersonFax nvarchar(250) NULL,
	PersonEmail nvarchar(250) NULL,
	PersonWeb nvarchar(250) NULL,
	Description nvarchar(4000) NULL,
	Discont decimal(28, 5) NOT NULL,
	DiscontIsPercent bit NOT NULL,
	PayType bit NULL,
	UpdateDate datetime NOT NULL,
	Updater bigint NOT NULL,
	DiscontTotalID bigint NULL,
	DiscontControlID bigint NULL,
	DiscontDetailID1 bigint NULL,
	DiscontDetailID2 bigint NULL,
	WarehouseID bigint NULL,
	WareOutID bigint NULL,
	FromWarehouse bit NULL,
	Avarez decimal(28, 5) NULL,
	AvarezIsPercent bit NULL,
	AvarezTotalID bigint NULL,
	AvarezControlID bigint NULL,
	AvarezDetailID1 bigint NULL,
	AvarezDetailID2 bigint NULL,
	Haml decimal(28, 5) NULL,
	HamlIsPercent bit NULL,
	HamlTotalID bigint NULL,
	HamlControlID bigint NULL,
	HamlDetailID1 bigint NULL,
	HamlDetailID2 bigint NULL,
	PayTime int NULL,
	Paid bit NULL,
	IsPermanent bit NULL,
	VizitorTotalID bigint NULL,
	VizitorControlID bigint NULL,
	VizitorDetailID1 bigint NULL,
	VizitorDetailID2 bigint NULL,
	SellType int NULL,
	PersonCodePosti nchar(15) NULL,
	PersonSabt nchar(20) NULL,
	PersonShenase nchar(20) NULL,
	CostCenter bigint NULL,
	Project bigint NULL,
	OrderID bigint NULL,
	IsFalsi bit NOT NULL,
	Zir5Darsad bit NOT NULL,
	HaghAmalKari bit NOT NULL,
	Type1Code int NULL,
	CityCode char(7) NULL,
	KalaTypeID int NULL,
	PrePayment decimal(28, 5) NULL,
	UID uniqueidentifier NULL,
	TaxUID nvarchar(100) NULL,
	TaxType int NULL,
	TaxIns int NOT NULL,
	Taxid char(22) NULL,
	Irtaxid char(22) NULL,
	IrID bigint NULL,
	Scc bigint NULL,
	Cdcn nvarchar(20) NULL,
	Cdcd datetime NULL,
	Cut char(3) NULL,
	Exr decimal(28, 5) NULL
	)  ON [PRIMARY]
GO
DECLARE @v sql_variant 
SET @v = N'کد گمرک'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_AccFactor', N'COLUMN', N'Cdcn'
GO
DECLARE @v sql_variant 
SET @v = N'شماره کوتاژ'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_AccFactor', N'COLUMN', N'Cdcd'
GO
DECLARE @v sql_variant 
SET @v = N'نوع ارز'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_AccFactor', N'COLUMN', N'Cut'
GO
DECLARE @v sql_variant 
SET @v = N'نرخ برابری ارز'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_AccFactor', N'COLUMN', N'Exr'
GO
ALTER TABLE dbo.Tmp_AccFactor ADD CONSTRAINT
	DF_AccFactor_UpdateDate DEFAULT (getdate()) FOR UpdateDate
GO
ALTER TABLE dbo.Tmp_AccFactor ADD CONSTRAINT
	DF_AccFactor_Updater DEFAULT ((0)) FOR Updater
GO
ALTER TABLE dbo.Tmp_AccFactor ADD CONSTRAINT
	DF_AccFactor_IsPermanent DEFAULT ((0)) FOR IsPermanent
GO
ALTER TABLE dbo.Tmp_AccFactor ADD CONSTRAINT
	DF_AccFactor_IsFalsi DEFAULT ((1)) FOR IsFalsi
GO
ALTER TABLE dbo.Tmp_AccFactor ADD CONSTRAINT
	DF_AccFactor_Zir5Darsad DEFAULT ((0)) FOR Zir5Darsad
GO
ALTER TABLE dbo.Tmp_AccFactor ADD CONSTRAINT
	DF_AccFactor_HaghAmalKari DEFAULT ((0)) FOR HaghAmalKari
GO
ALTER TABLE dbo.Tmp_AccFactor ADD CONSTRAINT
	DF_AccFactor_TaxIns DEFAULT ((1)) FOR TaxIns
GO
SET IDENTITY_INSERT dbo.Tmp_AccFactor ON
GO
IF EXISTS(SELECT * FROM dbo.AccFactor)
	 EXEC('INSERT INTO dbo.Tmp_AccFactor (ID, Type, Number, UserNumber, Date, SeriallID, PersonTotalID, PersonControlID, PersonDetailID1, PersonDetailID2, PersonAddress, PersonFinancialCode, PersonTel, PersonFax, PersonEmail, PersonWeb, Description, Discont, DiscontIsPercent, PayType, UpdateDate, Updater, DiscontTotalID, DiscontControlID, DiscontDetailID1, DiscontDetailID2, WarehouseID, WareOutID, FromWarehouse, Avarez, AvarezIsPercent, AvarezTotalID, AvarezControlID, AvarezDetailID1, AvarezDetailID2, Haml, HamlIsPercent, HamlTotalID, HamlControlID, HamlDetailID1, HamlDetailID2, PayTime, Paid, IsPermanent, VizitorTotalID, VizitorControlID, VizitorDetailID1, VizitorDetailID2, SellType, PersonCodePosti, PersonSabt, PersonShenase, CostCenter, Project, OrderID, IsFalsi, Zir5Darsad, HaghAmalKari, Type1Code, CityCode, KalaTypeID, PrePayment, UID, TaxUID, TaxType, TaxIns, Taxid, Irtaxid, IrID, Scc, Cdcn, Cdcd, Cut, Exr)
		SELECT ID, Type, Number, UserNumber, Date, SeriallID, PersonTotalID, PersonControlID, PersonDetailID1, PersonDetailID2, PersonAddress, PersonFinancialCode, PersonTel, PersonFax, PersonEmail, PersonWeb, Description, Discont, DiscontIsPercent, PayType, UpdateDate, Updater, DiscontTotalID, DiscontControlID, DiscontDetailID1, DiscontDetailID2, WarehouseID, WareOutID, FromWarehouse, Avarez, AvarezIsPercent, AvarezTotalID, AvarezControlID, AvarezDetailID1, AvarezDetailID2, Haml, HamlIsPercent, HamlTotalID, HamlControlID, HamlDetailID1, HamlDetailID2, PayTime, Paid, IsPermanent, VizitorTotalID, VizitorControlID, VizitorDetailID1, VizitorDetailID2, SellType, PersonCodePosti, PersonSabt, PersonShenase, CostCenter, Project, OrderID, IsFalsi, Zir5Darsad, HaghAmalKari, Type1Code, CityCode, KalaTypeID, PrePayment, UID, TaxUID, TaxType, TaxIns, Taxid, Irtaxid, IrID, Scc, Cdcn, Cdcd, Cut, Exr FROM dbo.AccFactor WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_AccFactor OFF
GO
DROP TABLE dbo.AccFactor
GO
EXECUTE sp_rename N'dbo.Tmp_AccFactor', N'AccFactor', 'OBJECT' 
GO
ALTER TABLE dbo.AccFactor ADD CONSTRAINT
	PK_AccFactor PRIMARY KEY CLUSTERED 
	(
	ID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
Create TRIGGER [dbo].[AccFactor_LastUpdate]
ON dbo.AccFactor FOR UPDATE 
	AS
	SET NOCOUNT ON;
	if not (UPDATE(UpdateDate))
		update AccFactor set UpdateDate=getdate() where id in (select id from inserted)

     	--update AppSetting set TheValue = dbo.StrDate(getdate()) where [Name]='AccFactorLastUpdate'
	return
GO
CREATE TRIGGER [dbo].[AccFactor_Delete]
ON dbo.AccFactor for DELETE 
	AS
		insert into dbo.AccFactorTemp  select *,GETDATE() from deleted
	return
GO
CREATE TRIGGER [dbo].[AccFactor_CheckDelete]
ON dbo.AccFactor INSTEAD OF DELETE 
	AS
		DECLARE @TaxUID as nvarchar(100)
		DECLARE @ID as bigint
		DECLARE @Number as bigint
		DECLARE @MSG as [nvarchar](100)
		
		DECLARE DelCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
			select TaxUID, ID, Number from Deleted
		OPEN DelCursor
		FETCH NEXT FROM DelCursor INTO @TaxUID, @ID, @Number
		WHILE @@FETCH_STATUS = 0 
		BEGIN
			if @TaxUID is null or @TaxUID = '' OR @TaxUID = CAST(CAST(0x0 AS UNIQUEIDENTIFIER) AS nvarchar(100))
			BEGIN
				delete From dbo.AccFactor where id=@ID
			END
			else
			BEGIN
				SET @MSG='امکان حذف فاکتور '+Str(@Number)+' ارسال شده به سامانه نیست'
			    RAISERROR(@MSG,16,20) WITH LOG, SETERROR
			END
		FETCH NEXT FROM DelCursor INTO @TaxUID, @ID, @Number
		END
		CLOSE DelCursor
		DEALLOCATE DelCursor
	return
GO
COMMIT
