package com.dut.logic.logic;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.dut.dao.BatchDBConnectionUtil;
import com.dut.logic.db.Hagakicd;
import com.dut.logic.db.Kensyou;
import com.dut.logic.db.Toukyucd;
import com.dut.logic.util.StringUtil;

public class KensyouLogic {
	
	static StringUtil U2=new StringUtil();//Date Turn Method		
//	static BatchDBConnectionUtil BU1=new BatchDBConnectionUtil();//Date Lianjie
	static SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");

	public static ArrayList A_Yeshu()
	{
		BatchDBConnectionUtil BU1=new BatchDBConnectionUtil();
		/*****************Yeshu*********************/
		String sq1="select HAGAKI_MEI,HAGAKI_ID from Hagakicd ORDER BY HAGAKI_ID ASC";
		ResultSet rs1=BU1.executeQuery(sq1);
		ArrayList A1=new ArrayList();
		
		try {
			while(rs1.next())
			{
				String H_mei=U2.trans_date(rs1.getString("HAGAKI_MEI"));
				int H_id=rs1.getInt("HAGAKI_ID");
				Hagakicd Ha=new Hagakicd();
				Ha.setHagaki_mei(H_mei);
				Ha.setHagaki_id(H_id);
				A1.add(Ha);
			}
			BU1.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return A1;
	}
	
	public static ArrayList A_Dengji()
	{
		BatchDBConnectionUtil BU1=new BatchDBConnectionUtil();
		/*****************dengji*********************/
		String sq2="select toukyu_id,toukyu_mei from Toukyucd ORDER BY toukyu_id ASC";
		ResultSet rs2=BU1.executeQuery(sq2);
		ArrayList A2=new ArrayList();
		
		try {
			while(rs2.next())
			{

				String T_mei=U2.trans_date(rs2.getString("toukyu_mei"));
				int T_id=rs2.getInt("toukyu_id");
				Toukyucd tou=new Toukyucd ();
				tou.setToukyu_id(T_id);
				tou.setToukyu_mei(T_mei);
				A2.add(tou);
			}
			BU1.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return A2;
	}
	
	/** **************Id Turn Content************* */
	public static String getDataNameThrough(String sql) {
		BatchDBConnectionUtil BU2 = new BatchDBConnectionUtil();
		ResultSet rs = BU2.executeQuery(sql);
		String mei = null;
		try {
			while (rs.next()) {
				mei = rs.getString(1);
				break;
			}
			BU2.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return mei;
	}
	public static ArrayList A_search()
	{
		BatchDBConnectionUtil BU1=new BatchDBConnectionUtil();
		ArrayList A3=new ArrayList();
		String sq_serch="select * from kensyou order by uketuke_s_date desc,nen desc,hagaki_id asc,toukyu_id asc";
		ResultSet rs3=BU1.executeQuery(sq_serch);
		try {
			while(rs3.next())
			{
				int nen=rs3.getInt("nen");
				int hagaki_id=rs3.getInt("hagaki_id");
				int toukyu_id=rs3.getInt("toukyu_id");
				int sketa=rs3.getInt("sketa");
				System.out.println("hagaki_id=="+hagaki_id);
				System.out.println("toukyu_id=="+toukyu_id);					

				Date uketuke_s_date=rs3.getDate("uketuke_s_date");
				Date uketuke_e_date=rs3.getDate("uketuke_e_date");
				Date insert_date=rs3.getDate("insert_date");
				Date update_date=rs3.getDate("update_date");
				
				String uketuke_s=date.format(new java.util.Date());
				String uketuke_e=date.format(new java.util.Date());
				String insert=date.format(new java.util.Date());	
				String update=date.format(new java.util.Date());				
				
				Kensyou ken=new Kensyou();
				
				String sq1="select hagaki_mei from hagakicd where Hagaki_id="+hagaki_id;
				System.out.println("select=="+sq1);
				String sq2="select toukyu_mei from toukyucd where Toukyu_id="+toukyu_id;
				String hagaki_mei=StringUtil.trans_date(getDataNameThrough(sq1));
				String toukyu_mei=StringUtil.trans_date(getDataNameThrough(sq2));
				
				ken.setNen(nen);
				ken.setHagaki_id(hagaki_id);				
				ken.setToukyu_id(toukyu_id);				
				ken.setHagaki_mei(hagaki_mei);
				ken.setToukyu_mei(toukyu_mei);
				ken.setSketa(sketa);
				ken.setUketuke_s_date(uketuke_s_date);
				ken.setUketuke_e_date(uketuke_e_date);
				ken.setInsert_date(insert_date);
				ken.setUpdate_date(update_date);
				
				A3.add(ken);
				
			}
			BU1.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return A3;
	}
	
	public static ResultSet Select(String sq)
	{	
		BatchDBConnectionUtil BU1=new BatchDBConnectionUtil();
		ResultSet rs=BU1.executeQuery(sq);
		return rs;	
	}
	public static void Insert_Delete(String sq)
	{	
		BatchDBConnectionUtil BU1=new BatchDBConnectionUtil();
		BU1.executeUpdate(sq);
	}
	public static ArrayList A_S1(String sq)
	{
		BatchDBConnectionUtil BU1=new BatchDBConnectionUtil();
		ArrayList A_D=new ArrayList();
		
		//String sq1="select * from kensyou where nen="+hidden1+" and hagaki_id="+hidden2+" and toukyu_id="+hidden3;
		//System.out.println(sq);
		ResultSet rs1=BU1.executeQuery(sq);
		try {
			while(rs1.next())
			{
				
				int nen=rs1.getInt("nen");
				int hagaki_id=rs1.getInt("hagaki_id");
				int toukyu_id=rs1.getInt("toukyu_id");
				int sketa=rs1.getInt("sketa");

				Date uketuke_s_date=rs1.getDate("uketuke_s_date");
				Date uketuke_e_date=rs1.getDate("uketuke_e_date");
				Date insert_date=rs1.getDate("insert_date");
				Date update_date=rs1.getDate("update_date");
				
				System.out.println("nen22=="+nen);
				System.out.println("update_date22=="+update_date);
				
				String sq2="select hagaki_mei from hagakicd where Hagaki_id="+hagaki_id;
				String sq3="select toukyu_mei from toukyucd where Toukyu_id="+toukyu_id;
				String hagaki_mei=StringUtil.trans_date(getDataNameThrough(sq2));
				String toukyu_mei=StringUtil.trans_date(getDataNameThrough(sq3));
				Kensyou ken_D=new Kensyou();
				
				ken_D.setNen(nen);
				
				ken_D.setHagaki_id(hagaki_id);				
				ken_D.setToukyu_id(toukyu_id);				
				ken_D.setHagaki_mei(hagaki_mei);
				ken_D.setToukyu_mei(toukyu_mei);
				ken_D.setSketa(sketa);
				ken_D.setUketuke_s_date(uketuke_s_date);
				ken_D.setUketuke_e_date(uketuke_e_date);
				ken_D.setInsert_date(insert_date);
				ken_D.setUpdate_date(update_date);
				
				A_D.add(ken_D);
						
			}
			BU1.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return A_D;
		
	}
	

}