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