importproduct.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 Products from I_Product
 *
 * 	@author 	Jorg Janke
 * 	@version 	$Id: ImportProduct.java,v 1.5 2003/01/16 04:33:38 jjanke Exp $
 */
public class ImportProduct extends SvrProcess
{
	/**
	 * 	Import Product Constructor
	 */
	public ImportProduct()
	{
		super();
		Log.trace(Log.l1_User, "ImportProduct");
	}	//	ImportProduct

	/**	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("ImportProduct.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_Product "
				+ "WHERE I_IsImported='Y'").append(clientCheck);
			no = DB.executeUpdate(sql.toString());
			Log.trace(Log.l5_DData, "ImportProduct.doIt", "Delete Old Impored =" + no);
		}

		//	Set Client, Org, IaActive, Created/Updated, 	ProductType
		sql = new StringBuffer ("UPDATE I_Product "
			+ "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),"
			+ " ProductType = COALESCE (ProductType, 'I'),"
			+ " 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, "ImportProduct.doIt", "Reset=" + no);

		//	Set Optional BPartner
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner p"
			+ " WHERE i.BPartner_Value=p.Value AND i.AD_Client_ID=p.AD_Client_ID) "
			+ "WHERE C_BPartner_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "BPartner=" + no);
		//
		sql = new StringBuffer ("UPDATE I_Product "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid BPartner,' "
			+ "WHERE C_BPartner_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportProduct.doIt", "Invalid BPartner=" + no);


		//	****	Find Product
		//	EAN/UPC
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
			+ " WHERE i.UPC=p.UPC AND i.AD_Client_ID=p.AD_Client_ID) "
			+ "WHERE M_Product_ID IS NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "Product Existing UPC=" + no);

		//	Value
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
			+ " WHERE i.Value=p.Value AND i.AD_Client_ID=p.AD_Client_ID) "
			+ "WHERE M_Product_ID IS NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "Product Existing Value=" + no);

		//	BP ProdNo
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product_po p"
			+ " WHERE i.C_BPartner_ID=p.C_BPartner_ID"
			+ " AND i.VendorProductNo=p.VendorProductNo AND i.AD_Client_ID=p.AD_Client_ID) "
			+ "WHERE M_Product_ID IS NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "Product Existing Vendor ProductNo=" + no);


		//	Copy From Product if Import does not have value
		String[] strFields = new String[] {"Value","Name","Description","DocumentNote","Help",
			"UPC","SKU","Classification","ProductType",
			"Discontinued","DiscontinuedBy","ImageURL","DescriptionURL"};
		for (int i = 0; i < strFields.length; i++)
		{
			sql = new StringBuffer ("UPDATE I_PRODUCT i "
				+ "SET ").append(strFields[i]).append(" = (SELECT ").append(strFields[i]).append(" FROM M_Product p"
				+ " WHERE i.M_Product_ID=p.M_Product_ID AND i.AD_Client_ID=p.AD_Client_ID) "
				+ "WHERE M_Product_ID IS NOT NULL"
				+ " AND ").append(strFields[i]).append(" IS NULL"
				+ " AND I_IsImported='N'").append(clientCheck);
			no = DB.executeUpdate(sql.toString());
		//	if (no != 0)
				Log.trace(Log.l5_DData, "ImportProduct.doIt", strFields[i] + " Default from existing Product=" + no);
		}
		String[] numFields = new String[] {"C_UOM_ID","M_Product_Category_ID",
			"Volume","Weight","ShelfWidth","ShelfHeight","ShelfDepth","UnitsPerPallet"};
		for (int i = 0; i < numFields.length; i++)
		{
			sql = new StringBuffer ("UPDATE I_PRODUCT i "
				+ "SET ").append(numFields[i]).append(" = (SELECT ").append(numFields[i]).append(" FROM M_Product p"
				+ " WHERE i.M_Product_ID=p.M_Product_ID AND i.AD_Client_ID=p.AD_Client_ID) "
				+ "WHERE M_Product_ID IS NOT NULL"
				+ " AND (").append(numFields[i]).append(" IS NULL OR ").append(numFields[i]).append("=0)"
				+ " AND I_IsImported='N'").append(clientCheck);
			no = DB.executeUpdate(sql.toString());
		//	if (no != 0)
				Log.trace(Log.l5_DData, "ImportProduct.doIt", numFields[i] + " Default from existing Product=" + no);
		}

		//	Copy From Product_PO if Import does not have value
		String[] strFieldsPO = new String[] {"UPC",
			"PriceEffective","VendorProductNo","VendorCategory","Manufacturer",
			"Discontinued","DiscontinuedBy"};
		for (int i = 0; i < strFieldsPO.length; i++)
		{
			sql = new StringBuffer ("UPDATE I_PRODUCT i "
				+ "SET ").append(strFieldsPO[i]).append(" = (SELECT ").append(strFieldsPO[i]).append(" FROM M_Product_PO p"
				+ " WHERE i.M_Product_ID=p.M_Product_ID AND i.C_BPartner_ID=p.C_BPartner_ID AND i.AD_Client_ID=p.AD_Client_ID) "
				+ "WHERE M_Product_ID IS NOT NULL AND C_BPartner_ID IS NOT NULL"
				+ " AND ").append(strFieldsPO[i]).append(" IS NULL"
				+ " AND I_IsImported='N'").append(clientCheck);
			no = DB.executeUpdate(sql.toString());
			if (no != 0)
				Log.trace(Log.l5_DData, "ImportProduct.doIt", strFieldsPO[i] + " Default from existing Product=" + no);
		}
		String[] numFieldsPO = new String[] {"C_UOM_ID","C_Currency_ID",
			"PriceList","PricePO","RoyaltyAmt",
			"Order_Min","Order_Pack","CostPerOrder","DeliveryTime_Promised"};
		for (int i = 0; i < numFieldsPO.length; i++)
		{
			sql = new StringBuffer ("UPDATE I_PRODUCT i "
				+ "SET ").append(numFieldsPO[i]).append(" = (SELECT ").append(numFieldsPO[i]).append(" FROM M_Product_PO p"
				+ " WHERE i.M_Product_ID=p.M_Product_ID AND i.C_BPartner_ID=p.C_BPartner_ID AND i.AD_Client_ID=p.AD_Client_ID) "
				+ "WHERE M_Product_ID IS NOT NULL AND C_BPartner_ID IS NOT NULL"
				+ " AND (").append(numFieldsPO[i]).append(" IS NULL OR ").append(numFieldsPO[i]).append("=0)"
				+ " AND I_IsImported='N'").append(clientCheck);
			no = DB.executeUpdate(sql.toString());
			if (no != 0)
				Log.trace(Log.l5_DData, "ImportProduct.doIt", numFieldsPO[i] + " Default from existing Product=" + no);
		}


		//	Set UOM (System/own)
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET X12DE355 = COALESCE (("
			+ "SELECT X12DE355 FROM C_UOM u WHERE u.IsDefault='Y' AND u.AD_Client_ID IN (0,i.AD_Client_ID) AND ROWNUM=1"
			+ "), 'EA') "
			+ "WHERE X12DE355 IS NULL AND C_UOM_ID IS NULL"
			+ " AND I_IsImported<>'Y'"
			+ " AND C_UOM_ID IS NULL").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set UOM Default=" + no);
		//
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET C_UOM_ID = (SELECT C_UOM_ID FROM C_UOM u WHERE u.X12DE355=i.X12DE355 AND u.AD_Client_ID IN (0,i.AD_Client_ID)) "
			+ "WHERE C_UOM_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set UOM=" + no);
		//
		sql = new StringBuffer ("UPDATE I_Product "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid UOM, ' "
			+ "WHERE C_UOM_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportProduct.doIt", "Invalid UOM=" + no);

		//	Set Product Category (own)
		sql = new StringBuffer ("UPDATE I_Product "
			+ "SET ProductCategory_Value=(SELECT Value FROM M_Product_Category"
			+ " WHERE IsDefault='Y' AND AD_Client_ID=").append(m_AD_Client_ID).append(" AND ROWNUM=1) "
			+ "WHERE ProductCategory_Value IS NULL AND M_Product_Category_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set Category Default=" + no);
		//
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET M_Product_Category_ID=(SELECT M_Product_Category_ID FROM M_Product_Category c"
			+ " WHERE i.ProductCategory_Value=c.Value AND i.AD_Client_ID=c.AD_Client_ID) "
			+ "WHERE M_Product_Category_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set Category=" + no);
		//
		sql = new StringBuffer ("UPDATE I_Product "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid ProdCategorty,' "
			+ "WHERE M_Product_Category_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportProduct.doIt", "Invalid Category=" + no);

		//	Set Currency
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET ISO_Code=(SELECT ISO_Code FROM C_Currency c"
			+ " INNER JOIN C_AcctSchema a ON (a.C_Currency_ID=c.C_Currency_ID)"
			+ " INNER JOIN AD_ClientInfo ci ON (a.C_AcctSchema_ID=ci.C_AcctSchema1_ID)"
			+ " WHERE ci.AD_Client_ID=i.AD_Client_ID) "
			+ "WHERE C_Currency_ID IS NULL AND ISO_Code IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set Currency Default=" + no);
		//
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET C_Currency_ID=(SELECT C_Currency_ID FROM C_Currency c"
			+ " WHERE i.ISO_Code=c.ISO_Code AND c.AD_Client_ID IN (0,i.AD_Client_ID)) "
			+ "WHERE C_Currency_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "Set Currency=" + no);
		//
		sql = new StringBuffer ("UPDATE I_Product "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Currency,' "
			+ "WHERE C_Currency_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportProduct.doIt", "Invalid Currency=" + no);

		//	Verify ProductType
		sql = new StringBuffer ("UPDATE I_Product "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid ProductType,' "
			+ "WHERE ProductType NOT IN ('I','S')"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportProduct.doIt", "Invalid ProductType=" + no);

		//	Unique UPC/Value
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Value not unique,' "
			+ "WHERE I_IsImported<>'Y'"
			+ " AND Value IN (SELECT Value FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID GROUP BY Value HAVING COUNT(*) > 1)").append(clientCheck);