Query Builder

// includes: sgcHTML_AI.hpp, sgcHTML_Component_QueryBuilder.hpp
oQB = new TsgcHTMLComponent_QueryBuilder(this);
oQB->ElementName = "orders";
TsgcHTMLQueryField *oField = oQB->Fields->Add();
oField->Name = "customer";
oField->Caption = "Customer";
oField = oQB->Fields->Add();
oField->Name = "amount";
oField->Caption = "Amount";
oField->DataType = aidtInteger;
oField->SQLName = "o.amount";
oQB->HTMXEngine = HTMXEngine1;
oQB->OnApply = QueryBuilderApply;
html = oQB->HTML;

void __fastcall TForm1::QueryBuilderApply(TObject *Sender,
  TsgcHTMLQueryNode* const aRules, UnicodeString &aResponse)
{
  TsgcHTMLComponent_QueryBuilder *oQB = (TsgcHTMLComponent_QueryBuilder *)Sender;
  TStringList *oParams = new TStringList();
  try
  {
    UnicodeString vWhere = oQB->GetSQLWhere(oParams, aRules);
    FDQuery1->Close();
    FDQuery1->SQL->Text = "select * from orders";
    if (vWhere != "")
      FDQuery1->SQL->Add("where " + vWhere);
    for (int i = 0; i < oParams->Count; i++)
      FDQuery1->Params->ParamByName(oParams->Names[i])->AsString =
        oParams->ValueFromIndex[i];
    FDQuery1->Open();
    DoSaveFilter(oQB->ToJSON(aRules));
    aResponse = DoOrdersHTML(FDQuery1);
  }
  __finally
  {
    delete oParams;
  }
}

using esegece.sgcWebSockets;

var qb = new TsgcHTMLComponent_QueryBuilder();
qb.ElementName = "orders";
var field = qb.Fields.Add();
field.Name = "customer";
field.Caption = "Customer";
field = qb.Fields.Add();
field.Name = "amount";
field.Caption = "Amount";
field.DataType = TsgcHTMLAIDataType.aidtInteger;
field.SQLName = "o.amount";
qb.HTMXEngine = htmxEngine;
qb.AIAssistant = aiAssistant;

// aRules is the tree of this request
qb.OnApply += (object sender, TsgcHTMLQueryNode aRules, ref string aResponse) =>
{
    var parameters = new List<string>();
    string where = qb.GetSQLWhere(parameters, aRules);
    using var cmd = connection.CreateCommand();
    cmd.CommandText = "select * from orders" + (where != "" ? " where " + where : "");
    foreach (var p in parameters)
    {
        var kv = p.Split(new[] { '=' }, 2);
        cmd.Parameters.AddWithValue(":" + kv[0], kv[1]);
    }
    DoSaveFilter(qb.ToJSON(aRules));
    aResponse = DoOrdersHTML(cmd.ExecuteReader());
};

string html = qb.HTML;