Alter proc [dbo].[PatientDiagnosis_Get]
@fromdate datetime, @todate datetime,
@patientID nvarchar(max),
@Code nvarchar(max),
@DiagnosisID int,
@pagenumber int, @pagesize int,
@export int
as


create table #tab (patientID nvarchar(max), visitID nvarchar(max), Diagnosis int,
     diagnosistype int, catagory int, IcdName nvarchar(max), Code nvarchar(max), patientName nvarchar(max),
     dtmDOB datetime, AgeSex nvarchar(max), intmobile nvarchar(max), FullAddress nvarchar(max))
if (@DiagnosisID = '' or @DiagnosisID = null) and (@Code = '' or @Code = null)
 begin
 insert into #tab
  select pd.patientID, pd.visitID, pd.Diagnosis, pd.diagnosistype, pd.catagory,
  case when pd.IcdName != '' or pd.IcdName != null then
  pd.IcdName
  else
   case when pd.Code != null or pd.Code != ''
    then
    isnuLL((select diagnosisName from tblICDdiagnosis where Code = pd.Code), 'Code -'+ pd.Code)
    else
     ld.diagnosisName
    end
  end IcdName,
  ISNULL(pd.Code, '') Code , concat(pt.srtfname,' ', pt.strlname) patientName,
  convert(date,dtmDOB) dtmDOB,
  concat( floor(DATEDIFF(Day, dtmDOB, getdate())/365.25),'y/',strGender) AgeSex,
  isnull(intmobile, '') intmobile,
  concat( (select Municipality from tblMunicipality where mID = pt.[VDC/Municipality] and districtid = pt.strDistrict) ,'-',strAddress,', ',
   (select DistrictName from tblDistrict where ID = pt.strDistrict)
   ) as FullAddress
  from tblPatientDiagnosis pd
  left join tblpaitentinfo pt with(nolock) on pd.patientId = pt.patientID
   and pt.patientID = case when @patientID = '' or @patientID = null then pt.patientID else @patientID end
  left join tblLocalDiagnosis ld with(nolock) on ld.diagnosisID = pd.Diagnosis
  where
  convert(date, pd.Entrydate) between convert(date, @fromdate) and convert(date, @todate)
 end
else if (@DiagnosisID = '' or @DiagnosisID = null)
 begin
 insert into #tab
  select pd.patientID, pd.visitID, pd.Diagnosis, pd.diagnosistype, pd.catagory,
  case when pd.IcdName != '' or pd.IcdName != null then
  pd.IcdName
  else
    isnuLL((select diagnosisName from tblICDdiagnosis where Code = pd.Code), 'Code -'+ pd.Code)
  end IcdName,
  ISNULL(pd.Code, '') Code , concat(pt.srtfname,' ', pt.strlname) patientName,
  convert(date,dtmDOB) dtmDOB,
  concat( floor(DATEDIFF(Day, dtmDOB, getdate())/365.25),'y/',strGender) AgeSex,
  isnull(intmobile, '') intmobile,
  concat( (select Municipality from tblMunicipality where mID = pt.[VDC/Municipality] and districtid = pt.strDistrict) ,'-',strAddress,', ',
   (select DistrictName from tblDistrict where ID = pt.strDistrict)
   ) as FullAddress
  from tblPatientDiagnosis pd
  left join tblpaitentinfo pt with(nolock) on pd.patientId = pt.patientID
   and pt.patientID = case when @patientID = '' or @patientID = null then pt.patientID else @patientID end
  where
  --pd.Diagnosis = 0 and
  convert(date, pd.Entrydate) between convert(date, @fromdate) and convert(date, @todate)
  and pd.code = case when @Code = '' or @Code = null then pd.code else @Code end
 end
else if (@Code = '' or @Code = null)
 begin
 insert into #tab
  select pd.patientID, pd.visitID, pd.Diagnosis, pd.diagnosistype, pd.catagory,
  case when pd.IcdName != '' or pd.IcdName != null then
  pd.IcdName
  else
   case when pd.Code != null or pd.Code != ''
    then
    isnuLL((select diagnosisName from tblICDdiagnosis where Code = pd.Code), 'Code -'+ pd.Code)
    else
     ld.diagnosisName
    end
  end IcdName,
  ISNULL(pd.Code, '') Code , concat(pt.srtfname,' ', pt.strlname) patientName,
  convert(date,dtmDOB) dtmDOB,
  concat( floor(DATEDIFF(Day, dtmDOB, getdate())/365.25),'y/',strGender) AgeSex,
  isnull(intmobile, '') intmobile,
  concat( (select Municipality from tblMunicipality where mID = pt.[VDC/Municipality] and districtid = pt.strDistrict) ,'-',strAddress,', ',
   (select DistrictName from tblDistrict where ID = pt.strDistrict)
   ) as FullAddress
  from tblPatientDiagnosis pd
  left join tblpaitentinfo pt with(nolock) on pd.patientId = pt.patientID
   and pt.patientID = case when @patientID = '' or @patientID = null then pt.patientID else @patientID end
  left join tblLocalDiagnosis ld with(nolock) on ld.diagnosisID = pd.Diagnosis
  where
  pd.Diagnosis <> 0 and
  convert(date, pd.Entrydate) between convert(date, @fromdate) and convert(date, @todate)
  and
  pd.Diagnosis = case when @DiagnosisID = '' or @DiagnosisID = null then pd.Diagnosis else @DiagnosisID end
 end
if @export = 0
 Begin
  declare @off int
  set @off = (@pagenumber - 1) * @pagesize

  select * from #tab
  order by patientID
  offset @off rows
  fetch first @pagesize rows only
 end
else
 begin
  select * from #tab
 end

drop table #tab;