Link to home
Start Free TrialLog in
Avatar of wjh7554
wjh7554

asked on

Pseudocode Enquiry: pls correct.

This is my DB schema...

P.Code   Qty.    Amt.
=====================
ABC       3       100
ABC       6         2
ABC       12       12
MNB        4       23
PLM        2       12



I need to do some sort like summary page to sum all the same p.code together to see the Qty and Amt.

This is my pseudocode,

-- select the data from DB order by P.COde (to make sure it's in sequence)

-- String ori_pcode = rs.getString("PCode");
   int counter = 1;

public Integer getQty() {
   if (Integer.parseInt(counter) == 1) {
        amt = add amt;
   } else {
    ..
...



????????

Actually I don;t know how to do....
Avatar of allahabad
allahabad

-- You can directly get this from table.
select sum(Amt) from DB group by Code;
-- group by will order for you also

 
-- You can directly get this from table.
select sum(Amt) from DB group by Code;
-- group by will order for you also

 
Avatar of wjh7554

ASKER

Yes did that but got some problem...

SQL Statement : Select prdcode, prddesp, sum(qty) from DB group by prdcode, prddesp order by prdcode


But how am I going to retrieve the "SUM*QTY) from the statement?

Normal I will do this..

public String prddesp = "";
.
..
prddesp = rs.getSring("prddesp");
..
..

public String getPrddesp() {
   return prddesp;
}

But how can I retirieve the total of QTY ( sum(qty) ) ??


Avatar of wjh7554

ASKER

allahabad,

seems that I need to do some sort like programming already.

SQL statement = "Select pCode, InvNum SUM(qty) as a, SUM(InvAmt) as b from DB group by pCode, InvNum order by pCode "
//No problems.

But my results is liket his

PCode   InvNum   InvAmt
=======================
DDU     DD8956    369
ABC     BA1001    442
ABC     PK8968    333
PKL     LMO233    100
PKL     LM0244    312

I need to make s sub-total at beside. How could I do that?
Still need programming right?? Can suggest the psedocode, in JSP or JavaBean?

PCode   InvNum    InvAmt    sub-total
=====================================
DDU     DD8956      369         369
ABC     BA1001      442
ABC     PK8968      333         776
PKL     LM0233      100
PKL     LM0244      312         412
               Grand Total :   1557




What do you think??
I am not sure how to do this via sql but in java just do this:

create an object called Product that has 3 properties:

class Product implements Serializable {
  String pCode;
  int qty = 0;
  int amt = 0;
 
  Product (String newPCode) { this.pCode = newPCode;}

  public String getPCode() { return this.pCode; }
  public void setPCode(String newPCode) { this.pCode = newPCode; }
  public int getQty() { return this.qty; }
  pulbic void setQty (int newQty) { this.qty = newQty; }
  public int getAmt() { return this.amt; }
  pulbic void setAmt (int newAmt) { this.amt = newAmt; }
}

// now you can add that to an arrayList

SQL statement = "Select pCode, SUM(qty) as qty_sum, SUM(amt) as amt_sum from table group by pCode order by pCode "

java.util.List products = new java.util.ArrayList();

while (rs.next()) {
  Product currentProduct = new Product(rs.getString("pcode"));
  currentProduct.setQty(rs.getInt("qty_sum");
  currentProduct.setAmt(rs.getInt("amt_sum");
  products.add(currentProduct);
}


Now you can iterate over products

java.util.Iterator prodIter = products.iterator();
while (prodIter.hasNext()) {
  Product currentProduct = (Product) prodIter.next();
  out.print(currentProduct.getPCode());
  out.print(currentProduct.getQty());
  out.print(currentProduct.getAmt());
}

HTH,
CJ
SQL statement = "Select pCode, InvNum, SUM(qty) as Qty, SUM(InvAmt) as Amt from DB group by pCode, InvNum order by pCode, InvNum"

int qtyTotal = 0, qtySubTotal = 0;
double amtTotal = 0, amtSubTotal = 0;
String lastPCode = "";

while( rs.next() ) {
  String pCode = rs.getString(1);
  String invNum = rs.getString(2);
  int qty = rs.getInt(3);
  double amt = rs.getDouble(4);
  if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
    System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
    qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
    qtySubTotal = 0; amtSubTotal = 0;
    lastPCode = pCode
  }
  System.out.println( pCode + " " + invNum + " " + qty + " " + amt );
  qtySubTotal += qty; amtSubTotal += amt;
}

System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
System.out.println( "Grand Total " + qtyTotal + " " + amtTotal );
sorry, should be:
if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {
Avatar of wjh7554

ASKER

Friend, how can I set my bigdecimal to zero ??
I mean the syntax, found out I can't do this.
sub_total = 0;
error;
unless i do this
sub_total= new BigDecimal("0.00");

is it the correct way to zero my sub_total?
>> is it the correct way to zero my sub_total?
yes, bigdecimal is immutable, you must always create a new object.
Avatar of wjh7554

ASKER

kennethxu, I think your logic got little bit wrong....
I change
if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
   System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
   qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
   qtySubTotal = 0; amtSubTotal = 0;
   lastPCode = pCode
 }

to
if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
   lastPCode = pCode;
   System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
   qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
   qtySubTotal = 0; amtSubTotal = 0;
 }


:-)

And... I am have way sovling another prblem. See either you are faster or me.

I want this,

PCode   InvNum    InvAmt    sub-total
=====================================
DDU     DD8956      369         369
ABC     BA1001      442
ABC     PK8968      333         776
PKL     LM0233      100
PKL     LM0234       22
PKL     LM0244      312         434
              Grand Total :   1557

BUt your code give me this,

PCode   InvNum    InvAmt    sub-total
=====================================
DDU     DD8956      369         369
ABC     BA1001      442         442
ABC     PK8968      333         776
PKL     LM0233      100         100
PKL     LM0234       22         122
PKL     LM0244      312         412
              Grand Total :   1557


And I am using JSP and JavaBean method... so quite some problem...

Planning to add in more code in the getSub_total method...

Is ithat the way??


>> kennethxu, I think your logic got little bit wrong....
>> if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
that's not right, i have corrected in my comment right after the code, it should be:

if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {

my code is just to illustrate the idea, I have not time to design the beans and etc. you of cause can abstract code into beans.
ASKER CERTIFIED SOLUTION
Avatar of kennethxu
kennethxu

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
did u try my code?

CJ
Avatar of wjh7554

ASKER

CJ,
I am so sorry....

I don't understand this fews things, but I knw it's good for me to learn. Arraylist and java.util.Iterator prodIter.

BUt I understand your logic and ideas.
:-)
Avatar of wjh7554

ASKER

TQ kennethux
my pleasure
No need to apologize, if you understand the logic.. that is good.  the APIs come with time :-)

CJ
Avatar of wjh7554

ASKER

CJ, go out sid to grab your pooints..

TQ.