stockact.java

企业进销存管理系统

/**
 * 
 */
package action;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import oracle.sql.DATE;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import actionform.Stockactform;

/**
 * 
 * 
 */
public class Stockact extends Action {
	static Connection conn = null;

	static String sql;

	static DATE stockdate;

	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws IOException, ServletException, SQLException {
		Stockactform myForm = (Stockactform) form;
		// .....
		String swareid = myForm.getWareid();
		String swarename = myForm.getWarename();
		if (swarename != null) {
			swarename = new String(swarename.getBytes("ISO8859-1"));
		}
		String syear = myForm.getYear();
		String smonth = myForm.getMonth();
		String sday = myForm.getDay();
		String ssell = myForm.getYear() + myForm.getMonth() + myForm.getDay();
		String sflag = myForm.getFlag();
		String sbox = myForm.getBox();
		if (swareid.equals("") && swarename.equals("") && ssell.equals("")) {
			sql = "select * from stock,waremessage where stock.wareid=waremessage.wareid ";
		}
		if (sflag != null && sflag.equals("sel")) {
			if (swareid.equals("") && swarename.equals("") && ssell.equals("")) {
				sql = "select * from stock,waremessage where stock.wareid=waremessage.wareid ";
			} else if (swareid != null && swarename.equals("")
					&& ssell.equals("")) {
				sql = "select * from stock,waremessage where stock.wareid='"
						+ swareid + "' and stock.wareid=waremessage.wareid";
			} else if (swareid.equals("") && swarename != null
					&& ssell.equals("")) {
				sql = "select * from  waremessage,stock  where waremessage.warename='"
						+ swarename
						+ "'and "
						+ "waremessage.wareid=stock.wareid";
			} else if (swareid.equals("") && swarename.equals("")
					&& ssell != null) {
				sql = "select *from stock,waremessage where stockdate=to_date('"
						+ ssell
						+ "','yyyy-mm-dd') and stock.wareid=waremessage.wareid";
			} else if (swareid != null && swarename != null && ssell.equals("")) {
				sql = "select * from stock, waremessage where stock.wareid='"
						+ swareid + "'and  waremessage.warename='" + swarename
						+ "'and waremessage.wareid=stock.wareid";
			} else if (swareid != null && swarename.equals("") && ssell != null) {
				sql = "select * from stock,waremessage where stock.wareid='"
						+ swareid + "' and stock.stockdate=to_date('" + ssell
						+ "','yyyy-mm-dd')";
			} else if (swareid.equals("") && swarename != null && ssell != null) {
				sql = "select * from stock, waremessage where waremessage.warename='"
						+ swarename
						+ "'and "
						+ "waremessage.wareid=stock.wareid and stock.stockdate=to_date('"
						+ ssell + "','yyyy-mm-dd')";
			} else if (swareid != null && swarename != null && ssell != null) {
				sql = "select * from stock, waremessage where waremessage.warename='"
						+ swarename
						+ "'"
						+ "and waremessage.wareid=stock.wareid and stock.stockdate=to_date('"
						+ ssell
						+ "','yyyy-mm-dd')and stock.wareid='"
						+ swareid
						+ "' ";
			}
			// if(sb!=null)
			// {
			// sql="delete from stock";
			// }
		}
		if (sflag != null && sflag.equals("del")) {
			String B[] = request.getParameterValues("box");
			if (sbox != null && !sbox.equals("")) {

				// request.getParameterValues("box");

				for (int i = 0; i <= B.length; i++) {

					try {
						Class.forName("oracle.jdbc.driver.OracleDriver");
						conn = DriverManager
								.getConnection("jdbc:oracle:thin:scott/tiger@localhost:1521:database");
						System.out.println("conn run");
					} catch (ClassNotFoundException e1) {

						e1.printStackTrace();
					} catch (SQLException e) {

						e.printStackTrace();
					}
					try{
						sql = "delete from stock where stockid='" + B[i] + "'";
						System.out.println("sql=" + sql);
						PreparedStatement psmt1 = conn.prepareStatement(sql);
						ResultSet res1 = psmt1.executeQuery();
						sql = "select * from stock,waremessage where stock.wareid=waremessage.wareid ";
						request.setAttribute("rs", getAlluser());

					}
					catch(ArrayIndexOutOfBoundsException e){
						System.out.println("ArrayIndexOutOfBoundsException 是啥");
					}
				}
			} else if (sbox.equals("") && sbox == null) {

			}
		}
		
		HttpSession session=request.getSession(true);
		if(!session.getAttribute("numrecord").equals("4"))
		{
		session.setAttribute("numrecord","4");
		int m=1;
		session.setAttribute("num",m);
		}
		int n=1;
		if(session.getAttribute("num")!=null){
			n=(Integer)session.getAttribute("num");
		}
		if(sflag!=null&&sflag.equals("next")){
			session.setAttribute("num", ++n);			
		}else if(sflag!=null&&sflag.equals("front")){			
			session.setAttribute("num", --n);			
		}
		request.setAttribute("rs", Show.page(getAlluser(), n, session));
		return mapping.findForward("true");
	}

	private static Connection conn() {

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager
					.getConnection("jdbc:oracle:thin:scott/tiger@localhost:1521:database");
			System.out.println("conn run");
		} catch (ClassNotFoundException e1) {

			e1.printStackTrace();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return conn;

	}

	public static ArrayList getAlluser() {
		Connection con = conn();
		System.out.println("conn1 run");
		ResultSet rs = null;
		// ResultSet res=null;
		PreparedStatement ps = null;

		ArrayList rslist = new ArrayList();
		// String sql="select * from stock";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			int i = 1;

			// while(res.next())
			// {
			// HashMap rscol = new HashMap();
			// rscol.put("user_warename",rs.getString("warename"));
			// rscol.put("user_standard",rs.getString("standard"));
			// rscol.put("user_unit",rs.getString("unit"));
			// rscol.put("user_rate",rs.getString("rate"));
			// }

			while (rs.next()) {
				HashMap rscol = new HashMap();
				rscol.put("number", i);
				rscol.put("user_stockid", rs.getString("stockid"));
				rscol.put("user_serveid", rs.getString("serveid"));
				rscol.put("user_wareid", rs.getString("wareid"));
				rscol.put("user_warename", rs.getString("warename"));
				rscol.put("user_standard", rs.getString("standard"));
				rscol.put("user_unit", rs.getString("unit"));
				rscol.put("user_rate", rs.getString("rate"));
				String date = rs.getString("stockdate").replaceAll(
						"00:00:00.0", "");
				rscol.put("user_stockdate", date);
				rscol.put("user_stockamount", rs.getString("stockamount"));
				rscol.put("user_moneysum", rs.getString("moneysum"));
				rscol.put("user_operator", rs.getString("operator"));
				rslist.add(rscol);
				i = i + 1;
			}
		} catch (SQLException e) {
			System.out.println("getAlluser sql error");
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (Exception e) {
				System.out.println("close(999)");
			}
		}
		return rslist;
	}
}