db.java

Java写的ERP系统

		Timestamp ConvDate, String RateType, int AD_Client_ID, int AD_Org_ID)
	{
		BigDecimal retValue = null;
		if (CurFrom_ID == CurTo_ID)
			return new BigDecimal("1");
		//
		try
		{
			String sql = "{? = call C_Currency_Rate(?,?,?,?,?,?)}";
			CallableStatement cstmt = prepareCall(sql);
			//
			cstmt.registerOutParameter(1, Types.NUMERIC);
			//
			cstmt.setInt(2, CurFrom_ID);             		//	CurFrom		IN 		NUMBER
			cstmt.setInt(3, CurTo_ID);                  	//	CurTo		IN 		NUMBER
			if (ConvDate == null)
				ConvDate = new Timestamp (System.currentTimeMillis());
			cstmt.setTimestamp(4, ConvDate);				//	ConvDate	IN 		DATE
			if (RateType == null || RateType.equals(""))
				RateType = "S";
			cstmt.setString(5, RateType);					//	RateType	IN 		CHAR
			cstmt.setInt(6, AD_Client_ID);
			cstmt.setInt(7, AD_Org_ID);
			//
			cstmt.executeUpdate();
			retValue = cstmt.getBigDecimal(1);
			cstmt.close();
		}
		catch(SQLException e)
		{
			s_log.error("getConvesionRate", e);
		}
		if (retValue == null)
			s_log.info("getConversionRate - not found - CurFrom=" + CurFrom_ID + ", CurTo=" + CurTo_ID
				+ ", " + ConvDate + ", " + RateType + ", Client=" + AD_Client_ID + ", Org=" + AD_Org_ID);
		return retValue;
	}	//	getConvesionRate


	/**
	 *  Get fully qualified Account Combination
	 *
	 *  @param AD_Client_ID client
	 *  @param AD_Org_ID org
	 *  @param C_AcctSchema_ID acct schema
	 *  @param Account_ID natural account
	 *  @param  base_ValidCombination_ID optional base combination to be specified
	 *  @param Alias aloas
	 *  @param AD_User_ID user
	 *  @param M_Product_ID product
	 *  @param C_BPartner_ID partner
	 *  @param AD_OrgTrx_ID trx org
	 *  @param C_LocFrom_ID loc from
	 *  @param C_LocTo_ID loc to
	 *  @param C_SRegion_ID sales region
	 *  @param C_Project_ID project
	 *  @param C_Campaign_ID campaign
	 *  @param C_Activity_ID activity
	 *  @param User1_ID user1
	 *  @param User2_ID user2
	 *  @return C_ValidCombination_ID of existing or new Combination
	 */
	public static int getValidCombination (int AD_Client_ID, int AD_Org_ID,
		int C_AcctSchema_ID, int Account_ID, int base_ValidCombination_ID, String Alias, int AD_User_ID,
		int M_Product_ID, int C_BPartner_ID, int AD_OrgTrx_ID,
		int C_LocFrom_ID, int C_LocTo_ID, int C_SRegion_ID, int C_Project_ID,
		int C_Campaign_ID, int C_Activity_ID, int User1_ID, int User2_ID)
	{
		int retValue = 0;
		StringBuffer sb = new StringBuffer ("C_ValidCombination_Get(");
		try
		{
			String sql = "{CALL C_ValidCombination_Get(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?)}";
			CallableStatement cstmt = prepareCall(sql);
			//  1 - ID
			cstmt.registerOutParameter(1, Types.NUMERIC);
			sb.append("v,");
			//  --  Mandatory Accounting fields
			//	2 - AD_Client_ID
			cstmt.setInt(2, AD_Client_ID);
			sb.append(AD_Client_ID).append(",");
			//	3 - AD_Org_ID
			cstmt.setInt(3, AD_Org_ID);
			sb.append(AD_Org_ID).append(",");
			//  4- C_AcctSchema_ID
			cstmt.setInt(4, C_AcctSchema_ID);
			sb.append(C_AcctSchema_ID).append(",");
			//  5 - Account_ID
			cstmt.setInt(5, Account_ID);
			sb.append(Account_ID).append(", ");

			//  --  Optional
			//  6 - Base C_ValidCombination_ID
			if (base_ValidCombination_ID != 0)
			{
				cstmt.setInt (6, base_ValidCombination_ID);
				sb.append(base_ValidCombination_ID).append(",");
			}
			else
			{
				cstmt.setNull(6, Types.NUMERIC);
				sb.append("NULL,");
			}
			//  7 - MustBeFullyQualified
			cstmt.setString(7, "Y");
			sb.append("'Y',");
			//  8 - Alias
			if (Alias != null && Alias.length() > 0)
			{
				cstmt.setString(8, Alias);
				sb.append("'").append(Alias).append("';");
			}
			else
			{
				cstmt.setNull(8, Types.VARCHAR);
				sb.append("NULL,");
			}
			//  9 - CreatedBy
			cstmt.setInt(9, AD_User_ID);
			sb.append(AD_User_ID).append(", ");

			//	--  Optional Accounting fields
			//  10 - M_Product_ID
			if (M_Product_ID != 0)
			{
				cstmt.setInt(10, M_Product_ID);
				sb.append(M_Product_ID).append(",");
			}
			else
			{
				cstmt.setNull(10, Types.NUMERIC);
				sb.append("NULL,");
			}
			//  11 - C_BPartner_ID
			if (C_BPartner_ID != 0)
			{
				cstmt.setInt(11, C_BPartner_ID);
				sb.append(C_BPartner_ID).append(",");
			}
			else
			{
				cstmt.setNull(11, Types.NUMERIC);
				sb.append("NULL,");
			}
			//  12 - AD_OrgTrx_ID
			if (AD_OrgTrx_ID != 0)
			{
				cstmt.setInt(12, AD_OrgTrx_ID);
				sb.append(AD_OrgTrx_ID).append(",");
			}
			else
			{
				cstmt.setNull(12, Types.NUMERIC);
				sb.append("NULL,");
			}
			//  13 - C_LocFrom_ID
			if (C_LocFrom_ID != 0)
			{
				cstmt.setInt(13, C_LocFrom_ID);
				sb.append(C_LocFrom_ID).append(",");
			}
			else
			{
				cstmt.setNull(13, Types.NUMERIC);
				sb.append("NULL,");
			}
			//  14 - C_LocTo_ID
			if (C_LocTo_ID != 0)
			{
				cstmt.setInt(14, (C_LocTo_ID));
				sb.append(C_LocTo_ID).append(", ");
			}
			else
			{
				cstmt.setNull(14, Types.NUMERIC);
				sb.append("NULL, ");
			}
			//  15 - C_SalesRegion_ID
			if (C_SRegion_ID != 0)
			{
				cstmt.setInt(15, (C_SRegion_ID));
				sb.append(C_SRegion_ID).append(",");
			}
			else
			{
				cstmt.setNull(15, Types.NUMERIC);
				sb.append("NULL,");
			}
			//  16 - C_Project_ID
			if (C_Project_ID != 0)
			{
				cstmt.setInt(16, (C_Project_ID));
				sb.append(C_Project_ID).append(",");
			}
			else
			{
				cstmt.setNull(16, Types.NUMERIC);
				sb.append("NULL,");
			}
			//  17 - C_Campaign_ID
			if (C_Campaign_ID != 0)
			{
				cstmt.setInt(17, (C_Campaign_ID));
				sb.append(C_Campaign_ID).append(",");
			}
			else
			{
				cstmt.setNull(17, Types.NUMERIC);
				sb.append("NULL,");
			}
			//  18 - C_Activity_ID
			if (C_Activity_ID != 0)
			{
				cstmt.setInt(18, (C_Activity_ID));
				sb.append(C_Activity_ID).append(",");
			}
			else
			{
				cstmt.setNull(18, Types.NUMERIC);
				sb.append("NULL,");
			}
			//  19 - User1_ID
			if (User1_ID != 0)
			{
				cstmt.setInt(19, (User1_ID));
				sb.append(User1_ID).append(",");
			}
			else
			{
				cstmt.setNull(19, Types.NUMERIC);
				sb.append("NULL,");
			}
			//  20 - User2_ID
			if (User2_ID != 0)
			{
				cstmt.setInt(20, (User2_ID));
				sb.append(User2_ID).append(")");
			}
			else
			{
				cstmt.setNull(20, Types.NUMERIC);
				sb.append("NULL)");
			}

			//
			cstmt.executeUpdate();
			retValue = cstmt.getInt(1);     //  1 - ID
			cstmt.close();
		}
		catch(SQLException e)
		{
			s_log.error("getValidCombination " + sb.toString(), e);
		}
		return retValue;
	}   //  getValidCombination

	/**
	 *  Insert Note
	 *  @param AD_Client_ID client
	 *  @param AD_Org_ID org
	 *  @param AD_User_ID user
	 *  @param AD_Table_ID table
	 *  @param Record_ID record
	 *  @param AD_MessageValue message
	 *  @param Text text
	 *  @param Reference subject
	 *  @return true if note was inserted
	 */
	public static boolean insertNote (int AD_Client_ID, int AD_Org_ID, int AD_User_ID,
		int AD_Table_ID, int Record_ID,
		String AD_MessageValue, String Text, String Reference)
	{
		if (AD_MessageValue == null || AD_MessageValue.length() == 0)
			throw new IllegalArgumentException("DB.insertNote - required parameter missing - AD_Message");

		//  Database limits
		if (Text == null)
			Text = "";
		if (Reference == null)
			Reference = "";
		//
		s_log.info("insertNote - " + AD_MessageValue + " - " + Reference);
		//
		StringBuffer sql = new StringBuffer ("INSERT INTO AD_Note (AD_Note_ID,");
		sql.append("AD_Client_ID,AD_Org_ID,IsActive, Created,CreatedBy,Updated,UpdatedBy, ")
			.append("AD_Message_ID,Text,Reference, ")
			.append("AD_User_ID,AD_Table_ID,Record_ID,Processed) ")
			.append("SELECT ");
		//
		String CompiereSys = "N";
		int AD_Note_ID = getKeyNextNo(AD_Client_ID, CompiereSys, "AD_Note");
		sql.append(AD_Note_ID).append(",");
		//
		sql.append(AD_Client_ID).append(",")
			.append(AD_Org_ID).append(", 'Y',SysDate,")
			.append(AD_User_ID).append(",SysDate,0,");
		//	AD_Message_ID,Text,Reference,
		sql.append(" AD_Message_ID,").append(DB.TO_STRING(Text, 2000)).append(",")
			.append(DB.TO_STRING(Reference, 60)).append(", ");
		//	AD_User_ID,AD_Table_ID,Record_ID,Processed
		sql.append(AD_User_ID).append(",").append(AD_Table_ID).append(",")
			.append(Record_ID).append(",'N' ");
		//
		sql.append("FROM AD_Message WHERE Value='").append(AD_MessageValue).append("'");
		//  Create Entry
		int no = executeUpdate(sql.toString());

		//  AD_Message must exist, so if not created, it is probably
		//  due to non-existing AD_Message
		if (no == 0)
		{
			sql = new StringBuffer ("INSERT INTO AD_Note (AD_Note_ID,");
			sql.append("AD_Client_ID,AD_Org_ID,IsActive, Created,CreatedBy,Updated,UpdatedBy, ")
				.append("AD_Message_ID,Text,Reference, ")
				.append("AD_User_ID,AD_Table_ID,Record_ID,Processed) ")
				.append("SELECT ");
			//  - use AD_Note_ID from above
			sql.append(AD_Note_ID).append(",");
			//
			sql.append(AD_Client_ID).append(",")
				.append(AD_Org_ID).append(", 'Y',SysDate,")
				.append(AD_User_ID).append(",SysDate,0, ");
			//	AD_Message_ID,Text,Reference,
			sql.append("AD_Message_ID,").append(TO_STRING (AD_MessageValue + ": " + Text, 2000)).append(",")
				.append(TO_STRING(Reference,60)).append(", ");
			//	AD_User_ID,AD_Table_ID,Record_ID,Processed
			sql.append(AD_User_ID).append(",").append(AD_Table_ID).append(",")
				.append(Record_ID).append(",'N' ");
			//  Hardcoded AD_Message - AD_Message is in Text
			sql.append("FROM AD_Message WHERE Value='NoMessageFound'");
			//  Try again
			no = executeUpdate(sql.toString());
		}

		return no == 1;
	}   //  insertNote


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

	/**
	 *	Print SQL Warnings.
	 *  <br>
	 *		Usage: DB.printWarning(rs.getWarnings(), "xx");
	 *  @param comment comment
	 *  @param warning warning
	 */
	public static void printWarning(String comment, SQLWarning warning)
	{
		if (comment == null || warning == null || comment.length() == 0)
			throw new IllegalArgumentException("DB.printException - required parameter missing");
		s_log.warn("SQL Warning: " + comment);
		if (warning == null)
			return;
		//
		SQLWarning warn = warning;
		while (warn != null)
		{
			StringBuffer buffer = new StringBuffer();
			buffer.append(warn.getMessage());
			buffer.append("; State=").append(warn.getSQLState()).append("; ErrorCode=").append(warn.getErrorCode());
			s_log.warn(buffer.toString());
			warn = warn.getNextWarning();
		}
	}	//	printWarning

	/**
	 *  Create SQL TO Date String from Timestamp
	 *
	 *  @param  time Date to be converted
	 *  @param  dayOnly true if time set to 00:00:00
	 *
	 *  @return TO_DATE('2001-01-30 18:10:20',''YYYY-MM-DD HH24:MI:SS')
	 *      or  TO_DATE('2001-01-30',''YYYY-MM-DD')
	 */
	public static String TO_DATE (Timestamp time, boolean dayOnly)
	{
		if (time == null)
		{
			if (dayOnly)
				return "TRUNC(SysDate)";
			return "SysDate";
		}

		StringBuffer dateString = new StringBuffer("TO_DATE('");
		//  YYYY-MM-DD HH24:MI:SS.mmmm  JDBC Timestamp format
		String myDate = time.toString();
		if (dayOnly)
		{
			dateString.append(myDate.substring(0,10));
			dateString.append("','YYYY-MM-DD')");
		}
		else
		{
			dateString.append(myDate.substring(0, myDate.indexOf(".")));	//	cut off miliseconds
			dateString.append("','YYYY-MM-DD HH24:MI:SS')");
		}
		return dateString.toString();
	}   //  TO_DATE

	/**
	 *  Create SQL TO Date String from Timestamp
	 *  @param time time
	 *  @return TO_DATE String
	 */
	public static String TO_DATE (Timestamp time)
	{
		return TO_DATE(time, true);
	}   //  TO_DATE

	/**
	 *  Create SQL for formatted Date, Number
	 *
	 *  @see org.compiere.util.DisplayType
	 *  @see org.compiere.util.Env
	 *
	 *  @param  columnName  the column name in the SQL
	 *  @param  displayType Display Type
	 *  @param  AD_Language 6 character language setting (from Env.LANG_*)
	 *
	 *  @return TRIM(TO_CHAR(columnName,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.'''))
	 *      or TRIM(TO_CHAR(columnName,'TM9')) depending on DisplayType and Language
	 */
	public static String TO_CHAR (String columnName, int displayType, String AD_Language)
	{
		if (columnName == null || AD_Language == null || columnName.length() == 0)
			throw new IllegalArgumentException("DB.TO_CHAR - required parameter missing");

		StringBuffer retValue = new StringBuffer("TRIM(TO_CHAR(");
		retValue.append(columnName);

		//  Numbers
		if (DisplayType.isNumeric(displayType))
		{
			if (displayType == DisplayType.Amount)
				retValue.append(",'9G999G990D00'");
			else
				retValue.append(",'TM9'");
			//  TO_CHAR(GrandTotal,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.''')
			if (!Language.isDecimalPoint(AD_Language))      //  reversed
				retValue.append(",'NLS_NUMERIC_CHARACTERS='',.'''");
		}
		else if (DisplayType.isDate(displayType))
		{
			retValue.append(",'")
				.append(Language.getLanguage(AD_Language).getDBdatePattern())
				.append("'");
		}

		retValue.append("))");
		//
		return retValue.toString();
	}   //  TO_CHAR_Number


	/**
	 *  Package Strings for SQL command
	 *  @param txt  String with text
	 *  @return escaped string for insert statement (NULL if null)
	 */
	public static String TO_STRING (String txt)
	{
		return TO_STRING (txt, 0);
	}   //  TO_STRING

	/**
	 *	Package Strings for SQL command.
	 *  <pre>
	 *		-	include in ' (single quotes)
	 *		-	replace ' with ''
	 *  </pre>
	 *  @param txt  String with text
	 *  @param maxLength    Maximum Length of content or 0 to ignore
	 *  @return escaped string for insert statement (NULL if null)
	 */
	public static String TO_STRING (String txt, int maxLength)
	{
		if (txt == null)
			return "NULL";

		//  Length
		String text = txt;
		if (maxLength != 0 && text.length() > maxLength)
			text = txt.substring(0, maxLength);

		char quote = '\'';
		//  copy characters		(wee need to look through anyway)
		StringBuffer out = new StringBuffer();
		out.append(quote);
		for (int i = 0; i < text.length(); i++)
		{
			char c = text.charAt(i);
			if (c == quote)
				out.append("''");
			else
				out.append(c);
		}
		out.append(quote);
		//
		return out.toString();
	}	//	TO_STRING

}	//	DB