Jumat, 27 November 2009

PACKAGE PKG_DY_TP php oracle

CREATE OR REPLACE PACKAGE PKG_DY_TP AS
TYPE ViewRecTyp IS RECORD(
sts_code mis_adhoc_trans.trans_status%type
,trans_id mis_adhoc_trans.trans_id%type
,sts_desc mis_adhoc_status.sts_desc%type
,trans_summary mis_adhoc_trans.trans_summary%type
,ana_name mis_adhoc_analyst.ana_name%type
,dev_name mis_adhoc_developer.dev_name%type);

TYPE ViewCurTyp IS REF CURSOR RETURN ViewRecTyp;

PROCEDURE MAIN;
PROCEDURE PROC_DY_TP
(
pDev VARCHAR2
,in_file utl_file.file_type
);
PROCEDURE VIEW_TP
(
pDev VARCHAR2
,cur_view_tp OUT ViewCurTyp
);
FUNCTION GET_COLOR(pSts VARCHAR2) RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY PKG_DY_TP AS

vchFileName VARCHAR2(100) := 'Daily_TP.html';
vchLocfile VARCHAR2(100) := LOG_PROCESS_NEW.GET_FOLDER('OUTPUT');

PROCEDURE VIEW_TP
(
pDev VARCHAR2
,cur_view_tp OUT ViewCurTyp
) IS
BEGIN
OPEN cur_view_tp FOR
SELECT pkg_dy_tp.get_color(mat.trans_status) sts_code
,mat.trans_id
,mas.sts_desc
,mat.trans_summary
,maa.ana_name
,mad.dev_name
FROM mis_adhoc_trans mat
,mis_adhoc_analyst maa
,mis_adhoc_developer mad
,mis_adhoc_status mas
WHERE mat.trans_pic_analyst = maa.ana_code
AND mat.trans_pic_developer = mad.dev_code
AND mat.trans_status = mas.sts_code
AND DECODE(pDev, 'ALL', '1', mat.trans_pic_developer) = DECODE(pDev, 'ALL', '1', pDev)
ORDER BY mad.dev_code;

END;

FUNCTION GET_COLOR(pSts VARCHAR2) RETURN VARCHAR2 IS
BEGIN
CASE pSts
WHEN 3 THEN
RETURN '"#3366FF"'; --biru
WHEN 4 THEN
RETURN '"#FFFFFF"'; --putih
WHEN 9 THEN
RETURN '"#CC6699"'; --merah
WHEN 1 THEN
RETURN '"#FFCCFF"'; --merah muda
WHEN 2 THEN
RETURN '"#00CC33"'; -- hijau
ELSE
RETURN '"#FFFFFF"'; --putih
END CASE;

END;

END;

Php nya :

$p_id = $_POST['p_id'];
$p_dev = "ALL";
$conn = oci_connect('KONSDEV','KONSDEV', 'DB_18_39') or die;
$sql1 ="begin pkg_dy_tp.view_tp(:p_dev, :cur_view_tp); end;";
$stmt1=oci_parse($conn,$sql1);
$cur_view_tp=oci_new_cursor($conn);
if (!$cur_view_tp)
{
$err=oci_error();
die ($err['message']);
}
if (!oci_bind_by_name($stmt1,":p_dev",$p_dev,32))
{
$err=oci_error($stmt1);
die ($err['message']);
}
if (!oci_bind_by_name($stmt1,":cur_view_tp",$cur_view_tp,-1,OCI_B_CURSOR))
{
$err=oci_error($stmt1);
die ($err['message']);
}

@oci_execute($stmt1);
@oci_execute($cur_view_tp);
@oci_fetch_all($cur_view_tp, $view_records, null, null, OCI_FETCHSTATEMENT_BY_ROW) ;

foreach ($view_records as $fld)
{
//echo "";
echo "";
echo "Update";
echo "View";

?>





}
oci_close($conn);
?>





Tidak ada komentar:

Posting Komentar