package supercms;
import java.util.*;
import java.sql.*;

import superdb.*;
import supershopmanager.*;
/*
 * 商品進貨管理
 * 完成對商品進貨信息的數據插入
 * 修改
 * 刪除
 * 對進貨信息進行查詢和統計
 * 
 * */
public class BuyGoodsManager {

	private SuperDBConn sdb;
	private SuperInfoManager sim;
	private Statement stmt;
	public BuyGoodsManager()
	{
		sdb=new SuperDBConn();
		sim=new SuperInfoManager(sdb.getConn());
		stmt=sim.getStmt();
	}
	/*----------完成對商品進貨信息的插入----------*/
	public void addBuyGoodsManager(BuyGoods buyGoods)
	{
		String sql="insert into buygoods (formid," +
				"goodsid,buygoodssum," +
				"buygoodsdate,employid," +
				"money," +
				"remarks)values('"+buyGoods.getFormid()+"','"+buyGoods.getGoodsid()+"'," +
				"'"+buyGoods.getBuygoodssum()+"',now(),'"+buyGoods.getEmployid()+"'," +
				"'"+buyGoods.getMoney()+"','"+buyGoods.getRemarks()+"')";
		try
		{
		stmt.executeUpdate(sql);
		System.out.println("商品進貨信息插入-------成功！！！！！");
		}catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("商品進貨信息插入-------失敗！！！！！");
		}
	}
	/*----------完成對商品進貨信息的刪除----------*/
	public void deleteBuyGoodsManager(BuyGoods buyGoods)
	{
		String sql="delete from buygoods where id="+buyGoods.getId();
		try
		{
		stmt.executeUpdate(sql);
		System.out.println("商品進貨信息刪除-------成功！！！！！");
		}catch(Exception e)
		{
			System.out.println("商品進貨信息刪除-------失敗！！！！！");
		}
	}
	/*------------完成對商品進貨信息的修改------------*/
	public void updateBuyGoodsManager(BuyGoods buyGoods)
	{
		String sql="update buygoods set formid='"+buyGoods.getFormid()+"'," +
				"goodsid='"+buyGoods.getGoodsid()+"',buygoodssum='"+buyGoods.getBuygoodssum()+"'" +
				",buygoodsdate=now()," +
				"employid='"+buyGoods.getEmployid()+"',money='"+buyGoods.getMoney()+"'," +
				"remarks='"+buyGoods.getRemarks()+"'where id="+buyGoods.getId();
		try
		{
		stmt.executeUpdate(sql);
		System.out.println("商品進貨信息修改-------成功！！！！！");
		}catch(Exception e)
		{
			System.out.println("商品進貨信息修改-------失敗！！！！！");
		}
	}
	/*-----------完成對商品進貨信息的查詢--------------*/
	public Vector selectBuyGoodsManager(BuyGoods buyGoods,int type)
	{
		Vector allBuyGoods=new Vector();
		String sql="";
		if(type==1)//說明按采購單據編號查詢
		{
			sql="select * from buygoods where id="+buyGoods.getId();
		}
		if(type==2)//說明按廠商編號進行查詢
		{
			sql="select * from buygoods where formid="+buyGoods.getFormid();
		}
		if(type==3)//說明按商品編號查詢
		{
			sql="select * from buygoods where goodsid="+buyGoods.getGoodsid();
		}
		if(type==4)//說明按采購日期進行查詢
		{
			sql="select * from buygoods where TO_DAYS(buygoodsdate)=TO_DAYS('"+buyGoods.getBuygoodsdate().getYear()+"-"+buyGoods.getBuygoodsdate().getMonth()+"-"+buyGoods.getBuygoodsdate().getDay()+"')";
			System.out.println(sql);
		}
		try
		{
			ResultSet rs=stmt.executeQuery(sql);//執行查詢
			while(rs.next())
			{
			
				BuyGoods bg=new BuyGoods();
				bg.setId(rs.getInt("id"));
				bg.setFormid(rs.getLong("formid"));
				bg.setGoodsid(rs.getLong("goodsid"));
				bg.setBuygoodssum(rs.getLong("buygoodssum"));
				long cc=(rs.getTimestamp("buygoodsdate").getTime());
				java.util.Date dd=new java.util.Date(cc);
				bg.setBuygoodsdate(rs.getTimestamp("buygoodsdate"));
				bg.setEmployid(rs.getInt("employid"));
				bg.setMoney(rs.getLong("money"));
				bg.setRemarks(rs.getString("remarks"));
				allBuyGoods.add(bg);
			}
			
		}catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("商品進貨信息查詢------失敗！！！！");
		}

		
		return allBuyGoods;
	}
	
	 /*------查詢分頁-----*/
	  
	public List getAllInfo(int pagesize,int nowpage) throws Exception
	{List allinfo=new ArrayList();
	String sql="select * from buygoods order by id";	
	  ResultSet rs=stmt.executeQuery(sql);
	  rs.last();// 定位到最后一條記錄
	  int rows=rs.getRow();
	  int num=pagesize;
	  int pagecount=rows/pagesize;//總頁數
	  if(rows%pagesize>0){pagecount++;}
	  if(nowpage>0&&nowpage<=pagecount)
	  {
		if(nowpage==1){rs.beforeFirst();}
		if(nowpage>1){rs.absolute((nowpage-1)*pagesize);}
	  while(rs.next()&&num>0)
	  { 
		  BuyGoods bg=new BuyGoods();
		  bg.setId(rs.getInt("id"));
		  bg.setFormid(rs.getLong("formid"));
		  bg.setGoodsid(rs.getLong("goodsid"));
		  bg.setBuygoodssum(rs.getLong("buygoodssum"));
		  long cc=(rs.getTimestamp("buygoodsdate").getTime());
			java.util.Date dd=new java.util.Date(cc);
			bg.setBuygoodsdate(rs.getTimestamp("buygoodsdate"));
		  bg.setEmployid(rs.getInt("employid"));
		  bg.setMoney(rs.getLong("money"));
		  bg.setRemarks(rs.getString("remarks"));
		  
	    num--;
	    allinfo.add(bg);
	  }
	  return allinfo;
	 }
	  return null;
	}
  
	public int getcount()throws Exception
	{
		int count=0;
		String sql="select * from buygoods order by id";
		 ResultSet rs=stmt.executeQuery(sql);
		 rs.last();
		 count=rs.getRow();
		return count;
	}
}