Use ATNDBTest
Go


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.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_Recorder
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_Editor
GO
ALTER TABLE dbo.Users SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_AccEntityL2CodeBook
GO
ALTER TABLE dbo.AccEntityL2CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_AccEntityL1CodeBook
GO
ALTER TABLE dbo.AccEntityL1CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_Degree
GO
ALTER TABLE dbo.Degree SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_GetterOrPayerFactorKind
GO
ALTER TABLE dbo.GetterOrPayerFactorKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT DF_GetterOrPayerFactor_Active
GO
CREATE TABLE dbo.Tmp_GetterOrPayerFactor
	(
	Serial bigint NOT NULL,
	Code bigint NULL,
	FAName varchar(50) NULL,
	ENName 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
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_GetterOrPayerFactor SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_GetterOrPayerFactor ADD CONSTRAINT
	DF_GetterOrPayerFactor_Active DEFAULT ((0)) FOR Active
GO
IF EXISTS(SELECT * FROM dbo.GetterOrPayerFactor)
	 EXEC('INSERT INTO dbo.Tmp_GetterOrPayerFactor (Serial, Code, FAName, ENName, KindRef, 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)
		SELECT Serial, Code, FAName, ENName, KindRef, 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 FROM dbo.GetterOrPayerFactor WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_GetterOrPayerFactor
GO
ALTER TABLE dbo.GritFactor
	DROP CONSTRAINT FK_GritFactor_GetterOrPayerFactor
GO
ALTER TABLE dbo.FactorHeader
	DROP CONSTRAINT FK_FactorHeader_GetterOrPayerFactor
GO
DROP TABLE dbo.GetterOrPayerFactor
GO
EXECUTE sp_rename N'dbo.Tmp_GetterOrPayerFactor', N'GetterOrPayerFactor', 'OBJECT' 
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	PK_GetterOrPayerFactor PRIMARY KEY CLUSTERED 
	(
	Serial
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_GetterOrPayerFactorKind FOREIGN KEY
	(
	KindRef
	) REFERENCES dbo.GetterOrPayerFactorKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_Degree FOREIGN KEY
	(
	DegreeRef
	) REFERENCES dbo.Degree
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_AccEntityL1CodeBook FOREIGN KEY
	(
	AccEntityL1CodeBookRef
	) REFERENCES dbo.AccEntityL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_AccEntityL2CodeBook FOREIGN KEY
	(
	AccEntityL2CodeBookRef
	) REFERENCES dbo.AccEntityL2CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_Recorder FOREIGN KEY
	(
	RecorderRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_Editor FOREIGN KEY
	(
	EditorRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.FactorHeader ADD CONSTRAINT
	FK_FactorHeader_GetterOrPayerFactor FOREIGN KEY
	(
	GetterOrPayerFactorRef
	) REFERENCES dbo.GetterOrPayerFactor
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.FactorHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GritFactor ADD CONSTRAINT
	FK_GritFactor_GetterOrPayerFactor FOREIGN KEY
	(
	GetterOrPayerFactorRef
	) REFERENCES dbo.GetterOrPayerFactor
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritFactor SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_GetterOrPayerFactor FOREIGN KEY
	(
	GetterOrPayerRef
	) REFERENCES dbo.GetterOrPayerFactor
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

---------------------------------------------------------------------------------------------------------------
-- ایجاد جدول دسته بندی مشتریان
Create Table GetterOrPayerFactorCategory(
	Serial Bigint Not Null,
	Code Bigint Null,
	FADescription Varchar(50) Null,
	ENDescription Varchar(50) Null,
	Comment Varchar(300) Null,
	RecorderRef Int Null,
	RecordDate char(10) Null,
	EditorRef Int Null,
	EditDate char(10) Null,
	OfficeDetailRef Int Null)
GO
Alter Table dbo.GetterOrPayerFactorCategory Add Constraint PK_GetterOrPayerFactorCategory Primary Key Clustered (Serial)
GO
---------------------------------------------------------------------------------------------------------------
-- تولید داده اولیه و پیش فرض برای همه مشتریان
Insert Into GetterOrPayerFactorCategory 
	(Serial, Code, FADescription, ENDescription, Comment, RecorderRef, RecordDate, OfficeDetailRef)
Select 
	ROW_NUMBER() Over (Order By OfficeDetail.serial)  As Serial, 
	dbo.new_Code('Getter_Or_Payer_Factor_Category','',OfficeDetail.serial,'') As Code,
		'گروه یک' As FADescription, 
		'First group' As ENDescription,
		Null As Comment, 
		OfficePeriod.RecorderRef, 
		dbo.Solar_Date_Converter(getdate()) As RecordDate, 
		OfficeDetail.serial As OfficeDetailRef
From 
	OfficeDetail Inner Join OfficePeriod On OfficePeriod.OfficeDetailRef = OfficeDetail.Serial
---------------------------------------------------------------------------------------------------------------
-- برقراری ارتباط بین جدول گروه بندی مشتریان و جدول مشتریان
Update 
	GetterOrPayerFactor
Set
	GetterOrPayerFactorCategoryRef = GetterOrPayerFactorCategory.Serial
From
	GetterOrPayerFactorCategory Inner Join 
	GetterOrPayerFactor On GetterOrPayerFactorCategory.OfficeDetailRef = GetterOrPayerFactor.OfficeDetailRef -- چون در بالا برای هر دفتر مالی فقط یک گروه درست کردیم
Where
	KindRef = 2
/*=============================================================================================================*/
