db.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-2001 Jorg Janke, parts
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package org.compiere.util;

import java.math.*;
import java.util.*;
import java.text.*;
import java.security.*;
import javax.swing.*;

import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;

import org.apache.log4j.Logger;

import org.compiere.Compiere;
import org.compiere.util.*;
import org.compiere.db.*;

/**
 *  General Database Interface
 *
 *  @author     Jorg Janke
 *  @version    $Id: DB.java,v 1.12 2003/04/13 05:53:30 jjanke Exp $
 */
public final class DB
{
	/** Connection Descriptor           */
	private static CConnection      s_cc = null;
	/** Connection Cache r/o            */
	private static Connection[]		s_connections = null;
	/** Connection Cache Size           */
	private static int              s_conCacheSize = 2;   //  client
	/** Connection counter              */
	private static int              s_conCount = 0;
	/** Connection r/w                  */
	private static Connection		s_connectionRW = null;
	/**	Logger							*/
	private static Logger			s_log = Logger.getLogger (DB.class);

	/**
	 *	Client Login.
	 *  <p>
	 *  - Get Connection
	 *  - Compare User info
	 *  <p>
	 *  Sets Conext with login info
	 *
	 * @param ctx context
	 * @param cc connection
	 * @param app_user user
	 * @param app_pwd pwd
	 * @param force ignore pwd
	 * @return  Array of Role KeyNamePair or null if error
	 * The error (NoDatabase, UserPwdError, DBLogin) is saved in the log
	 */
	protected static KeyNamePair[] login (Properties ctx,
		CConnection cc,
		String app_user, String app_pwd, boolean force)
	{
		if (ctx == null)
			throw new IllegalArgumentException("DB.login - required parameter missing");
		//	Establish connection
		closeTarget();
		setDBTarget(cc);
		Env.setContext(ctx, "#Host", s_cc.getAppsHost());
		Env.setContext(ctx, "#Database", s_cc.getDbName());
		if (getConnectionRO() == null)
		{
			Log.saveError("NoDatabase", "");
			return null;
		}
		if (app_pwd == null)
			return null;
		//
		return loginDB (ctx, app_user, app_pwd, force);
	}   //  login

	/**
	 *  Client Login.
	 *  <p>
	 *  Compare User Info
	 *  <p>
	 *  Sets Conext with login info
	 *
	 *  @param ctx context
	 *  @param app_user Principal
	 *  @return role array or null if in error.
	 *  The error (NoDatabase, UserPwdError, DBLogin) is saved in the log
	 */
	public static KeyNamePair[] login (Properties ctx, Principal app_user)
	{
		if (app_user == null)
			return null;
		//  login w/o password as previously authorized
		return loginDB (ctx, app_user.getName(), null, false);
	}   //  app_user

	/**
	 *  Client Login.
	 *  <p>
	 *  Compare User Info
	 *  <p>
	 *  Sets Conext with login info
	 *
	 *  @param ctx context
	 *  @param app_user user id
	 *  @param app_pwd password
	 *  @return role array or null if in error.
	 *  The error (NoDatabase, UserPwdError, DBLogin) is saved in the log
	 */
	public static KeyNamePair[] login (Properties ctx,
		String app_user, String app_pwd)
	{
		if (app_pwd == null)
			return null;
		return loginDB (ctx, app_user, app_pwd, false);
	}   //  login

