Doctor::getRows

private function getRows(): array
{
	$result = [];

	$db = Application::getConnection();

	// load crm reserves
	$crmReservesSql = '
		SELECT
			cprr.ROW_ID,
			cpr.OWNER_ID,
			cpr.OWNER_TYPE,
			cpr.PRODUCT_ID,
			cpr.PRODUCT_NAME,
			cprr.STORE_ID AS CRM_RESERVE_STORE_ID,
			cprr.RESERVE_QUANTITY AS CRM_RESERVE_QUANTITY,
			DATE(cprr.DATE_RESERVE_END) AS CRM_RESERVE_DATE_END,
			sbr.BASKET_ID
		FROM
			b_crm_product_row_reservation as cprr
			LEFT JOIN b_crm_product_reservation_map AS cprm ON cprr.ROW_ID = cprm.PRODUCT_ROW_ID
			LEFT JOIN b_sale_basket_reservation AS sbr ON cprm.BASKET_RESERVATION_ID = sbr.ID
			LEFT JOIN b_crm_product_row AS cpr ON cpr.ID = cprr.ROW_ID
	';
	$rows = $db->query($crmReservesSql);
	foreach ($rows as $row)
	{
		$rowId = (int)$row['ROW_ID'];

		$row['CRM_RESERVE_STORE_ID'] = (int)$row['CRM_RESERVE_STORE_ID'];
		$row['CRM_RESERVE_QUANTITY'] = (float)$row['CRM_RESERVE_QUANTITY'];
		$row['CRM_RESERVE_DATE_END'] = (string)$row['CRM_RESERVE_DATE_END'];

		$result[$rowId] = $row;
		$result[$rowId] += [
			'SALE_RESERVE_DATE_END' => '',
			'SALE_RESERVE_STORE_ID' => 0,
			'SALE_RESERVE_QUANTITY' => 0.0,
			'SALE_DEDUCTED_QUANTITY' => 0.0,
		];
	}

	if (empty($result))
	{
		return [];
	}

	// load sale reserves and deductes
	$basketToRowIds = BasketService::getInstance()->getRowIdsToBasketIdsByRows(array_keys($result));
	if (!empty($basketToRowIds))
	{
		$basketIdsSql = join(',', $basketToRowIds);
		$basketToRowIds = array_flip($basketToRowIds);

		$saleReservesSql = "
			SELECT
				BASKET_ID,
				STORE_ID AS SALE_RESERVE_STORE_ID,
				QUANTITY AS SALE_RESERVE_QUANTITY,
				DATE(DATE_RESERVE_END) AS SALE_RESERVE_DATE_END
			FROM
				b_sale_basket_reservation
			WHERE
				BASKET_ID IN ({$basketIdsSql})
		";

		$rows = $db->query($saleReservesSql);
		foreach ($rows as $row)
		{
			$basketId = (int)$row['BASKET_ID'];
			$rowId = $basketToRowIds[$basketId] ?? null;
			if ($rowId !== null)
			{
				$result[$rowId]['SALE_RESERVE_STORE_ID'] = (int)$row['SALE_RESERVE_STORE_ID'];
				$result[$rowId]['SALE_RESERVE_QUANTITY'] = (float)$row['SALE_RESERVE_QUANTITY'];
				$result[$rowId]['SALE_RESERVE_DATE_END'] = (string)$row['SALE_RESERVE_DATE_END'];
			}
		}

		$saleDeductedSql = "
			SELECT
				sodb.BASKET_ID,
				SUM(sodb.QUANTITY) as SALE_DEDUCTED_QUANTITY
			FROM
				b_sale_order_dlv_basket AS sodb
				INNER JOIN b_sale_order_delivery AS sod ON sodb.ORDER_DELIVERY_ID = sod.ID AND sod.DEDUCTED = 'Y'
			WHERE
				sodb.BASKET_ID IN ({$basketIdsSql})
			GROUP BY
				sodb.BASKET_ID
		";
		$rows = $db->query($saleDeductedSql);
		foreach ($rows as $row)
		{
			$basketId = (int)$row['BASKET_ID'];
			$rowId = $basketToRowIds[$basketId] ?? null;
			if ($rowId !== null)
			{
				$result[$rowId]['SALE_DEDUCTED_QUANTITY'] = (float)$row['SALE_DEDUCTED_QUANTITY'];
			}
		}
	}

	// sort columns
	$sortOrder = array_flip([
		'ROW_ID',
		'OWNER_ID',
		'OWNER_TYPE',
		'PRODUCT_ID',
		'PRODUCT_NAME',
		'BASKET_ID',
		'CRM_RESERVE_STORE_ID',
		'SALE_RESERVE_STORE_ID',
		'CRM_RESERVE_DATE_END',
		'SALE_RESERVE_DATE_END',
		'CRM_RESERVE_QUANTITY',
		'SALE_RESERVE_QUANTITY',
		'SALE_DEDUCTED_QUANTITY',
	]);

	foreach ($result as &$row)
	{
		uksort(
			$row,
			static fn($a, $b) => $sortOrder[$a] <=> $sortOrder[$b]
		);
	}
	unset($row);

	return $result;
}