unit TimeEntry.ServiceImpl;

interface

uses
  XData.Service.Common,
  XData.Server.Module,
  TimeEntry.Service,
  Api.Database,
  Uni;

type
  [ServiceImplementation]
  TTimeEntryService = class(TInterfacedObject, ITimeEntryService)
  strict private
    apiDB: TApiDatabase;

    procedure AfterConstruction; override;
    procedure BeforeDestruction; override;

    function ParseIsoDate(value: string): TDateTime;
    function FieldText(AQuery: TUniQuery; AFieldName: string): string;
    function BuildTaskNumber(AQuery: TUniQuery): string;
    function BuildTaskDisplay(AQuery: TUniQuery): string;

    procedure LoadUserName(AResponse: TTimeEntriesResponse; userId: string);
    procedure LoadTimeEntryItems(AResponse: TTimeEntriesResponse; userId: string; startDateValue, exclusiveEndDateValue: TDateTime);
    procedure LoadTaskOptions(AResponse: TTimeEntriesResponse; userId: string; startDateValue, exclusiveEndDateValue: TDateTime);
    procedure LoadCategoryOptions(AResponse: TTimeEntriesResponse);
    function GetNextIdValue(const AKeyName: string): string;
  public
    function GetTimeEntries(userId, startDate, endDate: string): TTimeEntriesResponse;
    function AddTimeEntry(userId, taskDate: string): string;
  end;

implementation

uses
  System.SysUtils,
  System.DateUtils,
  Common.Logging;

procedure TTimeEntryService.AfterConstruction;
begin
  inherited;
  apiDB := TApiDatabase.Create(nil);
end;


procedure TTimeEntryService.BeforeDestruction;
begin
  apiDB.Free;
  inherited;
end;


function TTimeEntryService.ParseIsoDate(value: string): TDateTime;
var
  yearValue: Integer;
  monthValue: Integer;
  dayValue: Integer;
begin
  yearValue := StrToInt(Copy(value, 1, 4));
  monthValue := StrToInt(Copy(value, 6, 2));
  dayValue := StrToInt(Copy(value, 9, 2));
  Result := EncodeDate(yearValue, monthValue, dayValue);
end;


function TTimeEntryService.FieldText(AQuery: TUniQuery; AFieldName: string): string;
begin
  if AQuery.FieldByName(AFieldName).IsNull then
    Result := ''
  else
    Result := Trim(AQuery.FieldByName(AFieldName).AsString);
end;


function TTimeEntryService.BuildTaskNumber(AQuery: TUniQuery): string;

  procedure AddPart(AValue: string);
  begin
    if AValue = '' then
      Exit;

    if Result <> '' then
      Result := Result + '.';

    Result := Result + AValue;
  end;

begin
  Result := '';

  AddPart(FieldText(AQuery, 'TASK_NUM_1'));
  AddPart(FieldText(AQuery, 'TASK_NUM_2'));
  AddPart(FieldText(AQuery, 'TASK_NUM_3'));
  AddPart(FieldText(AQuery, 'TASK_NUM_4'));
  AddPart(FieldText(AQuery, 'TASK_NUM_5'));
  AddPart(FieldText(AQuery, 'TASK_NUM_6'));
end;


function TTimeEntryService.BuildTaskDisplay(AQuery: TUniQuery): string;
var
  customerText: string;
  projectText: string;
  taskNumberText: string;
  subjectText: string;
  leftText: string;
  taskText: string;
begin
  customerText := FieldText(AQuery, 'CUSTOMER_SHORT_NAME');
  projectText := FieldText(AQuery, 'PROJECT_NAME');
  taskNumberText := BuildTaskNumber(AQuery);
  subjectText := FieldText(AQuery, 'TASK_SUBJECT');

  leftText := customerText;

  if projectText <> '' then
  begin
    if leftText <> '' then
      leftText := leftText + ' - ';

    leftText := leftText + projectText;
  end;

  taskText := taskNumberText;

  if subjectText <> '' then
  begin
    if taskText <> '' then
      taskText := taskText + ' - ';

    taskText := taskText + subjectText;
  end;

  Result := leftText;

  if taskText <> '' then
  begin
    if Result <> '' then
      Result := Result + ' | ';

    Result := Result + taskText;
  end;
