← Back to branch summary

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

« back to all changes in this revision

Viewing changes to local/in/dhis-web-reports/src/main/webapp/dhis-web-reports/Gujarat/gmf-4.jsp

  • 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-reports/src/main/webapp/dhis-web-reports/Gujarat/gmf-4.jsp
 
1
 
 
2
<%@ page import="java.sql.*,java.util.*" %>
 
3
<%@ page import="com.opensymphony.xwork.util.OgnlValueStack" %>
 
4
 
 
5
<%@ page session="true"%>
 
6
 
 
7
<%
 
8
    Connection con=null;
 
9
            
 
10
    // For finding organisationunit name of selected Orgunit based on Orgunit id
 
11
    Statement st1=null;
 
12
    ResultSet rs1=null;
 
13
      
 
14
    // For finding start date of selected period based on period id
 
15
    Statement st2=null;
 
16
    ResultSet rs2=null;
 
17
 
 
18
    // For finding child orgunit ids and names based on selected orgunit id
 
19
    Statement st3=null;
 
20
    ResultSet rs3=null;
 
21
 
 
22
   // For finding child orgunit 
 
23
    Statement st4=null;
 
24
    ResultSet rs4=null;
 
25
   
 
26
   // For Taluk name
 
27
    Statement st5=null;
 
28
    ResultSet rs5=null;
 
29
 
 
30
   // For District name
 
31
    Statement st6=null;
 
32
    ResultSet rs6=null;
 
33
 
 
34
   // For State name
 
35
    Statement st7=null;
 
36
    ResultSet rs7=null;
 
37
  
 
38
    String userName = "dhis";      
 
39
    String password = "";           
 
40
    String urlForConnection = "jdbc:mysql://localhost/gj_dhis2";
 
41
          
 
42
        OgnlValueStack stack = (OgnlValueStack)request.getAttribute("webwork.valueStack");
 
43
 
 
44
        String selectedId = (String) stack.findValue( "orgUnitId" );
 
45
        int selectedOrgUnitID =         Integer.parseInt( selectedId );
 
46
        
 
47
//      String selectedPeriodId = (String) stack.findValue( "periodSelect" );
 
48
//      int selectedDataPeriodID =      Integer.parseInt( selectedPeriodId );
 
49
  
 
50
    String startingDate  = (String) stack.findValue( "startingPeriod" );
 
51
        String endingDate  = (String) stack.findValue( "endingPeriod" );
 
52
        
 
53
        String monthlyPeriodId = (String) stack.findValue( "monthlyPeriodTypeId" );
 
54
        int periodTypeID =      Integer.parseInt( monthlyPeriodId );
 
55
 
 
56
      
 
57
        String selectedOrgUnitName = "";        
 
58
        String selectedDataPeriodStartDate = "";
 
59
        String stateName = ""; 
 
60
        String talukName = "";
 
61
        String districtName = "";
 
62
        
 
63
        int talukID = 0;
 
64
        int districtID = 0;
 
65
        
 
66
        String monthNames[] = { "", "January", "February", "March", "April", "May", "June", "July", "August", "September","October", "November", "December" };                  
 
67
 
 
68
        String monthlyDataElements[] = {
 
69
        
 
70
                                                                                        "DE for Malaria Paradigm",
 
71
                                                                                        
 
72
                                                                                        "DE for Population",
 
73
                                                                                        
 
74
                                                                                        // Active BSC, BSE, +ve
 
75
                                                                                        "MAL_DE2","MAL_DE3","MAL_DE4",
 
76
 
 
77
                                                                                        // Mass Contact - Coll,Exam,+ve
 
78
                                                                                        "MAL_DE5","MAL_DE6","MAL_DE7",
 
79
                                                                                        
 
80
                                                                                        // Passive BSC, BSE, +ve 
 
81
                                                                                        "MAL_DE8","MAL_DE9","MAL_DE10",
 
82
                                                                                        
 
83
                                                                                        // Age wise Positive - (0-1), (1-4), (5-14), (15+)
 
84
                                                                                        "MAL_DE11","MAL_DE12","MAL_DE13","MAL_DE14",  
 
85
                                                                                        
 
86
                                                                                        // RT done - (0-1), (1-4), (5-14), (15+)
 
87
                                                                                        "MAL_DE15","MAL_DE16","MAL_DE17","MAL_DE18",
 
88
                                                                                        
 
89
                                                                                        // Deaths
 
90
                                                                                        "MAL_DE19","MAL_DE20"
 
91
                                                                                         
 
92
                                                                                        
 
93
                                                                                };
 
