using System;

namespace WK5.Core.Basis.Filter
{
    public class KundenFilter : PageFilter
    {
        public override string ToCountQuery(FbController2 fbController)
        {
            sqlBuilder.Append("SELECT COUNT(*) as ANZAHL FROM KUNDEN WHERE 1 = 1");
            BuildQueryConditions(fbController);
            string tmp = sqlBuilder.ToString();
            sqlBuilder.Clear();
            return tmp;
        }

        public override string ToSqlQuery(FbController2 fbController)
        {
            sqlBuilder.Append(@$"SELECT FIRST {Limit} SKIP {(Seite - 1) * Limit} K.*,
(SELECT SUM(OP) AS OFFENEPOSTEN FROM
(
select
BELE_A_TYP, 
CASE WHEN BELE_A_TYP = 'GU' THEN
     -COALESCE(SUM(BELE_N_BRUTTO), 0) - coalesce(SUM(BezahltBetrag), 0)
ELSE
     COALESCE(SUM(BELE_N_BRUTTO), 0) - coalesce(SUM(BezahltBetrag), 0)
END AS OP
from
  KUNDENBELEGE
where
  (
    (
      BELE_A_TYP ='RE'
    and BELE_L_BEZAHLT = 'N'
    ) or
   (BELE_A_TYP ='GU' and BELE_L_VERRECHNET = 'N' )
  )
 and BELE_A_KUNDENNR = K.KUND_A_NR
 GROUP BY BELE_A_TYP))
FROM KUNDEN K  WHERE 1 = 1");
            BuildQueryConditions(fbController);

            sqlBuilder.Append(" ORDER BY K.KUND_WK5_N_WICHTIGKEIT DESC, KUND_A_NR DESC");


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

        protected override void BuildQueryConditions(FbController2 fbController)
        {
            if (!String.IsNullOrWhiteSpace(Suchbegriff))
            {
                if (Suchbegriff.Length > 255)
                {
                    throw new ArgumentOutOfRangeException(nameof(Suchbegriff));
                }

                sqlBuilder.Append(@$" AND 
(
   CAST(UPPER(KUND_A_NR) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(UPPER(KUND_A_SUCHCODE) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(UPPER(COALESCE(KUND_A_USTIDNR, '')) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(UPPER(KUND_A_PLZ) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(UPPER(KUND_A_NAME1) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(UPPER(KUND_A_NAME2) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(UPPER(KUND_A_NAME3) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(UPPER(KUND_A_ORT) AS VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(UPPER(KUND_A_GLN) AS VARCHAR(255)) LIKE @SUCHBEGRIFF)");

                fbController.AddParameter("@SUCHBEGRIFF", $"%{Suchbegriff}%");
            }
        }
    }

}
