package Stats::Redshift::SaleStage::DTMV;

use strict;
use warnings;

use lib '/app/tools/stats/lib';
use base 'Stats::Redshift::SaleStage';

sub exportsql {
    my $self = shift;
    my $cid  = $self->database->client_id;
    my $sql  = (
        qq{
SELECT /* :comment: */
	sale.sale_id,
	sale.file_id,
	ABS(TIMESTAMPDIFF(MONTH, sale.date_begin, '1916-08-01')) month_id,
	ABS(TIMESTAMPDIFF(MONTH, sale.date_end, '1916-08-01')) end_month_id,
	ABS(TIMESTAMPDIFF(QUARTER, sale.date_begin, '1916-07-01')) + 667 quarter_id,
	YEAR(sale.date_end) `year`,
	sale.product_id,
	'bk' product_type,
	onix_code.code_value format_type,
	IFNULL(sale.service_id, f.service_id) service_id,
	book_product.imprint_id,
	book_product.publisher_id,
	sale.units,
	sale.revenue price,
	sale.country_code,
	sale.conversion_rate,
	sale.revenue * sale.conversion_rate revenue,
	date_begin,
	date_end,
	DATE(NOW()),
	$cid
FROM
	:database:.sale 
	JOIN :database:.file f ON f.file_id = sale.file_id
		AND f.file_status IN(4, 5)
	JOIN :database:.book_product USING (product_id)
	JOIN :database:.book USING (book_id)
	JOIN :database:.book_format USING (book_format_id)
	JOIN :database:.book_format_type USING (book_format_type_id)
	JOIN :database:.onix_code USING (onix_code_id)
WHERE 1
	AND DAY(sale.date_begin) != 0
	AND IFNULL(sale.product_id, 0) != 0
	AND IFNULL(sale.revenue, 0) != 0
	AND IFNULL(sale.conversion_rate, 0) != 0
    AND sale.sale_id >= :min:
    AND sale.sale_id < :max:
UNION ALL
SELECT
	sale.sale_id,
	sale.file_id,
	ABS(TIMESTAMPDIFF(MONTH, sale.date_begin, '1916-08-01')) month_id,
	ABS(TIMESTAMPDIFF(MONTH, sale.date_end, '1916-08-01')) end_month_id,
	ABS(TIMESTAMPDIFF(QUARTER, sale.date_begin, '1916-07-01')) + 667 quarter_id,
	YEAR(sale.date_end) `year`,
	sale.product_id,
	'ch' product_type,
	book_format_id format_type,
	IFNULL(sale.service_id, f.service_id) service_id,
	chapter_product.imprint_id,
	chapter_product.publisher_id,
	sale.units,
	sale.revenue price,
	sale.country_code,
	sale.conversion_rate,
	sale.revenue * sale.conversion_rate revenue,
	date_begin,
	date_end,
	DATE(NOW()),
	$cid
FROM
	:database:.sale
	JOIN :database:.file f ON f.file_id = sale.file_id
		AND f.file_status IN(4, 5)
	JOIN :database:.chapter_product ON sale.product_id = chapter_product.product_id
	JOIN :database:.chapter ON chapter.chapter_id = chapter_product.chapter_id
	JOIN :database:.book ON chapter.book_id = book.book_id
WHERE 1
	AND DAY(sale.date_begin) != 0
	AND IFNULL(sale.product_id, 0) != 0
	AND IFNULL(sale.revenue, 0) != 0
	AND IFNULL(sale.conversion_rate, 0) != 0
	AND sale.sale_id >= :min:
	AND sale.sale_id < :max:
INTO OUTFILE ':file:'
	}
    );
    $sql;
}

