unit orm_utils;

{$TYPEINFO ON}

interface

uses
  System.Classes,
  System.Generics.Collections,
  System.Rtti,
  Uni,
  TypInfo,
  DB,
  SysUtils,
  StrUtils,
  DateUtils,
  Math,
  pixel_classes,
  pixel_utils,
  System.Variants,
  ORM_CustomAttributes;

type
  EORMUtilsException = class(Exception);

  TQryRecInfo = class
  private
    FDirtyFields: TDictionary<string, Boolean>;
    FPreviousValues: TDictionary<string, TValue>;
    procedure SetInfo(Index: Integer; const Value: TValue);
  public
    constructor Create;
    destructor Destroy; override;

    procedure SetInfoNullableString(Index: Integer; const Value: TNullable<string>);
    procedure SetInfoNullableInteger(Index: Integer; const Value: TNullable<Integer>);
    procedure SetInfoNullableInt64(Index: Integer; const Value: TNullable<Int64>);
    procedure SetInfoNullableExtended(Index: Integer; const Value: TNullable<Extended>);
    procedure SetInfoNullableCurrency(Index: Integer; const Value: TNullable<Currency>);
    procedure SetInfoNullableDateTime(Index: Integer; const Value: TNullable<TDateTime>);
    procedure SetInfoNullableBoolean(Index: Integer; const Value: TNullable<Boolean>);
    procedure SetInfoNullableVariant(Index: Integer; const Value: TNullable<Variant>);
    procedure SetInfoCurrency(Index: Integer; const Value: Currency);

    // Methods to handle dirty fields
    function IsFieldDirty(const FieldName: string): Boolean;
    procedure ClearDirtyFlags;
    property DirtyFields: TDictionary<string, Boolean> read FDirtyFields;

    // Methods to handle previous values
    function GetPreviousValue(const FieldName: string): TValue;
    procedure ClearPreviousValues;
    property PreviousValues: TDictionary<string, TValue> read FPreviousValues;

    /// <summary>
    /// Retrieves the formatted text for a given field based on the FmtProp attribute.
    /// </summary>
    function GetFormattedText(const FieldName: string): string;
    procedure Fetch(DBConn: TUniConnection; const QryTxt: string);

    /// <summary>
    /// Class method to fetch multiple records from a query and return a list of TQryRecInfo instances.
    /// </summary>
    class function FetchAll(DBConn: TUniConnection; const QryTxt: string): TObjectList<TQryRecInfo>;
  end;

function FormataInfo(info: Variant; tipo: TFormatType): string;

type
  TDynRec = class(TQryRecInfo)
  end;

implementation

function FormataInfo(info: Variant; tipo: TFormatType): string;
begin
  if VarIsNull(info) or VarIsEmpty(info) then
    Exit('');

  case tipo of
    tiString:
      Result := VarToStr(info);
    tiInteger:
      Result := IntToStr(Integer(info));
    tiMoney:
      Result := FormatFloat(FMT_MONEY, Currency(info));
    tiFloat:
      Result := FloatToStr(info);
    tiDate:
      if VarIsType(info, [varDate, varDouble]) then
      begin
        if VarToDateTime(info) < EncodeDate(1900, 1, 1) then
          Result := ''
        else
          Result := FormatDateTime('dd/mm/yyyy', VarToDateTime(info));
      end
      else
        raise EORMUtilsException.CreateFmt('FormataInfo: Cannot treat information [%s] as tiDate.', [VarToStr(info)]);
    tiDateTime:
      if VarIsType(info, [varDate, varDouble]) then
      begin
        if VarToDateTime(info) = 0 then
          Result := ''
        else
          Result := FormatDateTime('dd/mm/yyyy hh:mm:ss', VarToDateTime(info));
      end
      else
        raise EORMUtilsException.CreateFmt('FormataInfo: Cannot treat information [%s] as tiDateTime.', [VarToStr(info)]);
    tiBoolean:
      Result := IfThen(Boolean(info), 'S', 'N');
    tiByteArray:
      Result := ByteArrayToHex(info);
    tiNone:
      Result := '';
  else
    raise EORMUtilsException.CreateFmt('FormataInfo: Format type [%s] not recognized.', [GetEnumName(TypeInfo(TFormatType), Integer(tipo))]);
  end;
end;

{ TQryRecInfo }

constructor TQryRecInfo.Create;
begin
  inherited;
  FDirtyFields := TDictionary<string, Boolean>.Create;
  FPreviousValues := TDictionary<string, TValue>.Create;
