goodsexportgoods.java

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

    {
        float totalProfit;
        int nRet = 0;
        totalProfit = 0.0F;
        RepertoryGoodsPriceCurve rgpc = new RepertoryGoodsPriceCurve();
        GoodsExportInfo gei = new GoodsExportInfo();
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = 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 ";
        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;
        strSQL = strSQL + " Order by " + orderStr;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            for(rs = stmt.executeQuery(strSQL); rs.next();)
            {
                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);
                totalProfit += profit;
            }

            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 totalProfit;
    }

    public String getConfirmFlage(int billId, String goodsId)
    {
        String tmp;
        tmp = "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 ConfirmFlage FROM TabGoodsExportGoods WHERE BillID=" + billId + " AND GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "' ";
            rs = stmt.executeQuery(strSQL);
            if(rs.next())
                tmp = 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 tmp;
    }

    public String getCreateTime(int billId, String goodsId)
    {
        String tmp;
        tmp = "1978-06-01 00:00:00";
        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 CreateTime FROM TabGoodsExportGoods WHERE BillID=" + billId + " AND GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "' ";
            rs = stmt.executeQuery(strSQL);
            if(rs.next())
                tmp = 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 tmp;
    }

    public int getExportAmount(int billId, String goodsId)
    {
        int exportAmount;
        exportAmount = 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 ExportAmount FROM TabGoodsExportGoods WHERE BillID=" + billId + " AND GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "' ";
            rs = stmt.executeQuery(strSQL);
            if(rs.next())
                exportAmount = 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 exportAmount;
    }

    public float getExportUnitPrice(int billId, String goodsId)
    {
        float exportUnitPrice;
        exportUnitPrice = 0.0F;
        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 ExportUnitPrice FROM TabGoodsExportGoods WHERE BillID=" + billId + " AND GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "' ";
            rs = stmt.executeQuery(strSQL);
            if(rs.next())
                exportUnitPrice = rs.getFloat(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 exportUnitPrice;
    }

    public int getMemberId(int billId)
    {
        int tmp;
        tmp = 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 MemberId FROM TabGoodsExportGoods WHERE BillID=" + billId;
            rs = stmt.executeQuery(strSQL);
            if(rs.next())
                tmp = 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 tmp;
    }

    public float getTotalPaymentByMemberId(int memberId)
    {
        float totalPayment;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        totalPayment = 0.0F;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT ExportAmount,ExportUnitPrice FROM TabGoodsExportGoods WHERE MemberId=" + memberId + " AND ConfirmFlage='1'";
            for(rs = stmt.executeQuery(strSQL); rs.next();)
                totalPayment += (float)rs.getInt("ExportAmount") * rs.getFloat("ExportUnitPrice");

            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 totalPayment;
    }

    public String getRecentBuyTimeOfMember(int memberId)
    {
        String recentTime;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        recentTime = "";
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT CreateTime FROM TabGoodsExportGoods WHERE MemberId=" + memberId + " AND ConfirmFlage='1' Order by CreateTime desc";
            rs = stmt.executeQuery(strSQL);
            if(rs.next())
                recentTime = rs.getString("CreateTime");
            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 recentTime;
    }
}