94
        
 
95
        List childOrgUnitIDs = new ArrayList(); 
 
96
        List childOrgUnitNames = new ArrayList();       
 
97
                
 
98
        int childOrgUnitCount = 0;      
 
99
        int count = 0;          
 
100
        int i=0;
 
101
        int totPopulation = 0;
 
102
                
 
103
        int tempval[] = new int[monthlyDataElements.length+5];
 
104
        int total[] = new int[monthlyDataElements.length+5];
 
105
%>
 
106
 
 
107
<%
 
108
     try
 
109
      {
 
110
        Class.forName ("com.mysql.jdbc.Driver").newInstance ();
 
111
        con = DriverManager.getConnection (urlForConnection, userName, password);
 
112
        
 
113
        st1=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
114
        st2=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
115
        st3=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
116
        st4=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
117
        st5=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
118
        st6=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
119
        st7=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
120
 
 
121
 
 
122
        //rs1 = st1.executeQuery("SELECT organisationunit.shortname FROM organisationunit WHERE id="+selectedOrgUnitID);
 
123
        rs1 = st1.executeQuery("SELECT organisationunit.shortname FROM organisationunit WHERE organisationunitid="+selectedOrgUnitID);        
 
124
        if(rs1.next())    {  selectedOrgUnitName = rs1.getString(1);    }
 
125
        else  {  selectedOrgUnitName = "";     }  
 
126
                
 
127
  //      rs2 = st2.executeQuery("select startDate from period where id = "+selectedDataPeriodID);
 
128
//          if(rs2.next())      {       selectedDataPeriodStartDate =  rs2.getDate(1).toString();       }  
 
129
        
 
130
                selectedDataPeriodStartDate = startingDate;
 
131
                
 
132
                //rs5=st5.executeQuery("select organisationunit.id, organisationunit.shortname FROM organisationunit WHERE organisationunit.id in ( select organisationunit.parent from organisationunit where organisationunit.id = "+selectedOrgUnitID+")");
 
133
                rs5=st5.executeQuery("select organisationunit.organisationunitid, organisationunit.shortname FROM organisationunit WHERE organisationunit.organisationunitid in ( select organisationunit.parentid from organisationunit where organisationunit.organisationunitid = "+selectedOrgUnitID+")"); 
 
134
                if(rs5.next()) {  talukID = rs5.getInt(1); talukName = rs5.getString(2);}
 
135
                else { talukID=0; talukName = "";}  
 
136
                                
 
137
                //rs6=st6.executeQuery("select organisationunit.id, organisationunit.shortname FROM organisationunit WHERE organisationunit.id in ( select organisationunit.parent from organisationunit where organisationunit.id = "+talukID+")");
 
138
                rs6=st6.executeQuery("select organisationunit.organisationunitid, organisationunit.shortname FROM organisationunit WHERE organisationunit.organisationunitid in ( select organisationunit.parentid from organisationunit where organisationunit.organisationunitid = "+talukID+")"); 
 
139
                if(rs6.next()) {  districtID = rs6.getInt(1);  districtName = rs6.getString(2); }
 
140
                else { districtID = 0; districtName = "";}  
 
141
 
 
142
                //rs7=st7.executeQuery("select organisationunit.id, organisationunit.shortname FROM organisationunit WHERE organisationunit.id in ( select organisationunit.parent from organisationunit where organisationunit.id = "+districtID+")");
 
143
                rs7=st7.executeQuery("select organisationunit.organisationunitid, organisationunit.shortname FROM organisationunit WHERE organisationunit.organisationunitid in ( select organisationunit.parentid from organisationunit where organisationunit.organisationunitid = "+districtID+")"); 
 
144
                if(rs7.next()) {  stateName = rs7.getString(2);}
 
145
                else { stateName = "";}  
 
146
 
 
147
            //rs3 =  st3.executeQuery("select id,shortname from organisationunit where parent = "+selectedOrgUnitID);
 
148
            rs3 =  st3.executeQuery("select organisationunitid,shortname from organisationunit where parentid = "+selectedOrgUnitID);
 
149
            while(rs3.next())
 
150
                 {
 
151
                        Integer tempInt = new Integer(rs3.getInt(1));
 
152
                        childOrgUnitIDs.add(childOrgUnitCount,tempInt);                 
 
153
                        childOrgUnitNames.add(childOrgUnitCount,rs3.getString(2));                      
 
154
                        childOrgUnitCount++;
 
155
                 } 
 
156
                                 
 
157
         } // try block end
 
