mongodb-aggregate.js

var national = ["Join", "mobilePOST", "Tango GSM", "National", "SMSPremium"];
var voicemail = ["IVR", "Tango VM", "mobPOST VM"];

/* ============================================================================================================================================ */

var match = { $match : 
    {"SERVICE_TYPE" : {"$in" : [1,2,3,4] } } 
};

/* ============================================================================================================================================ */

var lookup_billing_cycles = { $lookup: 
    { from: "BILLING_CYCLE", localField: "BILLING_CYCLE_ID", foreignField: "BILLING_CYCLE_ID", as: "_BILLING_CYCLE" } 
};

/* ============================================================================================================================================ */

var lookup_charge_codes = { $lookup: 
    { from: "ACCT_ITEM_TYPE", localField: "ACCT_ITEM_TYPE_ID1", foreignField: "ACCT_ITEM_TYPE_ID", as: "_ACCT_ITEM_TYPE" } 
};

/* ============================================================================================================================================ */

//FROM COUNTRY
//zone_map_name: EVENT_EXTEND_PROPERTY9, 
//zone_code : EX_PROPERTY5
var lookup_from_zones = { $lookup: 
    { from: "ZONES",
      let: {
          zone_map_name: "$EVENT_EXTEND_PROPERTY9", 
          zone_code: "$EX_PROPERTY5"
      }, 
      pipeline: [ 
        { $match: 
            { $expr: 
                { $and: [ 
                    { $eq: [ "$ZONEMAP_NAME", "$$zone_map_name" ] },
                    { $eq: [ "$ZONE_CODE", "$$zone_code"] }
                    ] 
                }
            }
        } 
      ], 
      as: "FROM_ZONE" 
    } 
};

/* ============================================================================================================================================ */

//TO COUNTRY
//zone_map_name: EVENT_EXTEND_PROPERTY10, 
//zone_code : EX_PROPERTY7
var lookup_to_zones = { $lookup: 
    { from: "ZONES",
      let: {
          zone_map_name: "$EVENT_EXTEND_PROPERTY10", 
          zone_code: "$EX_PROPERTY7"
      }, 
      pipeline: [ 
        { $match: 
            { $expr: 
                { $and: [ 
                    { $eq: [ "$ZONEMAP_NAME", "$$zone_map_name" ] },
                    { $eq: [ "$ZONE_CODE", "$$zone_code"] }
                    ] 
                }
            }
        } 
      ], 
      as: "TO_ZONE" 
    } 
};

/* ============================================================================================================================================ */

var project_from_place = {
            //SOURCE: EX_PROPERTY6
            //DESTINATION: EX_PROPERTY8 
            $switch: {
                branches: [
                    {case: { $eq: ["$SERVICE_TYPE", 1]}, then: 
                        { $cond: [ {$eq: ["$EX_PROPERTY6", "mobilePOST"]}, "National", "Roaming" ] }
                    },
                    {case: { $or: [ 
                            { $eq: ["$SERVICE_TYPE", 3] },
                            { $eq: ["$SERVICE_TYPE", 4] } 
                        ]}, then: 
                        { $cond: [ {$in: ["$EX_PROPERTY6", national] }, "National", "Roaming" ] }
                    },
                    {case: {$eq: ["$SERVICE_TYPE", 2]}, then: 
                        { $cond: [ {$in: ["$EX_PROPERTY6", national] }, "National", "Roaming" ] }
                    }
                ]
            }
        };

/* ============================================================================================================================================ */

var project_to_place = {
            //SOURCE: EX_PROPERTY6
            //DESTINATION: EX_PROPERTY8 
            $switch: {
                branches: [
                    //DATA
                    {case: { $eq: ["$SERVICE_TYPE", 1]}, then: "APN"},
                    //SMS or MMS
                    {case: { $or: [
                        { $eq: ["$SERVICE_TYPE", 3] },
                        { $eq: ["$SERVICE_TYPE", 4] },
                    ]}, then: 
                        { $cond: [ {$in: ["$EX_PROPERTY8", national] }, "National", "International" ] }
                    },
                    //VOICE
                    {case: { $eq: ["$SERVICE_TYPE", 2]}, then: 
                        { $cond: [ {$in: ["$EX_PROPERTY8", national] }, "National", 
                            { $cond: [ {$in: ["$EX_PROPERTY8", voicemail] }, "Voicemail", "International" ] }
                        ] }
                    }
                ]
            }
        };
        
