plandaoimpl.java

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

          "roadsurfacewidth,townsId,villagesId,villageType,villageName,townName " + 
           " ,sum(milemeter) as mileCount ,sum(invest) as investCount ," +
			" sum(provincesubsidy) as procount,sum(invest-provincesubsidy) as zccount,sum(zytz) as zytzcount,sum(sjwctze) as sjtzcount  " + getTotalStr(qs) + " and townsid like '%" + code + "%'" + groupStr;
			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"));
			    pd.setSsqzc(rs.getDouble("zccount"));
			    pd.setZytz(rs.getDouble("zytzcount"));
			    pd.setSjwctze(rs.getDouble("sjtzcount"));
			    planList.add(pd);
			}
			rs.close();
			//CurrentUser.saveLog(sqlStr); //保存当前用户操作记录
			return planList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return null;
	}

	public int getTownOrVillageCount(QueryStr qs, String code,int Typeid) {
		DBOperator dbo = new DBOperator();
		int count = 0;
		try {
			String sqlStr = "";
			if (Typeid == 2)
				sqlStr = "select count(planid) as maxCount " + getTotalStr(qs) + " and townname='" + code + "'" ;	
			else 
			 sqlStr = "select count(townsid) as maxCount " + getTotalStr(qs) + " and townsid like '%" + code + "%'" ;
		    System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				count = rs.getInt("maxCount");
			}
			rs.close();
			//CurrentUser.saveLog(sqlStr); //保存当前用户操作记录
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return count;
	}

	public List getTownAndVillageCount(QueryStr qs, String code) {
		DBOperator dbo = new DBOperator();
		List planList = new ArrayList();
		String groupStr = " group by townname";
		try {
			String sqlStr = "select townName,count(townsid) as villageCount " + getTotalStr(qs) + " and townsid like '%" + code + "%'" + groupStr;
			System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				PlanDetail pd = new PlanDetail();
			    pd.setPlanId(rs.getInt("villageCount"));
		        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 PlanDetail getAllProvince(QueryStr qs ,int typeId,String code) {
		DBOperator dbo = new DBOperator();
		PlanDetail pd = new PlanDetail();
		String andStr = "";
		if (typeId == 1) andStr = ""  ;
		if (typeId == 2) andStr = " and left(townsid,4)=" + code ;
		if (typeId == 3) andStr = " and left(townsid,6)=" + code ;
		if (typeId == 4) andStr = " and left(townsid,9)=" + code ;
		try {
			String sqlStr = "select count(townsid) as villageCount ,sum(milemeter) as mile ,sum(invest) as investcount,sum(provincesubsidy) as procount," +
					"sum(invest-provincesubsidy) as zccount ,sum(sjwctze) as sjtzcount " + getTotalStr(qs) ;
			System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
			
			    pd.setPlanId(rs.getInt("villageCount"));
		        pd.setMilemeter(rs.getDouble("mile"));
		        pd.setInvest(rs.getDouble("investcount"));
		        pd.setProvinceSubsidy(rs.getDouble("procount"));
		        pd.setSsqzc(rs.getDouble("zccount"));
		        pd.setSjwctze(rs.getDouble("sjtzcount"));
			   
			}
			rs.close();
			//CurrentUser.saveLog(sqlStr); //保存当前用户操作记录
			return pd;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return null;
	}

	public boolean addTown(String townCode, String townName) {
		DBOperator dbo = new DBOperator();
		String query = "insert into tt_town(townCode,townName) values ('" + townCode + "','" + townName + "')";
		try {
			System.out.println(query);
			dbo.setPrepareStatement(query);
			dbo.executeUpdate();
			return true;
		} catch (Exception e) {
		} finally {
			dbo.close();
		}
		return false;
	}

	public List getPlanInfoByPlantype(QueryStr qs, String code) {
		DBOperator dbo = new DBOperator();
		List totalList = new ArrayList();
		try {
			String sqlStr = "";
			  sqlStr = "select plantype,sum(invest) as investcunt,sum(milemeter) as milecount,sum(provincesubsidy) as procount" +
			  		", sum(zytz) as zytzcount,sum(ssqzc) as zccount " 
				        + getTotalStr(qs) + " and townsid like '%" + code + "%' group by plantype" ;	
		
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				TotalInfo ti = new TotalInfo();
				   ti.setPlanType(rs.getInt("plantype"));
				   ti.setJhlc(rs.getDouble("milecount"));
				   ti.setJhsbe(rs.getDouble("procount"));
				   ti.setJhtze(rs.getDouble("investcunt"));
				   ti.setJhzytz(rs.getDouble("zytzcount"));
				   ti.setJhzczj(rs.getDouble("zccount"));
				   totalList.add(ti);
			}
			rs.close();
			//CurrentUser.saveLog(sqlStr); //保存当前用户操作记录
			return totalList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return totalList;
	}

	public boolean updatePlan(String townName, int planId) {
		DBOperator dbo = new DBOperator();
		String query = "update tt_plandetail set townName = '" + townName + "' ";
			   query += " where planId = " + planId;
	
		try {
			System.out.println(query);
			dbo.setPrepareStatement(query);
			dbo.executeUpdate();
			return true;
		} catch (Exception e) {
		} finally {
			dbo.close();
		}
		return false;
	}

	public List getPlanList() {
		DBOperator dbo = new DBOperator();
		List planList = new ArrayList();
		try {
			String sqlStr = "select e.planId,d.townName ,e.villageName,e.villagecode from tt_town d ,(select a.planId,a.villagename ,b.villagecode " +
					"from tt_plandetail a,tt_village b where a.villageName=b.villagename " +
					"and LEFT(a.townsid,6)=left(b.villagecode,6) and (a.townName ='乡道' or a.townname ='')) e " +
					"where left(e.villagecode,9) = left(d.towncode,9)";
			System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				PlanDetail pd = new PlanDetail();
			    pd.setPlanId(rs.getInt("planId"));
			    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 getPlanListByAll(String code) {
		DBOperator dbo = new DBOperator();
		List planList = new ArrayList();
	
		try {
			String sqlStr = "select planid,plantype,roadName,townsId,villageName,townName from tt_plandetail " +
					"where planType<3  ";
			      sqlStr += " and townsid like '%" + code +"%' order by plantype asc ,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.setRoadName(rs.getString("roadName"));
			    pd.setTownsId(rs.getString("townsId"));
			    String cityCode = rs.getString("townsId").substring(0,4)+"00000000";
			    String countyCode = rs.getString("townsId");
			    String cityName = City.getCityNameByNum(cityCode);
			    String countyName = City.getCountyName(countyCode);
			    pd.setCityName(cityName);
			    pd.setCountyName(countyName);
			
			    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 PlanDetail getPlanInfoByVillageName(String code, int planType, String villageName) {
		DBOperator dbo = new DBOperator();
		PlanDetail pd = new PlanDetail();
		RoleDAO roledao = new RoleDAOImpl();
		int count = 0;
	
		try {
			String sqlStr = " select *   from tt_plandetail where planType= " + planType + " and townsid like'%" + code + "%' and (villageName='"
			                            + villageName+"' or villageName like '%" + villageName.substring(0,villageName.length()-2) + "%')";
			System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
			    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"));
			    count = rs.getRow();
			}
			rs.close();
		//	CurrentUser.saveLog(sqlStr); //保存当前用户操作记录
			if (count > 0)
			return pd;
			else return null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return null;
	}

}