//static public string CreateFullName(esDataRequest request, esDynamicQuerySerializable query)
static public string CreateFullName(esDataRequest request, esDynamicQuery query)
{
//IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
IDynamicQueryInternal iQuery = query as IDynamicQueryInternal;
esProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as esProviderSpecificMetadata;
string name = String.Empty;
string schema = iQuery.Schema ?? request.Schema ?? providerMetadata.Schema;
if (schema != null)
{
name += Delimiters.TableOpen + schema + Delimiters.TableClose + ".";
}
name += Delimiters.TableOpen;
if (query.querySource != null)
{
name += query.querySource;
}
else
{
name += providerMetadata.Destination;
}
name += Delimiters.TableClose;
return(name);
}
protected static string GetFromStatement(StandardProviderParameters std, esDynamicQuery query)
{
IDynamicQueryInternal iQuery = query as IDynamicQueryInternal;
string sql = String.Empty;
if (iQuery.InternalFromQuery == null)
{
sql = Shared.CreateFullName(std.request);
if (iQuery.JoinAlias != " ")
{
sql += " " + iQuery.JoinAlias;
}
}
else
{
IDynamicQueryInternal iSubQuery = iQuery.InternalFromQuery as IDynamicQueryInternal;
iSubQuery.IsInSubQuery = true;
sql += "(";
sql += BuildQuery(std, iQuery.InternalFromQuery);
sql += ")";
if (iSubQuery.SubQueryAlias != " ")
{
sql += " AS " + iSubQuery.SubQueryAlias;
}
iSubQuery.IsInSubQuery = false;
}
return(sql);
}
private void PerformAutoLogic(esDataSourceSelectEventArgs e)
{
esDynamicQuery query = e.Query != null ? e.Query : e.Collection.es.Query;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (this.autoPaging)
{
query.es.PageNumber = e.PageNumber;
query.es.PageSize = e.PageSize;
}
if (this.autoSorting)
{
if (e.SortItems != null)
{
foreach (esDataSourceSortItem sortItem in e.SortItems)
{
esColumnMetadata col = e.Collection.es.Meta.Columns.FindByPropertyName(sortItem.Property);
if (col != null)
{
query.OrderBy(col.Name, sortItem.Direction);
}
else if (sortItem.Property[0] == '<')
{
query.OrderBy(sortItem.Property, sortItem.Direction);
}
}
}
else
{
if (this.AutoPaging)
{
List <esColumnMetadata> pks = e.Collection.es.Meta.Columns.PrimaryKeys;
if (pks != null)
{
foreach (esColumnMetadata pk in pks)
{
query.OrderBy(pk.Name, esOrderByDirection.Ascending);
}
}
}
}
}
if (this.autoSorting || this.AutoPaging)
{
if (e.Query != null)
{
IEntityCollection iColl = e.Collection as IEntityCollection;
iColl.HookupQuery(query);
}
query.Load();
}
}
protected static string BuildQuery(StandardProviderParameters std, esDynamicQuery query)
{
bool paging = false;
if (query.pageNumber.HasValue && query.pageSize.HasValue)
{
paging = true;
}
IDynamicQueryInternal iQuery = query as IDynamicQueryInternal;
string select = GetSelectStatement(std, query);
string from = GetFromStatement(std, query);
string join = GetJoinStatement(std, query);
string where = GetComparisonStatement(std, query, iQuery.InternalWhereItems, " WHERE ");
string groupBy = GetGroupByStatement(std, query);
string having = GetComparisonStatement(std, query, iQuery.InternalHavingItems, " HAVING ");
string orderBy = GetOrderByStatement(std, query);
string setOperation = GetSetOperationStatement(std, query);
string sql = String.Empty;
sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
if (paging)
{
int begRow = ((query.pageNumber.Value - 1) * query.pageSize.Value);
sql += " LIMIT " + query.pageSize.ToString();
sql += " OFFSET " + begRow.ToString() + " ";
}
else if (query.top >= 0)
{
sql += " LIMIT " + query.top.ToString() + " ";
}
else if (iQuery.Skip.HasValue || iQuery.Take.HasValue)
{
if (iQuery.Take.HasValue)
{
sql += " LIMIT " + iQuery.Take.ToString() + " ";
}
if (iQuery.Skip.HasValue)
{
sql += " OFFSET " + iQuery.Skip.ToString() + " ";
}
}
return(sql);
}
protected static string GetOrderByStatement(StandardProviderParameters std, esDynamicQuery query)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQueryInternal iQuery = query as IDynamicQueryInternal;
if (iQuery.InternalOrderByItems != null)
{
sql += " ORDER BY ";
foreach (esOrderByItem orderByItem in iQuery.InternalOrderByItems)
{
bool literal = false;
sql += comma;
string columnName = orderByItem.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
{
sql += columnName.Substring(1, columnName.Length - 2);
if (orderByItem.Direction == esOrderByDirection.Unassigned)
{
literal = true; // They must provide the DESC/ASC in the literal string
}
}
else
{
sql += GetExpressionColumn(std, query, orderByItem.Expression, false, false);
}
if (!literal)
{
if (orderByItem.Direction == esOrderByDirection.Ascending)
{
sql += " ASC";
}
else
{
sql += " DESC";
}
}
comma = ",";
}
}
return(sql);
}
/// <summary>
/// Used to describe the "where" conditions of the join itself
/// </summary>
/// <param name="items"></param>
/// <returns></returns>
public esDynamicQuery On(params object[] items)
{
if (this.data.WhereItems == null)
{
this.data.WhereItems = new List <esComparison>();
}
foreach (object item in items)
{
esComparison wi = item as esComparison;
if (wi != null)
{
if (wi.data.WhereExpression != null)
{
foreach (esComparison exp in wi.data.WhereExpression)
{
esDynamicQuery q = exp.Value as esDynamicQuery;
if (q != null)
{
IDynamicQueryInternal iQ = q as IDynamicQueryInternal;
iQ.HookupProviderMetadata(q);
}
}
this.data.WhereItems.AddRange(wi.data.WhereExpression);
}
else
{
this.data.WhereItems.Add(wi);
}
esDynamicQuery query = wi.Value as esDynamicQuery;
if (query != null)
{
IDynamicQueryInternal iQ = query as IDynamicQueryInternal;
iQ.HookupProviderMetadata(query);
}
}
else
{
throw new Exception("Unsupported Join Syntax");
}
}
return(this.parentQuery);
}
private static string GetSubquerySearchCondition(esDynamicQuery query)
{
string searchCondition = String.Empty;
IDynamicQueryInternal iQuery = query as IDynamicQueryInternal;
switch (iQuery.SubquerySearchCondition)
{
case esSubquerySearchCondition.All: searchCondition = "ALL"; break;
case esSubquerySearchCondition.Any: searchCondition = "ANY"; break;
case esSubquerySearchCondition.Some: searchCondition = "SOME"; break;
}
return(searchCondition);
}
protected static string GetJoinStatement(StandardProviderParameters std, esDynamicQuery query)
{
string sql = String.Empty;
IDynamicQueryInternal iQuery = query as IDynamicQueryInternal;
if (iQuery.InternalJoinItems != null)
{
foreach (esJoinItem joinItem in iQuery.InternalJoinItems)
{
esJoinItem.esJoinItemData joinData = (esJoinItem.esJoinItemData)joinItem;
switch (joinData.JoinType)
{
case esJoinType.InnerJoin:
sql += " INNER JOIN ";
break;
case esJoinType.LeftJoin:
sql += " LEFT JOIN ";
break;
case esJoinType.RightJoin:
sql += " RIGHT JOIN ";
break;
case esJoinType.FullJoin:
sql += " FULL JOIN ";
break;
}
IDynamicQueryInternal iSubQuery = joinData.Query as IDynamicQueryInternal;
sql += Shared.CreateFullName(std.request, joinData.Query);
sql += " " + iSubQuery.JoinAlias + " ON ";
sql += GetComparisonStatement(std, query, joinData.WhereItems, String.Empty);
}
}
return(sql);
}
static public string CreateFullName(esDynamicQuery query)
{
IDynamicQueryInternal iQuery = query as IDynamicQueryInternal;
esProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as esProviderSpecificMetadata;
string name = String.Empty;
name += Delimiters.TableOpen;
if (query.querySource != null)
{
name += query.querySource;
}
else
{
name += providerMetadata.Destination;
}
name += Delimiters.TableClose;
return(name);
}
protected static string GetExpressionColumn(StandardProviderParameters std, esDynamicQuery query, esExpression expression, bool inExpression, bool useAlias)
{
string sql = String.Empty;
if (expression.CaseWhen != null)
{
return(GetCaseWhenThenEnd(std, query, expression.CaseWhen));
}
if (expression.HasMathmaticalExpression)
{
sql += GetMathmaticalExpressionColumn(std, query, expression.MathmaticalExpression);
}
else
{
sql += GetColumnName(expression.Column);
}
if (expression.SubOperators != null)
{
if (expression.Column.Distinct)
{
sql = BuildSubOperationsSql(std, "DISTINCT " + sql, expression.SubOperators);
}
else
{
sql = BuildSubOperationsSql(std, sql, expression.SubOperators);
}
}
if (!inExpression && useAlias)
{
if (expression.SubOperators != null || expression.Column.HasAlias)
{
sql += " AS " + Delimiters.StringOpen + expression.Column.Alias + Delimiters.StringClose;
}
}
return(sql);
}
public esCase When(esComparison comparison)
{
this.WhenItem = new esExpressionOrComparison();
this.WhenItem.Comparisons = new List <esComparison>();
if (comparison != null)
{
if (comparison.data.WhereExpression != null)
{
foreach (esComparison exp in comparison.data.WhereExpression)
{
esDynamicQuery q = exp.Value as esDynamicQuery;
if (q != null)
{
IDynamicQueryInternal iQ = q as IDynamicQueryInternal;
iQ.HookupProviderMetadata(q);
}
}
this.WhenItem.Comparisons.AddRange(comparison.data.WhereExpression);
}
else
{
this.WhenItem.Comparisons.Add(comparison);
}
esDynamicQuery query = comparison.Value as esDynamicQuery;
if (query != null)
{
IDynamicQueryInternal iQ = query as IDynamicQueryInternal;
iQ.HookupProviderMetadata(query);
}
}
return(this);
}
protected static string GetGroupByStatement(StandardProviderParameters std, esDynamicQuery query)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQueryInternal iQuery = query as IDynamicQueryInternal;
if (iQuery.InternalGroupByItems != null)
{
sql += " GROUP BY ";
foreach (esGroupByItem groupBy in iQuery.InternalGroupByItems)
{
sql += comma;
string columnName = groupBy.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
{
sql += columnName.Substring(1, columnName.Length - 2);
}
else
{
sql += GetExpressionColumn(std, query, groupBy.Expression, false, false);
}
comma = ",";
}
if (query.withRollup)
{
sql += " WITH ROLLUP";
}
}
return(sql);
}
void IEntityCollection.HookupQuery(esDynamicQuery query)
{
this.HookupQuery(query);
}
virtual protected void HookupQuery(esDynamicQuery query)
{
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((ADefHelpDeskUserRolesQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((ConstructorTestQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((EmployeeTerritoryQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((OrderDetailsQuery)query);
}
void IEntityCollection.HookupQuery(esDynamicQuery query)
{
this.HookupQuery(query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((CustomFieldsClientQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((ReferredEmployeeQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((SuppliersQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((CustomerGroupQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((StudentClassQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery(query as esTransaksiBKUQuery);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((CustomerDemographicsQuery)query);
}
protected virtual void HookupQuery(esDynamicQuery query)
{
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((MySqlUnicodeTestQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((FullNameViewQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((ADefHelpDeskVersionQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((vStoreEmailTemplateQuery)query);
}
protected override void HookupQuery(esDynamicQuery query)
{
this.InitQuery((CategoriesQuery)query);
}
protected bool OnQueryLoaded(esDynamicQuery Query, DataTable table)
{
this.table = table;
return true;
}
