Logo ERP SQL Sorgu Örnekleri

-- Tüm fatura türleri için detaylı rapor
SELECT
    INVOICE.FICHENO                    AS FaturaNo,
    INVOICE.DATE_                      AS Tarih,
    CASE INVOICE.TRCODE
        WHEN 1  THEN 'Satınalma Faturası'
        WHEN 6  THEN 'Satınalma İade'
        WHEN 7  THEN 'Perakende Satış'
        WHEN 8  THEN 'Toptan Satış'
        WHEN 2  THEN 'Perakende İade'
        WHEN 3  THEN 'Toptan İade'
        WHEN 9  THEN 'Verilen Hizmet'
        WHEN 4  THEN 'Alınan Hizmet'
        ELSE CAST(INVOICE.TRCODE AS VARCHAR)
    END                                AS FaturaTuru,
    INVOICE.DOCODE                     AS BelgeNo,
    CLCARD.CODE                        AS CariKod,
    CLCARD.DEFINITION_                 AS CariAd,
    CLCARD.TAXNR                       AS VergiNo,
    ITEMS.CODE                         AS StokKod,
    ITEMS.NAME                         AS StokAd,
    STLINE.AMOUNT                      AS Miktar,
    STLINE.PRICE                       AS BirimFiyat,
    STLINE.DISCPER                     AS IskontoOrani,
    STLINE.TOTAL                       AS SatirToplam,
    STLINE.VAT                         AS KDVOrani,
    STLINE.VATAMNT                     AS KDVTutari,
    STLINE.VATMATRAH                   AS NetTutar,
    INVOICE.GROSSTOTAL                 AS FaturaBrut,
    INVOICE.TOTALDISCOUNTS             AS ToplamIskonto,
    INVOICE.TOTALVAT                   AS ToplamKDV,
    INVOICE.NETTOTAL                   AS FaturaNet,
    INVOICE.SPECODE                    AS OzelKod,
    INVOICE.GENEXP1                    AS Aciklama
FROM LG_FFF_DD_INVOICE  INVOICE WITH (NOLOCK)
LEFT JOIN LG_FFF_DD_STLINE  STLINE  ON STLINE.INVOICEREF = INVOICE.LOGICALREF
LEFT JOIN LG_FFF_CLCARD     CLCARD  ON CLCARD.LOGICALREF = INVOICE.CLIENTREF
LEFT JOIN LG_FFF_ITEMS      ITEMS   ON ITEMS.LOGICALREF  = STLINE.STOCKREF
WHERE INVOICE.CANCELLED = 0
  AND INVOICE.TRCODE IN (7, 8)   -- Satış faturaları
  AND INVOICE.DATE_ BETWEEN '2026-01-01' AND '2026-12-31'
ORDER BY INVOICE.DATE_ DESC, INVOICE.FICHENO;

-- Cari hesap bakiyeleri (borç-alacak farkı)
SELECT
    CLCARD.CODE                                                    AS CariKod,
    CLCARD.DEFINITION_                                             AS CariAd,
    CLCARD.TAXNR                                                   AS VergiNo,
    CLCARD.CITY                                                    AS Sehir,
    CASE CLCARD.CARDTYPE
        WHEN 1 THEN 'Alıcı'
        WHEN 2 THEN 'Satıcı'
        WHEN 3 THEN 'Alıcı+Satıcı'
    END                                                            AS KartTipi,
    ROUND(SUM(CASE WHEN CLFLINE.SIGN = 0 THEN CLFLINE.AMOUNT ELSE 0 END), 2) AS ToplamBorc,
    ROUND(SUM(CASE WHEN CLFLINE.SIGN = 1 THEN CLFLINE.AMOUNT ELSE 0 END), 2) AS ToplamAlacak,
    ROUND(SUM(CASE WHEN CLFLINE.SIGN = 0 THEN  CLFLINE.AMOUNT
                   WHEN CLFLINE.SIGN = 1 THEN -CLFLINE.AMOUNT
                   ELSE 0 END), 2)                                 AS Bakiye,
    CASE
        WHEN SUM(CASE WHEN CLFLINE.SIGN=0 THEN CLFLINE.AMOUNT ELSE -CLFLINE.AMOUNT END) > 0 THEN 'Borçlu'
        WHEN SUM(CASE WHEN CLFLINE.SIGN=0 THEN CLFLINE.AMOUNT ELSE -CLFLINE.AMOUNT END) < 0 THEN 'Alacaklı'
        ELSE 'Sıfır'
    END                                                            AS BakiyeDurumu
