/******************************************************************************
 * The contents of this file are subject to the   Compiere License  Version 1.1
 * ("License"); You may not use this file except in compliance with the License
 * You may obtain a copy of the License at http://www.compiere.org/license.html
 * Software distributed under the License is distributed on an  "AS IS"  basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 * The Original Code is Compiere ERP & CRM Smart Business Solution. The Initial
 * Developer of the Original Code is Jorg Janke. Portions created by Jorg Janke
 * are Copyright (C) 1999-2005 Jorg Janke.
 * All parts are Copyright (C) 1999-2005 ComPiere, Inc.  All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package compiere.process;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.logging.Level;

import org.compiere.impexp.MImpFormat;
import org.compiere.impexp.MImpFormatRow;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.Ctx;
import org.compiere.util.DB;

import compiere.model.X_I_ImpFormat_Row;

/**
 *	Create Import Format from I_ImpFormat_Row
 *
 * 	@author 	Jirimuto
 * 	@version 	$Id: ImportImpFormat.java,v 1.1 2008/02/14 13:04:21 jrmt Exp $
 */
public class ImportImpFormat extends SvrProcess
{
	/**	Client to be imported to		*/
	private int				m_AD_Client_ID = 0;
	/**	Organization to be imported to		*/
	private int				m_AD_Org_ID = 0;
	/**	Delete old Imported				*/
	private boolean			m_deleteOldImported = false;

	private String				m_ImpFormat;

	/** Effective						*/
	private Timestamp		m_DateValue = null;

	private Ctx 		m_ctx;
	/**
	 *  Prepare - e.g., get Parameters.
	 */
	private int impFormatID =0;

