DELIMITER $$
DROP procedure IF EXISTS `bd_RprtsOperatorInvoice`;
CREATE PROCEDURE `bd_RprtsOperatorInvoice`(IN opId INT,IN filterMonth TEXT,IN ilimit VARCHAR(50),IN isPrint INT)
BEGIN
	DROP temporary table if exists T1;
	DROP temporary table if exists T7;
	DROP temporary table if exists T14;
	DROP temporary table if exists T21;
	DROP temporary table if exists T28;
	DROP temporary table if exists TD;

	set @filterMonth = concat(filterMonth,'%');
	set @filterDate_7Start = concat(filterMonth,'-07 00:00:00');
	set @filterDate_7End = concat(filterMonth,'-07 23:59:59');
	set @filterDate_14Start = concat(filterMonth,'-14 00:00:00');
	set @filterDate_14End = concat(filterMonth,'-14 23:59:59');
	set @filterDate_21Start = concat(filterMonth,'-21 00:00:00');
	set @filterDate_21End = concat(filterMonth,'-21 23:59:59');
	set @filterDate_28Start = concat(filterMonth,'-28 00:00:00');
	set @filterDate_28End = concat(filterMonth,'-28 23:59:59');
	
	create temporary table T1 
	SELECT x.product_id,x.price FROM oc_product_option_value x inner join 
		oc_option_value_description y on x.option_value_id = y. option_value_id
		where y.name like '1 month';

		SET @queryStringData = concat("create temporary table TD SELECT ux.username,if(pd.is_package=0,0,1) as isPackage,c.operator_id,pd.product_id , pd.model AS product_number, d.name FROM `oc_subscriptionDesubscription` cas INNER JOIN oc_customer c ON c.customer_id = cas.user_id INNER JOIN oc_product_description d ON d.product_id = cas.product_id INNER JOIN oc_product pd ON d.product_id = pd.product_id INNER JOIN ( SELECT max(id) as id from oc_subscriptionDesubscription WHERE cmd_id IN(6,10) AND exp_date > '",@filterDate_7Start,"' AND modify_date <= '",@filterDate_28End,"' group by user_id, product_id ) bm on cas.id = bm.id inner join oc_user ux on ux.user_id = c.operator_id  GROUP BY pd.product_id,c.operator_id");

	SET @queryString7th = concat("create temporary table T7 SELECT ux.username,if(pd.is_package=0,0,1) as isPackage,c.operator_id,pd.product_id , pd.model AS product_number, d.name,COUNT(*) AS count FROM `oc_subscriptionDesubscription` cas INNER JOIN oc_customer c ON c.customer_id = cas.user_id INNER JOIN oc_product_description d ON d.product_id = cas.product_id INNER JOIN oc_product pd ON d.product_id = pd.product_id INNER JOIN ( SELECT max(id) as id from oc_subscriptionDesubscription WHERE cmd_id IN(6,10) AND exp_date > '",@filterDate_7Start,"' AND modify_date <= '",@filterDate_7End,"' group by user_id, product_id ) bm on cas.id = bm.id inner join oc_user ux on ux.user_id = c.operator_id  GROUP BY pd.product_id,c.operator_id");

	SET @queryString14th = concat("create temporary table T14 SELECT ux.username,if(pd.is_package=0,0,1) as isPackage,c.operator_id,pd.product_id , pd.model AS product_number, d.name,COUNT(*) AS count FROM `oc_subscriptionDesubscription` cas INNER JOIN oc_customer c ON c.customer_id = cas.user_id INNER JOIN oc_product_description d ON d.product_id = cas.product_id INNER JOIN oc_product pd ON d.product_id = pd.product_id INNER JOIN ( SELECT max(id) as id from oc_subscriptionDesubscription WHERE cmd_id IN(6,10) AND exp_date > '",@filterDate_14Start,"' AND modify_date <= '",@filterDate_14End,"' group by user_id, product_id ) bm on cas.id = bm.id inner join oc_user ux on ux.user_id = c.operator_id  GROUP BY pd.product_id,c.operator_id");

	SET @queryString21st = concat("create temporary table T21 SELECT ux.username,if(pd.is_package=0,0,1) as isPackage,c.operator_id,pd.product_id , pd.model AS product_number, d.name,COUNT(*) AS count FROM `oc_subscriptionDesubscription` cas INNER JOIN oc_customer c ON c.customer_id = cas.user_id INNER JOIN oc_product_description d ON d.product_id = cas.product_id INNER JOIN oc_product pd ON d.product_id = pd.product_id INNER JOIN ( SELECT max(id) as id from oc_subscriptionDesubscription WHERE cmd_id IN(6,10) AND exp_date > '",@filterDate_21Start,"' AND modify_date <= '",@filterDate_21End,"' group by user_id, product_id ) bm on cas.id = bm.id inner join oc_user ux on ux.user_id = c.operator_id  GROUP BY pd.product_id,c.operator_id");

	SET @queryString28th = concat("create temporary table T28 SELECT ux.username,if(pd.is_package=0,0,1) as isPackage,c.operator_id,pd.product_id , pd.model AS product_number, d.name,COUNT(*) AS count FROM `oc_subscriptionDesubscription` cas INNER JOIN oc_customer c ON c.customer_id = cas.user_id INNER JOIN oc_product_description d ON d.product_id = cas.product_id INNER JOIN oc_product pd ON d.product_id = pd.product_id INNER JOIN ( SELECT max(id) as id from oc_subscriptionDesubscription WHERE cmd_id IN(6,10) AND exp_date > '",@filterDate_28Start,"' AND modify_date <= '",@filterDate_28End,"' group by user_id, product_id ) bm on cas.id = bm.id inner join oc_user ux on ux.user_id = c.operator_id  GROUP BY pd.product_id,c.operator_id");

	PREPARE QStmtData FROM @queryStringData;

	PREPARE QStmt7 FROM @queryString7th;
	PREPARE QStmt14 FROM @queryString14th;
	PREPARE QStmt21 FROM @queryString21st;
	PREPARE QStmt28 FROM @queryString28th;
	EXECUTE QStmtData;
	EXECUTE QStmt7;
	EXECUTE QStmt14;
	EXECUTE QStmt21;
	EXECUTE QStmt28;
	
	DEALLOCATE PREPARE QStmtData;
	DEALLOCATE PREPARE QStmt7;
	DEALLOCATE PREPARE QStmt14;
	DEALLOCATE PREPARE QStmt21;
	DEALLOCATE PREPARE QStmt28;

	SET @QxData ="select b.*,TRUNCATE(a.price,2) as price from T1 a inner join TD b on a.product_id = b.product_id";

	SET @Qx7 ="select b.count as 7count,b.product_id from T1 a inner join T7 b on a.product_id = b.product_id";

	SET @Qx14 ="select b.count as 14count,b.product_id from T1 a inner join T14 b on a.product_id = b.product_id";
	
	SET @Qx21 ="select b.count as 21count,b.product_id from T1 a inner join T21 b on a.product_id = b.product_id";
	
	SET @Qx28 ="select b.count as 28count,b.product_id from T1 a inner join T28 b on a.product_id = b.product_id";

	if(opId!='-1')then
		SET @QxData = concat(@QxData," where operator_id=",opId);
		SET @Qx7 = concat(@Qx7," where operator_id=",opId);
		SET @Qx14 = concat(@Qx14," where operator_id=",opId);
		SET @Qx21 = concat(@Qx21," where operator_id=",opId);
		SET @Qx28 = concat(@Qx28," where operator_id=",opId);
	end if;
	SET @QxData=concat(@QxData," order by operator_id");
	SET @Qx7=concat(@Qx7," order by operator_id");
	SET @Qx14=concat(@Qx14," order by operator_id");
	SET @Qx21=concat(@Qx21," order by operator_id");
	SET @Qx28=concat(@Qx28," order by operator_id");
	 if(isPrint=1) then
		SET @QxData = concat(@QxData,ilimit);
		SET @Qx7 = concat(@Qx7,ilimit);
		SET @Qx14 = concat(@Qx14,ilimit);
		SET @Qx21 = concat(@Qx21,ilimit);
		SET @Qx28 = concat(@Qx28,ilimit);
	end if;
	PREPARE QStmtData FROM @QxData;
	PREPARE QStmt7 FROM @Qx7;
	PREPARE QStmt14 FROM @Qx14;
	PREPARE QStmt21 FROM @Qx21;
	PREPARE QStmt28 FROM @Qx28;
	EXECUTE QStmtData;
	EXECUTE QStmt7;
	EXECUTE QStmt14;
	EXECUTE QStmt21;
	EXECUTE QStmt28;

	DEALLOCATE PREPARE QStmtData;
	DEALLOCATE PREPARE QStmt7;
	DEALLOCATE PREPARE QStmt14;
	DEALLOCATE PREPARE QStmt21;
	DEALLOCATE PREPARE QStmt28;
	
	DROP temporary table if exists T1;
	DROP temporary table if exists T7;
	DROP temporary table if exists T14;
	DROP temporary table if exists T21;
	DROP temporary table if exists T28;
	DROP temporary table if exists TD;
END $$
DELIMITER ;