Пример #1
0
  /** Dynamic Init for Price Tab */
  private boolean dynInit() {
    //	Header
    Vector<String> columnNames = new Vector<String>();
    columnNames.add(
        Msg.translate(Env.getCtx(), m_C_BPartner_ID == 0 ? "C_BPartner_ID" : "M_Product_ID"));
    columnNames.add(Msg.translate(Env.getCtx(), "PriceActual"));
    columnNames.add(Msg.translate(Env.getCtx(), "C_Currency_ID"));
    columnNames.add(Msg.translate(Env.getCtx(), "QtyInvoiced"));
    columnNames.add(Msg.translate(Env.getCtx(), "Discount"));
    columnNames.add(Msg.translate(Env.getCtx(), "DocumentNo"));
    columnNames.add(Msg.translate(Env.getCtx(), "DateInvoiced"));
    columnNames.add(Msg.translate(Env.getCtx(), "AD_Org_ID"));

    //	Fill Data
    Vector<Vector<Object>> data = null;
    if (m_C_BPartner_ID == 0) data = queryBPartner(); // 	BPartner of Product
    else data = queryProduct(); // 	Product of BPartner

    //  Table
    m_modelPrice = new ListModelTable(data);
    m_tablePrice.setData(m_modelPrice, columnNames);
    //
    m_tablePrice.setColumnClass(0, String.class, true); //  Product/Partner
    m_tablePrice.setColumnClass(1, Double.class, true); //  Price
    m_tablePrice.setColumnClass(2, String.class, true); //  Currency
    m_tablePrice.setColumnClass(3, Double.class, true); //  Quantity
    m_tablePrice.setColumnClass(4, BigDecimal.class, true); //  Discount (%) to limit precision
    m_tablePrice.setColumnClass(5, String.class, true); //  DocNo
    m_tablePrice.setColumnClass(6, Timestamp.class, true); //  Date
    m_tablePrice.setColumnClass(7, String.class, true); //  Org
    //
    m_tablePrice.autoSize();
    //

    return data.size() != 0;
  } //	dynInit
