Use ATNDBTest
Go

If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'TaskDocDetail' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.TaskDocDetail
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'TaskDocHeader' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.TaskDocHeader
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'TaskDocKind' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.TaskDocKind
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'TaskOption' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.TaskOption
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'Task' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.Task
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'Project' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.Project
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'RelatedPersonPerContact' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.RelatedPersonPerContact
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'RelatedInformationPerContact' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.RelatedInformationPerContact
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'RelatedInformation' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.RelatedInformation
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'RelatedInformationType' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.RelatedInformationType
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'Contact' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.Contact
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'ActivityField' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.ActivityField
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'ContactType' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.ContactType
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'ContactSubGroup' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.ContactSubGroup
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'ContactMainGroup' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.ContactMainGroup
If Exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'IntroductionMethod' AND TABLE_SCHEMA = 'CRM') Drop Table CRM.IntroductionMethod
Go
If Not Exists(Select * From sys.schemas Where Name = 'CRM') Exec('Create Schema CRM')
Go
/*=============================================================================================================*/
Create Table CRM.IntroductionMethod(
	Serial Int Identity(1,1) Not Null,
	Code Int 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,
	Tag Varchar(50) Null)
Go
Alter Table CRM.IntroductionMethod Add Constraint PK_CRM_IntroductionMethod Primary Key Clustered (Serial)
Go
/*=============================================================================================================*/
Create Table CRM.ContactMainGroup(
	Serial Int Identity(1,1) Not Null,
	Code Int 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,
	Tag Varchar(50) Null)
Go
Alter Table CRM.ContactMainGroup Add Constraint PK_CRM_ContactMainGroup Primary Key Clustered (Serial)
Go
/*=============================================================================================================*/
Create Table CRM.ContactSubGroup(
	Serial Int Identity(1,1) Not Null,
	Code Int 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,
	Tag Varchar(50) Null)
Go
Alter Table CRM.ContactSubGroup Add Constraint PK_CRM_ContactSubGroup Primary Key Clustered (Serial)
Go
/*=============================================================================================================*/
Create Table CRM.ContactType(
	Serial Smallint Not Null,
	FADescription Varchar(50) Null,
	ENDescription Varchar(50) Null)
GO
Alter Table CRM.ContactType Add Constraint PK_CRM_ContactType Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
Create Table CRM.ActivityField(
	Serial Int Identity(1,1) Not Null,
	Code Int 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,
	Tag Varchar(50) Null)
Go
Alter Table CRM.ActivityField Add Constraint PK_CRM_ActivityField Primary Key Clustered (Serial)
Go
/*=============================================================================================================*/
Create Table CRM.Contact(
	Serial Bigint Identity(1,1) Not Null, -----
	Code Bigint Null, -------------------------
	PersonOrCompany Bit 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) ---------------------
GO
Alter Table CRM.Contact Add Constraint PK_CRM_Contact Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
Create Table CRM.RelatedInformationType(
	Serial Smallint Not Null,
	FADescription Varchar(50) Null,
	ENDescription Varchar(50) Null)
GO
Alter Table CRM.RelatedInformationType Add Constraint PK_CRM_RelatedInformationType Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
Create Table CRM.RelatedInformation(
	Serial Int Identity(1,1) Not Null,
	Code Int Null,
	RelatedInformationTypeRef Smallint 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,
	Tag Varchar(50) Null)
Go
Alter Table CRM.RelatedInformation Add Constraint PK_CRM_RelatedInformation Primary Key Clustered (Serial)
Go
/*=============================================================================================================*/
Create Table CRM.RelatedInformationPerContact(
	Serial Bigint Identity(1,1) Not Null,
	RelatedInformationRef Int Null,
	ContactRef Bigint Null,
	Value Varchar(300) Null,
	Tag Varchar(50) Null)
Go
Alter Table CRM.RelatedInformationPerContact Add Constraint PK_CRM_RelatedInformationPerContact Primary Key Clustered (Serial)
Go
/*=============================================================================================================*/
Create Table CRM.RelatedPersonPerContact(
	Serial Bigint Identity(1,1) Not Null, --
	ContactRef Bigint Null, ----------------
	Name Varchar(50) Null, -----------------
	Relation Varchar(100) Null, ------------
	ContactTitleRef smallint NULL, ---------
	Data Varchar(300) NULL, ----------------
	Comment Varchar(300) NULL, -------------
	Tag Varchar(50) Null) ------------------
Go
Alter Table CRM.RelatedPersonPerContact Add Constraint PK_CRM_RelatedPersonPerContact Primary Key Clustered (Serial)
Go
/*=============================================================================================================*/
Create Table CRM.Project(
	Serial Bigint Identity(1,1) Not Null, -----
	Code Bigint Null, -------------------------
	ProjectRef Bigint Null, -------------------
	FADescription Varchar(50) Null, -----------
	ENDescription Varchar(50) Null, -----------
	ManagerRef Int Null, ----------------------
	StartDate char(10) Null, ------------------
	DeadlineDate char(10) Null, ---------------
	EstimationDay int Null, -------------------
	EstimationHour int Null, ------------------
	EstimationMinute int Null, ----------------
	EstimationSecond int Null, ----------------
	Comment Varchar(300) Null, ----------------
	AccSecondEntityCodeBookRef Bigint Null, ---
	AccountMaximum Money Null, ----------------
	Active Bit Null, --------------------------
	RecorderRef Int Null, ---------------------
	RecordDate char(10) Null, -----------------
	EditorRef Int Null, -----------------------
	EditDate char(10) Null,	-------------------
	OfficeDetailRef Int Null, -----------------
	Tag Varchar(50) Null) ---------------------
GO
Alter Table CRM.Project Add Constraint PK_CRM_Project Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
Create Table CRM.Task(
	Serial Bigint Identity(1,1) Not Null, -----
	Code Bigint Null, -------------------------
	FADescription Varchar(50) Null, -----------
	ENDescription Varchar(50) Null, -----------
	Active Bit Null, --------------------------
	DurationDay int Null, ---------------------
	DurationHour int Null, --------------------
	DurationMinute int Null, ------------------
	DurationSecond int Null, ------------------
	Cost Money Null, --------------------------
	Comment Varchar(300) Null, ----------------
	RecorderRef Int Null, ---------------------
	RecordDate char(10) Null, -----------------
	EditorRef Int Null, -----------------------
	EditDate char(10) Null,	-------------------
	OfficeDetailRef Int Null, -----------------
	Tag Varchar(50) Null) ---------------------
GO
Alter Table CRM.Task Add Constraint PK_CRM_Task Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
Create Table CRM.TaskOption(
	Serial Bigint Identity(1,1) Not Null, -----
	TaskRef Bigint Not Null, ------------------
	FADescription Varchar(50) Null, -----------
	ENDescription Varchar(50) Null, -----------
	Tag Varchar(50) Null) ---------------------
GO
Alter Table CRM.TaskOption Add Constraint PK_CRM_TaskOption Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
Create Table CRM.TaskDocKind(
	Serial Int Not Null,
	FADescription Varchar(50) Null,
	ENDescription Varchar(50) Null,
	Priority smallint Null)
GO
Alter Table CRM.TaskDocKind Add Constraint PK_TaskDocKind Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
Create Table CRM.TaskDocHeader(
	Serial Bigint Identity(1,1) Not Null, -----
	Code Bigint Null, -------------------------
	TaskDocKindRef Int Not Null, --------------
	ContactRef Bigint Null, -------------------
	ProjectRef Bigint Null, -------------------
	ControlNo Varchar(20) Null, ---------------
	EffectiveDate char(10) Null, --------------
	TotalDurationDay int Null, ----------------
	TotalDurationHour int Null, ---------------
	TotalDurationMinute int Null, -------------
	TotalDurationSecond int Null, -------------
	TotalCost Money Null, ---------------------
	Description Varchar(300) Null, ------------
	RecorderRef Int Null, ---------------------
	RecordDate char(10) Null, -----------------
	EditorRef Int Null, -----------------------
	EditDate char(10) Null,	-------------------
	VerifierRef Int Null, ---------------------
	VerificationDate char(10) Null, -----------
	OfficeDetailRef Int Null, -----------------
	OfficePeriodRef Int Null, -----------------
	RowVersion DateTime, ----------------------
	Tag Varchar(50) Null) ---------------------
GO
Alter Table CRM.TaskDocHeader Add Constraint PK_CRM_TaskDocHeader Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
Create Table CRM.TaskDocDetail(
	Serial Bigint Identity(1,1) Not Null, ----
	TaskDocHeaderRef Bigint Null, ------------
	TaskRef Bigint Not Null, -----------------
	TaskDescription Varchar(Max) Null, -------
	TaskOptionRef Bigint Null, ---------------
	PlannedDate Datetime Null, ---------------
	StartDate Datetime Null, -----------------
	EndDate Datetime Null, -------------------
	DurationDay int Null, --------------------
	DurationHour int Null, -------------------
	DurationMinute int Null, -----------------
	DurationSecond int Null, -----------------
	Cost Money Null, -------------------------
	ExecutorRef Int Null, --------------------
	Progress Smallint Null, ------------------
	Tag Varchar(50) Null) --------------------
