unit Api.ServiceImpl;

interface

uses
  XData.Server.Module,
  XData.Service.Common,
  System.Variants,
  System.DateUtils,
  Api.Service,
  Api.Database,
  Common.Logging,
  System.SysUtils, JSON, System.IniFiles;

type
  [ServiceImplementation]
  TApiService = class(TInterfacedObject, IApiService)
  strict private
    apiDB: TApiDatabase;
  private
    function BuildTaskNumber: string;
    function BuildTaskTitle(const taskNumber, projectName, subject: string): string;
    function AddTaskRow(taskId: string; insertAfterItemNum: Integer): Boolean;
    function SaveTaskRow(Item: TTaskRowSave): Boolean;
    function TestApi(messageText: string): TJSONObject;
    function GetWebClientVersion: string;
    procedure MoveTaskRow(const taskId: Integer; const taskItemId: Integer; const newItemNum: Integer);
    function DeleteTaskRow(const taskId, taskItemId: Integer): Boolean;
  public
    procedure AfterConstruction; override;
    procedure BeforeDestruction; override;
    function GetTaskItems(taskId: string): TTaskItemsResponse;
  end;

implementation

procedure TApiService.AfterConstruction;
begin
  inherited;
  apiDB := TApiDatabase.Create(nil);
  Logger.Log(4, 'ApiService.AfterConstruction - apiDB created');
end;

procedure TApiService.BeforeDestruction;
begin
  Logger.Log(4, 'ApiService.BeforeDestruction - freeing apiDB');
  apiDB.Free;
  inherited;
end;


function TApiService.GetTaskItems(taskId: string): TTaskItemsResponse;
var
  taskHeader: TTaskHeader;
  taskUserOption: TTaskUserOption;
  taskItemCode: TTaskItemCode;
  item: TTaskItem;
begin
  Logger.Log(4, Format('ApiService.GetTaskItems - TASK_ID="%s"', [taskId]));

  Result := TTaskItemsResponse.Create;
  TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);

  try
    apiDB.uqTaskHeader.Close;
    apiDB.uqTaskHeader.ParamByName('TASK_ID').AsString := taskId;
    apiDB.uqTaskHeader.Open;

    if apiDB.uqTaskHeader.IsEmpty then
    begin
      Logger.Log(2, Format('ApiService.GetTaskItems - no task header found for TASK_ID="%s"', [taskId]));
      Result.count := 0;
      Exit;
    end;

    taskHeader := TTaskHeader.Create;
    TXDataOperationContext.Current.Handler.ManagedObjects.Add(taskHeader);

    taskHeader.taskId := apiDB.uqTaskHeaderTASK_ID.AsString;
    taskHeader.parentId := apiDB.uqTaskHeaderPARENT_ID.AsString;
    taskHeader.taskNumber := BuildTaskNumber;
    taskHeader.projectName := apiDB.uqTaskHeaderPROJECT_NAME.AsString;
    taskHeader.subject := apiDB.uqTaskHeaderSUBJECT.AsString;
    taskHeader.title := BuildTaskTitle(
      taskHeader.taskNumber,
      taskHeader.projectName,
      taskHeader.subject
    );

    Result.task := taskHeader;

    apiDB.uqTaskItemUsers.Close;
    apiDB.uqTaskItemUsers.ParamByName('TASK_ID').AsString := taskId;
    apiDB.uqTaskItemUsers.Open;

    while not apiDB.uqTaskItemUsers.Eof do
    begin
      taskUserOption := TTaskUserOption.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(taskUserOption);

      taskUserOption.name := apiDB.uqTaskItemUsersNAME.AsString;

      if SameText(apiDB.uqTaskItemUsersUSER_TYPE.AsString, 'Reported') then
        Result.reportedByOptions.Add(taskUserOption)
      else if SameText(apiDB.uqTaskItemUsersUSER_TYPE.AsString, 'Assigned') then
        Result.assignedToOptions.Add(taskUserOption);

      apiDB.uqTaskItemUsers.Next;
    end;

    apiDB.uqTaskItemCodes.Close;
    apiDB.uqTaskItemCodes.Open;

    while not apiDB.uqTaskItemCodes.Eof do
    begin
      taskItemCode := TTaskItemCode.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(taskItemCode);

      taskItemCode.code := apiDB.uqTaskItemCodesCODE.AsString;
      taskItemCode.codeDesc := apiDB.uqTaskItemCodesCODE_DESC.AsString;
      taskItemCode.codeType := apiDB.uqTaskItemCodesCODE_TYPE.AsString;

      Result.statusOptions.Add(taskItemCode);

      apiDB.uqTaskItemCodes.Next;
    end;

    apiDB.uqTaskItems.Close;
    apiDB.uqTaskItems.ParamByName('TASK_ID').AsString := taskId;
    apiDB.uqTaskItems.Open;

    while not apiDB.uqTaskItems.Eof do
    begin
      item := TTaskItem.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(item);

      item.taskItemId := apiDB.uqTaskItemsTASK_ITEM_ID.AsInteger;
      item.taskId := apiDB.uqTaskItemsTASK_ID.AsString;
      item.itemNum := apiDB.uqTaskItemsITEM_NUM.AsInteger;

      item.application := apiDB.uqTaskItemsAPPLICATION.AsString;
      item.version := apiDB.uqTaskItemsAPP_VERSION.AsString;

      if apiDB.uqTaskItemsTASK_DATE.IsNull then
        item.taskDate := 0
      else
        item.taskDate := apiDB.uqTaskItemsTASK_DATE.AsDateTime;

      item.reportedBy := apiDB.uqTaskItemsREPORTED_BY.AsString;
      item.assignedTo := apiDB.uqTaskItemsASSIGNED_TO.AsString;
      item.status := apiDB.uqTaskItemsSTATUS.AsString;

      if apiDB.uqTaskItemsSTATUS_DATE.IsNull then
        item.statusDate := Null
      else
        item.statusDate := apiDB.uqTaskItemsSTATUS_DATE.AsDateTime;

      item.fixedVersion := apiDB.uqTaskItemsFIXED_VERSION.AsString;
      item.formSection := apiDB.uqTaskItemsFORM_SECTION.AsString;
      item.issue := apiDB.uqTaskItemsISSUE.AsString;
      item.notes := apiDB.uqTaskItemsNOTES.AsString;

      Result.items.Add(item);

      apiDB.uqTaskItems.Next;
    end;

    Result.count := Result.items.Count;
    Logger.Log(4, Format('ApiService.GetTaskItems - returned %d item(s)', [Result.count]));
  except
    on E: Exception do
    begin
      Logger.Log(2, 'ApiService.GetTaskItems - ERROR: ' + E.Message);
      raise;
    end;
  end;
end;


function TApiService.AddTaskRow(taskId: string; insertAfterItemNum: Integer): Boolean;
var
  newItemNum: Integer;
  maxItemNum: Integer;
begin
  Logger.Log(4, Format('ApiService.AddTaskRow - TASK_ID="%s" INSERT_AFTER_ITEM_NUM="%d"', [taskId, insertAfterItemNum]));

  apiDB.uqGetTaskMaxItemNum.Close;
  apiDB.uqGetTaskMaxItemNum.ParamByName('TASK_ID').AsString := taskId;
  apiDB.uqGetTaskMaxItemNum.Open;
  maxItemNum := apiDB.uqGetTaskMaxItemNumMAX_ITEM_NUM.AsInteger;

  if insertAfterItemNum < 0 then
    insertAfterItemNum := 0;

  if insertAfterItemNum > maxItemNum then
    insertAfterItemNum := maxItemNum;

  if insertAfterItemNum = 0 then
    newItemNum := maxItemNum + 1
  else
    newItemNum := insertAfterItemNum + 1;

  apiDB.uqAddTaskRow.Connection.StartTransaction;
  try
    if insertAfterItemNum > 0 then
    begin
      apiDB.uqShiftTaskRowsForInsert.Close;
      apiDB.uqShiftTaskRowsForInsert.ParamByName('TASK_ID').AsString := taskId;
      apiDB.uqShiftTaskRowsForInsert.ParamByName('INSERT_AFTER_ITEM_NUM').AsInteger := insertAfterItemNum;
      apiDB.uqShiftTaskRowsForInsert.ExecSQL;
    end;

    apiDB.uqAddTaskRow.Close;
    apiDB.uqAddTaskRow.ParamByName('TASK_ID').AsString := taskId;
    apiDB.uqAddTaskRow.ParamByName('ITEM_NUM').AsInteger := newItemNum;
    apiDB.uqAddTaskRow.ExecSQL;

    apiDB.uqAddTaskRow.Connection.Commit;

    Result := True;
    Logger.Log(4, Format('ApiService.AddTaskRow - OK TASK_ID="%s" ITEM_NUM="%d"', [taskId, newItemNum]));
  except
    on E: Exception do
    begin
      if apiDB.uqAddTaskRow.Connection.InTransaction then
        apiDB.uqAddTaskRow.Connection.Rollback;

      Logger.Log(2, 'ApiService.AddTaskRow - ERROR: ' + E.Message);
      raise;
    end;
  end;