Пример #2
0
  /** Query ATP */
  private void initAtpTab() {
    //	Done already
    if (m_modelAtp != null) return;

    //	Header
    Vector<String> columnNames = new Vector<String>();
    columnNames.add(Msg.translate(Env.getCtx(), "Date"));
    columnNames.add(Msg.translate(Env.getCtx(), "QtyOnHand"));
    columnNames.add(Msg.translate(Env.getCtx(), "C_BPartner_ID"));
    columnNames.add(Msg.translate(Env.getCtx(), "QtyOrdered"));
    columnNames.add(Msg.translate(Env.getCtx(), "QtyReserved"));
    columnNames.add(Msg.translate(Env.getCtx(), "M_Locator_ID"));
    columnNames.add(Msg.translate(Env.getCtx(), "M_AttributeSetInstance_ID"));
    columnNames.add(Msg.translate(Env.getCtx(), "DocumentNo"));
    columnNames.add(Msg.translate(Env.getCtx(), "M_Warehouse_ID"));

    //	Fill Storage Data
    String sql =
        "SELECT s.QtyOnHand, s.QtyReserved, s.QtyOrdered,"
            + " productAttribute(s.M_AttributeSetInstance_ID), s.M_AttributeSetInstance_ID,";
    if (!showDetailATP)
      sql =
          "SELECT SUM(s.QtyOnHand), SUM(s.QtyReserved), SUM(s.QtyOrdered),"
              + " productAttribute(s.M_AttributeSetInstance_ID), 0,";
    sql +=
        " w.Name, l.Value "
            + "FROM M_Storage s"
            + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID)"
            + " LEFT JOIN M_LocatorType lt ON (l.M_LocatorType_ID=lt.M_LocatorType_ID)"
            + " INNER JOIN M_Warehouse w ON (l.M_Warehouse_ID=w.M_Warehouse_ID) "
            + "WHERE M_Product_ID=?";
    if (m_M_Warehouse_ID != 0) sql += " AND l.M_Warehouse_ID=?";
    if (m_M_AttributeSetInstance_ID > 0) sql += " AND s.M_AttributeSetInstance_ID=?";
    sql += " AND (s.QtyOnHand<>0 OR s.QtyReserved<>0 OR s.QtyOrdered<>0)";
    sql += " AND COALESCE(lt.IsAvailableForReservation,'Y')='Y'";
    if (!showDetailATP)
      sql += " GROUP BY productAttribute(s.M_AttributeSetInstance_ID), w.Name, l.Value";
    sql += " ORDER BY l.Value";

    Vector<Vector<Object>> data = new Vector<Vector<Object>>();
    double qty = 0;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, null);
      pstmt.setInt(1, m_M_Product_ID);
      if (m_M_Warehouse_ID != 0) pstmt.setInt(2, m_M_Warehouse_ID);
      if (m_M_AttributeSetInstance_ID > 0) pstmt.setInt(3, m_M_AttributeSetInstance_ID);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        Vector<Object> line = new Vector<Object>(9);
        line.add(null); //  Date
        double qtyOnHand = rs.getDouble(1);
        qty += qtyOnHand;
        line.add(new Double(qtyOnHand)); //  Qty
        line.add(null); //  BPartner
        line.add(new Double(rs.getDouble(3))); //  QtyOrdered
        line.add(new Double(rs.getDouble(2))); //  QtyReserved
        line.add(rs.getString(7)); //  Locator
        String asi = rs.getString(4);
        if (showDetailATP && (asi == null || asi.length() == 0)) asi = "{" + rs.getInt(5) + "}";
        line.add(asi); //  ASI
        line.add(null); //  DocumentNo
        line.add(rs.getString(6)); // 	Warehouse
        data.add(line);
      }
    } catch (SQLException e) {
      log.log(Level.SEVERE, sql, e);
    } finally {
      DB.close(rs, pstmt);
      rs = null;
      pstmt = null;
    }

    //	Orders
    sql =
        "SELECT o.DatePromised, ol.QtyReserved,"
            + " productAttribute(ol.M_AttributeSetInstance_ID), ol.M_AttributeSetInstance_ID,"
            + " dt.DocBaseType, bp.Name,"
            + " dt.PrintName || ' ' || o.DocumentNo As DocumentNo, w.Name "
            + "FROM C_Order o"
            + " INNER JOIN C_OrderLine ol ON (o.C_Order_ID=ol.C_Order_ID)"
            + " INNER JOIN C_DocType dt ON (o.C_DocType_ID=dt.C_DocType_ID)"
            + " INNER JOIN M_Warehouse w ON (ol.M_Warehouse_ID=w.M_Warehouse_ID)"
            + " INNER JOIN C_BPartner bp  ON (o.C_BPartner_ID=bp.C_BPartner_ID) "
            + "WHERE ol.QtyReserved<>0"
            + " AND ol.M_Product_ID=?";
    if (m_M_Warehouse_ID != 0) sql += " AND ol.M_Warehouse_ID=?";
    if (m_M_AttributeSetInstance_ID > 0) sql += " AND ol.M_AttributeSetInstance_ID=?";
    sql += " ORDER BY o.DatePromised";
    try {
      pstmt = DB.prepareStatement(sql, null);
      pstmt.setInt(1, m_M_Product_ID);
      if (m_M_Warehouse_ID != 0) pstmt.setInt(2, m_M_Warehouse_ID);
      if (m_M_AttributeSetInstance_ID > 0) pstmt.setInt(3, m_M_AttributeSetInstance_ID);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        Vector<Object> line = new Vector<Object>(9);
        line.add(rs.getTimestamp(1)); //  Date
        double oq = rs.getDouble(2);
        String DocBaseType = rs.getString(5);
        Double qtyReserved = null;
        Double qtyOrdered = null;
        if (MDocType.DOCBASETYPE_PurchaseOrder.equals(DocBaseType)) {
          qtyOrdered = new Double(oq);
          qty += oq;
        } else {
          qtyReserved = new Double(oq);
          qty -= oq;
        }
        line.add(new Double(qty)); //  Qty
        line.add(rs.getString(6)); //  BPartner
        line.add(qtyOrdered); //  QtyOrdered
        line.add(qtyReserved); //  QtyReserved
        line.add(null); //  Locator
        String asi = rs.getString(3);
        if (showDetailATP && (asi == null || asi.length() == 0)) asi = "{" + rs.getInt(4) + "}";
        line.add(asi); //  ASI
        line.add(rs.getString(7)); //  DocumentNo
        line.add(rs.getString(8)); // 	Warehouse
        data.add(line);
      }
    } catch (SQLException e) {
      log.log(Level.SEVERE, sql, e);
    } finally {
      DB.close(rs, pstmt);
      rs = null;
      pstmt = null;
    }

    //  Table
    m_modelAtp = new ListModelTable(data);
    m_tableAtp.setData(m_modelAtp, columnNames);
    //
    m_tableAtp.setColumnClass(0, Timestamp.class, true); //  Date
    m_tableAtp.setColumnClass(1, Double.class, true); //  Quantity
    m_tableAtp.setColumnClass(2, String.class, true); //  Partner
    m_tableAtp.setColumnClass(3, Double.class, true); //  Quantity
    m_tableAtp.setColumnClass(4, Double.class, true); //  Quantity
    m_tableAtp.setColumnClass(5, String.class, true); //  Locator
    m_tableAtp.setColumnClass(6, String.class, true); //  ASI
    m_tableAtp.setColumnClass(7, String.class, true); //  DocNo
    m_tableAtp.setColumnClass(8, String.class, true); //  Warehouse
    //
    m_tableAtp.autoSize();
  } //	initAtpTab
