bssapp/svc/queries/statement_header.go

package queries

import (
	"bssapp-backend/db"
	"bssapp-backend/models"
	"database/sql"
	"fmt"
	"strings"
)

// Ana tabloyu getiren fonksiyon (Vue header tablosu için)
func GetStatements(params models.StatementParams) ([]models.StatementHeader, error) {

	// AccountCode normalize: "ZLA0127" → "ZLA 0127"
	if len(params.AccountCode) == 7 && strings.ContainsAny(params.AccountCode, "0123456789") {
		params.AccountCode = params.AccountCode[:3] + " " + params.AccountCode[3:]
	}
	if strings.TrimSpace(params.LangCode) == "" {
		params.LangCode = "TR"
	}

	// Parislemler []string → '1','2','3'
	parislemFilter := "''"
	if len(params.Parislemler) > 0 {
		quoted := make([]string, 0, len(params.Parislemler))
		for _, v := range params.Parislemler {
			v = strings.TrimSpace(v)
			if v == "" {
				continue
			}
			quoted = append(quoted, fmt.Sprintf("'%s'", strings.ReplaceAll(v, "'", "''")))
		}
		if len(quoted) > 0 {
			parislemFilter = strings.Join(quoted, ",")
		}
	}

	query := fmt.Sprintf(`
;WITH CurrDesc AS (
    SELECT
        CurrAccCode,
        MAX(CurrAccDescription) AS CurrAccDescription
    FROM cdCurrAccDesc
    WHERE LangCode = @LangCode
    GROUP BY CurrAccCode
),

/* =========================================================
   ✅ Bu aralıkta hareket var mı?
   Varsa : Devir = startdate öncesi
   Yoksa : Devir = enddate dahil (enddate itibariyle bakiye)
   ========================================================= */
HasMovement AS (
    SELECT
        CASE WHEN EXISTS (
            SELECT 1
            FROM trCurrAccBook b
            INNER JOIN CurrAccBookATAttributesFilter f
              ON f.CurrAccBookID = b.CurrAccBookID
             AND f.ATAtt01 IN (%s)
            WHERE b.CurrAccCode LIKE '%%' + @Carikod + '%%'
              AND b.DocumentDate BETWEEN @startdate AND @enddate
        ) THEN 1 ELSE 0 END AS HasMov
),

/* =========================================================
   ✅ Opening (Devir) — TEK CARİ KOD ALTINDA KONSOLİDE
   Cari_Kod = @Carikod (sabit)
   ========================================================= */
Opening AS (
    SELECT
        @Carikod           AS Cari_Kod,
        b.DocCurrencyCode  AS Para_Birimi,
        SUM(ISNULL(c.Debit,0) - ISNULL(c.Credit,0)) AS Devir_Bakiyesi
    FROM trCurrAccBook b
    CROSS JOIN HasMovement hm
    INNER JOIN CurrAccBookATAttributesFilter f2
      ON f2.CurrAccBookID = b.CurrAccBookID
     AND f2.ATAtt01 IN (%s)
    LEFT JOIN trCurrAccBookCurrency c
      ON c.CurrAccBookID = b.CurrAccBookID
     AND c.CurrencyCode  = b.DocCurrencyCode
    WHERE b.CurrAccCode LIKE '%%' + @Carikod + '%%'
      AND (
            (hm.HasMov = 1 AND b.DocumentDate <  @startdate)  -- hareket varsa: klasik devir
         OR (hm.HasMov = 0 AND b.DocumentDate <= @enddate)    -- hareket yoksa: enddate itibariyle bakiye
      )
    GROUP BY b.DocCurrencyCode
),

/* =========================================================
   ✅ Hareketler (Movements) — TEK CARİ KOD ALTINDA KONSOLİDE
   Cari_Kod = @Carikod (sabit)
   Running sadece aralıktaki hareketlerden gelir.
   ========================================================= */
Movements AS (
    SELECT
        @Carikod AS Cari_Kod,

        COALESCE(
            (SELECT TOP 1 cd.CurrAccDescription
             FROM CurrDesc cd
             WHERE cd.CurrAccCode = @Carikod),
            (SELECT TOP 1 cd.CurrAccDescription
             FROM CurrDesc cd
             WHERE cd.CurrAccCode LIKE '%%' + @Carikod + '%%'
             ORDER BY cd.CurrAccCode)
        ) AS Cari_Isim,

        CONVERT(varchar(10), b.DocumentDate, 23) AS Belge_Tarihi,
        CONVERT(varchar(10), b.DueDate, 23)      AS Vade_Tarihi,

        b.RefNumber           AS Belge_No,
        b.BaseApplicationCode AS Islem_Tipi,
        b.LineDescription     AS Aciklama,

        b.DocCurrencyCode     AS Para_Birimi,

        ISNULL(c.Debit,0)     AS Borc,
        ISNULL(c.Credit,0)    AS Alacak,

        SUM(ISNULL(c.Debit,0) - ISNULL(c.Credit,0))
            OVER (
              PARTITION BY b.DocCurrencyCode
              ORDER BY b.DocumentDate, b.CurrAccBookID
            ) AS Hareket_Bakiyesi,

        f.ATAtt01 AS Parislemtipi

    FROM trCurrAccBook b
    INNER JOIN CurrAccBookATAttributesFilter f
      ON f.CurrAccBookID = b.CurrAccBookID
     AND f.ATAtt01 IN (%s)
    LEFT JOIN trCurrAccBookCurrency c
      ON c.CurrAccBookID = b.CurrAccBookID
     AND c.CurrencyCode  = b.DocCurrencyCode

    WHERE b.CurrAccCode LIKE '%%' + @Carikod + '%%'
      AND b.DocumentDate BETWEEN @startdate AND @enddate
)

SELECT
    m.Cari_Kod,
    m.Cari_Isim,
    m.Belge_Tarihi,
    m.Vade_Tarihi,
    m.Belge_No,
    m.Islem_Tipi,
    m.Aciklama,
    m.Para_Birimi,
    m.Borc,
    m.Alacak,

    /* ✅ Bakiye = Devir + Aralıktaki Running */
    ISNULL(o.Devir_Bakiyesi,0) + m.Hareket_Bakiyesi AS Bakiye,

    m.Parislemtipi AS Parislemler

FROM Movements m
LEFT JOIN Opening o
  ON o.Cari_Kod    = m.Cari_Kod
 AND o.Para_Birimi = m.Para_Birimi

UNION ALL

/* =========================================================
   ✅ Devir Satırı (kur bazında) — Opening'den gelir
   Hareket varsa: startdate öncesi
   Hareket yoksa: enddate itibariyle bakiye
   ========================================================= */
SELECT
    o.Cari_Kod,
    COALESCE(
        (SELECT TOP 1 cd.CurrAccDescription
         FROM CurrDesc cd
         WHERE cd.CurrAccCode = @Carikod),
        (SELECT TOP 1 cd.CurrAccDescription
         FROM CurrDesc cd
         WHERE cd.CurrAccCode LIKE '%%' + @Carikod + '%%'
         ORDER BY cd.CurrAccCode)
    ) AS Cari_Isim,

    CONVERT(varchar(10), @startdate, 23) AS Belge_Tarihi,
    CONVERT(varchar(10), @startdate, 23) AS Vade_Tarihi,

    'Baslangic_devir' AS Belge_No,
    'Devir'           AS Islem_Tipi,
    'Devir Bakiyesi'  AS Aciklama,

    o.Para_Birimi,

    CASE WHEN o.Devir_Bakiyesi >= 0 THEN o.Devir_Bakiyesi ELSE 0 END AS Borc,
    CASE WHEN o.Devir_Bakiyesi <  0 THEN ABS(o.Devir_Bakiyesi) ELSE 0 END AS Alacak,

    o.Devir_Bakiyesi AS Bakiye,

    CAST(NULL AS varchar(32)) AS Parislemler

FROM Opening o

ORDER BY
    Para_Birimi,
    Belge_Tarihi;
`,
		parislemFilter, // HasMovement
		parislemFilter, // Opening
		parislemFilter, // Movements
	)

	rows, err := db.MssqlDB.Query(query,
		sql.Named("startdate", params.StartDate),
		sql.Named("enddate", params.EndDate),
		sql.Named("Carikod", params.AccountCode),
		sql.Named("LangCode", params.LangCode),
	)
	if err != nil {
		return nil, fmt.Errorf("MSSQL query error: %v", err)
	}
	defer rows.Close()

	var results []models.StatementHeader
	for rows.Next() {
		var r models.StatementHeader
		if err := rows.Scan(
			&r.CariKod,
			&r.CariIsim,
			&r.BelgeTarihi,
			&r.VadeTarihi,
			&r.BelgeNo,
			&r.IslemTipi,
			&r.Aciklama,
			&r.ParaBirimi,
			&r.Borc,
			&r.Alacak,
			&r.Bakiye,
			&r.Parislemler,
		); err != nil {
			return nil, err
		}
		results = append(results, r)
	}
	return results, nil
}