← Back to branch summary

~mortenoh/+junk/dhis2-detailed-import-export

« back to all changes in this revision

Viewing changes to local/in/dhis-web-ga/WEB-INF/src/org/hisp/gtool/action/DataMartAction.java

  • Committer: larshelge at gmail
  • Date: 2009-03-03 16:46:36 UTC
  • Revision ID: larshelge@gmail.com-20090303164636-2sjlrquo7ib1gf7r
Initial check-in

Show diffs side-by-side

added added

removed removed

Lines of Context:
local/in/dhis-web-ga/WEB-INF/src/org/hisp/gtool/action/DataMartAction.java
 
1
package org.hisp.gtool.action;
 
2
 
 
3
import java.sql.Connection;
 
4
import java.sql.PreparedStatement;
 
5
import java.sql.ResultSet;
 
6
import java.sql.Statement;
 
7
import java.util.ArrayList;
 
8
import java.util.Iterator;
 
9
import java.util.List;
 
10
 
 
11
public class DataMartAction {
 
12
                
 
13
        Connection con = (new DBConnection()).openConnection();
 
14
                        
 
15
        /*
 
16
         * Returns the Period Ids for the periods between startdate and enddate
 
17
         */
 
18
        public List getPeriodIDsList(String startDate,String endDate)
 
19
        {
 
20
                Statement st = null;
 
21
                ResultSet rs = null;
 
22
                
 
23
                /*
 
24
                String query = "SELECT id FROM period " +
 
25
                                                        "WHERE startdate BETWEEN '"+startDate+"' AND '"+endDate+"'";            
 
26
                */
 
27
                String query = "SELECT periodid FROM period " +
 
28
                                                        "WHERE startdate BETWEEN '"+startDate+"' AND '"+endDate+"'";
 
29
                
 
30
                List periodIDsList = new ArrayList();
 
31
                try
 
32
                {
 
33
                        st = con.createStatement();
 
34
                        rs = st.executeQuery(query);                    
 
35
                        while(rs.next())
 
36
                                {       
 
37
                                periodIDsList.add(new Integer(rs.getInt(1))); 
 
38
                                }                                                       
 
39
                } // try block end
 
40
                catch(Exception e)      {       return null;    }
 
41
                finally
 
42
                {
 
43
                        try
 
44
                        {
 
45
                                if(rs!=null) rs.close();
 
46
                                if(st!=null) st.close();
 
47
                        }
 
48
                        catch(Exception e){return null;}
 
49
                }// finally block end           
 
50
                return periodIDsList;
 
51
        }// getPeriodIDsList end
 
52
        
 
53
        
 
54
        /*
 
55
         * Returns the Indicator Ids 
 
56
         */
 
57
        public List getIndicatorIDsList(String ide_type,String[] indicatorNamesList)
 
58
        {
 
59
                
 
60
                //Statement st = null;
 
61
                PreparedStatement ps = null;
 
62
                ResultSet rs = null;
 
63
                                
 
64
                String query = "";
 
65
                
 
66
                if(ide_type.equals("indicatorsRadio"))
 
67
                {
 
68
                        /*
 
69
                        query = "SELECT id FROM indicator "+                    
 
70
                                                        "WHERE name LIKE ?";
 
71
                        */
 
72
                        query = "SELECT indicatorid FROM indicator "+                   
 
73
                                                        "WHERE name LIKE ?";
 
74
                }
 
75
                else
 
76
                {
 
77
                        /*
 
78
                        query = "SELECT id FROM dataelement " +
 
79
                                                        "WHERE shortname LIKE ?";
 
80
                        */
 
81
                        query = "SELECT dataelementid FROM dataelement " +
 
82
                                                        "WHERE alternativeName LIKE ?";
 
83
                }
 
84
                                                                
 
85
                List indicatorIDsList = new ArrayList();
 
86
                try
 
87
                {
 
88
                        //st = con.createStatement();
 
89
                        ps = con.prepareStatement(query);
 
90
                        
 
91
                        System.out.println(indicatorNamesList.length);
 
92
                        int count1 = 0;
 
93
                        while(count1 < indicatorNamesList.length)
 
94
                        {       
 
95
                                //rs = st.executeQuery(query);
 
96
                                
 
97
                                ps.setString(1,indicatorNamesList[count1]);
 
98
                                rs = ps.executeQuery();                         
 
99
                                if(rs.next())   indicatorIDsList.add(new Integer(rs.getInt(1)));                                        
 
100
                                else indicatorIDsList.add(new Integer(0));
 
101
                                count1++;
 
102
                        } // while loop end                                                     
 
103
                } // try block end
 
104
                catch(Exception e)      {       return null;    }
 
105
                finally
 
106
                {
 
107
                        try
 
108
                        {
 
109
                                if(rs!=null) rs.close();
 
110
                                //if(st!=null) st.close();
 
111
                                if(ps!=null) ps.close();
 
112
                        }
 
113
                        catch(Exception e){return null;}
 
114
                }// finally block end           
 
115
                return indicatorIDsList;
 
116
        }// getIndicatorIDsList end
 
117
        
 
118
        
 
119
        /*
 
120
         * Returns the level number of selected orgunit
 
121
         */
 
122
        public int getOrgUnitLevel(int orgUnitID)
 
123
        {
 
124
                Statement st = null;
 
125
                ResultSet rs = null;
 
126
                
 
127
                int orgUnitLevel = 0;
 
128
                /*
 
129
                String query = "SELECT level FROM organisationunitstructure " +
 
130
                                                                "WHERE organisationUnitId = "+orgUnitID;
 
131
                */
 
132
                String query = "SELECT level FROM orgunitstructure " +
 
133
                                                                "WHERE organisationunitid = "+orgUnitID;
 
134
                try
 
135
                {
 
136
                        st = con.createStatement();
 
137
                        rs = st.executeQuery(query);                    
 
138
                        if(rs.next())   {       orgUnitLevel = rs.getInt(1); }                                                  
 
139
                } // try block end
 
140
                catch(Exception e)      {       return 0;       }
 
141
                finally
 
142
                {
 
143
                        try
 
144
                        {
 
145
                                if(rs!=null) rs.close();
 
146
                                if(st!=null) st.close();
 
147
                        }
 
148
                        catch(Exception e){return 0;}
 
149
                }// finally block end           
 
150
                return orgUnitLevel;            
 
151
        } // getOrgUnitLevel end
 
152
        
 
153
        
 
154
        /*
 
155
         * Returns the Maximum level number of selected orgunit
 
156
         */
 
157
        public int getMaxOrgUnitLevel()
 
158
        {
 
159
                Statement st = null;
 
160
                ResultSet rs = null;
 
161
                
 
162
                int maxOrgUnitLevel = 0;
 
163
                
 
164
                //String query = "SELECT MAX(level) FROM organisationunitstructure";
 
165
                String query = "SELECT MAX(level) FROM orgunitstructure";
 
166
                
 
167
                try
 
168
                {
 
169
                        st = con.createStatement();
 
170
                        rs = st.executeQuery(query);                    
 
171
                        if(rs.next())   {       maxOrgUnitLevel = rs.getInt(1); }                                                       
 
172
                } // try block end
 
173
                catch(Exception e)      {       return 0;       }
 
174
                finally
 
175
                {
 
176
                        try
 
177
                        {
 
178
                                if(rs!=null) rs.close();
 
179
                                if(st!=null) st.close();
 
180
                        }
 
181
                        catch(Exception e){return 0;}
 
182
                }// finally block end           
 
183
                return maxOrgUnitLevel;         
 
184
        } // getMaxOrgUnitLevel end
 
185
        
 
186
        
 
187
        
 
188
        /*
 
189
         * Returns the maximum id in the aggregatedindicatorvalue OR aggregateddatavalue table
 
190
         */
 
191
        public int getMaxIDofAggIndValue(String ide_type)
 
192
        {
 
193
                Statement st = null;
 
194
                ResultSet rs = null;
 
195
                
 
196
                int maXAggIndValueID = 0;
 
197
                String query = "";
 
198
                if(ide_type.equals("indicatorsRadio"))
 
199
                {
 
200
                        query = "SELECT MAX(id) FROM aggregatedindicatorvalue";
 
201
                }
 
202
                else
 
203
                {
 
204
                        query = "SELECT MAX(id) FROM aggregateddatavalue";
 
205
                }
 
206
                                
 
207
                try
 
208
                {
 
209
                        st = con.createStatement();
 
210
                        rs = st.executeQuery(query);                    
 
211
                        if(rs.next())   {       maXAggIndValueID = rs.getInt(1); }                                                      
 
212
                } // try block end
 
213
                catch(Exception e)      {       return 0;       }
 
214
                finally
 
215
                {
 
216
                        try
 
217
                        {
 
218
                                if(rs!=null) rs.close();
 
219
                                if(st!=null) st.close();
 
220
                        }
 
221
                        catch(Exception e){ return 0;}
 
222
                }// finally block end           
 
223
                return maXAggIndValueID;                
 
224
        } // getMaxIDofAggIndValue end
 
225
        
 
226
        
 
227
        /*
 
228
         * This returns the list of organisationunit ids as an array list 
 
229
         *      which are at input level 
 
230
         */
 
231
        public List getOUListForLevel(int selOULevel)
 
232
        {
 
233
                Statement st = null;
 
234
                ResultSet rs = null;
 
235
                
 
236
                List li = new ArrayList();
 
237
                /*
 
238
                String query = "SELECT organisationUnitId FROM organisationunitstructure " +
 
239
                                                        "WHERE level = "+selOULevel;
 
240
                */
 
241
                
 
242
                String query = "SELECT organisationunitid FROM orgunitstructure " +
 
243
                                                        "WHERE level = "+selOULevel;
 
244
 
 
245
                try
 
246
                {
 
247
                        st = con.createStatement();
 
248
                        rs = st.executeQuery(query);                    
 
249
                        while(rs.next())
 
250
                        {       
 
251
                                li.add(new Integer(rs.getInt(1))); 
 
252
                        }                                                       
 
253
                } // try block end
 
254
                catch(Exception e)      {       return null;    }
 
255
                finally
 
256
                {
 
257
                        try
 
258
                        {
 
259
                                if(rs!=null) rs.close();
 
260
                                if(st!=null) st.close();
 
261
                        }
 
262
                        catch(Exception e){ return null;}
 
263
                }// finally block end           
 
264
                return li;
 
265
        } // getOUListForLevel end
 
266
        
 
267
        
 
268
        public int aggregateIndicatorValues(
 
269
                                                int selOUID, 
 
270
                                                String startDate, 
 
271
                                                String endDate,
 
272
                                                String ide_type,
 
273
                                                String[] indNamesList)
 
274
        {
 
275
                
 
276
                int selOULevel = getOrgUnitLevel(selOUID);
 
277
                int maxOULevel = getMaxOrgUnitLevel();
 
278
                //int aggIndValueID = getMaxIDofAggIndValue(ide_type) + 1;
 
279
                int periodTypeID = 1;
 
280
                
 
281
                List ouList = (ArrayList) getOUListForLevel(selOULevel);
 
282
                List periodIDsList = (ArrayList) getPeriodIDsList(startDate, endDate);
 
283
                List indicatorIDsList = (ArrayList) getIndicatorIDsList(ide_type,indNamesList);
 
284
                
 
285
                PreparedStatement ps1 = null;
 
286
                PreparedStatement ps2 = null;
 
287
                PreparedStatement ps3 = null;
 
288
                
 
289
                ResultSet rs1 = null;
 
290
                ResultSet rs2 = null;
 
291
                
 
292
                if(ouList == null) {System.out.println("oulist is empty");}
 
293
                if(periodIDsList == null) {System.out.println("periodlist is empty");}
 
294
                if(indicatorIDsList == null) {System.out.println("indlist is empty");}
 
295
                
 
296
                int totRecordCount = 0;
 
297
                String query = "";
 
298
                try
 
299
                {
 
300
                        
 
301
                        if(ide_type.equals("indicatorsRadio"))
 
302
                        {
 
303
                                /*
 
304
                                query = "SELECT id FROM aggregatedindicatorvalue " +
 
305
                                                        "WHERE indicatorId = ? AND " +
 
306
                                                                "periodId= ? AND " +
 
307
                                                                "organisationUnitId  = ?";
 
308
                                */
 
309
                                query = "SELECT value FROM aggregatedindicatorvalue " +
 
310
                                                        "WHERE indicatorid = ? AND " +
 
311
                                                                        "periodid= ? AND " +
 
312
                                                                        "organisationunitid  = ?";
 
313
 
 
314
                        }
 
315
                        else
 
316
                        {
 
317
                                /*
 
318
                                query = "SELECT id FROM aggregateddatavalue " +
 
319
                                                        "WHERE dataElementId = ? AND " +
 
320
                                                                        "periodId= ? AND " +
 
321
                                                                        "organisationUnitId  = ?";
 
322
                                */                                      
 
323
                                query = "SELECT value FROM aggregateddatavalue " +
 
324
                                                        "WHERE dataelementid = ? AND " +
 
325
                                                                        "periodid= ? AND " +
 
326
                                                                        "organisationunitid  = ?";              
 
327
                        }
 
328
                        ps1 = con.prepareStatement(query);
 
329
                        
 
330
                        
 
331
                        if(ide_type.equals("indicatorsRadio"))
 
332
                        {
 
333
                                /*
 
334
                                query = "SELECT sum(value),count(*) FROM aggregatedindicatorvalue " +
 
335
                                                        "WHERE indicatorId=? AND " +
 
336
                                                                        "periodId=? AND " +
 
337
                                                                        "organisationUnitId IN (SELECT id FROM organisationunit WHERE parent = ?)";
 
338
                                */
 
339
                                query = "SELECT sum(value),count(*) FROM aggregatedindicatorvalue " +
 
340
                                                        "WHERE indicatorid=? AND " +
 
341
                                                                        "periodid=? AND " +
 
342
                                                                        "organisationunitid IN (SELECT organisationunitid FROM organisationunit WHERE parentid = ?)";
 
343
                        }
 
344
                        else
 
345
                        {
 
346
                                if(selOULevel == maxOULevel)
 
347
                                {
 
348
                                        /*
 
349
                                        query = "SELECT SUM(value),count(*) FROM datavalue " +
 
350
                                                                "WHERE dataElement=? AND " +
 
351
                                                                                "period=? AND " +
 
352
                                                                                "source = ?";
 
353
                                        */                                      
 
354
                                        query = "SELECT SUM(value),count(*) FROM datavalue " +
 
355
                                                                "WHERE dataelementid=? AND " +
 
356
                                                                                "periodid=? AND " +
 
357
                                                                                "sourceid = ?";                                 
 
358
                                }
 
359
                                else
 
360
                                {
 
361
                                        /*
 
362
                                        query = "SELECT sum(value),count(*) FROM aggregateddatavalue " +
 
363
                                                                "WHERE dataElementId=? AND " +
 
364
                                                                                "periodId=? AND " +
 
365
                                                                                "organisationUnitId IN (SELECT id FROM organisationunit WHERE parent = ?)";
 
366
                                        */
 
367
                                        query = "SELECT sum(value),count(*) FROM aggregateddatavalue " +
 
368
                                                                "WHERE dataelementid=? AND " +
 
369
                                                                                "periodid=? AND " +
 
370
                                                                                "organisationunitid IN (SELECT organisationunitid FROM organisationunit WHERE parentid = ?)";
 
371
                                }       
 
372
                        }
 
373
                        ps2 = con.prepareStatement(query);
 
374
                        
 
375
                        if(ide_type.equals("indicatorsRadio"))
 
376
                        {
 
377
                                /*
 
378
                                query = "INSERT IGNORE INTO aggregatedindicatorvalue " +
 
379
                                                        "(indicatorId,periodId,periodTypeId,organisationUnitId,level,value) " +
 
380
                                                        "values(?,?,?,?,?,?)";
 
381
                                */
 
382
                                query = "INSERT IGNORE INTO aggregatedindicatorvalue " +
 
383
                                                        "(indicatorid,periodid,periodtypeid,organisationunitid,level,value) " +
 
384
                                                        "VALUES(?,?,?,?,?,?)";
 
385
                        }
 
386
                        else
 
387
                        {
 
388
                                /*
 
389
                                query = "INSERT IGNORE INTO aggregateddatavalue " +
 
390
                                                        "(dataElementId,periodId,periodTypeId,organisationUnitId,level,value) " +
 
391
                                                        "values(?,?,?,?,?,?)";
 
392
                                */
 
393
                                query = "INSERT IGNORE INTO aggregateddatavalue " +
 
394
                                                        "(dataelementid,periodid,periodtypeid,organisationunitid,level,value) " +
 
395
                                                        "VALUES(?,?,?,?,?,?)";
 
396
                        }                       
 
397
                        ps3 = con.prepareStatement(query);
 
398
                        
 
399
                        
 
400
                        Iterator itForOUList = ouList.iterator();
 
401
                        while(itForOUList.hasNext())
 
402
                        {
 
403
                                int ouId = ((Integer)itForOUList.next()).intValue(); 
 
404
                                Iterator itForIndList = indicatorIDsList.iterator();
 
405
                                while(itForIndList.hasNext())
 
406
                                {
 
407
                                        int indId = ((Integer)itForIndList.next()).intValue();
 
408
                                        Iterator itForPeriodList = periodIDsList.iterator();
 
409
                                        while(itForPeriodList.hasNext())
 
410
                                        {
 
411
                                                int periodId = ((Integer)itForPeriodList.next()).intValue(); 
 
412
                                                
 
413
                                                ps1.setInt(1,indId);
 
414
                                                ps1.setInt(2,periodId);
 
415
                                                ps1.setInt(3,ouId);
 
416
                                                rs1 = ps1.executeQuery();
 
417
                                                if(rs1.next()) {}
 
418
                                                else
 
419
                                                {
 
420
                                                        ps2.setInt(1,indId);
 
421
                                                        ps2.setInt(2,periodId);
 
422
                                                        ps2.setInt(3,ouId);                                                     
 
423
                                                        rs2 = ps2.executeQuery();
 
424
                                                        if(rs2.next())
 
425
                                                        {
 
426
                                                                double aggIndValue = rs2.getDouble(1);
 
427
                                                                int childCount = rs2.getInt(2);
 
428
                                                                if(childCount != 0 && ide_type.equals("indicatorsRadio")) aggIndValue /= childCount;
 
429
                                                                if(aggIndValue !=0 )
 
430
                                                                {
 
431
                                                                        System.out.println(indId+"   "+periodId+"   "+periodTypeID+"   "+ouId+"   "+selOULevel+"   "+aggIndValue);
 
432
                                                                                                                                                
 
433
                                                                        ps3.setInt(1,indId);
 
434
                                                                        ps3.setInt(2,periodId);
 
435
                                                                        ps3.setInt(3,periodTypeID);
 
436
                                                                        ps3.setInt(4,ouId);
 
437
                                                                        ps3.setInt(5,selOULevel);
 
438
                                                                        ps3.setDouble(6,aggIndValue);
 
439
 
 
440
                                                                        ps3.executeUpdate();
 
441
                                                                        
 
442
                                                                        //aggIndValueID++;
 
443
                                                                        totRecordCount++;
 
444
                                                                }
 
445
                                                        } // rs2 if end
 
446
                                                }// rs1 else end
 
447
                                        }// Perios List While loop end
 
448
                                }// Indcator Names List while loop end
 
449
                        }// ouList while loop end
 
450
                }// try block end
 
451
                catch(Exception e) { System.out.println(e.getMessage()); return -1;}
 
452
                finally
 
453
                {
 
454
                        try
 
455
                        {
 
456
                                if(ps1 != null) ps1.close();
 
457
                                if(ps2 != null) ps2.close();
 
458
                                if(ps3 != null) ps3.close();
 
459
                                
 
460
                                if(rs1 != null) rs1.close();
 
461
                                if(rs2 != null) rs2.close();
 
462
                        }
 
463
                        catch(Exception e){     System.out.println(e.getMessage());     return -1;}
 
464
                }               
 
465
                
 
466
                return totRecordCount;
 
467
        }// aggregateIndicatorValues end
 
468
 
 
469
 
 
470
        /*
 
471
         * setters and getters
 
472
         */
 
473
        
 
474
} // class end