	protected void prepare()
	{
		m_ctx = getCtx();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].getParameterName();
			if (name.equals("AD_Client_ID"))
				m_AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("AD_Org_ID"))
				m_AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("DeleteOldImported"))
				m_deleteOldImported = "Y".equals(para[i].getParameter());
			else if (name.equals("ImpFormatName"))
				m_ImpFormat = ((String)para[i].getParameter());
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		if (m_DateValue == null)
			m_DateValue = new Timestamp (System.currentTimeMillis());
	}	//	prepare


	/**
	 *  Perrform process.
	 *  @return Message
	 *  @throws Exception
	 */
	protected String doIt() throws java.lang.Exception
	{
		log.info("");
		StringBuffer sql = null;
		int errNo = 0;	
		int no = 0;	
		String clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;

		//	Delete Old Imported
		if (m_deleteOldImported)
		{
			sql = new StringBuffer ("DELETE I_ImpFormat_Row "
				  + "WHERE I_IsImported='Y'").append (clientCheck);
			no = DB.executeUpdate(sql.toString(), get_TrxName());
			log.fine("Delete Old Impored =" + no);
		}
		//	****	Prepare	****

		//	Set Client, Org, IsActive, Created/Updated
		sql = new StringBuffer ("UPDATE I_ImpFormat_Row "
			  + "SET AD_Client_ID = COALESCE (AD_Client_ID,").append (m_AD_Client_ID).append ("),"
			  + " AD_Org_ID = COALESCE (AD_Org_ID,").append (m_AD_Org_ID).append ("),");
		sql.append(" IsActive = COALESCE (IsActive, 'Y'),"
			  + " Created = COALESCE (Created, SysDate),"
			  + " CreatedBy = COALESCE (CreatedBy, 0),"
			  + " Updated = COALESCE (Updated, SysDate),"
			  + " UpdatedBy = COALESCE (UpdatedBy, 0),"
			  + " I_ErrorMsg = NULL,"
			  + " I_IsImported = 'N' "
			  + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL").append (clientCheck);
		if(m_ImpFormat != null ){
			sql.append( " AND IMPFORMATNAME=" + "'"+m_ImpFormat+"'"); 
		}
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.info ("Reset=" + no);

		sql = new StringBuffer ("UPDATE I_ImpFormat_Row o "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Org, '"
			+ "WHERE (AD_Org_ID IS NULL "
			+ " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		errNo += no;
		if (no != 0)
			log.warning ("Invalid Org=" + no);
			
		//	Set Table ID
		sql = new StringBuffer("UPDATE I_ImpFormat_Row i "
			+ "SET i.AD_TABLE_ID="
			+ "( "
			+ " SELECT a.AD_TABLE_ID "
			+ " FROM AD_TABLE a "
			+ " WHERE a.TABLENAME=i.TableName "
			+ ") "
			+ "WHERE (i.I_IsImported<>'Y' "
			+ "OR i.I_IsImported IS NULL)").append(clientCheck);
		if(m_ImpFormat != null ){
			sql.append( " AND IMPFORMATNAME=" + "'"+m_ImpFormat+"'"); 
		}
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.info("AD_Table_ID=" + no);
		commit();
		//
		sql = new StringBuffer("UPDATE I_ImpFormat_Row "
			+ "SET I_isImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Table Name, ' "
			+ "WHERE AD_TABLE_ID IS NULL "
			+ "AND (I_isImported<>'Y' "
			+ "OR I_isImported IS NULL)").append(clientCheck);
		if(m_ImpFormat != null ){
			sql.append( " AND IMPFORMATNAME=" + "'"+m_ImpFormat+"'"); 
		}
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		errNo += no;
		if (no != 0)
			log.warning("Invalid Table Name=" + no);
		
		//	Set Column ID
		sql = new StringBuffer("UPDATE I_ImpFormat_Row i "
				+ "SET i.AD_COLUMN_ID="
				+ "( "
				+ " SELECT a.AD_COLUMN_ID "
				+ " FROM AD_COLUMN a "
				+ " WHERE a.COLUMNNAME=i.COLUMNNAME "
				+ " AND a.AD_TABLE_ID=i.AD_TABLE_ID "
				+ ") "
				+ "WHERE (i.I_IsImported<>'Y' "
				+ "OR i.I_IsImported IS NULL) AND (i.AD_Column_ID IS NULL or i.AD_Column_ID =0)").append(clientCheck);
		if(m_ImpFormat != null ){
			sql.append( " AND i.IMPFORMATNAME=" + "'"+m_ImpFormat+"'"); 
		}
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.info("Set Column=" + no);
		//
		sql = new StringBuffer ("UPDATE I_ImpFormat_Row "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Column,' "
			+ "WHERE AD_Column_ID IS NULL AND COLUMNNAME IS NOT NULL "
			+ "AND I_IsImported<>'E' "
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		if(m_ImpFormat != null ){
			sql.append( " AND IMPFORMATNAME=" + "'"+m_ImpFormat+"'"); 
		}
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		errNo += no;
		if (no != 0)
			log.warning("Invalid Column=" + no);
		
		commit();
		
		if( errNo != 0 )
			return "";

		//Import Imp Format
		sql = new StringBuffer("SELECT * FROM I_ImpFormat_Row"
			+ " WHERE I_IsImported<>'Y' AND ROWNUM=1").append(clientCheck);
		if(m_ImpFormat != null ){
			sql.append( " AND IMPFORMATNAME=" + "'"+m_ImpFormat+"'"); 
		};
			
		PreparedStatement pstmt = null; 
		int noInsert = 0;
		int noInsertItem = 0;
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			ResultSet rs = pstmt.executeQuery();
				
			while (rs.next())
			{ 
				X_I_ImpFormat_Row imp = new X_I_ImpFormat_Row(m_ctx, rs, get_TrxName());
				imp.setAD_ImpFormat_ID( MImpFormat.getAD_ImportFormat_ID( imp.getImpFormatName()));
				
				//	New Imp Format
				MImpFormat impFormat = null;
				if (imp.getAD_ImpFormat_ID() == 0)	//	Insert new ImpFormat
				{
					impFormat = new MImpFormat(m_ctx, imp.getAD_ImpFormat_ID(), get_TrxName());
					impFormat.setClientOrg(imp);
					impFormat.setAD_Org_ID(imp.getAD_Org_ID());
					impFormat.setName(imp.getImpFormatName());
					impFormat.setDescription(imp.getDescription());
					impFormat.setFormatType(imp.getFormatType());
					impFormat.setAD_Table_ID(imp.getAD_Table_ID());
					
					if (impFormat.save())
					{
						imp.setAD_ImpFormat_ID(impFormat.getAD_ImpFormat_ID());
						log.finest("Insert ImpFormat - " + impFormat.getAD_ImpFormat_ID());
						impFormatID = impFormat.getAD_ImpFormat_ID();
						noInsert++;
					}
					else
					{
						sql = new StringBuffer ("UPDATE I_ImpFormat_Row i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Cannot Insert ImpFormat,'")
							.append(" WHERE AD_ImpFormat_ID=").append(imp.getAD_ImpFormat_ID());
						DB.executeUpdate(sql.toString(), get_TrxName());
						continue;     
					}
				}
				else				//	Update existing ImpFormat
				{
					impFormat = new MImpFormat(m_ctx, imp.getAD_ImpFormat_ID(), get_TrxName());
					impFormat.setAD_Org_ID(imp.getAD_Org_ID());
					impFormat.setName(imp.getImpFormatName());
					impFormat.setDescription(imp.getDescription());
					impFormat.setFormatType(imp.getFormatType());
					impFormat.setAD_Table_ID(imp.getAD_Table_ID());
					
					//	Save ImpFormat
					if (impFormat.save())
					{
						imp.setAD_ImpFormat_ID(impFormat.getAD_ImpFormat_ID());
						imp.save();
						impFormatID = impFormat.getAD_ImpFormat_ID();
						noInsert++;
					}
					else
					{
						sql = new StringBuffer ("UPDATE I_ImpFormat_Row i "
								+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Cannot Update ImpFormat,'")
								.append(" WHERE AD_ImpFormat_ID=").append(imp.getAD_ImpFormat_ID());
							DB.executeUpdate(sql.toString(), get_TrxName());
						continue;
					}
				}
				commit();
			}
			
			//	Close database connection
			rs.close();
			pstmt.close();
			rs = null;
			pstmt = null;

		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		
		//Import Print Format Item
		sql = new StringBuffer("SELECT * FROM I_ImpFormat_Row"
			+ " WHERE I_IsImported<>'Y'").append(clientCheck);
		if(m_ImpFormat != null ){
			sql.append( " AND IMPFORMATNAME=" + "'"+m_ImpFormat+"'"); 
		};
			
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			ResultSet rs = pstmt.executeQuery();
				
			while (rs.next())
			{ 
				X_I_ImpFormat_Row imp = new X_I_ImpFormat_Row(m_ctx, rs, get_TrxName());
				
				//	New Print Format Item
				MImpFormatRow impFormatRow = null;
				if (imp.getAD_ImpFormat_Row_ID() == 0)	//	Insert new Imp Format Row
				{
					imp.setAD_ImpFormat_ID(impFormatID);
					impFormatRow = new MImpFormatRow(m_ctx, imp.getAD_ImpFormat_Row_ID(), get_TrxName());
					impFormatRow.setClientOrg(imp);
					impFormatRow.setAD_Org_ID(imp.getAD_Org_ID());
					impFormatRow.setAD_ImpFormat_ID(imp.getAD_ImpFormat_ID());
					impFormatRow.setName(imp.getRowName());
					impFormatRow.setStartNo(imp.getStartNo());
					impFormatRow.setEndNo(imp.getEndNo());
					impFormatRow.setDataType(imp.getDataType());
					impFormatRow.setDataFormat(imp.getDataFormat());
					impFormatRow.setDecimalPoint(imp.getDecimalPoint());
					impFormatRow.setDivideBy100(imp.isDivideBy100());
					impFormatRow.setConstantValue(imp.getConstantValue());
					impFormatRow.setCallout(imp.getCallout());
					impFormatRow.setScript(imp.getScript());
					impFormatRow.setSeqNo(imp.getSeqNo());
			        if ( imp.getAD_Column_ID()>0){
			        	impFormatRow.setAD_Column_ID(imp.getAD_Column_ID());
					}	//	MImpFormatRow
					
					if (impFormatRow.save())
					{
						imp.setAD_ImpFormat_ID(impFormatRow.getAD_ImpFormat_ID());
						imp.setAD_ImpFormat_Row_ID(impFormatRow.getAD_ImpFormat_Row_ID());
						imp.setI_IsImported(true);
						imp.setProcessed(true);
						imp.save();
						log.finest("Insert ImpFormatItem - " + impFormatRow.getAD_ImpFormat_Row_ID());
						noInsertItem++;
					}
					else
					{
						sql = new StringBuffer ("UPDATE I_ImpFormat_Row i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Cannot Insert ImpFormatItem,'")
							.append(" WHERE AD_ImpFormat_Row_ID=").append(imp.getAD_ImpFormat_Row_ID());
						DB.executeUpdate(sql.toString(), get_TrxName());
						continue;
					}
				}
				else				
				{
					impFormatRow.setAD_Org_ID(imp.getAD_Org_ID());
					impFormatRow.setName(imp.getRowName());
					impFormatRow.setStartNo(imp.getStartNo());
					impFormatRow.setEndNo(imp.getEndNo());
					impFormatRow.setDataType(imp.getDataType());
					impFormatRow.setDataFormat(imp.getDataFormat());
					impFormatRow.setDecimalPoint(imp.getDecimalPoint());
					impFormatRow.setDivideBy100(imp.isDivideBy100());
					impFormatRow.setConstantValue(imp.getConstantValue());
					impFormatRow.setCallout(imp.getCallout());
					impFormatRow.setScript(imp.getScript());
					impFormatRow.setSeqNo(imp.getSeqNo());
			        if ( imp.getAD_Column_ID()>0)
			        	impFormatRow.setAD_Column_ID(imp.getAD_Column_ID());
					
					//	Save ImpFormatRow
					if (impFormatRow.save())
					{
						imp.setAD_ImpFormat_ID(impFormatRow.getAD_ImpFormat_ID());
						imp.setAD_ImpFormat_Row_ID(impFormatRow.getAD_ImpFormat_Row_ID());
						imp.setI_IsImported(true);
						imp.setProcessed(true);
						imp.save();
						noInsertItem++;
					}
					else
					{
						sql = new StringBuffer ("UPDATE I_ImpFormat_Row i "
								+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Cannot Update ImpFormatItem,'")
								.append(" WHERE AD_ImpFormat_Row_ID=").append(imp.getAD_ImpFormat_Row_ID());
							DB.executeUpdate(sql.toString(), get_TrxName());
						continue;
					}
				}
				commit();
			}
			
			//	Close database connection
			rs.close();
			pstmt.close();
			rs = null;
			pstmt = null;

		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}		//	Set Error to indicator to not imported
		sql = new StringBuffer ("UPDATE I_ImpFormat_Row "
			+ "SET I_IsImported='N', Updated=SysDate "
			+ "WHERE I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		addLog (0, null, new BigDecimal (no), "@Errors@");
		//
		addLog (0, null, new BigDecimal (noInsert), "@AD_ImpFormat_ID@: @Inserted@");
		//
		addLog (0, null, new BigDecimal (noInsertItem), "@AD_ImpFormat_Row_ID@: @Inserted@");
		return "";
	}	//	doIt
	
}//	Import ImpFormat_Row
