unit MotohrDb;

interface
uses
  HyperStr,
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Buttons, StdCtrls, ComCtrls, ExtCtrls, ImgList,
  DB, FIBDataSet, pFIBDataSet, FIBDatabase, pFIBDatabase,
  FIBQuery, pFIBQuery, SIBEABase, SIBFIBEA;

type
  TCallbackLoadCounterTypes = procedure(data: pointer; id: Integer; descr: string; timer: Boolean);
  TCallbackLoadMachs        = procedure(data: pointer; id: Integer; name, descr: string);
  TCallbackLoadCounters     = procedure(data: pointer; ctnIdx, machId: Integer);

  procedure connectMotohrDb;
  procedure disconnectMotohrDb;
  procedure setDisconnectTimerActive(value: boolean);
  function getMotohrDb: TpFIBDatabase;

  procedure startTransaction(modeRW: boolean = false);
  procedure commitTransaction;
  procedure rollbackTransaction;

  procedure loadCounterTypes(data: pointer; callback: TCallbackLoadCounterTypes);
  procedure loadMachs(data: pointer; callback: TCallbackLoadMachs);
  procedure loadCounters(data: pointer; cntIdx, ctId: Integer; callback: TCallbackLoadCounters); overload;
  procedure loadCounters(sl: TStringList); overload;
  function insertCounterType(descr: string; timer: boolean): integer;
  procedure updateCounterType(id: integer; descr: string; timer: boolean);
  procedure deleteOtherCounterTypes(sids: string);
  procedure insertCounter(machId, ctId: integer);
  procedure deleteCounter(machId, ctId: integer);
  function insertLogbookRecord(dt: TDateTime; ctDescr, machName, text, fio: string; oldSec, newSec: integer): integer; overload;
  function insertLogbookRecord(dt: TDateTime; machName, text, fio: string): integer; overload;

  function loadNote(name: string; var text: string; var dt: TDateTime): integer;
  function saveNote(name: string; text: string): integer;


implementation
uses
  Utils,
  DbUtils,
  Motohr,
  DataMod,
  InfoDlg;

var
  db: TpFIBDatabase;
  trn: TpFIBTransaction;
  qry: TpFIBQuery;
  timer: TDisconnectTimer;


procedure connectMotohrDb;
begin
  if getMotohrDBName = '' then begin
    raise Exception.Create(
          'Не указана база данных моточасов!'#13#13+
          'Установите значение параметра: '#13+
          'Администрирование/Переменные/База данных моточасов');
  end;

  if db = nil then begin
    db := TpFIBDatabase.Create(dm);
    db.DBParams.Text := dm.pFIBDbMes.DBParams.Text;
    db.DBName := getMotohrDBName;

    trn := TpFIBTransaction.Create(dm);
    trn.DefaultDatabase := db;

    qry := TpFIBQuery.Create(dm);
    qry.Database := db;
    qry.Transaction := trn;

    timer := dm.createDisconnectTimer(db);
  end;

  if not db.Connected then
    db.Open;

  timer.update;
end;

procedure setDisconnectTimerActive(value: boolean);
begin
  if timer <> nil then
    timer.setActive(value); 
end;

function getMotohrDb: TpFIBDatabase;
begin
  result := db;
end;


procedure startTransaction(modeRW: boolean);
begin
  connectMotohrDb;
  if modeRW then
    trn.TRParams.Text := dm.trnWriteMes.TRParams.Text
  else
    trn.TRParams.Text := dm.trnReadMes.TRParams.Text;

  qry.Transaction.StartTransaction;
end;


procedure commitTransaction;
begin
  qry.Transaction.Commit;
end;


procedure rollbackTransaction;
begin
  try
    if (qry <> nil) and qry.Transaction.Active then begin
      qry.Transaction.Rollback;
      qry.Transaction.Active := false;
    end;  
  except
  end;
end;


procedure disconnectMotohrDb;
begin
  if db <> nil then begin
    try
      db.Close;
    except
    end;
  end;
end;


procedure loadCounterTypes(data: pointer; callback: TCallbackLoadCounterTypes);
begin
  qry.Close;
  qry.SQL.Text := 'select id, descr, timer from mh_counter_type where deleted=0';
  qry.ExecQuery;
  while not qry.Eof do begin
    callback(data,
          qry.Fields[0].AsInteger,
          qry.Fields[1].AsString,
          qry.Fields[2].AsInteger = 1
    );
    qry.Next;
  end;
end;


procedure loadMachs(data: pointer; callback: TCallbackLoadMachs);
begin
  qry.Close;
  qry.SQL.Text := 'select id, name, descr from mh_mach';
  qry.ExecQuery;
  while not qry.Eof do begin
    callback(data,
          qry.Fields[0].AsInteger,
          qry.Fields[1].AsString,
          qry.Fields[2].AsString
    );
    qry.Next;
  end;
