package com.cim.idm.mwmsextend.generic.util;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.SocketException;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.net.ftp.FTPClient;
import org.springframework.jdbc.core.JdbcTemplate;

import com.cim.idm.framework.IDMFrameServiceProxy;
import com.cim.idm.framework.util.sys.SystemPropHelper;

public class PolicyUtil {
	private static Log logger = LogFactory.getLog(PolicyUtil.class);
	public static String NEWLINE = SystemPropHelper.CR;

	/*
	* Name : getInspactionProgramInfo
	* Desc : This function is getInspactionProgramInfo
	* Author : ZXY IDM Team
	* Date : 2011.04.11
	*/
	public InspectionProgramInfo getInspactionProgramInfo(String machineName, String lotName) {
		String sql ="SELECT M.MACHINENAME,R.MACHINERECIPENAME,R.INITIALIZEDFLAG, PO.CONDITIONID, L.PRODUCTSPECNAME" +
        " FROM MACHINE M ,LOT L, POSMACHINERECIPE R, TPMPOLICY PO " +
	    " WHERE L.LOTNAME=?" +
        " AND M.MACHINENAME=?" +
		" AND L.PRODUCTSPECNAME=PO.PRODUCTSPECNAME" +
		" AND L.PRODUCTSPECVERSION=PO.PRODUCTSPECVERSION" +
		" AND M.MACHINENAME=PO.MACHINENAME" +
		" AND PO.CONDITIONID=R.CONDITIONID" ;
		
		JdbcTemplate jdbcTemplate = IDMFrameServiceProxy.getSqlTemplate().getJdbcTemplate();
		
		InspectionProgramInfo info=null;
		Map queryForMap = null;
		
		try {
		   queryForMap = jdbcTemplate.queryForMap(sql, new Object[] {lotName, machineName});
		  
		  info = new InspectionProgramInfo();
		  String conditionId = (String) queryForMap.get("CONDITIONID");
		  info.setconditionId(conditionId);
		  String programName = (String) queryForMap.get("MACHINERECIPENAME");
		  int lastIndexOf = programName.lastIndexOf("_");
		  String programVersion = programName.substring(lastIndexOf+1);
		  programName = programName.substring(0,lastIndexOf);
		  info.setProgramName(programName);
		  info.setProgramVersion(programVersion);
		  info.setInitializedFlag((String) queryForMap.get("INITIALIZEDFLAG"));
		  
		  }catch (Throwable e) {
		    return new InspectionProgramInfo(); 
		  }
		
		sql = "SELECT P.* " +
		"  FROM RECIPENAMESPACE R1," +
		"       RECIPENAMESPACE R2," +
		"       RECIPENAMESPACE R3," +
		"       RECIPEPARAMETER P " +
		" WHERE R3.RECIPENAMESPACENAME=?" +
		"   AND R3.SUPERRECIPENAMESPACENAME=R2.RECIPENAMESPACENAME" +
		"   AND R2.SUPERRECIPENAMESPACENAME=R1.RECIPENAMESPACENAME" +
		"   AND R1.RECIPENAMESPACENAME = P.RECIPENAMESPACENAME" +
		"   AND P.RECIPENAME = 'FTPInfo'";
		List queryForList = jdbcTemplate.queryForList(sql, new Object[] {queryForMap.get("MACHINERECIPENAME")});
		for (Object obj : queryForList) {
			Map map = (Map) obj;
			Object key = map.get("RECIPEPARAMETERNAME");
			String value = (String) map.get("TARGET");
			if(key.equals("Ip")) {
				info.setIp(value);
			}
			else if(key.equals("home")) {
				info.setHome(value);
			}
			else if(key.equals("passwd")) {
				info.setPasswd(value);
			}
			else if(key.equals("user")) {
				info.setUser(value);
			}
		}
		StringBuilder path = new StringBuilder();
		path = path.append(machineName);
		path = path.append("/");
		path = path.append(queryForMap.get("PRODUCTSPECNAME"));
		path = path.append("/");
		info.setProgramPath(info.getHome()+"/"+path.toString());
		return info;
	}
	
	/*
	* Name : ftpUpload
	* Desc : This function is ftpUpload
	* Author : ZXY IDM Team
	* Date : 2011.04.11
	*/
	public void ftpUpload(InspectionProgramInfo info, String conditionId) throws SocketException, IOException  {
		if( info.getProgramName().isEmpty()) 
			return;
		String ip = info.getIp();
		int port = Integer.valueOf(info.getPort());
		String password = info.getPasswd();
		String username = info.getUser();
		
		FTPClient client = new FTPClient();
		client.connect(ip, port);
		client.login(username, password);
		client.changeWorkingDirectory(info.getHome());
		String path = info.getProgramPath().replace(info.getHome()+"/", "");
		String[] dirs = path.split("/");
		for (String pathname : dirs) {
			if(!client.changeWorkingDirectory(pathname)) {
				if(client.makeDirectory(pathname)) {
					client.changeWorkingDirectory(pathname);
				}else {
					return;
				}
			}
		}
		
		OutputStream storeFileStream = client.storeFileStream(info.getProgramName()+"_"+info.getProgramVersion());
		BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(storeFileStream));
		JdbcTemplate jdbcTemplate = IDMFrameServiceProxy.getSqlTemplate().getJdbcTemplate();
		
		String sql = "SELECT P.RECIPENAME," +
		"       P.RECIPEPARAMETERNAME," +
		"       P.TARGET " +
		"  FROM RECIPEPARAMETER P," +
		"       POSMACHINERECIPE R " +
		" WHERE P.RECIPENAMESPACENAME=R.MACHINERECIPENAME" +
		"   AND R.CONDITIONID=?" +
		" ORDER BY P.RECIPENAME";
		
		List queryForList = jdbcTemplate.queryForList(sql, new Object[] {conditionId});
		String recipeName = "";
		for (Object object : queryForList) {
			Map map = (Map) object;
			Object rm = map.get("RECIPENAME");
			if(!recipeName.equals(rm)) {
				recipeName = (String)rm;
				writer.write(String.format("[%s]",recipeName));
				writer.write("\n");
			}
			String key = (String)map.get("RECIPEPARAMETERNAME");
			String value = (String)map.get("TARGET");
			value = value == null ?"":value;
			writer.write(String.format("%s=%s",key,value));
			writer.write("\n");
		}
		writer.close();
		client.logout();
		client.disconnect();
		logger.info("FTP Logout");
		
		sql = "UPDATE POSMACHINERECIPE SET INITIALIZEDFLAG = 'Y' " +
		"WHERE CONDITIONID = ? "; 
		jdbcTemplate.update(sql, new Object[] {conditionId});
	}
	
	/*
	* Name : getMachineRecipeName
	* Desc : This function is getMachineRecipeName
	* Author : ZXY IDM Team
	* Date : 2011.04.11
	*/
	public static String getMachineRecipeName(String factoryName, String productSpecName, String processOperationName, String machineName)
	{
		String sqlStmt =  " SELECT MACHINERECIPENAME " + NEWLINE
		                + "   FROM POSMACHINERECIPE " + NEWLINE
		                + "  WHERE CONDITIONID = " + NEWLINE
			            + "      ( SELECT CONDITIONID " + NEWLINE
		                + "          FROM TPOMPOLICY " + NEWLINE
		                + "         WHERE FACTORYNAME = ? " + NEWLINE
		                + "           AND PRODUCTSPECNAME = ? " + NEWLINE
		                + "           AND PRODUCTSPECVERSION = '00001' " + NEWLINE
		                + "           AND PROCESSOPERATIONNAME = ? " + NEWLINE
		                + "           AND PROCESSOPERATIONVERSION = '00001' " + NEWLINE
		                + "           AND MACHINENAME = ?) " + NEWLINE;
		
		Object[] bindSet = new String[]{factoryName, productSpecName, processOperationName, machineName};
		String[][] sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sqlStmt, bindSet);
		
		if(sqlResult.length > 0 && sqlResult[0].length > 0)
			return sqlResult[0][0];
		else 
			return "";
	}
	
	/*
	* Name : getAvailableMachine
	* Desc : This function is getAvailableMachine
	* Author : ZXY IDM Team
	* Date : 2011.04.11
	*/
	public static String[] getAvailableMachine(String factoryName, String productSpecName, String processOperationName)
	{
		String sqlStmt =  " SELECT MACHINENAME " + NEWLINE 
						+ "   FROM POSMACHINE " + NEWLINE
						+ "  WHERE CONDITIONID = " + NEWLINE 
						+ "      ( SELECT CONDITIONID " + NEWLINE 
						+ "          FROM TPOPOLICY " + NEWLINE
						+ "   	    WHERE FACTORYNAME= ?" + NEWLINE 
						+ "           AND PRODUCTSPECNAME = ? " + NEWLINE
						+ "           AND PRODUCTSPECVERSION = '00001' " + NEWLINE
						+ "           AND PROCESSOPERATIONNAME= ? " + NEWLINE
						+ "           AND PROCESSOPERATIONVERSION = '00001')";
		
		Object[] bindSet = new String[]{factoryName, productSpecName, processOperationName};
		String[][] sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sqlStmt, bindSet);
		
		if(sqlResult != null)
		{
			if(sqlResult.length > 0)
			{
				String[] returnMachineList = new String[sqlResult.length];
				for(int i = 0;i < returnMachineList.length; i++)
				{
					returnMachineList[i] = sqlResult[i][0];
				}
				return returnMachineList;
			}
			return new String[0];
		}
		return null;
	}

}
