package com.dut.logic.db;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.dut.dao.BatchDBConnectionUtil;
import com.dut.logic.util.DataTypeCheck;
import com.dut.logic.util.StringUtil;

public class Syouhin {

	private int nen; // smallint

	private int hagaki_id; // smallint

	private String hagaki_idstring;

	private int toukyu_id; // samllint

	private String toukyu_idstring;

	private int syouhin_id;

	private String syouhin_mei; // varchar

	private String syouhin_ryaku; // varchar

	private String bunrui; // varchar

	private int gyousya_id;

	private String keiyaku_cd; // varchar

	private int tyoutatusu;

	private int hikikaesu;

	private int hassousu;

	private int jitaku; // smallint

	private String jitakustring;

	private int nissu; // smallint

	private Date insert_date;

	private Date update_date; // datetime

	private int sum_hi;

	private int sum_ty;

	private int lose_num;

	private String toukyu_mei;

	private String people_lv_A;

	private String people_lv_B;

	private String hikikaesu_lv_A;

	private String hikikaesu_lv_B;

	private static int all_hikikaesu_A;

	private static int all_tyoutatusu_A;

	private static int all_hikikaesu_B;

	private static int all_tyoutatusu_B;

	private static String all_hikikaesu_lv_A;

	private static String all_hikikaesu_lv_B;
	
	private static String touy_mei;

	public static String getTouy_mei() {
		return touy_mei;
	}

	public static void setTouy_mei(String touy_mei) {
		Syouhin.touy_mei = touy_mei;
	}

	public String getPeople_lv_B() {
		return people_lv_B;
	}

	public void setPeople_lv_B(String people_lv_B) {
		this.people_lv_B = people_lv_B;
	}

	public String getPeople_lv_A() {
		return people_lv_A;
	}

	public void setPeople_lv_A(String people_lv_A) {
		this.people_lv_A = people_lv_A;
	}

	public String getBunrui() {
		return bunrui;
	}

	public void setBunrui(String bunrui) {
		this.bunrui = bunrui;
	}

	public int getGyousya_id() {
		return gyousya_id;
	}

	public void setGyousya_id(int gyousya_id) {
		this.gyousya_id = gyousya_id;
	}

	public int getHagaki_id() {
		return hagaki_id;
	}

	public void setHagaki_id(int hagaki_id) {
		this.hagaki_id = hagaki_id;
	}

	public int getHassousu() {
		return hassousu;
	}

	public void setHassousu(int hassousu) {
		this.hassousu = hassousu;
	}

	public int getHikikaesu() {
		return hikikaesu;
	}

	public void setHikikaesu(int hikikaesu) {
		this.hikikaesu = hikikaesu;
	}

	public Date getInsert_date() {
		return insert_date;
	}

	public void setInsert_date(Date insert_date) {
		this.insert_date = insert_date;
	}

	public int getJitaku() {
		return jitaku;
	}

	public void setJitaku(int jitaku) {
		this.jitaku = jitaku;
	}

	public String getKeiyaku_cd() {
		return keiyaku_cd;
	}

	public void setKeiyaku_cd(String keiyaku_cd) {
		this.keiyaku_cd = keiyaku_cd;
	}

	public int getNen() {
		return nen;
	}

	public void setNen(int nen) {
		this.nen = nen;
	}

	public int getNissu() {
		return nissu;
	}

	public void setNissu(int nissu) {
		this.nissu = nissu;
	}

	public int getSyouhin_id() {
		return syouhin_id;
	}

	public void setSyouhin_id(int syouhin_id) {
		this.syouhin_id = syouhin_id;
	}

	public String getSyouhin_mei() {
		return syouhin_mei;
	}

	public void setSyouhin_mei(String syouhin_mei) {
		this.syouhin_mei = syouhin_mei;
	}

	public String getSyouhin_ryaku() {
		return syouhin_ryaku;
	}

	public void setSyouhin_ryaku(String syouhin_ryaku) {
		this.syouhin_ryaku = syouhin_ryaku;
	}

	public int getToukyu_id() {
		return toukyu_id;
	}

