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

CREATE PROCEDURE [dbo].[SPUSS_KLINIKKALITESAYIYAZ_GUNLUK]
@SONGUNHESAPLA BIT,
@SUBEID INT,
@PERYOTBASLANGICTARIHI DATETIME,
@PERYOTBITISTARIHI DATETIME
AS
BEGIN

DECLARE @HATA VARCHAR(2000)=''
DECLARE @PERYOTBASLANGICTARIHIARTIR DATETIME
DECLARE @BASLANGICTARIHI DATETIME
DECLARE @BITISTARIHI DATETIME
DECLARE @SURECAL DATETIME
DECLARE @USSGUNSONUGONDERIMID INT
DECLARE @TKLINIKKALITEKOD INT
DECLARE @USSGUNSONUGONDERIMKLINIKKALITEID INT
DECLARE @KLINIKKODUAL INT
DECLARE @TBL TABLE (KLINIKKODU INT,KLINIKKALITESAYI INT)
DECLARE @GETID INT


IF @SONGUNHESAPLA=1
BEGIN
	SET @SUBEID = 1
	/*20180825*/
	/*20180814*/SET @PERYOTBASLANGICTARIHI = CONVERT(DATE,DATEADD(DAY,-5,GETDATE()))
	/*20180825*/SET @PERYOTBITISTARIHI = CONVERT(DATE,DATEADD(DAY,-1,GETDATE()))
END

IF @SUBEID IS NULL OR @SUBEID=0 SET @SUBEID =1
IF @PERYOTBASLANGICTARIHI IS NULL SET @PERYOTBASLANGICTARIHI =CONVERT(DATE,GETDATE())
IF @PERYOTBITISTARIHI IS NULL SET @PERYOTBITISTARIHI =CONVERT(DATE,GETDATE())

IF (SELECT COUNT(*) FROM TKLINIKKALITE)<10 RETURN


SET @GETID = 0
IF ISNULL((SELECT SIRANO FROM SAYACLAR WHERE TABLENAME = 'USSGUNSONUGONDERIMKLINIKKALITE'),0)<ISNULL((SELECT MAX(USSGUNSONUGONDERIMKLINIKKALITEID) FROM USSGUNSONUGONDERIMKLINIKKALITE),0)
BEGIN
    WHILE @GETID < (SELECT MAX(USSGUNSONUGONDERIMKLINIKKALITEID) FROM USSGUNSONUGONDERIMKLINIKKALITE)
    BEGIN
	   EXEC SPREADID2 'USSGUNSONUGONDERIMKLINIKKALITE','USSGUNSONUGONDERIMKLINIKKALITEID', @GETID OUTPUT
    END
END

