PGR_Complaints/service/QaQcReports.java

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

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
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 QaQcReports {
	
	@Autowired
	DBConnection dbcon;
	

	public String getqaqcQuery(String sFrDate, String sToDate) {
		String sQry=  " select cd.COMPLAINTNUMBER,dep.DEPT_NAME,ct.COMPLAINTTYPENAME,"+
			       " TO_CHAR(cd.COMPLAINTDATE,'DD-MM-RRRR') COMPLAINTDATE,"+
			       " TO_CHAR(cd.COMPLAINTDATE,'HH:MI:SS AM') COMPLAINTTIME,"+
				   " cd.COMPLAINTDETAILS,cd.COMPLAINANTFIRSTNAME, cd.MOBILENUMBER AS COMPLAINANTMOBILENUMBER, usr.USER_NAME, usr.extrafield2 as Mobile_number, "+
				   " c.compl_date as COMPLAINTCOMPLETIONDATE, "+
				   " DECODE ( qa.STATUS,null,'Not Attended','Attended') STATUS "+
				   " from EGGR_COMPLAINTDETAILS@erp cd , eg_department@erp dep, EGGR_COMPLAINTTYPES@erp ct, eg_user@erp usr, eggr_qams_store@erp qa, EGGR_REDRESSALDETAILS@erp rd, "+
				   " (SELECT c.redressalid, MAX(c.TIMESTAMP) compl_date "+
				   " FROM eggr_status_tracker@erp c,eggr_redressaldetails@ERP d WHERE c.statusid in(5,10) AND d.COMPLAINTSTATUSID in(5,10) "+
				   " AND d.REDRESSALID=c.redressalid GROUP BY c.redressalid) c "+
				   " where cd.DEPTID=dep.ID_DEPT and cd.COMPLAINTTYPE=ct.COMPLAINTTYPEID  AND rd.COMPLAINTID =cd.COMPLAINTID "+
				   //" and ct.COMPLAINTTYPENAME in ('Removal of garbage', 'Removal of fallen trees', 'Stagnation of water') "+
				   " AND rd.REDRESSALOFFICERID =usr.id_user and qa.FDBK_OBJ_REF =cd.COMPLAINTID(+) and "+
				   " rd.COMPLAINTSTATUSID not  in(2,3,4,9) and rd.redressalid=c.redressalid(+)  and ";

				String sFrTime = "00:00:00 AM";
				String sToTime = "";
				
//				  String sFrDate = ""; 
//				  String sToDate = "";
				 
				if (sFrTime.toUpperCase().equals("00:00:00 AM"))
					{
					sQry+=" TRUNC(cd.COMPLAINTDATE) between TO_DATE('"+sFrDate+"', 'yyyy-MM-dd') and TO_DATE('"+sToDate+"', 'yyyy-MM-dd')   ";
					}
				else
					{
					
					sQry+=" cd.COMPLAINTDATE  between TO_DATE('"+sFrDate+" "+sFrTime+"', 'DD-MM-RRRR hh:mi:ss am') and TO_DATE('"+sToDate+" "+sToTime+ "', 'DD-MM-RRRR hh:mi:ss am')   ";
					}
				
					sQry+="  order by dept_name";
					 System.out.println("qaqcquery:" + sQry);
	return sQry;
	}
	
	
	
	
	
	
	public List<Map<String, Object>> check1(String sFrDate, String sToDate) {
		Connection con =DBConnection.getConnection();
		Statement stmt3;
		try {
			stmt3 = con.createStatement();
			
			
			System.out.println( "checkingqaqc...");
			ResultSet rs3 = stmt3.executeQuery(getqaqcQuery(sFrDate, sToDate));
			

			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);
					
					 if (columnValue instanceof String) {
				            columnValue = HtmlEntityDecoder.convertHtmlEntitiesToUtf8((String) columnValue);
				        }
					
					row.put(columnName, columnValue);
				}

				
				rows.add(row);
			}
			return rows;
		}
		catch (Exception e){
			e.printStackTrace();
		}
		return null;	
		
	}
	
	
	
	


}