/*
   شنبه, 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 = 'CRM' And TABLE_NAME = 'Contact' 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_CRM_WorkHeader_Contact')
	ALTER TABLE CRM.WorkHeader DROP CONSTRAINT FK_CRM_WorkHeader_Contact
GO
If Exists(Select * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Where CONSTRAINT_NAME ='FK_CRM_RelatedInformationPerContact_Contact')
	ALTER TABLE CRM.RelatedInformationPerContact DROP CONSTRAINT FK_CRM_RelatedInformationPerContact_Contact
GO
If Exists(Select * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Where CONSTRAINT_NAME ='FK_CRM_RelatedPersonPerContact_Contact')
	ALTER TABLE CRM.RelatedPersonPerContact DROP CONSTRAINT FK_CRM_RelatedPersonPerContact_Contact
GO


-- ایجاد جدول جدید
CREATE TABLE CRM.Tmp_Contact
	(
	Serial bigint NOT NULL IDENTITY (1, 1),
	Code bigint NULL,
	PersonOrCompany smallint NULL,
	FAFirstName varchar(100) NULL,
	ENFirstName varchar(100) NULL,
	FALastName varchar(100) NULL,
	ENLastName varchar(100) NULL,
	ContactMainGroupRef int NULL,
	ContactSubGroupRef int NULL,
	CityRef int NULL,
	Location varchar(50) NULL,
	ContactTypeRef smallint NOT NULL,
	IntroductionMethodRef int NOT NULL,
	BrokerRef bigint NULL,
	ActivityFieldRef int NULL,
	AccEntityL1CodeBookRef bigint NULL,
	AccEntityL2CodeBookRef bigint NULL,
	AccountMaximum money NULL,
	Active bit NULL,
	Description varchar(300) NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	EditorRef int NULL,
	EditDate char(10) NULL,
	OfficeDetailRef int NULL,
	Tag varchar(50) NULL
	)  ON [PRIMARY]
GO


-- انتقال اطلاعات جدول قدیم به جدید
SET IDENTITY_INSERT CRM.Tmp_Contact ON
GO
IF EXISTS(SELECT * FROM CRM.Contact)
	 EXEC('INSERT INTO CRM.Tmp_Contact (Serial, Code, PersonOrCompany,                    FAFirstName, ENFirstName, FALastName, ENLastName, ContactMainGroupRef, ContactSubGroupRef, CityRef, Location, ContactTypeRef, IntroductionMethodRef, BrokerRef, ActivityFieldRef, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, AccountMaximum, Active, Description, RecorderRef, RecordDate, EditorRef, EditDate, OfficeDetailRef, Tag)
		                         SELECT Serial, Code, CONVERT(smallint, PersonOrCompany), FAFirstName, ENFirstName, FALastName, ENLastName, ContactMainGroupRef, ContactSubGroupRef, CityRef, Location, ContactTypeRef, IntroductionMethodRef, BrokerRef, ActivityFieldRef, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, AccountMaximum, Active, Description, RecorderRef, RecordDate, EditorRef, EditDate, OfficeDetailRef, Tag 
								 FROM CRM.Contact')
GO
SET IDENTITY_INSERT CRM.Tmp_Contact OFF
GO


-- حذف جدول قبلی
DROP TABLE CRM.Contact
GO


-- تغییر نام جدول جدید
EXECUTE sp_rename N'CRM.Tmp_Contact', N'Contact', 'OBJECT' 
GO


-- آپدیت مقادیر جدول مخاطبین
Update 
	CRM.Contact
Set
	PersonOrCompany = Case When PersonOrCompany = 0 Then 1
						   When PersonOrCompany = 1 Then 2
					  Else Null End
