using BrightIdeasSoftware;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;
using System.Web.UI.WebControls;

namespace DatenDB
{
  public class OLVBewertung
  {
    private static string TABLE = "kundenverwaltung.q_bewertung_neu";
    //                                  0         1         2       3       4     5       6       7         8     9         10            11          12        13      14        15       16                   17             18           19              20          21              22          23
    private static string COLUMNS = "kunde_id, tour_id, kunde_nr, name1, name2, straße, plz, bezeichnung, land, aktiv, bettenanzahl, kunde_gruppe, service, region, waescheart, umsatz, umsatz_pro_gast, var_kost_anteil, fixkosten, erfolg_pro_gast, erfolg_gesamt, rating";
    private static string SUMCOLUMNS = "kunde_id, min(tour_id) as tour_id, min(kunde_nr) as kunde_nr, min(name1) as name1, min(name2) as name2, min(straße) as straße, min(plz) as plz, min(bezeichnung) as bezeichnung, min(land) as land, aktiv, min(bettenanzahl) as bettenanzahl, min(kunde_gruppe) as kunde_gruppe, min(service) as service, min(region) as region, min(waescheart) as waescheart, sum(umsatz) as umsatz, sum(umsatz_pro_gast) as umsatz_pro_gast, sum(var_kost_anteil) as var_kost_anteil, sum(fixkosten) as fixkosten, sum(erfolg_pro_gast) as erfolg_pro_gast, sum(erfolg_gesamt) as erfolg_gesamt, sum(rating) as rating";
    public static Umsatz result;
    public int aktjahr;
    public int vpjahr;
    public static List<Umsatz> ulist;
    public OLVBewertung()
    {
    }
    public static int[] GetLast()
    {
      DatenbankConnection.GetConnection().Open();
      int[] result = new int[2];
      NpgsqlCommand command = new NpgsqlCommand();
      command.Connection = DatenbankConnection.GetConnection();
      command.CommandText = $"select max(quartal) as quartal, max(jahr) as jahr from {TABLE} where umsatz is not null";

      NpgsqlDataReader reader = command.ExecuteReader();
      while (reader.Read())
      {
        result[0] = (int)reader.GetDouble(0);
        result[1] = (int)reader.GetDouble(1);
      }
      reader.Close();
      DatenbankConnection.GetConnection().Close();
      return result;
    }
    public static List<OLVBewertung> GetList(int? aktjahr, int? monat, int? quartal, bool aktiv, int vpjahr, int[] quartale, int? kndid)
    {
      
      DatenbankConnection.GetConnection().Open();
      List<OLVBewertung> resultList = new List<OLVBewertung>();
      NpgsqlCommand command = new NpgsqlCommand();
      command.Connection = DatenbankConnection.GetConnection();

      if(kndid == null || kndid == 0)
      {
        if (monat == null || monat == 0)
        {
          if (quartal == null || quartal == 0)
          {
            if (quartale == null || quartale.Length == 0)
            {
              command.CommandText = $"select {SUMCOLUMNS} from {TABLE} where jahr = {aktjahr} and aktiv = {aktiv} group by aktiv, kunde_id, jahr order by jahr";
            }
            else
            {
              command.CommandText = $"select {SUMCOLUMNS} from {TABLE} where aktiv = {aktiv} and jahr = {aktjahr} and quartal between {quartale.Min()} and {quartale.Max()} group by aktiv, kunde_id order by kunde_id";
            } //mit DB klären
          }
          else
          {
            if (quartal == 4) command.CommandText = $"select {SUMCOLUMNS} from {TABLE} where aktiv = {aktiv} and jahr = {aktjahr} group by aktiv, kunde_id, jahr order by jahr";
            else command.CommandText = $"select {COLUMNS} from {TABLE} where aktiv = {aktiv} quartal = {quartal} and jahr = {aktjahr} order by jahr, quartal";
          }
        }
        else command.CommandText = $"select {COLUMNS} from kundenverwaltung.m_bewertung where aktiv = {aktiv} and jahr = {aktjahr} and monat = {monat}";

      }
      else
      {
        if (monat == null || monat == 0)
        {
          if (quartal == null || quartal == 0)
          {
            if (quartale == null || quartale.Length == 0)
            {
              command.CommandText = $"select {SUMCOLUMNS} from {TABLE} where kunde_id = {kndid} and jahr = {aktjahr} group by aktiv, kunde_id, jahr order by jahr";
            }
            else
            {
              command.CommandText = $"select {SUMCOLUMNS} from {TABLE} where kunde_id = {kndid} and jahr = {aktjahr} and quartal between {quartale.Min()} and {quartale.Max()} group by aktiv, kunde_id order by kunde_id";
            } //mit DB klären
          }
          else
          {
            if (quartal == 4) command.CommandText = $"select {SUMCOLUMNS} from {TABLE} where kunde_id = {kndid} and jahr = {aktjahr} group by aktiv, kunde_id, jahr order by jahr";
            else command.CommandText = $"select {COLUMNS} from {TABLE} where kunde_id = {kndid} and quartal = {quartal} and jahr = {aktjahr} order by jahr, quartal";
          }
        }
        else command.CommandText = $"select {COLUMNS} from kundenverwaltung.m_bewertung where kunde_id = {kndid} and jahr = {aktjahr} and monat = {monat}";
      }

      NpgsqlDataReader reader = command.ExecuteReader();
      while (reader.Read()) resultList.Add(new OLVBewertung(reader));
      reader.Close();
      DatenbankConnection.GetConnection().Close();

      //Umsatzliste Vorperiode holen und Betrag in OLVBewertung übertragen.
      ulist = new List<Umsatz>(Umsatz.GetList(aktjahr, monat, quartal, aktiv, vpjahr, quartale));
      foreach (OLVBewertung item in resultList)
      {
        result = new Umsatz();
        result = ulist.Find(x => x.KundeID.Equals(item.KundeID));
        if (result == null) item.UmsatzVorperiode = 0;
        else item.UmsatzVorperiode = result.Betrag;
        item.DifferenzEUR = Berechnungen.Differenz_berechnen(item.Umsatz1, item.UmsatzVorperiode)[0];
        item.DifferenzPRO = Berechnungen.Differenz_berechnen(item.Umsatz1, item.UmsatzVorperiode)[1];
      }
       
      return resultList;
    }
    public static List<OLVBewertung> GetMonatList(int? aktjahr, int? monat, int? quartal, bool aktiv, int vpjahr)
    {
      DatenbankConnection.GetConnection().Open();
      List<OLVBewertung> resultList = new List<OLVBewertung>();
      NpgsqlCommand command = new NpgsqlCommand();
      command.Connection = DatenbankConnection.GetConnection();
      if (monat != 0)
      {
        command.CommandText = $"select {COLUMNS} from kundenverwaltung.umsatz where quartal = {quartal} and jahr = {aktjahr} order by jahr, quartal";
      }
      NpgsqlDataReader reader = command.ExecuteReader();
      while (reader.Read()) resultList.Add(new OLVBewertung(reader));
      reader.Close();
      DatenbankConnection.GetConnection().Close();

      return resultList;
    }
    public static double[] GetTopLast(int? aktjahr, int? monat, int? quartal, bool aktiv, int vpjahr)
    {
      DatenbankConnection.GetConnection().Open();
      double[] result = new double[2];
      NpgsqlCommand command = new NpgsqlCommand();
      command.Connection = DatenbankConnection.GetConnection();
      if (quartal == null || quartal == 0)
      {
       
      }
      else
      {
        if (quartal == 4) command.CommandText = $"select min(erfolg_pro_gast), max(erfolg_pro_gast) from kundenverwaltung.jahresbewertung where jahr = {aktjahr}";
        else command.CommandText = $"select min(erfolg_pro_gast), max(erfolg_pro_gast) from {TABLE} where quartal = {quartal} and jahr = {aktjahr}";

      }

      NpgsqlDataReader reader = command.ExecuteReader();
      while (reader.Read())
      {
        result[0] = reader.GetDouble(0);
        result[1] = reader.GetDouble(1);
      }
      reader.Close();
      DatenbankConnection.GetConnection().Close();

      return result;
    }
    public OLVBewertung(NpgsqlDataReader reader)
    {
      this.KundeID = reader.GetInt32(0);
      this.TourID = reader.IsDBNull(1) ? null : (int?)reader.GetInt32(1);
      this.KundeNummer = reader.GetString(2);
      this.KundeName = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
      this.KundeName2 = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
      this.Strasse = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
      this.PLZ = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
      this.Ort = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
      this.Land = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
      this.Aktiv = reader.GetBoolean(9);
      this.Bettenanzahl = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10);
      this.KundeGruppe = reader.IsDBNull(11) ? string.Empty : reader.GetString(11);
      this.Service = reader.IsDBNull(12) ? string.Empty : reader.GetString(12);
      this.Region = reader.IsDBNull(13) ? string.Empty : reader.GetString(13);
      this.Waescheart = reader.IsDBNull(14) ? string.Empty : reader.GetString(14);
      //this.Quartal = reader.IsDBNull(15) ? 0 : reader.GetDouble(15);
      //this.Jahr = reader.IsDBNull(16) ? 0 : reader.GetDouble(16);
      this.Umsatz1 = reader.IsDBNull(15) ? 0 : reader.GetDouble(15);          
      this.UmsatzProGast = reader.IsDBNull(16) ? 0 : reader.GetDouble(16);
      this.KostenAnteilVariabel = reader.IsDBNull(17) ? 0 : reader.GetDouble(17);
      this.FixkostenAnteil = reader.IsDBNull(18) ? 0 : reader.GetDouble(18);
      this.ErfolgProGast = reader.IsDBNull(19) ? 0 : reader.GetDouble(19);
      this.ErfolgGesamt = reader.IsDBNull(20) ? 0 : reader.GetDouble(20);
      this.Bewertung = reader.IsDBNull(21) ? 0 : reader.GetDouble(21);

    }