158
    catch(Exception e)  { out.println(e.getMessage());  }
 
159
 
 
160
    String partsOfDataPeriodStartDate[] = selectedDataPeriodStartDate.split("-");                            
 
161
%>
 
162
 
 
163
 
 
164
<HTML>
 
165
        <HEAD>
 
166
                <TITLE>MONTHLY REPORT OF MALARIA PROGRAMME OF PRIMARY HEALTH CENTRE</TITLE>
 
167
        </HEAD>
 
168
        <BODY>
 
169
                <P align="center"><b>
 
170
                        <FONT face="Arial" size="3"><div align="right">M.F - 4</div>
 
171
                           MONTHLY REPORT OF MALARIA PROGRAMME OF PRIMARY HEALTH CENTRE - <%=selectedOrgUnitName%></font></b></p>
 
172
        
 
173
        <table cellpadding="0" cellspacing="0" bgcolor="ffffff" align="center" style="border-style:solid; border-width:0; border-collapse: collapse; padding: 0; position:relative; top:10" bordercolor="#ffffff" width="100%"  border="0">
 
174
                        <tr style="border-collapse: collapse; border-left-style: solid; border-left-width: 0; border-right-style: solid; border-right-width: 0; padding: 0" bordercolor="#111111" width="100%">
 
175
                                <td width="50%" style="border-collapse: collapse; border-left-style: solid; border-left-width: 0; border-right-style: solid; border-right-width: 0; padding-left:15; padding-right:0; padding-top:0; padding-bottom:0" bordercolor="#111111"  height="23"><b><font face="Arial" size="2">State&nbsp;:&nbsp;&nbsp;<%=stateName%></b></font></td>
 
176
                                <td width="50%" align="right" style="border-collapse: collapse; border-left-style: solid; border-left-width: 0; border-right-style: solid; border-right-width: 0; padding-left:15; padding-right:0; padding-top:0; padding-bottom:0" bordercolor="#111111"  height="23"><font face="Arial" size="2"><b>Month :&nbsp;&nbsp;<%=monthNames[Integer.parseInt(partsOfDataPeriodStartDate[1])]%> - <%=partsOfDataPeriodStartDate[0]%> </b></font></td>   
 
177
                        </tr>
 
178
                        <tr style="border-collapse: collapse; border-left-style: solid; border-left-width: 0; border-right-style: solid; border-right-width: 0; padding: 0" bordercolor="#111111" width="100%">
 
179
                                <td width="50%" style="border-collapse: collapse; border-left-style: solid; border-left-width: 0; border-right-style: solid; border-right-width: 0; padding-left:15; padding-right:0; padding-top:0; padding-bottom:0" bordercolor="#111111"  height="23"><b><font face="Arial" size="2">District&nbsp;:&nbsp;&nbsp;<%=districtName%></b></font></td>
 
