PGR_Complaints/service/AgeAnalysisDaskboards.java

package in.gov.chennaicorporation.gccoffice.pgr.service;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import in.gov.chennaicorporation.gccoffice.pgr.dbconnection.DBConnection;
import in.gov.chennaicorporation.gccoffice.pgr.dto.DtoAgeAnalysisDaskboard;

@Service
public class AgeAnalysisDaskboards {
	
	@Autowired
	DBConnection dbcon;
	
	public String sFrDate ="";
	public String sToDate ="";
	
	
	public List<DtoAgeAnalysisDaskboard> getAlldtoageanalysisDaskboard() {
	    List<DtoAgeAnalysisDaskboard> complaintdept = new ArrayList<>();
	    Connection con = DBConnection.getConnection();
	    Statement stmt = null;
	    ResultSet rs = null;

	    try {
	        stmt = con.createStatement();
	        rs = stmt.executeQuery(getdtoageanalysisdaskboardQuery());

	        while (rs.next()) {
	        	DtoAgeAnalysisDaskboard dtoageanalysisdaskboard = new DtoAgeAnalysisDaskboard();
	        	dtoageanalysisdaskboard.setId(rs.getString("id_dept"));
	        	dtoageanalysisdaskboard.setName(rs.getString("dept_name"));
	         complaintdept.add(dtoageanalysisdaskboard);
	        }

	        return complaintdept;
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        try {
	            if (rs != null) {
	                rs.close();
	            }
	            if (stmt != null) {
	                stmt.close();
	            }
	            if (con != null) {
	                con.close();
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }

	    return Collections.emptyList(); // Return an empty list if there's an error
	}
	
	private String getdtoageanalysisdaskboardQuery() {
	    return  "SELECT id_dept, dept_name "
	    		+ "FROM EG_DEPARTMENT@ERP "
	    		+ "WHERE NVL(dept_name, '0') NOT LIKE 'F%' AND dept_name IS NOT NULL "
	    		+ "ORDER BY id_dept";
	}

	public String getgenlysisdaskboardQuery(String sFrDate,String sToDate,String DEPT_NAME) {
	    String sQry = "SELECT c.dept_name, a.deptid, a.days_90, a.days_90_pen, a.days_90_reopen, "
	            + "a.days_61_90, a.days_61_90_pen, a.days_61_90_reopen, "
	            + "a.days_31_60, a.days_31_60_pen, a.days_31_60_reopen, "
	            + "a.days_16_30, a.days_16_30_pen, a.days_16_30_reopen, "
	            + "a.days_0_15, a.days_0_15_pen, a.days_0_15_reopen, "
	            + "a.Total, a.Total_pen, a.Total_reopen "
	            + "FROM ( "
	            + "SELECT "
	            + "a.deptid, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 90 then 1 end), '0') AS days_90, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 90 AND b.COMPLAINTSTATUSID IN (2, 3, 4) then 1 end), '0') AS days_90_pen, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 90 AND b.COMPLAINTSTATUSID IN (9) then 1 end), '0') AS days_90_reopen, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 60 AND sysdate - a.complaintdate <= 90 then 1 end), '0') AS days_61_90, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 60 AND sysdate - a.complaintdate <= 90 AND b.COMPLAINTSTATUSID IN (2, 3, 4) then 1 end), '0') AS days_61_90_pen, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 60 AND sysdate - a.complaintdate <= 90 AND b.COMPLAINTSTATUSID IN (9) then 1 end), '0') AS days_61_90_reopen, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 30 AND sysdate - a.complaintdate <= 60 then 1 end), '0') AS days_31_60, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 30 AND sysdate - a.complaintdate <= 60 AND b.COMPLAINTSTATUSID IN (2, 3, 4) then 1 end), '0') AS days_31_60_pen, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 30 AND sysdate - a.complaintdate <= 60 AND b.COMPLAINTSTATUSID IN (9) then 1 end), '0') AS days_31_60_reopen, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 15 AND sysdate - a.complaintdate <= 30 then 1 end), '0') AS days_16_30, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 15 AND sysdate - a.complaintdate <= 30 AND b.COMPLAINTSTATUSID IN (2, 3, 4) then 1 end), '0') AS days_16_30_pen, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 15 AND sysdate - a.complaintdate <= 30 AND b.COMPLAINTSTATUSID IN (9) then 1 end), '0') AS days_16_30_reopen, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 0 AND sysdate - a.complaintdate <= 15 then 1 end), '0') AS days_0_15, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 0 AND sysdate - a.complaintdate <= 15 AND b.COMPLAINTSTATUSID IN (2, 3, 4) then 1 end), '0') AS days_0_15_pen, "
	            + "nvl(sum(case when sysdate - a.complaintdate > 0 AND sysdate - a.complaintdate <= 15 AND b.COMPLAINTSTATUSID IN (9) then 1 end), '0') AS days_0_15_reopen, "
	            + "nvl(count(*), '0') AS Total, "
	            + "nvl(count(case when b.COMPLAINTSTATUSID IN (2, 3, 4) then 1 end), '0') AS Total_pen, "
	            + "nvl(count(case when b.COMPLAINTSTATUSID IN (9) then 1 end), '0') AS Total_reopen "
	            + "FROM eggr_complaintdetails@erp a, eggr_redressaldetails@erp b "
	            + "WHERE a.complaintid = b.complaintid ";

	    int iRptID = 0;
	    if (iRptID == 1) sQry += " AND nvl(RECEIVINGCENTERID,0) = 1 ";
	    else if (iRptID == 2) sQry += " AND nvl(RECEIVINGCENTERID,0) != 1 ";

	    String sComplainttype = "0";
	    if (sComplainttype.equals("52,1,22,32,57,59,70,18,69,7,71,19,58,75"))
	        sQry += " AND complainttype NOT IN(" + sComplainttype + ") ";
	    else if (!sComplainttype.equals("0")) sQry += " AND complainttype IN(" + sComplainttype + ") ";

	    if (!sFrDate.equals("")) {
	        if (sToDate == null || sToDate.equals("")) sToDate = sFrDate;
	        sQry += " AND trunc(a.complaintdate) BETWEEN to_date('" + sFrDate + "','yyyy/mm/dd') AND to_date('" + sToDate + "','yyyy/mm/dd') ";
	    }

	    if (!sComplainttype.equals("0")) {
	        sQry += " AND a.deptid >= 33 ";
	    }

	    if (DEPT_NAME != null && !DEPT_NAME.equals("0")) {
	        // Add condition to filter by dept_name
	        sQry += " AND EXISTS (SELECT 1 FROM EG_DEPARTMENT@ERP c WHERE a.deptid = c.id_dept AND (NVL(c.DEPT_NAME, 0) NOT LIKE 'F%' AND c.DEPT_NAME IS NOT NULL) AND c.dept_name = :deptName) ";
	    }

	    sQry += " AND a.complaintdate >= '25-oct-2010' GROUP BY a.deptid) a, EG_DEPARTMENT@ERP c " +
	            "WHERE a.deptid = c.id_dept AND (NVL(DEPT_NAME, 0) NOT LIKE 'F%' AND DEPT_NAME IS NOT NULL)";

	    System.out.println("ageanalysisquery : "+ sQry);
	    return sQry;
	}


	public List<Map<String, Object>> getdashboardquery(String sFrDate,String sTodate,String DEPT_NAME) {
	    Connection con = DBConnection.getConnection();
	    try {
	        PreparedStatement pstmt = con.prepareStatement(getgenlysisdaskboardQuery(sFrDate,sTodate,DEPT_NAME));

	        if (DEPT_NAME != null && !DEPT_NAME.equals("0")) {
	            // Set the parameter value for :deptName
	            pstmt.setString(1, DEPT_NAME);
	        }

	        System.out.println("sQry : " + pstmt.toString()); // Check the generated SQL query

	        ResultSet rs3 = pstmt.executeQuery();

	        List<Map<String, Object>> rows = new ArrayList<>();

	        while (rs3.next()) {
	            Map<String, Object> row = new HashMap<>();
	            ResultSetMetaData metaData = rs3.getMetaData();
	            int columnCount = metaData.getColumnCount();

	            for (int i = 1; i <= columnCount; i++) {
	                String columnName = metaData.getColumnName(i);
	                Object columnValue = rs3.getObject(i);
	                System.out.println(columnName + " : " + columnValue);
	                row.put(columnName, columnValue);
	            }

	            rows.add(row);
	        }

	        return rows;
	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        
	        try {
	            con.close();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }

	    return null;
	}

}