IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPWEB_SUBELER]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SPWEB_SUBELER
GO

CREATE PROCEDURE [dbo].[SPWEB_SUBELER]
AS
BEGIN
	--EXEC SPWEB_LOGLA 'subeler',@kisino,'Şube listesini çekti',''
	SELECT	SUBEID as subeid, SUBEADI as sube, 
	ISNULL((SELECT TOP 1 HTMLMETIN FROM KURUMMETINLERI WHERE METINANAHTARI='WebRandevuOnayMetni' AND (SUBENO=SUBEID OR SUBENO=0) ORDER BY SUBENO DESC),'') AS randevuonaymetni,
	ISNULL((SELECT TOP 1 HTMLMETIN FROM KURUMMETINLERI WHERE METINANAHTARI='WebLoginOnayMetni' AND (SUBENO=SUBEID OR SUBENO=0) ORDER BY SUBENO DESC),'') AS loginonaymetni,
	ISNULL((SELECT TOP 1 HTMLMETIN FROM KURUMMETINLERI WHERE METINANAHTARI='WebKVKKOnayMetni' AND (SUBENO=SUBEID OR SUBENO=0) AND ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WHERE SECTIONSTR='Kvkk' AND KEYNAME LIKE 'Kvkk gizlilik onam %'),'') IN ('','sms') ORDER BY SUBENO DESC),'') AS kvkkonaymetni,
	ISNULL((SELECT TOP 1 HTMLMETIN FROM KURUMMETINLERI WHERE METINANAHTARI='WebIYSOnayMetni' AND (SUBENO=SUBEID OR SUBENO=0) AND ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WHERE SECTIONSTR='Kvkk' AND KEYNAME LIKE 'Kvkk gizlilik onam %'),'') IN ('','sms') ORDER BY SUBENO DESC),'') AS iysonaymetni
	FROM	SUBELER WITH(NOLOCK)
	WHERE	MEDWEBKULLAN = 1 
			AND EXISTS(SELECT SUBEID FROM SUBEBOLUM WITH(NOLOCK) INNER JOIN TBOLUM WITH(NOLOCK) ON SUBEBOLUM.BOLUMID = TBOLUM.BOLUMID WHERE SUBEID = SUBELER.SUBEID AND WEBRANDEVUVERILIR=1)
		ORDER BY SUBEID  
END
GO

IF EXISTS (SELECT [name] FROM [sys].[database_principals] WHERE [type] = N'S' AND [name] = N'mediwebuser')
BEGIN
	BEGIN TRY
		GRANT EXECUTE ON [dbo].[SPWEB_SUBELER] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO




