Send email notification via XSJS application in Enterprise HANA 2.0

[pallab_haldar]

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: "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/

 

Hope this will help.