public static IList<IDividWepRecord> GetRecordsforDividWepFile(IDalSession session, int fileID)
{
Hashtable parameters = new Hashtable(1);
parameters.Add("fileID", fileID);
string hql = string.Format(
@"from DividWepRecord D
where D.ParentFile.Key = :fileID");
return session.GetTypedListByHQL<IDividWepRecord>(hql, parameters);
}
public static IInstrumentsCategories GetDefaultInstrumentsCategory(IDalSession session)
{
IInstrumentsCategories category = null;
string hql = "from InstrumentsCategories I where I.IsDefault = 1";
IList<IInstrumentsCategories> list = session.GetTypedListByHQL<IInstrumentsCategories>(hql);
if (list != null && list.Count == 1)
{
category = list[0];
}
return category;
}
public static IReport GetReport(IDalSession session, int accountId, int reportLetterYear, ReportLetterTypes reportLetterType)
{
Hashtable parameters = new Hashtable();
parameters.Add("AccountId", accountId);
parameters.Add("ReportLetterYear", reportLetterYear);
parameters.Add("ReportLetterType", (int)reportLetterType);
string hql = @"FROM Report R
WHERE R.Account.Key = :AccountId
AND R.ReportLetter.ReportLetterYear = :ReportLetterYear
AND R.ReportLetter.ReportLetterTypeId = :ReportLetterType
ORDER BY R.ReportLetter.CreationDate DESC";
List<IReport> reports = session.GetTypedListByHQL<IReport>(hql, parameters);
if (reports.Count == 0)
return null;
else if (reports.Count == 1)
return reports[0];
else
throw new ApplicationException(string.Format("More than one Report object found for account {0}, year {1}, and letter type '{2}'",
accountId, reportLetterYear, reportLetterType));
}
public static int[] GetAccountIdsWithFreshDocuments(IDalSession session, DocumentSubtypes documentSubtype,
int managementCompanyId, int accountId)
{
Hashtable parameters = new Hashtable();
string hql = string.Format(@"SELECT CA.Key FROM CustomerAccount CA
WHERE CA.Key IN (SELECT DISTINCT A.Key {0})", hqlDocumentsWithJoins(documentSubtype, false));
if (managementCompanyId != 0)
{
parameters.Add("ManagementCompanyId", managementCompanyId);
hql += " AND CA.AccountOwner.Key = :ManagementCompanyId";
}
if (accountId != 0)
{
parameters.Add("AccountId", accountId);
hql += " AND CA.Key = :AccountId";
}
hql += " ORDER BY CA.Number";
return session.GetTypedListByHQL<int>(hql, parameters).ToArray();
}
public static IList<IEndTermValue> GetYearEndValues(IDalSession session, DateTime endTermDate)
{
Hashtable parameters = new Hashtable(1);
parameters.Add("endTermDate", endTermDate);
string hql = string.Format(
@"From EndTermValue E
where E.Key in (
Select A.Key
from EndTermValue A
left join A.ReportingPeriod R
where R.EndTermDate = :endTermDate)");
return session.GetTypedListByHQL <IEndTermValue>(hql, parameters);
}
public static List<IContact> GetContacts(IDalSession session, int[] contactIds)
{
Hashtable parameters = new Hashtable();
Hashtable parameterLists = new Hashtable();
string where = "1 = 2";
if (contactIds.Length > 0)
{
parameterLists.Add("keys", contactIds);
where = "C.Key in (:keys)";
}
string hql = string.Format("from Contact C where {0} order by C.CurrentNAW.Name", where);
return session.GetTypedListByHQL<IContact>(hql, parameters, parameterLists);
}
// Used only for testing (for now)
public static List<IContact> GetContacts(IDalSession session, SendableDocumentCategories category, SendingOptions sendingOption, bool value)
{
Hashtable parameters = new Hashtable();
parameters.Add("Category", (int)category);
parameters.Add("SendingOption", (int)sendingOption);
parameters.Add("Value", value);
string hql = @"from Contact C
left join fetch C.contactSendingOptions SO
where SO.SendableDocumentCategory = :Category and SO.SendingOption = :SendingOption and SO.Value = :Value
order by C.CurrentNAW.Name";
return session.GetTypedListByHQL<IContact>(hql, parameters);
}
public static List<IContact> GetContacts(IDalSession session,
int assetManagerId, int remisierId, int remisierEmployeeId, string accountNumber, string contactName,
bool contactActive, bool contactInactive, ContactTypeEnum? contactTypeEnum, bool emailStatusYes, bool emailStatusNo,
bool? hasLogin, bool? passwordSent, bool? isLoginActive, bool accountActive, bool accountInactive, string bsN_KvK)
{
Hashtable parameters = new Hashtable();
StringBuilder sb = new StringBuilder();
string className = (contactTypeEnum == ContactTypeEnum.Person ? "ContactPerson" :
(contactTypeEnum == ContactTypeEnum.Company ? "Company" : "Contact"));
sb.AppendFormat(@"SELECT C.Key FROM {0} C
LEFT OUTER JOIN C.Login L WHERE 1=1", className);
if (assetManagerId != 0)
{
parameters.Add("AssetManagerId", assetManagerId);
sb.Append(" AND C.AssetManager.Key = :AssetManagerId");
}
string accountHolderWhereHql = "";
if (remisierId != 0)
{
parameters.Add("RemisierId", remisierId);
accountHolderWhereHql += " AND AccH.GiroAccount.RemisierEmployee.Remisier.Key = :RemisierId";
}
if (remisierEmployeeId != 0)
{
parameters.Add("RemisierEmployeeId", remisierEmployeeId);
accountHolderWhereHql += " AND AccH.GiroAccount.RemisierEmployee.Key = :RemisierEmployeeId";
}
if (!string.IsNullOrEmpty(accountNumber))
{
parameters.Add("AccountNumber", accountNumber + "%");
accountHolderWhereHql += " AND AccH.GiroAccount.Number LIKE :AccountNumber";
}
AccountStati? accountStatus = null;
if (accountActive && !accountInactive)
accountStatus = AccountStati.Active;
else if (!accountActive && accountInactive)
accountStatus = AccountStati.Inactive;
if (accountStatus != null)
{
parameters.Add("AccountStatus", (int)accountStatus);
accountHolderWhereHql += " AND AccH.GiroAccount.Status = :AccountStatus";
}
if (accountHolderWhereHql != "")
{
if (assetManagerId != 0)
accountHolderWhereHql += " AND AccH.GiroAccount.AccountOwner.Key = :AssetManagerId";
sb.AppendFormat(" AND C.Key IN (SELECT AccH.Contact.Key FROM AccountHolder AccH WHERE 1=1 {0})", accountHolderWhereHql);
}
if (!string.IsNullOrEmpty(contactName))
{
parameters.Add("ContactName", "%" + contactName + "%");
sb.Append(" AND C.CurrentNAW.Name LIKE :ContactName");
}
sb.Append(" AND C.IsActive ");
if (contactActive && !contactInactive)
sb.Append("= true");
else if (!contactActive && contactInactive)
sb.Append("= false");
else if (!contactActive && !contactInactive)
sb.Append("IS NULL");
else
sb.Append("IS NOT NULL");
if (!string.IsNullOrEmpty(bsN_KvK))
{
parameters.Add("BsN_KvK", "%" + bsN_KvK + "%");
sb.Append(@" AND (C.Key IN (SELECT CP.Key FROM ContactPerson CP WHERE CP.BurgerServiceNummer LIKE :BsN_KvK) OR
C.Key IN (SELECT CC.Key FROM ContactCompany CC WHERE CC.KvKNumber LIKE :BsN_KvK))");
}
string hasEmail = "(C.ContactDetails.Email IS NOT NULL AND C.ContactDetails.Email != '')";
if (!emailStatusYes)
sb.Append(" AND NOT " + hasEmail);
if (!emailStatusNo)
sb.Append(" AND " + hasEmail);
if (hasLogin != null)
sb.AppendFormat(" AND L IS {0} NULL", (bool)hasLogin ? "NOT" : "");
if (passwordSent != null)
sb.AppendFormat(" AND L.PasswordSent = {0}", passwordSent.ToString().ToLower());
if (isLoginActive != null)
sb.AppendFormat(" AND L.IsActive = {0}", isLoginActive.ToString().ToLower());
string hql = string.Format("FROM {0} CX WHERE CX.Key IN ({1})", className, sb);
return session.GetTypedListByHQL<IContact>(hql, parameters);
}
public static List<IValuation> GetValuations(IDalSession session, int accountID, int instrumentID, DateTime[] dates,
bool includeClosedPositions, bool includeChildInstruments)
{
Hashtable parameters = new Hashtable();
Hashtable parameterLists = new Hashtable();
parameters.Add("AccountID", accountID);
parameters.Add("InstrumentID", instrumentID);
parameterLists.Add("Dates", dates);
string hql = string.Format(@"from Valuation V
left join fetch {0} I
{1}
where V.Account.Key = :AccountID
and I.Key = :InstrumentID
and V.Date in (:Dates)
{2}
order by I.Key, V.Date",
(includeChildInstruments ? "V.Instrument.topParentInstrument" : "V.Instrument"),
(includeClosedPositions ? "" : "left join fetch V.ValuationMutation VM"),
(includeClosedPositions ? "" : "and VM.Size.Quantity != 0"));
return session.GetTypedListByHQL<IValuation>(hql, parameters, parameterLists);
}
public static List<IDocument> GetFreshDocuments(IDalSession session, DocumentSubtypes documentSubtype, int accountId)
{
Hashtable parameters = new Hashtable();
parameters.Add("AccountId", accountId);
string hql = string.Format(@"FROM Document DD
WHERE DD.Key IN (SELECT DISTINCT D.Key {0} AND A.Key = :AccountId)
ORDER BY DD.CreationDate",
hqlDocumentsWithJoins(documentSubtype, false));
return session.GetTypedListByHQL<IDocument>(hql, parameters);
}
public static List<IFinancialReportDocument> GetFinancialReportDocuments(IDalSession session, int accountId)
{
Hashtable parameters = new Hashtable();
parameters.Add("AccountId", accountId);
parameters.Add("ReportStatus", (int)ReportStatuses.PrintSuccess);
string hql = @"FROM FinancialReportDocument D
WHERE D.Report.Account = :AccountId
AND D.Report.ReportStatusId = :ReportStatus";
return session.GetTypedListByHQL<IFinancialReportDocument>(hql, parameters);
}
public static List<INotaDocument> GetNotaDocuments(IDalSession session, int accountId)
{
Hashtable parameters = new Hashtable();
parameters.Add("AccountId", accountId);
string hql = @"FROM NotaDocument DD WHERE
DD.Key IN (SELECT D.Key FROM NotaDocument D
JOIN D.bagOfNotas N
WHERE N.NotaAccount.Account.Key = :AccountId)";
return session.GetTypedListByHQL<INotaDocument>(hql, parameters);
}
private static IList<int> getTRansactions(IDalSession session)
{
string hqlstring = @"Select T.Key from Transaction T where T.Description = ''";
return session.GetTypedListByHQL<int>(hqlstring);
}
public static List<IReport> GetReports(IDalSession session, int reportLetterYear, ReportLetterTypes reportLetterType,
int? managementCompanyId, int[] accountIds, ReportStatuses? reportStatus)
{
Hashtable parameters = new Hashtable();
Hashtable parameterLists = new Hashtable();
string hql = @"FROM Report R
WHERE R.ReportLetter.ReportLetterYear = :ReportLetterYear
AND R.ReportLetter.ReportLetterTypeId = :ReportLetterType";
parameters.Add("ReportLetterYear", reportLetterYear);
parameters.Add("ReportLetterType", (int)reportLetterType);
if (managementCompanyId != null)
{
parameters.Add("CompanyId", managementCompanyId);
hql += @" AND R.Account.Key IN (SELECT AI.Key FROM AccountTypeInternal AI
WHERE AI.AccountOwner.Key = :CompanyId)";
}
if (accountIds != null)
{
if (accountIds.Length == 0)
accountIds = new int[] { 0 };
parameterLists.Add("AccountIds", accountIds);
hql += " AND R.Account.Key IN (:AccountIds)";
}
if (reportStatus != null)
{
parameters.Add("ReportStatus", (int)reportStatus);
hql += " AND R.ReportStatusId = :ReportStatus";
}
hql += " ORDER BY R.Account.Number";
return session.GetTypedListByHQL<IReport>(hql, parameters, parameterLists);
}
