goodsexportgoods.java

用jsp,java写的在线购物系统

            nRet = stmt.executeUpdate(strSQL);
            if(nRet != 1)
                nRet = -1;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            nRet = -1;
        }
        finally
        {
            try
            {
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return nRet;
    }

    public int getMaxBillIDNum()
    {
        int nRet;
        nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT max(BillID) FROM TabGoodsExportGoods;";
            rs = stmt.executeQuery(strSQL);
            if(!rs.next())
            {
                nRet = -1;
                throw new Exception("获得最大售货单号失败");
            }
            nRet = rs.getInt(1);
            rs.close();
            rs = null;
        }
        catch(Exception exception) { }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return nRet;
    }

    public int updateGoodsName(String goodsId, String newGoodsName)
    {
        int nRet;
        nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "UPDATE TabGoodsExportGoods SET GoodsName = '" + StrUtility.replaceString(newGoodsName, "'", "''") + "' WHERE GoodsID = '" + StrUtility.replaceString(goodsId, "'", "''") + "'";
            stmt.executeUpdate(strSQL);
        }
        catch(Exception e)
        {
            nRet = -1;
        }
        finally
        {
            try
            {
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return nRet;
    }

    public List getBillIdList()
    {
        List billIdList;
        billIdList = new ArrayList();
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT DISTINCT BillID FROM TabGoodsExportGoods Order by BillID";
            String billIdStr;
            for(rs = stmt.executeQuery(strSQL); rs.next(); billIdList.add(billIdStr))
                billIdStr = String.valueOf(rs.getInt(1));

            rs.close();
            rs = null;
        }
        catch(Exception exception) { }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return billIdList;
    }

    public List getSalesPersonList()
    {
        List salesPersonList;
        salesPersonList = new ArrayList();
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT DISTINCT SalesPerson FROM TabGoodsExportGoods Order by SalesPerson";
            String curSendPersons;
            for(rs = stmt.executeQuery(strSQL); rs.next(); salesPersonList.add(curSendPersons))
                curSendPersons = rs.getString(1);

            rs.close();
            rs = null;
        }
        catch(Exception exception) { }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return salesPersonList;
    }

    public List getSalesPersonList(int billId)
    {
        List tmpList;
        tmpList = new ArrayList();
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT DISTINCT SalesPerson FROM TabGoodsExportGoods WHERE BillID=" + billId;
            String curSendPersons;
            for(rs = stmt.executeQuery(strSQL); rs.next(); tmpList.add(curSendPersons))
                curSendPersons = rs.getString(1);

            rs.close();
            rs = null;
        }
        catch(Exception exception) { }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return tmpList;
    }

    public ExportItemSearchResultInfo getItemListByRule(ExportItemSearchRule eisr)
    {
        int totalRecNum;
        int totalAmount;
        List exportItemList;
        ExportItemSearchResultInfo eisri;
        int nRet = 0;
        totalRecNum = 0;
        totalAmount = 0;
        exportItemList = new ArrayList();
        RepertoryGoodsPriceCurve rgpc = new RepertoryGoodsPriceCurve();
        GoodsExportInfo gei = new GoodsExportInfo();
        eisri = new ExportItemSearchResultInfo();
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        String strSQLForCount = null;
        int startRecNum = eisr.getStartRecNum();
        int recNumOfPage = eisr.getRecNumOfPage();
        String orderStr = eisr.getOrderStr();
        int billId = eisr.getBillId();
        String goodsId = eisr.getGoodsId();
        String goodsName = eisr.getGoodsName();
        String salesPerson = eisr.getSalesPerson();
        String confirmFlage = eisr.getConfirmFlage();
        String createTime1 = eisr.getCreateTime1();
        String createTime2 = eisr.getCreateTime2();
        strSQL = " SELECT * FROM TabGoodsExportGoods ";
        strSQLForCount = " SELECT count(*) FROM TabGoodsExportGoods ";
        String whereStr = "";
        if(billId != 0)
            whereStr = whereStr + " BillID=" + billId + "  AND ";
        if(!goodsId.equals("") && goodsId != null)
            whereStr = whereStr + " GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "'  AND ";
        if(!goodsName.equals("") && goodsName != null)
            whereStr = whereStr + " GoodsName LIKE '%" + StrUtility.replaceString(goodsName, "'", "''") + "%'  AND ";
        if(!salesPerson.equals("") && salesPerson != null)
            whereStr = whereStr + " SalesPerson LIKE '%" + StrUtility.replaceString(salesPerson, "'", "''") + "%'  AND ";
        if(!confirmFlage.equals("") && confirmFlage != null)
            whereStr = whereStr + " ConfirmFlage='" + confirmFlage + "'  AND ";
        if(!createTime1.equals(""))
            whereStr = whereStr + " CreateTime>= '" + createTime1 + "'  AND ";
        if(!createTime2.equals(""))
            whereStr = whereStr + " CreateTime<='" + createTime2 + "'  AND ";
        int lenOfWhereStr = whereStr.length();
        if(lenOfWhereStr - 4 >= 0)
            whereStr = whereStr.substring(0, lenOfWhereStr - 4);
        if(!whereStr.equals(""))
        {
            strSQL = strSQL + " WHERE " + whereStr;
            strSQLForCount = strSQLForCount + " WHERE " + whereStr;
        }
        strSQL = strSQL + " Order by " + orderStr;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(strSQLForCount);
            if(!rs.next())
            {
                nRet = -1;
                throw new Exception("获得总记录数失败");
            }
            totalRecNum = rs.getInt(1);
            rs.close();
            rs = null;
            rs = stmt.executeQuery(strSQL);
            boolean rsresult = false;
            boolean hasnext = false;
            for(int j = 1; j <= startRecNum; j++)
                rsresult = rs.next();

            if(rsresult)
            {
                hasnext = true;
                for(int i = 1; i <= recNumOfPage && hasnext; i++)
                {
                    GoodsExportItemInfo geii = new GoodsExportItemInfo();
                    geii.setBillId(rs.getInt(1));
                    geii.setGoodsId(rs.getString(2));
                    geii.setGoodsName(rs.getString(3));
                    geii.setExportAmount(rs.getInt(4));
                    geii.setExportUnitPrice(rs.getFloat(5));
                    geii.setSalesPerson(rs.getString(6));
                    geii.setCreateTime(rs.getString(7));
                    geii.setConfirmFlage(rs.getString(8));
                    geii.setMemberId(rs.getInt(9));
                    totalAmount += rs.getInt(4);
                    float discount = 1.0F;
                    if("0".equals(rs.getString(8)))
                    {
                        int memberId = getMemberId(rs.getInt(1));
                        MemberInfoDAO mi = new MemberInfoDAO();
                        discount = mi.getDiscount(memberId);
                    } else
                    {
                        discount = gei.getDiscount(rs.getInt(1));
                    }
                    float upOfTheTime = rgpc.getUPOfTheTime(rs.getString(2), rs.getString(7));
                    float profit = (rs.getFloat(5) * discount - upOfTheTime) * (float)rs.getInt(4);
                    geii.setDiscount(discount);
                    geii.setProfit(profit);
                    exportItemList.add(geii);
                    if(rs.next())
                        hasnext = true;
                    else
                        hasnext = false;
                }

            }
            rs.close();
            rs = null;
        }
        catch(Exception exception) { }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        eisri.setExportItemList(exportItemList);
        eisri.setTotalRecNum(totalRecNum);
        eisri.setTotalAmount(totalAmount);
        return eisri;
    }

    public float getTotalProfitByRule(ExportItemSearchRule eisr)