    [OLVIgnore]
    public int? KundeID { get; set; }

    [OLVIgnore]
    public int? TourID { get; set; }

    [OLVColumn(IsVisible = false)]
    public string KundeGruppe { get; set; }

    [OLVColumn("KndNr.", DisplayIndex = 1, IsTileViewColumn = true, TextAlign = System.Windows.Forms.HorizontalAlignment.Center)]
    public string KundeNummer { get; set; }

    [OLVColumn("Name", DisplayIndex = 2)]
    public string KundeName { get; set; }

    [OLVColumn("Zusatz", DisplayIndex = 3, IsVisible = false)]
    public string KundeName2 { get; set; }

    [OLVColumn(DisplayIndex = 4)]
    public string Strasse { get; set; }

    [OLVColumn(DisplayIndex = 6, TextAlign = System.Windows.Forms.HorizontalAlignment.Center)]
    public int? PLZ { get; set; }

    [OLVColumn(DisplayIndex = 7)]
    public string Ort { get; set; }

    [OLVColumn(DisplayIndex = 8, TextAlign = System.Windows.Forms.HorizontalAlignment.Center)]
    public string Land { get; set; }

    [OLVColumn("Betten", DisplayIndex = 9, TextAlign = System.Windows.Forms.HorizontalAlignment.Center)]
    public int? Bettenanzahl { get; set; }

