today

      package com.fnst.spif.sepg.dao;

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

import com.fnst.spif.database.DBTable;
import com.fnst.spif.exception.SPIFException;
import com.fnst.spif.sepg.bean.ModelItem;
import com.fnst.spif.sepg.exception.DuplicateException;
import com.fnst.spif.sepg.exception.NotExistException;

public class ModelDao
{
	/**
	 * Add a new Software Life Circle Mode to database
	 * @param model Software Life Circle Model entity
	 * @throws DuplicateException 
	 * @throws SPIFException 
	 * @throws SQLException 
	 */
	public static void addModelItem(ModelItem model,String[] phases) throws DuplicateException, SQLException, SPIFException
	{
		if(modelExists(model.getSubId()))
		{
			throw new DuplicateException();
		}
		else
		{
			DBTable table = new DBTable();
			try
			{
				table.connect();
				String sql = "INSERT INTO spif_dictionary(id,sub_id,name_cn,name_jp,name_en,moo,remark,status)"
					+"VALUES(5,'"+model.getSubId()+"','"+model.getName_cn()+"','"+model.getName_jp()+"','"+model.getName_en()
					+"',"+model.getMoo()+",'"+model.getRemark()+"',1)";
				table.updateRecord(sql);				
				PhaseDao.addPhasesByModelId(model.getSubId(), phases);
			} 
			finally
			{
				table.disconnect();
			}
		}
	}
	
	/**
	 * Update a Software Life Circle Mode in the database
	 * @param model
	 * @throws SPIFException 
	 * @throws DuplicateException 
	 * @throws SQLException 
	 * @throws NotExistException 
	 */
	public static void updateModelItem(ModelItem model,String[] phases) throws SQLException, SPIFException, NotExistException
	{
		if(!modelExists(model.getSubId()))
		{
			throw new NotExistException();			
		}
		else
		{
			DBTable table = new DBTable();
			try
			{
				table.connect();
				String sql = "UPDATE spif_dictionary SET name_cn = '"
						+ model.getName_cn() + "', name_jp = '"
						+ model.getName_jp() + "', name_en = '"
						+ model.getName_en() + "', moo = " + model.getMoo()
						+ ",remark = '" + model.getRemark()
						+ "' WHERE id = 5 AND sub_id = '" + model.getSubId()
						+ "'";
				// update model_phase
				// remove legacy value first
				PhaseDao.removePhasesByModelId(model.getSubId());
				// and then, add new phases
				PhaseDao.addPhasesByModelId(model.getSubId(), phases);
			} finally
			{
				table.disconnect();
			}
		}
	}
	
	/**
	 * Check if there already exists a model according to subid
	 * @param id
	 * @return
	 * @throws SPIFException 
	 * @throws SQLException 
	 */
	public static boolean modelExists(String subId) throws SPIFException, SQLException
	{
		DBTable table = new DBTable();
		table.connect();
		String sql = "SELECT COUNT(*) FROM spif_dictionary WHERE sub_id = '" + subId + "'";
		try
		{
			ResultSet rs = table.findRecord(sql);
			if(rs.next())
			{
				if(rs.getInt(1) > 0)
					return true;
				else
					return false;
			}
		}
		finally
		{
			table.disconnect();
		}
		
		return false;
	}	
	
	/**
	 * Search models by name and status
	 * @param name Search condition: cn, jp or en
	 * @param status 2:all, 1:valid, 0:invalid
	 * @return List of selected models
	 * @throws SQLException
	 * @throws SPIFException
	 */
	public static List searchModel(String name, int status) throws SQLException, SPIFException
	{
		DBTable table = new DBTable();		
		table.connect();
		String sql = "";
		//sql syntax of selecting only by name
		if(status == 2)	//2 stands for all
		{
			System.out.println("status1");
			sql = "SELECT sub_id,moo,name_cn,name_jp,name_en,status FROM spif_dictionary WHERE id = 5"
				+" AND (name_cn LIKE '%" + name + "%' OR name_jp LIKE '%" + name + "%' OR name_en LIKE '%" + name + "%') ORDER BY moo";
		}
		//sql syntax of selecting by status and name
		else
		{
			System.out.println("status2");
			sql = "SELECT sub_id,moo,name_cn,name_jp,name_en,status FROM spif_dictionary WHERE id = 5"
			+" AND status = " + status +" AND (name_cn LIKE '%" + name + "%' OR name_jp LIKE '%" + name + "%' OR name_en LIKE '%" + name + "%') ORDER BY moo";
		}	
		System.out.println(sql);
		//list that contains models
		List models = null;
		try
		{
			ResultSet rs = table.findRecord(sql);
			models = new ArrayList();
			ModelItem item = null;
			while(rs.next())
			{
				item = new ModelItem();
				item.setSubId(rs.getString("sub_id"));
				item.setMoo(rs.getInt("moo"));
				item.setName_cn(rs.getString("name_cn"));
				item.setName_jp(rs.getString("name_jp"));
				item.setName_en(rs.getString("name_en"));
				item.setStatus(rs.getInt("status"));
				models.add(item);
			}
			return models;
		} 			
		finally
		{
			table.disconnect();			
		}		
	}
	
	/**
	 * Get all models with no condition
	 * @return List of all models
	 * @throws SQLException
	 * @throws SPIFException
	 */
	public static List getModelList() throws SQLException, SPIFException
	{		
		DBTable table = new DBTable();		
		table.connect();		
		String sql = "SELECT sub_id,moo,name_cn,name_jp,name_en,status FROM spif_dictionary WHERE id = 5";
		try
		{
			ResultSet rs = table.findRecord(sql);
			List models = new ArrayList();
			ModelItem item = null;
			while(rs.next())
			{
				item = new ModelItem();
				item.setSubId(rs.getString("sub_id"));
				item.setMoo(rs.getInt("moo"));
				item.setName_cn(rs.getString("name_cn"));
				item.setName_jp(rs.getString("name_jp"));
				item.setName_en(rs.getString("name_en"));
				item.setStatus(rs.getInt("status"));
				models.add(item);
			}
			return models;
		} 		
		finally
		{
			table.disconnect();			
		}		
	}	
	public static ModelItem getModelById(String id) throws SPIFException, SQLException
	{
		DBTable table = new DBTable();		
		table.connect();		
		String sql = "SELECT name_cn,name_jp,name_en,moo,remark FROM spif_dictionary WHERE sub_id = '"+id+"'";
		try
		{
			ResultSet rs = table.findRecord(sql);
			if(rs.next())
			{
				ModelItem model = new ModelItem();
				model.setSubId(id);
				model.setName_cn(rs.getString("name_cn"));
				model.setName_jp(rs.getString("name_jp"));
				model.setName_en(rs.getString("name_en"));
				model.setMoo(rs.getInt("moo"));
				model.setRemark(rs.getString("remark"));
				return model;
			}
			else
			{
				return null;
			}
		}
		finally
		{
			table.disconnect();
		}
	}
	
	public static void main(String[] args) throws SQLException, SPIFException
	{
		ModelItem model = new ModelItem();	
		model.setName_cn("中文");
		model.setName_jp("日本语");
		model.setName_en("English");
		model.setMoo(12);
		model.setRemark("some remarks");
		model.setSubId("general");
		String sql = "UPDATE spif_dictionary SET name_cn = '" + model.getName_cn()
		+ "', name_jp = '" + model.getName_jp() + "', name_en = '" + model.getName_en()
		+ "', moo = "+model.getMoo()+",remark = '" + model.getRemark() + "' WHERE id = 5 AND sub_id = '"
		+ model.getSubId() + "'";
		System.out.println(sql);
	}
}