end;


procedure TApiService.MoveTaskRow(const taskId: Integer; const taskItemId: Integer; const newItemNum: Integer);
var
  oldItemNum: Integer;
  maxItemNum: Integer;
  finalItemNum: Integer;
  tempItemNum: Integer;
begin
  logger.log(4, 'MoveTaskRow start TASK_ID=' + IntToStr(taskId) +
    ' TASK_ITEM_ID=' + IntToStr(taskItemId) +
    ' NEW_ITEM_NUM=' + IntToStr(newItemNum));

  apiDB.uqGetTaskRowPosition.Close;
  apiDB.uqGetTaskRowPosition.ParamByName('TASK_ID').AsInteger := taskId;
  apiDB.uqGetTaskRowPosition.ParamByName('TASK_ITEM_ID').AsInteger := taskItemId;
  apiDB.uqGetTaskRowPosition.Open;

  if apiDB.uqGetTaskRowPosition.IsEmpty then
  begin
    logger.log(2, 'MoveTaskRow row not found TASK_ID=' + IntToStr(taskId) +
      ' TASK_ITEM_ID=' + IntToStr(taskItemId));
    raise Exception.Create('Task row not found.');
  end;

  oldItemNum := apiDB.uqGetTaskRowPositionITEM_NUM.AsInteger;

  apiDB.uqGetTaskMaxItemNum.Close;
  apiDB.uqGetTaskMaxItemNum.ParamByName('TASK_ID').AsInteger := taskId;
  apiDB.uqGetTaskMaxItemNum.Open;

  maxItemNum := apiDB.uqGetTaskMaxItemNumMAX_ITEM_NUM.AsInteger;

  finalItemNum := newItemNum;

  if finalItemNum < 1 then
    finalItemNum := 1;

  if finalItemNum > maxItemNum then
    finalItemNum := maxItemNum;

  if finalItemNum = oldItemNum then
  begin
    logger.log(4, 'MoveTaskRow no move needed TASK_ID=' + IntToStr(taskId) +
      ' TASK_ITEM_ID=' + IntToStr(taskItemId) +
      ' ITEM_NUM=' + IntToStr(oldItemNum));
    Exit;
  end;

  tempItemNum := -1;

  apiDB.uqGetTaskRowPosition.Connection.StartTransaction;
  try
    apiDB.uqMoveTaskRowTemp.ParamByName('TEMP_ITEM_NUM').AsInteger := tempItemNum;
    apiDB.uqMoveTaskRowTemp.ParamByName('TASK_ID').AsInteger := taskId;
    apiDB.uqMoveTaskRowTemp.ParamByName('TASK_ITEM_ID').AsInteger := taskItemId;
    apiDB.uqMoveTaskRowTemp.ExecSQL;

    if finalItemNum < oldItemNum then
    begin
      apiDB.uqShiftTaskRowsDown.ParamByName('TASK_ID').AsInteger := taskId;
      apiDB.uqShiftTaskRowsDown.ParamByName('NEW_ITEM_NUM').AsInteger := finalItemNum;
      apiDB.uqShiftTaskRowsDown.ParamByName('OLD_ITEM_NUM').AsInteger := oldItemNum;
      apiDB.uqShiftTaskRowsDown.ExecSQL;
    end
    else
    begin
      apiDB.uqShiftTaskRowsUp.ParamByName('TASK_ID').AsInteger := taskId;
      apiDB.uqShiftTaskRowsUp.ParamByName('OLD_ITEM_NUM').AsInteger := oldItemNum;
      apiDB.uqShiftTaskRowsUp.ParamByName('NEW_ITEM_NUM').AsInteger := finalItemNum;
      apiDB.uqShiftTaskRowsUp.ExecSQL;
    end;

    apiDB.uqSetTaskRowFinalPosition.ParamByName('TASK_ID').AsInteger := taskId;
    apiDB.uqSetTaskRowFinalPosition.ParamByName('TASK_ITEM_ID').AsInteger := taskItemId;
    apiDB.uqSetTaskRowFinalPosition.ParamByName('NEW_ITEM_NUM').AsInteger := finalItemNum;
    apiDB.uqSetTaskRowFinalPosition.ExecSQL;

    apiDB.uqGetTaskRowPosition.Connection.Commit;

    logger.log(4, 'MoveTaskRow success TASK_ID=' + IntToStr(taskId) +
      ' TASK_ITEM_ID=' + IntToStr(taskItemId) +
      ' OLD_ITEM_NUM=' + IntToStr(oldItemNum) +
      ' NEW_ITEM_NUM=' + IntToStr(finalItemNum));
  except
    on E: Exception do
    begin
      if apiDB.uqGetTaskRowPosition.Connection.InTransaction then
        apiDB.uqGetTaskRowPosition.Connection.Rollback;

      logger.log(2, 'MoveTaskRow failed TASK_ID=' + IntToStr(taskId) +
        ' TASK_ITEM_ID=' + IntToStr(taskItemId) +
        ' ERROR=' + E.Message);
      raise;
    end;
  end;
