This code snippet will use to analyzing the documents storage in each business unit for everymonth and send the report to the business unit manager. This report will helps to unit manager to documentum usage for each business unit.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.StringTokenizer;
import javax.activation.DataHandler;
import javax.activation.FileDataSource;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.IndexedColors;
import com.documentum.com.DfClientX;
import com.documentum.fc.client.DfQuery;
import com.documentum.fc.client.IDfClient;
import com.documentum.fc.client.IDfCollection;
import com.documentum.fc.client.IDfDocument;
import com.documentum.fc.client.IDfQuery;
import com.documentum.fc.client.IDfSession;
import com.documentum.fc.client.IDfSessionManager;
import com.documentum.fc.common.DfException;
import com.documentum.fc.common.DfId;
import com.documentum.fc.common.IDfLoginInfo;
public class DCTMUsageReport {
/**
* @param args
* @throws IOException
*/
public static FileInputStream fis = null;
public static FileOutputStream fos = null;
public static String mailRecepient =null;
public static String host =null;
public static String fromId =null;
public static String userName =null;
public static String password =null;
public static String docbase =null;
public static String filePath =null;
public static String attachmentName =null;
public static String supportTeamID =null;
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
//Read the values from properties file
String PROP_FILE = "DCTMUsageReport.properties";
InputStream is = DCTMUsageReport.class.getResourceAsStream(PROP_FILE);
Properties prop = new Properties();
prop.load(is);
//Doc Base connection values
userName = prop.getProperty("SUPER_USER_ID");
password = prop.getProperty("SUPER_USER_PASS");
docbase = prop.getProperty("DOCBASE_NAME");
//Input data file
String dataFile =prop.getProperty("INPUT_DATA_FILE");
System.out.println("dataFile:"+dataFile);
//File Location
filePath =prop.getProperty("DATA_FILE_LOCATION");
System.out.println("filePath:"+filePath);
fromId =prop.getProperty("CUSTOM_MAIL_FROM_ID");
System.out.println("fromId:"+fromId);
supportTeamID =prop.getProperty("SUPPORT_TEAM_EMAIL_ID");
System.out.println("fromId:"+supportTeamID);
//List to store the values read from input data file
List sheetData = new ArrayList();
try {
//Read the Business Unit,report generation query,sheet name recipient list and sort order from excel file
fis = new FileInputStream(dataFile);
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();
List data = new ArrayList();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
data.add(cell);
}
sheetData.add(data);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis != null) {
fis.close();
}
}
//call the getInputData() method to generate report based on BU
getInputData(sheetData);
}
/**
* Method to generate the NA Activity Report for same Business Unit in the single workbook
* @param List sheetData
*
*/
private static void getInputData(List sheetData) throws Exception {
//Call the creationDate() method to get the report generation period
String strCreationDate=creationDate();
String strTempBU ="";
String wbName="";
String strBUnit="";
String strsortOrder="";
String strRQuery="";
String strRecipient="";
String strsheetName ="";
ArrayList mailBU = new ArrayList();
ArrayList mailReceipient = new ArrayList();
DCTMUsageReport dctmUsageReport = new DCTMUsageReport();
for (int i = 1; i < sheetData.size(); i++) {
List list = (List) sheetData.get(i);
//Retrieve the values from the Array list
HSSFCell bUnit = (HSSFCell) list.get(0);
HSSFCell reportQuery = (HSSFCell) list.get(1);
HSSFCell recipient = (HSSFCell) list.get(2);
HSSFCell sheetName = (HSSFCell) list.get(3);
HSSFCell sortOrder = (HSSFCell) list.get(4);
strBUnit = bUnit.getRichStringCellValue().getString();
strsortOrder = sortOrder.getRichStringCellValue().getString();
strRQuery = reportQuery.getRichStringCellValue().getString()+ strCreationDate +strsortOrder ;
strRecipient = recipient.getRichStringCellValue().getString();
strsheetName = sheetName.getRichStringCellValue().getString();
System.out.println("BusinessUnit:"+strBUnit);
System.out.println("ReportQuery:"+strRQuery);
//Check for recipient email id is not null,
//if it is null then send the report to support team email id mention in the properties file
if(strRecipient.equalsIgnoreCase("")){
strRecipient=supportTeamID;
}
//Check for previous report business unit - If business unit is same for the previous report,
//the new report will be added as a new sheet in the previous report, else the report will be created as a new workbook
if(!strTempBU.equalsIgnoreCase(strBUnit)){
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet firstSheet = workbook.createSheet(strsheetName);
wbName = strBUnit+" Usage Report.xls"; //Naming the report based on the business unit
//call for generating the report
dctmUsageReport.generateReport(strRQuery, workbook,firstSheet,wbName);
//Assign the Business Unit(BU) to temporary variable for creating multiple sheets in the same workbook for single BU
mailBU.add(strBUnit);
mailReceipient.add(strRecipient);
strTempBU =strBUnit;
}
else{// If BU is same for multiple doc types, create a new sheet in the existing workbook
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath+wbName)); //Read the existing file, that is created for same BU
// create a workbook out of the input stream
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet nextSheet = wb.createSheet(strsheetName);
//call for generating the report
dctmUsageReport.generateReport(strRQuery, wb,nextSheet,wbName);
}
}
//Call the sendMailAttachment() method to send the generated usage report
for (int j=0;j< mailBU.size(); j++){
String bussUnit =mailBU.get(j).toString();//To get the business unit
String receipientUnit =mailReceipient.get(j).toString();//To get the recipients list
String attachmentName = filePath+bussUnit+" Usage Report.xls";//To form the attachment file name based on the BU with location
dctmUsageReport.sendMailAttachment(attachmentName,receipientUnit,bussUnit);
}
}
/**
* Method to get the report generation period based on the current Date
* Usually the period will be completed period of last month from current date
* @return String
*
*/
public static String creationDate(){
//Set the date format
DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
Calendar cal = Calendar.getInstance();
//Get the first date of current month
cal.set(Calendar.DAY_OF_MONTH, cal.getActualMinimum(Calendar.DAY_OF_MONTH));
String endDate = (dateFormat.format(cal.getTime()));
//Get the first date for last month
cal.add(Calendar.MONTH,-1);
cal.set(Calendar.DAY_OF_MONTH, cal.getActualMinimum(Calendar.DAY_OF_MONTH));
String startDate = (dateFormat.format(cal.getTime()));
//Get the report generation period for last month
String creationDate =" and r_creation_date between date('"+ startDate+"') and date('" + endDate +"')";
//String creationDate =" and s.r_creation_date between date('03/01/2011') and date('04/01/2011') ";
return creationDate;
}
/**
* Method to generate the NA Activity Report
* @param query
* @param workbook
* @param sheet
*/
public void generateReport( String execQuery, HSSFWorkbook workbook, HSSFSheet sheet,String reportName)throws Exception{
DfClientX clientx = new DfClientX();
IDfClient client = clientx.getLocalClient();
IDfLoginInfo login = clientx.getLoginInfo();
login.setUser(userName);
login.setPassword(password);
login.setDomain("");
IDfSessionManager sMgr = client.newSessionManager();
IDfSession session = null;
String a_name ="";
String a_value ="";
ArrayList headers = new ArrayList();
try {
//Set the date format
DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
Calendar cal = Calendar.getInstance();
//Get the first date of current month
cal.add(Calendar.MONTH,-1);
cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH));
//Get the last day of previous month to mention in the report
String lastDate = (dateFormat.format(cal.getTime()));
sMgr.setIdentity(docbase,login);
session = sMgr.getSession(docbase);
IDfQuery query2 = new DfQuery();
query2.setDQL(execQuery);
//Execute the query
IDfCollection collection2 = query2.execute(session, IDfQuery.DF_EXEC_QUERY);
//Get the total attribute count in the query
int cnt = collection2.getAttrCount();
//Header for Activity report and Date & Time
HSSFRow hssfHeader1 = sheet.createRow(0);
//Create the style for cell
HSSFCellStyle cellStyle1 = workbook.createCellStyle();
//Set the alignment
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//Set the border
cellStyle1.setAlignment(HSSFCellStyle.BORDER_THICK);
//Merge the Caption Cells
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, cnt-2));
//Set the font for the Caption
HSSFFont capFont = workbook.createFont();
capFont.setFontName(HSSFFont.FONT_ARIAL);
capFont.setColor(IndexedColors.BLACK.getIndex());
capFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle1.setFont(capFont);
cellStyle1.setAlignment((short) 0);
HSSFCell hssfCell1 = hssfHeader1.createCell(0);
hssfCell1.setCellStyle(cellStyle1);
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//Set the header for the report
hssfCell1.setCellValue("DCTM Usage Report excludes documents created by ECMDSM1, ia_admin and Viewstar - For the Month Ending "+lastDate);
int i =1;
int cellIdx=0;
//To write the column headers
while(i < cnt )
{
headers.add(collection2.getAttr(i).getName());
i++;
}
HSSFRow hssfHeader = sheet.createRow(2);
//Set the font and styles
HSSFFont font = workbook.createFont();
font.setFontName(HSSFFont.FONT_ARIAL);
font.setColor(IndexedColors.BLACK.getIndex());
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setAlignment(HSSFCellStyle.BORDER_THIN);
//Get and set the column headers
for (Iterator cells = headers.iterator(); cells.hasNext();) {
HSSFCell hssfCell = hssfHeader.createCell(cellIdx);
hssfCell.setCellStyle(cellStyle);
hssfCell.setCellValue((String) cells.next());
sheet.autoSizeColumn(cellIdx);
//sheet.setColumnWidth(cellIdx,5000);
cellIdx++;
}
int rowIdx = 3;//Caption is written on top 2 rows and write the report from 3rd row
//Get the attribute values and set the values in the workbook
while(collection2.next())
{
i = 0;
int collIndex =0;
HSSFRow hssfRow = sheet.createRow(rowIdx++);
while(i < (cnt-1)){
collIndex=i+1;
HSSFCell hssfCell = hssfRow.createCell(i);
/*IDfAttr attr_name = collection2.getAttr(collIndex);
if(attr_name.isRepeating()){
a_name = attr_name.getName();
a_value= collection2.getAllRepeatingStrings(a_name, ",");
hssfCell.setCellValue(a_value);
}else {*/
String attribute = collection2.getAttr(i).getName();
String value = collection2.getValueAt(i++).toString();
IDfDocument object = (IDfDocument)(session.getObject(new DfId(collection2.getString("r_object_id"))));
String repeatingValue = object.getAllRepeatingStrings(attribute, ",");
if(repeatingValue != null && repeatingValue.trim() != "")
{
value = repeatingValue;
hssfCell.setCellValue(value);
}else{
hssfCell.setCellValue(value);
//hssfCell.setCellValue(collection2.getValueAt(collIndex).toString());
}
sheet.setColumnWidth(i,5000);
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
cellStyle2.setWrapText(true);
hssfCell.setCellStyle(cellStyle2);
i++;
}
}
collection2.close(); //close the collection
fos = new FileOutputStream(new File(filePath+reportName));
workbook.write(fos); //Write the data to workbook
System.out.println("Report Generated");
}
catch(DfException df){
System.out.println("dfc Exception :"+df.getStackTraceAsString());
}
catch (Exception e1) {
e1.printStackTrace();
}finally {
sMgr.release(session);
if (fos != null) {
fos.flush();
fos.close();
}
}
}
/**
* Method will send the generated NA usage report to the recipients mentioned each BU
* @param fileName
* @param recMailID
* @return businessUnit
*/
public void sendMailAttachment( String fileName, String recMailID,String businessUnit){
try {
//Set the date format
DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
Calendar cal = Calendar.getInstance();
//Get the first date of current month
cal.add(Calendar.MONTH,-1);
cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH));
//Get the last day of previous month to mention in the report
String lastDate = (dateFormat.format(cal.getTime()));
// Create the message to send
File tempFile = new File(fileName);
//Custom Mail Server
host = "mail10.custom.com";
Properties sysProperties = System.getProperties();
sysProperties.setProperty("mail.smtp.host", host);
Session session = Session.getDefaultInstance(sysProperties);
Message message = new MimeMessage(session);
// From Email Address
message.setFrom(new InternetAddress(fromId));
//Split the multiple recipients based on the ',' delimiter and add the email id to the recipient list
StringTokenizer st = new StringTokenizer(recMailID, ",");
while(st.hasMoreTokens()){
message.addRecipient(Message.RecipientType.TO,new InternetAddress(st.nextToken()));
}
// Create a multi-part to combine the parts
Multipart multipart = new MimeMultipart();
//Part 1 to add the body message
MimeBodyPart p1 = new MimeBodyPart();
//Part 2 to attach the NA usage report
MimeBodyPart p2 = new MimeBodyPart();
//Part 3 to add the signature
MimeBodyPart p3 = new MimeBodyPart();
//Form the Body message
StringBuffer bodyMessage = new StringBuffer();
bodyMessage.append("Hi,");
bodyMessage.append("\n");
bodyMessage.append("\n");
bodyMessage.append("Please find the attached Documentum Usage Report");
bodyMessage.append("\n");
bodyMessage.append("\n");
bodyMessage.append("\n");
//Form the signature
StringBuffer trailMessage = new StringBuffer();
trailMessage.append("\n");
trailMessage.append("\n");
trailMessage.append("\n");
trailMessage.append("Regards");
trailMessage.append("\n");
trailMessage.append("Documentum Support");
trailMessage.append("\n");
trailMessage.append("\n");
//Attach the report in the email
FileDataSource fds = new FileDataSource(tempFile);
p2.setDataHandler(new DataHandler(fds));
p2.setFileName(fds.getName());
p1.setText(bodyMessage.toString());
p3.setText(trailMessage.toString());
multipart.addBodyPart(p1);
multipart.addBodyPart(p2);
multipart.addBodyPart(p3);
//Set the subject message
message.setSubject(businessUnit+" Usage Report for Month ending with "+ lastDate);
message.setContent(multipart);
message.saveChanges();
// Send message
Transport.send(message);
} catch(Exception e){
e.printStackTrace();
}
}
}