FROM LG_FFF_CLCARD     CLCARD
JOIN LG_FFF_DD_CLFLINE CLFLINE ON CLFLINE.CLIENTREF = CLCARD.LOGICALREF
WHERE CLFLINE.CANCELLED = 0
  AND CLCARD.ACTIVE = 0
GROUP BY CLCARD.CODE, CLCARD.DEFINITION_, CLCARD.TAXNR, CLCARD.CITY, CLCARD.CARDTYPE
HAVING ROUND(SUM(CASE WHEN CLFLINE.SIGN=0 THEN CLFLINE.AMOUNT ELSE -CLFLINE.AMOUNT END), 2) <> 0
ORDER BY ABS(SUM(CASE WHEN CLFLINE.SIGN=0 THEN CLFLINE.AMOUNT ELSE -CLFLINE.AMOUNT END)) DESC;

-- Belirli bir cari hesabın ekstresi (tarih sıralı, kümülatif bakiye)
SELECT
    ROW_NUMBER() OVER (ORDER BY CLFLINE.DATE_, CLFLINE.LOGICALREF) AS SiraNo,
    CLFLINE.DATE_                                                    AS Tarih,
    CLFLINE.FICHENO                                                  AS FisNo,
    CASE CLFLINE.TRCODE
        WHEN 1  THEN 'Nakit Tahsilat'
        WHEN 2  THEN 'Kredi Kartı Tahsilat'
        WHEN 3  THEN 'Çek Tahsilat'
        WHEN 4  THEN 'Senet Tahsilat'
        WHEN 5  THEN 'Virman'
        WHEN 6  THEN 'Nakit Ödeme'
        WHEN 7  THEN 'Kredi Kartı Ödeme'
        WHEN 8  THEN 'Çek Ödeme'
        WHEN 9  THEN 'Senet Ödeme'
        WHEN 14 THEN 'Fatura'
        WHEN 15 THEN 'İrsaliye'
        ELSE CAST(CLFLINE.TRCODE AS VARCHAR)
    END                                                              AS HareketTuru,
    CLFLINE.DOCODE                                                   AS BelgeNo,
    CLFLINE.LINEEXP                                                  AS Aciklama,
    CASE WHEN CLFLINE.SIGN = 0 THEN CLFLINE.AMOUNT ELSE 0 END       AS Borc,
    CASE WHEN CLFLINE.SIGN = 1 THEN CLFLINE.AMOUNT ELSE 0 END       AS Alacak,
    SUM(CASE WHEN CLFLINE.SIGN=0 THEN CLFLINE.AMOUNT ELSE -CLFLINE.AMOUNT END)
        OVER (ORDER BY CLFLINE.DATE_, CLFLINE.LOGICALREF
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)      AS YuruyanBakiye
FROM LG_FFF_DD_CLFLINE CLFLINE
JOIN LG_FFF_CLCARD     CLCARD  ON CLCARD.LOGICALREF = CLFLINE.CLIENTREF
WHERE CLFLINE.CANCELLED = 0
  AND CLCARD.CODE = 'CARİ_KOD_BURAYA'   -- Cari kodu değiştirin
  AND CLFLINE.DATE_ BETWEEN '2026-01-01' AND '2026-12-31'
ORDER BY CLFLINE.DATE_, CLFLINE.LOGICALREF;

