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.

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

       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: "[email protected]"},
    	   to: [{ name: "Demo Company Credit History", address: "[email protected]", nameEncoding: "US-ASCII"}],
		   cc: ["[email protected]", "[email protected]"],
      	   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: ""[email protected]"},
    	   to: [{ name: "Demo Company Credit History Exceeds", address: "[email protected]", nameEncoding: "US-ASCII"}],
    	   cc: ["[email protected]", "[email protected]"],
      	   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/

Hope this will help.