aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
charge_amount	t_money	IN
refund_payment_id	integer	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)

Definition

declare
	_sqlstate text;
    _detail   text;
    _hint     text;
    _context  text;
    _msg_text  text;
   
     result_post_transaction json;
    _reference_noshow text;
    _reference_refund text;
    _reference text;
    _remark_noshow text;
    _remark_refund text;
    _return_code_post_transaction int4;
    _return_close_folio json;
    _department_id int;
    _folioS record;
    _trans_id int;
	_dept_code text;
    result_make_payment json;
    _result_make_payment int4;
    _refund_amount int4;

    v_noshow_charge_amount t_money;
    v_refund t_money;
    v_user_name text;
   
	begin
    /* 

     if user_name IS NULL THEN 
        user_name := fn_current_user();
        shift     := coalesce(shift, fn_current_shift());
     end if;


     วน loop update deposit
     update deposit: status R,H -> N, noshow_date, noshow_tran_id,   modify_user, modify_time
        
     อันนี้เป็นของ sp เรื่อง refund ที่ต้องไป update => refund_amount, refund_tran_id, modify_user, modify_time
    
     where ด้วย deposit_tran_id
     
     table transaction กัย deposite  join ด้วย  deposit_tran_id
		
    */
		-- =========================
		-- Create by T.Supol 2025-04-23
		-- =========================

		v_user_name :=  coalesce(user_name,fn_current_user());

     	select * into _department_id from fn_sys_param('DEPOSIT','NOSHOW_DEPT_ID', null::INT);
	
	    select code into _dept_code 
	    from department 
	    where id = _department_id  
	    and post_type = 'P'
	    ;
		
		_reference_noshow := format('deposit no-show deposite no. : %L', _dept_code);
		_remark_noshow := _dept_code; -- dept = department
	
	
		_reference_refund := format('deposit refund payment id. : %L', $3);
	    _remark_refund := $3;
        if refund_payment_id is null then 
      		select * into refund_payment_id from fn_sys_param('DEPOSIT','REFUND_PAYMENT_ID', null::INT);
		end if;
	
	    v_noshow_charge_amount := $2;
	
	    for _folioS in (
			select * 
		    from folio_items fi 
		    where fi.folio_id = $1
		    and fi.balance <> 0 
	    ) loop
		    
	        select string_agg(id::text, ', ') into _reference
		    from deposit d 
		    where d.deposit_tran_id in (select t.id from transactions t where t.folio_item = _folioS.item_id) 
			;
		    
		    
		    -- =========================
		    -- case no-show < ถ้ามีค่ามากกว่า 0 ทำ no-show charge >
			-- =========================
		    if v_noshow_charge_amount > 0 then 
		    
--		          RAISE NOTICE 'folio_id:%', $1;
--		          RAISE NOTICE 'folio_seq:%',_folioS.folio_seq;
--		          RAISE NOTICE 'dept_code:%',_department_id;
--		          RAISE NOTICE 'amount:%',v_noshow_charge_amount;
--		          RAISE NOTICE 'reference:%',_reference;
--		          RAISE NOTICE 'remark:%',_remark_noshow;
--		          RAISE NOTICE 'user_name:%',v_user_name;
   
			    
		    		result_post_transaction := sp_post_transaction(
					   		folio_id := $1,
					   		dept_id := _department_id,
					   		amount := v_noshow_charge_amount,
					   		qty := 1,
					   		folio_seq := _folioS.folio_seq,
					   		shift := null,
					   		user_name := v_user_name,
					   		reference := _reference,
					   		remark := _remark_noshow,
					   		description := null,
					   		charge_date := null,
					   		source_module := null,
					   		allow_redirect := null,
					   		post_flag => 'D'
			    	);
			    
			      
			  --     raise notice 'result_post_transaction : %L', result_post_transaction;
--			    
			       _return_code_post_transaction := (result_post_transaction ->> 'result_code')::int4;
	    			      
			     --   perform sp_raise_error_if(_return_code_post_transaction = 4  , result_post_transaction ->> 'result_msg');
			        perform sp_raise_error_if(_result_make_payment = 4  , result_make_payment ->> 'result_msg');
			      
			      
		            if _return_code_post_transaction = 0 then 
		        
			        	for _trans_id in (
			        					select t.id 
			        					from transactions t  
			        					where t.folio_item = _folioS.item_id
			        					)
			        	loop
				        	update deposit t
				            set 
				                status = case when t.status in ('R', 'H') then 'N' else t.status  end,
				                noshow_date = fn_system_date(), 
				                noshow_tran_id = (result_post_transaction ->> 'id')::int, 
				               -- noshow_user = user_name, 
				                noshow_user = v_user_name,      
				                noshow_time = current_timestamp  
				            where 
				         		t.deposit_tran_id = _trans_id
				            ;
			        	end loop;
		        
		            end if;
		    end if;
		   		    
		    v_refund := abs(_folioS.balance) - coalesce($2, 0);
		   		    
		    
		    -- =========================
		    -- case refund < ถ้า refund มีค่ามากกว่า 0 ให้ทำ refund >
			-- =========================
		    if v_refund > 0 then
		    
			    result_make_payment := sp_make_payment(
				   		folio_id := $1,
				   		folio_seq := _folioS.folio_seq,
				   		payment_id := refund_payment_id,
				   		amount := v_refund,
				   		reference := _reference,
				   		remark := _remark_refund,
				   		post_flag := 'D',
				   		user_name := v_user_name
		        );
		       
		        _result_make_payment := (result_make_payment ->> 'result_code')::int4;
	    
		      --  perform sp_raise_error_if(_result_make_payment = 4  , result_make_payment ->> 'result_msg');
			     perform sp_raise_error_if(_result_make_payment = 4  , result_make_payment ->> 'result_msg');
			       
		       
		        if _result_make_payment = 0 then 
		        
			        	for _trans_id in (
			        					select t.id 
			        					from transactions t  
			        					where t.folio_item = _folioS.item_id
			        					)
			        	loop
				        	update deposit t
				            set 
				              refund_amount = v_refund,
				              refund_tran_id = $3
				            where 
				         		t.deposit_tran_id = _trans_id
				            ;
			        	end loop;
		        
		         end if;
		    
		    end if;

	    end loop;
	   

	   _return_close_folio := sp_close_folio(folio_id := $1); 
     
 
        return _return_close_folio;
	  
	 -- return fn_result_success();
    


EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _msg_text = MESSAGE_TEXT,
        _sqlstate = RETURNED_SQLSTATE,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT; 
    RETURN fn_handle_error(
   		_sqlstate, 
   		_msg_text, 
   		_detail, 
   		_hint, 
   		_context, 
   		format('sp_deposit_noshow_charge(%L, %L, %L, %L)', $1, $2, $3, $4)
   	);



	END;