Oracleデータベースでビジネスプロセスの監視を設定し、無料バージョンのGrafanaを使用してプロットする方法

CPALL varchar2(30) := ' .';
CPNET varchar2(30) := ' ';
CPACB varchar2(30) := ' ';
CPI2C varchar2(30) := ' I2C';
CPADD varchar2(30) := '. ';
CPMP1 varchar2(30) := ' MPPT1';
CPMP2 varchar2(30) := ' MPPT2';
CPMP3 varchar2(30) := ' MPPT3';
CEDAY varchar2(30) := '  ';
CEMP1 varchar2(30) := ' MPPT1';
CEMP2 varchar2(30) := ' MPPT2';
CEMP3 varchar2(30) := ' MPPT3';
CETOB varchar2(30) := '  ';
CEFRB varchar2(30) := '  ';
CEFRN varchar2(30) := '  ';
CUNET varchar2(30) := ' ';
CUOUT varchar2(30) := ' ';
CUACB varchar2(30) := ' ';

function TsToUTs(v_Ts in timestamp) return number is
	v_Dt date;
begin
	v_Dt := v_ts;
	return trunc((v_Dt - to_date('01.01.1970','DD.MM.YYYY')) -- -   1  1970
	 * (24 * 60 * 60)) --   - 
	 * 1000 --  
	 + to_number(to_char(v_ts,'FF3')); --  
end;

procedure get_query(pInp in out nocopy JSON_OBJECT_T, pOut in out nocopy JSON_ARRAY_T) is
	type rtflag is record (
			 fTp varchar2(30)
			,fOb json_object_t
			,fAr json_array_t
		);
	type ttflag is table of rtflag index by varchar2(127);
	tflag ttflag;
	
	vTmpOb json_object_t;
	vTmpAr json_array_t;
	vTmpId varchar2(30);
	
	vDBeg timestamp;
	vDEnd timestamp;
	vDDBeg date;
	vDDEnd date;
	
	num_tz number;
	curts number;
	
	function GetFlag(pFlagName in varchar2) return boolean is
	begin
		if tflag.exists(pFlagName) then
			return true;
		else
			return false;
		end if;	
	end;

	--function GetFlagType(pFlagName in varchar2) return varchar2 is
	--begin
	--	if tflag.exists(pFlagName) then
	--		return tflag(pFlagName).fTp;
	--	else
	--		pragma error('  ['||pFlagName||']   tflag');
	--	end if;	
	--end;

	procedure AddTrgData(pTrgName in varchar2, pStamp in number, pValue in number) is
	begin
		vTmpAr := Json_Array_t;
		vTmpAr.append(pValue);
		vTmpAr.append(pStamp);
		tFlag(pTrgName).fAr.append(vTmpAr);
	end;
