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);
}
public static IList GetAverageHoldingsForManagementFee(IDalSession session, IAccountTypeInternal account, DateTime startDate, DateTime endDate)
{
Hashtable parameters = new Hashtable(2);
parameters.Add("account", account);
parameters.Add("endDate", endDate);
string hql = @"from AverageHolding A
left join fetch A.Instrument I
left join fetch A.Transaction T
where A.Account = :account
and IsNull(A.SkipFees, 0) = 0
and (A.BeginDate <= :endDate)
and IsNull(A.IsInValid, 0) = 0
and (T is null or T.StornoTransaction is not null)
and A.Key not in (
select A.PreviousHolding.Key
from AverageHolding A
where A.Account = :account
and IsNull(A.SkipFees, 0) = 0
and (A.BeginDate <= :endDate)
and A.PreviousHolding.Key is not null)
order by I.Key, A.BeginDate";
return session.GetListByHQL(hql, parameters);
}
public static IList GetUnitFeeOverviewList(
IDalSession session, int assetManagerId, int remisierId, int remisierEmployeeId,
int modelPortfolioId, string accountNumber, string accountName, bool showActive,
bool showInactive, int year, int quarter, AccountContinuationStati continuationStatus,
ManagementTypes managementType,ManagementPeriodUnitTradeStatus tradeStatus,
bool includeStornoedUnits, int[] mgtPeriodIds, string sortColumn,
bool ascending, bool keysOnly)
{
Hashtable parameterLists = new Hashtable(1);
Hashtable parameters = new Hashtable();
string where = GetUnitFeeOverviewWhereClause(assetManagerId, remisierId, remisierEmployeeId,
modelPortfolioId, accountNumber, accountName, showActive, showInactive, year, quarter,
continuationStatus, managementType, tradeStatus, includeStornoedUnits, parameters, !keysOnly);
if (mgtPeriodIds != null)
where += string.Format(" and P.Key IN ({0})",
(mgtPeriodIds.Length == 0 ? "0" : string.Join(", ", Array.ConvertAll<int, string>(mgtPeriodIds, id => id.ToString()))));
parameterLists.Add("periods", Util.GetPeriodsFromQuarter(year, quarter));
parameters.Add("managementType", managementType);
if (keysOnly)
{
string orderBy = "order by A.Number", contactsJoin = "";
bool sortOnRemisier = false;
if (sortColumn != "")
{
string ascendingStr = (ascending ? "ASC" : "DESC");
sortColumn = sortColumn.ToUpper();
string sortProperty = "";
switch (sortColumn)
{
case "KEY":
sortProperty = "P.Key";
break;
case "ACCOUNT_NUMBER":
sortProperty = "A.Number";
break;
case "ACCOUNT_SHORTNAME":
sortProperty = "CN.Name";
contactsJoin = @"left join A.bagOfAccountHolders AH
left join AH.Contact C
left join C.CurrentNAW CN";
where += " and AH.IsPrimaryAccountHolder = true";
break;
case "MANAGEMENTENDDATE":
sortProperty = "P.endDate";
break;
case "TRADEID":
sortProperty = "T.Key";
break;
case "ACCOUNT_REMISIEREMPLOYEE_REMISIER_NAME":
sortProperty = "R.Name";
sortOnRemisier = true;
break;
case "ACCOUNT_REMISIEREMPLOYEE_EMPLOYEE_FULLNAME":
sortProperty = "E.Employee.LastName";
sortOnRemisier = true;
break;
}
if (sortProperty != "")
orderBy = string.Format("order by {0} {1}", sortProperty, ascendingStr);
}
string hql = string.Format(@"select distinct P.Key, P.endDate, A.Number, A.Status, T.Key{0}{5}
from ManagementPeriodUnit U
left join U.ManagementPeriod P
left join U.UnitParent PU
left join U.ManagementFee T
left join PU.Account A
{1}
{2}
{6}
where (A.Family.managementTypesCharged & :managementType) <> 0
and PU.Period in (:periods) {3}
and P.ManagementType = :managementType {4}",
(contactsJoin != "" ? ", CN.Name" : ""),
contactsJoin,
modelPortfolioId > 0 ? "left join A.ModelPortfolio M " : "",
where, orderBy,
(sortOnRemisier ? ", R.Name, E.Employee.LastName" : ""),
(sortOnRemisier ? "left join A.RemisierEmployee E left join E.Remisier R" : ""));
return session.GetListByHQL(hql, parameters, parameterLists);
}
else
{
string queryName;
if (managementType == ManagementTypes.KickBack)
queryName = "B4F.TotalGiro.ApplicationLayer.Fee.ManagementPeriodUnitsForMgtFee";
else
queryName = "B4F.TotalGiro.ApplicationLayer.Fee.ManagementPeriodUnitsForKickBack";
IList<IManagementPeriodUnit> units = session.GetTypedListByNamedQuery<IManagementPeriodUnit>(queryName, where, parameters, parameterLists);
if (units != null && units.Count > 0)
{
IList avgHldFees = ManagementPeriodUnitMapper.GetManagementFees(session, mgtPeriodIds, ManagementFeeLocations.AverageHolding, year, quarter, managementType);
IList unitFees = ManagementPeriodUnitMapper.GetManagementFees(session, mgtPeriodIds, ManagementFeeLocations.Unit, year, quarter, managementType);
ICurrency currency = InstrumentMapper.GetBaseCurrency(session);
var fees = from a in avgHldFees.Cast<object[]>().Union(unitFees.Cast<object[]>())
select new { MgtPeriodID = (int)a[0], Period = (int)a[1], FeeType = (FeeTypes)a[2], FeeAmount = (decimal)a[3] };
var stuff = from u in units
join f in fees on new { MgtPeriodID = u.ManagementPeriod.Key, u.Period } equals new { f.MgtPeriodID, f.Period } into x
from f in x.DefaultIfEmpty()
group new { u, fp = (f != null ? new { Period = (int)f.Period, FeeAmount = new Money((decimal)f.FeeAmount, currency) } : null) } by u.ManagementPeriod
into g
select new { ManagementPeriod = g.Key, Units = (from gi in g select gi.u).ToList(), FeeAmounts = (from gj in g select gj.fp).ToList() };
List<UnitFeeOverview> list = new List<UnitFeeOverview>();
UnitFeeOverview.Year = year.ToString();
UnitFeeOverview.Quarter = string.Format("Q{0}", quarter);
UnitFeeOverview.Periods = Util.GetPeriodsFromQuarter(year, quarter);
//stuff.ToList().ForEach(p => list.Add(new UnitFeeOverview(p.Account, p.Units)));
foreach (var pair in stuff)
{
UnitFeeOverview item = new UnitFeeOverview(pair.ManagementPeriod, pair.Units);
foreach (var fee in pair.FeeAmounts)
{
if (fee != null)
item.AddFeeDetails(fee.Period, fee.FeeAmount);
}
list.Add(item);
}
return list;
}
}
return null;
}
public static IList GetEndValues(IDalSession session, IAccount account, EndTermType term, int year)
{
Hashtable parameters = new Hashtable(3);
DateTime[] dates = GetEndDates(term, year);
parameters.Add("account", account);
parameters.Add("startDate", dates[0]);
parameters.Add("endDate", dates[1]);
string hql = string.Format(
@"from EndTermValue A
left join ReportingPeriod R
where A.Account = :account
and R.EndTermDate in (:startDate, :endDate)");
return session.GetListByHQL(hql, parameters);
}
public static List<NotaDocumentView> GetNotaDocumentViews(IDalSession session, int accountId)
{
Hashtable parameters = new Hashtable();
parameters.Add("AccountId", accountId);
string hql = @"SELECT D.Key, D.CreationDate, MIN(N.Key), COUNT(*)
FROM Nota N
INNER JOIN N.Document D
WHERE N.NotaAccount.Account.Key = :AccountId
GROUP BY D.Key, D.CreationDate";
var documents = session.GetListByHQL(hql, parameters)
.ToDataTableFromHibernateList("Key, CreationDate, FirstNotaId, NotaCount", "Documents")
.Rows.Cast<DataRow>()
.Select(r => new
{
Key = (int)r["Key"],
CreationDate = (DateTime)r["CreationDate"],
FirstNotaId = (int)r["FirstNotaId"],
NotaCount = (int)(long)r["NotaCount"],
})
.ToList();
List<INota> notas = NotaMapper.GetNotas(session, documents.Select(d => d.FirstNotaId).ToArray());
return documents.Select(d =>
{
INota nota = notas.First(n => n.Key == d.FirstNotaId);
return new NotaDocumentView(
d.Key,
d.CreationDate,
nota.NotaNumber,
nota.Title,
d.NotaCount);
})
.ToList();
}
public static List<INota> GetUnprintedNotasByAccount(IDalSession session, int accountId, NotaReturnClass returnClass)
{
Hashtable parameters = new Hashtable();
parameters.Add("AccountId", accountId);
string underlyingAccObj = "";
string underlyingDateObj = "";
switch (returnClass)
{
case NotaReturnClass.NotaTransaction:
case NotaReturnClass.NotaTransfer:
case NotaReturnClass.NotaInstrumentConversion:
underlyingAccObj = "UnderlyingTx.AccountA";
underlyingDateObj = "UnderlyingTx.TransactionDate";
break;
default:
underlyingAccObj = "UnderlyingBooking.Account";
underlyingDateObj = "UnderlyingBooking.GeneralOpsJournalEntry.TransactionDate";
break;
}
return NHSession.ToList<INota>(session.GetListByHQL(
string.Format(@"FROM {0} N WHERE N.{1}.Key = :AccountId AND N.PrintCount = 0
ORDER BY N.{2}, N.NotaNumber",
getType(returnClass).Name,
underlyingAccObj,
underlyingDateObj),
parameters));
}
public static IList GetCustomerAccountsList(IDalSession session, int assetManagerId, int modelPortfolioId, string accountNumber,
string accountName, int[] accountIds, string sortColumn, bool ascending, bool keysOnly)
{
string where = "";
if (assetManagerId > 0)
where += string.Format(" and A.AccountOwner = {0} ", assetManagerId);
if (modelPortfolioId > 0)
where += string.Format(" and M.Key = {0}", modelPortfolioId);
if (accountNumber != null && accountNumber.Length > 0)
where += string.Format(" and A.Number LIKE '%{0}%'", accountNumber);
if (accountName != null && accountName.Length > 0)
where += string.Format(" and A.ShortName LIKE '%{0}%'", accountName);
if (accountIds != null)
where += string.Format(" and A.Key IN ({0})",
(accountIds.Length == 0 ? "0" : string.Join(", ", Array.ConvertAll<int, string>(accountIds, id => id.ToString()))));
string orderBy = "order by A.Key", contactsJoin = "";
if (keysOnly && sortColumn != "")
{
string ascendingStr = (ascending ? "ASC" : "DESC");
sortColumn = sortColumn.ToUpper();
string sortProperty = "";
switch (sortColumn)
{
case "KEY":
sortProperty = "A.Key";
break;
case "NUMBER":
sortProperty = "A.Number";
break;
case "SHORTNAME":
sortProperty = "CN.Name";
contactsJoin = @"left join A.bagOfAccountHolders AH
left join AH.Contact C
left join C.CurrentNAW CN";
where += " and AH.IsPrimaryAccountHolder = true";
break;
case "MODELPORTFOLIO_MODELNAME":
sortProperty = "M.ModelName";
break;
}
if (sortProperty != "")
orderBy = string.Format("order by {0} {1}", sortProperty, ascendingStr);
}
string hql = string.Format(@"{0}from CustomerAccount A
left join {1} A.ModelPortfolio M {2}
where M is not null
and A.Status = :status
and A.TradeableStatus = :tradeableStatus
and IsNull(A.IsExecOnlyCustomer, 0) = 0 {3} {4}",
(keysOnly ? "select A.Key " : ""),
(keysOnly ? "" : "fetch"),
contactsJoin, where, orderBy);
Hashtable parameters = new Hashtable();
parameters.Add("status", (int)AccountStati.Active);
parameters.Add("tradeableStatus", (int)Tradeability.Tradeable);
return session.GetListByHQL(hql, parameters);
}
public static IList GetInstructionsList(IDalSession session, int assetManagerId, int modelPortfolioId, string accountNumber, string accountName,
DateTime dateFrom, DateTime dateTo, ActivityReturnFilter activeStatus, WithdrawalInstructionsTypeReturnEnum returnType,
int[] instructionIds, string sortColumn, bool ascending, bool keysOnly)
{
string where = "";
if (assetManagerId > 0)
where += string.Format(" and A.AccountOwner = {0} ", assetManagerId);
if (modelPortfolioId > 0)
where += string.Format(" and M.Key = {0}", modelPortfolioId);
if (accountNumber != null && accountNumber.Length > 0)
where += string.Format(" and A.Number LIKE '%{0}%'", accountNumber);
if (accountName != null && accountName.Length > 0)
where += string.Format(" and A.ShortName LIKE '%{0}%'", accountName);
if (dateFrom != DateTime.MinValue || dateTo != DateTime.MinValue)
{
if (dateFrom != DateTime.MinValue && dateTo != DateTime.MinValue)
where += string.Format(" and I.WithdrawalDate between '{0}' and '{1}'", dateFrom.ToString("yyyy-MM-dd"), dateTo.ToString("yyyy-MM-dd"));
if (dateFrom != DateTime.MinValue)
where += string.Format(" and I.WithdrawalDate >= '{0}'", dateFrom.ToString("yyyy-MM-dd"));
if (dateTo != DateTime.MinValue)
where += string.Format(" and I.WithdrawalDate <= '{0}'", dateTo.ToString("yyyy-MM-dd"));
}
if (activeStatus == ActivityReturnFilter.Active)
where += string.Format(" and (I.IsActive = 1 or (I.IsActive = 0 and I.CloseDate >= '{0}'))", DateTime.Today.ToString("yyyy-MM-dd"));
else if (activeStatus == ActivityReturnFilter.InActive)
where += " and (I.IsActive = 0)";
if (returnType != WithdrawalInstructionsTypeReturnEnum.All)
where += string.Format(" and I.IsPeriodic = {0}", (returnType == WithdrawalInstructionsTypeReturnEnum.Periodic ? 1 : 0));
if (instructionIds != null)
where += string.Format(" and I.Key IN ({0})",
(instructionIds.Length == 0 ? "0" : string.Join(", ", Array.ConvertAll<int, string>(instructionIds, id => id.ToString()))));
string orderBy = "order by A.Key", contactsJoin = "";
if (keysOnly && sortColumn != "")
{
string ascendingStr = (ascending ? "ASC" : "DESC");
sortColumn = sortColumn.ToUpper();
string sortProperty = "";
switch (sortColumn)
{
case "KEY":
sortProperty = "A.Key";
break;
case "ACCOUNT_NUMBER":
sortProperty = "A.Number";
break;
case "ACCOUNT_SHORTNAME":
sortProperty = "CN.Name";
contactsJoin = @"left join A.bagOfAccountHolders AH
left join AH.Contact C
left join C.CurrentNAW CN";
where += " and AH.IsPrimaryAccountHolder = true";
break;
case "STATUS":
sortProperty = "I.Status";
break;
case "DONOTCHARGECOMMISSION":
sortProperty = "I.DoNotChargeCommission";
break;
case "MESSAGE":
sortProperty = "I.Message";
break;
case "WITHDRAWALDATE":
sortProperty = "I.WithdrawalDate";
break;
case "EXECUTIONDATE":
sortProperty = "I.ExecutionDate";
break;
case "CREATIONDATE":
sortProperty = "I.CreationDate";
break;
case "AMOUNT_QUANTITY":
sortProperty = "I.Amount.Quantity";
break;
case "ISACTIVE":
sortProperty = "I.IsActive";
break;
case "REGULARITY":
sortProperty = "R.Regularity";
break;
}
if (sortProperty != "")
orderBy = string.Format("order by {0} {1}", sortProperty, ascendingStr);
}
string hql = string.Format(@"{0}from CashWithdrawalInstruction I
left join {1} I.Account A
left join {1} A.ModelPortfolio M
left join {1} I.Rule R {2}
where (I.ExecutionDate <= '{3}') {4} {5}",
(keysOnly ? "select I.Key, I.IsActive " : ""),
(keysOnly ? "" : "fetch"),
contactsJoin, DateTime.Today.ToString("yyyy-MM-dd"), where, orderBy);
return session.GetListByHQL(hql, null);
}