0

We are using a custom module to generate category/subcategory wise sales report. For this we are using following query.

$sql = "SELECT sfoi.created_at, sfoi.name, sfoi.sku, sfoi.price, sfoi.qty_ordered FROM sales_flat_order_item as sfoi
                                    left join sales_flat_order as sfo on sfo.entity_id=sfoi.order_id
                                        $category
                                        WHERE sfoi.created_at between '$fromDate' and '$toDate' $store_id $categoryWhere
                                        order by sfoi.created_at";
            $connection = Mage::getSingleton('core/resource')->getConnection('core_read');
            $reportArr  =   $connection->fetchAll($sql);

Using date range selector, users select dates and sales report generated between two selected dates. We have provided option to export to csv. When a large amount of order comes, system got stuck. I understand it's because query selecting lot of orders between two dates(say orders in last 5 months). What would be best way to export to csv for such a case in magento ?

nha
  • 17,623
  • 13
  • 87
  • 133
Rajeevan
  • 13
  • 2
  • Is your bottleneck getting the data, or processing it afterward for download? – Douglas Radburn Jan 07 '16 at 10:03
  • your code looks like it is vulnerable to SQL-Injections. see http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – doerig Jan 07 '16 at 10:16
  • Hi Doerig, Thank you for mentioning it. I just give a raw query to make it simple,we will be adding sql injection prevention and will make it standard. – Rajeevan Jan 07 '16 at 11:37
  • Hi Douglas, My issue is exporting to csv. Using pagination I could show in grid view. using below method, I am able to get large data in csv. But am checking if there is any better way in magento? while ($row = mysql_fetch_array($result, MYSQL_NUM)) { fputcsv($fp, array_values($row)); } – Rajeevan Jan 07 '16 at 11:39

0 Answers0