Create Function
	[dbo].[Fn_Reception_GetTableDitasLoags]
(
--Declare
		@TypeDateSearch TinyInt = 2,
		@StartDate Varchar(12) = '1404/01/01',
		@EndDate Varchar(12) = '1404/04/21'
)
Returns Table
Return
Select
			---------- شروع اطلاعات پذیرش ------------------------------
			s.[mNumber],
			s.[mPreShowNo],	--اندیس
			s.[mShowNo],	--شماره
			s.[mDate],	--تاریخ و ساعت
			s.[mTime],	--تاریخ و ساعت
			rc.[ResponseDateTime], --تاریخ و ساعت آخرین تغییر در جواب دهی
			Substring(Cast(dl.[DateReg] As Varchar(8)),1,4) +'/'+ Substring(Cast(dl.[DateReg] As Varchar(8)),5,2) +'/'+Substring(Cast(dl.[DateReg] As Varchar(8)),7,2) MaxDateSendDitas,
			s.[mVerified], -- تائید مسئول فنی
			d1.[AdmissionTypeName],	--نوع پذیرش
			s.[mFName], --نام  بیمار
			s.[mLName], --فامیل بیمار
			s.[mMelliCode],	--کدملی
			s.[mMobile], --موبایل
			RTrim(LTrim(Replace(s.[mBirthDate],'/',''))) [mBirthDate], --تاریخ تولد
			Cast(RTrim(LTrim(Replace(s.[mBirthDate],'/',''))) As Int) [BirthDateInt],
			--[dbo].[Fn_DiffrentDateToDay](13630220) DiffrentDateToDay,
			s.[mAge],	--سن
			s.[mAgeUnit], --واحد سن
			--s.[mGender],	--جنسیت,
			d3.[MaritalName],	--وضعیت تاهل
			d4.[CountryName] + ' - ' + d4.[CountryDescription] [CountryNameInfo],	--کشور
			d6.[ReasonName] + ' - ' + d6.[ReasonDescription] [ReasonNameInfo],	--دلیل مراجعه

			d2.[GenderCoding],
			d2.[GenderName],
			d4.[CountryCoding],
			d4.[CountryName],
			d1.[AdmissionTypeCoding],
			d6.[ReasonCoding],
			d6.[ReasonName],
			s.[DitasFlagId],
			---------- پایان اطلاعات پذیرش ------------------------------

			---------- شروع اطلاعات دکتر ------------------------------
			dr.[mID],
			dr.[mFName] [mDoctorName], --نام پزشک
			dr.[mLName] [mDoctorFamili], --نام خانوادگی پزشک
			dr.[mMedicalNo], --کدنظام
			dr.[DitasDoctorsSpecialty],
			d7.[SpecialtyCoding], --کد تخصص
			d7.[SpecialtyName],
			dll.[IsError],
			dll.[compositionUID],
			dll.[errorMessage]
			---------- پایان اطلاعات دکتر ------------------------------
		From [dbo].[tblReception] As s
		Left Join (
					Select
							s.[mSickNo],
							Max(s.[mLastEditedTime]) ResponseDateTime
						From [dbo].[tblRecItems] As s
						Group By s.[mSickNo]
					) As rc On rc.[mSickNo] = s.[mNumber]
		Left Join [dbo].[Ditas_AdmissionType] As d1 On d1.[AdmissionTypeCoding] = s.[DitasAdmissionType]
		Left Join [dbo].[Ditas_Gender] As d2 On d2.[GenderName] = s.[mGender]
		Left Join [dbo].[Ditas_MaritalStatus] As d3 On d3.[MaritalCoding] = s.[DitasMaritalStatus]
		Left Join [dbo].[Ditas_CountriesIso] As d4 On d4.[CountryCoding] = s.[DitasCountriesIso]
		Left Join [dbo].[Ditas_ReasonForEncounter] As d6 On d6.[ReasonCoding] = s.[DitasReasonForEncounter]
		Left Join [dbo].[tblDoctors] As dr On dr.[mID] = s.[mDoctorNo]
		Left Join [dbo].[Ditas_DoctorsSpecialty] As d7 On d7.[SpecialtyCoding] = dr.[DitasDoctorsSpecialty]
		Left Join (
				Select 
					Dr.ReceptionId,
					Max(Dr.Id) MaxId,
					Max(dr.[DateReg]) [DateReg]
				From [dbo].[tbl_DitasResponseLogs] As Dr
				Group By 
					Dr.ReceptionId
				) As dl On dl.ReceptionId = s.mNumber
		Left Join [dbo].[tbl_DitasResponseLogs] As dll On dll.id= dl.MaxId
		Where	s.[mCanceled] = 0
		And		Case
					When @TypeDateSearch = 1 Then Substring(rc.[ResponseDateTime],1,10)
					When @TypeDateSearch = 2 Then Substring(Cast(dl.[DateReg] As Varchar(8)),1,4) +'/'+ Substring(Cast(dl.[DateReg] As Varchar(8)),5,2) +'/'+Substring(Cast(dl.[DateReg] As Varchar(8)),7,2)
					Else s.[mDate]
				End >= @StartDate
		And		Case
					When @TypeDateSearch = 1 Then Substring(rc.[ResponseDateTime],1,10)
					When @TypeDateSearch = 2 Then Substring(Cast(dl.[DateReg] As Varchar(8)),1,4) +'/'+ Substring(Cast(dl.[DateReg] As Varchar(8)),5,2) +'/'+Substring(Cast(dl.[DateReg] As Varchar(8)),7,2)
					Else s.[mDate]
				End <= @EndDate
		And		dl.[MaxId] Is Not Null