public static int izbrisiAerdrom(Aerodrom aerodrom)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("UPDATE Aerodrom SET ");
sb.AppendLine("Deleted = 1 WHERE AerodromId = @Id");
SqlCommand komanda = new SqlCommand(sb.ToString(), cnn);
komanda.Parameters.AddWithValue("@Id", aerodrom.Id);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
return(0);
}
catch (Exception xcp)
{
MessageBox.Show(xcp.Message);
return(-1);
}
finally
{
cnn.Close();
}
}
public static int napraviAerodrom(Aerodrom aerodrom)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("INSERT INTO Aerodrom VALUES ");
sb.AppendLine("(@Sifra,@Naziv, @Grad , 0)");
SqlCommand komanda = new SqlCommand(sb.ToString(), cnn);
komanda.Parameters.AddWithValue("@Sifra", aerodrom.Sifra);
komanda.Parameters.AddWithValue("@Naziv", aerodrom.Naziv);
komanda.Parameters.AddWithValue("@Grad", aerodrom.Grad);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
return(0);
}
catch (Exception xcp)
{
MessageBox.Show(xcp.Message);
return(-1);
}
finally
{
cnn.Close();
}
}
public static int IzbrisiLet(Let let)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("UPDATE Let SET ");
sb.AppendLine("Deleted= 1 ");
sb.AppendLine("WHERE LetId = @LetId ");
SqlCommand komanda = new SqlCommand(sb.ToString(), cnn);
komanda.Parameters.AddWithValue("@LetId", let.Id);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
cnn.Close();
return(0);
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(-1);
}
}
public static int IzmeniAvion(Avion avion)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("UPDATE Avion SET ");
sb.AppendLine("Naziv = @Naziv , AvioKompanijaId = @AvioKompanijaId ");
sb.AppendLine("WHERE AvionId = @AvionId");
SqlCommand komanda = new SqlCommand(sb.ToString(), cnn);
komanda.Parameters.AddWithValue("@Naziv", avion.Naziv);
komanda.Parameters.AddWithValue("@AvioKompanijaId", avion.AvioKompanija.Id);
komanda.Parameters.AddWithValue("@AvionId", avion.Id);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
return(0);
}
catch (Exception xcp)
{
MessageBox.Show(xcp.Message);
return(-1);
}
finally
{
cnn.Close();
}
}
public static ObservableCollection <AvioKompanija> vratiAvioKompanije()
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM AvioKompanija WHERE Deleted = 0", cnn);
ObservableCollection <AvioKompanija> lista = new ObservableCollection <AvioKompanija>();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
AvioKompanija av = new AvioKompanija();
av.Id = (int)dr["AvioKompanijaId"];
av.Naziv = dr["Naziv"].ToString();
av.Deleted = Convert.ToBoolean(dr["Deleted"]);
lista.Add(av);
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
return(lista);
}
public static AvioKompanija vratiAvioKompanijuPrekoImena(string naziv)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM AvioKompanija WHERE Naziv = @Naziv AND Deleted = 0", cnn);
komanda.Parameters.AddWithValue("Naziv", naziv);
AvioKompanija av = new AvioKompanija();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
av.Id = (int)dr["AvioKompanijaId"];
av.Naziv = dr["Naziv"].ToString();
av.Deleted = Convert.ToBoolean(dr["Deleted"]);
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
return(av);
}
public static ObservableCollection <Let> VratiLetove()
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM Let WHERE Deleted = 0", cnn);
ObservableCollection <Let> letovi = new ObservableCollection <Let>();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Let l = new Let();
l.Id = (int)dr["LetId"];
l.Sifra = dr["Sifra"].ToString();
l.Pilot = dr["Pilot"].ToString();
l.vremePolaska = DateTime.Parse(dr["VremePolaska"].ToString());
l.vremeDolaska = DateTime.Parse(dr["VremeDolaska"].ToString());
l.PolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["PolazniAerodrom"]);
l.DolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["DolazniAerodrom"]);
l.Avion = AvionDAO.vratiAvion((int)dr["AvionId"]);
l.Cena = (decimal)dr["Cena"];
l.Deleted = Convert.ToBoolean(dr["Deleted"]);
if (l.DolazniAerodrom.Deleted || l.DolazniAerodrom.Naziv == null)
{
IzbrisiLet(l);
}
else if (l.PolazniAerodrom.Deleted || l.PolazniAerodrom.Naziv == null)
{
IzbrisiLet(l);
}
else if (l.Avion.Deleted || l.Avion == null)
{
IzbrisiLet(l);
}
else
{
letovi.Add(l);
}
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
return(letovi);
}
public static ObservableCollection<Karta> vratiKarte()
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM Karta WHERE Deleted = 0", cnn);
ObservableCollection<Karta> karte = new ObservableCollection<Karta>();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Karta k = new Karta();
k.KartaId = (int)dr["KartaId"];
k.Let = LetDAO.vratiLet((int)dr["LetId"]);
Sediste s = new Sediste();
s.Id = 1;
s.Red = (int)dr["BrojReda"];
s.SedisteURedu = (int)dr["BrojSedista"];
EtipSedista tip;
Enum.TryParse(dr["Klasa"].ToString(), out tip);
s.tipSedista = tip;
s.Deleted = false;
k.Sediste = s;
k.Korisnik = KorisnikDAO.vratiKorisnika((int)dr["KorisnikId"]);
k.Kapija = dr["Kapija"].ToString();
k.Cena = (decimal)dr["Cena"];
k.Deleted = Convert.ToBoolean(dr["Deleted"]);
if (k.Let.Deleted || k.Let.Sifra == null)
{
IzbrisiKartu(k);
}
else
{
karte.Add(k);
}
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return null;
}
return karte;
}
public static Korisnik vratiKorisnikaPrekoUserName(string userName)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM Korisnik WHERE KorisnickoIme = @KorisnickoIme AND Deleted = 0", cnn);
komanda.Parameters.AddWithValue("KorisnickoIme", userName);
Korisnik k = new Korisnik();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
k.Id = (int)dr["KorisnikId"];
k.Ime = dr["Ime"].ToString();
k.Prezime = dr["Prezime"].ToString();
k.Email = dr["Email"].ToString();
k.Adresa = dr["Adresa"].ToString();
Epol pol;
Enum.TryParse(dr["Pol"].ToString(), out pol);
k.Pol = pol;
k.UserName = dr["KorisnickoIme"].ToString();
k.Password = dr["Lozinka"].ToString();
Etip tip;
Enum.TryParse(dr["TipKorisnika"].ToString(), out tip);
k.Tip = tip;
k.Deleted = Convert.ToBoolean(dr["Deleted"]);
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
if (k.Id != 0)
{
return(k);
}
return(null);
}
public static List<Karta> VratiKarteULetu(Let let)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM Karta WHERE LetId = @LetID AND Deleted = 0", cnn);
komanda.Parameters.AddWithValue("@LetID", let.Id);
List<Karta> karte = new List<Karta>();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Karta k = new Karta();
k.KartaId = (int)dr["KartaId"];
k.Let = LetDAO.vratiLet((int)dr["LetId"]);
Sediste s = new Sediste();
s.Id = 1;
s.Red = (int)dr["BrojReda"];
s.SedisteURedu = (int)dr["BrojSedista"];
EtipSedista tip;
Enum.TryParse(dr["Klasa"].ToString(), out tip);
s.tipSedista = tip;
s.Deleted = false;
k.Sediste = s;
k.Korisnik = KorisnikDAO.vratiKorisnika((int)dr["KorisnikId"]);
k.Kapija = dr["Kapija"].ToString();
k.Cena = (decimal)dr["Cena"];
k.Deleted = Convert.ToBoolean(dr["Deleted"]);
karte.Add(k);
}
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return null;
}
return karte;
}
public static ObservableCollection <Avion> vratiAvione()
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM Avion WHERE Deleted = 0", cnn);
ObservableCollection <Avion> avioni = new ObservableCollection <Avion>();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Avion a = new Avion();
a.Id = (int)dr["AvionId"];
int brRedovaEklase = (int)dr["BrojRedovaEkonomskeKlase"];
int brSedistaEklase = (int)dr["BrojSedistaUReduEkonomskeKlase"];
a.puniListuEkon(brRedovaEklase, brSedistaEklase);
int brRedovaBiznis = (int)dr["BrojRedovaBiznisKlase"];
int brSedistaBiznis = (int)dr["BrojSedistaUReduBiznisKlase"];
a.puniListuBiz(brRedovaBiznis, brSedistaBiznis);
a.AvioKompanija = AvioKompanijaDAO.vratiAvioKompaniju((int)dr["AvioKompanijaId"]);
a.Naziv = dr["Naziv"].ToString();
a.Deleted = Convert.ToBoolean(dr["Deleted"]);
if (a.AvioKompanija.Deleted || a.AvioKompanija.Naziv == null)
{
IzbirisAvio(a);
}
else
{
avioni.Add(a);
}
}
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
return(avioni);
}
public static ObservableCollection <Korisnik> VratiKorisnike()
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("Select * FROM Korisnik WHERE Deleted = 0", cnn);
ObservableCollection <Korisnik> korisnici = new ObservableCollection <Korisnik>();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Korisnik k = new Korisnik();
k.Id = (int)dr["KorisnikId"];
k.Ime = dr["Ime"].ToString();
k.Prezime = dr["Prezime"].ToString();
k.Email = dr["Email"].ToString();
k.Adresa = dr["Adresa"].ToString();
Epol pol;
Enum.TryParse(dr["Pol"].ToString(), out pol);
k.Pol = pol;
k.UserName = dr["KorisnickoIme"].ToString();
k.Password = dr["Lozinka"].ToString();
Etip tip;
Enum.TryParse(dr["TipKorisnika"].ToString(), out tip);
k.Tip = tip;
k.Deleted = Convert.ToBoolean(dr["Deleted"]);
korisnici.Add(k);
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
return(korisnici);
}
public static Let vratiLetPrekoSifre(string sifra)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM Let WHERE Sifra = @Sifra AND Deleted = 0", cnn);
komanda.Parameters.AddWithValue("@Sifra", sifra.ToString());
Let l = new Let();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
l.Id = (int)dr["LetId"];
l.Sifra = dr["Sifra"].ToString();
l.Pilot = dr["Pilot"].ToString();
l.vremePolaska = DateTime.Parse(dr["VremePolaska"].ToString());
l.vremeDolaska = DateTime.Parse(dr["VremeDolaska"].ToString());
l.PolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["PolazniAerodrom"]);
l.DolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["DolazniAerodrom"]);
l.Avion = AvionDAO.vratiAvion((int)dr["AvionId"]);
l.Cena = (decimal)dr["Cena"];
l.Deleted = Convert.ToBoolean(dr["Deleted"]);
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
if (l.Id != 0)
{
return(l);
}
return(null);
}
public static int IzmeniKorisnika(Korisnik k)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("UPDATE Korisnik SET ");
sb.AppendLine("Ime = @Ime, Prezime = @Prezime, Email = @Email, Adresa = @Adresa, Pol = @Pol, ");
sb.AppendLine("KorisnickoIme = @KorisnickoIme, Lozinka = @Lozinka, TipKorisnika = @Tip");
sb.AppendLine("WHERE KorisnikId = @KorisnikId ");
SqlCommand komanda = new SqlCommand(sb.ToString(), cnn);
komanda.Parameters.AddWithValue("@Ime", k.Ime);
komanda.Parameters.AddWithValue("@Prezime", k.Prezime);
komanda.Parameters.AddWithValue("@Email", k.Email);
komanda.Parameters.AddWithValue("@Adresa", k.Adresa);
komanda.Parameters.AddWithValue("@Pol", k.Pol.ToString());
komanda.Parameters.AddWithValue("@KorisnickoIme", k.UserName);
komanda.Parameters.AddWithValue("@Lozinka", k.Password);
komanda.Parameters.AddWithValue("@Tip", k.Tip.ToString());
komanda.Parameters.AddWithValue("@KorisnikId", k.Id);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
return(0);
}
catch (Exception xcp)
{
MessageBox.Show(xcp.Message);
return(-1);
}
finally
{
cnn.Close();
}
}
public static int IzmeniLet(Let let)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("UPDATE Let SET ");
sb.AppendLine("Sifra=@Sifra,Pilot = @Pilot,VremePolaska = @VremePolaska,VremeDolaska = @VremeDolaska, ");
sb.AppendLine("PolazniAerodrom = @PolazniAerodrom,DolazniAerodrom = @DolazniAerodrom, ");
sb.AppendLine("AvionId = @AvionId,Cena = @Cena, Deleted = @Deleted ");
sb.AppendLine("WHERE LetId = @LetId ");
SqlCommand komanda = new SqlCommand(sb.ToString(), cnn);
komanda.Parameters.AddWithValue("@Sifra", let.Sifra);
komanda.Parameters.AddWithValue("@Pilot", let.Pilot);
komanda.Parameters.AddWithValue("@VremePolaska", let.vremePolaska);
komanda.Parameters.AddWithValue("@VremeDolaska", let.vremeDolaska);
komanda.Parameters.AddWithValue("@PolazniAerodrom", let.PolazniAerodrom.Id);
komanda.Parameters.AddWithValue("@DolazniAerodrom", let.DolazniAerodrom.Id);
komanda.Parameters.AddWithValue("@AvionId", let.Avion.Id);
komanda.Parameters.AddWithValue("@Cena", let.Cena);
komanda.Parameters.AddWithValue("@Deleted", let.Deleted);
komanda.Parameters.AddWithValue("@LetId", let.Id);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
cnn.Close();
return(0);
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(-1);
}
}
public static Avion vratiAvion(int id)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM Avion WHERE AvionId = @AvionId AND Deleted = 0", cnn);
komanda.Parameters.AddWithValue("AvionId", id);
Avion a = new Avion();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
a.Id = (int)dr["AvionId"];
int brRedovaEklase = (int)dr["BrojRedovaEkonomskeKlase"];
int brSedistaEklase = (int)dr["BrojSedistaUReduEkonomskeKlase"];
a.puniListuEkon(brRedovaEklase, brSedistaEklase);
int brRedovaBiznis = (int)dr["BrojRedovaBiznisKlase"];
int brSedistaBiznis = (int)dr["BrojSedistaUReduBiznisKlase"];
a.puniListuBiz(brRedovaBiznis, brSedistaBiznis);
a.AvioKompanija = AvioKompanijaDAO.vratiAvioKompaniju((int)dr["AvioKompanijaId"]);
a.Naziv = dr["Naziv"].ToString();
a.Deleted = Convert.ToBoolean(dr["Deleted"]);
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
return(a);
}
public static List <int> vratiBrSedita(Avion a)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM Avion WHERE AvionId = @AvionId", cnn);
komanda.Parameters.AddWithValue("AvionId", a.Id);
List <int> lista = new List <int>();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
int brRedovaEklase = (int)dr["BrojRedovaEkonomskeKlase"];
int brSedistaEklase = (int)dr["BrojSedistaUReduEkonomskeKlase"];
int brRedovaBiznis = (int)dr["BrojRedovaBiznisKlase"];
int brSedistaBiznis = (int)dr["BrojSedistaUReduBiznisKlase"];
lista.Add(brRedovaEklase);
lista.Add(brSedistaEklase);
lista.Add(brRedovaBiznis);
lista.Add(brSedistaBiznis);
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
return(lista);
}
public static int NaparviKartu(Karta k)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("INSERT INTO Karta VALUES ");
sb.AppendLine("(@LetId, @BrojReda, @BrojSedista, @KorisnikId, @Klasa,@Kapija,@Cena, @Deleted)");
SqlCommand komanda = new SqlCommand(sb.ToString(),cnn);
komanda.Parameters.AddWithValue("@LetId", k.Let.Id);
komanda.Parameters.AddWithValue("@BrojReda", k.Sediste.Red);
komanda.Parameters.AddWithValue("@BrojSedista", k.Sediste.SedisteURedu);
komanda.Parameters.AddWithValue("@KorisnikId", k.Korisnik.Id);
komanda.Parameters.AddWithValue("@Klasa", k.Sediste.tipSedista.ToString());
komanda.Parameters.AddWithValue("@Kapija", k.Kapija);
komanda.Parameters.AddWithValue("@Cena", k.Cena);
komanda.Parameters.AddWithValue("@Deleted", 0);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
cnn.Close();
return 0;
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return -1;
}
}
public static int NapraviLet(Let let)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("INSERT INTO Let VALUES ");
sb.AppendLine("(@Sifra, @Pilot, @VremePolaska, @VremeDolaska,@PolazniAerodrom, ");
sb.AppendLine("@DolazniAerodrom, @AvionId, @Cena, 0)");
SqlCommand komanda = new SqlCommand(sb.ToString(), cnn);
komanda.Parameters.AddWithValue("@Sifra", let.Sifra);
komanda.Parameters.AddWithValue("@Pilot", let.Pilot);
komanda.Parameters.AddWithValue("@VremePolaska", let.vremePolaska);
komanda.Parameters.AddWithValue("@VremeDolaska", let.vremeDolaska);
komanda.Parameters.AddWithValue("@PolazniAerodrom", let.PolazniAerodrom.Id);
komanda.Parameters.AddWithValue("@DolazniAerodrom", let.DolazniAerodrom.Id);
komanda.Parameters.AddWithValue("@AvionId", let.Avion.Id);
komanda.Parameters.AddWithValue("@Cena", let.Cena);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
cnn.Close();
return(0);
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(-1);
}
}
public static ObservableCollection <Aerodrom> vratiAerodrome()
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM Aerodrom WHERE Deleted = 0", cnn);
ObservableCollection <Aerodrom> lista = new ObservableCollection <Aerodrom>();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Aerodrom ar = new Aerodrom();
ar.Id = (int)dr["AerodromId"];
ar.Sifra = dr["Sifra"].ToString();
ar.Naziv = dr["Naziv"].ToString();
ar.Grad = dr["Grad"].ToString();
ar.Deleted = Convert.ToBoolean(dr["Deleted"]);
lista.Add(ar);
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
return(lista);
}
public static int UbaciKorisnika(Korisnik k)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("INSERT INTO Korisnik Values(@Ime, @Prezime, @Email, @Adresa, @Pol, @UserName, @Passoword, @Tip, @Deleted) ; SELECT SCOPE_IDENTITY();", cnn);
komanda.Parameters.AddWithValue("@Ime", k.Ime);
komanda.Parameters.AddWithValue("@Prezime", k.Prezime);
komanda.Parameters.AddWithValue("@Email", k.Email);
komanda.Parameters.AddWithValue("@Adresa", k.Adresa);
if (k.Pol == null)
{
komanda.Parameters.AddWithValue("@Pol", string.Empty);
}
else
{
komanda.Parameters.AddWithValue("@Pol", k.Pol.ToString());
}
komanda.Parameters.AddWithValue("@UserName", k.UserName);
komanda.Parameters.AddWithValue("@Passoword", k.Password);
komanda.Parameters.AddWithValue("@Tip", k.Tip.ToString());
komanda.Parameters.AddWithValue("@Deleted", 0);
try
{
cnn.Open();
int id = Convert.ToInt32(komanda.ExecuteScalar());
return(id);
}
catch (Exception xcp)
{
MessageBox.Show(xcp.Message);
return(-1);
}
finally
{
cnn.Close();
}
}
public static int NapraviAvion(Avion avion, List <int> lista)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("INSERT INTO Avion VALUES ");
sb.AppendLine("(@BrojRedovaEkonKlase, @BrojSEdistaUReduEkonKlase,@BrojRedovaBizKlase, ");
sb.AppendLine(" @BrojSEdistaUReduBizKlase, @AvioKompanijaId, @Naziv, @Deleted);");
SqlCommand komanda = new SqlCommand("INSERT INTO Avion VALUES (@BrojRedovaEkonKlase, @BrojSEdistaUReduEkonKlase, @BrojRedovaBizKlase, @BrojSEdistaUReduBizKlase,@AvioKompanijaId , @Naziv, 0)", cnn);
komanda.Parameters.AddWithValue("@BrojRedovaEkonKlase", lista[0]);
komanda.Parameters.AddWithValue("@BrojSEdistaUReduEkonKlase", lista[1]);
komanda.Parameters.AddWithValue("@BrojRedovaBizKlase", lista[2]);
komanda.Parameters.AddWithValue("@BrojSEdistaUReduBizKlase", lista[3]);
komanda.Parameters.AddWithValue("@AvioKompanijaId", avion.AvioKompanija.Id);
komanda.Parameters.AddWithValue("@Naziv", avion.Naziv);
//komanda.Parameters.AddWithValue("@Deleted", 0);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
return(0);
}
catch (Exception xcp)
{
MessageBox.Show(xcp.Message);
return(-1);
}
finally
{
cnn.Close();
}
}
public static int DodajAvioKompaniju(AvioKompanija av)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("INSERT INTO AvioKompanija VALUES (@Naziv,@Deleted)", cnn);
komanda.Parameters.AddWithValue("@Naziv", av.Naziv);
komanda.Parameters.AddWithValue("@Deleted", 0);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
return(0);
}
catch (Exception xcp)
{
MessageBox.Show(xcp.Message);
return(-1);
}
finally
{
cnn.Close();
}
}
public static Aerodrom vratiAerodrom(int id)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
SqlCommand komanda = new SqlCommand("SELECT * FROM Aerodrom WHERE AerodromId = @AerodromId AND Deleted = 0", cnn);
komanda.Parameters.AddWithValue("AerodromId", id);
Aerodrom ar = new Aerodrom();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
ar.Id = (int)dr["AerodromId"];
ar.Sifra = dr["Sifra"].ToString();
ar.Naziv = dr["Naziv"].ToString();
ar.Grad = dr["Grad"].ToString();
ar.Deleted = Convert.ToBoolean(dr["Deleted"]);
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
return(ar);
}
public static int IzbrisiKorisnika(Korisnik k)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("UPDATE Korisnik SET ");
sb.AppendLine("Deleted = 1");
sb.AppendLine("WHERE KorisnikId = @KorisnikId;");
sb.AppendLine("UPDATE Karta SET ");
sb.AppendLine("Deleted = 1");
sb.AppendLine("WHERE KorisnikId = @KorisnikId;");
SqlCommand komanda = new SqlCommand(sb.ToString(), cnn);
komanda.Parameters.AddWithValue("@KorisnikId", k.Id);
try
{
cnn.Open();
komanda.ExecuteNonQuery();
return(0);
}
catch (Exception xcp)
{
MessageBox.Show(xcp.Message);
return(-1);
}
finally
{
cnn.Close();
}
}
public static ObservableCollection <Let> VratiPovratneLetove(Let let)
{
SqlConnection cnn = Konekcija.KreirajKoekciju();
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT Let.LetId, Let.Sifra, Let.Pilot,Let.VremePolaska,Let.VremeDolaska, ");
sb.AppendLine("Let.PolazniAerodrom,Let.DolazniAerodrom,Let.AvionId,Let.Cena,Let.Deleted FROM Let ");
sb.AppendLine("INNER JOIN Avion ON let.AvionId = Avion.AvionId WHERE ");
sb.AppendLine("Avion.AvioKompanijaId = @AvioKompanijaId AND Let.PolazniAerodrom = @PolazniAerodrom ");
sb.AppendLine("AND Let.DolazniAerodrom = @DolazniAerodrom AND Let.Deleted = 0;");
SqlCommand komanda = new SqlCommand(sb.ToString(), cnn);
komanda.Parameters.AddWithValue("@AvioKompanijaId", let.Avion.AvioKompanija.Id);
komanda.Parameters.AddWithValue("@PolazniAerodrom", let.DolazniAerodrom.Id);
komanda.Parameters.AddWithValue("@DolazniAerodrom", let.PolazniAerodrom.Id);
ObservableCollection <Let> letovi = new ObservableCollection <Let>();
try
{
cnn.Open();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Let l = new Let();
l.Id = (int)dr["LetId"];
l.Sifra = dr["Sifra"].ToString();
l.Pilot = dr["Pilot"].ToString();
l.vremePolaska = DateTime.Parse(dr["VremePolaska"].ToString());
l.vremeDolaska = DateTime.Parse(dr["VremeDolaska"].ToString());
l.PolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["PolazniAerodrom"]);
l.DolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["DolazniAerodrom"]);
l.Avion = AvionDAO.vratiAvion((int)dr["AvionId"]);
l.Cena = (decimal)dr["Cena"];
l.Deleted = Convert.ToBoolean(dr["Deleted"]);
if (l.DolazniAerodrom.Deleted || l.DolazniAerodrom.Naziv == null)
{
IzbrisiLet(l);
}
else if (l.PolazniAerodrom.Deleted || l.PolazniAerodrom.Naziv == null)
{
IzbrisiLet(l);
}
else if (l.Avion.Deleted || l.Avion == null)
{
IzbrisiLet(l);
}
else
{
letovi.Add(l);
}
}
cnn.Close();
}
catch (Exception xcp)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
MessageBox.Show(xcp.Message);
return(null);
}
return(letovi);
}
