package data;
import java.sql.Connection;
//import java.
import java.sql.DriverManager;
import javax.faces.component.UICommand;
import javax.faces.context.FacesContext;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
public class Data {
	public Connection con;
	private UICommand selectitem;
    private String ordernum;
    private String buyer;
    private String sum;
    private String itemnum;
    private String item;
    private String num;
    private String price;
    public List link;
    private List list;
    public Data()
    {
    	//System.out.print("no");
    	String CLASSFORNAME="com.microsoft.jdbc.sqlserver.SQLServerDriver";
		String SERVANDDB="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=scm";
		String USER="sa";
		String PWD="bigtwo863158";
		try
		{
		Class.forName(CLASSFORNAME);
		con = DriverManager.getConnection(SERVANDDB,USER,PWD);
		}
		catch(Exception e)
		{
		//System.out.println("slkdjf");
		e.printStackTrace();
		}
    }
    public void setOrdernum(String ordernum){
    	this.ordernum=ordernum;
    }
    public void setBuyer(String buyer){
    	this.buyer=buyer;
    }
    public void setSum(String sum){
    	this.sum=sum;
    }
    public void setItemnum(String itemnum){
    	this.itemnum=itemnum;
    }
    public void setItem(String item){
    	this.item=item;
    }
    public void setNum(String num){
    	this.num=num;
    }
    public void setPrice(String price){
    	this.price=price;
    }
	public void setLink(List link)
	{
		this.link=link;
	}
	public void setSelectitem(UICommand selectitem){
		this.selectitem=selectitem;
		//System.out.print("yes");
	}
	public void setList(List list)
	{
		this.list=list;
	}
	public List getList()
	{
		list=new ArrayList();
		try
		{
		Statement stm=con.createStatement();
		ResultSet result=stm.executeQuery("select * from t_orderitem");
		while(result.next())
		{
			Data d=new Data();
			d.setOrdernum(result.getString("Ordernum"));
			d.setItemnum(result.getString("Itemnum"));
			d.setItem(result.getString("Item"));
			d.setNum(result.getString("Num"));
			d.setPrice(result.getString("Price"));
			//Itemnum=result.getString("Itemnum");
			//Item=result.getString("Item");
			//Num=result.getString("Price");
			list.add(d);
		}
		stm.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return list;
	}
	public List getLink ()
	{
		//System.out.print("yes");
		link=new ArrayList();
		try
		{
		Statement stm=con.createStatement();
		ResultSet result=stm.executeQuery("select * from t_order");
		while(result.next())
		{
			Data d=new Data();
			d.setOrdernum(result.getString("Ordernum"));
			d.setBuyer(result.getString("Buyer"));
			d.setSum(result.getString("Sum"));
			//Itemnum=result.getString("Itemnum");
			//Item=result.getString("Item");
			//Num=result.getString("Price");
			link.add(d);
		}
		stm.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return link;
	}
	public String getOrdernum(){
		//System.out.print("ni");
		return ordernum;
	}
	public String getBuyer(){
		return buyer;
	}
	public String getSum(){
		return sum;
	}
	public String getItem(){
		return item;
	}
	public String getItemnum(){
		return itemnum;
	}
	public String getNum(){
		return num;
	}
	public String getPrice(){
		return price;
	}
	public UICommand getSelectitem(){
		return selectitem;
	}
	public String deleteorder(){
	    //System.out.print("go ");
		System.out.print(Data.getRequestParameter("del"));
		String order=Data.getRequestParameter("del");
		try{
			Statement stm=con.createStatement();
			ResultSet result=stm.executeQuery("delete from t_order where Ordernum='"+order+"'");
			stm.close();
			Statement stn=con.createStatement();
			result=stn.executeQuery("delete from t_orderitem where Order='"+order+"'");
			stn.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return "a";
	}
	 public static String getRequestParameter(String name)     
     {
        return (String)FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get(name);           
     }
	 public String addorder(){
		 
		 return "a";
	 }
	 public String deleteitem(){
		 System.out.print(Data.getRequestParameter("delitem"));
		 String order=Data.getRequestParameter("delorder");
		 String itemnum=Data.getRequestParameter("delitem");
		 int or;
		 int it;
		 int sum;
		 try{
				Statement stm=con.createStatement();
				ResultSet result=stm.executeQuery("select * from t_orderItem where Ordernum='"+order+"' and Itemnum='"+itemnum+"'");
				while(result.next())
				{
					sum=Integer.parseInt(result.getString("Price"))*Integer.parseInt(result.getString("Num"));
					stm.execute("delete from t_orderItem where Ordernum='"+order+"' and Itemnum='"+itemnum+"'");
					result=stm.executeQuery("select * from t_order where Ordernum='"+order+"'");
					while(result.next())
					{
					String name=result.getString("Buyer");
					int sumbin=Integer.parseInt(result.getString("Sum"));
					stm.execute("delete from t_order where Order='"+order+"'");
					stm.close();
					PreparedStatement state=con.prepareStatement("insert into t_order values(?,?,?)");
					state.setString(1, order);
					state.setString(2, name);
					state.setString(3, Integer.toString(sumbin-sum));
					state.executeUpdate();
					return "a";
					}
				}
				return "a";
			}
		     
			catch(Exception e)
			{
				e.printStackTrace();
			}
	 }

}