-- Anlık stok miktarı ve değeri
SELECT
    ITEMS.CODE                                                     AS StokKod,
    ITEMS.NAME                                                     AS StokAd,
    ITEMS.STGRPCODE                                                AS GrupKod,
    CASE ITEMS.CARDTYPE
        WHEN 1  THEN 'Ticari Mal'
        WHEN 10 THEN 'Hammadde'
        WHEN 11 THEN 'Yarı Mamul'
        WHEN 12 THEN 'Mamul'
        WHEN 4  THEN 'Hizmet'
    END                                                            AS MalzemeTuru,
    ROUND(SUM(CASE WHEN STLINE.IOCODE IN (0,1,2) THEN  STLINE.AMOUNT
                   WHEN STLINE.IOCODE IN (3,4)   THEN -STLINE.AMOUNT
                   ELSE 0 END), 4)                                 AS AnlikStok,
    ITEMS.SPECODE                                                  AS OzelKod,
    ITEMS.SPECODE2                                                 AS OzelKod2
FROM LG_FFF_ITEMS      ITEMS
LEFT JOIN LG_FFF_DD_STLINE STLINE ON STLINE.STOCKREF = ITEMS.LOGICALREF
                                  AND STLINE.CANCELLED = 0
                                  AND STLINE.LINETYPE  = 0
WHERE ITEMS.ACTIVE = 0
GROUP BY ITEMS.CODE, ITEMS.NAME, ITEMS.STGRPCODE, ITEMS.CARDTYPE,
         ITEMS.SPECODE, ITEMS.SPECODE2
HAVING ROUND(SUM(CASE WHEN STLINE.IOCODE IN (0,1,2) THEN STLINE.AMOUNT
                      WHEN STLINE.IOCODE IN (3,4)   THEN -STLINE.AMOUNT
                      ELSE 0 END), 4) <> 0
ORDER BY ITEMS.CODE;

-- Malzeme giriş/çıkış maliyet analizi
SELECT
    ITEMS.CODE                                                     AS MalzemeKodu,
    ITEMS.NAME                                                     AS MalzemeAdi,
    STLINE.DATE_                                                   AS Tarih,
    YEAR(STLINE.DATE_)                                             AS Yil,
    MONTH(STLINE.DATE_)                                            AS Ay,
    CASE STLINE.TRCODE
        WHEN 1  THEN 'Satınalma Faturası'
        WHEN 6  THEN 'Satınalma İade'
        WHEN 7  THEN 'Perakende Satış'
        WHEN 8  THEN 'Toptan Satış'
        WHEN 11 THEN 'Fire Fişi'
        WHEN 12 THEN 'Sarf Fişi'
        WHEN 13 THEN 'Üretimden Giriş'
        WHEN 14 THEN 'Devir Fişi'
        WHEN 25 THEN 'Ambar Fişi'
        WHEN 50 THEN 'Sayım Fazlası'
        WHEN 51 THEN 'Sayım Eksiği'
    END                                                            AS HareketTuru,
    CASE WHEN STLINE.IOCODE IN (0,1,2) THEN 'Giriş' ELSE 'Çıkış' END AS Yon,
    CASE WHEN STLINE.IOCODE IN (0,1,2) THEN STLINE.AMOUNT ELSE 0 END  AS GirenMiktar,
    CASE WHEN STLINE.IOCODE IN (3,4)   THEN STLINE.AMOUNT ELSE 0 END  AS CikanMiktar,
    ROUND(STLINE.VATMATRAH / NULLIF(STLINE.AMOUNT, 0), 4)         AS BirimFiyat,
    ROUND(STLINE.VATMATRAH, 2)                                     AS Tutar,
    ROUND(STLINE.OUTCOST, 4)                                       AS BirimMaliyet,
    ROUND(STLINE.AMOUNT * STLINE.OUTCOST, 2)                       AS MaliyetTutari
FROM LG_FFF_DD_STLINE STLINE WITH (NOLOCK)
JOIN LG_FFF_ITEMS     ITEMS  WITH (NOLOCK) ON ITEMS.LOGICALREF = STLINE.STOCKREF
WHERE STLINE.CANCELLED = 0
  AND STLINE.LINETYPE  = 0
  AND STLINE.TRCODE IN (1,2,3,6,7,8,11,12,13,14,25,50,51)
  AND YEAR(STLINE.DATE_) = YEAR(GETDATE())
