2

Take the following JSON. It is an array of objects.

[
  {
    "objects": [
      {
        "saleItems": [
          {
            "itemID": 1,
            "saleItemType": "Sale",
            "productCode": "072",
            "legacyProductCode": "1071",
            "legacyCategoryCode": "1071",
            "categoryCode": "1071",
            "subCategoryCode": "101",
            "amount": 12,
            "originalAmount": 15,
            "netAmount": 10,
            "originalNetAmount": 12.5,
            "vat": 2.5,
            "unitVAT": 1,
            "vatRate": 20,
            "unitMeasure": "EA",
            "unitPrice": 15,
            "quantity": 1,
            "saleChannel": 0
          }
        ]
      }
    ]
  },
  {
    "objects": [
      {
        "saleItems": [
          {
            "itemID": 1,
            "saleItemType": "Sale",
            "productCode": "072",
            "legacyProductCode": "1071",
            "legacyCategoryCode": "1071",
            "categoryCode": "1071",
            "subCategoryCode": "101",
            "amount": 12,
            "originalAmount": 15,
            "netAmount": 10,
            "originalNetAmount": 12.5,
            "vat": 2.5,
            "unitVAT": 1,
            "vatRate": 20,
            "unitMeasure": "EA",
            "unitPrice": 15,
            "quantity": 1,
            "saleChannel": 0
          }
        ]
      }
    ]
  },
  {
    "objects": [
      {
        "saleItems": [
          {
            "itemID": 1,
            "saleItemType": "Sale",
            "productCode": "072",
            "legacyProductCode": "1071",
            "legacyCategoryCode": "1071",
            "categoryCode": "1071",
            "subCategoryCode": "101",
            "amount": 12,
            "originalAmount": 15,
            "netAmount": 10,
            "originalNetAmount": 12.5,
            "vat": 2.5,
            "unitVAT": 1,
            "vatRate": 20,
            "unitMeasure": "EA",
            "unitPrice": 15,
            "quantity": 1,
            "saleChannel": 0
          }
        ]
      }
    ]
  }
]

The example output from this data needs to be:

{
  "sales": {
    "saleItems": [
      {
        "saleChannel": "1",
        "categoryCode": "1071",
        "productCode": "072",
        "salesAmountIncludingTax": 11.79,
        "salesAmountExcludingTax": 9.82,
        "discountAmountIncludingTax": 1.0,
        "discountAmountExcludingTax": 0.83,
        "salesQuantity": 10.0
      }
    ],
    "totalSalesAmountIncludingTax": 11.79,
    "totalSalesAmountExcludingTax": 9.82,
    "totalSalesQuantity": 10.0
  },
  "refunds": {
    "refundItems": [
      {
        "saleChannel": "1",
        "categoryCode": "1010",
        "productCode": "033",
        "refundAmountIncludingTax": 11.79,
        "refundAmountExcludingTax": 9.82,
        "discountAmountIncludingTax": 1.0,
        "discountAmountExcludingTax": 0.83,
        "refundQuantity": 10.0
      }
    ],
    "totalRefundAmountIncludingTax": 11.79,
    "totalRefundAmountExcludingTax": 9.82,
    "totalRefundQuantity": 10.0
  }
}

The complexity lies behind the aggregation/totalling. For example:

Within each element of the payload array, there must be an aggregation of the values based on a value. Every object contains saleItems array of objects. If the saleChannel = 0 and saleItemType = "Sale" and if the productCode = "X" (hasn't been seen before) this means there must be a "summing" of the values for every saleItem that the productCode = X.

Example: So for the first 2 objects there could be a productCode of "001". This means that these values must be totalled. The third objects productCode could be "002", a new object must be created within saleItems array of the output.

Example: (this can also be the same for refunds where the if statements are based if the saleItemType = "Refund")

"sales": {
  "saleItems": [
    {
      productCode 001
      salesAmountIncludingTax: **The sum of "amount" field, if saleChannel = 0 && saleItemType = "Sale**
    },
    {
      productCode 002
    }
  ]
}

The totalling of the values must follow this logic:

if saleChannel = 0
    group by subCategoryCode
        salesAmountIncludingTax = sum(saleItems.amount where saleItemType="Sale")
        salesAmountExcludingTax = sum(saleItems.netAmount where saleItemType="Sale")
    
if saleChannel != 0
    group by productCode
        salesAmountIncludingTax = sum(saleItems.originalAmount where saleItemType="Sale")
        salesAmountExcludingTax = sum(saleItems.originalNetAmount where saleItemType="Sale")
JackBruce
  • 23
  • 4
  • Hey Jack, are you missing the `productCode: 033` from the input? – George Jul 24 '20 at 13:26
  • Your example all product code are the same. Regarding that if you have `[pc=1,id =A, pc=1, id=B,pc=2,id=C,pc=1,id=D]` Is this case possible if so how should they be group? `[sum(A,B), sum(C), sum(D)]` or `[sum(A,B,D), sum(C)]` or `[sum(A,B), sum(C)]` – machaval Jul 24 '20 at 13:41
  • where does the discount in the output come from? – Shoki Jul 24 '20 at 14:42
  • https://pastebin.com/N5sRvBC9 Please check the pastebin link. This explains the aggregation logic – JackBruce Jul 24 '20 at 14:51

1 Answers1

4

If I understood correctly you need to do something like this:

%dw 2.0
output application/json

fun sumItems(itemsToSum, field1, field2) = do {
    var item = itemsToSum[0]
    var discounts = itemsToSum.priceAdjustments
    ---
    {
        "saleChannel": item.saleChannel,
        "categoryCode": item.categoryCode,
        "subCategoryCode": item.subCategoryCode,
        "productCode": item.productCode,
        "salesAmountIncludingTax": sum(itemsToSum[field1]),
        "salesAmountExcludingTax": sum(itemsToSum[field2]),
        "discountAmountIncludingTax": sum(discounts.amount default []),
        "discountAmountExcludingTax": sum(discounts.netAmount default []),
        "salesQuantity": sum(itemsToSum.quantity)
    }
}

var allItems       = flatten(payload..*saleItems default [])
var allSaleItems   = allItems filter ((item) -> item.saleItemType == "Sale")
var channel0       = allSaleItems filter ((item) -> item.saleChannel == 0)
var otherChannels  = allSaleItems filter ((item) -> item.saleChannel != 0)
var summedChannel0 = 
        channel0 
            groupBy ((item) -> item.subCategoryCode)
            pluck ((itemsToSum, groupName) -> do {
                sumItems(itemsToSum, "amount", "netAmount")
            })
var summedOtherChannels = 
        otherChannels 
            groupBy ((item) -> item.productCode)
            pluck ((itemsToSum, groupName) -> do {
                sumItems(itemsToSum, "originalAmount", "originalNetAmount")
            })
var saleItems = summedChannel0 ++ summedOtherChannels
---
{
    sales: {
        "saleItems": saleItems,
        "totalSalesAmountIncludingTax": sum(saleItems.salesAmountIncludingTax),
        "totalSalesAmountExcludingTax": sum(saleItems.salesAmountExcludingTax),
        "totalSalesQuantity": sum(saleItems.salesQuantity)
    }
}
Shoki
  • 1,508
  • 8
  • 13