← 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/Kerala/kform9.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/Kerala/kform9.jsp
 
1
 
 
2
<%@ page import="java.sql.*" %>
 
3
<%@ page import="com.opensymphony.xwork.util.OgnlValueStack" %>
 
4
 
 
5
<%
 
6
      Connection con=null;
 
7
      
 
8
      // for Performance in the Reporting Month
 
9
      Statement st=null;
 
10
      ResultSet rs=null;
 
11
      
 
12
      // for selected OrgUnit Name and Population
 
13
      Statement st1=null;
 
14
      ResultSet rs1=null;
 
15
      
 
16
      // for Performance in Corresponding month Last Year
 
17
      Statement st2=null;
 
18
      ResultSet rs2=null;
 
19
      
 
20
      // for Cumulative Performance till Current Month
 
21
      Statement st4=null;
 
22
      ResultSet rs4=null;
 
23
     
 
24
      // for Cumulative Performance till corresponding month of Last Year
 
25
      Statement st3=null;
 
26
      ResultSet rs3=null;
 
27
 
 
28
      // for Taluk Name and Id
 
29
      Statement st5=null;
 
30
      ResultSet rs5=null;
 
31
 
 
32
      // for District Name and Id
 
33
      Statement st6=null;
 
34
      ResultSet rs6=null;
 
35
 
 
36
     // for PHC Name and Id
 
37
      Statement st8=null;
 
38
      ResultSet rs8=null;
 
39
 
 
40
     // for CHC Name and Id
 
41
      Statement st9=null;
 
42
      ResultSet rs9=null;
 
43
 
 
44
     // for PHC Population Estimates
 
45
      Statement st10=null;
 
46
      ResultSet rs10=null;
 
47
 
 
48
     // for Data Period Start Date and End Date
 
49
      Statement st11=null;
 
50
      ResultSet rs11=null;
 
51
      
 
52
     // for DataElement ids based on DataElement code
 
53
      Statement st12=null;
 
54
      ResultSet rs12=null;
 
55
     
 
56
      
 
57
      String userName = "dhis";           
 
58
      String password = "";           
 
59
      String urlForConnection = "jdbc:mysql://localhost/kl_dhis2";
 
60
          
 
61
      int talukID = 0;
 
62
      String talukName = "";
 
63
      int districtID = 0; 
 
64
      String districtName = ""; 
 
65
      int CHCID = 0;
 
66
      String CHCName ="";
 
67
      int PHCID = 0;
 
68
      String PHCName ="";          
 
69
      int totPHCPopulation = -1;
 
70
      int totSCPopulation = -1;
 
71
 
 
72
          OgnlValueStack stack = (OgnlValueStack)request.getAttribute("webwork.valueStack");
 
73
          String selectedId = (String) stack.findValue( "orgUnitId" );
 
74
          int selectedOrgUnitID =  Integer.parseInt( selectedId );
 
75
        
 
76
  
 
77
          String startingDate  =  (String) stack.findValue( "startingPeriod" );
 
78
          String endingDate  =   (String) stack.findValue( "endingPeriod" );
 
79
      
 
80
          String monthlyPeriodId = (String) stack.findValue( "monthlyPeriodTypeId" );
 
81
          int periodTypeID =       Integer.parseInt( monthlyPeriodId );
 
82
               
 
83
          int lastYear = 0;
 
84
      
 
85
          String selectedOrgUnitName = "";
 
86
          String selectedDataPeriodStartDate = "";
 
87
          String selectedDataPeriodEndDate = "";
 
88
          String lastDataPeriodStartDate = "";
 
