Problems in while with if to accumulate

2

I have a problem I want to do an accumulation of parts and payments within a while and then accumulate based on the employee and then paint the data on screen.

But you skip the information related to the name by a column.

2939    DELIA.MORENO    13170   $299.84 //este es el dato correcto si solo busco

this employee.

If I look for all the employees, that's where the detail comes from.

 2912   ASIGNACION1 3941    $106.41

 2913   ASIGNACION2 1358    $38.67

 2914   CAPTURISTA1 2208    $66.31

 2936   JORGE.DIAZ  10544   $327.09

 2938   APOLINARIA.AGUSTIN  20  $0.52

 2939   DELIA.MORENO    17168   $384.93

 2940   DIANA.HERNANDEZ 13170   $299.84

 2941   GUILLERMO.BELLO 16507   $374.72

 2944   ELENA.ZAVALA    16380   $370.13

 2945   ALEJANDRA.ESCALANTE 20198   $453.7

 2946   NORMA.ESTRADA   12941   $298.71

 2947   SANDRA.CORONADO 20272   $456.55

 2947   SANDRA.CORONADO 15232   $342.61

And Delia Moreno has other pieces.

Here is the code for this calculation.

$_Query = "SELECT CR.CORTE_NUM,count(RL.codigo_qp) cant,RL.USUARIO_ID,US.USER_NAME "
 + " from XXQP.XXQP_REMESA_CORTE_LN_TBL rl left join XXQP.XXQP_REMESA_CORTE_HD_TBL cr on RL.CORTE_ID = CR.CORTE_ID "
 + " inner join APPLSYS.FND_USER US on US.USER_ID=RL.USUARIO_ID "
 + " where 1 = 1";   

if(!StRemesa.equals("")){
    $_Query = $_Query +" and cr.remesa_id="+StRemesa; //Remesa
}
if(!StCapturista.equals("")){
    $_Query = $_Query +" and rl.usuario_id="+StCapturista;//Capturista
}
if(!StFIni.equals("")){ //Fechas
    $_Query = $_Query +" and rl.fecha>=to_date('"+StFIni+" 00:00:00','yyyy-mm-dd hh24:mi:ss') and rl.fecha<=to_date('"+StFFin+" 23:59:00','yyyy-mm-dd hh24:mi:ss')";
}

$_Query = $_Query +" and rl.origen=3 and rl.usuario_id not in (2731,2912,2913,2914,2936) and rl.estatus!='CANCELADO' group by CR.CORTE_NUM,RL.USUARIO_ID,US.USER_NAME "
 + "order by RL.USUARIO_ID,CR.CORTE_NUM";
rs1 = st1.executeQuery($_Query);

while (rs1.next()) {
    StNCap = rs1.getString("USUARIO_ID");
    StCap = rs1.getString("USER_NAME");

    $_Query = "SELECT HP.PARTY_NAME,LK.MEANING, COALESCE(IT.ATTRIBUTE19,'0.00') comision from XXQP.XXQP_REMESA_CORTE_HD_TBL cr inner join "
        + "XXQP.XXQP_REMESA_TBL r on CR.REMESA_ID = R.REMESA_ID inner join APPLSYS.FND_LOOKUP_VALUES lk "
        + "on CR.PLAZA_ID = LK.LOOKUP_CODE and LK.LOOKUP_TYPE='XXQP_PLAZAS' and LK.LANGUAGE='ESA' inner join AR.HZ_CUST_ACCOUNTS hz "
        + "on  r.cliente_id = HZ.CUST_ACCOUNT_ID inner join AR.HZ_PARTIES hp on  HZ.PARTY_ID = HP.PARTY_ID inner join INV.MTL_SYSTEM_ITEMS_B "
        + "it on r.producto_id = IT.INVENTORY_ITEM_ID and r.almacen_id = IT.ORGANIZATION_ID where  CR.CORTE_NUM='"+rs1.getString("corte_num")+"'";
    rs2 = Gdr.consultar($_Query, 2);

    if (rs2.next()){
        StComm = rs2.getString("comision");
        Pago=Double.parseDouble(rs2.getString("comision")) * Integer.parseInt(rs1.getString("cant"));

        out.print("<tr bgcolor='lightgray' ALIGN='CENTER'>");
        if (cont >= 1) {
            if (!empleado.equals(rs1.getString("USUARIO_ID"))) {
                out.print(tr bgcolor='lightgray' ALIGN='CENTER');
                out.print(td + StNCap);
                out.print(/td);
                out.print(td + StCap);
                out.print(/td);
                out.print(td + piezas);
                out.print(/td);
                out.print(td + format.format(PagoTotal));
                out.print(/td);
                out.print(/tr);
                piezas = 0;
                PagoTotal = 0.0;
            }
        }
        empleado = rs1.getString("USUARIO_ID");
        PagoTotal +=Pago;
        piezas += rs1.getInt("cant");
        cont = cont+1;
    }
    rs2.close();
    if (r == 0) {
%>
<tr bgcolor='white' ALIGN='CENTER'>
<%
        r = 1;
    } else {
        r = 0;
%>
<tr bgcolor='#E6E6E6' ALIGN='CENTER'>
<% } %>
</tr>
<%    
}
out.print(tr bgcolor='lightgray' ALIGN='CENTER');
out.print(td + StNCap);
out.print(/td);
out.print(td + StCap );
out.print(/td);
out.print(td + piezas );
out.print(/td);
out.print(td> + format.format(PagoTotal));
out.print(/td);
out.print(/tr);
st1.close();
Gdr.cerrar();
    
asked by Jairo Ordaz Moreno 26.10.2016 в 20:10
source

2 answers

1

Your problem is in the if (rs2.next()) that you have within the while since you are calling the next() method without knowing if there will be another record or not.

Assuming that you are using a Iterator , one way to check it is by using the .hasNext() method, leaving the code such that:

while (rs1.hasNext()) {
    rs1.next();
    //Parte del código que tu tenias
    if(rs2.hasNext()){
       rs2.next();
       //resto del código
    }
}

You can also evaluate changing the type of loop by a for , with which you can surely have more control

    
answered by 28.10.2016 / 09:47
source
0

The problem is that it does not show the data of the last employee, try something like this:

if (rs2.next()) {
    StComm = rs2.getString("comision");
    Pago = Double.parseDouble(rs2.getString("comision")) * Integer.parseInt(rs1.getString("cant"));

    if (cont >= 1) {
        if (!empleado.equals(rs1.getString("USUARIO_ID"))) {
            out.print("<tr bgcolor='lightgray' ALIGN='CENTER'>");
            out.print("<td>" + StNCap);
            out.print("</td>");
            out.print("<td>" + StCap);
            out.print("</td>");
            out.print("<td>" + piezas);
            out.print("</td>");
            out.print("<td>" + format.format(PagoTotal));
            out.print("</td>");
            out.print("</tr>");
            piezas = 0;
            PagoTotal = 0.0;
        }
    }

    empleado = rs1.getString("USUARIO_ID");
    PagoTotal += Pago;
    piezas += rs1.getInt("cant");
    cont = cont + 1;

}

if (cont >= 1) {
    out.print("<tr bgcolor='lightgray' ALIGN='CENTER'>");
    out.print("<td>" + StNCap);
    out.print("</td>");
    out.print("<td>" + StCap);
    out.print("</td>");
    out.print("<td>" + piezas);
    out.print("</td>");
    out.print("<td>" + format.format(PagoTotal));
    out.print("</td>");
    out.print("</tr>");
}

Good luck!

    
answered by 26.10.2016 в 21:21