1
package org.hisp.dhis.reports.util;
3
import java.util.ArrayList;
4
import java.util.Calendar;
5
import java.util.Collections;
7
import java.util.Iterator;
9
import java.util.regex.Matcher;
10
import java.util.regex.Pattern;
12
import org.hisp.dhis.aggregation.AggregationService;
13
import org.hisp.dhis.dataelement.DataElement;
14
import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
15
import org.hisp.dhis.dataelement.DataElementCategoryService;
16
import org.hisp.dhis.dataelement.DataElementService;
17
import org.hisp.dhis.dataset.DataSet;
18
import org.hisp.dhis.dataset.DataSetService;
19
import org.hisp.dhis.datavalue.DataValue;
20
import org.hisp.dhis.datavalue.DataValueService;
21
import org.hisp.dhis.indicator.Indicator;
22
import org.hisp.dhis.indicator.IndicatorService;
23
import org.hisp.dhis.organisationunit.OrganisationUnit;
24
import org.hisp.dhis.period.Period;
25
import org.hisp.dhis.period.PeriodService;
26
import org.hisp.dhis.period.PeriodType;
27
import org.hisp.dhis.system.util.MathUtils;
28
import org.springframework.jdbc.core.JdbcTemplate;
29
import org.springframework.jdbc.support.rowset.SqlRowSet;
31
public class ReportService
34
public static final String KEY_RAFOLDER = "reportfolder";
36
public static final String NULL_REPLACEMENT = "0";
38
// -------------------------------------------------------------------------
40
// -------------------------------------------------------------------------
42
private PeriodService periodService;
44
public void setPeriodService( PeriodService periodService )
46
this.periodService = periodService;
50
* private DBConnection dbConnection;
52
* public void setDbConnection( DBConnection dbConnection ) {
53
* this.dbConnection = dbConnection; }
56
private JdbcTemplate jdbcTemplate;
58
public void setJdbcTemplate( JdbcTemplate jdbcTemplate )
60
this.jdbcTemplate = jdbcTemplate;
63
private DataElementService dataElementService;
65
public void setDataElementService( DataElementService dataElementService )
67
this.dataElementService = dataElementService;
70
private AggregationService aggregationService;
72
public void setAggregationService( AggregationService aggregationService )
74
this.aggregationService = aggregationService;
77
private DataElementCategoryService dataElementCategoryService;
79
public void setDataElementCategoryService( DataElementCategoryService dataElementCategoryService )
81
this.dataElementCategoryService = dataElementCategoryService;
84
private DataValueService dataValueService;
86
public void setDataValueService( DataValueService dataValueService )
88
this.dataValueService = dataValueService;
91
private DataSetService dataSetService;
93
public void setDataSetService( DataSetService dataSetService )
95
this.dataSetService = dataSetService;
98
private IndicatorService indicatorService;
100
public void setIndicatorService( IndicatorService indicatorService )
102
this.indicatorService = indicatorService;
107
// -------------------------------------------------------------------------
109
// -------------------------------------------------------------------------
111
public List<Period> getMonthlyPeriods( Date start, Date end )
113
List<Period> periodList = new ArrayList<Period>( periodService.getPeriodsBetweenDates( start, end ) );
114
PeriodType monthlyPeriodType = PeriodType.getByNameIgnoreCase( "monthly" );
116
List<Period> monthlyPeriodList = new ArrayList<Period>();
117
for ( Period period : periodList )
119
if ( period.getPeriodType().getId() == monthlyPeriodType.getId() )
121
monthlyPeriodList.add( period );
124
return monthlyPeriodList;
128
* Returns the Period Object of the given date For ex:- if the month is 3,
129
* year is 2006 and periodType Object of type Monthly then it returns the
130
* corresponding Period Object
132
public Period getPeriodByMonth( int month, int year, PeriodType periodType )
134
int monthDays[] = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
136
Calendar cal = Calendar.getInstance();
137
cal.set( year, month, 1, 0, 0, 0 );
138
Date firstDay = new Date( cal.getTimeInMillis() );
140
if ( periodType.getName().equals( "Monthly" ) )
142
cal.set( year, month, 1, 0, 0, 0 );
145
cal.set( Calendar.DAY_OF_MONTH, monthDays[month] + 1 );
149
cal.set( Calendar.DAY_OF_MONTH, monthDays[month] );
152
else if ( periodType.getName().equals( "Yearly" ) )
154
cal.set( year, Calendar.DECEMBER, 31 );
156
Date lastDay = new Date( cal.getTimeInMillis() );
157
System.out.println( lastDay.toString() );
158
Period newPeriod = new Period();
159
newPeriod = periodService.getPeriod( firstDay, lastDay, periodType );
164
* Returns the child tree of the selected Orgunit
166
public List<OrganisationUnit> getAllChildren( OrganisationUnit selecteOU )
168
List<OrganisationUnit> ouList = new ArrayList<OrganisationUnit>();
169
Iterator it = selecteOU.getChildren().iterator();
170
while ( it.hasNext() )
172
OrganisationUnit orgU = (OrganisationUnit) it.next();
178
public List<Integer> getLinelistingRecordNos( OrganisationUnit organisationUnit, Period period, String lltype )
180
List<Integer> recordNosList = new ArrayList<Integer>();
182
// Connection con = dbConnection.openConnection();
184
// Statement st = null;
186
// ResultSet rs1 = null;
190
int dataElementid = 1020;
193
* if( lltype.equalsIgnoreCase( "lllivebirth" ) ) dataElementid =
194
* LLDataSets.LLB_CHILD_NAME; else if( lltype.equalsIgnoreCase(
195
* "lllivebirth" ) ) dataElementid = LLDataSets.LLD_CHILD_NAME; else if(
196
* lltype.equalsIgnoreCase( "lllivebirth" ) ) dataElementid =
197
* LLDataSets.LLMD_MOTHER_NAME;
200
if ( lltype.equalsIgnoreCase( "lllivebirth-l4" ) || lltype.equalsIgnoreCase( "lllivebirth-l5" )
201
|| lltype.equalsIgnoreCase( "lllivebirth-l6" ) )
202
dataElementid = 1020;
203
else if ( lltype.equalsIgnoreCase( "lldeath-l4" ) || lltype.equalsIgnoreCase( "lldeath-l5" )
204
|| lltype.equalsIgnoreCase( "lldeath-l6" ) )
205
dataElementid = 1027;
206
else if ( lltype.equalsIgnoreCase( "llmaternaldeath-l4" ) || lltype.equalsIgnoreCase( "llmaternaldeath-l5" )
207
|| lltype.equalsIgnoreCase( "llmaternaldeath-l6" ) )
208
dataElementid = 1032;
212
// st = con.createStatement();
214
query = "SELECT recordno FROM lldatavalue WHERE dataelementid = " + dataElementid + " AND periodid = "
215
+ period.getId() + " AND sourceid = " + organisationUnit.getId();
216
// rs1 = st.executeQuery( query );
218
SqlRowSet rs1 = jdbcTemplate.queryForRowSet( query );
222
recordNosList.add( rs1.getInt( 1 ) );
225
Collections.sort( recordNosList );
227
catch ( Exception e )
229
System.out.println( "SQL Exception : " + e.getMessage() );
233
* finally { try { if(st != null) st.close(); if(rs1 != null)
236
* if(con != null) con.close(); } catch( Exception e ) {
237
* System.out.println("SQL Exception : "+e.getMessage()); return null; }
239
*/// finally block end
240
return recordNosList;
243
public String getRAFolderName()
245
// Connection con = dbConnection.openConnection();
247
// Statement st = null;
249
// ResultSet rs1 = null;
251
String raFolderName = "ra_national";
257
// st = con.createStatement();
260
* query = "SELECT mvalue FROM maintenancein WHERE mkey LIKE '" +
261
* KEY_RAFOLDER + "'"; //rs1 = st.executeQuery( query );
263
* SqlRowSet rs1 = jdbcTemplate.queryForRowSet( query );
265
* if(rs1.next()) { raFolderName = rs1.getString( 1 ); }
267
//raFolderName = configurationService.getConfigurationByKey( Configuration_IN.KEY_REPORTFOLDER ).getValue();
269
catch ( Exception e )
271
System.out.println( "Exception : " + e.getMessage() );
275
* finally { try { if(st != null) st.close(); if(con != null)
276
* con.close(); } catch( Exception e ) {
277
* System.out.println("SQL Exception : "+e.getMessage()); return null; }
279
*/// finally block end
285
* Only report period type if element is in a data set?
287
* Returns the PeriodType Object for selected DataElement, If no PeriodType
288
* is found then by default returns Monthly Period type
290
public PeriodType getDataElementPeriodType( DataElement de )
292
for ( DataSet ds : dataSetService.getAllDataSets() )
294
if ( ds.getDataElements().contains( de ) )
296
return ds.getPeriodType();
304
private String getResultDataValue( String formula, Date startDate, Date endDate, OrganisationUnit organisationUnit )
308
// System.out.println( "expression : " + formula + " ***** " +
309
// String.valueOf( startDate ) + " **** "
310
// + String.valueOf( endDate ) );
314
Pattern pattern = Pattern.compile( "(\\[\\d+\\.\\d+\\])" );
316
Matcher matcher = pattern.matcher( formula );
317
StringBuffer buffer = new StringBuffer();
319
String resultValue = "";
321
while ( matcher.find() )
323
String replaceString = matcher.group();
325
replaceString = replaceString.replaceAll( "[\\[\\]]", "" );
326
String optionComboIdStr = replaceString.substring( replaceString.indexOf( '.' ) + 1,
327
replaceString.length() );
329
replaceString = replaceString.substring( 0, replaceString.indexOf( '.' ) );
331
int dataElementId = Integer.parseInt( replaceString );
332
int optionComboId = Integer.parseInt( optionComboIdStr );
334
DataElement dataElement = dataElementService.getDataElement( dataElementId );
335
DataElementCategoryOptionCombo optionCombo = dataElementCategoryService
336
.getDataElementCategoryOptionCombo( optionComboId );
338
if ( dataElement == null || optionCombo == null )
341
matcher.appendReplacement( buffer, replaceString );
344
if ( dataElement.getType().equalsIgnoreCase( "int" ) )
346
Double aggregatedValue = aggregationService.getAggregatedDataValue( dataElement, optionCombo,
347
startDate, endDate, organisationUnit );
348
if ( aggregatedValue == null )
350
replaceString = NULL_REPLACEMENT;
354
replaceString = String.valueOf( aggregatedValue );
363
PeriodType dePeriodType = getDataElementPeriodType( dataElement );
364
List<Period> periodList = new ArrayList<Period>( periodService.getIntersectingPeriodsByPeriodType(
365
dePeriodType, startDate, endDate ) );
366
Period tempPeriod = new Period();
367
if ( periodList == null || periodList.isEmpty() )
370
matcher.appendReplacement( buffer, replaceString );
375
tempPeriod = (Period) periodList.get( 0 );
378
DataValue dataValue = dataValueService.getDataValue( organisationUnit, dataElement, tempPeriod,
381
if ( dataValue != null )
383
// Works for both text and boolean data types
385
replaceString = dataValue.getValue();
391
if ( replaceString == null )
394
matcher.appendReplacement( buffer, replaceString );
396
resultValue = replaceString;
399
matcher.appendTail( buffer );
407
d = MathUtils.calculateExpression( buffer.toString() );
409
catch ( Exception e )
422
// This is to display financial data as it is like 2.1476838
423
resultValue = "" + d;
425
// These lines are to display financial data that do not
431
resultValue = "" + (int) d / 10;
436
// These line are to display non financial data that do not
438
// if ( !(reportModelTB.equalsIgnoreCase( "STATIC-FINANCIAL"
440
resultValue = "" + (int) d;
442
// if ( resultValue.equalsIgnoreCase( "0" ) )
451
resultValue = buffer.toString();
454
if ( resultValue.equalsIgnoreCase( "" ) )
459
catch ( NumberFormatException ex )
461
throw new RuntimeException( "Illegal DataElement id", ex );
465
private String getIndividualResultDataValue( String formula, Date startDate, Date endDate,
466
OrganisationUnit organisationUnit )
472
Pattern pattern = Pattern.compile( "(\\[\\d+\\.\\d+\\])" );
474
Matcher matcher = pattern.matcher( formula );
475
StringBuffer buffer = new StringBuffer();
477
String resultValue = "";
478
boolean valueDoesNotExist = true;
480
while ( matcher.find() )
483
String replaceString = matcher.group();
485
replaceString = replaceString.replaceAll( "[\\[\\]]", "" );
486
String optionComboIdStr = replaceString.substring( replaceString.indexOf( '.' ) + 1,
487
replaceString.length() );
489
replaceString = replaceString.substring( 0, replaceString.indexOf( '.' ) );
491
int dataElementId = Integer.parseInt( replaceString );
492
int optionComboId = Integer.parseInt( optionComboIdStr );
494
DataElement dataElement = dataElementService.getDataElement( dataElementId );
495
DataElementCategoryOptionCombo optionCombo = dataElementCategoryService
496
.getDataElementCategoryOptionCombo( optionComboId );
498
if ( dataElement == null || optionCombo == null )
501
matcher.appendReplacement( buffer, replaceString );
504
if ( dataElement.getType().equalsIgnoreCase( "int" ) )
507
PeriodType dePeriodType = getDataElementPeriodType( dataElement );
508
List<Period> periodList = new ArrayList<Period>( periodService.getIntersectingPeriodsByPeriodType(
509
dePeriodType, startDate, endDate ) );
511
if ( periodList == null || periodList.isEmpty() )
514
matcher.appendReplacement( buffer, replaceString );
520
double aggregatedValue = 0.0;
521
for ( Period tempPeriod : periodList )
523
DataValue dataValue = dataValueService.getDataValue( organisationUnit, dataElement,
524
tempPeriod, optionCombo );
526
if ( dataValue != null )
528
aggregatedValue += Double.parseDouble( dataValue.getValue() );
530
valueDoesNotExist = false;
534
replaceString = String.valueOf( aggregatedValue );
543
PeriodType dePeriodType = getDataElementPeriodType( dataElement );
544
List<Period> periodList = new ArrayList<Period>( periodService.getIntersectingPeriodsByPeriodType(
545
dePeriodType, startDate, endDate ) );
546
Period tempPeriod = new Period();
547
if ( periodList == null || periodList.isEmpty() )
550
matcher.appendReplacement( buffer, replaceString );
555
tempPeriod = (Period) periodList.get( 0 );
558
DataValue dataValue = dataValueService.getDataValue( organisationUnit, dataElement, tempPeriod,
561
if ( dataValue != null )
563
// Works for both text and boolean data types
565
replaceString = dataValue.getValue();
566
valueDoesNotExist = false;
572
if ( replaceString == null )
575
matcher.appendReplacement( buffer, replaceString );
577
resultValue = replaceString;
580
matcher.appendTail( buffer );
587
d = MathUtils.calculateExpression( buffer.toString() );
589
catch ( Exception e )
603
// This is to display financial data as it is like 2.1476838
604
resultValue = "" + d;
606
// These lines are to display financial data that do not
612
resultValue = "" + (int) d / 10;
617
// These line are to display non financial data that do not
619
// if ( !(reportModelTB.equalsIgnoreCase( "STATIC-FINANCIAL"
621
resultValue = "" + (int) d;
623
// if ( resultValue.equalsIgnoreCase( "0" ) )
631
resultValue = buffer.toString();
634
if ( valueDoesNotExist )
637
if ( resultValue.equalsIgnoreCase( "" ) )
642
catch ( NumberFormatException ex )
644
throw new RuntimeException( "Illegal DataElement id", ex );
648
private String getBooleanDataValue( String formula, Date startDate, Date endDate, OrganisationUnit organisationUnit )
654
Pattern pattern = Pattern.compile( "(\\[\\d+\\.\\d+\\])" );
656
Matcher matcher = pattern.matcher( formula );
657
StringBuffer buffer = new StringBuffer();
659
while ( matcher.find() )
661
String replaceString = matcher.group();
663
replaceString = replaceString.replaceAll( "[\\[\\]]", "" );
664
String optionComboIdStr = replaceString.substring( replaceString.indexOf( '.' ) + 1,
665
replaceString.length() );
667
replaceString = replaceString.substring( 0, replaceString.indexOf( '.' ) );
669
int dataElementId = Integer.parseInt( replaceString );
670
int optionComboId = Integer.parseInt( optionComboIdStr );
672
DataElement dataElement = dataElementService.getDataElement( dataElementId );
673
DataElementCategoryOptionCombo optionCombo = dataElementCategoryService
674
.getDataElementCategoryOptionCombo( optionComboId );
676
if ( dataElement == null || optionCombo == null )
679
matcher.appendReplacement( buffer, replaceString );
683
if ( dataElement.getType().equalsIgnoreCase( "bool" ) )
686
PeriodType dePeriodType = getDataElementPeriodType( dataElement );
687
List<Period> periodList = new ArrayList<Period>( periodService.getIntersectingPeriodsByPeriodType(
688
dePeriodType, startDate, endDate ) );
689
Period tempPeriod = new Period();
690
if ( periodList == null || periodList.isEmpty() )
693
matcher.appendReplacement( buffer, replaceString );
698
tempPeriod = (Period) periodList.get( 0 );
701
DataValue dataValue = dataValueService.getDataValue( organisationUnit, dataElement, tempPeriod,
704
if ( dataValue != null )
706
// Works for both text and boolean data types
708
if ( dataValue.getValue().equalsIgnoreCase( "true" ) )
710
replaceString = "Yes";
712
else if ( dataValue.getValue().equalsIgnoreCase( "false" ) )
714
replaceString = "No";
718
replaceString = dataValue.getValue();
730
Double aggregatedValue = aggregationService.getAggregatedDataValue( dataElement, optionCombo,
731
startDate, endDate, organisationUnit );
732
if ( aggregatedValue == null )
734
replaceString = NULL_REPLACEMENT;
738
replaceString = String.valueOf( aggregatedValue );
743
matcher.appendReplacement( buffer, replaceString );
746
matcher.appendTail( buffer );
748
String resultValue = "";
754
d = MathUtils.calculateExpression( buffer.toString() );
756
catch ( Exception e )
766
d = Math.round( d * Math.pow( 10, 1 ) ) / Math.pow( 10, 1 );
767
resultValue = "" + (int) d;
777
resultValue = buffer.toString();
781
catch ( NumberFormatException ex )
783
throw new RuntimeException( "Illegal DataElement id", ex );
787
private String getResultIndicatorValue( String formula, Date startDate, Date endDate,
788
OrganisationUnit organisationUnit )
795
Pattern pattern = Pattern.compile( "(\\[\\d+\\.\\d+\\])" );
797
Matcher matcher = pattern.matcher( formula );
798
StringBuffer buffer = new StringBuffer();
800
while ( matcher.find() )
802
String replaceString = matcher.group();
804
replaceString = replaceString.replaceAll( "[\\[\\]]", "" );
806
replaceString = replaceString.substring( 0, replaceString.indexOf( '.' ) );
808
int indicatorId = Integer.parseInt( replaceString );
810
Indicator indicator = indicatorService.getIndicator( indicatorId );
812
if ( indicator == null )
815
matcher.appendReplacement( buffer, replaceString );
820
Double aggregatedValue = aggregationService.getAggregatedIndicatorValue( indicator, startDate, endDate,
823
if ( aggregatedValue == null )
825
replaceString = NULL_REPLACEMENT;
829
replaceString = String.valueOf( aggregatedValue );
832
matcher.appendReplacement( buffer, replaceString );
835
matcher.appendTail( buffer );
837
String resultValue = "";
843
d = MathUtils.calculateExpression( buffer.toString() );
845
catch ( Exception e )
853
d = Math.round( d * Math.pow( 10, 1 ) ) / Math.pow( 10, 1 );
854
resultValue = "" + d;
864
resultValue = buffer.toString();
868
catch ( NumberFormatException ex )
870
throw new RuntimeException( "Illegal DataElement id", ex );
874
private String getIndividualResultIndicatorValue( String formula, Date startDate, Date endDate,
875
OrganisationUnit organisationUnit )
882
Pattern pattern = Pattern.compile( "(\\[\\d+\\.\\d+\\])" );
884
Matcher matcher = pattern.matcher( formula );
885
StringBuffer buffer = new StringBuffer();
887
while ( matcher.find() )
889
String replaceString = matcher.group();
891
replaceString = replaceString.replaceAll( "[\\[\\]]", "" );
893
replaceString = replaceString.substring( 0, replaceString.indexOf( '.' ) );
895
int indicatorId = Integer.parseInt( replaceString );
897
Indicator indicator = indicatorService.getIndicator( indicatorId );
899
if ( indicator == null )
902
matcher.appendReplacement( buffer, replaceString );
907
String numeratorExp = indicator.getNumerator();
908
String denominatorExp = indicator.getDenominator();
909
int indicatorFactor = indicator.getIndicatorType().getFactor();
910
String numeratorVal = getIndividualResultDataValue( numeratorExp, startDate, endDate, organisationUnit );
911
String denominatorVal = getIndividualResultDataValue( denominatorExp, startDate, endDate,
914
double numeratorValue;
917
numeratorValue = Double.parseDouble( numeratorVal );
919
catch ( Exception e )
921
System.out.println( "Exception while getting Numerator : " + numeratorExp + " for Indicaotr "
922
+ indicator.getName() );
923
numeratorValue = 0.0;
926
double denominatorValue;
929
denominatorValue = Double.parseDouble( denominatorVal );
931
catch ( Exception e )
933
System.out.println( "Exception while getting Deniminator : " + denominatorExp + " for Indicaotr "
934
+ indicator.getName() );
935
denominatorValue = 1.0;
938
double aggregatedValue;
941
aggregatedValue = (numeratorValue / denominatorValue) * indicatorFactor;
943
catch ( Exception e )
945
System.out.println( "Exception while calculating Indicator value for Indicaotr "
946
+ indicator.getName() );
947
aggregatedValue = 0.0;
950
replaceString = String.valueOf( aggregatedValue );
953
matcher.appendReplacement( buffer, replaceString );
956
matcher.appendTail( buffer );
958
String resultValue = "";
964
d = MathUtils.calculateExpression( buffer.toString() );
966
catch ( Exception e )
974
d = Math.round( d * Math.pow( 10, 1 ) ) / Math.pow( 10, 1 );
975
resultValue = "" + d;
985
resultValue = buffer.toString();
989
catch ( NumberFormatException ex )
991
throw new RuntimeException( "Illegal DataElement id", ex );