SELECT 
	shp_orders.*,
	invoice.client_id AS order_client_id,
	invoice.client_firstname AS order_client_firstname,
	invoice.client_lastname AS order_client_lastname,
	invoice.client_company AS order_client_company,
	shipping.client_id AS order_invoice_client_id,
	order_shipping_item_ids,
	order_item_quantity_sum,
	order_shipping_states,
	order_payment_ids,
	order_payment_methods,
	order_payments_total_amount
FROM shp_orders
LEFT JOIN shp_clients AS shipping ON
	shipping.client_order_id = order_id AND
	( shipping.client_type != 'invoice')
LEFT JOIN shp_clients AS invoice ON
	invoice.client_order_id = order_id AND
	( invoice.client_type = 'invoice' OR ISNULL(invoice.client_type))
LEFT JOIN ( SELECT
	shipping_order_id,
	GROUP_CONCAT(CONCAT(shipping_id,':',item_id)) AS order_shipping_item_ids,
	GROUP_CONCAT(DISTINCT shipping_status) AS order_shipping_states,
	SUM(IFNULL(item_quantity, 0)) AS order_item_quantity_sum
	FROM shp_shippings
	LEFT JOIN shp_items ON item_shipping_id = shipping_id
	WHERE (shipping_order_id IN (:ids) OR  ISNULL(:ids))
	GROUP BY shipping_order_id
) shippings ON shipping_order_id = order_id
LEFT JOIN ( SELECT 
	payment_order_id,
	GROUP_CONCAT(payment_method) AS order_payment_methods,
	GROUP_CONCAT(payment_id) AS order_payment_ids,
	SUM(payment_amount) AS order_payments_total_amount
	FROM shp_payments 
	WHERE (payment_order_id IN (:ids) OR  ISNULL(:ids))
	GROUP BY payment_order_id
) payments ON payment_order_id = order_id
WHERE
	(order_id IN (:ids) OR (ISNULL(:ids)) AND order_id != 1 ) AND
	(	
		ISNULL(:filter) OR
		(:filter != 'shipping' AND order_status = :filter) OR
		:filter = 'all' OR
		(FIND_IN_SET(:filter,order_shipping_states) AND order_status != 'cancelled') OR
		(:filter = 'finished' AND order_status = 'shipped' AND order_payments_total_amount = order_total_amount) OR
		(:filter = 'problems' AND (

			invoice.client_email = '' OR
			order_status NOT IN ('unpaid', 'shipping', 'finished', 'cancelled')
		))
	) AND (
		order_number LIKE CONCAT('%',:search,'%') OR
		invoice.client_firstname LIKE CONCAT('%',:search,'%') OR
		invoice.client_lastname LIKE CONCAT('%',:search,'%') OR
		invoice.client_company LIKE CONCAT('%',:search,'%') OR
		order_id = :search OR
		ISNULL(:search)
    ) AND (
        shp_orders.order_created_date >= :from
    ) AND(
        shp_orders.order_created_date <= :to   
    )
ORDER BY 
	order_created_date DESC
LIMIT 
	:limit
OFFSET
	:paging