end;


function TApiService.BuildTaskNumber: string;

  procedure AddPart(const value: string);
  var
    s: string;
  begin
    s := Trim(value);
    if s = '' then
      Exit;

    if Result = '' then
      Result := s
    else
      Result := Result + '.' + s;
  end;

begin
  Result := '';

  AddPart(apiDB.uqTaskHeaderTASK_NUM_1.AsString);
  AddPart(apiDB.uqTaskHeaderTASK_NUM_2.AsString);
  AddPart(apiDB.uqTaskHeaderTASK_NUM_3.AsString);
  AddPart(apiDB.uqTaskHeaderTASK_NUM_4.AsString);
  AddPart(apiDB.uqTaskHeaderTASK_NUM_5.AsString);
  AddPart(apiDB.uqTaskHeaderTASK_NUM_6.AsString);
end;


function TApiService.BuildTaskTitle(const taskNumber, projectName, subject: string): string;
begin
  Result := 'Task';

  if Trim(taskNumber) <> '' then
    Result := Result + ' - ' + Trim(taskNumber);

  if Trim(projectName) <> '' then
    Result := Result + ' | ' + Trim(projectName);

  if Trim(subject) <> '' then
    Result := Result + ' | ' + Trim(subject);
end;


function TApiService.SaveTaskRow(Item: TTaskRowSave): Boolean;

  function ParseDateOrZero(const S: string; out D: TDateTime): Boolean;
  begin
    Result := Trim(S) <> '';
    if Result then
      D := ISO8601ToDate(S, False);
  end;

var
  d: TDateTime;
begin
  Logger.Log(4, Format('ApiService.SaveTaskRow - TASK_ITEM_ID="%d"', [Item.taskItemId]));

  try
    apiDB.uqSaveTaskRow.Close;

    apiDB.uqSaveTaskRow.ParamByName('TASK_ITEM_ID').AsInteger := Item.taskItemId;

    apiDB.uqSaveTaskRow.ParamByName('APPLICATION').AsString := Item.application;
    apiDB.uqSaveTaskRow.ParamByName('APP_VERSION').AsString := Item.version;

    if ParseDateOrZero(Item.taskDate, d) then
      apiDB.uqSaveTaskRow.ParamByName('TASK_DATE').AsDateTime := d
    else
      apiDB.uqSaveTaskRow.ParamByName('TASK_DATE').Clear;

    apiDB.uqSaveTaskRow.ParamByName('REPORTED_BY').AsString := Item.reportedBy;
    apiDB.uqSaveTaskRow.ParamByName('ASSIGNED_TO').AsString := Item.assignedTo;

    apiDB.uqSaveTaskRow.ParamByName('STATUS').AsString := Item.status;

    if ParseDateOrZero(Item.statusDate, d) then
      apiDB.uqSaveTaskRow.ParamByName('STATUS_DATE').AsDateTime := d
    else
      apiDB.uqSaveTaskRow.ParamByName('STATUS_DATE').AsDateTime := Date;

    apiDB.uqSaveTaskRow.ParamByName('FIXED_VERSION').AsString := Item.fixedVersion;
    apiDB.uqSaveTaskRow.ParamByName('FORM_SECTION').AsString := Item.formSection;
    apiDB.uqSaveTaskRow.ParamByName('ISSUE').AsString := Item.issue;
    apiDB.uqSaveTaskRow.ParamByName('NOTES').AsString := Item.notes;

    apiDB.uqSaveTaskRow.ExecSQL;

    Result := True;
    Logger.Log(4, 'ApiService.SaveTaskRow - OK');
  except
    on E: Exception do
    begin
      Logger.Log(2, 'ApiService.SaveTaskRow - ERROR: ' + E.Message);
      raise;
    end;
  end;
