CREATE   VIEW VW_TAHSILATLAR_BIRLESIK
AS

-- ÇEK BORDROLARI
SELECT 
    CONVERT(date, CSR.DATE_)             AS [Tarih],
    CLC.CODE                             AS [Cari Kod],
    CLC.DEFINITION_                      AS [Cari Açiklama],
	ISNULL(SLS.DEFINITION_,'')			 AS [Satis Elemani],
    SUM(CSC.AMOUNT)                      AS [Toplam Tutar],
	'Çek Bordrolari'				     AS [Islem Türü]
FROM LG_011_08_CSTRANS AS CST
JOIN LG_011_08_CSROLL  AS CSR ON CSR.LOGICALREF = CST.ROLLREF
JOIN LG_011_08_CSCARD  AS CSC ON CSC.LOGICALREF = CST.CSREF
JOIN LG_011_CLCARD     AS CLC ON CLC.LOGICALREF = CST.CARDREF
LEFT JOIN LG_SLSMAN    AS SLS ON SLS.FIRMNR = 11 AND SLS.LOGICALREF = CSR.SALESMANREF
WHERE CSR.TRCODE IN (1,2)
  AND CSR.CANCELLED = 0
GROUP BY 
    CONVERT(date, CSR.DATE_),
    CLC.CODE, 
    CLC.DEFINITION_,
    SLS.DEFINITION_

UNION ALL

-- KREDI KARTI
SELECT 
    CONVERT(date, CLF.DATE_) AS [Tarih],
    CC.CODE AS [Cari Kod],
    CC.DEFINITION_ AS [Cari Açiklama],
    ISNULL(SLS.DEFINITION_,'') AS [Satis Elemani],
    SUM(CLF.TRNET) AS [Toplam Tutar],
    'Kredi Karti' AS [Islem Türü]
FROM LG_011_08_CLFLINE AS CLF
LEFT JOIN LG_SLSMAN AS SLS ON SLS.LOGICALREF = CLF.SALESMANREF
LEFT JOIN LG_011_CLCARD AS CC ON CC.LOGICALREF = CLF.CLIENTREF
WHERE CLF.TRCODE = 70 AND CLF.CANCELLED = 0
GROUP BY CLF.DATE_, CC.CODE, CC.DEFINITION_, SLS.DEFINITION_

UNION ALL

-- NAKIT TAHSILAT
SELECT 
    CONVERT(date, CLF.DATE_) AS [Tarih],
    CC.CODE AS [Cari Kod],
    CC.DEFINITION_ AS [Cari Açiklama],
    ISNULL(SLS.DEFINITION_,'') AS [Satis Elemani],
    SUM(CLF.TRNET) AS [Toplam Tutar],
    'Nakit Tahsilat' AS [Islem Türü]
FROM LG_011_08_CLFLINE AS CLF
LEFT JOIN LG_SLSMAN AS SLS ON SLS.LOGICALREF = CLF.SALESMANREF
LEFT JOIN LG_011_CLCARD AS CC ON CC.LOGICALREF = CLF.CLIENTREF
WHERE CLF.TRCODE = 1 AND CLF.CANCELLED = 0
GROUP BY CLF.DATE_, CC.CODE, CC.DEFINITION_, SLS.DEFINITION_

UNION ALL

-- BANKA FISLERI
SELECT 
    CONVERT(date, CLF.DATE_) AS [Tarih],
    CC.CODE AS [Cari Kod],
    CC.DEFINITION_ AS [Cari Açiklama],
    ISNULL(SLS.DEFINITION_,'') AS [Satis Elemani],
    SUM(CLF.TRNET) AS [Toplam Tutar],
    'Banka Fisi' AS [Islem Türü]
FROM LG_011_08_BNFLINE AS CLF
LEFT JOIN LG_SLSMAN AS SLS ON SLS.LOGICALREF = CLF.SALESMANREF
LEFT JOIN LG_011_CLCARD AS CC ON CC.LOGICALREF = CLF.CLIENTREF
WHERE CLF.CANCELLED = 0 AND CLF.CLIENTREF <> 0 AND CLF.[SIGN] = 0
GROUP BY CLF.DATE_, CC.CODE, CC.DEFINITION_, SLS.DEFINITION_;