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)
DECLARE @GETID INT
DECLARE @KLINIKKODUTUT 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


/*SAYAÇ BAKIM*/
SET @GETID = 0
IF ISNULL((SELECT SIRANO FROM SAYACLAR WHERE TABLENAME = 'USSAYSONUGONDERIM'),0)<ISNULL((SELECT MAX(USSAYSONUGONDERIMID) FROM USSAYSONUGONDERIM),0)
BEGIN
    WHILE @GETID < (SELECT MAX(USSAYSONUGONDERIMID) FROM USSAYSONUGONDERIM)
    BEGIN
	   EXEC SPREADID2 'USSAYSONUGONDERIM','USSAYSONUGONDERIMID', @GETID OUTPUT
    END
END

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

SET @GETID = 0
IF ISNULL((SELECT SIRANO FROM SAYACLAR WHERE TABLENAME = 'USSAYSONUGONDERIMKLINIKKALITEKLINIK'),0)<ISNULL((SELECT MAX(USSAYSONUGONDERIMKLINIKKALITEKLINIKID) FROM USSAYSONUGONDERIMKLINIKKALITEKLINIK),0)
BEGIN
    WHILE @GETID < (SELECT MAX(USSAYSONUGONDERIMKLINIKKALITEKLINIKID) FROM USSAYSONUGONDERIMKLINIKKALITEKLINIK)
    BEGIN
	   EXEC SPREADID2 'USSAYSONUGONDERIMKLINIKKALITEKLINIK','USSAYSONUGONDERIMKLINIKKALITEKLINIKID', @GETID OUTPUT
    END
END
/*SAYAÇ BAKIMSON*/


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 AND SUBEID = @SUBEID) = 0
			 BEGIN
				SET @GETID = 0
				EXEC SPREADID2 'USSAYSONUGONDERIM','USSAYSONUGONDERIMID', @GETID OUTPUT
				INSERT INTO USSAYSONUGONDERIM (USSAYSONUGONDERIMID, YIL, AY, SUBEID, HAZIRLAMATARIHI) VALUES (@GETID, @YIL, @AY, @SUBEID, GETDATE())
			 END

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

			 UPDATE USSAYSONUGONDERIM SET HAZIRLAMATARIHI = GETDATE(), GONDERIMTARIHI = NULL WHERE USSAYSONUGONDERIMID=@USSAYSONUGONDERIMID

			 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
				    SET @GETID = 0
				    EXEC SPREADID2 'USSAYSONUGONDERIMKLINIKKALITE','USSAYSONUGONDERIMKLINIKKALITEID', @GETID OUTPUT
				    INSERT INTO USSAYSONUGONDERIMKLINIKKALITE (USSAYSONUGONDERIMKLINIKKALITEID,USSAYSONUGONDERIMID,KLINIKKALITEKOD) VALUES (@GETID, @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
				DECLARE EKSIKKLINIK CURSOR FOR SELECT KLINIKKODU FROM @TBL WHERE KLINIKKODU NOT IN (SELECT KLINIKKODU FROM USSAYSONUGONDERIMKLINIKKALITEKLINIK WHERE USSAYSONUGONDERIMKLINIKKALITEID=@USSAYSONUGONDERIMKLINIKKALITEID)
				OPEN EKSIKKLINIK 
				FETCH NEXT FROM EKSIKKLINIK INTO @KLINIKKODUTUT
				WHILE @@FETCH_STATUS = 0  
				BEGIN
				    SET @GETID = 0
				    EXEC SPREADID2 'USSAYSONUGONDERIMKLINIKKALITEKLINIK','USSAYSONUGONDERIMKLINIKKALITEKLINIKID', @GETID OUTPUT
				    INSERT INTO USSAYSONUGONDERIMKLINIKKALITEKLINIK (USSAYSONUGONDERIMKLINIKKALITEKLINIKID,USSAYSONUGONDERIMKLINIKKALITEID,KLINIKKODU) VALUES (@GETID,@USSAYSONUGONDERIMKLINIKKALITEID,@KLINIKKODUTUT)				    
				FETCH NEXT FROM EKSIKKLINIK INTO @KLINIKKODUTUT
				END
				CLOSE EKSIKKLINIK
				DEALLOCATE EKSIKKLINIK

				--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
