/**
 * 
 */
package com.ampiere.util;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.Locale;
import java.util.Properties;
import java.util.logging.Level;

import org.apache.commons.lang.StringUtils;
import org.apache.ecs.AlignType;
import org.apache.ecs.Element;
import org.apache.ecs.xhtml.head;
import org.apache.ecs.xhtml.input;
import org.apache.ecs.xhtml.link;
import org.apache.ecs.xhtml.script;
import org.apache.ecs.xhtml.span;
import org.apache.ecs.xhtml.td;
import org.compiere.framework.Lookup;
import org.compiere.model.MLookup;
import org.compiere.model.MRole;
import org.compiere.util.CLogger;
import org.compiere.util.Ctx;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.compiere.util.Util;
import org.compiere.util.WebEnv;
import org.compiere.util.WebSessionCtx;

import com.ampiere.web.struts.common.PopupBaseForm;

/**
 * @author Grape
 *
 */
public class AmpiereUtil {
	/**	Static Logger	*/
	private static CLogger	log	= CLogger.getCLogger (AmpiereUtil .class);
	// Max records number for search
	public final static int MAX_RECORD_NUM = 1000;

	/**
	 * 	Overwrite Util.maskHTML --> Convert "&nbsp;" to ""
	 *  i.e. replace characters with &values;
	 * 	@param content content
	 * 	@return masked content
	 */
	public static String maskHTML (String content)
	{
		String s = Util.maskHTML(content);
		if (WebEnv.NBSP.equalsIgnoreCase(s) || "&nbsp".equalsIgnoreCase(s)) {
			return "";
		}

		return s;
	}

	/**
	 * 	Overwrite maskHTML --> "\n" filter
	 * 	@param content content
	 * 	@return masked content
	 */
	public static String maskHtmlCR(String content)
	{
		String s = maskHTML(content);
		s = s.replaceAll("\n", WebEnv.NBSP);
		return s;
	}

	/**
	 * Get column ID from column name
	 * @param columnName
	 * @return column id
	 */
	public static int getFixedColumnID(String columnName) {
		if (columnName == null) {
			return 0;
		}
		int AD_Column_ID = 0;
		if (columnName.indexOf("BPartner") >= 0) {
			AD_Column_ID = 3499;    //  C_Invoice.C_BPartner_ID
		} else if (columnName.indexOf("Product") >= 0) {
			AD_Column_ID = 3840;    //  C_InvoiceLine.M_Product_ID
		} else if (columnName.indexOf("Invoice") >= 0) {
			AD_Column_ID = 5355;    //  C_Invoice.C_Invoice_ID
		} else if (columnName.indexOf("Asset") >= 0) {
			AD_Column_ID = 271;		//  A_Asset.A_Asset_ID
		} else if (columnName.indexOf("Order") >= 0) {
			AD_Column_ID = 12925;   //	C_Order.C_Order_ID
		} else if (columnName.indexOf("InOut") >= 0) {
		} else if (columnName.indexOf("Payment") >= 0) {
			AD_Column_ID = 4884;	//	C_Payment.C_Payment_ID
		} else if (columnName.indexOf("CashLine") >= 0) {
		} else if (columnName.indexOf("ResourceAssigment") >= 0) {
			AD_Column_ID = 6851;	//	S_Resource.S_ResourceType_ID
		} else if (columnName.indexOf("Currency") >= 0) {
			AD_Column_ID = 3505;    //  C_Invoice.C_Currency_ID
		} else if (columnName.indexOf("User") >= 0) {
			AD_Column_ID = 10510;	//  AD_WF_Activity.AD_User_UD
		}
		return AD_Column_ID;
	}

