php 嵌套集合数据中的MongoDB聚合

qco9c6ql  于 2023-02-28  发布在  PHP
关注(0)|答案(1)|浏览(128)
    • 在一个集合中，我保存了所有Store wise交易，并且在所有文档中有一个名为"items"的键，该键包含特定销售的所有项目。现在，我必须对其执行聚合并将数据发送到其他地方。下面我给出了集合数据**
[{
  "_id": {
    "$oid": "63ee26bcb64550cbb20de158"
  },
  "employee": "1",
  "custId": "6357cb852c61064d53040ff2",
  "store_code": "BAN-01",
  "store_no": "7",
  "shift": "Second",
  "employee_role": "POS Store Manager",
  "store_name": "175",
  "customer_name": "Vivek Kumar",
  "customer_email": "vivek.kumar98705@gmail.com",
  "customer_phone": "9958137221",
  "counter_code": "175-Counter-01",
  "deposit_amount": "100",
  "emp_code": "TCP004",
  "items": "[{\"internalid\":2969,\"type\":\"InvtPart\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"201450\",\"price\":\"252.50\",\"qty\":\"1\",\"itemamount\":\"265.13\",\"mrp\":\"252.50\",\"discount\":\"0.00\",\"tax\":\"12.63\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: BETADINE GARGLE 100ML\",\"item_upc_code\":\"5285239186\",\"item_hsn_code\":\"30041090\",\"uom\":\"EA\",\"batchDetails\":{\"#PJ0672\":{\"batch_expiry\":\"4/4/2025\",\"qty\":1}}},{\"internalid\":2963,\"type\":\"InvtPart\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"225124\",\"price\":\"7.00\",\"qty\":\"15\",\"itemamount\":\"110.25\",\"mrp\":\"7.00\",\"discount\":\"0.00\",\"tax\":\"5.25\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: ZINCOVIT 15TAB\",\"item_upc_code\":\"8035476959\",\"item_hsn_code\":\"21069099\",\"uom\":\"SH(15)\",\"batchDetails\":{\"#ZVT21203\":{\"batch_expiry\":\"4/4/2025\",\"qty\":15}}},{\"internalid\":3067,\"type\":\"InvtPart\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"315043\",\"price\":\"1.70\",\"qty\":\"45\",\"itemamount\":\"80.33\",\"mrp\":\"1.70\",\"discount\":\"0.00\",\"tax\":\"3.83\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: FOLVITE 45TAB\",\"item_upc_code\":\"9312066071\",\"item_hsn_code\":\"30045039\",\"uom\":\"SH(45)\",\"batchDetails\":{\"#GH3735\":{\"batch_expiry\":\"4/4/2025\",\"qty\":45}}},{\"internalid\":3069,\"type\":\"InvtPart\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"204009\",\"price\":\"75.44\",\"qty\":\"10\",\"itemamount\":\"792.12\",\"mrp\":\"75.44\",\"discount\":\"0.00\",\"tax\":\"37.72\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: DUPHASTON 10TAB\",\"item_upc_code\":\"3444014334\",\"item_hsn_code\":\"30043919\",\"uom\":\"SH(10)\",\"batchDetails\":{\"#KAVA2050\":{\"batch_expiry\":\"4/4/2025\",\"qty\":10}}}]",
  "total_amount": 1247.83,
  "total_tax": 59.43,
  "total_discount": 0,
  "subTotal": 1188.4,
  "payment_details": [
    [
      {
        "paymentMethod": "cash",
        "amount_paid": 1247.83,
        "transaction_date": "2/16/2023 4:58:19 pm",
        "payment_status": "Success",
        "transaction_id": "test123"
      }
    ]
  ],
  "auth_id": "master_ad14cca679b44ef1d78a3e_master",
  "created_by": "master_ad14cca679b44ef1d78a3e_master",
  "updated_by": "master_ad14cca679b44ef1d78a3e_master",
  "invoice_no": "WFINV#1",
  "updated_at": {
    "$date": {
      "$numberLong": "1676551868017"
    }
  },
  "created_at": {
    "$date": {
      "$numberLong": "1676551868017"
    }
  }
},
{
  "_id": {
    "$oid": "63ee2778b64550cbb20de17c"
  },
  "employee": "1",
  "custId": "6357cb852c61064d53040ff2",
  "store_code": "BAN-01",
  "store_no": "7",
  "shift": "Second",
  "employee_role": "POS Store Manager",
  "store_name": "175",
  "customer_name": "Vivek Kumar",
  "customer_email": "vivek.kumar98705@gmail.com",
  "customer_phone": "9958137221",
  "counter_code": "175-Counter-01",
  "deposit_amount": "100",
  "emp_code": "TCP004",
  "items": "[{\"internalid\":\"undefined\",\"type\":\"undefined\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"\",\"price\":\"2.30\",\"qty\":\"20\",\"itemamount\":\"48.30\",\"mrp\":\"2.30\",\"discount\":\"0.00\",\"tax\":\"2.3\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: COMBIFLAM 20TAB\",\"item_upc_code\":\"UPC000004\",\"item_hsn_code\":\"30049063\",\"uom\":\"SH(20)\",\"batchDetails\":{\"#1022563\":{\"batch_expiry\":\"4/4/2025\",\"qty\":20}}},{\"internalid\":\"undefined\",\"type\":\"undefined\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"\",\"price\":\"93.00\",\"qty\":\"1\",\"itemamount\":\"97.65\",\"mrp\":\"93.00\",\"discount\":\"0.00\",\"tax\":\"4.65\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: FEBREX-PLUS DS SYP 60ML\",\"item_upc_code\":\"1036121752\",\"item_hsn_code\":\"30049093\",\"uom\":\"EA\",\"batchDetails\":{\"#22430041\":{\"batch_expiry\":\"4/4/2025\",\"qty\":1}}}]",
  "total_amount": 145.95,
  "total_tax": 6.95,
  "total_discount": 0,
  "subTotal": 139,
  "payment_details": [
    [
      {
        "paymentMethod": "cash",
        "amount_paid": 145.95,
        "transaction_date": "2/16/2023 4:54:25 pm",
        "payment_status": "Success",
        "transaction_id": "test123"
      }
    ]
  ],
  "auth_id": "master_ad14cca679b44ef1d78a3e_master",
  "created_by": "master_ad14cca679b44ef1d78a3e_master",
  "updated_by": "master_ad14cca679b44ef1d78a3e_master",
  "invoice_no": "WFINV#4",
  "updated_at": {
    "$date": {
      "$numberLong": "1676552056047"
    }
  },
  "created_at": {
    "$date": {
      "$numberLong": "1676552056047"
    }
  }
}
]
    • 我尝试了以下聚合，但不起作用。**