	/**
	 *  Actual DB login procedure.
	 *
	 *  @param ctx context
	 *  @param app_user user
	 *  @param app_pwd pwd
	 *  @param force ignore pwd
	 *  @return role array or null if in error.
	 *  The error (NoDatabase, UserPwdError, DBLogin) is saved in the log
	 */
	private static KeyNamePair[] loginDB (Properties ctx,
		String app_user, String app_pwd, boolean force)
	{
		s_log.info("login - User=" + app_user);

		if (ctx == null)
			throw new IllegalArgumentException("DB.login - required parameter missing");
		if (app_user == null)
			return null;

		KeyNamePair[] retValue = null;
		ArrayList list = new ArrayList();
		//
		String sql = "SELECT AD_User.AD_User_ID, AD_User.Description,"
			+ " AD_Role.AD_Role_ID, AD_Role.Name "
			+ "FROM AD_User, AD_User_Roles, AD_Role "
			+ "WHERE AD_User.AD_User_ID=AD_User_Roles.AD_User_ID"
			+ " AND AD_User_Roles.AD_Role_ID=AD_Role.AD_Role_ID"
			+ " AND AD_User.Name=?"		        							//	#1
			+ " AND AD_User.IsActive='Y' AND AD_Role.IsActive='Y' AND AD_User_Roles.IsActive='Y'";
		if (app_pwd != null)
			sql += " AND (AD_User.Password=? OR AD_User.Password=?)";   	//  #2/3
		try
		{
			PreparedStatement pstmt = prepareStatement(sql);
			pstmt.setString(1, app_user);
			if (app_pwd != null)
			{
				pstmt.setString(2, app_pwd);
				pstmt.setString(3, Secure.getDigest(app_pwd));
			}
			//	execute a query
			ResultSet rs = pstmt.executeQuery();

			if (!rs.next())		//	no record found
				if (force)
				{
					Env.setContext(ctx, "#AD_User_Name", "System");
					Env.setContext(ctx, "#AD_User_ID", "0");
					Env.setContext(ctx, "#AD_User_Description", "System Forced Login");
					Env.setContext(ctx, "#User_Level", "S  ");  	//	Format 'SCO'
					Env.setContext(ctx, "#User_Client", "0");		//	Format c1, c2, ...
					Env.setContext(ctx, "#User_Org", "0"); 		//	Format o1, o2, ...
					rs.close();
					pstmt.close();
					retValue = new KeyNamePair[] {new KeyNamePair(0, "System Administrator")};
					return retValue;
				}
				else
				{
					rs.close();
					pstmt.close();
					Log.saveError("UserPwdError", app_user, false);
					return null;
				}

			Env.setContext(ctx, "#AD_User_Name", app_user);
			Env.setContext(ctx, "#AD_User_ID", rs.getInt("AD_User_ID"));
			Env.setContext(ctx, "#SalesRep_ID", rs.getInt("AD_User_ID"));
			Env.setContext(ctx, "#AD_User_Description", rs.getString("Description"));
			//
			Ini.setProperty(Ini.P_UID, app_user);
			if (Ini.getPropertyBool(Ini.P_STORE_PWD))
				Ini.setProperty(Ini.P_PWD, app_pwd);

			do	//	read all roles
			{
				int AD_Role_ID = rs.getInt("AD_Role_ID");
				String Name = rs.getString("Name");
				KeyNamePair p = new KeyNamePair(AD_Role_ID, Name);
				list.add(p);
			}
			while (rs.next());

			rs.close();
			pstmt.close();
		}
		catch (SQLException ex)
		{
			Log.saveError("DBLogin", ex.getLocalizedMessage());
			return null;
		}

		//	Change via SQL detection comes here
		Env.setContext(ctx, "#User_SecurityID", "85263");
		//
		retValue = new KeyNamePair[list.size()];
		list.toArray(retValue);
		s_log.debug("# roles = " + retValue.length);
		return retValue;
	}	//	login

	/**
	 *  Load Clients.
	 *  <p>
	 *  Sets Role info in context and loads its clients
	 *
	 *  @param ctx context
	 *  @param  role    role information
	 *  @return list of valid client KeyNodePairs or null if in error
	 */
	public static KeyNamePair[] loadClients (Properties ctx, KeyNamePair role)
	{
		if (ctx == null || role == null)
			throw new IllegalArgumentException("DB.loadClients - required parameter missing");

		s_log.debug("loadClients - Role=" + role.toString());

		ArrayList list = new ArrayList();
		//	get Role details
		try
		{
			String sql = "SELECT DISTINCT r.UserLevel,r.ClientList,r.OrgList,"
				+ " r.C_Currency_ID,r.AmtApproval, oa.AD_Client_ID,c.Name "
				+ "FROM AD_Role r"
				+ " INNER JOIN AD_Role_OrgAccess oa ON (r.AD_Role_ID=oa.AD_Role_ID)"
				+ " INNER JOIN AD_Client c ON (oa.AD_Client_ID=c.AD_Client_ID) "
				+ "WHERE r.AD_Role_ID=?"		//	#1
				+ " AND r.IsActive='Y' AND c.IsActive='Y'";

			PreparedStatement pstmt = prepareStatement(sql);
			pstmt.setInt(1, role.getKey());
			ResultSet rs = pstmt.executeQuery();

			if (!rs.next())
			{
				rs.close();
				pstmt.close();
				s_log.error("loadClients - No Clients for Role=" + role.getKey());
				return null;
			}

			//  Role Info
			Env.setContext(ctx, "#AD_Role_ID", role.getKey());
			Env.setContext(ctx, "#AD_Role_Name", role.getName());
			Ini.setProperty(Ini.P_ROLE, role.getName());

			//	User Level
			Env.setContext(ctx, "#User_Level", rs.getString(1));  	//	Format 'SCO'
			//	ClientList
			Env.setContext(ctx, "#User_Client", rs.getString(2));	//	Format c1, c2, ...
			//	OrgList
			Env.setContext(ctx, "#User_Org", rs.getString(3)); 		//	Format o1, o2, ...
			//  Approval Currency / Amount
			Env.setContext(ctx, "#Approval_C_Currency_ID", rs.getInt(4));
			BigDecimal approval = rs.getBigDecimal(5);
			String approvalStr = "0";
			if (approval != null)
				approvalStr = approval.toString();
			Env.setContext(ctx, "#Approval_Amt", approvalStr);


			//  load Clients
			do
			{
				int AD_Client_ID = rs.getInt(6);
				String Name = rs.getString(7);
				KeyNamePair p = new KeyNamePair(AD_Client_ID, Name);
				list.add(p);
			}
			while (rs.next());

			rs.close();
			pstmt.close();
		}
		catch (SQLException ex)
		{
			s_log.error("loadClients", ex);
			return null;
		}
		//
		KeyNamePair[] retValue = new KeyNamePair[list.size()];
		list.toArray(retValue);
		s_log.debug("# clients = " + retValue.length);
		return retValue;
	}   //  loadClients

