USE STATION

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchORStaff]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[SearchORStaff]
--declare 
@SearchCriteria varchar(15)

--set @SearchCriteria = ''sar''
AS 

SElect  DISTINCT employeeid AS CODE,isnull(lastname,'''') + '' ,'' +isnull(firstname,'''') + '' '' + isnull(middlename,'''') AS [OR STAFF] 
from PASSWORD..tbpasswordmain AS tbpasswordmain 
where originaldepartment = ''29''  
and lastname like @SearchCriteria +''%'' 
order by [OR STAFF]
-- or employeeid = ''SA'') order by [OR STAFF]
' 
END





GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Get_TransactionDate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_Get_TransactionDate]

AS

 Select GETDATE() AS TDATE
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Reindex_DBase]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Reindex_DBase] AS

Declare All_Tables cursor for
select name from sysobjects where objectproperty(id, ''IsTable'') = 1
AND name LIKE ''tb%'';

Declare @strTable as varchar(100);

Open All_Tables;

Fetch Next from All_Tables
    Into @strTable;
    Dbcc dbreindex(@strTable);

Begin Transaction

  While @@Fetch_Status = 0
  Begin
    Fetch Next from All_Tables
    Into @strTable;
    Dbcc dbreindex(@strTable);
  end;

  If @@error <> 0
    Begin
      goto Abort_Save;
    End;

  Commit Transaction;
  Close All_Tables;
  Deallocate All_Tables;
  Return 0;

Abort_Save:
  Rollback Transaction;
  Return -10001;



' 
END






GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateSchedules]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure [dbo].[sp_UpdateSchedules]
	@Status as varchar(1),
	@ProcessID as varchar(10),
	@TransNum as varchar(5)

As

If @Status = ''C'' 
	Begin
		Update Schedule..tbOrSchedules
		Set RecordStatus = @Status,
		ProcessBy = @ProcessID,
		ProcessDate = getdate()
		Where TransNum = @TransNum
	End
Else
	Begin
		Update Schedule..tbOrSchedules
		Set RecordStatus = @Status,
		RevokeBy = @ProcessID,
		RevokeDate = getdate()
		Where TransNum = @TransNum
	End




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_List_Of_ConfirmedSchedules_New]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'









CREATE Procedure [dbo].[sp_List_Of_ConfirmedSchedules_New]
--
AS 
declare @DateToday as varchar(10)
Set @DateToday = (Select convert(varchar(10),getdate(),101))

Select Distinct 
	[TIME],
	case when Surgeon = ''DR. '' then '''' else surgeon end SURGEON,
	case when Anesthesiologist = ''DR. '' then '''' else Anesthesiologist end ANESTHESIOLOGIST,
	PATIENTNAME,ROOM,PROCEDURENAME,
	case when ASSTDOCTOR = ''DR. '' then '''' else ASSTDOCTOR end ASSTDOCTOR,
	case when ltrim(rtrim(CNurse)) = '''' then '''' else CNurse end CNURSE,
	case when ltrim(rtrim(SNurse)) = '''' then '''' else SNurse end SNURSE,
	ORROOM,[Status],[Transnum],TransNum,ScheduleDate
From 
	(
	Select  Distinct 
			Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)) as [Time],
			''DR. '' + Max(Isnull(tbCoDoctor.LastName,'''')) as Surgeon,
			''DR. '' + Max(Isnull(tbCoDoctor2.LastName,'''')) as Anesthesiologist,
			Max(Isnull([Name],'''')) as PatientName,
	--		Convert(varchar(20),Details.Scheduledate,0) as [SCHEDULEDATE/TIME],
			Max(Isnull(Details.RoomID,'''')) as Room,
			Max(Isnull(Details.Procedurename,'''')) as ProcedureName,
			Max(Isnull(tbPasswordMaster.lastname,''''))+'', ''+ Max(Isnull(Upper(Substring(tbPasswordMaster.FirstName,1,1)),'''')) as CNurse,
			Max(Isnull(tbPasswordMaster2.lastname,''''))+'', ''+ Max(Isnull(Upper(Substring(tbPasswordMaster2.FirstName,1,1)),'''')) as SNurse,
			Max(Isnull(Details.OrRoomID,'''')) as ORRoom,
			case Max(isnull(Details.Recordstatus,''''))
				when '''' then ''Pending''
				when ''C'' then ''Confirmed''
				when ''R'' then ''Revoked''
				else ''''
			END As Status, Details.TransNum,
			Max(Isnull(ScheduleDate,'''')) as ScheduleDate,
			''DR. '' + Max(Isnull(tbCoDoctorAsst.LastName,'''')) as AsstDoctor
	From Schedule..tbOrSchedules Details
--		Left Outer Join Patient_Data..tbPatient tbPatient On Details.Idnum = tbPatient.Idnum
--		Left Outer Join Patient_Data..tbMaster tbMaster On Details.Hospnum = tbMaster.Hospnum
--		Left Outer Join Build_File..tbCoOtherRevenue tbCoOtherRevenue On Details.ProcedureID = tbCoOtherRevenue.OtherRevenueID and tbCoOtherRevenue.OtherSectionID = ''OL''
		Left Outer Join Password..tbPasswordMaster tbPasswordMaster On Details.CNurse = tbPasswordMaster.EmployeeID
		Left Outer Join Password..tbPasswordMaster tbPasswordMaster2 On Details.SNurse1 = tbPasswordMaster2.EmployeeID
		Left Outer Join Build_File..tbCoDoctor tbCoDoctor On Details.Surgeon = tbCoDoctor.DoctorID --SURGEON(MAIN)
		Left Outer Join Build_File..tbCoDoctor tbCoDoctor2 On Details.Anesthesiologist = tbCoDoctor2.DoctorID --ANEST(MAIN)
		Left Outer Join Build_File..tbCoDoctor tbCoDoctorAsst On Details.AsstDoctorID = tbCoDoctorAsst.DoctorID --ANEST(MAIN)
	Where Details.Scheduledate between @DateToday and @DateToday + '' 23:59:59.99'' and 
			Details.RecordStatus =''C''

	Group by Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)),Details.Transnum--,Details.RoomID,Details.ORRoomID

	UNION ALL

	Select  Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)) as [Time],
			''DR. '' + Max(isnull(tbCoDoctor.LastName,'''')) as Surgeon,
			''DR. '' + Max(isnull(tbCoDoctor2.LastName,'''')) as Anesthesiologist,
			Max(isnull([Name],'''')) as PatientName,
	--		Convert(varchar(20),Details.Scheduledate,0) as [SCHEDULEDATE/TIME],
			Max(Isnull(Details.RoomID,'''')) as Room,
			Max(isnull(Details.Procedurename,'''')) as ProcedureName,
			'''' as CNurse,
			Max(isnull(tbPasswordMaster2.lastname,''''))+'', ''+ Max(isnull(Upper(Substring(tbPasswordMaster2.FirstName,1,1)),'''')) as SNurse,
			Max(Isnull(Details.ORRoomID,'''')) as ORRoom,
			case Max(isnull(Details.Recordstatus,''''))
				when '''' then ''Pending''
				when ''C'' then ''Confirmed''
				when ''R'' then ''Revoked''
				else ''''
			END As Status, Details.TransNum,
			Max(Isnull(ScheduleDate,'''')) as ScheduleDate,
			'''' as AsstDoctor
	From Schedule..tbOrSchedules Details
--		Left Outer Join Patient_Data..tbPatient tbPatient On Details.Idnum = tbPatient.Idnum
--		Left Outer Join Patient_Data..tbMaster tbMaster On Details.Hospnum = tbMaster.Hospnum
--		Left Outer Join Build_File..tbCoOtherRevenue tbCoOtherRevenue On Details.ProcedureID = tbCoOtherRevenue.OtherRevenueID and tbCoOtherRevenue.OtherSectionID = ''OL''
		Left Outer Join Password..tbPasswordMaster tbPasswordMaster2 On Details.SNurse2 = tbPasswordMaster2.EmployeeID
		Left Outer Join Build_File..tbCoDoctor tbCoDoctor On Details.AsstSurgeon1 = tbCoDoctor.DoctorID --SURGEON(ASST1)
		Left Outer Join Build_File..tbCoDoctor tbCoDoctor2 On Details.AsstAnesth1 = tbCoDoctor2.DoctorID --ANEST(ASST1)

	Where Details.Scheduledate between @DateToday and @DateToday + '' 23:59:59.99'' and 
		Details.RecordStatus =''C''
		
	Group by Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)),Details.TransNum--,Details.RoomID,Details.ORRoomID


	UNION ALL
	--Surgeon3,''''
	Select	Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)) as [Time],
			''DR. '' + Max(Isnull(tbCoDoctor.LastName,'''')) as Surgeon,
			''DR. '' as Anesthesiologist,
			Max(isnull([Name],'''')) as PatientName,
			Max(Isnull(Details.RoomID,'''')) as Room,
			Max(isnull(Details.Procedurename,'''')) as ProcedureName,
			'''' as CNurse,
			'''' as SNurse,
			Max(Isnull(Details.ORRoomID,'''')) as ORRoom,
			case Max(isnull(Details.Recordstatus,''''))
				when '''' then ''Pending''
				when ''C'' then ''Confirmed''
				when ''R'' then ''Revoked''
				else ''''
			END As Status, Details.TransNum,
			Max(Isnull(ScheduleDate,'''')) as ScheduleDate,
			'''' as AsstDoctor
	From Schedule..tbOrSchedules Details
--		Left Outer Join Patient_Data..tbPatient tbPatient On Details.Idnum = tbPatient.Idnum
--		Left Outer Join Patient_Data..tbMaster tbMaster On Details.Hospnum = tbMaster.Hospnum
		--Left Outer Join Build_File..tbCoOtherRevenue tbCoOtherRevenue On Details.ProcedureID = tbCoOtherRevenue.OtherRevenueID and tbCoOtherRevenue.OtherSectionID = ''OL''
		Left Outer Join Build_File..tbCoDoctor tbCoDoctor On Details.AsstSurgeon2 = tbCoDoctor.DoctorID --SURGEON(ASST2)
	Where Details.Scheduledate between @DateToday and @DateToday + '' 23:59:59.99'' and 
		Details.RecordStatus =''C''
	Group by Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)),Details.TransNum--,Details.RoomID,Details.ORRoomID

)z
where surgeon <> ''dr. '' --and anesthesiologist <> ''dr. '' --and SNurse <> ''''
Order by ScheduleDate,PatientName,Transnum









' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetORSchedule]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'









CREATE Procedure [dbo].[sp_GetORSchedule]

AS

declare @DateToday as varchar(10)
Set @DateToday = (Select convert(varchar(10),getdate(),101))
Select	[Name],
		ProcedureName,
--		ORRoom.RoomName as Room,
		OrSchedules.RoomID as RoomID,
		OrSchedules.ORRoomID as Room,
		OrSchedules.Surgeon as SurgeonID,
		DoctorS.LastName+'', ''+ DoctorS.FirstName as Surgeon,
		OrSchedules.Anesthesiologist as AnesthesiologistID,
		DoctorA.LastName+'', ''+ DoctorA.FirstName as Anesthesiologist,
--		ScheduleDate,
		convert(varchar(20),scheduledate,0) as ScheduleDate,
		Remarks,
		TransNum,
		CNurse as CNurseID,
		CNurse.LastName+'', ''+CNurse.FirstName as CNurse,
		SNurse1 as SNurseID1,
		SNurse1.LastName+'', ''+SNurse1.FirstName as SNurse1,
		SNurse2 as SNurseID2, 
		SNurse2.LastName+'', ''+SNurse2.FirstName as SNurse2,
		AsstSurgeon1 as AsstSurgeonID1, 
		DoctorS1.LastName+'', ''+DoctorS1.FirstName as AsstSurgeon1,
		AsstSurgeon2 as AsstSurgeonID2, 
		DoctorS2.LastName+'', ''+DoctorS2.FirstName as AsstSurgeon2,
		AsstAnesth1 as AsstAnesthID1, 
		DoctorA1.LastName+'', ''+DoctorA1.FirstName as AsstAnesthesiologist,
		AsstDoctorID as AsstDoctorID,
		DoctorAsst.LastName+'', ''+DoctorAsst.FirstName as AssistantDoctor,
		OrRoom.RoomName as Room,
		OrSchedules.RecordStatus as Status
From Schedule..tbOrSchedules OrSchedules
--	Left Outer Join Schedule..tbORRoom ORRoom on OrSchedules.RoomID = ORRoom.RoomID
	--SURGEON
	Left Outer join BUILD_FILE..tbcoDoctor as DoctorS on OrSchedules.Surgeon = DoctorS.doctorid
	Left Outer join BUILD_FILE..tbcoDoctor as DoctorS1 on OrSchedules.AsstSurgeon1 = DoctorS1.doctorid      	
    Left Outer join BUILD_FILE..tbcoDoctor as DoctorS2 on OrSchedules.AsstSurgeon2 = DoctorS1.doctorid  
	--ANESTHESIOLOGIST
	Left Outer join BUILD_FILE..tbcoDoctor as DoctorA on OrSchedules.Anesthesiologist = DoctorA.doctorid 
	Left Outer join BUILD_FILE..tbcoDoctor as DoctorA1 on OrSchedules.AsstAnesth1 = DoctorA1.doctorid 
	--SCRUB NURSE
	Left Outer join PASSWORD..tbPasswordMaster as SNurse1 on OrSchedules.SNurse1 = SNurse1.EmployeeID 
	Left Outer join PASSWORD..tbPasswordMaster as SNurse2 on OrSchedules.SNurse2 = SNurse2.EmployeeID 
	--CIRCULATING NURSE
	Left Outer join PASSWORD..tbPasswordMaster as CNurse on OrSchedules.CNurse = CNurse.EmployeeID 
	--ASSISTANT DOCTOR
	Left Outer join BUILD_FILE..tbcoDoctor as DoctorAsst on OrSchedules.AsstDoctorID = DoctorAsst.DoctorID
	LEft Outer Join Schedule..tbOrROom as OrRoom On OrSchedules.OrRoomID = OrRoom.RoomID
Where OrSchedules.Scheduledate > = @DateToday
--between @DateToday and @DateToday + '' 23:59:59.99''
Order by [Name]









' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Get_ORSchedule_List]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[sp_Get_ORSchedule_List]
--Declare 
@TransNum varchar(10)
--set @Transnum = 1
AS
Select	[Name],
		ProcedureName,
		OrSchedules.RoomID, 
		ORRoom.RoomName,
		OrSchedules.Surgeon as SurgeonID,
		DoctorS.LastName+'', ''+ DoctorS.FirstName as Surgeon,
		OrSchedules.Anesthesiologist as AnesthesiologistID,
		DoctorA.LastName+'', ''+ DoctorA.FirstName as Anesthesiologist,
		ScheduleDate,
		Remarks,
		TransNum,
		CNurse as CNurseID,
		CNurse.LastName+'', ''+CNurse.FirstName as CNurse,
		SNurse1 as SNurseID1,
		SNurse1.LastName+'', ''+SNurse1.FirstName as SNurse1,
		SNurse2 as SNurseID2, 
		SNurse2.LastName+'', ''+SNurse2.FirstName as SNurse2,
		AsstSurgeon1 as AsstSurgeonID1, 
		DoctorS1.LastName+'', ''+DoctorS1.FirstName as AsstSurgeon1,
		AsstSurgeon2 as AsstSurgeonID2, 
		DoctorS2.LastName+'', ''+DoctorS2.FirstName as AsstSurgeon2,
		AsstAnesth1 as AsstAnesthID1, 
		DoctorA1.LastName+'', ''+DoctorA1.FirstName as AsstAnesthesiologist
From Schedule..tbOrSchedules OrSchedules
	Left Outer Join Schedule..tbORRoom ORRoom on OrSchedules.RoomID = ORRoom.RoomID
	--SURGEON
	Left Outer join BUILD_FILE..tbcoDoctor as DoctorS on OrSchedules.Surgeon = DoctorS.doctorid
	Left Outer join BUILD_FILE..tbcoDoctor as DoctorS1 on OrSchedules.AsstSurgeon1 = DoctorS1.doctorid      	
    Left Outer join BUILD_FILE..tbcoDoctor as DoctorS2 on OrSchedules.AsstSurgeon2 = DoctorS1.doctorid  
	--ANESTHESIOLOGIST
	Left Outer join BUILD_FILE..tbcoDoctor as DoctorA on OrSchedules.Anesthesiologist = DoctorA.doctorid 
	Left Outer join BUILD_FILE..tbcoDoctor as DoctorA1 on OrSchedules.AsstAnesth1 = DoctorA1.doctorid 
	--SCRUB NURSE
	Left Outer join PASSWORD..tbPasswordMaster as SNurse1 on OrSchedules.SNurse1 = SNurse1.EmployeeID 
	Left Outer join PASSWORD..tbPasswordMaster as SNurse2 on OrSchedules.SNurse2 = SNurse2.EmployeeID 
	--CIRCULATING NURSE
	Left Outer join PASSWORD..tbPasswordMaster as CNurse on OrSchedules.CNurse = CNurse.EmployeeID 
Where TransNum = @Transnum



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Save_OR_Schedule]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'






CREATE Procedure [dbo].[Save_OR_Schedule]
@Name as varchar(100),
@ORRoomID as varchar(1),
@Surgeon as varchar(10),
@Anesthesiologist  as varchar(10),
@ScheduleDate as datetime,           
@RequestBy as varchar(10) = NULL,  
@Remarks as varchar(100) = NULL,
@CNurse as varchar(10) = NULL,
@SNurse1 as varchar(10) = NULL,
@SNurse2 as varchar(10) = NULL,
@AsstSurgeon1 as varchar(10) = NULL,
@AsstSurgeon2 as varchar(10) = NULL,
@AsstAnesth1 as varchar(10) = NULL,
@ProcedureName as varchar(100) = NULL,
@RoomID as varchar(50) = NULL,
@RequestDate datetime,
@TransNum as varchar(5) = NULL,
@AsstDocID as varchar(8) = NULL,
@RecordStatus as varchar(1) = NULL


AS 


if not exists (Select TransNum From Schedule..tbOrSchedules where Transnum = @Transnum)
	Insert into Schedule..tbOrSchedules([Name], ORRoomID,Surgeon,Anesthesiologist,ScheduleDate,RequestBy,Remarks,CNurse,SNurse1,SNurse2,AsstSurgeon1,AsstSurgeon2,AsstAnesth1,ProcedureName,RoomID,RequestDate,AsstDoctorID)
	Values(@Name, @ORRoomID, @Surgeon, @Anesthesiologist, @ScheduleDate,@RequestBy,@Remarks,@CNurse,@SNurse1,@SNurse2,@AsstSurgeon1,@AsstSurgeon2,@AsstAnesth1,@ProcedureName,@RoomID,@RequestDate,@AsstDocID)
else
	Update Schedule..tbOrSchedules
	set --[Name],
		ORRoomID = @ORRoomID,
		Surgeon = @Surgeon,
		Anesthesiologist = @Anesthesiologist,
		ScheduleDate = @ScheduleDate,
		Remarks = @Remarks,
		CNurse = @CNurse,
		SNurse1 = @SNurse1,
		SNurse2 = @SNurse2,
		AsstSurgeon1 = @AsstSurgeon1,
		AsstSurgeon2 = @AsstSurgeon2,
		AsstAnesth1 = @AsstAnesth1,
		ProcedureName = @ProcedureName,
		RoomID = @RoomID,
		AsstDoctorID = @AsstDocID
	Where TransNum = @TransNum









' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Update_ScheduleParameters]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_Update_ScheduleParameters]
@Daylength as integer,
@ScheduleIncrement as integer

 AS

BEGIN	
	Update tbORApplication
	Set Daylength = @Daylength,
	       ScheduleIncrement = @ScheduleIncrement ;	
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_AddORRoom]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_AddORRoom]
@RoomName AS Varchar(20)

 AS
	Declare @RoomID AS INT

	BEGIN
		Select @RoomID = RoomID from tbORApplication ;
		Update tbORApplication
		Set RoomID = RoomID +1 ;
	
		Insert tbORRoom	(RoomID,RoomName,RevenueID)
			Values		(@RoomID,@RoomName,''OR'');
	END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Get_ORCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_Get_ORCategory]
@CategoryName AS Varchar(20)

 AS
    
     Select * from tbORCategory
     where CategoryName = @CategoryName

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GET_ORSchedule]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_GET_ORSchedule]
@Report AS Varchar(10)

AS

/*SELECT tbORSchedule.ScheduleId, tbORSchedule.ScheduleDate,
       tbORScheduleDetails.RowId, tbORScheduleDetails.StartTime,
       tbORScheduleDetails.Length, tbORScheduleDetails.Description,
       tbORRoom.RoomName AS RoomName, tbORCategory.CategoryName AS CategoryName
FROM tbORCategory RIGHT JOIN
     (tbORSchedule INNER JOIN (tbORScheduleDetails INNER JOIN tbORRoom ON tbORScheduleDetails.RoomId = tbORRoom.RoomId)
     ON tbORSchedule.ScheduleId = tbORScheduleDetails.ScheduleId) ON tbORCategory.CategoryId = tbORScheduleDetails.CategoryId
Where tbORSchedule.ScheduleDate >= @Report and tbORSchedule.ScheduleDate <= @Report +''  11:59:59 PM''*/

SELECT tbORScheduleDetails.ScheduleId, tbORScheduleDetails.ScheduleDate,
       tbORScheduleDetails.RowId, tbORScheduleDetails.StartTime,
       tbORScheduleDetails.Length, tbORScheduleDetails.Description,
       tbORRoom.RoomName AS RoomName, tbORCategory.CategoryName AS CategoryName
FROM tbORScheduleDetails
 LEFT OUTER JOIN  tbORRoom ON tbORScheduleDetails.RoomId = tbORRoom.RoomId
 LEFT OUTER JOIN tbORCategory  ON tbORCategory.CategoryId = tbORScheduleDetails.CategoryId
Where (tbORScheduleDetails.ScheduleDate >= @Report and tbORScheduleDetails.ScheduleDate <= @Report +''  11:59:59 PM'') and
	tbORScheduleDetails.RecordStatus = ''C'' or tbORScheduleDetails.RecordStatus = ''X''

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Get_ORRoom]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_Get_ORRoom]
@RoomName AS Varchar(20)

 AS
    
     Select * from tbORRoom
     where Roomname = @RoomName



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateORRoom]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_UpdateORRoom]
@RoomName AS Varchar(20),
@RoomID AS int 

AS

BEGIN
	
	Update SCHEDULE..tbORRoom 
             Set Roomname = @RoomName
	Where roomid = @RoomID

END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_List_ORRooms]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[sp_List_ORRooms]

 AS

SELECT distinct ROOMID AS [ROOMID],ROOMNAME AS [ROOM NAME]
FROM TBORROOM
WHERE RevenueID =''OR''
ORDER BY [ROOMID],[ROOM NAME]

' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Revoke_Confirmed_ORSchedule]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_Revoke_Confirmed_ORSchedule]
@IDNum as varchar(10),
@ProcedureID as varchar(4),
@UserID as varchar(10)


AS

		BEGIN
			Update tbORScheduleDetails
			Set	RecordStatus ='''',
				ProcessBy = @UserID,
				ProcessDate = Getdate()
			Where IDNum = @IDNum and ProcedureId = @ProcedureId and RecordStatus =''C''		
		END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Revoke_ORSchedule_Request]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_Revoke_ORSchedule_Request]
@IDNum as varchar(10),
@ProcedureID as varchar(5),
@UserID as varchar(10),
@TransNum As Int =0


AS

		BEGIN
			Update tbORScheduleDetails
			Set	RecordStatus =''R'',
				ProcessBy = @UserID,
				ProcessDate = Getdate()
			Where TransNum = @Transnum and Isnull(RecordStatus,'''') ='''';
				--IDNum = @IDNum and ProcedureId = @ProcedureId and RecordStatus =''''		
		END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Save_ORSchedule]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_Save_ORSchedule]
@RoomID as integer,
@StartTime as datetime,
@Length as integer,
@Description as varchar(250),
@ScheduleDate as datetime,
@IDNum as varchar(10),
@RequestBy as varchar(8)

 AS

	BEGIN
		Insert tbORScheduleDetails	(RoomID,StartTime,Length,Description,ScheduleDate,IDNum,RequestBy,RequestDate)
		Values				(@RoomID,@StartTime,@Length,@Description,@ScheduleDate,@IDNum,@RequestBy,Getdate())
	END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Save_RequestORSchedule]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'






CREATE PROCEDURE [dbo].[sp_Save_RequestORSchedule]
@StartTime as datetime,
@Description as varchar(250),
@ScheduleDate as datetime,
@IDNum as varchar(10),
@RequestBy as varchar(8),
@ProcedureID as varchar(8),
@DoctorID1 as varchar(4),
@DoctorID2 as varchar(4),
@HospNum as varchar(10),
@StationID As varchar(10),
@Remarks As Text = NULL,
@CNurse As varchar(10)= NULL,
@SNurse1 As varchar(10)= NULL,
@SNurse2 As varchar(10)= NULL,
@AsstSurgeon1 As varchar(10)= NULL,
@AsstSurgeon2 As varchar(10)= NULL,
@AsstAnesth1 As varchar(10)= NULL,
@ProcedureName As Varchar(100) = NULL

 AS

	BEGIN
		Insert tbORScheduleDetails	(StartTime,Description,ScheduleDate,IDNum,RequestBy,RequestDate,ProcedureID,DoctorID1,DoctorID2,HospNum,StationId,Remarks,CNurse,SNurse1,SNurse2,AsstSurgeon1,AsstSurgeon2,AsstAnesth1,ProcedureName)
		Values				(@StartTime,@Description,@ScheduleDate,@IDNum,@RequestBy,Getdate(),@ProcedureID,@DoctorID1,@DoctorID2,@HospNum,@StationID,@Remarks,@CNurse,@SNurse1,@SNurse2,@AsstSurgeon1,@AsstSurgeon2,@AsstAnesth1,@Procedurename)
	END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Update_ORSchedule_Request]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_Update_ORSchedule_Request]
@RoomID as integer,
@Length as integer,
@Description as varchar(250)/*,
@ScheduleDate as datetime*/,
@IDNum as varchar(10),
@ProcedureID as varchar(5),
@UserID as varchar(10),
@SurgeonID As Varchar(10)=NULL,
@AnestheID As Varchar(10)=NULL,
@Transnum As Int=0

AS

		BEGIN

			Update tbORScheduleDetails
			Set	RoomID = @RoomID,
				Length = @Length,
				Description = @Description,
				ProcessBy = @UserID,
				ProcessDate = GETDATE(),
				RecordStatus =''C'',
				DoctorID1 = Case Isnull(@SurgeonID,'''')
						When '''' then DoctorID1
						Else @SurgeonID
					       End,
				DoctorID2 = Case Isnull(@AnestheID,'''')
						When '''' then DoctorID1
						Else @AnestheID
					       End
			Where TransNum = @Transnum;
				--IDNum = @IDNum and ProcedureId = @ProcedureId and RecordStatus =''''		      
		END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Update_Schedule]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_Update_Schedule]
@RoomID as integer,
@StartTime as datetime,
@Length as integer,
@Description as varchar(500),
@ScheduleDate as datetime,
@RecordStatus as varchar(1),
@UserID as varchar(8)

 AS


	BEGIN

		Update tbORScheduleDetails
 		Set RoomID = @RoomID,
		       StartTime = @StartTime,
		       Length = @Length,
		       ScheduleDate = @ScheduleDate,
		       RecordStatus = @RecordStatus,
		       ProcessBy = @UserID,
		       ProcessDate = Getdate()

		Where ltrim(rtrim(Description)) = ltrim(rtrim(@Description)) ;
	END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_List_Of_ConfirmedSchedules]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE Procedure [dbo].[sp_List_Of_ConfirmedSchedules]
--
AS 
declare @DateToday as varchar(10)
Set @DateToday = (Select convert(varchar(10),getdate(),101))

Select Distinct 
	[TIME],
	case when Surgeon = ''DR. '' then '''' else surgeon end SURGEON,
	case when Anesthesiologist = ''DR. '' then '''' else Anesthesiologist end ANESTHESIOLOGIST,
	PATIENTNAME,ROOMID,PROCEDURENAME,
	case when ltrim(rtrim(CNurse)) = '''' then '''' else CNurse end CNURSE,
	case when ltrim(rtrim(SNurse)) = '''' then '''' else SNurse end SNURSE,
	ORROOM,[Status],[Transnum],IDnum,ScheduleDate
From 
	(
	Select  Distinct 
			Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)) as [Time],
			''DR. '' + Max(Isnull(tbCoDoctor.LastName,'''')) as Surgeon,
			''DR. '' + Max(Isnull(tbCoDoctor2.LastName,'''')) as Anesthesiologist,
			Max(Isnull(tbMaster.LastName+'', ''+ tbMaster.FirstName,'''')) as PatientName,
	--		Convert(varchar(20),Details.Scheduledate,0) as [SCHEDULEDATE/TIME],
			tbPatient.RoomID as RoomID,
			Max(Isnull(Details.Procedurename,'''')) as ProcedureName,
			Max(Isnull(tbPasswordMaster.lastname,''''))+'', ''+ Max(Isnull(Upper(Substring(tbPasswordMaster.FirstName,1,1)),'''')) as CNurse,
			Max(Isnull(tbPasswordMaster2.lastname,''''))+'', ''+ Max(Isnull(Upper(Substring(tbPasswordMaster2.FirstName,1,1)),'''')) as SNurse,
			Details.RoomID as ORRoom,
			case Max(isnull(Details.Recordstatus,''''))
				when '''' then ''Pending''
				when ''C'' then ''Confirmed''
				when ''R'' then ''Revoked''
				else ''''
			END As Status, Details.TransNum,
			Details.Idnum,Max(Isnull(ScheduleDate,'''')) as ScheduleDate
	From Schedule..tbOrScheduledetails Details
		Left Outer Join Patient_Data..tbPatient tbPatient On Details.Idnum = tbPatient.Idnum
		Left Outer Join Patient_Data..tbMaster tbMaster On Details.Hospnum = tbMaster.Hospnum
		--Left Outer Join Build_File..tbCoOtherRevenue tbCoOtherRevenue On Details.ProcedureID = tbCoOtherRevenue.OtherRevenueID and tbCoOtherRevenue.OtherSectionID = ''OL''
		Left Outer Join Password..tbPasswordMaster tbPasswordMaster On Details.CNurse = tbPasswordMaster.EmployeeID
		Left Outer Join Password..tbPasswordMaster tbPasswordMaster2 On Details.SNurse1 = tbPasswordMaster2.EmployeeID
		Left Outer Join Build_File..tbCoDoctor tbCoDoctor On Details.DoctorID1 = tbCoDoctor.DoctorID --SURGEON(MAIN)
		Left Outer Join Build_File..tbCoDoctor tbCoDoctor2 On Details.DoctorID2 = tbCoDoctor2.DoctorID --ANEST(MAIN)
	Where Details.Scheduledate between @DateToday and @DateToday + '' 23:59:59.99'' and 
			Details.RecordStatus =''C''

	Group by Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)),Details.Idnum,Details.RoomID,
		tbPatient.RoomID,Details.TransNum

	UNION ALL

	Select  Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)) as [Time],
			''DR. '' + Max(isnull(tbCoDoctor.LastName,'''')) as Surgeon,
			''DR. '' + Max(isnull(tbCoDoctor2.LastName,'''')) as Anesthesiologist,
			Max(isnull(tbMaster.LastName,''''))+'', ''+ Max(isnull(tbMaster.FirstName,'''')) as PatientName,
	--		Convert(varchar(20),Details.Scheduledate,0) as [SCHEDULEDATE/TIME],
			tbPatient.RoomID as RoomID,
			Max(isnull(Details.Procedurename,'''')) as ProcedureName,
			'''' as CNurse,
			Max(isnull(tbPasswordMaster2.lastname,''''))+'', ''+ Max(isnull(Upper(Substring(tbPasswordMaster2.FirstName,1,1)),'''')) as SNurse,
			Details.RoomID as ORRoom,
			case Max(isnull(Details.Recordstatus,''''))
				when '''' then ''Pending''
				when ''C'' then ''Confirmed''
				when ''R'' then ''Revoked''
				else ''''
			END As Status, Details.TransNum,
			Details.Idnum,Max(Isnull(ScheduleDate,'''')) as ScheduleDate
	From Schedule..tbOrScheduledetails Details
		Left Outer Join Patient_Data..tbPatient tbPatient On Details.Idnum = tbPatient.Idnum
		Left Outer Join Patient_Data..tbMaster tbMaster On Details.Hospnum = tbMaster.Hospnum
		--Left Outer Join Build_File..tbCoOtherRevenue tbCoOtherRevenue On Details.ProcedureID = tbCoOtherRevenue.OtherRevenueID and tbCoOtherRevenue.OtherSectionID = ''OL''
		Left Outer Join Password..tbPasswordMaster tbPasswordMaster2 On Details.SNurse2 = tbPasswordMaster2.EmployeeID
		Left Outer Join Build_File..tbCoDoctor tbCoDoctor On Details.AsstSurgeon1 = tbCoDoctor.DoctorID --SURGEON(ASST1)
		Left Outer Join Build_File..tbCoDoctor tbCoDoctor2 On Details.AsstAnesth1 = tbCoDoctor2.DoctorID --ANEST(ASST1)

	Where Details.Scheduledate between @DateToday and @DateToday + '' 23:59:59.99'' and 
		Details.RecordStatus =''C''
		
	Group by Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)),Details.Idnum,Details.RoomID,
		tbPatient.RoomID,Details.TransNum


	UNION ALL
	--Surgeon3,''''
	Select	Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)) as [Time],
			''DR. '' + Max(Isnull(tbCoDoctor.LastName,'''')) as Surgeon,
			''DR. '' as Anesthesiologist,
--			Case when Max(isnull(AsstSurgeon2,'''')) = '''' then ''''
--			else ''DR. '' + Max(isnull(tbCoDoctor.LastName,'''')) end as Surgeon,
--			'''' as Anesthesiologist,
			Max(isnull(tbMaster.LastName,''''))+'', ''+ Max(isnull(tbMaster.FirstName,'''')) as PatientName,
	--		Convert(varchar(20),Details.Scheduledate,0) as [SCHEDULEDATE/TIME],
			tbPatient.RoomID as RoomID,
			Max(isnull(Details.Procedurename,'''')) as ProcedureName,
			'''' as CNurse,
			'''' as SNurse,
			Details.RoomID as ORRoom,
			case Max(isnull(Details.Recordstatus,''''))
				when '''' then ''Pending''
				when ''C'' then ''Confirmed''
				when ''R'' then ''Revoked''
				else ''''
			END As Status, Details.TransNum,
			Details.Idnum,Max(Isnull(ScheduleDate,'''')) as ScheduleDate
	From Schedule..tbOrScheduledetails Details
		Left Outer Join Patient_Data..tbPatient tbPatient On Details.Idnum = tbPatient.Idnum
		Left Outer Join Patient_Data..tbMaster tbMaster On Details.Hospnum = tbMaster.Hospnum
		--Left Outer Join Build_File..tbCoOtherRevenue tbCoOtherRevenue On Details.ProcedureID = tbCoOtherRevenue.OtherRevenueID and tbCoOtherRevenue.OtherSectionID = ''OL''
		Left Outer Join Build_File..tbCoDoctor tbCoDoctor On Details.AsstSurgeon2 = tbCoDoctor.DoctorID --SURGEON(ASST2)
	Where Details.Scheduledate between @DateToday and @DateToday + '' 23:59:59.99'' and 
		Details.RecordStatus =''C''
	Group by Ltrim(Substring(Convert(Varchar(20),Details.scheduledate,100),12,9)),Details.Idnum,Details.RoomID,
		tbPatient.RoomID,Details.TransNum


)z
where surgeon <> ''dr. '' and anesthesiologist <> ''dr. '' --and SNurse <> ''''
Order by ScheduleDate,PatientName,Idnum
--group by z.Idnum

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Get_ORScheduleDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_Get_ORScheduleDetails]
@Description as varchar(250)

 AS


	BEGIN

		Select * from tbORScheduleDetails
		Where ltrim(rtrim(Description)) = ltrim(rtrim(@Description)) ;
	END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ListORSchedule_Request]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[sp_ListORSchedule_Request]
@StationID As varchar(10)

AS

select isnull(b.lastname,'''') + '' ,'' + isnull(b.firstname,'''') + '' '' + isnull(b.middlename,'''') as PatientName,e.otherrevenue as [OR Procedure],
		     isnull(c.lastname,'''') + '' ,'' + isnull(c.firstname,'''') + '' '' + isnull(c.middlename,'''')
		        as Surgeon,
		     isnull(d.lastname,'''') + '' ,'' + isnull(d.firstname,'''') + '' '' + isnull(d.middlename,'''')
		        as Anesthesiologist,
		     convert(varchar(20),a.scheduledate,0) as [Schedule Date/Time],a.IDNum as [ADM. #],
		a.ProcedureID as ProcID,
		case isnull(a.recordstatus,'''')
			when '''' then ''Pending''
			when ''C'' then ''Confirmed''
			when ''R'' then ''Revoked''
			else ''''
		END As Status,
		A.Transnum
from tborscheduledetails as a
    /* left outer join (select a.idnum,isnull(b.lastname,'''') + '' ,'' + isnull(b.firstname,'''') + '' '' + isnull(b.middlename,'''')
		        as PatientName from PATIENT_DATA..tbpatient as a
		  	left outer join PATIENT_DATA..tbmaster as b
				on a.hospnum = b.hospnum		
			) as b
        on a.idnum = b.idnum*/
     left outer join patient_data..tbMaster as b
	on a.hospnum=b.hospnum 	

     left outer join BUILD_FILE..tbcoDoctor as c
	on a.doctorid1 = c.doctorid      	
     left outer join BUILD_FILE..tbcoDoctor as d
	on a.doctorid2 = d.doctorid      
     left outer join BUILD_FILE..tbcoOtherrevenue as e
	on a.procedureID = e.otherrevenueid	
     left outer join PATIENT_DATA..tbpatient as f
	on a.idnum = f.idnum	
where  isnull(f.dcrdate,'''') ='''' and a.StationID = @StationID
--isnull(recordstatus,'''') =''''
order by PatientName,Status

' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_List_ConfirmedORSchedule]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[sp_List_ConfirmedORSchedule]

AS

select isnull(b.lastname,'''') + '' ,'' + isnull(b.firstname,'''') + '' '' + isnull(b.middlename,'''') as PatientName,e.otherrevenue as [OR Procedure],
		     isnull(c.lastname,'''') + '' ,'' + isnull(c.firstname,'''') + '' '' + isnull(c.middlename,'''')
		        as Surgeon,
		     isnull(d.lastname,'''') + '' ,'' + isnull(d.firstname,'''') + '' '' + isnull(d.middlename,'''')
		        as Anesthesiologist,
		     convert(varchar(20),a.scheduledate,0) as [Schedule Date/Time],a.IDNum as [ADM. #],
		a.ProcedureID as ProcID,
		case isnull(a.recordstatus,'''')
			when '''' then ''Pending''
			when ''C'' then ''Confirmed''
			when ''R'' then ''Revoked''
			else ''''
		END As Status
from tborscheduledetails as a
    /* left outer join (select a.idnum,isnull(b.lastname,'''') + '' ,'' + isnull(b.firstname,'''') + '' '' + isnull(b.middlename,'''')
		        as PatientName from PATIENT_DATA..tbpatient as a
		  	left outer join PATIENT_DATA..tbmaster as b
				on a.hospnum = b.hospnum		
			) as b
        on a.idnum = b.idnum*/
     left outer join patient_data..tbMaster as b
	on a.hospnum=b.hospnum 	

     left outer join BUILD_FILE..tbcoDoctor as c
	on a.doctorid1 = c.doctorid      	
     left outer join BUILD_FILE..tbcoDoctor as d
	on a.doctorid2 = d.doctorid      
     left outer join BUILD_FILE..tbcoOtherrevenue as e
	on a.procedureID = e.otherrevenueid	
     left outer join PATIENT_DATA..tbpatient as f
	on a.idnum = f.idnum	
where  isnull(f.dcrdate,'''') ='''' and isnull(a.recordstatus,'''') IN (''C'')
--isnull(recordstatus,'''') =''''
order by PatientName,Status
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetORSchedule_Request]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'







CREATE PROCEDURE [dbo].[sp_GetORSchedule_Request]

AS

select isnull(b.lastname,'''') + '' ,'' + isnull(b.firstname,'''') + '' '' + isnull(b.middlename,'''') as PatientName,A.ProcedureName  as [OR Procedure],
		     isnull(c.lastname,'''') + '' ,'' + isnull(c.firstname,'''') + '' '' + isnull(c.middlename,'''')
		        as Surgeon,
		     isnull(d.lastname,'''') + '' ,'' + isnull(d.firstname,'''') + '' '' + isnull(d.middlename,'''')
		        as Anesthesiologist,
		     convert(varchar(20),a.scheduledate,0) as [Schedule Date/Time],a.IDNum as [ADM. #],a.ProcedureID as ProcID,'''',
			a.Transnum As TransNum,
			a.DoctorID1 As Doc1,
			a.DoctorID2 As Doc2
from tborscheduledetails as a
    /* left outer join (select a.idnum,isnull(b.lastname,'''') + '' ,'' + isnull(b.firstname,'''') + '' '' + isnull(b.middlename,'''')
		        as PatientName from PATIENT_DATA..tbpatient as a
		  	left outer join PATIENT_DATA..tbmaster as b
				on a.hospnum = b.hospnum		
			) as b
        on a.idnum = b.idnum*/
     left outer join patient_data..tbMaster as b
	on a.hospnum=b.hospnum 	

     left outer join BUILD_FILE..tbcoDoctor as c
	on a.doctorid1 = c.doctorid      	
     left outer join BUILD_FILE..tbcoDoctor as d
	on a.doctorid2 = d.doctorid      
     left outer join BUILD_FILE..tbcoOtherrevenue as e
	on a.procedureID = e.otherrevenueid	
where isnull(recordstatus,'''') =''''

' 
END

GO