$data = \DB::collection("online_orders")->raw(function($collection) use ($requestData) {
            return $collection->aggregate([
                    [
                        '$match' => [
                            'created_at' => [
                                '$gte' => $requestData['from'], 
                                '$lte' => $requestData['to']
                            ]
                        ],

                        '$group' => [
                            '_id' => [
                                'store_code' => '$store_code',
                                'sku' => '$items.itemName'
                            ],
                            'items' => '$items',
                            'sold_qty' => [ '$sum' => '$items.qty' ]
                        ]

                    ]
                ]);
            });
    • 管道后**

预期结果：
结果键与集合键的关系仅供参考**

sku -> itemName
sold_qty -> how many time that particular item has been sold store code wise 
return_qty -> curerntly leave it as 0
total_amount -> total amount of particular item sold 
uom -> uom
batchNo -> batchDetails key is batchNo
Quantity -> batchDetails -- qty




{
            "order_type": "CS",
            "data": [
                {
                    "store_code": "175",
                    "cash_sale_details": [
                        {
                            "sku": "321405",
                            "sold_qty": "5",
                            "return_qty": "0",
                            "total_amount": "69.83",
                            "uom": "EA",
                            "batchDetails": [
                                {
                                    "batchNo": "LOT121",
                                    "Quantity": 5
                                }
                            ]
                        },
                        {
                            "sku": "223327",
                            "sold_qty": "5",
                            "return_qty": "0",
                            "total_amount": "488.25",
                            "uom": "EA",
                            "batchDetails": [
                                {
                                    "batchNo": "22430041",
                                    "Quantity": 5
                                }
                            ]
                        },                            
                        {
                            "sku": "222045",
                            "sold_qty": "5",
                            "return_qty": "0",
                            "total_amount": "842.89",
                            "uom": "EA",
                            "batchDetails": [
                                {
                                    "batchNo": "B2329D164",
                                    "Quantity": 5
                                }
                            ]
                        },
                        {
                            "sku": "341106",
                            "sold_qty": "5",
                            "return_qty": "0",
                            "total_amount": "623.44",
                            "uom": "EA",
                            "batchDetails": [
                                {
                                    "batchNo": "AS2211",
                                    "Quantity": 5
                                }
                            ]
                        }
                    ]
                }
            ]
        }
  • 谢谢 *