	/**
	 *  Load Organizations.
	 *  <p>
	 *  Sets Client info in context and loads its organization, the role has access to
	 *
	 *  @param ctx context
	 *  @param  client    client information
	 *  @return list of valid Org KeyNodePairs or null if in error
	 */
	public static KeyNamePair[] loadOrgs (Properties ctx, KeyNamePair client)
	{
		if (ctx == null || client == null)
			throw new IllegalArgumentException("DB.loadOrgs - required parameter missing");

		s_log.debug("loadOrgs - Client=" + client.toString());

		if (Env.getContext(ctx,"#AD_Role_ID").length() == 0)
			throw new UnsupportedOperationException("DB.loadPreferences - Missing Comtext #AD_Role_ID");

		ArrayList list = new ArrayList();
		int AD_Role_ID = Env.getContextAsInt(ctx,"#AD_Role_ID");
		s_log.debug("AD_Role_ID=" + AD_Role_ID);

		//	get Client details for role
		try
		{
			String sql = "SELECT c.Value,c.SMTPHost,c.IsMultiLingualDocument,c.AD_Language,"	//	1..4
				+ " o.AD_Org_ID,o.Name "				//	5..6
				+ "FROM AD_Client c"
				+ " INNER JOIN AD_Org o ON (o.AD_Client_ID=c.AD_Client_ID) "
				+ "WHERE o.AD_Client_ID=?"  			//  #1
				+ " AND o.IsSummary='N' AND o.IsActive='Y'"
				+ " AND o.AD_Org_ID IN "
				+   "(SELECT AD_Org_ID FROM AD_Role_OrgAccess WHERE AD_Role_ID=?)"; //  #2
			PreparedStatement pstmt = prepareStatement(sql);
			pstmt.setInt(1, client.getKey());
			pstmt.setInt(2, AD_Role_ID);
			ResultSet rs = pstmt.executeQuery();

			if (!rs.next())
			{
				rs.close();
				pstmt.close();
				s_log.error("loadOrgs - No Org for Client=" + client.getKey());
				return null;
			}

			//  Client Info
			Env.setContext(ctx, "#AD_Client_ID", client.getKey());
			Env.setContext(ctx, "#AD_Client_Name", client.getName());
			Ini.setProperty(Ini.P_CLIENT, client.getName());
			//
			Env.setContext(ctx, "#Client_Value", rs.getString(1));
			Env.setContext(ctx, "#Client_SMTP", rs.getString(2));
			Env.setContext(ctx, "#IsMultiLingualDocument", rs.getString(3));

			//  load Orgs
			do
			{
				int AD_Org_ID = rs.getInt(5);
				String Name = rs.getString(6);
				KeyNamePair p = new KeyNamePair(AD_Org_ID, Name);
				list.add(p);
			}
			while (rs.next());

			rs.close();
			pstmt.close();
		}
		catch (SQLException ex)
		{
			s_log.error("loadOrgs", ex);
			return null;
		}
		//
		KeyNamePair[] retValue = new KeyNamePair[list.size()];
		list.toArray(retValue);
		s_log.debug("# orgs = " + retValue.length);
		return retValue;
	}   //  loadOrgs

