CREATE OR REPLACE FUNCTION mt_f_avl_oee_period_e(i_station character varying, i_date character varying, i_start_time character varying, i_end_time character varying)
RETURNS integer AS
$BODY$
DECLARE
-- v_start_hour character varying;
-- v_end_hour character varying;
-- v_start_time character varying;
-- v_end_time character varying;
v_start_datetime timestamp;
v_end_datetime timestamp;
v_test9_count int;
v_test9_success int;
v_test30_count int;
v_test30_success int;
v_runningtime double precision;
v_availablerate double precision;
BEGIN
-- hour = even, minute > 30
-- exists
--
-- SELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '2 hours' ) into v_start_hour;
-- SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;
-- select v_start_hour || ':30' into v_start_time;
-- select v_end_hour || ':30' into v_end_time;
select to_timestamp(i_date|| ' ' || i_start_time , 'YYYY-MM-DD HH24:MI') into v_start_datetime;
select to_timestamp(i_date|| ' ' || i_end_time , 'YYYY-MM-DD HH24:MI') into v_end_datetime;
---------------------------------
-- 9M
select count(1) into v_test9_count from mt_t_serialno_test_data where testcell = i_station and testtype = '9M'
and createdon >= v_start_datetime
and createdon < v_end_datetime ;
-- 9M success
select count(1)into v_test9_success from mt_t_serialno_test t,mt_t_serialno_test_data d
where d.serialno=t.serialno
and d.testcell = i_station
and t.testtype='9M'
and t.teststatus='P'
and d.createdon >= v_start_datetime
and d.createdon < v_end_datetime ;
-- 30M
select count(1) into v_test30_count from mt_t_serialno_test_data
where testcell = i_station and testtype = '30M'
and createdon >= v_start_datetime
and createdon < v_end_datetime ;
-- 30M success
select count(1)into v_test30_success from mt_t_serialno_test t,mt_t_serialno_test_data d
where d.serialno=t.serialno
and d.testcell = i_station
and t.testtype='30M'
and t.teststatus='P'
and d.createdon >= v_start_datetime
and d.createdon < v_end_datetime ;
-- running time
select coalesce( sum(duration) , 0) into v_runningtime from engine_test where station = 'GCIC_' || i_station /* and type = 1 */
and state_start >= v_start_datetime
and state_stop < v_end_datetime ;
-- available rate
select v_runningtime/7200 into v_availablerate;
insert into avl_oee_period
(station, start_time, end_time, test9_count, test9_success, test30_count,test30_success,runningtime,availablerate,date_,
active, createdon, createdby, rowversionstamp)
values
(i_station, to_char( v_start_datetime, 'HH24:MI'), to_char( v_end_datetime, 'HH24:MI'), v_test9_count, v_test9_success, v_test30_count, v_test30_success, v_runningtime/60, v_availablerate, now(),
1, now(), 'mt', 1);
---------------------------------
RETURN 1;
EXCEPTION
WHEN others THEN
RAISE;
RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION mt_f_avl_oee_period_e(character varying, character varying, character varying, character varying)
OWNER TO postgres;
************************************************************************************************************************************************************
-- Function: mt_f_avl_oee_period_midnight(character varying, character varying, character varying, character varying)
-- DROP FUNCTION mt_f_avl_oee_period_midnight(character varying, character varying, character varying, character varying);
CREATE OR REPLACE FUNCTION mt_f_avl_oee_period_midnight(i_station character varying, i_date character varying, i_start_time character varying, i_end_time character varying)
RETURNS integer AS
$BODY$
DECLARE
-- v_start_hour character varying;
-- v_end_hour character varying;
-- v_start_time character varying;
-- v_end_time character varying;
v_start_datetime timestamp;
v_end_datetime timestamp;
v_test9_count int;
v_test9_success int;
v_test30_count int;
v_test30_success int;
v_runningtime double precision;
v_runtime double precision;
v_endtime_interval double precision;
v_starttime_interval double precision;
v_availablerate double precision;
BEGIN
-- hour = even, minute > 30
-- exists
--
-- SELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '2 hours' ) into v_start_hour;
-- SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;
-- select v_start_hour || ':30' into v_start_time;
-- select v_end_hour || ':30' into v_end_time;
-- select to_timestamp((timestamp i_date ) , 'YYYY-MM-DD HH24:MI') into v_start_datetime;
-- select to_timestamp(to_char((date i_date - interval '1 day' ), 'YYYY-MM-DD')|| ' ' || i_start_time , 'YYYY-MM-DD HH24:MI') into v_start_datetime;
select to_timestamp(i_date || ' ' || i_end_time , 'YYYY-MM-DD HH24:MI') into v_end_datetime;
select v_end_datetime - interval '450 minutes' into v_start_datetime;
---------------------------------
-- 9M
select count(1) into v_test9_count from mt_t_serialno_test_data where testcell = i_station and testtype = '9M'
and createdon >= v_start_datetime
and createdon < v_end_datetime ;
-- 9M success
select count(1)into v_test9_success from mt_t_serialno_test t,mt_t_serialno_test_data d
where d.serialno=t.serialno
and d.testcell = i_station
and t.testtype='9M'
and t.teststatus='P'
and d.createdon >= v_start_datetime
and d.createdon < v_end_datetime ;
-- 30M
select count(1) into v_test30_count from mt_t_serialno_test_data
where testcell = i_station and testtype = '30M'
and createdon >= v_start_datetime
and createdon < v_end_datetime ;
-- 30M success
select count(1)into v_test30_success from mt_t_serialno_test t,mt_t_serialno_test_data d
where d.serialno=t.serialno
and d.testcell = i_station
and t.testtype='30M'
and t.teststatus='P'
and d.createdon >= v_start_datetime
and d.createdon < v_end_datetime ;
-- running time
--select date_part('epoch', timestamp '2013-11-28 11:00'- timestamp '2013-11-28 01:30' );
select coalesce( sum(duration) , 0) into v_runningtime from engine_test where station = 'GCIC_' || i_station /* and type = 1 */
and state_start >= v_start_datetime
and state_stop < v_end_datetime ;
--select state_start - v_start_datetime into v_fronttime;
--select v_end_datetime - state_stop into v_endtime;
-- v_runtime := v_runningtime + v_fronttime + v_endtime
--starttime_interval
select state_stop - v_start_datetime into v_starttime_interval from engine_test
where state_start <= v_start_datetime and state_stop > v_start_datetime
and station = 'GCIC_' || i_station and type = 1 ;
--endtime_interval
select v_end_datetime - state_start into v_endtime_interval from engine_test
where state_start <= v_end_datetime and state_stop > v_end_datetime
and type = 1 and station = 'GCIC_' || i_station
union
select v_end_datetime - createdon from engine_test
where createdon < v_end_datetime and next_state =1
and id = (select max(id) from engine_test where station = 'GCIC_'|| i_station); /*the last item in engine_test table for the certain station */
v_runtime := v_start_datetime + v_runningtime + v_endtime_interval;
-- available rate
select v_runtime/72 into v_availablerate;
insert into avl_oee_period
(station, start_time, end_time, test9_count, test9_success, test30_count,test30_success,runningtime,availablerate,date_,
active, createdon, createdby, rowversionstamp)
values
(i_station, to_char( v_start_datetime, 'HH24:MI'), to_char( v_end_datetime, 'HH24:MI'), v_test9_count, v_test9_success, v_test30_count, v_test30_success, v_runningtime/60, v_availablerate, now(),
1, now(), 'mt', 1);
---------------------------------
RETURN 1;
EXCEPTION
WHEN others THEN
RAISE;
RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION mt_f_avl_oee_period_midnight(character varying, character varying, character varying, character varying)
OWNER TO postgres;