GO
Alter Table CRM.TaskDocDetail Add Constraint PK_CRM_TaskDocDetail Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
If Exists(Select * From sys.objects Where name = 'New_Code') Drop Function dbo.New_Code
Go
Create Function dbo.New_Code(@Table_Name Varchar(50),@Kind Varchar(50),	@OfficeDetailRef Int, @OfficePeriodRef	Int)
Returns Bigint
With Encryption
As  
Begin 
	Declare @Code	Bigint

	If @Table_Name = 'Storages'
		Select @Code = (Select Max(Code) From storages Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'Groups'	
		Select @Code = (Select Max(Code) From groups Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'Units'
		Select @Code = (Select Max(Code) From units Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'Warehouse'
		Select @Code = (Select Max(Code) From WarehoUse Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'UnitWeight'
		Select @Code = (Select Max(Code) From UnitWeight  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'Part' 
		Select @Code = (Select Max(Cast(Code As Bigint)) From part Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'GetterOrPayerFactor'
		Select @Code = (Select Max(Code) From GetterOrPayerFactor  Where KindRef = Cast(@Kind As Smallint) And OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'Broker'
		Select @Code = (Select Max(Code) From Broker  Where BrokerKindRef = Cast(@Kind As Smallint) And OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'FactorHeader'
		Select @Code = (Select Max(Code) From FactorHeader  Where FactorKindRef = @Kind And OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	Else If @Table_Name = 'WarehouseVerification'
		Select @Code = (Select Max(WarehouseVerificationCode) From FactorHeader  Where FactorKindRef in (Select Serial From FactorKind Where Stream = @Kind) And OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	Else If @Table_Name = 'Roles'
		Select @Code = (Select Max(Code) From Roles Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'Users'
		Select @Code = (Select Max(Code) From Users Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AccGroupCodeBook'
		Select @Code = (Select Max(Code) From AccGroupCodeBook Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AccGeneralCodeBook'
		Select @Code = (Select Max(Code) From AccGeneralCodeBook Where AccGroupCodeBookRef = Cast(@kind As Bigint) And OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AccAdjutantL1CodeBook'
		Select @Code = (Select Max(Code) From AccAdjutantL1CodeBook Where AccGeneralCodeBookRef = Cast(@kind As Bigint) And OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AccEntityL1CodeBook'
		Select @Code = (Select Max(Code) From AccEntityL1CodeBook Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AccEntityL2CodeBook'
		Select @Code = (Select Max(Code) From AccEntityL2CodeBook Where AccEntityL1CodeBookRef = Cast(@kind As Bigint) And OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AccSecondEntityCodeBook'
		Select @Code = (Select Max(Code) From AccSecondEntityCodeBook Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AccThirdEntityCodeBook'
		Select @Code = (Select Max(Code) From AccThirdEntityCodeBook Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AccDocumentHeader'
	Begin
		If @kind = 'TempCode'
			Select @Code = (Select Max(TempCode) From AccDocumentHeader Where OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
		Else If @kind = 'FinalCode'
			Select @Code = (Select Max(FinalCode) From AccDocumentHeader Where OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	End
	Else If @Table_Name = 'CashGetPay'
		Select @Code = (Select Max(Code) From CashGetPay  Where CashGetPayKindRef = @Kind And OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	Else If @Table_Name = 'CheckGetPayHeader'
		Select @Code = (Select Max(Code) From CheckGetPayHeader  Where CheckPositionRef = @Kind And OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	Else If @Table_Name = 'Bank'
		Select @Code = (Select Max(Code) From Bank Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AccEntityKind'
		Select @Code = (Select Max(Code) From AccEntityKind Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'BankAccount'
		Select @Code = (Select Max(Code) From BankAccount Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'OfficeDetail'
		Select @Code = (Select Max(Code) From OfficeDetail)
	Else If @Table_Name = 'OfficePeriod'
		Select @Code = (Select Max(Code) From OfficePeriod Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CheckBookHeader'
		Select @Code = (Select Max(Code) From CheckBookHeader Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'StorageCountHeader'
		Select @Code = (Select Max(Code) From StorageCountHeader Where OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	Else If @Table_Name = 'GritProduct'
		Select @Code = (Select Max(Code) From GritProduct Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'GritFactor'
		Select @Code = (Select Max(Code) From GritFactor Where GritFactorKindRef = @Kind And OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	Else If @Table_Name = 'CostBase'
		Select @Code = (Select Max(Code) From CostBase Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CostCenter'
		Select @Code = (Select Max(Code) From CostCenter Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CostCash'
		Select @Code = (Select Max(Code) From CostCash Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CostFunds'
		Select @Code = (Select Max(Code) From CostFunds Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CostCredit'
		Select @Code = (Select Max(Code) From CostCredit Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CostHeader'
		Select @Code = (Select Max(Code) From CostHeader  Where OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	Else If @Table_Name = 'Degree'
		Select @Code = (Select Max(Code) From Degree  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'Getter_Or_Payer_Factor_Category'
		Select @Code = (Select Max(Code) From GetterOrPayerFactorCategory  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'BIReportFile'
		Select @Code = (Select Max(Code) From BIReportFile  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'FactorCategory'
		Select @Code = (Select Max(Code) From FactorCategory  Where Kind = Cast(@Kind As Smallint) And OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'PAY.JobCategory'
		Select @Code = (Select Max(Code) From PAY.JobCategory  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'PAY.Job'
		Select @Code = (Select Max(Code) From PAY.Job  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'PAY.JobLocation'
		Select @Code = (Select Max(Code) From PAY.JobLocation  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'PAY.LoanType'
		Select @Code = (Select Max(Code) From PAY.LoanType  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'PAY.TaxGroup'
		Select @Code = (Select Max(Code) From PAY.TaxGroup  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'PAY.SSIOrg'
		Select @Code = (Select Max(Code) From PAY.SSIOrg  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'PAY.SPIOrg'
		Select @Code = (Select Max(Code) From PAY.SPIOrg  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'PAY.EmployeeTaxOrg'
		Select @Code = (Select Max(Code) From PAY.EmployeeTaxOrg  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'Employee' 
		Select @Code = (Select Max(Cast(Code As Bigint)) From PAY.Employee Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'PAY.Contract'
		Select @Code = (Select Max(ContractNumber) From PAY.Contract  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'PAY.TaxTableHeader'
		Select @Code = (Select Max(Code) From PAY.TaxTableHeader  Where OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	Else If @Table_Name = 'PaymentComputingHeader'
		Select @Code = (Select Max(Code) From PAY.PaymentComputingHeader  Where OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	Else If @Table_Name = 'AST.Collectors'
		Select @Code = (Select Max(Code) From AST.Collectors  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AST.AssetType'
		Select @Code = (Select Max(Code) From AST.AssetType  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AST.AssetGroup'
		Select @Code = (Select Max(Code) From AST.AssetGroup  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AST.Asset'
		Select @Code = (Select Max(Code) From AST.Asset  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'AssetDocHeader'
		Select @Code = (Select Max(Code) From AST.AssetDocHeader  Where AssetDocKindRef = @Kind And OfficeDetailRef = @OfficeDetailRef And OfficePeriodRef = @OfficePeriodRef)
	Else If @Table_Name = 'CRM.IntroductionMethod'
		Select @Code = (Select Max(Code) From CRM.IntroductionMethod  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CRM.ContactMainGroup'
		Select @Code = (Select Max(Code) From CRM.ContactMainGroup  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CRM.ContactSubGroup'
		Select @Code = (Select Max(Code) From CRM.ContactSubGroup  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CRM.ActivityField'
		Select @Code = (Select Max(Code) From CRM.ActivityField  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CRM.Contact'
		Select @Code = (Select Max(Code) From CRM.Contact  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CRM.RelatedInformation'
		Select @Code = (Select Max(Code) From CRM.RelatedInformation  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CRM.Project'
		Select @Code = (Select Max(Code) From CRM.Project  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CRM.Task'
		Select @Code = (Select Max(Code) From CRM.Task  Where OfficeDetailRef = @OfficeDetailRef)
	Else If @Table_Name = 'CRM.TaskDocHeader'
		Select @Code = (Select Max(Code) From CRM.TaskDocHeader  Where OfficeDetailRef = @OfficeDetailRef)

	If @Code Is Null
		Set  @Code = 1
	Else
		Set  @Code = @Code+1 
	
	Return @Code

End
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
	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) 
End
Go
/*=============================================================================================================*/
--
--
-- ايجاد داده هاي پيش فرص اوليه براي همه دفاتر مالي و در جداول کاربران بيلان
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





