using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using de.WWWings;
using System.EnterpriseServices;
using System.Runtime.CompilerServices;
using de.ITVisions.DALUtil;

namespace de.WWWings.DAL
{

 /// <summary>
 /// Datenzugriffsmanager für die Tabelle Passagier
 /// Alternative 1 von 3
 /// Verwendet Transaktionen in .NET Enterprise Services
 /// </summary>
 /// <summary>
 /// Datenzugriffsschicht für die Passagiere
 /// </summary>
 [Description("DataManager für Tabelle PS_Passagiere")]
 [Transaction(TransactionOption.Disabled)]
 [ObjectPoolingAttribute(true, 0, 20)]
 [EventTrackingEnabled(true)]
 [System.EnterpriseServices.Synchronization(SynchronizationOption.Required)]
 [System.Runtime.InteropServices.Guid("9C74BA6A-9D1C-4fe7-808E-04E6665D0250")]
 public class Passagier_DataManager : System.EnterpriseServices.ServicedComponent
 {

  /// <summary>
  /// Konstruktor: Setzen der Datenbank
  /// </summary>
  public Passagier_DataManager()
  {
   DBUtil.SetDatabase(DBUtil.Providers.MSSQL, DBConfig.CONN_MSSQL);
   //DBUtil.SetDatabase(DBUtil.Providers.ACCESS, CONN_ACCESS);
  }

  public Passagier_DataManager(string x)
  {
  }

  /// <summary>
  /// Hinzufügen eines Flugs
  /// </summary>
  /// <param name="FL_Abflugort"></param>
  /// <param name="FL_ZielOrt"></param>
  /// <param name="FL_NichtRaucherFlug"></param>
  /// <param name="FL_Plaetze"></param>
  /// <param name="FL_EingerichtetAm"></param>
  /// <param name="FL_FlugNr"></param>
  [AutoComplete]
  public static void Insert(string FL_Abflugort, string FL_ZielOrt, bool FL_NichtRaucherFlug, long FL_Plaetze, DateTime FL_EingerichtetAm, int FL_FlugNr)
  {
   DBUtil.Execute("Insert into Fl_Fluege (Fl_Flugnr, FL_Abflugort, Fl_Zielort, Fl_Plaetze, Fl_NichtraucherFlug) Values (" + FL_FlugNr + ",'" + FL_Abflugort + "', '" + FL_ZielOrt + "', " + FL_Plaetze + ", '" + FL_NichtRaucherFlug + "')");
  }


  /// <summary>
  /// Passagieranlegen
  /// </summary>
  [AutoComplete]
  public static void Anlegen(long Personalausweisnummer, string Vorname, string Nachname, string Land, byte Kundenstatus, DateTime KundenstatusSeit)
  {

   try
   {
    DBUtil.Execute("Insert into PE_Person (PE_PersonalausweisNr, PE_Vorname, PE_Name, PE_Land) Values (" + Personalausweisnummer + ",'" + Vorname + "', '" + Nachname + "', '" + Land + "')");
   }
   catch (Exception)
   {

   }

   DBUtil.Execute("Insert into PS_Passagier (PS_PE_Personalausweisnummer, PS_Kundenstatus, PS_KundenstatusSeit) Values (" + Personalausweisnummer + "," + Kundenstatus + ",'" + KundenstatusSeit.ToString(new System.Globalization.CultureInfo("en-us")) + "')");
  }

  [AutoComplete]
  public static long HolePassagierNummer(string Vorname, string Nachname)
  {
   string SQL = "Select PS_ID from AllePassagiere where PE_Vorname='" + Vorname + "' and PE_Name = '" + Nachname + "'";
   DataSet ds = DBUtil.GetDataSet(SQL);
   if (ds.Tables[0].Rows.Count == 0) return 0;
   else return Convert.ToInt64(ds.Tables[0].Rows[0][0]);
  }

  [AutoComplete]
  public static DataRow HolePassagier(long Nummer)
  {
   string SQL = "Select * from AllePassagiere where PS_ID=" + Nummer;
   DataSet ds = DBUtil.GetDataSet(SQL);
   if (ds.Tables[0].Rows.Count == 0) return null;
   else return ds.Tables[0].Rows[0];
  }

