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