130 lines
4.2 KiB
Go
130 lines
4.2 KiB
Go
package queries
|
||
|
||
import (
|
||
"bssapp-backend/db"
|
||
"bssapp-backend/models"
|
||
"database/sql"
|
||
"fmt"
|
||
"strings"
|
||
)
|
||
|
||
/* ============================ DETAIL (ALT TABLO) ============================ */
|
||
|
||
func GetStatementDetails(accountCode, startDate, endDate string, parislemler []string) ([]models.StatementDetail, error) {
|
||
// Parislemler filtresi hazırlanır (ör: 1,2,3)
|
||
inParislem := ""
|
||
if len(parislemler) > 0 {
|
||
pp := make([]string, len(parislemler))
|
||
for i, v := range parislemler {
|
||
pp[i] = strings.TrimSpace(v)
|
||
}
|
||
inParislem = strings.Join(pp, ",")
|
||
}
|
||
query := fmt.Sprintf(`
|
||
SELECT
|
||
CONVERT(varchar(10), a.InvoiceDate, 23) AS Belge_Tarihi,
|
||
a.InvoiceNumber AS Belge_Ref_Numarasi,
|
||
COALESCE(MAX(AnaGrupDesc.AttributeDescription), '') AS Urun_Ana_Grubu,
|
||
COALESCE(MAX(AltGrupDesc.AttributeDescription), '') AS Urun_Alt_Grubu,
|
||
COALESCE(MAX(GarsonDesc.AttributeDescription), '') AS Yetiskin_Garson,
|
||
COALESCE(MAX(FitDesc.AttributeDescription), '') AS Fit,
|
||
COALESCE(MAX(KisaKarDesc.AttributeDescription), '') AS Icerik,
|
||
a.ItemCode AS Urun_Kodu,
|
||
a.ColorCode AS Urun_Rengi,
|
||
SUM(a.Qty1) AS Toplam_Adet,
|
||
|
||
CAST(
|
||
SUM(a.Qty1 * ABS(a.Doc_Price))
|
||
/ NULLIF(SUM(a.Qty1),0)
|
||
AS numeric(18,4)) AS Doviz_Fiyat,
|
||
|
||
CAST(
|
||
SUM(a.Qty1 * ABS(a.Doc_Price))
|
||
AS numeric(18,2)) AS Toplam_Tutar
|
||
|
||
FROM AllInvoicesWithAttributes a
|
||
LEFT JOIN prItemAttribute AnaGrup
|
||
ON a.ItemCode = AnaGrup.ItemCode AND AnaGrup.AttributeTypeCode = 1
|
||
LEFT JOIN cdItemAttributeDesc AnaGrupDesc
|
||
ON AnaGrup.AttributeTypeCode = AnaGrupDesc.AttributeTypeCode
|
||
AND AnaGrup.AttributeCode = AnaGrupDesc.AttributeCode
|
||
AND AnaGrup.ItemTypeCode = AnaGrupDesc.ItemTypeCode
|
||
|
||
LEFT JOIN prItemAttribute AltGrup
|
||
ON a.ItemCode = AltGrup.ItemCode AND AltGrup.AttributeTypeCode = 2
|
||
LEFT JOIN cdItemAttributeDesc AltGrupDesc
|
||
ON AltGrup.AttributeTypeCode = AltGrupDesc.AttributeTypeCode
|
||
AND AltGrup.AttributeCode = AltGrupDesc.AttributeCode
|
||
AND AltGrup.ItemTypeCode = AltGrupDesc.ItemTypeCode
|
||
|
||
LEFT JOIN prItemAttribute Garson
|
||
ON a.ItemCode = Garson.ItemCode AND Garson.AttributeTypeCode = 44
|
||
LEFT JOIN cdItemAttributeDesc GarsonDesc
|
||
ON Garson.AttributeTypeCode = GarsonDesc.AttributeTypeCode
|
||
AND Garson.AttributeCode = GarsonDesc.AttributeCode
|
||
AND Garson.ItemTypeCode = GarsonDesc.ItemTypeCode
|
||
|
||
LEFT JOIN prItemAttribute FitTbl
|
||
ON a.ItemCode = FitTbl.ItemCode AND FitTbl.AttributeTypeCode = 38
|
||
LEFT JOIN cdItemAttributeDesc FitDesc
|
||
ON FitTbl.AttributeTypeCode = FitDesc.AttributeTypeCode
|
||
AND FitTbl.AttributeCode = FitDesc.AttributeCode
|
||
AND FitTbl.ItemTypeCode = FitDesc.ItemTypeCode
|
||
|
||
LEFT JOIN prItemAttribute KisaKar
|
||
ON a.ItemCode = KisaKar.ItemCode AND KisaKar.AttributeTypeCode = 41
|
||
LEFT JOIN cdItemAttributeDesc KisaKarDesc
|
||
ON KisaKar.AttributeTypeCode = KisaKarDesc.AttributeTypeCode
|
||
AND KisaKar.AttributeCode = KisaKarDesc.AttributeCode
|
||
AND KisaKar.ItemTypeCode = KisaKarDesc.ItemTypeCode
|
||
WHERE a.CurrAccCode LIKE @Carikod
|
||
AND a.InvoiceDate BETWEEN @StartDate AND @EndDate
|
||
%s
|
||
GROUP BY a.InvoiceDate, a.InvoiceNumber, a.ItemCode, a.ColorCode
|
||
ORDER BY Belge_Tarihi, Belge_Ref_Numarasi, Urun_Kodu;`,
|
||
func() string {
|
||
if inParislem == "" {
|
||
return ""
|
||
}
|
||
return fmt.Sprintf(`AND EXISTS (
|
||
SELECT 1
|
||
FROM CurrAccBookATAttributesFilter f
|
||
WHERE f.CurrAccBookID = a.CurrAccBookID
|
||
AND f.ATAtt01 IN (%s)
|
||
)`, inParislem)
|
||
}(),
|
||
)
|
||
rows, err := db.MssqlDB.Query(query,
|
||
sql.Named("Carikod", "%"+accountCode+"%"),
|
||
sql.Named("StartDate", startDate),
|
||
sql.Named("EndDate", endDate),
|
||
)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("detay sorgu hatası: %v", err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
var results []models.StatementDetail
|
||
for rows.Next() {
|
||
var d models.StatementDetail
|
||
if err := rows.Scan(
|
||
&d.BelgeTarihi,
|
||
&d.BelgeRefNumarasi,
|
||
&d.UrunAnaGrubu,
|
||
&d.UrunAltGrubu,
|
||
&d.YetiskinGarson,
|
||
&d.Fit,
|
||
&d.Icerik,
|
||
&d.UrunKodu,
|
||
&d.UrunRengi,
|
||
&d.ToplamAdet,
|
||
&d.ToplamFiyat,
|
||
&d.ToplamTutar,
|
||
); err != nil {
|
||
return nil, err
|
||
}
|
||
results = append(results, d)
|
||
}
|
||
return results, nil
|
||
}
|