/*
   شنبه, 12 خرداد 1403 - 10:10:22 ق.ظ
   User: 
   Server: TAHERIAN
   Database: ATNDB
   Application: 
*/

Use ATNDBTest
Go
/*=============================================================================================================*/

-- حذف همه کانسترینت های موجود
Declare @ConstraintName Varchar(Max)
Declare @SchemaName Varchar(Max)
Declare @TableName Varchar(Max)
Declare Cursor1 Cursor Local For 
		Select TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME From INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where TABLE_SCHEMA = 'dbo' And TABLE_NAME = 'GetterOrPayerFactor' And Left(Ltrim(Rtrim(CONSTRAINT_NAME)),2) = 'FK'
Open Cursor1
Fetch Next From Cursor1 Into @SchemaName, @TableName, @ConstraintName
While @@FETCH_STATUS = 0
Begin
	Exec('Alter Table ' + @SchemaName + '.' + @TableName + ' Drop Constraint ' + @ConstraintName)		
	Fetch Next From Cursor1 Into @SchemaName, @TableName, @ConstraintName
End
Close Cursor1
Deallocate Cursor1
Go


-- حذف رفرنسها به جدول
If Exists(Select * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Where CONSTRAINT_NAME ='FK_AST_AssetDocHeader_SupplierRef')
	ALTER TABLE AST.AssetDocHeader DROP CONSTRAINT FK_AST_AssetDocHeader_SupplierRef
GO

If Exists(Select * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Where CONSTRAINT_NAME ='FK_CashGetPay_GetterOrPayerFactor')
	ALTER TABLE dbo.CashGetPay DROP CONSTRAINT FK_CashGetPay_GetterOrPayerFactor
GO

If Exists(Select * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Where CONSTRAINT_NAME ='FK_FactorHeader_GetterOrPayerFactor')
	ALTER TABLE dbo.FactorHeader DROP CONSTRAINT FK_FactorHeader_GetterOrPayerFactor
GO

If Exists(Select * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Where CONSTRAINT_NAME ='FK_GritFactor_GetterOrPayerFactor')
	ALTER TABLE dbo.GritFactor DROP CONSTRAINT FK_GritFactor_GetterOrPayerFactor
GO

-- ایجاد جدول جدید
CREATE TABLE dbo.Tmp_GetterOrPayerFactor
	(
	Serial bigint NOT NULL,
	Code bigint NULL,
	GetterOrPayerFactorTypeRef smallint NULL,
	GetterOrPayerFactorPatternRef smallint NULL,
	CityRef int NULL,
	Location varchar(50) NULL,
	PersonOrCompany smallint NULL,
	FAFirstName varchar(50) NULL,
	ENFirstName varchar(50) NULL,
	FALastName varchar(50) NULL,
	ENLastName varchar(50) NULL,
	Branch varchar(50) NULL,
	KindRef smallint NOT NULL,
	GetterOrPayerFactorCategoryRef bigint NULL,
	Active bit NULL,
	Manager varchar(50) NULL,
	DegreeRef smallint NULL,
	Address varchar(300) NULL,
	PostCode varchar(20) NULL,
	Email varchar(50) NULL,
	Website varchar(50) NULL,
	RegNo varchar(50) NULL,
	SSN varchar(50) NULL,
	EconomicCode varchar(50) NULL,
	Tel1 varchar(20) NULL,
	Tel2 varchar(20) NULL,
	Fax varchar(20) NULL,
	Mobile varchar(20) NULL,
	BankName varchar(50) NULL,
	OffshootCode varchar(15) NULL,
	OffshootName varchar(50) NULL,
	BankAccountNo varchar(20) NULL,
	IBAN varchar(30) NULL,
	BankAddress varchar(300) NULL,
	AccEntityL1CodeBookRef bigint NULL,
	AccEntityL2CodeBookRef bigint NULL,
	AccountMaximum bigint NULL,
	SaleProfitPercent decimal(6, 3) NULL,
	SaleRebatePercent decimal(6, 3) NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	EditorRef int NULL,
	EditDate char(10) NULL,
	Description varchar(300) NULL,
	OfficeDetailRef int NULL,
	Tag varchar(50) NULL
	)  ON [PRIMARY]
GO

-- انتقال اطلاعات جدول قدیم به جدید
IF EXISTS(SELECT * FROM dbo.GetterOrPayerFactor)
	 EXEC('INSERT INTO dbo.Tmp_GetterOrPayerFactor (Serial, Code, GetterOrPayerFactorTypeRef, GetterOrPayerFactorPatternRef, CityRef, Location, PersonOrCompany,                    FAFirstName, ENFirstName, FALastName, ENLastName, Branch, KindRef, GetterOrPayerFactorCategoryRef, Active, Manager, DegreeRef, Address, PostCode, Email, Website, RegNo, SSN, EconomicCode, Tel1, Tel2, Fax, Mobile, BankName, OffshootCode, OffshootName, BankAccountNo, IBAN, BankAddress, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, AccountMaximum, SaleProfitPercent, SaleRebatePercent, RecorderRef, RecordDate, EditorRef, EditDate, Description, OfficeDetailRef, Tag)
		                                     SELECT Serial, Code, GetterOrPayerFactorTypeRef, GetterOrPayerFactorPatternRef, CityRef, Location, CONVERT(smallint, PersonOrCompany), FAFirstName, ENFirstName, FALastName, ENLastName, Branch, KindRef, GetterOrPayerFactorCategoryRef, Active, Manager, DegreeRef, Address, PostCode, Email, Website, RegNo, SSN, EconomicCode, Tel1, Tel2, Fax, Mobile, BankName, OffshootCode, OffshootName, BankAccountNo, IBAN, BankAddress, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, AccountMaximum, SaleProfitPercent, SaleRebatePercent, RecorderRef, RecordDate, EditorRef, EditDate, Description, OfficeDetailRef, Tag 
											 FROM dbo.GetterOrPayerFactor')
GO

-- حذف جدول قبلی
DROP TABLE dbo.GetterOrPayerFactor
GO

-- تغییر نام جدول جدید
EXECUTE sp_rename N'dbo.Tmp_GetterOrPayerFactor', N'GetterOrPayerFactor', 'OBJECT' 
GO

-- آپدیت مقادیر جدول مشتریان
Update 
	GetterOrPayerFactor
Set
	PersonOrCompany = Case When PersonOrCompany = 0 Then 1
						   When PersonOrCompany = 1 Then 2
					  Else Null End

