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)


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 @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 ) = 0
			 BEGIN
				INSERT INTO USSGUNSONUGONDERIM (TARIH, SUBEID, HAZIRLAMATARIHI) VALUES (@BASLANGICTARIHI, @SUBEID, GETDATE())
			 END

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

			 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
				    INSERT INTO USSGUNSONUGONDERIMKLINIKKALITE (USSGUNSONUGONDERIMKLINIKKALITEID,USSGUNSONUGONDERIMID,KLINIKKALITEKOD) VALUES (ISNULL((SELECT MAX(USSGUNSONUGONDERIMKLINIKKALITEID) FROM USSGUNSONUGONDERIMKLINIKKALITE),0)+1,@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



END
