/// <summary>
/// Handles the case when sale order is using credit
/// </summary>
/// <param name="db"></param>
/// <param name="so"></param>
internal static void HandleCreditRequest(NancyBlackDatabase db, SaleOrder saleorder)
{
if (saleorder.PaymentStatus != PaymentStatus.Credit)
{
return;
}
// only create one receivable per sale order
var existingReceivable = db.Query <AccountingEntry>().Where(e => e.SaleOrderId == saleorder.Id && e.IncreaseAccount == "Receivable").FirstOrDefault();
if (existingReceivable != null)
{
// update amount if changed
if (existingReceivable.IncreaseAmount != saleorder.TotalAmount)
{
existingReceivable.IncreaseAmount = saleorder.TotalAmount;
db.UpsertRecord(existingReceivable);
}
return;
}
AccountingEntry receivableEntry = new AccountingEntry();
receivableEntry.TransactionDate = DateTime.Now;
receivableEntry.DueDate = DateTime.Now.Date.AddDays(30);
receivableEntry.TransactionType = "newaccount";
receivableEntry.DebtorLoanerName = "Receivable From Sales";
receivableEntry.IncreaseAccount = "Receivable";
receivableEntry.IncreaseAmount = saleorder.TotalAmount;
receivableEntry.SaleOrderId = saleorder.Id;
db.UpsertRecord(receivableEntry);
}
public void AddRMAItem(NancyBlackDatabase db, RMAItem item, bool saveRMA = true)
{
item = db.UpsertRecord <RMAItem>(item);
this.RMAItemsId.Add(item.Id);
if (saveRMA)
{
db.UpsertRecord <RMA>(this);
}
}
/// <summary>
/// Translate the input to given locale
/// </summary>
/// <param name="input"></param>
/// <param name="language"></param>
/// <returns></returns>
public string Translate(string input, string language, string defaultTranslation = null, bool useMachineTranslation = true)
{
TranslateHelper.Initialize();
// primary language - is no translation
if (string.IsNullOrEmpty(language))
{
return(input);
}
var key = language + "-" + input;
string translated;
if (_Translations.TryGetValue(key, out translated) == false)
{
if (string.IsNullOrEmpty(defaultTranslation))
{
if (useMachineTranslation)
{
try
{
}
catch (Exception)
{
return(input);
}
}
}
lock (BaseModule.GetLockObject("Translate-" + key))
{
// when other threads unlocked - we have to check again
if (_Translations.ContainsKey(key))
{
return(_Translations[key]);
}
_Translations[key] = defaultTranslation;
_Database.UpsertRecord <TranslateEntry>(new TranslateEntry()
{
Primary = input,
Language = language,
Translated = defaultTranslation
});
return(defaultTranslation);
}
}
return(translated);
}
internal static void ProcessReceiptCreation(NancyBlackDatabase db, Receipt obj)
{
// When payment receipt is created, create accounting entry
db.Transaction(() =>
{
var saleorder = db.GetById <SaleOrder>(obj.SaleOrderId);
var paymentlog = db.GetById <PaymentLog>(obj.PaymentLogId);
if (saleorder == null || paymentlog == null)
{
// bogus receipt
throw new InvalidOperationException("Invalid Receipt was created");
}
// Ensures all sale order logic has been ran
// if the sale order was created before new system change
if (saleorder.__createdAt < TaxSystemEpoch)
{
saleorder.UpdateSaleOrder(AdminModule.ReadSiteSettings(), db, false);
}
// Receipt will create 2 entries
// 1) PaymentSource account increases, with total amount
// TODO: Mapping from PaymentSource to Account
AccountingEntry entry1 = new AccountingEntry();
entry1.TransactionDate = paymentlog.__createdAt;
entry1.TransactionType = "income";
entry1.DebtorLoanerName = "Customer";
entry1.IncreaseAccount = paymentlog.PaymentSource;
entry1.IncreaseAmount = saleorder.TotalAmount;
entry1.SaleOrderId = saleorder.Id;
db.UpsertRecord(entry1);
if (saleorder.TotalTax > 0)
{
// 2) paid tax is decreased
// (ภาษีขาย ทำให้ภาษีซื้อลดลง, ภาษีซื้อ บันทึกไว้ตอน InventoryInbound)
AccountingEntry entry2 = new AccountingEntry();
entry2.TransactionDate = paymentlog.__createdAt;
entry2.TransactionType = "expense";
entry2.DebtorLoanerName = "Tax";
entry2.DecreaseAccount = "Paid Tax";
entry2.DecreaseAmount = saleorder.TotalTax * -1;
entry2.SaleOrderId = saleorder.Id;
db.UpsertRecord(entry2);
}
});
}
/// <summary>
/// When inventory inbound is created, find the inventory item that needs to be fullfilled
/// and fullfil with item from inventory inbound
/// </summary>
/// <param name="db"></param>
/// <param name="obj"></param>
internal static void ProcessInventoryInboundCreation(NancyBlackDatabase db, InventoryInbound obj)
{
// ensures that only one thread will be doing this
lock (InventoryAdminModule.LockObject)
{
db.Transaction(() =>
{
var inboundItems = new List <InventoryItem>();
foreach (var item in obj.Items)
{
InventoryItem ivitm = new InventoryItem();
ivitm.InboundDate = obj.InboundDate;
ivitm.InventoryInboundId = obj.Id;
ivitm.ProductId = item.ProductId;
ivitm.BuyingCost = item.Price;
ivitm.BuyingTax = item.Tax;
db.UpsertRecord(ivitm);
inboundItems.Add(ivitm);
}
InventoryAdminModule.InboundCompleted(db, obj, inboundItems);
});
}
}
/// <summary>
/// Pull serial numbers into ItemsDetail Serial Number Attribute
/// </summary>
public void EnsuresSerialNumberVisible(NancyBlackDatabase db)
{
if (this.Status == SaleOrderStatus.Delivered ||
this.Status == SaleOrderStatus.Shipped ||
this.Status == SaleOrderStatus.ReadyToShip ||
this.Status == SaleOrderStatus.Testing)
{
if (this.ItemsDetail.Any(p => p.Attributes == null || p.Attributes.Serial == null))
{
var ivt = db.Query <InventoryItem>().Where(row => row.SaleOrderId == this.Id).ToLookup(row => row.ProductId);
foreach (var item in this.ItemsDetail)
{
if (item.Attributes == null)
{
item.Attributes = new JObject();
}
item.Attributes.Serial =
string.Join(",", ivt[item.Id].Select(row => row.SerialNumber));
}
db.UpsertRecord(this);
}
}
}
public static void GenerateUserCode(NancyBlackDatabase db, NcbUser user)
{
user.Code = Guid.NewGuid().ToString().Substring(0, 5).ToUpper();
user.CodeRequestDate = DateTime.Now;
db.UpsertRecord <NcbUser>(user);
}
public void Static_Insert()
{
var temp = Path.GetTempFileName();
SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(temp, true);
NancyBlackDatabase db = new NancyBlackDatabase(conn);
var instance = db.UpsertRecord(new TestClass());
Assert.IsTrue(instance.Id > 0, "ID was not set");
Assert.IsTrue(instance.__createdAt != DateTime.MinValue, "__createdAt was not set");
Assert.IsTrue(instance.__updatedAt != DateTime.MinValue, "__updatedAt was not set");
Assert.IsTrue(instance.__version != null, "Version was not set");
var instance2 = db.UpsertRecord(new TestClass());
Assert.IsTrue(instance2.Id > instance.Id, "ID was not running");
conn.Dispose();
File.Delete(temp);
}
/// <summary>
/// Handles Insert/Update Request
/// </summary>
/// <param name="action">The action.</param>
/// <returns></returns>
protected dynamic HandleInsertUpdateRequest(NancyBlackDatabase db, dynamic arg)
{
var entityName = (string)arg.table_name;
if (arg.body == null)
{
return(400);
}
if (this.SiteDatabase.DataType.FromName(entityName) == null)
{
if (this.Request.Url.HostName != "localhost")
{
return(403);
}
// enable all access for automatically created table
TableSecModule.SetTableSec(this.Context, entityName, true, true, true, true);
}
dynamic fromClient = arg.body.Value as JObject;
int? id = fromClient.id;
if (id == null)
{
id = fromClient.Id;
}
if (id == null || id == 0)
{
TableSecModule.ThrowIfNoPermission(this.Context, entityName, TableSecModule.PERMISSON_CREATE);
}
else
{
TableSecModule.ThrowIfNoPermission(this.Context, entityName, TableSecModule.PERMISSON_UPDATE);
}
// special treatment for IContent
if (typeof(IContent).IsAssignableFrom(db.DataType.FromName(entityName).GetCompiledType()))
{
if (id == null || id == 0)
{
fromClient.CreatedBy = this.CurrentUser.Id;
}
else
{
fromClient.UpdatedBy = this.CurrentUser.Id;
}
}
dynamic record = db.UpsertRecord(entityName, fromClient);
return(this.Negotiate
.WithContentType("application/json")
.WithModel((object)record));
}
/// <summary>
/// Registers
/// </summary>
/// <param name="db"></param>
/// <param name="registerParameters"></param>
/// <returns></returns>
public NcbUser Register(NancyBlackDatabase db, string email, string passwordHash, bool genCode = false, bool returnExisting = false, dynamic initialProfile = null)
{
var existing = db.Query <NcbUser>()
.Where(u => u.Email == email)
.FirstOrDefault();
if (existing != null)
{
if (returnExisting == true)
{
// Update the profile
if (initialProfile != null)
{
existing.Profile = initialProfile;
db.UpsertRecord(existing);
}
return(existing);
}
throw new InvalidOperationException("Email already in use");
}
var user = new NcbUser();
user.Email = email;
user.PasswordHash = passwordHash;
user.Guid = Guid.NewGuid();
user.Profile = initialProfile;
if (genCode == true)
{
user.Code = Guid.NewGuid().ToString();
user.CodeRequestDate = DateTime.Now;
}
db.UpsertRecord(user);
user.PasswordHash = null;
return(user);
}
/// <summary>
/// Upate Referer code of given user
/// </summary>
/// <param name="db"></param>
/// <param name="userId"></param>
/// <param name="refererCode"></param>
/// <returns></returns>
public static AffiliateRegistration UpdateReferer(NancyBlackDatabase db, int userId, string refererCode)
{
var registration = AffiliateModule.ApplyAffiliate(db, userId, refererCode);
if (registration.RefererAffiliateCode == null)
{
registration.RefererAffiliateCode = refererCode;
db.UpsertRecord(registration);
}
return(registration);
}
public void Static_InsertSpeed()
{
var temp = Path.GetTempFileName();
SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(temp, true);
NancyBlackDatabase db = new NancyBlackDatabase(conn);
for (int i = 0; i < 1000; i++)
{
db.UpsertRecord(new TestClass());
}
conn.Dispose();
File.Delete(temp);
}
/// <summary>
/// Store value to database, only dirty values are stored
/// </summary>
/// <param name="db"></param>
public void Persist(NancyBlackDatabase db)
{
lock (this)
{
db.Transaction(() =>
{
foreach (var key in _DirtyList)
{
db.UpsertRecord(_Variables[key]);
}
});
_DirtyList = new HashSet <string>();
}
}
public void Static_Delete()
{
var temp = Path.GetTempFileName();
SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(temp, true);
NancyBlackDatabase db = new NancyBlackDatabase(conn);
var instance = db.UpsertRecord(new TestClass());
db.DeleteRecord(instance);
var output = db.GetById<TestClass>(instance.Id);
Assert.IsTrue(output == null, "DELETE was not success");
conn.Dispose();
File.Delete(temp);
}
public void Static_Query()
{
var temp = Path.GetTempFileName();
SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(temp, true);
NancyBlackDatabase db = new NancyBlackDatabase(conn);
var instance = new TestClass();
instance.Name = "Test Query";
db.UpsertRecord(instance);
var check = db.Query<TestClass>().Where(i => i.Name == "Test Query").FirstOrDefault();
Assert.IsTrue(check.Name == "Test Query", "Query got wrong item");
conn.Dispose();
File.Delete(temp);
}
public static void SetPackingStatus(NancyBlackDatabase db, SaleOrder so)
{
// deduct stock of all product
// first, we group the product id to minimize selects
db.Transaction(() =>
{
var products = from item in so.Items
group item by item into g
select g;
foreach (var productIdGroup in products)
{
var product = db.GetById <Product>(productIdGroup.Key);
product.Stock = product.Stock - productIdGroup.Count();
db.UpsertRecord <Product>(product);
}
});
}
public void AddItem(NancyBlackDatabase db, dynamic currentSite, int itemId)
{
var list = this.Items.ToList();
list.Add(itemId);
this.Items = list.ToArray();
var newItem = db.GetById <Product>(itemId);
var existItem = this.ItemsDetail.Where(p => p.Id == itemId && p.Title == newItem.Title && p.Price == newItem.Price).FirstOrDefault();
if (existItem == null)
{
JObject attr = newItem.Attributes;
if (attr == null)
{
attr = new JObject();
newItem.Attributes = attr;
}
attr["Qty"] = 1;
newItem.ContentParts = null;
this.ItemsDetail.Add(newItem);
}
else
{
JObject attr = existItem.Attributes;
attr["Qty"] = attr.Value <int>("Qty") + 1;
}
if (newItem.IsPromotionPrice)
{
var discount = this.ItemsDetail.Where(p => p.Url == "/dummy/dummy").FirstOrDefault();
if (discount != null)
{
discount.Price += newItem.CurrentPrice - newItem.Price;
}
}
this.TotalAmount = this.TotalAmount - (this.ShippingFee + this.ShippingInsuranceFee + this.PaymentFee);
this.TotalAmount += newItem.CurrentPrice;
this.SetAllFee(currentSite);
this.TotalAmount += this.ShippingFee + this.ShippingInsuranceFee + this.PaymentFee;
db.UpsertRecord <SaleOrder>(this);
}
/// <summary>
/// Creates the click tracking from current context
/// </summary>
public static void CreateFromContext(NancyContext ctx, NancyBlackDatabase db, AffiliateRegistration reg)
{
var owner = db.GetById <NcbUser>(reg.NcbUserId);
var record = new AffiliateShareClick();
record.UserId = ctx.Items["userid"] as string;
// wont track own click
if (record.UserId == owner.Guid.ToString())
{
return;
}
record.IPAddress = ctx.Request.UserHostAddress;
record.Url = ctx.Request.Url.Path + "?" + ctx.Request.Url.Query;
record.AffiliateRegistrationId = reg.Id;
db.UpsertRecord(record);
}
/// <summary>
/// Creates a new Page
/// </summary>
/// <param name="url"></param>
/// <param name="layout"></param>
/// <returns></returns>
public static dynamic CreatePage(NancyBlackDatabase db, string url, string layout = "", string requiredClaims = "", int displayOrder = 0)
{
if (url.StartsWith("/") == false)
{
url = "/" + url;
}
// try to find matching view that has same name as url
var layoutFile = Path.Combine(_RootPath, "Site", "Views", url.Substring(1).Replace('/', '\\') + ".cshtml");
if (File.Exists(layoutFile))
{
layout = url.Substring(1);
}
if (layout == "")
{
layout = "content";
}
// if URL is "/" generate home instead
if (url == "/")
{
layout = "home";
}
if (url.StartsWith("/") == false)
{
url = "/" + url;
}
var createdContent = db.UpsertRecord <Page>(new Page()
{
Id = 0,
Title = Path.GetFileName(url),
Url = url.ToLowerInvariant(),
Layout = layout,
RequiredClaims = requiredClaims,
DisplayOrder = displayOrder
});
return(createdContent);
}
/// <summary>
/// Set Receipt Index
/// </summary>
/// <param name="db"></param>
/// <param name="month"></param>
public static void SetReceiptIdentifier(NancyBlackDatabase db, DateTime month)
{
db.Transaction(() =>
{
// now, find all payment log of this month
var startOfMonth = new DateTime(month.Year, month.Month, 1, 0, 0, 0, DateTimeKind.Utc);
// this is temporary fix for receipt number to be in order as they created
// because datetime in db are store in utc tick and we cannot use .ToLocalTime() in db.Query().Where() as it unsuported
// so we use startOfMonth - thaiTimeZone (7 hours) instead
var thaiTimeZone = TimeZoneInfo.FindSystemTimeZoneById("SE Asia Standard Time");
startOfMonth = startOfMonth.AddTicks(thaiTimeZone.BaseUtcOffset.Ticks * -1);
var endOfMonth = startOfMonth.AddMonths(1).AddMilliseconds(-1);
var paymentsThisMonth = db.Query <PaymentLog>()
.Where(l => l.__createdAt >= startOfMonth && l.__createdAt <= endOfMonth)
.OrderBy(l => l.Id).ToList();
int counter = 1;
foreach (var l in paymentsThisMonth)
{
var receipt = db.Query <Receipt>().Where(r => r.PaymentLogId == l.Id).FirstOrDefault();
if (receipt == null)
{
// payment is not successful - so no receipt
}
else
{
if (string.IsNullOrWhiteSpace(receipt.Identifier))
{
// our company is in Thailand so, we only publish doc in Thailand Time
var receiptPublishedDate = l.__createdAt.ToUniversalTime().Add(thaiTimeZone.BaseUtcOffset);
receipt.Identifier = receiptPublishedDate.ToString("RCyyyyMM-", System.Globalization.CultureInfo.InvariantCulture) + string.Format("{0:0000}", counter);
db.UpsertRecord(receipt);
}
counter++;
}
}
});
}
public static AffiliateRegistration ApplyAffiliate(NancyBlackDatabase db, int userId, string refererCode = null)
{
AffiliateRegistration reg = null;
// whether user already registered
var existing = db.Query <AffiliateRegistration>()
.Where(r => r.NcbUserId == userId)
.FirstOrDefault();
// dont replace existing code
if (existing == null)
{
reg = new AffiliateRegistration();
reg.NcbUserId = userId;
reg.Commission = 0.01M; // start at 1 percent
// automatic code
var bytes = Encoding.ASCII.GetBytes(userId.ToString());
reg.AffiliateCode = Crc32.ComputeChecksumString(bytes);
var user = db.GetById <NcbUser>(userId);
if (user.Profile != null && user.Profile.first_name != null)
{
reg.AffiliateName = user.Profile.first_name;
}
if (reg.AffiliateName == null)
{
reg.AffiliateName = "SQUAD51#" + userId;
}
reg.RefererAffiliateCode = refererCode;
db.UpsertRecord(reg);
return(reg);
}
else
{
return(existing);
}
}
/// <summary>
///
/// </summary>
/// <param name="context"></param>
/// <param name="user"></param>
/// <param name="newProfile"></param>
public void UpdateProfile(NancyBlackDatabase siteDb, int userId, dynamic newProfile)
{
var user = siteDb.GetById <NcbUser>(userId);
user.Profile = newProfile;
if (user.Profile != null)
{
siteDb.UpsertRecord <NcbUser>(user);
// refresh the cache after update
var key = "User-" + user.Guid;
MemoryCache.Default.Remove(key);
MemoryCache.Default.Add(key, user,
new CacheItemPolicy()
{
SlidingExpiration = TimeSpan.FromMinutes(15)
});
}
}
/// <summary>
/// Upate Referer code of given user
/// </summary>
/// <param name="db"></param>
/// <param name="userId"></param>
/// <param name="refererCode"></param>
/// <returns></returns>
public static AffiliateRegistration UpdateReferer(NancyBlackDatabase db, int userId, string refererCode)
{
var registration = AffiliateModule.ApplyAffiliate(db, userId, refererCode);
// can only change referer if does not already have one
// NOTE: already tried allowing referer to change - this cause
// problem with cycle and also possible fraud attempt
// also - if we allow referer to change the number of
// downline will be limited and also referer can 'steal'
// downline from other referer.
if (registration.RefererAffiliateCode == null &&
registration.AffiliateCode != refererCode)
{
registration.RefererAffiliateCode = refererCode;
db.UpsertRecord(registration);
}
return(registration);
}
/// <summary>
/// Registers
/// </summary>
/// <param name="db"></param>
/// <param name="registerParameters"></param>
/// <returns></returns>
public NcbUser Reset(NancyBlackDatabase db, string email, string passwordHash, string code)
{
var existing = db.Query <NcbUser>()
.Where(u => u.Email == email)
.FirstOrDefault();
if (existing == null)
{
throw new InvalidOperationException("Not a valid user");
}
if (existing.Code != code)
{
throw new InvalidOperationException("Invalid Code");
}
existing.PasswordHash = passwordHash;
db.UpsertRecord <NcbUser>(existing);
return(existing);
}
private void InsertTag(NancyBlackDatabase db, IContent content)
{
if (string.IsNullOrEmpty(content.Tags))
{
return;
}
var tags = content.Tags.Split(',').Distinct();
foreach (var tag in tags)
{
db.UpsertRecord <Tag>(
new Tag()
{
Name = tag,
Type = content.GetType().Name,
ContentId = content.Id,
Url = GetCategoryPath(content)
});
}
}
/// <summary>
/// Refresh access token for given user
/// </summary>
public static void RefreshTokenIfRequired(NancyBlackDatabase db, dynamic siteSettings, NcbUser user)
{
if (user.GoogleOAuthToken == null ||
user.GoogleOAuthToken.refresh_token == null)
{
throw new ArgumentException("User was never authenticated with google or does not have refresh_token");
}
// No need to refresh token
if (((DateTime)user.GoogleOAuthToken.Expiry).Subtract(DateTime.Now).TotalMinutes > 2)
{
return;
}
// Gets the token
{
var client = new RestClient("https://www.googleapis.com/");
var req = new RestRequest("/oauth2/v4/token");
req.Method = Method.POST;
req.AddParameter("client_id", siteSettings.google.ClientID);
req.AddParameter("client_secret", siteSettings.google.ClientSecret);
req.AddParameter("refresh_token", user.GoogleOAuthToken.refresh_token);
req.AddParameter("grant_type", "refresh_token");
var response = client.Execute(req);
if (response.StatusCode != System.Net.HttpStatusCode.OK)
{
throw new InvalidOperationException(response.Content);
}
dynamic result = JObject.Parse(response.Content);
user.GoogleOAuthToken.access_token = result.access_token;
user.GoogleOAuthToken.expires_in = result.expires_in;
user.GoogleOAuthToken.Expiry = DateTime.Now.AddSeconds((int)user.GoogleOAuthToken.expires_in);
db.UpsertRecord(user);
}
}
/// <summary>
/// Find the role by Name, roles are cached for 5 minutes
/// </summary>
/// <param name="siteDb"></param>
/// <param name="id"></param>
/// <returns></returns>
private NcbRole GetRoleByName(NancyBlackDatabase siteDb, string name)
{
var roles = MemoryCache.Default["Membership-RolesByName"] as Dictionary <string, NcbRole>;
if (roles == null)
{
roles = siteDb.Query <NcbRole>().ToDictionary(r => r.Name.ToLowerInvariant());
MemoryCache.Default.Add("Membership-RolesByName", roles, DateTimeOffset.Now.AddMinutes(5));
}
name = name.ToLowerInvariant();
NcbRole role;
if (roles.TryGetValue(name, out role))
{
return(role);
}
// Make sure admin is available
if (name == "admin")
{
role = new NcbRole()
{
Claims = new string[] { "admin" },
Name = "admin"
};
siteDb.UpsertRecord(role);
MemoryCache.Default.Remove("Membership-RolesByName");
MemoryCache.Default.Remove("Membership-Roles");
return(role);
}
RefreshRoleInCache(siteDb);
throw new InvalidOperationException("Invalid Role Name: " + name);
}
/// <summary>
/// Creates a content
/// </summary>
/// <param name="url"></param>
/// <param name="layout"></param>
/// <returns></returns>
public static dynamic CreateContent(NancyBlackDatabase db, string url, string layout = "", string requiredClaims = "", int displayOrder = 0)
{
// try to find matching view that has same name as url
var layoutFile = Path.Combine(_RootPath, "Site", "Views", url.Replace('/', '\\') + ".cshtml");
if (File.Exists(layoutFile))
{
layout = url;
}
if (layout == "")
{
layout = "content";
}
// if URL is "/" generate home instead
if (url == "/")
{
layout = "home";
}
if (url.StartsWith("/") == false)
{
url = "/" + url;
}
var createdContent = db.UpsertRecord("Content", new DefaultContent()
{
Id = 0,
Url = url,
Layout = layout,
RequiredClaims = requiredClaims,
DisplayOrder = displayOrder
});
return createdContent;
}
internal static void ProcessInboundCompleted(NancyBlackDatabase db, InventoryInbound inbound, List <InventoryItem> items)
{
// this already in transaction
// When inventory inbound is created, record into GL about current asset
{
var supplierLookup = db.Query <Supplier>().ToDictionary(s => s.Id);
// Inbound will create 2 entries
// 1) inventory increase and account decrease (without tax amount)
AccountingEntry entry1 = new AccountingEntry();
entry1.TransactionDate = inbound.PaymentDate;
entry1.TransactionType = "buy";
entry1.DebtorLoanerName = supplierLookup[inbound.SupplierId].Name;
entry1.IncreaseAccount = "Inventory";
entry1.IncreaseAmount = inbound.TotalAmountWithoutTax;
entry1.DecreaseAccount = inbound.PaymentAccount;
entry1.DecreaseAmount = inbound.TotalAmountWithoutTax * -1;
entry1.InventoryInboundId = inbound.Id;
db.UpsertRecord(entry1);
// 2) paid tax increase and account decrease (tax only amount)
// (ภาษีซื้อทำให้ภาษีขายที่ต้องจ่ายลดลง)
if (inbound.TotalTax > 0)
{
AccountingEntry entry2 = new AccountingEntry();
entry2.TransactionDate = inbound.PaymentDate;
entry2.TransactionType = "expense";
entry2.DebtorLoanerName = "Tax";
entry2.IncreaseAccount = "Paid Tax";
entry2.IncreaseAmount = inbound.TotalTax;
entry2.DecreaseAccount = inbound.PaymentAccount;
entry2.DecreaseAmount = inbound.TotalTax * -1;
entry2.InventoryInboundId = inbound.Id;
db.UpsertRecord(entry2);
}
}
// record that inventory was withdrawn
{
var allFullfilled = from item in items
where item.IsFullfilled == true
select item;
if (allFullfilled.Count() > 0)
{
// the inventory is withdrawn as expense
AccountingEntry entry1 = new AccountingEntry();
entry1.TransactionDate = inbound.PaymentDate;
entry1.TransactionType = "expense";
entry1.DebtorLoanerName = "Inventory Used";
entry1.DecreaseAccount = "Inventory";
entry1.DecreaseAmount = allFullfilled.Sum(item => item.BuyingCost) * -1;
entry1.Notes = "Inventory Used by Sale Order: " + string.Join(",", allFullfilled.Select(item => item.SaleOrderId)) +
"From Inbound Id:" + inbound.Id;
db.UpsertRecord(entry1);
// if there is net profit/loss - record it
// but does not remove the amount from account
var totalAmountBuy = allFullfilled.Sum(i => i.BuyingCost);
var totalAmountSold = allFullfilled.Sum(i => i.SellingPrice);
if (totalAmountBuy != totalAmountSold)
{
AccountingEntry entry2 = new AccountingEntry();
entry2.TransactionDate = inbound.PaymentDate;
entry2.TransactionType = "income";
entry2.DebtorLoanerName = "n/a";
entry2.IncreaseAccount = "Gross Profit";
entry2.IncreaseAmount = totalAmountSold - totalAmountBuy;
entry2.Notes = "From Inbound Id:" + inbound.Id + " the item were used. Profit/Loss is calculated and recorded into Profit(Loss) account for each account";
db.UpsertRecord(entry2);
}
}
}
}
/// <summary>
/// Find the role by Name, roles are cached for 5 minutes
/// </summary>
/// <param name="siteDb"></param>
/// <param name="id"></param>
/// <returns></returns>
private NcbRole GetRoleByName(NancyBlackDatabase siteDb, string name)
{
var roles = MemoryCache.Default["Membership-RolesByName"] as Dictionary<string, NcbRole>;
if (roles == null)
{
roles = siteDb.Query<NcbRole>().ToDictionary(r => r.Name.ToLowerInvariant());
MemoryCache.Default.Add("Membership-RolesByName", roles, DateTimeOffset.Now.AddMinutes(5));
}
name = name.ToLowerInvariant();
NcbRole role;
if (roles.TryGetValue(name, out role))
{
return role;
}
// Make sure admin is available
if (name == "admin")
{
role = new NcbRole()
{
Claims = new string[] { "admin" },
Name = "admin"
};
siteDb.UpsertRecord( role );
MemoryCache.Default.Remove("Membership-RolesByName");
MemoryCache.Default.Remove("Membership-Roles");
return role;
}
throw new InvalidOperationException("Invalid Role Name: " + name );
}
internal static void ProcessReceiptCreation(NancyBlackDatabase db, Receipt obj)
{
// When payment receipt is created, create accounting entry
db.Transaction(() =>
{
var saleorder = db.GetById <SaleOrder>(obj.SaleOrderId);
var paymentlog = db.GetById <PaymentLog>(obj.PaymentLogId);
if (paymentlog.Amount <= 0)
{
return; // perhaps it is an error
}
if (saleorder == null || paymentlog == null)
{
// bogus receipt
throw new InvalidOperationException("Invalid Receipt was created");
}
var currentSite = saleorder.SiteSettings;
if (currentSite == null)
{
currentSite = AdminModule.ReadSiteSettings();
}
// Ensures all sale order logic has been ran
// if the sale order was created before new system change
if (saleorder.__createdAt < TaxSystemEpoch)
{
saleorder.UpdateSaleOrder(currentSite, db, false);
}
// Receipt will create 4 entries
// 1) PaymentSource account increases, with amount paid
// TODO: Mapping from PaymentSource to Account
AccountingEntry entry1 = new AccountingEntry();
entry1.TransactionDate = paymentlog.__createdAt;
entry1.TransactionType = "income";
entry1.DebtorLoanerName = "Customer";
entry1.IncreaseAccount = paymentlog.PaymentSource;
entry1.IncreaseAmount = paymentlog.Amount;
entry1.SaleOrderId = saleorder.Id;
db.UpsertRecord(entry1);
// 2) Sales Tax Calculation
{
AccountingEntry taxExtry = new AccountingEntry();
taxExtry.TransactionDate = paymentlog.__createdAt;
taxExtry.TransactionType = "taxcredit";
taxExtry.DebtorLoanerName = "Tax";
taxExtry.DecreaseAccount = "Tax Credit";
taxExtry.SaleOrderId = saleorder.Id;
if (currentSite.commerce.billing.vattype == "addvat")
{
var tax = paymentlog.Amount * ((100 + (Decimal)currentSite.commerce.billing.vatpercent) / 100);
taxExtry.DecreaseAmount = tax * -1;
}
if (currentSite.commerce.billing.vattype == "includevat")
{
var tax = paymentlog.Amount * ((Decimal)currentSite.commerce.billing.vatpercent / 100);
taxExtry.DecreaseAmount = tax * -1;
}
db.UpsertRecord(taxExtry);
}
// 3) Payment Fee
if (paymentlog.Fee > 0)
{
AccountingEntry feeEntry = new AccountingEntry();
feeEntry.TransactionDate = paymentlog.__createdAt;
feeEntry.TransactionType = "buy";
feeEntry.DebtorLoanerName = paymentlog.PaymentSource;
feeEntry.IncreaseAccount = "Payment Fee - " + paymentlog.PaymentSource;
feeEntry.IncreaseAmount = paymentlog.Fee;
feeEntry.SaleOrderId = saleorder.Id;
db.UpsertRecord(feeEntry);
}
// 4) Receivable from the Sale Order
{
// existing receivable of this sale order
var existingReceivable = db.Query <AccountingEntry>().Where(e => e.SaleOrderId == saleorder.Id && e.IncreaseAccount == "Receivable").ToList();
// see if we have any receivable of this sale order
// if we had, we have to deduct it
if (existingReceivable.Count > 0)
{
AccountingEntry deductReceivableEntry = new AccountingEntry();
deductReceivableEntry.TransactionDate = paymentlog.__createdAt;
deductReceivableEntry.TransactionType = "arpayment";
deductReceivableEntry.DebtorLoanerName = "Receivable From Sales";
deductReceivableEntry.DecreaseAccount = "Receivable";
deductReceivableEntry.DecreaseAmount = paymentlog.Amount;
deductReceivableEntry.SaleOrderId = saleorder.Id;
db.UpsertRecord(deductReceivableEntry);
}
else
{
// this maybe the first payment, see if all amount has been paid
// see all payment log of this sale order
// we only query payments up to currently processing payment log
// so that when we re
var payments = db.Query <PaymentLog>().Where(l => l.SaleOrderId == saleorder.Id && l.Id <= paymentlog.Id).ToList();
var remaining = saleorder.TotalAmount - payments.Sum(p => p.Amount);
if (remaining > 0)
{
// this is partial payment - create new receivable
AccountingEntry receivableEntry = new AccountingEntry();
receivableEntry.TransactionDate = paymentlog.__createdAt;
receivableEntry.TransactionType = "newaccount";
receivableEntry.DebtorLoanerName = "Receivable From Sales";
receivableEntry.IncreaseAccount = "Receivable";
receivableEntry.IncreaseAmount = remaining;
receivableEntry.SaleOrderId = saleorder.Id;
db.UpsertRecord(receivableEntry);
}
// this is full payment in one go, no need for receivable
}
}
});
}
public void Static_Update()
{
var temp = Path.GetTempFileName();
SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(temp, true);
NancyBlackDatabase db = new NancyBlackDatabase(conn);
var instance = db.UpsertRecord(new TestClass());
instance.Name = "Test Update";
db.UpsertRecord(instance);
var check = db.GetById<TestClass>(instance.Id);
Assert.IsTrue(check.Name == "Test Update", "UPDATE was not success");
conn.Dispose();
File.Delete(temp);
}
/// <summary>
/// Registers
/// </summary>
/// <param name="db"></param>
/// <param name="registerParameters"></param>
/// <returns></returns>
public NcbUser Register(NancyBlackDatabase db, string userName, string email, string passwordHash, bool genCode = false, bool returnExisting = false, dynamic initialProfile = null, Guid?existingGuid = null)
{
var existing = db.Query <NcbUser>()
.Where(u => u.UserName == userName)
.FirstOrDefault();
if (existing != null)
{
if (returnExisting == true)
{
// try to get email if user specified one in facebook
if (initialProfile != null)
{
bool save = false;
// this will allow admin to add Email to User and have the profile updated
if (existing.Email != null && initialProfile.email == null && existing.Email.StartsWith("fb_") == false)
{
existing.Profile.email = existing.Email;
save = true;
}
// if user has set the email, we extract the email into email field
if (initialProfile.email != null && existing.Email.StartsWith("fb_"))
{
existing.Email = initialProfile.email;
save = true;
}
if (save)
{
db.UpsertRecord(existing);
}
}
return(existing);
}
throw new InvalidOperationException("Email already in use");
}
var user = new NcbUser();
user.UserName = userName;
user.Email = email;
user.PasswordHash = passwordHash;
user.Profile = initialProfile;
if (existingGuid != null)
{
user.Guid = existingGuid.Value;
}
else
{
user.Guid = Guid.NewGuid();
}
if (genCode == true)
{
user.Code = Guid.NewGuid().ToString();
user.CodeRequestDate = DateTime.Now;
}
// if user is facebook user, keep the id from profile too
if (user.UserName.StartsWith("fb_") && user.Profile != null)
{
user.FacebookAppScopedId = user.Profile.id;
}
db.UpsertRecord(user);
user.PasswordHash = null;
return(user);
}
/// <summary>
/// Claim the rewards
/// </summary>
/// <param name="db"></param>
/// <param name="rewardsId"></param>
/// <param name="registrationId"></param>
/// <returns></returns>
public static AffiliateRewardsClaim ClaimReward(NancyBlackDatabase db, int rewardsId, int registrationId)
{
var canClaim = AffiliateReward.CanClaim(db, rewardsId, registrationId);
if (canClaim == false)
{
return(null);
}
AffiliateReward rewards;
var reg = db.GetById <AffiliateRegistration>(registrationId);
rewards = db.GetById <AffiliateReward>(rewardsId);
if (rewards.MaxPerUser > 0)
{
lock (BaseModule.GetLockObject("RewardClaim-Reg-" + registrationId))
{
var totalClaimedByUser = db.Query <AffiliateRewardsClaim>()
.Where(c => c.AffiliateRewardsId == rewards.Id &&
c.AffiliateRegistrationId == registrationId).Count();
if (totalClaimedByUser >= rewards.MaxPerUser)
{
return(null);
}
}
}
if (rewards.TotalQuota > 0)
{
lock (BaseModule.GetLockObject("RewardClaim-" + rewardsId))
{
var totalClaimed = db.Query <AffiliateRewardsClaim>().Where(c => c.AffiliateRewardsId == rewards.Id).Count();
rewards.RemainingQuota = rewards.TotalQuota - totalClaimed;
db.UpsertRecord(rewards);
}
}
if (rewards.IsRewardsClaimable == false)
{
return(null);
}
if (rewards.IsCodeDiscount || rewards.IsFreeGiftInSaleOrder)
{
var until = DateTime.MaxValue.Ticks;
if (rewards.CodeDicountExpiryInDays != null)
{
until = DateTime.Now.AddDays(rewards.CodeDicountExpiryInDays.Value).Ticks;
}
if (rewards.CodeDiscountExpiryDate != null)
{
until = rewards.CodeDiscountExpiryDate.Value.Ticks;
}
AffiliateRewardsClaim claim = null;
db.Transaction(() =>
{
// free gift also gets created as code
Product p = new Product();
if (rewards.IsCodeDiscount)
{
p.Price = rewards.CodeDiscountAmount * -1;
p.Attributes = new
{
rewardId = rewards.Id,
description = rewards.Title + ", ราคาก่อนส่วนลดขั้นต่ำ: " + rewards.MinimumPurchaseAmount,
min = rewards.MinimumPurchaseAmount,
onetime = true,
until = until,
discount = rewards.CodeDiscountAmount,
affiliateName = reg.AffiliateName,
require = rewards.RequiredProductIds,
};
}
if (rewards.IsFreeGiftInSaleOrder)
{
p.DiscountPrice = 0;
p.Price = rewards.CodeDiscountAmount;
p.PromotionEndDate = new DateTime(until);
p.MasterProductId = rewards.RewardsProductId;
p.IsVariation = true;
p.Attributes = new
{
rewardId = rewards.Id,
description = rewards.Title + ", ราคาก่อนส่วนลดขั้นต่ำ: " + rewards.MinimumPurchaseAmount,
min = rewards.MinimumPurchaseAmount,
onetime = true,
until = until,
discount = rewards.CodeDiscountAmount,
isfreeproduct = 1,
affiliateName = reg.AffiliateName,
require = rewards.RequiredProductIds,
};
}
db.UpsertRecord(p);
var code = hashids.Encode(p.Id, reg.Id);
p.Url = "/promotions/code/" + code;
p.Title = "Affiliate Discount: " + code;
if (rewards.IsFreeGiftInSaleOrder)
{
p.Title = "GIFT ITEM:" + rewards.Title;
}
db.UpsertRecord(p);
claim = new AffiliateRewardsClaim();
claim.AffiliateRegistrationId = reg.Id;
claim.NcbUserId = reg.NcbUserId;
claim.AffiliateCode = reg.AffiliateCode;
claim.DiscountCode = code;
claim.RewardsName = rewards.Title;
claim.AffiliateRewardsId = rewards.Id;
claim.ProductId = p.Id;
claim.CouponAttributes = p.Attributes;
db.UpsertRecord(claim);
});
return(claim);
}
{
var claim = new AffiliateRewardsClaim();
claim.AffiliateRegistrationId = reg.Id;
claim.NcbUserId = reg.NcbUserId;
claim.AffiliateCode = reg.AffiliateCode;
claim.RewardsName = rewards.Title;
claim.AffiliateRewardsId = rewards.Id;
claim.ProductId = rewards.RewardsProductId;
db.UpsertRecord(claim);
return(claim);
}
}
public static void HandlePayment(NancyBlackDatabase db, PaymentLog log, DateTime paidWhen)
{
// ensure only one thread is processing this so
lock (BaseModule.GetLockObject(log.SaleOrderIdentifier))
{
// find the sale order
var so = db.Query <SaleOrder>()
.Where(row => row.SaleOrderIdentifier == log.SaleOrderIdentifier)
.FirstOrDefault();
bool isPaymentReceived = false;
JArray exceptions = new JArray();
if (so == null)
{
exceptions.Add(JObject.FromObject(new
{
type = "Wrong SO Number",
description = "Wrong SO Number"
}));
goto EndPayment;
}
log.SaleOrderId = so.Id;
log.PaymentDate = paidWhen;
// check duplicated payment log (sometime we got double request from PaySbuy)
if (log.PaymentSource == PaymentMethod.PaySbuy && !log.IsErrorCode)
{
var jsonStr = ((JObject)log.FormResponse).ToString();
var duplicatedRequests = db.QueryAsJObject("PaymentLog", "FormResponse eq '" + jsonStr + "'").ToList();
if (duplicatedRequests.Count > 0)
{
exceptions.Add(JObject.FromObject(new
{
type = "Duplicated Request",
description = string.Format(
"Duplicated with Id: {0}", duplicatedRequests.First().Value <int>("Id"))
}));
goto EndPayment;
}
}
// Wrong Payment Status
if (so.PaymentStatus == PaymentStatus.PaymentReceived)
{
so.IsDuplicatePayment = true;
exceptions.Add(JObject.FromObject(new
{
type = "Wrong Status",
description = string.Format(
"Current paymentlog status of SO is: {0}", PaymentStatus.DuplicatePayment)
}));
}
// Error code received
if (log.IsErrorCode)
{
so.PaymentStatus = PaymentStatus.WaitingForPayment;
exceptions.Add(JObject.FromObject(new
{
type = "Error Code",
description = "Error Code Received from Payment Processor: " + log.ResponseCode
}));
goto EndPayment;
}
// after this line will never be run until EndPayment when IsErrorCode == true
if (so.PaymentStatus != PaymentStatus.PaymentReceived && log.Amount != so.TotalAmount)
{
log.IsPaymentSuccess = true;
so.PaymentStatus = PaymentStatus.Deposit;
so.PaymentReceivedDate = DateTime.Now; // Need to use this to manage queue
exceptions.Add(JObject.FromObject(new
{
type = "Split Payment",
description = string.Format(
"Expects: {0} amount from SO, payment is {1}", so.TotalAmount, log.Amount)
}));
var paymentlogs = db.Query <PaymentLog>()
.Where(p => p.SaleOrderIdentifier == so.SaleOrderIdentifier);
var splitPaymentLogs = (from sPLog in paymentlogs
where sPLog.IsErrorCode == false
select sPLog).ToList();
isPaymentReceived = so.TotalAmount <= splitPaymentLogs.Sum(splog => splog.Amount) + log.Amount;
}
if (exceptions.Count == 0 || isPaymentReceived)
{
log.IsPaymentSuccess = true;
so.PaymentStatus = PaymentStatus.PaymentReceived;
so.PaymentReceivedDate = DateTime.Now;
}
EndPayment:
log.Exception = exceptions;
db.UpsertRecord <PaymentLog>(log);
CommerceModule.PaymentOccured(so, db);
if (log.IsPaymentSuccess)
{
// Set Receipt number
var rc = db.UpsertRecord <Receipt>(new Receipt()
{
SaleOrderId = so.Id, PaymentLogId = log.Id
});
rc.SetIdentifier();
db.UpsertRecord(rc);
CommerceModule.PaymentSuccess(so, db);
}
db.UpsertRecord <SaleOrder>(so);
// reset the one time code used
foreach (var item in so.ItemsDetail)
{
if (item.Url.StartsWith("/promotions/code"))
{
if (item.Attributes.onetime != null)
{
var product = db.GetById <Product>(item.Id);
product.Url = product.Url.Replace("/promotions/code", "/promotions/code/archive-onetime");
db.UpsertRecord(product);
}
}
}
// Automate change status to WaitingForOrder for add item to PO
if (exceptions.Count == 0 || isPaymentReceived)
{
if (so.Status == SaleOrderStatus.Confirmed)
{
so.Status = SaleOrderStatus.WaitingForOrder;
db.UpsertRecord <SaleOrder>(so);
}
}
}
}
public void RemoveRMAItem(NancyBlackDatabase db, RMAItem item)
{
db.DeleteRecord <RMAItem>(item);
db.UpsertRecord <RMA>(this);
}
/// <summary>
/// Registers
/// </summary>
/// <param name="db"></param>
/// <param name="registerParameters"></param>
/// <returns></returns>
public NcbUser Register( NancyBlackDatabase db, string email, string passwordHash )
{
var existing = db.Query<NcbUser>()
.Where(u => u.Email == email)
.FirstOrDefault();
if (existing != null)
{
throw new InvalidOperationException("Email already in use");
}
var user = new NcbUser();
user.Email = email;
user.PasswordHash = passwordHash;
user.Guid = Guid.NewGuid();
db.UpsertRecord(user);
user.PasswordHash = null;
return user;
}
/// <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);
}
