// ***************************************************************************
//
// DMS Container
//
// Copyright (c) 2016-2020 bit Time Professionals
//
//
// ***************************************************************************
//
// Licensed under the Commercial License
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License asking to the software provider
//
// Unless agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//
// *************************************************************************** }

unit DMSExcelModuleUtilsU;

interface

uses
  JsonDataObjects, Data.DB;

procedure PrepareJSONForExcelExport(const WorkBook: TJSONObject; const WorkSheetName: String; const DataSet: TDataSet);

implementation

uses
  MVCFramework.DataSet.Utils;

function DelphiDataTypeToExcel(const DataType: TFieldType): String;
begin
  case DataType of
    ftString, ftFixedChar, ftWideString:
      Exit('text');
    ftSmallint, ftInteger, ftWord, ftCurrency, ftBCD, ftAutoInc, ftLargeint, ftLongWord, ftShortint, ftByte,
      TFieldType.ftFloat, TFieldType.ftSingle, ftFMTBcd:
      Exit('number');
    ftBoolean:
      Exit('boolean');
    ftDate:
      Exit('date');
    ftTime:
      Exit('time');
    ftDateTime:
      Exit('datetime');
    ftMemo:
      Exit('text');
    ftFmtMemo:
      Exit('text');
    ftGuid:
      Exit('text');
    ftTimeStamp:
      Exit('datetime');
    ftFixedWideChar:
      Exit('text');
    ftWideMemo:
      Exit('text');
  else
    Result := 'general';
  end;

end;

procedure PrepareJSONForExcelExport(const WorkBook: TJSONObject; const WorkSheetName: String; const DataSet: TDataSet);
var
  lWorkSheets: TJsonArray;
  lWorkSheet: TJSONObject;
  I: Integer;
  lColumns: TJsonArray;
  lCol: TJSONObject;
begin
  DataSet.First;
  DataSet.DisableControls;
  try
    lWorkSheets := WorkBook.A['worksheets'];
    lWorkSheet := lWorkSheets.AddObject;
    lWorkSheet.S['name'] := WorkSheetName;
    lColumns := lWorkSheet.A['columns'];
    for I := 0 to DataSet.Fields.Count - 1 do
    begin
      lCol := lColumns.AddObject;
      lCol.S['title'] := DataSet.Fields[I].DisplayName;
      lCol.S['type'] := DelphiDataTypeToExcel(DataSet.Fields[I].DataType);
      if DataSet.Fields[I] is TDateTimeField then
      begin
        lCol.S['format'] := TDateTimeField(DataSet.Fields[I]).DisplayFormat;
      end
      else if DataSet.Fields[I] is TNumericField then
      begin
        lCol.S['format'] := TNumericField(DataSet.Fields[I]).DisplayFormat;
      end;
    end;
    DataSet.First;
    lWorkSheet.A['data'] := DataSet.AsJSONArrayOfValues;
    DataSet.First;
  finally
    DataSet.EnableControls;
  end;
end;

end.