89
          
 
90
           String dataElementCodes[] = {
 
91
                                                                                
 
92
                                                                                
 
93
 
 
94
                                                                                                                        
 
95
                                                                                "'Form6_DE1'", 
 
96
                                                                                
 
97
                                                                                "' '",
 
98
                                                                                
 
99
                                                                                "'Form6_DE3'", 
 
100
                                                                                
 
101
                                                                                "' '",
 
102
                                                                                
 
103
                                                                                "'Form6_DE6'", "'Form6_DE7'", 
 
104
                                                                                
 
105
                                                                                "' '","' '",
 
106
                                                                                
 
107
                                                                                "'Form6_DE4'", "'Form6_DE4'", "'Form8_DE1.3'", "'Form9_DE1'", "'Form9_DE2'", "'Form9_DE3'", "'Form9_DE4'", 
 
108
                                                                                
 
109
                                                                                "' '","' '","' '",
 
110
                                                                                
 
111
                                                                                "'Form6_DE13'",
 
112
                                                                                
 
113
                                                                                "'Form6_DE11','Form6_DE12'",
 
114
                                                                                
 
115
                                                                                "' '",
 
116
                                                                                
 
117
                                                                                "'Form6_DE15'","'Form8_DE2.3a'",
 
118
                                                                                "'Form6_DE16'", "'Form9_DE5'", "'Form9_DE6'", "'Form9_DE7'", "'Form9_DE8'",
 
119
                                                                                
 
120
                                                                                "' '",
 
121
                                                                                
 
122
                                                                                "'Form6_DE35'", "'Form6_DE36'", "'Form6_DE37'", 
 
123
                                                                                
 
124
                                                                                "'Form6_DE17','Form6_DE18'",
 
125
                                                                                "'Form6_DE19','Form6_DE20'",
 
126
                                                                                
 
127
                                                                                "' '",
 
128
                                                                                
 
129
                                                                                "'Form6_DE21','Form6_DE22'",
 
130
                                                                                "'Form6_DE23','Form6_DE24'",
 
131
                                                                                "'Form6_DE25','Form6_DE26'",
 
132
                                                                                
 
133
                                                                                "' '",
 
134
                                                                                
 
135
                                                                                "'Form6_DE29','Form6_DE30'",
 
136
                                                                                "'Form6_DE27','Form6_DE28'",
 
137
                                                                                
 
138
                                                                                "' '",
 
139
                                                                                
 
140
                                                                                "'Form6_DE31','Form6_DE32'",
 
141
                                                                                                                                                                                                                                        
 
142
                                                                                "'Form9_DE9'", 
 
143
                                                                                
 
144
                                                                                "'Form6_DE33'", 
 
145
                                                                                
 
146
                                                                                "' '",
 
147
                                                                                
 
148
                                                                                "'Form6_DE38'", "'Form6_DE39'", 
 
149
                                                                                
 
150
                                                                                "' '",
 
151
                                                                                
 
152
                                                                                "'Form6_DE38','Form6_DE39'",
 
153
                                                                                "'Form6_DE42','Form6_DE43'",
 
154
                                                                                
 
155
                                                                                "'Form9_DE10'", "'Form9_DE11'", 
 
156
                                                                                
 
157
                                                                                "'Form6_DE146'", 
 
158
                                                                                
 
159
                                                                                "' '","' '",
 
160
                                                                                
 
161
                                                                                "'Form6_DE44'", "'Form6_DE45'", "'Form6_DE46'", "'Form6_DE47'", "'Form6_DE48'", "'Form6_DE49'", 
 
162
                                                                                "'Form6_DE50'", "'Form6_DE51'", "'Form6_DE52'", "'Form6_DE53'", "'Form6_DE54'", "'Form6_DE55'","'Form6_DE56'", 
 
163
                                                                                "'Form6_DE57'", "'Form6_DE58'", "'Form6_DE59'",                                                                                                                                                 
 
164
                                                                                
 
165
 
 
166
                                                                                "'Form6_DE60'", "'Form6_DE61'", 
 
167
                                                                                
 
168
                                                                                "' '","' '",
 
169
                                                                                
 
170
                                                                                
 
171
                                                                                "'Form6_DE62'", "'Form6_DE63'", "'Form6_DE64'", "'Form6_DE65'", 
 
172
                                                                                
 
173
                                                                                "' '","' '","' '","' '",
 
174
                                                                                
 
175
                                                                                "'Form6_DE66'", "'Form6_DE67'",
 
176
                                                                                
 
177
                                                                                "' '","' '",
 
178
                                                                                
 
179
                                                                                "'Form6_DE68'", "'Form6_DE69'", 
 
180
                                                                                
 
181
                                                                                "' '","' '",
 
182
                                                                                
 
183
                                                                                "'Form6_DE70'", "'Form6_DE71'",
 
184
                                                                                
 
185
                                                                                "' '","' '",
 
186
                                                                                
 
187
                                                                                "'Form6_DE72'", "'Form6_DE73'", "'Form6_DE74'",  
 
188
                                                                                "'Form6_DE75'", "'Form6_DE76'", "'Form6_DE77'", 
 
189
                                                                                
 
190
                                                                                "' '","' '",
 
191
                                                                                
 
192
                                                                                "'Form6_DE94','Form6_DE95'",
 
193
                                                                                "'Form6_DE100','Form6_DE101'",
 
194
 
 
195
                                                                                "' '",
 
196
                                                                                
 
197
                                                                                "'Form6_DE78','Form6_DE79'",
 
198
                                                                                "'Form6_DE84','Form6_DE85'",
 
199
                                                                                
 
200
                                                                                "' '",
 
201
                                                                                
 
202
                                                                                "'Form6_DE86','Form6_DE87'",
 
203
                                                                                "'Form6_DE92','Form6_DE93'",
 
204
                                                                                
 
205
                                                                                "' '",
 
206
                                                                                
 
207
                                                                                "'Form8_DE9.1diM','Form8_DE9.1diF'",
 
208
                                                                                "'Form8_DE9.1diiM','Form8_DE9.1diiF'",
 
209
                                                                                
 
210
                                                                                "' '",
 
211
                                                                                
 
212
                                                                                "'Form8_DE9.1eiM','Form8_DE9.1eiF'",
 
213
                                                                                "'Form8_DE9.1eiiM','Form8_DE9.1eiiF'",
 
214
                                                                                
 
215
                                                                                "' '",
 
216
                                                                                
 
217
                                                                                "'Form6_DE102','Form6_DE103'",
 
218
                                                                                "'Form6_DE108','Form6_DE109'",
 
219
                                                                                
 
220
                                                                                "' '","' '",
 
221
                                                                                
 
222
                                                                                "'Form6_DE110','Form6_DE111'",
 
223
                                                                                "'Form6_DE112','Form6_DE113'",
 
224
                                                                                "'Form6_DE114','Form6_DE115'",
 
225
                                                                                
 
226
                                                                                "' '",
 
227
                                                                                
 
228
                                                                                "'Form6_DE110','Form6_DE111'",
 
229
                                                                                "'Form6_DE116','Form6_DE117'",
 
230
                                                                                "'Form6_DE118','Form6_DE119'",
 
231
                                                                                "'Form6_DE120','Form6_DE121'",
 
232
                                                                                "'Form6_DE122','Form6_DE123'",
 
233
                                                                                "'Form6_DE124','Form6_DE125'",
 
234
                                                                                "'Form6_DE126','Form6_DE127'",
 
235
                                                                                "'Form6_DE128','Form6_DE129'",
 
236
                                                                                "'Form8_DE11.1a','Form8_DE11.1b'",
 
237
                                                                                "'Form8_DE11.2a','Form8_DE11.2b'",                                                                              
 
238
 
 
239
                                                                                "'Form6_DE135'",
 
240
 
 
241
                                                                                "'Form6_DE139','Form6_DE140'",
 
242
                                                                                
 
243
                                                                                "'Form6_DE143'",
 
244
                                                                                
 
245
                                                                                "' '","' '","' '","' '","' '",
 
246
                                                                                
 
247
                                                                                "'Form6_DE146'", 
 
248
                                                                                
 
249
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
250
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
251
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
252
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
253
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
254
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
255
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
256
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
257
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
258
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
259
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
260
                                                                                "' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '","' '",
 
261
 
 
262
                                                                                                                                                                
 
263
                                                                                
 
264
                                                                                
 
265
                                                                                        
 
266
 
 
267
                                                                           };
 
