CM_Works_Dashboard_Files/WorksService.java

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

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;
import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestMapping;

@Service
public class WorksService {
	
  private JdbcTemplate jdbcTemplate;
  
  
//  @Autowired
//	public void setDataSourcemysql(@Qualifier("mysqlAppDataSource") DataSource dataSource) {		
//		this.jdbcTemplate = new JdbcTemplate(dataSource);	}
  
  @Autowired
	public void setDataSource(@Qualifier("oracleDataSource") DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
  
  @Transactional
	public List<Map<String, Object>> getCombinedCmDashboardReports(String zonename) {
	    String sqlQuery;
	    Object[] params;

	    if (zonename != null && !zonename.equalsIgnoreCase("all")) {
	        sqlQuery = "SELECT cm.schemename AS name, " +
	                   "COUNT(*) AS count, " +
	                   "SUM(cm.valueofwork) AS total_value_of_work, " +
	                   "'Scheme' AS type " +
	                   "FROM gcc_apps.cmworks cm " +
	                   "WHERE cm.zonename = ? " +
	                   "GROUP BY cm.schemename " +
	                   "UNION ALL " +
	                   "SELECT cm.workcategory AS name, " +
	                   "COUNT(*) AS count, " +
	                   "SUM(cm.valueofwork) AS total_value_of_work, " +
	                   "'Category' AS type " +
	                   "FROM gcc_apps.cmworks cm " +
	                   "WHERE cm.zonename = ? " +
	                   "GROUP BY cm.workcategory";
	        params = new Object[]{zonename, zonename};
	    } else {
	        sqlQuery = "SELECT cm.schemename AS name, " +
	                   "COUNT(*) AS count, " +
	        		   "SUM(cm.valueofwork) AS total_value_of_work, "+
	                   "'Scheme' AS type " +
	                   "FROM gcc_apps.cmworks cm " +
	                   "GROUP BY cm.schemename " +
	                   "UNION ALL " +
	                   "SELECT cm.workcategory AS name, " +
	                   "COUNT(*) AS count, " +
	                   "SUM(cm.valueofwork) AS total_value_of_work,"+
	                   "'Category' AS type " +
	                   "FROM gcc_apps.cmworks cm " +
	                   "GROUP BY cm.workcategory";
	        params = new Object[]{};
	    }

	    return jdbcTemplate.queryForList(sqlQuery, params);
	}


	 @Transactional
	    public List<Map<String, Object>> getRegionCounts() {
	        String sqlQuery = "SELECT regionname, COUNT(*) AS region_count FROM gcc_apps.cmworks GROUP BY regionname";
	        return jdbcTemplate.queryForList(sqlQuery);
	    }

	 @Transactional
	    public List<Map<String, Object>> getZoneCounts() {
	        String sqlQuery = "SELECT zonename, COUNT(*) AS zone_count FROM gcc_apps.cmworks GROUP BY zonename";
	        return jdbcTemplate.queryForList(sqlQuery);
	    }
	 
	 public List<Map<String,Object>> getImageUrl(){
		 String sqlQuery = "SELECT abs_est_number ,MAX(imageurl) AS imageurl FROM gcc_apps.cmworks GROUP BY abs_est_number";
		 return jdbcTemplate.queryForList(sqlQuery);
	 }


	public List<Map<String, Object>> getCmDashboardAllReports() {
		String sqlQuery ="SELECT * FROM gcc_apps.cmworks";
     List<Map<String, Object>> result = jdbcTemplate.queryForList(sqlQuery);
		
		return result;
	}
	
	public Map<String, Object> getCMDetails(int id) {
        String sql = "SELECT * FROM gcc_apps.cmworks WHERE data_id = ?";
        return jdbcTemplate.queryForMap(sql, id);
    }
	
	@Transactional
	public List<Map<String,Object>> getTradelicenseReports(String divisionName){
		String sqlquery = "";
		
		
		sqlquery ="select "
				+ "region.name region, "
				+ "zone.name Zone,boun.name Ward,NVL(new_lic,0) NEW_LIC,NVL(new_lic_amt,0) NEW_LIC_AMT,NVL(curr_lic_ren,0) REN_WITH_PENALTY, "
				+ "NVL(curr_lic_ren_amt,0) REN_WITH_PENALTY_AMT ,NVL(curr_lic_ren_before,0)  REN_WITH_OUT_PENALTY, "
				+ "NVL(curr_lic_ren_before_amt,0) REN_WITH_OUT_PENALTY_AMT,NVL(nonrnwdcnt,0) NON_RENEWED, NVL(NONRNWDCNTAMT ,0) AS NONRNWDCNTAMT "
				+ "from eg_boundary@erp boun left outer join "
				+ "    (SELECT ward_id ,SUM(NVL(new_lic ,0)) AS new_lic ,SUM(NVL(new_lic_amt ,0)) AS new_lic_amt ,SUM(NVL(totalCnt ,0)) AS totalCnt , "
				+ "    SUM(NVL(NONRNWDCNT ,0)) AS NONRNWDCNT,SUM(NVL(NONRNWDCNTAMT ,0)) AS NONRNWDCNTAMT "
				+ "    FROM (SELECT "
				+ "    CASE WHEN  lic.ID_INSTALLMENT =ins.ID_INSTALLMENT AND ins.ID_INSTALLMENT = (SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) THEN 1 ELSE 0 END AS new_lic , "
				+ "    CASE WHEN lic.ID_INSTALLMENT =ins.ID_INSTALLMENT AND ins.ID_INSTALLMENT = (SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) THEN lic.total_fee ELSE 0 END AS new_lic_amt , "
				+ "    CASE WHEN lic.id is not null AND LIC.ID_INSTALLMENT <(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) THEN 1 ELSE 0 END AS totalCnt , "
				+ "    CASE WHEN LIC.ID_INSTALLMENT <(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) AND LIC.ID NOT IN (SELECT R1.LICENSE_ID FROM egtl_renewal@erp R1 where R1.ID_INSTALLMENT=(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and R1.paid_status =1) THEN 1 ELSE 0 END AS NONRNWDCNT "
				+ "    ,CASE WHEN LIC.ID_INSTALLMENT <(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) AND LIC.ID NOT IN (SELECT R1.LICENSE_ID FROM egtl_renewal@erp R1 where R1.ID_INSTALLMENT=(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and R1.paid_status =1) THEN lic.total_fee ELSE 0 END AS NONRNWDCNTAMT "
				+ "    ,trade.division_id ward_id from EGTL_LICENSE@erp lic "
				+ "    inner join EGTL_TRADE@erp trade on trade.ID =lic.TRADE_ID "
				+ "    inner join eg_installment_master@erp ins on ins.id_installment = lic.id_installment "
				+ "where trade.active=1 and LIC.STATUS IN (2 ,4 ,8)) group by ward_id) new on boun.id_bndry=new.ward_id "
				+ "left outer join (select trade.division_id ward_id , "
				+ "sum(CASE WHEN re.ID_INSTALLMENT <(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date and re.renewed_Date <= ins.end_Date THEN 1 ELSE 0 END) old_lic_ren, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT =(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date THEN 1 ELSE 0 END) curr_lic_ren, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT =(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date < ins.start_Date THEN 1 ELSE 0 END) curr_lic_ren_before, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT >(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date and re.renewed_Date <= ins.end_Date THEN 1 ELSE 0 END) lic_ren_adv, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT <(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date and re.renewed_Date <= ins.end_Date THEN re.total_fee ELSE 0 END) old_lic_ren_amt, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT =(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date THEN re.total_fee ELSE 0 END) curr_lic_ren_amt, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT =(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date < ins.start_Date THEN re.total_fee ELSE 0 END) curr_lic_ren_before_amt, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT >(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date and re.renewed_Date <= ins.end_Date THEN re.total_fee ELSE 0 END) lic_ren_adv_amt "
				+ "from egtl_renewal@erp re ,EGTL_LICENSE@erp li ,EGTL_TRADE@erp trade ,eg_installment_master@erp ins "
				+ "where trade.ID =li.TRADE_ID "
				+ "and ins.ID_INSTALLMENT=(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) "
				+ "and li.STATUS IN (2 ,4 ,8) AND re.paid_status = 1 and li.id = re.license_id group by  trade.division_id) ren on ren.ward_id=boun.id_bndry "
				+ "left outer join eg_boundary@erp zone on zone.id_bndry=boun.parent "
				+ "left outer join eg_boundary@erp region on region.id_bndry=zone.parent "
				+ "where boun.id_bndry_type=3 and boun.name like 'N%' ";

		//and boun.name='"+divisionName+"' order by zone.name,boun.name"
		
		   // Append division name filtering based on the selected division
	    if (divisionName != null && !divisionName.isEmpty() && !"All".equalsIgnoreCase(divisionName)) {
	        sqlquery += "and boun.name = '"+divisionName+"' ";
	    }

	    sqlquery += "order by zone.name, boun.name";
		
		List<Map<String, Object>> result = jdbcTemplate.queryForList(sqlquery);
		
		return result;
	}
	//and zone.name='N01' and boun.name='N009'
	
	@Transactional
	public List<Map<String,Object>> getTradelicenseReportsAll(){
		String sqlquery = "";
		
		
		sqlquery ="select "
				+ "region.name region, "
				+ "zone.name Zone,boun.name Ward,NVL(new_lic,0) NEW_LIC,NVL(new_lic_amt,0) NEW_LIC_AMT,NVL(curr_lic_ren,0) REN_WITH_PENALTY, "
				+ "NVL(curr_lic_ren_amt,0) REN_WITH_PENALTY_AMT ,NVL(curr_lic_ren_before,0)  REN_WITH_OUT_PENALTY, "
				+ "NVL(curr_lic_ren_before_amt,0) REN_WITH_OUT_PENALTY_AMT,NVL(nonrnwdcnt,0) NON_RENEWED, NVL(NONRNWDCNTAMT ,0) AS NONRNWDCNTAMT "
				+ "from eg_boundary@erp boun left outer join "
				+ "    (SELECT ward_id ,SUM(NVL(new_lic ,0)) AS new_lic ,SUM(NVL(new_lic_amt ,0)) AS new_lic_amt ,SUM(NVL(totalCnt ,0)) AS totalCnt , "
				+ "    SUM(NVL(NONRNWDCNT ,0)) AS NONRNWDCNT,SUM(NVL(NONRNWDCNTAMT ,0)) AS NONRNWDCNTAMT "
				+ "    FROM (SELECT "
				+ "    CASE WHEN  lic.ID_INSTALLMENT =ins.ID_INSTALLMENT AND ins.ID_INSTALLMENT = (SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) THEN 1 ELSE 0 END AS new_lic , "
				+ "    CASE WHEN lic.ID_INSTALLMENT =ins.ID_INSTALLMENT AND ins.ID_INSTALLMENT = (SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) THEN lic.total_fee ELSE 0 END AS new_lic_amt , "
				+ "    CASE WHEN lic.id is not null AND LIC.ID_INSTALLMENT <(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) THEN 1 ELSE 0 END AS totalCnt , "
				+ "    CASE WHEN LIC.ID_INSTALLMENT <(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) AND LIC.ID NOT IN (SELECT R1.LICENSE_ID FROM egtl_renewal@erp R1 where R1.ID_INSTALLMENT=(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and R1.paid_status =1) THEN 1 ELSE 0 END AS NONRNWDCNT "
				+ "    ,CASE WHEN LIC.ID_INSTALLMENT <(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) AND LIC.ID NOT IN (SELECT R1.LICENSE_ID FROM egtl_renewal@erp R1 where R1.ID_INSTALLMENT=(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and R1.paid_status =1) THEN lic.total_fee ELSE 0 END AS NONRNWDCNTAMT "
				+ "    ,trade.division_id ward_id from EGTL_LICENSE@erp lic "
				+ "    inner join EGTL_TRADE@erp trade on trade.ID =lic.TRADE_ID "
				+ "    inner join eg_installment_master@erp ins on ins.id_installment = lic.id_installment "
				+ "where trade.active=1 and LIC.STATUS IN (2 ,4 ,8)) group by ward_id) new on boun.id_bndry=new.ward_id "
				+ "left outer join (select trade.division_id ward_id , "
				+ "sum(CASE WHEN re.ID_INSTALLMENT <(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date and re.renewed_Date <= ins.end_Date THEN 1 ELSE 0 END) old_lic_ren, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT =(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date THEN 1 ELSE 0 END) curr_lic_ren, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT =(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date < ins.start_Date THEN 1 ELSE 0 END) curr_lic_ren_before, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT >(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date and re.renewed_Date <= ins.end_Date THEN 1 ELSE 0 END) lic_ren_adv, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT <(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date and re.renewed_Date <= ins.end_Date THEN re.total_fee ELSE 0 END) old_lic_ren_amt, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT =(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date THEN re.total_fee ELSE 0 END) curr_lic_ren_amt, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT =(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date < ins.start_Date THEN re.total_fee ELSE 0 END) curr_lic_ren_before_amt, "
				+ "sum(CASE WHEN re.ID_INSTALLMENT >(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) and re.renewed_Date >= ins.start_Date and re.renewed_Date <= ins.end_Date THEN re.total_fee ELSE 0 END) lic_ren_adv_amt "
				+ "from egtl_renewal@erp re ,EGTL_LICENSE@erp li ,EGTL_TRADE@erp trade ,eg_installment_master@erp ins "
				+ "where trade.ID =li.TRADE_ID "
				+ "and ins.ID_INSTALLMENT=(SELECT ID_INSTALLMENT FROM EG_INSTALLMENT_MASTER@erp WHERE ID_MODULE = 185 AND START_DATE < SYSDATE AND END_DATE > SYSDATE) "
				+ "and li.STATUS IN (2 ,4 ,8) AND re.paid_status = 1 and li.id = re.license_id group by  trade.division_id) ren on ren.ward_id=boun.id_bndry "
				+ "left outer join eg_boundary@erp zone on zone.id_bndry=boun.parent "
				+ "left outer join eg_boundary@erp region on region.id_bndry=zone.parent "
				+ "where boun.id_bndry_type=3 and boun.name like 'N%' order by zone.name,boun.name";
		//region.name='North Region'
		List<Map<String, Object>> result = jdbcTemplate.queryForList(sqlquery);
		
		return result;
	}
	
	
}