首页 > 代码库 > 库存调拨 java 实现方式
库存调拨 java 实现方式
项目开发中客户提出一个要求：
我只选择商品和数量，完成商品的移库操作
在开发中发现这里商品又有批次的问题，那么计算成本的时候又需要根据批次来计算他的成本，那么这样就产生了几个问题
1.当某个商品的这个批次所对应的库存数量足够他调拨时的数量
2.当某个商品的这个批次不够的他需要调拨的数量，我就必须累加下一个批次的数量在这里又发现几种情况
2.1当两个批次正好够的情况
2.2 当两个批次大于他的情况
这里就涉及到要修改最后一条数据的库存数量
2.3 当两个批次还不够的情况
3.修改库存数量
如果调入仓库没有该批次的商品数量，我会插入一条新纪录
这些所有情况都需要调拨完成之后修改对应的库存信息
下面是具体代码
数据库结构
下面是实现代码
通用的JDBC
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.sql.ResultSetMetaData;public class DBUtil { private static String url = "jdbc:jtds:sqlserver://192.168.1.182:1433/erp"; private static String driverClass = "net.sourceforge.jtds.jdbc.Driver"; private static String userName = "sa"; private static String pwd = "admin"; public static final Connection getConn() { Connection conn = null; try { Class.forName(driverClass); } catch (ClassNotFoundException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } try { conn = DriverManager.getConnection(url, userName, pwd); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } return conn; } public static void beginTransaction(Connection conn) throws SQLException{ if(conn == null||conn.isClosed()){ throw new SQLException("数据库连接异常"); } conn.setAutoCommit(false); } public static void commit(Connection conn) throws SQLException{ if(conn == null||conn.isClosed()){ throw new SQLException("数据库连接异常"); } conn.commit(); conn.setAutoCommit(true); } public static boolean update(Connection conn,String sql) throws Exception{ Statement state = conn.createStatement(); int bs = state.executeUpdate(sql); return bs>0; } public static List<Map<String,Object>> query(Connection conn,String sql ){ List<Map<String, Object>> restList = null; try { restList = new ArrayList<Map<String, Object>>(); PreparedStatement prepState = conn.prepareStatement(sql); ResultSet result = prepState.executeQuery(); ResultSetMetaData metaData = result.getMetaData(); int colCount = metaData.getColumnCount(); while(result.next()){ Map<String,Object> map = new HashMap<String,Object>(); for(int i = 1 ; i<=colCount; i++){ String colName = metaData.getColumnName(i); Object value = result.getObject(i); map.put(colName,value); } restList.add(map); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return restList; }}
实现的主函数
package com;import java.sql.Connection;import java.util.List;import java.util.Map;public class WzdpService { public static void main(String args[]){ try { WzdpService wzdp = new WzdpService(); wzdp.one(20,"a","y","x"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void one(int sl,String spmc,String kfmc,String toKf) throws Exception{ int sysl = 0;//剩余物资数量 int dbsl = 0;//调拨数量 //查询总库总数量 String sql1 = "select sum(sl) cnt from test where ck = ‘"+kfmc+"‘ and sp = ‘"+spmc+"‘ order by cnt desc "; Connection conn = DBUtil.getConn(); List<Map<String,Object>> result2 = DBUtil.query(conn, sql1); Map<String,Object> spxx2 = result2.get(0); if(spxx2 == null){ System.out.println("无相关物品！"); return; } int csl = Integer.valueOf(spxx2.get("cnt").toString()); //判断库存数量能否满足调拨数量 if(csl<sl){ System.out.println("物品数量不足"); return; } String sql = "select id,sp,pc,ck,sl from test where ck = ‘" + kfmc + "‘ and sp = ‘" + spmc + "‘ order by sl desc "; List<Map<String, Object>> result = DBUtil.query(conn, sql); Map<String, Object> spxx = result.get(0); if (spxx == null) { System.out.println("无相关物品！"); return; } int zkmxsl = Integer.valueOf(spxx.get("sl").toString()); if (zkmxsl == 0) { return; } String sp = (String) spxx.get("sp"); String pc = (String) spxx.get("pc"); String ck = (String) spxx.get("ck"); DBUtil.beginTransaction(conn); // 1、库房单批次数量满足 mxsl>=sl mxsl - sl = nsl // 2、库当批次数量不满足 mxsl<sl 查询所有批次 if (zkmxsl >= sl) { dbsl = sl; } else { sysl = sl - zkmxsl; dbsl = zkmxsl; } // 入库 String cxsql = "select id,sp,pc,ck,sl from test where ck = ‘" + toKf + "‘ and sp = ‘" + sp + "‘ and pc = ‘" + pc + "‘"; List<Map<String, Object>> result1 = DBUtil.query(conn, cxsql); if (result1.isEmpty()) { String insert = "insert into test(SP,PC,CK,SL) values (‘" + sp + "‘,‘" + pc + "‘,‘" + toKf + "‘,‘" + dbsl + "‘)"; DBUtil.update(conn, insert); } else { Map<String, Object> spxx1 = result1.get(0); int nsl = Integer.valueOf(spxx1.get("sl").toString()); nsl = nsl + dbsl; String update = "update test set sl = " + nsl + " where ck = ‘" + toKf + "‘ and sp = ‘" + sp + "‘ and pc = ‘" + pc + "‘"; DBUtil.update(conn, update); } // 出库 dbsl = zkmxsl - sl; if (dbsl < 0) { dbsl = 0; } String updateZk = "update test set sl = " + dbsl + " where ck = ‘" + kfmc + "‘ and sp = ‘" + sp + "‘ and pc = ‘" + pc + "‘"; DBUtil.update(conn, updateZk); DBUtil.commit(conn); if (sysl <= 0) { return; } one(sysl, spmc, kfmc, toKf); }}
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。