/// <summary>
/// Vcituvanje na site podatoci za restoranot vo koj
/// raboti menadzerot koj e tekovno najaven
/// </summary>
private void popolniRestoran()
{
string sqlRestoran = @"SELECT * FROM RESTORAN WHERE RESTORAN_ID = :REST_ID";
OracleCommand cmd = new OracleCommand(sqlRestoran, this.Conn);
try
{
OracleParameter prm = new OracleParameter("REST_ID", OracleDbType.Int64);
prm.Value = this.Manager.RestoranID;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
CurrRestoran = new Restoran();
if (dr.Read())
{
CurrRestoran.RestoranID = (int)dr.GetValue(0);
CurrRestoran.Ime = dr.GetString(2);
if (!dr.IsDBNull(3))
{
CurrRestoran.Ulica = dr.GetString(3);
}
else
{
CurrRestoran.Ulica = "";
}
if (!dr.IsDBNull(4))
{
CurrRestoran.Grad = dr.GetString(4);
}
else
{
CurrRestoran.Grad = "";
}
CurrRestoran.Rejting = (float)dr.GetValue(5);
if (!dr.IsDBNull(6))
{
CurrRestoran.RabotnoVreme = dr.GetString(6);
}
else
{
CurrRestoran.RabotnoVreme = "";
}
if (!dr.IsDBNull(7))
{
CurrRestoran.Kapacitet = dr.GetInt16(7);
}
else
{
CurrRestoran.Kapacitet = null;
}
if (!dr.IsDBNull(8))
{
CurrRestoran.BrojMasi = dr.GetInt16(8);
}
else
{
CurrRestoran.BrojMasi = null;
}
if (!dr.IsDBNull(9))
{
CurrRestoran.CenaZaDostava = dr.GetInt16(9);
}
else
{
CurrRestoran.CenaZaDostava = null;
}
if (!dr.IsDBNull(10))
{
CurrRestoran.PragZaDostava = dr.GetInt16(10);
}
else
{
CurrRestoran.PragZaDostava = null;
}
if (!dr.IsDBNull(11))
{
CurrRestoran.DatumNaOtvoranje = dr.GetDateTime(11);
}
else
{
CurrRestoran.DatumNaOtvoranje = null;
}
Object LogoUrl = dr.GetValue(13);
if (LogoUrl == null)
{
CurrRestoran.LogoUrl = null;
}
else
{
CurrRestoran.LogoUrl = LogoUrl as String;
}
CurrRestoran.Kategorija = dr.GetString(12);
}
this.lblrest.Text = this.CurrRestoran.Ime;
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
}
/// <summary>
/// Vcituvanje na site podatoci za dadeniot vraboten
/// i popolnuvanje na soodvetnite polinja
/// </summary>
private void PopolniInfo()
{
this.lblVrabotenID.Text = this.VrabotenID.ToString();
// Vcituvanje na licnite podatoci na vraboteniot
string sqlVrab = @"SELECT * FROM VRABOTEN WHERE VRABOTEN_ID = :VRAB_ID";
OracleCommand cmd = new OracleCommand(sqlVrab, this.Conn);
cmd.CommandType = CommandType.Text;
OracleParameter prm;
try
{
prm = new OracleParameter("VRAB_ID", OracleDbType.Int64);
prm.Value = this.VrabotenID;
cmd.Parameters.Add(prm);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tbIme.Text = dr.GetString(1);
tbPrezime.Text = dr.GetString(2);
tbEmbg.Text = dr.GetString(3);
try
{
DateTime rag = dr.GetDateTime(4);
tbDenRagjanje.Text = rag.Day.ToString();
tbMesecRagjanje.Text = rag.Month.ToString();
tbGodinaRagjanje.Text = rag.Year.ToString();
}
catch (InvalidCastException e)
{
tbDenRagjanje.Text = "";
tbMesecRagjanje.Text = "";
tbGodinaRagjanje.Text = "";
}
try
{
tbAdresa.Text = dr.GetString(5);
}
catch (InvalidCastException e)
{
tbAdresa.Text = "";
}
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
// Popolnuvanje na poziciite povrzani so rabotniot odnos
// na vraboteniot vo dadeniot restoran
string sqlFunk = @"SELECT * FROM IZVRSHUVA WHERE VRABOTEN_ID = :VRAB_ID AND RESTORAN_ID = :REST_ID";
cmd = new OracleCommand(sqlFunk, this.Conn);
cmd.CommandType = CommandType.Text;
try
{
prm = new OracleParameter("VRAB_ID", OracleDbType.Int64);
prm.Value = this.VrabotenID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("REST_ID", OracleDbType.Int64);
prm.Value = this.Restoran.RestoranID;
cmd.Parameters.Add(prm);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
lblPozicija.Text = dr.GetString(2);
for (int i = 0; i < this.Pozicii.Count; i++)
{
if (this.Pozicii[i] == lblPozicija.Text)
{
this.PozInd = i;
break;
}
}
DateTime vrab = dr.GetDateTime(3);
tbDenVrab.Text = vrab.Day.ToString();
tbMesecVrab.Text = vrab.Month.ToString();
tbGodVrab.Text = vrab.Year.ToString();
string st = dr.GetString(4);
if (st == "1")
{
this.StatusVrab = true;
}
else
{
this.StatusVrab = false;
}
this.UpdateStatusVrab();
tbPlata.Text = dr.GetInt64(5).ToString();
tbOdmor.Text = dr.GetInt64(6).ToString();
tbVkNaracki.Text = dr.GetInt64(7).ToString();
tbStaz.Text = dr.GetInt64(8).ToString();
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
}
/// <summary>
/// Gi azurira podatocite za vraboteniot so menuvanje
/// na vrednostite na soodvetnite atributi vo bazata
/// </summary>
private bool AzurirajPodatoci()
{
// Azuriranje na licnite podatoci za vraboteniot
string sqlVrab = @"UPDATE VRABOTEN SET IME_VRABOTEN = :IME, PREZIME_VRABOTEN = :PREZIME, EMBG = :EMBG, DATUM_NA_RAGJANJE = TO_DATE(:DAT, 'dd.MM.yyyy'), ADRESA_NA_ZHIVEENJE = :ADR WHERE VRABOTEN_ID = :VRAB_ID";
//string sqlVrab = @"UPDATE VRABOTEN SET IME_VRABOTEN = :IME WHERE VRABOTEN_ID = 7";
OracleCommand cmd = new OracleCommand(sqlVrab, this.Conn);
cmd.CommandType = CommandType.Text;
try
{
OracleParameter prm = new OracleParameter("IME", OracleDbType.Varchar2);
prm.Value = this.tbIme.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("PREZIME", OracleDbType.Varchar2);
prm.Value = this.tbPrezime.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("EMBG", OracleDbType.Char);
prm.Value = this.tbEmbg.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("DAT", OracleDbType.Varchar2);
if (tbDenRagjanje.Text.Trim() != "")
{
prm.Value = tbDenRagjanje.Text.Trim() + "." + tbMesecRagjanje.Text.Trim() + "." + tbGodinaRagjanje.Text.Trim();
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("ADR", OracleDbType.Varchar2);
if (this.tbAdresa.Text.Trim() != "")
{
prm.Value = this.tbAdresa.Text.Trim();
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("VRAB_ID", OracleDbType.Int64);
prm.Value = this.VrabotenID;
cmd.Parameters.Add(prm);
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
//cmd.ExecuteNonQuery();
cmd.CommandType = CommandType.Text;
int br1;
try
{
br1 = cmd.ExecuteNonQuery();
}
catch (Exception)
{
br1 = -1;
}
// Azuriranje na podatocite povrzani so rabotniot odnos
// na vraboteniot vo dadeniot restoran
string sqlIzvrsuva = @"UPDATE IZVRSHUVA SET POZICIJA = :POZ, DATUM_NA_VRABOTUVANJE = TO_DATE(:DAT, 'dd.MM.yyyy'), STATUS = :STAT, PLATA = :PLATA, GODISHEN_ODMOR = :ODMOR WHERE VRABOTEN_ID = :VRAB_ID AND RESTORAN_ID = :REST_ID";
cmd = new OracleCommand(sqlIzvrsuva, this.Conn);
try
{
OracleParameter prm = new OracleParameter("POZ", OracleDbType.Varchar2);
prm.Value = this.lblPozicija.Text;
cmd.Parameters.Add(prm);
prm = new OracleParameter("DAT", OracleDbType.Varchar2);
if (tbDenVrab.Text.Trim() != "")
{
prm.Value = tbDenVrab.Text.Trim() + "." + tbMesecVrab.Text.Trim() + "." + tbGodVrab.Text.Trim();
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("STAT", OracleDbType.Char);
if (this.StatusVrab)
{
prm.Value = '1';
}
else
{
prm.Value = '0';
}
cmd.Parameters.Add(prm);
int plata = int.Parse(this.tbPlata.Text.Trim());
prm = new OracleParameter("PLATA", OracleDbType.Int64);
prm.Value = plata;
cmd.Parameters.Add(prm);
prm = new OracleParameter("ODMOR", OracleDbType.Int64);
if (tbOdmor.Text.Trim() != "")
{
int odmor = int.Parse(this.tbOdmor.Text.Trim());
prm.Value = odmor;
}
else
{
prm.Value = 0;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("VRAB_ID", OracleDbType.Int64);
prm.Value = this.VrabotenID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("REST_ID", OracleDbType.Int64);
prm.Value = this.Restoran.RestoranID;
cmd.Parameters.Add(prm);
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
//cmd.ExecuteNonQuery();
int br2;
try
{
br2 = cmd.ExecuteNonQuery();
}
catch (Exception)
{
br2 = -1;
}
if (br1 == -1 || br2 == -1)
{
this.PopolniInfo();
return(false);
}
this.PopolniInfo();
return(true);
}
private void logiranje()
{
{
username = tbUserName.Text;
password = tbPassword.Text;
if (username == "")
{
MessageBoxForm mbf = new MessageBoxForm("Внесeте корисничко име!", false);
mbf.ShowDialog();
tbUserName.Select();
}
else if (password == "")
{
MessageBoxForm mbf = new MessageBoxForm("Внесeте лозинка!", false);
mbf.ShowDialog();
tbPassword.Select();
}
else
{
string sql = @"SELECT LOZINKA FROM KORISNIK WHERE KORISNICHKO_IME = :KOR_IME"; // C#
OracleCommand cmd = new OracleCommand(sql, Conn);
try
{
OracleParameter prm = new OracleParameter("KOR_IME", OracleDbType.Varchar2);
prm.Value = username;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
String realPass = ""; // tocniot password od bazata
if (dr.Read()) // ako uspee da procita znaci postoi toa korisnicko ime
{
realPass = dr.GetString(0);
tocenUser = true;
}
else // ne postoi toa korisnicko ime
{
tocenUser = false;
MessageBoxForm mbf = new MessageBoxForm("Не постои тоа корисничко име. Обидете се повторно.", false);
mbf.ShowDialog();
tbPassword.Clear();
tbUserName.Clear();
tbUserName.Select();
}
// ako postoi korisnickoto ime proveri dali vneseniot password (password) se sovpagja so vistinskiot (realPass)
if (tocenUser)
{
if (password == realPass)
{
//MessageBox.Show("tocen pasvord");
tocenPass = true;
}
else
{
MessageBoxForm mbf = new MessageBoxForm("Внесовте погрешна лозинка. Обидете се повторно.", false);
mbf.ShowDialog();
tocenPass = false;
tbPassword.Clear();
}
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
//ako e vnesen tocniot password
if (tocenPass)
{
//prvo vcitaj vrabotenID i restoranID
sql = @"Select VRABOTEN_ID,RESTORAN_ID from KORISNIK where KORISNICHKO_IME= : KOR_IME";
cmd = new OracleCommand(sql, Conn);
try
{
OracleParameter prm = new OracleParameter("KOR_IME", OracleDbType.Varchar2);
prm.Value = username;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
VrabotenId = (int)dr.GetValue(0);
RestoranId = (int)dr.GetValue(1);
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
///////////////////////////////////////////
//ako vraboteniot e admin
if (VrabotenId == 0)
{
ManagerC manager = new ManagerC(VrabotenId, RestoranId, "ADMIN", "", username, password);
ManagerForma managerForma = new ManagerForma(Conn, manager);
managerForma.Show();
tbPassword.Clear();
}
else // ako vraboteniot ne e admin (vrabID!=0) vcitaj gi i dr informacii za nego
{
sql = @"Select v.ime_vraboten, v.prezime_vraboten, i.Pozicija, i.status From Korisnik k join Vraboten v on k.Vraboten_ID=v.Vraboten_ID Join Izvrshuva i on i.Vraboten_ID=v.Vraboten_ID Join Restoran r on r.Restoran_ID=i.Restoran_ID where korisnichko_ime = :KOR_IME";
cmd = new OracleCommand(sql, Conn);
try
{
OracleParameter prm = new OracleParameter("KOR_IME", OracleDbType.Varchar2);
prm.Value = username;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
// VrabotenId = (int)dr.GetValue(0);
ime = dr.GetString(0);
prezime = dr.GetString(1);
pozicija = dr.GetString(2);
int st;
if (int.TryParse(dr.GetString(3), out st))
{
status = st;
}
// RestoranId = (int)dr.GetValue(5);
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
// otkako se procitani podatocite proveri koja funkcija ja izvrsuva
// i soodvetno otvori mu forma
if (status == 0) //ako vraboteniot e neaktiven, nema privilegii za pristap
{
MessageBoxForm mbf = new MessageBoxForm("Немате привилегии за пристап!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
else
{
if (pozicija.ToLower() == "доставувач")
{
vraboten = new Dostavuvac(VrabotenId, RestoranId, ime, prezime, username, password);
}
else if (pozicija.ToLower() == "келнер")
{
vraboten = new Kelner(VrabotenId, RestoranId, ime, prezime, username, password);
}
if (pozicija.ToLower() == "менаџер") //ako e manager
{
ManagerC manager = new ManagerC(VrabotenId, RestoranId, ime, prezime, username, password);
ManagerForma managerForma = new ManagerForma(Conn, manager);
managerForma.Show();
tbPassword.Clear();
}
else //ako e obicen vraboten
{
VrabotenForma vf = new VrabotenForma(Conn, vraboten);
vf.Show();
tbPassword.Clear();
}
}
}
}
}
}
}
/// <summary>
/// Popolnuvanje na tabelata so soodvetnite
/// podatoci koi vleguvaat vo izvestajot
/// </summary>
private void VcitajPodatoci()
{
String sqlTab = @"SELECT MESEC_DODATOK, IZNOS_DODATOK
FROM DODATOK
WHERE RESTORAN_ID = :REST_ID AND VRABOTEN_ID = :VRAB_ID AND GODINA_DODATOK = :GOD
ORDER BY MESEC_DODATOK";
OracleCommand cmd = new OracleCommand(sqlTab, Conn);
try
{
OracleParameter prm = new OracleParameter("REST_ID", OracleDbType.Int64);
prm.Value = this.Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("VRAB_ID", OracleDbType.Int64);
prm.Value = this.VrabotenID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("GOD", OracleDbType.Int64);
prm.Value = this.tekovnaGodina;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
int ind = 0;
int godDodatok = 0;
while (dr.Read())
{
int tmp = dr.GetInt32(1);
//int pom = int.Parse(dr.GetString(0).ToString()) - 1;
//MessageBox.Show(pom.ToString());
ind = int.Parse(dr.GetString(0).ToString()) - 1;
this.DodatokMeseci[ind].Text = tmp.ToString() + " ден. ";
godDodatok += tmp;
//ind++;
}
if (ind != 0)
{
this.lblVkDodatok.Text = godDodatok.ToString() + " ден. ";
}
else
{
this.lblVkDodatok.Text = " - ";
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
}
/// <summary>
/// Dodavanje vo relacijata Vraboten
/// </summary>
/// <returns></returns>
private int DodadiVoDBVraboten()
{
string insertVrab = @"INSERT INTO VRABOTEN (VRABOTEN_ID, IME_VRABOTEN, PREZIME_VRABOTEN, EMBG, DATUM_NA_RAGJANJE, ADRESA_NA_ZHIVEENJE) VALUES (:VRAB_ID, :IME, :PREZIME, :EMBG, :DAT, :ADR)";
OracleCommand cmd = new OracleCommand(insertVrab, Conn);
try
{
OracleParameter prm = new OracleParameter("VRAB_ID", OracleDbType.Int64);
prm.Value = this.VrabotenID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("IME", OracleDbType.Varchar2);
prm.Value = this.tbIme.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("PREZIME", OracleDbType.Varchar2);
prm.Value = this.tbPrezime.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("EMBG", OracleDbType.Char);
prm.Value = this.tbEmbg.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("DAT", OracleDbType.Date);
if (tbDen.Text.Trim() != "")
{
int d = int.Parse(tbDen.Text.Trim());
int m = int.Parse(tbMesec.Text.Trim());
int y = int.Parse(tbGodina.Text.Trim());
DateTime dt;
try
{
dt = new DateTime(y, m, d);
prm.Value = dt;
}
catch (Exception e)
{
MessageBoxForm mbf = new MessageBoxForm("Невалиден датум!", false);
mbf.Show();
}
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("ADR", OracleDbType.Varchar2);
string adresa = "";
adresa += this.tbAdresa.Text.Trim();
if (this.tbGrad.Text.Trim() != "")
{
adresa += ", " + this.tbGrad.Text.Trim();
}
if (adresa != "")
{
prm.Value = adresa;
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
}
catch (Exception ex)
{
return(-1);
}
cmd.CommandType = CommandType.Text;
int br;
try
{
br = cmd.ExecuteNonQuery();
}
catch (Exception)
{
br = -1;
}
return(br);
}
private void btnDodadi_Click(object sender, EventArgs e)
{
//proverka dali username e unique
string sql = "Select KORISNICHKO_IME from KORISNIK";
OracleCommand cmd = new OracleCommand(sql, Conn);
bool uniqueUser = true;
bool uniqueRest = true;
try
{
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader(); // C#
dr = cmd.ExecuteReader();
while (dr.Read())
{
string usr = dr.GetString(0);
if (tbUser.Text.Trim() == usr)
{
uniqueUser = false;
break;
}
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
// proverka dali imeto na restoranot e unique
sql = "Select IME_RESTORAN from RESTORAN";
cmd = new OracleCommand(sql, Conn);
try
{
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader(); // C#
dr = cmd.ExecuteReader();
while (dr.Read())
{
string ime = dr.GetString(0);
if (tbIme.Text.Trim() == ime)
{
uniqueRest = false;
break;
}
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
if (!uniqueUser)
{
MessageBoxForm m = new MessageBoxForm("Корисничкото име веќе постои! Изберете друго.", false);
m.ShowDialog();
tbUser.SelectAll();
}
else if (!uniqueRest)
{
MessageBoxForm m = new MessageBoxForm("Името на ресторанот веќе постои! Изберете друго.", false);
m.ShowDialog();
tbIme.SelectAll();
}
else //ako e ok
{
MessageBoxForm mbf = new MessageBoxForm("Дали сте сигурни дека сакате да го додадете ресторанот во листата ресторани?");
if (mbf.ShowDialog() == DialogResult.Yes)
{
int br1 = this.DodadiVoDBRestoran();
int br2 = this.dodadiVoDBKorisnik();
int br3 = this.dodadiVoImenik();
int br4 = this.dodadiGlavnoMeni();
if (br1 == 1 && br2 == 1 && br3 == 1 && br4 == 1)
{
MessageBoxForm mbf1 = new MessageBoxForm("Ресторанот беше успешно додаден!", false);
mbf1.ShowDialog();
this.Close();
}
else
{
MessageBoxForm mbf1 = new MessageBoxForm("Грешка!\nРесторанот не може да се додаде!", false);
mbf1.ShowDialog();
}
}
}
}
public void vcitajRestorani()
{
Restorani = new List <Restoran>();
string sql = "Select * from RESTORAN"; // C#
OracleCommand cmd = new OracleCommand(sql, Conn);
cmd.CommandType = CommandType.Text;
try
{
OracleDataReader dr = cmd.ExecuteReader();
Restoran res;
while (dr.Read())
{
res = new Restoran();
Object LogoUrl = dr.GetValue(13);
if (LogoUrl == null)
{
res.LogoUrl = null;
}
else
{
res.LogoUrl = LogoUrl as String;
}
res.RestoranID = (int)dr.GetValue(0);
res.Ime = dr.GetString(2);
if (!dr.IsDBNull(3))
{
res.Ulica = dr.GetString(3);
}
else
{
res.Ulica = "";
}
if (!dr.IsDBNull(4))
{
res.Grad = dr.GetString(4);
}
else
{
res.Grad = "";
}
res.Rejting = (float)dr.GetValue(5);
if (!dr.IsDBNull(6))
{
res.RabotnoVreme = dr.GetString(6);
}
else
{
res.RabotnoVreme = "";
}
if (!dr.IsDBNull(7))
{
res.Kapacitet = dr.GetInt16(7);
}
else
{
res.Kapacitet = null;
}
if (!dr.IsDBNull(8))
{
res.BrojMasi = dr.GetInt16(8);
}
else
{
res.BrojMasi = null;
}
if (!dr.IsDBNull(9))
{
res.CenaZaDostava = dr.GetInt16(9);
}
else
{
res.CenaZaDostava = null;
}
if (!dr.IsDBNull(10))
{
res.PragZaDostava = dr.GetInt16(10);
}
else
{
res.PragZaDostava = null;
}
if (!dr.IsDBNull(11))
{
res.DatumNaOtvoranje = dr.GetDateTime(11);
}
else
{
res.DatumNaOtvoranje = null;
}
res.Kategorija = dr.GetString(12);
Restorani.Add(res);
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
sql = "Select * from IMENIK"; // C#
cmd = new OracleCommand(sql, Conn);
cmd.CommandType = CommandType.Text;
try
{
OracleDataReader drr = cmd.ExecuteReader();
while (drr.Read())
{
foreach (var obj in Restorani)
{
if (obj.RestoranID == (int)drr.GetValue(0))
{
obj.Kontakt.Add(new Telefon(drr.GetString(1)));
}
}
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
}
public void vcitajTabela()
{
String sql = getSqlVer2();
OracleCommand cmd = new OracleCommand(sql, Conn);
try
{
OracleParameter prm = new OracleParameter("REST_ID1", OracleDbType.Int64);
prm.Value = this.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("MESEC1", OracleDbType.NChar);
prm.Value = lblMesec.Text;
cmd.Parameters.Add(prm);
prm = new OracleParameter("GODINA1", OracleDbType.NChar);
prm.Value = lblGodina.Text;
cmd.Parameters.Add(prm);
prm = new OracleParameter("DATUM1", OracleDbType.Varchar2);
prm.Value = lblMesec.Text + "/" + lblGodina.Text;
cmd.Parameters.Add(prm);
prm = new OracleParameter("REST_ID2", OracleDbType.Int64);
prm.Value = this.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("REST_ID3", OracleDbType.Int64);
prm.Value = this.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("MESEC2", OracleDbType.NChar);
prm.Value = lblMesec.Text;
cmd.Parameters.Add(prm);
prm = new OracleParameter("GODINA2", OracleDbType.NChar);
prm.Value = lblGodina.Text;
cmd.Parameters.Add(prm);
prm = new OracleParameter("DATUM2", OracleDbType.Varchar2);
prm.Value = lblMesec.Text + "/" + lblGodina.Text;
cmd.Parameters.Add(prm);
prm = new OracleParameter("REST_ID4", OracleDbType.Int64);
prm.Value = this.RestoranID;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int id = dr.GetInt32(0);
string ime = dr.GetString(1);
string prezime = dr.GetString(2);
int promet = dr.GetInt32(3);
// lblPromet.Text = String.Format("{0}", dr.GetInt32(4));
decimal procent = dr.GetDecimal(5);
VrabPrometProcent v = new VrabPrometProcent(id, ime, prezime, promet, procent);
vraboteni.Add(v);
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
}
private bool azurirajInfo()
{
// Azuriranje na podatocite za restoranot
string sqlRest = @"UPDATE RESTORAN SET IME_RESTORAN = :IME, ULICA = :UL, GRAD = :GR, RABOTNO_VREME = :RB, KAPACITET = :KAP, BROJ_MASI = :BR_M, CENA_ZA_DOSTAVA = :CENA, PRAG_DOSTAVA = :PRAG, DATUM_NA_OTVORANJE = :DAT, KATEGORIJA = :KAT WHERE RESTORAN_ID = :REST_ID";
OracleCommand cmd = new OracleCommand(sqlRest, this.Conn);
cmd.CommandType = CommandType.Text;
try
{
OracleParameter prm = new OracleParameter("IME", OracleDbType.Varchar2);
prm.Value = this.tbIme.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("UL", OracleDbType.Varchar2);
if (tbUlica.Text.Trim() != "")
{
prm.Value = this.tbUlica.Text.Trim();
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("GR", OracleDbType.Varchar2);
if (tbGrad.Text.Trim() != "")
{
prm.Value = this.tbGrad.Text.Trim();
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("RB", OracleDbType.Varchar2);
if (tbRab.Text.Trim() != "")
{
prm.Value = this.tbRab.Text;
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("KAP", OracleDbType.Int32);
if (tbKapacitet.Text.Trim() != "")
{
prm.Value = int.Parse(this.tbKapacitet.Text.Trim());
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
//MessageBox.Show(""+prm.Value);
prm = new OracleParameter("BR_M", OracleDbType.Int32);
if (tbBrojMasi.Text.Trim() != "")
{
prm.Value = int.Parse(this.tbBrojMasi.Text.Trim());
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("CENA", OracleDbType.Int32);
if (tbCenaDostava.Text.Trim() != "")
{
prm.Value = int.Parse(this.tbCenaDostava.Text.Trim());
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("PRAG", OracleDbType.Int32);
if (tbPragDostava.Text.Trim() != "")
{
prm.Value = int.Parse(this.tbPragDostava.Text.Trim());
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("DAT", OracleDbType.Date);
if (tbDen.Text.Trim() != "" && tbMesec.Text.Trim() != "" && tbGodina.Text.Trim() != "")
//prm.Value = tbDen.Text.Trim() + "." + tbMesec.Text.Trim() + "." + tbGodina.Text.Trim();
{
DateTime dt = new DateTime(int.Parse(this.tbGodina.Text.Trim()), int.Parse(this.tbMesec.Text.Trim()), int.Parse(this.tbDen.Text.Trim()));
prm.Value = dt;
}
else
{
prm.Value = null;
}
cmd.Parameters.Add(prm);
prm = new OracleParameter("KAT", OracleDbType.Varchar2);
prm.Value = this.tbKategoorija.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("REST_ID", OracleDbType.Int32);
prm.Value = this.RestoranID;
cmd.Parameters.Add(prm);
//cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
int br1;
try
{
br1 = cmd.ExecuteNonQuery();
}
catch (Exception)
{
br1 = -1;
}
if (br1 == -1)
{
return(false);
}
return(true);
}
private void PopolniInfo()
{
string sqlVrab = @"SELECT * FROM RESTORAN WHERE RESTORAN_ID = :REST_ID";
OracleCommand cmd = new OracleCommand(sqlVrab, this.Conn);
cmd.CommandType = CommandType.Text;
try
{
OracleParameter prm = new OracleParameter("REST_ID", OracleDbType.Int64);
prm.Value = this.RestoranID;
cmd.Parameters.Add(prm);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tbIme.Text = dr.GetString(2);
if (!dr.IsDBNull(3))
{
tbUlica.Text = dr.GetString(3);
}
else
{
tbUlica.Text = "";
}
if (!dr.IsDBNull(4))
{
tbGrad.Text = dr.GetString(4);
}
else
{
tbGrad.Text = "";
}
if (!dr.IsDBNull(6))
{
tbRab.Text = dr.GetString(6);
}
else
{
tbRab.Text = "";
}
if (!dr.IsDBNull(7))
{
tbKapacitet.Text = string.Format("{0}", dr.GetInt16(7));
}
else
{
tbKapacitet.Text = "";
}
if (!dr.IsDBNull(8))
{
tbBrojMasi.Text = string.Format("{0}", dr.GetInt16(8));
}
else
{
tbBrojMasi.Text = "";
}
if (!dr.IsDBNull(9))
{
tbCenaDostava.Text = string.Format("{0}", dr.GetInt16(9));
}
else
{
tbCenaDostava.Text = "";
}
if (!dr.IsDBNull(10))
{
tbPragDostava.Text = string.Format("{0}", dr.GetInt16(10));
}
else
{
tbPragDostava.Text = null;
}
if (!dr.IsDBNull(11))
{
DateTime rag = dr.GetDateTime(11);
tbDen.Text = rag.Day.ToString();
tbMesec.Text = rag.Month.ToString();
tbGodina.Text = rag.Year.ToString();
}
else
{
tbDen.Text = "";
tbMesec.Text = "";
tbGodina.Text = "";
}
tbKategoorija.Text = dr.GetString(12);
}
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
}
public int AzurirajPodatoci()
{
if (this.KorisnickoIme != null && this.Lozinka != null)
{
// Azuriranje na korisnickoto ime i na lozinkata koi veke
// postojat vo bazata
string sqlUser = @"UPDATE KORISNIK SET KORISNICHKO_IME = :KOR, LOZINKA = :LOZ WHERE RESTORAN_ID = :REST_ID AND VRABOTEN_ID = :VRAB_ID";
OracleCommand cmd = new OracleCommand(sqlUser, this.Conn);
cmd.CommandType = CommandType.Text;
try
{
OracleParameter prm = new OracleParameter("KOR", OracleDbType.Varchar2);
prm.Value = this.tbUserName.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("LOZ", OracleDbType.Varchar2);
prm.Value = this.tbPassword.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("REST_ID", OracleDbType.Int64);
prm.Value = this.Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("VRAB_ID", OracleDbType.Int64);
prm.Value = this.VrabotenID;
cmd.Parameters.Add(prm);
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
int br;
try
{
br = cmd.ExecuteNonQuery();
}
catch (Exception)
{
br = -1;
}
this.KorisnickoIme = this.tbUserName.Text.Trim();
this.Lozinka = this.tbPassword.Text.Trim();
this.PopolniInfo();
return(br);
}
else
{
//Dodavanje na vnesenite podatoci za korisnicko ime i lozinka
string sqlUser = @"INSERT INTO KORISNIK (RESTORAN_ID, KORISNICHKO_IME, LOZINKA, VRABOTEN_ID) VALUES (:REST_ID, :KOR, :LOZ, :VRAB_ID)";
OracleCommand cmd = new OracleCommand(sqlUser, this.Conn);
cmd.CommandType = CommandType.Text;
try
{
OracleParameter prm = new OracleParameter("REST_ID", OracleDbType.Int64);
prm.Value = this.Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("KOR", OracleDbType.Varchar2);
prm.Value = this.tbUserName.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("LOZ", OracleDbType.Varchar2);
prm.Value = this.tbPassword.Text.Trim();
cmd.Parameters.Add(prm);
prm = new OracleParameter("VRAB_ID", OracleDbType.Int64);
prm.Value = this.VrabotenID;
cmd.Parameters.Add(prm);
}
catch (Exception ex)
{
MessageBoxForm mbf = new MessageBoxForm("Настана грешка при поврзувањето со базата!", false);
if (mbf.ShowDialog() == DialogResult.Yes)
{
this.Close();
}
else
{
this.Close();
}
}
int br;
try
{
br = cmd.ExecuteNonQuery();
}
catch (Exception)
{
br = -1;
}
this.PopolniInfo();
return(br);
}
}
