I am a newbie to Java and am trying to format an output for my beginner program I am trying to work out on.
The final output should somehow look like this:
Page 1
Sorted By: Campus Code College Code Term Code
Campus Code: Extention
College Code: Faculty of Agriculture
Term Code: 202410
Student ID Name Fee
Amount
----------------------------------------------------------------------------------------------------------------------------------
000145713 Anderson, Julia 113.79
000637412 Browne, Emily 13.05
000038860 Dean, George -163.99
000172312 Green, Gabriel -308.94
--------------------------------------
TOTAL FOR: Extension Faculty of Agriculture 202410 STUDENTS: 4 AMOUNT -346.09
Page 2
Sorted By: Campus Code College Code Term Code
Campus Code: Extension
College Code: Faculty of Agriculture
Term Code: 202420
Student ID Name Fee
Amount
----------------------------------------------------------------------------------------------------------------------------------
000127655 Adams, Tom -800.00
000933550 Aitken, Marlowe -788.58
000175243 Alexander, Julian -70.74
000157977 Allan, Agnes -700.00
000813241 Allison, Isaac 4.55
000135782 Anderson, Tyler 94.70
000077141 Armstrong, Burton 0.18
000157135 Blair, Meredith 6.96
000174165 Dunn, Olive 18.27
--------------------------------------
TOTAL FOR: Extension Faculty of Agriculture 202420 STUDENTS: 9 AMOUNT -2,234.66
Page 3
Sorted By: Campus Code College Code Term Code
Campus Code: Extension
College Code: Faculty of Science
Term Code: 202420
Student ID Name Fee
Amount
----------------------------------------------------------------------------------------------------------------------------------
000183967 Barclay, Priscilla 0.10
000144333 Macleod, Zack -2,053.18
000174281 Murdoch, Eva -244.97
000123253 Park, Elizabeth -600.00
--------------------------------------
TOTAL FOR: Extension Faculty of Science 202420 STUDENTS: 4 AMOUNT -2,898.05
Page 4
Sorted By: Campus Code College Code Term Code
Campus Code: Main Campus
College Code: Fac of Architecture & Planning
Term Code: 202410
Student ID Name Fee
Amount
----------------------------------------------------------------------------------------------------------------------------------
000125432 Anderson, Sabrina 200.00
000763448 Elliott, Sophia 200.00
--------------------------------------
TOTAL FOR: Main Campus Fac of Architecture & Planning 202410 STUDENTS: 2 AMOUNT 400.00
The method I am stuck with is this one, I want to process the sort variables in reverse order to simplify printing to the output but not sure how to proceed with what I have got, I am trying to first process the first sort then I have to check if sort3 changes then print the output then if it changes or sort2 changes or sort3 changes, then that would create a new page to the output. The sorts in the output above is as follows: SORT1 is Campus Code, SORT2 is College Code and SORT3 is Term Code. Any help is appreciated, TIA!
private void createReport(Connection connection, String tableName, int sortCount,
String sort1Attr, String sort2Attr, String sort3Attr,
String date, ArrayList<String> pTerm,String initiator,
String databaseName, String pPrintReport, String pPrintDetail,
ArrayList<String> studentReport, ArrayList<String> errors) throws SQLException {
//Tracks the page number
int[] pageCount = {0};
//Tracks the line count of the current page
int[] lineCount= {0};
//Tracks the number of filled lines on the current page
int[] pageLength = {0};
String queryString ="";
if(sort1Exists) queryString += sort1Attr + ", ";
if(sort2Exists) queryString += sort2Attr + ", ";
if(sort3Exists) queryString += sort3Attr + ", ";
//Selects records from the temporary table
String selectRecordsSQL =
" SELECT " + queryString
+ " tbraccd_pidm as PIDM, "
+ " SUM(decode(tbbdetc_type_ind,'P',(tbraccd_amount * -1),tbraccd_amount)) as AMOUNT, "
+ " spriden_id as SPRIDEN_ID, "
+ " UPPER(spriden_last_name) as LAST_NAME, "
+ " UPPER(spriden_first_name) as FIRST_NAME, "
+ " spriden_last_name as SPRIDEN_LNAME, "
+ " spriden_first_name as SPRIDEN_FNAME"
+ " FROM " + tableName + " b "
+ " WHERE (b.sgbstdn_term_code_eff = (SELECT MAX(sgbstdn_term_code_eff) "
+ " FROM sgbstdn "
+ " WHERE sgbstdn_pidm = b.sgbstdn_pidm "
+ " AND sgbstdn_term_code_eff <= b.tbraccd_term_code) "
+ " OR b.sgbstdn_term_code_eff IS NULL) "
+ " GROUP BY " + queryString + " tbraccd_pidm, spriden_id, spriden_last_name, spriden_first_name "
+ " HAVING SUM(DECODE(tbbdetc_type_ind,'P',(tbraccd_amount * -1),tbraccd_amount)) <> 0"
+ " UNION "
+ " SELECT " + queryString
+ " tbraccd_pidm, "
+ " SUM(decode(tbbdetc_type_ind,'P',(tbraccd_amount * -1),tbraccd_amount)), "
+ " spriden_id, "
+ " UPPER(spriden_last_name), "
+ " UPPER(spriden_first_name), "
+ " spriden_last_name, "
+ " spriden_first_name "
+ " FROM " + tableName + " b "
+ " WHERE (b.sgbstdn_term_code_eff = (SELECT MIN(sgbstdn_term_code_eff) "
+ " FROM sgbstdn "
+ " WHERE sgbstdn_pidm = b.sgbstdn_pidm) "
+ " AND sgbstdn_term_code_eff > b.tbraccd_term_code) "
+ " GROUP BY " + queryString + " tbraccd_pidm, spriden_id, spriden_last_name, spriden_first_name "
+ " HAVING SUM(DECODE(tbbdetc_type_ind,'P',(tbraccd_amount * -1),tbraccd_amount)) <> 0 "
+ " ORDER BY " + queryString + " LAST_NAME, FIRST_NAME ";
try {
PreparedStatement selectRecords = connection.prepareStatement(selectRecordsSQL);
ResultSet rs = selectRecords.executeQuery();
//Tracks the count
int reportCount = 0;
//Tracks the record count grand totals
int recordTotal = 0;
BigDecimal total = BigDecimal.ZERO.setScale(2);
//Full description of the sort variable's current value
String sort1Desc= "***";
String sort2Desc= "***";
String sort3Desc= "***";
//Values of the previous sort quantities
//Used to detect when one of the values changes
String prevSort1Value= "*";
String prevSort2Value= "*";
String prevSort3Value= "*";
while(rs.next()) {
//Extracts the current record from the ResultSet
String sort1Value = rs.getString(1);
String sort2Value = rs.getString(2);
String sort3Value = rs.getString(3);
String pidm = rs.getString("PIDM");
BigDecimal amount = rs.getBigDecimal("AMOUNT").setScale(2);
String ID = rs.getString("SPRIDEN_ID");
String upperFName = rs.getString("FIRST_NAME");
String upperLName = rs.getString("LAST_NAME");
String spridenFName = rs.getString("SPRIDEN_FNAME");
String spridenLName = rs.getString("SPRIDEN_LNAME");
//If any of the Sort values are null, sets them to blank
if(sort1Value==null) sort1Value = "";
if(sort2Value==null) sort2Value = "";
if(sort3Value==null) sort3Value = "";
total = total.add(amount);
studentTotal++;
//Processes the third sort value
if(sort3Exists && (!sort3Value.equals(prevSort3Value) ||
!sort2Value.equals(prevSort2Value) ||
!sort1Value.equals(prevSort1Value))) {
//Omits printing the previous record when the first record is being processed
if(!sort3Desc.equals("***")) {
}
//Obtains the full description of the new second sort value
sort3Desc = getSortDescription(connection, pSort3, sort3GetDescValue, sort3GetDescTable,
sort3GetDescCode, sort3Value, errors);
}
//Processes the second sort value
if(sort2Exists && (!sort2Value.equals(prevSort2Value) ||
!sort1Value.equals(prevSort1Value))) {
//Omits printing the previous record when the first record is being processed
if(!sort2Desc.equals("***")) {
}
//Obtains the full description of the new second sort value
sort2Desc = getSortDescription(connection, pSort2, sort2GetDescValue, sort2GetDescTable,
sort2GetDescCode, sort2Value, errors);
}
//Processes the first sort value
if(sort1Exists && !sort1Value.equals(prevSort1Value)) {
//Omits printing the previous record when the first record is being processed
if(!sort1Desc.equals("***")) {
}
//Obtains the full description of the new second sort value
sort1Desc = getSortDescription(connection, pSort1, sort1GetDescValue, sort1GetDescTable,
sort1GetDescCode, sort1Value, errors);
}
//Inserts the header for the first page of the main report
//(This is done after the description of the first sort value has been obtained
//since it is needed for the header)
if(pageCount[0] == 0) {
startNewPage(studentReport, pPrintReport, date, databaseName, pageCount, pTerm,
pInitiator, sort1Desc, sort2Desc, sort3Desc,
pSort1, pSort2, pSort3, pageLength, delim);
}
studentCount++;
studentReport.add("");
studentReport.add(Library.rPad(ID, 11, ' ') + Library.rPad(spridenLName + ", " + spridenFName, 30, ' ') + amount);
//Updates the previous value of each of the sort values
prevSort1Value = sort1Value;
prevSort2Value = sort2Value;
prevSort3Value = sort3Value;
}
String grandTotal = newline + Library.rPad("SUMMARY: ", 12, ' ') + total + " " + studentTotal;
selectRecords.close();
}
catch(SQLException e) {
errors.add("Exception caught in createStudentReport method");
throw e;
}
} //end method
1