package dal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import common.JdbcUtil;
import domain.DeviceCatagory;
import domain.DevicesInfo;

public class DevicesDao {

	private Log log = LogFactory.getLog(UserDAO.class);
	private Connection conn = null;
	public DevicesDao(){
		conn = JdbcUtil.getConnection();
	}
	/**
	 * 
	 * @param dc
	 * @return
	 */
	public boolean insertDb(DevicesInfo dc){
		boolean flag = false;
		PreparedStatement stat = null;
		try {
			conn.setAutoCommit(false);
			String insertsql = "insert into devices(CategoryId,deviceName,deviceDesc,deviceMaker,deviceFunction,deviceCode) values(?,?,?,?,?,?)";
			stat = conn.prepareStatement(insertsql);
			stat.setInt(1, dc.getCatagory().getCategoryId());
			stat.setString(2, dc.getDeviceName());
			stat.setString(3, dc.getDeviceDesc());
			stat.setString(4, dc.getDeviceMaker());
			stat.setString(5, dc.getDeviceFunction());
			stat.setString(6, dc.getDeviceCode());
			int i = stat.executeUpdate();
			if (i == 1) {
				conn.commit();
				flag = true;
			} else {
				JdbcUtil.BackDb(conn);
			}
		} catch (SQLException e) {
			log.error(e);
			JdbcUtil.BackDb(conn);
			e.printStackTrace();

		} catch (Exception e) {
			log.fatal(e);
			e.printStackTrace();
		} finally {
			JdbcUtil.ShutDownDB(conn);
		}
		return flag;
	}
	public boolean deleteDB(DevicesInfo dc){
		PreparedStatement stat = null;
		boolean flag = false;
		String sql = "delete from devices where deviceId=" +dc.getDeviceId();
		try {
			conn.setAutoCommit(false);
			stat = conn.prepareStatement(sql);
			int m = stat.executeUpdate();
			if (m == 1) {
				conn.commit();
				flag = true;
			} else {
				JdbcUtil.BackDb(conn);
			}
		} catch (SQLException e) {
			log.error(e);
			JdbcUtil.BackDb(conn);
			e.printStackTrace();
		} catch (Exception e) {
			log.fatal(e);
			e.printStackTrace();
		} finally {
			JdbcUtil.ShutDownDB(conn);
		}
		return flag;
	}
	
	public DevicesInfo getDevices(String id){
		PreparedStatement stat = null;
		ResultSet set = null;
		String sql = "select s.*,y.catagoryName,y.catogoryCode from devices s,devicecategory y where s.CategoryId=y.CategoryId and deviceId=" + id;
		DevicesInfo dc = new DevicesInfo();
		try {
			stat = conn.prepareStatement(sql);
			set = stat.executeQuery();
			while (set.next()) {
				
				int deviceId = set.getInt("deviceId");
				int catagoryId = set.getInt("CategoryId");
				String code = set.getString("deviceCode");
				String name = set.getString("deviceName");
				String desc = set.getString("deviceDesc");
				String market=set.getString("deviceMaker");
				String func=set.getString("deviceFunction");
				String catagoryName=set.getString("catagoryName");
				String catagoryCode=set.getString("catogoryCode");
				DeviceCatagory dy=new DeviceCatagory();
				dy.setCategoryId(catagoryId);
				dy.setCatagoryName(catagoryName);
				dy.setCatogoryCode(catagoryCode);

				dc.setCatagory(dy);
				dc.setDeviceId(deviceId);
				dc.setDeviceCode(code);
				dc.setDeviceName(name);
				dc.setDeviceFunction(func);
				dc.setDeviceMaker(market);
				dc.setDeviceDesc(desc);
			}
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
		} catch (Exception e) {
			log.fatal(e);
			e.printStackTrace();
		} finally {
			JdbcUtil.ShutDownDB(conn);
		}
		return dc;
	}
	
