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;
}