CREATE DEFINER=`root`@`%` PROCEDURE `usp_OEE_Get
MachineOEEAndStatusSingleClient`(IN `p_
machine_id` bigint,IN `p_
start_time` datetime,IN `p_end_time` datetime,IN `p_isHistory` CHAR(1),OUT `error` varchar(512))
label:BEGIN
DECLARE v_now datetime;
DECLARE v_
start_fh,v_end_fh datetime;
DECLARE v_now_date date;
DECLARE v_now_time time;
DECLARE v_
start_shift,v_end_shift datetime;
DECLARE V_QUERY_
START,V_QUERY_END datetime;
DECLARE V_MAX_TIME DATETIME;
DECLARE V_MIN_
START_TIME DATETIME;
DECLARE V_MIN_END_TIME DATETIME;
DECLARE V_
MACHINE_NO varchar(60);
DECLARE V_
MACHINE_NAME varchar(250);
DECLARE V_NOW_DAY DATETIME;
DECLARE Time_Frame VARCHAR(10);
SET v_now=NOW();
SET V_NOW_DAY=DATE_FORMAT(v_now,'%Y-%m-%d 08:00:00');
/*IF EXISTS(select * from usp_oee_get
machineoeeandstatussingleclient AS A WHERE A.
MACHINE_ID=p_
machine_id and A.V_QUERY_
START=p_
start_time and A.V_QUERY_END=p_end_time) then
SELECT * from usp_oee_get
machineoeeandstatussingleclient AS A WHERE A.
MACHINE_ID=p_
machine_id and A.V_QUERY_
START=p_
start_time and A.V_QUERY_END=p_end_time;
leave label;
end if;*/
SET v_now_date=DATE_FORMAT(v_now,'%Y-%m-%d');
SET v_now_time=DATE_FORMAT(v_now,'%H:%i:%s');
IF(p_isHistory = 'N') THEN
SET v_now=NOW();
SET v_now_date=DATE_FORMAT(v_now,'%Y-%m-%d');
SET v_now_time=DATE_FORMAT(v_now,'%H:00:00');
select
MACHINE_NO,
MACHINE_NAME,CASE WHEN c.BEGIN_TIME>c.END_TIME AND v_now_time<c.END_TIME THEN CONCAT(DATE_ADD(v_now_date,INTERVAL -1 DAY),' ',c.BEGIN_TIME) ELSE CONCAT(v_now_date,' ',c.BEGIN_TIME) END,
CASE WHEN c.BEGIN_TIME>=c.END_TIME AND v_now_time>c.BEGIN_TIME THEN CONCAT(DATE_ADD(v_now_date,INTERVAL 1 DAY),' ',c.END_TIME) ELSE CONCAT(v_now_date,' ',c.END_TIME) END
into V_
MACHINE_NO,V_
MACHINE_NAME,v_
start_shift,v_end_shift
from BAS_LINE_INFO a
inner join FM_
MACHINE_INFO d on a.LINE_ID=d.LINE_ID
inner join BAS_SHIFT_INFO b on a.SHIFT_ID=b.SHIFT_ID
inner join BAS_SHIFT_PER
IOD c on b.SHIFT_ID=c.SHIFT_ID
where d.
MACHINE_ID=p_
machine_id and (v_now_time>=c.BEGIN_TIME AND v_now_time<c.END_TIME
OR (c.BEGIN_TIME>c.END_TIME AND (v_now_time>=c.BEGIN_TIME OR v_now_time<c.END_TIME))
OR c.BEGIN_TIME=c.END_TIME);
SET V_QUERY_
START=v_
start_shift;
SET V_QUERY_END=v_now;
ELSE
SELECT
MACHINE_NO,
MACHINE_NAME,p_
start_time,p_end_time into V_
MACHINE_NO,V_
MACHINE_NAME,V_QUERY_
START,V_QUERY_END
FROM FM_
MACHINE_INFO WHERE
MACHINE_ID=p_
machine_id;
IF(p_end_time>NOW()) THEN
SET v_now=NOW();
SET V_QUERY_END=v_now;
ELSE
SET v_now=p_end_time;
END IF;
SET v_now_date=DATE_FORMAT(v_now,'%Y-%m-%d');
SET v_now_time=DATE_FORMAT(v_now,'%H:%i:%s');
END IF;
DROP TEMPORARY TABLE IF EXISTS TMP_PER
IOD;
CREATE TEMPORARY TABLE TMP_PER
IOD
(
MACHINE_ID BIGINT,
START_TIME DATETIME,
END_TIME DATETIME
);
INSERT INTO TMP_PER
IOD(
MACHINE_ID,
START_TIME,END_TIME) VALUES (p_
machine_id,V_QUERY_
START,V_QUERY_END);
DROP TEMPORARY TABLE IF EXISTS TMP_ALARM_RECORD_SINGLE;
CREATE TEMPORARY TABLE TMP_ALARM_RECORD_SINGLE
(
MACHINE_ID BIGINT,
START_TIME DATETIME,
END_TIME DATETIME,
ALARM_ID VARCHAR(30),
IS_STAND CHAR(1)
);
-- SELECT * FROM TMP_PER
IOD;
BEGIN
DECLARE V_
START_TIME DATETIME;
DECLARE V_END_TIME DATETIME;
DECLARE V_RECORD_
STARTTIME DATETIME;
DECLARE V_RECORD_ALARM_ID VARCHAR(20);
DECLARE S INT DEFAULT 0;
-- DECLARE MC CURSOR FOR SELECT
START_TIME,END_TIME FROM TMP_PER
IOD;
DECLARE MC CURSOR FOR SELECT min(
START_TIME),max(END_TIME) FROM TMP_PER
IOD;
DECLARE EXIT HANDLER FOR SQLSTATE '02000' SET S = 1 ;
OPEN MC;
REPEAT
FETCH MC INTO V_
START_TIME,V_END_TIME;
ALARM:BEGIN
insert into TMP_ALARM_RECORD_SINGLE(
MACHINE_ID,ALARM_ID,
START_TIME,END_TIME,IS_STAND)
select a.
MACHINE_ID,a.ALARM_ID,CASE WHEN V_
START_TIME<A.
START_TIME THEN A.
START_TIME ELSE V_
START_TIME END,
CASE WHEN V_END_TIME>A.END_TIME THEN A.END_TIME ELSE V_END_TIME END,case when c.TYPE_NO='stand' then 'Y' else 'N' end IS_STAND
from FM_ALARM_RECORD_SINGLE as a
inner join FM_ALARM_INFO b on a.alarm_id=b.alarm_id
inner join FM_ALARM_TYPE c on b.TYPE_ID=c.TYPE_ID
where a.
MACHINE_ID=p_
machine_id and END_TIME>V_
START_TIME and
START_TIME<V_END_TIME;
-- SELECT * FROM TMP_ALARM_RECORD_SINGLE;
IF NOT EXISTS(SELECT ALARM_ID FROM TMP_ALARM_RECORD_SINGLE WHERE END_TIME>=V_END_TIME) THEN
DROP TEMPORARY TABLE IF EXISTS TMP_ALARM;
CREATE TEMPORARY TABLE TMP_ALARM
(
MACHINE_ID BIGINT,
STARTTIME DATETIME,
ALARM_ID VARCHAR(30),
IS_STAND CHAR(1)
);
INSERT INTO TMP_ALARM
(SELECT C.
MACHINE_ID,A.
START_TIME,A.ALARM_ID,'Y'
FROM FM_ALARM_RECORD A inner join FM_STAT
ION_DEVICE B on A.DEVICE_ID=B.ID inner join FM_
MACHINE_INFO C on B.
MACHINE_ID=C.
MACHINE_ID
INNER JOIN FM_ALARM_INFO D on A.alarm_id=D.alarm_id inner join FM_ALARM_TYPE E on D.TYPE_ID=E.TYPE_ID
where B.
MACHINE_ID=p_
machine_id AND A.END_TIME IS NULL AND
START_TIME<V_END_TIME AND E.TYPE_NO='stand' ORDER BY A.
START_TIME LIMIT 1)
UN
ION
(SELECT C.
MACHINE_ID,A.
START_TIME,A.ALARM_ID,'N'
FROM FM_ALARM_RECORD A inner join FM_STAT
ION_DEVICE B on A.DEVICE_ID=B.ID inner join FM_
MACHINE_INFO C on B.
MACHINE_ID=C.
MACHINE_ID
INNER JOIN FM_ALARM_INFO D on A.alarm_id=D.alarm_id inner join FM_ALARM_TYPE E on D.TYPE_ID=E.TYPE_ID
where B.
MACHINE_ID=p_
machine_id AND A.END_TIME IS NULL AND
START_TIME<V_END_TIME AND E.TYPE_NO='stop' ORDER BY A.
START_TIME LIMIT 1);
-- SELECT * FROM TMP_ALARM;
IF EXISTS (SELECT ALARM_ID FROM TMP_ALARM) THEN
DROP TEMPORARY TABLE IF EXISTS TMP_ALARM_EXTEND;
CREATE TEMPORARY TABLE TMP_ALARM_EXTEND
SELECT * FROM TMP_ALARM_RECORD_SINGLE;
IF EXISTS (SELECT ALARM_ID FROM TMP_ALARM WHERE IS_STAND='Y') THEN
SELECT
STARTTIME,ALARM_ID INTO V_RECORD_
STARTTIME,V_RECORD_ALARM_ID FROM TMP_ALARM WHERE IS_STAND='Y';
DELETE FROM TMP_ALARM_EXTEND WHERE END_TIME>V_RECORD_
STARTTIME;
INSERT INTO TMP_ALARM_EXTEND(
MACHINE_ID,
START_TIME,END_TIME,ALARM_ID,IS_STAND)
VALUES (p_
machine_id,V_RECORD_
STARTTIME,V_END_TIME,V_RECORD_ALARM_ID,'Y');
INSERT INTO TMP_ALARM_EXTEND(
MACHINE_ID,
START_TIME,END_TIME,ALARM_ID,IS_STAND)
SELECT p_
machine_id,
START_TIME,V_RECORD_
STARTTIME,ALARM_ID,IS_STAND
FROM TMP_ALARM_RECORD_SINGLE WHERE
START_TIME<V_RECORD_
STARTTIME AND END_TIME>V_RECORD_
STARTTIME;
ELSEIF EXISTS (SELECT ALARM_ID FROM TMP_ALARM WHERE IS_STAND='N') THEN
SELECT
STARTTIME,ALARM_ID INTO V_RECORD_
STARTTIME,V_RECORD_ALARM_ID FROM TMP_ALARM WHERE IS_STAND='N';
IF NOT EXISTS (SELECT * FROM TMP_ALARM_RECORD_SINGLE WHERE END_TIME>V_RECORD_
STARTTIME) THEN
INSERT INTO TMP_ALARM_EXTEND(
MACHINE_ID,
START_TIME,END_TIME,ALARM_ID,IS_STAND)
VALUES (p_
machine_id,V_RECORD_
STARTTIME,V_END_TIME,V_RECORD_ALARM_ID,'N');
ELSE
SELECT MAX(END_TIME) INTO V_MAX_TIME FROM TMP_ALARM_RECORD_SINGLE WHERE END_TIME>V_RECORD_
STARTTIME;
INSERT INTO TMP_ALARM_EXTEND(
MACHINE_ID,
START_TIME,END_TIME,ALARM_ID,IS_STAND)
VALUES (p_
machine_id,V_MAX_TIME,V_END_TIME,V_RECORD_ALARM_ID,'N');
END IF;
END IF;
DELETE FROM TMP_ALARM_RECORD_SINGLE;
INSERT INTO TMP_ALARM_RECORD_SINGLE
SELECT * FROM TMP_ALARM_EXTEND;
END IF;
END IF;
END;
UNTIL S END REPEAT;
CLOSE MC;
END;
-- SELECT * FROM TMP_ALARM_RECORD_SINGLE;
BEGIN
DECLARE yield FLOAT DEFAULT 0;
DECLARE PF FLOAT;
DECLARE AV FLOAT;
DECLARE OEE FLOAT;
DECLARE Output INT;
DECLARE Input INT;
DECLARE Theory_Output INT;
DECLARE Fling INT;
DECLARE OKRate VARCHAR(10);
DECLARE TossingRate VARCHAR(10);
DECLARE Run_Durat
ion FLOAT;
DECLARE Theory_Durat
ion FLOAT;
DECLARE T_CT INT DEFAULT 0;
DECLARE Status VARCHAR(10);
DECLARE Stand_Durat
ion FLOAT;
DECLARE Stop_Durat
ion FLOAT;
#计算Input
select IFNULL(SUM(a.VALUE),0) into Input
from fm_data_record a
inner join FM_DATA_INFO c ON a.DATA_ID=c.DATA_ID
where a.
MACHINE_ID=p_
machine_id and c.TYPE in (1,2) and a.DATETIME BETWEEN V_QUERY_
START and V_QUERY_END;
select IFNULL(SUM(a.VALUE),0) into Output
from fm_data_record a
inner join FM_DATA_INFO c ON a.DATA_ID=c.DATA_ID
where a.
MACHINE_ID=p_
machine_id and c.TYPE=1 and a.DATETIME BETWEEN V_QUERY_
START and V_QUERY_END;
select IFNULL(SUM(a.VALUE),0) into Fling
from fm_data_record a
inner join FM_DATA_INFO c ON a.DATA_ID=c.DATA_ID
where a.
MACHINE_ID=p_
machine_id and c.TYPE<>1 and a.DATETIME BETWEEN V_QUERY_
START and V_QUERY_END;
SET Theory_Durat
ion=ROUND(TIMESTAMPDIFF(SECOND,V_QUERY_
START,V_QUERY_END)/60,2);
-- SELECT * FROM TMP_ALARM_RECORD_SINGLE;
SELECT
case when
IFNULL(ROUND(SUM(TIMESTAMPDIFF(SECOND,A.
START_TIME,A.END_TIME))/60,2),0)>Theory_Durat
ion then Theory_Durat
ion
else IFNULL(ROUND(SUM(TIMESTAMPDIFF(SECOND,A.
START_TIME,A.END_TIME))/60,2),0) end INTO Stand_Durat
ion
FROM TMP_ALARM_RECORD_SINGLE A WHERE A.IS_STAND='Y';
SELECT
case when
IFNULL(ROUND(SUM(TIMESTAMPDIFF(SECOND,A.
START_TIME,A.END_TIME))/60,2),0)>Theory_Durat
ion then Theory_Durat
ion
else IFNULL(ROUND(SUM(TIMESTAMPDIFF(SECOND,A.
START_TIME,A.END_TIME))/60,2),0) end
INTO Stop_Durat
ion
FROM TMP_ALARM_RECORD_SINGLE A WHERE A.IS_STAND='N';
set RUN_DURAT
ION=Theory_Durat
ion-Stand_Durat
ion-Stop_Durat
ion;
-- SELECT Theory_Durat
ion,V_QUERY_
START,V_QUERY_END,Stand_Durat
ion,Stop_Durat
ion;
select YIELD_RATE/100,THEORY_CT into yield,T_CT from bas_
machine_oee where
MACHINE_ID=p_
machine_id and v_now>=EFFECTIVE_DATE and v_now<FAILURE_DATE LIMIT 1;
SET Theory_Output=Theory_Durat
ion*60/T_CT;
SET PF=IF(Run_Durat
ion !=0,T_CT*Output/(Run_Durat
ion*60),0);
SET AV=IF(Theory_Durat
ion !=0,Run_Durat
ion/Theory_Durat
ion,0);
SET OEE=AV*PF*(CASE WHEN Input>0 THEN FORMAT(Output/Input,2) ELSE '0' END);
IF DATE_FORMAT(p_end_time,'%H:%i:%s')='08:00:00' and DATE_FORMAT(p_
start_time,'%H:%i:%s')='20:00:00' THEN
SET Theory_Output=720*60/T_CT;
SELECT p_
machine_id as
MACHINE_ID,V_
MACHINE_NO as
MACHINE_NO,V_
MACHINE_NAME AS
MACHINE_NAME,V_QUERY_
START,V_QUERY_END,TRUNCATE(OEE*100,2) OEE,TRUNCATE(AV*100,2) AV,TRUNCATE(PF*100,2) PF,Output,Fling,case when (Output+Fling)>0 then ROUND(Output/(Output+Fling)*100,2) ELSE 0 END AS OKRate,case when (Output+Fling)>0 then ROUND(Fling/(Output+Fling)*100,2) ELSE 0 END AS TossingRate,Theory_Output,RUN_DURAT
ION,720 as Theory_DURAT
ION, Stand_Durat
ion,Stop_Durat
ion ,Case when Output>0 then TRUNCATE(Run_Durat
ion*60/Output,2) else 0 end as CT;
ELSEIF DATE_FORMAT(p_end_time,'%H:%i:%s')='20:00:00' THEN
SET Theory_Output=720*60/T_CT;
SELECT p_
machine_id as
MACHINE_ID,V_
MACHINE_NO as
MACHINE_NO,V_
MACHINE_NAME AS
MACHINE_NAME,V_QUERY_
START,V_QUERY_END,TRUNCATE(OEE*100,2) OEE,TRUNCATE(AV*100,2) AV,TRUNCATE(PF*100,2) PF,Output,Fling,case when (Output+Fling)>0 then ROUND(Output/(Output+Fling)*100,2) ELSE 0 END AS OKRate,case when (Output+Fling)>0 then ROUND(Fling/(Output+Fling)*100,2) ELSE 0 END AS TossingRate,Theory_Output,RUN_DURAT
ION,720 as Theory_DURAT
ION, Stand_Durat
ion,Stop_Durat
ion ,Case when Output>0 then TRUNCATE(Run_Durat
ion*60/Output,2) else 0 end as CT;
else
SET Theory_Output=1440*60/T_CT;
SELECT p_
machine_id as
MACHINE_ID,V_
MACHINE_NO as
MACHINE_NO,V_
MACHINE_NAME AS
MACHINE_NAME,V_QUERY_
START,V_QUERY_END,TRUNCATE(OEE*100,2) OEE,TRUNCATE(AV*100,2) AV,TRUNCATE(PF*100,2) PF,Output,Fling,case when (Output+Fling)>0 then ROUND(Output/(Output+Fling)*100,2) ELSE 0 END AS OKRate,case when (Output+Fling)>0 then ROUND(Fling/(Output+Fling)*100,2) ELSE 0 END AS TossingRate,Theory_Output,RUN_DURAT
ION,1440 as Theory_DURAT
ION, Stand_Durat
ion,Stop_Durat
ion ,Case when Output>0 then TRUNCATE(Run_Durat
ion*60/Output,2) else 0 end as CT;
end if;
leave label;
-- SELECT * from usp_oee_get
machineoeeandstatussingleclient AS A WHERE A.
MACHINE_ID=p_
machine_id and A.V_QUERY_
START=p_
start_time and A.V_QUERY_END=p_end_time AND TimeFrame=Time_Frame;
IF NOT EXISTS(SELECT 1 FROM TMP_ALARM_RECORD_SINGLE) THEN
SELECT V_QUERY_
START START_TIME,V_QUERY_END END_TIME,TIMESTAMPDIFF(SECOND,V_QUERY_
START,V_QUERY_END) Durat
ion,'Run' Status;
ELSE
BEGIN
DECLARE V_
START_TIME DATETIME;
DECLARE V_END_TIME DATETIME;
DECLARE V_END_TIME_PRE DATETIME;
DECLARE V_IS_STAND VARCHAR(1);
DECLARE S INT DEFAULT 0;
DECLARE MC CURSOR FOR SELECT
START_TIME,END_TIME,IS_STAND FROM TMP_ALARM_RECORD_SINGLE ORDER BY
START_TIME;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET S = 1 ;
DROP TEMPORARY TABLE IF EXISTS TMP_ALARM_RECORD_SINGLE_COMPLEMENT;
CREATE TEMPORARY TABLE TMP_ALARM_RECORD_SINGLE_COMPLEMENT
(
START_TIME DATETIME,
END_TIME DATETIME
);
SET V_END_TIME_PRE=V_QUERY_
START;
OPEN MC;
REPEAT
FETCH MC INTO V_
START_TIME,V_END_TIME,V_IS_STAND;
IF(V_END_TIME_PRE<V_
START_TIME) THEN
INSERT INTO TMP_ALARM_RECORD_SINGLE_COMPLEMENT VALUES (V_END_TIME_PRE,V_
START_TIME);
END IF;
SET V_END_TIME_PRE=V_END_TIME;
UNTIL S END REPEAT;
CLOSE MC;
IF(V_END_TIME<V_QUERY_END) THEN
INSERT INTO TMP_ALARM_RECORD_SINGLE_COMPLEMENT VALUES (V_END_TIME,V_QUERY_END);
END IF;
select
START_TIME,END_TIME,TIMESTAMPDIFF(SECOND,
START_TIME,END_TIME) Durat
ion,case when IS_STAND='Y' then 'Stand' else 'Stop' end Status from TMP_ALARM_RECORD_SINGLE
UN
ION ALL
select
START_TIME,END_TIME,TIMESTAMPDIFF(SECOND,
START_TIME,END_TIME) Durat
ion,'Run' from TMP_ALARM_RECORD_SINGLE_COMPLEMENT
ORDER BY
START_TIME;
END;
END IF;
END;
END
本文深入分析了在友善mini2440提供的Linux2.6.32.2内核中，如何通过machine_desc结构体定义设备信息，解释了各个字段如nr, phys_io, io_pg_offst等的作用，并详细介绍了init_machine, init_irq, map_io等函数在内核启动过程中的应用。
扫一扫
4740
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
