Export Products as columns

Note: These codes should be added via Code Snippets plugin (otherwise put it to the functions.php of your current theme). Users of the PRO version should set mode “Run snippet everywhere” in the snippet if they use the scheduled jobs!

// Export Products as columns
// Format - XLS
// Checked - Output column titles as first line
// Button - Export w/o Progressbar
use WOE\PhpOffice\PhpSpreadsheet\Cell\Coordinate;
class Woe_Product_Columns_XLS {
	var $headers_added;
	var $output_field,$order_id;
	var $product_columns;
	var $include_all_products,$allowed_products;
    function __construct() {
        //add settings, , skip products
        add_action("woe_settings_above_buttons", array($this,"draw_options") );
        add_filter("woe_settings_validate_defaults",array($this,"skip_products"),10,1);
    }

    // 1
    function draw_options($settings){
        $selected = !empty($settings[ 'products_as_columns' ]) ? 'checked': '';
        echo '<br><br>
        <input type=hidden name="settings[products_as_columns]" value="0">
        <input type=checkbox name="settings[products_as_columns]" value="1" '. $selected .'>
        <span class="wc-oe-header">Export products as columns,  print <select name="settings[products_as_columns_output_field]" style="width: 100px">
            <option value="qty">Qty</option>
            <option value="line_total">Amount</option>
        </select>
         in cell</span><br>
         Format <b>XLS</b>, button <b>Export w/o progressbar</b>
        <br><br>';
    }

    function skip_products($current_job_settings) {
        global $wpdb;
        if( !empty($current_job_settings['products_as_columns']) )  {
             $current_job_settings["order_fields"]["products"]["checked"] = 0;//  just  skip standard products
             $this->output_field = $current_job_settings['products_as_columns_output_field'];
             // read orders
             add_action("woe_order_export_started",array($this,"start_new_order"),10,1);

             //stop default output for rows
             add_action("woe_xls_header_filter",array($this,"prepare_xls_vars"),10,2);
             add_action("woe_xls_output_filter",array($this,"record_xls_rows"),10,2);
             add_action("woe_xls_print_footer",array($this,"analyze_products_add_columns"),10,2);

             //to support checkbox "Export all products from the order"
             if( ! $current_job_settings['all_products_from_order'] ) {
                 $this->include_all_products   = false;
                 $this->allowed_products = $wpdb->get_col( WC_Order_Export_Data_Extractor::sql_get_product_ids( $current_job_settings ) );
             }
             else
                 $this->include_all_products = true;
        }
        return $current_job_settings;
    }

    // 2
    function prepare_xls_vars($data) {
    $this->headers_added = count($data);
        $this->product_columns = array();
        return $data;
    }

    //3
    function start_new_order($order_id) {
        $this->order_id = $order_id;
        return $order_id;
    }
    function record_xls_rows($data,$obj) {
        $order = new WC_Order($this->order_id);
        $extra_cells = array_fill(0, count($this->product_columns), 0);
        // work with products
        foreach($order->get_items('line_item') as $item_id=>$item) {
            if( !$this->include_all_products  AND !in_array($item['product_id'],$this->allowed_products) )
                continue;
            $product_name = $item['name'];
            $pos = array_search($product_name,$this->product_columns);
            if( $pos === false) { // new product detected
                $extra_cells[] = $item[ $this->output_field  ];
                $this->product_columns[] = $product_name;
            } else {
                $extra_cells[$pos] += $item[ $this->output_field  ];
            }
        }
        foreach($extra_cells as $pc)
            $data[] = $pc;
        return $data;
    }

    //4
    function analyze_products_add_columns($phpExcel,$formatter) {
        // add products as titles
        foreach($this->product_columns as $pos=>$text){
            $formatter->objPHPExcel->getActiveSheet()->getCell([$pos+$this->headers_added+1, 1])->setValue( $text );
            //add formulas for total
            $L = Coordinate::stringFromColumnIndex($pos + $this->headers_added+1);
            $row = $formatter->last_row;
            $formatter->objPHPExcel->getActiveSheet()->getCell([$pos+$this->headers_added+1,$row+1])->setValue("=SUM({$L}2:{$L}$row)");
        }
        $formatter->objPHPExcel->getActiveSheet()->getCell([1,$row+1])->setValue( "Total");//NEW
        //make first bold
        $last_column = $formatter->objPHPExcel->getActiveSheet()->getHighestDataColumn();
        $formatter->objPHPExcel->getActiveSheet()->getStyle( "A1:" . $last_column . "1" )->getFont()->setBold( true );
    }
}
new Woe_Product_Columns_XLS();

