/******************************************************************************
 * Product: Adempiere ERP & CRM Smart Business Solution                       *
 * Copyright (C) 1999-2006 ComPiere, Inc. All Rights Reserved.                *
 * This program is free software; you can redistribute it and/or modify it    *
 * under the terms version 2 of the GNU General Public License as published   *
 * by the Free Software Foundation. This program is distributed in the hope   *
 * that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.           *
 * See the GNU General Public License for more details.                       *
 * You should have received a copy of the GNU General Public License along    *
 * with this program; if not, write to the Free Software Foundation, Inc.,    *
 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.                     *
 * For the text or an alternative of this public license, you may reach us    *
 * ComPiere, Inc., 2620 Augustine Dr. #245, Santa Clara, CA 95054, USA        *
 * or via info@compiere.org or http://www.compiere.org/license.html           *
 *****************************************************************************/
package org.adempiere.process;

import java.sql.*;
import java.util.logging.*;

import org.compiere.model.MProcessPara;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.*;

/**
 *	Process to Fill 1099 Extract (T_1099EXTRACT) with data from bpartner, bpartner location and invoices.
 *  @author Carlos Ruiz
 */
@org.adempiere.base.annotation.Process
public class Fill1099Extract extends SvrProcess
{
	private Timestamp	p_Cut_Date = null;
	
	/**
	 *  Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare()
	{
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("Cut_Date"))
				p_Cut_Date = (Timestamp)para[i].getParameter();
			else
				MProcessPara.validateUnknownParameter(getProcessInfo().getAD_Process_ID(), para[i]);
		}
		if (p_Cut_Date == null)
			p_Cut_Date = new Timestamp (System.currentTimeMillis());
	}	//	prepare

	/**
	 * 	DoIt
	 *	@return Message
	 *	@throws Exception
	 */
	@Override
	protected String doIt() throws Exception
	{
		if (log.isLoggable(Level.INFO)) log.info("CUT_DATE=" + p_Cut_Date);
		int AD_PInstance_ID = getAD_PInstance_ID();
		//
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO T_1099EXTRACT ");
		sql.append("(ad_pinstance_id, ad_client_id, ad_org_id, isactive, created, ");
		sql.append("createdby, updated, updatedby, c_bpartner_id, value, name, taxid, ");
		sql.append("c_location_id, cut_year, cut_date, amtbucket01, amtbucket02, ");
		sql.append("amtbucket03, amtbucket04, amtbucket05, amtbucket06, amtbucket07, ");
		sql.append("amtbucket08, amtbucket09, amtbucket10, amtbucket11, amtbucket12, ");
		sql.append("amtbucket13, amtbucket14, amtbucket15, amtbucket16) ");
		sql.append("SELECT ?, bp.ad_client_id, bp.ad_org_id, bp.isactive, bp.created, bp.createdby, ");
		sql.append("bp.updated, bp.updatedby, bp.c_bpartner_id, bp.value, bp.name, bp.taxid, ");
		sql.append("bpl.c_location_id, ");
		if (DB.isPostgreSQL())
			sql.append("date_part('year', ?::timestamp), trunc(?::timestamp),");
		else
			sql.append("EXTRACT(year from ?), TRUNC (?), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 1), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 2), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 3), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 4), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 5), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 6), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 7), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 8), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 9), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 10), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 11), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 12), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 13), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 14), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 15), ");
		sql.append("get1099bucket (bp.c_bpartner_id, ?, 16) ");
		sql.append("FROM c_bpartner bp, c_bpartner_location bpl ");
		sql.append("WHERE bp.c_bpartner_id = bpl.c_bpartner_id ");
		sql.append("AND bp.isactive = 'Y' ");
		sql.append("AND bp.ad_client_id = ? ");
		sql.append("AND bp.isvendor = 'Y' ");
		sql.append("AND bp.is1099vendor = 'Y' ");
		sql.append("AND bpl.c_bpartner_location_id IN ( ");
		sql.append(" SELECT i.c_bpartner_location_id ");
		sql.append(" FROM c_invoice i, c_invoiceline il ");
		sql.append(" WHERE i.c_invoice_id = il.c_invoice_id ");
		sql.append(" AND i.issotrx = 'N' ");
		sql.append(" AND i.docstatus IN ('CO', 'CL') ");
		sql.append(" AND i.ad_client_id = ? ");
		sql.append(" AND i.ispaid = 'Y' "); 	//Yvonne 16/02/2009: only those which have been paid
		sql.append(" AND il.c_1099box_id IS NOT NULL ");
		sql.append(" AND i.dateacct BETWEEN ");
		if (DB.isPostgreSQL())
			sql.append("date_trunc('year', ?::timestamp) ");
		else
			sql.append("TRUNC (?, 'YEAR') ");
		sql.append("AND ?) ");
		if (log.isLoggable(Level.FINEST)) log.finest(sql.toString());
		
		PreparedStatement pstmt = null;
		//
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			pstmt.setInt(1, AD_PInstance_ID);
			pstmt.setTimestamp(2, p_Cut_Date);
			pstmt.setTimestamp(3, p_Cut_Date);
			pstmt.setTimestamp(4, p_Cut_Date);
			pstmt.setTimestamp(5, p_Cut_Date);
			pstmt.setTimestamp(6, p_Cut_Date);
			pstmt.setTimestamp(7, p_Cut_Date);
			pstmt.setTimestamp(8, p_Cut_Date);
			pstmt.setTimestamp(9, p_Cut_Date);
			pstmt.setTimestamp(10, p_Cut_Date);
			pstmt.setTimestamp(11, p_Cut_Date);
			pstmt.setTimestamp(12, p_Cut_Date);
			pstmt.setTimestamp(13, p_Cut_Date);
			pstmt.setTimestamp(14, p_Cut_Date);
			pstmt.setTimestamp(15, p_Cut_Date);
			pstmt.setTimestamp(16, p_Cut_Date);
			pstmt.setTimestamp(17, p_Cut_Date);
			pstmt.setTimestamp(18, p_Cut_Date);
			pstmt.setTimestamp(19, p_Cut_Date);
			pstmt.setInt(20, getAD_Client_ID());
			pstmt.setInt(21, getAD_Client_ID());
			pstmt.setTimestamp(22, p_Cut_Date);
			pstmt.setTimestamp(23, p_Cut_Date);
			
			int rows = pstmt.executeUpdate();
			if (log.isLoggable(Level.INFO)) log.info("inserted rows=" + rows);
		}
		finally
		{
			DB.close(pstmt);
			pstmt = null;
		}
		//
		return "";
	}	//	doIt

}	//	Fill1099Extract