private static void NancyBlackDatabase_ObjectCreated(NancyBlackDatabase db, string table, dynamic obj) { if (table == "Receipt") { AccountingSystemModule.ProcessReceiptCreation(db, obj); } }
private static void NancyBlackDatabase_ObjectUpdated(NancyBlackDatabase db, string table, dynamic obj) { if (table == "SaleOrder") { AccountingSystemModule.HandleCreditRequest(db, obj); } }
private static void NancyBlackDatabase_ObjectCreated(NancyBlackDatabase db, string table, dynamic obj) { if (table == "Receipt") { AccountingSystemModule.ProcessReceiptCreation(db, JObject.FromObject(obj).ToObject <Receipt>()); } if (table == "AccountingEntry") { AccountingSystemModule.ProcesAutoRecurranceCreation(db, obj); } }
public AccountingSystemModule() { Get["/admin/tables/accountingentry"] = this.HandleViewRequest("/Admin/accountingsystem-gl", null); Get["/admin/accounts"] = this.HandleViewRequest("/Admin/accountingsystem-accounts", (arg) => { dynamic model = new System.Dynamic.ExpandoObject(); /// All Accounts { var baseSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(IncreaseAmount) as TotalIncrease, IncreaseAccount as Account, MAX(TransactionDate) as LastUpdated, 0 as DueDate FROM AccountingEntry WHERE IncreaseAccount IS NOT NULL GROUP BY IncreaseAccount" , new { TotalIncrease = 0M, TotalDecrease = 0M, Account = "", LastUpdated = default(DateTime), DueDate = default(DateTime) }).ToList(); var decreaseSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(DecreaseAmount) as TotalDecrease, DecreaseAccount, MAX(TransactionDate) as LastUpdated, MIN(DueDate) as DueDate FROM AccountingEntry WHERE DecreaseAccount IS NOT NULL GROUP BY DecreaseAccount" , new { TotalDecrease = 0M, DecreaseAccount = "", LastUpdated = default(DateTime), DueDate = default(DateTime) }).ToList(); var decreaseLookup = decreaseSummary.ToLookup(item => (string)item.DecreaseAccount); foreach (dynamic item in baseSummary) { dynamic decreaseRecord = decreaseLookup[(string)item.Account].FirstOrDefault(); if (decreaseRecord == null) { continue; } item.TotalDecrease = decreaseRecord.TotalDecrease; if ((DateTime)decreaseRecord.LastUpdated > (DateTime)item.LastUpdated) { item.LastUpdated = decreaseRecord.LastUpdated; } item.DueDate = decreaseRecord.DueDate; } model.Accounts = baseSummary; } { var payableExpected = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(IncreaseAmount) as Amount, 'Expected Expense' As DebtorLoanerName, 'Projection' As DocumentNumber, MAX(TransactionDate) as LatestDate FROM AccountingEntry_2017 WHERE IncreaseAccount = 'Expense' GROUP BY IncreaseAccount", new { Amount = 0M, DebtorLoanerName = "", DocumentNumber = "", LastUpdated = default(DateTime), DueDate = default(DateTime) }).FirstOrDefault(); var payableSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(DecreaseAmount) as Amount, DebtorLoanerName, MAX(TransactionDate) as LastUpdated, MIN(DueDate) as DueDate FROM AccountingEntry WHERE DecreaseAmount < 0 AND DecreaseAccount = 'Payable' GROUP BY DebtorLoanerName, DocumentNumber", new { Amount = 0M, DebtorLoanerName = "", DocumentNumber = "", LastUpdated = default(DateTime), DueDate = default(DateTime) }).ToList(); var payablePaidSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(IncreaseAmount) as Amount, DebtorLoanerName, MAX(TransactionDate) as LastUpdated, MAX(DueDate) as DueDate FROM AccountingEntry WHERE IncreaseAmount > 0 AND IncreaseAccount = 'Payable' GROUP BY DebtorLoanerName, DocumentNumber", new { Amount = 0M, DebtorLoanerName = "", DocumentNumber = "", LastUpdated = default(DateTime), DueDate = default(DateTime) }).ToLookup(item => (string)item.DebtorLoanerName + "-" + (string)item.DocumentNumber); foreach (var item in payableSummary) { var payback = payablePaidSummary[(string)item.DebtorLoanerName + "-" + (string)item.DocumentNumber]; foreach (var row in payback) { item.Amount += row.Amount; } } var final = payableSummary.Where(item => item.Amount < 0).ToList(); payableExpected.Amount = (payableExpected.Amount / 12) * (12 - DateTime.Now.Month) * -1; payableExpected.DueDate = new DateTime(DateTime.Now.Year, 12, 31); final.Add(payableExpected); model.PayableSummary = final; } { var assetSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT 'Stock On Hand' As Name, SUM(BuyingPrice) As TotalAmount, Max(__updatedAt) As LastUpdated FROM InventoryPurchase WHERE InventoryItemId = 0", new { Name = "", TotalAmount = 0M, LastUpdated = default(DateTime) }); var equipmentSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT 'Equipment' As Name, SUM(IncreaseAmount) As TotalAmount, Max(__updatedAt) As LastUpdated FROM AccountingEntry WHERE IncreaseAccount = 'Equipment'", new { Name = "", TotalAmount = 0M, LastUpdated = default(DateTime) }); var final = new List <dynamic>(); final.AddRange(assetSummary); final.AddRange(equipmentSummary); model.AssetSummary = final; } { var receivableSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(IncreaseAmount) as Amount, SUM(DecreaseAmount) as PaidAmount, DebtorLoanerName, MAX(TransactionDate) as LatestDate, SaleOrderId FROM AccountingEntry WHERE IncreaseAccount == 'Receivable' OR DecreaseAccount == 'Receivable' GROUP BY DebtorLoanerName, SaleOrderId", new { Amount = 0M, PaidAmount = 0M, DebtorLoanerName = "", LatestDate = default(DateTime), SaleOrderId = 0 }); var final = new List <dynamic>(); foreach (var item in receivableSummary) { if (item.Amount == item.PaidAmount) { continue; } final.Add(item); } model.ReceivableSummary = final; } if (this.CurrentSite.accounting == null) { var ja = new JArray(); foreach (var item in model.Accounts) { ja.Add(JObject.FromObject(new { Name = item.Account, Type = "" })); } this.CurrentSite.accounting = JObject.FromObject(new { accounts = ja }); } else { // Ensures that the settings page contains all accounts used var existingAccounts = (from dynamic acc in this.CurrentSite.accounting.accounts as JArray select acc).ToDictionary(a => (string)a.Name); foreach (dynamic account in model.Accounts) { if (existingAccounts.ContainsKey((string)account.Account) == false) { var array = this.CurrentSite.accounting.accounts as JArray; array.Add(JObject.FromObject(new { Name = (string)account.Account, Type = "" })); } } } return(new StandardModel(this, null, model)); }); Get["/admin/tables/accountingentry/__opendocuments"] = this.HandleRequest((arg) => { return(this.SiteDatabase.Query("SELECT DISTINCT DocumentNumber as Name FROM AccountingEntry WHERE IsDocumentClosed == false AND DocumentNumber <> Null", new { Name = "" }).Select(item => ((dynamic)item).Name as string).ToList()); }); Get["/admin/tables/accountingentry/__autocompletes"] = this.HandleRequest(this.GenerateAutoComplete); Get["/admin/tables/accountingentry/__accountsummary"] = this.HandleRequest((arg) => { var totalIncrease = this.SiteDatabase.Query( @"SELECT IncreaseAccount as Account, SUM(IncreaseAmount) as Amount, MAX(TransactionDate) as LatestDate FROM AccountingEntry WHERE IncreaseAccount IS NOT NULL GROUP BY IncreaseAccount", new { Account = "", Amount = 0M, LatestDate = DateTime.Now }); var totalDecrease = this.SiteDatabase.Query( @"SELECT DecreaseAccount as Account, SUM(DecreaseAmount) as Amount, MAX(TransactionDate) as LatestDate FROM AccountingEntry WHERE DecreaseAccount IS NOT NULL GROUP BY DecreaseAccount", new { Account = "", Amount = 0M, LatestDate = DateTime.Now }); return(new { TotalIncrease = totalIncrease, TotalDecrease = totalDecrease }); }); Get["/admin/tables/accountingentry/accounts/{accountname}"] = this.HandleRequest((arg) => { var summary = this.SiteDatabase.QueryAsDynamic( @" SELECT * FROM ( SELECT Id, TransactionDate, Transactiontype, DecreaseAccount as RelatedAccount, IncreaseAmount, 0 As DecreaseAmount, 0 As FinalAmount, Notes, ProjectName, DebtorLoanerName FROM AccountingEntry WHERE IncreaseAccount = ? UNION SELECT Id, TransactionDate, Transactiontype, IncreaseAccount as RelatedAccount, 0 As IncreaseAmount, DecreaseAmount, 0 As FinalAmount, Notes, ProjectName, DebtorLoanerName FROM AccountingEntry WHERE DecreaseAccount = ? ) ORDER BY TransactionDate ", new { TransactionDate = DateTime.MinValue, Transactiontype = "", RelatedAccount = "", IncreaseAmount = 0M, DecreaseAmount = 0M, FinalAmount = 0M, Notes = "", ProjectName = "", DebtorLoanerName = "" }, new object[] { (string)arg.accountname, (string)arg.accountname }).ToList(); if (summary.Count == 0) { return(summary); } summary[0].FinalAmount = summary[0].IncreaseAmount == 0 ? summary[0].DecreaseAmount : summary[0].IncreaseAmount; for (int i = 1; i < summary.Count - 1; i++) { summary[i].FinalAmount = summary[i - 1].FinalAmount + summary[i].IncreaseAmount + summary[i].DecreaseAmount; } return(summary); }); Get["/admin/tables/accountingentry/__replayreceipt"] = this.HandleRequest((arg) => { // replay receipt since 2018 only var date = new DateTime(636503616000000000); var receipt = this.SiteDatabase.Query <Receipt>().Where(r => r.__createdAt >= date).ToList(); // Delete all the affected accounting entries var query = this.SiteDatabase.Connection.Execute("DELETE FROM AccountingEntry WHERE __createdAt > 636503616000000000 AND SaleOrderId > 0"); foreach (var item in receipt) { AccountingSystemModule.ProcessReceiptCreation(this.SiteDatabase, item); } return("OK"); }); Get["/admin/gen-report/bigaccount-report"] = this.HandleRequest((arg) => { List <InComeMovement> movements = new List <InComeMovement>(); // all SO from Jan 2017 - Dec 2018 var tableQuery = this.SiteDatabase.Query <SaleOrder>() .Where(row => row.Status == "Delivered" && row.Id > 0); // extract product from saleOrder foreach (var so in tableQuery) { if (so.ItemsDetail == null) { continue; } foreach (var product in so.ItemsDetail) { InComeMovement movement = new InComeMovement() { Product = product, SaleOrderId = so.Id, MovementDate = so.PaymentReceivedDate, Price = product.CurrentPrice, }; if (product.Attributes == null) { movement.Quantity = 1; } else { movement.Quantity = product.Attributes.Qty; } if (movement.Price > 0 && movement.Quantity != 0 && movement.MovementDate != default(DateTime)) { movements.Add(movement); } } } var count1 = movements.Count(); var sum1 = movements.Sum(item => item.Price * item.Quantity); // group movement with same product id together and merge duplicated movement var groups = movements.GroupBy(movement => movement.Product.Id); foreach (var group in groups) { int starter = 0; List <int> duplicatedIndexes = new List <int>(); int groupLength = group.Count(); var list = group.ToList(); for (int i = starter; i < groupLength - 1; i++) { if (duplicatedIndexes.Contains(i)) { continue; } for (int j = i + 1; j < groupLength; j++) { if (duplicatedIndexes.Contains(j)) { continue; } if (list[i].IsEqualTo(list[j])) { list[i].Quantity += list[j].Quantity; duplicatedIndexes.Add(j); } } } // remove duplicated item from movements foreach (var index in duplicatedIndexes) { movements.Remove(list[index]); } } var count2 = movements.Count(); var sum2 = movements.Sum(item => item.Price * item.Quantity); var csv = new StringBuilder(); csv.AppendLine("SaleOrder Id,Date,Product,Price,Qualtity,LineTotal"); // ex. when title price qty lineTotal foreach (var movement in movements.OrderBy(item => item.MovementDate)) { var newLine = string.Join(",", movement.SaleOrderId, movement.MovementDate.ToShortDateString(), "\"" + movement.Product.Title + "\"", movement.Price, movement.Quantity, movement.Price * movement.Quantity); csv.AppendLine(newLine); } //after your loop File.WriteAllText("D:\\report-4bigaccount.csv", csv.ToString()); return(200); }); Get["/admin/gen-report/2017-2018"] = this.HandleRequest((arg) => { List <InComeMovement> movements = new List <InComeMovement>(); // all SO from Jan 2017 - Dec 2018 var tableQuery = this.SiteDatabase.Query <SaleOrder>() .Where(row => row.Id >= 115 && row.Id <= 1629 && row.Status == "Delivered"); var saleOrders = tableQuery.ToList() .Where(row => row.__createdAt >= new DateTime(2016, 1, 1) && row.PaymentReceivedDate < new DateTime(2019, 1, 1)); // extract product from saleOrder foreach (var so in saleOrders) { foreach (var product in so.ItemsDetail) { InComeMovement movement = new InComeMovement() { Product = product, MovementDate = so.PaymentReceivedDate, Price = product.CurrentPrice, Quantity = product.Attributes.Qty }; if (movement.Product.Title != "Discount" && movement.Price != 0 && movement.Quantity != 0) { movements.Add(movement); } } } var count1 = movements.Count(); var sum1 = movements.Sum(item => item.Price * item.Quantity); // group movement with same product id together and merge duplicated movement var groups = movements.GroupBy(movement => movement.Product.Id); foreach (var group in groups) { int starter = 0; List <int> duplicatedIndexes = new List <int>(); int groupLength = group.Count(); var list = group.ToList(); for (int i = starter; i < groupLength - 1; i++) { if (duplicatedIndexes.Contains(i)) { continue; } for (int j = i + 1; j < groupLength; j++) { if (duplicatedIndexes.Contains(j)) { continue; } if (list[i].IsEqualTo(list[j])) { list[i].Quantity += list[j].Quantity; duplicatedIndexes.Add(j); } } } // remove duplicated item from movements foreach (var index in duplicatedIndexes) { movements.Remove(list[index]); } } var count2 = movements.Count(); var sum2 = movements.Sum(item => item.Price * item.Quantity); movements.Sort(); var csv = new StringBuilder(); // ex. when title price qty lineTotal foreach (var movement in movements) { var newLine = string.Join(",", movement.MovementDate.ToShortDateString(), "\"" + movement.Product.Title + "\"", movement.Price, movement.Quantity, movement.Price * movement.Quantity); csv.AppendLine(newLine); } //after your loop File.WriteAllText("D:\\report-4bigaccount-2017-2018.csv", csv.ToString()); return(200); }); }
public AccountingSystemModule() { Get["/admin/tables/accountingentry"] = this.HandleViewRequest("/Admin/accountingsystem-gl", null); Get["/admin/accounts"] = this.HandleViewRequest("/Admin/accountingsystem-accounts", (arg) => { dynamic model = new System.Dynamic.ExpandoObject(); /// All Accounts { var baseSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(IncreaseAmount) as TotalIncrease, IncreaseAccount as Account, MAX(TransactionDate) as LastUpdated, 0 as DueDate FROM AccountingEntry WHERE IncreaseAccount IS NOT NULL GROUP BY IncreaseAccount" , new { TotalIncrease = 0M, TotalDecrease = 0M, Account = "", LastUpdated = default(DateTime), DueDate = default(DateTime) }).ToList(); var decreaseSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(DecreaseAmount) as TotalDecrease, DecreaseAccount, MAX(TransactionDate) as LastUpdated, MIN(DueDate) as DueDate FROM AccountingEntry WHERE DecreaseAccount IS NOT NULL GROUP BY DecreaseAccount" , new { TotalDecrease = 0M, DecreaseAccount = "", LastUpdated = default(DateTime), DueDate = default(DateTime) }).ToList(); var decreaseLookup = decreaseSummary.ToLookup(item => (string)item.DecreaseAccount); foreach (dynamic item in baseSummary) { dynamic decreaseRecord = decreaseLookup[(string)item.Account].FirstOrDefault(); if (decreaseRecord == null) { continue; } item.TotalDecrease = decreaseRecord.TotalDecrease; if ((DateTime)decreaseRecord.LastUpdated > (DateTime)item.LastUpdated) { item.LastUpdated = decreaseRecord.LastUpdated; } item.DueDate = decreaseRecord.DueDate; } model.Accounts = baseSummary; } { var payableExpected = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(IncreaseAmount) as Amount, 'Expected Expense' As DebtorLoanerName, 'Projection' As DocumentNumber, MAX(TransactionDate) as LatestDate FROM AccountingEntry_2017 WHERE IncreaseAccount = 'Expense' GROUP BY IncreaseAccount", new { Amount = 0M, DebtorLoanerName = "", DocumentNumber = "", LastUpdated = default(DateTime), DueDate = default(DateTime) }).FirstOrDefault(); var payableSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(DecreaseAmount) as Amount, DebtorLoanerName, MAX(TransactionDate) as LastUpdated, MIN(DueDate) as DueDate FROM AccountingEntry WHERE DecreaseAmount < 0 AND DecreaseAccount = 'Payable' GROUP BY DebtorLoanerName, DocumentNumber", new { Amount = 0M, DebtorLoanerName = "", DocumentNumber = "", LastUpdated = default(DateTime), DueDate = default(DateTime) }).ToList(); var payablePaidSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(IncreaseAmount) as Amount, DebtorLoanerName, MAX(TransactionDate) as LastUpdated, MAX(DueDate) as DueDate FROM AccountingEntry WHERE IncreaseAmount > 0 AND IncreaseAccount = 'Payable' GROUP BY DebtorLoanerName, DocumentNumber", new { Amount = 0M, DebtorLoanerName = "", DocumentNumber = "", LastUpdated = default(DateTime), DueDate = default(DateTime) }).ToLookup(item => (string)item.DebtorLoanerName + "-" + (string)item.DocumentNumber); foreach (var item in payableSummary) { var payback = payablePaidSummary[(string)item.DebtorLoanerName + "-" + (string)item.DocumentNumber]; foreach (var row in payback) { item.Amount += row.Amount; } } var final = payableSummary.Where(item => item.Amount < 0).ToList(); payableExpected.Amount = (payableExpected.Amount / 12) * (12 - DateTime.Now.Month) * -1; payableExpected.DueDate = new DateTime(DateTime.Now.Year, 12, 31); final.Add(payableExpected); model.PayableSummary = final; } { var assetSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT 'Stock On Hand' As Name, SUM(BuyingPrice) As TotalAmount, Max(__updatedAt) As LastUpdated FROM InventoryPurchase WHERE InventoryItemId = 0", new { Name = "", TotalAmount = 0M, LastUpdated = default(DateTime) }); var equipmentSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT 'Equipment' As Name, SUM(IncreaseAmount) As TotalAmount, Max(__updatedAt) As LastUpdated FROM AccountingEntry WHERE IncreaseAccount = 'Equipment'", new { Name = "", TotalAmount = 0M, LastUpdated = default(DateTime) }); var final = new List <dynamic>(); final.AddRange(assetSummary); final.AddRange(equipmentSummary); model.AssetSummary = final; } { var receivableSummary = this.SiteDatabase.QueryAsDynamic(@" SELECT SUM(IncreaseAmount) as Amount, SUM(DecreaseAmount) as PaidAmount, DebtorLoanerName, MAX(TransactionDate) as LatestDate, SaleOrderId FROM AccountingEntry WHERE IncreaseAccount == 'Receivable' OR DecreaseAccount == 'Receivable' GROUP BY DebtorLoanerName, SaleOrderId", new { Amount = 0M, PaidAmount = 0M, DebtorLoanerName = "", LatestDate = default(DateTime), SaleOrderId = 0 }); var final = new List <dynamic>(); foreach (var item in receivableSummary) { if (item.Amount == item.PaidAmount) { continue; } final.Add(item); } model.ReceivableSummary = final; } if (this.CurrentSite.accounting == null) { var ja = new JArray(); foreach (var item in model.Accounts) { ja.Add(JObject.FromObject(new { Name = item.Account, Type = "" })); } this.CurrentSite.accounting = JObject.FromObject(new { accounts = ja }); } else { // Ensures that the settings page contains all accounts used var existingAccounts = (from dynamic acc in this.CurrentSite.accounting.accounts as JArray select acc).ToDictionary(a => (string)a.Name); foreach (dynamic account in model.Accounts) { if (existingAccounts.ContainsKey((string)account.Account) == false) { var array = this.CurrentSite.accounting.accounts as JArray; array.Add(JObject.FromObject(new { Name = (string)account.Account, Type = "" })); } } } return(new StandardModel(this, null, model)); }); Get["/admin/tables/accountingentry/__opendocuments"] = this.HandleRequest((arg) => { return(this.SiteDatabase.Query("SELECT DISTINCT DocumentNumber as Name FROM AccountingEntry WHERE IsDocumentClosed == false AND DocumentNumber <> Null", new { Name = "" }).Select(item => ((dynamic)item).Name as string).ToList()); }); Get["/admin/tables/accountingentry/__autocompletes"] = this.HandleRequest(this.GenerateAutoComplete); Get["/admin/tables/accountingentry/__accountsummary"] = this.HandleRequest((arg) => { var totalIncrease = this.SiteDatabase.Query( @"SELECT IncreaseAccount as Account, SUM(IncreaseAmount) as Amount, MAX(TransactionDate) as LatestDate FROM AccountingEntry WHERE IncreaseAccount IS NOT NULL GROUP BY IncreaseAccount", new { Account = "", Amount = 0M, LatestDate = DateTime.Now }); var totalDecrease = this.SiteDatabase.Query( @"SELECT DecreaseAccount as Account, SUM(DecreaseAmount) as Amount, MAX(TransactionDate) as LatestDate FROM AccountingEntry WHERE DecreaseAccount IS NOT NULL GROUP BY DecreaseAccount", new { Account = "", Amount = 0M, LatestDate = DateTime.Now }); return(new { TotalIncrease = totalIncrease, TotalDecrease = totalDecrease }); }); Get["/admin/tables/accountingentry/accounts/{accountname}"] = this.HandleRequest((arg) => { var summary = this.SiteDatabase.QueryAsDynamic( @" SELECT * FROM ( SELECT Id, TransactionDate, Transactiontype, DecreaseAccount as RelatedAccount, IncreaseAmount, 0 As DecreaseAmount, 0 As FinalAmount, Notes, ProjectName, DebtorLoanerName FROM AccountingEntry WHERE IncreaseAccount = ? UNION SELECT Id, TransactionDate, Transactiontype, IncreaseAccount as RelatedAccount, 0 As IncreaseAmount, DecreaseAmount, 0 As FinalAmount, Notes, ProjectName, DebtorLoanerName FROM AccountingEntry WHERE DecreaseAccount = ? ) ORDER BY TransactionDate ", new { TransactionDate = DateTime.MinValue, Transactiontype = "", RelatedAccount = "", IncreaseAmount = 0M, DecreaseAmount = 0M, FinalAmount = 0M, Notes = "", ProjectName = "", DebtorLoanerName = "" }, new object[] { (string)arg.accountname, (string)arg.accountname }).ToList(); if (summary.Count == 0) { return(summary); } summary[0].FinalAmount = summary[0].IncreaseAmount == 0 ? summary[0].DecreaseAmount : summary[0].IncreaseAmount; for (int i = 1; i < summary.Count - 1; i++) { summary[i].FinalAmount = summary[i - 1].FinalAmount + summary[i].IncreaseAmount + summary[i].DecreaseAmount; } return(summary); }); Get["/admin/tables/accountingentry/__replayreceipt"] = this.HandleRequest((arg) => { // replay receipt since 2018 only var date = new DateTime(636503616000000000); var receipt = this.SiteDatabase.Query <Receipt>().Where(r => r.__createdAt >= date).ToList(); // Delete all the affected accounting entries var query = this.SiteDatabase.Connection.Execute("DELETE FROM AccountingEntry WHERE __createdAt > 636503616000000000 AND SaleOrderId > 0"); foreach (var item in receipt) { AccountingSystemModule.ProcessReceiptCreation(this.SiteDatabase, item); } return("OK"); }); }