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);
}
public static DataSet GetInstructions(int assetManagerId, int modelPortfolioId, string accountNumber, string accountName,
DateTime dateFrom, DateTime dateTo, ActivityReturnFilter activeStatus, WithdrawalInstructionsTypeReturnEnum returnType,
int maximumRows, int pageIndex, string sortColumn)
{
const string propertyList =
"Key, Account.Key, Account.Number, Account.ShortName, Status, DisplayStatus, Message, Warning, WithdrawalDate, LatestPossibleFreeUpCashDate, Amount.Quantity, Amount.DisplayString, DisplayRegularity, HasOrders, MoneyTransferOrder.Key, IsEditable, IsActive, CreationDate, DoNotChargeCommission, IsCancellable";
string bareSortColumn = sortColumn.Split(' ')[0];
bool ascending = !(sortColumn.Split(' ').Length == 2 && sortColumn.Split(' ')[1] == "DESC");
IDalSession session = NHSessionFactory.CreateSession();
DataSet ds = null;
IList allInstructions;
if (isHqlSortingNeeded(bareSortColumn))
{
allInstructions = GetInstructionsList(session,
assetManagerId, modelPortfolioId, accountNumber, accountName, dateFrom, dateTo, activeStatus, returnType, null, bareSortColumn, ascending, true);
ds = DataSetBuilder.CreateDataSetFromHibernateList(allInstructions, "Key, IsActive");
}
else
{
allInstructions = GetInstructionsList(session,
assetManagerId, modelPortfolioId, accountNumber, accountName, dateFrom, dateTo, activeStatus, returnType, null, bareSortColumn, ascending, false);
ds = DataSetBuilder.CreateDataSetFromBusinessObjectList(allInstructions, "Key, " + bareSortColumn.Replace('_', '.'));
Util.SortDataTable(ds.Tables[0], sortColumn);
session.Close();
session = NHSessionFactory.CreateSession();
}
int[] instructionIds = Util.GetPageKeys(ds.Tables[0], maximumRows, pageIndex, "Key");
IList pageInstructions = GetInstructionsList(session, 0, 0, null, null, DateTime.MinValue, DateTime.MinValue, ActivityReturnFilter.All, WithdrawalInstructionsTypeReturnEnum.All, instructionIds, bareSortColumn, ascending, false);
DataSetBuilder.MergeDataTableWithBusinessObjectList(ds.Tables[0], pageInstructions, "Key", propertyList);
session.Close();
return ds;
}
