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 LoadPlaceOptions(AResponse: TTimeEntriesResponse);
    procedure LoadCategoryOptions(AResponse: TTimeEntriesResponse);
    function GetNextIdValue(const AKeyName: string): string;
  public
    function GetTimeEntries(userId, startDate, endDate: string): TTimeEntriesResponse;
    function AddTimeEntry(userId, taskDate: string): string;
    function SaveTimeEntry(Item: TTimeEntrySave): string;
    function SaveTimeEntryField(Item: TTimeEntryFieldSave): Boolean;
    function GetTaskPickerCustomers(userId: string): TTaskPickerOptionsResponse;
    function GetTaskPickerProjects(userId, customerId: string): TTaskPickerOptionsResponse;
    function GetTaskPickerTasks(userId, projectId: string): TTaskPickerOptionsResponse;
    function DeleteTimeEntry(userId: string; entryId: Integer): Boolean;
  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;

procedure AddTaskPickerOption(AResponse: TTaskPickerOptionsResponse; const AValue, ADisplay: string);
var
  option: TTaskPickerOption;
begin
  option := TTaskPickerOption.Create;
  TXDataOperationContext.Current.Handler.ManagedObjects.Add(option);

  option.value := AValue;
  option.display := ADisplay;

  AResponse.options.Add(option);
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.PLACE, ' +
      '  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, ' +
      '  plc.CODE_DESC as PLACE_DESC ' +
      '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 ' +
      'left join codes plc on plc.CATEGORY = ''GENERAL'' and plc.CODE_TYPE = ''PLACE'' and plc.CODE = ti.PLACE ' +
      '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.place := FieldText(itemsQuery, 'PLACE');
      item.placeDesc := FieldText(itemsQuery, 'PLACE_DESC');

      if item.placeDesc = '' then
        item.placeDesc := item.place;

      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 task_assigned_user tau ' +
      'join tasks t on t.TASK_ID = tau.TASK_ID ' +
      'join project p on p.PROJECT_ID = t.PROJECT_ID ' +
      'join customers c on c.CUSTOMER_ID = p.CUSTOMER_ID ' +
      'where tau.USER_ID = :USER_ID ' +
      '  and upper(trim(coalesce(tau.FOCUSED_TASK, ''''))) = ''T'' ' +
      '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.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;


procedure TTimeEntryService.LoadPlaceOptions(AResponse: TTimeEntriesResponse);
var
  placeQuery: TUniQuery;
  placeOption: TTimeEntryCategoryOption;
begin
  placeQuery := TUniQuery.Create(nil);
  try
    placeQuery.Connection := apiDB.ucETaskApi;
    placeQuery.SQL.Text :=
      'select CODE, CODE_DESC ' +
      'from codes ' +
      'where CATEGORY = ''GENERAL'' ' +
      '  and CODE_TYPE = ''PLACE'' ' +
      'order by CODE_DESC';

    placeQuery.Open;

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

      placeOption.code := FieldText(placeQuery, 'CODE');
      placeOption.codeDesc := FieldText(placeQuery, 'CODE_DESC');

      AResponse.placeOptions.Add(placeOption);
      placeQuery.Next;
    end;
  finally
    placeQuery.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);
  LoadPlaceOptions(Result);
  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;


function TTimeEntryService.SaveTimeEntry(Item: TTimeEntrySave): string;
var
  taskDateValue: TDateTime;
  entryId: string;
  isNewEntry: Boolean;
  q: TUniQuery;
begin
  if not Assigned(Item) then
    raise Exception.Create('SaveTimeEntry: Item is nil.');

  if Trim(Item.userId) = '' then
    raise Exception.Create('SaveTimeEntry: Invalid userId.');

  if Trim(Item.taskDate) = '' then
    raise Exception.Create('SaveTimeEntry: Task date is required.');

  if Trim(Item.taskId) = '' then
    raise Exception.Create('SaveTimeEntry: Task is required.');

  if Item.hours <= 0 then
    raise Exception.Create('SaveTimeEntry: Hours must be greater than zero.');

  if Trim(Item.category) = '' then
    raise Exception.Create('SaveTimeEntry: Category is required.');

  if Trim(Item.summary) = '' then
    raise Exception.Create('SaveTimeEntry: Summary is required.');

  Logger.Log(4, Format(
    'TimeEntryService.SaveTimeEntry - ENTRY_ID="%s" USER_ID="%s"',
    [Item.entryId, Item.userId]
  ));

  taskDateValue := ParseIsoDate(Item.taskDate);
  isNewEntry := StrToIntDef(Item.entryId, 0) <= 0;

  if isNewEntry then
  begin
    entryId := GetNextIdValue('TimeEntryId');

    q := TUniQuery.Create(nil);
    try
      q.Connection := apiDB.ucETaskApi;
      q.SQL.Text :=
        'insert into time_items ( ' +
        '  ENTRY_ID, ' +
        '  USER_ID, ' +
        '  TASK_DATE, ' +
        '  TASK_ID, ' +
        '  PROJECT_ID, ' +
        '  HOURS, ' +
        '  TASK_TIME, ' +
        '  PLACE, ' +
        '  CATEGORY, ' +
        '  SUMMARY, ' +
        '  CREATE_DATE, ' +
        '  CREATED_BY, ' +
        '  MODIFY_DATE, ' +
        '  MODIFIED_BY ' +
        ') values ( ' +
        '  :ENTRY_ID, ' +
        '  :USER_ID, ' +
        '  :TASK_DATE, ' +
        '  :TASK_ID, ' +
        '  (select t.PROJECT_ID from tasks t where t.TASK_ID = :TASK_ID), ' +
        '  :HOURS, ' +
        '  :TASK_TIME, ' +
        '  :PLACE, ' +
        '  :CATEGORY, ' +
        '  :SUMMARY, ' +
        '  now(), ' +
        '  :CREATED_BY, ' +
        '  now(), ' +
        '  :MODIFIED_BY ' +
        ')';

      q.ParamByName('ENTRY_ID').AsString := entryId;
      q.ParamByName('USER_ID').AsString := Item.userId;
      q.ParamByName('TASK_DATE').AsDateTime := taskDateValue;
      q.ParamByName('TASK_ID').AsString := Item.taskId;
      q.ParamByName('HOURS').AsFloat := Item.hours;

      if Trim(Item.taskTime) = '' then
        q.ParamByName('TASK_TIME').Clear
      else
        q.ParamByName('TASK_TIME').AsString := Item.taskTime;

      if Trim(Item.place) = '' then
        q.ParamByName('PLACE').Clear
      else
        q.ParamByName('PLACE').AsString := Item.place;

      q.ParamByName('CATEGORY').AsString := Item.category;
      q.ParamByName('SUMMARY').AsString := Item.summary;
      q.ParamByName('CREATED_BY').AsString := Item.userId;
      q.ParamByName('MODIFIED_BY').AsString := Item.userId;

      q.ExecSQL;
    finally
      q.Free;
    end;
  end
  else
  begin
    entryId := Item.entryId;

    apiDB.uqSaveTimeEntry.Close;
    apiDB.uqSaveTimeEntry.ParamByName('ENTRY_ID').AsString := entryId;
    apiDB.uqSaveTimeEntry.ParamByName('USER_ID').AsString := Item.userId;
    apiDB.uqSaveTimeEntry.ParamByName('TASK_DATE').AsDateTime := taskDateValue;
    apiDB.uqSaveTimeEntry.ParamByName('TASK_ID').AsString := Item.taskId;
    apiDB.uqSaveTimeEntry.ParamByName('HOURS').AsFloat := Item.hours;

    if Trim(Item.taskTime) = '' then
      apiDB.uqSaveTimeEntry.ParamByName('TASK_TIME').Clear
    else
      apiDB.uqSaveTimeEntry.ParamByName('TASK_TIME').AsString := Item.taskTime;

    if Trim(Item.place) = '' then
      apiDB.uqSaveTimeEntry.ParamByName('PLACE').Clear
    else
      apiDB.uqSaveTimeEntry.ParamByName('PLACE').AsString := Item.place;

    apiDB.uqSaveTimeEntry.ParamByName('CATEGORY').AsString := Item.category;
    apiDB.uqSaveTimeEntry.ParamByName('SUMMARY').AsString := Item.summary;
    apiDB.uqSaveTimeEntry.ParamByName('MODIFIED_BY').AsString := Item.userId;

    apiDB.uqSaveTimeEntry.ExecSQL;
  end;

  Result := entryId;

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


function TTimeEntryService.SaveTimeEntryField(Item: TTimeEntryFieldSave): Boolean;
var
  uqSaveField: TUniQuery;
  columnName: string;
  d: TDateTime;

  function MapFieldNameToColumn(AFieldName: string): string;
  begin
    Result := '';

    if SameText(AFieldName, 'taskDate') then
      Result := 'TASK_DATE'
    else if SameText(AFieldName, 'hours') then
      Result := 'HOURS'
    else if SameText(AFieldName, 'taskTime') then
      Result := 'TASK_TIME'
    else if SameText(AFieldName, 'place') then
      Result := 'PLACE'
    else if SameText(AFieldName, 'category') then
      Result := 'CATEGORY'
    else if SameText(AFieldName, 'summary') then
      Result := 'SUMMARY';
  end;

  function ParseDateOrZero(const S: string; out ADate: TDateTime): Boolean;
  var
    y: Integer;
    m: Integer;
    dInt: Integer;
  begin
    Result := False;
    ADate := 0;

    if Length(Trim(S)) <> 10 then
      Exit;

    y := StrToIntDef(Copy(S, 1, 4), 0);
    m := StrToIntDef(Copy(S, 6, 2), 0);
    dInt := StrToIntDef(Copy(S, 9, 2), 0);

    if (y > 0) and (m > 0) and (dInt > 0) then
    begin
      try
        ADate := EncodeDate(y, m, dInt);
        Result := True;
      except
        Result := False;
      end;
    end;
  end;

begin
  Result := False;

  if not Assigned(Item) then
    raise Exception.Create('SaveTimeEntryField: Item is nil.');

  if Item.entryId <= 0 then
    raise Exception.Create('SaveTimeEntryField: Invalid entryId.');

  if Trim(Item.userId) = '' then
    raise Exception.Create('SaveTimeEntryField: Invalid userId.');

  uqSaveField := TUniQuery.Create(nil);
  try
    uqSaveField.Connection := apiDB.ucETaskApi;

    if SameText(Item.fieldName, 'taskId') then
    begin
      if Trim(Item.value) = '' then
      begin
        uqSaveField.SQL.Text :=
          'update time_items ' +
          'set TASK_ID = null, ' +
          '    PROJECT_ID = null, ' +
          '    MODIFY_DATE = now(), ' +
          '    MODIFIED_BY = :MODIFIED_BY ' +
          'where ENTRY_ID = :ENTRY_ID ' +
          '  and USER_ID = :USER_ID';
      end
      else
      begin
        uqSaveField.SQL.Text :=
          'update time_items ' +
          'set TASK_ID = :TASK_ID, ' +
          '    PROJECT_ID = ( ' +
          '      select t.PROJECT_ID ' +
          '      from tasks t ' +
          '      where t.TASK_ID = :TASK_ID ' +
          '    ), ' +
          '    MODIFY_DATE = now(), ' +
          '    MODIFIED_BY = :MODIFIED_BY ' +
          'where ENTRY_ID = :ENTRY_ID ' +
          '  and USER_ID = :USER_ID';

        uqSaveField.ParamByName('TASK_ID').AsString := Item.value;
      end;
    end
    else
    begin
      columnName := MapFieldNameToColumn(Item.fieldName);
      if columnName = '' then
        raise Exception.Create('SaveTimeEntryField: Invalid field name: ' + Item.fieldName);

      uqSaveField.SQL.Text :=
        'update time_items ' +
        'set ' + columnName + ' = :' + columnName + ', ' +
        '    MODIFY_DATE = now(), ' +
        '    MODIFIED_BY = :MODIFIED_BY ' +
        'where ENTRY_ID = :ENTRY_ID ' +
        '  and USER_ID = :USER_ID';

      if SameText(Item.fieldName, 'taskDate') then
      begin
        if ParseDateOrZero(Item.value, d) then
          uqSaveField.ParamByName(columnName).AsDateTime := d
        else
          uqSaveField.ParamByName(columnName).Clear;
      end
      else if SameText(Item.fieldName, 'hours') then
      begin
        if Trim(Item.value) = '' then
          uqSaveField.ParamByName(columnName).Clear
        else
          uqSaveField.ParamByName(columnName).AsFloat := StrToFloatDef(Item.value, 0);
      end
      else
      begin
        if Trim(Item.value) = '' then
          uqSaveField.ParamByName(columnName).Clear
        else
          uqSaveField.ParamByName(columnName).AsString := Item.value;
      end;
    end;

    uqSaveField.ParamByName('ENTRY_ID').AsInteger := Item.entryId;
    uqSaveField.ParamByName('USER_ID').AsString := Item.userId;
    uqSaveField.ParamByName('MODIFIED_BY').AsString := Item.userId;

    uqSaveField.ExecSQL;
    Result := True;

    Logger.Log(4, Format(
      'TimeEntryService.SaveTimeEntryField - ENTRY_ID="%d" FIELD="%s"',
      [Item.entryId, Item.fieldName]
    ));
  finally
    uqSaveField.Free;
  end;
end;


function TTimeEntryService.GetTaskPickerCustomers(userId: string): TTaskPickerOptionsResponse;
begin
  Logger.Log(4, Format('TimeEntryService.GetTaskPickerCustomers - USER_ID="%s"', [userId]));

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

  apiDB.uqTaskPickerCustomers.Close;
  apiDB.uqTaskPickerCustomers.ParamByName('USER_ID').AsString := userId;
  apiDB.uqTaskPickerCustomers.Open;
  try
    while not apiDB.uqTaskPickerCustomers.Eof do
    begin
      AddTaskPickerOption(
        Result,
        FieldText(apiDB.uqTaskPickerCustomers, 'CUSTOMER_ID'),
        FieldText(apiDB.uqTaskPickerCustomers, 'CUSTOMER_SHORT_NAME')
      );

      apiDB.uqTaskPickerCustomers.Next;
    end;
  finally
    apiDB.uqTaskPickerCustomers.Close;
  end;
end;


function TTimeEntryService.GetTaskPickerProjects(userId, customerId: string): TTaskPickerOptionsResponse;
begin
  Logger.Log(4, Format('TimeEntryService.GetTaskPickerProjects - USER_ID="%s" CUSTOMER_ID="%s"', [userId, customerId]));

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

  apiDB.uqTaskPickerProjects.Close;
  apiDB.uqTaskPickerProjects.ParamByName('USER_ID').AsString := userId;
  apiDB.uqTaskPickerProjects.ParamByName('CUSTOMER_ID').AsString := customerId;
  apiDB.uqTaskPickerProjects.Open;
  try
    while not apiDB.uqTaskPickerProjects.Eof do
    begin
      AddTaskPickerOption(
        Result,
        FieldText(apiDB.uqTaskPickerProjects, 'PROJECT_ID'),
        FieldText(apiDB.uqTaskPickerProjects, 'PROJECT_NAME')
      );

      apiDB.uqTaskPickerProjects.Next;
    end;
  finally
    apiDB.uqTaskPickerProjects.Close;
  end;
end;


function TTimeEntryService.GetTaskPickerTasks(userId, projectId: string): TTaskPickerOptionsResponse;
begin
  Logger.Log(4, Format('TimeEntryService.GetTaskPickerTasks - USER_ID="%s" PROJECT_ID="%s"', [userId, projectId]));

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

  apiDB.uqTaskPickerTasks.Close;
  apiDB.uqTaskPickerTasks.ParamByName('USER_ID').AsString := userId;
  apiDB.uqTaskPickerTasks.ParamByName('PROJECT_ID').AsString := projectId;
  apiDB.uqTaskPickerTasks.Open;
  try
    while not apiDB.uqTaskPickerTasks.Eof do
    begin
      AddTaskPickerOption(
        Result,
        FieldText(apiDB.uqTaskPickerTasks, 'TASK_ID'),
        BuildTaskDisplay(apiDB.uqTaskPickerTasks)
      );

      apiDB.uqTaskPickerTasks.Next;
    end;
  finally
    apiDB.uqTaskPickerTasks.Close;
  end;
end;


function TTimeEntryService.DeleteTimeEntry(userId: string; entryId: Integer): Boolean;
var
  q: TUniQuery;
begin
  Result := False;

  if Trim(userId) = '' then
    raise Exception.Create('DeleteTimeEntry: Invalid userId.');

  if entryId <= 0 then
    raise Exception.Create('DeleteTimeEntry: Invalid entryId.');

  Logger.Log(4, Format(
    'TimeEntryService.DeleteTimeEntry - USER_ID="%s" ENTRY_ID="%d"',
    [userId, entryId]
  ));

  q := TUniQuery.Create(nil);
  try
    q.Connection := apiDB.ucETaskApi;
    q.SQL.Text :=
      'delete from time_items ' +
      'where ENTRY_ID = :ENTRY_ID ' +
      '  and USER_ID = :USER_ID';

    q.ParamByName('ENTRY_ID').AsInteger := entryId;
    q.ParamByName('USER_ID').AsString := userId;
    q.ExecSQL;

    if q.RowsAffected <= 0 then
      raise Exception.Create('Time entry was not found or does not belong to this user.');

    Result := True;

    Logger.Log(4, Format(
      'TimeEntryService.DeleteTimeEntry - deleted ENTRY_ID="%d"',
      [entryId]
    ));
  finally
    q.Free;
  end;
end;


initialization
  RegisterServiceType(TTimeEntryService);

end.
