You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
205 lines
15 KiB
205 lines
15 KiB
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;
|
|
}
|
|
|
|
|
|
}
|