Date with problem in mysql and java

1

I'm trying to use the date of a jYearChoser and a jMonthChoser to fetch the sale for a full month, I already tested it at the MySQL prompt and it worked perfectly with the command below:

SELECT FORMAT(SUM(vendas.total_pagar),2), FORMAT(SUM(desconto_venda),2), FORMAT(SUM(orcamentos.valor_orcamento),2), FORMAT(AVG(valor_venda),2), COUNT(vendas.id), COUNT(DISTINCT(vendas.id_cliente))
FROM genius.vendas
LEFT JOIN genius.orcamentos ON genius.orcamentos.id = id_orcamento
WHERE EXTRACT(YEAR_MONTH FROM 'data_venda') = (EXTRACT(YEAR_MONTH FROM '2016-08-01' - INTERVAL 0 MONTH)) AND vendas.Id_funcionario = 5;

But when I try to use Java in the way below it works, but, the account goes out wrong:

//abaixo preenche o painel cálculo de pagamento
    sql = "SELECT SUM(vendas.total_pagar), FORMAT(SUM(desconto_venda),2), FORMAT(SUM(orcamentos.valor_orcamento),2), FORMAT(AVG(valor_venda),2), COUNT(vendas.id), COUNT(DISTINCT(vendas.id_cliente))\n" +
                 "FROM genius.vendas\n" +
                 "LEFT JOIN genius.orcamentos ON genius.orcamentos.id = id_orcamento\n" +
                 "WHERE EXTRACT(YEAR_MONTH FROM 'data_venda') = (EXTRACT(YEAR_MONTH FROM ?'-1' - INTERVAL 0 MONTH)) AND vendas.Id_funcionario = ?";
    try {
        int setar = tblFuncionarios.getSelectedRow();
        String id = (tblFuncionarios.getModel().getValueAt(setar, 0).toString());
        pst = conexao.prepareStatement(sql);
        // passando o Id para o "?
        pst.setString(1,Integer.toString(jYearChooser1.getYear()) + "-" + Integer.toString(jMonthChooser1.getMonth()));
        pst.setString(2, id);
        rs = pst.executeQuery();
        if(rs.next()) {
            double vendas = (rs.getDouble(1));
            lblVendas.setText(Double.toString(vendas));
            lblDescontos.setText(rs.getString(2));
            lblOrcamentos.setText(rs.getString(3));
            lblVMC.setText(rs.getString(4));
            lblVendas_quant.setText(rs.getString(5));
            lblClientes.setText(rs.getString(6));

            if ("sim".equals(lblCG.getText())) { // a condição abaixo calcula a porcentagem para comissão gradativa
                double inicioCG = Double.parseDouble(lblCGInicio.getText());
                double intervaloCG = Double.parseDouble(lblCGIntervalo.getText());
                double maxPorcCG = Double.parseDouble(lblCGLimite.getText());
                int fator = (int) ((vendas - inicioCG) / intervaloCG + 1);
                lblCGPorc.setText(Integer.toString(fator));
            }
        }
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }

Does anyone have any tips?

    
asked by anonymous 01.09.2016 / 04:31

1 answer

1

I was able to sort out the code, I realized that I was calculating the value of the previous month, I solved the problem by adding 1 in jMonthChooser1.getMonth (), the problem was that the first month of the calendar class starts at 0, month starts at 1, so it was making a difference

    
06.09.2016 / 01:56