180
                                <td width="50%" align="right" style="border-collapse: collapse; border-left-style: solid; border-left-width: 0; border-right-style: solid; border-right-width: 0; padding-left:15; padding-right:0; padding-top:0; padding-bottom:0" bordercolor="#111111"  height="23"><b><font face="Arial" size="2">Population&nbsp;:&nbsp;&nbsp;<%=totPopulation%> </b></font></td>   
 
181
                        </tr>
 
182
                </table>
 
183
                                        
 
184
                <br>
 
185
                
 
186
                <%
 
187
                        
 
188
                        try
 
189
                          {
 
190
                                  count = 0;
 
191
                                  while(count < childOrgUnitCount)
 
192
                                   {                               
 
193
                                    if(count%7==0)
 
194
                                     {
 
195
                                       if(count != 0) {%></table><div align="right"><font face="Arial" size="1"><i>(page contd.)</i></font></div> <br><%}%>
 
196
                                       </table><br>
 
197
                                       <TABLE style="BORDER-COLLAPSE: collapse" borderColor=#111111 cellSpacing=3 cellPadding=3 width="100%" border=1>
 
198
                                                <TR>
 
199
                                                        <TD align="center" width="2%" rowSpan=3><b><font face="Arial" size="1">Sl.No</font></b></TD>
 
200
                                                        <TD align="center" width="26%" rowSpan=3><b><font face="Arial" size="1">Name of PHC/Sub-centre</font></b></TD>
 
201
                                                        <TD align="center" width="3%" rowSpan=3><b><font face="Arial" size="1">Type of malaria paradigm</font></b></TD>
 
202
                                                        <TD align="center" width="3%" rowSpan=3><b><font face="Arial" size="1">Total Pop.</font></b></TD>
 
203
                                                        <TD align="center" width="9%" colspan="3"><b><font face="Arial" size="1">Active</font></b></TD>
 
204
                                                        <TD align="center" width="9%" colspan="3"><b><font face="Arial" size="1">Mass &amp; Contact</font></b></TD>
 
205
                                                        <TD align="center" width="9%" colspan="3"><b><font face="Arial" size="1">Passive</font></b></TD>
 
206
                                                        <TD align="center" width="9%" colspan="3"><b><font face="Arial" size="1">Total</font></b></TD>
 
207
                                                        <TD align="center" width="12%" colspan="4"><b><font face="Arial" size="1">Agewise Positives</font></b></TD>
 
208
                                                        <TD align="center" width="12%" colspan="4"><b><font face="Arial" size="1">RT done</font></b></TD>
 
209
                                                        <TD align="center" width="6%" colspan="2"><b><font face="Arial" size="1">Death due to malaria</font></b></TD>
 
210
                                        </TR>
 
211
                                                <TR>
 
212
                                                        <TD align="center" width="9%" colspan="3"><b><font face="Arial" size="1">Blood Slides</font></b></TD>
 
213
                                                        <TD align="center" width="9%" dir="ltr" colspan="3"><b><font face="Arial" size="1">Blood Slides</font></b></TD>
 
214
                                                        <TD align="center" width="9%" colspan="3"><b><font face="Arial" size="1">Blood Slides</font></b></TD>
 
215
                                                        <TD align="center" width="9%" colspan="3"><b><font face="Arial" size="1">Blood Slides</font></b></TD>
 
216
                                                        <TD align="center" width="3%" rowspan="2"><b><font face="Arial" size="1">Under 1 yr.</font></b></TD>
 
217
                                                        <TD align="center" width="3%" rowspan="2"><b><font face="Arial" size="1">1-4</FONT></B></TD>
 
218
                                                        <TD align="center" width="3%" rowspan="2"><b><font face="Arial" size="1">5-15</FONT></B></TD>
 
219
                                                        <TD align="center" width="3%" rowspan="2"><b><font face="Arial" size="1">15+</FONT></B></TD>
 
220
                                                        <TD align="center" width="3%" rowspan="2"><b><font face="Arial" size="1">Under 1 yr.</font></b></TD>
 
221
                                                        <TD align="center" width="3%" rowspan="2"><b><font face="Arial" size="1">1-4</font></b></TD>
 
222
                                                        <TD align="center" width="3%" rowspan="2"><b><font face="Arial" size="1">5-15</font></b></TD>
 
223
                                                        <TD align="center" width="3%" rowspan="2"><b><font face="Arial" size="1">15+</font></b></TD>
 
224
                                                        <TD align="center" width="3%" rowspan="2"><b><font face="Arial" size="1">pf. micro-<br>scopically confirmed</font></b></TD>
 
225
                                                        <TD align="center" width="3%" rowspan="2"><b><font face="Arial" size="1">Only clinically diagnosed</font></b></TD>
 
226
                                        </TR>
 
227
                                                <TR>
 
228
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Coll-<br>ected</font></b></TD>
 
229
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Exam-<br>ined</font></b></TD>
 
230
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Posi-<br>tive</font></b></TD>
 
231
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Coll-<br>ected</font></b></TD>
 
232
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Exam-<br>ined</font></b></TD>
 
233
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Posi-<br>tive</font></b></TD>
 
234
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Coll-<br>ected</font></b></TD>
 
235
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Exam-<br>ined</font></b></TD>
 
236
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Posi-<br>tive</font></b></TD>
 
237
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Coll-<br>ected</font></b></TD>
 
238
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Exam-<br>ined</font></b></TD>
 
239
                                                        <TD align="center" width="3%"><b><font face="Arial" size="1">Posi-<br>tive</font></b></TD>
 
240
                                        </TR>
 
241
                                        <TR>
 
242
                                                <TD width="2%" HEIGHT="30" align="center"><FONT face="Arial" size="1"><i><b>1</b></i></FONT></TD>
 
243
                                                <TD width="26%" HEIGHT="30" align="center"><b><i><font face="Arial" size="1">2</font></i></b></TD>
 
244
                                                <TD width="3%" HEIGHT="30" align="center"><b><i><font face="Arial" size="1">3</font></i></b></TD>
 
245
                                                <TD width="3%" HEIGHT="30" align="center"><b><i><font face="Arial" size="1">4</font></i></b></TD>
 
246
                                                <TD width="3%"  HEIGHT="30" align="center"><FONT face="Arial" size="1"><i><b>5</b></i></FONT></TD>
 
247
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">6</font></i></b></TD>
 
248
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">7</font></i></b></TD>
 
249
                                                <TD width="3%"  HEIGHT="30" align="center"><FONT face="Arial" size="1"><i><b>8</b></i></FONT></TD>
 
250
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">9</font></i></b></TD>
 
251
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">10</font></i></b></TD>
 
252
                                                <TD width="3%"  HEIGHT="30" align="center"><FONT face="Arial" size="1"><i><b>11</b></i></FONT></TD>
 
253
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">12</font></i></b></TD>
 
254
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">13</font></i></b></TD>
 
255
                                                <TD width="3%"  HEIGHT="30" align="center"><FONT face="Arial" size="1"><i><b>14</b></i></FONT></TD>
 
256
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">15</font></i></b></TD>
 
257
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">16</font></i></b></TD>
 
258
                                                <TD width="3%"  HEIGHT="30" align="center"><FONT face="Arial" size="1"><i><b>17</b></i></FONT></TD>
 
259
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">18</font></i></b></TD>
 
260
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">19</font></i></b></TD>
 
261
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">20</font></i></b></TD>
 
262
                                                <TD width="3%"  HEIGHT="30" align="center"><FONT face="Arial" size="1"><i><b>21</b></i></FONT></TD>
 
263
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">22</font></i></b></TD>
 
264
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">23</font></i></b></TD>
 
265
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">24</font></i></b></TD>
 
266
                                                <TD width="3%"  HEIGHT="30" align="center"><FONT face="Arial" size="1"><i><b>25</b></i></FONT></TD>
 
267
                                                <TD width="3%"  HEIGHT="30" align="center"><b><i><font face="Arial" size="1">26</font></i></b></TD>
 
268
                                        </TR>
 
269
                                                                                     
 
270
                                     <%}
 
271
                                        Integer temp1 = (Integer) childOrgUnitIDs.get(count);
 
272
                                        int currentChildID = temp1.intValue();                  
 
273
                                        for(i=0;i<monthlyDataElements.length;i++)
 
274
                                         {                      
 
275
                                                
 
276
                                                // For PHC
 
277
                                                //"SELECT datavalue.value FROM datavalue INNER JOIN dataelement ON datavalue.dataElement = dataelement.id WHERE datavalue.period ="+selectedDataPeriodID+" AND datavalue.source ="+currentChildID+" AND dataelement.dataelementcode like '"+monthlyDataElements[i]+"'";
 
278
                                                
 
279
                                                // For District
 
280
                                                //"SELECT sum(datavalue.value) FROM datavalue INNER JOIN dataelement ON datavalue.dataElement = dataelement.id WHERE datavalue.period ="+selectedDataPeriodID+" AND datavalue.source in (select id from organisationunit where parent ="+currentChildID+")  AND dataelement.dataelementcode like '"+monthlyDataElements[i]+"'"; 
 
281
 
 
282
                                                //rs4 = st4.executeQuery("SELECT sum(datavalue.value) FROM datavalue INNER JOIN dataelement ON datavalue.dataElement = dataelement.id WHERE datavalue.period in (select id from period where startdate between '"+startingDate+"' and '"+startingDate+"') AND datavalue.source ="+currentChildID+" AND dataelement.code like '"+monthlyDataElements[i]+"'");
 
283
                                                rs4 = st4.executeQuery("SELECT sum(datavalue.value) FROM datavalue INNER JOIN dataelement ON datavalue.dataelementid = dataelement.dataelementid WHERE datavalue.periodid in (select periodid from period where startdate between '"+startingDate+"' and '"+startingDate+"') AND datavalue.sourceid ="+currentChildID+" AND dataelement.code like '"+monthlyDataElements[i]+"'");
 
284
                                                //SELECT datavalue.value FROM datavalue INNER JOIN dataelement ON datavalue.dataElement = dataelement.id WHERE datavalue.period="+selectedDataPeriodID+" AND datavalue.source="+currentChildID+" AND dataelement.dataelementcode like '"+monthlyDataElements[i]+"'");
 
285
                                                //SELECT sum(datavalue.value) FROM (organisationunit INNER JOIN datavalue ON organisationunit.id = datavalue.source) INNER JOIN period ON datavalue.period = period.id WHERE organisationunit.parent="+selectedOrgUnitID+" AND period.startDate='"+selectedDataPeriodStartDate+"' AND periodType="+periodTypeID+" AND datavalue.dataElement="+dataElementIDs[i])
 
286
                                                                                                
 
287
                                                if(!rs4.next())  {  tempval[i] = 0;      }
 
288
                                                else   {  tempval[i] = rs4.getInt(1);  }
 
289
                                                total[i] += tempval[i];
 
290
                                         }      
 
291
                           
 
292
                                   %>                                                                                           
 
293
                                        
 
294
                                        <TR>
 
295
                                        <TD width="2%" HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=(count+1)%></font></TD>
 
296
                                        <TD width="26%" HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=childOrgUnitNames.get(count)%></font></TD>
 
297
                                        <TD width="3%" HEIGHT="50" align="center"><FONT face="Arial" size="1"></font></TD>
 
298
                                        <TD width="3%" HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[1]%></font></TD>
 
299
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[2]%></font></TD>
 
300
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[3]%></font></TD>
 
301
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[4]%></font></TD>
 
302
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[5]%></font></TD>
 
303
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[6]%></font></TD>
 
304
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[7]%></font></TD>
 
305
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[8]%></font></TD>
 
306
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[9]%></font></TD>
 
307
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[10]%></font></TD>
 
308
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=(tempval[2]+tempval[5]+tempval[8])%></font></TD>
 
309
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=(tempval[3]+tempval[6]+tempval[9])%></font></TD>
 
310
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=(tempval[4]+tempval[7]+tempval[10])%></font></TD>
 
311
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[11]%></font></TD>
 
312
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[12]%></font></TD>
 
313
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[13]%></font></TD>
 
314
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[14]%></font></TD>
 
315
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[15]%></font></TD>
 
316
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[16]%></font></TD>
 
317
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[17]%></font></TD>
 
318
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[18]%></font></TD>
 
319
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[19]%></font></TD>
 
320
                                        <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=tempval[20]%></font></TD>                                     
 
321
                                </TR>
 
322
                                <%                                      
 
323
                                        count++;
 
324
                                   }    // while loop end
 
325
                          } // try block end
 
