예제 #1
0
  public void main(
      String login, String senha, String local, String diretorio, List<Integer> pracas) {
    int numeros = pracas.size();
    String pesquisa = "id_praca=" + pracas.get(0);
    for (int k = 1; k < numeros; k++) {
      pesquisa = pesquisa + " or id_praca=" + pracas.get(k);
    }
    AcessaBanco Conecta = new AcessaBanco(login, senha, local);
    Conecta.setSql(
        "select id_praca,count(case when latencia <= 1 then 1 else null end) as '1', "
            + "count(case when latencia > 1 and latencia <= 2 then 1 else null end) as '2', "
            + "count(case when latencia > 2 and latencia <= 4 then 1 else null end)as '4', "
            + "count(case when latencia > 4 and latencia <= 8 then 1 else null end)as '8', "
            + "count(case when latencia >8 and latencia <= 16 then 1 else null end)as '16', "
            + "count(case when latencia >16 and latencia <= 32 then 1 else null end)as '32', "
            + "count(case when latencia >32 and latencia <= 64 then 1 else null end)as '64', "
            + "count(case when latencia >64 and latencia <= 128 then 1 else null end)as '128', "
            + "count(case when latencia >128 and latencia <= 256 then 1 else null end)as '256', "
            + "count(case when latencia >256 and latencia <= 512 then 1 else null end)as '512', "
            + "count(case when latencia >512 and latencia <= 1024 then 1 else null end)as '1024', "
            + "count(case when latencia >1024 and latencia <= 2048 then 1 else null end)as '2048', "
            + "count(case when latencia >2048 and latencia <= 4096 then 1 else null end)as '4096', "
            + "count(case when latencia >4096 then 1 else null end) as '8192' "
            + "from latenciausuarios "
            + "where "
            + pesquisa
            + " group by id_praca;");

    Conecta.abrirConexao();
    ResultSet rs3 = Conecta.consulta();
    try {
      BufferedWriter StrW = null;
      try {
        StrW = new BufferedWriter(new FileWriter(diretorio + "\\fig15.csv"));
      } catch (IOException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
      StrW.write("praca ; 1 ; 2 ; 4 ; 8 ; 16; 32 ; 64 ; 128 ; 256 ; 512; 1024;2048;4096;8192 \n");
      while (rs3.next()) {

        try {
          StrW.write(
              rs3.getString(1)
                  + ";"
                  + rs3.getString(2)
                  + ";"
                  + rs3.getString(3)
                  + ";"
                  + rs3.getString(4)
                  + ";"
                  + rs3.getString(5)
                  + ";"
                  + rs3.getString(6)
                  + ";"
                  + rs3.getString(7)
                  + ";"
                  + rs3.getString(8)
                  + ";"
                  + rs3.getString(9)
                  + ";"
                  + rs3.getString(10)
                  + ";"
                  + rs3.getString(11)
                  + ";"
                  + rs3.getString(12)
                  + ";"
                  + rs3.getString(13)
                  + ";"
                  + rs3.getString(14)
                  + ";"
                  + rs3.getString(15)
                  + "\n");
        } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
      StrW.close();
      System.out.println("FIM 15");
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
  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");
  }
  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");
  }
  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();
  }
  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");
  }
  public void removeDuplicata() {

    AcessaBanco a = new AcessaBanco();
    a.abrirConexao();

    AcessaBanco a1 = new AcessaBanco();
    a1.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-08-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-04-30 23:30:00";
    @SuppressWarnings("unused")
    String dataHoraFim = "2015-08-01 00:30:00";

    @SuppressWarnings("unused")
    String horaCerta = null;

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

    ano = 2015;
    eam = "-";
    mes = 04;
    emd = "-";
    dia = 30;
    edh = " ";
    hora = 23;

    minutoRange = "50";

    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  '1001' AND '1070'");

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

        ano = 2015;
        eam = "-";
        mes = 04;
        emd = "-";
        dia = 30;
        edh = " ";
        hora = 23;

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

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

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

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

          // --------------------mes------------------------
          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 (dia < 10) {
            emd = "-0";
          } else {
            emd = "-";
          }

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

          a.setSql(
              "SELECT "
                  + "dados_praca.id_data_hora "
                  + "FROM "
                  + "dados_praca "
                  + "WHERE "
                  + "dados_praca.id_praca =  '"
                  + id_praca
                  + "' AND "
                  + "dados_praca.id_data_hora LIKE '"
                  + dataHoraIni
                  + "'");

          ResultSet rs = a.consulta();
          try {

            rs.next();

            while (rs.next()) {

              a1.setSql(
                  "DELETE FROM `dados_praca` WHERE (`id_data_hora`='"
                      + rs.getString(1)
                      + "') AND (`id_praca`='"
                      + id_praca
                      + "')  ");
              a1.executa();
            }
            a1.setSql(null);
            rs = null;
          } catch (SQLException e) {
            e.printStackTrace();
          }
          a.setSql(null);
        }
      }
    } catch (SQLException e2) {
      e2.printStackTrace();
    }

    a.fecharConexao();

    a1.fecharConexao();
    System.out.println("FEITO");
  }