importproduct.java

Java写的ERP系统

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

		//	Mandatory Value
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Mandatory Value,' "
			+ "WHERE Value IS NULL"
			+ " AND I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportProduct.doIt", "No Mandatory Value=" + no);

		//	Vendor Product No
	//	sql = new StringBuffer ("UPDATE I_Product i "
	//		+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Mandatory VendorProductNo,' "
	//		+ "WHERE I_IsImported<>'Y'"
	//		+ " AND VendorProductNo IS NULL AND (C_BPartner_ID IS NOT NULL OR BPartner_Value IS NOT NULL)").append(clientCheck);
	//	no = DB.executeUpdate(sql.toString());
	//	Log.trace(Log.l3_Util, "ImportProduct.doIt", "No Mandatory VendorProductNo=" + no);
		sql = new StringBuffer ("UPDATE I_Product "
			+ "SET VendorProductNo=Value "
			+ "WHERE C_BPartner_ID IS NOT NULL AND VendorProductNo IS NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportProduct.doIt", "VendorProductNo Set to Value=" + no);
		//
		sql = new StringBuffer ("UPDATE I_Product i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=VendorProductNo not unique,' "
			+ "WHERE I_IsImported<>'Y'"
			+ " AND C_BPartner_ID IS NOT NULL"
			+ " AND (C_BPartner_ID, VendorProductNo) IN "
			+ " (SELECT C_BPartner_ID, VendorProductNo FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID GROUP BY C_BPartner_ID, VendorProductNo HAVING COUNT(*) > 1)")
			.append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportProduct.doIt", "Not Unique VendorProductNo=" + no);

		//	Get Default Tax Category
		int C_TaxCategory_ID = 0;
		try
		{
			PreparedStatement pstmt = DB.prepareStatement
				("SELECT C_TaxCategory_ID FROM C_TaxCategory WHERE IsDefault='Y'" + clientCheck);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				C_TaxCategory_ID = rs.getInt(1);
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			throw new Exception ("ImportProduct.doIt - TaxCategory", e);
		}
		Log.trace(Log.l6_Database, "ImportProduct.doIt", "C_TaxCategory_ID=" + C_TaxCategory_ID);


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

		//	Go through Records
		sql = new StringBuffer ("SELECT I_Product_ID, M_Product_ID, C_BPartner_ID "
			+ "FROM I_Product WHERE I_IsImported='N'").append(clientCheck);
		Connection conn = DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED);
		try
		{
			//	Insert Product from Import
			PreparedStatement pstmt_insertProduct = conn.prepareStatement
				("INSERT INTO M_Product (M_Product_ID,"
				+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
				+ "Value,Name,Description,DocumentNote,Help,"
				+ "UPC,SKU,C_UOM_ID,IsSummary,M_Product_Category_ID,C_TaxCategory_ID,"
				+ "ProductType,ImageURL,DescriptionURL) "
				+ "SELECT ?,"
				+ "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
				+ "Value,Name,Description,DocumentNote,Help,"
				+ "UPC,SKU,C_UOM_ID,'N',M_Product_Category_ID," + C_TaxCategory_ID + ","
				+ "ProductType,ImageURL,DescriptionURL "
				+ "FROM I_Product "
				+ "WHERE I_Product_ID=?");

			//	Update Product from Import
			PreparedStatement pstmt_updateProduct = conn.prepareStatement
				("UPDATE M_PRODUCT "
				+ "SET (Value,Name,Description,DocumentNote,Help,"
				+ "UPC,SKU,C_UOM_ID,M_Product_Category_ID,Classification,ProductType,"
				+ "Volume,Weight,ShelfWidth,ShelfHeight,ShelfDepth,UnitsPerPallet,"
				+ "Discontinued,DiscontinuedBy,Updated,UpdatedBy)= "
				+ "(SELECT Value,Name,Description,DocumentNote,Help,"
				+ "UPC,SKU,C_UOM_ID,M_Product_Category_ID,Classification,ProductType,"
				+ "Volume,Weight,ShelfWidth,ShelfHeight,ShelfDepth,UnitsPerPallet,"
				+ "Discontinued,DiscontinuedBy,SysDate,UpdatedBy"
				+ " FROM I_Product WHERE I_Product_ID=?) "
				+ "WHERE M_Product_ID=?");

			//	Update Product_PO from Import
			PreparedStatement pstmt_updateProductPO = conn.prepareStatement
				("UPDATE M_Product_PO "
				+ "SET (IsCurrentVendor,C_UOM_ID,C_Currency_ID,UPC,"
				+ "PriceList,PricePO,RoyaltyAmt,PriceEffective,"
				+ "VendorProductNo,VendorCategory,Manufacturer,"
				+ "Discontinued,DiscontinuedBy,Order_Min,Order_Pack,"
				+ "CostPerOrder,DeliveryTime_Promised,Updated,UpdatedBy)= "
				+ "(SELECT 'Y',C_UOM_ID,C_Currency_ID,UPC,"
				+ "PriceList,PricePO,RoyaltyAmt,PriceEffective,"
				+ "VendorProductNo,VendorCategory,Manufacturer,"
				+ "Discontinued,DiscontinuedBy,Order_Min,Order_Pack,"
				+ "CostPerOrder,DeliveryTime_Promised,SysDate,UpdatedBy"
				+ " FROM I_Product"
				+ " WHERE I_Product_ID=?) "
				+ "WHERE M_Product_ID=? AND C_BPartner_ID=?");

			//	Insert Product from Import
			PreparedStatement pstmt_insertProductPO = conn.prepareStatement
				("INSERT INTO M_Product_PO (M_Product_ID,C_BPartner_ID, "
				+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
				+ "IsCurrentVendor,C_UOM_ID,C_Currency_ID,UPC,"
				+ "PriceList,PricePO,RoyaltyAmt,PriceEffective,"
				+ "VendorProductNo,VendorCategory,Manufacturer,"
				+ "Discontinued,DiscontinuedBy,Order_Min,Order_Pack,"
				+ "CostPerOrder,DeliveryTime_Promised) "
				+ "SELECT ?,?, "
				+ "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
				+ "'Y',C_UOM_ID,C_Currency_ID,UPC,"
				+ "PriceList,PricePO,RoyaltyAmt,PriceEffective,"
				+ "VendorProductNo,VendorCategory,Manufacturer,"
				+ "Discontinued,DiscontinuedBy,Order_Min,Order_Pack,"
				+ "CostPerOrder,DeliveryTime_Promised "
				+ "FROM I_Product "
				+ "WHERE I_Product_ID=?");

			//	Set Imported = Y
			PreparedStatement pstmt_setImported = conn.prepareStatement
				("UPDATE I_Product SET I_IsImported='Y', M_Product_ID=?, "
				+ "Updated=SysDate, Processed='Y' WHERE I_Product_ID=?");

			//
			PreparedStatement pstmt = DB.prepareStatement(sql.toString());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				int I_Product_ID = rs.getInt(1);
				int M_Product_ID = rs.getInt(2);
				int C_BPartner_ID = rs.getInt(3);
				boolean newProduct = M_Product_ID == 0;
				Log.trace(Log.l6_Database, "I_Product_ID=" + I_Product_ID + ", M_Product_ID=" + M_Product_ID + ", C_BPartner_ID=" + C_BPartner_ID);

				//	Product
				if (newProduct)			//	Insert new Product
				{
					M_Product_ID = DB.getKeyNextNo(m_AD_Client_ID, "N", "M_Product");
					pstmt_insertProduct.setInt(1, M_Product_ID);
					pstmt_insertProduct.setInt(2, I_Product_ID);
					try
					{
						no = pstmt_insertProduct.executeUpdate();
						Log.trace(10, "Insert Product = " + no);
						noInsert++;
					}
					catch (SQLException ex)
					{
						Log.trace(10, "Insert Product - " + ex.toString());
						sql = new StringBuffer ("UPDATE I_Product i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert Product: " + ex.toString()))
							.append("WHERE I_Product_ID=").append(I_Product_ID);
						DB.executeUpdate(sql.toString());
						continue;
					}
				}
				else					//	Update Product
				{
					pstmt_updateProduct.setInt(1, I_Product_ID);
					pstmt_updateProduct.setInt(2, M_Product_ID);
					try
					{
						no = pstmt_updateProduct.executeUpdate();
						Log.trace(10, "Update Product = " + no);
						noUpdate++;
					}
					catch (SQLException ex)
					{
						Log.trace(10, "Update Product - " + ex.toString());
						sql = new StringBuffer ("UPDATE I_Product i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update Product: " + ex.toString()))
							.append("WHERE I_Product_ID=").append(I_Product_ID);
						DB.executeUpdate(sql.toString());
						continue;
					}
				}

				//	Do we have PO Info
				if (C_BPartner_ID != 0)
				{
					no = 0;
					//	If Product existed, Try to Update first
					if (!newProduct)
					{
						pstmt_updateProductPO.setInt(1, I_Product_ID);
						pstmt_updateProductPO.setInt(2, M_Product_ID);
						pstmt_updateProductPO.setInt(3, C_BPartner_ID);
						try
						{
							no = pstmt_updateProductPO.executeUpdate();
							Log.trace(10, "Update Product_PO = " + no);
							noUpdatePO++;
						}
						catch (SQLException ex)
						{
							Log.trace(10, "Update Product_PO - " + ex.toString());
							noUpdate--;
							conn.rollback();
							sql = new StringBuffer ("UPDATE I_Product i "
								+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update Product_PO: " + ex.toString()))
								.append("WHERE I_Product_ID=").append(I_Product_ID);
							DB.executeUpdate(sql.toString());
							continue;
						}
					}
					if (no == 0)		//	Insert PO
					{
						pstmt_insertProductPO.setInt(1, M_Product_ID);
						pstmt_insertProductPO.setInt(2, C_BPartner_ID);
						pstmt_insertProductPO.setInt(3, I_Product_ID);
						try
						{
							no = pstmt_insertProductPO.executeUpdate();
							Log.trace(10, "Insert Product_PO = " + no);
							noInsertPO++;
						}
						catch (SQLException ex)
						{
							Log.trace(10, "Insert Product_PO - " + ex.toString());
							noInsert--;			//	assume that product also did not exist
							conn.rollback();
							sql = new StringBuffer ("UPDATE I_Product i "
								+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert Product_PO: " + ex.toString()))
								.append("WHERE I_Product_ID=").append(I_Product_ID);
							DB.executeUpdate(sql.toString());
							continue;
						}
					}
				}	//	C_BPartner_ID != 0

				//	Update I_Product
				pstmt_setImported.setInt(1, M_Product_ID);
				pstmt_setImported.setInt(2, I_Product_ID);
				no = pstmt_setImported.executeUpdate();
				//
				conn.commit();
			}	//	for all I_Product
			rs.close();
			pstmt.close();

			//
			pstmt_insertProduct.close();
			pstmt_updateProduct.close();
			pstmt_insertProductPO.close();
			pstmt_updateProductPO.close();
			pstmt_setImported.close();
			//
			conn.close();
			conn = null;
		}
		catch (SQLException e)
		{
			try
			{
				if (conn != null)
					conn.close();
				conn = null;
			}
			catch (SQLException ex)
			{
			}
			throw new Exception ("ImportProduct.doIt", e);
		}
		finally
		{
			if (conn != null)
				conn.close();
			conn = null;
		}

		//	Set Error to indicator to not imported
		sql = new StringBuffer ("UPDATE I_Product "
			+ "SET I_IsImported='N', Updated=SysDate "
			+ "WHERE I_IsImported<>'Y'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		addLog (null, 0, new BigDecimal (no), "@Errors@");
		addLog (null, 0, new BigDecimal (noInsert), "@M_Product_ID@: @Inserted@");
		addLog (null, 0, new BigDecimal (noUpdate), "@M_Product_ID@: @Updated@");
		addLog (null, 0, new BigDecimal (noInsertPO), "@M_Product_ID@ @Purchase@: @Inserted@");
		addLog (null, 0, new BigDecimal (noUpdatePO), "@M_Product_ID@ @Purchase@: @Updated@");
		return "";
	}	//	doIt

}	//	ImportProduct