电信报表系统管理 课程设计

package com.softtask.web.DAO; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import com.softtask.web.ActionForm.CollectionTypeForm; import com.softtask.web.Utils.AppException; import com.softtask.web.Utils.DB; import com.softtask.web.Utils.PageModel; import com.softtask.web.VO.CheckStatus; import com.softtask.web.VO.City; import com.softtask.web.VO.Collection_Type; import com.softtask.web.VO.Out_In_Type; import com.softtask.web.VO.Product; public class manualOperDaoImpl implements ManualOperDao { /* * 查出手工录入人员的出账收入信息条数 * * @see com.softtask.web.DAO.ManualOperDao#collectionCount(java.sql.Connection) */ public int collectionCount(Connection conn) { int total = 0; String sql = "select count(*) from Collection_Type"; ResultSet rs = null; PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); rs.next(); total = rs.getInt(1); } catch (SQLException e) { // 记录日志,log4j等..) e.printStackTrace(); throw new AppException("drp.basedata.item.error.findallitem"); } finally { DB.closeStmt(pstmt); } return total; } /* * 查出手工录入人员的出账收入信息(non-Javadoc) * * @see com.softtask.web.DAO.ManualOperDao#getCollectionTypeList(int, int) */ public List getCollectionTypeList(int pageno, int pagesize, String queryStr) { StringBuffer sbSql = new StringBuffer(); List collectionTypeList = new ArrayList(); sbSql .append( "select a.collection_Type_Id,a.Collection_Type_Input_Month,a.Collection_Type_City_Code as city_code, ") .append( "(select max(b.city_name) from city b where b.city_code = a.Collection_Type_City_Code) as city_name, ") .append("a.Collection_Type_Pro_Code as product_code, ") .append( "(select max(c.product_name) from product c where a.Collection_Type_Pro_Code=c.product_code) as product_name, ") .append("a.Collection_Type_Account_Type as Out_In_Type_Code, ") .append( "(select max(d.Out_In_Type_Name) from out_in_type d where a.Collection_Type_Account_Type = d.Out_In_Type_Code) as Out_In_Type_Name, ") .append("a.Collection_Type_CheckStatus as checkCode, ") .append( "(select e.checkName from checkStatus e where a.Collection_Type_CheckStatus = e.checkCode) as checkName, ") .append( " a.Collection_Type_Input_Amount,a.Collection_Type_CheckStatus ") .append("from collection_type a "); if (queryStr != null && queryStr.trim().length() != 0) { sbSql .append("where a.Collection_Type_Input_Month like '" + queryStr + "%' or (select b.city_name from city b where b.city_code = a.Collection_Type_City_Code) like '" + queryStr + "%'"); } sbSql.append("order by Collection_Type_City_Code ").append("limit ") .append((pageno - 1) * pagesize).append(", ").append(pagesize); Connection conn = null; ResultSet rs = null; Statement stmt = null; try { conn = DB.getConn(); stmt = conn.createStatement(); rs = stmt.executeQuery(sbSql.toString()); while (rs.next()) { Collection_Type collection_Type = new Collection_Type(); collection_Type.setCollectionTypeId(rs .getInt("collection_Type_Id")); collection_Type.setCollectionTypeInputMonth(rs .getString("Collection_Type_Input_Month")); collection_Type.setCollectionTypeInputAmount(rs .getString("Collection_Type_Input_Amount")); collection_Type.setCollectionTypeCheckStatus(rs .getString("Collection_Type_CheckStatus")); City city = new City(); city.setCity_code(rs.getString("city_code")); city.setCity_name(rs.getString("city_name")); collection_Type.setCity(city); Product product = new Product(); product.setProduct_code(rs.getString("product_code")); product.setProduct_name(rs.getString("product_name")); collection_Type.setProduct(product); Out_In_Type outInType = new Out_In_Type(); outInType.setOutInTypeCode(rs.getString("Out_In_Type_Code")); outInType.setOutInTypeName(rs.getString("Out_In_Type_Name")); collection_Type.setOutInType(outInType); CheckStatus checkStatus = new CheckStatus(); checkStatus.setCheckCode(rs.getString("checkCode")); checkStatus.setCheckName(rs.getString("checkName")); collection_Type.setCheckStatus(checkStatus); collectionTypeList.add(collection_Type); } } catch (SQLException e) { // 记录日志,log4j等...... e.printStackTrace(); throw new AppException("drp.basedata.item.error.findallitem"); } finally { DB.closeStmt(stmt); DB.closeRs(rs); DB.closeConn(conn); } return collectionTypeList; } public void collectionDelete(Connection conn, String[] selectFlag) { StringBuffer sbSql = new StringBuffer(); for (int i = 0; i < selectFlag.length; i++) { sbSql.append("'") // 批量删除的时候sbSql.sustring(i,j)分割时，不能有空格存在。 .append(selectFlag[i]).append("'").append(","); } String sql = "delete from collection_type where collection_Type_Id in (" + sbSql.substring(0, sbSql.length() - 1) + ")"; Statement stmt = null; try { stmt = conn.createStatement(); stmt.executeUpdate(sql); } catch (SQLException e) { // 记录日志,log4j等...... e.printStackTrace(); throw new AppException("drp.basedata.item.error.delete"); } finally { DB.closeStmt(stmt); } } public void addCollection(Connection conn, Collection_Type collectionType) { String sql = "insert into collection_type(Collection_Type_Input_Month,Collection_Type_City_Code,Collection_Type_Pro_Code,Collection_Type_Account_Type,Collection_Type_Input_Amount)" + "values(?, ?, ?, ?, ?) "; PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, collectionType.getCollectionTypeInputMonth()); pstmt.setString(2, collectionType.getCity().getCity_code()); pstmt.setString(3, collectionType.getProduct().getProduct_code()); pstmt .setString(4, collectionType.getOutInType() .getOutInTypeCode()); pstmt.setString(5, collectionType.getCollectionTypeInputAmount()); pstmt.executeUpdate(); } catch (SQLException e) { // 记录日志,log4j等...... e.printStackTrace(); throw new AppException("drp.basedata.item.error.add"); } finally { DB.closeStmt(pstmt); } } public Collection_Type findByCollectionTypeId(int collectionTypeId) { StringBuffer sbSql = new StringBuffer(); sbSql .append( "select a.collection_Type_Id,a.Collection_Type_Input_Month,a.Collection_Type_City_Code as city_code, ") .append( "(select b.city_name from city b where b.city_code = a.Collection_Type_City_Code) as city_name, ") .append("a.Collection_Type_Pro_Code as product_code, ") .append( "(select c.product_name from product c where a.Collection_Type_Pro_Code=c.product_code) as product_name, ") .append("a.Collection_Type_Account_Type as Out_In_Type_Code, ") .append( "(select d.Out_In_Type_Name from out_in_type d where a.Collection_Type_Account_Type = d.Out_In_Type_Code) as Out_In_Type_Name, ") .append("a.Collection_Type_In