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

import superdb.*;
import supershopmanager.*;
/*
 * 商品銷售管理
 * 完成對商品銷售信息管理
 * 完成對商品銷售信息的插入，刪除、修改
 * 對銷售信息進行查詢和統計
 * 
 * 
 * 
 * */
public class SaleGoodsManager {
	private SuperDBConn sdb;
	private SuperInfoManager sim;
	private Statement stmt;
	public SaleGoodsManager()
	{
		sdb=new SuperDBConn();
		sim=new SuperInfoManager(sdb.getConn());
		stmt=sim.getStmt();
	}
/*--------------完成對商品銷售信息的插入----------*/
	public void addSaleGoods(SaleGoods saleGoods)
	{
		String sql="insert into salegoods(clientid,salegoodsdate," +
				"employid,goodsid," +
				"salesum,money," +
				"remarks)values('"+saleGoods.getClientid()+"',now()," +
				"'"+saleGoods.getEmployid()+"','"+saleGoods.getGoodsid()+"'," +
				"'"+saleGoods.getSalesum()+"','"+saleGoods.getMoney()+"'," +
				"'"+saleGoods.getRemarks()+"')";
		try
		{
		stmt.executeUpdate(sql);
		System.out.println("商品銷售信息插入------成功?。。。。?quot;);
		}catch(Exception e)
		{
			System.out.println("商品銷售信息插入------失敗?。。。。?quot;);
		}
		
	}
	/*-----------完成對商品銷售信息的刪除----------*/
	public void deleteSaleGoods(SaleGoods saleGoods)
	{
		String sql="delete from salegoods where id="+saleGoods.getId();
		try
		{
		stmt.executeUpdate(sql);
		System.out.println("商品銷售信息刪除------成功?。。。?！");
		}catch(Exception e)
		{
			System.out.println("商品銷售信息刪除------失?。。。。?！");
		}
	}
	/*----------完成對商品銷售信息的查詢-----------*/
	public Vector selectSaleGoods(SaleGoods saleGoods,int type)
	{
		Vector allSaleGoods=new Vector();
		String sql="";
		if(type==1)//說明按銷售單據編號查詢
		{
			sql="select * from salegoods where id="+saleGoods.getId();
		}
		if(type==2)//說明按銷售時間進行查詢
		{
			sql=sql="select * from salegoods where TO_DAYS(salegoodsdate)=TO_DAYS('"+saleGoods.getSalegoodsdate().getYear()+"-"+saleGoods.getSalegoodsdate().getMonth()+"-"+saleGoods.getSalegoodsdate().getDay()+"')";
		}
		if(type==3)//說明按員工工號查詢
		{
			sql="select * from salegoods where employid="+saleGoods.getEmployid();
		}
		if(type==4)//說明按商品編號進行查詢
		{
			sql="select * from salegoods where goodsid="+saleGoods.getGoodsid();
		}
		try
		{
			ResultSet rs=stmt.executeQuery(sql);//執行查詢
			while(rs.next())
			{
			
				SaleGoods sg=new SaleGoods();
				sg.setId(rs.getInt("id"));
				sg.setClientid(rs.getLong("clientid"));
				sg.setEmployid(rs.getLong("employid"));
				sg.setGoodsid(rs.getInt("goodsid"));
				//long cc=(rs.getTimestamp("buygoodsdate").getTime());
				//java.util.Date dd=new java.util.Date(cc);
				sg.setSalegoodsdate(rs.getTimestamp("salegoodsdate"));
				sg.setSalesum(rs.getLong("salesum"));
				sg.setMoney(rs.getLong("money"));
				sg.setRemarks(rs.getString("remarks"));
				
				
				allSaleGoods.add(sg);
				
			}
			
		}catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("商品進貨信息查詢------失敗?。。?！");
		}
		
		return allSaleGoods;
	}
	/*------------完成對商品銷售信息的修改----------*/
	public void updateSaleGoods(SaleGoods saleGoods)
	{
		String sql="update salegoods set clientid='"+saleGoods.getClientid()+"'," +
				"salegoodsdate=now()," +
				"employid='"+saleGoods.getEmployid()+"',salesum='"+saleGoods.getSalesum()+"'," +
				"money='"+saleGoods.getMoney()+"',remarks='"+saleGoods.getRemarks()+"' " +
				"where id="+saleGoods.getId();
		try
		{
		stmt.executeUpdate(sql);
		System.out.println("商品銷售信息更新------成功！！?。?！");
		}catch(Exception e)
		{
			System.out.println("商品銷售信息更新------失?。。。。?！");
		}
	}
	
	 /*------查詢分頁-----*/
	  
	public List getAllInfo(int pagesize,int nowpage) throws Exception
	{List allinfo=new ArrayList();
	String sql="select * from salegoods 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)
	  {
		  SaleGoods sg=new SaleGoods();
		  sg.setId(rs.getInt("id"));
		  sg.setClientid(rs.getLong("clientid"));
		  ////sg.setGoodsid(rs.getLong("goodsid"));
		  sg.setEmployid(rs.getLong("employid"));
		 sg.setGoodsid(rs.getInt("goodsid"));
		  sg.setSalesum(rs.getLong("salesum"));
		  sg.setMoney(rs.getLong("money"));
		  sg.setRemarks(rs.getString("remarks"));
		  sg.setSalegoodsdate(rs.getTimestamp("salegoodsdate"));
			
	    num--;
	    allinfo.add(sg);
	  }
	  return allinfo;
	 }
	  return null;
	}
  
	public int getcount()throws Exception
	{
		int count=0;
		String sql="select * from salegoods order by id";
		 ResultSet rs=stmt.executeQuery(sql);
		 rs.last();
		 count=rs.getRow();
		return count;
	}
}