Hi,

I have written a Java PX on Change Status for Workflow Event to import price lines for Items.
User creates a Change Request and adds the price import file as attachment to the CR.When the CR is moved from Submit to Review status,the PX triggers and performs few validations at Pre trigger and at Post trigger import of prices will be done.

The validation I am doing here is to check whether the items listed in the import file are existing in Agile or not.If items are not existing in Agile, I am raising an exception.But at Pre trigger I am frequently receiving the error “ECR XXX has been modified. please cancel this action, refresh the page and try again “.What might be the reason?Please help.

Code:
1) PublishPricesToItems.java
package com;

import java.util.HashSet;

import org.apache.log4j.Logger;

import com.agile.api.APIException;
import com.agile.api.ChangeConstants;
import com.agile.api.IAgileSession;
import com.agile.api.IChange;
import com.agile.api.INode;
import com.agile.api.ITable;
import com.agile.px.ActionResult;
import com.agile.px.EventActionResult;
import com.agile.px.EventConstants;
import com.agile.px.IEventAction;
import com.agile.px.IEventInfo;
import com.agile.px.IWFChangeStatusEventInfo;
import com.bo.PublishPricesToItemsBO;
import com.geapp.agile.util.CommonUtil;

public class PublishPricesToItems implements IEventAction{
static Logger logger = Logger.getLogger(PublishPricesToItems.class);
static ActionResult actionResult = new ActionResult();
@Override
public EventActionResult doAction(IAgileSession session, INode arg1, IEventInfo eventInfo) {
CommonUtil.initAppLogger(PublishPricesToItems.class, session);
if(session!=null){
IWFChangeStatusEventInfo info = null;
IChange changeRequest = null;
ITable attachmentsTable = null;
HashSet<String> itemsNotInAgile = new HashSet<String>();
try {
if(eventInfo.getEventType()==EventConstants.EVENT_CHANGE_STATUS_FOR_WORKFLOW){
info = (IWFChangeStatusEventInfo) eventInfo;
if(info!=null){
changeRequest = (IChange) info.getDataObject();
logger.debug(“Change Request is”+changeRequest);
if(changeRequest!=null){
attachmentsTable = changeRequest.getTable(ChangeConstants.TABLE_ATTACHMENTS);
if(attachmentsTable!=null){
if(eventInfo.getEventTriggerType()==EventConstants.EVENT_TRIGGER_PRE){
logger.info(“pre trigger”);
itemsNotInAgile = PublishPricesToItemsBO.validateAttachments(attachmentsTable,eventInfo,session);
}else if(eventInfo.getEventTriggerType()==EventConstants.EVENT_TRIGGER_POST){
logger.debug(“post event”);

}else{
logger.info(“Invalid Trigger Type”);
}
}

}

}
}else{
logger.info(“Invalid event trigger”);

}
if(itemsNotInAgile.size()>0){
actionResult = new ActionResult(ActionResult.EXCEPTION, itemsNotInAgile+” are not present in Agile”);
}else{
actionResult = new ActionResult(ActionResult.STRING,”Success”);
}
}
catch (APIException e) {
e.printStackTrace();
}
}
return new EventActionResult(eventInfo, actionResult);
}
}

2) PublishPricesToItemsBO.java
package com.bo;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.agile.api.APIException;
import com.agile.api.ChangeConstants;
import com.agile.api.IAgileSession;
import com.agile.api.IAttachmentFile;
import com.agile.api.IImportManager;
import com.agile.api.IItem;
import com.agile.api.IRow;
import com.agile.api.ITable;
import com.agile.api.ITwoWayIterator;
import com.agile.api.ItemConstants;
import com.agile.px.IEventInfo;

