USE [Build_File]
GO

/****** Object:  StoredProcedure [dbo].[sp_Bui_Search_Doctor]    Script 

Date: 05/02/2012 18:33:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_Bui_Search_Doctor]



(@SearchCriterion1 as varchar(30), @SearchCriterion2 as varchar(30), 

 @SearchCriterion3 as varchar(30), @Type as varchar(1))



AS



If (@Type = '1')

   Select  A.*, B.Specialization, B2.Specialization as 

Specialization2, B3.Specialization as Specialization3, C.Category, 

	--	V.Company [Vendor] 

		'' [Vendor] 

  from tbcoDoctor A 

	Left Outer Join tbcoSpecialization B 

		On A.SpecializationID = B.SpecializationID 

	Left Outer Join tbcoSpecialization B2

		On A.SpecializationID2 = B2.SpecializationID

    Left Outer Join tbcoSpecialization B3
 
		On A.SpecializationID3 = B3.SpecializationID 

	Left Outer Join tbCoDocCategory C

		On A.CategoryID = C.CategoryID

	--Left Outer Join Accounting..Vend V

		--On A.VendorID = V.VendorID

	where ltrim(rtrim(doctorid)) = @SearchCriterion1

else

If (@Type = '2')

   Select distinct ltrim(rtrim(isnull(lastname,''))) + ', ' + 

ltrim(rtrim(isnull(firstname,''))) + ' ' + 

ltrim(rtrim(isnull(middlename,'')))   as DoctorName, DoctorID from 

tbcoDoctor 

   where ltrim(rtrim(isnull(LastName,''))) = @SearchCriterion1 

	 and ltrim(rtrim(isnull(FirstName,''))) = @SearchCriterion2

             and ltrim(rtrim(isnull(MiddleName,''))) = 

@SearchCriterion3

else

If (@Type = '3')

   Select  '' as dumm, ltrim(rtrim(isnull(lastname,''))) + ', ' + 

ltrim(rtrim(isnull(firstname,''))) + ' ' + 

ltrim(rtrim(isnull(middlename,'')))   as DoctorName, 

             DoctorID, License, taxnumber, PMCC

   from tbcoDoctor

   order by Doctorname

else

If (@Type = '4')

   Select  distinct '' as dumm,  ltrim(rtrim(isnull(A.lastname,''))) + 

', ' + ltrim(rtrim(isnull(A.firstname,''))) + ' ' + 

ltrim(rtrim(isnull(A.middlename,'')))   as DoctorName, 

             A.DoctorID, A.License, A.taxnumber, (case when A.status = 

'I' then 'Inactive' else 'Active' End) as status, 

A.pmcc,B.Specialization,C.Category,

             A.SchedDay1 as Monday,  A.SchedDay2 as Tuesday,  

A.SchedDay3 as Wednesday,  A.SchedDay4 as Thursday,  A.SchedDay5 as 

Friday, 

             A.SchedDay6 as Saturday,  A.SchedDay7 as Sunday

   from tbcoDoctor A Left Outer Join tbcoSpecialization B

          On A.SpecializationID = B.SpecializationID 

          Left Outer Join tbCoDocCategory C

         On A.CategoryID = C.CategoryID



   order by Doctorname







GO