using System;

namespace WK5.Core.Basis.Filter.Statistik
{
    public class KundenUmsatzFilter : Filter
    {
        public Zeitraum Zeitraum { get; set; }
        public int AnzahlMaxErgebnisse { get; set; } = 30;
        public override string ToSqlQuery(FbController2 fbController)
        {
            sqlBuilder.Append(@$"SELECT FIRST {AnzahlMaxErgebnisse} SKIP 0 KUND_A_NR, KUND_A_NAME1, SUM(SUMME) AS UMSATZ FROM
(SELECT 
KUND_A_NR, 
KUND_A_NAME1,  
BELE_A_TYP,
CASE WHEN BELE_A_TYP = 'RE' THEN
     SUM(BELE_N_NETTO)
ELSE
     -SUM(BELE_N_NETTO)
END AS SUMME
FROM BELEGE B
LEFT JOIN KUNDEN K ON K.KUND_A_NR = B.BELE_A_KUNDENNR
WHERE BELE_A_TYP IN ('RE', 'GU')");
            BuildQueryConditions(fbController);
            sqlBuilder.Append(@" 
GROUP BY KUND_A_NR, KUND_A_NAME1, BELE_A_TYP)
GROUP BY KUND_A_NR, KUND_A_NAME1
ORDER BY SUM(SUMME) DESC");

            string sql = sqlBuilder.ToString();
            sqlBuilder.Clear();
            return sql;
        }

        protected override void BuildQueryConditions(FbController2 fbController)
        {
            sqlBuilder.Append(" AND B.BELE_D_DATE BETWEEN @START AND @ENDE");
            fbController.AddParameter("@START", Zeitraum.Von.ToShortDateString());
            fbController.AddParameter("@ENDE", Zeitraum.Bis.ToShortDateString());
        }

        public KundenUmsatzFilter()
        {
            Zeitraum = new Zeitraum(new DateTime(DateTime.Today.Year - 1, 1, 1), new DateTime(DateTime.Today.Year - 1, 12, 31));
        }
    }
}
