1 /*
  2  * To change this license header, choose License Headers in Project Properties.
  3  * To change this template file, choose Tools | Templates
  4  * and open the template in the editor.
  5  */
  6 package unstructured_data_parser;
  7 
  8 import java.util.ArrayList;
  9 import java.util.StringTokenizer;
 10 import constants.MainConstants;
 11 import unstructured_data_idao.ExcelDAO_Constants;
 12 import unstructured_data_idao.TokenValueDAO;
 13 import unstructured_data_idao.ExcelCellMap2DAO;
 14 import unstructured_data_idao.ExcelColumnMap2DAO;
 15 import unstructured_data_idao.OneRow_ExcelCells;
 16 
 17 /**
 18  *
 19  * @author sameldin
 20  * 
 21  * The class does both the vertical and horizontal parsing of excel sheet.
 22  */
 23 public class ColumnCellParserUsingBusinessRule_1 {
 24 
 25     ArrayList <ExcelColumnMap2DAO> classAllHorizontalMap2DAO_ArrayList = null;
 26     ArrayList <ExcelColumnMap2DAO> classAllVerticalMap2DAO_ArrayList = null;
 27     
 28     public ColumnCellParserUsingBusinessRule_1(ArrayList <OneRow_ExcelCells> passedAllExcelRowsDAO_ArrayList) {
 29 
 30         if(null == passedAllExcelRowsDAO_ArrayList) {            
 31            System.out.print("error passedAllExcelRowsDAO_ArrayList = null ");
 32            return;
 33         }
 34         pasreHorizontalUsingBusinessRule_1(passedAllExcelRowsDAO_ArrayList);
 35         pasreVerticalUsingBusinessRule_1(passedAllExcelRowsDAO_ArrayList); 
 36     }
 37     /*
 38     
 39     */
 40     public  void pasreHorizontalUsingBusinessRule_1(ArrayList <OneRow_ExcelCells> passedAllExcelRowsDAO_ArrayList) {
 41         
 42         // Keep track of row number column number 
 43         int cellNumber    = 0;
 44         int rowNumber       = 0;
 45         // instantiate the arrayList of each column DAO
 46         classAllHorizontalMap2DAO_ArrayList = new ArrayList <ExcelColumnMap2DAO>();
 47         ExcelColumnMap2DAO localExcelColumnMap2DAO = null;
 48         // loop into all rows of cells
 49         int columnNumber = 0;
 50         for(rowNumber = 0; rowNumber < passedAllExcelRowsDAO_ArrayList.size(); rowNumber++){
 51             // get one row objectand make sure is not null
 52             OneRow_ExcelCells tempOneRow_ExcelCells = passedAllExcelRowsDAO_ArrayList.get(rowNumber);
 53             if(null == tempOneRow_ExcelCells){
 54                 continue;
 55             }
 56             // get the arrayList of out of the row object
 57             ArrayList <ExcelCellMap2DAO> oneArrayListCells = tempOneRow_ExcelCells.getOneRowDAOArrayList();
 58             // loop throe the row of cell and get each ExcelCellMap2DAO
 59             for(cellNumber = 0; cellNumber < oneArrayListCells.size(); cellNumber++){
 60                 //
 61                 ExcelCellMap2DAO localExcelCellMap2DAO = oneArrayListCells.get(cellNumber);
 62                 // instantiate the first Column map DAO to start populating and added ArrayList
 63                 // create on Column object for each row
 64                 if(0 == cellNumber) {
 65                     localExcelColumnMap2DAO = new ExcelColumnMap2DAO();
 66                     classAllHorizontalMap2DAO_ArrayList.add(localExcelColumnMap2DAO);
 67                 }
 68                 int result = populateExcelColumnMap2DAO(localExcelCellMap2DAO, localExcelColumnMap2DAO);                
 69             }
 70         }
 71     }
 72     /*
 73     
 74     */
 75     public  void pasreVerticalUsingBusinessRule_1(ArrayList <OneRow_ExcelCells> passedAllExcelRowsDAO_ArrayList) {
 76 
 77         if(null == passedAllExcelRowsDAO_ArrayList) {            
 78            System.out.print("error passedAllExcelRowsDAO_ArrayList = null ");
 79            return;
 80         }
 81         // Keep track of row number column number 
 82         int cellNumber    = 0;
 83         int rowNumber       = 0;
 84         // instantiate the arrayList of each column DAO
 85         classAllVerticalMap2DAO_ArrayList = new ArrayList <ExcelColumnMap2DAO>();
 86         ExcelColumnMap2DAO localExcelColumnMap2DAO = null;
 87         // loop into all rows of cells
 88         int numberOfRows = passedAllExcelRowsDAO_ArrayList.size();
 89         int columnNumber = 0;
 90         int numberOfColumns = 0;
 91         for(rowNumber = 0; rowNumber < passedAllExcelRowsDAO_ArrayList.size(); rowNumber++){
 92             // get one row objectand make sure is not null
 93             OneRow_ExcelCells tempOneRow_ExcelCells = passedAllExcelRowsDAO_ArrayList.get(rowNumber);
 94             if(null == tempOneRow_ExcelCells){
 95                 continue;
 96             }
 97             // get the arrayList of out of the row object
 98             ArrayList <ExcelCellMap2DAO> oneArrayListCells = tempOneRow_ExcelCells.getOneRowDAOArrayList();
 99             if(numberOfColumns < oneArrayListCells.size())
100                 numberOfColumns = oneArrayListCells.size();
101         }
102 
103         for(columnNumber = 0; columnNumber < numberOfColumns; columnNumber++){
104             localExcelColumnMap2DAO = new ExcelColumnMap2DAO();
105             classAllVerticalMap2DAO_ArrayList.add(localExcelColumnMap2DAO); 
106             
107             for(rowNumber = 0; rowNumber < passedAllExcelRowsDAO_ArrayList.size(); rowNumber++){
108                 // get one row objectand make sure is not null
109                 OneRow_ExcelCells tempOneRow_ExcelCells = passedAllExcelRowsDAO_ArrayList.get(rowNumber);
110                 if(null == tempOneRow_ExcelCells){
111                     continue;
112                 }
113             // get the arrayList of out of the row object
114                 ArrayList <ExcelCellMap2DAO> oneArrayListCells = tempOneRow_ExcelCells.getOneRowDAOArrayList();
115                 ExcelCellMap2DAO localExcelCellMap2DAO = oneArrayListCells.get(columnNumber); 
116                 int result = populateExcelColumnMap2DAO(localExcelCellMap2DAO, localExcelColumnMap2DAO); 
117             }
118         }
119     }
120     /*
121     
122     */
123     public ArrayList <ExcelColumnMap2DAO> getAllHorizontalColumnsParsed(){
124         
125         return(classAllHorizontalMap2DAO_ArrayList);
126     }
127     /*
128     
129     */
130     public ArrayList <ExcelColumnMap2DAO> getAllVerticalColumnsParsed(){
131         
132         return(classAllVerticalMap2DAO_ArrayList);
133     }
134     /*
135     
136     */
137     int populateExcelColumnMap2DAO(ExcelCellMap2DAO passedExcelCellMap2DAO,
138                                 ExcelColumnMap2DAO passedExcelColumnMap2DAO){
139         
140         int totalColumnCount = passedExcelColumnMap2DAO.getTotalCount() + 1;
141         passedExcelColumnMap2DAO.setTotalCount(totalColumnCount); 
142         int cellTypeIndex = passedExcelCellMap2DAO.getCellType();
143         
144         switch(cellTypeIndex)
145         {
146             case ExcelDAO_Constants.EXCEL_CELL_TYPE_BLANK:
147                 int blankCount = passedExcelColumnMap2DAO.getBlankCount() + 1;
148                 passedExcelColumnMap2DAO.setBooleanCount(blankCount);
149                 //System.out.print("EXCEL_CELL_TYPE_BLANK ");
150                 break;
151             case ExcelDAO_Constants.EXCEL_CELL_TYPE_NUMERIC:
152                 int numericCount = passedExcelColumnMap2DAO.getNumericCount() + 1;
153                 passedExcelColumnMap2DAO.setNumericCount(numericCount);
154                 //System.out.print("EXCEL_CELL_TYPE_NUMERIC ");
155                 break;
156             case ExcelDAO_Constants.EXCEL_CELL_TYPE_STRING:
157                 int evaluationIndex = passedExcelCellMap2DAO.getCellRowEvaluationIndex();
158                 int tempIndex = ExcelDAO_Constants.ERROR_EVALUATION_INDEX;
159                 switch(evaluationIndex)
160                 {
161                     case ExcelDAO_Constants.STRING_COMMENT_INDEX:
162                         tempIndex = passedExcelColumnMap2DAO.getCommentCount() + 1;
163                         passedExcelColumnMap2DAO.setCommentCount(tempIndex); 
164                     break;
165                     case ExcelDAO_Constants.STRING_LABEL_INDEX:
166                         tempIndex = passedExcelColumnMap2DAO.getLabelCount() + 1;
167                         passedExcelColumnMap2DAO.setLabelCount(tempIndex); 
168                     break;
169                     case ExcelDAO_Constants.STRING_KNOWN_HEADER_INDEX:
170                         tempIndex = passedExcelColumnMap2DAO.getKnownheaderCount()+ 1;
171                         passedExcelColumnMap2DAO.setKnownheaderCount(tempIndex); 
172                     break;
173                     case ExcelDAO_Constants.STRING_UNKNOWN_HEADER_INDEX:
174                         tempIndex = passedExcelColumnMap2DAO.getUnknownheaderCount() + 1;
175                         passedExcelColumnMap2DAO.setUnknownheaderCount(tempIndex); 
176                     break;
177                     default:
178                         tempIndex = passedExcelColumnMap2DAO.getErrorCount() + 1;
179                         passedExcelColumnMap2DAO.setErrorCount(tempIndex);                     
180                 }
181                 break;
182             case ExcelDAO_Constants.EXCEL_CELL_TYPE_BOOLEAN:
183                 int booleanCount = passedExcelColumnMap2DAO.getBlankCount()+ 1;
184                 passedExcelColumnMap2DAO.setBooleanCount(booleanCount);
185                 //System.out.print("EXCEL_CELL_TYPE_BOOLEAN ");
186                 break;
187             case ExcelDAO_Constants.EXCEL_CELL_TYPE_FORMULA:
188                 int formulaEvaluationIndex = passedExcelCellMap2DAO.getCellRowEvaluationIndex();
189                 int formulaIndex = ExcelDAO_Constants.ERROR_EVALUATION_INDEX;
190                 switch(formulaEvaluationIndex)
191                 {
192                     case ExcelDAO_Constants.FORMULA_SUM_ROW_CELLS_INDEX:
193                         tempIndex = passedExcelColumnMap2DAO.getFormulaSumCount() + 1;
194                         passedExcelColumnMap2DAO.setFormulaSumCount(tempIndex); 
195                     break;
196                     case ExcelDAO_Constants.FORMULA_SUM_COLUMN_CELLS_INDEX:
197                         tempIndex = passedExcelColumnMap2DAO.getFormulaSumCount() + 1;
198                         passedExcelColumnMap2DAO.setFormulaSumCount(tempIndex);
199                     break;
200                     default:
201                         tempIndex = passedExcelColumnMap2DAO.getErrorCount() + 1;
202                         passedExcelColumnMap2DAO.setErrorCount(tempIndex);                     
203                 }                
204                 break;
205             case ExcelDAO_Constants.EXCEL_CELL_TYPE_ERROR:
206                 int errorCount = passedExcelColumnMap2DAO.getErrorCount()+ 1;
207                 passedExcelColumnMap2DAO.setErrorCount(errorCount);
208                 //System.out.print("EXCEL_CELL_TYPE_ERROR ");
209                 break;
210             case ExcelDAO_Constants.EXCEL_CELL_TYPE_DEFAULT:
211                 int errorCount2 = passedExcelColumnMap2DAO.getErrorCount()+ 1;
212                 passedExcelColumnMap2DAO.setErrorCount(errorCount2);
213                 //System.out.print("EXCEL_CELL_TYPE_DEFAULT ");
214                 break;
215            default:
216                 System.out.print("default ");
217         }
218         
219         return(MainConstants.SUCCESS);
220     }
221     /*
222        
223     */
224     public static void main(String[] args){
225 
226         // this main runs all classes for testing and debugging
227         // this main runs all classes for testing and debugging
228         // this main runs all classes for testing and debugging
229         
230         // map excel sheet into DAO ExcelCellMap2DAO
231         Excel2DAO_Mapper localExcel2DAO_Mapper = new Excel2DAO_Mapper();
232         // retrieve the all the ExcelCellMap2DAO rows
233         ArrayList <OneRow_ExcelCells> localAllRows = localExcel2DAO_Mapper.getAllSheetRows();
234         // if null exist with a message
235         if(null == localAllRows){
236            System.out.print("rows arrayList  is null ");
237            return;
238         }
239         // load buiness rule_1 tokens and get the arrayList of tokens
240         LoadTokenValueArrayList tempLoadTokenValueArrayList = new LoadTokenValueArrayList();
241         ArrayList <TokenValueDAO> localTokenValueDAOArrayList = tempLoadTokenValueArrayList.getTokenValueArrayList();
242         // check if null and exist
243         if(null == localTokenValueDAOArrayList){
244            System.out.print("localTokenValueDAOArrayList  is null ");
245            return;
246         }        
247         int rowNumber = 0;
248         for(rowNumber = 0; rowNumber < localAllRows.size(); rowNumber++){
249             // loop throw the rows and parse each row
250             OneRow_ExcelCells localOneRow_ExcelCells = localAllRows.get(rowNumber);
251             RowCellParserUsingBusinessRule_1 localRowCellParserUsingBusinessRule_1 = 
252                                 new RowCellParserUsingBusinessRule_1(localOneRow_ExcelCells, localTokenValueDAOArrayList);
253         }   
254         
255         // start parsing all the rows both horizontal and vertical
256         ColumnCellParserUsingBusinessRule_1 localColumnCellParserUsingBusinessRule_1 = 
257                     new ColumnCellParserUsingBusinessRule_1(localAllRows);
258         // retrieve the arrayList  for both horizontal and vertical
259         ArrayList <ExcelColumnMap2DAO> columnMapHorizontalArrayList = localColumnCellParserUsingBusinessRule_1.getAllHorizontalColumnsParsed();
260         ArrayList <ExcelColumnMap2DAO> columnMapVerticalArrayList = localColumnCellParserUsingBusinessRule_1.getAllVerticalColumnsParsed();
261         // get the first row which has the columns names and type to creat DOA Java Class and store it in the proper folder
262         OneRow_ExcelCells firstRow = localAllRows.get(0);
263         Convert2DAO_Class_UsingBusinessRule_1 convertClasshandle = 
264                 new Convert2DAO_Class_UsingBusinessRule_1(localAllRows, columnMapHorizontalArrayList, columnMapVerticalArrayList);
265         // use SOAP to create the XML files with values from excel sheet
266         // The excel sheet values and ID (row+column) are stored in all the DOA rows.
267         // The first row should not be included since it has the propeties names and types
268         // row # 2 and higher have all the excel values
269         DAO2SOAP_XML_Converter tempDAO2SOAP_XML_Converter = new DAO2SOAP_XML_Converter(localAllRows);
270         
271         int stopDebugger = 0;
272     }   
273 }
274