PGR_Complaints/service/ZoneWiseDaskboards.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.util.ArrayList;
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;

@Service
public class ZoneWiseDaskboards {
	
	@Autowired
	DBConnection dbcon;
	
	public String sFrDate ;
	public String sToDate ;
	
    public String getzonedaskboardQuery(String sFrDate,String sToDate,String DEPTID) {
		String sQry=  "select * from (SELECT deptid , "
				+"nvl(sum( case when complainttype in (52,146) then 1 end),0) as \"NonBURSTLT\" , "
				+"nvl(sum( case when complainttype in (1,97) then 1 end),0) as \"REOFGAR\" , "
				+"nvl(sum( case when complainttype in (22,32,57,59,70,113,114,130,145) then 1 end),0) as \"ROAD\" , "
				+"nvl(sum( case when complainttype in (18,118) then 1 end),0) as  \"MOSQUITE\" , "
				+"nvl(sum( case when complainttype in (69,138) then 1 end),0) as  \"WATER\" , "
				+"nvl(sum( case when complainttype in (7,99) then 1 end),0) as \"REOFDEB\" , "
				+"nvl(sum( case when complainttype in (71,152) then 1 end),0) as \"TREE\" , "
				+"nvl(sum( case when complainttype in (19,119) then 1 end),0) as \"DOGS\" , "
				+"nvl(sum( case when complainttype in (75,181) then 1 end),0) as \"ENCMTN\" , "
				+"nvl(sum( case when complainttype in (58,112) then 1 end),0) as \"POT\" , "
				+"nvl(sum( case when complainttype in (43,166) then 1 end),0) as \"PTAX\" , "
				+"nvl(sum( case when complainttype not in (52,1,22,32,57,59,70,18,69,7,71,19,75,58,43,146,97,113,114,130,145,118,138,99,152,119,181,112,166) then 1 end),0) as \"OTHERS\" , "
				+"COUNT(*) Total  FROM eggr_complaintdetails@erp a, eggr_redressaldetails@erp b WHERE ";
		
		
				int iRptID = 0;
				if ( iRptID == 1 ) {
					sQry += " nvl(RECEIVINGCENTERID,0)=1 and ";
				} else if ( iRptID == 2 ) {
					sQry += " nvl(RECEIVINGCENTERID,0) !=1 and ";
				}
				//else sQry += " nvl(RECEIVINGCENTERID,0) !=3 and ";
				if ( !sFrDate.equals("") )
				{
					if (sToDate == null || sToDate.equals("") ) {
						sToDate = sFrDate;
					}
					//sQry += " trunc(complaintdate)=to_date('"+ sFrDate +"','dd/mm/yyyy') and ";
					sQry += " trunc(complaintdate) between to_date('"+ sFrDate +"','yyyy/mm/dd') and to_date('"+ sToDate +"','yyyy/mm/dd') and ";
				}
				
				sQry += " a.complaintid=b.complaintid and b.COMPLAINTSTATUSID IN(2,3,4,9) and TRUNC(COMPLAINTDATE) >= trunc(sysdate-730)"
						+"AND a.deptid = " + DEPTID  
						+"group by a.deptid ) a inner join EG_DEPARTMENT@ERP c on a.deptid=c.id_dept ";
				sQry+= " left outer join (  SELECT deptid , "
						+"nvl(sum( case when sysdate-complaintdate>90 and b.COMPLAINTSTATUSID IN(2,3,4,9) then 1 end),'0') as days_90_pen , "
						+"nvl(sum( case when sysdate-complaintdate>60 and sysdate-complaintdate<=90 and b.COMPLAINTSTATUSID IN(2,3,4,9) then 1 end),'0') as days_61_90_pen , "
						+"nvl(sum( case when sysdate-complaintdate>30 and sysdate-complaintdate<=60 and b.COMPLAINTSTATUSID IN(2,3,4,9) then 1 end),'0') as days_31_60_pen ,  "
						+"nvl(sum( case when sysdate-complaintdate>15 and sysdate-complaintdate<=30 and b.COMPLAINTSTATUSID IN(2,3,4,9) then 1 end),'0') as days_16_30_pen ,  "
						+"nvl(sum( case when sysdate-complaintdate<=15 and b.COMPLAINTSTATUSID IN(2,3,4,9) then 1 end),'0') as days_0_15_pen , "
						+"nvl(count(case when b.COMPLAINTSTATUSID IN(2,3,4,9) then 1 end),'0') as Total_pen "
						+"FROM eggr_complaintdetails@erp a, eggr_redressaldetails@erp b "
						+"WHERE a.complaintid=b.complaintid   and TRUNC(COMPLAINTDATE) >= trunc(sysdate-730) ";
						
				
				if ( iRptID == 1 ) {
					sQry += "and  nvl(RECEIVINGCENTERID,0)=1 ";
				} else if ( iRptID == 2 ) {
							sQry += "and  nvl(RECEIVINGCENTERID,0) !=1  ";
				}
				
				//else sQry += "and  nvl(RECEIVINGCENTERID,0) !=3  ";
				if ( !sFrDate.equals("") ) {
					if (sToDate == null || sToDate.equals("") ) {
						sToDate = sFrDate;
					}
					//sQry += " trunc(complaintdate)=to_date('"+ sFrDate +"','dd/mm/yyyy') and ";
					sQry += "and  trunc(complaintdate) between to_date('"+ sFrDate +"','yyyy/mm/dd') and to_date('"+ sToDate +"','yyyy/mm/dd') ";
				}
				
				if (DEPTID != null && !DEPTID.equals("0")) {
			        sQry += "AND EXISTS (SELECT 1 FROM EG_DEPARTMENT@ERP c WHERE a.deptid = c.id_dept " +
			                "AND (NVL(c.id_dept, 0) NOT LIKE 'F%' AND c.id_dept IS NOT NULL) " +
			                "AND c.id_dept = " + DEPTID + ") ";
			    }
				
				sQry +="group by a.deptid ) d on d.deptid=c.id_dept ";
				sQry +=" where (NVL(DEPT_NAME,0) NOT LIKE 'F%' AND DEPT_NAME IS NOT NULL) AND  a.deptid>=32 "
				+"order by a.deptid";

					
	    System.out.println("Gen Query:" + sQry);
	    return sQry;
	}
	
	public List<Map<String, Object>> getzonewisedaskboardQuery(String sFrDate,String sToDate,String DEPTID) {
		Connection con =DBConnection.getConnection();
//		Statement stmt3;
		
		try {
//			stmt3 = con.createStatement();
			
			String query = getzonedaskboardQuery(sFrDate,sToDate,DEPTID);
			
			PreparedStatement pstmt = con.prepareStatement(query);

	        if (query.contains(":deptID") && DEPTID != null && !DEPTID.equals("0")) {
	            pstmt.setString(1, DEPTID);
	        }

			
			System.out.println( "checkingzonewise...");
			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();
		}
		return null;

}

}