function getNumericValue(sheet,row, col) {
	var Row = sheet.getRow(row);
	if (Row != null) {
		var Cell = Row.getCell(col);
		if (Cell != null) {
			return(Cell.getNumericCellValue());
		}
	}
	return "";
}

function getStringValue(sheet,row, col) {
	var Row = sheet.getRow(row);
	var Cell = Row.getCell(col);
	if (Cell != null) {
		if (Cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return Cell.getStringCellValue();
		}
	}
	return "";
}

function getDateValue(sheet,row, col) {
	var Row = sheet.getRow(row);
	var Cell = Row.getCell(col);
	if (Cell != null) {
		if ((Cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
		|| (Cell.getCellType() == Cell.CELL_TYPE_FORMULA)) {
			return Cell.getDateCellValue();
		}
	}
	return "";
}

/* taken vom http://trephine.org/t/index.php?title=Extracting_Excel_data_with_Apache_POI_and_Rhino */
function cellValue(sheet, CellRow, CellCol) {
	row = sheet.getRow(CellRow);
	if (row != null) {
		cell = row.getCell(CellCol);
		if (cell != null) {
			var t = cell.cellType;
			if (t == Cell.CELL_TYPE_NUMERIC) {
				if (DateUtil.isCellDateFormatted(cell)) return new Date(cell.dateCellValue.time);
				else return cell.numericCellValue + 0.0;
			}
			if (t == Cell.CELL_TYPE_STRING) return cell.stringCellValue + '';
			if (t == Cell.CELL_TYPE_FORMULA) {
				try {
					if (DateUtil.isCellDateFormatted(cell)) return new Date(cell.dateCellValue.time);
					else return cell.numericCellValue + 0.0;
				} catch(err) { }
				try { return cell.stringCellValue + ''; } catch(err) { }
				try { return cell.numericCellValue + 0.0; } catch(err) { }
				try { return !!cell.booleanCellValue; } catch(err) { }
				try { return cell.errorCellValue + 0; } catch(err) { }
				return '';
			}
			if (t == Cell.CELL_TYPE_BLANK) return '';
			if (t == Cell.CELL_TYPE_BOOLEAN) return !!cell.booleanCellValue;
			if (t == Cell.CELL_TYPE_ERROR) return cell.errorCellValue + 0;
		}
	}
	return null;
}

function isCellEmpty(sheet,row, col) {
	var Row = sheet.getRow(row);
	if (Row != null) {
		var Cell = Row.getCell(col);
		if (Cell != null) {
			if (Cell.getCellType() != Cell.CELL_TYPE_BLANK) {
				return false;
			}
		}
	}
	return true;
}

var workspaceWithExcelSheet = "Customer|User Role Import";
var workProductName = "User Role Assignments";
var fileName = "User Role Assignments";
var sheetName = "Sheet1";

var rootProcess = stages.getProjectByPath(workspaceWithExcelSheet).getEntities("containsProcess@SYSTEM");

if (rootProcess.length > 0) {	
	var workProducts = rootProcess[0].getEntities("containsArtifact@SYSTEM");
	for each (workProduct in workProducts) {
		if (workProduct.getProperty("DisplayName") == workProductName) {
			var files = workProduct.getEntities("containsInstance@SYSTEM");
			for each (file in files) {
				if (file.getProperty("LogicalName") == fileName) {
					var filestream = file.getProperty("FileStream");
					
					if (filestream != null) {
						// Open file as an Excel workbook
						var wb = new WorkbookFactory.create(fileStream);
						var sheet = wb.getSheet(sheetName);

						if (sheet != null) {
							for (var row = 1; !isCellEmpty(sheet, row, 0); row++) {
								// We assume that the Excel sheet has a header in the top row and 
								// three columns with
								// 	 user id
								//	 role name
								//   workspace path (separated by '|' characters)
								
								var userName = getStringValue(sheet, row, 0);
								var roleName = getStringValue(sheet, row, 1);
								var workspacePath = getStringValue(sheet, row, 2);
								
								var user = stages.getUserByUsername(userName);
								var project = stages.getProjectByPath(workspacePath); 
								if (user != null && project != null) {
									var roles = project.getEntities("containsProcess@SYSTEM")[0].getEntities("containsRole@SYSTEM"); 
									for (var i = 0; i < roles.length; i++) { 
										var role = roles[i];
										if (role.getProperty("DisplayName") == roleName) {
											commands.execute(commands.newAssignUserCommand(role, [user]));
										}
									}
								}
							}
						}
					}
				}
			}
		}
	}
}