Библиотека для создания динамических SQL скриптов

Search_out(_путь : string): TConrolSearch;

tables_count := 2;tables[1] := 'uchet';tables[2] := 'ispol';

Param_.Count := 9;if (UchetCheck.Checked = true) and (IspolCheck.Checked = true) thenbegin  tables_count := 2;  tables[1] := 'uchet';  tables[2] := 'ispol';  Param_.Reference[1] := 1;  Param_.Reference[2] := 1;  Param_.Reference[3] := 1;  Param_.Reference[4] := 2;  Param_.Reference[5] := 1;  Param_.Reference[6] := 1;  Param_.Reference[8] := 2;  Param_.Reference[9] := 2;end;

unit Search_engine;interfaceconst  err0 = 'Search_out';  eq = '=';  min = '<';  max = '>';  eq_min = '=<';  eq_max = '>=';  min_max = '!=';  SEL = 'SELECT';  FROM = 'FROM ';  U = '''';  C = ',';  C_ = '.';  P = ' :PARAM_';  L = ' LIKE ';  A = ' AND ';  PP = '+';  PR = '%';  BTW = ' BETWEEN ';  N = ' !';  UP = 'upper';  ORD_ = 'ORDER BY ';  type PTypeList = 'A'..'Z';  type TFieldList = array [1..50] of string;  type EqTypeList = string[2];  type TControlSearch = record  SetByte: byte;  GetByteString: array [0..7] of string;end;type StrArr = array [1..50] of string[30];  RefArr = array [1..50] of integer;  PType = array [1..50] of PTypeList; // 'S' - string, 'D' - dateTime, 'N' - numeric, 'C' - vocabulary  EqType = array [1..50] of boolean;  TOrder = array [1..50] of integer;  ETL = array [1..50] of string[2];  Param = object  param_value : StrArr;  Field_Name : StrArr;  Reference : RefArr;  Count : integer;  param_name : string;  param_type : PType;  Equality : EqType;  Inverse : EqType; //Working only if Equality[i] = true  NumEq : ETL;  Order : TOrder;end;type Param_Result_ = object  param_value : StrArr;  Field_Name : StrArr;  Reference : RefArr;  Count : integer;  param_name : string;  param_type : PType;  Equality : EqType;  Inverse : EqType;  NumEq : ETL;  Order : TOrder;end;var  search_param_count, tables_count: integer;  Sql_File : Text;  tables : StrArr;  Param_ : Param;  Param_Result : Param_Result_;  ListField : TFieldList;  ListFieldCount : integer;  path_ : string;procedure Clear_Search;procedure SetOrder(o : integer;str : string);function Search_out(path : string) : TControlSearch;function Param_Set(NumParam: integer; FieldName: string; Ref: integer;  Equal: boolean; P_Type: char; P_Value: variant): TControlSearch;implementationuses  SysUtils;procedure Clear_Search;var  k: integer;begin  for k := 1 to 50 do    Param_.param_value[k] := '';end;function Search_out(path: string): TControlSearch;//Error Sectionconst  err1 = 'ZeroCtrlString';var  first_str : string;  i : integer;  table_str : string;  Result_param : StrArr;  CtrlString : string;  SELECT,  TMP_SELECT : string;  FieldCount : integer;  f_type : string;begin  i := 0;  Param_Result.Count := 0;  if ListFieldCount = 0 then    SELECT := 'SELECT* FROM '  else  begin    SELECT := SEL;    TMP_SELECT := '';    for FieldCount := 1 to ListFieldCount do    begin      if FieldCount = ListFieldCount then      begin        TMP_SELECT := TMP_SELECT + ' ' + ListField[FieldCount];        break;      end;      TMP_SELECT := TMP_SELECT + ' ' + ListField[FieldCount] + C;    end;    SELECT := SELECT + ' ' + TMP_SELECT + ' ' + FROM;  end;  repeat    inc(i);    if Param_.param_value[i] <> '' then    begin      inc(Param_Result.Count);      Param_Result.param_value[Param_Result.Count] := Param_.param_value[i];      CtrlString := CtrlString + Param_.param_value[i];      Param_Result.Field_Name[Param_Result.Count] := Param_.Field_Name[i];      Param_Result.Reference[Param_Result.Count] := Param_.Reference[i];      Param_Result.Param_type[Param_Result.Count] := Param_.Param_type[i];      Param_Result.Equality[Param_Result.Count] := Param_.Equality[i];      Param_Result.Inverse[Param_Result.Count] := Param_.Inverse[i];      Param_Result.NumEq[Param_Result.Count] := Param_.NumEq[i];    end;  until    i = Param_.Count;  // 1 BIT ERROR CHECK  if CtrlString = '' then  begin    Search_out.SetByte := 1;    Search_out.GetByteString[1] := Err0 + C_ + Err1;    AssignFile(Sql_File,path);    Rewrite(Sql_File);    writeln(Sql_File,SELECT+tables[1]);    CloseFile(Sql_file);    exit;  end  else  begin    Search_out.SetByte := 0;    Search_out.GetByteString[0] := '';  end;  i := 0;  AssignFile(Sql_File,path);  path_ := path;  Rewrite(Sql_File);  if tables_count > 1 then  begin    while i <> tables_count do    begin      inc(i);      if i = tables_count then        first_str := first_str + tables[i]      else        first_str := first_str + tables[i] + C;    end; //WHILE  end  else    first_str := tables[1];  first_str := SELECT + first_str;  writeln(Sql_File,first_str);  writeln(Sql_File,'WHERE');  i := 0;  {!MAIN REPEAT!}  repeat    inc(i);    table_str := tables[param_Result.Reference[i]];    Param_Result.param_name := Param_Result.param_value[i];    //СТРОКОВЫЙ ТИП    if (Param_Result.param_type[i] = 'S') then      if i < Param_Result.Count then      begin        if Param_Result.Equality[i] = false then          writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +          L + UP +'(' + U + Param_Result.param_name + PR + U +')' + A)        else        if Param_Result.Inverse[i] = false then          writeln(Sql_file,table_str + C_ + Param_Result.Field_Name[i] +          '='+U+ Param_Result.param_name+U+A)        else          writeln(Sql_file,table_str + C_ + Param_Result.Field_Name[i] +          N+'='+U+ Param_Result.param_name+U+A);      end      else      begin        if Param_Result.Equality[i] = false then          writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +          L + UP + '(' + U + Param_Result.param_name + PR + U + ')')        else        if Param_Result.Inverse[i] = false then          writeln(Sql_file,table_str + C_ + Param_Result.Field_Name[i] +          '='+U+ Param_Result.param_name+U)        else          writeln(Sql_file,table_str + C_ + Param_Result.Field_Name[i] +          N+'='+U+ Param_Result.param_name+U);      end;    // ТИП ДАТА    if (Param_Result.param_type[i] = 'D') then    begin      if i + 1 < Param_Result.Count then      begin        if (Param_Result.param_type[i+1] = 'D') and        (Param_Result.Reference[i] = Param_Result.Reference[i + 1]) then        begin          writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i]          + BTW + U+Param_Result.param_name +U+ ' ' + A +U +          Param_Result.param_value[i+1]+ U + ' '+ A);i := i + 1        end        else          writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i]          +'='+U+Param_Result.param_name +U+A);      end;      if (i + 1 = Param_Result.Count) and (Param_Result.param_type[i+1] <> 'D') then      begin        if (Param_Result.param_type[i+1] = 'D')        and (Param_Result.Reference[i] = Param_Result.Reference[i + 1]) then        begin          writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i]          + BTW + U+Param_Result.param_name +U+ ' ' + A +U +          Param_Result.param_value[i+1]+ U + ' '+ A);i := i + 1        end        else          writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i]          +'='+U+Param_Result.param_name +U+A);      end;      if (i + 1 = Param_Result.Count) and (Param_Result.param_type[i+1] = 'D') then      begin        if (Param_Result.param_type[i+1] = 'D') and        (Param_Result.Reference[i] = Param_Result.Reference[i + 1]) then        begin          writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i]          + BTW + U+Param_Result.param_name +U+ ' ' + A +U +          Param_Result.param_value[i+1]+ U + ' ');i := i + 1        end        else          writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i]          +'='+U+Param_Result.param_name +U);      end;    end;    // ТИП СЛОВАРЬ    if (Param_Result.param_type[i] = 'C') then      if i < Param_Result.Count then      begin        if Param_Result.Equality[i] = false then          writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +          L + Param_Result.param_name + PR + A)        else        if Param_Result.Inverse[i] = false then          writeln(Sql_file,table_str + C_ + Param_Result.Field_Name[i] +          '='+ Param_Result.param_name+A)        else          writeln(Sql_file,table_str + C_ + Param_Result.Field_Name[i] +          N+'='+ Param_Result.param_name+A);      end      else      begin        if Param_Result.Equality[i] = false then          writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +          L + Param_Result.param_name + PR )        else        if Param_Result.Inverse[i] = false then          writeln(Sql_file,table_str + C_ + Param_Result.Field_Name[i] +          '='+ Param_Result.param_name)        else          writeln(Sql_file,table_str + C_ + Param_Result.Field_Name[i] +          N+'='+ Param_Result.param_name);      end;      // ТИП ЧИСЛОВОЕ ЗНАЧЕНИЕ      if (Param_Result.param_type[i] = 'N') then        if i < Param_Result.Count then        begin          if Param_Result.NumEq[i] = eq then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            eq + Param_Result.param_name + A);          if Param_Result.NumEq[i] = min then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            min + Param_Result.param_name + A);          if Param_Result.NumEq[i] = max then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            max + Param_Result.param_name + A);          if Param_Result.NumEq[i] = eq_max then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            eq_max + Param_Result.param_name + A);          if Param_Result.NumEq[i] = eq_min then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            eq_min + Param_Result.param_name + A);          if Param_Result.NumEq[i] = min_max then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            min_max + Param_Result.param_name + A);        end        else        begin          if Param_Result.NumEq[i] = eq then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            eq + Param_Result.param_name);          if Param_Result.NumEq[i] = min then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            min + Param_Result.param_name);          if Param_Result.NumEq[i] = max then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            max + Param_Result.param_name);          if Param_Result.NumEq[i] = eq_max then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            eq_max + Param_Result.param_name);          if Param_Result.NumEq[i] = eq_min then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            eq_min + Param_Result.param_name);          if Param_Result.NumEq[i] = min_max then            writeln(Sql_File,table_str + C_ + Param_Result.Field_Name[i] +            min_max + Param_Result.param_name);        end;  {!MAIN REPEAT!}  until    i = Param_Result.Count;  CloseFile(Sql_File);  Clear_Search;end; // END FUNCTIONfunction Param_Set(NumParam: integer; FieldName: string; Ref: integer;  Equal: boolean; P_Type: char; P_Value: variant): TControlSearch;begin  Param_.Field_Name[NumParam] := FieldName;  Param_.Reference[NumParam] := Ref;  Param_.Equality[NumParam] := Equal;  Param_.param_type[NumParam] := P_Type;  Param_.param_value[NumParam] := P_value;end; //END FUNCTIONprocedure SetOrder(o: integer; str: string);var  t_str: string;begin  AssignFile(Sql_File,path_);  Append(Sql_File);  if str = 'N' then  begin    t_str := tables[param_.Reference[o]];    writeln(Sql_file,ORD_+t_str+'.'+Param_.Field_Name[o]);    Close(Sql_File);  end  else  begin    writeln(Sql_file,ORD_+' '+str);    Close(Sql_File);  end;end; // END PROCEDUREend.