-- FUNCTION: issue40019_updatedata(character varying, character varying)

-- DROP FUNCTION issue40019_updatedata(character varying, character varying);

CREATE OR REPLACE FUNCTION issue40019_updatedata(p_datefrom character varying, p_dateto character varying)
  RETURNS character varying AS
$BODY$ DECLARE 

v_datefrom TIMESTAMP without time zone;
v_dateto TIMESTAMP without time zone;
v_amounttokeep NUMERIC:=0;

Cur_rec RECORD;

BEGIN

    IF p_datefrom IS NULL OR p_dateto IS NULL THEN
		RAISE EXCEPTION '%', 'Please provide dates in valid format';
    END IF;
    
	BEGIN
		select p_datefrom::timestamp into v_datefrom from dual;
        select p_dateto::timestamp + INTERVAL '1' DAY - INTERVAL '1' SECOND into v_dateto from dual;
	EXCEPTION 
		WHEN OTHERS THEN
			RAISE EXCEPTION '%', 'Please provide dates in valid format : '|| SQLERRM; --OBTG:-20000--
	END;
    
	IF v_datefrom <= TO_DATE('01-01-2017', 'dd-MM-yyyy') OR v_dateto > TO_DATE('01-01-2020', 'dd-MM-yyyy') OR v_datefrom > v_dateto THEN
		RAISE EXCEPTION '%', 'Please provide dates in valid range';
	END IF;

    -- RAISE NOTICE 'Updating PaymentMethod Cashup from : % to %', v_datefrom, v_dateto;
    
    FOR Cur_rec in (select oac.obpos_applications_id, oac.cashupdate, opc.obpos_paymentmethodcashup_id, opc.obpos_app_payment_id,
                    opc.totalcounted
                    from obpos_app_cashup oac
                    join obpos_paymentmethodcashup opc on opc.obpos_app_cashup_id = oac.obpos_app_cashup_id
                    where oac.isprocessed = 'Y'
                    and oac.cashupdate >= v_datefrom and oac.cashupdate <= v_dateto
                    order by oac.obpos_applications_id asc, oac.cashupdate asc, opc.name asc)
	LOOP
		select coalesce(startingcash, 0) into v_amounttokeep from obpos_paymentmethodcashup 
        	where obpos_app_payment_id = Cur_rec.obpos_app_payment_id
			and obpos_app_cashup_id = (select obpos_app_cashup_id from obpos_app_cashup 
                                       where obpos_applications_id = Cur_rec.obpos_applications_id 
                                       and cashupdate > Cur_rec.cashupdate order by cashupdate asc limit 1);
       
		CONTINUE WHEN Cur_rec.totalcounted IS not NULL and Cur_rec.totalcounted > 0;
        
		update obpos_paymentmethodcashup set 
        	amounttokeep = (CASE WHEN amounttokeep = 0 THEN v_amounttokeep ELSE amounttokeep END),
			totalcounted = startingcash+totalsales-totalreturns+totaldeposits-totaldrops
            where obpos_paymentmethodcashup_id = Cur_rec.obpos_paymentmethodcashup_id;
    END LOOP;
    
    RETURN 'SUCCESS';
    							
EXCEPTION
	WHEN OTHERS THEN
		RAISE EXCEPTION '%', SQLERRM; --OBTG:-20000--
END ; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION issue40019_updatedata(character varying, character varying)
  OWNER TO tad;


/*

-- Please provide date in the format MM-dd-yyyy

-- Call function
select issue40019_updatedata('01-01-2018', '12-31-2019');

-- Drop function
DROP FUNCTION issue40019_updatedata(character varying, character varying);


*/