268
 
 
269
                 
 
270
        int dataElementIDs[] = new int[dataElementCodes.length+5];  
 
271
        int entryNumberValues[]=  new int[dataElementCodes.length+5];     
 
272
        int entryValuesForLastYear[]= new int[dataElementCodes.length+5];      
 
273
        int cumentryValuesForCurYear[]= new int[dataElementCodes.length+5];      
 
274
        int cumentryValuesForLastYear[]= new int[dataElementCodes.length+5];      
 
275
        
 
276
        String monthNames[] = { "", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" };            
 
277
   
 
278
%>
 
279
                                
 
280
<%
 
281
     try
 
282
      {
 
283
        Class.forName ("com.mysql.jdbc.Driver").newInstance ();
 
284
        con = DriverManager.getConnection (urlForConnection, userName, password);
 
285
        
 
286
        st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); 
 
287
        st1=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
288
        st2=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
289
        st3=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
290
        st4=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
291
        st5=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); 
 
292
        st6=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
293
        st8=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
294
        st9=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
295
        st10=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
296
        st11=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
297
        st12=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
 
298
 
 
299
     
 
300
        //rs1 = st1.executeQuery("SELECT organisationunit.shortname FROM organisationunit WHERE id ="+selectedOrgUnitID);
 
301
        rs1 = st1.executeQuery("SELECT organisationunit.shortname FROM organisationunit WHERE organisationunitid ="+selectedOrgUnitID);        
 
302
        if(rs1.next())  {   selectedOrgUnitName = rs1.getString(1);     }
 
303
        else   {        selectedOrgUnitName = "";                   }  
 
304
 
 
305
        //rs11 = st11.executeQuery("select startDate,endDate from period where id = "+selectedDataPeriodID);
 
