public static bool IsPlatInComanda(Plat p)
{
if (p == null || p.Id <= 0)
{
return(false);
}
try
{
using (ContextDB context = new ContextDB())
{
using (var connexio = context.Database.GetDbConnection())
{
connexio.Open();
using (var consulta = connexio.CreateCommand())
{
consulta.CommandText =
$@" SELECT * FROM `LINIES_COMANDA` WHERE `PLAT` = @plat_id and `ESTAT` = `EN_PREPARACIO`";
BD_Utils.CreateParameter(consulta, "plat_id", p.Id, DbType.Int32);
var reader = consulta.ExecuteReader();
return(reader.Read());
}
}
}
}
catch (Exception ex)
{
return(false);
}
}
public static ObservableCollection <Comanda> GetLlistaComandesPendents()
{
DbTransaction trans = null;
try
{
using (ContextDB context = new ContextDB())
{
using (var connexio = context.Database.GetDbConnection())
{
connexio.Open();
using (var consulta = connexio.CreateCommand())
{
trans = connexio.BeginTransaction();
consulta.Transaction = trans;
consulta.CommandText =
$@" SELECT c.*, ca.NOM, ca.COGNOM1 FROM `comandes` c join `cambrers` ca on ca.CAMBRER_ID = c.CAMBRER
WHERE `COMANDA_ID` IN (SELECT lc.COMANDA from `linies_comanda` lc where lc.COMANDA = c.COMANDA_ID and lc.ESTAT = 'EN_PREPARACIO') ";
var reader = consulta.ExecuteReader();
ObservableCollection <Comanda> comandes = new ObservableCollection <Comanda>();
while (reader.Read())
{
Comanda c = new Comanda();
BD_Utils.Llegeix(reader, out c.id, "COMANDA_ID");
BD_Utils.Llegeix(reader, out c.data_comanda, "DATA_COMANDA");
BD_Utils.Llegeix(reader, out c.taula_id, "TAULA");
BD_Utils.Llegeix(reader, out c.cambrer_name, "NOM");
string cambrerName = "";
BD_Utils.Llegeix(reader, out cambrerName, "COGNOM1");
c.Cambrer_name += " " + cambrerName;
comandes.Add(c);
}
connexio.Close();
foreach (Comanda c in comandes)
{
c.Linies = GetLiniesComanda(c);
}
comandes = new ObservableCollection <Comanda>(comandes.OrderBy(i => i));
return(comandes);
}
}
}
}
catch (Exception ex)
{
return(new ObservableCollection <Comanda>());
}
}
public static ObservableCollection <LiniaComanda> GetLiniesComanda(Comanda c)
{
if (c.Id <= 0)
{
return(new ObservableCollection <LiniaComanda>());
}
try
{
using (ContextDB context = new ContextDB())
{
using (var connexio = context.Database.GetDbConnection())
{
connexio.Open();
using (var consulta = connexio.CreateCommand())
{
consulta.CommandText =
$@" SELECT * FROM `linies_comanda` WHERE COMANDA = @id ";
BD_Utils.CreateParameter(consulta, "id", c.Id, DbType.Int32);
var reader = consulta.ExecuteReader();
ObservableCollection <LiniaComanda> linies = new ObservableCollection <LiniaComanda>();
while (reader.Read())
{
LiniaComanda l = new LiniaComanda();
BD_Utils.Llegeix(reader, out l.id, "LINIA_COM_ID");
BD_Utils.Llegeix(reader, out l.quantitat, "QUANTITAT");
BD_Utils.Llegeix(reader, out l.estat, "ESTAT");
long platId = 0;
BD_Utils.Llegeix(reader, out platId, "PLAT");
Plat p = PlatBD.GetPlat(platId);
l.Plat = p;
l.Comanda = c;
linies.Add(l);
}
linies = new ObservableCollection <LiniaComanda>(linies.OrderBy(i => i));
connexio.Close();
return(linies);
}
}
}
}
catch (Exception ex)
{
return(new ObservableCollection <LiniaComanda>());
}
}
public static int Delete(Plat p)
{
if (p == null || p.Id <= 0)
{
return(-1);
}
if (IsPlatInComanda(p))
{
return(-2);
}
DbTransaction trans = null;
try
{
using (ContextDB context = new ContextDB())
{
using (var connexio = context.Database.GetDbConnection())
{
connexio.Open();
using (DbCommand consulta = connexio.CreateCommand())
{
trans = connexio.BeginTransaction();
consulta.Transaction = trans;
consulta.CommandText = $@"DELETE FROM linies_escandall where PLAT = @platId";
BD_Utils.CreateParameter(consulta, "platId", p.Id, DbType.Int32);
consulta.ExecuteNonQuery();
consulta.CommandText = $@"DELETE FROM plats WHERE PLAT_ID=@platId";
int affectedPlats = consulta.ExecuteNonQuery();
if (affectedPlats != 1)
{
trans.Rollback();
return(-1);
}
else
{
trans.Commit();
return(0);
}
}
}
}
}
catch (Exception ex)
{
return(-2);
}
}
public static Plat GetPlat(long platId)
{
if (platId <= 0)
{
return(new Plat());
}
try
{
using (ContextDB context = new ContextDB())
{
using (var connexio = context.Database.GetDbConnection())
{
connexio.Open();
using (var consulta = connexio.CreateCommand())
{
consulta.CommandText =
$@" SELECT * FROM `plats` WHERE PLAT_ID = @id ";
BD_Utils.CreateParameter(consulta, "id", platId, DbType.Int32);
var reader = consulta.ExecuteReader();
Plat p = new Plat();
if (reader.Read())
{
BD_Utils.Llegeix(reader, out p.id, "PLAT_ID");
BD_Utils.Llegeix(reader, out p.nom, "NOM");
BD_Utils.Llegeix(reader, out p.descripcio, "DESCRIPCIO_MD", null);
BD_Utils.Llegeix(reader, out p.preu, "PREU");
BD_Utils.Llegeix(reader, out p.fotoBytes, "FOTO");
BD_Utils.Llegeix(reader, out p.disponible, "DISPONIBLE");
long categoriaId = 0;
BD_Utils.Llegeix(reader, out categoriaId, "CATEGORIA");
Categoria c = CategoriaBD.GetCategoria(categoriaId);
p.Categoria = c;
}
return(p);
}
}
}
}
catch (Exception ex)
{
return(new Plat());
}
}
public static ObservableCollection <Plat> GetLlistaPlats()
{
try
{
using (ContextDB context = new ContextDB())
{
using (var connexio = context.Database.GetDbConnection())
{
connexio.Open();
using (var consulta = connexio.CreateCommand())
{
consulta.CommandText =
$@" SELECT * FROM `plats` ORDER BY `CATEGORIA` ";
var reader = consulta.ExecuteReader();
ObservableCollection <Plat> plats = new ObservableCollection <Plat>();
while (reader.Read())
{
Plat p = new Plat();
BD_Utils.Llegeix(reader, out p.id, "PLAT_ID");
BD_Utils.Llegeix(reader, out p.nom, "NOM");
BD_Utils.Llegeix(reader, out p.descripcio, "DESCRIPCIO_MD", null);
BD_Utils.Llegeix(reader, out p.preu, "PREU");
BD_Utils.Llegeix(reader, out p.fotoBytes, "FOTO");
BD_Utils.Llegeix(reader, out p.disponible, "DISPONIBLE");
long categoriaId = 0;
BD_Utils.Llegeix(reader, out categoriaId, "CATEGORIA");
Categoria c = CategoriaBD.GetCategoria(categoriaId);
p.Categoria = c;
plats.Add(p);
}
return(plats);
}
}
}
}
catch (Exception ex)
{
return(new ObservableCollection <Plat>());
}
}
public static Categoria GetCategoria(long categoriaId)
{
if (categoriaId <= 0)
{
return(new Categoria());
}
try
{
using (ContextDB context = new ContextDB())
{
using (var connexio = context.Database.GetDbConnection())
{
connexio.Open();
using (var consulta = connexio.CreateCommand())
{
consulta.CommandText =
$@" SELECT * FROM `categories` WHERE `CATEGORIA_ID` = @categoriaId";
BD_Utils.CreateParameter(consulta, "categoriaId", categoriaId, DbType.Int32);
var reader = consulta.ExecuteReader();
Categoria c = null;
if (reader.Read())
{
c = new Categoria();
BD_Utils.Llegeix(reader, out c.id, "CATEGORIA_ID");
BD_Utils.Llegeix(reader, out c.nom, "NOM");
BD_Utils.Llegeix(reader, out c.color, "COLOR", null);
}
return(c);
}
}
}
}
catch (Exception ex)
{
return(new Categoria());
}
}
public static bool SetLiniaComandaPreparada(LiniaComanda liniaComanda)
{
DbTransaction trans = null;
try
{
using (ContextDB context = new ContextDB())
{
using (var connexio = context.Database.GetDbConnection())
{
connexio.Open();
using (DbCommand consulta = connexio.CreateCommand())
{
trans = connexio.BeginTransaction();
consulta.Transaction = trans;
consulta.CommandText = $@" UPDATE `linies_comanda` SET `ESTAT`='PREPARADA' WHERE COMANDA=@comanda_id and LINIA_COM_ID=@linia_id";
BD_Utils.CreateParameter(consulta, "linia_id", liniaComanda.Id, DbType.Int32);
BD_Utils.CreateParameter(consulta, "comanda_id", liniaComanda.Comanda.Id, DbType.Int32);
int filesAfectades = consulta.ExecuteNonQuery();
if (filesAfectades != 1)
{
trans.Rollback();
}
else
{
trans.Commit();
return(true);
}
}
}
}
}
catch (Exception ex)
{
}
return(false);
}
public static ObservableCollection <Categoria> GetLlistaCategories()
{
try
{
using (ContextDB context = new ContextDB())
{
using (var connexio = context.Database.GetDbConnection())
{
connexio.Open();
using (var consulta = connexio.CreateCommand())
{
consulta.CommandText =
$@" SELECT * FROM `categories` ORDER BY `CATEGORIA_ID` ";
var reader = consulta.ExecuteReader();
ObservableCollection <Categoria> categories = new ObservableCollection <Categoria>();
while (reader.Read())
{
Categoria c = new Categoria();
BD_Utils.Llegeix(reader, out c.id, "CATEGORIA_ID");
BD_Utils.Llegeix(reader, out c.nom, "NOM");
BD_Utils.Llegeix(reader, out c.color, "COLOR", null);
categories.Add(c);
}
return(categories);
}
}
}
}
catch (Exception ex)
{
return(new ObservableCollection <Categoria>());
}
}
public async static void Insert(Plat p)
{
if (p == null)
{
return;
}
DbTransaction trans = null;
try
{
using (ContextDB context = new ContextDB())
{
using (var connexio = context.Database.GetDbConnection())
{
connexio.Open();
using (DbCommand consulta = connexio.CreateCommand())
{
trans = connexio.BeginTransaction();
consulta.Transaction = trans;
consulta.CommandText = $@"SELECT `PLAT_ID` FROM `plats` ORDER BY `PLAT_ID` DESC LIMIT 1";
var tmp = consulta.ExecuteScalar();
int plat_id = (int)tmp;
if (plat_id <= 0)
{
plat_id = 1;
}
else
{
plat_id++;
}
consulta.CommandText = $@"
INSERT INTO `plats`(`PLAT_ID`, `NOM`, `DESCRIPCIO_MD`, `PREU`, `FOTO`, `DISPONIBLE`, `CATEGORIA`) VALUES
(@plat_id,@nom,@descripcio,@preu,FROM_BASE64(@foto),@disponible,@categoria_id)";
BD_Utils.CreateParameter(consulta, "plat_id", plat_id, DbType.Int32);
BD_Utils.CreateParameter(consulta, "nom", p.Nom, DbType.String);
BD_Utils.CreateParameter(consulta, "descripcio", p.Descripcio, DbType.String);
BD_Utils.CreateParameter(consulta, "preu", p.Preu, DbType.Decimal);
//BD_Utils.CreateParameter(consulta, "foto", p.Foto.UriSource.AbsolutePath, DbType.String);
string fotoS = string.Empty;
StorageFile file = await StorageFile.GetFileFromApplicationUriAsync(p.Foto.UriSource);
byte[] byteArray;
using (var inputStream = await file.OpenSequentialReadAsync())
{
var readStream = inputStream.AsStreamForRead();
byteArray = new byte[readStream.Length];
await readStream.ReadAsync(byteArray, 0, byteArray.Length);
}
fotoS = Convert.ToBase64String(byteArray);
byteArray = null;
BD_Utils.CreateParameter(consulta, "foto", fotoS, DbType.String);
BD_Utils.CreateParameter(consulta, "disponible", p.Disponible, DbType.Boolean);
BD_Utils.CreateParameter(consulta, "categoria_id", p.Categoria.Id, DbType.Int32);
int affectedPlats = consulta.ExecuteNonQuery();
if (affectedPlats != 1)
{
trans.Rollback();
return;
}
else
{
trans.Commit();
return;
}
}
}
}
}
catch (Exception ex)
{
return;
}
}
