Required task:
Export Giftcards with the below header:
ID, code, website, created, end, active, status, balance, payment_method, email (creator email), email (sent to), order idSolution 1: Use only SQL
SELECT gc.giftcardaccount_id AS ID,
gc.code,
gc.website_id AS website,
gc.date_created AS `created`,
gc.date_expires AS `end`,
gc.status AS `active`,
CASE
WHEN gc.state = 0 THEN 'AVAILABLE'
WHEN gc.state = 1 THEN 'USED'
WHEN gc.state = 2 THEN 'REDEEMED'
WHEN gc.state = 3 THEN 'EXPIRED'
END AS `status`,
gc.balance,
CASE
WHEN gch.additional_info LIKE 'Order #%'
THEN TRIM(BOTH 'Order #' FROM
SUBSTRING_INDEX(SUBSTRING_INDEX(gch.additional_info, 'Order #', -1), '.', 1))
ELSE null
END AS order_id,
IF(soi.price = 0, 'reward_points', sop.method) AS payment_method,
soi.sender_email,
soi.recipient_email,
gch.additional_info AS info
FROM magento_giftcardaccount AS gc
LEFT JOIN magento_giftcardaccount_history AS gch
ON gc.giftcardaccount_id = gch.giftcardaccount_id AND gch.action = '0'
LEFT JOIN
sales_order AS so
ON TRIM(BOTH 'Order #' FROM
SUBSTRING_INDEX(SUBSTRING_INDEX(gch.additional_info, 'Order #', -1), '.', 1)) = so.increment_id
LEFT JOIN sales_order_payment AS sop
ON so.entity_id = sop.parent_id
LEFT JOIN (
SELECT soi.order_id,
MAX(CASE WHEN qio.code = 'giftcard_sender_email' THEN qio.value END) AS sender_email,
MAX(CASE WHEN qio.code = 'giftcard_recipient_email' THEN qio.value END) AS recipient_email,
soi.price
FROM sales_order_item AS soi
LEFT JOIN quote_item_option AS qio
ON soi.quote_item_id = qio.item_id
WHERE soi.product_type = 'giftcard'
GROUP BY soi.order_id) as soi
ON soi.order_id = so.entity_id
order by ID desc;
Solution 2: Create "Download Giftcards CSV" button on admin page
Go to Admin > Report > Download Giftcards CSV
Create below files in app/code folder:
Lexim/GiftcardReport/registration.php
<?php
\Magento\Framework\Component\ComponentRegistrar::register(
\Magento\Framework\Component\ComponentRegistrar::MODULE,
'Lexim_GiftcardReport',
__DIR__
);
Lexim/GiftcardReport/etc/module.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
<module name="Lexim_GiftcardReport" setup_version="1.0.0">
<sequence>
<module name="Magento_GiftCard" />
</sequence>
</module>
</config>
Lexim/GiftcardReport/etc/adminhtml/menu.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Backend:etc/menu.xsd">
<menu>
<add id="Lexim_GiftcardReport::giftcard_menu"
title="Gift Cards"
translate="title"
module="Lexim_GiftcardReport"
sortOrder="100"
parent="Magento_Reports::report"
resource="Lexim_GiftcardReport::giftcard_menu"/>
<add id="Lexim_GiftcardReport::csv_export"
title="Download Gift Cards CSV" translate="title"
module="Lexim_GiftcardReport"
sortOrder="10"
parent="Lexim_GiftcardReport::giftcard_menu"
resource="Lexim_GiftcardReport::csv_export"
action="giftcardreport/csv/export"/>
</menu>
</config>
Lexim/GiftcardReport/etc/adminhtml/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="admin">
<route id="giftcardreport" frontName="giftcardreport">
<module name="Lexim_GiftcardReport" />
</route>
</router>
</config>
Lexim/GiftcardReport/etc/acl.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:framework:Acl/etc/acl.xsd">
<acl>
<resources>
<resource id="Magento_Backend::admin">
<resource id="Magento_Reports::report">
<resource id="Lexim_GiftcardReport::giftcard_menu"
title="Gift Cards" sortOrder="100">
<resource id="Lexim_GiftcardReport::csv_export"
title="Download Gift Cards CSV" sortOrder="10"/>
</resource>
</resource>
</resource>
</resources>
</acl>
</config>
Lexim/GiftcardReport/Controller/Adminhtml/Index.php
<?php
namespace Lexim\GiftcardReport\Controller\Adminhtml;
use Magento\Backend\App\Action;
use Laminas\Log\Writer\Stream;
use Laminas\Log\Logger;
abstract class Index extends Action
{
const LOGGER_NAME = 'Lexim_GiftcardReport';
/** @var Logger */
protected $logger;
/**
* @param Action\Context $context
*/
public function __construct(
\Magento\Backend\App\Action\Context $context,
) {
parent::__construct($context);
$writer = new Stream(BP . '/var/log/' . self::LOGGER_NAME . '.log');
$this->logger = new Logger();
$this->logger->addWriter($writer);
}
}
Lexim/GiftcardReport/Controller/Adminhtml/Csv/Export.php
<?php
namespace Lexim\GiftcardReport\Controller\Adminhtml\Csv;
use Magento\Framework\App\Action\HttpGetActionInterface;
use Magento\Backend\App\Action\Context;
use Magento\Framework\App\Response\Http\FileFactory;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\Filesystem\Io\File;
class Export extends \Lexim\GiftcardReport\Controller\Adminhtml\Index
implements HttpGetActionInterface
{
/** @var FileFactory */
protected $fileFactory;
/** @var ResourceConnection */
protected $resourceConnection;
/** @var File */
protected $file;
/**
* @param Context $context
* @param FileFactory $fileFactory
* @param ResourceConnection $resourceConnection
* @param File $file
*/
public function __construct(
Context $context,
FileFactory $fileFactory,
ResourceConnection $resourceConnection,
File $file
) {
parent::__construct($context);
$this->fileFactory = $fileFactory;
$this->resourceConnection = $resourceConnection;
$this->file = $file;
}
/**
* @throws \Exception
*/
public function execute()
{
try {
// Format the date and time
$currentDateTime = date('YFd-H\hi');
$fileName = 'GiftCards.' . $currentDateTime . '.csv';
$directory = BP . '/var/export/';
$filePath = $directory . $fileName;
// Ensure the directory exists
if (!$this->file->fileExists($directory)) {
$this->file->mkdir($directory, 0775);
}
$connection = $this->resourceConnection->getConnection();
// Optimized SQL query
$select = $connection->select()
->from(['gc' => 'magento_giftcardaccount'], [
'ID' => 'gc.giftcardaccount_id',
'gc.code',
'website' => 'gc.website_id',
'created' => 'gc.date_created',
'end' => 'gc.date_expires',
'active' => 'gc.status',
'status' => new \Zend_Db_Expr("
CASE
WHEN gc.state = 0 THEN 'AVAILABLE'
WHEN gc.state = 1 THEN 'USED'
WHEN gc.state = 2 THEN 'REDEEMED'
WHEN gc.state = 3 THEN 'EXPIRED'
END
"),
'gc.balance',
'order_id' => new \Zend_Db_Expr("
IF(gch.additional_info LIKE 'Order #%',
TRIM(BOTH 'Order #' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(gch.additional_info, 'Order #', -1), '.', 1)), NULL)
"),
'payment_method' => new \Zend_Db_Expr("
IF(soi.price = 0, 'reward_points', sop.method)
"),
'soi.sender_email',
'soi.recipient_email',
'gch.additional_info AS info'
])
->joinLeft(
['gch' => 'magento_giftcardaccount_history'],
'gc.giftcardaccount_id = gch.giftcardaccount_id
AND gch.action = \'0\'',
[]
)
->joinLeft(
['so' => 'sales_order'],
'TRIM(BOTH \'Order #\' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(gch.additional_info, \'Order #\', -1), \'.\', 1)) = so.increment_id',
[]
)
->joinLeft(
['sop' => 'sales_order_payment'],
'so.entity_id = sop.parent_id',
[]
)
->joinLeft(['soi' => new \Zend_Db_Expr("
(SELECT soi.order_id,
MAX(CASE WHEN qio.code = 'giftcard_sender_email'
THEN qio.value END) AS sender_email,
MAX(CASE WHEN qio.code = 'giftcard_recipient_email'
THEN qio.value END) AS recipient_email,
soi.price
FROM sales_order_item AS soi
LEFT JOIN quote_item_option AS qio
ON soi.quote_item_id = qio.item_id
WHERE soi.product_type = 'giftcard'
GROUP BY soi.order_id)
")], 'soi.order_id = so.entity_id', [])
->order('ID DESC');
$data = $connection->fetchAll($select);
$file = fopen($filePath, 'w+');
fputcsv($file, array_keys($data[0])); // add headers
foreach ($data as $row) {
fputcsv($file, $row);
}
fclose($file);
$response = $this->fileFactory->create(
$fileName,
[
'type' => 'filename',
'value' => $filePath,
'rm' => true
],
\Magento\Framework\App\Filesystem\DirectoryList::VAR_DIR,
'application/octet-stream'
);
return $response;
} catch (\Exception $e) {
$this->logger->crit('Error downloading and exporting gift card data: '
. $e->getMessage());
throw new \Exception($e);
}
}
}