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

CREATE PROCEDURE [dbo].[SPUSS_KLINIKKALITESAYIYAZ_AYLIK]
@SONAYHESAPLA 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 @USSAYSONUGONDERIMID INT
DECLARE @TKLINIKKALITEKOD INT
DECLARE @USSAYSONUGONDERIMKLINIKKALITEID INT
DECLARE @YIL INT
DECLARE @AY INT
DECLARE @TBL TABLE (KLINIKKODU INT,KLINIKKALITESAYI INT)

IF @SONAYHESAPLA=1
BEGIN
	SET @SUBEID = 1
	/*20180825*/SET @PERYOTBASLANGICTARIHI = DATEADD(MONTH,-1,GETDATE())
	/*20180825*/SET @PERYOTBITISTARIHI = DATEADD(MONTH,-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())

/*20180801*/SET @PERYOTBASLANGICTARIHI = CONVERT(DATETIME, CONVERT(VARCHAR,DATEPART(YEAR,@PERYOTBASLANGICTARIHI))+RIGHT('00'+CONVERT(VARCHAR,DATEPART(MONTH,@PERYOTBASLANGICTARIHI)),2)+'01')
/*20180901*/SET @PERYOTBITISTARIHI = CONVERT(DATETIME, CONVERT(VARCHAR,DATEPART(YEAR,@PERYOTBASLANGICTARIHI))+RIGHT('00'+CONVERT(VARCHAR,DATEPART(MONTH,@PERYOTBASLANGICTARIHI)),2)+'01')

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


SET @PERYOTBASLANGICTARIHIARTIR=@PERYOTBITISTARIHI

WHILE (@PERYOTBASLANGICTARIHIARTIR>=@PERYOTBASLANGICTARIHI)
BEGIN

	   DECLARE CURSUBELER CURSOR FOR SELECT SUBEID FROM SUBELER WHERE (@SONAYHESAPLA=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(MONTH,1,@BASLANGICTARIHI)
		
			 SET @YIL = DATEPART(YEAR,@BASLANGICTARIHI)
			 SET @AY = DATEPART(MONTH,@BASLANGICTARIHI)

			 IF (SELECT COUNT(*) FROM USSAYSONUGONDERIM WHERE YIL = @YIL AND AY = @AY ) = 0
			 BEGIN
				INSERT INTO USSAYSONUGONDERIM (USSAYSONUGONDERIMID, YIL, AY, SUBEID, HAZIRLAMATARIHI) VALUES ((ISNULL((SELECT MAX(USSAYSONUGONDERIMID) FROM USSAYSONUGONDERIM),0)+1),@YIL, @AY, @SUBEID, GETDATE())
			 END

			 SET @USSAYSONUGONDERIMID = (SELECT USSAYSONUGONDERIMID FROM USSAYSONUGONDERIM WHERE  YIL = @YIL AND AY = @AY)

			 DECLARE CURAYSONU CURSOR FOR SELECT KLINIKKALITEKOD FROM TKLINIKKALITE
			 OPEN CURAYSONU 
			 FETCH NEXT FROM CURAYSONU INTO @TKLINIKKALITEKOD
			 WHILE @@FETCH_STATUS = 0  
			 BEGIN		  
		  
				DELETE FROM @TBL
				INSERT INTO @TBL(KLINIKKODU, KLINIKKALITESAYI) exec SPUSS_KLINIKKALITESAYIBUL @SUBEID,@BASLANGICTARIHI,@BITISTARIHI,@TKLINIKKALITEKOD,0


				IF (SELECT COUNT(*) FROM USSAYSONUGONDERIMKLINIKKALITE WHERE USSAYSONUGONDERIMID=@USSAYSONUGONDERIMID AND KLINIKKALITEKOD=@TKLINIKKALITEKOD)=0
				BEGIN
				    INSERT INTO USSAYSONUGONDERIMKLINIKKALITE (USSAYSONUGONDERIMKLINIKKALITEID,USSAYSONUGONDERIMID,KLINIKKALITEKOD) VALUES (ISNULL((SELECT MAX(USSAYSONUGONDERIMKLINIKKALITEID) FROM USSAYSONUGONDERIMKLINIKKALITE),0)+1,@USSAYSONUGONDERIMID,@TKLINIKKALITEKOD)
				END
	   
				SET @USSAYSONUGONDERIMKLINIKKALITEID = (SELECT USSAYSONUGONDERIMKLINIKKALITEID FROM USSAYSONUGONDERIMKLINIKKALITE WHERE USSAYSONUGONDERIMID=@USSAYSONUGONDERIMID AND KLINIKKALITEKOD=@TKLINIKKALITEKOD)

				--Daha önce sayısı olan ama artık sayısı olmayan klinikleri silelim.
				DELETE FROM USSAYSONUGONDERIMKLINIKKALITEKLINIK WHERE USSAYSONUGONDERIMKLINIKKALITEID = @USSAYSONUGONDERIMKLINIKKALITEID AND KLINIKKODU NOT IN (SELECT KLINIKKODU FROM @TBL)

				--EKSİK KLİNİK VARSA EKLEYELİM
				INSERT INTO USSAYSONUGONDERIMKLINIKKALITEKLINIK (USSAYSONUGONDERIMKLINIKKALITEKLINIKID,USSAYSONUGONDERIMKLINIKKALITEID,KLINIKKODU) 
				SELECT ISNULL((SELECT MAX(USSAYSONUGONDERIMKLINIKKALITEKLINIKID) FROM USSAYSONUGONDERIMKLINIKKALITEKLINIK),0)+1,@USSAYSONUGONDERIMKLINIKKALITEID,KLINIKKODU 
				FROM @TBL 
				WHERE KLINIKKODU NOT IN (SELECT KLINIKKODU FROM USSAYSONUGONDERIMKLINIKKALITEKLINIK WHERE USSAYSONUGONDERIMKLINIKKALITEID=@USSAYSONUGONDERIMKLINIKKALITEID)

				--Bulunan sayıları ekleyelim
				UPDATE KKK SET KKK.KLINIKKALITESAYI = TBL.KLINIKKALITESAYI FROM USSAYSONUGONDERIMKLINIKKALITEKLINIK KKK JOIN @TBL TBL ON TBL.KLINIKKODU = KKK.KLINIKKODU WHERE KKK.USSAYSONUGONDERIMKLINIKKALITEID = @USSAYSONUGONDERIMKLINIKKALITEID
		  
				--TOPLAM SAYIYI EKLEYELİM
				UPDATE USSAYSONUGONDERIMKLINIKKALITE SET KLINIKKALITESAYI = ISNULL((SELECT SUM(ISNULL(KLINIKKALITESAYI,0)) FROM @TBL),0) WHERE USSAYSONUGONDERIMKLINIKKALITEID=@USSAYSONUGONDERIMKLINIKKALITEID
	   

			 FETCH NEXT FROM CURAYSONU INTO @TKLINIKKALITEKOD
			 END
			 CLOSE CURAYSONU
			 DEALLOCATE CURAYSONU

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

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

    END



END