306
                //if(rs11.next())
 
307
                //  {
 
308
                //      selectedDataPeriodStartDate =  rs11.getDate(1).toString();
 
309
                //      selectedDataPeriodEndDate   =  rs11.getDate(2).toString();
 
310
                //  }
 
311
                
 
312
                selectedDataPeriodStartDate = startingDate;
 
313
                
 
314
      } // try block end
 
315
     catch(Exception e)  { out.println(e.getMessage());  }
 
316
     finally
 
317
       {
 
318
                        try
 
319
                                {
 
320
                                        if(rs1!=null)  rs1.close();                     if(st1!=null)  st1.close();
 
321
                                        if(rs11!=null)  rs11.close();           if(st11!=null)  st11.close();
 
322
                                }
 
323
                        catch(Exception e)   {  out.println(e.getMessage());   }
 
324
       } // finally block end           
 
325
 
 
326
        String partsOfDataPeriodStartDate[]  =  selectedDataPeriodStartDate.split("-");
 
327
        lastYear  = Integer.parseInt(partsOfDataPeriodStartDate[0]) - 1;
 
328
        lastDataPeriodStartDate = lastYear+"-"+partsOfDataPeriodStartDate[1]+"-"+partsOfDataPeriodStartDate[2];
 
329
        
 
330
        int tempForMonth1 = Integer.parseInt(partsOfDataPeriodStartDate[1]);
 
331
        int tempForYear = 0;
 
332
                
 
333
        if(tempForMonth1 < 4)           {   tempForYear = lastYear;  }
 
334
        else  {   tempForYear = lastYear + 1;           }
 
335
         
 
336
         String curYearStart = tempForYear+"-04-01";
 
337
         String lastYearStart = (tempForYear-1)+"-04-01";
 
338
         String lastYearEnd = lastYear+"-"+partsOfDataPeriodStartDate[1]+"-"+partsOfDataPeriodStartDate[2];
 
339
         
 
340
     //for district, taluk, CHC names
 
341
     try
 
342
      {
 
343
                //rs8=st8.executeQuery("select organisationunit.id, organisationunit.name FROM organisationunit WHERE organisationunit.id in ( select organisationunit.parent from organisationunit where organisationunit.id = "+selectedOrgUnitID+")");
 
344
                rs8=st8.executeQuery("select organisationunit.organisationunitid, organisationunit.name FROM organisationunit WHERE organisationunit.organisationunitid in ( select organisationunit.parentid from organisationunit where organisationunit.organisationunitid = "+selectedOrgUnitID+")");        
 
345
                        if(rs8.next())  { PHCID = rs8.getInt(1);PHCName = rs8.getString(2);  } 
 
346
                        else  {  PHCID = 0; PHCName = "";  } 
 
347
 
 
348
                        //rs9=st9.executeQuery("select organisationunit.id, organisationunit.name FROM organisationunit WHERE organisationunit.id in ( select organisationunit.parent from organisationunit where organisationunit.id = "+PHCID+")");
 
349
                        rs9=st9.executeQuery("select organisationunit.organisationunitid, organisationunit.name FROM organisationunit WHERE organisationunit.organisationunitid in ( select organisationunit.parentid from organisationunit where organisationunit.organisationunitid = "+PHCID+")");   
 
350
                        if(rs9.next())  { CHCID = rs9.getInt(1);CHCName = rs9.getString(2);  } 
 
351
                        else  {  CHCID = 0; CHCName = "";  } 
 
352
 
 
353
                        //rs5=st5.executeQuery("select organisationunit.id, organisationunit.name FROM organisationunit WHERE organisationunit.id in ( select organisationunit.parent from organisationunit where organisationunit.id = "+CHCID+")");
 
354
                        rs5=st5.executeQuery("select organisationunit.organisationunitid, organisationunit.name FROM organisationunit WHERE organisationunit.organisationunitid in ( select organisationunit.parentid from organisationunit where organisationunit.organisationunitid = "+CHCID+")");   
 
355
                        if(rs5.next())  { talukID = rs5.getInt(1); talukName = rs5.getString(2);  } 
 
356
                        else  {  talukID = 0; talukName = "";  } 
 
357
        
 
358
                    //rs6=st6.executeQuery("select organisationunit.id, organisationunit.name FROM organisationunit WHERE organisationunit.id in ( select organisationunit.parent from organisationunit where organisationunit.id = "+talukID+")");
 
359
                    rs6=st6.executeQuery("select organisationunit.organisationunitid, organisationunit.name FROM organisationunit WHERE organisationunit.organisationunitid in ( select organisationunit.parentid from organisationunit where organisationunit.organisationunitid = "+talukID+")"); 
 
360
                    if(rs6.next()) {  districtID = rs6.getInt(1); districtName = rs6.getString(2);}
 
361
                        else {districtID = 0; districtName = "";}      
 
362
 
 
363
                        //rs10=st10.executeQuery("SELECT sum(datavalue.value) FROM organisationunit INNER JOIN (dataelement INNER JOIN datavalue ON dataelement.id = datavalue.dataElement) ON organisationunit.id = datavalue.source WHERE organisationunit.parent = "+PHCID+" AND dataelement.name like 'Total Population'");
 
364
                        rs10=st10.executeQuery("SELECT sum(datavalue.value) FROM organisationunit INNER JOIN (dataelement INNER JOIN datavalue ON dataelement.dataelementid = datavalue.dataelementid) ON organisationunit.organisationunitid = datavalue.sourceid WHERE organisationunit.parentid = "+PHCID+" AND dataelement.name like 'Total Population'");
 
365
                        if(rs10.next()) { totPHCPopulation = rs10.getInt(1);}
 
366
                        else {totPHCPopulation = 0;}      
 
367
       
 
368
       }   // try block end              
 
