ASSET_API/WebContent/api.jsp

{
"asset": [
<%@ page import="java.sql.*,oracle.jdbc.driver.OracleDriver"%>
<%@ page trimDirectiveWhitespaces="true" %>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","admin");
Statement stmt = conn.createStatement();
//out.println("Testing");
 String school_code = request.getParameter("UDISE");
 String limit = request.getParameter("limit");
 String offset = request.getParameter("offset");
 Integer new_offset = Integer.parseInt(offset) + 1;
 Integer new_limit = Integer.parseInt(offset) + Integer.parseInt(limit);
 //out.println("select * from (select sa.asset_id, v.vendor_area ||', ' || v.vendor_location || ',' || v.vendor_street as address, sa.asscat, asset_no, asset_type, hw_slno, purch_date, maintenance_type, v.vendor_name, vendor_phone1, row_number() over (order by sa.asset_id) rank from SCHOOL_ASSET sa INNER JOIN school_dtl s on s.dep_id = sa.dept_id join vendor_dtl v on v.vendor_id = sa.vendor_id where s.sec_id = "+ school_code +") where rank between "+new_offset+" and "+new_limit);
ResultSet rs = stmt.executeQuery("select * from(SELECT sa.asset_id,v.vendor_area ||', '|| v.vendor_location || ',' || v.vendor_street AS address, decode(asscat, 'S', 'SCHOOL ASSET') as asscat , asset_no, decode(asset_type, 'COC', 'CORPORATION OF CHENNAI') as asset_type, hw_slno, purch_date, decode(maintenance_type, 'W', 'WARRENTY') as maintenance_type, v.vendor_name, vendor_phone1, th.hardware_name, row_number() over (order by sa.asset_id) rank FROM SCHOOL_ASSET sa INNER JOIN school_dtl s ON s.dep_id = sa.dept_id JOIN vendor_dtl v ON v.vendor_id = sa.vendor_id JOIN  TBL_HARD_TYPE th ON th.HARDWARE_ID = sa.HW_ID WHERE s.sec_id ="+ school_code +") WHERE rank BETWEEN "+ new_offset +" AND "+ new_limit ); 

while(rs.next()){%>
{
"ASSET_CATEGORY": "<%=rs.getString("asscat")%>",
"rank": "<%=rs.getString("rank")%>",
"ASSET_NAME": "<%=rs.getString("asset_no")%>",
"SOURCE_OF_ASSET": "<%=rs.getString("asset_type")%>",
"SERIAL_NO": "<%=rs.getString("hw_slno")%>",
"HARDWARE_TYPE": "<%=rs.getString("hardware_name")%>"
"DATE_RECEIVED": "<%=rs.getString("PURCH_DATE")%>",
"AMC_WARENTY": "<%=rs.getString("MAINTENANCE_TYPE")%>",
"VENDOR_NAME": "<%=rs.getString("vendor_name")%>",
"VENDOR_MOBILE_NUMBER": "<%=rs.getString("vendor_phone1")%>",
"ADDRESS": "<%=rs.getString("address")%>"
},
<%	 
}
// finding total records
Integer total_records = 0;
ResultSet rs1 = stmt.executeQuery("select sa.asset_id, v.vendor_area ||', ' || v.vendor_location || ',' || v.vendor_street as address, sa.asscat, asset_no, asset_type, hw_slno, purch_date, maintenance_type, v.vendor_name, vendor_phone1, row_number() over (order by sa.asset_id) rank from SCHOOL_ASSET sa INNER JOIN school_dtl s on s.dep_id = sa.dept_id join vendor_dtl v on v.vendor_id = sa.vendor_id where s.sec_id = "+school_code+" order by rank asc");
while(rs1.next()){
	total_records = total_records + 1;
}
Float total_page = (total_records/ Float.parseFloat(limit));

  // Returns all employees (active and terminated) as json.
  response.setContentType("application/json");
%>
{
	"total_records" : <%=total_records %>,
	"total_page" : <%=(int) Math.ceil(total_page) %>,
	"record_per_page": <%=limit %>
}