ORDER BY STLINE.DATE_, ITEMS.CODE;

-- Vadesi gelecek çek ve senetler
SELECT
    CSCARD.NEWSERINO                                               AS CekNo,
    CSCARD.DUEDATE                                                 AS VadeTarihi,
    DATEDIFF(DAY, GETDATE(), CSCARD.DUEDATE)                       AS KalanGun,
    CASE
        WHEN DATEDIFF(DAY, GETDATE(), CSCARD.DUEDATE) < 0  THEN 'VADESİ GEÇMİŞ'
        WHEN DATEDIFF(DAY, GETDATE(), CSCARD.DUEDATE) <= 7 THEN 'BU HAFTA'
        WHEN DATEDIFF(DAY, GETDATE(), CSCARD.DUEDATE) <= 30 THEN 'BU AY'
        ELSE 'İLERİ TARİH'
    END                                                            AS VadeDurumu,
    CLCARD.CODE                                                    AS CariKod,
    CLCARD.DEFINITION_                                             AS CariAd,
    CSCARD.AMOUNT                                                  AS Tutar,
    CASE CSCARD.CSTYPE
        WHEN 1 THEN 'Müşteri Çeki'
        WHEN 2 THEN 'Müşteri Senedi'
        WHEN 3 THEN 'Firma Çeki'
        WHEN 4 THEN 'Firma Senedi'
    END                                                            AS CekTuru,
    CSCARD.PORTFOYNO                                               AS PortfoyNo,
    CSCARD.BANKNAME                                                AS BankaAdi
FROM LG_FFF_DD_CSCARD  CSCARD
LEFT JOIN LG_FFF_CLCARD CLCARD ON CLCARD.LOGICALREF = CSCARD.CLIENTREF
WHERE CSCARD.CANCELLED = 0
  AND CSCARD.PAID = 0
  AND CSCARD.DUEDATE >= DATEADD(DAY, -30, GETDATE())
ORDER BY CSCARD.DUEDATE ASC;

-- Muhasebe fişleri ve satırları
SELECT
    EMFICHE.FICHENO                                                AS FisNo,
    EMFICHE.DATE_                                                  AS Tarih,
    CASE EMFICHE.TRCODE
        WHEN 1 THEN 'Mahsup Fişi'
        WHEN 2 THEN 'Açılış Fişi'
        WHEN 3 THEN 'Kapanış Fişi'
        WHEN 4 THEN 'Tahsil Fişi'
        WHEN 5 THEN 'Tediye Fişi'
        WHEN 6 THEN 'Virman Fişi'
    END                                                            AS FisTuru,
    EMFICHE.DESCRIPTION                                            AS FisAciklamasi,
    EMUHACC.CODE                                                   AS HesapKodu,
    EMUHACC.DEFINITION_                                            AS HesapAdi,
    CASE WHEN EMFLINE.SIGN = 0 THEN EMFLINE.AMOUNT ELSE 0 END     AS Borc,
    CASE WHEN EMFLINE.SIGN = 1 THEN EMFLINE.AMOUNT ELSE 0 END     AS Alacak,
    EMFLINE.DESCRIPTION                                            AS SatirAciklamasi,
    EMFICHE.TOTALDEBIT                                             AS ToplamBorc,
    EMFICHE.TOTALCREDIT                                            AS ToplamAlacak
FROM LG_FFF_DD_EMFICHE EMFICHE
JOIN LG_FFF_DD_EMFLINE EMFLINE ON EMFLINE.FICHEREF   = EMFICHE.LOGICALREF
JOIN LG_FFF_EMUHACC    EMUHACC ON EMUHACC.LOGICALREF = EMFLINE.ACCOUNTREF
WHERE EMFICHE.CANCELLED = 0
  AND EMFICHE.DATE_ BETWEEN '2026-01-01' AND '2026-12-31'
