CODEWITHSUNDEEP
mongodbmongodb-query
Davi Moreira
Davi Moreira

Reputation: 25

Trying to use $cond to $sum and $subtract

My documents:

_id:"DwNMQtHYopXKK3rXt"
client_id:"ZrqKavXX8ieGpx5ae"
client_name:"luana"
companyId:"z3ern2Q7rdvviYCGv"
is_active:true
client_searchable_name:"luana"
status:"paid"
items:Object
id:912602
gross_amount:1000
type:"service"
description:"Pedicure com Zé (pacote)"
item_id:"bjmmPPjqKdWfwJqtC"
user_id:"gWjCskpHF2a3xHYy9"
user_id_commission:50
user_id_amount:0
use_package:true
quantity:1
item_costs:Array
discount_cost:Object
 type:"package"
 value:100
 charge_from:"company_only"
entity_id:"LLRirWu5DabkRna7X"
created_at:2019-10-29T10:35:39.493+00:00
updated_at:2019-10-29T10:36:42.983+00:00
version:"2"
created_by:"2QBRDN9MACQagSkJr"
amount:0
multiple_payment_methods:Array
closed_at:2019-10-29T10:36:52.781+00:00

So i made a $project:

{
  _id: 0,
  closed_at: 1,
  serviceId: "$items.item_id",
  serviceAmount: "$items.gross_amount",
  discounts:"$items.discount_cost"
}

And then $group

 _id: {
    month: { $month: "$closed_at" },
    serviceId: "$serviceId",
    discountType: "$discounts.type",
    discountValue: "$discounts.value"
  },
  totalServiceAmount: {
    $sum: "$serviceAmount"
  }
}

I'm trying to make a $sum of values of the categories in my DB, actually i filtered all the data, so i have exactly what i need, like that;

_id:Object
"month":10
"serviceId":"MWBqhMyW8ataGxjBT"
"discountType":""courtesy"
"discountValue":100

"totalServiceAmount":5000

So, i have 5 types of discounts on my DB, they are: Percentage (discount in percentage), courtesy (make the service amount 0), package (make the service amount 0), gross (gross discount of value) and null if there's no discount o value.

so, if the type of discount is;

Percentage: I need to subtract the discountValue for the totalServiceAmount (discountValue will be in percentage, how i do that subtract if total serviceAmount is on gross value)

Courtesy and package: I need to transform the totalServiceAmount in 0 value.

Gross: i need to subtract the discountValue for the totalServiceAmount.

Null: just let totalServiceAmount.

I tried like that, to make some test, but i really don't know if i'm goign to the right path, the result was null for every amountWithDiscount.

{
    $project: {
    {
    amountWithDiscount: {
    $cond: {
      if: {
        $eq: ["$_id.discountType", "null"]
      },
      then: "$serviceAmount", else: {
        $cond: {
          if: {
            $eq: ["$_id.discountType", "gross"]
          },
          then: {
            $subtract: ["$serviceAmount", "$_id.discountValue"]
          },
          else: "$serviceAmount"
        }
      }
    }
  }
}

Make sense?

Upvotes: 1

Views: 650

Answers (1)

Haruo
Haruo

Reputation: 516

I create a collection with your grouping result:

01) Example of Documents:

[
  {
    "_id": "5db9ca609a17899b8ba6650d",
    "month": 10,
    "serviceId": "MWBqhMyW8ataGxjBT",
    "discountType": "courtesy",
    "discountValue": 0,
    "totalServiceAmount": 5000
  },
  {
    "_id": "5db9d0859a17899b8ba66856",
    "month": 10,
    "serviceId": "MWBqhMyW8ataGxjBT",
    "discountType": "gross",
    "discountValue": 100,
    "totalServiceAmount": 5000
  },
  {
    "_id": "5db9d0ac9a17899b8ba66863",
    "month": 10,
    "serviceId": "MWBqhMyW8ataGxjBT",
    "discountType": "percentage",
    "discountValue": 10,
    "totalServiceAmount": 5000
  },
  {
    "_id": "5db9d0d89a17899b8ba6687f",
    "month": 10,
    "serviceId": "MWBqhMyW8ataGxjBT",
    "discountType": null,
    "discountValue": 10,
    "totalServiceAmount": 6000
  }
]

02) Query:

db.collection.aggregate([
  {
    $project: {
      discountType: "$discountType",
      amountWithDiscount: {
        $cond: {
          if: {
            $eq: [
              "$discountType",
              null
            ]
          },
          then: "$totalServiceAmount",
          else: {
            $cond: {
              if: {
                $eq: [
                  "$discountType",
                  "gross"
                ]
              },
              then: {
                $subtract: [
                  "$totalServiceAmount",
                  "$discountValue"
                ]
              },
              else: {
                $cond: {
                  if: {
                    $eq: [
                      "$discountType",
                      "percentage"
                    ]
                  },
                  then: {
                    $multiply: [
                      "$totalServiceAmount",
                      {
                        $subtract: [
                          1,
                          {
                            $divide: [
                              "$discountValue",
                              100
                            ]
                          }
                        ]
                      }
                    ]
                  },
                  else: "$totalServiceAmount"
                }
              }
            }
          }
        }
      }
    }
  }
])

A working example at https://mongoplayground.net/p/nU7vhGN-uSp.

I don't know if I fully understand your problem, but take a look and see if it solves your problem.

Upvotes: 1

Related Questions