SELECT TOP (100) PERCENT LG_300_01_PAYTRANS_1.PROCDATE AS [MAK.T.],
	dbo.LG_300_CLCARD.CODE AS [CH KOD],
	LEFT(dbo.LG_300_CLCARD.DEFINITION_, 40) AS [CH ACIKLAMA],
	LEFT(dbo.LG_300_01_INVOICE.FICHENO, 11) AS [FAT.NO.],
	dbo.LG_300_01_PAYTRANS.PROCDATE AS [FAT.T.],
	LG_300_01_PAYTRANS_1.DATE_ AS [MAK.V.],
	LEFT(dbo.LG_300_01_INVOICE.NETTOTAL, 10) AS [FAT.(TL)],
	DATEDIFF(DAY, dbo.LG_300_01_PAYTRANS.PROCDATE, LG_300_01_PAYTRANS_1.DATE_) AS GUN,
	MONTH(dbo.LG_300_01_INVOICE.DATE_) AS AY,
	CASE LG_300_01_PAYTRANS.TRCURR
		WHEN 0
			THEN (
					CASE LG_300_01_PAYTRANS_1.CROSSTOTAL
						WHEN 0
							THEN (
									CASE LG_300_01_PAYTRANS.TRCURR
										WHEN 0
											THEN (dbo.LG_300_01_PAYTRANS.TOTAL)
										ELSE (dbo.LG_300_01_PAYTRANS.TOTAL * dbo.LG_300_01_PAYTRANS.TRRATE)
										END
									)
						ELSE (LG_300_01_PAYTRANS_1.CROSSTOTAL)
						END
					)
		ELSE (LG_300_01_PAYTRANS_1.CROSSTOTAL * dbo.LG_300_01_PAYTRANS.TRRATE)
		END AS [MAK. (TL)],
	LEFT(dbo.LG_SLSMAN.DEFINITION_, 15) AS [SATIS ELEMANI],
	CASE LG_300_01_PAYTRANS_1.MODULENR
		WHEN 5
			THEN (
					CASE LG_300_01_PAYTRANS_1.TRCODE
						WHEN 4
							THEN 'İNDİRİM'
						WHEN 70
							THEN 'KREDİ KARTI'
						WHEN 5
							THEN 'VİRMAN'
						WHEN 14
							THEN 'AÇILIŞ FİŞİ'
						END
					)
		WHEN 10
			THEN (
					CASE LG_300_01_PAYTRANS_1.TRCODE
						WHEN 1
							THEN 'NAKİT'
						END
					)
		WHEN 6
			THEN (
					CASE LG_300_01_PAYTRANS_1.TRCODE
						WHEN 1
							THEN 'ÇEK'
						END
					)
		WHEN 7
			THEN (
					CASE LG_300_01_PAYTRANS_1.TRCODE
						WHEN 3
							THEN 'HAVALE'
						END
					)
		WHEN 4
			THEN (
					CASE LG_300_01_PAYTRANS_1.TRCODE
						WHEN 3
							THEN 'İADE FATURASI'
						WHEN 4
							THEN 'HİZMET FATURASI'
						WHEN 1
							THEN 'MAL FATURASI'
						END
					)
		END AS [MAK.TİPİ],
	dbo.LG_300_01_INVOICE.TRADINGGRP
FROM dbo.LG_SLSMAN
RIGHT OUTER JOIN dbo.LG_300_01_INVOICE
	ON dbo.LG_SLSMAN.LOGICALREF = dbo.LG_300_01_INVOICE.SALESMANREF
LEFT OUTER JOIN dbo.LG_300_CLCARD
	ON dbo.LG_300_01_INVOICE.CLIENTREF = dbo.LG_300_CLCARD.LOGICALREF
RIGHT OUTER JOIN dbo.LG_300_01_PAYTRANS AS LG_300_01_PAYTRANS_1
LEFT OUTER JOIN dbo.LG_300_BANKACC
	ON LG_300_01_PAYTRANS_1.BANKACCREF = dbo.LG_300_BANKACC.LOGICALREF
RIGHT OUTER JOIN dbo.LG_300_01_PAYTRANS
	ON LG_300_01_PAYTRANS_1.LOGICALREF = dbo.LG_300_01_PAYTRANS.CROSSREF
		ON dbo.LG_300_01_INVOICE.TRCODE = dbo.LG_300_01_PAYTRANS.TRCODE
			AND dbo.LG_300_01_INVOICE.LOGICALREF = dbo.LG_300_01_PAYTRANS.FICHEREF WHERE (
			dbo.LG_300_01_PAYTRANS.TRCODE = 8
			OR dbo.LG_300_01_PAYTRANS.TRCODE = 14
			)
		AND (
			CASE LG_300_01_PAYTRANS_1.MODULENR
				WHEN 5
					THEN (
							CASE LG_300_01_PAYTRANS_1.TRCODE
								WHEN 4
									THEN 'İNDİRİM'
								WHEN 70
									THEN 'KREDİ KARTI'
								WHEN 5
									THEN 'VİRMAN'
								WHEN 14
									THEN 'AÇILIŞ FİŞİ'
								END
							)
				WHEN 10
					THEN (
							CASE LG_300_01_PAYTRANS_1.TRCODE
								WHEN 1
									THEN 'NAKİT'
								END
							)
				WHEN 6
					THEN (
							CASE LG_300_01_PAYTRANS_1.TRCODE
								WHEN 1
									THEN 'ÇEK'
								END
							)
				WHEN 7
					THEN (
							CASE LG_300_01_PAYTRANS_1.TRCODE
								WHEN 3
									THEN 'HAVALE'
								END
							)
				WHEN 4
					THEN (
							CASE LG_300_01_PAYTRANS_1.TRCODE
								WHEN 3
									THEN 'İADE FATURASI'
								WHEN 4
									THEN 'HİZMET FATURASI'
								WHEN 1
									THEN 'MAL FATURASI'
								END
							)
				END = 'ÇEK'
			)
		AND (GETDATE() - LG_300_01_PAYTRANS_1.PROCDATE <= 90)
		AND (
			LG_300_01_PAYTRANS_1.FICHEREF NOT IN (
				SELECT CST.ROLLREF
				FROM LG_300_01_CSTRANS CST
				WHERE CST.CSREF IN (
						SELECT CSC.LOGICALREF
						FROM LG_300_01_CSCARD CSC
						WHERE CSC.CURRSTAT = 8
						)
				)
			)
ORDER BY [CH ACIKLAMA]
GO