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)]);
}
