PACKAGE cm_jasper_reports_pkg
is
FUNCTION construct_jasper_params(type_param type_jasper_params)
return varchar2 ;
FUNCTION construct_json_for_jasper(v_job_label varchar2,v_job_desc varchar2,v_timeZone VARCHAR2,v_rep_name varchar2,
v_params varchar2,v_output_filename varchar2,v_rep_format varchar2,v_rep_desc varchar2,v_body varchar2,v_to_mail varchar2,v_cc_mail varchar2,v_bcc_mail varchar2) return varchar2;
function constrct_json_for_alfresco(v_job_label varchar2,v_job_desc varchar2,v_timeZone VARCHAR2,v_rep_name varchar2,v_params varchar2,
v_output_filename varchar2,v_rep_format varchar2,v_rep_desc varchar2) return varchar2;
FUNCTION schedule_jasper_job(p_url varchar2,p_content varchar2)
return varchar2 ;
FUNCTION check_job_status( p_url in varchar2)
return varchar2;
PROCEDURE load_binary_from_url (p_url IN VARCHAR2,p_rep_name varchar2,p_resource_nt_fnd out number);
Function send_jasper_mails(v_to_mail varchar2,v_cc_mail varchar2,v_bcc_mail varchar2)
return varchar2;
End;
--------------------
PACKAGE BODY cm_jasper_reports_pkg
is
FUNCTION construct_jasper_params(type_param type_jasper_params)
return varchar2 is
v_params varchar2(3000);
j number :=1;
BEGIN
FOR i IN 1 .. type_param.count LOOP
--"pUnitCode":["~NULL~"],"pCompCode":["~NULL~"],"pAcctno":["~NULL~"]
if j=1 then
if type_param(i).param_value is null then
v_params:='"'||type_param(i).param_name||'":["~'||type_param(i).param_value||'~"]';
else
v_params:='"'||type_param(i).param_name||'":["'||type_param(i).param_value||'"]';
end if;
else
if type_param(i).param_value is null then
v_params:=v_params||',"'||type_param(i).param_name||'":["~'||type_param(i).param_value||'~"]';
else
v_params:=v_params||',"'||type_param(i).param_name||'":["'||type_param(i).param_value||'"]';
end if;
end if;
j:=j+1;
dbms_output.put_line(type_param(i).param_name||' = '||type_param(i).param_value);
END LOOP;
return v_params;
END;
FUNCTION construct_json_for_jasper(v_job_label varchar2,v_job_desc varchar2,v_timeZone VARCHAR2,v_rep_name varchar2,
v_params varchar2,v_output_filename varchar2,v_rep_format varchar2,v_rep_desc varchar2,v_body varchar2
,v_to_mail varchar2,v_cc_mail varchar2,v_bcc_mail varchar2) return varchar2
is
v_json varchar2(32767);
begin
--dbms_output.put_line('bccc addr'||v_bcc_mail||' '||v_cc_mail);
select '{ "label":'||'"'||v_job_label||'",'||'"description":'||'"'||v_job_desc||'", "trigger": {'||
'"simpleTrigger": {
"timezone":'||'"'||v_timeZone||'",' || '"startType": 1,"occurrenceCount": 1 }
},"source": {
"reportUnitURI": "/reports/'||v_rep_name||'","parameters": {
"parameterValues": {'||v_params||'}
}
},
"baseOutputFilename":'||'"'||v_output_filename||'", "outputTimeZone":"'||v_timeZone||'", "repositoryDestination": {
"folderURI": "/reports/Completed_Jobs"
},
"outputFormats": {
"outputFormat": ["'||v_rep_format||'"]
},
"mailNotification" : {
"toAddresses" : { "address": ['||v_to_mail ||'] },
"ccAddresses" : { "address": ['||v_cc_mail ||'] },
"bccAddresses" : { "address": ['||v_bcc_mail ||'] },
"subject" : "'||v_rep_desc||'",
"messageText" : "'||v_body||'",
"skipEmptyReports" : false,
"messageTextWhenJobFails" : null,
"includingStackTraceWhenJobFails" : false,
"skipNotificationWhenJobFails" : true,
"resultSendType" : "SEND_ATTACHMENT"
}
}' into v_json from dual;
return v_json;
end;
function constrct_json_for_alfresco(v_job_label varchar2,v_job_desc varchar2,v_timeZone VARCHAR2,v_rep_name varchar2,v_params varchar2,
v_output_filename varchar2,v_rep_format varchar2,v_rep_desc varchar2) return varchar2
IS
v_json varchar2(32767);
begin
select '{ "label":'||'"'||v_job_label||'",'||'"description":'||'"'||v_job_desc||'", "trigger": {'||
'"simpleTrigger": {
"timezone":'||'"'||v_timeZone||'",' || '"startType": 1,"occurrenceCount": 1 }
},"source": {
"reportUnitURI": "/reports/'||v_rep_name||'","parameters": {
"parameterValues": {'||v_params||'}
}
},
"baseOutputFilename":'||'"'||v_output_filename||'", "outputTimeZone":"'||v_timeZone||'", "repositoryDestination": {
"folderURI": "/reports/Completed_Jobs"
},
"outputFormats": {
"outputFormat": ["'||v_rep_format||'"]
}
}' into v_json from dual;
return v_json;
end;
FUNCTION schedule_jasper_job(p_url varchar2,p_content varchar2)
return varchar2 is
req utl_http.req;
res utl_http.resp;
buffer varchar2(32000);
v_job_id varchar2(500):=0;
begin
--dbms_output.put_line('----p_url:'||p_content);
req := utl_http.begin_request(p_url, 'PUT');
dbms_output.put_line('purl1');
utl_http.set_header(req, 'content-type', 'application/job+json');
dbms_output.put_line('purl2');
utl_http.set_header(req, 'Content-Length', length(p_content));
dbms_output.put_line('purl3');
utl_http.write_text(req, p_content);
dbms_output.put_line('p_content '||p_content);
res := utl_http.get_response(req);
-- dbms_output.put_line('res:'||res);
-- process the response from the HTTP call
begin
loop
dbms_output.put_line('purl6');
utl_http.read_line(res, buffer);
dbms_output.put_line('purl7');
dbms_output.put_line('buffer'||buffer);
v_job_id:= substr(buffer,instr(buffer,':',1)+1,instr(buffer,',',1)-(instr(buffer,':',1)+1));
end loop;
utl_http.end_response(res);
exception
when utl_http.end_of_body
then
utl_http.end_response(res);
when others then
dbms_output.put_line(sqlerrm||'Schedule_jasper_job');
end;
dbms_output.put_line('v_job_id:'||v_job_id);
return v_job_id;
end ;
FUNCTION check_job_status( p_url in varchar2)
return varchar2 is
req utl_http.req;
res utl_http.resp;
buffer varchar2(4000);
status varchar2(50);
begin
req := utl_http.begin_request(p_url, 'GET');
utl_http.set_header(req, 'content-type', 'application/job+json');
utl_http.set_header(req, 'Content-Length', 0);
-- utl_http.write_text(req, content);
res := utl_http.get_response(req);
begin
loop
utl_http.read_line(res, buffer);
begin
with testTable(xml_val) as (
select xmltype(buffer) from dual)
select xmlcast(xmlquery('/state/value' passing xml_val returning content) as varchar2(100)) into status
from testTable ;
exception
when others then
status:='NOTFOUND';
end ;
end loop;
utl_http.end_response(res);
exception
when utl_http.end_of_body
then
utl_http.end_response(res);
if status is null then
status:='NOTFOUND';
end if ;
end;
return status;
end ;
PROCEDURE blob_to_file (p_blob IN OUT NOCOPY BLOB,
p_dir IN VARCHAR2,
p_filename IN VARCHAR2)
AS
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
BEGIN
--dbms_output.put_line('lenth f '||l_blob_len);
l_blob_len := DBMS_LOB.getlength(p_blob);
--dbms_output.put_line('lenth '||l_blob_len);
-- Open the destination file.
l_file := UTL_FILE.fopen(p_dir, p_filename,'wb', 32767);
-- Read chunks of the BLOB and write them to the file until complete.
WHILE l_pos <= l_blob_len LOOP
DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END blob_to_file;
PROCEDURE load_binary_from_url (p_url IN VARCHAR2,p_rep_name varchar2,p_resource_nt_fnd out number)
AS
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_blob BLOB;
l_raw RAW(32767);
l_cnt number:=0;
BEGIN
-- Initialize the BLOB.
DBMS_LOB.createtemporary(l_blob, FALSE);
-- Make a HTTP request and get the response.
l_http_request := UTL_HTTP.begin_request(p_url);
l_http_response := UTL_HTTP.get_response(l_http_request);
-- Copy the response into the BLOB.
BEGIN
LOOP
if (l_http_response.status_code = UTL_HTTP.HTTP_OK) then
l_cnt:=0;
else
l_cnt:=1;
end if;
UTL_HTTP.read_raw(l_http_response, l_raw, 32767);
DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END;
if l_cnt=0 then
p_resource_nt_fnd:=l_cnt;
blob_to_file(l_blob,'EXPORT_REPORTS',p_rep_name);
else
p_resource_nt_fnd:=l_cnt;
end if;
-- Relase the resources associated with the temporary LOB.
DBMS_LOB.freetemporary(l_blob);
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.end_response(l_http_response);
DBMS_LOB.freetemporary(l_blob);
RAISE;
dbms_output.put_line('99');
-- Relase the resources associated with the temporary LOB.
DBMS_LOB.freetemporary(l_blob);
END ;
Function send_jasper_mails(v_to_mail varchar2,v_cc_mail varchar2,v_bcc_mail varchar2)
return varchar2
is
v_mail varchar2(20000);
begin
select '"mailNotification" : {
"toAddresses" : { "address": ["'||v_to_mail||'"] },
"ccAddresses" : { "address": ["'||v_cc_mail||'"] },
"bccAddresses" : { "address": ["'||v_bcc_mail||'"] },
"subject" : "TestMailforJasperJob",
"messageText" : "See attached report.",
"skipEmptyReports" : false,
"messageTextWhenJobFails" : null,
"includingStackTraceWhenJobFails" : false,
"skipNotificationWhenJobFails" : true,
"resultSendType" : "SEND_ATTACHMENT"
}'
into v_mail
from dual;
return v_mail;
end;
End;
------------------body end---------------
-----------------example start------------------
PROCEDURE send_USER_Privileges_mail(p_comp varchar2, p_dt date)
is
p_paramform varchar2(100):='NO';
v_srmgrmail varchar2(2000):=null;
to_emailid varchar2(2000):=null;
v_cc varchar2(32767):=null;
v_bcc varchar2(32767):=null;
v_fn_year number:=null;
p_login varchar2(100);
p_server varchar2(50);
p_repserver varchar2(100);
----- Start of Track seq - 10001 -----------
vRepMailAlfresco Varchar2(1) := 'N' ;
vMailFileName Varchar2(200) ;
vMailFilesPath Varchar2(200) ;
vDmsCntrl Number ;
vMailPathFileName Varchar2(400) := null;
vPdfPath Varchar2(1000) ;
vPwd Varchar2(100) ;
----- End of Track seq - 10001 -----------
-- jrep start
vJasperParams type_jasper_params := type_jasper_params();
vJasperParamString Varchar2(4000);
vJasperString Varchar2(20000);
vJobId Varchar2(500);
vJobStatus Varchar2(100);
vServerUrl Varchar2(100) := Get_param_val('RPT_SRVR_URL', '', '' ) ;
vReportName Varchar2(100) := 'USER_RESP_REP' ;
vReportNameText Varchar2(500) :='User Responsibility privileges History as On ';
vRepFileName Varchar2(100) := 'USER_RESP_REP'; --||to_char(sysdate,'ddmmyyhhmiss') ;
vRepFormat Varchar2(100) := 'PDF'; -- must be in caps
vErrorNo Number ;
vErrorMsg Varchar2(1000);
vSubject Varchar2(100) :='User Responsibility privileges History as On ';
vBodyMsg Varchar2(1000) ;
vChkCnt Number := 0 ;
vAsId Number ;
v_before_mnth number:='3';
-- jrep end
v_j_username varchar2(100);
v_j_password varchar2(100);
v_rep_server varchar2(100);
v_add_cc_ids varchar2(2000):=null;
v_frdt DATE := p_dt-1;
v_todt date ;
v_week number ;
vTradingTempId number;
vErrorNo number;
vErrorMsg varchar2(1000);
v_error_no number:=0;
v_reason varchar2(200);
v_mail_body varchar2(2000);
v_mail_to varchar2(2000);
v_mail_cc varchar2(2000);
v_mail_bcc varchar2(2000);
vToMailIds Cm_automail_config_dtl.mail_to%type ;
vCcMailIds Cm_automail_config_dtl.mail_cc%type ;
vBccMailIds Cm_automail_config_dtl.mail_bcc%type ;
vCatStr varchar2(20000);
v_fin varchar2(100);
Cursor c is
SELECT c.comp_code, c.comp_name
FROM Cm004_comp_mst c
WHERE c.comp_code = Decode(p_comp, 'ALL', c.comp_code, p_comp)
ORDER BY c.comp_code ;
cursor c1(comp varchar2) is
SELECT ad.comp_code, ad.mail_to, ad.mail_cc,
ad.mail_bcc,ad.unit_code, ah.mail_subject
FROM cm_automail_config_hdr ah, cm_automail_config_dtl ad
WHERE ah.comp_code = ad.comp_code
AND ah.MAIL_FUNC_CODE = ad.MAIL_FUNC_CODE
AND ah.Mail_func_code = 'USER_RESP_REP'
-- AND ad.unit_code = 'ALL'
and ah.comp_code = comp
AND nvl(ah.mail_active, 'N') = 'Y' ;
begin
v_rep_server := jasper_repo_config(v_j_username,v_j_password); -- jsper server credentials
For i in c
Loop
For j in c1(i.comp_code) loop
vToMailIds := j.mail_to ;
vCcMailIds := j.mail_cc;
vBccMailIds :=j.mail_bcc;
/*vToMailIds := 'narendrae@sathguru.com';--'SATHGURU@COURTS.COM.FJ';
vCcMailIds := null;
vBccMailIds :=null;*/
If vToMailIds is not null Then
--------- Getting parameter value for Reports integration to Alfresco -------
Select Cm_run_reports_mail_dms_seq.nextval into vMailFileName From Dual ;
vRepFileName :=vReportName||to_char(sysdate,'ddmmyyhhmiss') ;
vMailFileName := vRepFileName; --||vMailFileName;
--------- Getting parameter value for Reports integration to Alfresco -------
vRepMailAlfresco := Dms_AutoMail_data_Pkg.Chk_dms_enable_automail_yn() ;
If Nvl(vRepMailAlfresco, 'N') = 'Y' Then
vMailFilesPath := Dms_AutoMail_data_Pkg.Get_RepFile_Path(j.comp_code, '11');
If vMailFilesPath is not null Then
vmailPathFileName := vMailFilesPath||'/'||vMailFileName||'.'||vRepFormat ;
End If;
End if;
Begin -- jasper begin start
--vRepFileName := vReportName||to_char(sysdate,'ddmmyyhhmiss') ;
dbms_output.put_line('jasper report parametres assigning');
vJasperParams.extend;
vJasperParams(1) := jasper_params_obj('pUserCode','ALL') ;
vJasperParams.extend;
vJasperParams(2) := jasper_params_obj('pAsOnDate',to_char(v_frdt,'yyyy-mm-dd'));
vJasperParamString := Cm_jasper_reports_pkg.construct_jasper_params(vJasperParams) ;
dbms_output.put_line('Mail File Path ;'||vMailFilesPath);
dbms_output.put_line('after sconstruct jasper params');
If Nvl(vRepMailAlfresco, 'N') = 'N' Or vMailFilesPath is null Then
Begin
SELECT Replace(nvl2(vToMailIds,'"'||vToMailIds||'"',vToMailIds),',','","'),
Replace(nvl2(vCcMailIds,'"'||vCcMailIds||'"',vCcMailIds),',','","'),
Replace(nvl2(vBccMailIds,'"'||vBccMailIds||'"',vBccMailIds),',','","')
INTO vToMailIds, vCcMailIds, vBccMailIds
FROM Dual;
Exception
When Others Then
vToMailIds := null ;
vCcMailIds := null ;
vBccMailIds := null ;
End;
If vToMailIds is not null Then
select 'Dear Sir/Madam, '||CHR(13)||CHR(13)
||'Please find the attached '||vSubject||CHR(13)||CHR(13)
||'Regards'||CHR(13)||'Automail' ||to_char(v_frdt,'dd-mon-yyyy') into vBodyMsg from dual;
vJasperString := Cm_jasper_reports_pkg.construct_json_for_jasper (vReportNameText, 'Report',
'Pacific/Fiji', vReportName, vJasperParamString, vMailFileName, vRepFormat,
vSubject||to_char(v_frdt,'dd-mon-yyyy'), vBodyMsg, vToMailIds, vCcMailIds, vBccMailIds);
dbms_output.put_line('after submitting to jsper server '||vJasperString);
End If;
Else
Dbms_output.put_line(vReportName||' - '||vJasperParamString||' - '||vMailFileName||' - '||vRepFormat);
vJasperString := Cm_jasper_reports_pkg.constrct_json_for_alfresco (vReportNameText||to_char(v_frdt,'dd-mon-yyyy'), 'Report', 'Pacific/Fiji',
vReportName, vJasperParamString, vMailFileName, vRepFormat, vSubject||to_char(v_frdt,'dd-mon-yyyy')) ;
dbms_output.put_line('vJasperString : '||vJasperString);
vMailPathFileName := 'F:\export_reports\'||vRepFileName||'.pdf';
End If;
Begin
vJobId := Cm_jasper_reports_pkg.Schedule_jasper_job ( vServerUrl||'/rest_v2/jobs?j_username='||v_j_username||'&'||'j_password='||v_j_password, vJasperString);
dbms_output.put_line(vJobId);
Exception
When Others Then
dbms_output.put_line('Schedule_jasper_job@ '||sqlerrm);
End;
For obj in 1 .. vJasperParams.count
Loop
vJasperParams.trim();
End Loop ;
If Nvl(vRepMailAlfresco, 'N') = 'Y' And vMailFilesPath is not null Then
-- file move start
For i in 1 .. 3
Loop
dbms_lock.sleep(30);--locking for 30 secs
dbms_output.put_line('----------i value : '||i);
vJobStatus := Check_job_status('http://'||v_rep_server||'/jasperserver/rest_v2/jobs/'||vJobId||'/state?j_username='||v_j_username||'&='||v_j_password);
dbms_output.put_line('vJobStatus '||vJobStatus);
If vJobStatus = 'NOTFOUND' Then
Exit;
End If;
End Loop ;
If vJobStatus = 'NOTFOUND' Then
Begin
Cm_jasper_reports_pkg.load_binary_from_url('http://'||v_rep_server||'/jasperserver/rest_v2/resources/reports/Completed_Jobs/'||vRepFileName||'.pdf?j_username='||v_j_username||'&='||v_j_password,''||vRepFileName||'.pdf', vChkCnt);
End;
End if;
-- file move start end
vDmsCntrl := Cm_run_reports_mail_dms_seq.nextval;
If Nvl(vRepMailAlfresco, 'N') = 'Y' And vMailPathFileName is not null And vJobId is not null Then
Ins_runrep_dtl_dms(i.comp_code, vToMailIds, 11, vMailPathFileName, vRepFileName||'.pdf', vRepFormat,
vJobId, 'Y', 'Jasper Server'||' -Subject- '||vSubject, 'Jasper Server', 'AUTOMAIL',
vDmsCntrl, vCcMailIds, vBccMailIds, 'Report Mail', vSubject||to_char(v_frdt,'dd-mon-yyyy')) ;
End If;
End If;
Exception
When others then
dbms_output.put_line(':SQLCODE: '||sqlcode||' :SQLERRM:'||SQLERRM);
End; -- jasper begin end
End if; ---- Track seq - 10001 -----------
End loop; -- automail loop
End Loop; -- comp loop ends
Exception
when others then
dbms_output.put_line('SQLCODE '||sqlcode||' SQLERRM'||SQLERRM);
End send_USER_Privileges_mail;
Comments
Post a Comment