pomaindao.java

使用JSP+Servlet+Ajax的供应商管理系统（不包含数据库部分）

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()
	{
		String sql = "select POID,CreateTime,Account,TipFee,ProductTotal,POTotal,PayType,PrePayFee,Status,Name from pomain,vender where pomain.PayType='3' and pomain.Status=3 and pomain.VenderCode = vender.VenderCode";
		return test(sql);
	}
	//封装部分
	public ArrayList test(String sql)
	{
		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"));
				pomain.setStatus(rs.getInt("Status"));
				al.add(pomain);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		conn.close();
		return al;
	}
	//了结
	public int liaojie(int Poid)
	{
		String sql = "update pomain set Status = 4 where POID="+Poid;
		int num = conn.executeUpdate(sql);
		return num;
	}
	//采购单查询
	public ArrayList query(Pomain pomain,String createTime1,String createTime2)
	{   
		String sql="";
		String poid ="";
		String payTypeValue ="";
		String statusValue = "";
		String venderCode = pomain.getVenderCode();
		//采购单编号不为空
		if(pomain.getPoid()!=-1)
		{
			poid = Integer.toString(pomain.getPoid());
		}
		//供应商编号为空
		if(venderCode==null)
		{
			venderCode="";
		}
		//付款方式为value!=0
		if(Integer.parseInt(pomain.getPayType())!=0)
		{
			payTypeValue=pomain.getPayType();
		}
	    //状态value!=0
		if(pomain.getStatus()!=0)
		{
			statusValue=Integer.toString(pomain.getStatus());
		}
		if("".equalsIgnoreCase(createTime1 )||"".equalsIgnoreCase(createTime2))
		{
			sql = "select POID,CreateTime,Account,TipFee,ProductTotal,POTotal,PayType,PrePayFee,Status,Name from pomain,vender "
		         +"where pomain.VenderCode = vender.VenderCode  and pomain.POID like '%"+poid
		         +"%' and pomain.VenderCode like '%"+venderCode
		         +"%' and pomain.PayType like '%"+payTypeValue
		         +"%' and pomain.Status like '%"+statusValue+"%';";
		
		}else{
			sql = "select POID,CreateTime,Account,TipFee,ProductTotal,POTotal,PayType,PrePayFee,Status,Name from pomain,vender "
		         +"where pomain.VenderCode = vender.VenderCode  and pomain.CreateTime between '"+createTime1+"' and '"+createTime2
		         +"' and pomain.POID like '%"+poid
		         +"%' and pomain.VenderCode like '%"+venderCode
		         +"%' and pomain.PayType like '%"+payTypeValue
		         +"%' and pomain.Status like '%"+statusValue+"%';";
		}
		
		return test(sql);
	}
	//查看明细
	public ArrayList ck_detial(int poid)
	{
		String sql = "select poitem.ProductCode,UnitPrice,Num,poitem.UnitName,ItemPrice,Name from poitem,product where poitem.ProductCode=product.ProductCode and poitem.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.setName(rs.getString("product.Name"));
					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) {
				e.printStackTrace();
			}
	  }
		return al;
	}
	//月度报表
	public ArrayList selectMonth(String month)
	{
		String  sql = "select POID,pomain.VenderCode,vender.Name,CreateTime,scmuser.Name,POTotal,PrePayFee,PayType,pomain.Status "
			+"from pomain,scmuser,vender where pomain.CreateTime between '"+ month+"-01'and '"+month+"-31'" 
			+"and pomain.VenderCode=vender.VenderCode and pomain.Account=scmuser.Account";
		System.out.println(sql);
		ResultSet rs = conn.executeQuery(sql);
		Pomain pomain;
		try {
			while(rs.next())
			{
				pomain = new Pomain();
				pomain.setPoid(rs.getInt("POID"));
				pomain.setVenderCode(rs.getString("pomain.VenderCode"));
				pomain.setVenderName(rs.getString("vender.Name"));
				pomain.setCreateTime(rs.getString("CreateTime"));
				pomain.setAccount(rs.getString("scmuser.Name"));
				pomain.setPoTotal(rs.getFloat("POTotal"));
				pomain.setPrePayFee(rs.getFloat("PrePayFee"));
				pomain.setPayType(rs.getString("PayType"));
				pomain.setStatus(rs.getInt("Status"));
                al.add(pomain);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		conn.close();
		return al;
	}
	
}