369
     catch(Exception e)  { out.println(e.getMessage());  }
 
370
     finally
 
371
       {
 
372
                 try
 
373
                          {
 
374
                            if(rs5!=null)  rs5.close();                 if(st5!=null)  st5.close();
 
375
                            if(rs6!=null)  rs6.close();                 if(st6!=null)  st6.close();
 
376
                            if(rs8!=null)  rs8.close();                 if(st8!=null)  st8.close();   
 
377
                            if(rs9!=null)  rs9.close();                 if(st9!=null)  st9.close();
 
378
                            if(rs10!=null)  rs10.close();               if(st10!=null)  st10.close();                                                                                                                
 
379
                          }
 
380
                catch(Exception e)   {  out.println(e.getMessage());   }
 
381
       }  // finally block end
 
382
    
 
383
     try
 
384
      {
 
385
                int i=0;    
 
386
                int j= dataElementCodes.length;
 
387
                String query = "";
 
388
                int in = 0;
 
389
                        
 
390
                while(i!=j)
 
391
                        {               
 
392
                                entryNumberValues[i]     = -1;
 
393
                                entryValuesForLastYear[i] = -1;
 
394
                                cumentryValuesForCurYear[i] = -1;
 
395
                                cumentryValuesForLastYear[i] = -1;
 
396
                                                                                                                                
 
397
                                // for Performance in the reporting month
 
398
                                if(i==8 || i==9)                                
 
399
                                    //query = "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 in ( select id from organisationunit where parent in (select id from organisationunit where parent in (select id from organisationunit where parent in (select id from organisationunit where parent = "+selectedOrgUnitID+"))))  AND dataelement.code in ("+dataElementCodes[i]+")";
 
400
                                    query = "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 in ( select organisationunitid from organisationunit where parentid in (select organisationunitid from organisationunit where parentid in (select organisationunitid from organisationunit where parentid in (select organisationunitid from organisationunit where parentid = "+selectedOrgUnitID+"))))  AND dataelement.code in ("+dataElementCodes[i]+")"; 
 
401
                                else 
 
402
                                        //query = "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 in ( select id from organisationunit where parent ="+selectedOrgUnitID+") AND dataelement.code in ("+dataElementCodes[i]+")";
 
403
                                        query = "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 in ( select organisationunitid from organisationunit where parentid ="+selectedOrgUnitID+") AND dataelement.code in ("+dataElementCodes[i]+")"; 
 
404
                                rs=st.executeQuery(query);
 
405
                                if(rs.next())  {  entryNumberValues[i] = rs.getInt(1);  } 
 
406
                                else  {  entryNumberValues[i] = 0;  } 
 
407
                                                                                                
 
408
                        // for Cumulative Performance till Current Month
 
409
                                if(i==8 || i==9)                                
 
410
                                    //query = "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 '"+curYearStart+"' and '"+selectedDataPeriodStartDate+"') AND datavalue.source in ( select id from organisationunit where parent in (select id from organisationunit where parent in (select id from organisationunit where parent in (select id from organisationunit where parent = "+selectedOrgUnitID+"))))  AND dataelement.code in ("+dataElementCodes[i]+")";
 
411
                                    query = "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 '"+curYearStart+"' and '"+selectedDataPeriodStartDate+"') AND datavalue.sourceid in ( select organisationunitid from organisationunit where parentid in (select organisationunitid from organisationunit where parentid in (select organisationunitid from organisationunit where parentid in (select organisationunitid from organisationunit where parentid = "+selectedOrgUnitID+"))))  AND dataelement.code in ("+dataElementCodes[i]+")"; 
 
412
                                else
 
413
                                        //query = "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 '"+curYearStart+"' and '"+selectedDataPeriodStartDate+"' and periodType = "+periodTypeID+")  AND datavalue.source in ( select id from organisationunit where parent ="+selectedOrgUnitID+")  AND dataelement.code in ("+dataElementCodes[i]+")";
 
414
                                        query = "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 '"+curYearStart+"' and '"+selectedDataPeriodStartDate+"' and periodtypeid = "+periodTypeID+")  AND datavalue.sourceid in ( select organisationunitid from organisationunit where parentid ="+selectedOrgUnitID+")  AND dataelement.code in ("+dataElementCodes[i]+")";
 
415
                            rs4=st4.executeQuery(query);
 
416
                        if(rs4.next())  {  cumentryValuesForCurYear[i]= rs4.getInt(1);  } 
 
417
                            else  {  cumentryValuesForCurYear[i] = 0;  } 
 
418
                                    
 
419
                                i++;
 
420
                        }  // while loop end
 
421
      }  // try block end                
 