--TKLINIKKALITEREFERANS TEMPE AT PERFORMANS İÇİN
IF OBJECT_ID('tempdb..#TMPKKRSUT') IS NOT NULL
BEGIN
    IF (SELECT TOP 1 TRH FROM #TMPKKRSUT) IS NULL OR (SELECT TOP 1 TRH FROM #TMPKKRSUT) < DATEADD(DAY,-6,GETDATE())
    DROP TABLE #TMPKKRSUT
END

IF OBJECT_ID('tempdb..#TMPKKRSUT') IS NULL
BEGIN
    SELECT DISTINCT KLINIKKALITEKOD,REFERANSKOD,GETDATE() AS TRH INTO #TMPKKRSUT FROM
    (
		  SELECT KLINIKKALITEKOD,REFERANSKOD FROM TKLINIKKALITEREFERANS WHERE REFERANSTUR = 'SUT'
		  UNION
		  SELECT KLINIKKALITEKOD,'P'+REFERANSKOD AS REFERANSKOD FROM TKLINIKKALITEREFERANS WHERE REFERANSTUR = 'SUT'
    ) AS TBL ORDER BY KLINIKKALITEKOD,REFERANSKOD
END

IF OBJECT_ID('tempdb..#TMPKKRICD') IS NOT NULL
BEGIN
    IF (SELECT TOP 1 TRH FROM #TMPKKRICD) IS NULL OR (SELECT TOP 1 TRH FROM #TMPKKRICD) < DATEADD(DAY,-6,GETDATE())
    DROP TABLE #TMPKKRICD
END

IF OBJECT_ID('tempdb..#TMPKKRICD') IS NULL
BEGIN
    SELECT DISTINCT KLINIKKALITEKOD,REFERANSKOD,GETDATE() AS TRH INTO #TMPKKRICD FROM TKLINIKKALITEREFERANS WHERE REFERANSTUR = 'ICD'
END


SET @PERYOTBASLANGICTARIHIARTIR=@PERYOTBITISTARIHI

WHILE (@PERYOTBASLANGICTARIHIARTIR>=@PERYOTBASLANGICTARIHI)
BEGIN

	   DECLARE CURSUBELER CURSOR FOR SELECT SUBEID FROM SUBELER WHERE (@SONGUNHESAPLA=1 OR SUBEID=@SUBEID) AND EXISTS(SELECT TOP 1 HDOSYAID FROM HDOSYA WHERE HDOSYA.SUBEID=SUBELER.SUBEID AND HDTARIH>@PERYOTBASLANGICTARIHI)
	   OPEN CURSUBELER 
	   FETCH NEXT FROM CURSUBELER INTO @SUBEID
	   WHILE @@FETCH_STATUS = 0  
	   BEGIN
	   
			 SET @BASLANGICTARIHI = @PERYOTBASLANGICTARIHIARTIR
			 SET @BITISTARIHI = DATEADD(DAY,1,@BASLANGICTARIHI)

			 IF (SELECT COUNT(*) FROM USSGUNSONUGONDERIM WHERE TARIH =  @BASLANGICTARIHI AND SUBEID=@SUBEID ) = 0
			 BEGIN
				INSERT INTO USSGUNSONUGONDERIM (TARIH, SUBEID, HAZIRLAMATARIHI) VALUES (@BASLANGICTARIHI, @SUBEID, GETDATE())
			 END			 

			 SET @USSGUNSONUGONDERIMID = (SELECT USSGUNSONUGONDERIMID FROM USSGUNSONUGONDERIM WHERE TARIH=@BASLANGICTARIHI AND SUBEID=@SUBEID)

			 UPDATE USSGUNSONUGONDERIM SET HAZIRLAMATARIHI = GETDATE(), GONDERIMTARIHI = NULL WHERE USSGUNSONUGONDERIMID=@USSGUNSONUGONDERIMID

			 DECLARE CURGUNSONU CURSOR FOR SELECT KLINIKKALITEKOD FROM TKLINIKKALITE
			 OPEN CURGUNSONU 
			 FETCH NEXT FROM CURGUNSONU INTO @TKLINIKKALITEKOD
			 WHILE @@FETCH_STATUS = 0  
			 BEGIN
				DELETE FROM @TBL

				IF (SELECT COUNT(*) FROM USSGUNSONUGONDERIMKLINIKKALITE WHERE USSGUNSONUGONDERIMID=@USSGUNSONUGONDERIMID AND KLINIKKALITEKOD=@TKLINIKKALITEKOD)=0
				BEGIN
				    SET @GETID = 0
				    EXEC SPREADID2 'USSGUNSONUGONDERIMKLINIKKALITE','USSGUNSONUGONDERIMKLINIKKALITEID', @GETID OUTPUT
				    INSERT INTO USSGUNSONUGONDERIMKLINIKKALITE (USSGUNSONUGONDERIMKLINIKKALITEID,USSGUNSONUGONDERIMID,KLINIKKALITEKOD) VALUES (@GETID,@USSGUNSONUGONDERIMID,@TKLINIKKALITEKOD)
				END
	   
				SET @USSGUNSONUGONDERIMKLINIKKALITEID = (SELECT USSGUNSONUGONDERIMKLINIKKALITEID FROM USSGUNSONUGONDERIMKLINIKKALITE WHERE USSGUNSONUGONDERIMID=@USSGUNSONUGONDERIMID AND KLINIKKALITEKOD=@TKLINIKKALITEKOD)


				INSERT INTO @TBL(KLINIKKODU, KLINIKKALITESAYI) exec SPUSS_KLINIKKALITESAYIBUL @SUBEID,@BASLANGICTARIHI,@BITISTARIHI,@TKLINIKKALITEKOD,1
	   
				UPDATE USSGUNSONUGONDERIMKLINIKKALITE SET KLINIKKALITESAYI = ISNULL((SELECT TOP 1 KLINIKKALITESAYI FROM @TBL),0) WHERE USSGUNSONUGONDERIMKLINIKKALITEID=@USSGUNSONUGONDERIMKLINIKKALITEID
	   

			 FETCH NEXT FROM CURGUNSONU INTO @TKLINIKKALITEKOD
			 END
			 CLOSE CURGUNSONU
			 DEALLOCATE CURGUNSONU
	   FETCH NEXT FROM CURSUBELER INTO @SUBEID
	   END
	   CLOSE CURSUBELER
	   DEALLOCATE CURSUBELER

	   SET @PERYOTBASLANGICTARIHIARTIR = DATEADD(DAY,-1,@PERYOTBASLANGICTARIHIARTIR)

    END

IF OBJECT_ID('tempdb..#TMPKKRSUT') IS NOT NULL
BEGIN
    DROP TABLE #TMPKKRSUT
END
IF OBJECT_ID('tempdb..#TMPKKRICD') IS NOT NULL
BEGIN
    DROP TABLE #TMPKKRICD
END

END
