--/************************************************************
-- * Code by SaVaS
-- * Update by BAU
-- * Time: 06.12.2018 13:31:14
-- ************************************************************/

--DECLARE @Tarih AS DATETIME
--DECLARE @SUBEKODU AS INT
--DECLARE @MERKEZSUBEOLARAKCALIS AS INT
--DECLARE @CARIKODU AS NVARCHAR(15)


--SET @Tarih = GETDATE()
--SET @SUBEKODU = 0
--SET @MERKEZSUBEOLARAKCALIS = 0
--SET @CARIKODU = 'MUS00015'
create procedure [dbo].[_Brk_Mutabakat_CariHareket_Dovizli](@CARIKODU nvarchar(15), @Tarih datetime, @SUBEKODU int, @MERKEZSUBEOLARAKCALIS int) as
BEGIN


SELECT 
		--convert(varchar, MAX(Tarih), 103) AS TARIH,
		convert(varchar, @Tarih, 103) AS TARIH, 
		
		--CariKodu,
  --     CariAdi,
  --     DovizTuru,
       ISNULL( ISNULL(
           DovizIsmi,
           (
               SELECT yedek6
               FROM   TBLPARAM AS t
               WHERE  t.SUBE_KODU = @SUBEKODU
           )
       ),'TL')            AS PARABIRIMI,
       --SUM(BORC)    AS Borc,
       --SUM(ALACAK)  AS Alacak,
       convert(varchar, cast((CASE 
            WHEN (SUM(Borc) - SUM(Alacak)) > 0 THEN SUM(Borc - Alacak)
            ELSE 0
       END) as money), 1)          AS BORÇ,
       convert(varchar, cast((CASE 
            WHEN (SUM(Alacak) - SUM(BORC)) > 0 THEN SUM(Alacak - Borc)
            ELSE 0
       END) as money), 1)          AS ALACAK
	   
	   --,CASE 
    --        WHEN (SUM(Borc) - SUM(Alacak)) > 0 THEN SUM(Borc - Alacak)
    --        ELSE 0
    --   END - CASE 
    --              WHEN (SUM(Alacak) - SUM(Borc)) > 0 THEN SUM(Alacak - Borc)
    --              ELSE 0
    --         END    AS Bakiye,
    --   CariAdres,
    --   VergiNo,
    --   GrupKodu,
    --   Kod1,
    --   Kod2,
    --   Kod3,
    --   Kod4,
    --   Kod5,
    --   Telefon,
    --   Faks,
    --   CariTipi
	   
FROM   (
           SELECT dbo.TBLCAHAR.CARI_KOD  AS CariKodu,
                  dbo.TRK(dbo.TBLCASABIT.CARI_ISIM) AS CariAdi,
                  dbo.TBLCAHAR.DOVIZ_TURU AS DovizTuru,
                  DovizIsmi = (
                      SELECT [ISIM]
                      FROM   [NETSIS].[dbo].[KUR]
                      WHERE  [SIRA] = dbo.TBLCAHAR.DOVIZ_TURU
                  ),
                  CASE 
                       WHEN dbo.TBLCAHAR.DOVIZ_TURU <> 0 THEN CASE 
                                                                   WHEN (dbo.TBLCAHAR.BORC) > 0 THEN (dbo.TBLCAHAR.DOVIZ_TUTAR)
                                                                   ELSE 0
                                                              END
                       ELSE (dbo.TBLCAHAR.BORC)
                  END                    AS Borc,
                  CASE 
                       WHEN dbo.TBLCAHAR.DOVIZ_TURU <> 0 THEN CASE 
                                                                   WHEN (dbo.TBLCAHAR.ALACAK) > 0 THEN (dbo.TBLCAHAR.DOVIZ_TUTAR)
                                                                   ELSE 0
                                                              END
                       ELSE (dbo.TBLCAHAR.ALACAK)
                  END                    AS Alacak,
                  dbo.TRK(dbo.TBLCASABIT.CARI_IL) AS CariIl,
                  dbo.TRK(dbo.TBLCASABIT.CARI_ILCE) AS CariIlce,
                  dbo.TRK(dbo.TBLCASABIT.CARI_TEL) AS Telefon,
                  dbo.TRK(dbo.TBLCASABIT.FAX) AS Faks,
                  dbo.TRK(dbo.TBLCASABIT.CARI_ADRES) AS CariAdres,
                  dbo.TBLCASABIT.VERGI_NUMARASI AS VergiNo,
                  dbo.TRK(dbo.TBLCASABIT.GRUP_KODU) AS GrupKodu,
                  dbo.TRK(dbo.TBLCASABIT.RAPOR_KODU1) AS Kod1,
                  dbo.TRK(dbo.TBLCASABIT.RAPOR_KODU2) AS Kod2,
                  dbo.TRK(dbo.TBLCASABIT.RAPOR_KODU3) AS Kod3,
                  dbo.TRK(dbo.TBLCASABIT.RAPOR_KODU4) AS Kod4,
                  dbo.TRK(dbo.TBLCASABIT.RAPOR_KODU5) AS Kod5,
                  dbo.TBLCASABIT.CARI_TIP AS CariTipi,
                  dbo.TBLCAHAR.TARIH     AS Tarih
           FROM   dbo.TBLCAHAR WITH(NOLOCK)
                  LEFT OUTER JOIN dbo.TBLCASABIT WITH(NOLOCK)
                       ON  dbo.TBLCAHAR.CARI_KOD = dbo.TBLCASABIT.CARI_KOD
           WHERE  TBLCAHAR.TARIH < @Tarih
                  AND TBLCAHAR.CARI_KOD = @CARIKODU
                  AND (
                          CASE 
                               WHEN @MERKEZSUBEOLARAKCALIS = 1
                          AND (
                                  SELECT COUNT(*)
                                  FROM   dbo.TBLSUBELER
                                  WHERE  SUBE_KODU <> -1
                                         AND SUBE_KODU <> 32767
                                         AND MERKEZMI = 'E'
                                         AND SUBE_KODU = @SUBEKODU
                              ) > 0 THEN
                              'TRUE'
                              ELSE
                              (
                                  CASE 
                                       WHEN TBLCAHAR.SUBE_KODU = @SUBEKODU THEN 'TRUE'
                                       ELSE 'FALSE'
                                  END
                              )
                              END
                      ) = 'TRUE'
       )            AS dch
GROUP BY
       dch.DovizTuru,
       dch.DovizIsmi,
       dch.CariKodu,
       dch.CariAdi,
       dch.CariIl,
       dch.CariIlce,
       dch.CariAdres,
       dch.VergiNo,
       dch.GrupKodu,
       dch.Kod1,
       dch.Kod2,
       dch.Kod3,
       dch.Kod4,
       dch.Kod5,
       dch.Telefon,
       dch.Faks,
       dch.CariTipi
END