end;

destructor TQryRecInfo.Destroy;
begin
  FDirtyFields.Free;
  FPreviousValues.Free;
  inherited;
end;

function TQryRecInfo.GetFormattedText(const FieldName: string): string;
var
  Ctx: TRttiContext;
  RttiType: TRttiType;
  Prop: TRttiProperty;
  Field: TRttiField;
  Value: TValue;
  FmtType: TFormatType;
  UnderlyingType: TRttiType;
  HasValueField: TRttiField;
  ValueField: TRttiField;
  HasValue: Boolean;
  ActualValue: TValue;
  Attr: TCustomAttribute;
  FmtAttr: FmtPropAttribute;
begin
  Result := '';
  Ctx := TRttiContext.Create;
  try
    RttiType := Ctx.GetType(Self.ClassType);

    // Find the property by name
    Prop := RttiType.GetProperty(FieldName);
    if not Assigned(Prop) then
      raise EORMUtilsException.CreateFmt('Property "%s" not found.', [FieldName]);

    // Get the value of the property
    Value := Prop.GetValue(Self);
    FmtType := tiNone;

    // Check for FmtProp attribute on the property
    for Attr in Prop.GetAttributes do
    begin
      if Attr is FmtPropAttribute then
      begin
        FmtAttr := FmtPropAttribute(Attr);
        FmtType := FmtAttr.FormatType;
        Break;
      end;
    end;

    // If not found on property, check on the corresponding field
    if FmtType = tiNone then
    begin
      Field := RttiType.GetField('_' + FieldName);
      if Assigned(Field) then
      begin
        for Attr in Field.GetAttributes do
        begin
          if Attr is FmtPropAttribute then
          begin
            FmtAttr := FmtPropAttribute(Attr);
            FmtType := FmtAttr.FormatType;
            Break;
          end;
        end;
      end;
    end;

    // Determine default FmtType based on property type if not set by attribute
    if FmtType = tiNone then
    begin
      UnderlyingType := Prop.PropertyType;

      // Handle TNullable<T>
      if UnderlyingType.TypeKind = tkRecord then
      begin
        ValueField := UnderlyingType.GetField('Value');
        if Assigned(ValueField) then
          UnderlyingType := ValueField.FieldType;
      end;

      case UnderlyingType.TypeKind of
        tkInteger, tkInt64:
          FmtType := tiInteger;
        tkFloat:
          if UnderlyingType.Handle = TypeInfo(TDateTime) then
            FmtType := tiDateTime
          else
            FmtType := tiFloat;
        tkString, tkLString, tkWString, tkUString:
          FmtType := tiString;
        tkEnumeration:
          if UnderlyingType.Handle = TypeInfo(Boolean) then
            FmtType := tiBoolean
          else
            FmtType := tiString;
        tkVariant:
          FmtType := tiString;
      else
        FmtType := tiNone;
      end;
    end;

    // Extract actual value from TNullable<T> if necessary
    if Value.Kind = tkRecord then
    begin
      // Assuming TNullable<T> has 'HasValue' and 'Value' fields
      UnderlyingType := Prop.PropertyType;
      HasValueField := UnderlyingType.GetField('HasValue');
      ValueField := UnderlyingType.GetField('Value');

      if Assigned(HasValueField) and Assigned(ValueField) then
      begin
        HasValue := HasValueField.GetValue(Value.GetReferenceToRawData).AsBoolean;
        if HasValue then
        begin
          ActualValue := ValueField.GetValue(Value.GetReferenceToRawData);
          // Format the actual value
          Result := FormataInfo(ActualValue.AsVariant, FmtType);
        end
        else
          Result := '';
      end
      else
      begin
        // Not TNullable<T>, handle value directly
        if not Value.IsEmpty then
          Result := FormataInfo(Value.AsVariant, FmtType)
        else
          Result := '';
      end;
    end
    else
    begin
      // Not a record, handle value directly
      if not Value.IsEmpty then
        Result := FormataInfo(Value.AsVariant, FmtType)
      else
        Result := '';
    end;
  finally
    // No need to free TRttiContext as it's a record
  end;
end;

procedure TQryRecInfo.Fetch(DBConn: TUniConnection; const QryTxt: string);
var
  qr: TUniQuery;
  Ctx: TRttiContext;
  ObjType: TRttiType;
  Prop: TRttiProperty;
  Field: TField;
  NullableFieldName: string;