	public void setToukyu_id(int toukyu_id) {
		this.toukyu_id = toukyu_id;
	}

	public int getTyoutatusu() {
		return tyoutatusu;
	}

	public void setTyoutatusu(int tyoutatusu) {
		this.tyoutatusu = tyoutatusu;
	}

	public Date getUpdate_date() {
		return update_date;
	}

	public void setUpdate_date(Date update_date) {
		this.update_date = update_date;
	}
	
	public String getJitakustring() {
		return jitakustring;
	}

	public void setJitakustring(String jitakustring) {
		this.jitakustring = jitakustring;
	}

	public String getHagaki_idstring() {
		return hagaki_idstring;
	}

	public void setHagaki_idstring(String hagaki_idstring) {
		this.hagaki_idstring = hagaki_idstring;
	}

	public String getToukyu_idstring() {
		return toukyu_idstring;
	}

	public void setToukyu_idstring(String toukyu_idstring) {
		this.toukyu_idstring = toukyu_idstring;
	}

	public int getSum_hi() {
		return sum_hi;
	}

	public void setSum_hi(int sum_hi) {
		this.sum_hi = sum_hi;
	}

	public int getSum_ty() {
		return sum_ty;
	}

	public void setSum_ty(int sum_ty) {
		this.sum_ty = sum_ty;
	}

	public int getLose_num() {
		return lose_num;
	}

	public void setLose_num(int lose_num) {
		this.lose_num = lose_num;
	}

	public String getToukyu_mei() {
		return toukyu_mei;
	}

	public void setToukyu_mei(String toukyu_mei) {
		this.toukyu_mei = toukyu_mei;
	}

	public String getHikikaesu_lv_A() {
		return hikikaesu_lv_A;
	}

	public void setHikikaesu_lv_A(String hikikaesu_lv_A) {
		this.hikikaesu_lv_A = hikikaesu_lv_A;
	}

	public String getHikikaesu_lv_B() {
		return hikikaesu_lv_B;
	}

	public void setHikikaesu_lv_B(String hikikaesu_lv_B) {
		this.hikikaesu_lv_B = hikikaesu_lv_B;
	}

	public static int getAll_hikikaesu_A() {
		return all_hikikaesu_A;
	}

	public static void setAll_hikikaesu_A(int all_hikikaesu_A) {
		Syouhin.all_hikikaesu_A = all_hikikaesu_A;
	}

	public static int getAll_hikikaesu_B() {
		return all_hikikaesu_B;
	}

	public static void setAll_hikikaesu_B(int all_hikikaesu_B) {
		Syouhin.all_hikikaesu_B = all_hikikaesu_B;
	}

	public static int getAll_tyoutatusu_A() {
		return all_tyoutatusu_A;
	}

	public static void setAll_tyoutatusu_A(int all_tyoutatusu_A) {
		Syouhin.all_tyoutatusu_A = all_tyoutatusu_A;
	}

	public static int getAll_tyoutatusu_B() {
		return all_tyoutatusu_B;
	}

	public static void setAll_tyoutatusu_B(int all_tyoutatusu_B) {
		Syouhin.all_tyoutatusu_B = all_tyoutatusu_B;
	}

	public static String getAll_hikikaesu_lv_A() {
		return all_hikikaesu_lv_A;
	}

	public static void setAll_hikikaesu_lv_A(String all_hikikaesu_lv_A) {
		Syouhin.all_hikikaesu_lv_A = all_hikikaesu_lv_A;
	}

	public static String getAll_hikikaesu_lv_B() {
		return all_hikikaesu_lv_B;
	}

	public static void setAll_hikikaesu_lv_B(String all_hikikaesu_lv_B) {
		Syouhin.all_hikikaesu_lv_B = all_hikikaesu_lv_B;
	}

	

	/**
	 * 通過聯合查詢,查出顯示人氣率表中的數據,返回鏈表List
	 * 
	 * @param sqlstr
	 * @param page
	 * @return List
	 * @author 王楠楠 2006-04-19
	 */