end;


procedure loadCounters(data: pointer; cntIdx, ctId: Integer; callback: TCallbackLoadCounters);
begin
  qry.Close;
  qry.SQL.Text := 'select mach_id from mh_counter where deleted=0 and counter_type_id=' + IntToStr(ctId);
  qry.ExecQuery;
  while not qry.Eof do begin
    callback(data, cntIdx, qry.Fields[0].AsInteger);
    qry.Next;
  end;
end;

procedure loadCounters(sl: TStringList);
begin
  qry.Close;
  qry.SQL.Text := 'select mach_id, counter_type_id from mh_counter where deleted=0';
  qry.ExecQuery;
  while not qry.Eof do begin
    sl.add( IntToStrPair(qry.Fields[0].AsInteger, qry.Fields[1].AsInteger));
    qry.Next;
  end;
  sl.Sort;
end;


function insertCounterType(descr: string; timer: boolean): integer;
begin
  qry.Close;
  qry.SQL.Text := Format(
        'update or insert into mh_counter_type (descr, timer, deleted) '+
        'values(''%s'', %d, 0) matching (descr) returning id', [
              descr,
              Integer(timer)
        ]);

  qry.ExecQuery;
  Result := qry.Fields[0].AsInteger;
end;


procedure updateCounterType(id: integer; descr: string; timer: boolean);
begin
  qry.Close;
  qry.SQL.Text := Format('update mh_counter_type set descr=''%s'', timer=%d, deleted=0 where id=%d', [
        descr,
        Integer(timer),
        id
  ]);
  qry.ExecQuery;
end;


procedure deleteOtherCounterTypes(sids: string);
begin
  qry.Close;
  qry.SQL.Text := Format('update mh_counter_type set deleted=1 where id not in (%s)', [
        sids
  ]);
  qry.ExecQuery;
end;


procedure insertCounter(machId, ctId: integer);
begin
  qry.Close;
  qry.SQL.Text := Format(
        'update or insert into mh_counter (mach_id, counter_type_id, deleted) '+
        'values(%d, %d, 0) matching (mach_id, counter_type_id)', [
              machId,
              ctId
        ]);

  qry.ExecQuery;
end;


procedure deleteCounter(machId, ctId: integer);
begin
  qry.Close;
  qry.SQL.Text := Format('update mh_counter set deleted=1 where mach_id=%d and counter_type_id=%d', [
        machId,
        ctId
  ]);
  qry.ExecQuery;
end;


function insertLogbookRecord(dt: TDateTime; ctDescr, machName, text, fio: string; oldSec, newSec: integer): integer;
  function secToStr(sec: integer): string;
  begin
    Result := iif(sec = COUNTER_DISABLED, 'Откл', FloatToStrF(sec / SEC_IN_HOUR, ffFixed, 18, 1));
  end;
begin
  text := Trim(text);
  text := Format('УСТ "%s" = %s (было %s)%s%s', [
        getCounterTypeDescrLong(ctDescr),
        secToStr(newSec),
        secToStr(oldSec),
        iif(text = '', '', ',  '),
        text
  ]);

  result := insertLogbookRecord(dt, machName, text, fio);
end;


function insertLogbookRecord(dt: TDateTime; machName, text, fio: string): integer;
begin
  qry.Close;
  qry.SQL.Text :=
        'insert into mh_logbook (mach_id, dt, text, fio) values '+
        '((select id from mh_mach where name=:name), :dt, :text, :fio) '+
        'returning id';

  qry.ParamByName('name').AsString := machName;
  qry.ParamByName('dt').AsDateTime := dt;
  qry.ParamByName('text').AsString := text;
  qry.ParamByName('fio').AsString := fio;

  qry.ExecQuery;
  Result := qry.Fields[0].AsInteger;
end;


function loadNote(name: string; var text: string; var dt: TDateTime): integer;
begin
  text := '';
  dt := now;
  
  try
    startTransaction();
    qry.SQL.Text := Format('select id, text, dt from mh_note where name = ''%s''', [name]);
    qry.ExecQuery;
    if not qry.Eof then begin
      result := qry.Fields[0].AsInteger;
      text := qry.Fields[1].AsString;
      dt := qry.Fields[2].AsDateTime;
    end;
    commitTransaction;
  except
    rollbackTransaction;
  end;
end;

function saveNote(name: string; text: string): integer;
begin
  result := 0;
  try
    startTransaction();
    qry.SQL.Text := 'update or insert into mh_note(name, text, dt) values (:name, :text, :dt) matching (name) returning id';
    qry.ParamByName('name').AsString := name;       
    qry.ParamByName('text').AsString := text;
    qry.ParamByName('dt').AsDateTime := now;
    qry.ExecQuery;
    result := qry.Fields[0].AsInteger;
    commitTransaction;
  except
    rollbackTransaction;
  end;
end;

end.
