0

i'm pretty new at sql queries and using the terminal in general for accessing data in MySQL and Magento. I've been asked to export a daily list of database entries in a Magento database and export this into a .csv file. Basically, I need to select what I need from the orders tables in the database and then write that to a csv file which I can then save and send to my client. I've done some research and I know which tables I need to target, but I'm not sure how to put together my script to write the query and save the file.

Any help would be appreciated.

Thanks in advance.

1 Answer 1

1

I would not recommend to use direct queries, not only particularly for this case, but in general when speaking of a Magento functionality implementation.

Why? The main reason is also pointed into the Magento doc Avoid raw SQL queries:

Raw SQL queries can lead to potential security vulnerabilities and database portability issues.

Now, speaking about your requirement, Magento is offering a useful layer of abstraction for its main entities(orders, customers, products and so on) which you can use.

For a kick-start I wrote for you an extension which is exporting some fields from order, shipping address, billing address into the var/orderExport.csv file, every day at 01:00 A.M., for the orders placed within the last 24 hours.

If you need any other orders search criteria, fields into the csv file or other related order information, you can easily follow my implementation.

app/code/Stack/OrderExport/registration.php

<?php

\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'Stack_OrderExport',
    __DIR__
);

app/code/Stack/OrderExport/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="Stack_OrderExport" setup_version="1.0.0">
    <sequence>
        <module name="Magento_Sales" />
    </sequence>
</module>

app/code/Stack/OrderExport/etc/crontab.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Cron:etc/crontab.xsd">
<group id="default">
    <job name="custom_export_orders" instance="Stack\OrderExport\Cron\ExportOrders" method="exportOrdersToCsv">
        <schedule>0 1 * * *</schedule>
    </job>
</group>

app/code/Stack/OrderExport/Cron/ExportOrders.php

<?php

namespace Stack\OrderExport\Cron;

class ExportOrders
{

/**
 * @var \Magento\Sales\Api\OrderRepositoryInterface
 */
private $orderRepository;

/**
 * @var \Magento\Framework\Api\SearchCriteriaBuilder
 */
private $searchCriteriaBuilder;

/**
 * @var \Magento\Framework\Locale\ResolverInterface
 */
private $localeResolver;

/**
 * @var \Magento\Framework\Stdlib\DateTime\TimezoneInterface
 */
private $localeDate;

/**
 * Filter builder
 *
 * @var \Magento\Framework\Api\FilterBuilder
 */
private $filterBuilder;

/**
 * Directory List
 *
 * @var \Magento\Framework\App\Filesystem\DirectoryList
 */
private $directoryList;

/**
 * File CSV
 *
 * @var \Magento\Framework\File\Csv
 */
private $csvProcessor;

public function __construct(
    \Magento\Sales\Api\OrderRepositoryInterface $orderRepository,
    \Magento\Framework\Api\SearchCriteriaBuilder $searchCriteria,
    \Magento\Framework\Locale\ResolverInterface $localeResolver,
    \Magento\Framework\Stdlib\DateTime\TimezoneInterface $timezone,
    \Magento\Framework\Api\FilterBuilder $filterBuilder,
    \Magento\Framework\App\Filesystem\DirectoryList $directoryList,
    \Magento\Framework\File\Csv $csvProcessor
) {
    $this->orderRepository = $orderRepository;
    $this->searchCriteriaBuilder = $searchCriteria;
    $this->localeResolver = $localeResolver;
    $this->localeDate = $timezone;
    $this->filterBuilder = $filterBuilder;
    $this->directoryList = $directoryList;
    $this->csvProcessor = $csvProcessor;
}


/**
 * Export orders to csv
 *
 * @return void
 */
public function exportOrdersToCsv()
{
    $lastDayOrders = $this->getLastDayOrders();
    $exportFieldsArray = [];

    foreach($lastDayOrders as $order){
        $shippingAddress = $order->getShippingAddress();
        $billingAddress = $order->getBillingAddress();
        $exportFieldsArray[] = [
            'increment_id' => $order->getIncrementId(),
            'shipping_description' => $order->getShippingDescription(),
            'grand_total' => $order->getGrandTotal(),
            'shipping_name' => $shippingAddress->getFirstname(),
            'billing_name' => $billingAddress->getFirstname()
        ];
    }

    $fileDirectoryPath = $this->directoryList->getPath(\Magento\Framework\App\Filesystem\DirectoryList::VAR_DIR);

    $fileName = 'orderExport.csv';
    $filePath =  $fileDirectoryPath . '/' . $fileName;
    $this->csvProcessor
        ->setEnclosure('"')
        ->setDelimiter(',')
        ->saveData($filePath, $exportFieldsArray);
}

/**
 * Get orders from the last day
 */
private function getLastDayOrders()
{
    $this->localeResolver->emulate(0);
    $currentDate = $this->localeDate->date();
    $date = $currentDate->sub(new \DateInterval('PT24H'));

    $filter = $this->filterBuilder
            ->setField('created_at')
            ->setValue($date)
            ->setConditionType('gt')
            ->create();
    $this->searchCriteriaBuilder->addFilters([$filter]);

    $orders = $this->orderRepository->getList(
        $this->searchCriteriaBuilder->create()
    );

    return $orders;
}
}

Good luck!

1
  • It's working fine for me, But how to we add columns name in Csv with this code ? Oct 12, 2021 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.