示例#1
0
        /// <summary>
        /// Generic save for all P and L 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 profitAndLossSave(JObject json, string sign, int [] acctTypes)
        {
            initialiseReport(json);
            setSeries(new AccountTypeField(), new ChartField("AccountCode"), new AccountNameField(), new NameField());
            _fields.Add(new DecimalField("Amount", sign + "Amount AS Amount"));
            _fields.Add(new DecimalField("Vat", sign + "Vat AS Vat"));
            _fields.Add(new DecimalField("Gross", sign + "(Amount + Vat) AS Gross"));
            _settings.Y         = "Amount";
            _settings.X1        = "AccountName";
            _filters.Add(_dates = new Reports.DateFilter(Settings, "DocumentDate", Reports.DateRange.LastYear));
            _filters.Add(new Reports.RecordFilter("Account", "Journal.AccountId", selectAccounts(acctTypes)));
            _filters.Add(new Reports.RecordFilter("AccountType", "AccountTypeId", SelectAccountTypes().Where(t => acctTypes.Contains(t.AsInt("id")))));
            _filters.Add(new Reports.RecordFilter("DocumentType", "DocumentTypeId", SelectDocumentTypes()));
            _filters.Add(new Reports.RecordFilter("NameAddress", "NameAddressId", SelectNames()));
            _filters.Add(new Reports.DecimalFilter("JournalAmount", "Amount"));
            readSettings(json);
            string sql = buildSql(acctTypes, out NameList fields, out NameList sort);

            sql += "\r\nORDER BY " + sort;
            return(chartJson(json, buildChart(Database.Query(sql))));
        }
示例#2
0
        /// <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));
        }