php

1条答案

按热度按时间
nhhxz33t

nhhxz33t1#

我更改了如下所示的集合

[
{
  "_id": {
    "$oid": "63f5d5f66229de80d201113b"
  },
  "employee": "9",
  "custId": "6357cb852c61064d53040ff2",
  "store_code": "BAN-01",
  "store_no": "7",
  "shift": "General",
  "employee_role": "Sales Person",
  "store_name": "175",
  "customer_name": "RahulTest raja",
  "customer_email": "hdsudashudasuduha@gamail.com",
  "customer_phone": "8 (787) 878-7878",
  "counter_code": "175-Counter-03",
  "deposit_amount": "0",
  "emp_code": "TCP004",
  "order_type": "INV",
  "custterms": "NET 15",
  "status": "Pending Billing",
  "items": [
    {
      "internalid": "undefined",
      "type": "undefined",
      "islotitem": false,
      "isserialitem": false,
      "isspecialorderitem": false,
      "itemName": "321405",
      "price": "14.00",
      "qty": "11",
      "itemamount": "161.70",
      "mrp": "14.00",
      "discount": "0.00",
      "tax": "7.70",
      "promotion": "NA",
      "item_display_name": "UOM: JANUMET 50MG/500MG 15TAB",
      "item_upc_code": "9346943598",
      "item_hsn_code": "30021500",
      "uom": "EA",
      "batchDetails": {
        "#LOT121": {
          "batch_expiry": "null",
          "qty": 5
        },
        "#LOT122": {
          "batch_expiry": "null",
          "qty": 6
        }
      }
    }
  ],
  "total_amount": 161.7,
  "total_tax": 7.7,
  "total_discount": 0,
  "subTotal": 154,
  "payment_details": [
    [
      {
        "type": "terms",
        "amount": 0,
        "termsId": "NET 15"
      }
    ]
  ],
  "auth_id": "master_ad14cca679b44ef1d78a3e_master",
  "created_by": "master_ad14cca679b44ef1d78a3e_master",
  "updated_by": "master_ad14cca679b44ef1d78a3e_master",
  "invoice_no": "WFINV#1",
  "updated_at": {
    "$date": {
      "$numberLong": "1677055478717"
    }
  },
  "created_at": {
    "$date": {
      "$numberLong": "1677055478717"
    }
  }
}
]

然后编写完成此工作的以下聚合

$data = Order::raw(function ($collection) use ($requestData) {
                return $collection->aggregate([

                    [
                        '$match' => [
                            'created_at' => [
                                '$gte' => $requestData['from'],
                                '$lte' => $requestData['to']
                            ],
                            'order_type' => $requestData['order_type']
                        ],
                    ],

                    [
                        '$unwind' => '$items'
                    ],

                    [
                            '$group' => [
                            '_id' => [
                                'store_code' => '$store_code',
                                'sku' => '$items.itemName',
                                'order_type' => '$order_type',                                
                            ],
                            'sold_qty' => [ '$first' => '$items.qty'],
                            // 'return_qty' => [0],
                            'total_amount' => ['$first' => '$items.itemamount' ],
                            'uom' => ['$first' => '$items.uom' ],
                            'batchDetails' => [ '$first' => [ '$objectToArray' => '$items.batchDetails'] ],
                            // 'items' => [ '$first' => '$items' ],
                        ]
                    ],
                ]);
            });
赞(0）分享  回复(0）举报  2023-02-28
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com