begin
  if not Assigned(DBConn) or Trim(QryTxt).IsEmpty then
    Exit;

  qr := TUniQuery.Create(nil);
  try
    qr.Connection := DBConn;
    qr.SpecificOptions.Values['FetchAll'] := 'True';
    qr.SpecificOptions.Values['CreateConnection'] := 'False';
    qr.SQL.Text := QryTxt;
    qr.Open;

    if qr.RecordCount > 0 then
    begin
      Ctx := TRttiContext.Create;
      try
        ObjType := Ctx.GetType(Self.ClassType);

        for Prop in ObjType.GetProperties do
        begin
          if not Prop.IsWritable then
            Continue;

          Field := qr.FindField(Prop.Name);
          if not Assigned(Field) then
            Continue;

          // Handle TNullable<T> types
          if Prop.PropertyType.TypeKind = tkRecord then
          begin
            NullableFieldName := '_' + Prop.Name;
            if ObjType.GetField(NullableFieldName) = nil then
              Continue;

            if Prop.PropertyType.Name.StartsWith('TNullable<') then
            begin
              // Determine underlying type
              if Field.IsNull then
              begin
                if Prop.PropertyType.Name = 'TNullable<System.string>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<string>>(NullValue))
                else if Prop.PropertyType.Name = 'TNullable<System.Integer>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Integer>>(NullValue))
                else if Prop.PropertyType.Name = 'TNullable<System.Int64>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Int64>>(NullValue))
                else if Prop.PropertyType.Name = 'TNullable<System.Extended>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Extended>>(NullValue))
                else if Prop.PropertyType.Name = 'TNullable<System.Currency>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Currency>>(NullValue))
                else if Prop.PropertyType.Name = 'TNullable<System.TDateTime>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<TDateTime>>(NullValue))
                else if Prop.PropertyType.Name = 'TNullable<System.Boolean>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Boolean>>(NullValue))
                else if Prop.PropertyType.Name = 'TNullable<System.Variant>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Variant>>(NullValue));
              end
              else
              begin
                if Prop.PropertyType.Name = 'TNullable<System.string>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<string>>(Field.AsString))
                else if Prop.PropertyType.Name = 'TNullable<System.Integer>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Integer>>(Field.AsInteger))
                else if Prop.PropertyType.Name = 'TNullable<System.Int64>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Int64>>(Field.AsLargeInt))
                else if Prop.PropertyType.Name = 'TNullable<System.Extended>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Extended>>(Field.AsFloat))
                else if Prop.PropertyType.Name = 'TNullable<System.Currency>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Currency>>(Field.AsCurrency))
                else if Prop.PropertyType.Name = 'TNullable<System.TDateTime>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<TDateTime>>(Field.AsDateTime))
                else if Prop.PropertyType.Name = 'TNullable<System.Boolean>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Boolean>>(Field.AsBoolean))
                else if Prop.PropertyType.Name = 'TNullable<System.Variant>' then
                  ObjType.GetField(NullableFieldName).SetValue(Self, TValue.From<TNullable<Variant>>(Field.Value));
              end;
            end
            else
              raise EORMUtilsException.CreateFmt('Unsupported record type: %s', [Prop.PropertyType.Name]);
          end
          else
          begin
            // Handle non-nullable types
            case Prop.PropertyType.TypeKind of
              tkString, tkLString, tkWString, tkUString:
                Prop.SetValue(Self, Field.AsString);
              tkInteger:
                Prop.SetValue(Self, Field.AsInteger);
              tkInt64:
                Prop.SetValue(Self, Field.AsLargeInt);
              tkFloat:
                if Prop.PropertyType.Handle = TypeInfo(Currency) then
                  Prop.SetValue(Self, Field.AsCurrency)
                else
                  Prop.SetValue(Self, Field.AsFloat);
              tkVariant:
                Prop.SetValue(Self, TValue.FromVariant(Field.AsVariant));
              tkEnumeration:
                if Prop.PropertyType.Handle = TypeInfo(Boolean) then
                  Prop.SetValue(Self, Field.AsBoolean)
                else
                  raise EORMUtilsException.CreateFmt('Unsupported enumeration type: %s', [Prop.PropertyType.Name]);
            else
              // Add additional handling for other types if necessary
            end;
          end;
        end;
      finally
        // No need to free TRttiContext as it's a record
      end;
    end;
  finally
    qr.Free;
  end;