end;


procedure TTimeEntryService.LoadUserName(AResponse: TTimeEntriesResponse; userId: string);
var
  userQuery: TUniQuery;
begin
  userQuery := TUniQuery.Create(nil);
  try
    userQuery.Connection := apiDB.ucETaskApi;
    userQuery.SQL.Text :=
      'select USER_ID, NAME, USER_NAME ' +
      'from users ' +
      'where USER_ID = :USER_ID';

    userQuery.ParamByName('USER_ID').AsString := userId;
    userQuery.Open;

    AResponse.userName := FieldText(userQuery, 'NAME');
    if AResponse.userName = '' then
      AResponse.userName := FieldText(userQuery, 'USER_NAME');
  finally
    userQuery.Free;
  end;
end;


procedure TTimeEntryService.LoadTimeEntryItems(AResponse: TTimeEntriesResponse; userId: string; startDateValue, exclusiveEndDateValue: TDateTime);
var
  itemsQuery: TUniQuery;
  item: TTimeEntry;
begin
  itemsQuery := TUniQuery.Create(nil);
  try
    itemsQuery.Connection := apiDB.ucETaskApi;
    itemsQuery.SQL.Text :=
      'select ' +
      '  ti.ENTRY_ID, ' +
      '  ti.TASK_DATE, ' +
      '  ti.TASK_ID, ' +
      '  ti.HOURS, ' +
      '  ti.TASK_TIME, ' +
      '  ti.CATEGORY, ' +
      '  cat.CODE_DESC as CATEGORY_DESC, ' +
      '  ti.SUMMARY, ' +
      '  c.SHORT_NAME as CUSTOMER_SHORT_NAME, ' +
      '  p.NAME as PROJECT_NAME, ' +
      '  t.TASK_NUM_1, ' +
      '  t.TASK_NUM_2, ' +
      '  t.TASK_NUM_3, ' +
      '  t.TASK_NUM_4, ' +
      '  t.TASK_NUM_5, ' +
      '  t.TASK_NUM_6, ' +
      '  t.SUBJECT as TASK_SUBJECT ' +
      'from time_items ti ' +
      'left join tasks t on t.TASK_ID = ti.TASK_ID ' +
      'left join project p on p.PROJECT_ID = t.PROJECT_ID ' +
      'left join customers c on c.CUSTOMER_ID = p.CUSTOMER_ID ' +
      'left join codes cat on cat.CATEGORY = ''TASK'' and cat.CODE_TYPE = ''CATGRY'' and cat.CODE = ti.CATEGORY ' +
      'where ti.USER_ID = :USER_ID ' +
      '  and ti.TASK_DATE >= :START_DATE ' +
      '  and ti.TASK_DATE < :END_DATE ' +
      'order by ti.TASK_DATE, ti.ENTRY_ID';

    itemsQuery.ParamByName('USER_ID').AsString := userId;
    itemsQuery.ParamByName('START_DATE').AsDateTime := startDateValue;
    itemsQuery.ParamByName('END_DATE').AsDateTime := exclusiveEndDateValue;
    itemsQuery.Open;

    while not itemsQuery.Eof do
    begin
      item := TTimeEntry.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(item);

      item.entryId := itemsQuery.FieldByName('ENTRY_ID').AsInteger;

      if itemsQuery.FieldByName('TASK_DATE').IsNull then
        item.taskDate := ''
      else
        item.taskDate := FormatDateTime('yyyy-mm-dd', itemsQuery.FieldByName('TASK_DATE').AsDateTime);

      item.taskId := FieldText(itemsQuery, 'TASK_ID');
      item.taskDisplay := BuildTaskDisplay(itemsQuery);

      if not itemsQuery.FieldByName('HOURS').IsNull then
        item.hours := itemsQuery.FieldByName('HOURS').AsFloat;

      item.taskTime := FieldText(itemsQuery, 'TASK_TIME');

      item.category := FieldText(itemsQuery, 'CATEGORY');
      item.categoryDesc := FieldText(itemsQuery, 'CATEGORY_DESC');

      if item.categoryDesc = '' then
        item.categoryDesc := item.category;

      item.summary := FieldText(itemsQuery, 'SUMMARY');

      AResponse.items.Add(item);
      itemsQuery.Next;
    end;
  finally
    itemsQuery.Free;
  end;
