1 | /* |
---|
2 | * rapidandroid - SMS gateway for the android platform |
---|
3 | * Copyright (C) 2009 Dimagi Inc., UNICEF |
---|
4 | * |
---|
5 | * This program is free software: you can redistribute it and/or modify |
---|
6 | * it under the terms of the GNU General Public License as published by |
---|
7 | * the Free Software Foundation, either version 3 of the License, or |
---|
8 | * (at your option) any later version. |
---|
9 | * |
---|
10 | * This program is distributed in the hope that it will be useful, |
---|
11 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
---|
12 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
---|
13 | * GNU General Public License for more details. |
---|
14 | * |
---|
15 | * You should have received a copy of the GNU General Public License |
---|
16 | * along with this program. If not, see <http://www.gnu.org/licenses/>. |
---|
17 | * |
---|
18 | */ |
---|
19 | package org.rapidandroid.data.controller; |
---|
20 | |
---|
21 | import java.io.File; |
---|
22 | import java.io.FileInputStream; |
---|
23 | import java.io.FileOutputStream; |
---|
24 | import java.io.IOException; |
---|
25 | import java.text.ParseException; |
---|
26 | import java.util.Calendar; |
---|
27 | import java.util.Date; |
---|
28 | import java.util.zip.GZIPOutputStream; |
---|
29 | |
---|
30 | import org.rapidandroid.data.RapidSmsDBConstants; |
---|
31 | import org.rapidandroid.data.SmsDbHelper; |
---|
32 | import org.rapidsms.java.core.Constants; |
---|
33 | import org.rapidsms.java.core.model.Form; |
---|
34 | import org.rapidsms.java.core.model.Message; |
---|
35 | |
---|
36 | import android.content.Context; |
---|
37 | import android.database.Cursor; |
---|
38 | import android.database.sqlite.SQLiteDatabase; |
---|
39 | import android.os.Environment; |
---|
40 | |
---|
41 | /** |
---|
42 | * |
---|
43 | * @author Daniel Myung [email protected] |
---|
44 | * @created Jan 30, 2009 |
---|
45 | * |
---|
46 | */ |
---|
47 | public class ParsedDataReporter { |
---|
48 | |
---|
49 | private String[] messageColumns = new String[] { "message_time", "monitor_id", "monitor_phone", "message_text" }; |
---|
50 | |
---|
51 | public synchronized static Date getOldestMessageDate(Context context, Form f) { |
---|
52 | SmsDbHelper mHelper = new SmsDbHelper(context); |
---|
53 | Date toReturn = getOldestMessageDate(mHelper, f); |
---|
54 | mHelper.close(); |
---|
55 | return toReturn; |
---|
56 | |
---|
57 | } |
---|
58 | |
---|
59 | public synchronized static Date getOldestMessageDate(SmsDbHelper mHelper, Form f) { |
---|
60 | // TODO Auto-generated method stub |
---|
61 | StringBuilder query = new StringBuilder(); |
---|
62 | query.append("select min(rapidandroid_message.time) "); |
---|
63 | query.append(" from " + RapidSmsDBConstants.FormData.TABLE_PREFIX + f.getPrefix()); |
---|
64 | query.append(" join rapidandroid_message on ("); |
---|
65 | query.append(RapidSmsDBConstants.FormData.TABLE_PREFIX + f.getPrefix()); |
---|
66 | query.append(".message_id = rapidandroid_message._id"); |
---|
67 | query.append(") "); |
---|
68 | |
---|
69 | SQLiteDatabase db = mHelper.getReadableDatabase(); |
---|
70 | Cursor cr = db.rawQuery(query.toString(), null); |
---|
71 | if (cr.getCount() == 0) { |
---|
72 | cr.close(); |
---|
73 | db.close(); |
---|
74 | // this is the caller's responsibility |
---|
75 | // mHelper.close(); |
---|
76 | return Constants.NULLDATE; |
---|
77 | |
---|
78 | } |
---|
79 | cr.moveToFirst(); |
---|
80 | String dateString = cr.getString(0); |
---|
81 | |
---|
82 | if (dateString == null) { |
---|
83 | cr.close(); |
---|
84 | db.close(); |
---|
85 | // this is the caller's responsibility |
---|
86 | // mHelper.close(); |
---|
87 | return Constants.NULLDATE; |
---|
88 | } |
---|
89 | |
---|
90 | Date ret = new Date(); |
---|
91 | try { |
---|
92 | ret = Message.SQLDateFormatter.parse(dateString); |
---|
93 | } catch (ParseException e) { |
---|
94 | // TODO Auto-generated catch block |
---|
95 | |
---|
96 | e.printStackTrace(); |
---|
97 | try { |
---|
98 | if (cr != null) { |
---|
99 | cr.close(); |
---|
100 | } |
---|
101 | if (db != null) { |
---|
102 | db.close(); |
---|
103 | } |
---|
104 | if (mHelper != null) { |
---|
105 | // this is the caller's responsibility |
---|
106 | // mHelper.close(); |
---|
107 | } |
---|
108 | } catch (Exception ex2) { |
---|
109 | |
---|
110 | } |
---|
111 | } |
---|
112 | cr.close(); |
---|
113 | db.close(); |
---|
114 | // this is the caller's responsibility |
---|
115 | // mHelper.close(); |
---|
116 | return ret; |
---|
117 | } |
---|
118 | |
---|
119 | public synchronized static void exportFormDataToCSV(Context context, Form f, Calendar startDate, Calendar endDate) { |
---|
120 | SmsDbHelper mHelper = new SmsDbHelper(context); |
---|
121 | // build the query |
---|
122 | StringBuilder query = new StringBuilder(); |
---|
123 | query.append("select " + RapidSmsDBConstants.FormData.TABLE_PREFIX); |
---|
124 | query.append(f.getPrefix() + ".*"); |
---|
125 | query |
---|
126 | .append(", rapidandroid_message.message,rapidandroid_message.time, rapidandroid_monitor._id as monitor_id, rapidandroid_monitor.phone as monitor_phone "); |
---|
127 | query.append(" from " + RapidSmsDBConstants.FormData.TABLE_PREFIX + f.getPrefix()); |
---|
128 | query.append(" join rapidandroid_message on ("); |
---|
129 | query.append(RapidSmsDBConstants.FormData.TABLE_PREFIX + f.getPrefix()); |
---|
130 | query.append(".message_id = rapidandroid_message._id"); |
---|
131 | query.append(") "); |
---|
132 | |
---|
133 | query.append(" join rapidandroid_monitor on ("); |
---|
134 | query.append("rapidandroid_monitor._id = rapidandroid_message.monitor_id"); |
---|
135 | query.append(") "); |
---|
136 | |
---|
137 | query.append("WHERE rapidandroid_message.time > '" + startDate.get(Calendar.YEAR) + "-" |
---|
138 | + (startDate.get(Calendar.MONTH) + 1) + "-" + startDate.get(Calendar.DATE) + "' AND "); |
---|
139 | query.append(" rapidandroid_message.time < '" + endDate.get(Calendar.YEAR) + "-" |
---|
140 | + (1 + endDate.get(Calendar.MONTH)) + "-" + endDate.get(Calendar.DATE) + "';"); |
---|
141 | |
---|
142 | Cursor cr = mHelper.getReadableDatabase().rawQuery(query.toString(), null); |
---|
143 | |
---|
144 | File sdcard = Environment.getExternalStorageDirectory(); |
---|
145 | File destinationdir = new File(sdcard, "rapidandroid/exports"); |
---|
146 | destinationdir.mkdir(); |
---|
147 | Date now = new Date(); |
---|
148 | File destinationfile = new File(destinationdir, "formdata_" + f.getPrefix() + now.getYear() + now.getMonth() |
---|
149 | + now.getDate() + "-" + now.getHours() + now.getMinutes() + ".csv"); |
---|
150 | FileOutputStream fOut = null; |
---|
151 | try { |
---|
152 | destinationfile.createNewFile(); |
---|
153 | fOut = new FileOutputStream(destinationfile); |
---|
154 | String[] cols = cr.getColumnNames(); |
---|
155 | int colcount = cols.length; |
---|
156 | StringBuilder sbrow = new StringBuilder(); |
---|
157 | for (int i = 0; i < colcount; i++) { |
---|
158 | // sbrow.append(cols[i] + ","); |
---|
159 | sbrow.append(cols[i]); |
---|
160 | if (i < colcount - 1) { |
---|
161 | sbrow.append(","); |
---|
162 | } else { |
---|
163 | sbrow.append("\n"); |
---|
164 | } |
---|
165 | } |
---|
166 | fOut.write(sbrow.toString().getBytes()); |
---|
167 | cr.moveToFirst(); |
---|
168 | do { |
---|
169 | sbrow = new StringBuilder(); |
---|
170 | for (int i = 0; i < colcount; i++) { |
---|
171 | // sbrow.append(cr.getString(i) + ","); |
---|
172 | sbrow.append(cr.getString(i)); |
---|
173 | if (i < colcount - 1) { |
---|
174 | sbrow.append(","); |
---|
175 | } else { |
---|
176 | sbrow.append("\n"); |
---|
177 | } |
---|
178 | } |
---|
179 | fOut.write(sbrow.toString().getBytes()); |
---|
180 | } while (cr.moveToNext()); |
---|
181 | } catch (IOException e) { |
---|
182 | // TODO Auto-generated catch block |
---|
183 | e.printStackTrace(); |
---|
184 | } finally { |
---|
185 | cr.close(); |
---|
186 | mHelper.close(); |
---|
187 | if (fOut != null) { |
---|
188 | try { |
---|
189 | fOut.close(); |
---|
190 | } catch (IOException e) { |
---|
191 | // TODO Auto-generated catch block |
---|
192 | e.printStackTrace(); |
---|
193 | } |
---|
194 | } |
---|
195 | // compressFile(destinationfile); |
---|
196 | } |
---|
197 | |
---|
198 | } |
---|
199 | |
---|
200 | void compressFile(File rawFile) { |
---|
201 | FileInputStream fin = null; |
---|
202 | GZIPOutputStream gz = null; |
---|
203 | try { |
---|
204 | fin = new FileInputStream(rawFile); |
---|
205 | FileOutputStream fout = new FileOutputStream(rawFile.getAbsoluteFile() + ".gz"); |
---|
206 | gz = new GZIPOutputStream(fout); |
---|
207 | byte[] buf = new byte[4096]; |
---|
208 | int readCount; |
---|
209 | while ((readCount = fin.read(buf)) != -1) { |
---|
210 | gz.write(buf, 0, readCount); |
---|
211 | } |
---|
212 | |
---|
213 | } catch (Exception ex) { |
---|
214 | } finally { |
---|
215 | // Close the BufferedInputStream |
---|
216 | try { |
---|
217 | if (fin != null) |
---|
218 | fin.close(); |
---|
219 | if (gz != null) |
---|
220 | gz.close(); |
---|
221 | } catch (IOException ex) { |
---|
222 | ex.printStackTrace(); |
---|
223 | } |
---|
224 | |
---|
225 | } |
---|
226 | } |
---|
227 | } |
---|