CREATE PROC [dbo].[SP_RAPORLAR_AYSONU_ISKONTO]
(
@ilktarih DATE,
@sontarih DATE,
@odemetarih DATE
)
AS
BEGIN
SELECT
QQ.CODE AS [FRMKODU],
QQ.DEFINITION_ AS [FRMUNV],
UPPER(QQ.CITY) AS [SEHIR],
QQ.SPECODE2 AS [PLASIYER],
QQ.[ISKONTO],
QQ.[SATIS] AS [SATISTOPLAMI],
QQ.IADE AS [IADETOPLAMI],
QQ.[SATIS]-QQ.IADE AS [AYLIKTOPLAM],
(QQ.[SATIS]-QQ.IADE)*(ISKONTO/100) AS [INDIRIM],
((QQ.[SATIS]-QQ.IADE) - ((QQ.[SATIS]-QQ.IADE)*(ISKONTO/100))) AS [ODENECEKTUTAR],
QQ.MAY AS [AYICIODEME],
QQ.HAZ AS [AYSONUODEME],
QQ.ODEME AS [TOPLAMODEME],
QQ.BAK AS [ARTIKBORC],
QQ.SON AS [SONBAK],
CASE WHEN (QQ.ODEME-(QQ.BAK+((QQ.[SATIS]-QQ.IADE) - ((QQ.[SATIS]-QQ.IADE)*(ISKONTO/100)))))>0 THEN 0 ELSE ABS((QQ.ODEME-(QQ.BAK+((QQ.[SATIS]-QQ.IADE) - ((QQ.[SATIS]-QQ.IADE)*(ISKONTO/100)))))) END AS [KALAN],
FIYATFARK,FIYATFARKEXP,
CASE WHEN QQ.ODEME=0 THEN 'BEKLEMEDE' WHEN (QQ.ODEME-(QQ.BAK+((QQ.[SATIS]-QQ.IADE) - ((QQ.[SATIS]-QQ.IADE)*(ISKONTO/100)))))>0 THEN 'TAMAMLANDI' ELSE 'EKSIK ÖDEME' END AS DURUM
FROM
(
(SELECT
CC.CODE,
CC.DEFINITION_,
CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CC.SPECODE,' ',''),'AYSONU', ''),'T', ''),'fis', ''),'N', ''),'+', ''),':', '') AS FLOAT) AS ISKONTO,
CC.CITY,
CC.SPECODE2,
ISNULL((SELECT SUM(NETTOTAL) FROM LG_011_07_INVOICE AS SAT WITH (NOLOCK) WHERE CC.LOGICALREF=SAT.CLIENTREF AND SAT.TRCODE=8 AND SAT.CANCELLED=0 AND SAT.DATE_ BETWEEN @ilktarih AND @sontarih),0) AS SATIS,
ISNULL((SELECT SUM(NETTOTAL) FROM LG_011_07_INVOICE AS SAT WITH (NOLOCK) WHERE CC.LOGICALREF=SAT.CLIENTREF AND SAT.TRCODE=3 AND SAT.CANCELLED=0 AND SAT.DATE_ BETWEEN @ilktarih AND @sontarih),0) AS IADE,
ISNULL((SELECT SUM(CLF.TRNET) FROM LG_011_07_CLFLINE AS CLF WITH (NOLOCK) WHERE CLF.CLIENTREF=CC.LOGICALREF AND CLF.CANCELLED=0 AND TRCODE IN (1,4,20,70) AND CLF.DATE_ BETWEEN @ilktarih AND @odemetarih),0)-ISNULL((SELECT TOP 1 CLF.TRNET FROM LG_011_07_CLFLINE AS CLF WITH (NOLOCK) WHERE CLF.CLIENTREF=CC.LOGICALREF AND CLF.CANCELLED=0 AND CLF.TRCODE=4 AND LINEEXP LIKE '%AY%' AND (LINEEXP LIKE '%ISK%' OR LINEEXP LIKE '%ISK%') AND CLF.DATE_ > @sontarih AND CLF.DATE_ <= @odemetarih ORDER BY CLF.DATE_ DESC),0) AS ODEME,
ISNULL((SELECT SUM(CLF.TRNET) FROM LG_011_07_CLFLINE AS CLF WITH (NOLOCK) WHERE CLF.CLIENTREF=CC.LOGICALREF AND CLF.CANCELLED=0 AND TRCODE IN (1,4,20,70) AND CLF.DATE_ > @sontarih AND CLF.DATE_ <= @odemetarih),0)-ISNULL((SELECT TOP 1 CLF.TRNET FROM LG_011_07_CLFLINE AS CLF WITH (NOLOCK) WHERE CLF.CLIENTREF=CC.LOGICALREF AND CLF.CANCELLED=0 AND CLF.TRCODE=4 AND LINEEXP LIKE '%AY%' AND (LINEEXP LIKE '%ISK%' OR LINEEXP LIKE '%ISK%') AND CLF.DATE_ > @sontarih AND CLF.DATE_ <= @odemetarih ORDER BY CLF.DATE_ DESC),0) AS HAZ,
ISNULL((SELECT SUM(CLF.TRNET) FROM LG_011_07_CLFLINE AS CLF WITH (NOLOCK) WHERE CLF.CLIENTREF=CC.LOGICALREF AND CLF.CANCELLED=0 AND TRCODE IN (1,4,20,70) AND CLF.DATE_ BETWEEN @ilktarih AND @sontarih),0) AS MAY,
ISNULL((SELECT (SUM((1-CLF.SIGN) * CLF.TRNET) - SUM(CLF.SIGN*CLF.TRNET)) FROM LG_011_07_CLFLINE AS CLF WITH (NOLOCK) WHERE CLF.CLIENTREF=CC.LOGICALREF AND CLF.CANCELLED=0 AND CLF.DATE_<@ilktarih),0) AS BAK,
ISNULL((SELECT (SUM((1-CLF.SIGN) * CLF.TRNET) - SUM(CLF.SIGN*CLF.TRNET)) FROM LG_011_07_CLFLINE AS CLF WITH (NOLOCK) WHERE CLF.CLIENTREF=CC.LOGICALREF AND CLF.CANCELLED=0 AND CLF.DATE_<@sontarih),0) AS SON,
ISNULL((SELECT TOP 1 CLF.TRNET FROM LG_011_07_CLFLINE AS CLF WITH (NOLOCK) WHERE CLF.CLIENTREF=CC.LOGICALREF AND CLF.CANCELLED=0 AND CLF.TRCODE=4 AND LINEEXP LIKE '%AY%' AND (LINEEXP LIKE '%ISK%' OR LINEEXP LIKE '%ISK%') AND CLF.DATE_ > @sontarih ORDER BY CLF.DATE_ DESC),0) AS [FIYATFARK],
ISNULL((SELECT TOP 1 CONCAT(CONCAT(CLF.LINEEXP, ' '),CONVERT(VARCHAR, CLF.DATE_,104)) FROM LG_011_07_CLFLINE AS CLF WITH (NOLOCK) WHERE CLF.CLIENTREF=CC.LOGICALREF AND CLF.CANCELLED=0 AND CLF.TRCODE=4 AND LINEEXP LIKE '%AY%' AND (LINEEXP LIKE '%ISK%' OR LINEEXP LIKE '%ISK%') AND CLF.DATE_ > @sontarih ORDER BY CLF.DATE_ DESC),'') AS [FIYATFARKEXP]
--ISNULL((SELECT TOP 1 CONVERT(VARCHAR, CLF.DATE_,104) FROM LG_011_06_CLFLINE AS CLF WITH (NOLOCK) WHERE CLF.CLIENTREF=CC.LOGICALREF AND CLF.CANCELLED=0 AND CLF.TRCODE=4 AND LINEEXP LIKE '%AY%' AND (LINEEXP LIKE '%ISK%' OR LINEEXP LIKE '%ISK%') AND CLF.DATE_ > @sontarih AND CLF.DATE_ <= @odemetarih ORDER BY CLF.DATE_ DESC),'') AS [FIYATFARKDATE]
FROM
LG_011_CLCARD AS CC WITH (NOLOCK)
WHERE CC.ACTIVE=0 AND CC.SPECODE<>''
GROUP BY
CC.CODE,
CC.DEFINITION_,
CC.SPECODE,
CC.CITY,
CC.SPECODE2,
CC.LOGICALREF)
)QQ
END