HistoryModel.php

	function get_message_summary_by_range($start_date, $end_date)
	{
		// Convert the date to start and end for MongoDB ISODate format
		$start_date = new UTCDateTime(new \DateTime($start_date . ' 00:00:00', new DateTimeZone('UTC')));
		$end_date = new UTCDateTime(new \DateTime($end_date . ' 23:59:59', new DateTimeZone('UTC')));

		// get the date range correct
		$match_stage = [
			'$match' => [
				'date_sent' => [
					'$gte' => $start_date,
					'$lt'  => $end_date,
				],
			],
		];

		// Define the aggregation pipeline
		$pipeline = [
			$match_stage,
			[
				'$addFields' => [
					'formatted_date' => [
						'$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$date_sent'],
					],
				],
			],
			[
				'$lookup' => [
					'from'         => 'clickhistory',
					'localField'   => 'tracking',
					'foreignField' => 'click_id',
					'as'           => 'click_info',
				],
			],
			[
				'$unwind' => [
					'path'                       => '$click_info',
					'preserveNullAndEmptyArrays' => TRUE,
				],
			],
			[
				'$lookup' => [
					'from'         => 'conversionhistory',
					'localField'   => 'tracking',
					'foreignField' => 'click_id',
					'as'           => 'conversion_info',
				],
			],
			[
				'$unwind' => [
					'path'                       => '$conversion_info',
					'preserveNullAndEmptyArrays' => TRUE,
				],
			],
			[
				'$addFields' => [
					'has_click' => [
						'$cond' => [['$gt' => ['$click_info', NULL]], 1, 0], // Add field to indicate if there was a click
					],
				],
			],
			[
				'$group' => [
					'_id' => [
						'drop_date'     => '$formatted_date',
						'drop_id'       => '$drop_id',
						'sender_id'     => '$sender_id',
						'list_id'       => '$list_id',
						'vendor'        => '$vendor',
						'campaign_id'   => '$campaign_id',
						'carrier_group' => '$carrier_group',
						'carrier'       => '$carrier',
						'template_id'   => '$template_id',
						'status'        => '$status',
					],
					'total_messages' => ['$sum' => 1],
					'total_payout'   => ['$sum' => ['$toDouble' => '$conversion_info.payout']],
					'total_clicks'   => ['$sum' => ['$cond' => [['$gt' => ['$click_info.click_id', NULL]], 1, 0]]],
					'unique_clicks'  => ['$sum' => '$has_click'],
				],
			],
			[
				'$project' => [
					'_id'            => 0, // Exclude the _id field
					'drop_date'      => '$_id.drop_date',
					'drop_id'        => '$_id.drop_id',
					'sender_id'      => '$_id.sender_id',
					'list_id'        => '$_id.list_id',
					'vendor'         => '$_id.vendor',
					'campaign_id'    => '$_id.campaign_id',
					'carrier_group'  => '$_id.carrier_group',
					'carrier'        => '$_id.carrier',
					'template_id'    => '$_id.template_id',
					'status'         => '$_id.status',
					'total_messages' => 1,
					'total_payout'   => 1,
					'total_clicks'   => 1,
					'unique_clicks'  => 1,
				],
			],
			[
				'$sort' => ['drop_date' => 1],
			],
		];

		try {
			// Execute the aggregation
			$result = $this->collection->aggregate($pipeline);
		} catch (\MongoDB\Driver\Exception\Exception $exception) {
			exit('Exception: ' . $exception->getMessage());
		}
		// Collect and return the results
		$summary = [];

		foreach ($result as $doc) {
			$summary[] = $doc->getArrayCopy();
		}

		return $summary;
	}