aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_tr_sales_target_user_log
Parameters
Name
Type
Mode
Definition
DECLARE v_row public.sales_target; v_reference text; v_log_type text; v_action text; v_values text; v_user text := COALESCE( NULLIF(current_setting('sales_target.user_name', true), ''), NULLIF(current_setting('current.username', true), ''), fn_current_user()::text ); BEGIN IF TG_OP = 'UPDATE' AND (to_jsonb(OLD) - ARRAY['updated_at','updated_by']) = (to_jsonb(NEW) - ARRAY['updated_at','updated_by']) THEN RETURN NULL; END IF; v_row := CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END; -- 'YYYY-MM' and the raw segment_type stay in the text because the settings log is -- searched by free text; 'overall' would only ever read 'overall: Overall', so the -- prefix is dropped there (sp_get_user_logs matches with ilike, 'Overall' still hits) v_reference := to_char(v_row.target_month, 'YYYY-MM') || ' / ' || CASE WHEN v_row.segment_type = 'overall' THEN '' ELSE v_row.segment_type || ': ' END || COALESCE(fn_sales_target_segment_name(v_row.segment_type, v_row.segment_id), 'Unassigned'); IF TG_OP = 'UPDATE' THEN v_action := 'Update'; v_log_type := 'Sales Target Update'; v_values := fn_format_log('Occupancy %', OLD.occ_percent, NEW.occ_percent) || fn_format_log('Room Night', OLD.room_night, NEW.room_night) || fn_format_log('Revenue', OLD.revenue, NEW.revenue) || fn_format_log('ADR', OLD.adr, NEW.adr) || fn_format_log('RevPAR', OLD.rev_par, NEW.rev_par) || fn_format_log('Note', NULLIF(trim(OLD.note), ''), NULLIF(trim(NEW.note), ''), single_line => FALSE); -- nothing we track actually moved IF v_values = '' THEN RETURN NULL; END IF; ELSE v_action := CASE TG_OP WHEN 'INSERT' THEN 'Add' ELSE 'Delete' END; v_log_type := CASE TG_OP WHEN 'INSERT' THEN 'Sales Target Add' ELSE 'Sales Target Delete' END; v_values := concat_ws(E'\n', CASE WHEN v_row.occ_percent IS NOT NULL THEN '* Occupancy % = "' || v_row.occ_percent || '"' END, CASE WHEN v_row.room_night IS NOT NULL THEN '* Room Night = "' || v_row.room_night || '"' END, CASE WHEN v_row.revenue IS NOT NULL THEN '* Revenue = "' || v_row.revenue || '"' END, CASE WHEN v_row.adr IS NOT NULL THEN '* ADR = "' || v_row.adr || '"' END, CASE WHEN v_row.rev_par IS NOT NULL THEN '* RevPAR = "' || v_row.rev_par || '"' END, CASE WHEN NULLIF(trim(v_row.note), '') IS NOT NULL THEN '* Note = "' || trim(v_row.note) || '"' END); END IF; PERFORM public.sp_user_log('settings', v_log_type, v_action || ' sales target "' || v_reference || E'"\n' || rtrim(v_values, E'\n'), v_user, v_row.id); RETURN NULL; END