goodsimportgoods.java

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

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

    public List getCreatePersonList(int billId)
    {
        List createPersonList;
        createPersonList = 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 CreatePerson FROM TabGoodsImportGoods WHERE BillID=" + billId;
            String curSendPersons;
            for(rs = stmt.executeQuery(strSQL); rs.next(); createPersonList.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 createPersonList;
    }

    public ImportItemSearchResultInfo getItemListByRule(ImportItemSearchRule iisr)
    {
        int totalRecNum;
        List importItemList;
        ImportItemSearchResultInfo iisri;
        int nRet = 0;
        totalRecNum = 0;
        importItemList = new ArrayList();
        iisri = new ImportItemSearchResultInfo();
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        String strSQLForCount = null;
        int startRecNum = iisr.getStartRecNum();
        int recNumOfPage = iisr.getRecNumOfPage();
        String orderStr = iisr.getOrderStr();
        int billId = iisr.getBillId();
        String goodsId = iisr.getGoodsId();
        String goodsName = iisr.getGoodsName();
        String createPerson = iisr.getCreatePerson();
        String confirmFlage = iisr.getConfirmFlage();
        strSQL = " SELECT * FROM TabGoodsImportGoods ";
        strSQLForCount = " SELECT count(*) FROM TabGoodsImportGoods ";
        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(!createPerson.equals("") && createPerson != null)
            whereStr = whereStr + " CreatePerson LIKE '%" + StrUtility.replaceString(createPerson, "'", "''") + "%'  AND ";
        if(!confirmFlage.equals("") && confirmFlage != null)
            whereStr = whereStr + " ConfirmFlage='" + confirmFlage + "'  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;
        System.out.println("strSQL:" + strSQL);
        try
        {
            File file = new File("sql.txt");
            file.createNewFile();
        }
        catch(IOException ioexception) { }
        try
        {
            BufferedWriter out = new BufferedWriter(new FileWriter("sql.txt"));
            out.write(strSQL);
            out.write("\r\n");
            out.write(strSQLForCount);
            out.close();
        }
        catch(IOException ioexception1) { }
        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++)
                {
                    GoodsImportItemInfo giii = new GoodsImportItemInfo();
                    giii.setBillId(rs.getInt(1));
                    giii.setGoodsId(rs.getString(2));
                    giii.setGoodsName(rs.getString(3));
                    giii.setImportAmount(rs.getInt(4));
                    giii.setImportUnitPrice(rs.getFloat(5));
                    giii.setCreatePerson(rs.getString(6));
                    giii.setCreateTime(rs.getString(7));
                    giii.setConfirmFlage(rs.getString(8));
                    String cancelAbleFlage = "0";
                    cancelAbleFlage = getCancelAbleFlageByGoodsId(rs.getString(2), rs.getInt(4));
                    giii.setCancelAbleFlage(cancelAbleFlage);
                    importItemList.add(giii);
                    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) { }
        }
        iisri.setImportItemList(importItemList);
        iisri.setTotalRecNum(totalRecNum);
        return iisri;
    }

    public String getCancelAbleFlageByGoodsId(String goodsId, int importAmount)
    {
        GoodsInfo gi = new GoodsInfo();
        int repertoryAmount = gi.getRepertoryAmount(goodsId);
        if(repertoryAmount >= importAmount)
            return "1";
        else
            return "0";
    }

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

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

    public void cancelImportItem(int billId, String goodsId, String confirmFlage, String adjustPerson)
    {
        int importAmount = getImportAmount(billId, goodsId);
        float importUnitPrice = getImportUnitPrice(billId, goodsId);
        GoodsInfo gi = new GoodsInfo();
        GoodsImportGoods gig = new GoodsImportGoods();
        GoodsImportInfo gii = new GoodsImportInfo();
        int repertoryAmount = gi.getRepertoryAmount(goodsId);
        if(repertoryAmount >= importAmount)
        {
            gi.exportAdjustAandU(goodsId, importAmount, importUnitPrice, adjustPerson);
            gig.deleteGoodsRec(billId, goodsId, confirmFlage);
        }
        List newGoodsList = gig.getGoodsList(billId);
        if(newGoodsList.size() <= 0)
            gii.deleteImportBill(billId);
    }

    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 TabGoodsImportGoods 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 getRecCount1(String goodsId)
    {
        int count;
        count = 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 count(*) FROM TabGoodsImportGoods WHERE GoodsID='" + StrUtility.replaceString(goodsId, "'", "''") + "' and ConfirmFlage='0'";
            rs = stmt.executeQuery(strSQL);
            if(rs.next())
                count = 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 count;
    }
}