private dynamic UpdateQty(dynamic arg) { if (!this.CurrentUser.HasClaim("admin")) { return(403); } try { var so = arg.Body.Value as JObject; var existingSo = so.ToObject <SaleOrder>(); var newItemsList = new List <int>(); foreach (var item in existingSo.ItemsDetail.ToList()) // create a copy { if (item.Attributes["Qty"] == null) { continue; } else { for (int i = 0; i < (int)item.Attributes["Qty"]; i++) { newItemsList.Add(item.Id); } } } existingSo.Items = newItemsList.ToArray(); var save = this.Request.Url.Path.EndsWith("updateqty"); existingSo.UpdateSaleOrder(this.CurrentSite, this.SiteDatabase, save); if (save) { InventoryItemModule.ProcessSaleOrderUpdate(this.SiteDatabase, existingSo, true, DateTime.Now); } return(new { SaleOrder = existingSo, InventoryRequests = this.SiteDatabase.Query <InventoryItem>().Where(ivt => ivt.SaleOrderId == existingSo.Id).ToList() }); } catch (Exception) { return(400); } }
static InventoryItemModule() { NancyBlackDatabase.ObjectUpdated += (db, table, obj) => { if (table == "SaleOrder") { InventoryItemModule.ProcessSaleOrderUpdate(db, (SaleOrder)obj); } if (table == "InventoryItem") { InventoryItemModule.ProcessInventoryItemUpdate(db, (InventoryItem)obj); } }; }
private dynamic AddProductToSaleOrder(dynamic arg) { if (!this.CurrentUser.HasClaim("admin")) { return(403); } var so = this.SiteDatabase.GetById <SaleOrder>((int)arg.id); dynamic input = arg.Body.Value as JObject; so.AddItem(this.SiteDatabase, this.CurrentSite, (int)input.Id, (int)input.Qty); InventoryItemModule.ProcessSaleOrderUpdate(this.SiteDatabase, so, true, DateTime.Now); return(new { SaleOrder = so, InventoryRequests = this.SiteDatabase.Query <InventoryItem>().Where(ivt => ivt.SaleOrderId == so.Id).ToList() }); }
internal static void ProcessSaleOrderUpdate(NancyBlackDatabase db, SaleOrder saleOrder) { InventoryItemModule.ProcessSaleOrderUpdate(db, saleOrder, false, DateTime.Now); }
public InventoryItemModule() { this.RequiresClaims("admin"); // insert updatedStock here Get["/admin/tables/inventoryitem"] = this.HandleViewRequest("/Admin/commerceadmin-inventory"); Get["/admin/tables/inventoryitem/__averageprice"] = this.HandleRequest((arg) => { return(this.SiteDatabase.Query("SELECT ProductId, AVG(BuyingPrice + BuyingTax) as Price FROM InventoryPurchase WHERE BuyingPrice > 0 GROUP BY ProductId", new { ProductId = 0, Price = 0.0 })); }); Get["/admin/tables/inventoryitem/__recheck"] = this.HandleRequest((arg) => { var saleOrder = this.SiteDatabase.Query <SaleOrder>().AsEnumerable(); IEnumerable <dynamic> priceListResult = this.SiteDatabase.Query("SELECT ProductId, AVG(BuyingPrice) as AvgCost FROM InventoryPurchase GROUP BY ProductId", new { ProductId = 1, AvgCost = 0M }); var priceList = priceListResult.ToLookup(i => i.ProductId); foreach (var so in saleOrder) { if (so.Status == "WaitingForOrder" || so.Status == "WaitingForParts" || so.Status == "Inbound" || so.Status == "CustomsClearance" || so.Status == "OrderProcessing") { // all items must not be fullfilled if status is not yet building or testing var requests = this.SiteDatabase.Query <InventoryItem>().Where(ivt => ivt.SaleOrderId == so.Id).ToList(); foreach (var item in requests) { if (item.IsFullfilled) { item.IsFullfilled = false; item.FulfilledDate = DateTime.MinValue; // breaks connection with inventory purchase id if (item.InventoryPurchaseId != 0) { var ip = this.SiteDatabase.GetById <InventoryPurchase>(item.InventoryPurchaseId); if (ip != null) { ip.InventoryItemId = 0; this.SiteDatabase.UpsertRecord(ip); } item.InventoryPurchaseId = 0; } this.SiteDatabase.Connection.Update(item); } } InventoryItemModule.ProcessSaleOrderUpdate(this.SiteDatabase, so, true, DateTime.Now); } if (so.Status == SaleOrderStatus.Cancel) { InventoryItemModule.ProcessSaleOrderUpdate(this.SiteDatabase, so, true, DateTime.Now); } if (so.Status == "ReadyToShip" || so.Status == "Shipped" || so.Status == "Delivered") { // for those shipped, delivered - make it all fulfilled var requests = this.SiteDatabase.Query <InventoryItem>().Where(ivt => ivt.SaleOrderId == so.Id).ToList(); foreach (var item in requests) { if (item.IsFullfilled == false) { item.IsFullfilled = true; item.FulfilledDate = DateTime.MinValue; var price = priceList[item.ProductId].FirstOrDefault(); if (price != null) { item.BuyingCost = (Decimal)price.AvgCost; } this.SiteDatabase.Connection.Update(item); } } } } return("OK"); }); }
/// <summary> /// When Saleorder is set to waiting for order, generate InventoryItem for each item in the sale order /// TransformInventoryRequest event is called to finalize the list of items. /// </summary> /// <param name="db"></param> /// <param name="saleOrder"></param> internal static void ProcessSaleOrderUpdate(NancyBlackDatabase db, SaleOrder saleOrder, bool forceUpdate, DateTime now) { if (forceUpdate == false) { now = DateTime.Now; // fulfill the requests of this sale order // (No longer used) //InventoryItemModule.ProcessFulfillSaleOrder(db, saleOrder); // only do when status is waiting for order if (saleOrder.Status != SaleOrderStatus.WaitingForOrder) { return; } // if previous status is already waiting for order - do nothing if (db.GetOlderVersions(saleOrder).Any(s => s.Status == SaleOrderStatus.WaitingForOrder)) { return; } } if (saleOrder.Status == SaleOrderStatus.Cancel) { db.Transaction(() => { var relatedRequest = db.Query <InventoryItem>().Where(ivt => ivt.SaleOrderId == saleOrder.Id).ToList(); foreach (var item in relatedRequest) { // remove the usage from purchase var relatedPurchase = db.Query <InventoryPurchase>().Where(ip => ip.InventoryItemId == item.Id).ToList(); foreach (var p in relatedPurchase) { p.InventoryItemId = 0; db.UpsertRecord(p); } // remove the request from database db.DeleteRecord(item); } }); return; } if (saleOrder.PaymentStatus == PaymentStatus.PaymentReceived || saleOrder.PaymentStatus == PaymentStatus.Deposit || saleOrder.PaymentStatus == PaymentStatus.Credit) { } else { return; } var totalDiscount = 0M; var items = new List <InventoryItem>(); foreach (var item in saleOrder.ItemsDetail) { if (item.CurrentPrice < 0) // dont take negative prices (coupon) { totalDiscount += item.CurrentPrice * -1; // record the discount continue; } if (item.Url == "/dummy/dummy" && item.CurrentPrice == 0) { continue; } // For each items in sale order, create an inventory item for (int i = 0; i < (int)item.Attributes.Qty; i++) { // virtual products does not need inventory inbound if (item.Attributes.IsVirtual == 1) { continue; } var ivitm = new InventoryItem() { SaleOrderId = saleOrder.Id, ProductId = item.Id, RequestedDate = now, IsFullfilled = false, QuotedPrice = item.CurrentPrice, SellingPrice = item.CurrentPrice, }; items.Add(ivitm); if (item.CurrentPrice != item.Price) { totalDiscount += item.Price - item.CurrentPrice; } } } // distribute discount into items which has actual sell price var totalTrueItems = items.Where(item => item.QuotedPrice > 0).Count(); var discountRemaining = totalDiscount; while (discountRemaining > 0) { foreach (var item in items) { if (item.SellingPrice > 0) { // discount by 1% var discount = item.SellingPrice * 0.01M; if (discountRemaining - discount < 0) { discount = discountRemaining; } discountRemaining -= discount; item.SellingPrice = item.SellingPrice - discount; if (discountRemaining == 0) { break; } } } } var currentSite = AdminModule.ReadSiteSettings(); foreach (var item in items) { item.__updatedAt = DateTime.Now; item.__createdAt = DateTime.Now; if (item.SellingPrice > 0) { if (currentSite.commerce.billing.vattype == "addvat") { item.SellingTax = item.SellingPrice * (100 + (int)currentSite.commerce.billing.vatpercent) / 100; } if (currentSite.commerce.billing.vattype == "includevat") { var priceWithoutTax = item.SellingPrice * 100 / (100 + (int)currentSite.commerce.billing.vatpercent); item.SellingTax = item.SellingPrice - priceWithoutTax; item.SellingPrice = priceWithoutTax; } } } InventoryItemModule.TransformInventoryRequest(db, saleOrder, items); // NOTE: sometimes we give free, so price can be 0 items = (from item in items where item.SellingPrice >= 0 select item).ToList(); // attempt to merge the old list and new list using product id // by seeing if there is any item that was already fulfilled - if // there is any - copy the information into new list var existing = db.Query <InventoryItem>().Where(ivt => ivt.SaleOrderId == saleOrder.Id).ToLookup(ivt => ivt.ProductId); var newList = items.ToLookup(ivt => ivt.ProductId); foreach (var group in existing) { var existingGroup = existing[group.Key].ToList(); var newGroup = newList[group.Key].ToList(); for (int i = 0; i < existingGroup.Count; i++) { if (i >= newGroup.Count) { // old list has more items - keep them if it is already fulfilled if (existingGroup[i].IsFullfilled) { existingGroup[i].Note = "This sale order have less items, this is an orphaned row"; db.Connection.Update(existingGroup[i]); } else { // otherwise, just deletes them db.Connection.Delete <InventoryItem>(existingGroup[i].Id); } continue; } if (existingGroup[i].IsFullfilled) { // copy information from the existing one // for some reason not yet understand - i cant just remove newGroup[i] from items // and add existingGroup[i] into it instead! newGroup[i].IsFullfilled = true; newGroup[i].SerialNumber = existingGroup[i].SerialNumber; newGroup[i].FulfilledDate = existingGroup[i].FulfilledDate; newGroup[i].BuyingCost = existingGroup[i].BuyingCost; newGroup[i].BuyingTax = existingGroup[i].BuyingTax; newGroup[i].InventoryPurchaseId = existingGroup[i].InventoryPurchaseId; newGroup[i].Note = existingGroup[i].Note; } db.Connection.Delete <InventoryItem>(existingGroup[i].Id); } } db.Connection.InsertAll(items); }
private dynamic HandleSaleorderDetailPage(dynamic arg) { if (!this.CurrentUser.HasClaim("admin")) { return(403); } var id = (int)arg.id; var so = this.SiteDatabase.GetById <SaleOrder>(id); if (so == null) { return(404); } var dummyPage = new Page(); List <AffiliateRewardsClaim> rewardList = null; List <AffiliateRewardsClaim> discountCodes = null; if (so.Customer != null && so.Customer.User != null) { int userId = so.Customer.User.Id; rewardList = AffiliateRewardsClaim.GetRewards(this.SiteDatabase, userId); discountCodes = AffiliateRewardsClaim.GetDiscountCodes(this.SiteDatabase, userId); } if (so.InboundDate == null) { try { // walk back the version and see which one got set to inbound var lastInboundStatus = so.GetRowVersions(this.SiteDatabase) .Select(o => ((JObject)o).ToObject <SaleOrder>()) .Where(s => s.Status == "Inbound") .OrderBy(s => s.__updatedAt) .Select(s => (DateTime?)s.__updatedAt) .LastOrDefault(); so.InboundDate = lastInboundStatus; this.SiteDatabase.UpsertRecord(so); } catch (Exception) { } } // process the inventory InventoryItemModule.ProcessSaleOrderUpdate(this.SiteDatabase, so, false, DateTime.Now); var inventoryItems = this.SiteDatabase.Query <InventoryItem>().Where(i => i.SaleOrderId == so.Id).ToList(); var inventoryToTitleDict = new JObject(); foreach (var item in inventoryItems) { var product = this.SiteDatabase.GetById <Product>(item.ProductId); string productSku = null; if (product.Url.StartsWith("/products/laptop-sku/")) { productSku = product.Title; // before year 2021 sku system } else if (product.Url.Contains("/laptop/")) { productSku = product.GetSKUNumber(so); // since year 2021 sku system } else { productSku = product.Title; } inventoryToTitleDict.Add($"inv-{item.Id}", productSku); } var data = new { SaleOrder = so, PaymentLogs = so.GetPaymentLogs(this.SiteDatabase), RowVerions = so.GetRowVersions(this.SiteDatabase), PaymentMethods = AccountingSystem.AccountingSystemModule.GetCashAccounts(this.SiteDatabase), InventoryRequests = inventoryItems, InventoryToTitleDict = inventoryToTitleDict, LogisticsCompanies = this.SiteDatabase.Query <LogisticsCompany>().ToList(), AffiliateRewardsClaims = rewardList, AffiliateDiscountCodes = discountCodes }; return(View["/Admin/saleorderdetailmanager", new StandardModel(this, dummyPage, data)]); }