Use ATNDBTest
Go


/*=============================================================================================================*/
-- اضافه کردن ستون نوع فرایند به جدول فرآیندها
BEGIN TRANSACTION
GO
ALTER TABLE CRM.Task ADD
	TaskTypeRef Smallint NULL
GO
ALTER TABLE CRM.Task SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
/*=============================================================================================================*/
Go
Set Ansi_Nulls On
Go
Set Quoted_Identifier On
Go
If Exists(Select * From sys.procedures Where name = 'CRM_Create_New_Office')Drop Procedure CRM.CRM_Create_New_Office
Go
Create Procedure CRM.CRM_Create_New_Office 
	@RecorderRef Bigint,
	@OfficeDetailRef Bigint
With Encryption
As
Begin
	Declare @TaskSerial Int

	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''),   1 , 'موبایل', 'Mobile', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''),   2 , 'تلفن', 'Tel', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''),   3 , 'فکس', 'Fax', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''),   4 , 'ایمیل', 'Email', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''),   5 , 'تاریخ تولد', 'BirthDate', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''), 100 , 'مدیر', 'Manager', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''), 100 , 'معرف', 'Introduce', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''), 100 , 'آدرس', 'Address', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''), 100 , 'کد پستی', 'PostCode', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''), 100 , 'وب سایت', 'Website', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''), 100 , 'کد اقتصادی', 'EconomicCode', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''), 100 , 'شماره ثبت', 'RegNo', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''), 100 , 'شماره شناسنامه', 'BirthCertificateNo', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.RelatedInformation (Code, RelatedInformationTypeRef, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.RelatedInformation','',@OfficeDetailRef,''), 100 , 'کد ملی', 'SSN', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	-----------------------------------------------------------------------------------------------------------
	Insert Into CRM.IntroductionMethod (Code, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.IntroductionMethod','',@OfficeDetailRef,''), 'بازاریاب', '', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.IntroductionMethod (Code, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.IntroductionMethod','',@OfficeDetailRef,''), 'اینترنت', '', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.IntroductionMethod (Code, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.IntroductionMethod','',@OfficeDetailRef,''), 'روزنامه و مجله', '', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.IntroductionMethod (Code, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.IntroductionMethod','',@OfficeDetailRef,''), 'صدا و سیما', '', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	Insert Into CRM.IntroductionMethod (Code, FADescription, ENDescription, RecorderRef, RecordDate, OfficeDetailRef) values (dbo.new_Code('CRM.IntroductionMethod','',@OfficeDetailRef,''), 'معرفی مشتریان', '', @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef) 
	-----------------------------------------------------------------------------------------------------------
	Insert Into CRM.Task (Code, FADescription, ENDescription, TaskTypeRef, Active, DurationMinute, Cost, RecorderRef, RecordDate, OfficeDetailRef) VALUES (dbo.new_Code('CRM.Task','',@OfficeDetailRef,''), N'ارسال پیامک', N'Send SMS', 1, 1, 5, 1, @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef)
	Select @TaskSerial = Scope_Identity() 
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'در انتظار ارسال', N'Waiting to send')
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'ارسال شد', N'Sent')
	
	Insert Into CRM.Task (Code, FADescription, ENDescription, TaskTypeRef, Active, DurationMinute, Cost, RecorderRef, RecordDate, OfficeDetailRef) VALUES (dbo.new_Code('CRM.Task','',@OfficeDetailRef,''), N'تماس تلفنی', N'Direct call', 2, 1, 10, 1, @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef)
	Select @TaskSerial = Scope_Identity() 
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'در انتظار تماس', N'Waiting for calls')
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'تماس گرفته شد', N'Was called')

	Insert Into CRM.Task (Code, FADescription, ENDescription, TaskTypeRef, Active, DurationMinute, Cost, RecorderRef, RecordDate, OfficeDetailRef) VALUES (dbo.new_Code('CRM.Task','',@OfficeDetailRef,''), N'ارسال فکس', N'Send fax', 3, 1, 5, 1, @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef)
	Select @TaskSerial = Scope_Identity() 
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'در انتظار ارسال', N'Waiting to send')
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'ارسال شد', N'Sent')

	Insert Into CRM.Task (Code, FADescription, ENDescription, TaskTypeRef, Active, DurationMinute, Cost, RecorderRef, RecordDate, OfficeDetailRef) VALUES (dbo.new_Code('CRM.Task','',@OfficeDetailRef,''), N'ارسال ایمیل', N'Send mail', 4, 1, 5, 1, @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef)
	Select @TaskSerial = Scope_Identity() 
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'در انتظار ارسال', N'Waiting to send')
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'ارسال شد', N'Sent')

	Insert Into CRM.Task (Code, FADescription, ENDescription, TaskTypeRef, Active, DurationMinute, Cost, RecorderRef, RecordDate, OfficeDetailRef) VALUES (dbo.new_Code('CRM.Task','',@OfficeDetailRef,''), N'جلسه داخل مجموعه', N'Internal meeting', 5, 1, 30, 1, @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef)
	Select @TaskSerial = Scope_Identity() 
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'در انتظار برگزاری جلسه', N'Waiting for the meeting')
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'جلسه برگزار شد', N'The meeting was held')

	Insert Into CRM.Task (Code, FADescription, ENDescription, TaskTypeRef, Active, DurationMinute, Cost, RecorderRef, RecordDate, OfficeDetailRef) VALUES (dbo.new_Code('CRM.Task','',@OfficeDetailRef,''), N'جلسه در محل مخاطب', N'External meeting', 5, 1, 30, 1, @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef)
	Select @TaskSerial = Scope_Identity() 
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'در انتظار برگزاری جلسه', N'Waiting for the meeting')
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'جلسه برگزار شد', N'The meeting was held')

	Insert Into CRM.Task (Code, FADescription, ENDescription, TaskTypeRef, Active, DurationMinute, Cost, RecorderRef, RecordDate, OfficeDetailRef) VALUES (dbo.new_Code('CRM.Task','',@OfficeDetailRef,''), N'جلسه اینترنتی', N'Internet meeting', 5, 1, 30, 1, @RecorderRef, dbo.Solar_Date_Converter(getdate()), @OfficeDetailRef)
	Select @TaskSerial = Scope_Identity() 
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'در انتظار برگزاری جلسه', N'Waiting for the meeting')
	Insert Into CRM.TaskOption (TaskRef, FADescription, ENDescription) VALUES (@TaskSerial, N'جلسه برگزار شد', N'The meeting was held')
