Magento Code Snippets

  public function statAction(){

        $fileName   ='report.csv';

        $io = new Varien_Io_File();

        $path = Mage::getBaseDir('var') . DS . 'export'. DS;

        $name = md5(microtime());

        $file = $path . DS . $name . '.csv';

        $io->setAllowCreateFolders(true);

        $io->open(array('path'=> $path));

        $io->streamOpen($file,'w+');

        $io->streamLock(true);

         

        $resource =Mage::getSingleton('core/resource');

        $readConnection =$resource->getConnection('core_read');       

        $table =$resource->getTableName('sales/order');

         

        $totalTitle0 = array('Total CustomerReport');

        $io->streamWriteCsv($totalTitle0); 

        $totalHeaders0 = array('Time','Total ofInvoices','Total Invoiced','Total Shipping Cost');

        $io->streamWriteCsv($totalHeaders0);

        $sql0 = "select t.created_at astime,count(t.entity_id) as num,sum(t.total_invoiced) asinvoice,sum(t.shipping_invoiced) as ship from (SELECTentity_id,total_invoiced,shipping_invoiced,DATE_FORMAT(`created_at`, '%Y-%m') as created_at FROM {$table} ) t   groupby time having invoice >0 order by time desc";

        $results0 =$readConnection->fetchAll($sql0);        

        foreach ($results0 as $result) {

            $io->streamWriteCsv($result);            

        }      

         

        $io->streamWriteCsv(array(' ',''));

        $totalTitle1 = array('Repeated CustomerReport');

        $io->streamWriteCsv($totalTitle1); 

        $totalHeaders1 = array('Time','Total ofInvoices','Total Invoiced','Total Shipping Cost');

        $io->streamWriteCsv($totalHeaders1);

        $sql1 = "SELECT t.created_at AS TIME, COUNT(t.entity_id ) AS num, SUM( t.total_invoiced ) AS invoice, SUM(t.shipping_invoiced ) AS ship

FROM (SELECT entity_id, total_invoiced, shipping_invoiced,DATE_FORMAT( created_at,   '%Y-%m' ) AScreated_at

FROM sales_order WHERE customer_id IN (SELECT customer_id FROMsales_order WHERE customer_id >0 GROUP BYcustomer_id HAVING COUNT( customer_id ) >1))t

GROUP BY TIME HAVING invoice >0 ORDER BY TIMEDESC";

        $results1 =$readConnection->fetchAll($sql1);        

        foreach ($results1 as $result) { 

            $io->streamWriteCsv($result);            

        } 

         

        $io->streamWriteCsv(array(' ','')); 

        $totalTitle2 = array('Registered Customersbuying for the first time');

        $io->streamWriteCsv($totalTitle2); 

        $totalHeaders2 = array('Time','Total ofInvoices','Total Invoiced','Total Shipping Cost');

        $io->streamWriteCsv($totalHeaders2);

        $sql2 =   "SELECT t.created_atAS TIME, COUNT( t.entity_id ) AS num, SUM( t.total_invoiced ) ASinvoice, SUM( t.shipping_invoiced ) AS ship FROM (

                    SELECT customer_id,  `entity_id` ,   `total_invoiced`,   `shipping_invoiced` , DATE_FORMAT( `created_at`,   '%Y-%m' ) AS created_at

                    FROM sales_order WHERE customer_id

                    IN ( SELECT customer_id FROM sales_order WHEREcustomer_id >0 GROUP BY customer_id HAVING COUNT(customer_id ) =1) )t

                    GROUP BY TIME HAVING invoice >0ORDER BY TIME DESC";

        $results2 =$readConnection->fetchAll($sql2);        

        foreach ($results2 as $result) {             

            $io->streamWriteCsv($result);            

        } 

         

        $io->streamWriteCsv(array(' ',''));

        $totalTitle3 = array('Anonymous CustomerReport');

        $io->streamWriteCsv($totalTitle3); 

        $totalHeaders3 = array('Time','Total ofInvoices','Total Invoiced','Total Shipping Cost');

        $io->streamWriteCsv($totalHeaders3);

        $sql3 = "select t.created_at astime,count(t.entity_id) as num,sum(t.total_invoiced) asinvoice,sum(t.shipping_invoiced) as ship from (SELECT`entity_id`,`total_invoiced`,`shipping_invoiced`,DATE_FORMAT(`created_at`, '%Y-%m') as created_at FROM {$table} WHERE customer_id is null )t   group by t.created_at having invoice> 0 order by time desc";

        $results3 =$readConnection->fetchAll($sql3);        

        foreach ($results3 as $result) {

            $io->streamWriteCsv($result);            

        } 

         

        $io->streamWriteCsv(array(' ',''));

        $totalTitle4 = array('Quantity InvoicedReport');

        $io->streamWriteCsv($totalTitle4); 

        $totalHeaders4 = array('Time','ProductName','Product SKU','Quantity Invoiced');

        $io->streamWriteCsv($totalHeaders4);

        $sql4 = "SELECT DATE_FORMAT(st.created_at ,  '%Y-%m' ) AS time,st.name,st.sku, SUM(st.total_qty_ordered ) AS total  FROM 

                (selectso.created_at,so.total_qty_ordered,so.total_invoiced,si.name,si.skufrom sales_order so inner join sales_flat_order_item si onso.entity_id = si.order_id where so.total_invoiced >0) st

                GROUP BYDATE_FORMAT(st.created_at ,'%Y-%m' ),st.name,st.sku having total> 0 order by time desc";

        $results4 =$readConnection->fetchAll($sql4);        

        foreach ($results4 as $result) {

            $io->streamWriteCsv($result);            

        } 

         

        $io->streamWriteCsv(array(' ',''));

        $totalTitle5 = array('Quantity CreditedReport');

        $io->streamWriteCsv($totalTitle5); 

        $totalHeaders5 = array('Time','ProductName','Product SKU','Quantity Credited');

        $io->streamWriteCsv($totalHeaders5);

        $sql5 = "SELECT DATE_FORMAT(st.created_at ,  '%Y-%m' ) AS time,st.name,st.sku, SUM(st.total_qty_ordered ) AS total  FROM 

                  (selectso.created_at,so.total_qty_ordered,so.total_paid,si.name,si.skufrom sales_order so inner join sales_flat_order_item si onso.entity_id = si.order_id) st

                  wheretotal_paid > 0 GROUP BY DATE_FORMAT(st.created_at,'%Y-%m' ),st.name,st.sku having total > 0 order bytime desc "; 

        $results5 =$readConnection->fetchAll($sql5);        

        foreach ($results5 as $result) {

            $io->streamWriteCsv($result);            

        } 

               

         

        $io->streamUnlock();

        $io->streamClose();

        $csvFile = array(

            'type'  => 'filename',

            'value'=> $file,

            'rm'    =>true 

        );

        $this->wonprepareDownloadRespon se($fileName,$csvFile);        

    }

     

    private functionwonprepareDownloadRespon se(

        $fileName,

        $content,

        $contentType = 'application/octet-stream',

        $contentLength = null)

    {

        $session =Mage::getSingleton('admin/session');

        if($session->isFirstPageAfterLogin()) {

            $this->_redirect($session->getUser()->getStartupPageUrl());

            return$this;

        }

        $isFile = false;

        $file   = null;

        if (is_array($content)) {

            if(!isset($content['type']) || !isset($content['value'])) {

                return $this;

            }

            if($content['type'] == 'filename') {

                $isFile         =true;

                $file           = $content['value'];

                $contentLength  = filesize($file);

            }

        }

        $this->getResponse()

            ->setHttpResponseCode(200)

            ->setHeader('Pragma', 'public', true)

            ->setHeader('Cache-Control', 'must-revalidate,post-check=0, pre-check=0', true)

            ->setHeader('Content-type', $contentType,true)

            ->setHeader('Content-Length',is_null($contentLength) ? strlen($content) : $contentLength,true)

            ->setHeader('Content-Disposition', 'attachment;filename="'.$fileName.'"', true)

            ->setHeader('Last-Modified', date('r'), true);

        if (!is_null($content)) {

            if($isFile) {

                $this->getResponse()->clearBody();

                $this->getResponse()->sendHeaders();

                $ioAdapter = newVarien_Io_File();

                $ioAdapter->open(array('path' =>$ioAdapter->dirname($file)));

                $ioAdapter->streamOpen($file, 'r');

                while ($buffer =$ioAdapter->streamRead()) {

                    print $buffer;

                }

                $ioAdapter->streamClose();

                if (!empty($content['rm'])){

                    $ioAdapter->rm($file);

                }

                exit(0);

            } else{

                $this->getResponse()->setBody($content);

            }

        }

        return $this;

    }