422
     catch(Exception e)  { out.println(e.getMessage());  }
 
423
     finally
 
424
       {
 
425
                         try
 
426
                                {
 
427
                                    if(rs!=null)  rs.close();           if(st!=null)  st.close();                                                       
 
428
                                        if(rs2!=null)  rs2.close();             if(st2!=null)  st2.close();    
 
429
                                        if(rs3!=null)  rs3.close();             if(st3!=null)  st3.close();                                     
 
430
                                        if(rs4!=null)  rs4.close();             if(st4!=null)  st4.close();
 
431
                                        if(rs12!=null)  rs12.close();   if(st12!=null)  st12.close();
 
432
                                                                                
 
433
                                        if(con!=null) con.close();                                      
 
434
                        }        
 
435
                        catch(Exception e)   {  out.println(e.getMessage());   }
 
436
       }  // finally block end                                  
 
437
%>
 
438
 
 
439
<HTML>
 
440
<HEAD>
 
441
   <TITLE> Form - 9</TITLE>
 
442
   <script src="../dhis-web-reports/Kerala/KDENamesForForm9.js" type="text/javascript" language="Javascript"></script>  
 
443
   <script>
 
444
                function fun1()
 
445
                { 
 
446
                    
 
447
                        var start=0;
 
448
                        var end = 28;    
 
449
                        var j=1;
 
450
                        var k=0;                
 
451
                        var id="";                      
 
452
                
 
453
                        while(j<=5)
 
454
                         {                          
 
455
                                
 
456
                                if(j==1) end = 40;
 
457
                                        else if(j==2) end = 55;
 
458
                                        else if(j==3) end = 56;
 
459
                                        else if(j==4) end = 37;
 
460
                                
 
461
                                
 
462
                                        for(start=0;start<=end;start++)
 
463
                                         {      
 
464
                                                 id="cell1"+k;                                    
 
465
                                                 document.getElementById(id).innerHTML = slnoForForm9[k];
 
466
                                                 id="cell2"+k;                                    
 
467
                                                 document.getElementById(id).innerHTML = servicesForForm9[k];
 
468
                                                 id="cell3"+k;                                    
 
469
                                                 document.getElementById(id).innerHTML = denamesForForm9[k];
 
470
                                                 
 
471
                                                 k++;
 
472
                                         }
 
473
                                        j++; 
 
474
                                 }                                                              
 
475
                        }
 
476
        </script>                   
 
477
</HEAD>
 
478
<BODY BGCOLOR="#FFFFFF" onload="fun1()">  
 
479
        <font face="Arial" size="2">(To be submitted by 25th of following month to 
 
480
    State Family Welfare Department and Department of Family Welfare, 
 
481
    MOHFW,GOI,New Delhi throuth NICNET)</font>
 
482
        <center>
 
483
                <font face="Arial" size="3">
 
484
                        <b>FORM 9 <br> CONSOLIDATIED MONTHLY REPORT FROM DISTRICT TO STATE / 
 
485
        CENTRE  
 
486
        </center>
 
487
        GENERAL
 
488
        </b></font>
 
489
                <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">
 
490
                        <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%">
 
491
                        <td width="60%" 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">
 
492
                                <font face="Arial" size="2">1. State : &nbsp;&nbsp;&nbsp;<%=selectedOrgUnitName%></font>
 
493
                </td>
 
494
                        <td width="40%" 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">
 
