private void LoadLogo()
{
// Доколку нема линк постави ја предодредената слика
if (Restoran.LogoUrl == null)
{
return;
}
Image img;
try
{
// Пробај да ја вчиташ сликата
img = ImageFromURL(Restoran.LogoUrl);
// Забелешка: Вчитување со посебен метод за да се земе Image објект. Можно е и директно во PictureBox
// но така не е можно да се обезбеди паралелизам, а да не се примети кочење, бидејќи самите контроли
// синхронизираат и не дозволуваат во исто време две нишки да прават измени.
}
catch (Exception)
{
// Доколку не може да се вчита постави предодредена слика
img = Resources.FASAP_LOGO;
}
// Чекај додека не заврши почетното исцртување на формата (важно за да не се приметува сецкање при појавувањето на формата)
LoadingSemaphore.WaitOne();
// Постави ја сликата на pictureBoxLogo
SetPbDefaultLogo(pictureBoxLogo, img);
}
public void PostaviNarackiPorast()
{
string sql = @"with t as (select r.restoran_id, r.ime_restoran, pret.pret_mesec, tek.tek_mesec, tek.tek_mesec / pret.pret_mesec as koef
from restoran r left outer join
(select r.restoran_id, r.ime_restoran, count(*) as tek_mesec
from narachka n, restoran r
where n.restoran_id = r.restoran_id and to_char(n.vreme, 'mm.yyyy') like to_char(sysdate, 'mm.yyyy')
group by r.restoran_id, r.ime_restoran) tek on r.ime_restoran = tek.ime_restoran
left outer join
(select r.restoran_id, r.ime_restoran, count(*) as pret_mesec
from narachka n, restoran r
where n.restoran_id = r.restoran_id and to_char(n.vreme, 'mm.yyyy') like to_char( add_months(sysdate, -1), 'mm.yyyy')
group by r.restoran_id, r.ime_restoran) pret on tek.ime_restoran = pret.ime_restoran
)
select Претходен_месец, Тековен_месец, Пораст
from
((select t.restoran_id, t.ime_restoran as Ресторан, NVL(t.pret_mesec, 0) || ' нарачки' as Претходен_месец, NVL(t.tek_mesec, 0) || ' нарачки' as Тековен_месец, round((koef - 1) * 100, 1) || '%' as Пораст
from t
where t.koef <= 1 or t.koef is null)
union
(select t.restoran_id, t.ime_restoran as Ресторан, NVL(t.pret_mesec, 0) || ' нарачки' as Претходен_месец, NVL(t.tek_mesec, 0) || ' нарачки' as Тековен_месец, round(koef * 100, 1) || '%' as Пораст
from t
where t.koef > 1 or t.koef is null)) tmp
where restoran_id = :RES_ID";
OracleCommand cmd = new OracleCommand(sql, Conn);
OracleParameter prm = new OracleParameter("RES_ID", OracleDbType.Int32);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.Text;
string predhoden = null;
string tekoven = null;
string procent = null;
try
{
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
predhoden = dr.GetString(0);
tekoven = dr.GetString(1);
procent = dr.GetString(2);
break;
}
}
catch (Exception)
{
SetText(lblErrorMessage, "Имате проблем со поврзување на конекцијата");
SetVisible(lblErrorMessage, true);
}
LoadingSemaphore.WaitOne();
SetObject(lblPredhoden, predhoden);
SetObject(lblTekoven, tekoven);
SetObject(lblProcent, procent);
}
public void PostaviNajverenKlient()
{
string sql = @"SELECT *
FROM (
SELECT ONL.IME_KLIENT AS IME, ONL.PREZIME_KLIENT AS PREZIME, ONL.ADRESA_ZA_DOSTAVA AS ADRESA, SUM(VKUPNA_CENA) AS SUMA
FROM RESTORAN RES JOIN NARACHKA NAR ON RES.RESTORAN_ID = NAR.RESTORAN_ID
JOIN ONLINE_NARACHKA ONL ON NAR.RESTORAN_ID = ONL.RESTORAN_ID AND NAR.NARACHKA_ID = ONL.NARACHKA_ID
WHERE RES.RESTORAN_ID = :RES_ID
GROUP BY (RES.RESTORAN_ID, ONL.ADRESA_ZA_DOSTAVA, ONL.IME_KLIENT, ONL.PREZIME_KLIENT)
ORDER BY SUM(VKUPNA_CENA) DESC
)";
OracleCommand cmd = new OracleCommand(sql, Conn);
OracleParameter prm = new OracleParameter("RES_ID", OracleDbType.Int64);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.Text;
string ime = null;
string prezime = null;
string suma = null;
string adresa = null;
try
{
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ime = dr.GetString(0);
prezime = dr.GetString(1);
suma = dr.GetInt32(3).ToString();
adresa = dr.GetString(2).ToString();
break;
}
}
catch (Exception)
{
SetText(lblErrorMessage, "Имате проблем со поврзување на конекцијата");
SetVisible(lblErrorMessage, true);
}
LoadingSemaphore.WaitOne();
SetObject(lblIme, ime);
SetObject(lblPrezime, prezime);
SetObject(lblSuma, suma);
SetObject(lblAdresa, adresa);
}
/// <summary>
/// Креирање на мени со помош на хеш мапи.
/// Идеја: Да се овозможи еднократно читање од база и потоа линеарно креирање на дрвото на менија.
///
/// Ги поставува: Restoran.GlavnoMeni и CurrMenu (иста истанца)
/// </summary>
private void KreirajMeni()
{
Dictionary <string, Meni> Menus = new Dictionary <string, Meni>();
Dictionary <StavkaKey, Stavka> Items = new Dictionary <StavkaKey, Stavka>();
// Вчитување на менија
string sql = @"SELECT * FROM MENI WHERE RESTORAN_ID = :RES_ID AND VALIDNOST_MENI = 1";
OracleCommand cmd = new OracleCommand(sql, Conn);
OracleParameter prm = new OracleParameter("RES_ID", OracleDbType.Int64);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
Meni meni;
while (dr.Read())
{
meni = new Meni(dr.GetString(1));
Object obj = dr.GetValue(3);
if (obj == null)
{
meni.ImeGlavno = null;
}
else
{
meni.ImeGlavno = obj as String;
}
String IsValid = (string)dr.GetValue(2);
if (IsValid == "1")
{
meni.ValidnostMeni = true;
}
else
{
meni.ValidnostMeni = false;
}
Menus.Add(meni.Ime, meni);
}
// Вчитување на ставки
sql = @"SELECT * FROM STAVKA WHERE VALIDNOST_STAVKA LIKE '1' AND RESTORAN_ID = :RES_ID";
cmd = new OracleCommand(sql, Conn);
prm = new OracleParameter("RES_ID", OracleDbType.Int64);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
Dodatok dodatok;
Stavka stavka;
while (dr.Read())
{
String IsDecorator = (string)dr.GetValue(5);
Object ImeGlavno = dr.GetValue(1);
Object OpisStavka = dr.GetValue(3);
if (IsDecorator == "1")
{
dodatok = new Dodatok((int)dr.GetValue(2), dr.GetString(7), (decimal)dr.GetValue(4));
if (ImeGlavno == null)
{
dodatok.ImeGlavno = null;
}
else
{
dodatok.ImeGlavno = ImeGlavno as String;
}
if (OpisStavka == null)
{
dodatok.Opis = null;
}
else
{
dodatok.Opis = OpisStavka as String;
}
Items.Add(dodatok.GetStavkaKey(), dodatok);
}
else
{
stavka = new Stavka((int)dr.GetValue(2), dr.GetString(7), (decimal)dr.GetValue(4));
if (ImeGlavno == null)
{
stavka.ImeGlavno = null;
}
else
{
stavka.ImeGlavno = ImeGlavno as String;
}
if (OpisStavka == null)
{
stavka.Opis = null;
}
else
{
stavka.Opis = OpisStavka as String;
}
Items.Add(stavka.GetStavkaKey(), stavka);
}
}
foreach (var obj in Menus)
{
Meni menu;
if (obj.Value.ImeGlavno != null && Menus.TryGetValue(obj.Value.ImeGlavno, out menu))
{
menu.AddComp(obj.Value);
obj.Value.Parent = menu;
}
else
{
Restoran.GlavnoMeni = obj.Value;
}
}
foreach (var obj in Items)
{
Meni menu;
if (Menus.TryGetValue(obj.Value.ImeGlavno, out menu))
{
menu.AddComp(obj.Value);
obj.Value.Parent = menu;
}
}
//lblOsnovnoMeni.UpdateObject(Restoran.GlavnoMeni);
LoadingSemaphore.WaitOne();
SetObject(lblOsnovnoMeni, Restoran.GlavnoMeni);
CurrMenu = Restoran.GlavnoMeni;
PopolniListaMenija();
}
public void PrevzemiPodatociZaRegioni()
{
DateTime Od = DateTime.ParseExact(tbDatumOd.Text + " 00:00", "dd.MM.yyyy HH:mm", CultureInfo.InvariantCulture);
DateTime Do = DateTime.ParseExact(tbDatumDo.Text + " 23:59", "dd.MM.yyyy HH:mm", CultureInfo.InvariantCulture);
string sql = @"select
r.region,
NVL(period_rg.period, 0) as BROJ_NARACHKI, ROUND(nvl(period_rg.period/period_vk.period * 100, 0), 2) AS PROCENT
from
(
select distinct SUBSTR(adresa_za_dostava, 1, INSTR(adresa_za_dostava,'""', 1, 1) - 2) AS REGION
from online_narachka
where restoran_id = :RES_ID and adresa_za_dostava is not null
) r
natural left outer join
(
select count(*) as period
from
(
select narachka_id
from online_narachka
where restoran_id = :RES_ID1 and adresa_za_dostava is not null
) n
natural join
(
select narachka_id
from narachka
where restoran_id = :RES_ID2 AND vreme >= :DatumOd AND vreme <= :DatumDo
) o
) period_vk
left outer join
(
select n.region, count(*) as period
from
(
select narachka_id, SUBSTR(adresa_za_dostava, 1, INSTR(adresa_za_dostava,'""', 1, 1) - 2) AS REGION
from online_narachka
where restoran_id = :RES_ID3 and adresa_za_dostava is not null
) n
natural join
(
select narachka_id
from narachka
where restoran_id = :RES_ID4 AND vreme >= :Od AND vreme <= :Do
) o
group by n.region
) period_rg on period_rg.region = r.region
order by ROUND(nvl(period_rg.period/period_vk.period * 100, 0), 2) desc, r.region asc";
OracleCommand cmd = new OracleCommand(sql, Conn);
OracleParameter prm = new OracleParameter("RES_ID", OracleDbType.Int32);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("RES_ID1", OracleDbType.Int32);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("RES_ID2", OracleDbType.Int32);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("DatumOd", OracleDbType.Date);
prm.Value = Od;
cmd.Parameters.Add(prm);
prm = new OracleParameter("DatumDo", OracleDbType.Date);
prm.Value = Do;
cmd.Parameters.Add(prm);
prm = new OracleParameter("RES_ID3", OracleDbType.Int32);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("RES_ID4", OracleDbType.Int32);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("Od", OracleDbType.Date);
prm.Value = Od;
cmd.Parameters.Add(prm);
prm = new OracleParameter("Do", OracleDbType.Date);
prm.Value = Do;
cmd.Parameters.Add(prm);
Regioni = new List <Naracki.Region>();
try
{
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Naracki.Region reg = new Naracki.Region(dr.GetString(0), dr.GetInt32(1), dr.GetDecimal(2));
Regioni.Add(reg);
}
}
catch (Exception ex)
{
SetText(lblErrorMessage, "Имате проблем со поврзување на конекцијата");
SetVisible(lblErrorMessage, true);
}
LoadingSemaphore.WaitOne();
PostaviRegioni();
}
public void PrevzemiStavki()
{
string sql = @"SELECT A.IME_STAVKA AS STAVKA, A.CENA_STAVKA || ' ден.' AS CENA, NVL(A.PROSEK, 0) || ' ком/ден' AS NOV, NVL(B.PROSEK, 0) || ' ком/ден' AS STAR, ROUND((A.PROSEK - B.PROSEK)*100/B.PROSEK, 2) || '%' AS PROCENT_NA_PORAST
FROM
(
SELECT S.IME_MENI, S.IME_STAVKA, S.OPIS_STAVKA, S.CENA_STAVKA, VALIDNOST_STAVKA, ROUND(SUM(V.KOLICHINA_STAVKA) / TO_NUMBER(MAX(N.VREME) - MIN(N.VREME) + 1), 1) AS PROSEK
FROM
(
SELECT RESTORAN_ID, IME_MENI, STAVKA_ID, IME_STAVKA, VALIDNOST_STAVKA, OPIS_STAVKA, CENA_STAVKA
FROM
(
SELECT
RESTORAN_ID, IME_MENI, STAVKA_ID, IME_STAVKA, VALIDNOST_STAVKA, OPIS_STAVKA, CENA_STAVKA
FROM
STAVKA
WHERE
RESTORAN_ID = :RES_ID AND VALIDNOST_STAVKA = 1
)
WHERE
(RESTORAN_ID, IME_MENI, IME_STAVKA) IN
(
SELECT
RESTORAN_ID, IME_MENI, IME_STAVKA
FROM
STAVKA
WHERE RESTORAN_ID = :RES_ID1 AND VALIDNOST_STAVKA = 0
)
) S
LEFT OUTER JOIN
VKLUCHUVA V
ON
S.RESTORAN_ID = V.RESTORAN_ID AND S.IME_MENI = V.IME_MENI AND S.STAVKA_ID = V.STAVKA_ID
LEFT OUTER JOIN
NARACHKA N
ON
V.RESTORAN_ID = N.RESTORAN_ID AND V.NARACHKA_ID = N.NARACHKA_ID
GROUP BY
(S.RESTORAN_ID, S.IME_MENI, S.STAVKA_ID, S.IME_STAVKA, S.VALIDNOST_STAVKA, S.OPIS_STAVKA, S.CENA_STAVKA)
) A
JOIN
(
SELECT IME_MENI, IME_STAVKA, OPIS_STAVKA, AVG(PROSEK) AS PROSEK
FROM
(
SELECT S.IME_MENI, S.IME_STAVKA, S.OPIS_STAVKA, VALIDNOST_STAVKA, ROUND(SUM(V.KOLICHINA_STAVKA) / TO_NUMBER(MAX(N.VREME) - MIN(N.VREME) + 1), 1) AS PROSEK
FROM
(
SELECT RESTORAN_ID, IME_MENI, STAVKA_ID, IME_STAVKA, VALIDNOST_STAVKA, OPIS_STAVKA
FROM
(
SELECT
RESTORAN_ID, IME_MENI, STAVKA_ID, IME_STAVKA, VALIDNOST_STAVKA, OPIS_STAVKA
FROM
STAVKA
WHERE
RESTORAN_ID = :RES_ID2 AND VALIDNOST_STAVKA = 0
)
WHERE
(RESTORAN_ID, IME_MENI, IME_STAVKA) IN
(
SELECT
RESTORAN_ID, IME_MENI, IME_STAVKA
FROM
STAVKA
WHERE RESTORAN_ID = :RES_ID3 AND VALIDNOST_STAVKA = 0
)
) S
LEFT OUTER JOIN
VKLUCHUVA V
ON
S.RESTORAN_ID = V.RESTORAN_ID AND S.IME_MENI = V.IME_MENI AND S.STAVKA_ID = V.STAVKA_ID
LEFT OUTER JOIN
NARACHKA N
ON
V.RESTORAN_ID = N.RESTORAN_ID AND V.NARACHKA_ID = N.NARACHKA_ID
GROUP BY
(S.RESTORAN_ID, S.IME_MENI, S.STAVKA_ID, S.IME_STAVKA, S.VALIDNOST_STAVKA, S.OPIS_STAVKA)
)
GROUP BY (IME_MENI, IME_STAVKA, OPIS_STAVKA)
) B
ON
A.IME_STAVKA = B.IME_STAVKA AND A.IME_MENI = B.IME_MENI AND NVL(A.OPIS_STAVKA, 0) = NVL(B.OPIS_STAVKA, 0)" ;
if (CurrSortiranje.Equals(Sortiranje.ImeOpagjacki))
{
sql += "\nORDER BY STAVKA desc";
}
else if (CurrSortiranje.Equals(Sortiranje.ImeRastecki))
{
sql += "\nORDER BY STAVKA asc";
}
else if (CurrSortiranje.Equals(Sortiranje.ProcentOpagjacki))
{
sql += "\nORDER BY ROUND((A.PROSEK - B.PROSEK)*100/B.PROSEK, 2) desc, STAVKA";
}
else if (CurrSortiranje.Equals(Sortiranje.ProcentRastecki))
{
sql += "\nORDER BY ROUND((A.PROSEK - B.PROSEK)*100/B.PROSEK, 2) asc, STAVKA";
}
else if (CurrSortiranje.Equals(Sortiranje.StaraOpagjacki))
{
sql += "\nORDER BY NVL(B.PROSEK, 0) desc, STAVKA";
}
else if (CurrSortiranje.Equals(Sortiranje.StaraRastecki))
{
sql += "\nORDER BY NVL(B.PROSEK, 0) asc, STAVKA";
}
else if (CurrSortiranje.Equals(Sortiranje.NovaOpagjacki))
{
sql += "\nORDER BY NVL(A.PROSEK, 0) desc, STAVKA";
}
else if (CurrSortiranje.Equals(Sortiranje.NovaRastecki))
{
sql += "\nORDER BY NVL(A.PROSEK, 0) asc, STAVKA";
}
OracleCommand cmd = new OracleCommand(sql, Conn);
OracleParameter prm = new OracleParameter("RES_ID", OracleDbType.Int32);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("RES_ID1", OracleDbType.Int32);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("RES_ID2", OracleDbType.Int32);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
prm = new OracleParameter("RES_ID3", OracleDbType.Int32);
prm.Value = Restoran.RestoranID;
cmd.Parameters.Add(prm);
Stavki = new List <StavkaPorast>();
try
{
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
StavkaPorast st = new StavkaPorast(dr.GetString(0), dr.GetString(3), dr.GetString(2), dr.GetString(1), dr.GetString(4));
Stavki.Add(st);
}
}
catch (Exception ex)
{
SetText(lblErrorMessage, "Имате проблем со поврзување на конекцијата");
SetVisible(lblErrorMessage, true);
}
LoadingSemaphore.WaitOne();
PostaviTabela();
}
