/// <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));
}
/// <summary>
/// Build Chart object from data
/// </summary>
Chart buildChart(IEnumerable <JObject> data)
{
Chart chart = new Chart();
Dataset dataset = null;
bool dateOrder = _x1.Names.DataName == "DocumentDate";
FieldValue x2 = null;
DateTime minDate = _dates.Active ? _dates.CurrentPeriod()[0] : DateTime.MaxValue;
DateTime maxDate = _dates.Active ? _dates.CurrentPeriod()[1] : DateTime.MinValue;
if (_x2 == null)
{
// Only 1 dataset - create it now
dataset = new Dataset()
{
label = "Total"
};
chart.datasets.Add(dataset);
}
foreach (JObject record in data)
{
if (dateOrder)
{
// Record min and max dates
DateTime date = record.AsDate("DocumentDate").Date;
if (date < minDate)
{
minDate = date;
}
if (date > maxDate)
{
maxDate = date;
}
}
FieldValue v;
if (_x2 != null)
{
// Multiple datasets - do we need a new one yet
v = _x2.ValueFor(record);
if (v != x2)
{
// Yes - create it
dataset = new Dataset()
{
label = v.ToString()
};
chart.datasets.Add(dataset);
x2 = v;
}
}
// x1 value
v = _x1.ValueFor(record);
// Accumulate figure for current record into dataset
dataset.AddValue(v, _y.Value(record));
}
// All values to display in chart
List <FieldValue> labels;
if (dateOrder)
{
// We require labels and values for every period between min and max dates
DateField d = (DateField)_x1;
Dictionary <int, decimal> investmentValues = new Dictionary <int, decimal>();
chart.Labels = new List <FieldValue>();
for (DateTime date = minDate; date <= maxDate; date = nextPeriod(date))
{
chart.Labels.Add(d.ValueFor(date));
}
}
else
{
if (_settings.SortByValue)
{
// We require the labels sorted by the value in the first dataset
chart.Labels = chart.datasets[0].Data.OrderByDescending(i => i.Value).Select(i => i.Key).ToList();
}
else
{
// The labels should be the union of all the distinct labels in the datasets, sorted appropriately
chart.Labels = chart.datasets.SelectMany(d => d.Data.Keys).OrderBy(k => k).Distinct().ToList();
}
}
// Build the data lists for each dataset
foreach (Dataset d in chart.datasets)
{
// 1 value for each label
d.data = chart.Labels.Select(l => d.Data.TryGetValue(l, out decimal val) ? val : 0).ToList();
}
// Label strings for Chart.js
chart.labels = new List <string>(chart.Labels.Select(l => l.ToString()));
return(chart);
}