C# IDatabase.Fetch方法代码示例

        private void getRolesHierarchyRecursive(IDatabase db, Role parentRole)
        {
            if (parentRole == null)
                return;


            parentRole.juniorRolesList = db.Fetch<Role>(
                                "SELECT Role.* FROM Role "
                                + "INNER JOIN RoleHierarchy ON Role.roleId = RoleHierarchy.juniorRoleId "
                                + "WHERE RoleHierarchy.roleId = @0;",
                                parentRole.RoleId
            );

            foreach (Role juniorRole in parentRole.juniorRolesList)
                getRolesHierarchyRecursive(db, juniorRole);

        }

        public TransactionsAllReport GetTransactionsAllReport(IDatabase db, int companyId, DateTime from, DateTime to)
        {
            var transactions = db.Fetch<TransactionView>(@"
            select *
            from transactionview v
            inner join runningbalance rb on rb.transactionid = v.transactionid
            where  v.companyid = @0", companyId).OrderBy(x => x.Account).ThenBy(y => y.TransactionDate).ThenBy(z => z.TransactionId).ToList();

            var report = new TransactionsAllReport(from, to);

            var account = "";
            foreach (var t in transactions)
            {
                var currentBalance = 0m;
                var balDispaly = (account != t.AccountNumber) ? (t.Balance + t.Amount).ToString("C") : "";
                account = t.AccountNumber;

                report.TransactionReportItems.Add(new TransactionReportItem
                {
                    BankAccount = t.AccountNumber,
                    Amount = t.Amount,
                    BalanceAfter = t.Balance,
                    OpeningBalance = t.Balance + t.Amount,
                    OpeningBalanceDisplay = balDispaly,
                    Category = t.Category,
                    TransactionDate = t.TransactionDate,
                    TransactionDateStr = t.TransactionDate.ToString("yyyy-MM-dd"),
                    TransactionId = t.TransactionId,
                    Quarter = GetQuarter(t.TransactionDate)
                });
            }

            return report;
        }

        //Support method to retrieve Roles Hierarchy from database
        private void getRolesHierarchy(IDatabase db)
        {
            listRoles = db.Fetch<Role>("SELECT Role.* FROM Role");

            foreach (Role parentRole in listRoles)
                getRolesHierarchyRecursive(db, parentRole);

        }

        public TransactionReport GetTransactionReport(IDatabase db, int companyId, DateTime from, DateTime to)
        {
            var bankAccounts = db.Query<BankAccount>().Where(x => x.CompanyId == companyId).ToList();

            var transactions = db.Fetch<TransactionView>(@"select * from transactionview v
            where exists(
            select * from bankaccounts b where  b.companyid = @0 and b.Accountnumber = v.AccountNumber )",
                companyId).ToList();

            var report = new TransactionReport(from, to);

            var quarters = QuarterHelper.GetQuarters(from, to);
            foreach (var quarter in quarters)
            {
                var reportQuarter = new TransactionReportQuarter {Quarter = quarter};

                foreach (var account in bankAccounts)
                {
                    var currentBalance = 0m;

                    if (from <= reportQuarter.Quarter.StartDate && to >= reportQuarter.Quarter.EndDate)
                    {
                        currentBalance = account.Openingbalance;

                        reportQuarter.TransactionReportItems.Add(new TransactionReportItem
                        {
                            BankAccount = account.Accountnumber,
                            Amount = currentBalance,
                            BalanceAfter = currentBalance,
                            TransactionDate = account.Openingbalancedate,
                            TransactionDateStr = account.Openingbalancedate.FormatStandardStr(),
                            Line = "Opening Balance as at " + account.Openingbalancedate.ToString("D")
                        });
                    }

                    foreach (var transaction in transactions.Where(x => x.AccountNumber == account.Accountnumber && x.TransactionDate >= reportQuarter.Quarter.StartDate && x.TransactionDate <= reportQuarter.Quarter.EndDate))
                    {
                        reportQuarter.TransactionReportItems.Add(new TransactionReportItem
                        {
                            BankAccount = account.Accountnumber,
                            Category = transaction.Account,
                            Amount = transaction.Amount,
                            BalanceAfter = currentBalance - transaction.Amount,
                            TransactionDate = transaction.TransactionDate,
                            TransactionDateStr =
                                (transaction.TransactionDate == new DateTime(1, 1, 1)
                                    ? ""
                                    : transaction.TransactionDate.ToString("yyyy-MM-dd")),
                            Line = transaction.Description
                        });

                        reportQuarter.Balance += transaction.Amount;
                        reportQuarter.TotalForQuarter += transaction.Amount;
                        reportQuarter.TotalTransactions++;

                    }

                    report.Quarters.Add(reportQuarter);
                }
            }

            return report;
        }