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