	/**
	 * Get identification table name from column name
	 * @param columnName
	 * @return id
	 */
	public static String getFixedColumnName(String columnName) {
		String id = "General";
		if (columnName == null) {
			return id;
		}
		if (columnName.indexOf("BPartner") >= 0) {
			id = "BPartner";
		} else if (columnName.indexOf("Product") >= 0) {
			id = "Product";
		} else if (columnName.indexOf("Invoice") >= 0) {
			id = "Invoice";
		} else if (columnName.indexOf("Asset") >= 0) {
			id = "Asset";
		} else if (columnName.indexOf("Order") >= 0) {
			id = "Order";
		} else if (columnName.indexOf("InOut") >= 0) {
			id = "InOut";
		} else if (columnName.indexOf("Payment") >= 0) {
			id = "Payment";
		} else if (columnName.indexOf("CashLine") >= 0) {
			id = "CashLine";
		} else if (columnName.indexOf("ResourceAssignment") >= 0) {
			id = "Assignment";
		}
		return id;
	}

	/**
	 * Get month name list
	 * @param loc
	 * @return
	 */
	public static String getMonths(Locale loc) {
		SimpleDateFormat formatDate = (SimpleDateFormat)DateFormat.getDateInstance(DateFormat.LONG, loc);
		return generateJavascriptArray(formatDate.getDateFormatSymbols().getMonths());
	}

	/**
	 * Get short month name list
	 * @param loc
	 * @param dateFormat
	 * @return
	 */
	public static String getShortMonths(Locale loc) {
		SimpleDateFormat formatDate = (SimpleDateFormat)DateFormat.getDateInstance(DateFormat.LONG, loc);
		return generateJavascriptArray(formatDate.getDateFormatSymbols().getShortMonths());
	}

	/**
	 * Get week name list
	 * @param loc
	 * @return
	 */
	public static String getWeeks(Locale loc) {
		SimpleDateFormat formatDate = (SimpleDateFormat)DateFormat.getDateInstance(DateFormat.LONG, loc);
		String wk[] = formatDate.getDateFormatSymbols().getWeekdays();
		String weekss[] = new String [7];
		// 0:blank, 1:Sunday ... ==> 0:Sunday 1:Monday ...
		for (int i = 1; i < wk.length; i++) {
			weekss[i - 1] = wk[i];
		}
		return generateJavascriptArray(weekss);
	}

	/**
	 * Get short week name list
	 * @param loc
	 * @return
	 */
	public static String getShortWeeks(Locale loc) {
		SimpleDateFormat formatDate = (SimpleDateFormat)DateFormat.getDateInstance(DateFormat.LONG, loc);
		String swk[] = formatDate.getDateFormatSymbols().getShortWeekdays();
		String shortWeekss[] = new String [7];
		// 0:blank, 1:Sunday ... ==> 0:Sunday 1:Monday ...
		for (int i = 1; i < swk.length; i++) {
			shortWeekss[i - 1] = swk[i];
		}
		return generateJavascriptArray(shortWeekss);
	}

	/**
	 * Javascript array deceleration
	 * @param p
	 * @return
	 */
	public static String generateJavascriptArray(String p[]) {
		String s = "new Array(";
		for (int i = 0; i < p.length; i++) {
			if (p[i] != null && p[i].length() > 0) {
				s += ("\"" + p[i] + "\",");
			}
		}
		s = s.substring(0, s.length() - 1);
		s += ")";
		return s;
	}

	/**
	 * Add date picker elements
	 * @param hd
	 * @param wsc
	 * @return
	 */
	public static head addDatePicker(head hd, WebSessionCtx wsc) {
//		hd.addElement(new script((Element)null, "js/DatePicker.js"));
//		hd.addElement(new script("var paraMonths = " + AmpiereUtil.getMonths(wsc.language.getLocale()) + ";"));
//		hd.addElement(new script("var paraShortMonths = " + AmpiereUtil.getShortMonths(wsc.language.getLocale()) + ";"));
//		hd.addElement(new script("var paraWeeks = " + AmpiereUtil.getWeeks(wsc.language.getLocale()) + ";"));
//		hd.addElement(new script("var paraShortWeeks = " + AmpiereUtil.getShortWeeks(wsc.language.getLocale()) + ";"));
//		hd.addElement(new script("var paraDateFormat = " + "\"" + wsc.dateFormat.toPattern() + "\"" + ";"));
//		hd.addElement(new script("var paraInvalidDateMsg = " + "\"" + Msg.translate(wsc.ctx, "InvalidDate") + "\"" + ";"));
		
		//clmg 2009/03/08
		addDateTimePicker(hd, wsc);
		
		return hd;
	}
	
