/// <summary> /// Build standard sql and get field list and sort list /// </summary> string buildSql(int[] acctTypes, out NameList fields, out NameList sort) { sort = new NameList(n => n.SortName, _x2, _x1); sort.Add("DocumentDate"); fields = new NameList(n => n.FieldName, _x1, _x2); fields.AddRange(sort); NameList allFields = new NameList(fields, _y.GetNames().Select(n => n.FieldName)); // Build standard sql to use if there are no opening balances to consider return("SELECT " + allFields + @" 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), _dates.Active ? null : "DocumentDate < " + Database.Quote(Utils.Today.AddDays(1)))); }
public NameList GetMergedNameList() { List <NameList> nameLists = nameListRepository.GetAll().ToList(); if (nameLists.Count == 1) { return(nameLists[0]); } NameList mergedNameList = new NameList(); nameLists.ForEach(mergedNameList.AddRange); foreach (NameList nameList in nameLists) { mergedNameList.AddRange(nameList); } return(mergedNameList); }
/// <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)); }