  /// <summary>
  /// Führt eine einzelne Flugbuchung aus. 
  /// </summary>
  public long Buchung(long Buchungscode, long PS_ID, long FL_ID)
  {
   long RestPlaetze = 0;
   try
   {
    RestPlaetze = Buche(Buchungscode, PS_ID, FL_ID);
    System.EnterpriseServices.ContextUtil.SetComplete();
   }
   catch (Exception ex)
   {
    System.EnterpriseServices.ContextUtil.SetAbort();
    throw ex;
   }
   return RestPlaetze;
  }


  /// <summary>
  /// Führt eine Menge von Flugbuchungen aus
  /// </summary>
  [AutoComplete]
  public void MehrfachBuchung(long BuchungsCode, long PS_ID, long[] FL_IDs)
  {
   foreach (long FL_ID in FL_IDs)
   {
    Buche(BuchungsCode, PS_ID, FL_ID);
   }
  }

  /// <summary>
  /// Führt eine einzelne Flugbuchungen  aus. Erzeugt eine Ausnahme, wenn es einen Fehler gab.
  /// </summary>
  [AutoComplete]
  private long Buche(long Buchungscode, long PS_ID, long FL_ID)
  {
   // WORKAROUND: Buchung erst löschen, wenn schon vorhanden
   string SQL = @"delete from GF_GebuchteFluege where GF_FL_FlugNr=" + FL_ID + " and GF_PS_ID= " + PS_ID;
   long e = DBUtil.Execute(SQL);

   // Prototyp: Daten tlw. statisch!
   string SQL1 = @"Select * From FL_Fluege where FL_Flugnr=" + FL_ID;
   string SQL2 = @"INSERT INTO GF_GebuchteFluege ([GF_PS_ID], [GF_FL_FlugNr], [GF_Preis], [GF_Klasse], [GF_Buchungscode]) " + "VALUES (" + PS_ID + "," + FL_ID + ", 0, 'F'," + Buchungscode.ToString() + ")";
   // --- Versuche Platzanzahl zu reduzieren
   DataSet ds = DBUtil.GetDataSet(SQL1);
   if (ds.Tables[0].Rows.Count == 0) throw new ApplicationException("Flug nicht vorhanden: " + FL_ID);
   long Plaetze = Convert.ToInt32(ds.Tables[0].Rows[0]["FL_FreiePlaetze"]);
   if (Plaetze == 0) throw new ApplicationException("Keine Plätze vorhanden für Flug " + FL_ID);
   Plaetze--;
   ds.Tables[0].Rows[0]["FL_FreiePlaetze"] = Plaetze;
   try
   {
    DBUtil.UpdateDS(ds, SQL1);
   }
   catch (Exception ex)
   {
    throw new ApplicationException("Platzreduzierung nicht erfolgreich. Flug=" + FL_ID + " " + ex.Message);
   }
   // --- Versuche, Flug zu buchen
   try
   {


    DBUtil.Execute(SQL2);
   }
   catch (Exception ex)
   {
    throw new ApplicationException("Fehler beim Buchen von Flug " + FL_ID + " für Passagier " + PS_ID + ": " + ex.Message, ex);
   }


   // Alle Buchungen erfolgreich!
   return Buchungscode;
  }


  /// <summary>
  /// Löschen aller Buchungen
  /// </summary>
  [AutoComplete]
  public long AlleBuchungenLoeschen(long PS_ID)
  {
   string SQL = "Delete from GF_GebuchteFluege where GF_PS_ID = " + PS_ID;
   return DBUtil.Execute(SQL);
  }

  /// <summary>
  /// Löschen aller Buchungen
  /// </summary>
  [AutoComplete]
  public long AlleBuchungenLoeschen()
  {
   string SQL = "Delete from GF_GebuchteFluege";
   return DBUtil.Execute(SQL);
  }

  /// <summary>
  /// Löschen aller Buchungen
  /// </summary>
  [AutoComplete]
  public long BuchungenLoeschen(long Buchungscode)
  {
   string SQL = "Delete from GF_GebuchteFluege where GF_Buchungscode = " + Buchungscode;
   return DBUtil.Execute(SQL);
  }

  [AutoComplete]
  public DbDataReader AlleBuchungen_Reader(long PS_ID)
  {
   string SQL = "Select * from GF_GebuchteFluege where GF_PS_ID = " + PS_ID;
   return DBUtil.GetReader(SQL);
  }

  [AutoComplete]
  public DataTable AlleBuchungen_DataTable(long PS_ID)
  {
   string SQL = "Select * from GF_GebuchteFluege where GF_PS_ID = " + PS_ID;
   return DBUtil.GetDataTable(SQL);
  }



 }
}
