Support » Plugin: Advanced Order Export For WooCommerce » Sum quantities of products ordered by product name

  • Resolved intelcoders

    (@intelcoders)


    Hi,

    I am trying to add a calculated field that sums all product quantities(product wise- not orderwise) within the report that is being exported.

    For e.g, I want to add a custom field that will sum each products qty from all orders (within the report)

    OrderNumber ProductName ItemQty ItemPrice ItemTotal CustomField
    123 Product1 3 100 300 11
    1233 Product2 4 100 400 9
    1235 Product2 3 100 300 9
    1236 Product1 3 100 300 11
    1237 Product1 5 100 500 11

    I’ve gone through the documentation and but unable to find a way to do it. Please help.

    Thanks

Viewing 13 replies - 1 through 13 (of 13 total)
  • Plugin Author algol.plus

    (@algolplus)

    Hello

    what format do you use ?

    Thread Starter intelcoders

    (@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 algol.plus

    (@algolplus)

    Excel? Csv ? Pdf?

    Thread Starter intelcoders

    (@intelcoders)

    It’s an .xls file. Thanks.

    • This reply was modified 1 month, 1 week ago by intelcoders.
    Plugin Author algol.plus

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

    (@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 algol.plus

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

    (@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 algol.plus

    (@algolplus)

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

    Thread Starter intelcoders

    (@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 algol.plus

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

    (@intelcoders)

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

    Plugin Author algol.plus

    (@algolplus)

    you’re very welcome 🙂

Viewing 13 replies - 1 through 13 (of 13 total)
  • You must be logged in to reply to this topic.