Saturday, 16 December 2023

Send email notification via XSJS application in Enterprise HANA 2.0

Send email notification via XSJS application in Enterprise HANA 2.0.

Today I will discuss how we can create an XSJS application to send an email notification if any condition or threshold exceeds. i.e. if the value of a table field value exceeds a certain value ( like 50) it will send an email notification with all the rows exceeding the threshold otherwise, it sends all table data.

1. Create a XSJS application – 

From HANA studio go to development perspective and then create an XSJS project.

Send email notification via XSJS application in Enterprise HANA 2.0

Send email notification via XSJS application in Enterprise HANA 2.0

2. after finishing the creation below two files will be created by default –

1. .XSACCESS   -> Used to specify different application-access rules for individual sub-packages in the package hierarchy.
2. .XSAPP ->  The .xsapp file marks the root folder of an SAP HANA XS application

Send email notification via XSJS application in Enterprise HANA 2.0

3. We have created the XSJS file with the name DEMO_NOTIFICATION_TEST to write the JavaScript program which will send the notification to emails. The sample file is given below –

Note: SMTP server expected to be installed in your HANA server. Please check it from HANA Admin cockpit.

function check_credit_threshold(){
var todaysDate = new todaysDate().toString().slice(4, -24);
var body = 'Employee ID, Employee Name,Credit Threshold \n';
var query = "SELECT  \"EMP_ID\",\"EMP_NAME\",\"CRD_THRD\"  FROM \"_SYS_BIC\".\"CreditHistory/E_CREDIT_HISTORY\" ";
var conn = $.db.getConnection();
    var pstmt = conn.prepareStatement(query);
    pstmt.execute();
    var rs = pstmt.getResultSet();
    
    while(rs.next()) {
        
body += rs.getString(1)+
","+rs.getString(2).replace(/,/g,' ')+
","+rs.getString(3).replace(/,/g,' ')+"\r\n";
}
    var mail = new $.net.Mail({
       sender: {address: "hstryCheck@mydemo.com"},
       to: [{ name: "Demo Company Credit History", address: "pallab@mydemo.com", nameEncoding: "US-ASCII"}],
   cc: ["debu123@mydemo.com", "rita456@mydemo.com"],
         subject: "Demo Company Credit History",
       subjectEncoding: "UTF-8",
       parts: [ new $.net.Mail.Part({
           type: $.net.Mail.Part.TYPE_TEXT,
           text: " Please Demo Company Credit History for Date " + todaysDate,
           contentType: "text/plain",
           encoding: "UTF-8"
       })]
       });
       mail.parts.push(
       new $.net.Mail.Part({
          type: $.net.Mail.Part.TYPE_ATTACHMENT,
          data: (body),
          contentType: "application/vnd.ms-excel; charset=utf-16le",
          encoding: "UTF-8",
          fileName: "Credit_History" + ' ' + todaysDate + ".csv"
       })
          );
    
     mail.send();     
     rs.close();
     pstmt.close();
     conn.close();
     
      /*var columnCount = 0;*/
var columnCount = 0;
var todaysDate1 = new todaysDate().toString().slice(4, -24);
var body1 = 'Employee ID,Credit Threshold \n';
var query1 = "SELECT  \"EMP_ID\",\"CRD_THRD\"  FROM \"_SYS_BIC\".\"CreditHistory/E_CREDIT_HISTORY\" ";
var conn1 = $.db.getConnection();
var pstmt1 = conn1.prepareStatement(query1);
    pstmt1.execute();
    var rs1 = pstmt1.getResultSet();
    
    while(rs1.next()) {
        var rcount= 0; 
rcount = Number(rs1.getString(2));
if (rcount>150)
{
body1 += rs1.getString(1).replace(/,/g,' ')+
"\t"+rs1.getString(2)+ "\r\n";
columnCount += 1;
}
}    
    
    if( columnCount > 0) 
    {
         var mail1 = new $.net.Mail({
       sender: {address: ""hstryCheck@mydemo.com"},
       to: [{ name: "Demo Company Credit History Exceeds", address: "pallab@mydemo.com", nameEncoding: "US-ASCII"}],
       cc: ["debu123@mydemo.com", "rita456@mydemo.com"],
         subject: "Customer credit threshold exceeds and more than 150, Please blok credit card",
       subjectEncoding: "UTF-8",
       parts: [ new $.net.Mail.Part({
           type: $.net.Mail.Part.TYPE_TEXT,
           text: "Customer credit threshold exceeds and more than 150  " + todaysDate1 + "\r\n" + ". Please blok credit card",
           contentType: "text/plain",
           encoding: "UTF-8"
       })]
       });
       mail1.parts.push(
       new $.net.Mail.Part({
          type: $.net.Mail.Part.TYPE_ATTACHMENT,
          data: (body1),
          contentType: "application/vnd.ms-excel",
          encoding: "UTF-8",
          fileName: "Customer_Credit_Exceeds_list" + ' ' + todaysDate + ".xls"
       })
          );
          
       mail1.send();
    
    }
    
     rs1.close();
     pstmt1.close();
     conn1.close();
    
}

4.  Create a Demo.xsJob file to call the XSJS file the below code.

{
  "description": "Demo Company Credit History",
  "action": "CreditHistory:DEMO_NOTOFICATION_TEST.xsjs::check_credit_threshold",
  "schedules":
  [
  {
  "description": "Generate Demo Company Credit History",
  "xscron": "* * * mon:fri 8 0 0"
  }
  ]
}

5. Activate and schedule the job vis the XSJS dashboard URL.

https://testserver.hana.ondemand.com/sap/hana/xs/admin/jobs/

No comments:

Post a Comment