/// <summary>
/// Build the sql statement including its filters to avoid using the .NET filtering. This improves performance greatly.
/// </summary>
/// <param name="limit">Return the specified number of rows. E.g. "TOP 1"</param>
/// <param name="parentID">Organization Id.</param>
/// <param name="orderBy">Fields in sql format to sort the results by. E.g. "LastName, FirstName"</param>
/// <param name="filters">Filters to be applied. Specified in the URL request.</param>
/// <param name="filterParameters">SqlParamenterCollection for the input parameters of the sql query.</param>
/// <returns>A string with the full sql statement.</returns>
public string BuildLoadByParentOrganizationIdSql(string limit, int organizationParentId, string orderBy, NameValueCollection filters, SqlParameterCollection filterParameters, bool isCustomer = false, bool useMaxDop = false)
{
StringBuilder result = new StringBuilder();
//check if it has the PhoneNumber filter by itself and also if it has a square bracket for the contains, lt, gt,... extra operators
bool hasPhoneNumberFilter = filters.AllKeys.Where(p => p.ToLower() == "phonenumber").Any() || filters.AllKeys.Where(p => p.ToLower().Contains("phonenumber[")).Any();
//If phoneNumber filter is used then SELECT needs to be DISTINCT to avoid identity exceptions on the DataTable when a contact has multiple phonenumbers (one-to-many relationship between ContactsView and PhoneNumbers)
if (hasPhoneNumberFilter)
{
result.Append("SELECT DISTINCT " + limit + " ContactsView.* ");
}
else
{
result.Append("SELECT " + limit + " * ");
}
result.Append("FROM ContactsView ");
if (hasPhoneNumberFilter)
{
result.Append("LEFT JOIN PhoneNumbers ON ContactsView.UserID = PhoneNumbers.RefID ");
}
result.Append("WHERE " + (isCustomer ? "OrganizationID" : "OrganizationParentID") + " = @OrganizationParentId AND (MarkDeleted = 0) ");
result.Append(DataUtils.BuildWhereClausesFromFilters(this.LoginUser, this, organizationParentId, filters, ReferenceType.Contacts, "UserID", null, ref filterParameters) + " ");
result.Append("ORDER BY " + orderBy);
if (useMaxDop)
{
result.Append(" OPTION (MAXDOP 1);");
}
return(result.ToString());
}
public void LoadByOrganizationID(int organizationId, NameValueCollection filters, int?PageNumber = null, int?PageSize = null)
{
//Get the column names
LoadColumns("DeletedTickets");
string orderBy = "ORDER BY DateDeleted DESC";
string sql = string.Empty;
using (SqlCommand command = new SqlCommand())
{
SqlParameterCollection filterParameters = command.Parameters;
string whereClause = DataUtils.BuildWhereClausesFromFilters(this.LoginUser, this, organizationId, filters, ReferenceType.DeletedTickets, "ID", null, ref filterParameters);
sql = @"
SELECT
*
FROM
DeletedTickets
WHERE
OrganizationID = @organizationId " + whereClause + " " + orderBy;
sql = DataUtils.AddPaging(sql, PageSize, PageNumber, command);
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@organizationId", organizationId);
Fill(command);
}
}
/// <summary>
/// Build the sql statement including its filters to avoid using the .NET filtering. This improves performance greatly.
/// </summary>
/// <param name="limit">Return the specified number of rows. E.g. "TOP 1"</param>
/// <param name="parentID">Organization Id.</param>
/// <param name="orderBy">Fields in sql format to sort the results by. E.g. "LastName, FirstName"</param>
/// <param name="filters">Filters to be applied. Specified in the URL request.</param>
/// <param name="filterParameters">SqlParamenterCollection for the input parameters of the sql query.</param>
/// <returns>A string with the full sql statement.</returns>
public string BuildLoadByParentOrganizationIdSql(string limit, int organizationParentId, string orderBy, NameValueCollection filters, SqlParameterCollection filterParameters)
{
StringBuilder result = new StringBuilder();
result.Append("SELECT " + limit + " * ");
result.Append("FROM AssetsView ");
result.Append("WHERE OrganizationID = @OrganizationId ");
result.Append(DataUtils.BuildWhereClausesFromFilters(this.LoginUser, this, organizationParentId, filters, ReferenceType.Assets, "AssetID", null, ref filterParameters) + " ");
result.Append("ORDER BY " + orderBy);
return(result.ToString());
}
private string BuildLoadByParentIDSql(string limit, int parentID, string orderBy, NameValueCollection filters, SqlParameterCollection filterParameters)
{
StringBuilder whereClauses = new StringBuilder();
StringBuilder result = new StringBuilder();
result.Append("SELECT " + limit + " * ");
result.Append("FROM OrganizationsView ");
result.Append("WHERE ParentID = @ParentID " + DataUtils.BuildWhereClausesFromFilters(this.LoginUser, this, parentID, filters, ReferenceType.Organizations, "OrganizationID", null, ref filterParameters) + " ");
result.Append("ORDER BY " + orderBy);
filterParameters.AddWithValue("@ParentID", parentID);
return(result.ToString());
}
