package com.nsi.components.util;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.nsi.control.exceptions.NsiEventException;
import com.nsi.persistence.DataSrcUtil;
import com.nsi.persistence.IsqlDataSource;
import java.text.ParseException; 
import java.text.SimpleDateFormat; 
import java.util.Calendar; 
import java.util.Date; 


/**
 * 
 * @author xavier.huang
 *
 */
public final class MigrationUtil {
	
	private static Log log = LogFactory.getLog(MigrationUtil.class);	
	
	private static final String GET_MIGRATION_DATA_SQL="SELECT " +
															"p.proj_name project_name," +
															"p.proj_info_start_date project_start_date," +
															"p.proj_info_finish_date project_end_date," +
															"u1.user_name userid," +
															"t.btask_cost_category activity_code,"+			
															"u2.user_name project_manager," +
															"h.bh_date effort_date," +
															"(h.bh_hours + h.bh_ot_hours) effort"+ 							
												 " FROM msp_projects p," +
												 	    "pms_users u1," +
												        "pms_users u2," +
												        "pms_billing_tasks t," +
												        "pms_billing_hours h," +
												        "pms_resources r"+
												  " WHERE p.proj_id = t.proj_id " +
												  	" AND p.proj_id = h.proj_id"+
												  	" AND p.proj_id = r.proj_id"+
												  	" AND t.btask_id = h.btask_id"+
												  	" AND u1.user_id = h.user_id"+
												  	" AND u2.user_id = r.user_id"+
												  	" AND r.role_id = 9"+
												  	" AND h.bh_date > getdate() - 7"+
												  	" AND h.bh_date < getdate()"+
												  	" ORDER BY p.proj_name,u1.user_name,h.bh_date";
	
	//weeks
	private static final String Mon="monday";
	private static final String Tue="tuesday";
	private static final String Wed="wednesday";
	private static final String Thu="thursday";
	private static final String Fri="friday";
	private static final String Sat="saturday";
	private static final String Sun="sunday";
	private static final String Zero="0.00";
	
	//fields
	private static final String PorjectName="project_name";
	private static final String EffortDate="effort_date";
	private static final String UserID="userid";
	private static final String Effort="effort";
	private static final String ActivityCode="activity_code";
	private static final String BeginningDate="beginningDate";
	
	private MigrationUtil()
	{
		
	}
	
	

	private static class MigtrationUtilHolder
	{
		static final MigrationUtil migrationUtil = new MigrationUtil();
	}
	
	/**
	 * @return an instance of MigrationUtil
	 */
	public static MigrationUtil getInstance()
	{
		return MigtrationUtilHolder.migrationUtil;
	}
	
	
	/**
	 * convert data for migration
	 * @param oriList
	 * @return
	 */
	public List<Map<String,String>> filterData(List<Map<String,String>> oriList)
	{		
		List<Map<String, String>> mergedList =new ArrayList<Map<String, String>>();
		Map<String, String> effortMap = new HashMap<String, String>();
		 
		boolean isLastRecord=false;
		
		for(int i=0;i<oriList.size()-1;i++)
		{
			Map<String, String> currentMap=oriList.get(i);			
			Map<String, String> nextMap=oriList.get(i+1);			 
			String project=currentMap.get(PorjectName);
			String userid=currentMap.get(UserID);
			String activityCode=currentMap.get(ActivityCode);
			String str_date=currentMap.get(EffortDate);		
			String weeks = getWeek(str_date);					
			String effort=currentMap.get(Effort);
			effortMap.put(weeks, effort);
			//Last record
			if(i==oriList.size()-2)
			{
				effortMap.put(getWeek(nextMap.get(EffortDate)),nextMap.get(Effort));
				isLastRecord=true;
			}			
			//merge records
			if(!(nextMap.get(PorjectName).equals(project)&&nextMap.get(UserID).equals(userid))&&nextMap.get(ActivityCode).equals(activityCode)||isLastRecord)
			{			
				//add weeks & beginning date fields
				currentMap.put(Sun, Zero);
				currentMap.put(Mon, Zero);
				currentMap.put(Tue, Zero);
				currentMap.put(Wed, Zero);
				currentMap.put(Thu, Zero);
				currentMap.put(Fri, Zero);
				currentMap.put(Sat, Zero);				
				currentMap.putAll(effortMap);
				//date
				java.sql.Date d = new java.sql.Date(System.currentTimeMillis()); 
				currentMap.put(BeginningDate, d.toString());				
				effortMap.clear();
				//remove invalid fields
				currentMap.remove(Effort);
				currentMap.remove(EffortDate);
				mergedList.add(currentMap);
			}		
		}		
		return mergedList;
	}
	
	
	/**
	 * convert data for migration
	 * @param oriList
	 * @return
	 */
	public List<Map<String,String>> mergeData(List<Map<String,String>> oriList)
	{		
		List<Map<String, String>> mergedList =new ArrayList<Map<String, String>>();
		Map<String, String> effortMap = new HashMap<String, String>();	 
		
		double saveEffort =0.00;
		
		for(int i=0;i<oriList.size()-1;i++)
		{
			Map<String, String> currentMap=oriList.get(i);			
			Map<String, String> nextMap=oriList.get(i+1);			
			String project=currentMap.get(PorjectName);
			String userid=currentMap.get(UserID);
			String activityCode=currentMap.get(ActivityCode);		
			String str_date=currentMap.get(EffortDate);		
			String weeks = getWeek(str_date);					
			double effort=Double.parseDouble( currentMap.get(Effort));
			currentMap.put(Sun, Zero);
			currentMap.put(Mon, Zero);
			currentMap.put(Tue, Zero);
			currentMap.put(Wed, Zero);
			currentMap.put(Thu, Zero);
			currentMap.put(Fri, Zero);
			currentMap.put(Sat, Zero);		
						
			if((nextMap.get(PorjectName).equals(project)&&nextMap.get(UserID).equals(userid))&&nextMap.get(ActivityCode).equals(activityCode))
			{
				if(str_date.equals(nextMap.get(EffortDate)))
				{
					saveEffort=saveEffort+effort;
				}
				else
				{
					effortMap.put(weeks, String.valueOf(saveEffort+effort));
					saveEffort=0.00;
				}				
			}
			else
			{					
				java.sql.Date d = new java.sql.Date(System.currentTimeMillis()); 
				currentMap.put(BeginningDate, d.toString());	
				effortMap.put(weeks, String.valueOf(effort+saveEffort));
				currentMap.putAll(effortMap);				
				saveEffort =0.00;
				effortMap.clear();
				//remove invalid fields
				currentMap.remove(Effort);
				currentMap.remove(EffortDate);
				mergedList.add(currentMap);				
			}			
			//Last record
			if(i==oriList.size()-2)			
			{
				nextMap.put(getWeek(nextMap.get(EffortDate)),nextMap.get(Effort));
				mergedList.add(nextMap);				
			}				
		}		
		return mergedList;
	}
	
	
	/**
	 * convert a date to week
	 * @param dstr
	 * @return
	 */
	public String getWeek(String dstr) 
	{		
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd",java.util.Locale.US); 
		String[] weekdays = new String[]{"", Mon,Tue,Wed,Thu,Fri,Sat,Sun};
		Calendar cd=Calendar.getInstance(); 
		try 
		{
			Date date = sf.parse(dstr);			
			cd.clear();
			cd.setTime(date);			
		}		
		catch (ParseException e)
		{			
			e.printStackTrace();
			log.error( "getWeek() caught ParseException: ", e );
		}		
		return weekdays[cd.get(Calendar.DAY_OF_WEEK)-1];
	}
	
	
	/**
	 * get data of migration
	 * @return
	 */
	public List<Map<String,String>> getMigDatalist()
	{		
		String sSql = GET_MIGRATION_DATA_SQL;		
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection("MSSQL");
				result = src.executeRetrieve(conn, sSql);
				src.closeConn(conn);
			}
			catch( SQLException se )
			{
				log.error( "getMigDatalist() caught SQLException: ", se );
			}
			catch( Exception ex )
			{
				log.error( "getMigDatalist() caught Exception: ", ex );
			}
			finally
			{
				src.closeConn(conn);
			}		
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getMigDatalist() caught NsiEventException: ", nsiex );
		}
		return result;		
	} 
}