public override void Default()
{
Record = new JObject().AddRange(
"schedule", DefaultScheduleListing(),
"bank", total(Database.Query("Account.*, SUM(Amount) AS Balance",
"WHERE AccountTypeId = " + (int)AcctType.Bank
+ " AND DocumentDate <= " + Database.Quote(Utils.Today)
+ " AND HideAccount != 1 GROUP BY idAccount ORDER BY AccountTypeId, AccountName",
"Account", "Journal", "Document"), "AccountName", "Balance"),
"creditcard", total(Database.Query("Account.*, SUM(Amount) AS Balance",
"WHERE AccountTypeId = " + (int)AcctType.CreditCard
+ " AND DocumentDate <= " + Database.Quote(Utils.Today)
+ " AND HideAccount != 1 GROUP BY idAccount ORDER BY AccountTypeId, AccountName",
"Account", "Journal", "Document"), "AccountName", "Balance"),
"asset", total(Database.Query("Account.*, SUM(Amount) AS Balance",
"WHERE AccountTypeId = " + (int)AcctType.OtherAsset
+ " AND DocumentDate <= " + Database.Quote(Utils.Today)
+ " AND HideAccount != 1 GROUP BY idAccount ORDER BY AccountTypeId, AccountName",
"Account", "Journal", "Document"), "AccountName", "Balance"),
"liability", total(Database.Query("Account.*, SUM(Amount) AS Balance",
"WHERE AccountTypeId = " + (int)AcctType.OtherLiability
+ " AND DocumentDate <= " + Database.Quote(Utils.Today)
+ " AND HideAccount != 1 GROUP BY idAccount ORDER BY AccountTypeId, AccountName",
"Account", "Journal", "Document"), "AccountName", "Balance"),
"investments", total(Database.Query(@"SELECT Account.*, Amount AS CashBalance, Value
FROM (SELECT AccountId, SUM(Amount) AS Amount FROM Journal GROUP BY AccountId) AS Balances
JOIN Account ON idAccount = Balances.AccountId
JOIN AccountType ON idAccountType = AccountTypeId
LEFT JOIN (" + Investments.AccountValue(Database, Utils.Today) + @") AS AccountValues ON AccountValues.ParentAccountId = Balances.AccountId
WHERE AccountTypeId = " + (int)AcctType.Investment + @"
AND (Amount <> 0 OR Value <> 0)
GROUP BY idAccount ORDER BY AccountName"), "Name", "CashBalance", "Value"),
"customer", total(Database.Query(@"SELECT NameAddress.*, Sum(Outstanding) AS Outstanding
FROM NameAddress
LEFT JOIN Journal ON NameAddressId = idNameAddress
AND AccountId = " + (int)Acct.SalesLedger + @"
WHERE Type='C'
AND Outstanding <> 0
GROUP BY idNameAddress
ORDER BY Name
"), "Name", "Outstanding"),
"supplier", total(Database.Query(@"SELECT NameAddress.*, Sum(Outstanding) AS Outstanding
FROM NameAddress
LEFT JOIN Journal ON NameAddressId = idNameAddress
AND AccountId = " + (int)Acct.PurchaseLedger + @"
WHERE Type='S'
AND Outstanding <> 0
GROUP BY idNameAddress
ORDER BY Name
"), "Name", "Outstanding")
);
}
/// <summary>
/// Generic save for all Balance sheet charts
/// </summary>
/// <param name="json">Report json</param>
/// <param name="sign">"-" or "" - sign to put in front of all amounts</param>
/// <param name="acctTypes">List of account types to include or filter from</param>
/// <returns>Chart data ready for javascript to display</returns>
object balancesSave(JObject json, string sign, int[] acctTypes)
{
initialiseReport(json);
setSeries(new AccountTypeField(), new ChartField("AccountCode"), new AccountNameField());
_fields.Add(new DecimalField("Amount", sign + "Amount AS Amount"));
_settings.Y = "Amount";
_settings.X1 = "AccountName";
_filters.Add(_dates = new Reports.DateFilter(Settings, "DocumentDate", Reports.DateRange.LastYear));
_filters.Add(new Reports.RecordFilter("Account", "idAccount", selectAccounts(acctTypes)));
_filters.Add(new Reports.RecordFilter("AccountType", "AccountTypeId", SelectAccountTypes().Where(t => acctTypes.Contains(t.AsInt("id")))));
readSettings(json);
string sql = buildSql(acctTypes, out NameList fields, out NameList sort);
if (_dates.Active)
{
// We are only covering some dates - need to add opening balances dated 1st day of 1st period
string periodStart = Database.Quote(_dates.CurrentPeriod()[0]);
// New field list with fixed DocumentDate
NameList otherFields = new NameList(fields);
otherFields[otherFields.IndexOf("DocumentDate")] = Database.Cast(periodStart, "DATETIME") + " AS DocumentDate";
// Y field will be sum of records to date
otherFields.AddRange(_y.GetNames().Select(y => "SUM(" + y.SortName + ") AS " + y.DataName));
// Need to group by other sort fields
string[] group = sort.Where(s => s != "DocumentDate").ToArray();
// Final sort will be on output fields of union, instead of input ones
sort = new NameList(n => n.DataName, _x2, _x1);
// Exclude date range (range will be all dates up to and excluding period start)
_dates.Apply = false;
string sql1 = "SELECT " + otherFields + @"
FROM Journal
LEFT JOIN Account ON Account.idAccount = Journal.AccountId
LEFT JOIN AccountType ON AccountType.idAccountType = Account.AccountTypeId
LEFT JOIN NameAddress ON NameAddress.idNameAddress = Journal.NameAddressId
LEFT JOIN Line ON Line.idLine = Journal.idJournal
LEFT JOIN Document ON Document.idDocument = Journal.DocumentId
LEFT JOIN DocumentType ON DocumentType.idDocumentType = Document.DocumentTypeId"
+ getFilterWhere("AccountTypeId " + Database.In(acctTypes),
"DocumentDate < " + periodStart);
_dates.Apply = true;
if (group.Length > 0)
{
sql1 += "\r\nGROUP BY " + string.Join(",", group);
}
sql = "SELECT * FROM (\r\nSELECT * FROM (" + sql1 + ") AS ob\r\nWHERE Amount <> 0\r\nUNION\r\n" + sql + ") AS result";
}
sql += "\r\nORDER BY " + sort;
// Set flag to accumulate output figures if in date order
Chart chart = buildChart(Database.Query(sql));
//#if false
Dataset dataset = chart.datasets[0];
if (_x1.Names.DataName == "DocumentDate")
{
// For balance sheet date order reports, each period's balance accumulates
foreach (Dataset d in chart.datasets)
{
for (int i = 1; i < d.data.Count; i++)
{
d.data[i] += d.data[i - 1];
}
}
// In date order - calculate any investment values for each date
DateTime maxDate = _dates.Active ? _dates.CurrentPeriod()[1] : Utils.Today;
foreach (FieldValue <DateTime, string> period in chart.Labels)
{
DateTime next = nextPeriod(period.Value1);
if (next > maxDate)
{
next = maxDate;
}
_dates.Apply = false;
string sqli = "SELECT AccountTypeId, AccountCode, AccountName, Value AS Amount, "
+ Database.Quote(period.Value1) + " AS DocumentDate FROM ("
+ Investments.AccountValue(Database, next) + @") AS AccountValues
JOIN Account ON idAccount = ParentAccountId
" + getFilterWhere();
_dates.Apply = true;
foreach (JObject investment in Database.Query(sqli))
{
FieldValue v;
if (_x2 != null)
{
v = _x2.ValueFor(investment);
dataset = chart.datasets.FirstOrDefault(ds => ds.label == v.ToString());
}
if (dataset != null)
{
// x1 value
v = _x1.ValueFor(investment);
// Add value of investment at period end into data
int index = chart.Labels.IndexOf(v);
if (index >= 0)
{
dataset.data[index] += _y.Value(investment);
}
else
{
System.Diagnostics.Debug.WriteLine("X1 value not found:" + v);
}
}
}
}
}
else
{
_dates.Apply = false;
string sqli = "SELECT AccountTypeId, AccountCode, AccountName, Value AS Amount FROM ("
+ Investments.AccountValue(Database, Utils.Today) + @") AS AccountValues
JOIN Account ON idAccount = ParentAccountId
" + getFilterWhere();
_dates.Apply = true;
foreach (JObject investment in Database.Query(sqli))
{
FieldValue v;
if (_x2 != null)
{
v = _x2.ValueFor(investment);
dataset = chart.datasets.FirstOrDefault(ds => ds.label == v.ToString());
}
if (dataset != null)
{
// x1 value
v = _x1.ValueFor(investment);
// Add current investment value into dataset
dataset.AddValue(v, _y.Value(investment));
}
}
}
return(chartJson(json, chart));
}