	public static List num_l(String str,int n) {
		int sum_hiki = 0;
		int sum_tyou = 0;
		/** 拼湊查詢語句 */
		String sql_str = "SELECT distinct C.TOUKYU_MEI,D.* FROM SEIKYU AS A,TOUKYUCD AS C,SYOUHIN AS D WHERE A.TOUKYU_ID=D.TOUKYU_ID AND D.TOUKYU_ID=C.TOUKYU_ID"
				+ str+" AND A.TOUKYU_ID = "+n;
		String sum_hi = "SELECT sum(E.HIKIKAESU) FROM ("+sql_str+") as E";
		String sum_ty = "SELECT sum(E.TYOUTATUSU) FROM ("+sql_str+") as E";
		System.out.println(sql_str);
		System.out.println(sum_hi);
		System.out.println(sum_ty); 
		
		/** 調用求某字段最大值的方法求引換數的最大值 */
		sum_hiki = Syouhin.sum_hi_ty(sum_hi);
		/** 調用求某字段最大值的方法求調達本數的最大值 */
		sum_tyou = Syouhin.sum_hi_ty(sum_ty);
		List list = new ArrayList();
		BatchDBConnectionUtil con = new BatchDBConnectionUtil();
		ResultSet rs = con.executeQuery(sql_str);
		try {
			while(rs.next()){
				Syouhin sy = new Syouhin();
				System.out.println(rs.getString("TOUKYU_MEI")+",");
				System.out.println(rs.getString("SYOUHIN_MEI")+",");
				System.out.println(rs.getInt("HIKIKAESU")+",");
				System.out.println(rs.getInt("TYOUTATUSU")+",");				
				System.out.println((rs.getInt("HIKIKAESU")-rs.getInt("TYOUTATUSU"))+",");
				System.out.println(Seikyu.hikikae_lv(rs.getInt("HIKIKAESU"),rs.getInt("TYOUTATUSU"))+",");
				System.out.println(Seikyu.hikikae_lv(rs.getInt("HIKIKAESU"),sum_hiki)+",");
			    
				Syouhin.setTouy_mei(StringUtil.trans_date(rs.getString("TOUKYU_MEI")));
				sy.setToukyu_mei(StringUtil.trans_date(rs.getString("TOUKYU_MEI")));
				sy.setSyouhin_mei(StringUtil.trans_date(rs.getString("SYOUHIN_MEI")));
				sy.setHikikaesu(rs.getInt("HIKIKAESU"));
				sy.setTyoutatusu(rs.getInt("TYOUTATUSU"));
				sy.setLose_num(rs.getInt("HIKIKAESU")-rs.getInt("TYOUTATUSU"));
				
				/** 調用計算比率的函數求出引換率 */
				sy.setHikikaesu_lv_A(Seikyu.hikikae_lv(rs.getInt("HIKIKAESU"),rs.getInt("TYOUTATUSU")));
				
				/** 調用計算比率的函數求出人氣率 */
				sy.setPeople_lv_A(Seikyu.hikikae_lv(rs.getInt("HIKIKAESU"),sum_hiki));
				list.add(sy);
			}
			Syouhin.setAll_tyoutatusu_A(sum_tyou);
			Syouhin.setAll_hikikaesu_A(sum_hiki);
			Syouhin.setAll_hikikaesu_lv_A(Seikyu.hikikae_lv(sum_hiki,
					sum_tyou));
			System.out.println("list(add);;;;"+list);
			System.out.println(",,,,,,,"+Seikyu.hikikae_lv(10,100000));
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
	
	/**
	 * 查詢出符合條件的隱換數或配達本數的分別的總和
	 * 
	 * @param nen
	 * @param hagaki_id
	 * @param toukyu_id
	 * @param syouhin_id
	 * @return int sum
	 * @author 王楠楠????? 2006-04-17
	 */
	public static int sum_hi_ty(String st){
		int sum=0;
		BatchDBConnectionUtil con = new BatchDBConnectionUtil();
		ResultSet rs = con.executeQuery(st);
		try {
			while(rs.next()){
				sum = rs.getInt(1);
				System.out.println("sum_hi,sum_ty????"+sum);
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sum;
	}
	
	/**
	 * 該函數用于查詢賞品名略稱????????
	 * @param nen,hagaki_id,toukyu_id,syouhin_id
	 * @return String
	 * @author 楊麗娟 2006-04-10
	 */
	public static String selectSyouhin_ryaku(int nen,int hagaki_id,
						int toukyu_id, int syouhin_id){
		String mei = null;
		String Sqlstr = "SELECT SYOUHIN_RYAKU FROM SYOUHIN "
						+ "WHERE NEN = " + nen
						+ " AND HAGAKI_ID = " + hagaki_id
						+ " AND TOUKYU_ID = " + toukyu_id
						+ " AND SYOUHIN_ID = " + syouhin_id;
		System.out.println("selectSyouhin_ryaku Sqlstr is !!~~~~~>" + Sqlstr);
		BatchDBConnectionUtil con = new BatchDBConnectionUtil();
		ResultSet rs = con.executeQuery(Sqlstr);
		try {
			if(rs.next()){
				mei = StringUtil.trans_date(rs.getString("SYOUHIN_RYAKU"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		con.close();
		return mei;
	}

	
	/**
	 * 該函數用于獲得Syouhin表的結果集List
	 * @param sqlstr
	 * @return List
	 * @author 楊麗娟 2006-04-13
	 */
	public static List getSyouhinData(String sqlstr){
		List SyouhinList = new ArrayList();
		BatchDBConnectionUtil con = new BatchDBConnectionUtil();
		ResultSet rs = con.executeQuery(sqlstr);
		try {
			//以下代碼用于封裝List
			while(rs.next()){
				Syouhin SYH = new Syouhin();
				
				int nen = Integer.parseInt(rs.getString("NEN"));
				SYH.setNen(nen);
				
				int hagaki_id = Integer.parseInt(rs.getString("HAGAKI_ID"));
				SYH.setHagaki_id(hagaki_id);
				
				int toukyu_id = Integer.parseInt(rs.getString("TOUKYU_ID"));
				SYH.setToukyu_id(toukyu_id);
				
				int syouhin_id = Integer.parseInt(rs.getString("SYOUHIN_ID"));
				SYH.setSyouhin_id(syouhin_id);
				
				String syouhin_mei = StringUtil.trans_date(rs.getString("SYOUHIN_MEI"));
				SYH.setSyouhin_mei(syouhin_mei);
				
				String syouhin_ryaku = StringUtil.trans_date(rs.getString("SYOUHIN_RYAKU"));
				SYH.setSyouhin_ryaku(syouhin_ryaku);
				
				String bunrui = StringUtil.trans_date(rs.getString("BUNRUI"));
				SYH.setBunrui(bunrui);
				
				int gyousya_id = Integer.parseInt(rs.getString("GYOUSYA_ID"));
				SYH.setGyousya_id(gyousya_id);
				
				String keiyaku_cd = StringUtil.trans_date(rs.getString("KEIYAKU_CD"));
				SYH.setKeiyaku_cd(keiyaku_cd);
				
				int tyoutatusu = Integer.parseInt(rs.getString("TYOUTATUSU"));
				SYH.setTyoutatusu(tyoutatusu);
				
				int hikikaesu = Integer.parseInt(rs.getString("HIKIKAESU"));
				SYH.setHikikaesu(hikikaesu);
				
				int hassousu = Integer.parseInt(rs.getString("HASSOUSU"));
				SYH.setHassousu(hassousu);
				
				int jitaku = Integer.parseInt(rs.getString("JITAKU"));
				SYH.setJitaku(jitaku);
				
				int nissu = Integer.parseInt(rs.getString("NISSU"));
				SYH.setNissu(nissu);
				
				Date insert_date = DataTypeCheck.StringtoDate(rs.getString("INSERT_DATE"));
				SYH.setInsert_date(insert_date);
				
				Date update_date = DataTypeCheck.StringtoDate(rs.getString("UPDATE_DATE"));
				SYH.setUpdate_date(update_date);
				
				SyouhinList.add(SYH);

			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return SyouhinList;
	}

}