public static bool AddFotoForInstrument(int instrID, List <InstrumentImages> fotos)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var qry = db.InstrumentImages.Where(f => f.InstrumentID == instrID).ToList();
foreach (var fot in qry)
{
db.InstrumentImages.Remove(fot);
}
foreach (var foto in fotos)
{
db.InstrumentImages.Add(foto);
}
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
public static bool AddFotoForNet(int netID, List <Fotos> fotos)
{
if (fotos == null)
{
return(true);
}
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var qry = db.Fotos.Where(f => f.NetID == netID).ToList();
foreach (var fot in qry)
{
db.Fotos.Remove(fot);
}
foreach (var foto in fotos)
{
db.Fotos.Add(foto);
}
//Fotos foto = new Fotos { NetID = netID, Foto = image };
//db.Fotos.Add(foto);
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
public bool DeleteInstrumentFromNet(int netID, int instrumentID)
{
try
{
using (ERBA_Entities db = new ERBA_Entities())
{
var qry = db.NetInstrument.Where(ni => ni.NETID == netID && instrumentID == ni.INSID).ToList();
foreach (NetInstrument inst in qry)
{
db.NetInstrument.Remove(inst);
}
db.SaveChanges();
return(true);
//if (qry.AantalINS > 0)
//{
// qry.AantalINS -= 1;
// db.SaveChanges();
//}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
public static bool AddMediaForNet(int netID, List <Media> films)
{
if (films == null)
{
return(true);
}
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var qry = db.Media.Where(f => f.NetID == netID).ToList();
foreach (var med in qry)
{
db.Media.Remove(med);
}
foreach (var film in films)
{
db.Media.Add(film);
}
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
public bool ChangeNetLocatiomn(string netTekst, int locationID, int subLocationID)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
//var net = db.Netten.Where(n => n.ID == netNumber).FirstOrDefault();
var nets = db.Netten.Where(n => n.Naam_net.Contains(netTekst) || n.NetNumber.Contains(netTekst)).ToList();
if (nets.Count == 1)
{
nets[0].LocatieID = locationID;
nets[0].SubLocatieID = subLocationID;
db.SaveChanges();
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
public bool AddEditCategory(Categorie cat)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
if (cat.ID > 0)
{
var qry = db.Categorie.Where(g => g.ID == cat.ID).FirstOrDefault();
qry.Name = cat.Name;
}
else
{
db.Categorie.Add(cat);
}
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
public static string GetValueFromSettingsBySleutel(string sectionKey, string subSection1, string subSection2, string sleutel)
{
using (ERBA_Entities dc = new ERBA_Entities())
{
var qry = (from s in dc.Setting where s.SectionKey == sectionKey && s.SubSection1 == subSection1 && s.SubSection2 == subSection2 && s.Sleutel == sleutel select s).FirstOrDefault();
if (qry == null)
{
return("");
}
else
{
return(qry.Waarde.ToString());
}
}
}
public List <Netten> GetAllNettenPerLocation(int locatieID, int subLocatieID)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var netten = db.Netten.Where(n => n.LocatieID == locatieID && n.SubLocatieID == subLocatieID).ToList();
return(netten);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public List <Media> GetNetMedia(int netID)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var res = db.Media.Where(m => m.NetID == netID).ToList();
return(res);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public List <SubLocaties> GetAllSubLocations(int locatieID)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var subLocaties = db.SubLocaties.Where(l => l.LocatieID == locatieID).ToList();
return(subLocaties);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public List <spGetFullLocations_Result> GetAllLocaties()
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var locaties = db.spGetFullLocations().ToList();
return(locaties);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public static List <Temperatuur> GetListTempratuur()
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var qry = db.Temperatuur.ToList();
return(qry);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public Leveranciers GetLeverancierByID(int id)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var supplier = db.Leveranciers.Where(g => g.LEVID == id).FirstOrDefault();
return(supplier);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public List <Gebruiker> GetAllUesr()
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var geb = db.Gebruiker.ToList();
return(geb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public Gebruiker GetGebruikerByID(int id)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var geb = db.Gebruiker.Where(g => g.ID == id).FirstOrDefault();
return(geb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public Gebruiker GetGebruikerByInlogGegs(string naam, string pw)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var geb = db.Gebruiker.Where(g => g.Inlognaam == naam && g.Wachtwoord == pw).FirstOrDefault();
return(geb);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public List <Categorie> GetAllCategories()
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var cats = db.Categorie.OrderBy(l => l.Name).ToList();
return(cats);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public List <spGetInstrumentNetList_Result> GetInstrumentsNets(int instrID)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var qry = db.spGetInstrumentNetList(instrID).ToList();
return(qry);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public List <spGetNetten_Result> GetNetten()
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var netten = db.spGetNetten().ToList().OrderBy(l => l.Naam).ToList();
return(netten);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public static List <Fotos> GetFotosForNet(int netID)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var qry = db.Fotos.Where(f => f.NetID == netID).ToList();
return(qry);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public List <Leveranciers> GetAllLeveranciers()
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
var suppliers = db.Leveranciers.OrderBy(l => l.Naam).ToList();
return(suppliers);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public spGetNetByID_Result GetNetByID(int netNumber)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
//var net = db.Netten.Where(n => n.ID == netNumber).FirstOrDefault();
var net = db.spGetNetByID(netNumber).FirstOrDefault();
return(net);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public static bool UpdateSetting(Setting setting)
{
try
{
using (ERBA_Entities dc = new ERBA_Entities())
{
var set = (from s in dc.Setting where s.SectionKey == setting.SectionKey && s.SubSection1 == setting.SubSection1 && s.SubSection2 == setting.SubSection2 && s.Sleutel == setting.Sleutel select s).FirstOrDefault();
set.Waarde = setting.Waarde;
dc.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Fout", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
public List <Categorie> GetInstrumentsCategoreis()
{
if (_categories != null)
{
return(_categories);
}
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
_categories = db.Categorie.ToList();
return(_categories);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
public bool EditNetBon(int netID)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
int numberOfMonth = 0;
numberOfMonth = Convert.ToInt32(clsSettings.GetValueFromSettingsBySleutel("Settings", "NetSettings", "Expiration", "Month")); //Vervallenop
var res = db.Netten.Where(m => m.ID == netID).FirstOrDefault();
res.DateCSA = DateTime.Now;
res.Vervallenop = DateTime.Now.AddMonths(numberOfMonth);
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
public List <string> GetUesrFunctie()
{
using (ERBA_Entities db = new ERBA_Entities())
{
List <string> toReturn = new List <string>();
try
{
var gebs = db.Gebruiker.Select(o => new { o.Functie }).ToList();
gebs = gebs.OrderBy(g => g.Functie).ToList();
foreach (var fun in gebs)
{
toReturn.Add(fun.Functie);
}
return(toReturn);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}
// public List<InstrumentSerialnumbers> GetInstrumentsSerials(int instrID)
//{
// using (ERBA_Entities db = new ERBA_Entities())
// {
// try
// {
// var qry = db.InstrumentSerialnumbers.Where(I => I.InstrumentID == instrID).ToList();
// return qry;
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
// return null;
// }
// }
//}
public int AddEditInstrument(Instrumenten Instrument)
{
using (ERBA_Entities db = new ERBA_Entities())
{
int toReturn = 0;
try
{
if (Instrument.ID > 0)
{
Instrumenten qry = db.Instrumenten.Where(n => n.ID == Instrument.ID).FirstOrDefault();
qry.CategorieID = Instrument.CategorieID;
qry.Instrumentnaam = Instrument.Instrumentnaam;
qry.LeverancierID = Instrument.LeverancierID;
qry.Gewicht = Instrument.Gewicht;
qry.AantalVoorraad = Instrument.AantalVoorraad;
qry.VoorraadLocatie = Instrument.VoorraadLocatie;
qry.Datumwijziging = Instrument.Datumwijziging;
qry.MaxTemp = Instrument.MaxTemp;
qry.MinTemp = Instrument.MinTemp;
qry.Bijzonderheden = Instrument.Bijzonderheden;
db.SaveChanges();
toReturn = Instrument.ID;
}
else
{
db.Instrumenten.Add(Instrument);
db.SaveChanges();
toReturn = Instrument.ID;
}
return(toReturn);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(0);
}
}
}
public bool AddEditLeverancier(Leveranciers supplier)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
if (supplier.LEVID > 0)
{
var qry = db.Leveranciers.Where(g => g.LEVID == supplier.LEVID).FirstOrDefault();
qry.Naam = supplier.Naam;
qry.Adres = supplier.Adres;
qry.Postcode = supplier.Postcode;
qry.Plaats = supplier.Plaats;
qry.Vertegenwoordiger = supplier.Vertegenwoordiger;
qry.Telefoonnummer = supplier.Telefoonnummer;
qry.Mobiel = supplier.Mobiel;
qry.Specialisatie = supplier.Specialisatie;
qry.Website = supplier.Website;
qry.Emailadres = supplier.Emailadres;
qry.Crediteurnummer = supplier.Crediteurnummer;
qry.Klantnummer = supplier.Klantnummer;
qry.ExtraInfo = supplier.ExtraInfo;
}
else
{
db.Leveranciers.Add(supplier);
}
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
public bool AddInstrumentToNet(int netID, int instrumentID)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
int usedInstNumbers = db.NetInstrument.Where(instr => instr.INSID == instrumentID).Count();
//int availableInstNumbers = 0;
int availableInstNumbers = Convert.ToInt32(db.Instrumenten.Where(instr => instr.ID == instrumentID).FirstOrDefault().AantalVoorraad);
if (usedInstNumbers >= availableInstNumbers)
{
MessageBox.Show("Voorraad niet genoeg", "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
var qry = db.NetInstrument.Where(ni => ni.NETID == netID && ni.INSID == instrumentID).FirstOrDefault();
if (qry != null)
{
qry.AantalINS += 1;
}
else
{
NetInstrument netInst = new NetInstrument {
NETID = netID, INSID = instrumentID, AantalINS = 1, Inlegvolgnummer = 0
};
db.NetInstrument.Add(netInst);
}
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
public Netten GetNetByNameOrNumber(string netTekst)
{
using (ERBA_Entities db = new ERBA_Entities())
{
try
{
//var net = db.Netten.Where(n => n.ID == netNumber).FirstOrDefault();
var nets = db.Netten.Where(n => n.Naam_net.Contains(netTekst) || n.NetNumber.Contains(netTekst)).ToList();
if (nets.Count == 1)
{
return(nets[0]);
}
else
{
return(null);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "FOUT", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
}