/*
   چهارشنبه, 24 آبان 16:26:45 ب.ظ
   User: 
   Server: TAHERIAN
   Database: ATNDB
   Application: 
*/

Use ATNDBTest
Go
/*=============================================================================================================*/
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'TaskDocAccCode') Drop Table CRM.TaskDocAccCode
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'TaskDocDetail') Drop Table CRM.TaskDocDetail
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'TaskDocHeader') Drop Table CRM.TaskDocHeader
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'TaskDocKind') Drop Table CRM.TaskDocKind
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'SystemAccCodeDefaultPerTask') Drop Table CRM.SystemAccCodeDefaultPerTask
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'TaskOption') Drop Table CRM.TaskOption
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'WorkDetailTask') Drop Table CRM.WorkDetailTask
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'TaskPriority') Drop Table CRM.TaskPriority
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'WorkAccCode') Drop Table CRM.WorkAccCode
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'WorkDetail') Drop Table CRM.WorkDetail
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'WorkHeader') Drop Table CRM.WorkHeader
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'WorkKind') Drop Table CRM.WorkKind
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'Task') Drop Table CRM.Task
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'TaskType') Drop Table CRM.TaskType
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'TaskProgressState') Drop Table CRM.TaskProgressState
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'SystemAccCodeDefaultPerWorkflow') Drop Table CRM.SystemAccCodeDefaultPerWorkflow
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'Workflow') Drop Table CRM.Workflow
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'WorkflowType') Drop Table CRM.WorkflowType
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'Project') Drop Table CRM.Project
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'RelatedPersonPerContact') Drop Table CRM.RelatedPersonPerContact
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'RelatedInformationPerContact') Drop Table CRM.RelatedInformationPerContact
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'RelatedInformation') Drop Table CRM.RelatedInformation
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'RelatedInformationType') Drop Table CRM.RelatedInformationType
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'Contact') Drop Table CRM.Contact
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'ActivityField') Drop Table CRM.ActivityField
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'ContactType') Drop Table CRM.ContactType
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'ContactSubGroup') Drop Table CRM.ContactSubGroup
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'ContactMainGroup') Drop Table CRM.ContactMainGroup
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'CRM' And TABLE_NAME = 'IntroductionMethod') Drop Table CRM.IntroductionMethod

-- جداول زیر کلید خارجی ندارند و میتوان با کویری حذفشان کرد
Declare @TableName Varchar(Max)
Declare @Schema Varchar(Max)
Declare Tbl_Cursor Cursor Local For 
									Select 
										TABLE_SCHEMA,
										TABLE_NAME
									From 
										INFORMATION_SCHEMA.TABLES 
									Where 
										TABLE_TYPE = 'BASE TABLE' And 
										TABLE_SCHEMA In ('CRM')
Open Tbl_Cursor
Fetch Next From Tbl_Cursor Into @Schema, @TableName
While @@FETCH_STATUS = 0
Begin
	Exec('Drop Table ' + @Schema + '.' + @TableName)		
	Fetch Next From Tbl_Cursor Into @Schema, @TableName
End
Close Tbl_Cursor
Deallocate Tbl_Cursor
Go
/*=============================================================================================================*/
/*=============================================================================================================*/
/*=============================================================================================================*/
/*=============================================================================================================*/
/*==================================================== CRM ====================================================*/
/*=============================================================================================================*/
/*=============================================================================================================*/
/*=============================================================================================================*/
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
/*=============================================================================================================*/
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
/*=============================================================================================================*/
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
/*=============================================================================================================*/
Create Table CRM.ContactType(
	Serial Smallint Not Null,
	FADescription Varchar(50) Null,
	ENDescription Varchar(50) Null)
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
/*=============================================================================================================*/
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
/*=============================================================================================================*/
Create Table CRM.RelatedInformationType(
	Serial Smallint Not Null,
	FADescription Varchar(50) Null,
	ENDescription Varchar(50) Null)
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
/*=============================================================================================================*/
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
/*=============================================================================================================*/
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
/*=============================================================================================================*/
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
/*=============================================================================================================*/
Create Table CRM.WorkflowType(
	Serial Int Identity(1,1) Not Null,
	Code Int Null,
	FADescription Varchar(50) Null,
	ENDescription Varchar(50) Null,
	RecorderRef Int Null, 
	RecordDate char(10) Null, 
	EditorRef Int Null, 
	EditDate char(10) Null,	
	OfficeDetailRef Int Null)
