namespace WK5.Core.Basis.Filter
{
    public class BestelldispositionFilter : Filter
    {
        public int LieferantenId { get; set; }
        public override string ToSqlQuery(FbController2 fbController)
        {
            sqlBuilder.Clear();
            sqlBuilder.Append(@"SELECT ARTI_A_NR, ARTI_A_BEZ1, ARTI_L_ABVERKAUF,
cast((
    (
      coalesce(ARWE_N_BEDARF, 0) +
      coalesce(ARTI_N_MINBESTAND, 0)
    ) - (
      coalesce(ARWE_N_BESTAND, 0) +
      coalesce(ARWE_N_OFFBESTELL, 0)
    )
  ) * coalesce(ARTI_N_EK, 0) / iif(coalesce(ARTI_N_PREISEINHEITFAKTOR,0)=0,1,ARTI_N_PREISEINHEITFAKTOR)
as decimal(10,2))
as BESTELLVORSCHLAG_EURO, 
(COALESCE(ARWE_N_BEDARF,0) - COALESCE(ARWE_N_BESTAND,0) - COALESCE(AW.ARWE_N_OFFBESTELL,0) + COALESCE(A.ARTI_N_MINBESTAND,0)) AS BESTELLVORSCHLAG, 
AW.ARWE_N_BESTAND, AW.ARWE_N_BEDARF, A.ARTI_N_MINBESTAND, AW.ARWE_N_OFFBESTELL,
ARTI_L_INAKTIV,");
            if(LieferantenId > 0)
            {
                sqlBuilder.Append(@"
ARLI_N_MINDESTABNAHME,");
            }
            sqlBuilder.AppendLine(@"
(SELECT FIRST 1 SKIP 0 AB.ARBE_N_AENDERUNG FROM ARTIKELBEWEGUNG AB WHERE AB.ARBE_A_ARTINR = A.ARTI_A_NR AND AB.ARBE_A_ART = 'ZUGA' ORDER BY AB.ARBE_TIMESTAMP DESC) AS LETZTER_ZUGANG,
(
    SELECT CASE WHEN SUM(MENGE) IS NULL THEN 0 ELSE SUM(MENGE) END AS BEDARF FROM ARTIKEL_BEDARF AB 
    LEFT JOIN BELEGE B ON (B.BELE_A_TYP = 'AU' AND B.BELE_N_NR = AB.BPOS_N_NR)
    LEFT JOIN ZAHLUNGSBEDINGUNG Z ON Z.ZABD_N_NR = B.BELE_N_ZAHLUNG
    WHERE BPOS_A_ARTIKELNR = ARTI_A_NR AND (B.BELE_L_BEZAHLT = 'Y' OR Z.ZABD_L_FACTORING = 'Y' OR Z.ZABD_L_ABBUCHUNG = 'Y')
) AS BEZAHLTER_BEDARF
FROM ARTIKEL A
LEFT JOIN ARTIKELWERTE AW ON AW.ARWE_A_NR = A.ARTI_A_NR");

            BuildQueryConditions(fbController);


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

        }

        protected override void BuildQueryConditions(FbController2 fbController)
        {
            if (LieferantenId > 0)
            {
                sqlBuilder.AppendLine(@"
LEFT JOIN LIEFERANTENARTIKEL LFA ON LFA.ARLI_A_ARTIKELNR = A.ARTI_A_NR");
            }

            sqlBuilder.AppendLine(@"
WHERE (COALESCE(ARWE_N_BEDARF,0) - COALESCE(ARWE_N_BESTAND,0) - COALESCE(AW.ARWE_N_OFFBESTELL,0) + COALESCE(A.ARTI_N_MINBESTAND,0)) > 0
AND not exists(select ARST_N_POSITION from Artikelstueckliste where ARST_A_HAUPTARTI = ARTI_A_NR)
AND ARTI_L_LAGERFUEHR = 'Y'
AND ARTI_L_INAKTIV = 'N'");

            if (LieferantenId > 0)
            {
                sqlBuilder.Append(" AND ARLI_N_LIEFNR = @ARLI_N_LIEFNR");
                fbController.AddParameter("@ARLI_N_LIEFNR", LieferantenId);
            }
        }
    }

    

}