/* ============================================================================================================================================ */

var project_charge_amounts = {
          $switch: {
              branches : [
                    { case: { $eq: ["$ACCT_RES_ID1", 1] }, then : "$CHARGE1" }, 
                    { case: { $eq: ["$ACCT_RES_ID2", 1] }, then : "$CHARGE2" }, 
                    { case: { $eq: ["$ACCT_RES_ID3", 1] }, then : "$CHARGE3" }, 
                    { case: { $eq: ["$ACCT_RES_ID4", 1] }, then : "$CHARGE4" }, 
                    { case: { $eq: ["$ACCT_RES_ID5", 1] }, then : "$CHARGE5" }, 
                    { case: { $eq: ["$ACCT_RES_ID6", 1] }, then : "$CHARGE6" }, 
                    { case: { $eq: ["$ACCT_RES_ID7", 1] }, then : "$CHARGE7" }, 
                    { case: { $eq: ["$ACCT_RES_ID8", 1] }, then : "$CHARGE8" }, 
                    { case: { $eq: ["$ACCT_RES_ID9", 1] }, then : "$CHARGE9" }
              ],
              default: 0
          }
        };

/* ============================================================================================================================================ */
        
var project_type_com = {
            $switch: {
                branches : [
                    { case: { $eq: ["$SERVICE_TYPE", 1] }, then : "GPRS" }, 
                    { case: { $eq: ["$SERVICE_TYPE", 2] }, then : "VOICE" },
                    { case: { $eq: ["$SERVICE_TYPE", 3] }, then : "SMS" },
                    { case: { $eq: ["$SERVICE_TYPE", 4] }, then : "MMS" }
                ],
                default: "?"
            }            
        };

/* ============================================================================================================================================ */

var project_type_unit = {
            $switch: {
                branches : [
                    { case: { $eq: ["$SERVICE_TYPE", 1] }, then : "kb" }, 
                    { case: { $eq: ["$SERVICE_TYPE", 2] }, then : "s" },
                    { case: { $eq: ["$SERVICE_TYPE", 3] }, then : "SMS" },
                    { case: { $eq: ["$SERVICE_TYPE", 4] }, then : "MMS" }
                ],
                default: "?"
            }            
        };

/* ============================================================================================================================================ */

var project = { $project : 
    {
        "event_id": "$UNIQUE_CDR_SEQ", 
        "from_country": {"$arrayElemAt": ["$FROM_ZONE.ISO2", 0]},
        "to_country": {"$arrayElemAt": ["$TO_ZONE.ISO2", 0]},
        "charge_code": {"$arrayElemAt": ["$_ACCT_ITEM_TYPE.ACCT_ITEM_TYPE_NAME", 0]},
        "from_number": "$CALLING_NBR", 
        "to_number": "$CALLED_NBR",
        "type_com": project_type_com,
        "type_unit": project_type_unit,
        "charge_amount": project_charge_amounts,
        "from_place_cat": project_from_place,
        "to_place_cat": project_to_place,
        "customer_id": "$ACCT_ID",
        "bar_no": "$CUST_ID",
        "subscriber_id": "$SUBS_ID",
        "from_place": "$EX_PROPERTY6",
        "to_place": "$EX_PROPERTY8",
        "cycle_year" : {"$arrayElemAt": [ { $split: [ {"$arrayElemAt": ["$_BILLING_CYCLE.START_DATE",0 ] }, "-"] }, 0 ] },
        "cycle_month" : {"$arrayElemAt": [ { $split: [ {"$arrayElemAt": ["$_BILLING_CYCLE.START_DATE", 0 ] }, "-"] }, 1 ] }
    } 
};

/* ============================================================================================================================================ */

db.CDR.aggregate([
  match, 
  lookup_billing_cycles, 
  lookup_charge_codes, 
  lookup_from_zones,
  lookup_to_zones, 
  project,
  { $out : "HUHUUHU" }
]);