100

101

102

103

104

105

// Export Products as columns

// Format - XLS

// Checked - Output column titles as first line

// Button - Export w/o Progressbar

use WOE\PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class Woe_Product_Columns_XLS {

var $headers_added;

var $output_field,$order_id;

var $product_columns;

var $include_all_products,$allowed_products;

function __construct() {

//add settings, , skip products

add_action("woe_settings_above_buttons", array($this,"draw_options") );

add_filter("woe_settings_validate_defaults",array($this,"skip_products"),10,1);

}

// 1

function draw_options($settings){

$selected = !empty($settings[ 'products_as_columns' ]) ? 'checked': '';

echo '<br><br>

<span class="wc-oe-header">Export products as columns, print <select name="settings[products_as_columns_output_field]" style="width: 100px">

<option value="line_total">Amount</option>

</select>

in cell</span><br>

Format <b>XLS</b>, button <b>Export w/o progressbar</b>

<br><br>';

}

function skip_products($current_job_settings) {

global $wpdb;

if( !empty($current_job_settings['products_as_columns']) ) {

$current_job_settings["order_fields"]["products"]["checked"] = 0;// just skip standard products

$this->output_field = $current_job_settings['products_as_columns_output_field'];

// read orders

add_action("woe_order_export_started",array($this,"start_new_order"),10,1);

//stop default output for rows

add_action("woe_xls_header_filter",array($this,"prepare_xls_vars"),10,2);

add_action("woe_xls_output_filter",array($this,"record_xls_rows"),10,2);

add_action("woe_xls_print_footer",array($this,"analyze_products_add_columns"),10,2);

//to support checkbox "Export all products from the order"

if( ! $current_job_settings['all_products_from_order'] ) {

$this->include_all_products = false;

$this->allowed_products = $wpdb->get_col( WC_Order_Export_Data_Extractor::sql_get_product_ids( $current_job_settings ) );

}

else

$this->include_all_products = true;

}

return $current_job_settings;

}

// 2

function prepare_xls_vars($data) {

$this->headers_added = count($data);

$this->product_columns = array();

return $data;

}

//3

function start_new_order($order_id) {

$this->order_id = $order_id;

return $order_id;

}

function record_xls_rows($data,$obj) {

$order = new WC_Order($this->order_id);

$extra_cells = array_fill(0, count($this->product_columns), 0);

// work with products

foreach($order->get_items('line_item') as $item_id=>$item) {

if( !$this->include_all_products AND !in_array($item['product_id'],$this->allowed_products) )

continue;

$product_name = $item['name'];

$pos = array_search($product_name,$this->product_columns);

if( $pos === false) { // new product detected

$extra_cells[] = $item[ $this->output_field ];

$this->product_columns[] = $product_name;

} else {

$extra_cells[$pos] += $item[ $this->output_field ];

}

foreach($extra_cells as $pc)

$data[] = $pc;

return $data;

}

//4

function analyze_products_add_columns($phpExcel,$formatter) {

// add products as titles

foreach($this->product_columns as $pos=>$text){

$formatter->objPHPExcel->getActiveSheet()->getCell([$pos+$this->headers_added+1, 1])->setValue( $text );

//add formulas for total

$L = Coordinate::stringFromColumnIndex($pos + $this->headers_added+1);

$row = $formatter->last_row;

$formatter->objPHPExcel->getActiveSheet()->getCell([$pos+$this->headers_added+1,$row+1])->setValue("=SUM({$L}2:{$L}$row)");

}

$formatter->objPHPExcel->getActiveSheet()->getCell([1,$row+1])->setValue( "Total");//NEW

//make first bold

$last_column = $formatter->objPHPExcel->getActiveSheet()->getHighestDataColumn();

$formatter->objPHPExcel->getActiveSheet()->getStyle( "A1:" . $last_column . "1" )->getFont()->setBold( true );

}

new Woe_Product_Columns_XLS();

// Export Products as columns
// Format - CSV
// Checked - Output column titles as first line
class Woe_Product_Columns_CSV {
    function __construct() {
        @session_start();
        //add settings, , skip products
        add_action("woe_settings_above_buttons", array($this,"draw_options") );
        add_filter("woe_settings_validate_defaults",array($this,"modify_export"),10,1);
    }

    // 1
    function draw_options($settings){
        $selected = !empty($settings[ 'products_as_columns' ]) ? 'checked': '';
        echo '<br><br>
        <input type=hidden name="settings[products_as_columns]" value="0">
        <input type=checkbox name="settings[products_as_columns]" value="1" '. $selected .'>
        <span class="wc-oe-header">Export products as columns,  print <select name="settings[products_as_columns_output_field]" style="width: 100px">
			<option value="qty">Qty</option>
			<option value="line_total">Amount</option>
        </select>
         in cell</span><br>';
    }

    function modify_export($current_job_settings) {
        if( empty($current_job_settings['products_as_columns']) )
            return $current_job_settings;

        // remove products/coupons fields which might require 2+ rows per order
        foreach($current_job_settings["order_fields"] as $k=>$f){
            if ( $f["segment"] == "products" OR $f["segment"] == "coupons" )
                unset($current_job_settings["order_fields"][$k]);
        }

        //remember field to output in new cells
        $this->output_field = $current_job_settings['products_as_columns_output_field'];

        //save all rows to array
        add_filter( "woe_csv_custom_output_func", function($custom_output,$handle,$data,$delimiter,$linebreak,$enclosure,$is_header) {
            if($is_header) {
                $_SESSION['woe_rows']= array();
                $_SESSION['woe_product_columns']= array();
                $_SESSION['woe_rows'][] = $data; //
                return true;
            }

            $order = new WC_Order(WC_Order_Export_Engine::$order_id);
            $extra_cells = [];
            foreach($_SESSION['woe_product_columns'] as $name)
                $extra_cells[$name] = "";
            // work with products
            foreach($order->get_items('line_item') as $item_id=>$item) {
                $product   = $order->get_product_from_item( $item );
                $column_name = $product->get_sku(); // we use SKU
                //$column_name = $item['name'];    // uncomment to use "item name" as column
                if($column_name === "") $column_name = "-empty-";
                if( !isset($extra_cells[$column_name]) ) { // new product detected
                    $extra_cells[$column_name] = $item[ $this->output_field  ];
                    $_SESSION['woe_rows'][0][] = $column_name;//modify header!
                    $_SESSION['woe_product_columns'][] = $column_name;
                } else {
                    $extra_cells[$column_name] = $item[ $this->output_field  ];
                }
            }
            foreach($extra_cells as $column_name=>$pc)
                $data[$column_name] = $pc;
            $_SESSION['woe_rows'][] = $data;

            return true;
        },10,7);

        //output session data
        add_action("woe_csv_print_footer", function($handle, $formatter) {
            //make summary row
            if(count($_SESSION['woe_rows']) > 1) {
                $summary_row = $_SESSION['woe_rows'][1];//take 1st with real data
                foreach($summary_row as $k=>$v)
                    $summary_row[$k] = in_array($k,$_SESSION['woe_product_columns']) ? 0 : "";
                foreach($_SESSION['woe_rows'] as $row)
                    foreach($_SESSION['woe_product_columns'] as $name)
                        $summary_row[$name] += (float)$row[$name];
               // $_SESSION['woe_rows'][] = $summary_row; //uncomment if you need summary row
            }
            //done
            foreach($_SESSION['woe_rows'] as $row)
                fputcsv($handle,$row);
            unset($_SESSION['woe_rows']);//done
        },10,2);

        return $current_job_settings;
    }
}
new Woe_Product_Columns_CSV();

// Export Products as columns

// Format - CSV

// Checked - Output column titles as first line

class Woe_Product_Columns_CSV {

function __construct() {

@session_start();

//add settings, , skip products

add_action("woe_settings_above_buttons", array($this,"draw_options") );

add_filter("woe_settings_validate_defaults",array($this,"modify_export"),10,1);

}

// 1

function draw_options($settings){

$selected = !empty($settings[ 'products_as_columns' ]) ? 'checked': '';

echo '<br><br>

<span class="wc-oe-header">Export products as columns, print <select name="settings[products_as_columns_output_field]" style="width: 100px">

<option value="line_total">Amount</option>

</select>

in cell</span><br>';

}

function modify_export($current_job_settings) {

if( empty($current_job_settings['products_as_columns']) )

return $current_job_settings;

// remove products/coupons fields which might require 2+ rows per order

foreach($current_job_settings["order_fields"] as $k=>$f){

if ( $f["segment"] == "products" OR $f["segment"] == "coupons" )

unset($current_job_settings["order_fields"][$k]);

}

//remember field to output in new cells

$this->output_field = $current_job_settings['products_as_columns_output_field'];

//save all rows to array

add_filter( "woe_csv_custom_output_func", function($custom_output,$handle,$data,$delimiter,$linebreak,$enclosure,$is_header) {

if($is_header) {

$_SESSION['woe_rows']= array();

$_SESSION['woe_product_columns']= array();

$_SESSION['woe_rows'][] = $data; //

return true;

}

$order = new WC_Order(WC_Order_Export_Engine::$order_id);

$extra_cells = [];

foreach($_SESSION['woe_product_columns'] as $name)

$extra_cells[$name] = "";

// work with products

foreach($order->get_items('line_item') as $item_id=>$item) {

$product = $order->get_product_from_item( $item );

$column_name = $product->get_sku(); // we use SKU

//$column_name = $item['name']; // uncomment to use "item name" as column

if($column_name === "") $column_name = "-empty-";

if( !isset($extra_cells[$column_name]) ) { // new product detected

$extra_cells[$column_name] = $item[ $this->output_field ];

$_SESSION['woe_rows'][0][] = $column_name;//modify header!

$_SESSION['woe_product_columns'][] = $column_name;

} else {

$extra_cells[$column_name] = $item[ $this->output_field ];

}

foreach($extra_cells as $column_name=>$pc)

$data[$column_name] = $pc;

$_SESSION['woe_rows'][] = $data;

return true;

},10,7);

//output session data

add_action("woe_csv_print_footer", function($handle, $formatter) {

//make summary row

if(count($_SESSION['woe_rows']) > 1) {

$summary_row = $_SESSION['woe_rows'][1];//take 1st with real data

foreach($summary_row as $k=>$v)

$summary_row[$k] = in_array($k,$_SESSION['woe_product_columns']) ? 0 : "";

foreach($_SESSION['woe_rows'] as $row)

foreach($_SESSION['woe_product_columns'] as $name)

$summary_row[$name] += (float)$row[$name];

// $_SESSION['woe_rows'][] = $summary_row; //uncomment if you need summary row

}

//done

foreach($_SESSION['woe_rows'] as $row)

fputcsv($handle,$row);

unset($_SESSION['woe_rows']);//done

},10,2);

return $current_job_settings;

}

new Woe_Product_Columns_CSV();

// Pro version, send separate CSV to each seller
class Woe_Seller_CSV_mod {
	function __construct() {
		add_action("woe_test_destination", array($this,"add_hooks") );
		add_action("woe_start_cron_job_1", array($this,"add_hooks") ); // adjust job ID here 
	}
	
	function add_hooks () {
		add_action("woe_formatter_csv_start", array($this,"csv_start"),10,1);
		add_action("woe_order_export_started",array($this,"start_new_order"),10,1);
		add_action("woe_get_order_product",array($this,"detect_seller"),10,1);
		add_action("woe_formatter_set_handler_for_csv_row",array($this,"switch_file"),10,1);
		add_action("woe_formatter_csv_finished",array($this,"close_csv_files"),10,1);
		add_action("woe_custom_export_to_email",array($this,"send_csv_files"),10,4);
	}

	function csv_start($header) {
		$this->header = $header;
		$this->filenames = $this->files = array();
	}

	function start_new_order($order_id) {
		//remember sellers is for rows here 
		$this->product_sellers = array();
		$this->product_sellers_pos = 0;
                return $order_id;
	}

	function detect_seller($product) {
		$seller_id = $product->post->post_author;
		
		// remember seller  sequence for rows
		$this->product_sellers[] = $seller_id; 
		
		//put header to new file
		if( !isset($this->files[$seller_id]) ) {
			$this->filenames[$seller_id] = tempnam("/tmp",$seller_id);
			$this->files[$seller_id] = fopen( $this->filenames[$seller_id], "wb+");
			fputcsv($this->files[$seller_id], $this->header);
		}
	}

	function switch_file($handle) {
		$seller_id = $this->product_sellers[$this->product_sellers_pos++];
		return $this->files[$seller_id];
	}
	
	function close_csv_files() {
		foreach($this->files as $f)
			if(get_resource_type($f) == 'stream')
				fclose($f);
	}
	
	function send_csv_files($processed, $filename, $filepath, $exporter) {
		global $ts_mail_errors;
		
		foreach($this->filenames as $seller_id => $filepath) { // override filepath
			$user_info = get_userdata($seller_id);
			if( !empty($user_info->user_email) ) {
				$exporter->destination['email_recipients'] = $user_info->user_email;//override email
				$ts_mail_errors = array();
				echo $exporter->run_export( $filename, $filepath )."n";
			}	
		}
		return true;
	}
}
new Woe_Seller_CSV_mod();

// Pro version, send separate CSV to each seller

class Woe_Seller_CSV_mod {

function __construct() {

add_action("woe_test_destination", array($this,"add_hooks") );

add_action("woe_start_cron_job_1", array($this,"add_hooks") ); // adjust job ID here

}

function add_hooks () {

add_action("woe_formatter_csv_start", array($this,"csv_start"),10,1);

add_action("woe_order_export_started",array($this,"start_new_order"),10,1);

add_action("woe_get_order_product",array($this,"detect_seller"),10,1);

add_action("woe_formatter_set_handler_for_csv_row",array($this,"switch_file"),10,1);

add_action("woe_formatter_csv_finished",array($this,"close_csv_files"),10,1);

add_action("woe_custom_export_to_email",array($this,"send_csv_files"),10,4);

}

function csv_start($header) {

$this->header = $header;

$this->filenames = $this->files = array();

}

function start_new_order($order_id) {

//remember sellers is for rows here

$this->product_sellers = array();

$this->product_sellers_pos = 0;

return $order_id;

}

function detect_seller($product) {

$seller_id = $product->post->post_author;

// remember seller sequence for rows

$this->product_sellers[] = $seller_id;

//put header to new file

if( !isset($this->files[$seller_id]) ) {

$this->filenames[$seller_id] = tempnam("/tmp",$seller_id);

$this->files[$seller_id] = fopen( $this->filenames[$seller_id], "wb+");

fputcsv($this->files[$seller_id], $this->header);

}

function switch_file($handle) {

$seller_id = $this->product_sellers[$this->product_sellers_pos++];

return $this->files[$seller_id];

}

function close_csv_files() {

foreach($this->files as $f)

if(get_resource_type($f) == 'stream')

fclose($f);

}

function send_csv_files($processed, $filename, $filepath, $exporter) {

global $ts_mail_errors;

foreach($this->filenames as $seller_id => $filepath) { // override filepath

$user_info = get_userdata($seller_id);

if( !empty($user_info->user_email) ) {

$exporter->destination['email_recipients'] = $user_info->user_email;//override email

$ts_mail_errors = array();

echo $exporter->run_export( $filename, $filepath )."n";

}

return true;

}

new Woe_Seller_CSV_mod();

Export Products as columns

Advanced Order Exportfor WooCommercePRO

Advanced Order Export
for WooCommerce
PRO