コード例 #1
0
  public void trocaColuna() {
    AcessaBanco a1 = new AcessaBanco();
    AcessaBanco a2 = new AcessaBanco();

    a1.setSql(
        "SELECT "
            + "dados_praca.id_data_hora, "
            + "dados_praca.id_praca, "
            + "dados_praca.entrada, "
            + "dados_praca.saida "
            + "FROM "
            + "dados_praca "
            + "WHERE "
            + "dados_praca.id_praca >  '900'"
            + " AND dados_praca.entradaWCS IS NULL");
    System.out.println(a1.getSql());
    a1.abrirConexao();
    a2.abrirConexao();
    ResultSet rs1 = a1.consulta();
    try {
      while (rs1.next()) {

        a2.setSql(
            "UPDATE `dados_praca` SET `entrada`=NULL,`saida`=NULL,`entradaWCS`='"
                + rs1.getString(3)
                + "',`saidaWCS`='"
                + rs1.getString(4)
                + "' WHERE (`id_data_hora`='"
                + rs1.getString(1)
                + "') AND (`id_praca`='"
                + rs1.getString(2)
                + "')");
        // UPDATE `dados_praca` SET
        // `entrada`=NULL,`saida`=NULL,`entradaWCS`='2993895295',`saidaWCS`='2941491328' WHERE
        // (`id_data_hora`='2014-10-28 18:00:01') AND (`id_praca`='1005')
        a2.executa();

        System.out.println(
            rs1.getString(2)
                + " | "
                + rs1.getString(1)
                + " | "
                + rs1.getString(3)
                + " | "
                + rs1.getString(4));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    a2.fecharConexao();
    a1.fecharConexao();

    System.out.println("FEITO");
  }
コード例 #2
0
  public void calculaWCSKbits() {
    AcessaBanco a3 = new AcessaBanco();
    AcessaBanco a4 = new AcessaBanco();

    AcessaBanco a20 = new AcessaBanco();
    a20.setSql("SELECT praca.id_praca FROM praca WHERE praca.id_praca > '900'");
    a20.abrirConexao();
    a3.abrirConexao();
    a4.abrirConexao();

    ResultSet rs20 = a20.consulta();
    try {
      while (rs20.next()) {
        String id_praca = rs20.getString(1);
        double entradaAnt = 0;
        double saidaAnt = 0;

        a3.setSql(
            "SELECT "
                + "dados_praca.id_data_hora, "
                + "dados_praca.id_praca, "
                + "dados_praca.entradaWCS, "
                + "dados_praca.saidaWCS "
                + "FROM "
                + "dados_praca "
                + "WHERE "
                + "dados_praca.id_praca =  '"
                + id_praca
                + "' AND "
                + "dados_praca.un_entrada IS NULL AND "
                + "dados_praca.entradaWCS IS NOT NULL ");

        System.out.println(a3.getSql());

        ResultSet rs2 = a3.consulta();
        try {
          while (rs2.next()) {

            double entrada = Double.parseDouble(rs2.getString(3));
            if (entrada < entradaAnt) {
              double aux = 4294967296.0 - entradaAnt;
              entrada = ((aux + entrada) / 1024) / 600;
            } else {
              entrada = ((entrada - entradaAnt) / 1024) / 600;
            }
            entradaAnt = Double.parseDouble(rs2.getString(3));

            double saida = Double.parseDouble(rs2.getString(4));
            if (saida < saidaAnt) {
              double aux1 = 4294967296.0 - saidaAnt;
              saida = ((aux1 + saida) / 1024) / 600;
            } else {
              saida = ((saida - saidaAnt) / 1024) / 600;
            }
            saidaAnt = Double.parseDouble(rs2.getString(4));

            System.out.println(
                rs2.getString(2)
                    + " | "
                    + rs2.getString(1)
                    + " | "
                    + entrada
                    + " | K | "
                    + saida
                    + " | K ");

            a4.setSql(
                "UPDATE `dados_praca` SET `entrada`='"
                    + entrada
                    + "',`un_entrada`='K',`saida`='"
                    + saida
                    + "',`un_saida`='K' WHERE (`id_data_hora`='"
                    + rs2.getString(1)
                    + "') AND (`id_praca`='"
                    + id_praca
                    + "')");
            a4.executa();
          }
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    } catch (NumberFormatException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    a4.fecharConexao();
    a3.fecharConexao();

    a20.fecharConexao();

    System.out.println("FEITO");
  }
コード例 #3
0
  public void transforma10minParaHora(String ultimaPracaId) {
    AcessaBanco a = new AcessaBanco();
    a.abrirConexao();

    AcessaBanco ab = new AcessaBanco();
    AcessaBanco ac = new AcessaBanco();
    ac.abrirConexao();

    int numRes = -1;

    ab.setSql(
        "SELECT "
            + "Count(dados_praca.id_data_hora) "
            + "FROM "
            + "dados_praca "
            + "WHERE "
            + "dados_praca.id_praca =  '1001' AND "
            + "dados_praca.id_data_hora BETWEEN  '2015-04-30 23:30:00' AND '2015-05-01 00:30:00'");

    ab.abrirConexao();
    ResultSet rs1 = ab.consulta();

    try {
      while (rs1.next()) {
        numRes = Integer.parseInt(rs1.getString(1));
      }
    } catch (SQLException e1) {

      e1.printStackTrace();
    }
    ab.fecharConexao();

    System.out.println(numRes + "\n\n\n");

    // ==============================================================================

    String dataHoraIni = "2015-07-30 00:00:00";
    String dataHoraFim = "2015-11-01 23:50:00";

    //	String dataHoraFim = "2015-08-31 00:30:00";

    String horaCerta = null;

    String eam, emd, edh; // tipos de espaços m(mês dia, ano mes, dia hora)
    int ano, mes, dia, hora;

    String numUs = null;
    String numEn = null;
    String numSa = null;

    ano = 2014;
    eam = "-";
    mes = 8;
    emd = "-";
    dia = 30;
    edh = " ";
    hora = 00;

    String id_praca = "1001";

    /*
    ab.setSql("SELECT DISTINCT "
    		+ "dados_praca.id_praca "
    		+ "FROM "
    		+ "dados_praca "
    		+ "WHERE "
    		+ "dados_praca.id_data_hora <  '2015-05-01 00:00:09' AND "
    		+ "dados_praca.id_praca BETWEEN  '"+ultimaPracaId+"' AND '1070'");
    */
    ab.setSql(
        "SELECT DISTINCT "
            + "dados_praca.id_praca "
            + "FROM "
            + "dados_praca "
            + "WHERE "
            + "dados_praca.id_praca BETWEEN  '"
            + ultimaPracaId
            + "' AND '1070'");

    ab.abrirConexao();
    ResultSet rs3 = ab.consulta();
    try {
      while (rs3.next()) {

        ano = 2015;
        eam = "-";
        mes = 7;
        emd = "-";
        dia = 30;
        edh = " ";
        hora = 00;

        id_praca = rs3.getString(1);
        System.out.println(id_praca);

        /*
        		try {
        			Thread.sleep(5000);
        		} catch (InterruptedException e1) {
        			e1.printStackTrace();
        		}

        */

        for (int i = 0; i < 3496; i++) {

          dataHoraIni = ano + eam + mes + emd + dia + edh + hora + ":00:00";

          // ----------------hora------------------
          hora = hora + 1;
          if (hora == 24) {
            hora = 0;
            dia = dia + 1;
          }
          if (hora < 10) {
            edh = " 0";
          } else {
            edh = " ";
          }

          // --------------------mes------------------------
          if ((mes == 12) && (dia == 32) && (ano == 2014)) {
            mes = 01;
            dia = 01;
            ano = 2015;
          }

          if ((mes == 01) && (dia == 32)) {
            mes = 02;
            dia = 1;
          }

          if ((mes == 02) && (dia == 29)) {
            mes = 03;
            dia = 1;
          }

          if ((mes == 03) && (dia == 32)) {
            mes = 04;
            dia = 1;
            ano = 2015;
          }

          if ((mes == 04) && (dia == 31)) {
            mes = 05;
            dia = 1;
          }

          if ((mes == 05) && (dia == 32)) {
            mes = 06;
            dia = 1;
          }

          if ((mes == 06) && (dia == 31)) {
            mes = 07;
            dia = 1;
            ano = 2015;
          }

          if ((mes == 07) && (dia == 32)) {
            mes = 8;
            dia = 1;
          }

          if ((mes == 8) && (dia == 32)) {
            mes = 9;
            dia = 1;
          }

          if ((mes == 9) && (dia == 31)) {
            mes = 10;
            dia = 1;
          }

          if ((mes == 10) && (dia == 32)) {
            mes = 11;
            dia = 1;
          }

          if ((mes == 11) && (dia == 31)) {
            mes = 12;
            dia = 1;
          }

          if ((mes == 12) && (dia == 32) && (ano == 2015)) {
            mes = 01;
            dia = 01;
            ano = 2016;
          }

          if (dia < 10) {
            emd = "-0";
          } else {
            emd = "-";
          }

          if (mes < 10) {
            eam = "-0";
          } else {
            eam = "-";
          }

          dataHoraFim = ano + eam + mes + emd + dia + edh + hora + ":00:00";
          horaCerta = ano + eam + mes + emd + dia + edh + hora + ":00:00";

          //		System.out.println(dataHoraIni+" | "+dataHoraFim+" | "+horaCerta);

          DecimalFormat fmt = new DecimalFormat("0");
          DecimalFormat eS = new DecimalFormat("0.00");

          a.setSql(
              "SELECT "
                  + "Avg(dados_praca.entrada), "
                  + "Avg(dados_praca.saida), "
                  + "Avg(dados_praca.usuarios) "
                  + "FROM "
                  + "dados_praca "
                  + "WHERE "
                  + "dados_praca.id_praca =  '"
                  + id_praca
                  + "' AND "
                  + "dados_praca.id_data_hora BETWEEN  '"
                  + dataHoraIni
                  + "' AND '"
                  + dataHoraFim
                  + "'");

          ResultSet rs = a.consulta();
          try {

            while (rs.next()) {

              //	System.out.println(rs.getString(1)+" | "+rs.getString(2)+" | "+rs.getString(3));
              String sql1a, sql2a;
              sql1a = "INSERT INTO `dados_praca_por_hora` (`id_praca`,`id_data_hora`";
              sql2a = ") VALUES ('" + id_praca + "','" + horaCerta + "'";

              if (!(rs.getString(1) == null)) {
                double en = Double.parseDouble(rs.getString(1));
                numEn = eS.format(en);
                numEn = numEn.replace(",", ".");
                sql1a = sql1a + ",`entrada`,`un_entrada`";
                sql2a = sql2a + ",'" + numEn + "','K'";
              }

              if (!(rs.getString(2) == null)) {
                double sa = Double.parseDouble(rs.getString(2));
                numSa = eS.format(sa);
                numSa = numSa.replace(",", ".");
                sql1a = sql1a + ",`saida`,`un_saida`";
                sql2a = sql2a + ",'" + numSa + "','K'";
              }

              if (!(rs.getString(3) == null)) {
                double us = Double.parseDouble(rs.getString(3));
                numUs = fmt.format(us);
                sql1a = sql1a + ",`usuarios`";
                sql2a = sql2a + ",'" + numUs + "'";
              }

              //	System.out.println(rs.getString(1)+" | "+rs.getString(2)+" | "+rs.getString(3));
              //	System.out.println(id_praca+" | "+horaCerta+" | "+numEn+" | "+numSa+" | "+numUs);

              ac.setSql(sql1a + sql2a + ")");
              System.out.println(ac.getSql());
              ac.executa();
            }
          } catch (SQLException e) {
            e.printStackTrace();
          }
        }
      }
    } catch (SQLException e2) {
      e2.printStackTrace();
    }

    a.fecharConexao();
  }
コード例 #4
0
  public void diminuiLarguraBanda() {

    AcessaBanco a1 = new AcessaBanco();
    AcessaBanco a2 = new AcessaBanco();
    AcessaBanco a3 = new AcessaBanco();

    a1.setSql("SELECT " + "praca.id_praca, " + "praca.n_max_usuarios " + "FROM " + "praca ");
    System.out.println(a1.getSql());
    a1.abrirConexao();
    a2.abrirConexao();
    a3.abrirConexao();
    ResultSet rs1 = a1.consulta();
    try {
      while (rs1.next()) {
        double max_banda = 0;
        max_banda = Double.parseDouble(rs1.getString(2)) * 512;
        max_banda = converterDoubleDoisDecimais(max_banda);

        //	System.out.println(rs1.getString(1)+" | "+ rs1.getString(2)+" | "+max_banda);
        a2.setSql(
            "SELECT "
                + "dados_praca.id_praca, "
                + "dados_praca.entrada, "
                + "dados_praca.id_data_hora "
                + "FROM "
                + "dados_praca "
                + "WHERE "
                + "dados_praca.id_praca =  '"
                + rs1.getString(1)
                + "' AND "
                + "dados_praca.entrada >  '"
                + max_banda
                + "'");
        ResultSet rs2 = a2.consulta();
        try {
          while (rs2.next()) {
            double dim_banda = 0;
            dim_banda = Double.parseDouble(rs2.getString(2)) / 10;
            dim_banda = converterDoubleDoisDecimais(dim_banda);

            System.out.println(
                rs1.getString(1)
                    + " | "
                    + rs1.getString(2)
                    + " | "
                    + max_banda
                    + " | "
                    + rs2.getString(2)
                    + " | "
                    + dim_banda);
            a3.setSql(
                "UPDATE `dados_praca` SET `entrada`='"
                    + dim_banda
                    + "' WHERE (`id_data_hora`='"
                    + rs2.getString(3)
                    + "') AND (`id_praca`='"
                    + rs1.getString(1)
                    + "')");

            System.out.println(a3.getSql());
            a3.executa();
          }
        } catch (SQLException e) {

          e.printStackTrace();
        }
      }
    } catch (SQLException e) {

      e.printStackTrace();
    }
    a3.fecharConexao();
    a2.fecharConexao();
    a1.fecharConexao();

    System.out.println("FIM");
  }