unit UMisc;

interface

uses IBCustomDataSet, StrUtils, SysUtils, Classes, IBDataBase, DBCtrls;

type
  TSQLQueryIndex = (SelectIndex, DeleteIndex, InsertIndex, ModifyIndex,
    RefreshIndex);
  TSQLElements = (FormatString, FirstQueryPart, LastQueryPart);
  TSQLComplexQueryIndex = (ComplexSelectIndex, ComplexRefreshIndex);

  procedure DeleteAmp(var AString: string);
  procedure DeleteEndComma(var AString: string);
  function ReplaceDash(AString: string): string;
  function NormalizeStr(AString: string): string;
  procedure InitStrings(const AStrings: TStrings);

  function MakeCaptionFromTable(const ATableName: string): string;
  function MakeCaptionForSchedule(const ATableName: string): string;

  function GetRelatedRefIndex(ARefIndex: integer): integer;
  function MakeQueryStr(
    AQueryIndex: TSQLQueryIndex; ARefIndex: integer): string;
  function MakeComplexQueryStr(
    AQueryIndex: TSQLComplexQueryIndex; ARefIndex: integer): string;

  function GetNeededRefIndex(AField: string; ARefIndex: integer): integer;
  function MakeCustomSelect(AField: string; ARefIndex: integer): string;
  procedure SetLookupFields(var ABox: TDBLookupCombobox;
    const AFieldName: string);

  procedure ReOpenDataSet(DSet: TIBDataSet);

  function MakeIdField(const AIndex: integer): string;
  function GetRefName(const AId: integer): string;

const
  EM_DASH = #151;

const
  REF_COUNT = 9;
  REFERENCES: array[1..REF_COUNT] of string =
    ('auditoriums', 'days', 'disciplines', 'groups', 'lecturers', 'lessons',
     'lecturers_disciplines', 'groups_disciplines', 'schedule');

  SCHEDULE_INDEX = 9;

  CM_REF_COUNT = 3;
  COMPLEX_REFERENCES = [7, 8, 9];

  REF_INDEX: array[boolean, 1..REF_COUNT] of byte =
    ((1, 2, 3, 4, 5, 6, 0, 0, 0),
     (7, 8, 9, 0, 0, 0, 0, 0, 0));

  MAX_FIELD_COUNT = 6;
  REF_FIELDS: array[1..REF_COUNT, 1..MAX_FIELD_COUNT] of string =
    (('name', '', '', '', '', ''),
     ('number', 'name', '', '', '', ''),
     ('name', '', '', '', '', ''),
     ('name', 'quantity', '', '', '', ''),
     ('name', '', '', '', '', ''),
     ('number', 'name', '', '', '', ''),
     ('lecturer_id', 'discipline_id', '', '', '', ''),
     ('group_id', 'discipline_id', '', '', '', ''),
     ('day_id', 'lesson_id', 'group_id', 'discipline_id', 'lecturer_id',
      'auditorium_id'));

  CM_REF_FK_ALIAS: array[1..CM_REF_COUNT, 1..REF_COUNT] of string =
    (('', '', 'discipline', '', 'lecturer', '', '', '', ''),
     ('', '', 'discipline', 'group', '', '', '', '', ''),
     ('auditorium', 'day', 'discipline', 'group', 'lecturer', 'lesson', '', '', ''));

  CM_REF_FK_IDX: array[1..CM_REF_COUNT, 1..REF_COUNT] of byte =
    ((0, 0, 2, 0, 1, 0, 0, 0, 0),
     (0, 0, 2, 1, 0, 0, 0, 0, 0),
     (6, 1, 4, 3, 5, 2, 0, 0, 0));

  CM_REF_FK_NAMES: array[0..5] of string =
    ('Day', 'Lesson', 'Group', 'Discipline', 'Lecturer', 'Auditorium');

const
  SELECT_TEMPLATE_START = 'select id, ';
  SELECT_TEMPLATE_END = ' from %s';
  DELETE_TEMPLATE = 'delete from %s where id = :id';
  INSERT_TEMPLATE_START = 'insert into %s values((gen_id(%0:s_id_gen, 1)),';
  INSERT_TEMPLATE_END = ')';
  MODIFY_TEMPLATE_START = 'update %s set ';
  MODIFY_TEMPLATE_END = ' where id = :old_id';
  REFRESH_TEMPLATE_START = SELECT_TEMPLATE_START;
  REFRESH_TEMPLATE_END = SELECT_TEMPLATE_END + ' where id = :id';

  TEMPLATES: array[TSQLQueryIndex, TSQLElements] of string =
    (('%s,',         SELECT_TEMPLATE_START,  SELECT_TEMPLATE_END),
     ('',            DELETE_TEMPLATE,        ''),
     (':%s,',        INSERT_TEMPLATE_START,  INSERT_TEMPLATE_END),
     ('%s = :%0:s,', MODIFY_TEMPLATE_START,  MODIFY_TEMPLATE_END),
     ('%s,',         REFRESH_TEMPLATE_START, REFRESH_TEMPLATE_END));