	/**
	 * Add date timer picker elements
	 * @param hd
	 * @param wsc
	 * @return
	 */
	public static head addDateTimePicker(head hd, WebSessionCtx wsc) {
		hd.addElement(new script((Element)null, WebEnv.getBaseDirectory("js/protocalendar-js-1.1.0/javascripts/prototype.js")));
		hd.addElement(new script((Element)null, WebEnv.getBaseDirectory("js/protocalendar-js-1.1.0/javascripts/effects.js")));
		hd.addElement(new script((Element)null, WebEnv.getBaseDirectory("js/protocalendar-js-1.1.0/javascripts/protocalendar.js")));
		hd.addElement(new script((Element)null, WebEnv.getBaseDirectory("js/protocalendar-js-1.1.0/javascripts/lang_ja.js")));
		
		hd.addElement(new link(WebEnv.getBaseDirectory("js/protocalendar-js-1.1.0/stylesheets/simple.css"), link.REL_STYLESHEET, link.TYPE_CSS));

		return hd;
	}
	
	/**
	 * Add date timer picker script
	 * @param hd
	 * @param wsc
	 * @return
	 */
//    <script type="text/javascript">
//    InputCalendar.createOnLoaded('calendar-hm3',
//         {format: 'yyyy/mm/dd HH:MM',
//          enableHourMinute: true,
//          lang: 'ja',
//          triggers: ['calendar_icon'],
//          inputReadOnly: true});
//  </script>
	public static void addDateTimePickerScript(Element element, String inputBoxId,  String buttonId) {
		StringBuffer sb = new StringBuffer();
		sb.append( "InputCalendar.createOnLoaded('" )
		  .append( inputBoxId + "'," )
		  .append( "{format: 'yyyy/mm/dd HH:MM:ss JST',")
		  .append("enableHourMinute: true,")
		  .append("lang: 'ja',")
		  .append("triggers: ['" + buttonId + "'],")
		  .append("inputReadOnly: true});");
		element.addElementToRegistry(new script( sb.toString() ));
		
	}
	
	public static void addDatePickerScript(Element element, String inputBoxId,  String buttonId) {
		StringBuffer sb = new StringBuffer();
		sb.append( "InputCalendar.createOnLoaded('" )
		  .append( inputBoxId + "'," )
		  .append( "{format: 'yyyy/mm/dd',")
		  .append("enableHourMinute: false,")
		  .append("lang: 'ja',")
		  .append("triggers: ['" + buttonId + "'],")
		  .append("inputReadOnly: true});");
		element.addElementToRegistry(new script( sb.toString() ));
		
	}

	/**
	 * Create close(submit) button
	 * @param ctx
	 * @param hasSubmit
	 * @return
	 */
	public static td createButtons(Ctx ctx, boolean hasSubmit) {
		td tdButton = (td)new td().setClass("button-box");////clmg 2009/02/15
		if (hasSubmit) {
			tdButton.addElement(createImageButton(ctx, "Ok", "this.form.submit();"));
			tdButton.addElement(new span("OK"));//clmg 2009/02/15
		}

		tdButton.addElement(createImageButton(ctx, "Cancel", "window.opener.top.WWindow.document.WForm.PCommand.value='Refresh';window.opener.top.WWindow.document.WForm.submit();window.close();"));
		//here 2009/03/23
		tdButton.addElement(new span("キャンセル"));//clmg 2009/02/15
		return tdButton;
	}

	/**
	 * @param ctx
	 * @param name
	 * @param script
	 * @return
	 */
	public static input createImageButton(Ctx ctx, String name, String script) {
		input imgButton = new input(input.TYPE_IMAGE, name, name);
		imgButton.setID("Popup" + name);
		imgButton.setOnClick(script);
		imgButton.setSrc(WebEnv.getImageDirectory(name + "32.png"));
		imgButton.setAlt(Msg.translate(ctx, name));
		return imgButton;
	}