end;


procedure TTimeEntryService.LoadTaskOptions(AResponse: TTimeEntriesResponse; userId: string; startDateValue, exclusiveEndDateValue: TDateTime);
var
  tasksQuery: TUniQuery;
  taskOption: TTimeEntryTaskOption;
begin
  tasksQuery := TUniQuery.Create(nil);
  try
    tasksQuery.Connection := apiDB.ucETaskApi;
    tasksQuery.SQL.Text :=
      'select distinct ' +
      '  t.TASK_ID, ' +
      '  c.SHORT_NAME as CUSTOMER_SHORT_NAME, ' +
      '  p.NAME as PROJECT_NAME, ' +
      '  t.TASK_NUM_1, ' +
      '  t.TASK_NUM_2, ' +
      '  t.TASK_NUM_3, ' +
      '  t.TASK_NUM_4, ' +
      '  t.TASK_NUM_5, ' +
      '  t.TASK_NUM_6, ' +
      '  t.SUBJECT as TASK_SUBJECT ' +
      'from tasks t ' +
      'join project p on p.PROJECT_ID = t.PROJECT_ID ' +
      'join customers c on c.CUSTOMER_ID = p.CUSTOMER_ID ' +
      'where ( ' +
      '  ( ' +
      '    t.ASSIGNED_TO = :USER_ID ' +
      '    and t.FOCUS = ''T'' ' +
      '    and (t.START_DATE <= :ACTIVE_DATE or t.START_DATE is null) ' +
      '    and (t.COMPLETION_DATE >= :ACTIVE_DATE or t.COMPLETION_DATE is null) ' +
      '  ) ' +
      '  or t.TASK_ID in ( ' +
      '    select distinct ti.TASK_ID ' +
      '    from time_items ti ' +
      '    where ti.USER_ID = :USER_ID_EXISTING ' +
      '      and ti.TASK_DATE >= :START_DATE ' +
      '      and ti.TASK_DATE < :END_DATE ' +
      '      and ti.TASK_ID is not null ' +
      '  ) ' +
      ') ' +
      'order by c.SHORT_NAME, p.NAME, t.TASK_NUM_1, t.TASK_NUM_2, t.TASK_NUM_3, t.TASK_NUM_4, t.TASK_NUM_5, t.TASK_NUM_6';

    tasksQuery.ParamByName('USER_ID').AsString := userId;
    tasksQuery.ParamByName('USER_ID_EXISTING').AsString := userId;
    tasksQuery.ParamByName('ACTIVE_DATE').AsDateTime := startDateValue;
    tasksQuery.ParamByName('START_DATE').AsDateTime := startDateValue;
    tasksQuery.ParamByName('END_DATE').AsDateTime := exclusiveEndDateValue;
    tasksQuery.Open;

    while not tasksQuery.Eof do
    begin
      taskOption := TTimeEntryTaskOption.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(taskOption);

      taskOption.taskId := FieldText(tasksQuery, 'TASK_ID');
      taskOption.taskDisplay := BuildTaskDisplay(tasksQuery);

      AResponse.taskOptions.Add(taskOption);
      tasksQuery.Next;
    end;
  finally
    tasksQuery.Free;
  end;
end;


procedure TTimeEntryService.LoadCategoryOptions(AResponse: TTimeEntriesResponse);
var
  categoriesQuery: TUniQuery;
  categoryOption: TTimeEntryCategoryOption;
