/// <summary>
/// Select the record.
/// </summary>
/// <param name="id">rezervace id</param>
public Rezervace selectCollection(int cislo_rezervace, Database pDb = null)
{
Database db;
if (pDb == null)
{
db = new Database();
db.Connect();
}
else
{
db = (Database)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT_ID);
command.Parameters.AddWithValue("@cislo_rezervace", cislo_rezervace);
SqlDataReader reader = db.Select(command);
Collection <Rezervace> rezervaces = Read2(reader);
Rezervace rezervace = null;
if (rezervaces.Count == 1)
{
rezervace = rezervaces[0];
}
reader.Close();
if (pDb == null)
{
db.Close();
}
return(rezervace);
}
public int SaveRez(Rezervace rezervace)
{
SqlConnectionStringBuilder builder = DBConnector.GetBuilder();
int status = -1;
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
StringBuilder sb = new StringBuilder();
sb.Clear();
sb.Append("INSERT INTO vis.rezervace (cislo_rezervace,id_zakaznika,vyzvednuti,vraceni)");
sb.Append("VALUES (@id_rezervace, @idZ, @vyzvednuti, @vraceni);");
//sb.Append("SELECT CAST(scope_identity() AS int)");
string sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@id_rezervace", rezervace.cisloRezervace);
command.Parameters.AddWithValue("@idZ", rezervace.idZakaznika);
command.Parameters.AddWithValue("@vyzvednuti", rezervace.zacatekRezervace);
command.Parameters.AddWithValue("@vraceni", rezervace.konecRezervace);
status = command.ExecuteNonQuery();
}
}
return(status);
}
// funkce 2. c)
// Vytvořit rezervaci ( transakce )
public static bool CreateReservation(Rezervace rezervace)
{
bool povedloSe = false;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringMsSql"].ConnectionString))
{
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("vytvoreniRezervace", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@p_jmeno", rezervace.Jmeno));
cmd.Parameters.Add(new SqlParameter("@p_prijmeni", rezervace.Prijmeni));
cmd.Parameters.Add(new SqlParameter("@p_pocet_osob", rezervace.Pocet_osob));
cmd.Parameters.Add(new SqlParameter("@p_rezervovane_datum", rezervace.Zarezervovane_datum));
cmd.Parameters.Add(new SqlParameter("@p_pruvodce", rezervace.Pruvodce_pID));
// execute the command
using (SqlDataReader rdr = cmd.ExecuteReader())
{
// iterate through results, printing each to console
while (rdr.Read())
{
//Console.WriteLine("Product: {0,-35} Total: {1,2}", rdr["ProductName"], rdr["Total"]);
}
povedloSe = true;
}
}
return(povedloSe);
}
/// <summary>
/// Update the record.
/// </summary>
public static int update(Rezervace rezervace, Database pDb = null)
{
Database db;
if (pDb == null)
{
db = new Database();
db.Connect();
}
else
{
db = (Database)pDb;
}
SqlCommand command = db.CreateCommand(SQL_UPDATE);
PrepareCommand(command, rezervace);
int ret = db.ExecuteNonQuery(command);
if (pDb == null)
{
db.Close();
}
return(ret);
}
private void FillData(Rezervace reservation)
{
textBlock_puvodni_datum_rezervace.Text = reservation.Zarezervovane_datum.ToString();
for (int i = 2017; i < 2022; i++)
{
comboBox_year.Items.Add(i.ToString());
}
comboBox_year.SelectedIndex = 3;
for (int i = 1; i < 13; i++)
{
comboBox_month.Items.Add(i.ToString());
}
int reservation_month = reservation.Zarezervovane_datum.Month;
comboBox_month.SelectedIndex = reservation_month - 1;
for (int i = 1; i < 32; i++)
{
comboBox_day.Items.Add(i.ToString());
}
int reservation_day = reservation.Zarezervovane_datum.Day;
comboBox_day.SelectedIndex = reservation_day - 1;
slider_hour.Value = reservation.Zarezervovane_datum.Hour;
textBlock_set_hour.Text = slider_hour.Value.ToString();
slider_minute.Value = reservation.Zarezervovane_datum.Minute;
textBlock_set_minute.Text = slider_minute.Value.ToString();
}
private void detailR(int cisloRezervace)
{
rezervace = new RezervaceTable().selectCollection(cisloRezervace);
if (rezervace != null)
{
faktura = new FakturaTable().selectCRez(rezervace.cislo_rezervace);
detailRez.Rows.Clear();
detailFak.Rows.Clear();
autaNaRez.Rows.Clear();
object nZap = "";
if (faktura.zaplaceno == null)
{
nZap = (string)nZap;
nZap = "NEZAPLACENO";
}
else
{
nZap = faktura.zaplaceno;
}
detailRez.Rows.Add(rezervace.cislo_rezervace, rezervace.zakaznik.cislo_RP, rezervace.id_zam, rezervace.vyzvednuti, rezervace.vraceni);
detailFak.Rows.Add(faktura.cislo_faktury, faktura.vytvoreno, faktura.potvrzeno, nZap);
foreach (Auto a in rezervace.autaNaRez)
{
autaNaRez.Rows.Add(a.spz, a.model, a.znacka, a.stk);
}
}
}
private bool DeleteSelectedReservation()
{
Rezervace reservation = (Rezervace)dataGrid_vypis_rezervaci2.SelectedItem;
int id_reservation;
bool success = false;
if (reservation != null)
{
id_reservation = reservation.rID;
try
{
success = RezervaceTable.DeleteRezervaceById(id_reservation);
if (success)
{
textBlock_info_o_zruseni_rezervace.Text = "Rezervace zrušena";
GetReservations();
return(true);
}
}
catch (Exception)
{
// exception - nelze zrusit rezervaci, protoze rezervace uz byla vyuzita tzn. ze navstevnik uz navstivil muzeum
textBlock_info_o_zruseni_rezervace.Text = "Rezervace nelze zrušit";
return(false);
//throw;
}
}
else
{
textBlock_info_o_zruseni_rezervace.Text = "Nebyla vybrána rezervace";
return(false);
}
return(false);
}
private static void PrepareCommand(SqlCommand command, Rezervace rezervace)
{
command.Parameters.AddWithValue("@cislo_rezervace", rezervace.cislo_rezervace);
command.Parameters.AddWithValue("@cislo_RP", rezervace.cislo_rp);
command.Parameters.AddWithValue("@idZamestnance", rezervace.id_zam);
command.Parameters.AddWithValue("@vyzvednuti", rezervace.vyzvednuti);
command.Parameters.AddWithValue("@vraceni", rezervace.vraceni);
}
public void RegisterVisitors()
{
int id_reception = id_logged_receptione ?? 1;
Rezervace reservation = (Rezervace)dataGrid_rezervace.SelectedItem;
int? id_reservation = null;
if (reservation != null)
{
id_reservation = reservation.rID;
}
int num_of_visitors;
bool res = int.TryParse(textBox_pocet.Text, out num_of_visitors);
if (res && num_of_visitors > 0)
{
// without reservation
if (!(bool)checkBox_ma_rezervaci.IsChecked)
{
int count = NavstevaTable.VlozitNavstevu(num_of_visitors, null, id_reception);
if (count > 0)
{
textBlock_info_o_zaevidovani.Text = "Navsteva zaevidovana";
}
else
{
textBlock_info_o_zaevidovani.Text = "Navsteva nebyla zaevidovana";
}
}
// with reservation
else if ((bool)checkBox_ma_rezervaci.IsChecked && id_reservation != null)
{
int count = NavstevaTable.VlozitNavstevu(num_of_visitors, id_reservation, id_reception);
if (count > 0)
{
textBlock_info_o_zaevidovani.Text = "Navsteva zaevidovana";
}
else
{
textBlock_info_o_zaevidovani.Text = "Navsteva nebyla zaevidovana";
}
}
else
{
textBlock_info_o_zaevidovani.Text = "Navsteva nebyla zaevidovana";
}
}
else
{
textBlock_info_o_zaevidovani.Text = "Navsteva nebyla zaevidovana";
}
textBox_pocet.Text = "";
checkBox_ma_rezervaci.IsChecked = false;
vypis_rezervaci_grid.Visibility = Visibility.Hidden;
}
private static Rezervace MapRezToObj(SqlDataReader reader)
{
Rezervace rezervace = new Rezervace();
rezervace.cisloRezervace = reader.GetInt32(0);
rezervace.idZakaznika = reader.GetInt32(1);
rezervace.zacatekRezervace = reader.GetDateTime(2);
rezervace.konecRezervace = reader.GetDateTime(3);
return(rezervace);
}
public ActionResult Rezervace(int?z)
{
if (!z.HasValue)
{
return(RedirectToAction("Index"));
}
ZajezdDao zd = new ZajezdDao();
Zajezd zajzed = zd.GetById(z.Value);
Rezervace r = new Rezervace();
r.zajezd = zajzed;
return(View(r));
}
private Rezervace GetSelectedRez()
{
// The "SelectionMode" property of the data grid view must be set to "FullRowSelect".
if (RezGrid.SelectedRows.Count == 1)
{
Rezervace rezervace = RezGrid.SelectedRows[0].DataBoundItem as Rezervace;
return(rezervace);
}
else
{
return(null);
}
}
private void vlozitD_Click(object sender, EventArgs e)
{
if (datDo.Value.Date >= DatOd.Value.Date)
{
Rezervace rezervace = MenuF.rezervace;
rezervace.vyzvednuti = DatOd.Value.Date;
rezervace.vraceni = datDo.Value.Date;
RezervaceTable.update(rezervace);
}
else
{
MessageBox.Show("Nelze zadat datum vyzvednutí větší než datum vrácení");
}
}
protected void EditRecord()
{
Rezervace selectedRez = GetSelectedRez();
if (selectedRez != null)
{
DetailRezervace form = new DetailRezervace();
if (form.OpenRecord(selectedRez.idRez))
{
form.ShowDialog();
GetData();
}
}
}
public bool OpenRecord(object primaryKey)
{
if (primaryKey != null)
{
int idRez = (int)primaryKey;
rezervace = RezervaceTable.select(idRez);
newRecord = false;
}
else
{
rezervace = new Rezervace();
rezervace.datumStrelby = new DateTime(2000, 01, 01);
newRecord = true;
}
BindData();
return(true);
}
private void EditReservation()
{
Rezervace reservation = (Rezervace)dataGrid_vypis_rezervaci2.SelectedItem;
int id_reservation;
if (reservation == null)
{
upravit_rezervaci_grid.Visibility = Visibility.Hidden;
btn_vypis_vsech_rezervaci.Visibility = Visibility.Visible;
textBlock_info_o_editaci_rezervace.Text = "Rezervace nebyla vybrána";
//Console.WriteLine("reservation is NULL!");
return;
}
id_reservation = reservation.rID;
textBlock_id_rezervace_cislo.Text = id_reservation.ToString();
FillData(reservation);
}
public int DeleteRez(Rezervace rezervace)
{
SqlConnectionStringBuilder builder = DBConnector.GetBuilder();
int status = -1;
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
StringBuilder sb = new StringBuilder();
sb.Clear();
sb.Append("DELETE FROM vis.rezervace where cislo_rezervace = @id");
string sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@id", rezervace.cisloRezervace);
status = command.ExecuteNonQuery();
}
}
return(status);
}
public ActionResult Smaz(int?id)
{
if (!id.HasValue)
{
return(RedirectToAction("Index"));
}
RezervaceDao rd = new RezervaceDao();
Rezervace r = rd.GetById(id.Value);
Zajezd z = r.zajezd;
MailClient.sendMail(r.uzivatel.login, "Zrušení rezervace zájezdu", "Vážený zákazníku, vaše rezervace byla zrušena.");
int pocet = r.pocetDeti + r.pocetDospelych;
z.kapacita = z.kapacita + pocet;
rd.Update(r);
rd.Delete(r);
return(RedirectToAction("Detail", "Uzivatele", new { id = r.uzivatel.Id }));
}
public NovaRezervace()
{
InitializeComponent();
mapper = new DataMapper();
rezervace = new Rezervace();
rezervace.idZakaznika = 0;
List <string> typy = mapper.FindAutoTyp();
foreach (string typ in typy)
{
typyAut.Items.Add(typ);
}
List <Zakaznik> zakazniks = new List <Zakaznik>();
zakazniks = mapper.FindZak();
foreach (Zakaznik zak in zakazniks)
{
seznamZak.Items.Add(zak.Jmeno + " " + zak.Prijmeni + " " + zak.cisloRidicskehoPrukazu);
}
}
private static Collection <Rezervace> Read(SqlDataReader reader)
{
Collection <Rezervace> rezervaces = new Collection <Rezervace>();
while (reader.Read())
{
int i = -1;
Rezervace rezervace = new Rezervace();
rezervace.cislo_rezervace = reader.GetInt32(++i);
rezervace.cislo_rp = reader.GetString(++i);
rezervace.zakaznik = new Zakaznik();
rezervace.zakaznik.cislo_RP = rezervace.cislo_rp;
rezervace.id_zam = reader.GetString(++i);
rezervace.zamestnanec = new Zamestnanec();
rezervace.zamestnanec.id_zamestnance = rezervace.id_zam;
rezervace.vyzvednuti = reader.GetDateTime(++i);
rezervace.vraceni = reader.GetDateTime(++i);
rezervaces.Add(rezervace);
}
return(rezervaces);
}
private static Collection <Rezervace> Read(SqlDataReader reader)
{
Collection <Rezervace> rezervace = new Collection <Rezervace>();
while (reader.Read())
{
Rezervace jedna_rezervace = new Rezervace();
int i = -1;
jedna_rezervace.rID = reader.GetInt32(++i);
jedna_rezervace.Jmeno = reader.GetString(++i);
jedna_rezervace.Prijmeni = reader.GetString(++i);
jedna_rezervace.Pocet_osob = reader.GetInt32(++i);
jedna_rezervace.Zarezervovane_datum = reader.GetDateTime(++i);
jedna_rezervace.Datum_vytvoreni = reader.GetDateTime(++i);
if (!reader.IsDBNull(++i))
{
jedna_rezervace.Pruvodce_pID = reader.GetInt32(i);
}
rezervace.Add(jedna_rezervace);
}
return(rezervace);
}
public ActionResult Rezervuj(int?id)
{
if (!id.HasValue)
{
return(RedirectToAction("Index"));
}
RezervaceDao rd = new RezervaceDao();
Rezervace r = rd.GetById(id.Value);
MailClient.sendMail(r.uzivatel.login, "Potvrzení rezervace zájezdu", "Vážený zákazníku, vaše rezervace byla úspěšně evidována jako zaplacená.");
string deti = ".";
if (r.pocetDeti > 0)
{
deti = "a pro" + r.pocetDeti + " dětí.";
}
MailClient.sendMail(r.zajezd.hotel.email, "Rezervace zájezdu", "Na váš hotel" + r.zajezd.hotel.nazev + " byla vytvořena rezervace pro" + r.pocetDospelych + " dospělých osob" + deti);
r.zaplaceno = true;
rd.Update(r);
return(RedirectToAction("Detail", "Uzivatele", new { id = r.uzivatel.Id }));
}
public ActionResult Rezervovat(Rezervace r, int?zi)
{
if (!zi.HasValue)
{
return(RedirectToAction("Index"));
}
ZajezdDao zj = new ZajezdDao();
Zajezd z = zj.GetById(zi.Value);
r.zajezd = z;
RezervaceDao rd = new RezervaceDao();
int pocet = r.pocetDeti + r.pocetDeti;
if (pocet > r.zajezd.kapacita)
{
TempData["Rezervace"] = "Omlouváme se, ale nepodařilo se vytvořit rezervaci z důvodu nedostatečné kapacity";
return(RedirectToAction("Detail", "Zajezdy", z.hotel.Id));
}
UzivatelDao ud = new UzivatelDao();
Uzivatel uzivatel = ud.GetByLogin(User.Identity.Name);
int kap = r.zajezd.kapacita - pocet;
z.kapacita = kap;
r.uzivatel = uzivatel;
rd.Create(r);
zj.Update(r.zajezd);
IList <Uzivatel> uzivatele = new UzivatelDao().GetUsersNotInRole(new UzivatelskaPravaDao().GetById(2));
foreach (Uzivatel u in uzivatele)
{
MailClient.sendMail(u.login, "Vytvořená nová rezervace", "Vážený zaměstnanče, byla vytvořena nová rezervace od uživatele" + r.uzivatel.jmeno + " " + r.uzivatel.prijmeni + ".");
}
TempData["Rezervace"] = "Rezervace proběhla úspěšně";
return(RedirectToAction("Index", "Zajezdy"));
}
private void CreateReservation()
{
Rezervace newRezervace = new Rezervace();
newRezervace.Jmeno = textBox_jmeno.Text;
newRezervace.Prijmeni = textBox_prijmeni.Text;
if (string.IsNullOrEmpty(textBox_jmeno.Text) || string.IsNullOrEmpty(textBox_prijmeni.Text))
{
textBlock_info_create.Text = "Není zadané jméno nebo příjmení!";
return;
}
int n;
bool isNumeric = int.TryParse(textBox_pocet_osob.Text, out n);
if (!isNumeric)
{
textBlock_info_create.Text = "Neni zadáno platné číslo počtu lidí!";
return;
}
newRezervace.Pocet_osob = n;
int?year = Convert.ToInt32(comboBox_year_new_reservation.SelectedItem);
int?month = Convert.ToInt32(comboBox_month_new_reservation.SelectedItem);
int?day = Convert.ToInt32(comboBox_day_new_reservation.SelectedItem);
if (year == null || month == null || day == null)
{
textBlock_info_create.Text = "Neni vybrán rok, nebo měsíc, nebo den!";
return;
}
int year2 = (int)year;
int month2 = (int)month;
int day2 = (int)day;
int hour = (int)slider_hour_new_reservation.Value;
int minute = (int)slider_minute_new_reservation.Value;
DateTime datum_rezervace = new DateTime(year2, month2, day2, hour, minute, 0);
newRezervace.Zarezervovane_datum = datum_rezervace;
int pruvodce = 0;
if ((bool)checkBox_pruvodce.IsChecked)
{
pruvodce = 1;
}
newRezervace.Pruvodce_pID = pruvodce;
bool rezervace_vytvorena = RezervaceTable.CreateReservation(newRezervace);
if (rezervace_vytvorena)
{
textBlock_info_create.Text = "Rezervace probehla uspesne.";
UnselectData();
}
else
{
textBlock_info_create.Text = "Rezervaci se nepodarilo provest.";
}
}
