OBPIH-6332 Add string interpolation to query

alannadolny · alannadolny · commit 4ad33e4584dc · 2024-05-20T10:31:05.000+02:00
diff --git a/grails-app/services/org/pih/warehouse/dashboard/IndicatorDataService.groovy b/grails-app/services/org/pih/warehouse/dashboard/IndicatorDataService.groovy
@@ -10,6 +10,7 @@ import org.grails.plugins.web.taglib.ApplicationTagLib
 import org.grails.web.json.JSONObject
 import org.joda.time.LocalDate
 import org.pih.warehouse.LocalizationUtil
+import org.pih.warehouse.api.StockMovementDirection
 import org.pih.warehouse.core.Location
 import org.pih.warehouse.inventory.InventorySnapshot
 import org.pih.warehouse.inventory.TransactionCode
@@ -1112,9 +1113,24 @@ class IndicatorDataService {
         return graphData.toJson()
     }
 
-    Map getBackdatedShipmentsChart(String query, String locationId, Integer monthsLimit) {
+    Map getBackdatedShipmentsChart(String locationId, Integer monthsLimit, StockMovementDirection direction) {
         Map<String, Integer> data = Constants.backdataAxes
         Date timeLimit = LocalDate.now().minusMonths(monthsLimit).toDate()
+        String transactionProperty = direction == StockMovementDirection.OUTBOUND ? 'outgoing_shipment_id' : 'incoming_shipment_id'
+        String shipmentLocationProperty = direction == StockMovementDirection.OUTBOUND ? 'origin_id' : 'destination_id'
+        String query = """
+            SELECT (
+                CASE 
+                    WHEN DATEDIFF(t.date_created, t.transaction_date) > 7 THEN '7+ days'
+                    ELSE CONCAT(DATEDIFF(t.date_created, t.transaction_date), ' days')
+                END
+            ) as days_backdated, COUNT(t.id) as shipments FROM shipment s
+            INNER JOIN transaction t ON t.${transactionProperty} = s.id
+            WHERE s.${shipmentLocationProperty} = :locationId 
+            AND t.date_created > :timeLimit
+            GROUP BY days_backdated
+            HAVING days_backdated > :daysOffsetjebane
+        """
         List queryData = dataService.executeQuery(query, [
                 locationId: locationId,
                 timeLimit: timeLimit,
@@ -1136,40 +1152,12 @@ class IndicatorDataService {
 
     @Cacheable(value = "dashboardCache", key = { "getBackdatedOutboundShipments-${locationId}${monthsLimit}" })
     Map getBackdatedOutboundShipmentsData(String locationId, Integer monthsLimit) {
-        String query = """
-            SELECT (
-                CASE 
-                    WHEN DATEDIFF(t.date_created, t.transaction_date) > 7 THEN '7+ days'
-                    ELSE CONCAT(DATEDIFF(t.date_created, t.transaction_date), ' days')
-                END
-            ) as days_backdated, COUNT(t.id) as shipments  FROM shipment s
-            INNER JOIN transaction t ON t.outgoing_shipment_id  = s.id
-            WHERE s.origin_id = :locationId 
-            AND t.date_created > :timeLimit
-            GROUP BY days_backdated
-            HAVING days_backdated > :daysOffset
-        """
-
-        return getBackdatedShipmentsChart(query, locationId, monthsLimit)
+        return getBackdatedShipmentsChart(locationId, monthsLimit, StockMovementDirection.OUTBOUND)
     }
 
     @Cacheable(value = "dashboardCache", key = { "getBackdatedInboundShipments-${locationId}${monthsLimit}" })
     Map getBackdatedInboundShipmentsData(String locationId, Integer monthsLimit) {
-        String query = """
-            SELECT (
-                CASE 
-                    WHEN DATEDIFF(t.date_created, t.transaction_date) > 7 THEN '7+ days'
-                    ELSE CONCAT(DATEDIFF(t.date_created, t.transaction_date), ' days')
-                END
-            ) as days_backdated, COUNT(t.id) as shipments  FROM shipment s
-            INNER JOIN transaction t ON t.incoming_shipment_id  = s.id
-            WHERE s.destination_id = :locationId 
-            AND t.date_created > :timeLimit
-            GROUP BY days_backdated
-            HAVING days_backdated > :daysOffset
-        """
-
-        return getBackdatedShipmentsChart(query, locationId, monthsLimit)
+        return getBackdatedShipmentsChart(locationId, monthsLimit, StockMovementDirection.INBOUND)
     }
 
     private List fillLabels(int querySize) {
