📄 goodsinfo.java
字号:
strSQL = " SELECT * FROM TabGoodsInfo ";
strSQLForCount = " SELECT count(*) FROM TabGoodsInfo ";
String whereStr = "";
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(!subjectPersons.equals("") && subjectPersons != null)
whereStr = whereStr + " subjectPersons LIKE '%" + StrUtility.replaceString(subjectPersons, "'", "''") + "%' AND ";
if(repertoryAmount1 != -1)
whereStr = whereStr + " RepertoryAmount>= " + repertoryAmount1 + " AND ";
if(repertoryAmount2 != -1)
whereStr = whereStr + " RepertoryAmount<= " + repertoryAmount2 + " AND ";
if(importUnitPrice1 != -1F)
whereStr = whereStr + " ImportUnitPrice>= " + importUnitPrice1 + " AND ";
if(importUnitPrice2 != -1F)
whereStr = whereStr + " ImportUnitPrice<= " + importUnitPrice2 + " AND ";
if(purchaseUnitPrice1 != -1F)
whereStr = whereStr + " PurchaseUnitPrice>= " + purchaseUnitPrice1 + " AND ";
if(purchaseUnitPrice2 != -1F)
whereStr = whereStr + " PurchaseUnitPrice<= " + purchaseUnitPrice2 + " AND ";
if(!goodsType.equals("") && goodsType != null)
whereStr = whereStr + " GoodsType='" + StrUtility.replaceString(goodsType, "'", "''") + "' AND ";
if(!goodsClass.equals("") && goodsClass != null)
whereStr = whereStr + " GoodsClass='" + StrUtility.replaceString(goodsClass, "'", "''") + "' AND ";
if(!goodsShelfId.equals("") && goodsShelfId != null)
whereStr = whereStr + " GoodsShelfID='" + goodsShelfId + "' AND ";
if(!creators.equals("") && creators != null)
whereStr = whereStr + " Creators LIKE '%" + creators + "%' AND ";
if(!confirmFlage.equals("") && confirmFlage != null)
whereStr = whereStr + " ConfirmFlage='" + confirmFlage + "' AND ";
if(!specialUnitPriceStatus.equals("") && specialUnitPriceStatus != null)
if(specialUnitPriceStatus.equals("1"))
whereStr = whereStr + " SpecialUnitPrice IS NULL AND ";
else
whereStr = whereStr + " SpecialUnitPrice IS NOT NULL 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++)
{
GoodsInfoObj gio = new GoodsInfoObj();
gio.setGoodsId(rs.getString(1));
gio.setGoodsType(rs.getString(2));
gio.setGoodsTypeName(gtc.getGoodsTypeName(rs.getString(2)));
gio.setGoodsClass(rs.getString(3));
gio.setGoodsClassName(gcc.getGoodsClassName(rs.getString(3)));
gio.setGoodsName(rs.getString(4));
gio.setSubjectPersons(rs.getString(5));
gio.setRepertoryAmount(rs.getInt(6));
gio.setImportUnitPrice(rs.getFloat(7));
gio.setPurchaseUnitPrice(rs.getFloat(8));
gio.setSpecialUnitPrice(rs.getFloat(9));
gio.setGoodsShelfId(rs.getString(10));
gio.setGoodsShelfIdName(gsic.getGoodsShelfIDName(rs.getString(10)));
gio.setShelfLayerId(rs.getString(11));
gio.setLayerCellId(rs.getString(12));
gio.setCreators(rs.getString(13));
gio.setLastCreator(rs.getString(14));
gio.setLastUpdateTime(rs.getString(15));
gio.setConfirmFlage(rs.getString(16));
String deleteAbleFlage = "0";
if(rs.getInt(6) <= 0 && gig.getRecCount1(rs.getString(1)) <= 0)
deleteAbleFlage = "1";
gio.setDeleteAbleFlage(deleteAbleFlage);
GoodsList.add(gio);
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) { }
}
gsri.setGoodsList(GoodsList);
gsri.setTotalRecNum(totalRecNum);
return gsri;
}
public int getGoodsTypeCount(String goodsType)
{
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 TabGoodsInfo WHERE GoodsType='" + StrUtility.replaceString(goodsType, "'", "''") + "'";
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;
}
public int getGoodsClassCount(String goodsClass)
{
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 TabGoodsInfo WHERE GoodsClass='" + StrUtility.replaceString(goodsClass, "'", "''") + "'";
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;
}
public int getGoodsShelfIdCount(String goodsShelfId)
{
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 TabGoodsInfo WHERE GoodsShelfID='" + goodsShelfId + "'";
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;
}
public int updateRepertoryAmount(String goodsId, int exportAmount, int repertoryAmount)
{
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 RepertoryAmount = " + (repertoryAmount - exportAmount) + " 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 GoodsInfoObj getGoodsInfoByGoodsId(String goodsId)
{
GoodsInfoObj gio;
int nRet = 0;
gio = new GoodsInfoObj();
GoodsTypeCode gtc = new GoodsTypeCode();
GoodsClassCode gcc = new GoodsClassCode();
GoodsShelfIDCode gsic = new GoodsShelfIDCode();
DBConnection dbc = null;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String strSQL = null;
strSQL = " SELECT * FROM TabGoodsInfo WHERE GoodsID='" + goodsId + "'";
try
{
dbc = new DBConnection();
conn = dbc.getDBConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(strSQL);
if(rs.next())
{
gio.setGoodsId(rs.getString(1));
gio.setGoodsType(rs.getString(2));
gio.setGoodsTypeName(gtc.getGoodsTypeName(rs.getString(2)));
gio.setGoodsClass(rs.getString(3));
gio.setGoodsClassName(gcc.getGoodsClassName(rs.getString(3)));
gio.setGoodsName(rs.getString(4));
gio.setSubjectPersons(rs.getString(5));
gio.setRepertoryAmount(rs.getInt(6));
gio.setImportUnitPrice(rs.getFloat(7));
gio.setPurchaseUnitPrice(rs.getFloat(8));
gio.setSpecialUnitPrice(rs.getFloat(9));
gio.setGoodsShelfId(rs.getString(10));
gio.setGoodsShelfIdName(gsic.getGoodsShelfIDName(rs.getString(10)));
gio.setShelfLayerId(rs.getString(11));
gio.setLayerCellId(rs.getString(12));
gio.setCreators(rs.getString(13));
gio.setLastCreator(rs.getString(14));
gio.setLastUpdateTime(rs.getString(15));
gio.setConfirmFlage(rs.getString(16));
}
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 gio;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -