public void HandlelOrders(List <int> aListOrder, int aEmployeeId)
{
DateTime nu = DateTime.Now;
using (KfsContext ctx = new KfsContext(_constring))
{
foreach (var item in aListOrder)
{
OrderIn gevondenOrderIn = ctx.OrderIns
.Include(nameof(OrderIn.OrderLineIns))
.Include(nameof(OrderIn.OrderLineIns) + "." + nameof(OrderLineIn.Product))
.Where(x => x.Id == item).FirstOrDefault();
if (gevondenOrderIn == null)
{
throw new KeyNotFoundException($"id {item} niet gevonden tijdens het afhandelen van OrderIn");
}
foreach (var orderline in gevondenOrderIn.OrderLineIns)
{
//de stock updaten
orderline.Product.CountInStock += orderline.NumberOfProducts;
}
gevondenOrderIn.Id_HandledBy = aEmployeeId;
gevondenOrderIn.HandledAt = nu;
}
ctx.SaveChanges();
}
}
//{
// return _context.Set<TDSentity>().ToList();
//}
////----------------------------------------------------------------------------------
//public virtual IEnumerable<TDSentity> Find(Expression<Func<TDSentity, bool>> aPredicate)
//{
// throw new NotImplementedException();
//}
////----------------------------------------------------------------------------------
//public virtual TDSentity SingleOrDefault(Expression<Func<TDSentity, bool>> aPredicate)
//{
// return _context.Set<TDSentity>().SingleOrDefault(aPredicate);
//}
////===================================================================================== Create's
public virtual TDSentity Add(TDSentity aEntity)
{
if (aEntity is IDateTimeCreateAndUpdate)
{
DateTime nu = DateTime.Now;
(aEntity as IDateTimeCreateAndUpdate).CreatedAt = nu;
(aEntity as IDateTimeCreateAndUpdate).UpdatedAt = nu;
}
using (KfsContext ctx = new KfsContext(_constring))
{
try
{
ctx.Set <TDSentity>().Add(aEntity);
ctx.SaveChanges();
}
//catch (System.Data.Entity.Infrastructure.DbUpdateException uex)
//{
// if(uex.InnerException.InnerException.HResult == -2146232060)
// {
// throw new EvaluateException("bestaat");
// }
//}
catch (System.Data.Entity.Validation.DbEntityValidationException ex)
{
throw new EvaluateException("FATAL ERROR: fout tijdens het wegschrijven naar DB\n" +
$"{ex.EntityValidationErrors.ToList()[0].ValidationErrors.ToList()[0].ErrorMessage}");
//System.Windows.Forms.MessageBox.Show(ex.EntityValidationErrors.ToList()[0].ValidationErrors.ToList()[0].ErrorMessage);
}
}
return(aEntity);
}
public Client Update(Client aClient)
{
Client gevonden = null;
using (KfsContext ctx = new KfsContext(_constring))
{
gevonden = ctx.Set <Client>()
.FirstOrDefault(u => u.Id == aClient.Id);
if (gevonden == null)
{
throw new DuplicateNameException($"user with {aClient.FirstName} not exist");
}
ctx.Entry(gevonden).CurrentValues.SetValues(aClient);
ctx.Entry(gevonden.CltAddresss.ToList()[0]).CurrentValues.SetValues(aClient.CltAddresss.ToList()[0]);
ctx.SaveChanges();
}
return(gevonden);
}
//=====================================================================================
public override Employee Add(Employee aEntity)
{
EmpAppAccount gevonden = null;
if (aEntity.EmpAppAccount != null)
{
using (KfsContext ctx = new KfsContext(_constring))
{
gevonden = ctx.Set <EmpAppAccount>()
.FirstOrDefault(u => u.UserName == aEntity.EmpAppAccount.UserName);
}
}
if (gevonden != null)
{
throw new DuplicateNameException($"user with {aEntity.EmpAppAccount.UserName} already exist");
}
if (aEntity.EmpAppAccount != null)
{
//Console.WriteLine(aEntity.EmpAppAccount.Password + " " + aEntity.EmpAppAccount.UserName);
string encryptedPW = encrypter.EncriptString(aEntity.EmpAppAccount.Password, aEntity.EmpAppAccount.UserName);
//string decryptedPW = encrypter.DecriptString(encryptedPW, aEntity.EmpAppAccount.UserName);
//Console.WriteLine("=================" + decryptedPW);
aEntity.EmpAppAccount.Password = encryptedPW;
}
return(base.Add(aEntity));
}
public override OrderOut Add(OrderOut aEntity)
{
OrderOut terug = null;
using (KfsContext ctx = new KfsContext(_constring))
{
//bijwerken stok
foreach (var lines in aEntity.OrderLineOuts)
{
Product gevondenProdukt = ctx.Products.FirstOrDefault(x => x.EAN == lines.EAN_Product);
if (gevondenProdukt == null)
{
throw new KeyNotFoundException($"EAN {lines.EAN_Product}, niet gevonden bij het bijwerken van de stock");
}
gevondenProdukt.CountInStock -= lines.NumberOfProducts;
}
//als dat gelukt is dan bijwerken OrderOut
terug = base.Add(aEntity);
//uiteindelijk saven
ctx.SaveChanges();
}
return(terug);
}
public Product Update(Product aProduct)
{
Product gevonden = null;
using (KfsContext ctx = new KfsContext(_constring))
{
gevonden = ctx.Set <Product>()
.FirstOrDefault(u => u.EAN == aProduct.EAN);
if (gevonden == null)
{
throw new DuplicateNameException($"user with {aProduct.EAN} not exist");
}
ctx.Entry(gevonden).CurrentValues.SetValues(aProduct);
//ctx.Entry(gevonden.EmpAddress).CurrentValues.SetValues(aEmployee.EmpAddress);
//ctx.Entry(gevonden.EmpContract).CurrentValues.SetValues(aEmployee.EmpContract);
//ctx.Entry(gevonden.EmpAppAccount).CurrentValues.SetValues(aEmployee.EmpAppAccount);
ctx.SaveChanges();
}
return(gevonden);
}
public Employee Update(Employee aEmployee)
{
Employee gevonden = null;
using (KfsContext ctx = new KfsContext(_constring))
{
gevonden = ctx.Set <Employee>()
.FirstOrDefault(u => u.Id == aEmployee.Id);
if (gevonden == null)
{
throw new DuplicateNameException($"user with {aEmployee.FirstName} not exist");
}
ctx.Entry(gevonden).CurrentValues.SetValues(aEmployee);
ctx.Entry(gevonden.EmpAddress).CurrentValues.SetValues(aEmployee.EmpAddress);
ctx.Entry(gevonden.EmpContract).CurrentValues.SetValues(aEmployee.EmpContract);
ctx.Entry(gevonden.EmpAppAccount).CurrentValues.SetValues(aEmployee.EmpAppAccount);
ctx.SaveChanges();
}
return(gevonden);
}
//private List<Supplier> _memoryList;
//public override IEnumerable<Supplier> GetAll()
//{
// if (_memoryList == null)
// {
// _memoryList = base.GetAll().ToList();
// }
// return _memoryList;
// //return base.GetAll();
//}
public List <Supplier> GetAllForOverview()
{
List <Supplier> terug = new List <Supplier>();
using (KfsContext ctx = new KfsContext(_constring))
{
terug = ctx.Set <Supplier>()
.Include(nameof(Supplier.CmpSites))
.Include(nameof(Supplier.Supplier_Product_Prices))
.Include(nameof(Supplier.Supplier_Product_Prices) + "." + nameof(Supplier_Product_Price.Product))
//.OrderBy(x => x.Supplier_Product_Prices.)//.Include(parent => parent.Children.OrderBy(child => child.Order))
.Where(x => x.IsActive)
.ToList();
for (int i = 0; i < terug.Count; i++)
{
terug[i].Supplier_Product_Prices = terug[i].Supplier_Product_Prices.OrderBy(x => x.Product.ProductTitle).ThenBy(x => x.Id).ToList();
//address ook nog als default setten
terug[i].AddressForDetails = terug[i].CmpSites.ToList()[0].CmpSiteAddress;
}
}
return(terug);
}
//======================================================================================== reads
//public virtual TDSentity GetById(int aId)
//{
// //hetEntity Framework vereist dat deze methode hetzelfde exemplaar retourneert
// //elke keer dat het wordt aangeroepen voor een gegeven contextinstantie en entiteitstype.
// //de niet-algemene System.Data.Entity.DbSet geretourneerd door de System.Data.Entity.DbContext.Set(System.Type)
// //methode moet dezelfde onderliggende query en set entiteiten bevatten.Deze invarianten
// //moet worden gehandhaafd als deze methode wordt overschreven voor iets anders dan het maken van
// //test verdubbelt voor het testen van eenheden. Zie de klasse System.Data.Entity.DbSet'1 voor meer informatie
// //DbSet<TDSentity> tmp = _context.Set<TDSentity>();
// //Vindt een entiteit met de opgegeven primaire sleutelwaarden. Als een entiteit met de opgegeven
// //primaire sleutelwaarden bestaan in de context en worden onmiddellijk zonder
// //het doen van een verzoek aan de DB, anders wordt er een verzoek gedaan aan de DB voor
// //een entiteit met de opgegeven primaire sleutelwaarden en deze entiteit, indien gevonden, is bijgevoegd
// //aan de context en keerde terug.Als er geen entiteit wordt gevonden in de context of de DB,
// //dan wordt null geretourneerd.
// //TDSentity terug = tmp.Find(aId);
// TDSentity terug = null;
// return terug;
//}
////----------------------------------------------------------------------------------
public virtual IEnumerable <TDSentity> GetAll()
{
IEnumerable <TDSentity> terug = null;
using (var ctx = new KfsContext(_constring))
{
terug = ctx.Set <TDSentity>().ToList();
ctx.Dispose();
}
return(terug);
}
public List <Product> GetAllforOrderOut()
{
List <Product> terug = new List <Product>();
using (KfsContext ctx = new KfsContext(_constring))
{
terug = ctx.Products
.Include(nameof(Product.ProductType))
.ToList();
}
return(terug);
}
public List <Client> GetAllClientsWithAdress()
{
List <Client> terug = new List <Client>();
using (KfsContext ctx = new KfsContext(_constring))
{
terug = ctx.Clients
.Include(nameof(Client.CltAddresss)).ToList()
.ToList();
}
return(terug);
}
public List <Client> GetAllForOverview()
{
List <Client> terug = new List <Client>();
using (KfsContext ctx = new KfsContext(_constring))
{
terug = ctx.Set <Client>()
.Include(nameof(Client.CltAddresss))
.Where(x => x.IsActive)
.ToList();
}
return(terug);
}
public List <Product> GetAllForOverview()
{
List <Product> terug = new List <Product>();
using (KfsContext ctx = new KfsContext(_constring))
{
terug = ctx.Set <Product>()
.Include(nameof(Product.Supplier_Product_Prices))
.Include(nameof(Product.Supplier_Product_Prices) + "." + nameof(Supplier_Product_Price.Supplier))
//.Include(nameof(Employee.EmpContract) + "." + nameof(EmpContract.EmpContractStatuutType))
//.Include(nameof(Employee.EmpContract) + "." + nameof(EmpContract.EmpContractType))
//.Include(nameof(Employee.EmpAppAccount))
.ToList();
}
return(terug);
}
public List <OrderIn> GetAll_forOrderInHandling()
{
List <OrderIn> terug = null;
using (KfsContext ctx = new KfsContext(_constring))
{
terug = ctx.OrderIns
.Include(nameof(OrderIn.OrderLineIns))
.Include(nameof(OrderIn.OrderLineIns) + "." + (nameof(OrderLineIn.Product)))
.Include(nameof(OrderIn.Supplier))
.Where(x => x.HandledBy == null)
.ToList();
}
return(terug);
}
public List <OrderOut> GetForBalance()
{
List <OrderOut> terug = new List <OrderOut>();
using (KfsContext ctx = new KfsContext(_constring))
{
terug = ctx.OrderOuts
.Include(nameof(OrderOut.Client))
//.Include(nameof(OrderOut.Client) + "." + nameof(Client.CltAddresss))
.Include(nameof(OrderOut.OrderLineOuts))
.Include(nameof(OrderOut.OrderLineOuts) + "." + nameof(OrderLineOut.Product))
.Include(nameof(OrderOut.SoldBy))
.ToList();
}
return(terug);
}
public List <Employee> GetAllForOverview()
{
List <Employee> terug = new List <Employee>();
using (KfsContext ctx = new KfsContext(_constring))
{
terug = ctx.Set <Employee>()
.Include(nameof(Employee.EmpDepartment))
.Include(nameof(Employee.EmpAddress))
.Include(nameof(Employee.EmpContract) + "." + nameof(EmpContract.EmpContractStatuutType))
.Include(nameof(Employee.EmpContract) + "." + nameof(EmpContract.EmpContractType))
.Include(nameof(Employee.EmpAppAccount))
.Where(x => x.IsActive)
.ToList();
}
return(terug);
}
public OrderOut GetForInvoiceById(int aOrderoutId)
{
OrderOut terug = null;
using (KfsContext ctx = new KfsContext(_constring))
{
terug = ctx.OrderOuts
.Include(nameof(OrderOut.Client))
.Include(nameof(OrderOut.Client) + "." + nameof(Client.CltAddresss))
.Include(nameof(OrderOut.OrderLineOuts))
.Include(nameof(OrderOut.OrderLineOuts) + "." + nameof(OrderLineOut.Product))
.Include(nameof(OrderOut.SoldBy))
.Where(x => x.Id == aOrderoutId)
.FirstOrDefault();
}
return(terug);
}
public List <OrderIn> GetForBalance()
{
List <OrderIn> terug = new List <OrderIn>();
using (KfsContext ctx = new KfsContext(_constring))
{
terug = ctx.OrderIns
.Include(nameof(OrderIn.Supplier))
////.Include(nameof(OrderOut.Client) + "." + nameof(Client.CltAddresss))
.Include(nameof(OrderIn.OrderLineIns))
.Include(nameof(OrderIn.OrderLineIns) + "." + nameof(OrderLineIn.Product))
.Include(nameof(OrderIn.OrderedBy))
.Include(nameof(OrderIn.HandledBy))
.ToList();
}
return(terug);
}
public List <string> GetExistingEANsFromEanList(List <string> aListEANs)
{
List <string> terug = new List <string>();
using (KfsContext ctx = new KfsContext(_constring))
{
foreach (var item in aListEANs)
{
var gevonden = ctx.Set <Product>()
.Select(p => p.EAN)
.FirstOrDefault(pean => pean == item);
if (!string.IsNullOrEmpty(gevonden))
{
terug.Add(gevonden);
}
}
}
return(terug);
}
public Client Delete(Client aClient)
{
Client gevonden = null;
using (KfsContext ctx = new KfsContext(_constring))
{
gevonden = ctx.Set <Client>()
.FirstOrDefault(u => u.Id == aClient.Id);
if (gevonden == null)
{
throw new DuplicateNameException($"user with {aClient.FirstName} not exist");
}
gevonden.IsActive = false;
ctx.SaveChanges();
}
return(gevonden);
}
public Employee Delete(Employee aEmployee)
{
Employee gevonden = null;
using (KfsContext ctx = new KfsContext(_constring))
{
gevonden = ctx.Set <Employee>()
.FirstOrDefault(u => u.Id == aEmployee.Id);
if (gevonden == null)
{
throw new DuplicateNameException($"user with {aEmployee.EmpAppAccount.UserName} not exist");
}
gevonden.IsActive = false;
ctx.SaveChanges();
}
return(gevonden);
}
public ProductsForStockManagementPackageDTO GetAllForStockManagement()
{
ProductsForStockManagementPackageDTO terug = new ProductsForStockManagementPackageDTO();
List <ProductForStockDTO> opgehaald;
List <SupplierMinDTO> opgehaaldSupplierMin;
List <int> tmp = new List <int>();
using (KfsContext ctx = new KfsContext(_constring))
{
//ophalen van data
//gegroepeerd per EAN en employeeId en hieruit enkel het laatste id nemen, datum ontbrak :-(
opgehaald = ctx.Products.Join(ctx.Supplier_Product_Prices,
p => p.EAN,
spp => spp.EAN_Product,
(p, ssp) => new ProductForStockDTO()
{
EAN = p.EAN,
ProductTittle = p.ProductTitle,
CountInStock = p.CountInStock,
MinCountInStock = p.MinCountInStock,
MaxCountInStock = p.MaxCountInStock,
WareHouseLocation = p.WareHouseLocation,
UnitPrice = ssp.UnitPrice,
ID_Supplier = ssp.Id_Supplier,
OploopNummer = ssp.Id,
})
.GroupBy(x => new { x.ID_Supplier, x.EAN }).Select(g => g.OrderByDescending(y => y.OploopNummer).FirstOrDefault())
//.OrderByDescending(x => x).Take(100)
.ToList();
Console.WriteLine("===================================================================");
foreach (var item in opgehaald)
{
int xxx = 0;
try
{
xxx = ctx.OrderIns.Join(ctx.OrderLineIns,
o => o.Id,
ol => ol.Id_OrderIn,
(o, ol) => new
{
idAfgehandeld = o.Id_HandledBy,
EAN = ol.EAN_Product,
NumOfProducts = ol.NumberOfProducts,
})
.Where(x => x.EAN == item.EAN && x.idAfgehandeld == null).Select(x => x.NumOfProducts).Sum();
}
catch { }
item.CountInProgress = xxx;
}
Console.WriteLine("===================================================================");
//id's van de suppliers die betrekking hebben op de gevonden lijst distincten
tmp = opgehaald.Select(x => x.ID_Supplier).Distinct().ToList();
opgehaaldSupplierMin = ctx.Suppliers
.Where(t => tmp.Contains(t.Id))
.Select(s => new SupplierMinDTO()
{
Name = s.Name,
Id = s.Id
})
.ToList();
}
foreach (var item in opgehaald)
{
item.SuplierName = opgehaaldSupplierMin.Where(x => x.Id == item.ID_Supplier).Select(x => x.Name).FirstOrDefault();
//1 als bijbestellen nodig is
item.IsOrderNeeded = (item.CountInStock + item.CountInProgress) < item.MinCountInStock;
}
//Console.WriteLine("===================================================");
//foreach (var item in opgehaaldSupplierMin)
//{
// //Console.WriteLine(item.EAN +"," + item.ID_Supplier + "," + item.UnitPrice);
// //Console.WriteLine(item);
// Console.WriteLine(item.Id + ":" + item.Name);
//}
//Console.WriteLine("===================================================");
terug.ProductForStockDTO = opgehaald;
terug.SupplierMinkDTO = opgehaaldSupplierMin;
return(terug);
}
//=====================================================================================
public EmployeeLoggedInDTO LogIn(string aUsername, string aPassword)
{
//EmployeeLoggedInDTO terug = null;
Console.WriteLine(aUsername + " " + aPassword);
EmpAppAccount gevonden = null;
using (KfsContext ctx = new KfsContext(_constring))
{
gevonden = ctx.Set <EmpAppAccount>()
.Include(nameof(EmpAppAccount.Employee))
.FirstOrDefault(u => u.UserName == aUsername && u.Employee.IsActive);
}
if (gevonden != null)
{
Console.WriteLine("gebruiker gevonden");
//kijken of pw overeen komt, zoniet null returnen
string decryptydPw = encrypter.DecriptString(gevonden.Password, aUsername);
if (decryptydPw != aPassword)
{
_inloggedEmployee = null;
return(null);
}
//permissies inladen
//------------------
// de lsb is is de meest rechtse bit
// de hsb is de meest linkse bit, maar deze gaat maar tot 63, van een UInt64 maakt
// EF6 geen tabel, de msb kon ook niet geset worden, daarom hebben we nu maar 63
// mogelijkheden ipv 64 :-(
List <int> tmpPersmisses = new List <int>();
for (int i = 0; i < 64; i++)
{
if (((gevonden.AppPermissions >> i) & 1) != 0)
{
tmpPersmisses.Add(i + 1);
}
}
List <EmployeeLoggedInDTO.Permissions> tmptest = new List <EmployeeLoggedInDTO.Permissions>();
for (int i = 0; i < 64; i++)
{
if (((gevonden.AppPermissions >> i) & 1) != 0)
{
Console.WriteLine(i + 1);
if (Enum.IsDefined(typeof(EmployeeLoggedInDTO.Permissions), (i + 1)))
{
//tmptest.Add(i + 1);
tmptest.Add((EmployeeLoggedInDTO.Permissions)Enum.ToObject(typeof(EmployeeLoggedInDTO.Permissions), i + 1));
}
}
}
foreach (var item in tmptest)
{
Console.WriteLine(item);
}
//bool IsBitSet(byte b, int pos)
//{
// return ((b >> pos) & 1) != 0;
//}
foreach (var item in tmpPersmisses)
{
Console.Write(item + ", ");
}
Console.WriteLine("========================" + tmpPersmisses.Count + "======" + gevonden.AppPermissions);
_inloggedEmployee = new EmployeeLoggedInDTO()
{
AppPermissions = tmptest,
FirstName = gevonden.Employee.FirstName,
NameAddition = gevonden.Employee.NameAddition,
LastName = gevonden.Employee.LastName,
Id = gevonden.Employee.Id,
UserName = gevonden.UserName,
};
}
else
{
_inloggedEmployee = null;
}
return(_inloggedEmployee);
}