Skip to content

armgit / Vvveb

Public Imported from GitHub
0

Powerful and easy to use cms to build websites, blogs or ecommerce stores.

1.5 KB
Raw Blame
-- Tax report: collected tax grouped by rate title and ship-to country,
-- plus refunded tax totals. Filing support: collected minus refunded.

	CREATE PROCEDURE collected(
		IN date_from CHAR,
		IN date_to CHAR,
		IN order_status_id ARRAY,
		IN country_id INT,
		IN site_id INT,
		OUT fetch_all
	)
	BEGIN
		-- order_total
		SELECT order_total.title AS name, country.name AS country, SUM(order_total.value) AS total, COUNT(DISTINCT order_total.order_id) AS orders
			FROM order_total
			LEFT JOIN `order` ON (order_total.order_id = `order`.order_id)
			LEFT JOIN country ON (`order`.shipping_country_id = country.country_id)
		WHERE order_total.`key` = 'tax'

		@IF !empty(:date_from)
		THEN
			AND `order`.created_at >= :date_from
		END @IF

		@IF !empty(:date_to)
		THEN
			AND `order`.created_at <= :date_to
		END @IF

		@IF !empty(:order_status_id)
		THEN
			AND `order`.order_status_id IN (:order_status_id)
		END @IF

		@IF !empty(:country_id)
		THEN
			AND `order`.shipping_country_id = :country_id
		END @IF

		@IF isset(:site_id) AND :site_id != ""
		THEN
			AND `order`.site_id = :site_id
		END @IF

		GROUP BY order_total.title, country.name
		ORDER BY total DESC;
	END

	CREATE PROCEDURE refunded(
		IN date_from CHAR,
		IN date_to CHAR,
		OUT fetch_one
	)
	BEGIN
		SELECT SUM(tax_amount) FROM order_transaction
		WHERE kind = 'refund' AND status = 'succeeded'

		@IF !empty(:date_from)
		THEN
			AND created_at >= :date_from
		END @IF

		@IF !empty(:date_to)
		THEN
			AND created_at <= :date_to
		END @IF
		;
	END