	/**
	 * Check if record number is larger than MAX_RECORD_NUM
	 * @param ctx
	 * @param rowNum
	 * @return
	 */
	public static int setLargeRecordMsg(Ctx ctx, PopupBaseForm form, int rowNum) {
	    if (rowNum > MAX_RECORD_NUM) {
			MessageFormat mf = null;
			try
			{
				mf = new MessageFormat(Msg.getMsg(Env.getAD_Language(ctx), "LargeRecords"));
			}
			catch (Exception e)
			{
				log.log(Level.SEVERE, "LargeRecords=" + Msg.getMsg(Env.getAD_Language(ctx), "LargeRecords"), e);
			}

			/**********************************************************************
			 *	** Message: LargeRecords **
			 *	Found {0} records. Returned {1} of them.
			 *
			 *	{0} - Number of found records
			 *	{1} - Number of returned records
			 */
			Object[] arguments = new Object[2];
			arguments[0] = new Integer(rowNum);
			arguments[1] = new Integer(MAX_RECORD_NUM);
			form.setLargeRecords(mf.format(arguments));
			return MAX_RECORD_NUM;
		}
		form.setLargeRecords("");
	    return rowNum;
	}

	/**
	 * 	Generate Access SQL for Search.
	 * 	The SQL returns the ID of the value entered
	 * 	Also sets m_tableName and m_keyColumnName
	 *	@param text uppercase text for LIKE comparison
	 *	@return sql or ""
	 *  Example
	 *	SELECT C_Payment_ID FROM C_Payment WHERE UPPER(DocumentNo) LIKE x OR ...
	 */
	public static String getDirectAccessSQL (Ctx ctx, Lookup lookup, String columnName, String text)
	{
		StringBuffer sql = new StringBuffer();
		String tableName = columnName.substring(0, columnName.length()-3);
		String keyColumnName = columnName;
		//
		if (columnName.equals("M_Product_ID"))
		{
			//	Reset
//			Env.setContext(ctx, Env.WINDOW_INFO, Env.TAB_INFO, "M_Product_ID", "0");
//			Env.setContext(ctx, Env.WINDOW_INFO, Env.TAB_INFO, "M_AttributeSetInstance_ID", "0");
			ctx.setContext(Env.WINDOW_INFO, "M_Product_ID", "0");
			ctx.setContext(Env.WINDOW_INFO, "M_AttributeSetInstance_ID", "0");
			//
			sql.append("SELECT M_Product_ID FROM M_Product WHERE (UPPER(Value) LIKE ")
				.append(DB.TO_STRING(text))
				.append(" OR UPPER(Name) LIKE ").append(DB.TO_STRING(text))
				.append(" OR UPC LIKE ").append(DB.TO_STRING(text)).append(")");
		}
		else if (columnName.equals("C_BPartner_ID"))
		{
			sql.append("SELECT C_BPartner_ID FROM C_BPartner WHERE (UPPER(Value) LIKE ")
				.append(DB.TO_STRING(text))
				.append(" OR UPPER(Name) LIKE ").append(DB.TO_STRING(text)).append(")");
		}
		else if (columnName.equals("C_Order_ID"))
		{
			sql.append("SELECT C_Order_ID FROM C_Order WHERE UPPER(DocumentNo) LIKE ")
				.append(DB.TO_STRING(text));
		}
		else if (columnName.equals("C_Invoice_ID"))
		{
			sql.append("SELECT C_Invoice_ID FROM C_Invoice WHERE UPPER(DocumentNo) LIKE ")
				.append(DB.TO_STRING(text));
		}
		else if (columnName.equals("M_InOut_ID"))
		{
			sql.append("SELECT M_InOut_ID FROM M_InOut WHERE UPPER(DocumentNo) LIKE ")
				.append(DB.TO_STRING(text));
		}
		else if (columnName.equals("C_Payment_ID"))
		{
			sql.append("SELECT C_Payment_ID FROM C_Payment WHERE UPPER(DocumentNo) LIKE ")
				.append(DB.TO_STRING(text));
		}
		else if (columnName.equals("GL_JournalBatch_ID"))
		{
			sql.append("SELECT GL_JournalBatch_ID FROM GL_JournalBatch WHERE UPPER(DocumentNo) LIKE ")
				.append(DB.TO_STRING(text));
		}
		else if (columnName.equals("SalesRep_ID"))
		{
			sql.append("SELECT AD_User_ID FROM AD_User WHERE UPPER(Name) LIKE ")
				.append(DB.TO_STRING(text));
			tableName = "AD_User";
			keyColumnName = "AD_User_ID";
		}
		//	Predefined
		if (sql.length() > 0)
		{
			String wc = getWhereClause(ctx, lookup, columnName);
			if (wc != null && wc.length() > 0)
				sql.append(" AND ").append(wc);
			sql.append(" AND IsActive='Y'");
			//	***
			log.finest(columnName + " (predefined) " + sql.toString());
			return MRole.getDefault().addAccessSQL(sql.toString(),
				tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
		}
		
		//	Check if it is a Table Reference
		if (lookup != null && lookup instanceof MLookup)
		{
			int AD_Reference_ID = ((MLookup)lookup).getAD_Reference_Value_ID();
			if (AD_Reference_ID != 0)
			{
				String query = "SELECT kc.ColumnName, dc.ColumnName, t.TableName "
					+ "FROM AD_Ref_Table rt"
					+ " INNER JOIN AD_Column kc ON (rt.AD_Key=kc.AD_Column_ID)"
					+ " INNER JOIN AD_Column dc ON (rt.AD_Display=dc.AD_Column_ID)"
					+ " INNER JOIN AD_Table t ON (rt.AD_Table_ID=t.AD_Table_ID) "
					+ "WHERE rt.AD_Reference_ID=?";
				String displayColumnName = null;
				PreparedStatement pstmt = null;
				try
				{
					pstmt = DB.prepareStatement(query, null);
					pstmt.setInt(1, AD_Reference_ID);
					ResultSet rs = pstmt.executeQuery();
					if (rs.next())
					{
						keyColumnName = rs.getString(1);
						displayColumnName = rs.getString(2);
						tableName = rs.getString(3);
					}
					rs.close();
					pstmt.close();
					pstmt = null;
				}
				catch (Exception e)
				{
					log.log(Level.SEVERE, query, e);
				}
				try
				{
					if (pstmt != null)
						pstmt.close();
					pstmt = null;
				}
				catch (Exception e)
				{
					pstmt = null;
				}
				if (displayColumnName != null)
				{
					sql = new StringBuffer();
					sql.append("SELECT ").append(keyColumnName)
						.append(" FROM ").append(tableName)
						.append(" WHERE UPPER(").append(displayColumnName)
						.append(") LIKE ").append(DB.TO_STRING(text))
						.append(" AND IsActive='Y'");
					String wc = getWhereClause(ctx, lookup, columnName);
					if (wc != null && wc.length() > 0)
						sql.append(" AND ").append(wc);
					//	***
					log.finest(columnName + " (Table) " + sql.toString());
					return MRole.getDefault().addAccessSQL(sql.toString(),
								tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
				}
			}	//	Table Reference
		}	//	MLookup
		
		/** Check Well Known Columns of Table - assumes TableDir	**/
		String query = "SELECT t.TableName, c.ColumnName "
			+ "FROM AD_Column c "
			+ " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID AND t.IsView='N') "
			+ "WHERE (c.ColumnName IN ('DocumentNo', 'Value', 'Name') OR c.IsIdentifier='Y')"
			+ " AND c.AD_Reference_ID IN (10,14)"
			+ " AND EXISTS (SELECT * FROM AD_Column cc WHERE cc.AD_Table_ID=t.AD_Table_ID"
				+ " AND cc.IsKey='Y' AND cc.ColumnName=?)";
		keyColumnName = columnName;
		sql = new StringBuffer();
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement(query, null);
			pstmt.setString(1, keyColumnName);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				if (sql.length() != 0)
					sql.append(" OR ");
				tableName = rs.getString(1);
				sql.append("UPPER(").append(rs.getString(2)).append(") LIKE ").append(DB.TO_STRING(text));
			}
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (SQLException ex)
		{
			log.log(Level.SEVERE, query, ex);
		}
		try
		{
			if (pstmt != null)
				pstmt.close();
		}
		catch (SQLException ex1)
		{
		}
		pstmt = null;
		//
		if (sql.length() == 0)
		{
			log.log(Level.SEVERE, columnName + " (TableDir) - no standard/identifier columns");
			return "";
		}
		//
		StringBuffer retValue = new StringBuffer ("SELECT ")
			.append(columnName).append(" FROM ").append(tableName)
			.append(" WHERE ").append(sql)
			.append(" AND IsActive='Y'");
		String wc = getWhereClause(ctx, lookup, columnName);
		if (wc != null && wc.length() > 0)
			retValue.append(" AND ").append(wc);
		//	***
		log.finest(columnName + " (TableDir) " + sql.toString());
		return MRole.getDefault().addAccessSQL(retValue.toString(),
					tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
	}	//	getDirectAccessSQL

	/**
	 * 	Get Where Clause
	 *	@return where clause or ""
	 */
	public static String getWhereClause(Ctx ctx, Lookup lookup, String columnName)
	{
		String whereClause = "";
		if (lookup == null)
			return "";
		if (lookup.getZoomQuery() != null)
			whereClause = lookup.getZoomQuery().getWhereClause();
		String validation = lookup.getValidation();
		if (validation == null)
			validation = "";
		if (whereClause.length() == 0)
			whereClause = validation;
		else if (validation.length() > 0)
			whereClause += " AND " + validation;
	//	log.finest("ZoomQuery=" + (m_lookup.getZoomQuery()==null ? "" : m_lookup.getZoomQuery().getWhereClause())
	//		+ ", Validation=" + m_lookup.getValidation());
		if (whereClause.indexOf('@') != -1)
		{
			String validated = Env.parseContext(ctx, lookup.getWindowNo(), whereClause, false);
			if (validated.length() == 0)
				log.severe(columnName + " - Cannot Parse=" + whereClause);
			else
			{
				log.fine(columnName + " - Parsed: " + validated);
				return validated;
			}
		}
		return whereClause;
	}	//	getWhereClause

	/**
	 * 	Get Warehouse restriction if any.
	 *	@return	M_Warehouse_ID or 0
	 */
	public static int getOnly_Warehouse_ID(Ctx ctx, int windowNo)
	{
		String only_Warehouse = ctx.getContext(windowNo, "M_Warehouse_ID", true);
		int only_Warehouse_ID = 0;
		try
		{
			if (only_Warehouse != null && only_Warehouse.length () > 0)
				only_Warehouse_ID = Integer.parseInt (only_Warehouse);
		}
		catch (Exception ex)
		{
		}
		return only_Warehouse_ID;
	}	//	getOnly_Warehouse_ID
	
	/**
	 * <p>
	 * サニタイジングを行います。
	 * </p>
	 * 渡されたString文字列が既にサニタイジング後の文字を含んでいる場合はサニタイジングの処理を行わない
	 *
	 * @param string サニタイジング対象となる文字列
	 * @return サニタイジング後文字列 String
	 */
	public static String sanitizing(String str) {
		if (!StringUtils.isEmpty(str)) {
			if (str.indexOf("&amp;") >= 0 || str.indexOf("&lt;") >= 0 || str.indexOf("&gt;") >= 0
					|| str.indexOf("&quot;") >= 0 || str.indexOf("&#39;") >= 0) {
				return str;
			}
			str = str.replaceAll("&", "&amp;");
			str = str.replaceAll("<", "&lt;");
			str = str.replaceAll(">", "&gt;");
			str = str.replaceAll("\"", "&quot;");
			str = str.replaceAll("'", "&#39;");
		}
		return str;
	}	
}
