private static string GetUnitFeeOverviewWhereClause(
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, Hashtable parameters, bool useParametersOnly)
{
string where = "";
if (assetManagerId > 0)
{
parameters.Add("assetManagerId", assetManagerId);
if (!useParametersOnly)
where += " and A.AccountOwner = :assetManagerId";
}
if (remisierId > 0)
{
parameters.Add("remisierId", remisierId);
if (!useParametersOnly)
where += " and A.RemisierEmployee.Remisier.Key = :remisierId";
}
if (remisierEmployeeId > 0)
{
parameters.Add("remisierEmployeeId", remisierEmployeeId);
if (!useParametersOnly)
where += " and A.RemisierEmployee.Key = :remisierEmployeeId";
}
if (modelPortfolioId > 0)
{
parameters.Add("modelPortfolioId", modelPortfolioId);
if (!useParametersOnly)
where += " and A.ModelPortfolio.Key = :modelPortfolioId";
}
if (accountNumber != null && accountNumber.Length > 0)
{
parameters.Add("accountNumber", Util.PrepareNamedParameterWithWildcard(accountNumber));
if (!useParametersOnly)
where += " and A.Number like :accountNumber";
}
if (accountName != null && accountName.Length > 0)
{
parameters.Add("accountName", Util.PrepareNamedParameterWithWildcard(accountName));
if (!useParametersOnly)
where += " and A.ShortName like :accountName";
}
if (showActive && !showInactive)
{
parameters.Add("accountStatus", (int)AccountStati.Active);
if (!useParametersOnly)
where += " and A.Status = :accountStatus";
}
else if (!showActive && showInactive)
{
parameters.Add("accountStatus", (int)AccountStati.Inactive);
if (!useParametersOnly)
where += " and A.Status = :accountStatus";
}
if (continuationStatus != AccountContinuationStati.All)
{
DateTime minDate, maxDate;
Util.GetDatesFromQuarter(year, quarter, out minDate, out maxDate);
if (continuationStatus == AccountContinuationStati.Current)
where += string.Format(" and (P.endDate is null or P.endDate > '{0}')", maxDate.ToString("yyyy-MM-dd"));
else
where += string.Format(" and P.endDate between '{0}' and '{1}'", minDate.ToString("yyyy-MM-dd"), maxDate.ToString("yyyy-MM-dd"));
}
if (tradeStatus != ManagementPeriodUnitTradeStatus.All)
where += string.Format(" and U.ManagementFee is {0} null", (tradeStatus == ManagementPeriodUnitTradeStatus.InclTrade ? "not" : ""));
if (!includeStornoedUnits)
where += " and IsNull(U.IsStornoed, 0) = 0";
return where;
}
public static DataSet GetUnitFeeOverviewSummary(
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)
{
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, true) +
"\ngroup by PU.Period";
parameterLists.Add("periods", Util.GetPeriodsFromQuarter(year, quarter));
parameters.Add("managementType", managementType);
IDalSession session = NHSessionFactory.CreateSession();
IList summary = session.GetListByNamedQuery("B4F.TotalGiro.ApplicationLayer.Fee.ManagementPeriodUnitsForSummary", where, parameters, parameterLists);
IList avgHldFees = ManagementPeriodUnitMapper.GetManagementFeeSummary(
session, ManagementFeeLocations.AverageHolding, assetManagerId, remisierId, remisierEmployeeId,
modelPortfolioId, accountNumber, accountName, showActive, showInactive, year, quarter,
continuationStatus, managementType, tradeStatus, includeStornoedUnits);
IList unitFees = ManagementPeriodUnitMapper.GetManagementFeeSummary(
session, ManagementFeeLocations.Unit, assetManagerId, remisierId, remisierEmployeeId,
modelPortfolioId, accountNumber, accountName, showActive, showInactive, year, quarter,
continuationStatus, managementType, tradeStatus, includeStornoedUnits);
ICurrency currency = InstrumentMapper.GetBaseCurrency(session);
if (managementType == ManagementTypes.ManagementFee)
{
var fees = from a in avgHldFees.Cast<object[]>().Union(unitFees.Cast<object[]>())
group a by (int)a[0] into g
select new
{
Period = g.Key,
TotalFeeAmount = g.Sum(a => (decimal)a[2]),
TotalFixedFeeAmount = g.Where(a => (FeeTypes)a[1] == FeeTypes.FixedFee).Sum(a => (decimal)a[2]),
TotalOtherFeeAmount = g.Where(a => (FeeTypes)a[1] != FeeTypes.FixedFee).Sum(a => (decimal)a[2])
};
//select new { Period = (int)a[0], TotalFeeAmount = (decimal)a[1] };
var sum = from a in summary.Cast<object[]>()
select new { Period = (int)a[0], TotalValue = (decimal)a[1] };
var stuff = from s in sum
join f in fees on s.Period equals f.Period into x
from f in x.DefaultIfEmpty()
group new
{
s,
v = (decimal)s.TotalValue,
fp = (f != null ? (decimal)f.TotalFeeAmount : 0M),
ff = (f != null ? (decimal)f.TotalFixedFeeAmount : 0M),
fo = (f != null ? (decimal)f.TotalOtherFeeAmount : 0M)
} by s.Period
into g
select new
{
Period = g.Key,
TotalValue = new Money((decimal)g.Sum(s => s.v), currency),
TotalFee = new Money((decimal)g.Sum(f => f.fp), currency),
TotalFixedFee = new Money((decimal)g.Sum(f => f.ff), currency),
TotalOtherFee = new Money((decimal)g.Sum(f => f.fo), currency)
};
//"Period, TotalValue.DisplayString, TotalFee.DisplayString");
return stuff
.Select(c => new
{
c.Period,
TotalValue = c.TotalValue != null ? c.TotalValue.DisplayString : "",
TotalFee = c.TotalFee != null ? c.TotalFee.DisplayString : "",
FixedFee = c.TotalFixedFee != null ? c.TotalFixedFee.DisplayString : "",
OtherFee = c.TotalOtherFee != null ? c.TotalOtherFee.DisplayString : ""
})
.ToDataSet();
}
else
{
var fees = from a in avgHldFees.Cast<object[]>().Union(unitFees.Cast<object[]>())
group a by (int)a[0] into g
select new
{
Period = g.Key,
TotalFeeAmount = g.Sum(a => (decimal)a[2])
};
//select new { Period = (int)a[0], TotalFeeAmount = (decimal)a[1] };
var sum = from a in summary.Cast<object[]>()
select new { Period = (int)a[0], TotalValue = (decimal)a[1] };
var stuff = from s in sum
join f in fees on s.Period equals f.Period into x
from f in x.DefaultIfEmpty()
group new
{
s,
v = (decimal)s.TotalValue,
fp = (f != null ? (decimal)f.TotalFeeAmount : 0M)
} by s.Period
into g
select new
{
Period = g.Key,
TotalValue = new Money((decimal)g.Sum(s => s.v), currency),
TotalFee = new Money((decimal)g.Sum(f => f.fp), currency)
};
//"Period, TotalValue.DisplayString, TotalFee.DisplayString");
return stuff
.Select(c => new
{
c.Period,
TotalValue = c.TotalValue != null ? c.TotalValue.DisplayString : "",
TotalFee = c.TotalFee != null ? c.TotalFee.DisplayString : ""
})
.ToDataSet();
}
}
public static DataSet GetUnitsOverview(
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 maximumRows, int pageIndex, string sortColumn)
{
string propertyList =
string.Format("Key, {0}Account.Key, Account.Number, Account.ShortName, Account.ModelPortfolio.ShortName, Account.Status, TradeID, ManagementEndDate, Error, Period1, MgtPeriod1.TotalValue.DisplayString, MgtPeriod1.FeeAmount.DisplayString, MgtPeriod1.HasMessage, MgtPeriod1.Message, Period2, MgtPeriod2.TotalValue.DisplayString, MgtPeriod2.FeeAmount.DisplayString, MgtPeriod2.HasMessage, MgtPeriod2.Message, Period3, MgtPeriod3.TotalValue.DisplayString, MgtPeriod3.FeeAmount.DisplayString, MgtPeriod3.HasMessage, MgtPeriod3.Message, AllowCreateTransaction",
(managementType == ManagementTypes.KickBack ? "Account.RemisierEmployee.Remisier.Name, Account.RemisierEmployee.Employee.FullName, IsKickBackExported, " : ""));
string bareSortColumn = sortColumn.Split(' ')[0];
bool ascending = !(sortColumn.Split(' ').Length == 2 && sortColumn.Split(' ')[1] == "DESC");
IDalSession session = NHSessionFactory.CreateSession();
DataSet ds = null;
IList allUnitFeeItems;
if (isHqlSortingNeeded(bareSortColumn))
{
allUnitFeeItems = GetUnitFeeOverviewList(session,
assetManagerId, remisierId, remisierEmployeeId, modelPortfolioId, accountNumber, accountName,
showActive, showInactive, year, quarter, continuationStatus, managementType,
tradeStatus, includeStornoedUnits, null, bareSortColumn, ascending, true);
if (allUnitFeeItems != null && allUnitFeeItems.Count > 0)
{
var items = from a in allUnitFeeItems.Cast<object[]>()
group a by (int)a[0] into g
select new { ID = g.Key, AccountStatus = from b in g orderby b[3] descending select b[3], TradeID = from b in g orderby b[4] descending select b[4] };
ds = new DataSet("UnitFeeKeys");
DataTable dt = new DataTable();
ds.Tables.Add(dt);
dt.Columns.Add("Key", typeof(int));
dt.Columns.Add("AllowCreateTransaction", typeof(bool));
foreach (var item in items)
{
bool allowCreateTransaction = false;
AccountStati s = (AccountStati)item.AccountStatus.First();
object t = item.TradeID.First();
if (s == AccountStati.Active && (t == null || string.IsNullOrEmpty(t.ToString())))
allowCreateTransaction = true;
dt.Rows.Add(new object[] { item.ID, allowCreateTransaction });
}
//ds = DataSetBuilder.CreateDataSetFromHibernateList(allUnitFeeItems, "Key");
}
}
else
{
allUnitFeeItems = GetUnitFeeOverviewList(session,
assetManagerId, remisierId, remisierEmployeeId, modelPortfolioId, accountNumber, accountName,
showActive, showInactive, year, quarter, continuationStatus, managementType,
tradeStatus, includeStornoedUnits, null, bareSortColumn, ascending, false);
if (allUnitFeeItems != null)
ds = DataSetBuilder.CreateDataSetFromBusinessObjectList(allUnitFeeItems, "Key, " + bareSortColumn.Replace('_', '.'));
if (ds != null)
Util.SortDataTable(ds.Tables[0], sortColumn);
session.Close();
session = NHSessionFactory.CreateSession();
}
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
int[] mgtPeriodIds = Util.GetPageKeys(ds.Tables[0], maximumRows, pageIndex, "Key");
IList pageUnitFeeItems = GetUnitFeeOverviewList(session, 0, 0, 0, 0, null, null, false, false,
year, quarter, continuationStatus, managementType, tradeStatus, includeStornoedUnits, mgtPeriodIds, bareSortColumn, ascending, false);
DataSetBuilder.MergeDataTableWithBusinessObjectList(ds.Tables[0], pageUnitFeeItems, "Key", propertyList);
}
session.Close();
return ds;
}
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;
}
