importreportline.java

Java写的ERP系统

		sql = new StringBuffer ("UPDATE I_ReportLine i "
			+ "SET PA_ReportLine_ID=(SELECT PA_ReportLine_ID FROM PA_ReportLine r"
			+ " WHERE i.Name=r.Name AND i.PA_ReportLineSet_ID=r.PA_ReportLineSet_ID AND ROWNUM=1) "
			+ "WHERE PA_ReportLine_ID IS NULL AND PA_ReportLineSet_ID IS NOT NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportReportLine.doIt", "Set PA_ReportLine_ID=" + no);

		//	-------------------------------------------------------------------
		int noInsertLine = 0;
		int noUpdateLine = 0;
		Connection conn = DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED);

		//	****	Create Missing ReportLines
		sql = new StringBuffer ("SELECT DISTINCT PA_ReportLineSet_ID, Name "
			+ "FROM I_ReportLine "
			+ "WHERE I_IsImported='N' AND PA_ReportLine_ID IS NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		try
		{
			//	Insert ReportLine
			PreparedStatement pstmt_insertLine = conn.prepareStatement
				("INSERT INTO PA_ReportLine "
				+ "(PA_ReportLine_ID,PA_ReportLineSet_ID,"
				+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
				+ "Name,SeqNo,IsPrinted,IsSummary,LineType)"
				+ "SELECT ?,PA_ReportLineSet_ID,"
				+ "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
				+ "Name,SeqNo,IsPrinted,IsSummary,LineType "
				+ "FROM I_ReportLine "
				+ "WHERE PA_ReportLineSet_ID=? AND Name=? AND ROWNUM=1"		//	#2..3
				+ clientCheck);

			PreparedStatement pstmt = DB.prepareStatement(sql.toString());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				int PA_ReportLineSet_ID = rs.getInt(1);
				String Name = rs.getString(2);
				//
				int PA_ReportLine_ID = DB.getKeyNextNo(m_AD_Client_ID, "N", "PA_ReportLine");
				pstmt_insertLine.setInt(1, PA_ReportLine_ID);
				pstmt_insertLine.setInt(2, PA_ReportLineSet_ID);
				pstmt_insertLine.setString(3, Name);
				try
				{
					no = pstmt_insertLine.executeUpdate();
					Log.trace(10, "Insert ReportLine = " + no + ", PA_ReportLine_ID=" + PA_ReportLine_ID);
					noInsertLine++;
				}
				catch (SQLException ex)
				{
					Log.trace(10, "Insert ReportLine - " + ex.toString());
					continue;
				}
			}
			rs.close();
			pstmt.close();
			//
			pstmt_insertLine.close();
		}
		catch (SQLException e)
		{
			Log.error("ImportReportLine - Create ReportLine", e);
		}

		//	Set PA_ReportLine_ID (for newly created)
		sql = new StringBuffer ("UPDATE I_ReportLine i "
			+ "SET PA_ReportLine_ID=(SELECT PA_ReportLine_ID FROM PA_ReportLine r"
			+ " WHERE i.Name=r.Name AND i.PA_ReportLineSet_ID=r.PA_ReportLineSet_ID AND ROWNUM=1) "
			+ "WHERE PA_ReportLine_ID IS NULL AND PA_ReportLineSet_ID IS NOT NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		no = DB.executeUpdate(sql.toString());
		Log.trace(Log.l5_DData, "ImportReportLine.doIt", "Set PA_ReportLine_ID=" + no);

		//	****	Update ReportLine
		sql = new StringBuffer ("UPDATE PA_ReportLine r "
			+ "SET (Description,SeqNo,IsSummary,IsPrinted,LineType,CalculationType,AmountType,PostingType,Updated,UpdatedBy)="
			+ " (SELECT Description,SeqNo,IsSummary,IsPrinted,LineType,CalculationType,AmountType,PostingType,SysDate,UpdatedBy"
			+ " FROM I_ReportLine i WHERE r.Name=i.Name AND r.PA_ReportLineSet_ID=i.PA_ReportLineSet_ID"
			+ " AND i.I_ReportLine_ID=(SELECT MIN(I_ReportLine_ID) FROM I_ReportLine iii"
			+ " WHERE i.Name=iii.Name AND i.PA_ReportLineSet_ID=iii.PA_ReportLineSet_ID)) "
			+ "WHERE EXISTS (SELECT *"
			+ " FROM I_ReportLine i WHERE r.Name=i.Name AND r.PA_ReportLineSet_ID=i.PA_ReportLineSet_ID"
			+ " AND i.I_ReportLine_ID=(SELECT MIN(I_ReportLine_ID) FROM I_ReportLine iii"
			+ " WHERE i.Name=iii.Name AND i.PA_ReportLineSet_ID=iii.PA_ReportLineSet_ID AND i.I_IsImported='N'))")
			.append(clientCheck);
		noUpdateLine = DB.executeUpdate(sql.toString());
		Log.trace(Log.l3_Util, "ImportReportLine.doIt", "Update PA_ReportLine=" + noUpdateLine);


		//	-------------------------------------------------------------------
		int noInsertSource = 0;
		int noUpdateSource = 0;

		//	****	Create ReportSource
		sql = new StringBuffer ("SELECT I_ReportLine_ID, PA_ReportSource_ID "
			+ "FROM I_ReportLine "
			+ "WHERE PA_ReportLine_ID IS NOT NULL"
			+ " AND I_IsImported='N'").append(clientCheck);
		try
		{
			//	Insert ReportSource
			PreparedStatement pstmt_insertSource = conn.prepareStatement
				("INSERT INTO PA_ReportSource "
				+ "(PA_ReportSource_ID,"
				+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
				+ "PA_ReportLine_ID,ElementType,C_ElementValue_ID) "
				+ "SELECT ?,"
				+ "AD_Client_ID,AD_Org_ID,'Y',SysDate,CreatedBy,SysDate,UpdatedBy,"
				+ "PA_ReportLine_ID,'AC',C_ElementValue_ID "
				+ "FROM I_ReportLine "
				+ "WHERE I_ReportLine_ID=?"
				+ " AND I_IsImported='N'"
				+ clientCheck);

			//	Update ReportSource
			PreparedStatement pstmt_updateSource = conn.prepareStatement
				("UPDATE PA_ReportSource "
				+ "SET (ElementType,C_ElementValue_ID,Updated,UpdatedBy)="
				+ " (SELECT 'AC',C_ElementValue_ID,SysDate,UpdatedBy"
				+ " FROM I_ReportLine"
				+ " WHERE I_ReportLine_ID=?) "
				+ "WHERE PA_ReportSource_ID=?"
				+ clientCheck);

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

			PreparedStatement pstmt = DB.prepareStatement(sql.toString());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				int I_ReportLine_ID = rs.getInt(1);
				int PA_ReportSource_ID = rs.getInt(2);
				//
				if (PA_ReportSource_ID == 0)			//	New ReportSource
				{
					PA_ReportSource_ID = DB.getKeyNextNo(m_AD_Client_ID, "N", "PA_ReportSource");
					pstmt_insertSource.setInt(1, PA_ReportSource_ID);
					pstmt_insertSource.setInt(2, I_ReportLine_ID);
					try
					{
						no = pstmt_insertSource.executeUpdate();
						Log.trace(10, "Insert ReportSource = " + no + ", I_ReportLine_ID=" + I_ReportLine_ID + ", PA_ReportSource_ID=" + PA_ReportSource_ID);
						noInsertSource++;
					}
					catch (SQLException ex)
					{
						Log.trace(10, "Insert ReportSource - " + ex.toString());
						sql = new StringBuffer ("UPDATE I_ReportLine i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Insert ElementSource: " + ex.toString()))
							.append("WHERE I_ReportLine_ID=").append(I_ReportLine_ID);
						DB.executeUpdate(sql.toString());
						continue;
					}
				}
				else								//	update Report Source
				{
					pstmt_updateSource.setInt(1, I_ReportLine_ID);
					pstmt_updateSource.setInt(2, PA_ReportSource_ID);
					try
					{
						no = pstmt_updateSource.executeUpdate();
						Log.trace(10, "Update ReportSource = " + no + ", I_ReportLine_ID=" + I_ReportLine_ID + ", PA_ReportSource_ID=" + PA_ReportSource_ID);
						noUpdateSource++;
					}
					catch (SQLException ex)
					{
						Log.trace(10, "Update ReportSource - " + ex.toString());
						sql = new StringBuffer ("UPDATE I_ReportLine i "
							+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append(DB.TO_STRING("Update ElementSource: " + ex.toString()))
							.append("WHERE I_ReportLine_ID=").append(I_ReportLine_ID);
						DB.executeUpdate(sql.toString());
						continue;
					}
				}	//	update source

				//	Set Imported to Y
				pstmt_setImported.setInt(1, PA_ReportSource_ID);
				pstmt_setImported.setInt(2, I_ReportLine_ID);
				no = pstmt_setImported.executeUpdate();
				if (no != 1)
					Log.error("ImportReportLine.doIt - Set Imported=" + no);
			}
			rs.close();
			pstmt.close();
			//
			pstmt_insertSource.close();
			pstmt_updateSource.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 ("ImportReportLine.doIt", e);
		}
		finally
		{
			if (conn != null)
				conn.close();
			conn = null;
		}

		//	Set Error to indicator to not imported
		sql = new StringBuffer ("UPDATE I_ReportLine "
			+ "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 (noInsertLine), "@PA_ReportLine_ID@: @Inserted@");
		addLog (null, 0, new BigDecimal (noUpdateLine), "@PA_ReportLine_ID@: @Updated@");
		addLog (null, 0, new BigDecimal (noInsertSource), "@PA_ReportSource_ID@: @Inserted@");
		addLog (null, 0, new BigDecimal (noUpdateSource), "@PA_ReportSource_ID@: @Updated@");

		return "";
	}	//	doIt

}	//	ImportReportLine