End
Go
/*=============================================================================================================*/
-- عملیات زیر در آپدیت 130 یک بار اجرا شده ولی آن فایل یونیکد فارسی نداشته
-- لذاداده های قبلی را حذف کرده و دوباره انجام می دهیم
Delete From CRM.RelatedInformation
Delete From CRM.IntroductionMethod
Delete From CRM.TaskOption
Delete From CRM.Task

-- ايجاد داده هاي پيش فرص اوليه براي همه دفاتر مالي و در جداول کاربران بيلان
Declare @RecorderRef Int
Declare @OfficeDetailRef Int

Declare Tmp_Cursor2 Cursor Local For Select Serial From OfficeDetail 
Open Tmp_Cursor2
Fetch Next From Tmp_Cursor2 Into @OfficeDetailRef
While @@FETCH_STATUS = 0
Begin
	Select @RecorderRef = RecorderRef From OfficePeriod Where OfficeDetailRef = @OfficeDetailRef
	Exec CRM.CRM_Create_New_Office @RecorderRef, @OfficeDetailRef
	-----------------------------------------------------------------------------------------------------------
	Fetch Next From Tmp_Cursor2 Into @OfficeDetailRef
End
Close Tmp_Cursor2
Deallocate Tmp_Cursor2
/*=============================================================================================================*/
-- ایجاد جدول کدینگ حسابداری اسناد
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'TaskDocAccCode' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.TaskDocAccCode
Go
Create Table CRM.TaskDocAccCode(
	Serial Bigint Identity(1,1) Not Null, ----------
	TaskDocHeaderRef Bigint Not Null,
	TaskDocDetailRef Bigint Null,
	SystemAccCodeDefaultBaseRef Int Not Null,
	DebitAccAdjutantL1CodeBookRef Bigint Null, -----
	DebitAccEntityL1CodeBookRef Bigint Null, -------
	DebitAccEntityL2CodeBookRef Bigint Null, -------
	DebitAccSecondEntityCodeBookRef Bigint Null, ---
	DebitAccThirdEntityCodeBookRef Bigint Null,	----
	CreditAccAdjutantL1CodeBookRef Bigint Null,	----
	CreditAccEntityL1CodeBookRef Bigint Null, ------
	CreditAccEntityL2CodeBookRef Bigint Null, ------
	CreditAccSecondEntityCodeBookRef Bigint Null, --
	CreditAccThirdEntityCodeBookRef Bigint Null) ---
Go
Alter Table CRM.TaskDocAccCode Add Constraint PK_CRM_TaskDocAccCode Primary Key Clustered (Serial)
Go
/*=============================================================================================================*/
-- اضافه کردن ستونهای جدید به جدول سربرگ اسناد فرآیندها
BEGIN TRANSACTION
GO
ALTER TABLE CRM.TaskDocHeader ADD
	AccDocumentHeaderRef bigint NULL,
	Vat Money Null, 
	Toll Money Null,
	TotalProgress int Null
GO
ALTER TABLE CRM.TaskDocHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
/*=============================================================================================================*/
Create Table CRM.SystemAccCodeDefaultPerTask(
	Serial Bigint Identity(1,1) Not Null, ----------
	TaskRef Bigint Not Null,
	SystemAccCodeDefaultBaseRef Int Not Null,
	DebitAccAdjutantL1CodeBookRef Bigint Null, -----
	DebitAccEntityL1CodeBookRef Bigint Null, -------
	DebitAccEntityL2CodeBookRef Bigint Null, -------
	DebitAccSecondEntityCodeBookRef Bigint Null, ---
	DebitAccThirdEntityCodeBookRef Bigint Null,	----
	CreditAccAdjutantL1CodeBookRef Bigint Null,	----
	CreditAccEntityL1CodeBookRef Bigint Null, ------
	CreditAccEntityL2CodeBookRef Bigint Null, ------
	CreditAccSecondEntityCodeBookRef Bigint Null, --
	CreditAccThirdEntityCodeBookRef Bigint Null) ---
GO
Alter Table CRM.SystemAccCodeDefaultPerTask Add Constraint PK_CRM_SystemAccCodeDefaultPerTask Primary Key Clustered (Serial)
