--------------- SQL ---------------

COMMENT ON COLUMN "lpu"."bill"."bill_id"
IS 'Ид-р счета на оплату';


--------------- SQL ---------------

COMMENT ON COLUMN "lpu"."bill"."bill_date"
IS 'Дата счета на оплату';


--------------- SQL ---------------

COMMENT ON COLUMN "lpu"."bill"."bill_number"
IS 'Номер счета на оплату';


--------------- SQL ---------------

COMMENT ON COLUMN "lpu"."bill"."bill_amount"
IS 'Сумма к оплате';


--------------- SQL ---------------

COMMENT ON COLUMN "lpu"."bill"."payed"
IS 'Оплаченная сумма';

--------------- SQL ---------------

COMMENT ON COLUMN "lpu"."bill"."payment_number"
IS 'Номер платежного поручения';


--------------- SQL ---------------

COMMENT ON COLUMN "lpu"."bill"."payment_date"
IS 'Дата оплаты';


--------------- SQL ---------------

COMMENT ON TABLE "lpu"."bill"
IS 'Счета на оплату полученные от ЛПУ';



--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."get_last_act_num" (_yy varchar, _type integer) RETURNS varchar AS
$body$
DECLARE
    last_num VARCHAR(10);
BEGIN

	SELECT MAX(substring(a.reg_number from '....$')) INTO last_num
		FROM lpu.act a 
        	INNER JOIN lpu.contract c ON c.contract_id = a.contract_id
	WHERE с.contract_type = _type AND 
    	  CAST(to_char(a.reg_date, 'YY') AS INTEGER) =  CAST(_yy AS INTEGER);

    IF last_num IS NULL THEN
    	last_num := '0000';
    END IF;
    
	return last_num;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;




--------------- SQL ---------------

CREATE OR REPLACE FUNCTION "lpu"."get_last_act_num" (_yy varchar, _type integer) RETURNS varchar AS
$body$
DECLARE
    last_num VARCHAR(10);
BEGIN

	SELECT MAX(substring(a.reg_number from '....$')) INTO last_num
		FROM lpu.act a 
        	INNER JOIN lpu.contract c ON c.contract_id = a.contract_id
	WHERE c.contract_type = _type AND 
    	  CAST(to_char(a.reg_date, 'YY') AS INTEGER) =  CAST(_yy AS INTEGER);

    IF last_num IS NULL THEN
    	last_num := '0000';
    END IF;
    
	return last_num;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;