sub redshift_insert {
    my $self = shift;
    my $sql  = (
        qq{
SELECT
	sale.sale_id,
	sale.file_id,
	DATEDIFF(MONTH,TO_DATE('19160801','YYYYMMDD'),sale.date_begin) month_id,
	DATEDIFF(MONTH,TO_DATE('19160801','YYYYMMDD'),sale.date_end) end_month_id,
	DATEDIFF(QUARTER,TO_DATE('19160701','YYYYMMDD'),sale.date_begin)+667 quarter_id,
	EXTRACT(YEAR FROM sale.date_end) AS year,
	sale.product_id,
	'bk' product_type,
	o.code_value format_type,
	NVL(sale.service_id, f.service_id) service_id,
	bp.imprint_id,
	bp.publisher_id,
	sale.units,
	sale.revenue price,
	sale.country_code,
	sale.conversion_rate,
	sale.revenue * sale.conversion_rate revenue,
	sale.date_begin,
	sale.date_end,
	sale.date_loaded,
	sale.client_id
FROM
	dtmv.sale
	JOIN dtmv.file f ON f.file_id = sale.file_id
		AND f.client_id = sale.client_id
		AND f.file_status IN(4, 5)
	JOIN dtmv.book_product bp ON bp.product_id = sale.product_id
		AND bp.client_id = sale.client_id
	JOIN dtmv.book b ON b.book_id = bp.book_id
		AND b.client_id = bp.client_id
	JOIN dtmv.book_format bf ON bf.book_format_id = bp.book_format_id
		AND bf.client_id = bp.client_id
	JOIN dtmv.book_format_type bft ON bft.book_format_type_id = bf.book_format_type_id
		AND bft.client_id = bf.client_id
	JOIN dtmv.onix_code o ON o.onix_code_id = bft.onix_code_id
		AND o.client_id = bft.client_id
WHERE
	1
	AND EXTRACT(DAY FROM sale.date_begin) != 0
	AND NVL(sale.product_id, 0) != 0
	AND NVL(sale.revenue, 0) != 0
	AND NVL(sale.conversion_rate, 0) != 0
UNION ALL
SELECT
	sale.sale_id,
	sale.file_id,
	DATEDIFF(MONTH,TO_DATE('19160801','YYYYMMDD'),sale.date_begin) month_id,
	DATEDIFF(MONTH,TO_DATE('19160801','YYYYMMDD'),sale.date_end) end_month_id,
	DATEDIFF(QUARTER,TO_DATE('19160701','YYYYMMDD'),sale.date_begin)+667 quarter_id,
	EXTRACT(YEAR FROM sale.date_end) AS year,
	sale.product_id,
	'ch' product_type,
	o.code_value format_type,
	NVL(sale.service_id, f.service_id) service_id,
	cp.imprint_id,
	cp.publisher_id,
	sale.units,
	sale.revenue price,
	sale.country_code,
	sale.conversion_rate,
	sale.revenue * sale.conversion_rate revenue,
	sale.date_begin,
	sale.date_end,
	sale.date_loaded,
	sale.client_id
FROM
	dtmv.sale
	JOIN dtmv.file f ON f.file_id = sale.file_id
		AND f.client_id = sale.client_id
		AND f.file_status IN(4, 5)
	JOIN dtmv.chapter_product cp ON cp.product_id = sale.product_id
		AND cp.client_id = sale.client_id
	JOIN dtmv.chapter c ON c.chapter_id=cp.chapter_id
		AND c.client_id=cp.client_id
	JOIN dtmv.book_format bf ON bf.book_format_id = cp.book_format_id
		AND bf.client_id = cp.client_id
	JOIN dtmv.book_format_type bft ON bft.book_format_type_id = bf.book_format_type_id
		AND bft.client_id = bf.client_id
	JOIN dtmv.book b ON b.book_id = c.book_id
		AND b.client_id = c.client_id
	JOIN dtmv.onix_code o ON o.onix_code_id = bft.onix_code_id
		AND o.client_id = bft.client_id
WHERE
	1
	AND EXTRACT(DAY FROM sale.date_begin) != 0
	AND NVL(sale.product_id, 0) != 0
	AND NVL(sale.revenue, 0) != 0
	AND NVL(sale.conversion_rate, 0) != 0
}
    );
}

1;