GO
/*=============================================================================================================*/
Create Table CRM.Workflow(
	Serial Bigint Identity(1,1) Not Null, ----
	Code Bigint Null, ------------------------
	FADescription Varchar(50) Null, ----------
	ENDescription Varchar(50) Null, ----------
	WorkflowTypeRef Int Not 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
/*=============================================================================================================*/
Create Table CRM.SystemAccCodeDefaultPerWorkflow(
	Serial Bigint Identity(1,1) Not Null, ---------
	WorkflowRef 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
/*=============================================================================================================*/
Create Table CRM.TaskProgressState(
	Serial Smallint Not Null,
	FADescription Varchar(50) Null,
	ENDescription Varchar(50) Null)
GO
/*=============================================================================================================*/
Create Table CRM.Task(
	Serial Bigint Identity(1,1) Not Null, ----
	WorkflowRef Bigint Not Null, -------------
	TaskProgressStateRef Smallint Not Null, --
	FADescription Varchar(50) Null, ----------
	ENDescription Varchar(50) Null, ----------
	ProgressPercent Smallint Null, -----------
	Tag Varchar(50) Null) --------------------
GO
/*=============================================================================================================*/
Create Table CRM.WorkKind(
	Serial Int Not Null,
	FADescription Varchar(50) Null,
	ENDescription Varchar(50) Null,
	Priority smallint Null)
GO
/*=============================================================================================================*/
Create Table CRM.WorkHeader(
	Serial Bigint Identity(1,1) Not Null, ----
	Code Bigint Null, ------------------------
	WorkKindRef Int Not Null, ----------------
	AccDocumentHeaderRef Bigint NULL, --------
	ContactRef Bigint Null, ------------------
	ProjectRef Bigint Null, ------------------
	ControlNo Varchar(20) Null, --------------
	EffectiveDate char(10) Null, -------------
	WorkHeaderPlannedDate char(10) Null, -----
	WorkHeaderStartDate char(10) Null, -------
	WorkHeaderEndDate char(10) Null, ---------
	WorkHeaderDurationDay int Null, ----------
	WorkHeaderDurationHour int Null, ---------
	WorkHeaderDurationMinute int Null, -------
	WorkHeaderDurationSecond int Null, -------
	WorkHeaderProgressPercent Smallint Null, 
	WorkHeaderCost Money Null, ---------------
	Vat Money Null, --------------------------
	Toll Money Null, -------------------------
	RecorderRef Int Null, --------------------
	RecordDate char(10) Null, ----------------
	EditorRef Int Null, ----------------------
	EditDate char(10) Null,	------------------
	VerifierRef Int Null, --------------------
	VerificationDate char(10) Null, ----------
	WorkHeaderComment Varchar(Max) Null, -----
	OfficeDetailRef Int Null, ----------------
	OfficePeriodRef Int Null, ----------------
	RowVersion DateTime, ---------------------
	Tag Varchar(50) Null) --------------------
GO
/*=============================================================================================================*/
Create Table CRM.WorkDetail(
	Serial Bigint Identity(1,1) Not Null, -------
	WorkHeaderRef Bigint Not Null, --------------
	WorkflowRef Bigint Not Null, ----------------
	WorkDetailPlannedDate char(10) Null, --------
	WorkDetailStartDate char(10) Null, ----------
	WorkDetailEndDate char(10) Null, ------------
	WorkDetailDurationDay int Null, -------------
	WorkDetailDurationHour int Null, ------------
	WorkDetailDurationMinute int Null, ----------
	WorkDetailDurationSecond int Null, ----------
	WorkDetailCost Money Null, ------------------
	WorkDetailProgressPercent Smallint Null, ----
	WorkflowProgressPercent Smallint Null, ------
	WorkflowStarterRef Int Null, ----------------
	StartTaskRef Bigint Null, -------------------
	WorkDetailComment Varchar(Max) Null, --------
	Tag Varchar(50) Null) -----------------------
GO
/*=============================================================================================================*/
Create Table CRM.WorkAccCode(
	Serial Bigint Identity(1,1) Not Null, ----------
	WorkHeaderRef Bigint Not Null,
	WorkDetailRef 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
/*=============================================================================================================*/
Create Table CRM.TaskPriority(
	Serial Smallint Not Null,
	FADescription Varchar(10) Null,
	ENDescription Varchar(10) Null)
GO
/*=============================================================================================================*/
Create Table CRM.WorkDetailTask(
	Serial Bigint Identity(1,1) Not Null, --------
	WorkHeaderRef Bigint Not Null, ---------------
	WorkDetailRef Bigint Not Null, ---------------
	TaskRef Bigint Null, -------------------------
	TaskPriorityRef Smallint Null, ---------------
	TaskPlannedDate Char(10) Null, ---------------
	TaskStartDate Char(10) Null, -----------------
	TaskEndDate Char(10) Null, -------------------
	TaskDurationDay Int Null, --------------------
	TaskDurationHour Int Null, -------------------
	TaskDurationMinute Int Null, -----------------
	TaskDurationSecond Int Null, -----------------
	TaskProgressPercent Smallint Null, -----------
	TaskCost Money Null, -------------------------
	TaskExecutorRef Int Null, --------------------
	RepetitionNumber Smallint Null, --------------
	WorkDetailTaskComment Varchar(Max) Null, -----
	RowVersion DateTime, -------------------------
	Tag Varchar(50) Null) -----------------------
GO
/*=============================================================================================================*/















































