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,
	ISNULL((SELECT VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='MediWEB' AND KEYNAME='Hasta online randevu alirken isimini girmeli'),'0') AS isimzorunlu,
	ISNULL((SELECT VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='MediWEB' AND KEYNAME='Online islemlerde iletisim bilgisi gosterilsin'),'0') AS iletisimgoster,
	ISNULL((SELECT TOP 1 CAPTCHAOLSUN FROM MEDIWEBAYARLAR),0) AS captchaolsun,
	ADRES1 AS adres,
	EK1 AS email,
	EK2 AS website,
	IL AS il,
	ILCE AS ilce,
	TEL AS tel,
	FAX AS fax,
	LOGOLINK AS logolink,
	KOORDINAT as koordinat
	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

IF (SELECT COUNT(*) FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='MediWEB' AND KEYNAME='Hasta online randevu alirken isimini girmeli')=0
BEGIN
	INSERT INTO CONFIG (PROGNAME,SECTIONSTR,KEYNAME,VALUESTR) VALUES ('MEDISOFT','MediWEB','Hasta online randevu alirken isimini girmeli','0')
END
GO

IF (SELECT COUNT(*) FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='MediWEB' AND KEYNAME='Online islemlerde iletisim bilgisi gosterilsin')=0
BEGIN
	INSERT INTO CONFIG (PROGNAME,SECTIONSTR,KEYNAME,VALUESTR) VALUES ('MEDISOFT','MediWEB','Online islemlerde iletisim bilgisi gosterilsin','1')
END
GO

