importreportline.java

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

		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(), get_TrxName());
		log.fine("Set PA_ReportLine_ID=" + no);

		commit();
		
		//	-------------------------------------------------------------------
		int noInsertLine = 0;
		int noUpdateLine = 0;

		//	****	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 = DB.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, get_TrxName());

			PreparedStatement pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				int PA_ReportLineSet_ID = rs.getInt(1);
				String Name = rs.getString(2);
				//
				try
				{
					int PA_ReportLine_ID = DB.getNextID(m_AD_Client_ID, "PA_ReportLine", get_TrxName());
					if (PA_ReportLine_ID <= 0)
						throw new DBException("No NextID (" + PA_ReportLine_ID + ")");
					pstmt_insertLine.setInt(1, PA_ReportLine_ID);
					pstmt_insertLine.setInt(2, PA_ReportLineSet_ID);
					pstmt_insertLine.setString(3, Name);
					//
					no = pstmt_insertLine.executeUpdate();
					log.finest("Insert ReportLine = " + no + ", PA_ReportLine_ID=" + PA_ReportLine_ID);
					noInsertLine++;
				}
				catch (Exception ex)
				{
					log.finest(ex.toString());
					continue;
				}
			}
			rs.close();
			pstmt.close();
			//
			pstmt_insertLine.close();
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "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(), get_TrxName());
		log.fine("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(), get_TrxName());
		log.config("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 = DB.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, get_TrxName());

			//	Update ReportSource
			PreparedStatement pstmt_updateSource = DB.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, get_TrxName());

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

			PreparedStatement pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			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
				{
					try
					{
						PA_ReportSource_ID = DB.getNextID(m_AD_Client_ID, "PA_ReportSource", get_TrxName());
						if (PA_ReportSource_ID <= 0)
							throw new DBException("No NextID (" + PA_ReportSource_ID + ")");
						pstmt_insertSource.setInt(1, PA_ReportSource_ID);
						pstmt_insertSource.setInt(2, I_ReportLine_ID);
						//
						no = pstmt_insertSource.executeUpdate();
						log.finest("Insert ReportSource = " + no + ", I_ReportLine_ID=" + I_ReportLine_ID + ", PA_ReportSource_ID=" + PA_ReportSource_ID);
						noInsertSource++;
					}
					catch (Exception ex)
					{
						log.finest("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(), get_TrxName());
						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.finest("Update ReportSource = " + no + ", I_ReportLine_ID=" + I_ReportLine_ID + ", PA_ReportSource_ID=" + PA_ReportSource_ID);
						noUpdateSource++;
					}
					catch (SQLException ex)
					{
						log.finest( "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(), get_TrxName());
						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.log(Level.SEVERE, "Set Imported=" + no);
				//
				commit();
			}
			rs.close();
			pstmt.close();
			//
			pstmt_insertSource.close();
			pstmt_updateSource.close();
			pstmt_setImported.close();
			//
		}
		catch (SQLException e)
		{
		}

		//	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(), get_TrxName());
		addLog (0, null, new BigDecimal (no), "@Errors@");
		addLog (0, null, new BigDecimal (noInsertLine), "@PA_ReportLine_ID@: @Inserted@");
		addLog (0, null, new BigDecimal (noUpdateLine), "@PA_ReportLine_ID@: @Updated@");
		addLog (0, null, new BigDecimal (noInsertSource), "@PA_ReportSource_ID@: @Inserted@");
		addLog (0, null, new BigDecimal (noUpdateSource), "@PA_ReportSource_ID@: @Updated@");

		return "";
	}	//	doIt

}	//	ImportReportLine