using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WK5.Core.Basis.Filter
{
    public class WiederkehrendeRechnungenFilter : PageFilter
    {
        public bool IsFilterActive
        {
            get
            {
                if(!String.IsNullOrWhiteSpace(Suchbegriff))
                {
                    return true;
                }
                return false;
            }
        }
        public override string ToCountQuery(FbController2 fbController)
        {
            sqlBuilder.Append(@"SELECT COUNT(*)
FROM WK5_WDH_RECHNUNGEN WR
LEFT JOIN BELEGE B ON(B.BELE_N_NR = WR.WIED_N_NR AND B.BELE_A_TYP = 'RE')
LEFT JOIN KUNDEN K ON(K.KUND_A_NR = B.BELE_A_KUNDENNR)
WHERE 1 = 1");
            BuildQueryConditions(fbController);

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

        public override string ToSqlQuery(FbController2 fbController)
        {
            sqlBuilder.Append(@$"SELECT FIRST {Limit} SKIP {(Seite - 1) * Limit} WR.*, 
K.KUND_A_NAME1, K.KUND_A_NAME2, K.KUND_A_LAND, K.KUND_A_STRASSE, K.KUND_A_PLZ, K.KUND_A_ORT,
B.BELE_N_NR_AU, B.BELE_N_RECHADRESSE
FROM WK5_WDH_RECHNUNGEN WR
LEFT JOIN BELEGE B ON(B.BELE_N_NR = WR.WIED_N_NR AND B.BELE_A_TYP = 'RE')
LEFT JOIN KUNDEN K ON(K.KUND_A_NR = B.BELE_A_KUNDENNR)
WHERE 1 = 1");
            BuildQueryConditions(fbController);

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

        protected override void BuildQueryConditions(FbController2 fbController)
        {
            if (!string.IsNullOrWhiteSpace(Suchbegriff))
            {
                sqlBuilder.Append(@" AND 
(
CAST(UPPER(KUND_A_NAME1) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(UPPER(WIED_N_NR) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(UPPER(KUND_A_NR) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
)");
                fbController.AddParameter("@SUCHBEGRIFF", $"%{Suchbegriff}%");
            }
        }
    }
}
