SET ARITHABORT ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FatIslDovizTRH]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FatIslDovizTRH]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  FUNCTION [dbo].[FatIslDovizTRH] (
	@Tarih as datetime, 
	@HesapDoviz as nvarchar(10), 
	@idx1 as int, 
	@FatDoviz as nvarchar(10), 
	@idx2 as int,
	@hdid as int)  
RETURNS table AS  
return
SELECT TITEM.SIRANO, 
       TITEM.SIRANO1, 
       TITEM.SIRANO2, 
       TITEM.SIRANO3, 
       TITEM.SIRANO4, 
       TITEM.SIRANO5, 
       TITEM.SIRANO6, 
       HESAPISL.IPTAL, 
       HESAPISL.HESAPISLID, 
       HESAPISL.ITARIH, 
       HESAPISL.HDOSYAID, 
       TITEM.ITEMID, 
       CONVERT(NVARCHAR(10), HESAPISL.ITARIH, 104) + ' - ' + TITEM.ITEMADI AS ITEMADI, 
       CONVERT(NVARCHAR(10), HESAPISL.ITARIH, 104) + ' - ' + TITEM.ITEMADI1 AS ITEMADI1, 
       CONVERT(NVARCHAR(10), HESAPISL.ITARIH, 104) + ' - ' + TITEM.ITEMADI2 AS ITEMADI2, 
       CONVERT(NVARCHAR(10), HESAPISL.ITARIH, 104) + ' - ' + TITEM.ITEMADI3 AS ITEMADI3, 
       CONVERT(NVARCHAR(10), HESAPISL.ITARIH, 104) + ' - ' + TITEM.ITEMADI4 AS ITEMADI4, 
       CONVERT(NVARCHAR(10), HESAPISL.ITARIH, 104) + ' - ' + TITEM.ITEMADI5 AS ITEMADI5, 
       CONVERT(NVARCHAR(10), HESAPISL.ITARIH, 104) + ' - ' + TITEM.ITEMADI6 AS ITEMADI6, 
       TITEM.ITEMKODU, 
       TITEM.ITEMKODU1, 
       TITEM.ITEMKODU2, 
       TITEM.ITEMKODU3, 
       TITEM.ITEMKODU4, 
       TITEM.ITEMKODU5, 
       TITEM.ITEMKODU6, 
       HESAPISL.KRMHFATISLID, 
       HESAPISL.OZELHFATISLID, 
       HESAPISL.HSTKDVORAN, 
       HESAPISL.KRMKDVORAN, 
       HESAPISL.TABLOTIPI, 
       TITEM.ITEMTURUID, 
       UYGBLM.BOLUMTURU, 
       TTIPADI AS CISLEM, 
       HESAPISL.ADET, 
       HESAPISL.KRMTUTAR * CASE
                               WHEN @HesapDoviz = 'TL'
                               THEN 1
                               ELSE CASE @idx1
                                        WHEN 1
                                        THEN ISNULL(HDVZISL.KUR1, 0)
                                        WHEN 2
                                        THEN ISNULL(HDVZISL.KUR2, 0)
                                        WHEN 3
                                        THEN ISNULL(HDVZISL.KUR3, 0)
                                        WHEN 4
                                        THEN ISNULL(HDVZISL.KUR4, 0)
                                        ELSE 0
                                    END
                           END / CASE
                                     WHEN @FatDoviz = 'TL'
                                     THEN 1
                                     ELSE CASE @idx2
                                              WHEN 1
                                              THEN ISNULL(FDVZISL.KUR1, 0)
                                              WHEN 2
                                              THEN ISNULL(FDVZISL.KUR2, 0)
                                              WHEN 3
                                              THEN ISNULL(FDVZISL.KUR3, 0)
                                              WHEN 4
                                              THEN ISNULL(FDVZISL.KUR4, 0)
                                              ELSE 0
                                          END
                                 END KRMTUTAR, 
       (HESAPISL.HIKRMNETTUTAR + HESAPISL.HSTKATKITUTAR) * CASE
                                                               WHEN @HesapDoviz = 'TL'
                                                               THEN 1
                                                               ELSE CASE @idx1
                                                                        WHEN 1
                                                                        THEN ISNULL(HDVZISL.KUR1, 0)
                                                                        WHEN 2
                                                                        THEN ISNULL(HDVZISL.KUR2, 0)
                                                                        WHEN 3
                                                                        THEN ISNULL(HDVZISL.KUR3, 0)
                                                                        WHEN 4
                                                                        THEN ISNULL(HDVZISL.KUR4, 0)
                                                                        ELSE 0
                                                                    END
                                                           END / CASE
                                                                     WHEN @FatDoviz = 'TL'
                                                                     THEN 1
                                                                     ELSE CASE @idx2
                                                                              WHEN 1
                                                                              THEN ISNULL(FDVZISL.KUR1, 0)
                                                                              WHEN 2
                                                                              THEN ISNULL(FDVZISL.KUR2, 0)
                                                                              WHEN 3
                                                                              THEN ISNULL(FDVZISL.KUR3, 0)
                                                                              WHEN 4
                                                                              THEN ISNULL(FDVZISL.KUR4, 0)
                                                                              ELSE 0
                                                                          END
                                                                 END NETKRMTUTAR, 
       HESAPISL.HIKRMNETTUTAR * CASE
                                    WHEN @HesapDoviz = 'TL'
                                    THEN 1
                                    ELSE CASE @idx1
                                             WHEN 1
                                             THEN ISNULL(HDVZISL.KUR1, 0)
                                             WHEN 2
                                             THEN ISNULL(HDVZISL.KUR2, 0)
                                             WHEN 3
                                             THEN ISNULL(HDVZISL.KUR3, 0)
                                             WHEN 4
                                             THEN ISNULL(HDVZISL.KUR4, 0)
                                             ELSE 0
                                         END
                                END / CASE
                                          WHEN @FatDoviz = 'TL'
                                          THEN 1
                                          ELSE CASE @idx2
                                                   WHEN 1
                                                   THEN ISNULL(FDVZISL.KUR1, 0)
                                                   WHEN 2
                                                   THEN ISNULL(FDVZISL.KUR2, 0)
                                                   WHEN 3
                                                   THEN ISNULL(FDVZISL.KUR3, 0)
                                                   WHEN 4
                                                   THEN ISNULL(FDVZISL.KUR4, 0)
                                                   ELSE 0
                                               END
                                      END NETKRMTUTAR2, 
       HESAPISL.OZELTUTAR * CASE
                                WHEN @HesapDoviz = 'TL'
                                THEN 1
                                ELSE CASE @idx1
                                         WHEN 1
                                         THEN ISNULL(HDVZISL.KUR1, 0)
                                         WHEN 2
                                         THEN ISNULL(HDVZISL.KUR2, 0)
                                         WHEN 3
                                         THEN ISNULL(HDVZISL.KUR3, 0)
                                         WHEN 4
                                         THEN ISNULL(HDVZISL.KUR4, 0)
                                         ELSE 0
                                     END
                            END / CASE
                                      WHEN @FatDoviz = 'TL'
                                      THEN 1
                                      ELSE CASE @idx2
                                               WHEN 1
                                               THEN ISNULL(FDVZISL.KUR1, 0)
                                               WHEN 2
                                               THEN ISNULL(FDVZISL.KUR2, 0)
                                               WHEN 3
                                               THEN ISNULL(FDVZISL.KUR3, 0)
                                               WHEN 4
                                               THEN ISNULL(FDVZISL.KUR4, 0)
                                               ELSE 0
                                           END
                                  END HSTTUTAR, 
       HESAPISL.HIHSTNETTUTAR * CASE
                                    WHEN @HesapDoviz = 'TL'
                                    THEN 1
                                    ELSE CASE @idx1
                                             WHEN 1
                                             THEN ISNULL(HDVZISL.KUR1, 0)
                                             WHEN 2
                                             THEN ISNULL(HDVZISL.KUR2, 0)
                                             WHEN 3
                                             THEN ISNULL(HDVZISL.KUR3, 0)
                                             WHEN 4
                                             THEN ISNULL(HDVZISL.KUR4, 0)
                                             ELSE 0
                                         END
                                END / CASE
                                          WHEN @FatDoviz = 'TL'
                                          THEN 1
                                          ELSE CASE @idx2
                                                   WHEN 1
                                                   THEN ISNULL(FDVZISL.KUR1, 0)
                                                   WHEN 2
                                                   THEN ISNULL(FDVZISL.KUR2, 0)
                                                   WHEN 3
                                                   THEN ISNULL(FDVZISL.KUR3, 0)
                                                   WHEN 4
                                                   THEN ISNULL(FDVZISL.KUR4, 0)
                                                   ELSE 0
                                               END
                                      END NETHSTTUTAR, 
       HESAPISL.HSTKATKITUTAR * CASE
                                    WHEN @HesapDoviz = 'TL'
                                    THEN 1
                                    ELSE CASE @idx1
                                             WHEN 1
                                             THEN ISNULL(HDVZISL.KUR1, 0)
                                             WHEN 2
                                             THEN ISNULL(HDVZISL.KUR2, 0)
                                             WHEN 3
                                             THEN ISNULL(HDVZISL.KUR3, 0)
                                             WHEN 4
                                             THEN ISNULL(HDVZISL.KUR4, 0)
                                             ELSE 0
                                         END
                                END / CASE
                                          WHEN @FatDoviz = 'TL'
                                          THEN 1
                                          ELSE CASE @idx2
                                                   WHEN 1
                                                   THEN ISNULL(FDVZISL.KUR1, 0)
                                                   WHEN 2
                                                   THEN ISNULL(FDVZISL.KUR2, 0)
                                                   WHEN 3
                                                   THEN ISNULL(FDVZISL.KUR3, 0)
                                                   WHEN 4
                                                   THEN ISNULL(FDVZISL.KUR4, 0)
                                                   ELSE 0
                                               END
                                      END HSTKATKITUTAR, 
       HESAPISL.SGTUTAR * CASE
                              WHEN @HesapDoviz = 'TL'
                              THEN 1
                              ELSE CASE @idx1
                                       WHEN 1
                                       THEN ISNULL(HDVZISL.KUR1, 0)
                                       WHEN 2
                                       THEN ISNULL(HDVZISL.KUR2, 0)
                                       WHEN 3
                                       THEN ISNULL(HDVZISL.KUR3, 0)
                                       WHEN 4
                                       THEN ISNULL(HDVZISL.KUR4, 0)
                                       ELSE 0
                                   END
                          END / CASE
                                    WHEN @FatDoviz = 'TL'
                                    THEN 1
                                    ELSE CASE @idx2
                                             WHEN 1
                                             THEN ISNULL(FDVZISL.KUR1, 0)
                                             WHEN 2
                                             THEN ISNULL(FDVZISL.KUR2, 0)
                                             WHEN 3
                                             THEN ISNULL(FDVZISL.KUR3, 0)
                                             WHEN 4
                                             THEN ISNULL(FDVZISL.KUR4, 0)
                                             ELSE 0
                                         END
                                END SGTUTAR, 
       HESAPISL.HISGNETTUTAR * CASE
                                   WHEN @HesapDoviz = 'TL'
                                   THEN 1
                                   ELSE CASE @idx1
                                            WHEN 1
                                            THEN ISNULL(HDVZISL.KUR1, 0)
                                            WHEN 2
                                            THEN ISNULL(HDVZISL.KUR2, 0)
                                            WHEN 3
                                            THEN ISNULL(HDVZISL.KUR3, 0)
                                            WHEN 4
                                            THEN ISNULL(HDVZISL.KUR4, 0)
                                            ELSE 0
                                        END
                               END / CASE
                                         WHEN @FatDoviz = 'TL'
                                         THEN 1
                                         ELSE CASE @idx2
                                                  WHEN 1
                                                  THEN ISNULL(FDVZISL.KUR1, 0)
                                                  WHEN 2
                                                  THEN ISNULL(FDVZISL.KUR2, 0)
                                                  WHEN 3
                                                  THEN ISNULL(FDVZISL.KUR3, 0)
                                                  WHEN 4
                                                  THEN ISNULL(FDVZISL.KUR4, 0)
                                                  ELSE 0
                                              END
                                     END NETSGTUTAR, 
       UYGBLM.SRMMRKID, 
       HDDRK.SRMMRKID AS HDDRSRMMRKID, 
       UYGDRK.SRMMRKID AS UYGDRSRMMRKID, 
       ISTDRK.SRMMRKID AS ISTDRSRMMRKID, 
       HESAPISL.HSTODEMEID, 
       HESAPISL.OZELODENDI, 
       TITEM.BARKOD, 
       TITEMGRUP.AYYIHESAPID, 
       TITEMGRUP.AYYDHESAPID, 
       TITEMGRUP.YTYIHESAPID, 
       TITEMGRUP.YTYDHESAPID, 
       TITEMGRUP.GELIRISTUYGDR, 
       HDOSYA.HDYATIYOR, 
       HESAPISL.SGKDVORAN, 
       HESAPISL.SGFATID, 
       HESAPISL.BAZFYTBIRIM,
       CASE
           WHEN HDBLMIG.AYYIHESAPID IS NOT NULL
           THEN HDBLMIG.AYYIHESAPID
           ELSE HDBLM.AYYIHESAPID
       END AS HDBLMAYYIHESAPID,
       CASE
           WHEN HDBLMIG.AYYDHESAPID IS NOT NULL
           THEN HDBLMIG.AYYDHESAPID
           ELSE HDBLM.AYYDHESAPID
       END AS HDBLMAYYDHESAPID,
       CASE
           WHEN HDBLMIG.YTYIHESAPID IS NOT NULL
           THEN HDBLMIG.YTYIHESAPID
           ELSE HDBLM.YTYIHESAPID
       END AS HDBLMYTYIHESAPID,
       CASE
           WHEN HDBLMIG.YTYDHESAPID IS NOT NULL
           THEN HDBLMIG.YTYDHESAPID
           ELSE HDBLM.YTYDHESAPID
       END AS HDBLMYTYDHESAPID,
       CASE
           WHEN HDBLMIG.INDHESAPID IS NOT NULL
           THEN HDBLMIG.INDHESAPID
           ELSE HDBLM.INDHESAPID
       END AS HDBLMINDHESAPID,
       CASE
           WHEN UYGBLMIG.AYYIHESAPID IS NOT NULL
           THEN UYGBLMIG.AYYIHESAPID
           ELSE UYGBLM.AYYIHESAPID
       END AS UYGBLMAYYIHESAPID,
       CASE
           WHEN UYGBLMIG.AYYDHESAPID IS NOT NULL
           THEN UYGBLMIG.AYYDHESAPID
           ELSE UYGBLM.AYYDHESAPID
       END AS UYGBLMAYYDHESAPID,
       CASE
           WHEN UYGBLMIG.YTYIHESAPID IS NOT NULL
           THEN UYGBLMIG.YTYIHESAPID
           ELSE UYGBLM.YTYIHESAPID
       END AS UYGBLMYTYIHESAPID,
       CASE
           WHEN UYGBLMIG.YTYDHESAPID IS NOT NULL
           THEN UYGBLMIG.YTYDHESAPID
           ELSE UYGBLM.YTYDHESAPID
       END AS UYGBLMYTYDHESAPID,
       CASE
           WHEN UYGBLMIG.INDHESAPID IS NOT NULL
           THEN UYGBLMIG.INDHESAPID
           ELSE UYGBLM.INDHESAPID
       END AS UYGBLMINDHESAPID, 
		COALESCE(CKURUMGRUPITEMGRUP.HSTAYYIHESAPID,CARIGRUP.HSTAYYIHESAPID) AS HSTAYYIHESAPID, 
		COALESCE(CKURUMGRUPITEMGRUP.HSTAYYDHESAPID,CARIGRUP.HSTAYYDHESAPID) AS HSTAYYDHESAPID, 
		COALESCE(CKURUMGRUPITEMGRUP.HSTYTYIHESAPID,CARIGRUP.HSTYTYIHESAPID) AS HSTYTYIHESAPID, 
		COALESCE(CKURUMGRUPITEMGRUP.HSTYTYDHESAPID,CARIGRUP.HSTYTYDHESAPID) AS HSTYTYDHESAPID,
		COALESCE(CKURUMGRUPITEMGRUP.KRMAYYIHESAPID,CARIGRUP.KRMAYYIHESAPID) AS KRMAYYIHESAPID,
		COALESCE(CKURUMGRUPITEMGRUP.KRMAYYDHESAPID,CARIGRUP.KRMAYYDHESAPID) AS KRMAYYDHESAPID,
		COALESCE(CKURUMGRUPITEMGRUP.KRMYTYIHESAPID,CARIGRUP.KRMYTYIHESAPID) AS KRMYTYIHESAPID, 
		COALESCE(CKURUMGRUPITEMGRUP.KRMYTYDHESAPID,CARIGRUP.KRMYTYDHESAPID) AS KRMYTYDHESAPID,
		COALESCE(SKURUMGRUPITEMGRUP.HSTAYYIHESAPID,SGKRMGRUP.HSTAYYIHESAPID) AS SGHSTAYYIHESAPID, 
		COALESCE(SKURUMGRUPITEMGRUP.HSTAYYDHESAPID,SGKRMGRUP.HSTAYYDHESAPID) AS SGHSTAYYDHESAPID, 
		COALESCE(SKURUMGRUPITEMGRUP.HSTYTYIHESAPID,SGKRMGRUP.HSTYTYIHESAPID) AS SGHSTYTYIHESAPID, 
		COALESCE(SKURUMGRUPITEMGRUP.HSTYTYDHESAPID,SGKRMGRUP.HSTYTYDHESAPID) AS SGHSTYTYDHESAPID, 
		COALESCE(SKURUMGRUPITEMGRUP.KRMAYYIHESAPID,SGKRMGRUP.KRMAYYIHESAPID) AS SGKRMAYYIHESAPID, 
		COALESCE(SKURUMGRUPITEMGRUP.KRMAYYDHESAPID,SGKRMGRUP.KRMAYYDHESAPID) AS SGKRMAYYDHESAPID, 
		COALESCE(SKURUMGRUPITEMGRUP.KRMYTYIHESAPID,SGKRMGRUP.KRMYTYIHESAPID) AS SGKRMYTYIHESAPID, 
		COALESCE(SKURUMGRUPITEMGRUP.KRMYTYDHESAPID,SGKRMGRUP.KRMYTYDHESAPID) AS SGKRMYTYDHESAPID,
       HDOSYA.MDLTEDAVITURU, 
       HDOSYA.ICMALID, 
       HDOSYA.SGICMALID, 
       TITEMGRUP.OKCKISIMNO
