jars/gss-lib-2.2/src/com/pras/WorkSheet.java

   1 /*\r
   2  * Copyright (C) 2010 Prasanta Paul, http://prasanta-paul.blogspot.com\r
   3  *\r
   4  * Licensed under the Apache License, Version 2.0 (the "License");\r
   5  * you may not use this file except in compliance with the License.\r
   6  * You may obtain a copy of the License at\r
   7  *\r
   8  *      http://www.apache.org/licenses/LICENSE-2.0\r
   9  *\r
  10  * Unless required by applicable law or agreed to in writing, software\r
  11  * distributed under the License is distributed on an "AS IS" BASIS,\r
  12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\r
  13  * See the License for the specific language governing permissions and\r
  14  * limitations under the License.\r
  15  */\r
  16 \r
  17 package com.pras;\r
  18 \r
  19 import java.util.ArrayList;\r
  20 import java.util.HashMap;\r
  21 import java.util.Iterator;\r
  22 import java.util.Set;\r
  23 \r
  24 import com.pras.conn.HttpConHandler;\r
  25 import com.pras.conn.Response;\r
  26 import com.pras.sp.Entry;\r
  27 import com.pras.sp.Feed;\r
  28 import com.pras.sp.Field;\r
  29 import com.pras.sp.ParseFeed;\r
  30 import com.pras.table.Record;\r
  31 import com.pras.table.Table;\r
  32 \r
  33 /**\r
  34  * This class represents an WorkSheet and utility methods to manage WorkSheet data.\r
  35  * WorkSheets will manage (Add/Edit/Delete) data in Tables. It also supports <u>List \r
  36  * based Feed</u>, but only to <b>retrieve data</b>. At present it doesn't provide any method to Add/Edit/Delete\r
  37  * data in List based Feed.\r
  38  * </br>\r
  39  * </br>\r
  40  * <b><a href="http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#TableFeeds">Table Feed</a></b>\r
  41  * </br>\r
  42  * To Add/Edit/Delete Data, you need to use addRecord(), updateRecord() and deleteRecord(). It \r
  43  * internally stores data in a single Table. \r
  44  * </br>\r
  45  * <b>Note:</b> <u>Table Feed will ignore data entered through Web UI.</u>\r
  46  * So, if you want all data records irrespective of from where those are inserted/updated, you should use List Feed.\r
  47  *    \r
  48  * </br>\r
  49  * </br>\r
  50  * <b><a href="http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#ListFeeds">List Feed</a></b>\r
  51  * </br>\r
  52  * To Retrieve data using List based Feed, use getData() method.\r
  53  * \r
  54  * @author Prasanta Paul\r
  55  */\r
  56 \r
  57 public class WorkSheet {\r
  58 \r
  59         /*\r
  60          *TODO:\r
  61          *Add set and get methods without creating local variables (use entry instance)\r
  62          */\r
  63         private String TAG = "WorkSheet";\r
  64         private String workSheetID;\r
  65         private String workSheetURL;\r
  66         private String title;\r
  67         private int colCount;\r
  68         private int rowCount;\r
  69         /**\r
  70          * Entries of the Header Row of List Feed\r
  71          */\r
  72         String[] columns = null;\r
  73         \r
  74         /**\r
  75          * Table associated with this WorkSheet. It will hold data records\r
  76          */\r
  77         private Table table;\r
  78 \r
  79         /**\r
  80          * To access all low level Feed values for this Work Sheet\r
  81          */\r
  82         private Entry entry;\r
  83         \r
  84         /**\r
  85          * It will hold WorkSheet records\r
  86          */\r
  87         private ArrayList<WorkSheetRow> records = new ArrayList<WorkSheetRow>();\r
  88         \r
  89         /**\r
  90          * Get WorkSheet ID\r
  91          * @return\r
  92          */\r
  93         public String getWorkSheetID() {\r
  94                 return workSheetID;\r
  95         }\r
  96         /**\r
  97          * Set WorkSheet ID\r
  98          * @param workSheetID\r
  99          */\r
 100         public void setWorkSheetID(String workSheetID) {\r
 101                 this.workSheetID = workSheetID;\r
 102         }\r
 103         \r
 104         /**\r
 105          * Get WorkSheetURL\r
 106          * @return\r
 107          */\r
 108         public String getWorkSheetURL() {\r
 109                 /*\r
 110                  * Sample: https://spreadsheets.google.com/feeds/worksheets/key/private/full\r
 111                  * Retrieved from src attribute-\r
 112                  * <content type='application/atom+xml;type=feed' src=''/>\r
 113                  */\r
 114                 return workSheetURL;\r
 115         }\r
 116         /**\r
 117          * Set WorkSheet\r
 118          * @param workSheetURL\r
 119          */\r
 120         public void setWorkSheetURL(String workSheetURL) {\r
 121                 this.workSheetURL = workSheetURL;\r
 122         }\r
 123         /**\r
 124          * Get WorkSheet Title\r
 125          * @return\r
 126          */\r
 127         public String getTitle() {\r
 128                 return title;\r
 129         }\r
 130         /**\r
 131          * Set WorkSheet Title\r
 132          * @param title\r
 133          */\r
 134         public void setTitle(String title) {\r
 135                 this.title = title;\r
 136         }\r
 137         /**\r
 138          * Get column count of this WorkSheet \r
 139          * @return\r
 140          */\r
 141         public int getColCount() {\r
 142                 return colCount;\r
 143         }\r
 144         /**\r
 145          * Set column count of this WorkSheet \r
 146          * @param colCount\r
 147          */\r
 148         public void setColCount(int colCount) {\r
 149                 this.colCount = colCount;\r
 150         }\r
 151         /**\r
 152          * Get row count of this WorkSheet \r
 153          * @return\r
 154          */\r
 155         public int getRowCount() {\r
 156                 return rowCount;\r
 157         }\r
 158         /**\r
 159          * Set row count of this WorkSheet \r
 160          * @param rowCount\r
 161          */\r
 162         public void setRowCount(int rowCount) {\r
 163                 this.rowCount = rowCount;\r
 164         }\r
 165         \r
 166         /**\r
 167          * Get columns of the WorkSheet. It will return columns of associated table, otherwise\r
 168          * columns of List Feed i.e. content of Header row of ListFeed\r
 169          * @return\r
 170          */\r
 171         public String[] getColumns() {\r
 172                 if(columns != null)\r
 173                         return columns;\r
 174                 \r
 175                 if(table != null){\r
 176                         ArrayList<String> tableCols = table.getCols();\r
 177                         if(tableCols != null){\r
 178                                 String[] cols = new String[tableCols.size()];\r
 179                                 tableCols.toArray(cols);\r
 180                                 return cols;\r
 181                         }\r
 182                 }\r
 183                 return null;\r
 184         }\r
 185         \r
 186         /**\r
 187          * Set columns of ListFeed\r
 188          * @param columns\r
 189          */\r
 190         public void setColumns(String[] columns) {\r
 191                 this.columns = columns;\r
 192         }\r
 193         \r
 194         /**\r
 195          * Get Entry instance for this WorkSheet.\r
 196          * Entry contains low level Feed Details\r
 197          * \r
 198          * @return\r
 199          */\r
 200         public Entry getEntry() {\r
 201                 return entry;\r
 202         }\r
 203         /**\r
 204          * Set Feed Entry instance\r
 205          * @param entry\r
 206          */\r
 207         public void setEntry(Entry entry) {\r
 208                 this.entry = entry;\r
 209         }\r
 210         \r
 211         void setTable(Table table){\r
 212                 this.table = table;\r
 213         }\r
 214         \r
 215         /**\r
 216          * Get all data of this WorkSheet (List based Feed)\r
 217          * \r
 218          * @return\r
 219          */\r
 220         /**\r
 221          * Get all data of this WorkSheet (List based Feed)\r
 222          * \r
 223          * @param isCachedData Do you want to read cached data or data from Server\r
 224          * @return\r
 225          */\r
 226         public ArrayList<WorkSheetRow> getData(boolean isCachedData){\r
 227                 return getData(isCachedData, false, null, null);\r
 228         }\r
 229 \r
 230         /**\r
 231          * List based Feed for a particular Work Sheet. Use this method if you want to retrieve data entered\r
 232          * through Web GUI and also through Table records.\r
 233          * \r
 234          * @param isCachedData Do you want to read cached data or data from Server\r
 235          * @param doReverse Do you need data in reverse order ?\r
 236          * @param sq <a href="http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#SendingStructuredRowQueries">Structured Query</a>. If you don't need this, set to <b>null</b>. (Make sure that column name is in lower case).\r
 237          * @param orderBy If you don't need this, set to <b>null</b>. (Make sure that column name is in lower case). \r
 238          * @return\r
 239          */\r
 240         public ArrayList<WorkSheetRow> getData(boolean isCachedData, boolean doReverse, String sq, String orderBy){\r
 241                 // Sample URL: GET https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full\r
 242                 \r
 243                 if(isCachedData)\r
 244                         return records;\r
 245                 \r
 246                 // Add Headers\r
 247                 HashMap<String, String> httpHeaders = new HashMap<String, String>();\r
 248                 httpHeaders.put(HttpConHandler.AUTHORIZATION_HTTP_HEADER, "GoogleLogin auth="+ SpreadSheetFactory.authToken);\r
 249                 httpHeaders.put(HttpConHandler.GDATA_VERSION_HTTP_HEADER, "3.0");\r
 250                 \r
 251                 HttpConHandler http = new HttpConHandler();\r
 252                 \r
 253                 String url = workSheetURL;\r
 254                 \r
 255                 // Add data Filter\r
 256                 if (doReverse) {\r
 257                         // Table record in reverse order (last row first)\r
 258                         url = url.concat("?reverse=true");\r
 259                         if (sq != null) {\r
 260                                 // Structured Query\r
 261                                 url = url.concat("&sq=" + sq);\r
 262                         }\r
 263                         if (orderBy != null) {\r
 264                                 // Order By Statement\r
 265                                 url = url.concat("&orderby=" + orderBy);\r
 266                         }\r
 267                 } else {\r
 268                         // Structured Query & Order By\r
 269                         if (sq != null) {\r
 270                                 url = url.concat("?sq=" + sq);\r
 271                                 if(orderBy != null)\r
 272                                         url = url.concat("&orderby=" + orderBy);\r
 273                         } else {\r
 274                                 if (orderBy != null)\r
 275                                         url = url.concat("?orderby="+ orderBy);\r
 276                         }\r
 277                 }\r
 278                 \r
 279                 Response res = http.doConnect(url, HttpConHandler.HTTP_GET, httpHeaders, null);\r
 280                 \r
 281                 if(res.isError()){\r
 282                         return null;\r
 283                 }\r
 284                 \r
 285                 String xmlOut = res.getOutput();\r
 286                 \r
 287                 // XML Parsing\r
 288                 ParseFeed pf = new ParseFeed();\r
 289                 Feed f = pf.parse(xmlOut.getBytes());\r
 290                 ArrayList<Entry> entries = f.getEntries();\r
 291                 \r
 292                 // [Fix_11_JAN_2011_Prasanta multiple call to getRecords() while changing Android orientation\r
 293                 records.clear();\r
 294                 // Fix_11_JAN_2011_Prasanta]\r
 295                 \r
 296                 if(entries != null){\r
 297                         for(int i=0; i<entries.size(); i++){\r
 298                                 Entry e = entries.get(i);\r
 299                                 WorkSheetRow row = new WorkSheetRow();\r
 300                                 row.setId(e.getId());\r
 301                                 row.setCells(e.getCells());\r
 302                                 \r
 303                                 // Add to records\r
 304                                 records.add(row);\r
 305                         }\r
 306                 }\r
 307                 \r
 308                 return records;\r
 309         }\r
 310         \r
 311         /**\r
 312          * Add List Feed row. One row at a time.\r
 313          * TODO: need to test\r
 314          * @param records\r
 315          */\r
 316         public WorkSheetRow addListRow(HashMap<String, String> records){\r
 317                 /*\r
 318                  * It will send request to WorkSheet URL:\r
 319                  * https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full\r
 320                  * Unlike Tables, there can be only one List Feed associated with a given WorkSheet\r
 321                  */\r
 322                 \r
 323                 StringBuffer listRecordXML = new StringBuffer();\r
 324                 listRecordXML.append("<entry xmlns=\"http://www.w3.org/2005/Atom\" xmlns:gsx=\"http://schemas.google.com/spreadsheets/2006/extended\">");\r
 325                 \r
 326                 Iterator<String> ks = records.keySet().iterator();\r
 327                 while(ks.hasNext()){\r
 328                         String colName = ks.next();\r
 329                         String value = records.get(colName);\r
 330                         listRecordXML.append(" <gsx:"+ colName +">"+ value +"</gsx:"+ colName +">");\r
 331                 }\r
 332                 listRecordXML.append("</entry>");\r
 333                 \r
 334                 // Do server transaction\r
 335                 // Add Headers\r
 336                 HashMap<String, String> httpHeaders = new HashMap<String, String>();\r
 337                 httpHeaders.put(HttpConHandler.AUTHORIZATION_HTTP_HEADER, "GoogleLogin auth="+ SpreadSheetFactory.authToken);\r
 338                 httpHeaders.put(HttpConHandler.GDATA_VERSION_HTTP_HEADER, "3.0");\r
 339                 httpHeaders.put(HttpConHandler.CONTENT_TYPE_HTTP_HEADER, "application/atom+xml");\r
 340                 \r
 341                 HttpConHandler http = new HttpConHandler();\r
 342                 Response res = http.doConnect(workSheetURL, HttpConHandler.HTTP_POST, httpHeaders, listRecordXML.toString());\r
 343                 \r
 344                 // Add the into local Record cache\r
 345                 // Parse response and create new Row instance\r
 346                 if(res.isError()){\r
 347                         Log.p(TAG, "Error in updating List Row...");\r
 348                         return null;\r
 349                 }\r
 350                 \r
 351                 String xmlOut = res.getOutput();\r
 352                 \r
 353                 // XML Parsing\r
 354                 ParseFeed pf = new ParseFeed();\r
 355                 Feed f = pf.parse(xmlOut.getBytes());\r
 356                 ArrayList<Entry> entries = f.getEntries();\r
 357                 \r
 358                 if(entries == null || entries.size() == 0){\r
 359                         Log.p(TAG, "Error in parsing...");\r
 360                         return null;\r
 361                 }\r
 362                 \r
 363                 Entry e = entries.get(0);\r
 364                 WorkSheetRow row = new WorkSheetRow();\r
 365                 row.setId(e.getId());\r
 366                 row.setCells(e.getCells());\r
 367                 \r
 368                 // Update local Cache\r
 369                 if(this.records == null){\r
 370                         this.records = new ArrayList<WorkSheetRow>();\r
 371                         this.records.add(row);\r
 372                         return row;\r
 373                 }\r
 374                 // Update existing Row instance\r
 375                 this.records.add(row);\r
 376                 return row;\r
 377         }\r
 378         \r
 379         /**\r
 380          * Delete List Row\r
 381          * @param r WorkSheetRow which need to be deleted\r
 382          */\r
 383         public void deleteListRow(String key, WorkSheetRow r)\r
 384         {\r
 385                 if(r == null){\r
 386                         Log.p(TAG, "WorkSheetRow is null...");\r
 387                         return;\r
 388                 }\r
 389                 \r
 390                 // Sample DELETE URL- https://spreadsheets.google.com/feeds/list/tmG5DprMeR-l2j91JQBB1TQ/odp/private/full/cokwr\r
 391                 String listRowURL = "https://spreadsheets.google.com/feeds/list/"+ key +"/"+ workSheetID + "/private/full/"+ r.getRowIndex();\r
 392                 \r
 393                 // Do server transaction\r
 394                 // Add Headers\r
 395                 HashMap<String, String> httpHeaders = new HashMap<String, String>();\r
 396                 httpHeaders.put(HttpConHandler.AUTHORIZATION_HTTP_HEADER, "GoogleLogin auth="+ SpreadSheetFactory.authToken);\r
 397                 httpHeaders.put(HttpConHandler.GDATA_VERSION_HTTP_HEADER, "3.0");\r
 398                 //If you want to delete the row regardless of whether someone else has updated it since you retrieved it\r
 399                 httpHeaders.put("If-Match", "*");\r
 400                 \r
 401                 HttpConHandler http = new HttpConHandler();\r
 402                 http.doConnect(listRowURL, HttpConHandler.HTTP_DELETE, httpHeaders, null);\r
 403                 \r
 404                 // Delete this entry from local record list\r
 405                 records.remove(r);\r
 406         }\r
 407         \r
 408         /**\r
 409          * Update List row\r
 410          * @param rowID ID of the List row to be updated\r
 411          * @param records records need to be updated\r
 412          */\r
 413 \r
 414         /**\r
 415          * Update List row\r
 416          * \r
 417          * @param r WorkSheetRow to be updated\r
 418          * @param records new records\r
 419          * @return\r
 420          */\r
 421         public WorkSheetRow updateListRow(String key, WorkSheetRow r, HashMap<String, String> records)\r
 422         {\r
 423                 if(r == null){\r
 424                         Log.p(TAG, "WorkSheetRow is null...");\r
 425                         return r;\r
 426                 }\r
 427                 \r
 428                 // Sample PUT URL- https://spreadsheets.google.com/feeds/list/tmG5DprMeR-l2j91JQBB1TQ/odp/private/full/cokwr\r
 429                 String listRowURL = "https://spreadsheets.google.com/feeds/list/"+ key +"/"+ workSheetID + "/private/full/"+ r.getRowIndex();\r
 430                 \r
 431                 StringBuffer rowUpdateXML = new StringBuffer("<entry xmlns=\"http://www.w3.org/2005/Atom\" xmlns:gsx=\"http://schemas.google.com/spreadsheets/2006/extended\">");\r
 432                 \r
 433                 Iterator<String> ks = records.keySet().iterator();\r
 434                 while(ks.hasNext()){\r
 435                         String colName = ks.next();\r
 436                         String value = records.get(colName);\r
 437                         rowUpdateXML.append(" <gsx:"+ colName +">"+ value +"</gsx:"+ colName +">");\r
 438                 }\r
 439                 rowUpdateXML.append("</entry>");\r
 440                 \r
 441                 // Do server transaction- PUT\r
 442                 // Add Headers\r
 443                 HashMap<String, String> httpHeaders = new HashMap<String, String>();\r
 444                 httpHeaders.put(HttpConHandler.AUTHORIZATION_HTTP_HEADER, "GoogleLogin auth="+ SpreadSheetFactory.authToken);\r
 445                 httpHeaders.put(HttpConHandler.GDATA_VERSION_HTTP_HEADER, "3.0");\r
 446                 httpHeaders.put(HttpConHandler.CONTENT_TYPE_HTTP_HEADER, "application/atom+xml");\r
 447                 //If you want to delete the row regardless of whether someone else has updated it since you retrieved it\r
 448                 httpHeaders.put("If-Match", "*");\r
 449                 \r
 450                 HttpConHandler http = new HttpConHandler();\r
 451                 Response res = http.doConnect(listRowURL, HttpConHandler.HTTP_PUT, httpHeaders, rowUpdateXML.toString());\r
 452                 \r
 453                 // Parse response and create new Row instance\r
 454                 if(res.isError()){\r
 455                         Log.p(TAG, "Error in updating List Row...");\r
 456                         return null;\r
 457                 }\r
 458                 \r
 459                 String xmlOut = res.getOutput();\r
 460                 \r
 461                 // XML Parsing\r
 462                 ParseFeed pf = new ParseFeed();\r
 463                 Feed f = pf.parse(xmlOut.getBytes());\r
 464                 ArrayList<Entry> entries = f.getEntries();\r
 465                 \r
 466                 if(entries == null || entries.size() == 0){\r
 467                         Log.p(TAG, "Error in parsing...");\r
 468                         return null;\r
 469                 }\r
 470                 \r
 471                 Entry e = entries.get(0);\r
 472                 WorkSheetRow row = new WorkSheetRow();\r
 473                 row.setId(e.getId());\r
 474                 row.setCells(e.getCells());\r
 475                 \r
 476                 // Update local Cache\r
 477                 if(this.records == null){\r
 478                         this.records = new ArrayList<WorkSheetRow>();\r
 479                         this.records.add(row);\r
 480                         return row;\r
 481                 }\r
 482                 // Update existing Row instance\r
 483                 this.records.set(this.records.indexOf(row), row);\r
 484                 return row;\r
 485         }\r
 486 \r
 487         /**\r
 488          * Add record into WorkSheet. This WorkSheet need to have associated Table.\r
 489          * \r
 490          * Use SpreadSheet.addWorkSheet()\r
 491          * </br>\r
 492          * <b>Note:</b>\r
 493          * </br>\r
 494          * All data entered through Web Interface will be ignored.\r
 495          * \r
 496          * @param key Key of SpreadSheet\r
 497          * @param records Record to be added ([col_name],[value])\r
 498          */\r
 499         public void addRecord(String key, HashMap<String, String> records){\r
 500                 if(table == null){\r
 501                         throw new IllegalAccessError("This WorkSheet doesn't have any Table");\r
 502                 }\r
 503                 \r
 504                 Log.p(TAG, "Associated Table ID: "+ table.getId());\r
 505                 String tableRecordURL = "https://spreadsheets.google.com/feeds/"+ key +"/records/"+ table.getId();\r
 506                 \r
 507                 String tableRecordXML = "<entry xmlns='http://www.w3.org/2005/Atom' xmlns:gs='http://schemas.google.com/spreadsheets/2006'>"+\r
 508                                                                 "<title>"+ table.getName() +"</title>";\r
 509                 \r
 510                 Set keys = records.keySet();\r
 511                 Iterator<String> it = keys.iterator();\r
 512                 \r
 513                 while(it.hasNext()){\r
 514                         String k = it.next();\r
 515                         tableRecordXML = tableRecordXML.concat("<gs:field name='"+ k +"'>"+ records.get(k) +"</gs:field>");\r
 516                 }\r
 517                 \r
 518                 tableRecordXML = tableRecordXML.concat("</entry>");\r
 519                 \r
 520                 Log.p(TAG, "Table Record XML: "+ tableRecordXML);\r
 521                 \r
 522                 // Add Headers\r
 523                 HashMap<String, String> httpHeaders = new HashMap<String, String>();\r
 524                 httpHeaders.put(HttpConHandler.AUTHORIZATION_HTTP_HEADER, "GoogleLogin auth="+ SpreadSheetFactory.authToken);\r
 525                 httpHeaders.put(HttpConHandler.GDATA_VERSION_HTTP_HEADER, "3.0");\r
 526                 httpHeaders.put(HttpConHandler.CONTENT_TYPE_HTTP_HEADER, "application/atom+xml");\r
 527                 \r
 528                 HttpConHandler http = new HttpConHandler();\r
 529                 http.doConnect(tableRecordURL, HttpConHandler.HTTP_POST, httpHeaders, tableRecordXML);\r
 530         }\r
 531         \r
 532 \r
 533         /**\r
 534          * Get data stored in this WorkSheet\r
 535          * \r
 536          * @param key SpreadSheet Key\r
 537          * @return\r
 538          */\r
 539         public ArrayList<Record> getRecords(String key){\r
 540                 return getRecords(key, false, null, null);\r
 541         }\r
 542         \r
 543         /**\r
 544          * @param key SpreadSheet Key\r
 545          * @param sq  <a href="http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#SendingStructuredRowQueries">Structured Query</a> \r
 546          * \r
 547          * @return\r
 548          */\r
 549         public ArrayList<Record> getRecords(String key, String sq){\r
 550                 return getRecords(key, false, sq, null);\r
 551         }\r
 552 \r
 553 \r
 554         /**\r
 555          * Get data stored in this WorkSheet. Retrieved data will be in a HashMap-\r
 556          * <COL_NAME>,<VALUE>\r
 557          * <br/>\r
 558          * It supports following conditional Query- <br/>\r
 559          * <b>Structured Query:</b> you can define conditional statements like in SQL e.g. <col_name> != <value><br/>\r
 560          * <b>Order By:</b> Order by a given column name or position <br/>\r
 561          * <b>Reverse:</b> Record retrived in reverse order (last row 1st)<br/>\r
 562          * <br/>\r
 563          * <b>NOTE:</b>\r
 564          * <br/>\r
 565          * It will retrieve records present in the Table. Tables can't be accessed by the Web UI.\r
 566          * <br/>\r
 567          * So, any data inserted by Web UI will be discarded.\r
 568          * <br/>\r
 569          * @param key SpreadSheet Key\r
 570          * @param doReverse Display data in reverse order (last row first)\r
 571          * @param sq <a href="http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#SendingStructuredRowQueries">Structured Query</a>\r
 572          * @param orderBy <a href="http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#SendingStructuredRowQueries">Order By</a>\r
 573          * @return\r
 574          */\r
 575         public ArrayList<Record> getRecords(String key, boolean doReverse, String sq, String orderBy){\r
 576                 \r
 577                 if(table == null){\r
 578                         Log.p(TAG, "No Associated Table to Hold Data!!");\r
 579                         return null;\r
 580                 }\r
 581                 \r
 582                 // Table Access URL\r
 583                 String tableRecordURL = "https://spreadsheets.google.com/feeds/"+ key +"/records/"+ table.getId();\r
 584                 \r
 585                 if(doReverse){\r
 586                         // Table record in reverse order (last row first)\r
 587                         tableRecordURL = tableRecordURL.concat("?reverse=true");\r
 588                         if(sq != null){\r
 589                                 // Structured Query\r
 590                                 tableRecordURL = tableRecordURL.concat("&sq="+ sq);\r
 591                         }\r
 592                         if(orderBy != null){\r
 593                                 // Order By Statement\r
 594                                 tableRecordURL = tableRecordURL.concat("&orderby="+ orderBy);\r
 595                         }\r
 596                 }\r
 597                 else{\r
 598                         // Structured Query & Order By\r
 599                         if(sq != null){\r
 600                                 tableRecordURL = tableRecordURL.concat("?sq="+ sq);\r
 601                                 if(orderBy != null)\r
 602                                         tableRecordURL = tableRecordURL.concat("&orderby="+ orderBy);\r
 603                         }else{\r
 604                                 if(orderBy != null)\r
 605                                         tableRecordURL = tableRecordURL.concat("?orderby="+ orderBy);\r
 606                         }\r
 607                 }\r
 608                 \r
 609                 Log.p(TAG, "tableRecordURL="+ tableRecordURL);\r
 610                 \r
 611                 // Add Headers\r
 612                 HashMap<String, String> httpHeaders = new HashMap<String, String>();\r
 613                 httpHeaders.put(HttpConHandler.AUTHORIZATION_HTTP_HEADER, "GoogleLogin auth="+ SpreadSheetFactory.authToken);\r
 614                 httpHeaders.put(HttpConHandler.GDATA_VERSION_HTTP_HEADER, "3.0");\r
 615                 \r
 616                 HttpConHandler http = new HttpConHandler();\r
 617                 Response res = http.doConnect(tableRecordURL, HttpConHandler.HTTP_GET, httpHeaders, null);\r
 618                 \r
 619                 if(res.isError()){\r
 620                         return null;\r
 621                 }\r
 622                 \r
 623                 String xmlOut = res.getOutput();\r
 624                 \r
 625                 // XML Parsing\r
 626                 ParseFeed pf = new ParseFeed();\r
 627                 Feed f = pf.parse(xmlOut.getBytes());\r
 628                 ArrayList<Entry> entries = f.getEntries();\r
 629                 \r
 630                 if(entries == null){\r
 631                         Log.p(TAG, "No Reord found!!");\r
 632                         return null;\r
 633                 }\r
 634                 \r
 635                 // [Fix_11_JAN_2011_Prasanta multiple call to getRecords() while changing Android orientation\r
 636                 table.clearData();\r
 637                 // Fix_11_JAN_2011_Prasanta]\r
 638                 \r
 639                 for(int i=0; i< entries.size(); i++){\r
 640                         Entry e = entries.get(i);\r
 641                         ArrayList<Field> fields = e.getFields();\r
 642                         Record r = new Record();\r
 643                         r.setId(e.getId());\r
 644                         r.setEditURL(e.getEditLink());\r
 645                         \r
 646                         for(int j=0; j<fields.size(); j++){\r
 647                                 Field fd = fields.get(j);\r
 648                                 r.addData(fd.getColName(), fd.getValue());\r
 649                         }\r
 650                         table.addRecord(r);\r
 651                 }\r
 652                 \r
 653                 return table.getRecords();\r
 654         }\r
 655         \r
 656         /**\r
 657          * Get Cached Data (previously retrieved from server)\r
 658          * @return\r
 659          */\r
 660         public ArrayList<Record> getRecords(){\r
 661                 return table.getRecords();\r
 662         }\r
 663         \r
 664         /**\r
 665          * Record instance you want to Update\r
 666          * Keep only those data which you want to update for this record\r
 667          * \r
 668          * @param record Record to be updated\r
 669          */\r
 670         public void updateRecord(Record record){\r
 671                 \r
 672                 if(record == null){\r
 673                         throw new IllegalAccessError("Pass a valid Record!!");\r
 674                 }\r
 675                 \r
 676                 String recordXML = "<entry xmlns='http://www.w3.org/2005/Atom' xmlns:gs='http://schemas.google.com/spreadsheets/2006'>"+\r
 677                                                   "<id>"+ record.getId() +"</id>";\r
 678                 \r
 679                 HashMap<String, String> data = record.getData();\r
 680                 \r
 681                 Set keys = data.keySet();\r
 682                 Iterator<String> it = keys.iterator();\r
 683                 \r
 684                 while(it.hasNext()){\r
 685                         String k = it.next();\r
 686                         recordXML = recordXML.concat("<gs:field name='"+ k +"'>"+ data.get(k) +"</gs:field>");\r
 687                 }\r
 688                 \r
 689                 recordXML += "</entry>";\r
 690                 \r
 691                 Log.p(TAG, "Update Record XML: "+ recordXML);\r
 692                 \r
 693                 // Add Headers\r
 694                 HashMap<String, String> httpHeaders = new HashMap<String, String>();\r
 695                 httpHeaders.put(HttpConHandler.AUTHORIZATION_HTTP_HEADER, "GoogleLogin auth="+ SpreadSheetFactory.authToken);\r
 696                 httpHeaders.put(HttpConHandler.GDATA_VERSION_HTTP_HEADER, "3.0");\r
 697                 httpHeaders.put(HttpConHandler.CONTENT_TYPE_HTTP_HEADER, "application/atom+xml");\r
 698                 httpHeaders.put("If-Match", "*");\r
 699                 \r
 700                 // HTTP Connection\r
 701                 HttpConHandler http = new HttpConHandler();\r
 702                 http.doConnect(record.getEditURL(), HttpConHandler.HTTP_PUT, httpHeaders, recordXML);\r
 703         }\r
 704         \r
 705         /**\r
 706          * Delete a Record\r
 707          * @param record Record to be deleted\r
 708          */\r
 709         public void deleteRecord(Record record){\r
 710                 \r
 711                 if(record == null){\r
 712                         throw new IllegalAccessError("Pass a valid Record!!");\r
 713                 }\r
 714                 \r
 715                 // Add Headers\r
 716                 HashMap<String, String> httpHeaders = new HashMap<String, String>();\r
 717                 httpHeaders.put(HttpConHandler.AUTHORIZATION_HTTP_HEADER, "GoogleLogin auth="+ SpreadSheetFactory.authToken);\r
 718                 httpHeaders.put(HttpConHandler.GDATA_VERSION_HTTP_HEADER, "3.0");\r
 719                 httpHeaders.put("If-Match", "*");\r
 720                 \r
 721                 // HTTP Connection\r
 722                 HttpConHandler http = new HttpConHandler();\r
 723                 http.doConnect(record.getEditURL(), HttpConHandler.HTTP_DELETE, httpHeaders, null);\r
 724         }\r
 725         \r
 726         /**\r
 727          * Delete this WorkSheet. It will also delete its associated Table.\r
 728          * There is no separate method for deleting a Table\r
 729          */\r
 730         public void delete(){\r
 731                 // Sample URL: DELETE https://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId/version\r
 732                 // We don't need "version"\r
 733                 \r
 734                 // Add Headers\r
 735                 HashMap<String, String> httpHeaders = new HashMap<String, String>();\r
 736                 httpHeaders.put(HttpConHandler.AUTHORIZATION_HTTP_HEADER, "GoogleLogin auth="+ SpreadSheetFactory.authToken);\r
 737                 httpHeaders.put(HttpConHandler.GDATA_VERSION_HTTP_HEADER, "3.0");\r
 738                 httpHeaders.put("If-Match", "*");\r
 739                 \r
 740                 HttpConHandler http = new HttpConHandler();\r
 741                 String wsDeleteURL = entry.getId().substring(0, entry.getId().lastIndexOf("/"));\r
 742                 wsDeleteURL = wsDeleteURL.concat("/private/full/").concat(workSheetID);\r
 743                 \r
 744                 http.doConnect(wsDeleteURL, HttpConHandler.HTTP_DELETE, httpHeaders, null);\r
 745         }\r
 746 }\r