USE [PATIENT_DATA]
GO
/****** Object:  StoredProcedure [dbo].[sp_Med_DoctorPatientLoad]    Script Date: 01/02/2018 14:37:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_Med_DoctorPatientLoad]  --'2016/01'
 @ReportMonth As VarChar(7) 
AS

Declare @ReportMonthx varchar(7)
Declare @StartMonth VarChar(7)
Declare @LastStartMonth VarChar(7)
Declare @LastReportMonth VarChar(7)
Declare @LastYear VarChar(4)

set @ReportMonthx=@ReportMonth
Set @StartMonth = SubString( @ReportMonthx,1,5) + '01';
Set @LastYear = Convert( VarChar(4), Convert( Int, SubString( @ReportMonthx,1,4) ) - 1 ) ;
Set @LastReportMonth = @LastYear + SubString(@ReportMonthx,5,3) ;
Set @LastStartMonth = SubString( @LastReportMonth, 1, 5 ) + '01' ;




SELECT ISNULL(a.DoctorName,'0') AS Doctor_Name
,ISNULL(a.ThisYear_Month,'0') AS ThisYear_Month
,ISNULL(b.ThisYear_YearToDate,'0') AS ThisYear_YearToDate
,ISNULL(c.LastYear_Month,'0') AS LastYear_Month
,ISNULL(d.LastYear_YearToDate,'0') AS LastYear_YearToDate
 FROM (
SELECT  main.Doctorname
		,count(main.thisyear_month) AS ThisYear_Month

FROM
						(
						SELECT 'DR. ' + Doctor.Lastname + ' ' + Doctor.Firstname +' '+ LEFT(Doctor.Middlename,1) + '.' AS DoctorName
								,Patient.HospNum as [ThisYear_Month]
								,'I' AS [Type]	
						FROM
								PATIENT_DATA..tbpatient Patient
								 JOIN
								BUILD_FILE..tbCoDoctor Doctor
								on Patient.AttendingDr1 =doctor.DoctorID
						WHERE  left(CONVERT(varchar(7),AdmDate,111),7)= @ReportMonth
						UNION ALL
						SELECT 'DR. ' + Doctor.Lastname + ' ' + Doctor.Firstname +' '+ LEFT(Doctor.Middlename,1) + '.' AS Doctor
								,outPatient.HospNum	as [Total Patient]
								,'O' AS [Type]	
						FROM
								PATIENT_DATA..tboutpatient outPatient
								 JOIN
								BUILD_FILE..tbCoDoctor Doctor
								on outPatient.DoctorID1 =doctor.DoctorID

						WHERE  left(CONVERT(varchar(7),AdmDate,111),7)= @ReportMonth
						) main
						GROUP BY MAIN.DoctorName   
) a
--UNION ALL
 LEFT JOIN
(SELECT  main2.Doctorname
		,COUNT(main2.[Total Patient]) AS [ThisYear_YearToDate]

from(
						SELECT 'DR. ' + Doctor.Lastname + ' ' + Doctor.Firstname + ' '+LEFT(Doctor.Middlename,1) + '.' AS DoctorName
								,'' as [ThisYear_Month]
								,patient.HospNum as [Total Patient]
								--,COUNT(Patient.HospNum) as [LastYear_Month]
								--,Count(Patient.HospNum) as [LastYear_YearToDate]
								,'I' AS [Type]		
						FROM
								PATIENT_DATA..tbpatient Patient
								 JOIN
								BUILD_FILE..tbCoDoctor Doctor
								on Patient.AttendingDr1 =doctor.DoctorID
						WHERE  left(CONVERT(varchar(7),AdmDate,111),7)between @StartMonth and @ReportMonth
						UNION ALL
						SELECT 'DR. ' + Doctor.Lastname + ' ' + Doctor.Firstname + ' '+LEFT(Doctor.Middlename,1) + '.' AS Doctor
								,'' as [ThisYear_Month]
								,outPatient.HospNum	as [Total Patient]
								,'O' AS [Type]	
						FROM
								PATIENT_DATA..tboutpatient outPatient
								 JOIN
								BUILD_FILE..tbCoDoctor Doctor
								on outPatient.DoctorID1 =doctor.DoctorID
						WHERE  left(CONVERT(varchar(7),AdmDate,111),7) between @StartMonth and @ReportMonth
						) main2
						GROUP BY main2.DoctorName

)b
ON A.DoctorName=B.DoctorName

 LEFT JOIN
(SELECT  main2.Doctorname
		,COUNT(main2.LastYear_month) AS [LastYear_Month]
from(
						SELECT 'DR. ' + Doctor.Lastname + ' ' + Doctor.Firstname +' '+ LEFT(Doctor.Middlename,1) + '.' AS DoctorName
								,Patient.HospNum as [LastYear_Month]
								,'I' AS [Type]		
						FROM
								PATIENT_DATA..tbpatient Patient
								 JOIN
								BUILD_FILE..tbCoDoctor Doctor
								on Patient.AttendingDr1 =doctor.DoctorID
						WHERE  left(CONVERT(varchar(7),AdmDate,111),7)= @LastStartMonth
						UNION ALL
						SELECT 'DR. ' + Doctor.Lastname + ' ' + Doctor.Firstname +' '+ LEFT(Doctor.Middlename,1) + '.' AS Doctor
								,outPatient.HospNum	as [Total Patient]
								,'O' AS [Type]	
						FROM
								PATIENT_DATA..tboutpatient outPatient
								 JOIN
								BUILD_FILE..tbCoDoctor Doctor
								on outPatient.DoctorID1 =doctor.DoctorID
						WHERE  left(CONVERT(varchar(7),AdmDate,111),7) = @LastStartMonth
						) main2
						GROUP BY main2.DoctorName

)c
ON b.DoctorName=c.DoctorName

 LEFT JOIN
(SELECT  main2.Doctorname
		,COUNT(main2.LastYear_YearToDate) AS [LastYear_YearToDate]
from(
						SELECT 'DR. ' + Doctor.Lastname + ' ' + Doctor.Firstname +' '+ LEFT(Doctor.Middlename,1) + '.' AS DoctorName
								,Patient.HospNum as [LastYear_YearToDate]
								,'I' AS [Type]		
						FROM
								PATIENT_DATA..tbpatient Patient
								 JOIN
								BUILD_FILE..tbCoDoctor Doctor
								on Patient.AttendingDr1 =doctor.DoctorID
						WHERE  left(CONVERT(varchar(7),AdmDate,111),7) between @LastStartMonth and @ReportMonth
						UNION ALL
						SELECT 'DR. ' + Doctor.Lastname + ' ' + Doctor.Firstname +' '+ LEFT(Doctor.Middlename,1) + '.' AS Doctor
								,outPatient.HospNum	as [Total Patient]
								,'O' AS [Type]	
						FROM
								PATIENT_DATA..tboutpatient outPatient
								 JOIN
								BUILD_FILE..tbCoDoctor Doctor
								on outPatient.DoctorID1 =doctor.DoctorID
						WHERE  left(CONVERT(varchar(7),AdmDate,111),7) between @LastStartMonth and @ReportMonth
						) main2
						GROUP BY main2.DoctorName

)d
ON c.DoctorName=d.DoctorName

ORDER BY Doctor_Name
GO




-----------------------------------


USE [PATIENT_DATA]
GO
/****** Object:  StoredProcedure [dbo].[sp_Med_DemographyByProvince]    Script Date: 12/29/2017 10:16:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sp_Med_DemographyByProvince]
@StartDate as varchar(10),
@EndDate as varchar(10),
@Province as varchar(50)

as
--DECLARE 
--@StartDate as varchar(10),
--@EndDate as varchar(10),
--@Province as varchar(50)

--SET @StartDate ='01/01/2017'
--SET @EndDate ='12/30/2017'
--SET @Province='LA UNION'


SELECT  Patient.hospnum
		,patient.IdNum
		,patient.AdmDate
		,patient.DcrDate
		,tbmaster.ZipCode
		,tbmaster.LastName + ', '+ tbmaster.Firstname + ' '+ LEFT(tbmaster.MiddleName, 1) +'.' as [Px Name]
		,Address.Province

FROM
		PATIENT_DATA..tbpatient Patient
		INNER JOIN
		PATIENT_DATA..tbmaster tbmaster
		on patient.HospNum=tbmaster.HospNum
		INNER JOIN
		BUILD_FILE..tbcoAddress Address
		on tbmaster.ZipCode=Address.ZipCode

WHERE
Patient.Dcrdate   between @StartDate and @EndDate  + ' 23:59:59.000'
AND
Address.Province = LTRIM(@Province)

GO


-----------------------
USE [PATIENT_DATA]
GO
/****** Object:  StoredProcedure [dbo].[sp_MedRecSearchProvince]    Script Date: 12/29/2017 1:22:46 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[sp_MedRecSearchProvince] 
    @Search as varchar(50)
AS

--declare @Search as varchar(50)
--set @Search =''

select DISTINCT 
CASE WHEN RTrim(LTrim(Isnull(Province,'')))='MM' then 'MANILA' 
	 WHEN RTrim(LTrim(Isnull(Province,'')))<>'MM' then RTrim(LTrim(Isnull(Province,''))) end as Province
 from BUILD_FILE..tbcoAddress
where Province like @Search + '%' and Province <> '' 
order by Province asc
GO

-------------------------------------

use PATIENT_DATA
GO

ALTER Table tbMedrecSetup
ADD IsAllowDemographyByProvince  bit

GO


-------------------------------------


use PATIENT_DATA
GO

Update tbMedrecSetup set IsAllowDemographyByProvince  ='1'

GO