private void fullAssociated() {

    boolean associated = true;

    String SQL =
        "Select * "
            + "from XX_VMR_REFERENCEMATRIX "
            + "where M_product IS NULL AND XX_VMR_PO_LINEREFPROV_ID="
            + LineRefProv.get_ID();

    try {
      PreparedStatement pstmt = DB.prepareStatement(SQL, null);
      ResultSet rs = pstmt.executeQuery();

      while (rs.next()) {
        associated = false;
      }

      rs.close();
      pstmt.close();

    } catch (Exception a) {
      log.log(Level.SEVERE, SQL, a);
    }

    if (associated == true) {
      String SQL10 =
          "UPDATE XX_VMR_PO_LineRefProv "
              + " SET XX_ReferenceIsAssociated='Y'"
              + " WHERE XX_VMR_PO_LineRefProv_ID="
              + LineRefProv.getXX_VMR_PO_LineRefProv_ID();

      DB.executeUpdate(null, SQL10);

      //			LineRefProv.setXX_ReferenceIsAssociated(true);
      //			LineRefProv.save();

      int dialog = Env.getCtx().getContextAsInt("#Dialog_Associate_Aux");

      if (dialog == 1) {
        ADialog.info(m_WindowNo, m_frame, "MustRefresh");
        Env.getCtx().remove("#Dialog_Associate_Aux");
      }

    } else {
      String SQL10 =
          "UPDATE XX_VMR_PO_LineRefProv "
              + " SET XX_ReferenceIsAssociated='N'"
              + " WHERE XX_VMR_PO_LineRefProv_ID="
              + LineRefProv.getXX_VMR_PO_LineRefProv_ID();

      DB.executeUpdate(null, SQL10);
      //			LineRefProv.setXX_ReferenceIsAssociated(false);
      //			LineRefProv.save();
    }
  }
  public void cleanup() {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
      conn = getConnection();
      ps = conn.prepareStatement("SELECT COUNT(*) FROM FILES");
      rs = ps.executeQuery();
      dbCount = 0;

      if (rs.next()) {
        dbCount = rs.getInt(1);
      }

      rs.close();
      ps.close();
      PMS.get().getFrame().setStatusLine(Messages.getString("DLNAMediaDatabase.2") + " 0%");
      int i = 0;
      int oldpercent = 0;

      if (dbCount > 0) {
        ps =
            conn.prepareStatement(
                "SELECT FILENAME, MODIFIED, ID FROM FILES",
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_UPDATABLE);
        rs = ps.executeQuery();
        while (rs.next()) {
          String filename = rs.getString("FILENAME");
          long modified = rs.getTimestamp("MODIFIED").getTime();
          File file = new File(filename);
          if (!file.exists() || file.lastModified() != modified) {
            rs.deleteRow();
          }
          i++;
          int newpercent = i * 100 / dbCount;
          if (newpercent > oldpercent) {
            PMS.get()
                .getFrame()
                .setStatusLine(Messages.getString("DLNAMediaDatabase.2") + newpercent + "%");
            oldpercent = newpercent;
          }
        }
      }
    } catch (SQLException se) {
      LOGGER.error(null, se);
    } finally {
      close(rs);
      close(ps);
      close(conn);
    }
  }
  /**
   * verifica si hay productos asociados
   *
   * @param table table
   */
  private boolean verify() {
    String sql =
        "SELECT XX_VMR_ReferenceMatrix_ID "
            + "FROM XX_VMR_ReferenceMatrix "
            + "WHERE XX_VMR_PO_LINEREFPROV_ID="
            + (Integer) LineRefProv.getXX_VMR_PO_LineRefProv_ID();

    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
      pstmt = DB.prepareStatement(sql, null);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        associatedReference_ID = rs.getInt("XX_VMR_ReferenceMatrix_ID");
        rs.close();
        pstmt.close();
        return true;
      }

    } catch (SQLException e) {
      log.log(Level.SEVERE, sql, e);
    } finally {

      try {
        rs.close();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    return false;
  } //  tableLoad
Example #4
0
  // event handling
  public void actionPerformed(ActionEvent e) {
    if (e.getSource() == btnok) {
      PreparedStatement pstm;
      ResultSet rs;
      String sql;
      // if no entries has been made and hit ok button throw an error
      // you can do this step using try clause as well
      if ((tf1.getText().equals("") && (tf2.getText().equals("")))) {
        lblmsg.setText("Enter your details ");
        lblmsg.setForeground(Color.magenta);
      } else {

        try {
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          Connection connect = DriverManager.getConnection("jdbc:odbc:student_base");
          System.out.println("Connected to the database");
          pstm = connect.prepareStatement("insert into student_base values(?,?)");
          pstm.setString(1, tf1.getText());
          pstm.setString(2, tf2.getText());
          // execute method to execute the query
          pstm.executeUpdate();
          lblmsg.setText("Details have been added to database");

          // closing the prepared statement  and connection object
          pstm.close();
          connect.close();
        } catch (SQLException sqe) {
          System.out.println("SQl error");
        } catch (ClassNotFoundException cnf) {
          System.out.println("Class not found error");
        }
      }
    }
    // upon clickin button addnew , your textfield will be empty to enternext record
    if (e.getSource() == btnaddnew) {
      tf1.setText("");
      tf2.setText("");
    }

    if (e.getSource() == btnexit) {
      System.exit(1);
    }
  }
Example #5
0
  private void fill_combo() {
    try {
      String sql = "select distinct townname from towntrends.trends ";
      pst = conn.prepareStatement(sql);
      rs = pst.executeQuery();

      while (rs.next()) {
        String name = rs.getString("townname");
        jComboBox1.addItem(name);
      }
    } catch (Exception ex) {
      JOptionPane.showMessageDialog(null, ex);
    } finally {
      try {
        pst.close();
        rs.close();
      } catch (Exception ex) {

      }
    }
  }
  private boolean isInMatrix() {

    String SQL =
        "Select * "
            + "from XX_VMR_REFERENCEMATRIX "
            + "where XX_VMR_PO_LINEREFPROV_ID="
            + LineRefProv.get_ID();

    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
      pstmt = DB.prepareStatement(SQL, null);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        return true;
      }

    } catch (Exception a) {
      log.log(Level.SEVERE, SQL, a);
    } finally {

      try {
        rs.close();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    return false;
  }
  public ArrayList<DLNAMediaInfo> getData(String name, long modified) {
    ArrayList<DLNAMediaInfo> list = new ArrayList<DLNAMediaInfo>();
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement stmt = null;
    try {
      conn = getConnection();
      stmt = conn.prepareStatement("SELECT * FROM FILES WHERE FILENAME = ? AND MODIFIED = ?");
      stmt.setString(1, name);
      stmt.setTimestamp(2, new Timestamp(modified));
      rs = stmt.executeQuery();
      while (rs.next()) {
        DLNAMediaInfo media = new DLNAMediaInfo();
        int id = rs.getInt("ID");
        media.setDuration(toDouble(rs, "DURATION"));
        media.setBitrate(rs.getInt("BITRATE"));
        media.setWidth(rs.getInt("WIDTH"));
        media.setHeight(rs.getInt("HEIGHT"));
        media.setSize(rs.getLong("SIZE"));
        media.setCodecV(rs.getString("CODECV"));
        media.setFrameRate(rs.getString("FRAMERATE"));
        media.setAspect(rs.getString("ASPECT"));
        media.setAspectRatioContainer(rs.getString("ASPECTRATIOCONTAINER"));
        media.setAspectRatioVideoTrack(rs.getString("ASPECTRATIOVIDEOTRACK"));
        media.setReferenceFrameCount(rs.getByte("REFRAMES"));
        media.setAvcLevel(rs.getString("AVCLEVEL"));
        media.setBitsPerPixel(rs.getInt("BITSPERPIXEL"));
        media.setThumb(rs.getBytes("THUMB"));
        media.setContainer(rs.getString("CONTAINER"));
        media.setModel(rs.getString("MODEL"));
        if (media.getModel() != null && !FormatConfiguration.JPG.equals(media.getContainer())) {
          media.setExtrasAsString(media.getModel());
        }
        media.setExposure(rs.getInt("EXPOSURE"));
        media.setOrientation(rs.getInt("ORIENTATION"));
        media.setIso(rs.getInt("ISO"));
        media.setMuxingMode(rs.getString("MUXINGMODE"));
        media.setFrameRateMode(rs.getString("FRAMERATEMODE"));
        media.setMediaparsed(true);
        PreparedStatement audios =
            conn.prepareStatement("SELECT * FROM AUDIOTRACKS WHERE FILEID = ?");
        audios.setInt(1, id);
        ResultSet subrs = audios.executeQuery();
        while (subrs.next()) {
          DLNAMediaAudio audio = new DLNAMediaAudio();
          audio.setId(subrs.getInt("ID"));
          audio.setLang(subrs.getString("LANG"));
          audio.setFlavor(subrs.getString("FLAVOR"));
          audio.getAudioProperties().setNumberOfChannels(subrs.getInt("NRAUDIOCHANNELS"));
          audio.setSampleFrequency(subrs.getString("SAMPLEFREQ"));
          audio.setCodecA(subrs.getString("CODECA"));
          audio.setBitsperSample(subrs.getInt("BITSPERSAMPLE"));
          audio.setAlbum(subrs.getString("ALBUM"));
          audio.setArtist(subrs.getString("ARTIST"));
          audio.setSongname(subrs.getString("SONGNAME"));
          audio.setGenre(subrs.getString("GENRE"));
          audio.setYear(subrs.getInt("YEAR"));
          audio.setTrack(subrs.getInt("TRACK"));
          audio.getAudioProperties().setAudioDelay(subrs.getInt("DELAY"));
          audio.setMuxingModeAudio(subrs.getString("MUXINGMODE"));
          audio.setBitRate(subrs.getInt("BITRATE"));
          media.getAudioTracksList().add(audio);
        }
        subrs.close();
        audios.close();

        PreparedStatement subs = conn.prepareStatement("SELECT * FROM SUBTRACKS WHERE FILEID = ?");
        subs.setInt(1, id);
        subrs = subs.executeQuery();
        while (subrs.next()) {
          DLNAMediaSubtitle sub = new DLNAMediaSubtitle();
          sub.setId(subrs.getInt("ID"));
          sub.setLang(subrs.getString("LANG"));
          sub.setFlavor(subrs.getString("FLAVOR"));
          sub.setType(SubtitleType.valueOfStableIndex(subrs.getInt("TYPE")));
          media.getSubtitleTracksList().add(sub);
        }
        subrs.close();
        subs.close();

        list.add(media);
      }
    } catch (SQLException se) {
      LOGGER.error(null, se);
      return null;
    } finally {
      close(rs);
      close(stmt);
      close(conn);
    }
    return list;
  }
  /** Process Button Pressed - Process Matching */
  private void cmd_newProduct() {
    // Selecciono el departamento
    int depart = 0;
    String SQL =
        "Select XX_VMR_DEPARTMENT_ID "
            + "from XX_VMR_VENDORPRODREF "
            + "where XX_VMR_VENDORPRODREF_ID="
            + LineRefProv.getXX_VMR_VendorProdRef_ID();

    try {
      PreparedStatement pstmt = DB.prepareStatement(SQL, null);
      ResultSet rs = pstmt.executeQuery();

      while (rs.next()) {
        depart = rs.getInt("XX_VMR_DEPARTMENT_ID");
      }

      rs.close();
      pstmt.close();

    } catch (Exception a) {
      log.log(Level.SEVERE, SQL, a);
    }

    MVMRVendorProdRef vendorProdRef =
        new MVMRVendorProdRef(Env.getCtx(), LineRefProv.getXX_VMR_VendorProdRef_ID(), null);

    if (vendorProdRef.getXX_VMR_ProductClass_ID() > 0
        && vendorProdRef.getXX_VMR_TypeLabel_ID() > 0) {
      MOrder order = new MOrder(Env.getCtx(), LineRefProv.getC_Order_ID(), null);

      // Selecciono el departamento
      X_XX_VMR_Department dept =
          new X_XX_VMR_Department(Env.getCtx(), vendorProdRef.getXX_VMR_Department_ID(), null);
      int category = dept.getXX_VMR_Category_ID();

      // Selecciono la línea
      X_XX_VMR_Line line = new X_XX_VMR_Line(Env.getCtx(), vendorProdRef.getXX_VMR_Line_ID(), null);
      int typeInventory = line.getXX_VMR_TypeInventory_ID();

      MProduct newProduct =
          new MProduct(
              Env.getCtx(),
              vendorProdRef.getXX_VMR_Department_ID(),
              vendorProdRef.getXX_VMR_Line_ID(),
              vendorProdRef.getXX_VMR_Section_ID(),
              vendorProdRef.get_ID(),
              vendorProdRef.getC_TaxCategory_ID(),
              vendorProdRef.getXX_VME_ConceptValue_ID(),
              typeInventory,
              null);

      // Se buscará si por la referencia para producto ya existe para asignarle el Tipo de
      // Exhibición
      String sql =
          "select * from M_Product where XX_VMR_DEPARTMENT_ID = "
              + vendorProdRef.getXX_VMR_Department_ID()
              + " and "
              + "XX_VMR_LINE_ID = "
              + vendorProdRef.getXX_VMR_Line_ID()
              + " and XX_VMR_SECTION_ID = "
              + vendorProdRef.getXX_VMR_Section_ID()
              + " and "
              + "XX_VMR_VendorProdRef_id = "
              + vendorProdRef.getXX_VMR_VendorProdRef_ID()
              + " order by M_Product_ID desc";
      PreparedStatement pstmt = DB.prepareStatement(sql, null);
      ResultSet rs = null;
      try {
        rs = pstmt.executeQuery();
        if (rs.next())
          newProduct.setXX_VMR_TypeExhibition_ID(rs.getInt("XX_VMR_TypeExhibition_ID"));
      } catch (SQLException e) {

        e.printStackTrace();
      } finally {
        DB.closeResultSet(rs);
        DB.closeStatement(pstmt);
      }

      if (vendorProdRef.getXX_VMR_Section_ID() > 0) {
        X_XX_VMR_Section section =
            new X_XX_VMR_Section(Env.getCtx(), vendorProdRef.getXX_VMR_Section_ID(), null);
        newProduct.setName(section.getName());
      } else {
        newProduct.setName(vendorProdRef.getName());
      }
      newProduct.setXX_VMR_Category_ID(category);
      newProduct.setXX_VMR_LongCharacteristic_ID(vendorProdRef.getXX_VMR_LongCharacteristic_ID());
      newProduct.setXX_VMR_Brand_ID(vendorProdRef.getXX_VMR_Brand_ID());
      newProduct.setXX_VMR_UnitConversion_ID(vendorProdRef.getXX_VMR_UnitConversion_ID());
      newProduct.setXX_PiecesBySale_ID(vendorProdRef.getXX_PiecesBySale_ID());
      newProduct.setXX_VMR_UnitPurchase_ID(vendorProdRef.getXX_VMR_UnitPurchase_ID());
      newProduct.setXX_SaleUnit_ID(vendorProdRef.getXX_SaleUnit_ID());
      newProduct.setC_Country_ID(order.getC_Country_ID());
      newProduct.setIsActive(true);
      newProduct.setC_BPartner_ID(vendorProdRef.getC_BPartner_ID());
      newProduct.setXX_VMR_TypeLabel_ID(vendorProdRef.getXX_VMR_TypeLabel_ID());
      newProduct.setXX_VMR_ProductClass_ID(vendorProdRef.getXX_VMR_ProductClass_ID());
      newProduct.setM_AttributeSet_ID(Env.getCtx().getContextAsInt("#XX_L_P_ATTRIBUTESETST_ID"));
      newProduct.setProductType(X_Ref_M_Product_ProductType.ITEM.getValue());
      newProduct.save();

    } else {
      // Creo variables de sesion para atraparlas en la ventana producto
      Env.getCtx().setContext("#Depart_Aux", depart);
      Env.getCtx().setContext("#Section_Aux", LineRefProv.getXX_VMR_Section_ID());
      Env.getCtx().setContext("#Line_Aux", LineRefProv.getXX_VMR_Line_ID());
      Env.getCtx().setContext("#VendorRef_Aux", LineRefProv.getXX_VMR_VendorProdRef_ID());
      Env.getCtx().setContext("#FromProcess_Aux", "Y");

      AWindow window_product = new AWindow();
      Query query = Query.getNoRecordQuery("M_Product", true);
      window_product.initWindow(140, query);
      AEnv.showCenterScreen(window_product);

      // Obtenemos el GridController para setear la variable m_changed=true
      JRootPane jRootPane = ((JRootPane) window_product.getComponent(0));
      JLayeredPane jLayeredPane = (JLayeredPane) jRootPane.getComponent(1);
      JPanel jPanel = (JPanel) jLayeredPane.getComponent(0);
      APanel aPanel = (APanel) jPanel.getComponent(0);
      VTabbedPane vTabbedPane = (VTabbedPane) aPanel.getComponent(0);
      GridController gridController = (GridController) vTabbedPane.getComponent(0);
      GridTable mTable = gridController.getMTab().getTableModel();
      mTable.setChanged(true);

      MProduct.loadLineRefProv(LineRefProv, Env.getCtx());

      // Borro las variables de sesion creadas
      Env.getCtx().remove("#Depart_Aux");
      Env.getCtx().remove("#FromProcess_Aux");
      Env.getCtx().remove("#Line_Aux");
      Env.getCtx().remove("#Section_Aux");
      Env.getCtx().remove("#VendorRef_Aux");
    }
  } //  cmd_newProduct
  /** Dispose */
  public void dispose() {
    if (m_frame != null) m_frame.dispose();
    m_frame = null;

    MOrder order = new MOrder(Env.getCtx(), LineRefProv.getC_Order_ID(), null);
    String oS = order.getXX_OrderStatus();
    String compS = order.getDocStatus();
    if (!oS.equals("AN") && !compS.equals("CO")) {

      if (isInMatrix()) {

        fullAssociated();

      } else {
        String SQL10 =
            "UPDATE XX_VMR_PO_LineRefProv "
                + " SET XX_ReferenceIsAssociated='N'"
                + " WHERE XX_VMR_PO_LineRefProv_ID="
                + LineRefProv.getXX_VMR_PO_LineRefProv_ID();

        DB.executeUpdate(null, SQL10);
        //				LineRefProv.setXX_ReferenceIsAssociated(false);
        //				LineRefProv.save();
      }
    }

    // pelle

    String SQL =
        ("SELECT A.XX_REFERENCEISASSOCIATED AS ASOCIATE "
            + "FROM XX_VMR_PO_LINEREFPROV A , C_ORDER B "
            + "WHERE A.C_ORDER_ID = B.C_ORDER_ID "
            + "AND A.C_ORDER_ID = '"
            + LineRefProv.getC_Order_ID()
            + "' ");

    try {

      PreparedStatement pstmt = DB.prepareStatement(SQL, null);
      ResultSet rs = pstmt.executeQuery();

      BigDecimal cifTotal = new BigDecimal(0);
      Boolean check = true;
      Integer maritimo = 0;
      Integer aereo = 0;
      Integer terrestre = 0;
      BigDecimal montoBs = new BigDecimal(0);
      BigDecimal rate = new BigDecimal(0);
      BigDecimal Seguro = new BigDecimal(0);
      BigDecimal perfleteInt = new BigDecimal(0);
      BigDecimal fleteInternac = new BigDecimal(0);

      Integer productid = 0;
      Integer poline = 0;
      BigDecimal costoitem = new BigDecimal(0);
      BigDecimal porcentitem = new BigDecimal(0);
      BigDecimal cifuni = new BigDecimal(0);
      BigDecimal percentarancel = new BigDecimal(0);
      BigDecimal arancelitem = new BigDecimal(0);
      BigDecimal rateseniat = new BigDecimal(0);
      BigDecimal montoseniat = new BigDecimal(0);
      BigDecimal ratetesoreria = new BigDecimal(0);
      BigDecimal montotesoreria = new BigDecimal(0);
      BigDecimal tasaaduana = new BigDecimal(0);
      BigDecimal part1 = new BigDecimal(0);
      BigDecimal part2 = new BigDecimal(0);
      BigDecimal iva = new BigDecimal(0);
      BigDecimal ivaactual = new BigDecimal(0);
      BigDecimal part = new BigDecimal(0);
      BigDecimal montesorerianac = new BigDecimal(0);

      while (rs.next()) {
        if (rs.getString("ASOCIATE").trim().equalsIgnoreCase("N")) {
          check = false;
        }
      }
      rs.close();
      pstmt.close();
      if (check == true) {

        String SQL2 =
            ("SELECT (A.TOTALLINES * B.MULTIPLYRATE) + A.XX_INTNACESTMEDAMOUNT + A.XX_ESTEEMEDINSURANCEAMOUNT  AS CIFTOTAL "
                + "FROM C_ORDER A, C_CONVERSION_RATE B "
                + "WHERE B.C_CONVERSION_RATE_ID = A.XX_CONVERSIONRATE_ID "
                + "AND A.C_ORDER_ID = '"
                + LineRefProv.getC_Order_ID()
                + "' "
                + "AND A.AD_Client_ID IN(0,"
                + Env.getCtx().getAD_Client_ID()
                + ") "
                + "AND B.AD_Client_ID IN(0,"
                + Env.getCtx().getAD_Client_ID()
                + ")");

        PreparedStatement pstmt2 = DB.prepareStatement(SQL2, null);
        ResultSet rs2 = pstmt2.executeQuery();

        if (rs2.next()) {
          // Calculo del CIF Total

          cifTotal = rs2.getBigDecimal("CIFTOTAL");
        }
        rs2.close();
        pstmt2.close();

        // Busco la via de despacho de la O/C
        String SQL3 =
            ("SELECT XX_L_DISPATCHROUTE AS AEREO, XX_L_DISPATCHROUTEMAR AS MARITIMO, XX_L_DISPATCHROUTETER AS TERRESTRE "
                + "FROM XX_VSI_KEYNAMEINFO ");

        PreparedStatement pstmt3 = DB.prepareStatement(SQL3, null);
        ResultSet rs3 = pstmt3.executeQuery();

        if (rs3.next()) {
          maritimo = rs3.getInt("MARITIMO");
          terrestre = rs3.getInt("TERRESTRE");
          aereo = rs3.getInt("AEREO");
        }
        rs3.close();
        pstmt3.close();

        String SQL4 =
            ("SELECT (A.TOTALLINES * B.MULTIPLYRATE) AS MONTO "
                + "FROM C_ORDER A, C_CONVERSION_RATE B "
                + "WHERE B.C_CONVERSION_RATE_ID = A.XX_CONVERSIONRATE_ID "
                + "AND A.C_ORDER_ID = '"
                + LineRefProv.getC_Order_ID()
                + "' ");

        PreparedStatement pstmt4 = DB.prepareStatement(SQL4, null);
        ResultSet rs4 = pstmt4.executeQuery();

        if (rs4.next()) {
          // Monto de la O/C en Bs
          montoBs = rs4.getBigDecimal("MONTO");
        }
        rs4.close();
        pstmt4.close();

        if (order.getXX_VLO_DispatchRoute_ID() == maritimo) {

          String SQL5 =
              ("SELECT XX_RATE AS RATE "
                  + "FROM XX_VLO_DispatchRoute "
                  + "WHERE XX_VLO_DispatchRoute_ID = '"
                  + maritimo
                  + "' ");

          PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
          ResultSet rs5 = pstmt5.executeQuery();

          if (rs5.next()) {
            rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100));
            // (new BigDecimal(100), 2, RoundingMode.HALF_UP);

            // Calculo del Seguro con Via Maritimo
            Seguro = montoBs.multiply(rate);
          }
          rs5.close();
          pstmt5.close();

        } else if (order.getXX_VLO_DispatchRoute_ID() == aereo) {
          String SQL5 =
              ("SELECT XX_RATE AS RATE "
                  + "FROM XX_VLO_DispatchRoute "
                  + "WHERE XX_VLO_DispatchRoute_ID = '"
                  + aereo
                  + "' ");

          PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
          ResultSet rs5 = pstmt5.executeQuery();

          if (rs5.next()) {
            rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);

            // Calculo del Seguro con Via aereo
            Seguro = montoBs.multiply(rate);
          }
          rs5.close();
          pstmt5.close();
        } else if (order.getXX_VLO_DispatchRoute_ID() == terrestre) {
          String SQL5 =
              ("SELECT XX_RATE AS RATE "
                  + "FROM XX_VLO_DispatchRoute "
                  + "WHERE XX_VLO_DispatchRoute_ID = '"
                  + terrestre
                  + "' ");

          PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
          ResultSet rs5 = pstmt5.executeQuery();

          if (rs5.next()) {
            rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);

            // Calculo del Seguro con Via terrestre
            Seguro = montoBs.multiply(rate);
          }
          rs5.close();
          pstmt5.close();
        }

        // Busco el porcentaje del flete internacional segun el proveedor, puerto y pais de la O/C

        String SQL6 =
            ("SELECT DISTINCT A.XX_INTERFREESTIMATEPERT AS PERTFLEINTER "
                + "FROM XX_VLO_COSTSPERCENT A, C_ORDER B "
                + "WHERE B.XX_VLO_ARRIVALPORT_ID = '"
                + order.getXX_VLO_ArrivalPort_ID()
                + "' "
                + "AND B.C_BPARTNER_ID = '"
                + order.getC_BPartner_ID()
                + "' "
                + "AND B.C_COUNTRY_ID = '"
                + order.getC_Country_ID()
                + "' "
                + "AND B.XX_VLO_ARRIVALPORT_ID = A.XX_VLO_ARRIVALPORT_ID "
                + "AND B.XX_VLO_ARRIVALPORT_ID = A.XX_VLO_ARRIVALPORT_ID "
                + "AND B.C_BPARTNER_ID = A.C_BPARTNER_ID "
                + "AND B.C_COUNTRY_ID = A.C_COUNTRY_ID ");

        PreparedStatement pstmt6 = DB.prepareStatement(SQL6, null);
        ResultSet rs6 = pstmt6.executeQuery();

        if (rs6.next()) {
          // Porcentaje del flete internacional
          perfleteInt =
              rs6.getBigDecimal("PERTFLEINTER")
                  .divide(new BigDecimal(100), 4, RoundingMode.HALF_UP);
          // Flete Internacional
          fleteInternac = montoBs.multiply(perfleteInt);
        }
        rs6.close();
        pstmt6.close();

        // Busco los productos que tienen asociados las Ref de la O/C

        String SQL7 =
            ("SELECT C.M_PRODUCT AS PRODUCT, A.XX_VMR_PO_LINEREFPROV_ID AS POLINE "
                + "FROM XX_VMR_PO_LINEREFPROV A, C_ORDER B, XX_VMR_REFERENCEMATRIX C "
                + "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
                + "AND A.C_ORDER_ID = B.C_ORDER_ID "
                + "AND A.C_ORDER_ID = '"
                + LineRefProv.getC_Order_ID()
                + "' ");

        PreparedStatement pstmt7 = DB.prepareStatement(SQL7, null);
        ResultSet rs7 = pstmt7.executeQuery();

        while (rs7.next()) {
          // Calculo del Porcentaje de cada item = costo de cada item Bs / Monto O/C Bs
          productid = rs7.getInt("PRODUCT");
          poline = rs7.getInt("POLINE");

          // Costo de cada item Bs

          /*String SQL8 = ("SELECT (A.XX_UNITPURCHASEPRICE / B.XX_UNITCONVERSION) AS COSTO " +
          "FROM XX_VMR_PO_LINEREFPROV A, XX_VMR_UNITCONVERSION B, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D " +
          "WHERE A.XX_VMR_UNITCONVERSION_ID = B.XX_VMR_UNITCONVERSION_ID " +
          "AND A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID " +
          "AND C.M_PRODUCT = D.M_PRODUCT_ID " +
          "AND C.M_PRODUCT = '"+productid+"' " +
          "AND A.XX_VMR_PO_LINEREFPROV_ID = '"+poline+"' ");*/

          String SQL8 =
              ("SELECT (A.LINENETAMT * E.MULTIPLYRATE) AS COSTO "
                  + "FROM XX_VMR_PO_LINEREFPROV A, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D, C_CONVERSION_RATE E, C_ORDER F  "
                  + "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
                  + "AND C.M_PRODUCT = D.M_PRODUCT_ID "
                  + "AND E.C_CONVERSION_RATE_ID = F.XX_CONVERSIONRATE_ID "
                  + "AND A.C_ORDER_ID = F.C_ORDER_ID "
                  + "AND C.M_PRODUCT = '"
                  + productid
                  + "' "
                  + "AND A.XX_VMR_PO_LINEREFPROV_ID = '"
                  + poline
                  + "' ");

          PreparedStatement pstmt8 = DB.prepareStatement(SQL8, null);
          ResultSet rs8 = pstmt8.executeQuery();

          if (rs8.next()) {
            costoitem = rs8.getBigDecimal("COSTO");

            // Porcentaje de cada item = costo de cada item Bs / Monto O/C Bs
            porcentitem = (costoitem.divide(montoBs, 8, RoundingMode.HALF_UP));

            // CIF Unitario = Porcentaje de cada Item * CIF total
            cifuni = porcentitem.multiply(cifTotal);

            // Busco Porcentaje Arancelario
            String SQL9 =
                ("SELECT (D.XX_PERCENTAGETARIFF/100) AS PERARANCEL "
                    + "FROM XX_VMR_PO_LINEREFPROV A, C_ORDER B, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D "
                    + "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
                    + "AND A.C_ORDER_ID = B.C_ORDER_ID "
                    + "AND C.M_PRODUCT = D.M_PRODUCT_ID "
                    + "AND D.M_PRODUCT_ID = '"
                    + productid
                    + "' "
                    + "AND A.C_ORDER_ID = '"
                    + LineRefProv.getC_Order_ID()
                    + "' ");

            PreparedStatement pstmt9 = DB.prepareStatement(SQL9, null);
            ResultSet rs9 = pstmt9.executeQuery();

            if (rs9.next()) {
              // Porcentaje Arancelario
              percentarancel = rs9.getBigDecimal("PERARANCEL");

              // Arancel de cada item = CIF unitario * Porcentaje Arancelario
              arancelitem = cifuni.multiply(percentarancel).add(arancelitem);
            }
            rs9.close();
            pstmt9.close();
          }
          rs8.close();
          pstmt8.close();

          // cif total(Creo que no hace falta) t arancel item se usan abajo
        } // end While rs7
        rs7.close();
        pstmt7.close();

        String SQL9 =
            ("SELECT XX_RATE/100 AS RATESENIAT FROM XX_VLO_IMPORTRATE WHERE NAME = 'Tasa de Servicio de aduanas SENIAT' "
                + "And AD_Client_ID IN(0,"
                + Env.getCtx().getAD_Client_ID()
                + ")");

        PreparedStatement pstmt9 = DB.prepareStatement(SQL9, null);
        ResultSet rs9 = pstmt9.executeQuery();

        if (rs9.next()) {
          rateseniat = rs9.getBigDecimal("RATESENIAT");
          montoseniat = arancelitem.multiply(rateseniat);

          String SQL10 =
              ("SELECT XX_RATE/100 AS RATETESORERIA FROM XX_VLO_IMPORTRATE WHERE NAME = 'Tasa de Servicio Aduana Tesorería' "
                  + "And AD_Client_ID IN(0,"
                  + Env.getCtx().getAD_Client_ID()
                  + ")");

          PreparedStatement pstmt10 = DB.prepareStatement(SQL10, null);
          ResultSet rs10 = pstmt10.executeQuery();

          if (rs10.next()) {
            ratetesoreria = rs10.getBigDecimal("RATETESORERIA");
            montotesoreria = arancelitem.multiply(ratetesoreria);

            // Monto Tasa aduanera =monto tasa seniat + monto tasa tesoreria
            tasaaduana = montoseniat.add(montotesoreria);
          }
          rs10.close();
          pstmt10.close();
        }
        rs9.close();
        pstmt9.close();

        // Calculo del IVA = (CIF total + Impuesto de importación + tasa aduanera) *%iva actual

        String SQL11 =
            ("SELECT MAX (VALIDFROM) AS FECHA, A.RATE/100 AS IVACT "
                + "FROM C_TAX A, C_TAXCATEGORY B "
                + "WHERE A.C_TAXCATEGORY_ID = B.C_TAXCATEGORY_ID "
                + "AND B.DESCRIPTION = 'Impuesto al Valor Agregado' "
                + "AND A.C_TAXCATEGORY_ID = B.C_TAXCATEGORY_ID "
                + "GROUP BY A.RATE ");

        PreparedStatement pstmt11 = DB.prepareStatement(SQL11, null);
        ResultSet rs11 = pstmt11.executeQuery();

        if (rs11.next()) {
          part1 = cifTotal.add(arancelitem);
          part2 = part1.add(tasaaduana);
          iva = part2.multiply(rs11.getBigDecimal("IVACT"));
        }
        rs11.close();
        pstmt11.close();

        // Monto tesorería Nacional = IVA + impuesto de importación + monto tasa tesoreria

        part = iva.add(arancelitem);
        montesorerianac = part.add(montotesoreria);

        // redondeo los BigDecimal
        montesorerianac = montesorerianac.setScale(2, BigDecimal.ROUND_UP);
        montoseniat = montoseniat.setScale(2, BigDecimal.ROUND_UP);

        String sql =
            "UPDATE C_Order po"
                + " SET XX_NatTreasuryEstAmount="
                + montesorerianac
                + "    , XX_SENIATESTEEMEDAMUNT="
                + montoseniat
                + " WHERE po.C_Order_ID="
                + LineRefProv.getC_Order_ID();
        DB.executeUpdate(null, sql);

      } // end if Check
      else {
        // alguna referencia no tiene producto asociado
        String sql =
            "UPDATE C_Order po"
                + " SET XX_NatTreasuryEstAmount="
                + 0
                + "    , XX_SENIATESTEEMEDAMUNT="
                + 0
                + " WHERE po.C_Order_ID="
                + LineRefProv.getC_Order_ID();
        DB.executeUpdate(null, sql);
      }

    } // end try
    catch (Exception e) {

    }
  } //	dispose