vmatch.java

Java写的ERP系统

			KeyNamePair Product = (KeyNamePair)xMatchedTable.getValueAt(row, I_Product);
			Log.trace(Log.l5_DData, "BPartner=" + BPartner, "Product=" + Product);
			//
			if (sameBPartner.isSelected())
				m_sql.append(" AND hdr.C_BPartner_ID=").append(BPartner.getKey());
			if (sameProduct.isSelected())
				m_sql.append(" AND lin.M_Product_ID=").append(Product.getKey());

			//  calculate qty
			double docQty = ((Double)xMatchedTable.getValueAt(row, I_QTY)).doubleValue();
			double matchedQty = ((Double)xMatchedTable.getValueAt(row, I_MATCHED)).doubleValue();
			qty = docQty - matchedQty;
			if (sameQty.isSelected())
				m_sql.append(" AND ").append(m_qtyColumn).append("=").append(docQty);
			//  ** Load Table **
			tableLoad (xMatchedToTable);
		}
		//  Display To be Matched Qty
		m_xMatched = new BigDecimal (qty);
		xMatched.setValue(m_xMatched);
		xMatchedTo.setValue(Env.ZERO);
		difference.setValue(m_xMatched);
		//  Status Info
		statusBar.setStatusLine(matchFrom.getSelectedItem().toString()
			+ "# = " + xMatchedTable.getRowCount() + " - "
			+ matchTo.getSelectedItem().toString()
			+  "# = " + xMatchedToTable.getRowCount(),
			xMatchedToTable.getRowCount() == 0);
		statusBar.setStatusDB(0);
	}   //  cmd_seachTo

	/**************************************************************************/

	/**
	 *  Table Model Listener - calculate matchd Qty
	 *  @param e event
	 */
	public void tableChanged (TableModelEvent e)
	{
		if (e.getColumn() != 0)
			return;
		Log.trace(Log.l4_Data, "VMatch.tableChanged",
			"Row=" + e.getFirstRow() + "-" + e.getLastRow() + ", Col=" + e.getColumn()
			+ ", Type=" + e.getType());
		setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));

		//  Matched From
		int matchedRow = xMatchedTable.getSelectedRow();
		KeyNamePair Product = (KeyNamePair)xMatchedTable.getValueAt(matchedRow, 5);

		//  Matched To
		double qty = 0.0;
		int noRows = 0;
		for (int row = 0; row < xMatchedToTable.getRowCount(); row++)
		{
			IDColumn id = (IDColumn)xMatchedToTable.getValueAt(row, 0);
			if (id != null && id.isSelected())
			{
				KeyNamePair ProductCompare = (KeyNamePair)xMatchedToTable.getValueAt(row, 5);
				if (Product.getKey() != ProductCompare.getKey())
				{
					id.setSelected(false);
				}
				else
				{
					if (matchMode.getSelectedIndex() == MODE_NOTMATCHED)
						qty += ((Double)xMatchedToTable.getValueAt(row, I_QTY)).doubleValue();  //  doc
					qty -= ((Double)xMatchedToTable.getValueAt(row, I_MATCHED)).doubleValue();  //  matched
					noRows++;
				}
			}
		}
		//  update qualtities
		m_xMatchedTo = new BigDecimal(qty);
		xMatchedTo.setValue(m_xMatchedTo);
		difference.setValue(m_xMatched.subtract(m_xMatchedTo));
		bProcess.setEnabled(noRows != 0);
		setCursor(Cursor.getDefaultCursor());
		//  Status
		statusBar.setStatusDB(noRows);
	}   //  tableChanged

	/*************************************************************************/

	/**
	 *  Initialise Table access - create SQL, dateColumn.
	 *  <br>
	 *  The driving table is "hdr", e.g. for hdr.C_BPartner_ID=..
	 *  The line table is "lin", e.g. for lin.M_Product_ID=..
	 *  You use the dateColumn/qtyColumn variable directly as it is table specific.
	 *  <br>
	 *  The sql is dependent on MatchMode:
	 *  - If Matched - all (fully or partially) matched records are listed
	 *  - If Not Matched - all not fully matched records are listed
	 *  @param display (Invoice, Shipment, Order) see MATCH_*
	 *  @param matchTo (Invoice, Shipment, Order) see MATCH_*
	 */
	private void tableInit (int display, int matchTo)
	{
		boolean matched = matchMode.getSelectedIndex() == MODE_MATCHED;
		Log.trace(Log.l4_Data, "VMatch.tableInit", "Display=" + m_matchOptions[display]
			+ ", MatchTo=" + m_matchOptions[matchTo]
			+ ", Matched=" + matched);

		m_sql = new StringBuffer ();
		if (display == MATCH_INVOICE)
		{
			m_dateColumn = "hdr.DateInvoiced";
			m_qtyColumn = "lin.QtyInvoiced";
			m_sql.append("SELECT hdr.C_Invoice_ID,hdr.DocumentNo, hdr.DateInvoiced, bp.Name,hdr.C_BPartner_ID,"
				+ " lin.Line,lin.C_InvoiceLine_ID, p.Name,lin.M_Product_ID,"
				+ " lin.QtyInvoiced,SUM(NVL(mi.Qty,0)) "
				+ "FROM C_Invoice hdr, C_BPartner bp, C_InvoiceLine lin, M_Product p,"
				+ " C_DocType dt, M_MatchInv mi "
				+ "WHERE hdr.C_BPartner_ID=bp.C_BPartner_ID AND hdr.C_Invoice_ID=lin.C_Invoice_ID"
				+ " AND lin.M_Product_ID=p.M_Product_ID AND hdr.C_DocType_ID=dt.C_DocType_ID"
				+ " AND dt.DocBaseType IN ('API','APC')"
				+ " AND lin.C_InvoiceLine_ID=mi.C_InvoiceLine_ID(+)");
			m_groupBy = " GROUP BY hdr.C_Invoice_ID,hdr.DocumentNo,hdr.DateInvoiced,bp.Name,hdr.C_BPartner_ID,"
				+ " lin.Line,lin.C_InvoiceLine_ID,p.Name,lin.M_Product_ID,lin.QtyInvoiced "
				+ "HAVING "
				+ (matched ? "0" : "lin.QtyInvoiced")
				+ "<>SUM(NVL(mi.Qty,0))";
		}
		else if (display == MATCH_ORDER)
		{
			m_dateColumn = "hdr.DateOrdered";
			m_qtyColumn = "lin.QtyOrdered";
			m_sql.append("SELECT hdr.C_Order_ID,hdr.DocumentNo, hdr.DateOrdered, bp.Name,hdr.C_BPartner_ID,"
				+ " lin.Line,lin.C_OrderLine_ID, p.Name,lin.M_Product_ID,"
				+ " lin.QtyOrdered,SUM(NVL(mo.Qty,0)) "
				+ "FROM C_Order hdr, C_BPartner bp, C_OrderLine lin, M_Product p,"
				+ " C_DocType dt, M_MatchPO mo "
				+ "WHERE hdr.C_BPartner_ID=bp.C_BPartner_ID AND hdr.C_Order_ID=lin.C_Order_ID"
				+ " AND lin.M_Product_ID=p.M_Product_ID AND hdr.C_DocType_ID=dt.C_DocType_ID"
				+ " AND dt.DocBaseType='POO'"
				+ " AND lin.C_OrderLine_ID=mo.C_OrderLine_ID(+)");
			m_groupBy = " GROUP BY hdr.C_Order_ID,hdr.DocumentNo,hdr.DateOrdered,bp.Name,hdr.C_BPartner_ID,"
				+ " lin.Line,lin.C_OrderLine_ID,p.Name,lin.M_Product_ID,lin.QtyOrdered "
				+ "HAVING "
				+ (matched ? "0" : "lin.QtyOrdered")
				+ "<>SUM(NVL(mo.Qty,0))";
		}
		else    //  Shipment
		{
			m_dateColumn = "hdr.MovementDate";
			m_qtyColumn = "lin.MovementQty";
			boolean toPO = matchTo == MATCH_ORDER;
			m_sql.append("SELECT hdr.M_InOut_ID,hdr.DocumentNo, hdr.MovementDate, bp.Name,hdr.C_BPartner_ID,"
				+ " lin.Line,lin.M_InOutLine_ID, p.Name,lin.M_Product_ID,"
				+ " lin.MovementQty,SUM(NVL(m.Qty,0)) "
				+ "FROM M_InOut hdr, C_BPartner bp, M_InOutLine lin, M_Product p, C_DocType dt, ")
				.append(toPO ? "M_MatchPO m " : "M_MatchInv m ")
				.append("WHERE hdr.C_BPartner_ID=bp.C_BPartner_ID AND hdr.M_InOut_ID=lin.M_InOut_ID"
				+ " AND lin.M_Product_ID=p.M_Product_ID AND hdr.C_DocType_ID = dt.C_DocType_ID"
				+ " AND dt.DocBaseType='MMR'"
				+ " AND lin.M_InOutLine_ID=m.M_InOutLine_ID(+)");
			m_groupBy = " GROUP BY hdr.M_InOut_ID,hdr.DocumentNo,hdr.MovementDate,bp.Name,hdr.C_BPartner_ID,"
				+ " lin.Line,lin.M_InOutLine_ID,p.Name,lin.M_Product_ID,lin.MovementQty "
				+ "HAVING "
				+ (matched ? "0" : "lin.MovementQty")
				+ "<>SUM(NVL(m.Qty,0))";
		}
		Log.trace(7, "VMatch.tableInit", m_sql);
	}   //  tableInit


	/**
	 *  Fill the table using m_sql
	 *  @param table table
	 */
	private void tableLoad (MiniTable table)
	{
		String sql = Access.addROAccessSQL(Env.getCtx(), m_sql.toString(), "hdr", true)
			+ m_groupBy;
		try
		{
			Statement stmt = DB.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			table.loadTable(rs);
			stmt.close();
		}
		catch (SQLException e)
		{
			Log.error("MVatch.tableFill " + sql, e);
		}
	}   //  tableLoad

	/**
	 *  Create Matching Record
	 *  @param invoice true if matching invoice false if matching PO
	 *  @param M_InOutLine_ID shipment line
	 *  @param Line_ID C_InvoiceLine_ID or C_OrderLine_ID
	 *  @param M_Product_ID product
	 *  @param qty quantity
	 *  @return true if created
	 */
	private boolean createMatchRecord (boolean invoice, int M_InOutLine_ID, int Line_ID,
		int M_Product_ID, double qty)
	{
		if (qty == 0.0)
			return true;
		Log.trace(Log.l6_Database, "VMatch.createMatchRecord", "IsInvoice=" + invoice
			+ ", M_InOutLine_ID=" + M_InOutLine_ID + ", Line_ID=" + Line_ID
			+ ", M_Product_ID=" + M_Product_ID + ", Qty=" + qty);
		//
		StringBuffer sql = new StringBuffer ("INSERT INTO ");
		int id = 0;
		if (invoice)
		{
			sql.append("M_MatchInv (M_MatchInv_ID, ");
			id = DB.getKeyNextNo(Env.getCtx(), m_WindowNo, "M_MatchInv");
		}
		else
		{
			sql.append("M_MatchPO (M_MatchPO_ID, ");
			id = DB.getKeyNextNo(Env.getCtx(), m_WindowNo, "M_MatchInv");
		}
		sql.append("AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy, "
			+ "M_InOutLine_ID,");
		sql.append(invoice ? "C_InvoiceLine_ID," : "C_OrderLine_ID,");
		sql.append("M_Product_ID, DateTrx,Qty,"
			+ "Processing,Processed,Posted) VALUES (");
		//  Values
		sql.append(id).append(",");
		//  AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
		sql.append(m_AD_Client_ID).append(",").append(m_AD_Org_ID).append(",'Y',SysDate,")
			.append(m_by).append(",SysDate,").append(m_by).append(",");
		//  M_InOutLine_ID, xxLine_ID,
		sql.append(M_InOutLine_ID).append(",").append(Line_ID).append(",");
		//  M_Product_ID, DateTrx,Qty,
		if (M_Product_ID == 0)
			sql.append("NULL");
		else
			sql.append(M_Product_ID);
		sql.append(",SysDate,").append(qty).append(",");
		//  Processing,Processed,Posted
		sql.append("'N','Y','N')");
		//
		int no = DB.executeUpdate(sql.toString());
		if (no != 1)
			Log.error("VMatch.createMatchRecord - not created: " + sql.toString());

		//	Update Ordered Quantity when matched to PO
		if (!invoice && no == 1)
		{
			//	Invoice=false, M_InOutLine_ID=108, Line_ID=105, M_Product_ID=127, Qty=6
			sql = new StringBuffer ("UPDATE M_Storage "
				+ "SET QtyOrdered = QtyOrdered-").append(qty)
				.append(" WHERE M_Product_ID=").append(M_Product_ID)
				.append(" AND M_Locator_ID=(SELECT M_Locator_ID FROM M_InOutLine WHERE M_InOutLine_ID=")
				.append(M_InOutLine_ID).append(")");
			no = DB.executeUpdate(sql.toString());
			if (no != 1)
				Log.error("VMatch.createMatchRecord - QtyOrdered not updated: " + sql.toString());
		}
		return no == 1;
	}   //  createMatchRecord

}   //  VMatch