package supercms;

import java.sql.*;

import superdb.*;
import supershopmanager.GoodsInfo;
import supershopmanager.GoodsType;

import java.util.*;

/*
 * 商品信息管理
 * 完成商品基本資料的數據插入、刪除、修改、查詢、統計
 * 
 * */
public class GoodsInfoManager {

	private SuperDBConn sdb;
	private SuperInfoManager sim;
	private Statement stmt;// 創建命令對象

	public GoodsInfoManager() {

		sdb = new SuperDBConn();
		sim = new SuperInfoManager(sdb.getConn());
		stmt = sim.getStmt();
	}

	/*-----------商品基本資料的數據插入----------*/
	public void addGoodsInfo(GoodsInfo goodsInfo) {
		String sql = "insert into goodsinfo(goodsnumber,goodsname,guigetype,kindnumber,singleprice,safesum,unit,remarks)" +
				"values('"+goodsInfo.getGoodsnumber()+"','"+goodsInfo.getGoodsname()+"','"+goodsInfo.getGuigetype()+"','"+goodsInfo.getKindnumber()+"','"+goodsInfo.getSingleprice()+"','"+goodsInfo.getSafesum()+"','"+goodsInfo.getUnit()+"','"+goodsInfo.getRemarks()+"')";

		try
		{
		stmt.executeUpdate(sql);
		System.out.println("商品基本資料數據插入------成功！！！！！");
		}catch(Exception e){
			
			System.out.println("商品基本資料數據插入------失敗！！！！！");
		}
	}
	/*-----------商品基本資料的數據查詢----------*/
	public Vector selectGoodsInfo(GoodsInfo goodsInfo,int type)
	{
		Vector allGoodsInfo=new Vector();
		String sql="";
		if(type==1)//說明按編號查詢
		{
		 sql="select * from goodsinfo where id="+goodsInfo.getId();
		}
		if(type==2)//說明按商品碼進行查詢
		{
			sql="select * from goodsinfo where goodsnumber='"+goodsInfo.getGoodsnumber()+"'";
		}
		if(type==3)//說明按照商品名進行查詢
		{
			sql="select * from goodsinfo where goodsname='"+goodsInfo.getGoodsname()+"'";
		}
		if(type==4)//說明按照商品種類型號進行查詢
		{
			sql="select * from goodsinfo where kindnumber='"+goodsInfo.getKindnumber()+"'";
		}
		if(type==5)//說明按照單價進行查詢
		{
			sql="select * from goodsinfo where singleprice="+goodsInfo.getSingleprice();
		}
		try
		{
			ResultSet rs=stmt.executeQuery(sql);//執行查詢
			while(rs.next())
			{
				GoodsInfo gi=new GoodsInfo();
				gi.setId(rs.getInt("id"));
				gi.setGoodsnumber(rs.getString("goodsnumber"));
				gi.setGoodsname(rs.getString("goodsname"));
				gi.setGuigetype(rs.getString("guigetype"));
				gi.setKindnumber(rs.getInt("kindnumber"));
				gi.setSingleprice(rs.getLong("singleprice"));
				gi.setSafesum(rs.getInt("safesum"));
				gi.setUnit(rs.getString("unit"));
				gi.setRemarks(rs.getString("remarks"));
				allGoodsInfo.add(gi);
				System.out.println("商品基本資料數據查詢------成功！！！！");
				
			}
			
		}catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("商品基本資料數據查詢------失敗！！！！");
		}
		return allGoodsInfo;
	}
/*-----------商品基本資料數據的刪除------------*/
	public void deleteGoodsInfo(GoodsInfo goodsInfo)
	{
		String sql="delete from goodsinfo where id="+goodsInfo.getId();
		try
		{
		stmt.executeUpdate(sql);
		System.out.println("刪除商品基本資料------成功！！！！");
		}catch(Exception e)
		{
			System.out.println("刪除商品基本資料------失敗！！！！");
		}
		
	}
	public void delGoodsInfo(GoodsInfo goodsInfo)
	{
		String sql="delete from goodsinfo where kindnumber="+goodsInfo.getKindnumber();
		try
		{
		stmt.executeUpdate(sql);
		System.out.println("刪除商品基本資料------成功！！！！");
		}catch(Exception e)
		{
			System.out.println("刪除商品基本資料------失敗！！！！");
		}
	}
	/*---------------商品基本資料數據的修改----------*/
	public void updateGoodsInfo(GoodsInfo goodsInfo)
	{
		String sql="update goodsinfo set goodsnumber='"+goodsInfo.getGoodsnumber()+"'," +
				"goodsname='"+goodsInfo.getGoodsname()+"',guigetype='"+goodsInfo.getGuigetype()+"'," +
				"kindnumber='"+goodsInfo.getKindnumber()+"',singleprice='"+goodsInfo.getSingleprice()+"',safesum='"+goodsInfo.getSafesum()+"'," +
				"unit='"+goodsInfo.getUnit()+"',remarks='"+goodsInfo.getRemarks()+"' where id="+goodsInfo.getId();
		try
		{
		stmt.executeUpdate(sql);
		System.out.println("修改商品基本資料------成功！！！！");
		}catch(Exception e)
		{
			System.out.println("修改商品基本資料------失敗！！！！");
		}
		
	}
	/*-----------------統計商品數量------------------*/
	public long countGoodsInfo(GoodsInfo goodsInfo,int type)
	{
		long count=0;
		String sql="";
		if(type==1)//說明按編號查詢
		{
		 sql="select count(*) from goodsinfo where id="+goodsInfo.getId();
		}
		if(type==2)//說明按商品碼進行查詢
		{
			sql="select count(*) from goodsinfo where goodsnumber='"+goodsInfo.getGoodsnumber()+"'";
		}
		if(type==3)//說明按照商品名進行查詢
		{
			sql="select count(*) from goodsinfo where goodsname='"+goodsInfo.getGoodsname()+"'";
		}
		if(type==4)//說明按照商品種類型號進行查詢
		{
			sql="select count(*) from goodsinfo where kindnumber="+goodsInfo.getKindnumber();
		}
		if(type==5)//說明按照單價進行查詢
		{
			sql="select count(*) from goodsinfo where singleprice="+goodsInfo.getSingleprice();
		}
		
		try
		{
			ResultSet rs=stmt.executeQuery(sql);//執行查詢
			while(rs.next())
			{
				count++;
				System.out.println("商品基本資料數據統計------成功！！！！");
				
			}
			
		}catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("商品基本資料數據統計------失敗！！！！");
		}
		
		return count;
	}
	
	 /*------查詢分頁-----*/
	  
	public List getAllInfo(int pagesize,int nowpage) throws Exception
	{List allinfo=new ArrayList();
	String sql="select * from goodsinfo 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)
	  { 
		  GoodsInfo gi=new GoodsInfo();
		  gi.setId(rs.getInt("id"));
		  gi.setGoodsnumber(rs.getString("goodsnumber"));
		  gi.setGuigetype(rs.getString("guigetype"));
		  gi.setKindnumber(rs.getInt("kindnumber"));
		  gi.setSafesum(rs.getInt("safesum"));
		  gi.setSingleprice(rs.getLong("singleprice"));
		  gi.setUnit(rs.getString("unit"));
		  gi.setRemarks(rs.getString("remarks"));
		  
	    num--;
	    allinfo.add(gi);
	  }
	  return allinfo;
	 }
	  return null;
	}
  
	public int getcount()throws Exception
	{
		int count=0;
		String sql="select * from goodsinfo order by id";
		 ResultSet rs=stmt.executeQuery(sql);
		 rs.last();
		 count=rs.getRow();
		return count;
	}
}