package com.niat;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Pattern;

public class IT_UTIL{
    public final String DB_DRIVER = "weblogic.jdbc.sqlserver.SQLServerDriver";
	public final String DB_CONNECTION = "jdbc:bea:sqlserver://127.0.0.1:1433;DatabaseName=IT_DB";
	public final String DB_USER = "sa";
	public final String DB_PASSWORD = "111111";//先為空密碼

	private Connection con;
	private Statement  state;
	private ResultSet result;
	private PreparedStatement param;

	// 數(shù)據(jù)庫存的連接
	public IT_UTIL(){
		try{
			Class.forName(DB_DRIVER);
			con = DriverManager.getConnection(DB_CONNECTION,DB_USER,DB_PASSWORD);
		}catch(Exception e){
			System.out.println(e);
		}
	}

	// 返回 statement
	public  Statement getStmtread(){
		try{
			state = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			return state;
		}catch(Exception e){
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	// 獲取查詢結(jié)果
	public  ResultSet getRs(String sql){
		try{
			getStmtread();
			result = state.executeQuery(sql);
			return result;
		}catch(Exception e){
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	// 返回 Statement
	public  Statement getStmt(){
		try{
			state = con.createStatement();
			return state;
		}catch(Exception e){
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	// 返回 PreparedStatement
	public  PreparedStatement getPstmt(String sql){
		try{
			param = con.prepareStatement(sql);
			return param;
		}catch(Exception e){
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	// 關(guān)閉流
	public void close(){
		try{
			if(result != null){
				result.close(); 
				result = null;
			}
		}catch(Exception e){
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		try{
			if(state != null){
				state.close();
				state=null;
			}
		}catch(Exception e){
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		try{
			if(con != null){
				con.close();
				con = null; 
			}
		}catch(Exception e){
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
	}

	// 插入操作
	public int insert(String sql){
		int count=0;
		getStmt();
		try{
			count = state.executeUpdate(sql);
		}catch(Exception e){
			count = -2;
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return count;
	}

	// 更新操作
	public int update(String sql){
		int count=0;
		getStmt();
		try{
			count = state.executeUpdate(sql);
		}catch(Exception e){
			count=-2;
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		
		return count;
	}

	// 刪除操作
	public int delete(String sql){
		int count = 0;
		getStmt();
		try{
			count = state.executeUpdate(sql);
		}catch(Exception e){
			count=-2;
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		return count;
	}

    //中文字符的轉(zhuǎn)換
	public String transGB(String s){
		String temp = "";
		try{
			temp = new String(s.getBytes("iso-8859-1"));
		}catch(UnsupportedEncodingException e){
			System.out.println (e);
			temp = s;
		}
		return temp;
	}

	// 從當前編號中獲取自動生成ID,
	public String get_id(String table_name){
		String return_id = "";
		table_name = table_name.trim();
		String sql = "select * from tec_table where tableName='"+table_name+"'";

		try{
			result = getRs(sql);
			if(result.next()){
				int return_update = update("update tec_table set tableCount = tableCount+1 where tableName='"+table_name+"'");
				if(return_update != 0){
					sql = "select tableCount from tec_table where tableName='"+table_name+"'";
					result = getRs(sql);
					if(result.next()){
						return_id = result.getString(1).trim();
						return_id = exchange(return_id);
					}
					else{
						return_id = "000000001";
					}
					result.close();
				}
				else{
					System.out.println("更新當前編號表記錄失敗！");
				}
			}
			else{
				return_id = "000000001";
				//當前編號表中沒有那條記錄
				sql = "insert into tec_table values('"+table_name+"',1)";
				int return_insert = insert(sql);
				if(return_insert == 0){
					System.out.println("在當前編號表添加ID記錄失敗！");
				}
			}

		}catch (Exception e){
			System.out.println("獲取"+table_name+"表的當前編號時出錯!"+e);
		}

		return return_id;
	}

	// 將取出的字符轉(zhuǎn)換為規(guī)范的ID，供其他的函數(shù)調(diào)用
	public String exchange(String s){
		String temp = s;
		while(temp.length() < 9)
			temp = "0"+temp;
		return temp;
	}

	// 生成日期(8位:yyyyMMdd)
    public String getRQ(){
        SimpleDateFormat myFmt=new SimpleDateFormat("yyyyMMdd");
        Date now=new Date();
	    return (myFmt.format(now));
	}

	// 生成具體時間(14位:yyyyMMddHHmmss)
    public String getSJ(){
        SimpleDateFormat myFmt=new SimpleDateFormat("yyyyMMddHHmmss");
        Date now=new Date();
	    return (myFmt.format(now));
	}

	// 生成日期的格式 
	public String formatDate(String date){
		String temp = "";
		StringBuffer buffer = new StringBuffer();

		if(date.length() == 8 || date.length() == 14){
			buffer.append(date.substring(0,4));
			buffer.append("-");
			buffer.append(date.substring(4,6));
			buffer.append("-");
			buffer.append(date.substring(6,8));
			if(date.length() == 14){
				buffer.append(" ");
				buffer.append(date.substring(8,10));
				buffer.append(":");
				buffer.append(date.substring(10,12));
				buffer.append(":");
				buffer.append(date.substring(12,14));
			}
			temp = buffer.toString();
		}
		return temp;
	}
}