private void SyncSaleInvoice_Inventory(Guid branch_id, Guid product_id, int qty, Guid sale_inv_items_id)
{
iSpeakContext context = new iSpeakContext();
var inventory = context.Inventory.AsNoTracking().Where(x => x.Branches_Id == branch_id && x.Products_Id == product_id && x.AvailableQty > 0).OrderBy(x => x.ReceiveDate).FirstOrDefault();
InventoryModels inventoryModels = context.Inventory.Find(inventory.Id);
if (qty >= inventory.AvailableQty)
{
inventoryModels.AvailableQty = 0;
}
else
{
inventoryModels.AvailableQty -= qty;
}
context.Entry(inventoryModels).State = EntityState.Modified;
SaleInvoiceItems_InventoryModels sii_i = new SaleInvoiceItems_InventoryModels
{
Id = Guid.NewGuid(),
SaleInvoiceItems_Id = sale_inv_items_id,
Inventory_Id = inventory.Id,
Qty = inventory.AvailableQty - inventoryModels.AvailableQty
};
context.SaleInvoiceItems_Inventory.Add(sii_i);
context.SaveChanges();
if (qty > inventory.AvailableQty)
{
SyncSaleInvoice_Inventory(branch_id, product_id, qty - inventory.AvailableQty, sale_inv_items_id);
}
}
public void DeleteInventory(object parameter)
{
var selectedInventory = (InventoryModel)parameter;
if (selectedInventory == null)
{
return;
}
if (MessageBox.Show("Soll die ausgewählte Inventurliste gelöscht werden?", "Frage", MessageBoxButton.OKCancel, MessageBoxImage.Question) == MessageBoxResult.Cancel)
{
return;
}
try
{
using (var db = new PetaPoco.Database("db"))
{
db.Execute(sql: "DELETE FROM Inventories WHERE inventoryId =@0;", selectedInventory.Id);
db.Execute(sql: "DELETE FROM InventoryLines WHERE InventoryId =@0;", selectedInventory.Id);
InventoryModels.Remove(selectedInventory);
InventoryLineModels.Clear();
MessageBox.Show("Ausgewählte Bestückungsliste wurde gelöscht.");
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Daten konnten nicht abgerufen werden:\n\n" + ex.Message), "Fehler");
}
}
public ActionResult Edit(StoreInvViewModel sivm)
{
InventoryModels invEdit = db.InventoryModels.Find(sivm.inventory_Id);
db.InventoryModels.Remove(invEdit);
db.SaveChanges();
if (ModelState.IsValid)
{
invEdit.Id = sivm.inventory_Id;
invEdit.price = sivm.price;
invEdit.productName = sivm.productName;
invEdit.quantity = sivm.quantity;
invEdit.SKU = sivm.SKU;
invEdit.warningSent = 0;
invEdit.warningLevel = sivm.warningLevel;
invEdit.refillLevel = sivm.refillLevel;
invEdit.store_id = sivm.inv_store_id;
db.InventoryModels.Add(invEdit);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(sivm));
}
public async Task <ActionResult> Edit(Guid?id)
{
Permission p = new Permission();
bool auth = p.IsGranted(User.Identity.Name, this.ControllerContext.RouteData.Values["controller"].ToString() + "_" + this.ControllerContext.RouteData.Values["action"].ToString());
if (!auth)
{
return(new ViewResult()
{
ViewName = "Unauthorized"
});
}
else
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
InventoryModels inventoryModels = await db.Inventory.FindAsync(id);
if (inventoryModels == null)
{
return(HttpNotFound());
}
ViewBag.listBranch = new SelectList(db.Branches.Where(x => x.Active == true).OrderBy(x => x.Name).ToList(), "Id", "Name");
ViewBag.listProduct = new SelectList(db.Products.Where(x => x.ForSale == true && x.Active == true).OrderBy(x => x.Description).ToList(), "Id", "Description");
ViewBag.listSupplier = new SelectList(db.Suppliers.Where(x => x.Active == true).OrderBy(x => x.Name).ToList(), "Id", "Name");
return(View(inventoryModels));
}
}
public ActionResult DeleteConfirmed(int id)
{
InventoryModels inventoryModels = db.productData.Find(id);
db.productData.Remove(inventoryModels);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Edit([Bind(Include = "Id,productType,productCategory,productVariety,productQuantity,addDate")] InventoryModels inventoryModels)
{
if (ModelState.IsValid)
{
db.Entry(inventoryModels).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(inventoryModels));
}
public ActionResult Create([Bind(Include = "Id,productType,productCategory,productVariety,productQuantity,addDate")] InventoryModels inventoryModels)
{
if (ModelState.IsValid)
{
db.productData.Add(inventoryModels);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(inventoryModels));
}
// GET: Inventory/Delete/5
public ActionResult Delete(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
InventoryModels inventoryModels = db.productData.Find(id);
if (inventoryModels == null)
{
return(HttpNotFound());
}
return(View(inventoryModels));
}
public async Task <ActionResult> Edit([Bind(Include = "Id,Branches_Id,Products_Id,ReceiveDate,BuyQty,AvailableQty,Suppliers_Id,BuyPrice,Notes")] InventoryModels inventoryModels)
{
if (ModelState.IsValid)
{
//inventoryModels.ReceiveDate = new DateTime(inventoryModels.ReceiveDate.Year, inventoryModels.ReceiveDate.Month, inventoryModels.ReceiveDate.Day, DateTime.UtcNow.Hour, DateTime.UtcNow.Minute, DateTime.UtcNow.Second);
//inventoryModels.AvailableQty = inventoryModels.BuyQty;
//db.Entry(inventoryModels).State = EntityState.Modified;
#region Substract Qty
InventoryModels inventoryModels_before = await db.Inventory.AsNoTracking().Where(x => x.Id == inventoryModels.Id).FirstOrDefaultAsync();
Products_QtyModels pq_substract = await db.Products_Qty.Where(x => x.Branches_Id == inventoryModels_before.Branches_Id && x.Products_Id == inventoryModels_before.Products_Id).FirstOrDefaultAsync();
pq_substract.Qty -= inventoryModels_before.BuyQty;
db.Entry(pq_substract).State = EntityState.Modified;
#endregion
#region Added Qty
Products_QtyModels pq_add = await db.Products_Qty.Where(x => x.Branches_Id == inventoryModels.Branches_Id && x.Products_Id == inventoryModels.Products_Id).FirstOrDefaultAsync();
pq_add.Qty += inventoryModels.BuyQty;
db.Entry(pq_add).State = EntityState.Modified;
#endregion
#region Inventory Update
var current_data = await db.Inventory.FindAsync(inventoryModels.Id);
current_data.ReceiveDate = new DateTime(inventoryModels.ReceiveDate.Year, inventoryModels.ReceiveDate.Month, inventoryModels.ReceiveDate.Day, DateTime.UtcNow.Hour, DateTime.UtcNow.Minute, DateTime.UtcNow.Second);
current_data.Products_Id = inventoryModels.Products_Id;
current_data.Suppliers_Id = inventoryModels.Suppliers_Id;
current_data.BuyQty = inventoryModels.BuyQty;
current_data.AvailableQty = (inventoryModels.BuyQty - inventoryModels_before.BuyQty) + inventoryModels.AvailableQty;
current_data.BuyPrice = inventoryModels.BuyPrice;
current_data.Notes = inventoryModels.Notes;
db.Entry(current_data).State = EntityState.Modified;
#endregion
await db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
ViewBag.listBranch = new SelectList(db.Branches.Where(x => x.Active == true).OrderBy(x => x.Name).ToList(), "Id", "Name");
ViewBag.listProduct = new SelectList(db.Products.Where(x => x.ForSale == true && x.Active == true).OrderBy(x => x.Description).ToList(), "Id", "Description");
ViewBag.listSupplier = new SelectList(db.Suppliers.Where(x => x.Active == true).OrderBy(x => x.Name).ToList(), "Id", "Name");
return(View(inventoryModels));
}
public static List <InventoryModels> GetList(DataTable excelTable)
{
List <InventoryModels> list = new List <InventoryModels>();
foreach (DataRow row in excelTable.Rows)
{
InventoryModels model = new InventoryModels();
var key = row.ItemArray;
for (int i = 0; i < key.Length; i++)
{
switch (i)
{
case 0:
model.ChineseName = row[i].ToString();
continue;
case 1:
model.Number = Convert.ToInt32(row[i]);
continue;
case 2:
model.ForeignName = row[i].ToString();
continue;
case 3:
model.E_Name = row[i].ToString();
continue;
case 4:
model.M_Function = row[i].ToString();
continue;
case 5:
model.Number = Convert.ToInt32(row[i]);
continue;
}
}
list.Add(model);
}
return(list);
}
// GET: Inventory/Edit/5
public ActionResult Edit(int?id)
{
InventoryModels inv = db.InventoryModels.Find(id);
List <StoreModels> stores = db.StoreModels.ToList();
List <SelectListItem> storelist = new List <SelectListItem>();
StoreInvViewModel storeinv = new StoreInvViewModel();
foreach (StoreModels store in stores)
{
string txt = "Id: " + store.Id + " City: " + store.city;
storelist.Add(new SelectListItem()
{
Text = txt, Value = store.Id.ToString()
});
}
storeinv.inventory_Id = inv.Id;
storeinv.SKU = inv.SKU;
storeinv.productName = inv.productName;
storeinv.price = inv.price;
storeinv.quantity = inv.quantity;
storeinv.warningLevel = inv.warningLevel;
storeinv.refillLevel = inv.refillLevel;
storeinv.inv_store_id = inv.store_id;
storeinv.alist = storelist;
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
InventoryModels inventoryModels = db.InventoryModels.Find(id);
if (inventoryModels == null)
{
return(HttpNotFound());
}
return(View(storeinv));
}
public async Task <ActionResult> Create([Bind(Include = "Id,Branches_Id,Products_Id,ReceiveDate,BuyQty,AvailableQty,Suppliers_Id,BuyPrice,Notes")] InventoryModels inventoryModels)
{
if (ModelState.IsValid)
{
inventoryModels.Id = Guid.NewGuid();
inventoryModels.ReceiveDate = new DateTime(inventoryModels.ReceiveDate.Year, inventoryModels.ReceiveDate.Month, inventoryModels.ReceiveDate.Day, DateTime.UtcNow.Hour, DateTime.UtcNow.Minute, DateTime.UtcNow.Second);
inventoryModels.AvailableQty = inventoryModels.BuyQty;
db.Inventory.Add(inventoryModels);
Products_QtyModels pq = await db.Products_Qty.Where(x => x.Branches_Id == inventoryModels.Branches_Id && x.Products_Id == inventoryModels.Products_Id).FirstOrDefaultAsync();
if (pq == null)
{
Products_QtyModels products_QtyModels = new Products_QtyModels
{
Id = Guid.NewGuid(),
Branches_Id = inventoryModels.Branches_Id,
Products_Id = inventoryModels.Products_Id,
Qty = inventoryModels.BuyQty
};
db.Products_Qty.Add(products_QtyModels);
}
else
{
pq.Qty += inventoryModels.BuyQty;
db.Entry(pq).State = EntityState.Modified;
}
await db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
ViewBag.listBranch = new SelectList(db.Branches.Where(x => x.Active == true).OrderBy(x => x.Name).ToList(), "Id", "Name");
ViewBag.listProduct = new SelectList(db.Products.Where(x => x.ForSale == true && x.Active == true).OrderBy(x => x.Description).ToList(), "Id", "Description");
ViewBag.listSupplier = new SelectList(db.Suppliers.Where(x => x.Active == true).OrderBy(x => x.Name).ToList(), "Id", "Name");
return(View(inventoryModels));
}
public ActionResult Create(StoreInvViewModel storeinv)
{
InventoryModels inv = new InventoryModels();
if (ModelState.IsValid)
{
inv.Id = storeinv.inventory_Id;
inv.price = storeinv.price;
inv.productName = storeinv.productName;
inv.quantity = storeinv.quantity;
inv.SKU = storeinv.SKU;
inv.warningLevel = storeinv.warningLevel;
inv.refillLevel = storeinv.refillLevel;
inv.warningSent = 0;
inv.store_id = storeinv.inv_store_id;
db.InventoryModels.Add(inv);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(storeinv));
}
// GET: Inventory/Create
public ActionResult Create()
{
List <InventoryModels> inv = db.InventoryModels.ToList();
List <StoreModels> stores = db.StoreModels.ToList();
for (int i = 0; i < inv.Count(); i++)
{
InventoryModels inventory = inv[i];
}
List <SelectListItem> storelist = new List <SelectListItem>();
StoreInvViewModel storemodel = new StoreInvViewModel();
foreach (StoreModels store in stores)
{
string txt = "Id: " + store.Id + " City: " + store.city;
storelist.Add(new SelectListItem()
{
Text = txt, Value = store.Id.ToString()
});
}
storemodel.alist = storelist;
return(View(storemodel));
}
public void GetInventories(object patameter)
{
using (var db = new PetaPoco.Database("db"))
{
try
{
int?storeId = null;
var isStoreId = int.TryParse(App.Current.Properties["StoreId"].ToString(), out int resultStoreId);
if (isStoreId)
{
storeId = resultStoreId;
}
InventoryModels.Clear();
InventoryModels.AddRange(db.Fetch <InventoryModel>("SELECT InventoryId AS Id, Dt AS DATE, IsProcd AS IsProcd, StoreId AS StoreId FROM Inventories WHERE StoreId = @0", storeId));
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Daten konnten nicht abgerufen werden:\n\n" + ex.Message), "Fehler");
}
}
}
public void CreateInventory(object paramater)
{
try
{
var vm = (InventoryViewModel)paramater;
if (vm == null)
{
return;
}
int?storeId = null;
var isStoreId = int.TryParse(App.Current.Properties["StoreId"].ToString(), out int resultStoreId);
if (isStoreId)
{
storeId = resultStoreId;
}
int userId;
var isUserId = int.TryParse(App.Current.Properties["UserId"].ToString(), out int resultUserId);
if (isUserId)
{
userId = resultUserId;
}
else
{
throw new Exception("Vorgang nicht möglich:\n\nUnbekannter Benutzer.");
}
using (var db = new PetaPoco.Database("db"))
{
using (var scope = db.GetTransaction())
{
var rowId = db.ExecuteScalar <int>("INSERT INTO Inventories(Dt, StoreId, UserId, IsProcd) VALUES(date('now'), @0, @1,@2);SELECT last_insert_rowid();", storeId, userId, 0);
var inventory = new InventoryModel()
{
Date = DateTime.Now,
Id = rowId,
IsProcd = false,
StoreId = storeId,
UserId = userId,
};
//InventoryModels.Add(inventory);
//InventoryLineModels.AddRange(db.Fetch<InventoryLineModel>("SELECT a.GTIN AS GTIN, a.ADesc AS ArtDesc, s.StorageName AS StorageName, a.ArticleId AS ArtId FROM Articles a LEFT JOIN Storages s ON a.StorageId = s.StorageId ORDER BY a.GTIN"));
InventoryModels.Add(inventory);
scope.Complete();
}
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Daten konnten nicht abgerufen werden:\n\n" + ex.Message), "Fehler");
}
}