tax.java

Java写的ERP系统

			variable = "M_Product_ID";
			pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, M_Product_ID);
			rs = pstmt.executeQuery();
			if (rs.next())
				C_TaxCategory_ID = rs.getInt(1);
			rs.close();
			pstmt.close();
			if (C_TaxCategory_ID == 0)
			{
				Log.saveError("TaxCriteriaNotFound", Msg.translate(ctx, variable));
				return 0;
			}
			s_log.debug("C_TaxCategory_ID=" + C_TaxCategory_ID);

		//	AD_Org_ID					->	billFromC_Location_ID
			sql = "SELECT C_Location_ID FROM AD_OrgInfo "
				+ "WHERE AD_Org_ID=?";
			variable = "AD_Org_ID";
			pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, AD_Org_ID);
			rs = pstmt.executeQuery();
			if (rs.next())
				billFromC_Location_ID = rs.getInt(1);
			rs.close();
			pstmt.close();
			if (billFromC_Location_ID == 0)
			{
				Log.saveError("TaxCriteriaNotFound", Msg.translate(Env.getAD_Language(ctx), variable));
				return 0;
			}

		//	billC_BPartner_Location_ID  ->	billToC_Location_ID
			sql = "SELECT l.C_Location_ID, b.IsTaxExempt "
				+ "FROM C_BPartner_Location l INNER JOIN C_BPartner b ON (l.C_BPartner_ID=b.C_BPartner_ID) "
				+ "WHERE C_BPartner_Location_ID=?";
			variable = "BillTo_ID";
			pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, billC_BPartner_Location_ID);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				billToC_Location_ID = rs.getInt(1);
				IsTaxExempt = rs.getString(2);
			}
			rs.close();
			pstmt.close();
			if (billToC_Location_ID == 0)
			{
				Log.saveError("TaxCriteriaNotFound", Msg.translate(Env.getAD_Language(ctx), variable));
				return 0;
			}
			if ("Y".equals(IsTaxExempt))
				return getExemptTax(ctx, AD_Org_ID);

			//  Reverse for PO
			if (!IsSOTrx)
			{
				int temp = billFromC_Location_ID;
				billFromC_Location_ID = billToC_Location_ID;
				billToC_Location_ID = temp;
			}
			s_log.debug("billFromC_Location_ID = " + billFromC_Location_ID);
			s_log.debug("billToC_Location_ID = " + billToC_Location_ID);

			//-----------------------------------------------------------------

		//	M_Warehouse_ID				->	shipFromC_Location_ID
			sql = "SELECT C_Location_ID FROM M_Warehouse "
				+ "WHERE M_Warehouse_ID=?";
			variable = "M_Warehouse_ID";
			pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, M_Warehouse_ID);
			rs = pstmt.executeQuery();
			if (rs.next())
				shipFromC_Location_ID = rs.getInt(1);
			rs.close();
			pstmt.close();
			if (shipFromC_Location_ID == 0)
			{
				Log.saveError("TaxCriteriaNotFound", Msg.translate(Env.getAD_Language(ctx), variable));
				return 0;
			}

		//	shipC_BPartner_Location_ID 	->	shipToC_Location_ID
			sql = "SELECT C_Location_ID FROM C_BPartner_Location "
				+ "WHERE C_BPartner_Location_ID=?";
			variable = "C_BPartner_Location_ID";
			pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, shipC_BPartner_Location_ID);
			rs = pstmt.executeQuery();
			if (rs.next())
				shipToC_Location_ID = rs.getInt(1);
			rs.close();
			pstmt.close();
			if (shipToC_Location_ID == 0)
			{
				Log.saveError("TaxCriteriaNotFound", Msg.translate(Env.getAD_Language(ctx), variable));
				return 0;
			}

			//  Reverse for PO
			if (!IsSOTrx)
			{
				int temp = shipFromC_Location_ID;
				shipFromC_Location_ID = shipToC_Location_ID;
				shipToC_Location_ID = temp;
			}
			s_log.debug("shipFromC_Location_ID = " + shipFromC_Location_ID);
			s_log.debug("shipToC_Location_ID = " + shipToC_Location_ID);
		}
		catch (SQLException e)
		{
			s_log.error("getProduct (" + variable + ")", e);
		}

		return get(C_TaxCategory_ID,
			shipDate, shipFromC_Location_ID, shipToC_Location_ID,
			billDate, billFromC_Location_ID, billToC_Location_ID);
	}	//	getProduct

	/**
	 * 	Get Exempt Tax Code
	 * 	@param ctx context
	 * 	@param AD_Org_ID org to find client
	 * 	@return C_Tax_ID
	 */
	private static int getExemptTax (Properties ctx, int AD_Org_ID)
	{
		int C_Tax_ID = 0;
		String sql = "SELECT t.C_Tax_ID "
			+ "FROM C_Tax t"
			+ " INNER JOIN AD_Org o ON (t.AD_Client_ID=o.AD_Client_ID) "
			+ "WHERE t.IsTaxExempt='Y' AND o.AD_Org_ID=? "
			+ "ORDER BY t.Rate DESC";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, AD_Org_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				C_Tax_ID = rs.getInt(1);
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			s_log.error("Tax.getExemptTax", e);
		}
		s_log.debug("TaxExempt=Y - C_Tax_ID=" + C_Tax_ID);
		if (C_Tax_ID == 0)
			Log.saveError("TaxCriteriaNotFound", Msg.getMsg(ctx, "TaxNoExemptFound"));
		return C_Tax_ID;
	}	//	getExemptTax

	/*************************************************************************/

	/**
	 *	Get Tax ID (Detail)
	 *  If error return 0 and set error log (TaxNotFound)
	 *	@param C_TaxCategory_ID tax category
	 *	@param shipDate ship date
	 *	@param shipFromC_Locction_ID ship from
	 *	@param shipToC_Location_ID ship to
	 *	@param billDate invoice date
	 *	@param billFromC_Location_ID invoice from
	 *	@param billToC_Location_ID invoice to
	 *	@return C_Tax_ID
	 */
	protected static int get (int C_TaxCategory_ID,
		Timestamp shipDate, int shipFromC_Locction_ID, int shipToC_Location_ID,
		Timestamp billDate, int billFromC_Location_ID, int billToC_Location_ID)
	{
		s_log.info("get(Detail) - Category=" + C_TaxCategory_ID
			+ ", BillFrom=" + billFromC_Location_ID + ", ShipTo=" + shipToC_Location_ID + ", BillDate=" + billDate);
		int	C_Tax_ID = 0;

		//	C_TaxCategory contains CommodityCode
		//	API to Tax Vendor comes here


		/**********************************************************************
		 *	own table lookup
		 *	- uses billFromC_Location, shipToC_Location, billDate
		 */
		s_log.debug("DestinationTax");
		String sql = "SELECT t.C_Tax_ID, t.ValidFrom "
			+ "FROM C_Tax t, C_Location lf, C_Location lt "
			+ "WHERE t.Parent_Tax_ID IS NULL"
			+ " AND t.C_TaxCategory_ID=?"   //  C_TaxCategory_ID
			+ " AND lf.C_Location_ID=?"     //  billFromC_Location_ID
			+ " AND t.C_Country_ID=lf.C_Country_ID"
			+ " AND (t.C_Region_ID=lf.C_Region_ID OR (t.C_Region_ID IS NULL AND lf.C_Region_ID IS NULL))"
			+ " AND lt.C_Location_ID=?"     //  shipToC_Location_ID
			+ " AND t.To_Country_ID=lt.C_Country_ID"
			+ " AND (t.To_Region_ID=lt.C_Region_ID OR (t.To_Region_ID IS NULL AND lt.C_Region_ID IS NULL)) "
			+ "ORDER BY t.ValidFrom";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, C_TaxCategory_ID);
			pstmt.setInt(2, billFromC_Location_ID);
			pstmt.setInt(3, shipToC_Location_ID);
			ResultSet rs = pstmt.executeQuery();
			//	Take latest one -> wrong, needs to search
			boolean noTax = true;
			while (rs.next() && noTax)
			{
				Timestamp taxDate = rs.getTimestamp(2);
				//	we have the tax rate,
				//	if the tax valid from date is after or equal bill date
				if (taxDate == null || taxDate.after(billDate))
					continue;
				noTax = false;
				C_Tax_ID = rs.getInt(1);
			}
			rs.close();
			pstmt.close();

			//	Try with no destination country in tax table
			if (noTax)
			{
				s_log.debug("OriginTax");
				sql = "SELECT t.C_Tax_ID, t.ValidFrom "
					+ "FROM C_Tax t, C_Location lf "
					+ "WHERE t.Parent_Tax_ID IS NULL"
					+ " AND t.C_TaxCategory_ID=?"       //  C_TaxCategory_ID
					+ " AND lf.C_Location_ID=?"         //  billFromC_Location_ID
					+ " AND t.C_Country_ID=lf.C_Country_ID"
					+ " AND (t.C_Region_ID=lf.C_Region_ID OR (t.C_Region_ID IS NULL AND lf.C_Region_ID IS NULL))"
					+ " AND t.To_Country_ID IS NULL AND t.To_Region_ID IS NULL "
					+ "ORDER BY t.ValidFrom";
				pstmt = DB.prepareStatement(sql);
				pstmt.setInt(1, C_TaxCategory_ID);
				pstmt.setInt(2, billFromC_Location_ID);
				rs = pstmt.executeQuery();
				while (rs.next() && noTax)
				{
					Timestamp taxDate = rs.getTimestamp(2);
					if (taxDate == null || taxDate.after(billDate))
						continue;
					noTax = false;
					C_Tax_ID = rs.getInt(1);
				}
				rs.close();
				pstmt.close();
			}	//	no destination country

			//  Get Default Tax
			if (noTax)
			{
				s_log.debug("DefaultTax");
				sql = "SELECT t.C_Tax_ID, t.ValidFrom "
					+ "FROM C_Tax t, C_Location lf "
					+ "WHERE t.AD_Client_ID=lf.AD_Client_ID"    //  get Client from bill-from
					+ " AND lf.C_Location_ID=?"     //  billFromC_Location_ID
					+ " AND t.IsDefault='Y' "
					+ "ORDER BY t.ValidFrom";
				pstmt = DB.prepareStatement(sql);
				pstmt.setInt(1, billFromC_Location_ID);
				rs = pstmt.executeQuery();
				while (rs.next() && noTax)
				{
					Timestamp taxDate = rs.getTimestamp(2);
					if (taxDate == null || taxDate.after(billDate))
						continue;
					noTax = false;
					C_Tax_ID = rs.getInt(1);
				}
				rs.close();
				pstmt.close();
			}
		}
		catch (SQLException e)
		{
			s_log.error("get (C_Tax_ID) - " + sql, e);
		}

		if (C_Tax_ID == 0)
			Log.saveError("TaxNotFound", "");

		return C_Tax_ID;
	}	//	get

}	//	Tax