DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_debtor_aging_by_salesman`; $$

CREATE PROCEDURE `sp_debtor_aging_by_salesman` (IN p_createdby INT, IN p_report_id VARCHAR(50), IN backdate BOOL,
	IN p_salesman_id INT, IN date_to DATETIME, IN by_bill_agent INT, IN get_mtd_coll_amt BOOL, IN p_division_str VARCHAR(100))
BEGIN
	DECLARE v_trans_id, v_customer_id, v_doc_id, v_division_id INT(10);
	DECLARE v_doc_date, v_due_date DATETIME;
	DECLARE v_local_amt, v_paid_local_amt  DECIMAL(11,4);
	DECLARE done INT DEFAULT FALSE;
	DECLARE v_doc_type VARCHAR(3);
	DECLARE insert_values LONGTEXT  DEFAULT '';
	
    DECLARE ko_after_amt DECIMAL(11,4) DEFAULT 0;
    DECLARE sum_ko_after_amt DECIMAL(11,4) DEFAULT 0;
    DECLARE any_payment_in_ko_is_postdate DECIMAL(11,4) DEFAULT 0;
    DECLARE any_payment_in_ko_is_postdate_2 DECIMAL(11,4) DEFAULT 0;
    DECLARE postdate_ko_amt DECIMAL(11,4) DEFAULT 0;
    DECLARE draft_collection_amt DECIMAL(11,4) DEFAULT 0;
    DECLARE mtd_collection_amt DECIMAL(11,4) DEFAULT 0;

	-- 1. Declare cursor and query all the trans_id for particular salesman
	DECLARE cur1 CURSOR FOR
	SELECT t.id, t.customer_id, t.doc_date, t.local_amt, t.paid_local_amt, t.doc_id, t.doc_type, t.division_id, t.due_date
	FROM `debtor_trans` t
	WHERE t.`createdby` > 0 AND t.`doc_date` <= date_to
	AND t.`salesman_id` = p_salesman_id
	ORDER BY t.`doc_date`, t.`doc_no`;
	
	DECLARE cur2 CURSOR FOR
	SELECT t.id, t.customer_id, t.doc_date, t.local_amt, t.paid_local_amt, t.doc_id, t.doc_type, t.division_id, t.due_date
	FROM `debtor_trans` t
	LEFT JOIN `customer` customer ON customer.`id` = t.`customer_id`
	LEFT JOIN `user_customer` user_customer ON user_customer.`customer_id` = t.`customer_id`
	WHERE t.`createdby` > 0 AND t.`doc_date` <= date_to
	AND customer.useryesno_03 <> 1 AND user_customer.`user_id` = p_salesman_id
	ORDER BY t.`doc_date`, t.`doc_no`;
	
	-- clear all the previous result
	IF p_division_str <> '' THEN
		SET @delete_as_string = CONCAT('DELETE FROM z_debtor_aging WHERE createdby = ', p_createdby, 
		' AND report_id = \'', p_report_id, '\' AND salesman_id = ', p_salesman_id, ' AND division_id IN(', p_division_str, ');');
	ELSE
		SET @delete_as_string = CONCAT('DELETE FROM z_debtor_aging WHERE createdby = ', p_createdby, 
		' AND report_id = \'', p_report_id, '\' AND salesman_id = ', p_salesman_id, ';');
	END IF;

	PREPARE del_statement FROM @delete_as_string;
	EXECUTE del_statement;
	DEALLOCATE PREPARE del_statement;
	
	-- get the MTD collection amount
	IF get_mtd_coll_amt = TRUE THEN 
		IF by_bill_agent = 1 THEN
			SELECT IFNULL(SUM(dt.local_amt * dt.sign) , 0)
			INTO mtd_collection_amt
			FROM debtor_trans dt
			WHERE dt.createdby > 0 AND dt.doc_type IN ('RE', 'XP') AND MONTH(dt.doc_date) = MONTH(date_to) AND YEAR(dt.doc_date) = YEAR(date_to) 
			AND dt.doc_date <= date_to AND dt.salesman_id = p_salesman_id
			GROUP BY dt.`salesman_id`;
		ELSE
			SELECT IFNULL(SUM(dt.local_amt * dt.sign) , 0)
			INTO mtd_collection_amt
			FROM debtor_trans dt
			LEFT JOIN `user_customer` uc ON uc.`customer_id` = dt.`customer_id`			
			WHERE dt.createdby > 0 AND dt.doc_type IN ('RE', 'XP') AND MONTH(dt.doc_date) = MONTH(date_to) AND YEAR(dt.doc_date) = YEAR(date_to) 
			AND dt.doc_date <= date_to AND uc.`user_id` = p_salesman_id
			GROUP BY uc.`user_id`;
		END IF;
	END IF;
	
	SET insert_values = '';
	
	BEGIN 
	
	-- declare handle
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
	-- open cursor
	IF by_bill_agent = 1 THEN
		OPEN cur1;
	ELSE
		OPEN cur2;
	END IF;
 
	-- starts the loop
	the_loop: LOOP
		-- get the values of each column into our variables
		
		IF by_bill_agent = 1 THEN
			FETCH cur1 INTO v_trans_id, v_customer_id, v_doc_date, v_local_amt, v_paid_local_amt, v_doc_id, v_doc_type, v_division_id, v_due_date;
		ELSE
			FETCH cur2 INTO v_trans_id, v_customer_id, v_doc_date, v_local_amt, v_paid_local_amt, v_doc_id, v_doc_type, v_division_id, v_due_date;
		END IF;
		
		IF done THEN
		  LEAVE the_loop;
		END IF;
		
		IF p_division_str <> '' THEN
			IF v_doc_type NOT IN ('XP', 'RE', 'XD') AND LOCATE(CONCAT(v_division_id, ','), CONCAT(p_division_str, ',')) = 0 THEN
				ITERATE the_loop;
			END IF;
		END IF;
		
		IF v_paid_local_amt > 0 THEN
			
			IF backdate = 1 THEN 
			
				IF v_due_date > date_to THEN
				
					SELECT IFNULL(SUM(ko1.ko_local_amt), 0)
					INTO ko_after_amt
					FROM `debtor_trans_ko` ko1
					WHERE ko1.hdr_date > date_to AND ko1.doc_id = v_trans_id;

					IF ko_after_amt > 0 THEN
					  SELECT IFNULL(SUM(ko1.ko_local_amt), 0)
					  INTO sum_ko_after_amt
					  FROM `debtor_trans_ko` ko1
					  WHERE (ko1.hdr_id = v_trans_id OR ko1.doc_id = v_trans_id) AND (ko1.hdr_date > date_to OR ko1.doc_date > date_to);
					END IF;

					IF ko_after_amt <= 0 THEN
					  SELECT IFNULL(SUM(ko2.ko_local_amt), 0)
					  INTO any_payment_in_ko_is_postdate
					  FROM debtor_trans_ko ko2
					  WHERE ko2.doc_id = v_trans_id AND ko2.id IN
					  (SELECT ko2.id FROM debtor_trans_ko ko3
					  WHERE ko3.hdr_id = ko2.hdr_id AND ko3.doc_date > date_to);
					END IF;

					IF ko_after_amt <= 0 THEN
					  SELECT IFNULL((CASE WHEN (EXISTS (SELECT ko4.id FROM debtor_trans_ko ko4
					  WHERE ko4.hdr_id = ko3.hdr_id AND ko4.doc_date > date_to)) THEN SUM(ko3.sign * ko3.ko_local_amt)
					  ELSE 0 END), 0)
					  INTO any_payment_in_ko_is_postdate_2
					  FROM debtor_trans_ko ko3
					  WHERE ko3.hdr_id = v_trans_id;
					END IF;

					IF ko_after_amt > 0 THEN
					  SET postdate_ko_amt = sum_ko_after_amt;
					ELSE
					  SET postdate_ko_amt = any_payment_in_ko_is_postdate - any_payment_in_ko_is_postdate_2;
					END IF;
					
				END IF;
				
				-- find draft collection amount
				IF v_doc_id = 0 THEN
					SELECT IFNULL(SUM(dtl.sign * dtl.ko_local_amt), 0)
					INTO draft_collection_amt
					FROM debtor_payment_dtl dtl
					LEFT JOIN debtor_payment_hdr hdr ON dtl.hdr_id = hdr.id
					WHERE hdr.status = 0 AND dtl.doc_type = v_doc_type AND dtl.doc_id = v_trans_id;
				ELSE
					SELECT IFNULL(SUM(dtl.sign * dtl.ko_local_amt), 0)
					INTO draft_collection_amt
					FROM debtor_payment_dtl dtl
					LEFT JOIN debtor_payment_hdr hdr ON dtl.hdr_id = hdr.id
					WHERE hdr.status = 0 AND dtl.doc_type = v_doc_type AND dtl.doc_id = v_doc_id;
				END IF;
				
			END IF;
			
			IF insert_values IS NULL THEN
				SET insert_values = '';
			END IF;
			
			IF insert_values <> '' THEN
				SET insert_values = CONCAT_WS('', insert_values, ',');
			END IF;
			
			IF postdate_ko_amt IS NULL THEN
				SET postdate_ko_amt = 0;
			END IF;
			
			SET insert_values = CONCAT_WS('', insert_values, '(NULL, ', v_trans_id, ',', p_salesman_id, ',', v_customer_id, ',\'', 
				v_doc_date, '\',', v_local_amt, ',', postdate_ko_amt, ',', draft_collection_amt, ',', v_paid_local_amt, ',', mtd_collection_amt, ',',
				'\'', p_report_id, '\'', ',\'', date_to, '\',', p_createdby, ',\'', now(), '\',', v_division_id, ')');
				
			SET postdate_ko_amt = 0;
		ELSE
			
			IF insert_values IS NULL THEN
				SET insert_values = '';
			END IF;
			
			IF insert_values <> '' THEN
				SET insert_values = CONCAT_WS('', insert_values, ',');
			END IF;
			
			SET insert_values = CONCAT_WS('', insert_values, '(NULL, ', v_trans_id, ',', p_salesman_id, ',', v_customer_id, ',\'', 
				v_doc_date, '\',', v_local_amt, ',', 0, ',', 0, ',', v_paid_local_amt, ',', mtd_collection_amt, ',', 
				'\'', p_report_id, '\'', ',\'', date_to, '\',', p_createdby, ',\'', now(), '\',', v_division_id, ')');
			
		END IF;
		-- IF insert_values IS NULL THEN
			-- INSERT INTO z_debtor_aging VALUES (NULL, 1000, 25, 1000, '2013-01-01', 0.00, 0.00, 0.00, 0.00, 0.00, mtd_collection_amt, '2013-01-01', 1, now());
		-- END IF;
	END LOOP the_loop;
	 
	IF by_bill_agent = 1 THEN
		CLOSE cur1;
	ELSE
		CLOSE cur2;
	END IF;
	
	END;
	
	IF insert_values <> '' THEN
	
		SET @query_as_string = CONCAT('INSERT INTO z_debtor_aging VALUES ', insert_values);

		PREPARE statement_1 FROM @query_as_string;
		EXECUTE statement_1;
		DEALLOCATE PREPARE statement_1;
	END IF;
	
END $$