Пример #3
0
  /** Query Unconfirmed */
  private void initUnconfirmedTab() {
    //	Done already
    if (m_modelUnconfirmed != null) return;

    //	Header
    Vector<String> columnNames = new Vector<String>();
    columnNames.add(
        Msg.translate(Env.getCtx(), m_C_BPartner_ID == 0 ? "C_BPartner_ID" : "M_Product_ID"));
    columnNames.add(Msg.translate(Env.getCtx(), "MovementQty"));
    columnNames.add(Msg.translate(Env.getCtx(), "MovementDate"));
    columnNames.add(Msg.translate(Env.getCtx(), "IsSOTrx"));
    columnNames.add(Msg.translate(Env.getCtx(), "DocumentNo"));
    columnNames.add(Msg.translate(Env.getCtx(), "M_Warehouse_ID"));

    //	Fill Data
    String sql = null;
    int parameter = 0;
    if (m_C_BPartner_ID == 0) {
      sql =
          "SELECT bp.Name,"
              + " CASE WHEN io.IsSOTrx='Y' THEN iol.MovementQty*-1 ELSE iol.MovementQty END AS MovementQty,"
              + " io.MovementDate,io.IsSOTrx,"
              + " dt.PrintName || ' ' || io.DocumentNo As DocumentNo,"
              + " w.Name "
              + "FROM M_InOutLine iol"
              + " INNER JOIN M_InOut io ON (iol.M_InOut_ID=io.M_InOut_ID)"
              + " INNER JOIN C_BPartner bp  ON (io.C_BPartner_ID=bp.C_BPartner_ID)"
              + " INNER JOIN C_DocType dt ON (io.C_DocType_ID=dt.C_DocType_ID)"
              + " INNER JOIN M_Warehouse w ON (io.M_Warehouse_ID=w.M_Warehouse_ID)"
              + " INNER JOIN M_InOutLineConfirm lc ON (iol.M_InOutLine_ID=lc.M_InOutLine_ID) "
              + "WHERE iol.M_Product_ID=?"
              + " AND lc.Processed='N' "
              + "ORDER BY io.MovementDate,io.IsSOTrx";
      parameter = m_M_Product_ID;
    } else {
      sql =
          "SELECT p.Name,"
              + " CASE WHEN io.IsSOTrx='Y' THEN iol.MovementQty*-1 ELSE iol.MovementQty END AS MovementQty,"
              + " io.MovementDate,io.IsSOTrx,"
              + " dt.PrintName || ' ' || io.DocumentNo As DocumentNo,"
              + " w.Name "
              + "FROM M_InOutLine iol"
              + " INNER JOIN M_InOut io ON (iol.M_InOut_ID=io.M_InOut_ID)"
              + " INNER JOIN M_Product p  ON (iol.M_Product_ID=p.M_Product_ID)"
              + " INNER JOIN C_DocType dt ON (io.C_DocType_ID=dt.C_DocType_ID)"
              + " INNER JOIN M_Warehouse w ON (io.M_Warehouse_ID=w.M_Warehouse_ID)"
              + " INNER JOIN M_InOutLineConfirm lc ON (iol.M_InOutLine_ID=lc.M_InOutLine_ID) "
              + "WHERE io.C_BPartner_ID=?"
              + " AND lc.Processed='N' "
              + "ORDER BY io.MovementDate,io.IsSOTrx";
      parameter = m_C_BPartner_ID;
    }
    Vector<Vector<Object>> data = new Vector<Vector<Object>>();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, null);
      pstmt.setInt(1, parameter);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        Vector<Object> line = new Vector<Object>(6);
        //	1-Name, 2-MovementQty, 3-MovementDate, 4-IsSOTrx, 5-DocumentNo
        line.add(rs.getString(1)); //  Name
        line.add(new Double(rs.getDouble(2))); //  Qty
        line.add(rs.getTimestamp(3)); //  Date
        line.add(new Boolean("Y".equals(rs.getString(4)))); //  IsSOTrx
        line.add(rs.getString(5)); //  DocNo
        line.add(rs.getString(6)); //  Warehouse
        data.add(line);
      }
    } catch (SQLException e) {
      log.log(Level.SEVERE, sql, e);
    } finally {
      DB.close(rs, pstmt);
      rs = null;
      pstmt = null;
    }
    if (log.isLoggable(Level.FINE)) log.fine("#" + data.size());

    //  Table
    m_modelUnconfirmed = new ListModelTable(data);
    m_tableUnconfirmed.setData(m_modelUnconfirmed, columnNames);
    //
    m_tableUnconfirmed.setColumnClass(0, String.class, true); //  Product/Partner
    m_tableUnconfirmed.setColumnClass(1, Double.class, true); //  MovementQty
    m_tableUnconfirmed.setColumnClass(2, Timestamp.class, true); //  MovementDate
    m_tableUnconfirmed.setColumnClass(3, Boolean.class, true); //  IsSOTrx
    m_tableUnconfirmed.setColumnClass(4, String.class, true); //  DocNo
    //
    m_tableUnconfirmed.autoSize();
  } //	initUnconfirmedTab