    [OLVColumn(CheckBoxes = true, DisplayIndex = 10, IsVisible = false)]
    public bool Aktiv { get; set; }

    [OLVColumn(DisplayIndex = 11)]
    public string Service { get; set; }

    [OLVColumn(DisplayIndex = 12)]
    public string Region { get; set; }

    [OLVColumn(DisplayIndex = 13)]
    public string Waescheart { get; set; }

    [OLVColumn("Umsatz AP", DisplayIndex = 17, AspectToStringFormat = "{0:C}", TextAlign = System.Windows.Forms.HorizontalAlignment.Right)]
    public double Umsatz1{ get; set; }

    [OLVColumn("Umsatz VP", DisplayIndex = 16, AspectToStringFormat = "{0:C}")]
    public double UmsatzVorperiode { get; set; }

    [OLVColumn(DisplayIndex = 18, AspectToStringFormat = "{0:C}", TextAlign = System.Windows.Forms.HorizontalAlignment.Right)]
    public double DifferenzEUR { get; set; }

    [OLVColumn(DisplayIndex = 19, AspectToStringFormat = "{0:P1}", TextAlign = System.Windows.Forms.HorizontalAlignment.Right)]
    public double DifferenzPRO { get; set; }

    [OLVColumn("Var.Kosten", DisplayIndex = 20, AspectToStringFormat = "{0:C}", TextAlign = System.Windows.Forms.HorizontalAlignment.Right)]
    public double KostenAnteilVariabel { get; set; }

