unit u_sql_buscar;

{$mode delphi}

interface

uses
  Classes, SysUtils, Math;

function construirFiltroBusquedaTexto(strbuscar: string; campos: TStrings): string;

implementation

function strtr(s: string; busca_chars_str, remplaza_chars_str: string): string;
var
  res: string;
  k, i: integer;
  c: char;
  m: integer;
begin
  m := length(remplaza_chars_str);
  res := s;
  for k := 1 to length(s) do
  begin
    c := s[k];
    i := min(pos(c, busca_chars_str), m);
    if i > 0 then
      res[k] := remplaza_chars_str[i];
  end;
  Result := res;
end;

function str_replace(OldPattern, NewPattern, S: string): string;
begin
  Result := StringReplace(S, OldPattern, NewPattern, [rfReplaceAll]);
end;

function normalizarPalabra(palabra: string): string;
var
  strbx: string;
begin
  strbx := strtr(palabra, '+"''',
    '   ');
  (*
  strbx := strtr(palabra, 'ñâêîôûàèìòùäëïöüáéíóúÁÉÍÓÚ+"''',
    'ÑaeiouaeiouaeiouaeiouAEIOU   ');
  strbx := str_replace('A', '[ÁA]', strbx);
  strbx := str_replace('E', '[ÉE]', strbx);
  strbx := str_replace('I', '[ÍI]', strbx);
  strbx := str_replace('O', '[ÓO]', strbx);
  strbx := str_replace('U', '[ÚU]', strbx);
  strbx := str_replace('a', '[áa]', strbx);
  strbx := str_replace('e', '[ée]', strbx);
  strbx := str_replace('i', '[íi]', strbx);
  strbx := str_replace('o', '[óo]', strbx);
  strbx := str_replace('u', '[úu]', strbx);
  strbx := str_replace('Ñ', '[nNñÑ]', strbx);
  *)
  strbx := str_replace(' ', '', strbx);
  strbx := LowerCase(strbx);
  Result := strbx;
end;



function explode(sep: string; S: string): TStringList;
var
  res: TStringList;
  r: string;
  i, n: integer;
  pal: string;
  filtroBuscar: string;

begin
  res := TStringList.Create;
  n := length(sep);
  r := trim(S);
  while r <> '' do
  begin
    i := pos(sep, r);
    if i > 0 then
    begin
      if i > 1 then
      begin
        pal := trim(copy(r, 1, i - 1));
        if pal <> '' then
          res.add(pal);
      end;
      Delete(r, 1, i - 1 + n);
      r := trim(r);
    end
    else
    begin
      res.add(r);
      r := '';
    end;
  end;
  result:= res;
end;

function construirFiltroBusquedaTexto(strbuscar: string; campos: TStrings): string;

var
  nc: integer;
  strb, strbx: string;
  pals: TStringList;
  npals: integer;
  ipal: integer;
  ucs: string;
  pal: string;
  uc: char;
  filtroBuscar: string;
  pongaAND, pongaOR: boolean;
  ic: integer;
begin
  nc := campos.Count;

  //  strbuscar= stripslashes(trim($strbuscar));
  strbuscar := trim(strbuscar);
  if ((nc = 0) or (strbuscar = '')) then
  begin
    Result := '';
    exit;
  end;

  strbx := strbuscar;
  strbuscar := str_replace('"', '', strbuscar);
  strbx := strtr(strbx, ',.*+''', '     ');
  strb := trim(strbx);
  pals := explode(' ', strb);
  npals := pals.Count;

  // manejo de las palabras de búsqueda.
  ipal := 0;
  while (ipal < npals) do
  begin
    pal := pals[ipal];
    if (length(pal) < 1) then
    begin
      Dec(npals);
      pals.Delete(ipal);
    end
    else
    begin
      pal := normalizarPalabra(pal);
      pals[ipal] :=  AnsiToUtf8( pal );
      Inc(ipal);
    end;
  end;


  //---------------------
  // armado del filtro
  //--------------
  filtroBuscar := '';
  if (npals > 0) then
  begin
    pongaAND := False;
    for ipal := 0 to npals - 1 do
    begin
      pal := pals[ipal];
      if length(pal) > 1 then
      begin
        if (pongaAND) then
          filtroBuscar := filtroBuscar + ' AND'
        else
          pongaAND := True;

        filtroBuscar := filtroBuscar + ' (';
        pongaOR := False;
        for  ic := 0 to nc - 1 do
        begin
          if pongaOR then
            filtroBuscar := filtroBuscar + ' OR '
          else
            pongaOR := True;

          // ojo la palabra (REGEXP) es para mysql, para Postgress hay que poner (~*) otra cosa.
          filtroBuscar := filtroBuscar + '( LOWER(' + campos[ic] + ') ~* ''.*'+pal+'.*'')';
//          filtroBuscar := filtroBuscar + '(' + campos[ic] + ' REGEXP ''.*'+pal+'.*'')';
        end;
      end;
      filtroBuscar := filtroBuscar + ') ';
    end;
  end;
  Result := filtroBuscar;
end;

end.
