Sum quantities of products ordered by product name

(@algolplus)

Hello

what format do you use ?

Thread Starter

(@intelcoders)

Hellow @algolplus

I am not sure I understand what you mean, I assume you mean field format? The field format would be number since it’ll display the total sum of products qty, and the format of the file is .XLS

This reply was modified 1 month, 1 week ago by intelcoders.

Plugin Author

(@algolplus)

Excel? Csv ? Pdf?

Thread Starter

(@intelcoders)

It’s an .xls file. Thanks.

This reply was modified 1 month, 1 week ago by intelcoders.

Plugin Author

(@algolplus)

Could you try checkbox “Summary Report by Products”.
it’s below Date filter.

if I misunderstood you — please, share sample Excel file

Thread Starter

(@intelcoders)

Hi, Thanks for the quick reply. I have uploaded a screenshot of the excel file that I am trying to output. As seen on the screenshot I am trying to add column G that will output the sum of product quantities for all orders within the sheet.

The image URL is https://i.imgur.com/CDlEnn6.png

Also, just to note, I have tried exporting “summary report by products” but it always exports an empty excel even though there are completed orders within the given daterange. If I uncheck the summary report by products checkbox and export a regular report, then it’s working fine.

Plugin Author

(@algolplus)

“summary report by products” — can you try button “export without progress” ?

Thank you for the file.
Visit https://docs.algolplus.com/algol_order_export/sort-by-product-names/
Copy last code
Add this code to functions.php
Try to export , you will see checkbox at bottom.

If everything works ok – you must modify code!
– edit function sort_rows
– remove usort
– add your foreach(twice!) to fill custom field

NB. You must add this field via >Setup Fields>Product Order Items>Add Field .
As described at https://docs.algolplus.com/algol_order_export/add-calculated-field-for-product/

Thread Starter

(@intelcoders)

Hi @algolplus , ‘summary report by products’ is working if using ‘export without progress’ button. Thanks a lot.

I also added the given code to functions.php and I am able to see the checkbox at the bottom called “Sort by products, Format XLS”. Checking this option and exporting generates the report with products column sorted.

Now on to editing the sort_rows function code.
-removed the usort
-added the field via >Setup Fields>Product Order Items>Add Field .

updated function below

function sort_rows($phpExcel,$formatter) {
		foreach($_SESSION['woe_temp_rows'] as $pos=>$data)
			$this->output_modified_xls($formatter,$data,$pos+2);
	}

-Could you please help me out with the foreach function that I have to add twice?

Appreciate your help

Plugin Author

(@algolplus)

could you please share your settings as .txt file?
use tab Tools to get them.

Thread Starter

(@intelcoders)

Hi, I’ve pasted the settings below

