public ConditionEC(String tableName, String nameCondition, String EventsSupport)
{
InitializeComponent();
TableName = tableName;
if (TablesNamesListBinding == null)
{
TablesNamesListBinding = new BindingList <string>
{
tableName
};
}
if (RowsCharge == null)
{
RowsCharge = new List <ItemconditionModel>();
}
QueryStructure = "{SelecCondition} ";
TxtQueryResult.Multiline = true;
TxtQueryResult.ScrollBars = ScrollBars.Vertical;
TxtQueryResult.AcceptsTab = true;
TxtQueryResult.AcceptsReturn = true;
TxtQueryResult.WordWrap = true;
TxtQueryResult.Height = 200;
TxtQueryResult.Text = QueryStructure.Replace("{SelecCondition}", "SELECT * FROM " + TablesNamesListBinding[0] + " WHERE ");
LblTableNAme.Text = tableName;
LblNotificationName.Text = nameCondition;
LblEventNotification.Text = EventsSupport;
}
protected void AddPagingClause(StringBuilder sb, QueryBuildInfo buildInfo, QueryStructure structure)
{
long pageSize = structure.Fetch;
long currentOffset = structure.Skip;
if (currentOffset > 0 && pageSize == 0)
{
pageSize = long.MaxValue;
}
if (pageSize > 0)
{
sb.Append(" LIMIT ? ");
QueryExecParam param = new QueryExecParam();
param.Index = buildInfo.ExecInfo.Params.Count;
param.Type = ColumnType.Long;
param.Value = pageSize;
buildInfo.ExecInfo.Params.Add(param);
}
if (currentOffset > 0)
{
sb.Append(" OFFSET ? ");
QueryExecParam param = new QueryExecParam();
param.Index = buildInfo.ExecInfo.Params.Count;
param.Type = ColumnType.Long;
param.Value = currentOffset;
buildInfo.ExecInfo.Params.Add(param);
}
}
protected override PagedResult <IMedia> PerformSearch(QueryStructure query)
{
if (query.Lucene.IsNullOrWhiteSpace())
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
var result =
SearchProvider.Search(
SearchProvider.CreateSearchCriteria().RawQuery(query.Lucene),
query.PageSize);
var paged = result.Skip(GetSkipSize(query.PageIndex, query.PageSize)).ToArray();
//TODO: We really need to make a model mapper from search result to IMedia, for now well just go lookup that content :(
if (paged.Any())
{
var foundContent = MediaService.GetByIds(paged.Select(x => x.Id)).WhereNotNull();
return(new PagedResult <IMedia>(result.TotalItemCount, query.PageIndex + 1, query.PageSize)
{
Items = foundContent
});
}
return(new PagedResult <IMedia>(result.TotalItemCount, query.PageIndex + 1, query.PageSize)
{
Items = Enumerable.Empty <IMedia>()
});
}
/// <summary>
/// Binds the model to a value by using the specified controller context and binding context.
/// </summary>
/// <returns>
/// true if model binding is successful; otherwise, false.
/// </returns>
/// <param name="actionContext">The action context.</param><param name="bindingContext">The binding context.</param>
public bool BindModel(HttpActionContext actionContext, ModelBindingContext bindingContext)
{
var queryStructure = new QueryStructure();
var query = actionContext.Request.GetQueryNameValuePairs().ToArray();
int pageSize;
if (query.Any(x => x.Key.InvariantEquals("pageSize")) && int.TryParse(query.Single(x => x.Key.InvariantEquals("pageSize")).Value, out pageSize))
{
queryStructure.PageSize = pageSize;
}
int pageIndex;
if (query.Any(x => x.Key.InvariantEquals("pageIndex")) && int.TryParse(query.Single(x => x.Key.InvariantEquals("pageIndex")).Value, out pageIndex))
{
queryStructure.PageIndex = pageIndex;
}
if (query.Any(x => x.Key.InvariantEquals("lucene")))
{
queryStructure.Lucene = query.Single(x => x.Key.InvariantEquals("lucene")).Value;
}
bindingContext.Model = queryStructure;
return(true);
}
public void ChangeValueItem(String Identifier, String value, String conector)
{
Boolean hastLasRowtValue = true;
int postition = 0;
Boolean hastValueCurrentQuery = false;
hastValueCurrentQuery = RowsCharge.FirstOrDefault(r => r.Identifier == Identifier) != null;
if (hastValueCurrentQuery)
{
RowsCharge.FirstOrDefault(r => r.Identifier == Identifier).Conector = conector;
postition = RowsCharge.IndexOf(RowsCharge.FirstOrDefault(r => r.Identifier == Identifier));
}
if (postition > 0)
{
hastLasRowtValue = RowsCharge[postition - 1].ValueQuery != null;
}
if (RowsCharge.FirstOrDefault(v => v.LastValueQuery.Contains(value)) == null && hastLasRowtValue && hastValueCurrentQuery)
{
if (postition == 0)
{
RowsCharge[postition].ValueQuery = value;
QueryStructure = QueryStructure.Replace(RowsCharge[postition].LastValueQuery, value);
RowsCharge[postition].LastValueQuery = value;
}
else
{
RowsCharge[postition].ValueQuery = " " + RowsCharge[postition - 1].Conector + " " + value;
QueryStructure = QueryStructure.Replace(RowsCharge[postition].LastValueQuery, " " + RowsCharge[postition - 1].Conector + " " + value);
RowsCharge[postition].LastValueQuery = " " + RowsCharge[postition - 1].Conector + " " + value;
}
}
else if (RowsCharge[postition].LastValueQuery != null && TableDelete != null)
{
Boolean hasContains = false;
foreach (String item in TableDelete)
{
hasContains = RowsCharge[postition].LastValueQuery.Contains(item) || RowsCharge[postition].LastValueQuery.Contains(" {" + RowsCharge[postition].Identifier + "}");
}
if (hasContains)
{
QueryStructure = QueryStructure.Replace(RowsCharge[postition].LastValueQuery, " {" + RowsCharge[postition].Identifier + "}");
}
}
if (RowsCharge.Count - 1 > postition && RowsCharge[postition + 1].ValueQuery != null)
{
RowsCharge[postition + 1].ValueQuery = " " + RowsCharge[postition].Conector + RowsCharge[postition + 1].ValueQuery.Replace("OR ", "").Replace("AND ", "");
QueryStructure = QueryStructure.Replace(RowsCharge[postition + 1].LastValueQuery, RowsCharge[postition + 1].ValueQuery);
RowsCharge[postition + 1].LastValueQuery = RowsCharge[postition + 1].ValueQuery;
}
SetTextInterface();
}
public override void workWithElements()
{
QueryStructure query = new QueryStructure();
query.TableIdName = "OpinionDocuments";
query.TableField = "Html";
query.TableField = "OpinionDocumentId";
query.QueryWhere = "OpinionDocuments.Date<'01-01-1700'";
GetElementFromDB(query);
}
/// <summary>
/// Get the elements from Db based on our parameters
/// </summary>
/// <param name="values"></param>
public void GetElementFromDB(QueryStructure values)
{
using (var context = new CaseLaw.Data.CaseLawContext())
{
var results = context.Database.SqlQuery(typeof(GottenItem),
"select @tableColumn from @tableName where @queryWhere",
new SqlParameter("@tableName", values.TableName),
new SqlParameter("@tableName", values.TableName),
new SqlParameter("@tableName", values.TableName));
}
}
public HttpResponseMessage Search(
[ModelBinder(typeof(QueryStructureModelBinder))]
QueryStructure query)
{
var result = PerformSearch(query);
return(result == null
? Request.CreateResponse(HttpStatusCode.NotImplemented)
: Request.CreateResponse(HttpStatusCode.OK, CreatePagedContentRepresentation(
result,
LinkTemplate.Search,
new { lucene = query.Lucene })));
}
private void ProcessJoin(StringBuilder sb, QueryBuildInfo buildInfo, QueryStructure structure)
{
var joinList = structure.JoinList;
if (joinList.Count == 0)
{
return;
}
foreach (var join in joinList)
{
sb.Append(" ");
sb.Append(CreateJoinSql(join, buildInfo));
}
}
private void ProcessFrom(StringBuilder sb, QueryBuildInfo buildInfo, QueryStructure structure)
{
sb.Append(" FROM ");
ICollection <IQueryFrom> fromList = structure.FromList;
bool initial = true;
foreach (IQueryFrom from in fromList)
{
if (!initial)
{
sb.Append(",");
}
sb.Append(CreateFromSql(from, buildInfo));
initial = false;
}
}
public QueryBuildInfo ProcessQuery(QueryBuildInfo buildInfo, QueryStructure structure)
{
buildInfo = new QueryBuildInfo(buildInfo);
buildInfo.CurrentQueryId = structure.QueryId;
StringBuilder sb = new StringBuilder();
ProcessFrom(sb, buildInfo, structure);
ProcessJoin(sb, buildInfo, structure);
ProcessWhere(sb, buildInfo, structure);
ProcessGroup(sb, buildInfo, structure);
ProcessGroupCondition(sb, buildInfo, structure);
ProcessOrderBy(sb, buildInfo, structure);
AddPagingClause(sb, buildInfo, structure);
ProcessSelection(sb, buildInfo, structure);
buildInfo.ExecInfo.Sql = FixUpQuery(sb.ToString());
return(buildInfo);
}
protected override PagedResult <IPublishedContent> PerformSearch(QueryStructure query)
{
if (query.Lucene.IsNullOrWhiteSpace())
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
//TODO: This would be more efficient if we went straight to the ExamineManager and used it's built in Skip method
// but then we have to write our own model mappers and don't have time for that right now.
var result = Umbraco.ContentQuery.TypedSearch(SearchProvider.CreateSearchCriteria().RawQuery(query.Lucene), SearchProvider)
.ToArray();
var paged = result.Skip(GetSkipSize(query.PageIndex, query.PageSize)).Take(query.PageSize);
return(new PagedResult <IPublishedContent>(result.Length, query.PageIndex + 1, query.PageSize)
{
Items = paged
});
}
private void BtnDeleteCondition_Click(object sender, EventArgs e)
{
int postition = 0;
List <ItemconditionModel> listDelete = RowsCharge.Where(r => r.IsCheked).ToList();
if (listDelete != null)
{
foreach (ItemconditionModel item in listDelete)
{
RowsCharge.Remove(item);
QueryStructure = QueryStructure.Replace(item.LastValueQuery, "");
postition = PnlConditions.Controls.IndexOf(item.Intance);
PnlConditions.Controls.Remove(item.Intance);
if (postition == 0 && item.ValueQuery != null)
{
QueryStructure = QueryStructure.Replace(item.LastValueQuery, item.ValueQuery?.Replace(item.Conector, ""));
}
ReajustAling();
SetTextInterface();
}
listDelete.Clear();
}
}
private void ProcessWhere(StringBuilder sb, QueryBuildInfo buildInfo, QueryStructure structure)
{
ICollection <IQueryCondition> conditionList = structure.ConditionList;
if (conditionList.Count == 0)
{
return;
}
sb.Append(" WHERE ");
bool initial = true;
foreach (IQueryCondition condition in conditionList)
{
if (!initial)
{
sb.Append(" AND ");
}
sb.Append(CreateWhereSql(condition, buildInfo));
initial = false;
}
}
private void ProcessGroup(StringBuilder sb, QueryBuildInfo buildInfo, QueryStructure structure)
{
ICollection <IQueryGroup> groupList = structure.GroupList;
if (groupList.Count == 0)
{
return;
}
sb.Append(" GROUP BY ");
bool initial = true;
foreach (IQueryGroup group in groupList)
{
if (!initial)
{
sb.Append(",");
}
sb.Append(CreateGroupSql(group, buildInfo));
initial = false;
}
}
private void ProcessOrderBy(StringBuilder sb, QueryBuildInfo buildInfo, QueryStructure structure)
{
ICollection <IQueryOrderBy> orderList = structure.OrderList;
if (orderList.Count == 0)
{
return;
}
sb.Append(" ORDER BY ");
bool initial = true;
foreach (IQueryOrderBy orderBy in orderList)
{
if (!initial)
{
sb.Append(",");
}
sb.Append(CreateOrderBySql(orderBy, buildInfo));
initial = false;
}
}
protected virtual PagedResult <TEntity> PerformSearch(QueryStructure query)
{
return(null);
}
private void ProcessSelection(StringBuilder querySb, QueryBuildInfo buildInfo, QueryStructure structure)
{
var selectionSb = new StringBuilder();
selectionSb.Append("SELECT ");
if (structure.SelectList.Count == 0)
{
selectionSb.Append(" * ");
}
if (structure.Distinct)
{
selectionSb.Append(" DISTINCT ");
}
ICollection <IQuerySelection> selections = structure.SelectList;
bool initial = true;
foreach (IQuerySelection selection in selections)
{
if (!initial)
{
selectionSb.Append(",");
}
selectionSb.Append(CreateSelectionSql(selection, buildInfo));
initial = false;
}
querySb.Insert(0, selectionSb.ToString());
}
public Query()
{
Structure = new QueryStructure();
}
private void ProcessGroupCondition(StringBuilder sb, QueryBuildInfo buildInfo, QueryStructure structure)
{
ICollection <IQueryGroupCondition> groupConditionList = structure.GroupConditionList;
if (groupConditionList.Count == 0)
{
return;
}
sb.Append(" HAVING ");
bool initial = true;
foreach (IQueryGroupCondition groupCondition in groupConditionList)
{
if (!initial)
{
sb.Append(" AND ");
}
sb.Append(CreateGroupConditionSql(groupCondition, buildInfo));
initial = false;
}
}
