package wssd;

import java.sql.*;
import java.util.*;

public class wssdDB
{
	Connection conn = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;
	String DBUrl = "jdbc:oracle:thin:@localhost:1521:ORACLE";
	String DBDriver = "oracle.jdbc.driver.OracleDriver";
	String DBUser = "jerry";
	String DBPsw = "123456";

	public Connection initDB()
	{
		try
		{
			Class.forName(DBDriver);
			return 	conn = DriverManager.getConnection(DBUrl,DBUser,DBPsw);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
	}

	public ArrayList getOrder(String user_id)
	{
		try
		{
			conn = initDB();
			ArrayList list = new ArrayList();
			String sql = "select * from wssd_order where user_id = " + user_id;
			stmt = null;
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			while(rs.next())
			{
				wssd.shopping sl = new wssd.shopping();
				sl.setShoplist();
				sl.setPaystle(rs.getString("pay_style"));
				sl.setUser_id(rs.getString("user_id"));
				sl.setShopprice(rs.getDouble("price"));
				sl.setShoptime(rs.getString("order_time"));
				sl.setOrderstatus(rs.getString("status"));
				sl.setId(rs.getString("id"));
				////////////////////////////////////////////////
				ResultSet rs1 = null;
				Statement st = null;
				sql = "select b.id,b.title,b.introduction, b.price from wssd_carorder a,wssd_car b where a.order_id = "+rs.getString("id")+" and a.car_id = b.id";
				st = conn.createStatement();
				rs1 = st.executeQuery(sql);
				while(rs1.next())
				{
					wssd.carTable ct = new wssd.carTable();
					ct.setId(rs1.getString(1));
					ct.setTitle(rs1.getString(2));
					ct.setIntroduction(rs1.getString(3));
					ct.setPrice(rs1.getString(4));
					sl.shoplist.add(ct);
				}
				////////////////////////////////////////////////
				rs1.close();
				st.close();
				list.add(sl);
                        }

			return list;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				rs.close();
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public int deleteOrder(String [] id)
	{
		try
		{
			conn = initDB();
			for(int i=0; i<id.length; i++)
			{
				String sql = new String("delete from wssd_order where id ="+id[i]);
				stmt = null;
				stmt = conn.prepareStatement(sql);
				stmt.executeUpdate();
			}
			return 1;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public int updateOrder(wssd.orderTable otable)
	{
		try
		{
			conn = initDB();
			String sql = "update wssd_order set car_id=?,price=?,order_time=?,pay_style=?,user_id=?,status=? where id =?";
			stmt = null;
			stmt = conn.prepareStatement(sql);
			stmt.setString(1,otable.getCarid());
			stmt.setDouble(2,Double.parseDouble(otable.getPrice()));
			stmt.setString(3,otable.getOrdertime());
			stmt.setString(4,otable.getPaystle());
			stmt.setString(5,otable.getUserid());
//			stmt.setString(6,otable.getStatus());
			stmt.setString(7,otable.getId());

			stmt.executeUpdate();

			return 1;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public ArrayList getUpdateOrder(String pr)
	{
		try
		{
			conn = initDB();
			ArrayList list = new ArrayList();
//			String sql = null;
//			if("1".equals(pr))
//			{
			String	sql = "select a.*,b.title from wssd_order a,wssd_car b where a.car_id = b.id";
//			}
/*			if("2".equals(pr))
			{
				sql =
			}
*/			stmt = null;
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			while(rs.next())
			{
				wssd.orderTable otable = new wssd.orderTable();
				otable.setId(rs.getString("id"));
				otable.setPrice(String.valueOf(rs.getDouble("price")));
				otable.setCarid(rs.getString("car_id"));
				otable.setOrdertime(rs.getString("order_time"));
				otable.setUserid(rs.getString("userid"));
				otable.setPaystle(rs.getString("pay_style"));
//				otable.setStatus(rs.getString("status"));
//				otable.setCartitle(rs.getString("title"));
				list.add(otable);
			}

			return list;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				rs.close();
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public int insertComUser(wssd.comuserTable ctable)
	{
		try
		{
			conn = initDB();
			String sql = "select MAX(id) from wssd_comuser";
			stmt = null;
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			int d = 0;
			if(rs.next())
			{
				d = rs.getInt(1) + 1;
			}

			sql = "insert into wssd_comuser(id,username,password,name,sex,age,email,phone) values(?,?,?,?,?,?,?,?)";
			stmt = null;
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1,d);
			stmt.setString(2,ctable.getUsername());
			stmt.setString(3,ctable.getPassword());
			stmt.setString(4,ctable.getName());
			stmt.setString(5,ctable.getSex());
			stmt.setString(6,ctable.getAge());
			stmt.setString(7,ctable.getEmail());
			stmt.setString(8,ctable.getPhone());

			stmt.executeUpdate();

			return 1;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				rs.close();
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public ArrayList getComUser()
	{
		try
		{
			ArrayList list = new ArrayList();
			conn = initDB();
			String sql = "select * from wssd_comuser";
			stmt = null;
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			while(rs.next())
			{
				wssd.comuserTable ct = new wssd.comuserTable();

				ct.setUsername(rs.getString("username"));
				ct.setName(rs.getString("name"));
				ct.setAge(rs.getString("age"));
				ct.setSex(rs.getString("sex"));

				ct.setId(rs.getString("id"));
				ct.setEmail(rs.getString("email"));
				ct.setPhone(rs.getString("phone"));
				ct.setOrder_cishu(rs.getString("order_cishu"));

				list.add(ct);
			}

			return list;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				rs.close();
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public ArrayList getAdminUser()
	{
		try
		{
			ArrayList list = new ArrayList();
			conn = initDB();
			String sql = "select * from wssd_adminuser";
			stmt = null;
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			while(rs.next())
			{
				wssd.adminuserTable ct = new wssd.adminuserTable();

				ct.setUsername(rs.getString("username"));
				ct.setPriority(rs.getString("priority"));
				ct.setId(rs.getString("id"));

				list.add(ct);
			}

			return list;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				rs.close();
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public int checklogin(String username, String password)
	{
		try
		{
			conn = initDB();
			String sql = "select * from wssd_comuser where username = ?";
			stmt = null;
			stmt = conn.prepareStatement(sql);
			stmt.setString(1,username);
			rs = stmt.executeQuery();

			if(rs.next())
			{
				int id = rs.getInt("id");
				String ps = rs.getString("password");
				rs.close();
				if(password.equals(ps))
				{
					sql = "update wssd_comuser set login_cishu = login_cishu + 1 where id = ?";
					stmt = null;
					stmt = conn.prepareStatement(sql);
					stmt.setInt(1,id);
					stmt.executeUpdate();
					return id;
				}
			}
			return 0;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				rs.close();
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public wssd.carTable getCarById(String id)
	{
		try
		{
			wssd.carTable ct = new wssd.carTable();
			conn = initDB();
			String sql = "select * from wssd_car where id="+id;
			stmt = null;
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			if(rs.next())
			{
				ct.setId(id);
				ct.setTitle(rs.getString("title"));
				ct.setIntroduction(rs.getString("introduction"));
				ct.setPrice(rs.getString("price"));
			}

			return ct;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				rs.close();
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public int insertOrder(wssd.shopping list)
	{
		try
		{
			conn = initDB();
			String sql = "select MAX(id) from wssd_order";
			stmt = null;
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			int d = 0;
			if(rs.next())
			{
				d = rs.getInt(1) + 1;
			}
			rs.close();
			sql = "insert into wssd_order(id,price,order_time,pay_style,user_id) values(?,?,?,?,?)";
			stmt = null;
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1,d);
			stmt.setDouble(2,list.getShoprice());
//			stmt.setString(3,(new java.util.Date()).toLocaleString());
			stmt.setString(4,list.getPaystyle());
			stmt.setString(5,list.getUser_id());

			stmt.executeUpdate();

			for(int i=0; i<list.shoplist.size(); i++)
			{
				rs = null;
				sql = "select MAX(id) from wssd_carorder";
				stmt = null;
				stmt = conn.prepareStatement(sql);
				rs = stmt.executeQuery();

				int dd = 0;

				if(rs.next())
				{
					dd = rs.getInt(1) + 1;
				}

				sql = "insert into wssd_carorder(id,car_id,order_id) values (?,?,?)";
				stmt = null;
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1,dd);
				stmt.setString(2,((wssd.carTable)list.shoplist.get(i)).getId());
				stmt.setInt(3,d);
				stmt.executeUpdate();
			}
			return 1;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				rs.close();
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public ArrayList getOrder()
	{
		try
		{
			conn = initDB();
			ArrayList list = new ArrayList();
			String sql = "select * from wssd_order";
			stmt = null;
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			while(rs.next())
			{
/*				shopping sl = new shopping();
				sl.setShoplist();
				sl.setPaystle(rs.getString("pay_style"));
				sl.setUser_id(rs.getString("user_id"));
				sl.setShopprice(rs.getDouble("price"));
				sl.setShoptime(rs.getString("order_time"));
				sl.setOrderstatus(rs.getString("status"));
				sl.setId(rs.getString("id"));
				////////////////////////////////////////////////

				ResultSet rs1 = null;
				Statement st = null;
				sql = "select b.id,b.title,b.introduction, b.price from wssd_carorder a,wssd_car b where a.order_id = "+rs.getString("id")+" and a.car_id = b.id";
				st = conn.createStatement();
				rs1 = st.executeQuery(sql);
				while(rs1.next())
				{
					wssd.carTable ct = new wssd.carTable();
					ct.setId(rs1.getString(1));
					ct.setTitle(rs1.getString(2));
					ct.setIntroduction(rs1.getString(3));
					ct.setPrice(rs1.getString(4));
					sl.shoplist.add(ct);
				}
				////////////////////////////////////////////////

				rs1.close();
				st.close();
				list.add(sl);
*/			}

			return list;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				rs.close();
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{
			}
		}
	}

	public int updateOrderStatus(String id, String status)
	{
		try
		{
			conn = initDB();
			String sql = "update wssd_order set status=? where id =?";
			stmt = null;
			stmt = conn.prepareStatement(sql);
			stmt.setString(1,status);
			stmt.setString(2,id);

			stmt.executeUpdate();

			return 1;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
			}
		}
	}
}