	public List<DevicesInfo> getDeviceCatagoryList(){
		PreparedStatement stat = null;
		ResultSet set = null;
		String sql = "select s.*,y.catagoryName,y.catogoryCode from devices s,devicecategory y where s.CategoryId=y.CategoryId ";
		List<DevicesInfo> dclist = new ArrayList<DevicesInfo>();
		try {
			stat = conn.prepareStatement(sql);
			set = stat.executeQuery();
			while (set.next()) {
				DevicesInfo dc = new DevicesInfo();
				int deviceId = set.getInt("deviceId");
				int catagoryId = set.getInt("CategoryId");
				String code = set.getString("deviceCode");
				String name = set.getString("deviceName");
				String desc = set.getString("deviceDesc");
				String market=set.getString("deviceMaker");
				String func=set.getString("deviceFunction");
				String catagoryName=set.getString("catagoryName");
				String catagoryCode=set.getString("catogoryCode");
				DeviceCatagory dy=new DeviceCatagory();
				dy.setCategoryId(catagoryId);
				dy.setCatagoryName(catagoryName);
				dy.setCatogoryCode(catagoryCode);

				dc.setCatagory(dy);
				dc.setDeviceId(deviceId);
				dc.setDeviceCode(code);
				dc.setDeviceName(name);
				dc.setDeviceFunction(func);
				dc.setDeviceMaker(market);
				dc.setDeviceDesc(desc);
				dclist.add(dc);
			}
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
		} catch (Exception e) {
			log.fatal(e);
			e.printStackTrace();
		} finally {
			JdbcUtil.ShutDownDB(conn);
		}
		return dclist;
	}
	public List<DevicesInfo> getDeviceCatagoryList(DevicesInfo d){
		PreparedStatement stat = null;
		ResultSet set = null;
		String sql = getSql(d);
		List<DevicesInfo> dclist = new ArrayList<DevicesInfo>();
		try {
			stat = conn.prepareStatement(sql);
			set = stat.executeQuery();
			while (set.next()) {
				DevicesInfo dc = new DevicesInfo();
				int deviceId = set.getInt("deviceId");
				int catagoryId = set.getInt("CategoryId");
				String code = set.getString("deviceCode");
				String name = set.getString("deviceName");
				String desc = set.getString("deviceDesc");
				String market=set.getString("deviceMaker");
				String func=set.getString("deviceFunction");
				String catagoryName=set.getString("catagoryName");
				String catagoryCode=set.getString("catogoryCode");
				DeviceCatagory dy=new DeviceCatagory();
				dy.setCategoryId(catagoryId);
				dy.setCatagoryName(catagoryName);
				dy.setCatogoryCode(catagoryCode);

				dc.setCatagory(dy);
				dc.setDeviceId(deviceId);
				dc.setDeviceCode(code);
				dc.setDeviceName(name);
				dc.setDeviceFunction(func);
				dc.setDeviceMaker(market);
				dc.setDeviceDesc(desc);
				dclist.add(dc);
			}
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
		} catch (Exception e) {
			log.fatal(e);
			e.printStackTrace();
		} finally {
			JdbcUtil.ShutDownDB(conn);
		}
		return dclist;
	}
	public boolean updataDB(DevicesInfo dc){
		PreparedStatement stat = null;
		boolean flag = false;
		String sql = "update devices set CategoryId=?,deviceName=?,deviceDesc=?,deviceCode=?,deviceMaker=?,deviceFunction=? where deviceId=?";
		try {
			conn.setAutoCommit(false);
			stat = conn.prepareStatement(sql);
			stat.setInt(1, dc.getCatagory().getCategoryId());
			stat.setString(2, dc.getDeviceName());
			stat.setString(3, dc.getDeviceDesc());
			stat.setString(4, dc.getDeviceCode());
			stat.setString(5, dc.getDeviceMaker());
			stat.setString(6, dc.getDeviceFunction());
			stat.setInt(7, dc.getDeviceId());
			int i = stat.executeUpdate();
			if (i == 1) {
				flag = true;
				conn.commit();
			} else {
				JdbcUtil.BackDb(conn);
			}
		} catch (SQLException e) {
			log.error(e);
			JdbcUtil.BackDb(conn);
			e.printStackTrace();
		} catch (Exception e) {
			log.fatal(e);
			e.printStackTrace();
		} finally {
			JdbcUtil.ShutDownDB(conn);
		}
		return flag;
	}
	
	private String getSql(DevicesInfo d){
		StringBuffer sb = new StringBuffer( "select s.*,y.catagoryName,y.catogoryCode from devices s,devicecategory y where s.CategoryId=y.CategoryId ");
		if(d!=null){
			if(d.getDeviceId()!=0){
				sb.append(" and s.deviceId="+d.getDeviceId());
			}
			if(d.getDeviceName()!=null){
				sb.append(" and s.deviceName like '%"+d.getDeviceName()+"%'");
			}
			if(d.getDeviceCode()!=null){
				sb.append(" and s.deviceCode like '%"+d.getDeviceCode()+"%'");
			}
			if(d.getCatagory()!=null&&d.getCatagory().getCategoryId()!=0){
				sb.append(" and y.CategoryId ="+d.getCatagory().getCategoryId());
			}
			if(d.getCatagory()!=null&&d.getCatagory().getCatagoryName()!=null){
				sb.append(" and y.catagoryName like '%"+d.getCatagory().getCatagoryName()+"%'");
			}
		}
		return sb.toString();
	}
}