package com.ztev.fipmis.baseinfo;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import com.ztev.fipmis.DefaultJdbcBaseDao;
import com.ztev.fipmis.StringUtils;
import com.ztev.pagination.Page;
import com.ztev.pagination.PageInfo;
import com.ztev.pagination.PageParameter;
/**
* 灌区数据访问。
* @author 朱志国
* @modified 朱志国
*/
@Repository
public class PrnmsrDao extends DefaultJdbcBaseDao{
/**
* 查询灌区信息并分页显示。
* @param pp 分页参数。
* @param prnmsrBean 查询条件。
* @return 当前页的数据。
*/
public PageInfo findPage(PageParameter pp, PrnmsrBean prnmsrBean, String adcdCodes)
{
//拼接SQL语句
StringBuffer sb = new StringBuffer();
sb.append("select a.ennmcd, a.ennm, b.dsc, a.irType, a.sRiver, a.irScope, a.adunnm, a.irArea " +
"from TB0001_prnmsr a left join adcd_b b on a.adcd = b.adcd where 1=1 ");
if(prnmsrBean!=null){
if(StringUtils.isNotBlank(prnmsrBean.getEnnmcd())){
sb.append(" and a.ennmcd like '"+prnmsrBean.getEnnmcd()+"%'");
}
if(StringUtils.isNotBlank(prnmsrBean.getEnnm())){
sb.append("and a.ennm like '"+prnmsrBean.getEnnm()+"'");
}
if(StringUtils.isNotBlank(adcdCodes)){
sb.append("and a.adcd in(").append(adcdCodes).append(")");
}
}
Page page = getPagination();
page.setPageParameter(pp);
page.setDataFromSql(sb.toString());
RowMapper<PrnmsrBean> rm = new BeanPropertyRowMapper<PrnmsrBean>(PrnmsrBean.class);
return page.execute(rm);
}// end findPage
/**
* 统计当前行政区划下灌区信息
* @param irCurrent
* @param adcdCodes
* @return
*/
public List<Map<String, Object>> statAll(String... adcdCodes){
//拼接SQL语句
StringBuffer sql = new StringBuffer();
String adcdSql = "";
//所在行政区域
if(adcdCodes != null && adcdCodes.length != 0){
String codesStr = StringUtils.join(adcdCodes,",");
if(StringUtils.isNotBlank(codesStr)){
adcdSql = " and B.adcd in ("+codesStr+")";
}
}
sql.append("select * from (");
//全统计
sql.append(" select '0' as irArea,count(*) as rows ");
sql.append(" from TB0001_prnmsr as A,adcd_b as B where A.adcd = B.adcd");
sql.append(adcdSql);
sql.append(" union ");
//规模分类统计
sql.append(" select isnull(A.irArea,'5') as irArea,count(*) as rows ");
sql.append(" from TB0001_prnmsr as A,adcd_b as B where A.adcd = B.adcd");
sql.append(adcdSql);
sql.append(" group by A.irArea");
sql.append(" ) as C order by C.irArea");
List<Map<String, Object>> stats = getSimpleJdbcTemplate().queryForList(sql.toString());
//全统计行数为0,则清空结果集
if(stats.size() == 1 && (Integer)stats.get(0).get("rows") == 0)stats.clear();
return stats;
}
/**
* 插入一个灌区信息。
* @param prnmsrBean 灌区信息。
* @return 灌区的唯一标识。
*/
public Integer insert(PrnmsrBean prnmsrBean) {
SimpleJdbcInsert sji = newSimpleJdbcInsert("TB0001_prnmsr");
int num = sji.execute(new BeanPropertySqlParameterSource( prnmsrBean ));
return num;
}//end insert
/**
* 判断是否存在指定灌区。
* @param ennmcd 灌区标识。
* @return 如果存在返回true,否则返回false。
*/
public boolean exist(String ennmcd) {
String sql = "select count(ennmcd) from TB0001_prnmsr where ennmcd = ?";
return getSimpleJdbcTemplate().queryForInt(sql, ennmcd) > 0;
}
/**
* 根据灌区标识查找一个灌区的信息。
* @param ennmcd 灌区唯一标识。
* @return 灌区信息。
*/
public PrnmsrBean find(String ennmcd) {
String sql = "select * from TB0001_prnmsr where ennmcd = ? ";
RowMapper<PrnmsrBean> rm = new BeanPropertyRowMapper<PrnmsrBean>(PrnmsrBean.class);
return getSimpleJdbcTemplate().queryForObject(sql, rm, ennmcd);
}//end find
/**
* 根据灌区标识查找一个灌区的信息。
* @param ennmcd 灌区唯一标识。
* @return 灌区信息。
*/
public MapView findMapView(String ennmcd) {
MapView map=new MapView();
String sql = "select top 1 a.ennm as irName,a.irType,(select tPopulation from tb_ircurrent_status c where c.ennmcd=a.ennmcd) as tPopulation,(select effectArea from tb_ircurrent_status c where c.ennmcd=a.ennmcd) as effectArea,(select designArea from tb_ircurrent_status c where c.ennmcd=a.ennmcd) as designArea,(select layYear from tb_irbuild_layout c where c.ennmcd=a.ennmcd) as layYear,(select layMoney from tb_irbuild_layout c where c.ennmcd=a.ennmcd) as layMoney,(select top 1 source from tb_capital_b b,tb_capital_use c where b.serialNo =c.serialNo and c.pId=(select pId from tb_project_b where ennmcd=a.ennmcd)) as layMoneyFrom from TB0001_prnmsr a where ennmcd =? ";
RowMapper<MapView> rm = new BeanPropertyRowMapper<MapView>(MapView.class);
map=getSimpleJdbcTemplate().queryForObject(sql, rm, ennmcd);
sql="select pId from tb_project_b where ennmcd=?";
RowMapper<ProjectBean> rm1= new BeanPropertyRowMapper<ProjectBean>(ProjectBean.class);
List<ProjectBean> list=getSimpleJdbcTemplate().query(sql,rm1,ennmcd);
for(ProjectBean bean:list){
map.getMutimedia().add(bean.getpId());
}
return map;
}//end find
/**
* 更新灌区信息。
* @param prnmsrBean 灌区信息。
* @return 更新的行数。
*/
public int update(PrnmsrBean prnmsrBean)
{
String sql = "update TB0001_prnmsr set ennmcd=:ennmcd,ennm=:ennm, "
+"adcd=:adcd,irType=:irType,irArea=:irArea,sRiver=:sRiver,irScope=:irScope,adunnm=:adunnm where ennmcd=:ennmcd";
BeanPropertySqlParameterSource bpsq = new BeanPropertySqlParameterSource(prnmsrBean);
return getSimpleJdbcTemplate().update(sql, bpsq);
}
/**
* 删除指定的灌区信息。
* @param ennmcd 灌区唯一标识。
* @return 删除的行数。
*/
public int delete(String ennmcd) {
String sql = "delete from TB0001_prnmsr where ennmcd=? ";
return getSimpleJdbcTemplate().update(sql, ennmcd);
}//end delete
/**
* 得到所有的灌区信息。
* @return 所有的灌区信息。
*/
public List<PrnmsrBean> findAll() {
String sql = "select * from TB0001_prnmsr order by ennmcd ";
RowMapper<PrnmsrBean> rm = new BeanPropertyRowMapper<PrnmsrBean>(PrnmsrBean.class);
return getSimpleJdbcTemplate().query(sql, rm);
}
/**
* 根据传入的参数查询灌区信息。
* @return 所查询到的灌区信息。
*/
public List<PrnmsrBean> findAll(List<AdcdBean> filterAdcd)
{
String adcdStr = "";
String sql = "";
if(filterAdcd != null && filterAdcd.size() > 0)
{
for(int i = 0; i < filterAdcd.size(); i++)
{
AdcdBean adcdBean = new AdcdBean();
adcdBean = (AdcdBean)filterAdcd.get(i);
if(i == 0)
{
adcdStr = adcdBean.getAdcd();
}
else
{
adcdStr += ", " + adcdBean.getAdcd();
}
}
}
//System.out.println(adcdStr);
if(adcdStr == null || adcdStr.equals("") || adcdStr.length() <= 0)
{
sql = "select * from TB0001_prnmsr order by ennmcd ";
}
else
{
sql = "select * from TB0001_prnmsr where adcd in ("+adcdStr+") order by ennmcd ";
}
RowMapper<PrnmsrBean> rm = new BeanPropertyRowMapper<PrnmsrBean>(PrnmsrBean.class);
return getSimpleJdbcTemplate().query(sql, rm);
}
public List<PrnmsrBean> findAll(String adcd)
{
//System.out.println(adcd);
String sql = "";
if(adcd != null && adcd.length() > 0)
{
if(!adcd.substring(4, 6).equals("00"))
{
sql = "select * from TB0001_prnmsr where adcd = '" + adcd + "' order by ennmcd ";
}
else
{
if(!adcd.substring(2, 4).equals("00"))
{
sql = "select * from TB0001_prnmsr where adcd like '" + adcd.subSequence(0, 4) + "%' order by ennmcd ";
}
else
{
sql = "select * from TB0001_prnmsr where adcd like '" + adcd.subSequence(0, 2) + "%' order by ennmcd ";
}
}
}
else
{
sql = "select * from TB0001_prnmsr order by ennmcd ";
}
//System.out.println(adcdStr);
RowMapper<PrnmsrBean> rm = new BeanPropertyRowMapper<PrnmsrBean>(PrnmsrBean.class);
return getSimpleJdbcTemplate().query(sql, rm);
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
PrnmsrDao prnmsrDao = new PrnmsrDao();
List<PrnmsrBean> result = prnmsrDao.findAll("361027");
System.out.println(result.size());
}
}