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?