end;

class function TQryRecInfo.FetchAll(DBConn: TUniConnection; const QryTxt: string): TObjectList<TQryRecInfo>;
var
  qr: TUniQuery;
  Ctx: TRttiContext;
  ObjType: TRttiType;
  Prop: TRttiProperty;
  Field: TField;
  NullableFieldName: string;
  RecInfo: TQryRecInfo;
begin
  Result := TObjectList<TQryRecInfo>.Create(True); // Owns objects

  if not Assigned(DBConn) or Trim(QryTxt).IsEmpty then
    Exit;

  qr := TUniQuery.Create(nil);
  try
    qr.Connection := DBConn;
    qr.SpecificOptions.Values['FetchAll'] := 'True';
    qr.SpecificOptions.Values['CreateConnection'] := 'False';
    qr.SQL.Text := QryTxt;
    qr.Open;

    if qr.RecordCount > 0 then
    begin
      Ctx := TRttiContext.Create;
      try
        ObjType := Ctx.GetType(TQryRecInfo);

        qr.First;
        while not qr.Eof do
        begin
          RecInfo := TQryRecInfo.Create;
          try
            for Prop in ObjType.GetProperties do
            begin
              if not Prop.IsWritable then
                Continue;

              Field := qr.FindField(Prop.Name);
              if not Assigned(Field) then
                Continue;

              // Handle TNullable<T> types
              if Prop.PropertyType.TypeKind = tkRecord then
              begin
                NullableFieldName := '_' + Prop.Name;
                if ObjType.GetField(NullableFieldName) = nil then
                  Continue;

                if Prop.PropertyType.Name.StartsWith('TNullable<') then
                begin
                  if Field.IsNull then
                  begin
                    if Prop.PropertyType.Name = 'TNullable<System.string>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<string>>(NullValue))
                    else if Prop.PropertyType.Name = 'TNullable<System.Integer>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Integer>>(NullValue))
                    else if Prop.PropertyType.Name = 'TNullable<System.Int64>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Int64>>(NullValue))
                    else if Prop.PropertyType.Name = 'TNullable<System.Extended>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Extended>>(NullValue))
                    else if Prop.PropertyType.Name = 'TNullable<System.Currency>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Currency>>(NullValue))
                    else if Prop.PropertyType.Name = 'TNullable<System.TDateTime>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<TDateTime>>(NullValue))
                    else if Prop.PropertyType.Name = 'TNullable<System.Boolean>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Boolean>>(NullValue))
                    else if Prop.PropertyType.Name = 'TNullable<System.Variant>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Variant>>(NullValue));
                  end
                  else
                  begin
                    if Prop.PropertyType.Name = 'TNullable<System.string>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<string>>(Field.AsString))
                    else if Prop.PropertyType.Name = 'TNullable<System.Integer>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Integer>>(Field.AsInteger))
                    else if Prop.PropertyType.Name = 'TNullable<System.Int64>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Int64>>(Field.AsLargeInt))
                    else if Prop.PropertyType.Name = 'TNullable<System.Extended>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Extended>>(Field.AsFloat))
                    else if Prop.PropertyType.Name = 'TNullable<System.Currency>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Currency>>(Field.AsCurrency))
                    else if Prop.PropertyType.Name = 'TNullable<System.TDateTime>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<TDateTime>>(Field.AsDateTime))
                    else if Prop.PropertyType.Name = 'TNullable<System.Boolean>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Boolean>>(Field.AsBoolean))
                    else if Prop.PropertyType.Name = 'TNullable<System.Variant>' then
                      ObjType.GetField(NullableFieldName).SetValue(RecInfo, TValue.From<TNullable<Variant>>(Field.Value));
                  end;
                end
                else
                  raise EORMUtilsException.CreateFmt('Unsupported record type: %s', [Prop.PropertyType.Name]);
              end
              else
              begin
                // Handle non-nullable types
                case Prop.PropertyType.TypeKind of
                  tkString, tkLString, tkWString, tkUString:
                    Prop.SetValue(RecInfo, Field.AsString);
                  tkInteger:
                    Prop.SetValue(RecInfo, Field.AsInteger);
                  tkInt64:
                    Prop.SetValue(RecInfo, Field.AsLargeInt);
                  tkFloat:
                    if Prop.PropertyType.Handle = TypeInfo(Currency) then
                      Prop.SetValue(RecInfo, Field.AsCurrency)
                    else
                      Prop.SetValue(RecInfo, Field.AsFloat);
                  tkVariant:
                    Prop.SetValue(RecInfo, TValue.FromVariant(Field.AsVariant));
                  tkEnumeration:
                    if Prop.PropertyType.Handle = TypeInfo(Boolean) then
                      Prop.SetValue(RecInfo, Field.AsBoolean)
                    else
                      raise EORMUtilsException.CreateFmt('Unsupported enumeration type: %s', [Prop.PropertyType.Name]);
                else
                  // Add additional handling for other types if necessary
                end;
              end;
            end;

            Result.Add(RecInfo);
          except
            RecInfo.Free;
            raise;
          end;

          qr.Next;
        end;
      finally
        // No need to free TRttiContext as it's a record
      end;
    end;
  finally
    qr.Free;
  end;