const
  FK_SELECT = 'select %s.id, %s from %0:s %2:s';
  FK_REFRESH = FK_SELECT + ' where %0:s.id = :id';

  FK_TEMPLATES: array[TSQLComplexQueryIndex] of string =
    (FK_SELECT, FK_REFRESH);

  START_FMT_TEMPLATE = '%s.name as "%s",';
  END_FMT_TEMPLATE = 'inner join %s on %0:s.id = %s.%s_id ';

implementation

procedure DeleteAmp(var AString: string);
begin
  Delete(AString, Pos('&', AString), 1);
end;

procedure DeleteEndComma(var AString: string);
begin
  if AnsiEndsStr(',', AString) then Delete(AString, Length(AString), 1);
end;

function ReplaceDash(AString: string): string;
begin
  Result := AnsiReplaceStr(AString, '-', EM_DASH);
end;

function NormalizeStr(AString: string): string;
var
  i: integer;
begin
  Result := AnsiReplaceStr(AnsiLowerCase(AString), '_', ' ');
  Result := AnsiReplaceStr(Result, '"', '');
  for i := 1 to Length(Result) do
    if (i = 1) or (Result[i - 1] = ' ') then Result[i] := UpCase(Result[i]);
end;

procedure InitStrings(const AStrings: TStrings);
begin
  AStrings.Clear;
  AStrings.Add('');
end;

function MakeCaptionFromTable(const ATableName: string): string;
begin
  Result := NormalizeStr(ATableName);
  Result := ReplaceDash(AnsiReplaceStr(Result, ' ', ' - '));
end;

function MakeCaptionForSchedule(const ATableName: string): string;
begin
  Result := MakeCaptionFromTable(ATableName);
  Delete(Result, Length(Result), 1);
end;

function GetRelatedRefIndex(ARefIndex: integer): integer;
var
  i: integer;
  isComplexReference: boolean;
begin
  Result := 0;
  isComplexReference := ARefIndex in COMPLEX_REFERENCES;
  for i := 1 to REF_COUNT do
    if REF_INDEX[isComplexReference, i] = ARefIndex then begin
      Result := i;
      Exit;
    end;
end;

function MakeQueryStr(AQueryIndex: TSQLQueryIndex; ARefIndex: integer): string;
var
  i: integer;
  fieldName: string;
begin
  for i := 1 to MAX_FIELD_COUNT do begin
    fieldName := REF_FIELDS[ARefIndex, i];
    if fieldName <> '' then
      Result := Result + Format(TEMPLATES[AQueryIndex, FormatString],
        [fieldName]);
  end;
  DeleteEndComma(Result);
  Result :=
    Format(TEMPLATES[AQueryIndex, FirstQueryPart], [REFERENCES[ARefIndex]]) +
    Result +
    Format(TEMPLATES[AQueryIndex, LastQueryPart], [REFERENCES[ARefIndex]]);
end;

function MakeComplexQueryStr(
  AQueryIndex: TSQLComplexQueryIndex; ARefIndex: integer): string;
var
  endStr: string;
  i, foreignIndex, fieldIndex: integer;
  fieldList: array[1..REF_COUNT] of string;
begin
  Result := '';
  endStr := '';
  foreignIndex := GetRelatedRefIndex(ARefIndex);
  for i := 1 to REF_COUNT do begin
    if CM_REF_FK_ALIAS[foreignIndex, i] <> '' then begin
      fieldIndex := CM_REF_FK_IDX[foreignIndex, i];
      fieldList[fieldIndex] := Format(START_FMT_TEMPLATE,
        [REFERENCES[i], CM_REF_FK_ALIAS[foreignIndex, i]]);
      endStr := endStr + Format(END_FMT_TEMPLATE,
        [REFERENCES[i], REFERENCES[ARefIndex], CM_REF_FK_ALIAS[foreignIndex, i]]);
    end;
  end;
  for i := 1 to REF_COUNT do
    if fieldList[i] <> '' then Result := Result + fieldList[i];
  DeleteEndComma(Result);
  Result := Format(FK_TEMPLATES[AQueryIndex],
    [REFERENCES[ARefIndex], Result, endStr]);
end;

function GetNeededRefIndex(AField: string; ARefIndex: integer): integer;
begin
  Result := AnsiIndexText(
    AField, CM_REF_FK_ALIAS[GetRelatedRefIndex(ARefIndex)]) + 1;
end;

function MakeCustomSelect(AField: string; ARefIndex: integer): string;
var
  neededRefIndex: integer;
begin
  neededRefIndex := GetNeededRefIndex(AField, ARefIndex);
  Result := MakeQueryStr(SelectIndex, neededRefIndex);
end;

procedure SetLookupFields(
  var ABox: TDBLookupCombobox; const AFieldName: string);
const
  NAME = 'name';
  KEY = 'id';
begin
  ABox.DataField := Format('%s_%s', [AFieldName, KEY]);
  ABox.ListField := NAME;
  ABox.KeyField := KEY;
end;

procedure ReOpenDataSet(DSet: TIBDataSet);
begin
  DSet.Close;
  DSet.Open;
end;

function MakeIdField(const AIndex: integer): string;
begin
  Result := REFERENCES[AIndex];
  Delete(Result, Length(Result), 1);
  Result := Format('%s_id', [Result]);
end;

function GetRefName(const AId: integer): string;
begin
  Result := NormalizeStr(CM_REF_FK_ALIAS[3, AId]);
end;

end.