2
<%@ page import="java.sql.*,java.util.*" %>
3
<%@ page import="com.opensymphony.xwork.util.OgnlValueStack" %>
5
<%@ page session="true"%>
10
// For finding organisationunit name of selected Orgunit based on Orgunit id
14
// For finding start date of selected period based on period id
18
// For finding child orgunit ids and names based on selected orgunit id
22
// For finding child orgunit
27
String userName = "dhis";
29
String urlForConnection = "jdbc:mysql://localhost/jh_dhis2";
31
OgnlValueStack stack = (OgnlValueStack)request.getAttribute("webwork.valueStack");
33
String selectedId = (String) stack.findValue( "orgUnitId" );
34
int selectedOrgUnitID = Integer.parseInt( selectedId );
36
// String selectedPeriodId = (String) stack.findValue( "periodSelect" );
37
// int selectedDataPeriodID = Integer.parseInt( selectedPeriodId );
39
String startingDate = (String) stack.findValue( "startingPeriod" );
40
String endingDate = (String) stack.findValue( "endingPeriod" );
43
String monthlyPeriodId = (String) stack.findValue( "monthlyPeriodTypeId" );
44
int periodTypeID = Integer.parseInt( monthlyPeriodId );
47
String selectedOrgUnitName = "";
48
String selectedDataPeriodStartDate = "";
50
String monthNames[] = { "", "January", "February", "March", "April", "May", "June", "July", "August", "September","October", "November", "December" };
52
String monthlyDataElements[] = {
64
List childOrgUnitIDs = new ArrayList();
65
List childOrgUnitNames = new ArrayList();
67
int childOrgUnitCount = 0;
70
int totPopulation = 0;
72
int tempval[] = new int[16];
73
int total[] = new int[16];
79
Class.forName ("com.mysql.jdbc.Driver").newInstance ();
80
con = DriverManager.getConnection (urlForConnection, userName, password);
82
st1=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
83
st2=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
84
st3=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
85
st4=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
87
//rs1 = st1.executeQuery("SELECT organisationunit.shortname FROM organisationunit WHERE id ="+selectedOrgUnitID);
88
rs1 = st1.executeQuery("SELECT organisationunit.shortname FROM organisationunit WHERE organisationunitid ="+selectedOrgUnitID);
89
if(rs1.next()) { selectedOrgUnitName = rs1.getString(1); }
90
else { selectedOrgUnitName = ""; }
92
// rs2 = st2.executeQuery("select startDate from period where id = "+selectedDataPeriodID);
93
// if(rs2.next()) { selectedDataPeriodStartDate = rs2.getDate(1).toString(); }
95
selectedDataPeriodStartDate = startingDate;
98
//rs3 = st3.executeQuery("select id,shortname from organisationunit where parent = "+selectedOrgUnitID);
99
rs3 = st3.executeQuery("select organisationunitid,shortname from organisationunit where parentid = "+selectedOrgUnitID);
102
Integer tempInt = new Integer(rs3.getInt(1));
103
childOrgUnitIDs.add(childOrgUnitCount,tempInt);
104
childOrgUnitNames.add(childOrgUnitCount,rs3.getString(2));
109
catch(Exception e) { out.println(e.getMessage()); }
113
String partsOfDataPeriodStartDate[] = selectedDataPeriodStartDate.split("-");
114
int lastYear = Integer.parseInt(partsOfDataPeriodStartDate[0]) - 1;
115
String lastDataPeriodStartDate = lastYear+"-"+partsOfDataPeriodStartDate[1]+"-"+partsOfDataPeriodStartDate[2];
117
int tempForMonth1 = Integer.parseInt(partsOfDataPeriodStartDate[1]);
120
if(tempForMonth1 < 4) { tempForYear = lastYear; }
121
else { tempForYear = lastYear + 1; }
123
String tempForMonth2 = "";
124
if(tempForMonth1-1 ==0) tempForMonth2 = "-"+(tempForMonth1-1)+"-01";
125
else if(tempForMonth1-1 <= 9) {tempForMonth2 = "-0"+(tempForMonth1-1)+"-01";}
126
else tempForMonth2 = "-"+(tempForMonth1-1)+"-01";
128
String curYearStart = tempForYear+"-04-01";
129
String lastYearStart = (tempForYear-1)+"-04-01";
130
String lastYearEnd = lastYear+"-"+partsOfDataPeriodStartDate[1]+"-"+partsOfDataPeriodStartDate[2];
131
String curYearEnd = ""+partsOfDataPeriodStartDate[0]+""+tempForMonth2;
140
<TITLE>Monthly Report on Kala-Azar</TITLE>
143
<P align="center"><FONT face="Arial" size="3"><b><u>Kala-Azar Report</u></b></font><br></p>
145
<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">
146
<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%">
147
<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">
148
<font face="Arial" size="3"><b>District : <%=selectedOrgUnitName%></b></font>
150
<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">
151
<font face="Arial" size="2"><b>Month : <%=monthNames[Integer.parseInt(partsOfDataPeriodStartDate[1])]%> - <%=partsOfDataPeriodStartDate[0]%> </b></font>
163
while(count < childOrgUnitCount)
167
if(count != 0) {%></table><div align="right"><font face="Arial" size="1"><i>(page contd.)</i></font></div> <br><%}%>
169
<TABLE style="BORDER-COLLAPSE: collapse" borderColor=#111111 cellSpacing=3 cellPadding=3 width="100%" border=1>
171
<TD align="center" width="3%" rowSpan=2><b><font face="Arial" size="2">Sl. No.</font></b></TD>
172
<TD align="center" width="43%" rowSpan=2><b><font face="Arial" size="2">PHC</font></b></TD>
173
<TD align="center" width="18%" colspan="3"><b><font face="Arial" size="2">Report up to previous month</font></b></TD>
174
<TD align="center" width="18%" colspan="3"><b><font face="Arial" size="2">During the month</font></b></TD>
175
<TD align="center" width="18%" colspan="3"><b><font face="Arial" size="2">Progressive Total</font></b></TD>
178
<TD align="center" width="6%"><b><font face="Arial" size="2">Case</font></b></TD>
179
<TD align="center" width="6%"><b><font face="Arial" size="2">Death</font></b></TD>
180
<TD align="center" width="6%"><b><font face="Arial" size="2">Treated</font></b></TD>
181
<TD align="center" width="6%"><b><font face="Arial" size="2">Case</font></b></TD>
182
<TD align="center" width="6%"><b><font face="Arial" size="2">Death</font></b></TD>
183
<TD align="center" width="6%"><b><font face="Arial" size="2">Treated</font></b></TD>
184
<TD align="center" width="6%"><b><font face="Arial" size="2">Case</font></b></TD>
185
<TD align="center" width="6%"><b><font face="Arial" size="2">Death</font></b></TD>
186
<TD align="center" width="6%"><b><font face="Arial" size="2">Treated</font></b></TD>
190
Integer temp1 = (Integer) childOrgUnitIDs.get(count);
191
int currentChildID = temp1.intValue();
197
// 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 '"+curYearEnd+"' and periodType = "+periodTypeID+") AND datavalue.source ="+currentChildID+" AND dataelement.code like '"+monthlyDataElements[i]+"'";
198
//else query = "SELECT datavalue.value FROM datavalue INNER JOIN dataelement ON datavalue.dataElement = dataelement.id WHERE datavalue.period ="+selectedDataPeriodID+" AND datavalue.source ="+currentChildID+" AND dataelement.code like '"+monthlyDataElements[i]+"'";
203
//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 '"+curYearEnd+"' and periodType = "+periodTypeID+") AND datavalue.source in (select id from organisationunit where parent ="+currentChildID+") AND dataelement.code like '"+monthlyDataElements[i]+"'";
204
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 '"+curYearEnd+"' and periodtypeid = "+periodTypeID+") AND datavalue.sourceid in (select organisationunitid from organisationunit where parentid ="+currentChildID+") AND dataelement.code like '"+monthlyDataElements[i]+"'";
206
//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 ="+currentChildID+") AND dataelement.code like '"+monthlyDataElements[i]+"'";
207
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 ="+currentChildID+") AND dataelement.code like '"+monthlyDataElements[i]+"'";
209
rs4 = st4.executeQuery(query);
211
if(!rs4.next()) { tempval[i] = 0; }
212
else { tempval[i] = rs4.getInt(1); }
213
total[i] += tempval[i];
219
<TD width="3%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=(count+1)%>.</font></TD>
220
<TD width="43%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=childOrgUnitNames.get(count)%></font></TD>
221
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=tempval[0]%></font></TD>
222
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=tempval[1]%></font></TD>
223
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=tempval[2]%></font></TD>
224
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=tempval[3]%></font></TD>
225
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=tempval[4]%></font></TD>
226
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=tempval[5]%></font></TD>
227
<TD width="6%" HEIGHT="20" align="center"> </TD>
228
<TD width="6%" HEIGHT="20" align="center"> </TD>
229
<TD width="6%" HEIGHT="20" align="center"> </TD>
235
catch(Exception e) { out.println(e.getMessage()); }
240
if(rs1!=null) rs1.close(); if(st1!=null) st1.close();
241
if(rs2!=null) rs2.close(); if(st2!=null) st2.close();
242
if(rs3!=null) rs3.close(); if(st3!=null) st3.close();
243
if(rs4!=null) rs4.close(); if(st4!=null) st4.close();
245
if(con!=null) con.close();
247
catch(Exception e) { out.println(e.getMessage()); }
248
} // finally block end
252
<TD width="3%" HEIGHT="20" align="center"><FONT face="Arial" size="1"> </font></TD>
253
<TD width="43%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><b>Total</b></font></TD>
254
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=total[0]%></font></TD>
255
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=total[1]%></font></TD>
256
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=total[2]%></font></TD>
257
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=total[3]%></font></TD>
258
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=total[4]%></font></TD>
259
<TD width="6%" HEIGHT="20" align="center"><FONT face="Arial" size="2"><%=total[5]%></font></TD>
260
<TD width="6%" HEIGHT="20" align="center"></TD>
261
<TD width="6%" HEIGHT="20" align="center"> </TD>
262
<TD width="6%" HEIGHT="20" align="center"> </TD>
b'\\ No newline at end of file'