    [OLVColumn("Umsatz/Gast", DisplayIndex = 21, AspectToStringFormat = "{0:C}", TextAlign = System.Windows.Forms.HorizontalAlignment.Right)]
    public double UmsatzProGast { get; set; }

    [OLVColumn("Fix.Kosten", DisplayIndex = 22, AspectToStringFormat = "{0:C}", TextAlign = System.Windows.Forms.HorizontalAlignment.Right)]
    public double FixkostenAnteil { get; set; }

    [OLVColumn("Erfolg/Gast", DisplayIndex = 23, AspectToStringFormat = "{0:C}", TextAlign = System.Windows.Forms.HorizontalAlignment.Right)]
    public double ErfolgProGast { get; set; }

    [OLVColumn("Ges.Erfolg", DisplayIndex = 24, AspectToStringFormat = "{0:C}", TextAlign = System.Windows.Forms.HorizontalAlignment.Right)]
    public double ErfolgGesamt { get; set; }

    [OLVColumn("Rating", DisplayIndex = 0, TextAlign = System.Windows.Forms.HorizontalAlignment.Left, AspectToStringFormat = "{0:C}", MinimumWidth = 100)]
    public double Bewertung { get; set; }


    


    public int Save()
    {
      DatenbankConnection.GetConnection().Open();

      NpgsqlCommand command = new NpgsqlCommand();
      command.Connection = DatenbankConnection.GetConnection();

      if (this.KundeID.HasValue & this.KundeID != 0)
      {
        command.CommandText = $"update {TABLE} set tour_id = :p1, kunde_nr = :p2, name1 = :p3, name2 = :p4, straße = :p5, plz = :p6, bezeichnung = :p7, land = :p8, aktiv = :p9, bettenanzahl = :p10, kunde_gruppe = :p11, service = :p12, region = :p13, waescheart = :p14 WHERE kunde_id = :p0";
      }
      else
      {
        command.CommandText = "select nextval('kundenverwaltung.kunde_seq')";
        this.KundeID = (int)(long)command.ExecuteScalar();
        command.CommandText = $"insert into {TABLE} ({COLUMNS}) values (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14)";
      }

      command.Parameters.AddWithValue("p0", this.KundeID);
      command.Parameters.AddWithValue("p1", this.TourID.HasValue ? this.TourID.Value : (object)DBNull.Value);
      command.Parameters.AddWithValue("p2", this.KundeNummer);
      command.Parameters.AddWithValue("p3", string.IsNullOrEmpty(this.KundeName) ? (object)DBNull.Value : this.KundeName);
      command.Parameters.AddWithValue("p4", string.IsNullOrEmpty(this.KundeName2) ? (object)DBNull.Value : this.KundeName2);
      command.Parameters.AddWithValue("p5", string.IsNullOrEmpty(this.Strasse) ? (object)DBNull.Value : this.Strasse);
      command.Parameters.AddWithValue("p6", this.PLZ.HasValue ? this.PLZ.Value : (object)DBNull.Value);
      command.Parameters.AddWithValue("p7", string.IsNullOrEmpty(this.Ort) ? (object)DBNull.Value : this.Ort);
      command.Parameters.AddWithValue("p8", string.IsNullOrEmpty(this.Land) ? (object)DBNull.Value : this.Land);
      command.Parameters.AddWithValue("p9", this.Aktiv);
      command.Parameters.AddWithValue("p10", this.Bettenanzahl.HasValue ? this.Bettenanzahl.Value : (object)DBNull.Value);
      command.Parameters.AddWithValue("p11", string.IsNullOrEmpty(this.KundeGruppe) ? (object)DBNull.Value : this.KundeGruppe);
      command.Parameters.AddWithValue("p12", string.IsNullOrEmpty(this.Service) ? (object)DBNull.Value : this.Service);
      command.Parameters.AddWithValue("p13", string.IsNullOrEmpty(this.Region) ? (object)DBNull.Value : this.Region);
      command.Parameters.AddWithValue("p14", string.IsNullOrEmpty(this.Waescheart) ? (object)DBNull.Value : this.Waescheart);

      int result = command.ExecuteNonQuery();
      DatenbankConnection.GetConnection().Close();
      return result;
    }
  }
}