001package com.identityworksllc.iiq.common.task.export;
002
003import com.identityworksllc.iiq.common.Utilities;
004import org.apache.commons.logging.Log;
005import org.apache.commons.logging.LogFactory;
006import sailpoint.api.SailPointContext;
007import sailpoint.api.TaskManager;
008import sailpoint.object.Attributes;
009import sailpoint.object.JasperResult;
010import sailpoint.object.PersistedFile;
011import sailpoint.object.TaskDefinition;
012import sailpoint.object.TaskResult;
013import sailpoint.object.TaskSchedule;
014import sailpoint.persistence.PersistedFileInputStream;
015import sailpoint.task.AbstractTaskExecutor;
016import sailpoint.task.TaskMonitor;
017import sailpoint.tools.GeneralException;
018import sailpoint.tools.JdbcUtil;
019import sailpoint.tools.Message;
020import sailpoint.tools.RFC4180LineIterator;
021import sailpoint.tools.RFC4180LineParser;
022import sailpoint.tools.Util;
023
024import java.io.BufferedReader;
025import java.io.IOException;
026import java.io.InputStreamReader;
027import java.sql.Connection;
028import java.sql.PreparedStatement;
029import java.sql.SQLException;
030import java.sql.Timestamp;
031import java.util.ArrayList;
032import java.util.HashMap;
033import java.util.List;
034import java.util.Map;
035import java.util.Optional;
036import java.util.concurrent.atomic.AtomicBoolean;
037
038public class ReportExporter extends AbstractTaskExecutor {
039    /**
040     * Opens the connection to the target database using the provided connection info
041     * @param context The sailpoint context, used to decrypt the password
042     * @param connectionInfo The provided connection info, extracted from the export task def
043     * @return The open connection
044     * @throws GeneralException if any failures occur
045     */
046    public static Connection openConnection(SailPointContext context, ExportConnectionInfo connectionInfo) throws GeneralException {
047        String decryptedPassword = context.decrypt(connectionInfo.getEncryptedPassword());
048        return JdbcUtil.getConnection(connectionInfo.getDriver(), null, connectionInfo.getUrl(), connectionInfo.getUsername(), decryptedPassword, connectionInfo.getOptions());
049    }
050
051
052    private final Log log;
053    private final AtomicBoolean terminated;
054
055    public ReportExporter() {
056        this.terminated = new AtomicBoolean();
057        this.log = LogFactory.getLog(ReportExporter.class);
058    }
059
060    @Override
061    public void execute(SailPointContext context, TaskSchedule taskSchedule, TaskResult taskResult, Attributes<String, Object> attributes) throws Exception {
062        List<String> reportTaskDefNames = attributes.getStringList("reports");
063        String driver = attributes.getString("driver");
064        String url = attributes.getString("url");
065        String username = attributes.getString("username");
066        String password = attributes.getString("password");
067
068        ExportConnectionInfo connectionInfo = new ExportConnectionInfo(url, username, password);
069        connectionInfo.setDriver(driver);
070
071        TaskMonitor monitor = new TaskMonitor(context, taskResult);
072
073        Timestamp taskTimestamp = new Timestamp(System.currentTimeMillis());
074
075        for(String reportName : reportTaskDefNames) {
076            TaskDefinition report = context.getObjectByName(TaskDefinition.class, reportName);
077            if (report == null) {
078                taskResult.addMessage(Message.warn("Unable to find report called: {0}", reportName));
079                context.saveObject(taskResult);
080                context.commitTransaction();
081                continue;
082            }
083
084            monitor.forceProgress("Executing: " + reportName);
085
086            TaskManager taskManager = new TaskManager(context);
087            TaskResult reportOutput = taskManager.runSync(report, new HashMap<>());
088
089            boolean reportExported = false;
090
091            if (reportOutput != null) {
092                JasperResult jasperResult = reportOutput.getReport();
093
094                if (jasperResult != null) {
095                    List<PersistedFile> fileList = jasperResult.getFiles();
096                    if (fileList != null) {
097                        Optional<PersistedFile> csvFileMaybe = Utilities.safeStream(fileList).filter(PersistedFile::isCsv).findFirst();
098                        if (csvFileMaybe.isPresent()) {
099                            PersistedFile csvFile = csvFileMaybe.get();
100                            writeCsvContents(context, connectionInfo, taskTimestamp, report, csvFile);
101                        } else {
102                            log.warn("Report output did not contain a CSV file");
103                        }
104                    } else {
105                        log.warn("Report output did not contain a list of files; do you need to check CSV on the list?");
106                    }
107                } else {
108                    log.warn("TaskResult did not contain a report object");
109                }
110            } else {
111                log.warn("TaskResult did not contain a report object for task: " + reportName);
112            }
113        }
114    }
115
116    /**
117     * Writes the CSV contents from a report into the report export table
118     * @param context The IIQ context
119     * @param connectionInfo The connection info
120     * @param taskTimestamp The task timestamp (set at start)
121     * @param report The report taskdef
122     * @param csvFile The CSV file report output
123     * @throws SQLException if any DB failures occur
124     * @throws IOException if any file read failures occur
125     * @throws GeneralException if any IIQ failures occur
126     */
127    private void writeCsvContents(SailPointContext context, ExportConnectionInfo connectionInfo, Timestamp taskTimestamp, TaskDefinition report, PersistedFile csvFile) throws SQLException, IOException, GeneralException {
128        try (Connection connection = openConnection(context, connectionInfo); PreparedStatement rowInsert = connection.prepareStatement("insert into de_report_data ( report_name, row_index, attribute, value, insert_date ) values (?, ?, ?, ?, ?)")) {
129            int batchCount = 0;
130            try (BufferedReader reader = new BufferedReader(new InputStreamReader(new PersistedFileInputStream(context, csvFile)))) {
131                RFC4180LineIterator lineIterator = new RFC4180LineIterator(reader);
132                RFC4180LineParser parser = new RFC4180LineParser(',');
133                try {
134                    String header = lineIterator.readLine();
135                    if (header == null) {
136                        // File is empty
137                        log.info("File " + csvFile.getName() + " is empty");
138                    } else {
139                        List<String> headerElements = new ArrayList<>(parser.parseLine(header));
140                        String line;
141                        int rowIndex = 0;
142
143                        while ((line = lineIterator.readLine()) != null) {
144                            Map<String, String> row = new HashMap<>();
145                            List<String> csvElements = parser.parseLine(line);
146                            for (int i = 0; i < csvElements.size() && i < headerElements.size(); i++) {
147                                String col = headerElements.get(i);
148                                String val = csvElements.get(i);
149                                if (Util.isNotNullOrEmpty(val)) {
150                                    row.put(col, val);
151                                }
152                            }
153
154                            if (!row.isEmpty()) {
155                                exportRow(rowInsert, report, rowIndex, row, taskTimestamp);
156                                batchCount++;
157                            }
158
159                            rowIndex++;
160
161                            if (batchCount > 12) {
162                                rowInsert.executeBatch();
163                                batchCount = 0;
164                            }
165
166                        }
167
168                        rowInsert.executeBatch();
169                    }
170                } finally {
171                    lineIterator.close();
172                }
173            }
174        }
175    }
176
177    private void exportRow(PreparedStatement rowInsert, TaskDefinition report, int rowIndex, Map<String, String> row, Timestamp taskTimestamp) throws SQLException {
178        rowInsert.setString(1, report.getName());
179        rowInsert.setInt(2, rowIndex);
180        rowInsert.setTimestamp(5, taskTimestamp);
181
182        for(String key : row.keySet()) {
183            String val = row.get(key);
184            rowInsert.setString(3, key);
185            rowInsert.setString(4, val);
186
187            rowInsert.addBatch();
188        }
189    }
190
191    @Override
192    public boolean terminate() {
193        this.terminated.set(true);
194        return true;
195    }
196}