FROM HESAPISL WITH(NOLOCK)
     INNER JOIN TITEM WITH(NOLOCK) ON HESAPISL.ITEMID = TITEM.ITEMID
     INNER JOIN TITEMGRUP WITH(NOLOCK) ON TITEM.ITEMGRUPID = TITEMGRUP.ITEMGRUPID
     INNER JOIN HDOSYA WITH(NOLOCK) ON HESAPISL.HDOSYAID = HDOSYA.HDOSYAID
     INNER JOIN TTABLOTIPI WITH(NOLOCK) ON HESAPISL.TABLOTIPI = TTABLOTIPI.TABLOTIPI
     LEFT JOIN TBOLUM HDBLM WITH(NOLOCK) ON HDOSYA.BOLUMID = HDBLM.BOLUMID
     LEFT JOIN TBOLUMITEMGRUP HDBLMIG WITH(NOLOCK) ON HDOSYA.BOLUMID = HDBLMIG.BOLUMID
                                               AND TITEM.ITEMGRUPID = HDBLMIG.ITEMGRUPID
     LEFT JOIN CARI WITH(NOLOCK) ON HDOSYA.HKCARIID = CARI.CARIID
     LEFT JOIN CARIGRUP WITH(NOLOCK) ON CARI.CARIGRUPID = CARIGRUP.CARIGRUPID
	 LEFT JOIN TKURUMGRUPITEMGRUP CKURUMGRUPITEMGRUP WITH(NOLOCK) ON CKURUMGRUPITEMGRUP.KURUMGRUPID=CARIGRUP.CARIGRUPID
     LEFT JOIN TDRKIMLIK HDDRK WITH(NOLOCK) ON HDOSYA.DRID = HDDRK.DRID
     LEFT JOIN TDRKIMLIK UYGDRK WITH(NOLOCK) ON HESAPISL.UYGDRID = UYGDRK.DRID
     LEFT JOIN TBOLUM UYGBLM WITH(NOLOCK) ON HESAPISL.UYGBOLUMID = UYGBLM.BOLUMID
     LEFT JOIN TBOLUMITEMGRUP UYGBLMIG WITH(NOLOCK) ON HDOSYA.BOLUMID = UYGBLMIG.BOLUMID
                                                AND TITEM.ITEMGRUPID = UYGBLMIG.ITEMGRUPID
     LEFT JOIN TDRKIMLIK ISTDRK WITH(NOLOCK) ON ISTDRK.DRID = HESAPISL.ISTDRID
     LEFT JOIN CARI SGKRM WITH(NOLOCK) ON SGKRM.CARIID = HDOSYA.HDSGKURUMID
     LEFT JOIN CARIGRUP SGKRMGRUP WITH(NOLOCK) ON SGKRM.CARIGRUPID = SGKRMGRUP.CARIGRUPID
	 LEFT JOIN TKURUMGRUPITEMGRUP SKURUMGRUPITEMGRUP WITH(NOLOCK) ON SKURUMGRUPITEMGRUP.KURUMGRUPID=SGKRMGRUP.CARIGRUPID
     LEFT JOIN DOVIZISL HDVZISL WITH(NOLOCK) ON HDVZISL.KODU = @HesapDoviz
                                         AND HDVZISL.TARIH = @Tarih
     LEFT JOIN DOVIZISL FDVZISL WITH(NOLOCK) ON FDVZISL.KODU = @FatDoviz
                                         AND FDVZISL.TARIH = @Tarih
WHERE HESAPISL.HDOSYAID = CASE
                              WHEN @hdid <> -1
                              THEN @hdid
                              ELSE HESAPISL.HDOSYAID
                          END
      AND (HESAPISL.IPTAL = 0)
      AND (HESAPISL.ONAYLI = 1)
      AND (HESAPISL.TEKLIF = 0)
      AND (TITEM.GELIRDISI = 0)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

