goodsinfo.java

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

            stmt = conn.createStatement();
            strSQL = "SELECT RepertoryAmount FROM TabGoodsInfo WHERE GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "'";
            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 float getPurchaseUnitPrice(String goodsId)
    {
        float purchaseUnitPrice;
        int nRet = 0;
        purchaseUnitPrice = 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 PurchaseUnitPrice FROM TabGoodsInfo WHERE GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "'";
            rs = stmt.executeQuery(strSQL);
            if(!rs.next())
            {
                nRet = -1;
                throw new Exception("获得最大进货单号失败");
            }
            purchaseUnitPrice = 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 purchaseUnitPrice;
    }

    public void exportAdjustAandU(String goodsId, int importAmount, float importUnitPrice, String adjustPerson)
    {
        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 RepertoryAmount, ImportUnitPrice, Creators FROM TabGoodsInfo WHERE GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "'";
            rs = stmt.executeQuery(strSQL);
            if(!rs.next())
                throw new Exception("获得库存量、平均进价和创建人串失败");
            int repertoryAmount = rs.getInt(1);
            float oldImportUnitPrice = rs.getFloat(2);
            String oldCreators = rs.getString(3);
            if(oldCreators == null)
                oldCreators = "";
            rs.close();
            rs = null;
            int newRepertoryAmount = repertoryAmount - importAmount;
            float newImportUnitPrice = (oldImportUnitPrice * (float)repertoryAmount - importUnitPrice * (float)importAmount) / (float)newRepertoryAmount;
            String newCreators = "";
            if(oldCreators.indexOf(adjustPerson) == -1)
                if(oldCreators.equals(""))
                    newCreators = adjustPerson;
                else
                    newCreators = oldCreators + "," + adjustPerson;
            strSQL = "UPDATE TabGoodsInfo SET RepertoryAmount=" + newRepertoryAmount + ", ImportUnitPrice=" + newImportUnitPrice + ", Creators='" + StrUtility.replaceString(newCreators, "'", "''") + "', LastCreator='" + StrUtility.replaceString(adjustPerson, "'", "''") + "', LastUpdateTime=getdate()  WHERE GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "'";
            stmt.executeUpdate(strSQL);
            if(oldImportUnitPrice != newImportUnitPrice)
            {
                int maxIndexNum = 2;
                strSQL = "SELECT max(IndexNum) FROM TabRepertoryGoodsPriceCurve WHERE GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "'";
                rs = stmt.executeQuery(strSQL);
                if(!rs.next())
                    throw new Exception("获得最大序号数失败");
                maxIndexNum = rs.getInt(1) + 1;
                rs.close();
                rs = null;
                strSQL = "UPDATE TabRepertoryGoodsPriceCurve SET EndTime=getdate() WHERE GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "' and IndexNum=" + (maxIndexNum - 1);
                stmt.executeUpdate(strSQL);
                strSQL = "insert into TabRepertoryGoodsPriceCurve( GoodsID, IndexNum, StartTime, EndTime, ImportUnitPrice) values ('" + StrUtility.replaceString(goodsId, "'", "''") + "', " + maxIndexNum + ", getdate(), '2078-06-01 00:00:00', " + newImportUnitPrice + ")";
                stmt.executeUpdate(strSQL);
            }
        }
        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;
    }

    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 TabGoodsInfo 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 int updateSubjectPersons(String goodsId, String newSubjectPersons)
    {
        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 TabGoodsInfo SET SubjectPersons = '" + StrUtility.replaceString(newSubjectPersons, "'", "''") + "' 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 int updateShelfLayerId(String goodsId, String newShelfLayerId)
    {
        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 TabGoodsInfo SET ShelfLayerID = '" + newShelfLayerId + "' 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 int updateLayerCellId(String goodsId, String newLayerCellId)
    {
        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 TabGoodsInfo SET LayerCellID = '" + newLayerCellId + "' 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 int updateCreators(String goodsId, String curPerson)
    {
        int nRet;
        nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        String creators = "";
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT Creators FROM TabGoodsInfo WHERE GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "'";
            rs = stmt.executeQuery(strSQL);
            if(!rs.next())
            {
                nRet = -1;
                throw new Exception("获得创建人失败");
            }
            creators = rs.getString(1);
            rs.close();
            rs = null;
            if(creators == null)
                creators = "";
            if(creators.equals(""))
                creators = curPerson;
            else
            if(creators.indexOf(curPerson) <= -1)
                creators = creators + "," + curPerson;
            strSQL = "UPDATE TabGoodsInfo SET Creators='" + StrUtility.replaceString(creators, "'", "''") + "', LastCreator='" + StrUtility.replaceString(curPerson, "'", "''") + "', LastUpdateTime=getdate() 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 int updateGoodsType(String goodsId, String newGoodsType)
    {
        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 TabGoodsInfo SET GoodsType = '" + StrUtility.replaceString(newGoodsType, "'", "''") + "' WHERE GoodsID = '" + StrUtility.replaceString(goodsId, "'", "''") + "'";
            stmt.executeUpdate(strSQL);
        }
        catch(Exception e)
        {
            nRet = -1;
        }
        finally
        {
            try
            {