begin
	
	vTmpOb := pInp.get_Object('range');
	num_tz := to_number(GetSetting('MALINA_TIME_ZONE'));
	
	vDBeg := vTmpOb.get_Timestamp('from') + numtodsinterval(num_tz,'hour');
	vDEnd := vTmpOb.get_Timestamp('to')   + numtodsinterval(num_tz,'hour');
	vDDBeg := to_date(to_char(vDBeg,'dd.mm.yyyy hh24:mi:ss'),'dd.mm.yyyy hh24:mi:ss');
	vDDEnd := to_date(to_char(vDEnd,'dd.mm.yyyy hh24:mi:ss'),'dd.mm.yyyy hh24:mi:ss');
	
	vTmpAr := pInp.get_Array('targets');
	for i in 0 .. vTmpAr.get_size - 1 loop
		vTmpOb := JSON_OBJECT_T(vTmpAr.get(i));
		vTmpId := vTmpOb.get_string('target');
		tflag(vTmpId).fTp := vTmpOb.get_string('type');
		tflag(vTmpId).fOb := Json_object_t;
		tflag(vTmpId).fAr := Json_array_t;
		tflag(vTmpId).fOb.put('target',vTmpId);
	end loop;
		
	--      
	if GetFlag(CPALL) or GetFlag(CPNET) or GetFlag(CPACB) or GetFlag(CPI2C) or GetFlag(CUNET) or GetFlag(CUOUT) or GetFlag(CUACB) then
		for ... loop
			curts := TsToUTs(x.qtime - numtodsinterval(num_tz,'hour'));
			
			vTmpId := tflag.first;
			while vTmpId is not null loop
				if vTmpId = CPALL then AddTrgData(vTmpId,curts,x.pall); end if;
				if vTmpId = CPNET then AddTrgData(vTmpId,curts,x.pnet); end if;
				if vTmpId = CPACB then AddTrgData(vTmpId,curts,x.pacb); end if;
				if vTmpId = CPI2C then AddTrgData(vTmpId,curts,x.pi2c); end if;
				if vTmpId = CUNET then AddTrgData(vTmpId,curts,x.unet); end if;
				if vTmpId = CUOUT then AddTrgData(vTmpId,curts,x.uout); end if;
				if vTmpId = CUACB then AddTrgData(vTmpId,curts,x.uacb); end if;
				end;
				vTmpId := tflag.next(vTmpId);
			end loop;
		end loop;
	end if;

	--   
	if GetFlag(CPMP1) or GetFlag(CPMP2) or GetFlag(CPMP3) then
		...
		) loop
			curts := TsToUTs(x.qtime - numtodsinterval(num_tz,'hour'));
			if x.fuid = 1 then if GetFlag(CPMP1) then AddTrgData(CPMP1,curts,x.fpower); end if; end if;
			if x.fuid = 2 then if GetFlag(CPMP2) then AddTrgData(CPMP2,curts,x.fpower); end if; end if;
			if x.fuid = 3 then if GetFlag(CPMP3) then AddTrgData(CPMP3,curts,x.fpower); end if; end if;
		end loop;
	end if;

	--      
	if GetFlag(CPADD) then
		declare
			tqend timestamp;
			paend number;
		begin
			for ... loop
				curts := TsToUTs(x.qtime - numtodsinterval(num_tz,'hour'));
				tqend := x.qtime;
				paend := x.padd;
				AddTrgData(CPADD,curts,x.padd);
			end loop;
			curts := TsToUTs(vDEnd - numtodsinterval(num_tz,'hour'));
			AddTrgData(CPADD,curts,paend);
		end;
	end if;

	--     
	if GetFlag(CEDAY) or GetFlag(CEMP1) or GetFlag(CEMP2) or GetFlag(CEMP3) or GetFlag(CEFRN) then
		declare
			vDEBeg date;
			vDEEnd date;
			vDECur date;
			curEn number;
			prven number;
			vTSCur timestamp;
			curEnToBat number;
			curEnFromBat number;
			procedure GetCeMp(vCeMp in varchar2, vMpUID in number) is
			begin
				vDECur := vDEBeg;
				while vDECur <= vDEEnd loop
					vTSCur := to_timestamp(to_char(vDECur,'dd.mm.yyyy'),'dd.mm.yyyy');

					select ...
						into curEn;
					
					curts := TsToUTs(vTsCur - numtodsinterval(num_tz,'hour'));
					AddTrgData(vCeMp,curts,curen);
					vDECur := vDECur + 1;
				end loop;
			end;
		begin
			vDEBeg := trunc(vDDBeg);
			vDEEnd := trunc(vDDEnd);
			
			if GetFlag(CEDAY) or GetFlag(CETOB) or GetFlag(CEFRB) then
				vDECur := vDEBeg;
				while vDECur <= vDEEnd loop
					vTSCur := to_timestamp(to_char(vDECur,'dd.mm.yyyy'),'dd.mm.yyyy');
					select						 ...
						into curEn,curEnToBat,curEnFromBat;
					curts := TsToUTs(vTsCur - numtodsinterval(num_tz,'hour'));
					
					if GetFlag(CEDAY) then AddTrgData(CEDAY,curts,curen); end if;
					if GetFlag(CETOB) then AddTrgData(CETOB,curts,curenToBat); end if;
					if GetFlag(CEFRB) then AddTrgData(CEFRB,curts,curenFromBat); end if;

					vDECur := vDECur + 1;
				end loop;
			end if;
			
			if GetFlag(CEMP1) then
				GetCeMp(CEMP1,1);
			end if;
			if GetFlag(CEMP2) then
				GetCeMp(CEMP2,2);
			end if;
			if GetFlag(CEMP3) then
				GetCeMp(CEMP3,3);
			end if;

			--     
			if GetFlag(CEFRN) then
				vDECur := vDEBeg-1;
				prven := null;
				while vDECur <= vDEEnd loop
					vTSCur := to_timestamp(to_char(vDECur,'dd.mm.yyyy'),'dd.mm.yyyy');

					curen := 0;
					for ... loop
						curen := x.enet;
						exit;
					end loop;	
					
					if curen = 0 and prven != 0 then
						curen := prven;
					end if;	
					
					if prven is null then
						prven := curen;
					else	
						if prven = 0 then
							prven := curen;
						end if;
						curts := TsToUTs(vTsCur - numtodsinterval(num_tz,'hour'));

						AddTrgData(CEFRN,curts,curen - prven);
						prven := curen;
					end if;	
					
					vDECur := vDECur + 1;
				end loop;
			end if;

						
		end;
	end if;

	--     
	vTmpId := tflag.first;
	while vTmpId is not null loop
		tflag(vTmpId).fOb.put('datapoints',tflag(vTmpId).fAr);
		tflag(vTmpId).fAr := null;
		pOut.append(tflag(vTmpId).fOb);
		tflag(vTmpId).fOb := null;
		vTmpId := tflag.next(vTmpId);
	end loop;
end;	

procedure get_search(pInp in out nocopy JSON_OBJECT_T, pOut in out nocopy JSON_ARRAY_T) is
	vTarget varchar2(100);
begin
	vTarget := trim(pInp.get_String('target'));
	if vTarget is null then
		pOut.Append(CPALL);
		pOut.Append(CPNET);
		pOut.Append(CPACB);
		pOut.Append(CPI2C);
		pOut.Append(CPADD);
		pOut.Append(CPMP1);
		pOut.Append(CPMP2);
		pOut.Append(CPMP3);
		pOut.Append(CEDAY);
		pOut.Append(CEMP1);
		pOut.Append(CEMP2);
		pOut.Append(CEMP3);
		pOut.Append(CETOB);
		pOut.Append(CEFRB);
		pOut.Append(CEFRN);
		pOut.Append(CUNET);
		pOut.Append(CUOUT);
		pOut.Append(CUACB);
	end if;	
end;	

procedure GetJson(pPathInfo in varchar2, pInpPost in blob, pOutPost out blob) is
	vInp JSON_OBJECT_T;
	vOut JSON_ARRAY_T;
begin
	vInp := JSON_OBJECT_T(pInpPost);
	vOut := JSON_ARRAY_T();

	--      pPathInfo
	if pPathInfo = '/search' then
		get_search(vInp, vOut);
	elsif pPathInfo = '/query' then
		get_query(vInp, vOut);
	end if;
	
	pOutPost := vOut.to_Blob;
end;