public class PublishPricesToItemsBO {
static Logger logger = Logger.getLogger(PublishPricesToItemsBO.class);
public static HashSet<String> validateAttachments(ITable attachmentsTable,IEventInfo eventInfo,IAgileSession session){
HashSet<String> itemsNotInAgile = new HashSet<String>();
try {

String fileType = null;
IRow row = null;
InputStream sourceContent = null;
ITwoWayIterator attachmentsTableIterator = null;
if(attachmentsTable!=null){
attachmentsTableIterator = (ITwoWayIterator) attachmentsTable.iterator();
while(attachmentsTableIterator.hasNext()){
row = (IRow) attachmentsTableIterator.next();
if(row!=null){
fileType = (String) row.getValue(ChangeConstants.ATT_ATTACHMENTS_FILE_TYPE);
logger.debug(“FIle type is”+fileType);
if(!fileType.equalsIgnoreCase(“”) && (fileType.equalsIgnoreCase(“xls”) || fileType.equalsIgnoreCase(“xlsx”))){
sourceContent = ((IAttachmentFile)row).getFile();
itemsNotInAgile = validateSourceFile(sourceContent,fileType,eventInfo,session);
}
}
}
}
} catch (IOException e) {
e.printStackTrace();
} catch (APIException e) {
e.printStackTrace();
}
return itemsNotInAgile;
}

@SuppressWarnings(“resource”)
private static HashSet<String> validateSourceFile(InputStream sourceContent,String fileType,IEventInfo eventInfo,IAgileSession session) throws IOException, APIException {
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
HashSet<String> itemsNotInAgile = new HashSet<String>();
String itemNumber = null;
Cell itemNumberCell = null;
int itemCellNumber = 0;
int numberOfSheets = 0;
HashSet<String> itemsList = new HashSet<String>();

if(!fileType.equalsIgnoreCase(“”)&&fileType.equalsIgnoreCase(“xls”)){
workbook = new HSSFWorkbook(sourceContent);

}else if(!fileType.equalsIgnoreCase(“”)&&fileType.equalsIgnoreCase(“xlsx”)){
workbook = new XSSFWorkbook(sourceContent);
}
else{
logger.info(“Invalid file type”);
}
if(workbook!=null){
numberOfSheets = workbook.getNumberOfSheets();
}
for (int i = 0; i < numberOfSheets; i++) {
sheet = workbook.getSheetAt(i);
logger.info(“Total number of rows: “+sheet.getPhysicalNumberOfRows());
if(sheet!=null){
itemCellNumber = getItemColumnIndex(sheet.getRow(0));
for(int j=1;j<sheet.getPhysicalNumberOfRows();j++){
row = (Row) sheet.getRow(j);

itemNumberCell = row.getCell(itemCellNumber);
if(itemNumberCell!=null){
itemNumber = itemNumberCell.getStringCellValue();
logger.debug(“Item Number is”+itemNumber);
if(itemNumber!=null){
itemsList.add(itemNumber);
}
}
}

}
}

itemsNotInAgile = queryItems(itemsList,session);
return itemsNotInAgile;
}
private static int getItemColumnIndex(Row headerRow) {
String cellValue = null;
int itemCellNumber = 0 ;
if(headerRow!=null){
for(Cell cell:headerRow){
cellValue = cell.toString();
logger.debug(“cellValue is”+cellValue);
if((cellValue!=null)&& cellValue.equalsIgnoreCase(“Item#”)){
// cellsOfHeaderRow.add(cellValue);
itemCellNumber = cell.getColumnIndex();
logger.debug(“Item Cell number is”+itemCellNumber);
break;
}
}

}
return itemCellNumber;

}

private static HashSet<String> queryItems(HashSet<String> itemsList,IAgileSession session) {
Iterator<String> itemsListIterator = itemsList.iterator();
HashSet<String> itemsNotInAgile = new HashSet<String>();
String itemStr = null;
IItem item = null;
while(itemsListIterator.hasNext()){
itemStr = itemsListIterator.next();
try {
item = (IItem) session.getObject(ItemConstants.CLASS_PARTS_CLASS, itemStr);
} catch (APIException e) {
e.printStackTrace();
}
logger.debug(“Item is”+item);
if(item==null){
itemsNotInAgile.add(itemStr);
}

}
logger.debug(“Items not in Agile are”+itemsNotInAgile);
return itemsNotInAgile;
}
@SuppressWarnings(“unused”)
private static String importData(InputStream sourceContent,String fileType,IEventInfo eventInfo,IAgileSession session) throws IOException, APIException {
byte[] sourceFile = null;
String result = null;
byte[] mappingFile = null;
String FILE_PATH = “/home/lg332312/ItemMappingFile.xml”;
sourceFile = stream2byte(sourceContent);
InputStream mappingFileContent = new FileInputStream(FILE_PATH);
mappingFile = stream2byte(mappingFileContent);
String[] operations = new String[]{“prices”, “prices.priceLines”};
List<String> options=new ArrayList<String>();
options.add(“BusinessRuleOptions|ChangeMode=Redline”);
options.add(“BusinessRuleOptions|ChangeType=PCO”);
options.add(“BusinessRuleOptions|ChangeNumber=PCO00018”);
options.add(“BusinessRuleOptions|MultiRowUpdateMode=AddUpdateOnly”);

IImportManager imgr = (IImportManager)session.getManager(IImportManager.class);
byte[] logData =imgr.validateData(sourceFile,”ExcelFile”,mappingFile,null,operations,options);
InputStream logDataContent = byte2stream(logData);
result = getStringFromInputStream(logDataContent);
logger.debug(“Log data is:”+result);
return result;
}

private static byte[] stream2byte(InputStream stream) throws IOException {
ByteArrayOutputStream outStream=new ByteArrayOutputStream();
byte buf[]=new byte[1024*4];
int n=0;
while((n=stream.read(buf))!=-1){
outStream.write(buf, 0, n);
}
byte[] data=outStream.toByteArray();
outStream.close();
return data;
}

private static InputStream byte2stream(byte[] data) throws IOException{
ByteArrayInputStream stream=new ByteArrayInputStream(data);
return stream;
}

private static String getStringFromInputStream(InputStream is) {

BufferedReader br = null;
StringBuilder sb = new StringBuilder();

String line;
try {

br = new BufferedReader(new InputStreamReader(is));
while ((line = br.readLine()) != null) {
sb.append(line);
}

} catch (IOException e) {
e.printStackTrace();
} finally {
if (br != null) {
try {
br.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}

return sb.toString();

}

}