326
                        catch(Exception e)  { out.println(e.getMessage());  }
 
327
                finally
 
328
                 {
 
329
                                try
 
330
                                 {
 
331
                                        if(rs1!=null)  rs1.close();             if(st1!=null)  st1.close();
 
332
                                        if(rs2!=null)  rs2.close();             if(st2!=null)  st2.close();
 
333
                                        if(rs3!=null)  rs3.close();             if(st3!=null)  st3.close();                                     
 
334
                                        if(rs4!=null)  rs4.close();             if(st4!=null)  st4.close();
 
335
                                        if(rs5!=null)  rs5.close();             if(st5!=null)  st5.close();                                     
 
336
                                        if(rs6!=null)  rs6.close();             if(st6!=null)  st6.close();                                     
 
337
                                        if(rs7!=null)  rs7.close();             if(st7!=null)  st7.close();                                     
 
338
                                
 
339
                                        if(con!=null)  con.close();
 
340
                                 }
 
341
                                catch(Exception e)   {  out.println(e.getMessage());   }
 
342
                 } // finally block end                                 
 
343
                %>
 
344
 
 
345
                        <TR>
 
346
                                <TD width="28%" HEIGHT="50" align="center" colspan="2"><b><font face="Arial" size="2">Total</font></b></TD>
 