ORDER BY EMFICHE.DATE_, EMFICHE.FICHENO, EMFLINE.LINEORDER;

-- Aylık satış analizi - müşteri ve ürün bazında
SELECT
    YEAR(INVOICE.DATE_)                                            AS Yil,
    MONTH(INVOICE.DATE_)                                           AS Ay,
    DATENAME(MONTH, INVOICE.DATE_)                                 AS AyAdi,
    CLCARD.CODE                                                    AS MusteriKod,
    CLCARD.DEFINITION_                                             AS MusteriAd,
    ITEMS.CODE                                                     AS UrunKod,
    ITEMS.NAME                                                     AS UrunAd,
    ITEMS.STGRPCODE                                                AS UrunGrubu,
    SUM(STLINE.AMOUNT)                                             AS ToplamMiktar,
    ROUND(SUM(STLINE.TOTAL), 2)                                    AS BrutTutar,
    ROUND(SUM(STLINE.VATAMNT), 2)                                  AS KDVTutari,
    ROUND(SUM(STLINE.VATMATRAH), 2)                                AS NetTutar,
    COUNT(DISTINCT INVOICE.LOGICALREF)                             AS FaturaAdedi
FROM LG_FFF_DD_INVOICE  INVOICE
JOIN LG_FFF_DD_STLINE   STLINE  ON STLINE.INVOICEREF  = INVOICE.LOGICALREF
JOIN LG_FFF_CLCARD      CLCARD  ON CLCARD.LOGICALREF  = INVOICE.CLIENTREF
JOIN LG_FFF_ITEMS       ITEMS   ON ITEMS.LOGICALREF   = STLINE.STOCKREF
WHERE INVOICE.CANCELLED = 0
  AND STLINE.CANCELLED  = 0
  AND STLINE.LINETYPE   = 0
  AND INVOICE.TRCODE IN (7, 8)
  AND YEAR(INVOICE.DATE_) = YEAR(GETDATE())
GROUP BY YEAR(INVOICE.DATE_), MONTH(INVOICE.DATE_), DATENAME(MONTH, INVOICE.DATE_),
         CLCARD.CODE, CLCARD.DEFINITION_, ITEMS.CODE, ITEMS.NAME, ITEMS.STGRPCODE
ORDER BY Yil, Ay, NetTutar DESC;

-- Ürün barkod ve güncel satış fiyatları
SELECT
    ITEMS.CODE                                                     AS StokKod,
    ITEMS.NAME                                                     AS StokAd,
    ITEMS.STGRPCODE                                                AS GrupKod,
    BARCODE.BARCODE                                                AS Barkod,
    UNITSETL.CODE                                                  AS Birim,
    PRCLIST.PRICE                                                  AS SatisFiyati,
    PRCLIST.CURRENCY                                               AS DovizTuru,
    PRCLIST.BEGDATE                                                AS FiyatBaslangic,
    PRCLIST.ENDDATE                                                AS FiyatBitis,
    ITEMS.VAT                                                      AS KDVOrani
FROM LG_FFF_ITEMS       ITEMS
LEFT JOIN LG_FFF_ITMUNITA    BARCODE  ON BARCODE.ITEMREF    = ITEMS.LOGICALREF
                                      AND BARCODE.LINENR    = 1
LEFT JOIN LG_FFF_UNITSETL    UNITSETL ON UNITSETL.UNITSETREF = ITEMS.UNITSETREF
                                      AND UNITSETL.LINENR   = 1
LEFT JOIN LG_FFF_PRCLIST     PRCLIST  ON PRCLIST.CARDREF    = ITEMS.LOGICALREF
                                      AND PRCLIST.PTYPE     = 2  -- Satış fiyatı
                                      AND PRCLIST.ACTIVE    = 0
                                      AND GETDATE() BETWEEN PRCLIST.BEGDATE AND PRCLIST.ENDDATE
WHERE ITEMS.ACTIVE = 0
  AND ITEMS.CODE <> 'ÿ'
ORDER BY ITEMS.CODE;