plandaoimpl.java

基于Java的地图数据管理软件。使用MySQL数据库管理系统。

				    pd.setAttachFile(rs.getString("attachFile"));
				    pd.setGriphicFile(rs.getString("graphicFile"));
				    pd.setEndTime(CurrentUser.getDateToString(rs.getDate("endTime")));
				    pd.setStartTime(CurrentUser.getDateToString(rs.getDate("startTime")));
				    pd.setInvest(rs.getDouble("investCount"));
				    pd.setMilemeter(rs.getDouble("mileCount"));
				    pd.setNote(rs.getString("note"));
				    pd.setRoadbedWidth(rs.getDouble("roadbedwidth"));
				    pd.setRoadName(rs.getString("roadName"));
				    pd.setRoadSurfaceType(rs.getInt("roadsurfaceType"));
				    pd.setRoadSurfaceWidth(rs.getDouble("roadsurfacewidth"));
				    pd.setTownsId(rs.getString("townsId"));
				    pd.setVillagesId(rs.getString("villagesId"));
				    pd.setVillageType(rs.getString("villageType"));
				    pd.setVillageName(rs.getString("villageName"));
				    pd.setTownName(rs.getString("townName"));
				    planList.add(pd);

				} while ((hasNext = rs.next()) && (--count > 0));
				page = new Page(planList, start, hasNext);
			} else {
				page = Page.EMPTY_PAGE;
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			dbmanager.freeConnection(conn, "DB");
			// dbo.close();
		}

		return page;
	}

	/**
	 * 获取计划统计总数
	 */
	public int getTotalPlanCount(QueryStr qs) {
	       int count=0;
	        DBOperator dbo = new DBOperator();
	        try {
	        	String sqlStr = "select count(planId) as maxCount " + getTotalStr(qs);
	    		dbo.setPrepareStatement(sqlStr);
	            ResultSet rs = dbo.executeQuery();
	            System.out.println(sqlStr);
	            while (rs.next()) {
	            	count ++;
	                }
	           
	            rs.close();
	            return count;
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            dbo.close();
	        }

	        return count;
	}
	
	/**
	 * 统计查询语句
	 */
	public String getTotalStr(QueryStr qs){
		String queryStr = "  from tt_plandetail where planId > 0 " ;
		String [] villageType = qs.getVillageType();
		
		if (qs.getPlanType() != 0)
		    queryStr += " and planType = " + qs.getPlanType();
		if (qs.getJhlx() != 0)
		    queryStr += " and jhlx = " + qs.getJhlx();
		if (qs.getJsxz() != 0)
		    queryStr += " and jsxz = " + qs.getJsxz();
		if (qs.getZyjsnr() != 0)
		    queryStr += " and zyjsnr = " + qs.getZyjsnr();
		if (qs.getJhyear() != 0)
		    queryStr += " and jhyear = " + qs.getJhyear();
		if ( qs.getMilemeter() != null )
			queryStr += " and Milemeter " + qs.getMileStr()+  qs.getMilemeter();
		if ( qs.getProvinceSubsidy() != null )
			queryStr += " and ProvinceSubsidy " + qs.getProStr()+  qs.getProvinceSubsidy();
		if ( qs.getJhztz()!= null )
			queryStr += " and invest " + qs.getZtzStr()+  qs.getJhztz();
		if (qs.getCity() != null && qs.getCity().length()>1)
		{
			queryStr += " and left(townsid,4) = '" + qs.getCity().substring(0,4)+ "'";
		}
		if (qs.getCounty() != null && !qs.getCounty().equals("0"))
			queryStr += " and left(townsId,6) = '" + qs.getCounty().substring(0,6) + "'";
		if (qs.getTownName() != null)
			queryStr += " and townName like '%" + qs.getTownName() + "%'";
		if (qs.getVillageName() != null)
			queryStr += " and villageName like '%" + qs.getVillageName() + "%'";
		for (int i = 0; villageType!= null && i < villageType.length;i++){
			queryStr += " and villageType like '%" + villageType[i] + "%'";
		}
		if (qs.getStartTimeFrom() != null && qs.getStartTimeTo() != null){
			queryStr += " and (startTime between '" + qs.getStartTimeFrom() 
			            + "' and '" + qs.getStartTimeTo()+ "')";
		}
	    if (qs.getEndTimeFrom() != null && qs.getEndTimeTo() != null){
				queryStr += " and (endTime between '" + qs.getEndTimeFrom() 
				            + "' and '" + qs.getEndTimeTo()+ "')";	
		}
	    
	  

	         return queryStr;
	}

	public List getAllPlanList(QueryStr qs ) {
		DBOperator dbo = new DBOperator();
		List planList = new ArrayList();
		try {
			String sqlStr = "select planid,plantype,ProvinceSubsidy,attachFile, graphicFile" +
                      " ,endTime,startTime,invest,milemeter,note,roadbedwidth,roadName,roadsurfaceType, " +
                        "roadsurfacewidth,townsId,villagesId,villageType,villageName,townName , " +
                      "isPKX,isMZDQ,isgmlq,jsxz,jsdj,ql,ljwctz,zytz,zytzcgs,zygz,ssqzc,gndk," +
                      "zyjsnr,qqgzjz,sjjslc,sjwctze,jhlx,txzmc,tjzcmc,jhyear " +  getQueryStr(qs);
			System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				PlanDetail pd = new PlanDetail();
			    pd.setPlanId(rs.getInt("planId"));
			    pd.setPlanType(rs.getInt("planType"));
			    pd.setProvinceSubsidy(rs.getDouble("ProvinceSubsidy"));
			    pd.setAttachFile(rs.getString("attachFile"));
			    pd.setGriphicFile(rs.getString("graphicFile"));
			    pd.setEndTime(CurrentUser.getDateToString(rs.getDate("endTime")));
			    pd.setStartTime(CurrentUser.getDateToString(rs.getDate("startTime")));
			    pd.setInvest(rs.getDouble("invest"));
			    pd.setMilemeter(rs.getDouble("milemeter"));
			    pd.setNote(rs.getString("note"));
			    pd.setRoadbedWidth(rs.getDouble("roadbedwidth"));
			    pd.setRoadName(rs.getString("roadName"));
			    pd.setRoadSurfaceType(rs.getInt("roadsurfaceType"));
			    pd.setRoadSurfaceWidth(rs.getDouble("roadsurfacewidth"));
			    pd.setTownsId(rs.getString("townsId"));
			    String cityCode = rs.getString("townsId").substring(0,4)+"00000000";
			    String countyCode = rs.getString("townsId");
			   // System.out.println("cityCode==" + cityCode + "   countyCode===" + countyCode);
			    String cityName = City.getCityNameByNum(cityCode);
			    String countyName = City.getCountyName(countyCode);
			    pd.setCityName(cityName);
			    pd.setCountyName(countyName);
			    pd.setVillagesId(rs.getString("villagesId"));
			    pd.setVillageType(rs.getString("villageType"));
			    pd.setVillageName(rs.getString("villageName"));
			    pd.setTownName(rs.getString("townName"));
			    
			    //新增字段
			    pd.setIsPKX(rs.getInt("isPKX"));
			    pd.setIsMZDQ(rs.getInt("isMZDQ"));
			    pd.setIsGMLQ(rs.getInt("isgmlq"));
			    pd.setJsxz(rs.getInt("jsxz"));
			    pd.setJsdj(rs.getInt("jsdj"));
			    pd.setQl(rs.getString("ql"));
			    pd.setLjwctz(rs.getDouble("ljwctz"));
			    pd.setZytz(rs.getDouble("zytz"));
			    pd.setZytzcgs(rs.getDouble("zytzcgs"));
			    pd.setZygz(rs.getDouble("zygz"));
			    pd.setSsqzc(rs.getDouble("ssqzc"));
			    pd.setGndk(rs.getDouble("gndk"));
			    pd.setZyjsnr(rs.getInt("zyjsnr"));
			    pd.setSjjslc(rs.getDouble("sjjslc"));
			    pd.setSjwctze(rs.getDouble("sjwctze"));
			    pd.setJhlx(rs.getInt("jhlx"));
			    pd.setTxzmc(rs.getString("txzmc"));
			    pd.setQqgzjz(rs.getString("qqgzjz"));
			    pd.setTjzcmc(rs.getString("tjzcmc"));
			    pd.setJhyear(rs.getInt("jhyear"));
			    planList.add(pd);
			}
			rs.close();
			//CurrentUser.saveLog(sqlStr); //保存当前用户操作记录
			return planList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return null;
	}

	public List getAllToltalPlanList(QueryStr qs) {
		DBOperator dbo = new DBOperator();
		List planList = new ArrayList();
		try {
			String sqlStr = "select planid,plantype,ProvinceSubsidy,attachFile, graphicFile" +
            " ,endTime,startTime,invest,milemeter,note,roadbedwidth,roadName,roadsurfaceType," +
          "roadsurfacewidth,townsId,villagesId,villageType,villageName,townName " + 
            ",sum(milemeter) as mileCount ,sum(invest) as investCount ," +
			" sum(provincesubsidy) as procount " + getTotalStr(qs);
			System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				PlanDetail pd = new PlanDetail();
			    pd.setPlanId(rs.getInt("planId"));
			    pd.setPlanType(rs.getInt("planType"));
			    pd.setProvinceSubsidy(rs.getDouble("procount"));
			    pd.setAttachFile(rs.getString("attachFile"));
			    pd.setGriphicFile(rs.getString("graphicFile"));
			    pd.setEndTime(CurrentUser.getDateToString(rs.getDate("endTime")));
			    pd.setStartTime(CurrentUser.getDateToString(rs.getDate("startTime")));
			    pd.setInvest(rs.getDouble("investCount"));
			    pd.setMilemeter(rs.getDouble("mileCount"));
			    pd.setNote(rs.getString("note"));
			    pd.setRoadbedWidth(rs.getDouble("roadbedwidth"));
			    pd.setRoadName(rs.getString("roadName"));
			    pd.setRoadSurfaceType(rs.getInt("roadsurfaceType"));
			    pd.setRoadSurfaceWidth(rs.getDouble("roadsurfacewidth"));
			    pd.setTownsId(rs.getString("townsId"));
			    pd.setVillagesId(rs.getString("villagesId"));
			    pd.setVillageType(rs.getString("villageType"));
			    pd.setVillageName(rs.getString("villageName"));
			    pd.setTownName(rs.getString("townName"));
			    planList.add(pd);
			}
			rs.close();
			//CurrentUser.saveLog(sqlStr); //保存当前用户操作记录
			return planList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return null;
	}

	public List getPlanListByCode(String code,int jhlx) {
		DBOperator dbo = new DBOperator();
		List planList = new ArrayList();
		try {
			String sqlStr = "select planid,plantype,ProvinceSubsidy,attachFile, graphicFile" +
            " ,endTime,startTime,invest,milemeter,note,roadbedwidth,roadName,roadsurfaceType," +
          "roadsurfacewidth,townsId,villagesId,villageType,villageName,townName, " + 
          "isPKX,isMZDQ,isgmlq,jsxz,jsdj,ql,ljwctz,zytz,zytzcgs,zygz,ssqzc,gndk," +
          "zyjsnr,qqgzjz,sjjslc,sjwctze,jhlx,txzmc,tjzcmc,jhyear " +
            " from tt_plandetail where townsId like '%" + code + "%' and jhlx = " + jhlx + "  order by townsid asc";
			System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				PlanDetail pd = new PlanDetail();
			    pd.setPlanId(rs.getInt("planId"));
			    pd.setPlanType(rs.getInt("planType"));
			    pd.setProvinceSubsidy(rs.getDouble("ProvinceSubsidy"));
//			    pd.setAttachFile(rs.getString("attachFile"));
//			    pd.setGriphicFile(rs.getString("graphicFile"));
			    pd.setEndTime(CurrentUser.getDateToString(rs.getDate("endTime")));
			    pd.setStartTime(CurrentUser.getDateToString(rs.getDate("startTime")));
			    pd.setInvest(rs.getDouble("invest"));
			    pd.setMilemeter(rs.getDouble("milemeter"));
			    pd.setNote(rs.getString("note"));
			//    pd.setRoadbedWidth(rs.getDouble("roadbedwidth"));
			    pd.setRoadName(rs.getString("roadName"));
			//    pd.setRoadSurfaceType(rs.getInt("roadsurfaceType"));
			//    pd.setRoadSurfaceWidth(rs.getDouble("roadsurfacewidth"));
			    pd.setTownsId(rs.getString("townsId"));
			    String cityCode = rs.getString("townsId").substring(0,4)+"00000000";
			    String countyCode = rs.getString("townsId");
			   // System.out.println("cityCode==" + cityCode + "   countyCode===" + countyCode);
			    String cityName = City.getCityNameByNum(cityCode);
			    String countyName = City.getCountyName(countyCode);
			    pd.setCityName(cityName);
			    pd.setCountyName(countyName);
//			    pd.setVillagesId(rs.getString("villagesId"));
//			    pd.setVillageType(rs.getString("villageType"));
//			    pd.setVillageName(rs.getString("villageName"));
//			    pd.setTownName(rs.getString("townName"));
			    //新增字段
			    pd.setIsPKX(rs.getInt("isPKX"));
			    pd.setIsMZDQ(rs.getInt("isMZDQ"));
			    pd.setIsGMLQ(rs.getInt("isgmlq"));
			    pd.setJsxz(rs.getInt("jsxz"));
			    pd.setJsdj(rs.getInt("jsdj"));
			    pd.setQl(rs.getString("ql"));
			    pd.setLjwctz(rs.getDouble("ljwctz"));
			    pd.setZytz(rs.getDouble("zytz"));
			    pd.setZytzcgs(rs.getDouble("zytzcgs"));
			    pd.setZygz(rs.getDouble("zygz"));
			    pd.setSsqzc(rs.getDouble("ssqzc"));
			    pd.setGndk(rs.getDouble("gndk"));
			    pd.setZyjsnr(rs.getInt("zyjsnr"));
			    pd.setSjjslc(rs.getDouble("sjjslc"));
			    pd.setSjwctze(rs.getDouble("sjwctze"));
			    pd.setJhlx(rs.getInt("jhlx"));
			    pd.setTxzmc(rs.getString("txzmc"));
			    pd.setQqgzjz(rs.getString("qqgzjz"));
			    pd.setTjzcmc(rs.getString("tjzcmc"));
			    pd.setJhyear(rs.getInt("jhyear"));
			    planList.add(pd);
			}
			rs.close();
			//CurrentUser.saveLog(sqlStr); //保存当前用户操作记录
			return planList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return null;
	}


	public List getGroupByPlanList(QueryStr qs ,int groupId,String code) {
		DBOperator dbo = new DBOperator();
		List planList = new ArrayList();
		String groupStr = " group by ";
		if (groupId == 0) groupStr = "";
		if (groupId == 1) groupStr = " group by left(townsid,2) ";
		if (groupId == 2) groupStr = " group by left(townsid,4) ";
		if (groupId == 3) groupStr = " group by left(townsid,6) ";
		if (groupId == 4) groupStr = " group by townname ";
		try {
			String sqlStr = "select planid,plantype,ProvinceSubsidy,attachFile, graphicFile" +
            " ,endTime,startTime,invest,milemeter,note,roadbedwidth,roadName,roadsurfaceType," +