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

namespace WK5.Core.Basis.Filter.Statistik
{
    public class EARStatistikFilter : Filter
    {
        public DateTime From { get; set; }
        public DateTime To { get; set; }

        public EARStatistikFilter()
        {
            From = new DateTime(DateTime.Now.Year-1, 1, 1);
            To = new DateTime(DateTime.Now.Year - 1, 12, 31);
        }

        public override string ToSqlQuery(FbController2 fbController)
        {
            sqlBuilder.AppendLine(@"
select ARTI_A_NR, ARTI_A_BEZ1, BPOS_N_MENGE, ARTI_N_GEWICHT, BPOS_A_TYP, BPOS_N_NR from belegpos
left join Artikel on Artikel.ARTI_A_NR = belegpos.BPOS_A_ARTIKELNR 
where BPOS_A_TYP = 'LS'
AND BPOS_A_ARTIKELNR  <> 'DIVERS'
AND BPOS_A_ARTIKELNR <> 'KLOPAPIER'
AND BPOS_A_ARTIKELNR <> 'DIVERS'
AND BPOS_A_ARTIKELNR <> 'TEXT'
AND BPOS_A_ARTIKELNR not like 'PHG%'
AND Artikel.ARTI_A_NR in (
    SELECT distinct AB.ARBE_A_ARTINR FROM ARTIKELBEWEGUNG as AB
    left join ARTIKEL on AB.ARBE_A_ARTINR = ARTIKEL.ARTI_A_NR   
    WHERE ARBE_A_ART = 'ZUGA'
    AND ARBE_N_AENDERUNG > 0 
    AND ARTIKEL.WK5_ARTI_L_KEINE_ELEKTRONIK <> 'Y'
    AND AB.ARBE_A_NOTIZ2 <> 'Cipi - Brady BV -- '
    AND ARTIKEL.ARTI_A_NR <> 'DIVERS'
    AND ARTIKEL.ARTI_A_NR <> 'KLOPAPIER'
    AND ARTIKEL.ARTI_A_NR not like 'PHG%'
    AND ARBE_A_BELEG in(
        select ZUGA_N_NR 
        from Zugaenge 
        where ZUGA_N_LIEF in (select distinct LIEF_N_NR from LIEFERANTEN where LIEF_A_LAND <>'DE')
    )
)
AND BPOS_N_NR in
(
    SELECT DISTINCT BELE_N_NR from BELEGE
    WHERE BELE_D_DATE BETWEEN @FROM AND @TO    
    AND BELE_A_TYP = 'LS'
    AND BELE_A_LAND <>'DE'
)");

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

        protected override void BuildQueryConditions(FbController2 fbController)
        {            
        }
    }
}