end;


function TApiService.DeleteTaskRow(const taskId: Integer; const taskItemId: Integer): Boolean;
var
  oldItemNum: Integer;
begin
  Logger.Log(4, Format('ApiService.DeleteTaskRow - TASK_ID="%d" TASK_ITEM_ID="%d"', [taskId, taskItemId]));

  apiDB.uqGetTaskRowPosition.Close;
  apiDB.uqGetTaskRowPosition.ParamByName('TASK_ID').AsInteger := taskId;
  apiDB.uqGetTaskRowPosition.ParamByName('TASK_ITEM_ID').AsInteger := taskItemId;
  apiDB.uqGetTaskRowPosition.Open;

  if apiDB.uqGetTaskRowPosition.IsEmpty then
  begin
    Logger.Log(2, Format('ApiService.DeleteTaskRow - row not found TASK_ID="%d" TASK_ITEM_ID="%d"', [taskId, taskItemId]));
    raise Exception.Create('Task row not found.');
  end;

  oldItemNum := apiDB.uqGetTaskRowPositionITEM_NUM.AsInteger;

  apiDB.uqGetTaskRowPosition.Connection.StartTransaction;
  try
    apiDB.uqDeleteTaskRow.ParamByName('TASK_ID').AsInteger := taskId;
    apiDB.uqDeleteTaskRow.ParamByName('TASK_ITEM_ID').AsInteger := taskItemId;
    apiDB.uqDeleteTaskRow.ExecSQL;

    apiDB.uqShiftTaskRowsAfterDelete.ParamByName('TASK_ID').AsInteger := taskId;
    apiDB.uqShiftTaskRowsAfterDelete.ParamByName('OLD_ITEM_NUM').AsInteger := oldItemNum;
    apiDB.uqShiftTaskRowsAfterDelete.ExecSQL;

    apiDB.uqGetTaskRowPosition.Connection.Commit;

    Result := True;
    Logger.Log(4, Format('ApiService.DeleteTaskRow - OK TASK_ID="%d" TASK_ITEM_ID="%d" OLD_ITEM_NUM="%d"', [taskId, taskItemId, oldItemNum]));
  except
    on E: Exception do
    begin
      if apiDB.uqGetTaskRowPosition.Connection.InTransaction then
        apiDB.uqGetTaskRowPosition.Connection.Rollback;

      Logger.Log(2, Format('ApiService.DeleteTaskRow - ERROR TASK_ID="%d" TASK_ITEM_ID="%d" MSG="%s"', [taskId, taskItemId, E.Message]));
      raise;
    end;
  end;
end;


function TApiService.TestApi(messageText: string): TJSONObject;
var
  requiredVersion: string;
begin
  Logger.Log(3, 'IApiService.TestApi called');

  Result := TJSONObject.Create;
  TXDataOperationContext.Current.Handler.ManagedObjects.Add(Result);

  requiredVersion := GetWebClientVersion;

  Result.AddPair('messageEcho', messageText);
  Result.AddPair('serverTime', DateTimeToStr(Now));
  Result.AddPair('requiredWebClientVersion', requiredVersion);
  Result.AddPair('note', 'If this endpoint is reachable, JWT auth passed. Version enforcement on every API call is a separate step (middleware).');
end;


function TApiService.GetWebClientVersion: string;
var
  iniFile: TIniFile;
begin
  iniFile := TIniFile.Create(ChangeFileExt(ParamStr(0), '.ini'));
  try
    Result := iniFile.ReadString('Settings', 'webClientVersion', '');
  finally
    iniFile.Free;
  end;
end;

initialization
  RegisterServiceType(TypeInfo(IApiService));
  RegisterServiceType(TApiService);

end.