495
                                <font face="Arial" size="2">4. Reporting for the month of :  &nbsp;&nbsp;<%=monthNames[Integer.parseInt(partsOfDataPeriodStartDate[1])]%> - <%=partsOfDataPeriodStartDate[0]%></font>
 
496
                </td>   
 
497
                </tr>
 
498
                <tr>
 
499
                        <td width="60%" 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="19">
 
500
                                <font face="Arial" size="2">2. District : &nbsp;&nbsp;<%=selectedOrgUnitName%></font>
 
501
                </td>
 
502
                        <td width="40%" 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="19">
 
503
                                <font face="Arial" size="2">5. Eligible Couples ( as on 1st 
 
504
                    April of the year) </font>
 
505
                </td>
 
506
                </tr>           
 
507
                <tr>
 
508
                        <td width="60%" 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="19">
 
509
                                <font face="Arial" size="2">3. Population of District</font>
 
510
                </td>
 
511
                        <td width="40%" 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="19">
 
512
                                <font face="Arial" size="2"></font>
 
513
                </td>
 
514
                </tr>           
 
515
        </table>  
 
516
<br>
 
517
 
 
518
<%
 
519
        int i;
 
520
        int j = 1;
 
521
        int k = 0;
 
522
        int l = 0;
 
523
        int endcount = 28;
 
524
        int flag = 0;
 
525
        String tempForentryNumberValues[] = new String[12];
 
526
        String tempForcumentryValuesForCurYear[] = new String[12];
 
527
        
 
528
                        
 
529
        String temp1 = "";
 
530
        
 
531
        String temp2 = "";
 
532
        
 
533
        int temp = 0;
 
534
int bt =0;      
 
535
        while(j<=4)
 
