goodsexportinfo.java

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

        strSQLForCount = " SELECT count(*) FROM TabGoodsExportInfo ";
        String whereStr = "";
        if(billId != 0)
            whereStr = whereStr + " BillID=" + billId + "  AND ";
        if(memberId != -1)
            whereStr = whereStr + " MemberId=" + memberId + "  AND ";
        if(!salesPersons.equals("0"))
            whereStr = whereStr + " SalesPersons LIKE '%" + StrUtility.replaceString(salesPersons, "'", "''") + "%'  AND ";
        whereStr = whereStr + " ExportTime >= '" + exportTime1 + "'  AND ";
        whereStr = whereStr + " ExportTime <='" + exportTime2 + "'  AND ";
        if(!paymentTime1.equals(""))
            whereStr = whereStr + " PaymentTime>= '" + paymentTime1 + "'  AND ";
        if(!paymentTime2.equals(""))
            whereStr = whereStr + " PaymentTime<='" + paymentTime2 + "'  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++)
                {
                    GoodsExportBillInfo gebi = new GoodsExportBillInfo();
                    gebi.setBillId(rs.getInt(1));
                    gebi.setMemberId(rs.getInt(2));
                    gebi.setSalesPersons(rs.getString(3));
                    gebi.setTotalPrice(rs.getFloat(4));
                    gebi.setDiscount(rs.getFloat(5));
                    gebi.setTotalDisPrice(rs.getFloat(6));
                    gebi.setExportTime(rs.getString(7));
                    gebi.setPaymentTime(rs.getString(8));
                    exportBillList.add(gebi);
                    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) { }
        }
        ebsri.setExportBillList(exportBillList);
        ebsri.setTotalRecNum(totalRecNum);
        return ebsri;
    }

    public int deleteExportBill(int billId)
    {
        int nRet;
        nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            String strSQL = "DELETE FROM TabGoodsExportInfo WHERE BillID=" + billId;
            stmt = conn.createStatement();
            nRet = stmt.executeUpdate(strSQL);
            if(nRet != 1)
                nRet = -1;
            strSQL = "DELETE FROM TabGoodsExportGoods WHERE BillID=" + billId;
            stmt = conn.createStatement();
            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 float getDiscount(int billId)
    {
        float discount;
        discount = 1.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 Discount FROM TabGoodsExportInfo WHERE BillID=" + billId;
            rs = stmt.executeQuery(strSQL);
            if(rs.next())
                discount = 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 discount;
    }

    public int updatePaymentTime(int billId)
    {
        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 TabGoodsExportInfo SET PaymentTime = getdate() WHERE BillID = " + billId;
            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 int updateTotalPrice(int billId, float totalPrice)
    {
        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 TabGoodsExportInfo SET TotalPrice = " + totalPrice + " WHERE BillID = " + billId;
            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 int updateTotalDisPrice(int billId, float totalDisPrice)
    {
        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 TabGoodsExportInfo SET TotalDisPrice = " + totalDisPrice + " WHERE BillID = " + billId;
            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;
    }
}