I have a method in dao class called "getDetails". In this method, I union the two select statments from two tables with almost same field called "main shop" & "sub shops" and put those queries to preparedstatement. Then I put the preparedStatement to resultSet.
This "getDetials" method return "details" and I'll use it into another method in mediator called "writefile" in order to print the values in microsoft word like "writefile(details)". In the "writefile" method, there are strings of values and put the values of "details" to respective string. And append the "outputString" for each value.
On the input screen, user may check "sub shops" check box and will fill the details of "sub shops" after checking it. If they don't check, they need to fill only "main details" and not "sub shops". If they check "main shop", I need to print only one letter. If they check "sub shops" and fill the "quantity of sub shops", I need to print the letters where the letter number is equal to "quantity of subshops" + one(main letter). The letter format is totally same but only change the value of the fields of "shop name" (main or sub shop), "shop bank code" (main or sub shop) according to user's choice.
In the mediator, I loop the "writefile(details)" according to the quantity of "sub shops".
The problem is the data in different letters are same (only show the "main shop" data) though the letter quanity is correct (eg. if there are two "sub shops", it prints three letters). How should I do to get the value of "sub shops" in subsequent letters after "main shop" letter is printed.
This is "writefile" method
if(flag){
this.getHeading();
StringBuffer outputString = new StringBuffer();
Date date = new Date();
SimpleDateFormat formatter=new SimpleDateFormat("dd MMMMM,yyyy");
String dateString=formatter.format(date);
details.sysDate =dateString;
String date1=(String)details.getSysDate();
if(details.getNo() != null){
no=details.getNo();
}else {no=" ";}
if(details.bankName() != null){
bname=details.getBankName();
}else {bname = " ";}
outputString.append('\"');
outputString.append(date1);
outputString.append('\"');
outputString.append(",");
outputString.append('\"');
outputString.append(no);
outputString.append('\"');
outputString.append(",");
outputString.append('\"');
outputString.append(bname);
outputString.append('\"');
outputString.append(",");
outputString.append('\"');
dos.writeBytes(outputString.toString());
dos.flush();
dos.close();
fos.close();
}
The query sample is
public Details getDetails (String No, String LoginID, String LetterID)
{
connection = DBConnection.getConnection();
StringBuffer query = new StringBuffer();
query.append("select TO_CHAR(TRUNC(SYSDATE),'DD MONTH,YYYY'),a.no, b.bank_name");
query.append("from t_newappl a,t_newappl_bank b where b.no = a.no and a.no=(select no from t_newappl whereno=?) and rownum=1 and status = 'PEND'");
query.append(" union all ");
query.append("select TO_CHAR(TRUNC(SYSDATE),'DD MONTH,YYYY'),a.no,b.bank_name");
query.append("from t_newappl a,t_newappl_bank b, newappl_sub c, t_newappl_sub_bank d where a.no = c.no and c.sub_id= d.sub_id and a.no=(select no from t_newappl where no=?) and rownum=1 and d.status = 'SPEND'");
PreparedStatement preparedStatement = connection.prepareStatement(query.toString());
preparedStatement.setString(1,ApplicationNo);
preparedStatement.setString(2,ApplicationNo);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
Details = new BankDetails();
Details.No= Util.Trim(resultSet.getString("NO"));
Details.BankName= Util.Trim(resultSet.getString("BANK_NAME"));
}
resultSet.close();
preparedStatement.close();
return Details;
}
The looping to print letter is
int retail = Outletht.size();
int ctr = 0;
for (int i = 1; i <= subshop.size ; i++ ){
ctr++;
Letter Letter = new Letter(lmediator);
BankDetails Details = Letter.printLetter(applicationNumber);
if (Details!=null){
if (ctr == 1) {
if ((Details.getNo() == null)
&& (Details.getLetterID() == null)
&& ((Details.getLoginID() == null) || (Details.getLoginID().equals("")))) {
} else {
Letter.databaseUpdate(Details);
}
}
Letter.writefile(Details);
Letter.callfile(Details);
context.showMessage("I01015");
}
else
{
context.showMessage("I04004");
}
}