========    SO List   ===============

========    ~/eWms/salesOrder/list/    ===============

db.EvolveSalesOrder.aggregate([

                {
                    "$lookup": {
                        "from": "EvolveCustomer",
                        "localField": "EvolveCustomer_ID",
                        "foreignField": "_id",
                        "as": "EvolveCustomer_ID",

                    }
                },
                {
                    $unwind: {
                        path: "$EvolveCustomer_ID",
                        preserveNullAndEmptyArrays: true
                    }
                },

                {
                    "$lookup": {
                        "from": "EvolveAddress",
                        "localField": "EvolveBillTo_ID",
                        "foreignField": "_id",
                        "as": "EvolveBillTo_ID",

                    }
                },
                {
                    $unwind: {
                        path: "$EvolveBillTo_ID",
                        preserveNullAndEmptyArrays: true
                    }
                },

                {
                    "$lookup": {
                        "from": "EvolveAddress",
                        "localField": "EvolveShipTo_ID",
                        "foreignField": "_id",
                        "as": "EvolveShipTo_ID",

                    }
                },
                {
                    $unwind: {
                        path: "$EvolveShipTo_ID",
                        preserveNullAndEmptyArrays: true
                    }
                },



                {
                    "$lookup": {
                        "from": "EvolveCreditTerms",
                        "localField": "EvolveCreditTerms_ID",
                        "foreignField": "_id",
                        "as": "EvolveCreditTerms_ID",

                    }
                },
                {
                    $unwind: {
                        path: "$EvolveCreditTerms_ID",
                        preserveNullAndEmptyArrays: true
                    }
                },
                {
                    "$lookup": {
                        "from": "EvolveProject",
                        "localField": "EvolveProject_ID",
                        "foreignField": "_id",
                        "as": "EvolveProject_ID",

                    }
                },
                {
                    $unwind: {
                        path: "$EvolveProject_ID",
                        preserveNullAndEmptyArrays: true
                    }
                },
                {
                    "$lookup": {
                        "from": "EvolveCurrency",
                        "localField": "EvolveCurrency_ID",
                        "foreignField": "_id",
                        "as": "EvolveCurrency_ID",
                    }
                },
                {
                    $unwind: {
                        path: "$EvolveCurrency_ID",
                        preserveNullAndEmptyArrays: true
                    }
                },
                { $unwind: '$EvolveSalesOrderDetails' }, 
                {
                    "$lookup": {
                        "from": "EvolveUom",
                        "localField": "EvolveSalesOrderDetails.EvolveUom_ID",
                        "foreignField": "_id",
                        "as": "EvolveSalesOrderDetails.EvolveUom_ID",

                    }
                },
                {
                    $unwind: {
                        path: "$EvolveSalesOrderDetails.EvolveUom_ID",
                        preserveNullAndEmptyArrays: true
                    }
                },
                {
                    "$lookup": {
                        "from": "EvolveItem",
                        "localField": "EvolveSalesOrderDetails.EvolveItem_ID",
                        "foreignField": "_id",
                        "as": "EvolveSalesOrderDetails.EvolveItem_ID",

                    }
                },
                {
                    $unwind: {
                        path: "$EvolveSalesOrderDetails.EvolveItem_ID",
                        preserveNullAndEmptyArrays: true
                    }
                },
                {
                    $group: {
                        _id: "$_id",
                        EvolveSalesOrder_Number: { $first: "$EvolveSalesOrder_Number" },
                        EvolveSalesOrder_Type: { $first: "$EvolveSalesOrder_Type" },
                        EvolveUnit_ID: { $first: "$EvolveUnit_ID" },
                        EvolveCustomer_ID: { $first: "$EvolveCustomer_ID" },
                        EvolvePO_Number: { $first: "$EvolvePO_Number" },
                        EvolveSalesOrder_Date: { $first: "$EvolveSalesOrder_Date" },
                        EvolveSalesOrder_ReqDate: { $first: "$EvolveSalesOrder_ReqDate" },
                        EvolveSalesOrder_DueDate: { $first: "$EvolveSalesOrder_DueDate" },
                        EvolveBillTo_ID: { $first: "$EvolveBillTo_ID" },
                        EvolveShipTo_ID: { $first: "$EvolveShipTo_ID" },
                        EvolveSalesOrder_TaxEnvironment: { $first: "$EvolveSalesOrder_TaxEnvironment" },
                        EvolveSalesOrder_TaxClass: { $first: "$EvolveSalesOrder_TaxClass" },
                        EvolveSalesOrder_TotalCustomerPrice: { $first: "$EvolveSalesOrder_TotalCustomerPrice" },
                        EvolveSalesOrder_LandedCost: { $first: "$EvolveSalesOrder_LandedCost" },
                        EvolveSalesOrder_ProfitMargin: { $first: "$EvolveSalesOrder_ProfitMargin" },
                        EvolveSalesOrder_Freight: { $first: "$EvolveSalesOrder_Freight" },
                        EvolveCreditTerms_ID: { $first: "$EvolveCreditTerms_ID" },
                        EvolveProject_ID: { $first: "$EvolveProject_ID" },
                        EvolveCurrency_ID: { $first: "$EvolveCurrency_ID" },
                        EvolveSalesOrder_Status: { $first: "$EvolveSalesOrder_Status" },
                        EvolveSalesOrder_Comments: { $first: "$EvolveSalesOrder_Comments" },
                        EvolveSalesOrderAttributes: { $first: "$EvolveSalesOrderAttributes" },
                        EvolveSalesOrder_IsScheduled: { $first: "$EvolveSalesOrder_IsScheduled" },
                        EvolveSalesOrder_StandardPackingQty: { $first: "$EvolveSalesOrder_StandardPackingQty" },
                        EvolveSalesOrderDetails: { $first: "$EvolveSalesOrderDetails" },
                        EvolveSalesOrderDetails: { $push: "$EvolveSalesOrderDetails" }
                    }
                },
                 {
                    $sort: {
                        _id:
                            -1
                    }
                },
               

                {
                    $facet: {
                        result: [],
                        totalCount: [{
                            $count: 'count'
                        }]
                    }
                },
                
                ])

========================================================