SP inv to invoice

USE `unisol_dev2`$$

DROP PROCEDURE IF EXISTS `sp_inv_to_invoice`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_inv_to_invoice`(
	param_grn_id INT(11),
	param_user_id INT(11)
)
BEGIN
	-- DECLARE var_year_id INT;
	DECLARE var_last_voucher_head_id, v_voucher_head_id, var_year_id, var_trn_last_no, var_trn_increment INT;
	-- DECLARE var_trn_last_no INT;
	-- DECLARE var_trn_increment INT;
	DECLARE var_trn_number VARCHAR(12);
	DECLARE v_netamt, v_taxamt DECIMAL(20,5);
	DECLARE v_suppgorup, v_subacc, v_acccode VARCHAR(50);

	SELECT last_number, increment INTO var_trn_last_no, var_trn_increment FROM acc_trn_no_setup WHERE `code` = 'AP--';
	SET var_trn_last_no = var_trn_last_no + var_trn_increment;
	SET var_trn_number = CONCAT('AP--', LPAD(var_trn_last_no, 8, 0));
	UPDATE acc_trn_no_setup SET last_number = var_trn_last_no WHERE `code` = 'AP--';

	SELECT id INTO var_year_id FROM `year` WHERE title = YEAR(CURRENT_TIMESTAMP);

	INSERT INTO acc_voucher_head(`date`, year_id, `period`, `status`, created_by, created_at, `voucher_number`, reference, `type`)
		SELECT CURRENT_TIMESTAMP, var_year_id, MONTH(CURRENT_TIMESTAMP), 'open', param_user_id, CURRENT_TIMESTAMP,
		var_trn_number, CONCAT('Invoiced for GRN number ',var_trn_number), 'account-payable'
		FROM inv_grn_head igh
		WHERE igh.id = param_grn_id AND `status` = 'Confirmed'
		;


	SET var_last_voucher_head_id = LAST_INSERT_ID();
	IF var_last_voucher_head_id > 0 THEN
		-- Debit entry
		INSERT INTO acc_voucher_detail(acc_voucher_head_id, acc_chart_of_accounts_id, prime_amount,
			base_amount, created_by, created_at, inv_supplier_id)
			SELECT var_last_voucher_head_id, e.id, CONVERT(b.receive_quantity, DECIMAL) * CONVERT(b.cost_price, DECIMAL),
			CONVERT(b.row_amount, DECIMAL), param_user_id, CURRENT_TIMESTAMP, a.inv_supplier_id
			FROM inv_grn_head a
			JOIN inv_grn_detail b ON b.inv_grn_head_id  = a.id AND a.status = 'Confirmed'
			JOIN `inv_supplier` c ON c.id = a.inv_supplier_id
			JOIN acc_codesparam d ON CONVERT(d.type USING utf8) = CONVERT('Supplier Group' USING utf8)
				AND CONVERT(d.code USING utf8) = CONVERT(c.group_code USING utf8)
			JOIN acc_chart_of_accounts e ON CONVERT(e.account_code USING utf8) = CONVERT(d.account_code USING utf8)
			WHERE a.id = param_grn_id
			-- group by b.inv_grn_head_id
			;

		-- Credit entry
		/*INSERT INTO acc_voucher_detail(acc_voucher_head_id, acc_chart_of_accounts_id, prime_amount, base_amount,
			created_by, created_at, inv_supplier_id)
			SELECT var_last_voucher_head_id, e.id, 0 - SUM(CONVERT(b.receive_quantity,DECIMAL) * CONVERT(b.cost_price,DECIMAL)),
			0 - SUM(CONVERT(b.row_amount,DECIMAL)), param_user_id, CURRENT_TIMESTAMP, a.inv_supplier_id
			FROM inv_grn_head a
			JOIN inv_grn_detail b ON b.inv_grn_head_id  = a.id AND a.status = 'Confirmed'
			JOIN `inv_supplier` c ON c.id = a.inv_supplier_id
			JOIN acc_codesparam d ON CONVERT(d.type USING utf8) = CONVERT('Supplier Group' USING utf8)
				AND CONVERT(d.code USING utf8) = CONVERT(c.group_code USING utf8)
			JOIN acc_chart_of_accounts e ON CONVERT(e.account_code USING utf8) = CONVERT(d.account_code USING utf8)
			WHERE a.id = param_grn_id
			-- GROUP BY b.inv_grn_head_id
			;
		*/
		/*set v_voucher_head_id = 123;
		set v_netamt = 1234 ;
		set v_taxamt = 20.25;
		set v_suppgorup = 'eee';
		set v_subacc = 'rrr';
		set v_acccode = 'ttt';*/

		SELECT IFNULL(a.tax_amount,0) AS v_taxamt, a.amount, b.group_code, a.inv_supplier_id, e.id
		INTO v_taxamt, v_netamt, v_suppgorup, v_subacc, v_acccode
		FROM inv_grn_head a
		INNER JOIN inv_supplier b ON a.inv_supplier_id = b.id
		-- LEFT JOIN acc_codesparam c ON CONVERT(c.type USING utf8) = CONVERT('Supplier Group' USING utf8)
			-- AND CONVERT(c.code  USING utf8) = CONVERT(b.group_code USING utf8)
		JOIN acc_codesparam d ON CONVERT(d.type USING utf8) = CONVERT('Supplier Group' USING utf8)
				AND CONVERT(d.code USING utf8) = CONVERT(b.group_code USING utf8)
		JOIN acc_chart_of_accounts e ON CONVERT(e.account_code USING utf8) = CONVERT(d.account_code USING utf8)
		WHERE a.id = param_grn_id;

		INSERT INTO acc_voucher_detail(acc_voucher_head_id, inv_supplier_id, prime_amount, base_amount,
			note, created_at, created_by, acc_chart_of_accounts_id)
		VALUES(var_last_voucher_head_id, v_subacc, 0-v_netamt, 0-(v_netamt),
			'Receiveable Credit automatic', CURRENT_TIMESTAMP, param_user_id, v_acccode);

		-- IF v_taxamt<>0 THEN -- 2 If tax amount is not zero then credit account will enter.
		--	INSERT INTO am_voucherdetail(am_vouchernumber,am_accountcode,am_subacccode,am_currency,am_exchagerate,am_primeamt,am_baseamt,am_branch,am_note,inserttime,insertuser)
		--	VALUES(v_voucher,v_acctax,'',v_currency,v_exchange,v_taxamt,(v_exchange*v_taxamt),v_branch,'Inventory Credit automatic',CURRENT_TIMESTAMP,p_User);
		-- END IF; -- 2

		UPDATE inv_grn_head SET `status` = 'Invoiced' WHERE id = param_grn_id  AND  `status` = 'Confirmed';

		CALL sp_acc_voucherpost(var_last_voucher_head_id, param_user_id);
	END IF;
END$$

DELIMITER ;