C# IDalSession.GetListByHQL方法代码示例

        public static IImportedBankBalance GetImportedBankBalance(IDalSession session, IJournal bankJournal, DateTime bookBalanceDate)
        {
            Hashtable parameters = new Hashtable();
            parameters.Add("BankJournalKey", bankJournal.Key);
            parameters.Add("BookBalanceDate", bookBalanceDate);

            string hql = @"from ImportedBankBalance I
                            where I.BankJournal.Key = :BankJournalKey
                            and I.BookBalanceDate = :BookBalanceDate";

            IList result = session.GetListByHQL(hql, parameters);
            if (result != null && result.Count == 1)
                return (ImportedBankBalance)result[0];
            else
                return null;

            //List<ICriterion> expressions = new List<ICriterion>();
            //expressions.Add(Expression.Eq("BankJournal.Key", bankJournal.Key));
            //expressions.Add(Expression.Eq("BookBalanceDate", bookBalanceDate));
            //IList result = session.GetList(typeof(ImportedBankBalance), expressions);
            //if (result != null && result.Count == 1)
            //    return (IImportedBankBalance)result[0];
            //else
            //    return null;
        }

        public static IList GetAverageHoldings(IDalSession session, IAccountTypeInternal account, DateTime startDate, DateTime endDate, ValuationTypesFilterOptions filterOption)
        {
            Hashtable parameters = new Hashtable(1);
            parameters.Add("account", account);
            parameters.Add("startDate", startDate);
            parameters.Add("endDate", endDate);
            string instrumentFilter = "";

            switch (filterOption)
            {
                case ValuationTypesFilterOptions.Security:
                    instrumentFilter = "and I.Key in (select S.Key from TradeableInstrument S) ";
                    break;
                case ValuationTypesFilterOptions.Monetary:
                    instrumentFilter = "and I.Key in (select C.Key from Currency C) ";
                    break;
            }

            string hql = string.Format(@"from AverageHolding A
                left join fetch A.Instrument I
                where A.Account = :account
                and (A.BeginDate between :startDate
                and :endDate ) {0}
                order by I.Key, A.BeginDate", instrumentFilter);
            return session.GetListByHQL(hql, parameters);
        }

        //public static IList GetUnprintedNotas(IDalSession session)
        //{
        //    List<ICriterion> expressions = new List<ICriterion>();
        //    expressions.Add(Expression.Eq("PrintCount", 0));
        //    return session.GetList(typeof(Nota), expressions);
        //}
        //        public static List<IManagementCompany> GetMgmtCompaniesWithUnprintedNotas(IDalSession session,
        //                                                                                  NotaReturnClass returnClass, int[] managementCompanyIds)
        //        {
        //            string hql = string.Format(@"FROM ManagementCompany M WHERE M IN
        //                                            (SELECT A.AccountOwner FROM CustomerAccount A WHERE A IN
        //                                                (SELECT N.UnderlyingTx.AccountA FROM {0} N WHERE N.PrintCount = 0))",
        //                                       getType(returnClass).Name);
        //            if (managementCompanyIds != null && managementCompanyIds.Length > 0)
        //            {
        //                string idList = "";
        //                for (int i = 0; i < managementCompanyIds.Length; i++)
        //                    idList += (i > 0 ? ", " : "") + managementCompanyIds[i].ToString();
        //                hql += string.Format(" AND M IN ({0})", idList);
        //            }
        //            return NHSession.ToList<IManagementCompany>(session.GetListByHQL(hql, new Hashtable()));
        //        }
        public static int[] GetAccountsIdsWithUnprintedNotas(IDalSession session, int managementCompanyId, NotaReturnClass returnClass)
        {
            Hashtable parameters = new Hashtable();
            parameters.Add("ManagementCompanyId", managementCompanyId);
            string underlyingAccObj = "";
            switch (returnClass)
            {
                case NotaReturnClass.NotaTransaction:
                case NotaReturnClass.NotaTransfer:
                case NotaReturnClass.NotaInstrumentConversion:
                    underlyingAccObj = "UnderlyingTx.AccountA";
                    break;
                default:
                    underlyingAccObj = "UnderlyingBooking.Account";
                    break;
            }

            ArrayList accountIds = (ArrayList)session.GetListByHQL(
                string.Format(@"SELECT A.Key FROM CustomerAccount A
                                WHERE A.Key IN (SELECT N.{0}.Key FROM {1} N WHERE N.PrintCount = 0)
                                AND A.AccountOwner.Key = :ManagementCompanyId
                                ORDER BY A.Number",
                                underlyingAccObj,
                                getType(returnClass).Name),
                                parameters);
            return (int[])accountIds.ToArray(typeof(int));
        }

        public static IList GetEndValues(IDalSession session, DateTime endTermDate)
        {
            Hashtable parameters = new Hashtable(1);
            parameters.Add("endTermDate", endTermDate);

            string hql = string.Format(
                @"from EndTermValue A
                left join A.ReportingPeriod R
                where R.EndTermDate = :endTermDate");
            return session.GetListByHQL(hql, parameters);
        }

        public static IBookYearClosure GetBookYearClosure(IDalSession session, int bookYearClosureID)
        {
            string hql;
            Hashtable parameters = new Hashtable();

            hql = @"FROM BookYearClosure B WHERE B.Key = :bookYearClosureID";
            parameters.Add("bookYearClosureID", bookYearClosureID);

            IList balances = session.GetListByHQL(hql, parameters);
            return (balances.Count > 0 ? (IBookYearClosure)balances[0] : null);
        }

        public static IList GetUnMappedAccounts(IDalSession session)
        {
            Hashtable parameters = new Hashtable();

            string hql = @"from EGAccount ega
                join fetch ega.AccountRequest T
                where (ega.TGAccount is null)
                and (T.FormOntvangen = 1)";

            IList result = session.GetListByHQL(hql,parameters);
            return result;
        }

        public static ClientCashPositionFromGLLedger GetClientCashPositionFromGLLedger(IDalSession session, DateTime bookDate)
        {
            string hql;
            Hashtable parameters = new Hashtable();

            hql = @"FROM ClientCashPositionFromGLLedger T WHERE T.Key = :bookDate";
            parameters.Add("bookDate", bookDate);

            IList balances = session.GetListByHQL(hql, parameters);

            return (balances.Count > 0 ? (ClientCashPositionFromGLLedger)balances[0] : null);
        }

        public static TrialBalance GetTrialBalance(IDalSession session, DateTime transactionDate)
        {
            string hql;
            Hashtable parameters = new Hashtable();

            hql = @"FROM TrialBalance T WHERE T.Key = :TransactionDate";
            parameters.Add("TransactionDate", transactionDate);

            IList balances = session.GetListByHQL(hql, parameters);

            return (balances.Count > 0 ? (TrialBalance)balances[0] : null);
        }

 public static IDividWepFile GetLastDividWepFileCreated(IDalSession session)
 {
     string hql = string.Format(
         @"from DividWepFile DW
             where DW.FinancialYear in
             (Select Max(D.FinancialYear)
             from DividWepFile D)");
     IList files = session.GetListByHQL(hql);
     if (files != null && files.Count == 1)
         return (DividWepFile)(files[0]);
     else
         return null;
 }

 public static IPeriodicReporting GetLastReportingPeriod(IDalSession session)
 {
     string hql = string.Format(
         @"from PeriodicReporting A
         where A.EndTermDate in (
         Select max(PR.EndTermDate)
         from PeriodicReporting PR)");
     IList result = session.GetListByHQL(hql);
     if ((result != null) && (result.Count > 0))
         return (IPeriodicReporting)result[0];
     else
         return null;
 }

        public static IJobHistory GetJobHistoryDetail(IDalSession session, int jobHistoryID)
        {
            string hql = "";
            Hashtable parameters = new Hashtable();
            IJobHistory detail = null;

            hql = "from JobHistory J where J.Key = :key";
            parameters.Add("key", jobHistoryID);

            IList list = session.GetListByHQL(hql, parameters);
            if (list != null && list.Count == 1)
            {
                detail = (IJobHistory)list[0];
            }
            return detail;
        }

        public static IList GetManagementFeeDatesToProcess(IDalSession session, DateTime startDate)
        {
            Hashtable parameters = new Hashtable();

            StringBuilder sb = new StringBuilder(@"select distinct m.TransactionDate from ManagementFee m");
            sb.Append(@" where m.TransactionDate > :startDate");
            sb.Append(@" and m.Key not in (Select J.TotalGiroTrade.Key from JournalEntryLine J where J.TotalGiroTrade is not null)");
            sb.Append(@" order by m.TransactionDate");

            parameters.Add("startDate", startDate);

            string hql = sb.ToString();
            IList balances = session.GetListByHQL(hql, parameters);

            return balances;
        }

        public static IList GetJobHistoryDetails(IDalSession session, IManagementCompany company, string jobName, string componentName, DateTime startDate, DateTime endDate)
        {
            Hashtable parameters = new Hashtable();
            string hql = "";

            if (company == null)
                throw new ApplicationException("The current company can not be null");
            else
            {
                if (!company.IsStichting)
                    hql = " where IsNull(J.ManagementCompanyID, 0) in (0," + company.Key.ToString() + ")";
            }

            if (endDate != DateTime.MinValue)
                endDate = endDate.AddDays(1).Date;

            if (jobName + string.Empty != string.Empty)
                hql = (hql == "" ? " where " : " and ") + "J.Job like '%" + jobName + "%'";

            if (componentName + string.Empty != string.Empty)
                hql += (hql == "" ? " where " : " and ") + "J.JobComponent like '%" + componentName + "%'";

            if (startDate != DateTime.MinValue && endDate != DateTime.MinValue)
            {
                parameters.Add("StartTime", startDate);
                parameters.Add("EndTime", endDate);
                hql += (hql == "" ? " where " : " and ") + "J.StartTime between :StartTime and :EndTime";
            }
            else
            {
                if (startDate != DateTime.MinValue)
                {
                    hql += (hql == "" ? " where " : " and ") + "J.StartTime >= :StartTime";
                    parameters.Add("StartTime", startDate);
                }

                if (endDate != DateTime.MinValue)
                {
                    hql += (hql == "" ? " where " : " and ") + "J.StartTime <= :EndTime";
                    parameters.Add("EndTime", endDate);
                }
            }

            hql = "from JobHistory J " + hql;
            return session.GetListByHQL(hql, parameters);
        }

        public static IPeriodicReporting GetReportingPeriod(IDalSession session, ReportingPeriodDetail reportingPeriodDetail)
        {
            Hashtable parameters = new Hashtable(2);
            if(reportingPeriodDetail.TermType == EndTermType.FullYear)
                parameters.Add("termType", EndTermType.FourthQtr);
            else
                parameters.Add("termType", reportingPeriodDetail.TermType);
            parameters.Add("endTermYear", reportingPeriodDetail.EndTermYear);

            string hql = string.Format(
                @"from PeriodicReporting P
                where P.ReportingPeriod.TermType = :termType
                and P.ReportingPeriod.EndTermYear = :endTermYear");
            IList result = session.GetListByHQL(hql, parameters);
            if ((result != null) && (result.Count > 0))
                return (IPeriodicReporting)result[0];
            else
                return null;
        }

        public static IList GetNavCalculations(IDalSession session, IVirtualFund fund, DateTime navDateFrom,
                                              DateTime navDateTo)
        {
            Hashtable parameters = new Hashtable();
            string hql = string.Format("FROM NavCalculation S WHERE (1 = 1");

            if (navDateFrom != DateTime.MinValue)
            {
                parameters.Add("NavDateFrom", navDateFrom);
                hql += " AND S.ValuationDate >= :NavDateFrom";
            }

            if (navDateTo != DateTime.MinValue)
            {
                parameters.Add("NavDateTo", navDateTo);
                hql += " AND S.ValuationDate <= :NavDateTo";
            }

            hql += ")";

            return session.GetListByHQL(hql, parameters);
        }