536
         { 
 
537
           
 
538
            if(j==1) 
 
539
              { %>
 
540
                <table cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1;padding: 0" bordercolor="#111111" width="100%">
 
541
                        <tr>
 
542
                                        <td width="3%" align="center" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; " bordercolor="#111111" align="left"><font face="Arial" size="2"><b>Sl<br>No</b></font></td>
 
543
                                        <td width="36%" align="center" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:15; padding-right:1; " bordercolor="#111111" ><font face="Arial" size="2"><b>Service</b></font></td>
 
544
                                        <td width="45%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:3; padding-right:1; " bordercolor="#111111" dir="ltr"><font face="Arial" size="2"></font></td>
 
545
                                        <td width="16%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:3; padding-right:1; " bordercolor="#111111" align="center" colspan="3"><font face="Arial" size="2"></font></td>
 
546
                                </tr>
 
547
                   <%                 
 
548
               }
 
549
            else if(j==2)
 
550
              { %>
 
551
                        <br><br><br><br><br><br>
 
552
                <table cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1;padding: 0" bordercolor="#111111" width="100%">              
 
553
              <% 
 
554
                        }
 
555
        else if(j==3)
 
556
              { %>
 
557
                        <br><br><br>
 
558
                <table cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1;padding: 0" bordercolor="#111111" width="100%">              
 
559
              <% 
 
560
                        }
 
561
                else if(j==4)
 
562
              { %>
 
563
                        <br><br><br>
 
564
                <table cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1;padding: 0" bordercolor="#111111" width="100%">              
 
565
              <% 
 
566
                        }
 
567
                
 
568
            else
 
569
              { %>
 
570
                        <br>
 
571
                <table cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1;padding: 0" bordercolor="#111111" width="100%">              
 
572
              <% 
 
573
                        }
 
574
 
 
575
                if(j==1) endcount = 40; 
 
576
                else if(j==2) endcount = 55; 
 
577
                else if(j==3) endcount = 56;
 
578
                else if(j==4) endcount = 37;
 
579
                
 
580
                                                                                                
 
581
                for(i=0;i<=endcount;i++)
 
582
                  {     
 
583
                String id1="cell1"+k;
 
584
                String id2="cell2"+k;
 
585
                String id3="cell3"+k;
 
586
                            
 
587
                if((k>=54 && k<=77))
 
588
                { 
 
589
               if(k==54) 
 
590
                    {
 
591
                      tempForentryNumberValues[0]= "<b>M</b>";
 
592
                      tempForentryNumberValues[1]= "<b>F</b>";
 
593
                                  tempForentryNumberValues[2]= "<b>T</b>";
 
594
                      
 
595
                      l=l+2;
 
596
                    }
 
597
                   else if(k==1|| k==3 || k==7 || k==16 || k==17 || k==20 || k==34 || k==38 || k==41 || k==48 || k==64 || k==68 || k==70 || k==74 || k==78 || k==79 || k==82 || k==85 || k==88 || k==91 || k==94 || k==97 || k==98 || k==102 || k==116)
 
598
                                {
 
599
                      tempForentryNumberValues[0]= " ";
 
600
                      tempForentryNumberValues[1]= " ";
 
601
                                  tempForentryNumberValues[2]= " ";
 
602
                      
 
603
                      l=l+2;
 
604
                    }
 
605
                    
 
606
                   else 
 
607
                    {
 
608
                                        temp =  entryNumberValues[l];
 
609
                                if(temp==-1) tempForentryNumberValues[0] = "";
 
610
                                else tempForentryNumberValues[0] = ""+temp;
 
611
//tempForentryNumberValues[0]=dataElementCodes[l];
 
612
                                        
 
613
                                        l++;
 
614
                                                         
 
615
                                temp =  entryNumberValues[l];
 
616
                                if(temp==-1) tempForentryNumberValues[1] = "";
 
617
                                else tempForentryNumberValues[1] = ""+temp;
 
618
//tempForentryNumberValues[1]=dataElementCodes[l];
 
619
                                                                        
 
620
                                l++;
 
621
                                
 
622
                                tempForentryNumberValues[2]= tempForentryNumberValues[0] + tempForentryNumberValues[1];
 
623
                    } 
 
624
                %>              
 
625
           
 
626
                <tr>
 
627
                                <td id="<%=id1%>" name="<%=id1%>" width="3%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; " bordercolor="#111111" align="left">&nbsp;</td>
 
628
                                <td id="<%=id2%>" name="<%=id2%>" width="36%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:15; padding-right:1; " bordercolor="#111111" ></td>
 
629
                                <td id="<%=id3%>" name="<%=id3%>" width="45%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:3; padding-right:1; " bordercolor="#111111" dir="ltr"></td>
 
630
                                <td width="5%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:3; padding-right:1; " bordercolor="#111111" align="center"><font face="Arial" size="2"><%=tempForentryNumberValues[0]%></font></td>
 
631
                                <td width="5%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:3; padding-right:1; " bordercolor="#111111" align="center"><font face="Arial" size="2"><%=tempForentryNumberValues[1]%></font></td>
 
632
                                <td width="6%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:3; padding-right:1; " bordercolor="#111111" align="center"><font face="Arial" size="2"><%=tempForentryNumberValues[2]%></font></td>
 
633
                        </tr>
 
634
                
 
635
           <%   }
 
636
             else 
 
637
              {                
 
638
                         if(k==1 || k==3 || k==7 || k==16 || k==17 || k==20 || k==34 || k==38 || k==41 || k==48 || k==64 || k==68 || k==70 || k==74 || k==78 || k==79 || k==82 || k==85 || k==88 || k==91 || k==94 || k==97 || k==98 || k==102 || k==116) 
 
639
                    {
 
640
                      tempForentryNumberValues[0]= "";
 
641
 
 
642
                      l++;
 
643
                     }
 
644
                         else 
 
645
                  {
 
646
                                        temp =  entryNumberValues[l];
 
647
                                if(temp==-1) tempForentryNumberValues[0] = "";
 
648
                                else tempForentryNumberValues[0] = ""+temp;
 
649
//tempForentryNumberValues[0]=dataElementCodes[l];
 
650
                                                                                                        
 
651
                                        l++;
 
652
                  }     
 
653
               %>
 
654
                <tr>
 
655
                                <td id="<%=id1%>" name="<%=id1%>" width="3%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; " bordercolor="#111111" align="left">&nbsp;</td>
 
656
                                <td id="<%=id2%>" name="<%=id2%>" width="36%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:15; padding-right:1; " bordercolor="#111111" ></td>
 
657
                                <td id="<%=id3%>" name="<%=id3%>" width="45%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:3; padding-right:1; " bordercolor="#111111" dir="ltr"><font face="Arial" size="2"></font></td>
 
658
                                <td width="16%" style="border-collapse: collapse; border-top-style: solid; border-top-width: 1; border-bottom-style: solid; border-bottom-width: 1; border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; padding-bottom:0; padding-left:3; padding-right:1; " bordercolor="#111111" align="center" colspan="3"><font face="Arial" size="2"><%=tempForentryNumberValues[0]%></font></td>
 
659
                        </tr>
 
660
                <% 
 
661
             }                     
 
662
                 k++;
 
663
                }               
 
664
                 j++;
 
665
                %>
 
666
                </table>
 
667
                <%
 
668
         }      
 
669
 %>
 
670
</BODY>
 
671
</HTML>
 
 
b'\\ No newline at end of file'