Magento Code Snippets

最新推荐文章于 2020-08-14 07:30:44 发布

最新推荐文章于 2020-08-14 07:30:44 发布 · 61 阅读

本文介绍了一个用于导出订单报表的脚本实现，该脚本能够从数据库中获取不同类型的订单数据，并将其整理成CSV文件进行下载。具体包括总客户报告、重复客户报告等五个部分的数据。

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 totalFROM

(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 totalFROM

(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

);