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;
var
qr: TUniQuery;
Ctx: TRttiContext;
ObjType: TRttiType;
Prop: TRttiProperty;
Field: TField;
NullableFieldName: string;
RecInfo: TQryRecInfo;
begin
Result := TObjectList.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(Value));
end;

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

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

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

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

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

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

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

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

end.

Review and fix the FetchAll function, but use generics to handle the descendants of TQryRecInfo. Give a concrete usage example