347
                                <TD width="3%" HEIGHT="50" align="center"><FONT face="Arial" size="1"></font></TD>
 
348
                                <TD width="3%" HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[1]%></font></TD>
 
349
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[2]%></font></TD>
 
350
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[3]%></font></TD>
 
351
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[4]%></font></TD>
 
352
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[5]%></font></TD>
 
353
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[6]%></font></TD>
 
354
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[7]%></font></TD>
 
355
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[8]%></font></TD>
 
356
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[9]%></font></TD>
 
357
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[10]%></font></TD>
 
358
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=(total[2]+total[5]+total[8])%></font></TD>
 
359
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=(total[3]+total[6]+total[9])%></font></TD>
 
360
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=(total[4]+total[7]+total[10])%></font></TD>
 
361
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[11]%></font></TD>
 
362
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[12]%></font></TD>
 
363
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[13]%></font></TD>
 
364
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[14]%></font></TD>
 
365
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[15]%></font></TD>
 
366
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[16]%></font></TD>
 
367
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[17]%></font></TD>
 
368
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[18]%></font></TD>
 
369
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[19]%></font></TD>
 
370
                                <TD width="3%"  HEIGHT="50" align="center"><FONT face="Arial" size="1"><%=total[20]%></font></TD>
 
371
                        </TR>
 
372
                </TABLE>
 
373
        </BODY>
 
374
</HTML>
 
 
b'\\ No newline at end of file'