	/**
	 *	Load Preferences into Context for selected client.
	 *  <p>
	 *  Sets Org info in context and loads relevant field from
	 *	- AD_Client/Info,
	 *  - C_AcctSchema,
	 *  - C_AcctSchema_Elements
	 *	- AD_Preference
	 *  <p>
	 *  Assumes that the context is set for #AD_Client_ID, #AD_User_ID, #AD_Role_ID
	 *
	 *  @param ctx context
	 *  @param  org    org information
	 *  @param  warehouse   optional warehouse information
	 *  @param  timestamp   optional date
	 *  @param  printerName optional printer info
	 *  @returns AD_Message of error (NoValidAcctInfo) or ""
	 */
	public static String loadPreferences (Properties ctx,
		KeyNamePair org, KeyNamePair warehouse, Timestamp timestamp, String printerName)
	{
		s_log.info("loadPreferences - Org=" + org);

		if (ctx == null || org == null)
			throw new IllegalArgumentException("DB.loadPreferences - required parameter missing");
		if (Env.getContext(ctx,"#AD_Client_ID").length() == 0)
			throw new UnsupportedOperationException("DB.loadPreferences - Missing Comtext #AD_Client_ID");
		if (Env.getContext(ctx,"#AD_User_ID").length() == 0)
			throw new UnsupportedOperationException("DB.loadPreferences - Missing Comtext #AD_User_ID");
		if (Env.getContext(ctx,"#AD_Role_ID").length() == 0)
			throw new UnsupportedOperationException("DB.loadPreferences - Missing Comtext #AD_Role_ID");


		//  Org Info - assumes that it is valid
		Env.setContext(ctx, "#AD_Org_ID", org.getKey());
		Env.setContext(ctx, "#AD_Org_Name", org.getName());
		Ini.setProperty(Ini.P_ORG, org.getName());

		//  Warehouse Info
		if (warehouse != null)
		{
			Env.setContext(ctx, "#M_Warehouse_ID", warehouse.getKey());
			Ini.setProperty(Ini.P_WAREHOUSE, warehouse.getName());
		}

		//	Date (default today)
		long today = System.currentTimeMillis();
		if (timestamp != null)
			today = timestamp.getTime();
		java.sql.Date sd = new java.sql.Date(today);
		Env.setContext(ctx, "#Date", sd.toString());	//	YYYY-MM-DD

		//	Optional Printer
		if (printerName == null)
			printerName = "";
		Env.setContext(ctx, "#Printer", printerName);
		Ini.setProperty(Ini.P_PRINTER, printerName);

		//	Other
		Env.setAutoCommit(ctx, Ini.getProperty(Ini.P_A_COMMIT).equals("Y"));
		Env.setContext(ctx, "#CompiereSys", Ini.getProperty(Ini.P_COMPIERESYS));
		Env.setContext(ctx, "#ShowAcct", Ini.getProperty(Ini.P_SHOW_ACCT));
		Env.setContext(ctx, "#ShowTrl", Ini.getProperty(Ini.P_SHOW_TRL));

		String retValue = "";
		int AD_Client_ID = Env.getContextAsInt(ctx, "#AD_Client_ID");
		int AD_Org_ID =  org.getKey();
		int AD_User_ID =  Env.getContextAsInt(ctx, "#AD_User_ID");
		int AD_Role_ID =  Env.getContextAsInt(ctx, "#AD_Role_ID");

		//	Other Settings
		Env.setContext(ctx, "#YYYY", "Y");
		Env.setContext(ctx, "#StdPrecision", 2);

		//	AccountSchema Info (first)
		String sql = "SELECT * "
			+ "FROM C_AcctSchema a, AD_ClientInfo c "
			+ "WHERE a.C_AcctSchema_ID=c.C_AcctSchema1_ID "
			+ "AND c.AD_Client_ID=?";
		try
		{
			int C_AcctSchema_ID = 0;
			PreparedStatement pstmt = prepareStatement(sql);
			pstmt.setInt(1, AD_Client_ID);
			ResultSet rs = pstmt.executeQuery();

			if (!rs.next())
			{
				//  No Warning for System
				if (AD_Role_ID != 0)
					retValue = "NoValidAcctInfo";
			}
			else
			{
				//	Accounting Info
				C_AcctSchema_ID = rs.getInt("C_AcctSchema_ID");
				Env.setContext(ctx, "$C_AcctSchema_ID", C_AcctSchema_ID);
				Env.setContext(ctx, "$C_Currency_ID", rs.getInt("C_Currency_ID"));
				Env.setContext(ctx, "$HasAlias", rs.getString("HasAlias"));
			}
			rs.close();
			pstmt.close();

			//	Accounting Elements
			sql = "SELECT ElementType "
				+ "FROM C_AcctSchema_Element "
				+ "WHERE C_AcctSchema_ID=?"
				+ " AND IsActive='Y'";
			pstmt = prepareStatement(sql);