Skip to content

Instantly share code, notes, and snippets.

@winex01

winex01/gist:b793c53333bc974ab2f3f6dfd20b9d60

Created September 26, 2017 09:45
Show Gist options
  • Save winex01/b793c53333bc974ab2f3f6dfd20b9d60 to your computer and use it in GitHub Desktop.
Save winex01/b793c53333bc974ab2f3f6dfd20b9d60 to your computer and use it in GitHub Desktop.
Download ZIP
dtr_issue(seaworld)
Raw
gistfile1.txt
-- Function: dtr.sp_process_log(integer)
-- DROP FUNCTION dtr.sp_process_log(integer);
CREATE OR REPLACE FUNCTION dtr.sp_process_log(var_log_id integer)
RETURNS integer AS
$BODY$
DECLARE
lkr dtr.log_keeper%ROWTYPE;
clog TIMESTAMP;
trans BOOLEAN;
var_is_legal BOOLEAN;
var_is_special BOOLEAN;
var_shift RECORD;
var_credit_late_tbl TIME;
vartmp TIME;
var_overtime INTEGER;
var_form_id INTEGER;
var_temp_app INTEGER;
ot dtr.employee_labor_hour%ROWTYPE;
BEGIN
-- Reset all the previous computation
DELETE FROM dtr.employee_labor_hour WHERE log_id = var_log_id;
UPDATE dtr.log_keeper SET (shift_code, credit_leave, credit_log, credit_late, credit_possible_ot, credit_undertime, log_in, log_out) = (NULL, NULL, NULL, NULL, NULL, NULL, '{}', '{}') WHERE log_id = var_log_id;
-- end reset computation
SELECT * INTO lkr FROM dtr.log_keeper WHERE log_id = var_log_id;
-- check if there is no log
PERFORM 1 FROM dtr.log_keeper_raw WHERE employee_id = lkr.employee_id AND credit_date = lkr.credit_date LIMIT 1;
IF NOT FOUND THEN
RETURN 1;
END IF;
-- end checking
-- Update logs
FOR clog, trans IN SELECT credit_log, transaction FROM dtr.log_keeper_raw WHERE employee_id = lkr.employee_id AND credit_date = lkr.credit_date GROUP BY credit_log, transaction ORDER BY credit_log LOOP
IF trans THEN
lkr.log_in = ARRAY_APPEND(lkr.log_in, clog);
ELSE
lkr.log_out = ARRAY_APPEND(lkr.log_out, clog);
END IF;
END LOOP;
-- end log update
UPDATE dtr.log_keeper SET log_in = lkr.log_in, log_out = lkr.log_out WHERE log_id = lkr.log_id;
-- check for log pairs
IF ARRAY_UPPER(lkr.log_in, 1) <> ARRAY_UPPER(lkr.log_out, 1) THEN
RETURN 2;
END IF;
-- end checking pairing of logs
-- check for invalid logs
FOR i IN 1..ARRAY_UPPER(lkr.log_in, 1) LOOP
IF lkr.log_out[i] - lkr.log_in[i] < '00:00:00'::TIME THEN
RETURN 4;
END IF;
END LOOP;
-- end checking invalid logs
-- check shift schedule
lkr.shift_code := dtr.sp_get_shift_code(lkr.employee_id, lkr.credit_date);
IF lkr.shift_code IS NULL THEN
RETURN 3;
END IF;
-- end check schedule
UPDATE dtr.log_keeper SET shift_code = lkr.shift_code WHERE log_id = lkr.log_id;
PERFORM 1 FROM dtr.employee_form WHERE employee_id = lkr.employee_id AND date_credit = lkr.credit_date AND form_code = 'OT' AND form_status = 'A' LIMIT 1;
IF FOUND THEN
-- compute overtime
PERFORM dtr.sp_compute_labor_hour(var_log_id);
ELSE
-- compute possible OT
lkr.credit_possible_ot := dtr.sp_compute_pot(var_log_id);
END IF;
-- compute for reg
-- the computation is shift credit - (total late + total undertime)
IF lkr.shift_code <> 'RD' AND lkr.shift_code <> 'NL' THEN
var_shift := dtr.sp_get_shift_detail(lkr.employee_id, lkr.credit_date);
lkr.credit_break := var_shift.credit_break;
lkr.credit_late := dtr.sp_get_late(lkr.log_id, lkr.log_in, TRUE) + dtr.sp_get_late(lkr.log_id, lkr.log_in, FALSE)::INTERVAL;
RAISE NOTICE '% %', dtr.sp_get_late(lkr.log_id, lkr.log_in, TRUE), dtr.sp_get_late(lkr.log_id, lkr.log_in, FALSE);
lkr.credit_undertime := dtr.sp_get_undertime(lkr.log_id, lkr.log_out);
lkr.credit_log := var_shift.credit_shift;
SELECT form_credit INTO lkr.credit_leave FROM dtr.employee_leave WHERE employee_id = lkr.employee_id AND lkr.credit_date BETWEEN date_start AND date_end AND form_status = 'A';
IF NOT FOUND THEN
lkr.credit_leave := 0;
ELSE
IF lkr.credit_leave = 1 OR lkr.credit_leave = 0 THEN
lkr.credit_log := '00:00:00'::TIME;
lkr.credit_undertime := '00:00:00'::TIME;
lkr.credit_late := '00:00:00'::TIME;
lkr.credit_break := '00:00:00'::TIME;
ELSE
vartmp := lkr.credit_log * lkr.credit_leave;
lkr.credit_log := lkr.credit_log - vartmp;
IF lkr.credit_late > lkr.credit_undertime THEN
IF lkr.credit_late > vartmp THEN
lkr.credit_late := lkr.credit_late - vartmp;
ELSE
lkr.credit_late := '00:00:00'::TIME;
END IF;
ELSE
IF lkr.credit_undertime > vartmp THEN
lkr.credit_undertime := lkr.credit_undertime - vartmp;
ELSE
lkr.credit_undertime := '00:00:00'::TIME;
END IF;
END IF;
END IF;
END IF;
IF var_shift.with_late THEN
SELECT late_value INTO var_credit_late_tbl FROM shared.late_interval WHERE lkr.credit_late BETWEEN late_from AND late_to;
IF NOT FOUND THEN
lkr.credit_late := '00:00:00'::TIME;
ELSE
lkr.credit_late := var_credit_late_tbl;
END IF;
END IF;
--holiday
var_is_legal := dtr.sp_is_holiday(lkr.employee_id, lkr.credit_date, TRUE);
var_is_special := dtr.sp_is_holiday(lkr.employee_id, lkr.credit_date, FALSE);
IF NOT var_is_legal AND NOT var_is_special THEN
IF var_shift.with_undertime THEN
SELECT undertime_value INTO var_credit_late_tbl FROM shared.undertime_interval WHERE lkr.credit_undertime BETWEEN undertime_from AND undertime_to;
IF NOT FOUND THEN
lkr.credit_undertime := '00:00:00'::TIME;
ELSE
lkr.credit_undertime := var_credit_late_tbl;
END IF;
END IF;
IF lkr.late_policy THEN
lkr.credit_late := lkr.credit_late * 1.5;
END IF;
IF lkr.undertime_policy THEN
lkr.credit_undertime := lkr.credit_undertime * 1.5;
END IF;
END IF; -- / IF NOT var_is_legal AND NOT var_is_special THEN
-----------------------------------------------------------------------------------
--compute shift_credit - (late + undertime)
IF lkr.credit_log > (lkr.credit_late + lkr.credit_undertime::INTERVAL) THEN
--RAISE NOTICE 'lkr.credit_log: %', lkr.credit_log;
lkr.credit_log := lkr.credit_log - (lkr.credit_late + lkr.credit_undertime::INTERVAL);
ELSE
lkr.credit_log := '00:00:00';
END IF;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Auto add overtime
IF var_is_special OR var_is_legal THEN
--Get the applicable prefixes.
IF lkr.shift_code <> 'RD' AND var_is_special THEN
var_overtime := 300;
ELSEIF lkr.shift_code <> 'RD' AND var_is_legal THEN
var_overtime := 200;
ELSEIF lkr.shift_code <> 'RD' AND NOT var_is_special AND NOT var_is_legal THEN
var_overtime := 100;
END IF;
SELECT form_id INTO var_form_id FROM dtr.employee_form WHERE form_code = 'OT' AND date_credit = lkr.credit_date AND employee_id = lkr.employee_id AND time_start BETWEEN var_shift.time_in1 AND var_shift.time_out1;
IF var_form_id IS NOT NULL THEN
IF var_shift.time_in2 IS NOT NULL THEN
UPDATE dtr.employee_form SET time_start = var_shift.time_in1, time_end = var_shift.time_out2, break_start = var_shift.break_start1, break_end = var_shift.break_start2 WHERE form_id = var_form_id;
ELSE
UPDATE dtr.employee_form SET time_start = var_shift.time_in1, time_end = var_shift.time_out1, break_start = var_shift.break_start1, break_end = var_shift.break_start2 WHERE form_id = var_form_id;
END IF;
ELSE
IF var_shift.time_in2 IS NOT NULL THEN
INSERT INTO dtr.employee_form(employee_id, form_code, date_granted, date_credit, time_start, time_end, break_start, break_end, form_status, reason) VALUES (lkr.employee_id , 'OT', NOW(), lkr.credit_date, var_shift.time_in1, var_shift.time_out2, var_shift.break_start1, var_shift.break_start2, 'A', 'auto add by system');
ELSE
INSERT INTO dtr.employee_form(employee_id, form_code, date_granted, date_credit, time_start, time_end, break_start, break_end, form_status, reason) VALUES (lkr.employee_id , 'OT', NOW(), lkr.credit_date, var_shift.time_in1, var_shift.time_out1, var_shift.break_start1, var_shift.break_start2, 'A', 'auto add by system');
END IF;
END IF;
PERFORM dtr.sp_compute_labor_hour(var_log_id);
PERFORM 1 FROM dtr.employee_labor_hour WHERE log_id = lkr.log_id AND labor_hour_id = var_overtime + 2;
IF FOUND THEN
SELECT count(*) INTO var_temp_app FROM dtr.employee_form WHERE form_code = 'OT' AND date_credit = lkr.credit_date AND employee_id = lkr.employee_id;
IF var_temp_app <= 1 THEN
UPDATE dtr.employee_labor_hour SET labor_hour_value = lkr.credit_log::TIME WHERE log_id = lkr.log_id AND labor_hour_id = var_overtime + 2;
END IF;
ELSE
INSERT INTO dtr.employee_labor_hour (log_id, labor_hour_id, labor_hour_value) VALUES (lkr.log_id, var_overtime + 2, lkr.credit_log::TIME);
END IF;
END IF;
/*-- Offset the paid overtime
FOR ot IN SELECT * FROM dtr.employee_labor_hour WHERE log_id = lkr.log_id AND labor_hour_id = ANY('{102,104,202,302}') LOOP
IF lkr.credit_late <> '00:00:00'::TIME AND ot.labor_hour_value <> '00:00:00'::TIME THEN
IF ot.labor_hour_value > lkr.credit_late THEN
ot.labor_hour_value := ot.labor_hour_value - lkr.credit_late::INTERVAL;
lkr.credit_late := '00:00:00'::TIME;
UPDATE dtr.employee_labor_hour SET labor_hour_value = ot.labor_hour_value WHERE log_id = lkr.log_id AND labor_hour_id = ot.labor_hour_id;
ELSE
lkr.credit_late := lkr.credit_late - ot.labor_hour_value::INTERVAL;
ot.labor_hour_value := '00:00:00'::TIME;
DELETE FROM dtr.employee_labor_hour WHERE log_id = lkr.log_id AND labor_hour_id = ot.labor_hour_id;
END IF;
END IF;
IF lkr.credit_undertime <> '00:00:00'::TIME AND ot.labor_hour_value <> '00:00:00'::TIME THEN
IF ot.labor_hour_value > lkr.credit_undertime THEN
ot.labor_hour_value := ot.labor_hour_value - lkr.credit_undertime::INTERVAL;
lkr.credit_undertime := '00:00:00'::TIME;
UPDATE dtr.employee_labor_hour SET labor_hour_value = ot.labor_hour_value WHERE log_id = lkr.log_id AND labor_hour_id = ot.labor_hour_id;
ELSE
lkr.credit_undertime := lkr.credit_undertime - ot.labor_hour_value::INTERVAL;
ot.labor_hour_value := '00:00:00'::TIME;
DELETE FROM dtr.employee_labor_hour WHERE log_id = lkr.log_id AND labor_hour_id = ot.labor_hour_id;
END IF;
END IF;
END LOOP;
*/
------------------------------------------------------------------------------------------------
RAISE NOTICE '-------------------------------------------------------------------------';
RAISE NOTICE 'CREDIT LOG %', lkr.credit_log;
RAISE NOTICE 'CREDIT LATE %', lkr.credit_late;
RAISE NOTICE 'CREDIT UNDERTIME %', lkr.credit_undertime;
--RAISE EXCEPTION 'END';
------------------------------------------------------------------------------------------------
IF var_is_special OR var_is_legal THEN
lkr.credit_log := '00:00:00';
lkr.credit_break := '00:00:00';
lkr.credit_undertime := '00:00:00'::TIME;
lkr.credit_late := '00:00:00'::TIME;
END IF;
END IF;
-- end reg computation
UPDATE dtr.log_keeper SET (credit_leave, credit_log, credit_break, credit_late, credit_possible_ot, credit_undertime) = (lkr.credit_leave, lkr.credit_log, lkr.credit_break, lkr.credit_late, lkr.credit_possible_ot, lkr.credit_undertime) WHERE log_id = lkr.log_id;
RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION dtr.sp_process_log(integer)
OWNER TO postgres;
COMMENT ON FUNCTION dtr.sp_process_log(integer) IS '
0 Success
1 No logs
2 Unpaired logs
3 No shift schedule
4 Invalid logs
';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment