vcreatefrominvoice.java

Java写的ERP系统

		int AD_Client_ID = ((Integer)m_mTab.getValue("AD_Client_ID")).intValue();
		int AD_Org_ID = ((Integer)m_mTab.getValue("AD_Org_ID")).intValue();
		int CreatedBy = Env.getContextAsInt(Env.getCtx(), "#AD_User_ID");
		int C_Invoice_ID = ((Integer)m_mTab.getValue("C_Invoice_ID")).intValue();
		Log.trace(Log.l4_Data, "Client=" + AD_Client_ID + ", Org=" + AD_Org_ID
			+ ", User=" + CreatedBy + ", Invoice=" + C_Invoice_ID);

		//  required to derive values
		Timestamp billDate = (Timestamp)m_mTab.getValue("DateInvoiced");
		Timestamp shipDate = billDate;                                  //  wrong, should get it from shipment (if shipment)
		int M_Warehouse_ID = Env.getContextAsInt(Env.getCtx(), "#M_Warehouse_ID");   //  wromg, should get it from shipment or order
		int bill_Location_ID = ((Integer)m_mTab.getValue("C_BPartner_Location_ID")).intValue();
		int ship_Location_ID = bill_Location_ID;                        //  wrong

		int M_PriceList_ID = ((Integer)m_mTab.getValue("M_PriceList_ID")).intValue();;
		int M_PriceListVersion_ID = 0;
		String sql0 = "SELECT plv.M_PriceList_Version_ID "
			+ "FROM M_PriceList pl,M_PriceList_Version plv "
			+ "WHERE pl.M_PriceList_ID=plv.M_PriceList_ID AND plv.IsActive='Y'"
			+ " AND plv.ValidFrom <= ? AND pl.M_PriceList_ID=? "
			+ "ORDER BY plv.ValidFrom DESC";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql0);
			pstmt.setTimestamp(1, billDate);
			pstmt.setInt(2, M_PriceList_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				M_PriceListVersion_ID = rs.getInt(1);
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			Log.error ("VCreateFromInvoice.save - PL_Version - " + sql0, e);
		}
		//  No PriceList
		if (M_PriceListVersion_ID == 0)
		{
			ADialog.error(m_WindowNo, this, "PriceListVersionNotFound", String.valueOf(M_PriceList_ID) + " " + billDate.toString());
			Log.trace(Log.l6_Database, sql0);
			return false;
		}


		//  Lines
		TableModel model = dataTable.getModel();
		int rows = model.getRowCount();
		for (int i = 0; i < rows; i++)
		{
			if (((Boolean)model.getValueAt(i, 0)).booleanValue())
			{
				//  variable values
				int C_InvoiceLine_ID = DB.getKeyNextNo(Env.getCtx(), m_WindowNo, "C_InvoiceLine");
				Double d = (Double)model.getValueAt(i, 1);              //  1-Qty
				BigDecimal QtyInvoiced = new BigDecimal(d.doubleValue());
				KeyNamePair pp = (KeyNamePair)model.getValueAt(i, 2);   //  2-UOM
				int C_UOM_ID = pp.getKey();
				//
				pp = (KeyNamePair)model.getValueAt(i, 3);               //  3-Product
				int M_Product_ID = 0;
				if (pp != null)
					M_Product_ID = pp.getKey();
				int C_Charge_ID = 0;
				//
				int C_OrderLine_ID = 0;
				pp = (KeyNamePair)model.getValueAt(i, 4);               //  4-OrderLine
				if (pp != null)
					C_OrderLine_ID = pp.getKey();
				int M_InOutLine_ID = 0;
				pp = (KeyNamePair)model.getValueAt(i, 5);               //  5-Shipment
				if (pp != null)
					M_InOutLine_ID = pp.getKey();
				//
				Log.trace(Log.l5_DData, "Line=" + C_InvoiceLine_ID + ", Qty=" + QtyInvoiced
					+ ", Product=" + M_Product_ID + ", OrderLine=" + C_OrderLine_ID + ", ShipmentLine=" + M_InOutLine_ID);

				//  Info
				String Description = null;

				/**
				 *  Get Shipment Info: Description & C_OrderLine_ID
				 */
				if (M_InOutLine_ID != 0)
				{
					sql0 = "SELECT Description, C_OrderLine_ID FROM M_InOutLine WHERE M_InOutLine_ID=?";
					try
					{
						PreparedStatement pstmt = DB.prepareStatement(sql0);
						pstmt.setInt(1, M_InOutLine_ID);
						ResultSet rs = pstmt.executeQuery();
						if (rs.next())
						{
							Description = rs.getString(1);
							C_OrderLine_ID = rs.getInt(2);
						}
						rs.close();
						pstmt.close();
					}
					catch (SQLException e)
					{
						Log.error ("VCreateFromInvoice.save-InOutLine", e);
					}
				}

				/**
				 *  Get Prices
				 */
				BigDecimal PriceList = new BigDecimal(0.0);
				BigDecimal PriceActual = new BigDecimal(0.0);
				BigDecimal PriceLimit = new BigDecimal(0.0);
				//
				if (C_OrderLine_ID != 0)
				{
					//  Get Price from Order
					sql0 = "SELECT PriceActual,PriceList,PriceLimit,Description,C_Charge_ID FROM C_OrderLine WHERE C_OrderLine_ID=?";
					try
					{
						PreparedStatement pstmt = DB.prepareStatement(sql0);
						pstmt.setInt(1, C_OrderLine_ID);
						ResultSet rs = pstmt.executeQuery();
						if (rs.next())
						{
							PriceActual = rs.getBigDecimal(1);
							PriceList = rs.getBigDecimal(2);
							PriceLimit = rs.getBigDecimal(3);
							if (Description == null)    //  might be set in Shipment
								Description = rs.getString(4);
							C_Charge_ID = rs.getInt(5);
						}
						rs.close();
						pstmt.close();
					}
					catch (SQLException e)
					{
						Log.error ("VCreateFromInvoice.save-OrderLine", e);
					}
				}
				else
				{
					//  Get Prices from PriceList
					sql0 = "SELECT BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceStd,"
						+ "BOM_PriceList(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceList,"
						+ "BOM_PriceLimit(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceLimit "
						+ "FROM M_ProductPrice pp "
						+ "WHERE pp.M_Product_ID=?"				//	1
						+ " AND pp.M_PriceList_Version_ID=?";	//	2
					try
					{
						PreparedStatement pstmt = DB.prepareStatement(sql0);
						pstmt.setInt(1, M_Product_ID);
						pstmt.setInt(2, M_PriceListVersion_ID);
						ResultSet rs = pstmt.executeQuery();
						if (rs.next())
						{
							PriceActual = rs.getBigDecimal(1);
							PriceList = rs.getBigDecimal(2);
							PriceLimit = rs.getBigDecimal(3);
						}
						rs.close();
						pstmt.close();
					}
					catch (SQLException e)
					{
						Log.error ("VCreateFromInvoice.save-Price - " + sql0, e);
					}
				}

				//  Get Tax
				boolean IsSOTrx = false;
				int C_Tax_ID = Tax.get(Env.getCtx(), M_Product_ID, C_Charge_ID, billDate, shipDate,
					AD_Org_ID, M_Warehouse_ID, bill_Location_ID, ship_Location_ID, IsSOTrx);
				//
				BigDecimal LineNetAmt = PriceActual.multiply(QtyInvoiced).setScale(2);
				Log.trace(Log.l5_DData, ".. PriceList=" + PriceList + ", Actual=" + PriceActual
					+ ", Limit=" + PriceLimit + ", Net=" + LineNetAmt + ", Tax=" + C_Tax_ID);

				//
				StringBuffer sql = new StringBuffer("INSERT INTO C_InvoiceLine");
				sql.append("(C_InvoiceLine_ID,C_Invoice_ID,")
					.append("AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,")
					.append("C_OrderLine_ID,M_InOutLine_ID,")
					.append("Line,Description,")
					.append("M_Product_ID,C_UOM_ID,QtyInvoiced,")
					.append("PriceList,PriceActual,PriceLimit,")
					.append("LineNetAmt,")
					.append("C_Charge_ID,ChargeAmt,")
					.append("C_Tax_ID)")
					.append(" VALUES (");
				//
				sql.append(C_InvoiceLine_ID).append(",").append(C_Invoice_ID).append(",")
					.append(AD_Client_ID).append(",").append(AD_Org_ID).append(",'Y',")
					.append("SysDate,").append(CreatedBy).append(",SysDate,").append(CreatedBy).append(",");
				//  C_OrderLine_ID,M_InOutLine_ID,
				if (C_OrderLine_ID == 0)
					sql.append("NULL,");
				else
					sql.append(C_OrderLine_ID).append(",");
				if (M_InOutLine_ID == 0)
					sql.append("NULL,");
				else
					sql.append(M_InOutLine_ID).append(",");
				//  Line,
				sql.append("(SELECT (NVL(Max(Line),0))+10 FROM C_InvoiceLine WHERE C_Invoice_ID=").append(C_Invoice_ID).append("),");
				//  Description
				if (Description == null || Description.length() == 0)
					sql.append("NULL,");
				else
					sql.append("'").append(Description).append("',");
				//  M_Product_ID,C_UOM_ID,QtyInvoiced,
				if (M_Product_ID == 0)
					sql.append("NULL");
				else
					sql.append(M_Product_ID);
				sql.append(",").append(C_UOM_ID).append(",").append(QtyInvoiced).append(",");
				//  PriceList,PriceActual,PriceLimit,
				sql.append(PriceList).append(",").append(PriceActual).append(",").append(PriceLimit).append(",");
				sql.append(LineNetAmt).append(",");
				//  C_Charge_ID,ChargeAmt,
				if (C_Charge_ID == 0)
					sql.append("NULL,0,");
				else
					sql.append(C_Charge_ID).append(",0,");
				//  C_Tax_ID
				sql.append(C_Tax_ID).append(")");
				//
				int no = DB.executeUpdate(sql.toString());
				if (no != 1)
					Log.error("VCreateFromInvoice.save - Line created NOT #" + no);
			}   //   if selected
		}   //  for all rows

		/**
		 *  Update Header
		 *  - if linked to another order - remove link
		 *  - if no link set it
		 */
		if (m_C_Order_ID != 0)
		{
			StringBuffer sql = new StringBuffer ("UPDATE C_Invoice SET C_Order_ID=NULL"
				+ " WHERE C_Invoice_ID=").append(C_Invoice_ID)
				.append(" AND C_Order_ID IS NOT NULL AND C_Order_ID <> ").append(m_C_Order_ID);
			int no = DB.executeUpdate(sql.toString());
			if (no == 0)
			{
				sql = new StringBuffer ("UPDATE C_Invoice"
					+ " SET (C_Order_ID, M_PriceList_ID, PaymentRule, C_PaymentTerm_ID)="
					+ " (SELECT C_Order_ID, M_PriceList_ID, PaymentRule, C_PaymentTerm_ID"
					+ " FROM C_Order WHERE C_Order_ID=").append(m_C_Order_ID).append(") "
					+ "WHERE C_Invoice_ID=").append(C_Invoice_ID);
				no = DB.executeUpdate(sql.toString());
			}
		}
		return true;
	}   //  saveInvoice


}   //  VCreateFromInvoice