gt: 				if(row != null) {
context: package utilities;
  
  
  /*
  This file is part of SMAP.
  
  SMAP is free software: you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation, either version 3 of the License, or
  (at your option) any later version.
  
  SMAP is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.
  
  You should have received a copy of the GNU General Public License
  along with SMAP.  If not, see <http://www.gnu.org/licenses/>.
  
   */
  
  import java.io.InputStream;
  import java.sql.Connection;
  import java.util.ArrayList;
  
  import java.util.HashMap;
  import java.util.ResourceBundle;
  import java.util.Stack;
  import java.util.regex.Pattern;
  
  import org.apache.poi.ss.usermodel.*;
  import org.javarosa.xpath.XPathParseTool;
  import org.smap.sdal.Utilities.ApplicationException;
  import org.smap.sdal.Utilities.ApplicationWarning;
  import org.smap.sdal.Utilities.GeneralUtilityMethods;
  import org.smap.sdal.constants.SmapQuestionTypes;
  import org.smap.sdal.model.Form;
  import org.smap.sdal.model.KeyValueSimp;
  import org.smap.sdal.model.Label;
  import org.smap.sdal.model.Language;
  import org.smap.sdal.model.ManifestInfo;
  import org.smap.sdal.model.MetaItem;
  import org.smap.sdal.model.Option;
  import org.smap.sdal.model.OptionList;
  import org.smap.sdal.model.Pulldata;
  import org.smap.sdal.model.Question;
  import org.smap.sdal.model.Role;
  import org.smap.sdal.model.RoleColumnFilterRef;
  import org.smap.sdal.model.SqlFrag;
  import org.smap.sdal.model.Survey;
  
  public class XLSTemplateUploadManager {
  
  	/*
  	 * Globals
  	 */
  	Workbook wb = null;
  	Sheet surveySheet = null;
  	Sheet choicesSheet = null;
  	Sheet settingsSheet = null;
  
  	int rowNumSurvey = 0;			// Heading row is 0
  	int rowNumChoices = 0;		
  	int rowNumSettings = 0;		
  	int lastRowNumSurvey = 0;
  	int lastRowNumChoices = 0;
  	int lastRowNumSettings = 0;
  
  	HashMap<String, Integer> surveyHeader = null;
  	HashMap<String, Integer> choicesHeader = null;
  	HashMap<String, Integer> settingsHeader = null;
  	HashMap<String, Integer> choiceFilterHeader = null;
  	HashMap<String, Integer> columnRoleHeader = null;
  	HashMap<String, Integer> rowRoleHeader = null;
  	
  	HashMap<String, String> questionNames;	// Mapping between original name and truncated name
  	HashMap<String, String> optionNames;		// Mapping between original name and truncated name
  	boolean merge;
  
  	HashMap<String, Integer> qNameMap = new HashMap<> ();							// Use in question name validation
  	HashMap<String, HashMap<String, Integer>> oNameMap = new HashMap<> ();		// Use in option name validation
  	Pattern validQname = Pattern.compile("^[A-Za-z_][A-Za-z0-9_\\-\\.]*$");
  	Pattern validChoiceName = Pattern.compile("^[A-Za-z0-9_@\\-\\.\\+%,():/]*$");
  
  	HashMap<Integer, Stack<Question>> groupStackMap = new HashMap<>();			// Keep track of groups in forms
  	boolean inFieldList = false;												// Only some questions are allowed inside a field list
  	
  	boolean useDefaultLanguage = false;
  	
  	boolean inTableListGroup = false;
  	boolean foundSelectInTableListGroup = false;
  	boolean justStartedTableListGroup = false;
  	
  	int metaId = -1000;
  
  	Survey survey = null;
  
  	private class FunctionCheck {
  		String name;
  		int args;
  		String template;
  		
  		FunctionCheck(String n, int a, String t) {
  			name = n;
  			args = a;
  			template = t;
  		}
  	}
  	private ArrayList<FunctionCheck> functions = new ArrayList<> ();
  	private ResourceBundle localisation = null;
  	private ArrayList<ApplicationWarning> warnings = null;
  
  	public XLSTemplateUploadManager(ResourceBundle l, ArrayList<ApplicationWarning> w) {
  		
  		localisation = l;
  		warnings = w;
  		
  		// Initialise Function Check array
  		functions.add(new FunctionCheck("count", 1, "count(nodeset)"));
  		functions.add(new FunctionCheck("if", 3, "if(condition, a, b)"));
  		
  	}
  
  	/*
  	 * Get a survey definition from an XLS file
  	 */
  	public Survey getSurvey(
  			Connection sd,
  			int oId, 
  			String type, 
  			InputStream inputStream, 
  			String displayName,
  			int p_id,
  			HashMap<String, String> questionNames,
  			HashMap<String, String> optionNames,
  			boolean merge,
  			int existingVersion) throws Exception {
  
  		this.questionNames = questionNames;
  		this.optionNames = optionNames;
  		this.merge = merge;
  
  		wb = WorkbookFactory.create(inputStream);
  
  		// Create survey and set defaults
  		survey = new Survey();
  		survey.displayName = displayName;
  		survey.o_id = oId;
  		survey.p_id = p_id;
  		survey.version = merge ? existingVersion + 1 : 1;
  		survey.loadedFromXLS = true;
  		survey.deleted = false;
  		survey.blocked = false;
  		survey.meta.add(new MetaItem(metaId--, "string", "instanceID", null, "instanceid", null, false, null, null));
  		survey.meta.add(new MetaItem(metaId--, "string", "instanceName", null, "instancename", null, false, null, null));
  
  		surveySheet = wb.getSheet("survey");
  		choicesSheet = wb.getSheet("choices");
  		settingsSheet = wb.getSheet("settings");
  
  		if(surveySheet == null) {
  			throw XLSUtilities.getApplicationException(localisation, "tu_nw", -1, "survey", null, null, null);
  		} else if(surveySheet.getPhysicalNumberOfRows() == 0) {
  			throw XLSUtilities.getApplicationException(localisation, "tu_ew", -1, "survey", null, null, null);
  		} else {
  
  			lastRowNumSurvey = surveySheet.getLastRowNum();
  			if(choicesSheet != null) {
  				lastRowNumChoices = choicesSheet.getLastRowNum();
  			}
  			if(settingsSheet != null) {
  				lastRowNumSettings = settingsSheet.getLastRowNum();
  			}			
  
  			getHeaders();	// get headers and set the languages from them
  			
  			/*
  			 * 1. Process the choices sheet
  			 */
  			if(choicesSheet != null) {
  				while(rowNumChoices <= lastRowNumChoices) {
  
  					Row row = choicesSheet.getRow(rowNumChoices++);
  
  					if(row != null) {
  						int lastCellNum = row.getLastCellNum();	
  						String listName = XLSUtilities.getTextColumn(row, "list name", choicesHeader, lastCellNum, null);
  						if(listName == null) {
  							listName = XLSUtilities.getTextColumn(row, "list_name", choicesHeader, lastCellNum, null);
  						}
  						
  						if(listName != null) {
  							OptionList ol = survey.optionLists.get(listName);
  							if(ol == null) {
  								ol = new OptionList();
  								survey.optionLists.put(listName, ol);
  							}
  							ol.options.add(getOption(row, listName));
  						}
  
  					}
  				}
  			}
  
  			/*
  			 * 2. Process the survey sheet
  			 */
  			Form f = getForm("main", -1, -1, null);
  			// Validate the top level form
  			if(survey.forms.get(0).questions.size() == 0) {
  				throw new ApplicationException(localisation.getString("tu_nq"));
  			}
  			validateForm(1, f);
  			
  			/*
  			 * 3, Process the settings sheet
  			 */
  			if(settingsSheet != null && settingsHeader != null) {
  				Row row = settingsSheet.getRow(rowNumSettings++);
  				if(row != null) {
  					int lastCellNum = row.getLastCellNum();
  					
  					// Default language
  					survey.def_lang = XLSUtilities.getTextColumn(row, "default_language", settingsHeader, lastCellNum, null);
  					if(survey.def_lang != null) {
  						boolean validLanguage = false;
  						for(Language l : survey.languages) {
  							if(l.name.equals(survey.def_lang)) {
  								validLanguage = true;
  								break;
  							}
  						}
  						if(!validLanguage) {
  							throw new ApplicationException(localisation.getString("tu_idl"));
  						}
  					}
  					
  					survey.instanceNameDefn = XLSUtilities.getTextColumn(row, "instance_name", settingsHeader, lastCellNum, null);
  					survey.surveyClass = XLSUtilities.getTextColumn(row, "style", settingsHeader, lastCellNum, null);
  					survey.task_file = getBooleanColumn(row, "allow_import", settingsHeader, lastCellNum);
  					survey.setHideOnDevice(getBooleanColumn(row, "hide_on_device", settingsHeader, lastCellNum));
  					survey.timing_data = getBooleanColumn(row, "timing_data", settingsHeader, lastCellNum);
  					survey.hrk = XLSUtilities.getTextColumn(row, "key", settingsHeader, lastCellNum, null);
  					String pdRepeats = XLSUtilities.getTextColumn(row, "pulldata_repeat", settingsHeader, lastCellNum, null);
  					if(pdRepeats != null) {
  						String [] pdArray = pdRepeats.split(":");
  						if(pdArray.length > 0) {
  							for(String pd : pdArray) {
  								pd = pd.trim();
  								int idx = pd.indexOf("(");
  								if(idx > 0) {
  									String sName = pd.substring(0, idx);
  									String key = pd.substring(idx + 1, pd.length() - 1);
  									if(survey.pulldata == null) {
  										survey.pulldata = new ArrayList<Pulldata> ();
  									}
  									survey.pulldata.add(new Pulldata(sName, key));
  								}
  								
  							}
  						}
  					}
  					survey.key_policy = XLSUtilities.getTextColumn(row, "key_policy", settingsHeader, lastCellNum, null);
  
  					
  					// Add row filters
  					if(rowRoleHeader != null && rowRoleHeader.size() > 0) {
  						for(String h : rowRoleHeader.keySet()) {
  							String filter = XLSUtilities.getTextColumn(row, h, settingsHeader, lastCellNum, null);
  							if(filter != null) {
  								Role r = survey.roles.get(h);
  								if(r != null) {
  									SqlFrag sq = new SqlFrag();
  									sq.addSqlFragment(filter, false, localisation);
  									settingsQuestionInSurvey(sq.humanNames, h);		// validate question names
  									r.row_filter = filter;
  								}
  							}
  						}
  					}
  				}
  			}
  
  		}
  		
  		/*
  		 * Add default preloads
  		 */
  		if(!hasMeta("start")) {
  			survey.meta.add(new MetaItem(metaId--, "dateTime", "_start", "start", "_start", "timestamp", true, "start", null));
  		}
  		if(!hasMeta("end")) {
  			survey.meta.add(new MetaItem(metaId--, "dateTime", "_end", "end", "_end", "timestamp", true, "end", null));
  		}
  		if(!hasMeta("deviceid")) {
  			survey.meta.add(new MetaItem(metaId--, "string", "_device", "deviceid", "_device", "property", true, "device", null));
  		}
  		
  		validateSurvey();	// 4. Final Validation
  
  		return survey;
  
  	}
  	
  	private boolean hasMeta(String sourceParam) {
  		for(MetaItem mi : survey.meta) {
  			if(mi.isPreload && mi.sourceParam.equals(sourceParam)) {
  				return true;
  			}
  		}
  		return false;
  	}
  
  	private Option getOption(Row row, String listName) throws ApplicationException, Exception {
  
  		Option o = new Option();
  		int lastCellNum = row.getLastCellNum();
  		o.optionList = listName;
  
  		o.value = XLSUtilities.getTextColumn(row, "name", choicesHeader, lastCellNum, null);
  		o.display_name = XLSUtilities.getTextColumn(row, "display_name", choicesHeader, lastCellNum, null);
  		getLabels(row, lastCellNum, choicesHeader, o.labels, "choice");
  		
  		if(merge) {
  			// Attempt to get existing column name
  			String n = optionNames.get(listName + "__" + o.value);
  			if(n != null) {
  				o.columnName = n;
  			} else {
  				o.columnName = GeneralUtilityMethods.cleanName(o.value, false, false, false);
  			}
  		} else {
  			o.columnName = GeneralUtilityMethods.cleanName(o.value, false, false, false);
  		}
  		o.cascade_filters = new HashMap<String, String> ();  
  		for(String key : choiceFilterHeader.keySet()) {
  			String value = XLSUtilities.getTextColumn(row, key, choicesHeader, lastCellNum, null);
  			if (value != null) {
  				o.cascade_filters.put(key, value);
  			}
  		}
  
  		o.published = false;		// Default to unpublised TODO work out when this can be set to published
  		validateOption(o, rowNumChoices);
  
  		return o;
  	}
  
  	/*
  	 * Get the survey header and the choices header so we can identify all the languages up front
  	 * This should work gracefully with a badly designed forms where there are inconsistent language
  	 *  names between the survey and choices sheet
  	 */
  	private void getHeaders() throws ApplicationException {
  
  		choiceFilterHeader = new HashMap<String, Integer> ();		
  		HashMap<String, String> langMap = new HashMap<String, String> ();
  
  		// Get survey sheet headers
  		while(rowNumSurvey <= lastRowNumSurvey) {
  			Row row = surveySheet.getRow(rowNumSurvey++);
  			if(row != null) {
  				surveyHeader = XLSUtilities.getHeader(row, localisation, rowNumSurvey, "survey");
  
  				// Add languages in order they exist in the header hence won't use keyset of surveyHeader
  				int lastCellNum = row.getLastCellNum();							
  				for(int i = 0; i <= lastCellNum; i++) {
  					Cell cell = row.getCell(i);
  					if(cell != null) {
  						String name = cell.getStringCellValue();
  						if(name.startsWith("label::")) {	 // Only check the question label for languages, any others will be assumed to be errors
  							String [] sArray = name.split("::");
  							if(sArray.length > 0) {
  								String exists = langMap.get(sArray[1]);
  								if(exists == null) {
  									langMap.put(sArray[1], sArray[1]);
  									survey.languages.add(new Language(0, sArray[1]));
  								}
  							}
  						}
  					}
  				}
  				
  				// Get security roles
  				for(String h : surveyHeader.keySet()) {
  					if(h.startsWith("role::")) {
  						if(columnRoleHeader == null) {
  							columnRoleHeader = new HashMap<String, Integer> ();
  						}
  						columnRoleHeader.put(h, surveyHeader.get(h));
  						String [] roleA = h.split("::");
  						if(roleA.length > 1) {
  							survey.roles.put(h, new Role(roleA[1]));
  						}
  					}
  				}
  
  				break;
  			}
  		}
  
  		// Get choice sheet header
  		if(choicesSheet != null) {
  			while(rowNumChoices <= lastRowNumChoices) {
  				Row row = choicesSheet.getRow(rowNumChoices++);
  