public ObservableCollection <ListaPonudbModel> DobiListoPonudb()
{
var connectionString = ConfigurationManager.ConnectionStrings["myDatabaseConnection"].ConnectionString;
var query = "select id_ponudbe, group_concat(v.vrsta_dela separator ', ') as vrsta_dela, cena, datum_zacetka, datum_konca, ustvarjeno, opis, dimenzije, teza, material, tip_oglasa, id_uporabnika, " +
"u.podjetje as podjetje, u.odgovorna_oseba as odgovorna_oseba, u.kraj as kraj, u.tel_st as tel_st, u.email as email, " +
"u.logo as logo, priponke from lista_ponudb l " +
"join uporabniki u on l.id_uporabnika = u.id " +
"join delo_mapping m on l.vrsta_dela = m.id " +
"join vrste_dela v on m.vrste_dela_id = v.id " +
"where izbrisana_ponudba != 1 " + izbrane_ponudbe + " and l.id_uporabnika != " + (Application.Current as App).uporabnikId + " group by l.id_ponudbe order by id_ponudbe desc; ";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand command = new MySqlCommand(query, connection);
//try
//{
connection.Open();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ListaPonudbModel ponudbe = new ListaPonudbModel();
if (reader["id_ponudbe"] != DBNull.Value)
{
ponudbe.IdPonudbe = Convert.ToInt32(reader["id_ponudbe"]);
}
if (reader["vrsta_dela"] != DBNull.Value)
{
ponudbe.VrstaDela = Convert.ToString(reader["vrsta_dela"]);
}
if (reader["cena"] != DBNull.Value)
{
ponudbe.Cena = Convert.ToString(reader["cena"]);
}
if (reader["datum_zacetka"] != DBNull.Value)
{
ponudbe.DatumZacetka = Convert.ToDateTime(reader["datum_zacetka"]);
}
if (reader["datum_konca"] != DBNull.Value)
{
ponudbe.DatumKonca = Convert.ToDateTime(reader["datum_konca"]);
}
if (reader["ustvarjeno"] != DBNull.Value)
{
ponudbe.Ustvarjeno = Convert.ToDateTime(reader["ustvarjeno"]);
}
if (reader["opis"] != DBNull.Value)
{
ponudbe.Opis = Convert.ToString(reader["opis"]);
}
if (reader["dimenzije"] != DBNull.Value)
{
ponudbe.Dimenzije = Convert.ToString(reader["dimenzije"]);
}
if (reader["teza"] != DBNull.Value)
{
ponudbe.Teza = Convert.ToString(reader["teza"]);
}
if (reader["material"] != DBNull.Value)
{
ponudbe.Material = Convert.ToString(reader["material"]);
}
if (reader["tip_oglasa"] != DBNull.Value)
{
//ponudbe.TipOglasa = Convert.ToInt32(reader["tip_oglasa"]);
ponudbe.TipOglasa = "NUDIM DELO";
}
if (reader["id_uporabnika"] != DBNull.Value)
{
ponudbe.IdUporabnika = Convert.ToInt32(reader["id_uporabnika"]);
}
if (reader["podjetje"] != DBNull.Value)
{
ponudbe.UporabnikiPodjetje = Convert.ToString(reader["podjetje"]);
}
if (reader["odgovorna_oseba"] != DBNull.Value)
{
ponudbe.UporabnikiOdgovornaOseba = Convert.ToString(reader["odgovorna_oseba"]);
}
if (reader["kraj"] != DBNull.Value)
{
ponudbe.UporabnikiKraj = Convert.ToString(reader["kraj"]);
}
if (reader["tel_st"] != DBNull.Value)
{
ponudbe.UporabnikiTelSt = Convert.ToString(reader["tel_st"]);
}
if (reader["email"] != DBNull.Value)
{
ponudbe.UporabnikiEmail = Convert.ToString(reader["email"]);
}
if (reader["priponke"] != DBNull.Value)
{
ponudbe.Priponke = Convert.ToInt32(reader["priponke"]);
}
if (reader["logo"] != DBNull.Value)
{
byte[] blob = reader["logo"] as byte[];
using (MemoryStream ms = new MemoryStream())
{
ms.Write(blob, 0, blob.Length);
Bitmap bm = (Bitmap)Image.FromStream(ms);
using (MemoryStream msJpg = new MemoryStream())
{
bm.Save(msJpg, ImageFormat.Jpeg);
ponudbe.Logo = msJpg.GetBuffer();
}
}
}
lista.Add(ponudbe);
}
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.Message);
//}
connection.Close();
return(lista);
}
}
public ObservableCollection <ListaPonudbModel> DobiListoPonudb()
{
var query = "select id_ponudbe, group_concat(v.vrsta_dela separator ', ') as vrsta_dela, cena, datum_zacetka, datum_konca, opis, dimenzije, teza, material, id_uporabnika, " +
"u.podjetje as podjetje, u.odgovorna_oseba as odgovorna_oseba, u.tel_st as tel_st, u.email as email, " +
"u.logo as logo from lista_ponudb l " +
"join uporabniki u on l.id_uporabnika = u.id " +
"join delo_mapping m on l.vrsta_dela = m.id " +
"join vrste_dela v on m.vrste_dela_id = v.vrsta_dela_id " +
"where izbrisana_ponudba != 1 and l.id_uporabnika != " + (Application.Current as App).uporabnikId + " and tip_oglasa = 1 group by l.id_ponudbe order by id_ponudbe desc; ";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand command = new MySqlCommand(query, connection);
try
{
connection.Open();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ListaPonudbModel ponudbe = new ListaPonudbModel();
if (reader["id_ponudbe"] != DBNull.Value)
{
ponudbe.IdPonudbe = Convert.ToInt32(reader["id_ponudbe"]);
}
if (reader["vrsta_dela"] != DBNull.Value)
{
ponudbe.VrstaDela = Convert.ToString(reader["vrsta_dela"]);
}
if (reader["cena"] != DBNull.Value)
{
ponudbe.Cena = Convert.ToString(reader["cena"]);
}
if (reader["datum_zacetka"] != DBNull.Value)
{
ponudbe.DatumZacetka = Convert.ToDateTime(reader["datum_zacetka"]);
}
if (reader["datum_konca"] != DBNull.Value)
{
ponudbe.DatumKonca = Convert.ToDateTime(reader["datum_konca"]);
}
if (reader["opis"] != DBNull.Value)
{
ponudbe.Opis = Convert.ToString(reader["opis"]);
}
if (reader["dimenzije"] != DBNull.Value)
{
ponudbe.Dimenzije = Convert.ToString(reader["dimenzije"]);
}
if (reader["teza"] != DBNull.Value)
{
ponudbe.Teza = Convert.ToString(reader["teza"]);
}
if (reader["material"] != DBNull.Value)
{
ponudbe.Material = Convert.ToString(reader["material"]);
}
if (reader["id_uporabnika"] != DBNull.Value)
{
ponudbe.IdUporabnika = Convert.ToInt32(reader["id_uporabnika"]);
}
if (reader["podjetje"] != DBNull.Value)
{
ponudbe.UporabnikiPodjetje = Convert.ToString(reader["podjetje"]);
}
if (reader["odgovorna_oseba"] != DBNull.Value)
{
ponudbe.UporabnikiOdgovornaOseba = Convert.ToString(reader["odgovorna_oseba"]);
}
if (reader["tel_st"] != DBNull.Value)
{
ponudbe.UporabnikiTelSt = Convert.ToString(reader["tel_st"]);
}
if (reader["email"] != DBNull.Value)
{
ponudbe.UporabnikiEmail = Convert.ToString(reader["email"]);
}
if (!reader.IsDBNull(reader.GetOrdinal("logo")))
{
using (MemoryStream ms = new MemoryStream(reader.GetOrdinal("logo")))
{
//var imageSource = new BitmapImage();
//imageSource.BeginInit();
//imageSource.StreamSource = ms;
//imageSource.CacheOption = BitmapCacheOption.OnLoad;
//imageSource.EndInit();
//ponudbe.Logo = imageSource;
// Assign the Source property of your image
//yourImage.Source = imageSource;
}
}
lista.Add(ponudbe);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
connection.Close();
return(lista);
}
}