Пример #4
0
  /**
   * Query Reserved/Ordered
   *
   * @param reserved po/so
   */
  private void initReservedOrderedTab(boolean reserved) {
    //	Done already
    if (reserved && m_modelReserved != null) return;
    if (!reserved && m_modelOrdered != null) return;

    //	Header
    Vector<String> columnNames = new Vector<String>();
    columnNames.add(
        Msg.translate(Env.getCtx(), m_C_BPartner_ID == 0 ? "C_BPartner_ID" : "M_Product_ID"));
    columnNames.add(Msg.translate(Env.getCtx(), "PriceActual"));
    columnNames.add(Msg.translate(Env.getCtx(), "C_Currency_ID"));
    columnNames.add(Msg.translate(Env.getCtx(), reserved ? "QtyReserved" : "QtyOrdered"));
    columnNames.add(Msg.translate(Env.getCtx(), "Discount"));
    columnNames.add(Msg.translate(Env.getCtx(), "DocumentNo"));
    columnNames.add(Msg.translate(Env.getCtx(), "DateOrdered"));
    columnNames.add(Msg.translate(Env.getCtx(), "M_Warehouse_ID"));

    //	Fill Data
    Vector<Vector<Object>> data = null;
    if (m_C_BPartner_ID == 0) {
      String sql =
          "SELECT bp.Name, ol.PriceActual,c.Iso_Code,ol.PriceList,ol.QtyReserved,"
              + "o.DateOrdered,dt.PrintName || ' ' || o.DocumentNo As DocumentNo, "
              + "w.Name,"
              + "ol.Discount, 0 " // 8,9=M_PriceList_ID
              + "FROM C_Order o"
              + " INNER JOIN C_OrderLine ol ON (o.C_Order_ID=ol.C_Order_ID)"
              + " INNER JOIN C_DocType dt ON (o.C_DocType_ID=dt.C_DocType_ID)"
              + " INNER JOIN M_Warehouse w ON (ol.M_Warehouse_ID=w.M_Warehouse_ID)"
              + " INNER JOIN C_BPartner bp  ON (o.C_BPartner_ID=bp.C_BPartner_ID) "
              + " INNER JOIN C_Currency c ON (o.C_Currency_ID=c.C_Currency_ID) "
              + "WHERE ol.QtyReserved<>0"
              + " AND ol.M_Product_ID=?"
              + " AND o.IsSOTrx="
              + (reserved ? "'Y'" : "'N'")
              + " ORDER BY o.DateOrdered";
      data = fillTable(sql, m_M_Product_ID); // 	Product By BPartner
    } else {
      String sql =
          "SELECT p.Name, ol.PriceActual,c.Iso_Code,ol.PriceList,ol.QtyReserved,"
              + "o.DateOrdered,dt.PrintName || ' ' || o.DocumentNo As DocumentNo, "
              + "w.Name,"
              + "ol.Discount, 0 " // 8,9=M_PriceList_ID
              + "FROM C_Order o"
              + " INNER JOIN C_OrderLine ol ON (o.C_Order_ID=ol.C_Order_ID)"
              + " INNER JOIN C_DocType dt ON (o.C_DocType_ID=dt.C_DocType_ID)"
              + " INNER JOIN M_Warehouse w ON (ol.M_Warehouse_ID=w.M_Warehouse_ID)"
              + " INNER JOIN M_Product p  ON (ol.M_Product_ID=p.M_Product_ID) "
              + " INNER JOIN C_Currency c ON (o.C_Currency_ID=c.C_Currency_ID) "
              + "WHERE ol.QtyReserved<>0"
              + " AND o.C_BPartner_ID=?"
              + " AND o.IsSOTrx="
              + (reserved ? "'Y'" : "'N'")
              + " ORDER BY o.DateOrdered";
      data = fillTable(sql, m_C_BPartner_ID); // 	Product of BP
    }

    //  Table
    if (reserved) {
      m_modelReserved = new ListModelTable(data);
      m_tableReserved.setData(m_modelReserved, columnNames);
      //
      m_tableReserved.setColumnClass(0, String.class, true); //  Product/Partner
      m_tableReserved.setColumnClass(1, BigDecimal.class, true); //  Price
      m_tableReserved.setColumnClass(2, String.class, true); //  Currency
      m_tableReserved.setColumnClass(3, Double.class, true); //  Quantity
      m_tableReserved.setColumnClass(4, BigDecimal.class, true); //  Discount (%)
      m_tableReserved.setColumnClass(5, String.class, true); //  DocNo
      m_tableReserved.setColumnClass(6, Timestamp.class, true); //  Date
      m_tableReserved.setColumnClass(7, String.class, true); //  Warehouse
      //
      m_tableReserved.autoSize();
    } else {
      m_modelOrdered = new ListModelTable(data);
      m_tableOrdered.setData(m_modelOrdered, columnNames);
      //
      m_tableOrdered.setColumnClass(0, String.class, true); //  Product/Partner
      m_tableOrdered.setColumnClass(1, BigDecimal.class, true); //  Price
      m_tableOrdered.setColumnClass(2, String.class, true); //  Currency
      m_tableOrdered.setColumnClass(3, Double.class, true); //  Quantity
      m_tableOrdered.setColumnClass(4, BigDecimal.class, true); //  Discount (%)
      m_tableOrdered.setColumnClass(5, String.class, true); //  DocNo
      m_tableOrdered.setColumnClass(6, Timestamp.class, true); //  Date
      m_tableOrdered.setColumnClass(7, String.class, true); //  Warehouse
      //
      m_tableOrdered.autoSize();
    }
  } //	initReservedOrderedTab