📄 plandaoimpl.java
字号:
query += pd.getPlanType() + ",roadName = '";
query += pd.getRoadName() + "',villageType = '";
query += pd.getVillageType() + "',villageName = '";
query += pd.getVillageName() + "',townName = '";
query += pd.getTownName() + "',milemeter = ";
query += pd.getMilemeter() + ",note='";
query += pd.getNote() + "',invest =";
query += pd.getInvest() + ",roadbedwidth=";
query += pd.getRoadbedWidth() + ",roadsurfaceType=";
query += pd.getRoadSurfaceType() + ",roadsurfacewidth=";
query += pd.getRoadSurfaceWidth() ;
if (pd.getEndTime()!=null && pd.getEndTime().length() > 0){
query += ",endTime = '" + pd.getEndTime() + "'";
}else
query += ",endTime = null";
if (pd.getStartTime()!=null&& pd.getStartTime().length() > 0){
query += ",startTime = '" + pd.getStartTime() + "'";
}else
query += ",startTime = null";
query += ",ProvinceSubsidy = ";
query += pd.getProvinceSubsidy();
query += ",isPKX=";
query += pd.getIsPKX() + ",isMZDQ=";
query += pd.getIsMZDQ()+ ",isGMLQ=";
query += pd.getIsGMLQ() +",jsxz=";
query += pd.getJsxz() +",jsdj = ";
query += pd.getJsdj() + ",ql='";
query += pd.getQl() + "',ljwctz=";
query += pd.getLjwctz() +",zytz=";
query += pd.getProvinceSubsidy() + ",zytzcgs=";
query += pd.getProvinceSubsidy() + ",zygz=";
query += pd.getZygz() + ",ssqzc=";
double zc = pd.getInvest() - pd.getProvinceSubsidy();
query += zc + ",gndk=";
query += pd.getGndk() + ",zyjsnr=";
query += pd.getZyjsnr() + ",qqgzjz='";
query += pd.getQqgzjz() + "',sjjslc=";
query += pd.getSjjslc() + ",sjwctze=";
query += pd.getSjwctze() + ",jhlx=";
query += pd.getJhlx() + ",txzmc='";
query += pd.getTxzmc() + "',tjzcmc='";
query += pd.getTjzcmc() + "',jhyear=";
query += pd.getJhyear() + "";
query += " ,modifiedTime = curdate() where planId = " + planId;
try {
System.out.println(query);
dbo.setPrepareStatement(query);
dbo.executeUpdate();
return true;
} catch (Exception e) {
} finally {
dbo.close();
}
return false;
}
public Page QueryPlan(int start, int count, QueryStr qs) {
Connection conn = null;
DBConnectionManager dbmanager = DBConnectionManager.getInstance();
Page page = null;
try {
conn = dbmanager.getConnection("DB");
// String sqlStr = "select * " + getQueryStr(qs);
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);
PreparedStatement pstmt = conn.prepareStatement(sqlStr,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = pstmt.executeQuery();
if (start >= 0 && rs.absolute(start + 1)) {
boolean hasNext = false;
List planList = new ArrayList();
do {
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);
} 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 String getQueryStr(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()+ "')";
}
queryStr += " order by " ;
if (qs.getTypeId() == 6) {
if (qs.getStartType() == 1)
queryStr += " startTime asc ";
else
queryStr += " startTime desc ";
}
if (qs.getTypeId() == 7) {
if (qs.getEndType() == 1)
queryStr += " endTime asc ";
else
queryStr += " endTime desc ";
}
if (qs.getTypeId() == 1) {
if (qs.getMileType() == 1)
queryStr += " Milemeter asc ";
else
queryStr += " Milemeter desc ";
}
if (qs.getTypeId() == 2) {
if (qs.getLjkdType() == 1)
queryStr += " roadbedwidth asc ";
else
queryStr += " roadbedwidth desc ";
}
if (qs.getTypeId() == 3) {
if (qs.getLmkdType() == 1)
queryStr += " roadsurfacewidth asc ";
else
queryStr += " roadsurfacewidth desc ";
}
if (qs.getTypeId() == 4) {
if (qs.getInvestType() == 1)
queryStr += " Invest asc ";
else
queryStr += " Invest desc ";
}
if (qs.getTypeId() == 5) {
if (qs.getProType() == 1)
queryStr += " provincesubsidy asc ";
else
queryStr += " provincesubsidy desc ";
}
if (qs.getTypeId() == 8) {
if (qs.getRoadType() == 1)
queryStr += " roadname asc ";
else
queryStr += " roadname desc ";
}
if (qs.getTypeId() == 9) {
if (qs.getJhType() == 1)
queryStr += " planType asc ";
else
queryStr += " planType desc ";
}
if (qs.getTypeId() == 10) {
if (qs.getTownType() == 1)
queryStr += " townName asc ";
else
queryStr += " townName desc ";
}
if (qs.getTypeId() == 11) {
if (qs.getCunType() == 1)
queryStr += " villageName asc ";
else
queryStr += " villageName desc ";
}
if (qs.getTypeId() == 12) {
if (qs.getCityType() == 1)
queryStr += " townsid asc ";
else
queryStr += " townsid desc ";
}
return queryStr;
}
public int getQueryPlanCount(QueryStr qs) {
ArrayList articlelist=new ArrayList();
int count=0;
DBOperator dbo = new DBOperator();
try {
String sqlStr = "select count(planId) as maxCount " + getQueryStr(qs);
dbo.setPrepareStatement(sqlStr);
ResultSet rs = dbo.executeQuery();
while (rs.next()) {
count = rs.getInt("maxCount");
}
rs.close();
return count;
} catch (SQLException e) {
e.printStackTrace();
} finally {
dbo.close();
}
return count;
}
public Page TotalPlan(int start, int count, QueryStr qs) {
Connection conn = null;
DBConnectionManager dbmanager = DBConnectionManager.getInstance();
Page page = null;
try {
conn = dbmanager.getConnection("DB");
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);
PreparedStatement pstmt = conn.prepareStatement(sqlStr,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = pstmt.executeQuery();
if (start >= 0 && rs.absolute(start + 1)) {
boolean hasNext = false;
List planList = new ArrayList();
do {
PlanDetail pd = new PlanDetail();
pd.setPlanId(rs.getInt("planId"));
pd.setPlanType(rs.getInt("planType"));
pd.setProvinceSubsidy(rs.getDouble("procount"));
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -