importbpartner.java

Java写的ERP系统

/******************************************************************************
 * 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  Business Solution
 * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
 * Portions created by Jorg Janke are Copyright (C) 1999-2003 Jorg Janke, parts
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package org.compiere.process;

import java.sql.*;
import java.math.*;

import org.compiere.util.*;

/**
 *	Import BPartners from I_BPartner
 *
 * 	@author 	Jorg Janke
 * 	@version 	$Id: ImportBPartner.java,v 1.6 2003/04/01 05:54:09 jjanke Exp $
 */
public class ImportBPartner extends SvrProcess
{
	/**
	 * 	Import BPartner Constructor
	 */
	public ImportBPartner()
	{
		super();
		Log.trace(Log.l1_User, "ImportBPartner");
	}	//	ImportBPartner

	/**	Client to be imported to		*/
	private int				m_AD_Client_ID = 0;
	/**	Delete old Imported				*/
	private boolean			m_deleteOldImported = false;

	/** Organization to be imported to	*/
	private int				m_AD_Org_ID = 0;
	/** Effective						*/
	private Timestamp		m_DateValue = null;

	/**
	 *  Prepare - e.g., get Parameters.
	 */
	protected void prepare()
	{
		Parameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].ParameterName;
			if (name.equals("AD_Client_ID"))
				m_AD_Client_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("DeleteOldImported"))
				m_deleteOldImported = "Y".equals(para[i].Parameter);
			else
				Log.error("ImportBPartner.prepare - 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
	{
		StringBuffer sql = null;
		int no = 0;
		String clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;

		//	****	Prepare	****

		//	Delete Old Imported
		if (m_deleteOldImported)
		{
			sql = new StringBuffer ("DELETE I_BPartner "
				+ "WHERE I_IsImported='Y'").append(clientCheck);
			no = DB.executeUpdate(sql.toString());
			Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Delete Old Impored =" + no);
		}

		//	Set Client, Org, IsActive, Created/Updated
		sql = new StringBuffer ("UPDATE I_BPartner "
			+ "SET AD_Client_ID = COALESCE (AD_Client_ID, ").append(m_AD_Client_ID).append("),"
			+ " AD_Org_ID = COALESCE (AD_Org_ID, 0),"
			+ " 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");
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Reset=" + no);

		//	Set BP_Group
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "SET GroupValue=(SELECT Value FROM C_BP_Group g WHERE g.IsDefault='Y'"
			+ " AND g.AD_Client_ID=i.AD_Client_ID AND ROWNUM=1) "
			+ "WHERE GroupValue IS NULL AND C_BP_Group_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Group Default=" + no);
		//
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "SET C_BP_Group_ID=(SELECT C_BP_Group_ID FROM C_BP_Group g"
			+ " WHERE i.GroupValue=g.Value AND g.AD_Client_ID=i.AD_Client_ID) "
			+ "WHERE C_BP_Group_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Group=" + no);
		//
		sql = new StringBuffer ("UPDATE I_BPartner "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Group, ' "
			+ "WHERE C_BP_Group_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportBPartner.doIt", "Invalid Group=" + no);

		//	Set Country
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'"
			+ " AND c.AD_Client_ID IN (0, i.AD_Client_ID) AND ROWNUM=1) "
			+ "WHERE CountryCode IS NULL AND C_Country_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Country Default=" + no);
		//
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c"
			+ " WHERE i.CountryCode=c.CountryCode AND c.AD_Client_ID IN (0, i.AD_Client_ID)) "
			+ "WHERE C_Country_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Country=" + no);
		//
		sql = new StringBuffer ("UPDATE I_BPartner "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Country, ' "
			+ "WHERE C_Country_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportBPartner.doIt", "Invalid Country=" + no);

		//	Set Region
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "Set RegionName=(SELECT Name FROM C_Region r"
			+ " WHERE r.IsDefault='Y' AND r.C_Country_ID=i.C_Country_ID"
			+ " AND r.AD_Client_ID IN (0, i.AD_Client_ID) AND ROWNUM=1) "
			+ "WHERE RegionName IS NULL AND C_Region_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Region Default=" + no);
		//
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r"
			+ " WHERE r.Name=i.RegionName AND r.C_Country_ID=i.C_Country_ID"
			+ " AND r.AD_Client_ID IN (0, i.AD_Client_ID)) "
			+ "WHERE C_Region_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Region=" + no);
		//
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Region, ' "
			+ "WHERE C_Region_ID IS NULL "
			+ " AND EXISTS (SELECT * FROM C_Country c"
			+ " WHERE c.C_Country_ID=i.C_Country_ID AND c.HasRegion='Y')"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportBPartner.doIt", "Invalid Region=" + no);

		//	Set Greeting
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "SET C_Greeting_ID=(SELECT C_Greeting_ID FROM C_Greeting g"
			+ " WHERE i.BPContactGreeting=g.Name AND g.AD_Client_ID IN (0, i.AD_Client_ID)) "
			+ "WHERE C_Greeting_ID IS NULL AND BPContactGreeting IS NOT NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Set Greeting=" + no);
		//
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Greeting, ' "
			+ "WHERE C_Greeting_ID IS NULL AND BPContactGreeting IS NOT NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportBPartner.doIt", "Invalid Greeting=" + no);


		//	Existing BPartner ? Match Value
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner p"
			+ " WHERE i.Value=p.Value AND p.AD_Client_ID=i.AD_Client_ID) "
			+ "WHERE C_BPartner_ID IS NULL AND Value IS NOT NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Found BPartner=" + no);

		//	Existing Contact ? Match Name
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "SET C_BPartner_Contact_ID=(SELECT C_BPartner_Contact_ID FROM C_BPartner_Contact c"
			+ " WHERE i.ContactName=c.Name AND i.C_BPartner_ID=c.C_BPartner_ID AND c.AD_Client_ID=i.AD_Client_ID) "
			+ "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Contact_ID IS NULL AND ContactName IS NOT NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Found Contact=" + no);

		//	Existing Location ? Exact Match
		sql = new StringBuffer ("UPDATE I_BPartner i "
			+ "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID"
			+ " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)"
			+ " WHERE i.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=i.AD_Client_ID"
			+ " AND DUMP(i.Address1)=DUMP(l.Address1) AND DUMP(i.Address2)=DUMP(l.Address2)"
			+ " AND DUMP(i.City)=DUMP(l.City) AND DUMP(i.Postal)=DUMP(l.Postal) AND DUMP(i.Postal_Add)=DUMP(l.Postal_Add)"
			+ " AND DUMP(i.C_Region_ID)=DUMP(l.C_Region_ID) AND DUMP(i.C_Country_ID)=DUMP(l.C_Country_ID)) "
			+ "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportBPartner.doIt", "Found Location=" + no);


		//	-------------------------------------------------------------------
		int noInsert = 0;
		int noUpdate = 0;

		//	Go through Records
		sql = new StringBuffer ("SELECT I_BPartner_ID, C_BPartner_ID,"
			+ "C_BPartner_Location_ID,COALESCE (Address1,Address2,City),"
			+ "C_BPartner_Contact_ID,ContactName "
			+ "FROM I_BPartner "
			+ "WHERE I_IsImported='N'").append(clientCheck);
		Connection conn = DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED);
		try
		{
			//	Insert BPartner
			PreparedStatement pstmt_insertBPartner = conn.prepareStatement
				("INSERT INTO C_BPartner (C_BPartner_ID,"
				+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
				+ "Value,Name,Name2,Description,DUNS,TaxID,NAICS,C_BP_Group_ID,IsSummary) "
				+ "SELECT ?,"
				+ "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
				+ "Value,Name,Name2,Description,DUNS,TaxID,NAICS,C_BP_Group_ID,'N' "
				+ "FROM I_BPartner "
				+ "WHERE I_BPartner_ID=?");

			//	Update BPartner
			PreparedStatement pstmt_updateBPartner = conn.prepareStatement
				("UPDATE C_BPartner "
				+ "SET (Value,Name,Name2,Description,DUNS,TaxID,NAICS,C_BP_Group_ID,Updated,UpdatedBy)="
				+ "(SELECT Value,Name,Name2,Description,DUNS,TaxID,NAICS,C_BP_Group_ID,SysDate,UpdatedBy"
				+ " FROM I_BPartner"
				+ " WHERE I_BPartner_ID=?) "
				+ "WHERE C_BPartner_ID=?");

			//	Insert Location
			PreparedStatement pstmt_insertLocation = conn.prepareStatement
				("INSERT INTO C_Location (C_Location_ID,"
				+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
				+ "Address1,Address2,City,Postal,Postal_Add,C_Country_ID,C_Region_ID) "
				+ "SELECT ?,"
				+ "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
				+ "Address1,Address2,City,Postal,Postal_Add,C_Country_ID,C_Region_ID "
				+ "FROM I_BPartner "
				+ "WHERE I_BPartner_ID=?");