# HG changeset patch
# User Alain Mazy <am@orthanc.team>
# Date 1734368980 -3600
# Node ID 591ce1e2d36745258a99bd66d0e39397563df55e
# Parent  3d853c8f5a9e88a935533ddda36b6579710f186e
added ordering with cast to int/float

diff -r 3d853c8f5a9e -r 591ce1e2d367 Framework/Plugins/ISqlLookupFormatter.cpp
--- a/Framework/Plugins/ISqlLookupFormatter.cpp	Mon Dec 16 16:36:12 2024 +0100
+++ b/Framework/Plugins/ISqlLookupFormatter.cpp	Mon Dec 16 18:09:40 2024 +0100
@@ -924,8 +924,21 @@
         {
           orderByField = "CASE WHEN order" + boost::lexical_cast<std::string>(i) + ".value IS NULL THEN 1 ELSE 0 END, ";
         }
-        orderByField += "order" + boost::lexical_cast<std::string>(i) + ".value";
-         
+
+        switch (ordering.cast())
+        {
+          case Orthanc::DatabasePluginMessages::OrderingCast::ORDERING_CAST_INT:
+            orderByField += "CAST(order" + boost::lexical_cast<std::string>(i) + ".value AS " + formatter.FormatIntegerCast() + ")";
+            break;
+          case Orthanc::DatabasePluginMessages::OrderingCast::ORDERING_CAST_FLOAT:
+            orderByField += "CAST(order" + boost::lexical_cast<std::string>(i) + ".value AS " + formatter.FormatFloatCast() + ")";
+            break;
+          case Orthanc::DatabasePluginMessages::OrderingCast::ORDERING_CAST_STRING:
+          default:
+            orderByField += "order" + boost::lexical_cast<std::string>(i) + ".value";
+            break;
+        }
+
         if (ordering.direction() == Orthanc::DatabasePluginMessages::OrderingDirection::ORDERING_DIRECTION_ASC)
         {
           orderByField += " ASC";
diff -r 3d853c8f5a9e -r 591ce1e2d367 Framework/Plugins/ISqlLookupFormatter.h
--- a/Framework/Plugins/ISqlLookupFormatter.h	Mon Dec 16 16:36:12 2024 +0100
+++ b/Framework/Plugins/ISqlLookupFormatter.h	Mon Dec 16 18:09:40 2024 +0100
@@ -73,6 +73,10 @@
 
     virtual bool SupportsNullsLast() const = 0;
 
+    virtual std::string FormatIntegerCast() const = 0;
+
+    virtual std::string FormatFloatCast() const = 0;
+
     static void GetLookupLevels(Orthanc::ResourceType& lowerLevel,
                                 Orthanc::ResourceType& upperLevel,
                                 const Orthanc::ResourceType& queryLevel,
diff -r 3d853c8f5a9e -r 591ce1e2d367 Framework/Plugins/IndexBackend.cpp
--- a/Framework/Plugins/IndexBackend.cpp	Mon Dec 16 16:36:12 2024 +0100
+++ b/Framework/Plugins/IndexBackend.cpp	Mon Dec 16 18:09:40 2024 +0100
@@ -2282,6 +2282,38 @@
       return (dialect_ == Dialect_PostgreSQL);
     }
 
+    virtual std::string FormatIntegerCast() const
+    {
+      switch (dialect_)
+      {
+        case Dialect_MSSQL:
+          return "INT";
+        case Dialect_SQLite:
+        case Dialect_PostgreSQL:
+          return "INTEGER";
+        case Dialect_MySQL:
+          return "SIGNED";
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+      }
+    }
+
+    virtual std::string FormatFloatCast() const
+    {
+      switch (dialect_)
+      {
+        case Dialect_SQLite:
+          return "REAL";
+        case Dialect_MSSQL:
+        case Dialect_PostgreSQL:
+          return "FLOAT";
+        case Dialect_MySQL:
+          return "DECIMAL(10,10)";
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+      }
+    }
+
     void PrepareStatement(DatabaseManager::StandaloneStatement& statement) const
     {
       statement.SetReadOnly(true);