gys.rar_gys.sstpark.com

package com.aowin.scm.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import java.util.Random; import com.aowin.scm.model.Poitem; import com.aowin.scm.model.Pomain; import com.aowin.scm.model.Pro; import com.aowin.scm.util.DBConnection; public class PomainDAO { DBConnection conn = new DBConnection(); ArrayList al = new ArrayList(); HashMap hm = new HashMap(); //遍历pomain表 public ArrayList quarry() { String sql = "select POID,CreateTime,Account,TipFee,ProductTotal,POTotal,PayType,PrePayFee,Name from pomain,vender where pomain.VenderCode = vender.VenderCode"; ResultSet rs = conn.executeQuery(sql); Pomain pomain; try { while(rs.next()){ pomain = new Pomain(); pomain.setPoid(rs.getInt("POID")); pomain.setCreateTime(rs.getString("CreateTime")); //在VenderCode处附Name的值 pomain.setVenderCode(rs.getString("Name")); pomain.setAccount(rs.getString("Account")); pomain.setTipFee(rs.getFloat("TipFee")); pomain.setProductTotal(rs.getFloat("ProductTotal")); pomain.setPoTotal(rs.getFloat("POTotal")); //付款方式进行转化 int id = Integer.parseInt(rs.getString("PayType")); String PayTypeText = getPayTypeText(id); pomain.setPayType(PayTypeText); pomain.setPrePayFee(rs.getFloat("PrePayFee")); al.add(pomain); } } catch (SQLException e) { e.printStackTrace(); } conn.close(); return al; } public String getPayTypeText(int id) { String PayTypeText = ""; switch(id) { case 1: PayTypeText = "货到付款";break; case 2: PayTypeText = "款到发货";break; case 3: PayTypeText = "预付款到发货";break; } return PayTypeText; } //插入一行 public int addItems_CG(Pomain pomain,ArrayList<Poitem> array) { ArrayList<String> sqls1 = new ArrayList<String>(); String sql1 = "INSERT INTO `pomain` VALUES" +"("+pomain.getPoid()+",'"+pomain.getVenderCode()+"','"+pomain.getAccount() +"','"+pomain.getCreateTime()+"',"+pomain.getTipFee()+","+pomain.getProductTotal() +","+pomain.getPoTotal()+",'"+pomain.getPayType()+"',"+pomain.getPrePayFee() +","+pomain.getStatus()+",'"+pomain.getRemark()+"','"+pomain.getStockTime() +"','"+pomain.getStockUser()+"','"+pomain.getPayTime()+"','"+pomain.getPayUser() +"','"+pomain.getPrePayTime()+"','"+pomain.getPrePayUser() +"','"+pomain.getEndTime()+"','"+pomain.getEndUser()+"');"; sqls1.add(sql1); Iterator it = array.iterator(); while(it.hasNext()) { Poitem poitem = (Poitem)it.next(); String sql2 = "INSERT INTO `poitem` VALUES (" +poitem.getPoid()+",'"+poitem.getProductCode()+"',"+poitem.getUnitPrice() +","+poitem.getNum()+",'"+poitem.getUnitName()+"',"+poitem.getItemPrice() +");"; sqls1.add(sql2); String sql3 = "update product set PONum = PONum+"+poitem.getNum()+" where ProductCode = '"+poitem.getProductCode()+"'"; sqls1.add(sql3); } boolean flag = conn.executeBatch(sqls1); int num = flag ? 1:0; return num; } //随机获得采购单的编号 public int getCGpoid() { Random random=new Random();//创建random对象 int intNumber=Math.abs(random.nextInt());//获取一个正整数 return intNumber; } //修改时获得备注的值 public String get_Remark(String poid) { String sql = "select Remark from pomain where POID ="+poid; ResultSet rs = conn.executeQuery(sql); String remark = ""; if(rs!=null) { try { while(rs.next()) { remark = rs.getString("Remark"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } conn.close(); return remark; } //修改时获得明细单的信息 public ArrayList getProductDetail(String poid) { String sql = "select * from poitem where POID="+poid; ResultSet rs = conn.executeQuery(sql); Poitem poitem; if(rs!=null) { try { while(rs.next()){ poitem = new Poitem(); poitem.setProductCode(rs.getString("ProductCode")); poitem.setUnitPrice(rs.getFloat("UnitPrice")); poitem.setNum(rs.getInt("Num")); poitem.setUnitName(rs.getString("UnitName")); poitem.setItemPrice(rs.getFloat("ItemPrice")); al.add(poitem); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } conn.close(); return al; } //修改连数据库 public int modifyItems_CG(Pomain pomain,ArrayList<Poitem> array) { ArrayList<String> sqls = new ArrayList<String>(); String sql0 = "update pomain set Account='"+pomain.getAccount()+"',TipFee= "+pomain.getTipFee() +",ProductTotal="+pomain.getProductTotal()+",poTotal="+pomain.getPoTotal() +",PayType='"+pomain.getPayType()+"',PrePayFee="+pomain.getPrePayFee()+" where POID="+pomain.getPoid(); sqls.add(sql0); //删除明细单的在途采购数 HashMap hm1 =delPNum(pomain.getPoid()); for(Iterator iter = hm1.entrySet().iterator(); iter.hasNext();) { Map.Entry entry = (Map.Entry) iter.next(); String key = entry.getKey().toString(); int val = (Integer)entry.getValue(); String sql1="update product set PONum = PONum-"+val+" where ProductCode = '"+key+"'"; sqls.add(sql1); } String sql2 = "delete from poitem where POID="+pomain.getPoid(); sqls.add(sql2); Iterator it = array.iterator(); while(it.hasNext()) { Poitem poitem =(Poitem)it.next(); String sql3 = "INSERT INTO `poitem` VALUES (" +poitem.getPoid()+",'"+poitem.getProductCode()+"',"+poitem.getUnitPrice() +","+poitem.getNum()+",'"+poitem.getUnitName()+"',"+poitem.getItemPrice() +");"; sqls.add(sql3); //添加采购在途数 String sql4 = "update product set PONum = PONum+"+poitem.getNum()+" where ProductCode = '"+poitem.getProductCode()+"'"; sqls.add(sql4); } boolean flag = conn.executeBatch(sqls); int num = flag?1:0; return num; } //获得productCode和num用于修改采购在途数 public HashMap delPNum(int poid) { String sql = "select ProductCode,Num from poitem where POID="+poid; ResultSet rs = conn.executeQuery(sql); try { while(rs.next()){ String productCode = rs.getString("productCode"); int num = (int)rs.getInt("num"); hm.put(productCode, num); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return hm; } //删除 public int delMainItems(int poid) { ArrayList<String> sqls = new ArrayList<String>(); HashMap hm1 = delPNum(poid); for(Iterator iter = hm1.entrySet().iterator(); iter.hasNext();) { Map.Entry entry = (Map.Entry) iter.next(); String key = entry.getKey().toString(); int val = (Integer)entry.getValue(); String sql0="update product set PONum = PONum-"+val+" where ProductCode = '"+key+"'"; sqls.add(sql0); } String sql1 = "delete from pomain where POID="+poid; String sql2 = "delete from poitem where POID="+poid; sqls.add(sql2); sqls.add(sql1); boolean flag = conn.executeBatch(sqls); int num = flag ? 1: 0; return num; } //货到付款1,已付款3 public ArrayList gPayment() { String sql = "select POID,CreateTime,Account,TipFee,ProductTotal,POTotal,PayType,PrePayFee,Status,Name from pomain,vender where pomain.PayType='1' and pomain.Status=3 and pomain.VenderCode = vender.VenderCode"; return test(sql); } //款到发货2，已收货2 public ArrayList pAllGoods() { String sql = "select POID,CreateTime,Account,TipFee,ProductTotal,POTotal,PayType,PrePayFee,Status,Name from pomain,vender where pomain.PayType='2' and pomain.Status=2 and pomain.VenderCode = vender.VenderCode"; return test(sql); } //预付款到发货3，已付款3 public ArrayList pGoods()