In Magento 2, there is no core functionality for programmatically fetching canceled orders. In this blog post, we will explore two methods to retrieve canceled order details such as name, email, shipping method, order comment, and other information from the database tables sales_order and sales_order_status_history. Additionally, we have implemented a frontend form allowing users to specify a date range (from and to) for fetching order details. The method outlined in this post will then fetch and display the relevant canceled orders based on the provided date range and after clicking submit, it will download a CSV file.
Method: 1
While the following example provides a direct SQL query for fetching canceled order details in Magento 2, it’s essential to note that this method is not considered best practice and may not adhere to Magento coding standards. It is recommended to explore alternative, more Magento-friendly approaches for optimal code quality and maintainability.
Path: pub/script/index.php
<?php
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['from_date'], $_POST['to_date'])) {
// Connect to the database
$dbHost = 'Your_hostname';
$dbUser = 'Your_username';
$dbPass = 'Your_Password';
$dbName = 'Your_Database_Name';
$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}
$fromDate = date('Y-m-d', strtotime($_POST['from_date'])) . ' 00:00:00';
$toDate = date('Y-m-d', strtotime($_POST['to_date'])) . ' 23:59:59';
// Prepare the SQL query to fetch canceled orders
$sql = "SELECT so.*, GROUP_CONCAT(sosh.comment SEPARATOR ' | ') AS order_comments
FROM `sales_order` AS so
LEFT JOIN `sales_order_status_history` AS sosh ON so.entity_id = sosh.parent_id
WHERE so.`status` = 'canceled' AND so.`created_at` >= ? AND so.`updated_at` <= ?
GROUP BY so.entity_id";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $fromDate, $toDate);
$stmt->execute();
$result = $stmt->get_result();
$orders = [];
while ($row = $result->fetch_assoc()) {
$orders[] = $row;
}
$stmt->close();
$conn->close();
// Check if any orders were found
if (empty($orders)) {
echo "No canceled orders found for the specified date range.";
} else {
// Proceed with CSV export
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="canceled_orders.csv"');
$output = fopen('php://output', 'w');
fputcsv($output, array_keys($orders[0])); // Column headers
foreach ($orders as $order) {
fputcsv($output, $order); // Output each order as a CSV row
}
fclose($output);
exit;
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Export Canceled Orders</title>
</head>
<body>
<form method="POST" action="">
<label for="from_date">From Date:</label>
<input type="date" id="from_date" name="from_date" required>
<label for="to_date">To Date:</label>
<input type="date" id="to_date" name="to_date" required>
<input type="submit" value="Export">
</form>
</body>
</html>That’s it, Don’t forget to change hostname, database name, username and password.
Method: 2
If you don’t know how to create basic module in Magento 2 – Read this article
Path: app/code/Lokesh/CancelorderExport/etc/frontend/routes.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:App/etc/routes.xsd">
<router id="standard">
<route id="exportorders" frontName="exportorders">
<module name="Lokesh_CancelorderExport"/>
</route>
</router>
</config>Path: app/code/Lokesh/CancelorderExport/Controller/Index/Index.php
<?php
namespace Lokesh\CancelorderExport\Controller\Index;
use Magento\Framework\App\Action\Action;
use Magento\Framework\App\Action\Context;
use Magento\Framework\App\ResponseInterface;
use Lokesh\CancelorderExport\Model\Exporter;
class Index extends Action
{
protected $exporter;
public function __construct(
Context $context,
Exporter $exporter
) {
parent::__construct($context);
$this->exporter = $exporter;
}
public function execute()
{
$fromDate = $this->getRequest()->getParam('from_date');
$toDate = $this->getRequest()->getParam('to_date');
// Check if both from_date and to_date parameters are provided
if ($fromDate && $toDate) {
// If parameters are provided, proceed with export
$this->exporter->exportOrders($fromDate, $toDate);
} else {
// If parameters are not provided, load and display the form
$this->_view->loadLayout();
$this->_view->renderLayout();
}
}
}Path: app/code/Lokesh/CancelorderExport/Model/Exporter.php
<?php
namespace Lokesh\CancelorderExport\Model;
use Magento\Framework\App\ResourceConnection;
class Exporter
{
protected $resourceConnection;
public function __construct(
ResourceConnection $resourceConnection
) {
$this->resourceConnection = $resourceConnection;
}
public function exportOrders($fromDate, $toDate)
{
$connection = $this->resourceConnection->getConnection();
// Prepare the query
$select = $connection->select()
->from(
['so' => $this->resourceConnection->getTableName('sales_order')],
['entity_id', 'increment_id', 'created_at', 'status'] // Add other fields as needed
)
->joinLeft(
['sosh' => $this->resourceConnection->getTableName('sales_order_status_history')],
'so.entity_id = sosh.parent_id',
['comments' => new \Zend_Db_Expr('GROUP_CONCAT(sosh.comment SEPARATOR "|")')] // Concatenate all comments
)
->where('so.status = ?', 'canceled')
->where('so.created_at >= ?', $fromDate)
->where('so.updated_at <= ?', $toDate)
->group('so.entity_id');
$orders = $connection->fetchAll($select);
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="canceled_orders.csv"');
$fp = fopen('php://output', 'w');
// Write the header row to the CSV
fputcsv($fp, ['Order ID', 'Increment ID', 'Created At', 'Status', 'Comments']); // Add other headers as needed
// Write each order's data to the CSV
foreach ($orders as $row) {
fputcsv($fp, [
$row['entity_id'],
$row['increment_id'],
$row['created_at'],
$row['status'],
$row['comments'] // Ensure to handle any necessary sanitization or formatting
// Add more data according to your requirement.
]);
}
fclose($fp);
}
}If you want to gain a deep understanding of how controllers and routes work – Read this article
Path: code/Lokesh/CancelorderExport/view/frontend/layout/exportorders_index_index.xml
<?xml version="1.0"?>
<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
<body>
<referenceContainer name="content">
<block class="Lokesh\CancelorderExport\Block\ExportForm" name="export.form" template="Lokesh_CancelorderExport::export_form.phtml"/>
</referenceContainer>
</body>
</page>Path: app/code/Lokesh/CancelorderExport/Block/ExportForm.php
<?php
namespace Lokesh\CancelorderExport\Block;
use Magento\Framework\View\Element\Template;
class ExportForm extends Template
{
protected function _prepareLayout()
{
parent::_prepareLayout();
$this->setTemplate('Lokesh_CancelorderExport::export_form.phtml');
}
}Path: app/code/Lokesh/CancelorderExport/view/frontend/templates/export_form.phtml
<form action="<?= $block->getUrl('exportorders/index/index') ?>" method="GET">
<div class="field">
<label for="from_date">From Date:</label>
<input type="date" id="from_date" name="from_date" required />
</div>
<div class="field">
<label for="to_date">To Date:</label>
<input type="date" id="to_date" name="to_date" required />
</div>
<div class="actions">
<button type="submit" class="action submit primary">Export Orders</button>
</div>
</form>Enable the module and update the database schema:
Run the following Magento CLI commands:
php bin/magento module:enable Lokesh_CancelorderExport
php bin/magento setup:upgrade
With this setup, when you navigates to the URL that corresponds to your route (for example, http://example.com/exportorders), it will be presented with the form. Upon filling out the form and submitting it, the data is sent via GET to your controller’s execute method, which then calls the exportOrders method with the provided dates.
Thank you for reading this Article! Feel free to share your thoughts or ask any questions in the comments section below and spread the word by sharing. Your engagement is appreciated!
