importbpartner.java

Java写的ERP系统

		//	PreparedStatement pstmt_updateLocation = conn.prepareStatement
		//		("");

			//	Insert BP Location
			PreparedStatement pstmt_insertBPLocation = conn.prepareStatement
				("INSERT INTO C_BPartner_Location (C_BPartner_Location_ID,"
				+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
				+ "Name,IsBillTo,IsShipTo,IsPayFrom,IsRemitTo,"
				+ "Phone,Phone2,Fax, C_BPartner_ID,C_Location_ID) "
				+ "SELECT ?,"
				+ "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
				+ "City,'Y','Y','Y','Y',"
				+ "Phone,Phone2,Fax, ?,? "
				+ "FROM I_BPartner "
				+ "WHERE I_BPartner_ID=?");

		//	PreparedStatement pstmt_updateBPLocation = conn.prepareStatement
		//		("");

			//	Insert Contact
			PreparedStatement pstmt_insertBPContact = conn.prepareStatement
				("INSERT INTO C_BPartner_Contact (C_BPartner_Contact_ID,"
				+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
				+ "C_BPartner_ID,C_BPartner_Location_ID,C_Greeting_ID,"
				+ "Name,Title,Description,Comments,Phone,Phone2,Fax,EMail,Birthday) "
				+ "SELECT ?,"
				+ "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
				+ "?,?,C_Greeting_ID,"
				+ "ContactName,Title,ContactDescription,Comments,Phone,Phone2,Fax,EMail,Birthday "
				+ "FROM I_BPartner "
				+ "WHERE I_BPartner_ID=?");

			//	Update Contact
			PreparedStatement pstmt_updateBPContact = conn.prepareStatement
				("UPDATE C_BPartner_Contact "
				+ "SET (C_Greeting_ID,"
				+ "Name,Title,Description,Comments,Phone,Phone2,Fax,EMail,Birthday,Updated,UpdatedBy)="
				+ "(SELECT C_Greeting_ID,"
				+ "ContactName,Title,ContactDescription,Comments,Phone,Phone2,Fax,EMail,Birthday,SysDate,UpdatedBy"
				+ " FROM I_BPartner WHERE I_BPartner_ID=?) "
				+ "WHERE C_BPartner_Contact_ID=?");

			//	Set Imported = Y
			PreparedStatement pstmt_setImported = conn.prepareStatement
				("UPDATE I_BPartner SET I_IsImported='Y',"
				+ " C_BPartner_ID=?, C_BPartner_Location_ID=?, C_BPartner_Contact_ID=?, "
				+ " Updated=SysDate, Processed='Y' WHERE I_BPartner_ID=?");
			//
			PreparedStatement pstmt = DB.prepareStatement(sql.toString());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				int I_BPartner_ID = rs.getInt(1);
				int C_BPartner_ID = rs.getInt(2);
				boolean newBPartner = C_BPartner_ID == 0;
				int C_BPartner_Location_ID = rs.getInt(3);
				boolean newLocation = rs.getString(4) != null;
				int C_BPartner_Contact_ID = rs.getInt(5);
				boolean newContact =  rs.getString(6) != null;
				Log.trace(Log.l6_Database, "I_BPartner_ID=" + I_BPartner_ID
					+ ", C_BPartner_ID=" + C_BPartner_ID
					+ ", C_BPartner_Location_ID=" + C_BPartner_Location_ID + " create=" + newLocation
					+ ", C_BPartner_Contact_ID=" + C_BPartner_Contact_ID + " create=" + newContact);


				//	****	Create/Update BPartner
				if (newBPartner)	//	Insert new BPartner
				{
					C_BPartner_ID = DB.getKeyNextNo(m_AD_Client_ID, "N", "C_BPartner");
					pstmt_insertBPartner.setInt(1, C_BPartner_ID);
					pstmt_insertBPartner.setInt(2, I_BPartner_ID);
					try
					{
						no = pstmt_insertBPartner.executeUpdate();
						Log.trace(10, "Insert BPartner = " + no);
						noInsert++;
					}
					catch (SQLException ex)
					{
						Log.trace(10, "Insert BPartner - " + ex.toString());
						sql = new StringBuffer ("UPDATE I_BPartner i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert BPartner: " + ex.toString()))
							.append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
						DB.executeUpdate(sql.toString());
						continue;
					}
				}
				else				//	Update existing BPartner
				{
					pstmt_updateBPartner.setInt(1, I_BPartner_ID);
					pstmt_updateBPartner.setInt(2, C_BPartner_ID);
					try
					{
						no = pstmt_updateBPartner.executeUpdate();
						Log.trace(10, "Update BPartner = " + no);
						noUpdate++;
					}
					catch (SQLException ex)
					{
						Log.trace(10, "Update BPartner - " + ex.toString());
						sql = new StringBuffer ("UPDATE I_BPartner i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update BPartner: " + ex.toString()))
							.append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
						DB.executeUpdate(sql.toString());
						continue;
					}
				}

				//	****	Create/Update BPartner Location
				if (C_BPartner_Location_ID != 0)		//	Update Location
				{
				}
				else if (newLocation)					//	New Location
				{
					int C_Location_ID = DB.getKeyNextNo(m_AD_Client_ID, "N", "C_Location");
					pstmt_insertLocation.setInt(1, C_Location_ID);
					pstmt_insertLocation.setInt(2, I_BPartner_ID);
					try
					{
						no = pstmt_insertLocation.executeUpdate();
						Log.trace(10, "Insert Location = " + no);
					}
					catch (SQLException ex)
					{
						Log.trace(10, "Insert Location - " + ex.toString());
						conn.rollback();
						noInsert--;
						sql = new StringBuffer ("UPDATE I_BPartner i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert Location: " + ex.toString()))
							.append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
						DB.executeUpdate(sql.toString());
						continue;
					}
					//
					C_BPartner_Location_ID = DB.getKeyNextNo(m_AD_Client_ID, "N", "C_BPartner_Location");
					pstmt_insertBPLocation.setInt(1, C_BPartner_Location_ID);
					pstmt_insertBPLocation.setInt(2, C_BPartner_ID);
					pstmt_insertBPLocation.setInt(3, C_Location_ID);
					pstmt_insertBPLocation.setInt(4, I_BPartner_ID);
					try
					{
						no = pstmt_insertBPLocation.executeUpdate();
						Log.trace(10, "Insert BP Location = " + no);
					}
					catch (SQLException ex)
					{
						Log.trace(10, "Insert BPLocation - " + ex.toString());
						conn.rollback();
						noInsert--;
						sql = new StringBuffer ("UPDATE I_BPartner i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert BPLocation: " + ex.toString()))
							.append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
						DB.executeUpdate(sql.toString());
						continue;
					}
				}

				//	****	Create/Update Contact
				if (C_BPartner_Contact_ID != 0)
				{
					pstmt_updateBPContact.setInt(1, I_BPartner_ID);
					pstmt_updateBPContact.setInt(2, C_BPartner_Contact_ID);
					try
					{
						no = pstmt_updateBPContact.executeUpdate();
						Log.trace(10, "Update BP Contact = " + no);
					}
					catch (SQLException ex)
					{
						Log.trace(10, "Update BP Contact - " + ex.toString());
						conn.rollback();
						noInsert--;
						sql = new StringBuffer ("UPDATE I_BPartner i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update BP Contact: " + ex.toString()))
							.append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
						DB.executeUpdate(sql.toString());
						continue;
					}
				}
				else if (newContact)					//	New Contact
				{
					C_BPartner_Contact_ID = DB.getKeyNextNo(m_AD_Client_ID, "N", "C_BPartner_Contact");
					pstmt_insertBPContact.setInt(1, C_BPartner_Contact_ID);
					pstmt_insertBPContact.setInt(2, C_BPartner_ID);
					if (C_BPartner_Location_ID == 0)
						pstmt_insertBPContact.setNull(3, Types.NUMERIC);
					else
						pstmt_insertBPContact.setInt(3, C_BPartner_Location_ID);
					pstmt_insertBPContact.setInt(4, I_BPartner_ID);
					try
					{
						no = pstmt_insertBPContact.executeUpdate();
						Log.trace(10, "Insert BP Contact = " + no);
					}
					catch (SQLException ex)
					{
						Log.trace(10, "Insert BPContact - " + ex.toString());
						conn.rollback();
						noInsert--;
						sql = new StringBuffer ("UPDATE I_BPartner i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert BPContact: " + ex.toString()))
							.append("WHERE I_BPartner_ID=").append(I_BPartner_ID);
						DB.executeUpdate(sql.toString());
						continue;
					}
				}

				//	Update I_Product
				pstmt_setImported.setInt(1, C_BPartner_ID);
				if (C_BPartner_Location_ID == 0)
					pstmt_setImported.setNull(2, Types.NUMERIC);
				else
					pstmt_setImported.setInt(2, C_BPartner_Location_ID);
				if (C_BPartner_Contact_ID == 0)
					pstmt_setImported.setNull(3, Types.NUMERIC);
				else
					pstmt_setImported.setInt(3, C_BPartner_Contact_ID);
				pstmt_setImported.setInt(4, I_BPartner_ID);
				no = pstmt_setImported.executeUpdate();
				//
				conn.commit();
			}	//	for all I_Product
			rs.close();
			pstmt.close();
			//
			pstmt_insertBPartner.close();
			pstmt_updateBPartner.close();
			pstmt_insertLocation.close();
		//	pstmt_updateLocation.close();
			pstmt_insertBPLocation.close();
		//	pstmt_updateBPLocation.close();
			pstmt_insertBPContact.close();
			pstmt_updateBPContact.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 ("ImportBPartner.doIt", e);
		}
		finally
		{
			if (conn != null)
				conn.close();
			conn = null;
		}

		//	Set Error to indicator to not imported
		sql = new StringBuffer ("UPDATE I_BPartner "
			+ "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), "@C_BPartner_ID@: @Inserted@");
		addLog (null, 0, new BigDecimal (noUpdate), "@C_BPartner_ID@: @Updated@");
		return "";
	}	//	doIt

}	//	ImportBPartner