{
    "now": {
        "version": "2.0",
        "mode": "now",
        "title": "",
        "skip_empty_file": true,
        "log_results": false,
        "from_status": [],
        "to_status": [],
        "change_order_status_to": "",
        "statuses": [],
        "from_date": "2022-05-17",
        "to_date": "2022-05-17",
        "sub_start_from_date": "",
        "sub_start_to_date": "",
        "sub_end_from_date": "",
        "sub_end_to_date": "",
        "sub_next_paym_from_date": "",
        "sub_next_paym_to_date": "",
        "from_order_id": "",
        "to_order_id": "",
        "shipping_locations": [],
        "shipping_methods": [],
        "item_names": [],
        "item_metadata": [],
        "user_roles": [],
        "user_names": [],
        "user_custom_fields": [],
        "billing_locations": [],
        "payment_methods": [],
        "any_coupon_used": "0",
        "coupons": [],
        "order_custom_fields": [],
        "product_categories": [],
        "product_vendors": [],
        "products": [],
        "product_sku": "",
        "exclude_products": [],
        "product_taxonomies": [],
        "product_custom_fields": [],
        "product_attributes": [],
        "product_itemmeta": [],
        "format": "XLS",
        "format_xls_use_xls_format": "0",
        "format_xls_sheet_name": "Orders",
        "format_xls_display_column_names": "1",
        "format_xls_auto_width": "1",
        "format_xls_direction_rtl": "0",
        "format_xls_force_general_format": "0",
        "format_xls_remove_emojis": "0",
        "format_xls_row_images_width": "50",
        "format_xls_row_images_height": "50",
        "format_csv_enclosure": "\"",
        "format_csv_delimiter": ",",
        "format_csv_linebreak": "\\r\\n",
        "format_csv_display_column_names": "1",
        "format_csv_add_utf8_bom": "0",
        "format_csv_item_rows_start_from_new_line": "0",
        "format_csv_encoding": "UTF-8",
        "format_csv_delete_linebreaks": "0",
        "format_csv_remove_linebreaks": "0",
        "format_csv_force_quotes": "0",
        "format_tsv_linebreak": "\\r\\n",
        "format_tsv_display_column_names": "1",
        "format_tsv_add_utf8_bom": "0",
        "format_tsv_item_rows_start_from_new_line": "0",
        "format_tsv_encoding": "UTF-8",
        "format_xml_root_tag": "Orders",
        "format_xml_order_tag": "Order",
        "format_xml_product_tag": "Product",
        "format_xml_coupon_tag": "Coupon",
        "format_xml_prepend_raw_xml": "",
        "format_xml_append_raw_xml": "",
        "format_xml_self_closing_tags": "1",
        "format_xml_preview_format": "0",
        "format_json_start_tag": "[",
        "format_json_end_tag": "]",
        "format_json_unescaped_slashes": 0,
        "format_json_numeric_check": 0,
        "format_json_encode_unicode": 0,
        "format_pdf_display_column_names": "1",
        "format_pdf_repeat_header": "1",
        "format_pdf_direction_rtl": 0,
        "format_pdf_orientation": "L",
        "format_pdf_page_size": "A4",
        "format_pdf_font_size": "8",
        "format_pdf_header_text": "",
        "format_pdf_footer_text": "",
        "format_pdf_pagination": "C",
        "format_pdf_fit_page_width": "0",
        "format_pdf_cols_width": "25",
        "format_pdf_cols_align": "L",
        "format_pdf_cols_vertical_align": "T",
        "format_pdf_page_header_text_color": "#000000",
        "format_pdf_page_footer_text_color": "#000000",
        "format_pdf_table_header_text_color": "#000000",
        "format_pdf_table_header_background_color": "#FFFFFF",
        "format_pdf_table_row_text_color": "#000000",
        "format_pdf_table_row_background_color": "#FFFFFF",
        "format_pdf_logo_source_id": "0",
        "format_pdf_logo_source": "",
        "format_pdf_logo_width": "0",
        "format_pdf_logo_height": "15",
        "format_pdf_logo_align": "R",
        "format_pdf_row_images_width": "15",
        "format_pdf_row_images_height": "15",
        "format_pdf_row_images_add_link": "0",
        "format_pdf_row_dont_page_break_order_lines": "0",
        "format_html_display_column_names": "1",
        "format_html_repeat_header_last_line": "0",
        "format_html_font_size": "13",
        "format_html_header_text": "",
        "format_html_footer_text": "",
        "format_html_cols_align": "L",
        "format_html_header_text_color": "#000000",
        "format_html_footer_text_color": "#000000",
        "format_html_table_header_text_color": "#000000",
        "format_html_table_header_background_color": "#FFFFFF",
        "format_html_table_row_text_color": "#000000",
        "format_html_table_row_background_color": "#FFFFFF",
        "format_html_row_images_width": "100",
        "format_html_row_images_height": "100",
        "format_html_images_add_link": "0",
        "format_html_custom_css": "",
        "all_products_from_order": "1",
        "skip_refunded_items": "0",
        "skip_suborders": "0",
        "export_refunds": "0",
        "export_matched_items": "0",
        "date_format": "Y-m-d",
        "time_format": "H:i",
        "sort_direction": "DESC",
        "sort": "order_id",
        "format_number_fields": "0",
        "export_all_comments": "0",
        "export_refund_notes": "0",
        "strip_tags_product_fields": "0",
        "round_item_tax_rate": "0",
        "cleanup_phone": "0",
        "convert_serialized_values": "0",
        "enable_debug": "0",
        "billing_details_for_shipping": "0",
        "custom_php": "0",
        "custom_php_code": "",
        "mark_exported_orders": "0",
        "export_unmarked_orders": "0",
        "summary_report_by_products": "0",
        "duplicated_fields_settings": {
            "products": {
                "repeat": "rows",
                "populate_other_columns": "1",
                "max_cols": "10",
                "group_by": "product",
                "line_delimiter": "\\n"
            },
            "coupons": {
                "repeat": "rows",
                "max_cols": "10",
                "group_by": "product",
                "line_delimiter": "\\n"
            }
        },
        "summary_report_by_customers": "0",
        "order_fields": [
            {
                "segment": "coupons",
                "key": "coupons",
                "colname": "Coupons",
                "label": "Coupons",
                "format": "undefined"
            },
            {
                "segment": "common",
                "key": "plain_orders_admin_agent_note",
                "label": "admin_agent_note",
                "format": "",
                "colname": "Agent"
            },
            {
                "segment": "common",
                "key": "plain_orders__greenbox_important_note",
                "label": "_greenbox_important_note",
                "format": "",
                "colname": "Payment"
            },
            {
                "segment": "common",
                "key": "plain_orders_order_type",
                "label": "order_type",
                "format": "",
                "colname": "order type"
            },
            {
                "segment": "products",
                "key": "products",
                "colname": "Products",
                "label": "Products",
                "format": "undefined"
            },
            {
                "segment": "products",
                "key": "plain_products_name",
                "label": "Item Name",
                "format": "string",
                "colname": "Item Name"
            },
            {
                "segment": "products",
                "key": "plain_products_qty",
                "label": "Quantity",
                "format": "number",
                "colname": "Quantity"
            },
            {
                "segment": "products",
                "key": "plain_products_item_price",
                "label": "Item Cost",
                "format": "money",
                "colname": "Item Cost"
            },
            {
                "segment": "products",
                "key": "plain_products_line_total",
                "label": "Order Line Total",
                "format": "money",
                "colname": "Order Line Total"
            },
            {
                "segment": "cart",
                "key": "payment_method",
                "label": "Payment Method",
                "format": "string",
                "colname": "Payment Method"
            },
            {
                "segment": "common",
                "key": "order_number",
                "label": "Order Number",
                "format": "string",
                "colname": "Order Number"
            },
            {
                "segment": "common",
                "key": "order_status",
                "label": "Order Status",
                "format": "string",
                "colname": "Order Status"
            },
            {
                "segment": "products",
                "key": "plain_products_supplier_name",
                "label": "supplier_name",
                "format": "",
                "colname": "supplier_name"
            },
            {
                "segment": "common",
                "key": "order_date",
                "label": "Order Date",
                "format": "date",
                "colname": "Order Date"
            },
            {
                "segment": "billing",
                "key": "billing_first_name",
                "label": "First Name (Billing)",
                "format": "string",
                "colname": "First Name (Billing)"
            },
            {
                "segment": "billing",
                "key": "billing_last_name",
                "label": "Last Name (Billing)",
                "format": "string",
                "colname": "Last Name (Billing)"
            },
            {
                "segment": "cart",
                "key": "order_subtotal",
                "label": "Order Subtotal Amount",
                "format": "money",
                "colname": "Order Subtotal Amount"
            },
            {
                "segment": "ship_calc",
                "key": "shipping_method_title",
                "label": "Shipping Method Title",
                "format": "string",
                "colname": "Shipping Method Title"
            },
            {
                "segment": "ship_calc",
                "key": "order_shipping",
                "label": "Order Shipping Amount",
                "format": "money",
                "colname": "Order Shipping Amount"
            },
            {
                "segment": "totals",
                "key": "order_total",
                "label": "Order Total Amount",
                "format": "money",
                "colname": "Order Total Amount"
            },
            {
                "segment": "products",
                "key": "plain_products_product_sum",
                "label": "product_sum",
                "format": "number",
                "colname": "product_sum"
            }
        ],
        "order_product_fields": [
            {
                "label": "SKU",
                "format": "string",
                "colname": "SKU",
                "default": 1,
                "key": "sku"
            },
            {
                "label": "Item #",
                "format": "number",
                "colname": "Item #",
                "default": 1,
                "key": "line_id"
            },
            {
                "label": "Item Name",
                "format": "string",
                "colname": "Item Name",
                "default": 1,
                "key": "name"
            },
            {
                "label": "Quantity",
                "format": "number",
                "colname": "Quantity",
                "default": 1,
                "key": "qty"
            },
            {
                "label": "Item Cost",
                "format": "money",
                "colname": "Item Cost",
                "default": 1,
                "key": "item_price"
            }
        ],
        "order_coupon_fields": [
            {
                "label": "Coupon Code",
                "format": "string",
                "colname": "Coupon Code",
                "default": 1,
                "key": "code"
            },
            {
                "label": "Discount Amount",
                "format": "money",
                "colname": "Discount Amount",
                "default": 1,
                "key": "discount_amount"
            },
            {
                "label": "Discount Amount Tax",
                "format": "money",
                "colname": "Discount Amount Tax",
                "default": 1,
                "key": "discount_amount_tax"
            }
        ],
        "id": 0,
        "post_type": "shop_order",
        "export_rule_field": "date",
        "export_filename": "orders-%y-%m-%d-%h-%i-%s.xlsx",
        "sort_by_products": "1"
    },
    "profiles": [],
    "cron": [],
    "order-action": []
}

Plugin Author

(@algolplus)

please, replace function sort_rows() with this code

function sort_rows($phpExcel,$formatter) {
		$product_sums = array();
		// count sums 
		foreach($_SESSION['woe_temp_rows'] as $pos=>$data) {
			$key = $data['plain_products_name'];
			if( !isset($product_sums[$key]) ) 	
				$product_sums[$key] = 0;
			$product_sums[$key] += $data['plain_products_qty'];
		}
		// fill column
		foreach($_SESSION['woe_temp_rows'] as $pos=>$data) {
			$key = $data['plain_products_name'];
			$_SESSION['woe_temp_rows'][$pos]['plain_products_product_sum'] = $product_sums[$key];
		}

		// output
		foreach($_SESSION['woe_temp_rows'] as $pos=>$data)
			$this->output_modified_xls($formatter,$data,$pos+2);
	}

Thread Starter

(@intelcoders)

It worked!! I cannot thank you enough, that was exactly what I was trying to achieve. Thanks again.

Plugin Author

(@algolplus)