begin
  categoriesQuery := TUniQuery.Create(nil);
  try
    categoriesQuery.Connection := apiDB.ucETaskApi;
    categoriesQuery.SQL.Text :=
      'select CODE, CODE_DESC ' +
      'from codes ' +
      'where CATEGORY = ''TASK'' ' +
      '  and CODE_TYPE = ''CATGRY'' ' +
      'order by CODE_DESC';

    categoriesQuery.Open;

    while not categoriesQuery.Eof do
    begin
      categoryOption := TTimeEntryCategoryOption.Create;
      TXDataOperationContext.Current.Handler.ManagedObjects.Add(categoryOption);

      categoryOption.code := FieldText(categoriesQuery, 'CODE');
      categoryOption.codeDesc := FieldText(categoriesQuery, 'CODE_DESC');

      AResponse.categoryOptions.Add(categoryOption);
      categoriesQuery.Next;
    end;
  finally
    categoriesQuery.Free;
  end;
end;


function TTimeEntryService.GetTimeEntries(userId, startDate, endDate: string): TTimeEntriesResponse;
var
  startDateValue: TDateTime;
  endDateValue: TDateTime;
  exclusiveEndDateValue: TDateTime;
begin
  Logger.Log(4, Format('TimeEntryService.GetTimeEntries - USER_ID="%s" START="%s" END="%s"', [userId, startDate, endDate]));

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

  startDateValue := ParseIsoDate(startDate);
  endDateValue := ParseIsoDate(endDate);
  exclusiveEndDateValue := IncDay(endDateValue, 1);

  LoadUserName(Result, userId);
  LoadTimeEntryItems(Result, userId, startDateValue, exclusiveEndDateValue);
  LoadTaskOptions(Result, userId, startDateValue, exclusiveEndDateValue);
  LoadCategoryOptions(Result);

  Result.count := Result.items.Count;

  Logger.Log(4, Format('TimeEntryService.GetTimeEntries - returned %d item(s)', [Result.count]));
end;


function TTimeEntryService.GetNextIdValue(const AKeyName: string): string;
var
  q: TUniQuery;
  nextId: Integer;
begin
  q := TUniQuery.Create(nil);
  try
    q.Connection := apiDB.ucETaskApi;

    q.SQL.Text :=
      'select KeyValue from idfield where KeyName = :KEY_NAME';
    q.ParamByName('KEY_NAME').AsString := AKeyName;
    q.Open;

    nextId := q.FieldByName('KeyValue').AsInteger + 1;

    q.Close;
    q.SQL.Text :=
      'update idfield set KeyValue = :KEY_VALUE where KeyName = :KEY_NAME';
    q.ParamByName('KEY_VALUE').AsInteger := nextId;
    q.ParamByName('KEY_NAME').AsString := AKeyName;
    q.ExecSQL;

    Result := IntToStr(nextId);
  finally
    q.Free;
  end;
end;


function TTimeEntryService.AddTimeEntry(userId, taskDate: string): string;
var
  taskDateValue: TDateTime;
  entryId: string;
begin
  Logger.Log(4, Format('TimeEntryService.AddTimeEntry - USER_ID="%s" TASK_DATE="%s"', [userId, taskDate]));

  taskDateValue := ParseIsoDate(taskDate);
  entryId := GetNextIdValue('TimeEntryId');

  apiDB.uqAddTimeEntry.Close;
  apiDB.uqAddTimeEntry.ParamByName('ENTRY_ID').AsString := entryId;
  apiDB.uqAddTimeEntry.ParamByName('USER_ID').AsString := userId;
  apiDB.uqAddTimeEntry.ParamByName('TASK_DATE').AsDateTime := taskDateValue;
  apiDB.uqAddTimeEntry.ParamByName('CREATED_BY').AsString := userId;
  apiDB.uqAddTimeEntry.ParamByName('MODIFIED_BY').AsString := userId;
  apiDB.uqAddTimeEntry.ExecSQL;

  Result := entryId;

  Logger.Log(4, 'TimeEntryService.AddTimeEntry - new ENTRY_ID=' + Result);
end;


initialization
  RegisterServiceType(TTimeEntryService);

end.