end;

function TQryRecInfo.GetPreviousValue(const FieldName: string): TValue;
begin
  if not FPreviousValues.TryGetValue(FieldName, Result) then
    raise EORMUtilsException.CreateFmt('No previous value found for field "%s".', [FieldName]);
end;

function TQryRecInfo.IsFieldDirty(const FieldName: string): Boolean;
begin
  Result := FDirtyFields.ContainsKey(FieldName);
end;

procedure TQryRecInfo.ClearDirtyFlags;
begin
  FDirtyFields.Clear;
end;

procedure TQryRecInfo.ClearPreviousValues;
begin
  FPreviousValues.Clear;
end;

procedure TQryRecInfo.SetInfo(Index: Integer; const Value: TValue);
var
  PropName: string;
  PropKind: TTypeKind;
  Ctx: TRttiContext;
  Prop: TRttiProperty;
  Field: TRttiField;
  OldValue: TValue;
begin
  // Retrieve property name and kind based on index
  // Assuming that the index corresponds to the property order
  Ctx := TRttiContext.Create;
  try
    Prop := Ctx.GetType(Self.ClassType).GetProperties()[Index];
    if not Assigned(Prop) then
      raise EORMUtilsException.CreateFmt('Error: The index "%d" does not correspond to any property in class "%s".', [Index, ClassName]);

    PropName := Prop.Name;
    PropKind := Prop.PropertyType.TypeKind;

    Field := Ctx.GetType(Self.ClassType).GetField('_' + PropName);
    if not Assigned(Field) then
      raise EORMUtilsException.CreateFmt('Error: Field "_%s" not found in class "%s".', [PropName, ClassName]);

    // If the field is not yet dirty, store the previous value
    if not FDirtyFields.ContainsKey(PropName) then
    begin
      OldValue := Field.GetValue(Self);
      FPreviousValues.AddOrSetValue(PropName, OldValue);
    end;

    // Set the new value
    Field.SetValue(Self, Value);

    // Mark the property as dirty
    FDirtyFields.AddOrSetValue(PropName, True);
  finally
    // No need to free TRttiContext as it's a record
  end;
end;

procedure TQryRecInfo.SetInfoCurrency(Index: Integer; const Value: Currency);
begin
  SetInfo(Index, TValue.From<Currency>(Value));
end;

procedure TQryRecInfo.SetInfoNullableBoolean(Index: Integer; const Value: TNullable<Boolean>);
begin
  SetInfo(Index, TValue.From<TNullable<Boolean>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableCurrency(Index: Integer; const Value: TNullable<Currency>);
begin
  SetInfo(Index, TValue.From<TNullable<Currency>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableDateTime(Index: Integer; const Value: TNullable<TDateTime>);
begin
  SetInfo(Index, TValue.From<TNullable<TDateTime>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableExtended(Index: Integer; const Value: TNullable<Extended>);
begin
  SetInfo(Index, TValue.From<TNullable<Extended>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableInteger(Index: Integer; const Value: TNullable<Integer>);
begin
  SetInfo(Index, TValue.From<TNullable<Integer>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableInt64(Index: Integer; const Value: TNullable<Int64>);
begin
  SetInfo(Index, TValue.From<TNullable<Int64>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableString(Index: Integer; const Value: TNullable<string>);
begin
  SetInfo(Index, TValue.From<TNullable<string>>(Value));
end;

procedure TQryRecInfo.SetInfoNullableVariant(Index: Integer; const Value: TNullable<Variant>);
begin
  SetInfo(Index, TValue.From<TNullable<Variant>>(Value));
end;

end.

Review and fix the Fetall function