/**
* Create shipment/receipt line
*
* @param invoice
* @param invoiceLine
* @return shipment/receipt line
*/
private MInOutLine createLine(MInvoice invoice, MInvoiceLine invoiceLine) {
BigDecimal qtyMatched = invoiceLine.getMatchedQty();
BigDecimal qtyInvoiced = invoiceLine.getQtyInvoiced();
BigDecimal qtyNotMatched = qtyInvoiced.subtract(qtyMatched);
// If is fully matched don't create anything
if (qtyNotMatched.signum() == 0) {
return null;
}
MInOut inout = getCreateHeader(invoice);
MInOutLine sLine = new MInOutLine(inout);
sLine.setInvoiceLine(
invoiceLine,
0, // Locator
invoice.isSOTrx() ? qtyNotMatched : Env.ZERO);
sLine.setQtyEntered(qtyNotMatched);
sLine.setMovementQty(qtyNotMatched);
if (invoice.isCreditMemo()) {
sLine.setQtyEntered(sLine.getQtyEntered().negate());
sLine.setMovementQty(sLine.getMovementQty().negate());
}
sLine.saveEx();
//
invoiceLine.setM_InOutLine_ID(sLine.getM_InOutLine_ID());
invoiceLine.saveEx();
//
return sLine;
}
/**
* Create Shipment
*
* @return info
* @throws Exception
*/
protected String doIt() throws Exception {
log.info("C_Invoice_ID=" + p_C_Invoice_ID + ", M_Warehouse_ID=" + p_M_Warehouse_ID);
if (p_C_Invoice_ID <= 0) throw new FillMandatoryException("C_Invoice_ID");
if (p_M_Warehouse_ID == 0) throw new FillMandatoryException(PARAM_M_Warehouse_ID);
//
MInvoice invoice = new MInvoice(getCtx(), p_C_Invoice_ID, null);
if (invoice.get_ID() <= 0) throw new AdempiereException("@NotFound@ @C_Invoice_ID@");
if (!MInvoice.DOCSTATUS_Completed.equals(invoice.getDocStatus()))
throw new AdempiereException("@InvoiceCreateDocNotCompleted@");
//
for (MInvoiceLine invoiceLine : invoice.getLines(false)) {
createLine(invoice, invoiceLine);
}
if (m_inout == null) throw new InvoiceFullyMatchedException();
//
return m_inout.getDocumentNo();
} // doIt
/**
* Complete Invoice
*
* @param invoice invoice
*/
private void completeInvoice(MInvoice invoice) {
if (invoice == null) return;
invoice.setDocAction(DocAction.ACTION_Prepare);
invoice.processIt(DocAction.ACTION_Prepare);
if (!invoice.save()) new IllegalStateException("Cannot save Invoice");
//
m_noInvoices++;
addLog(
invoice.get_ID(),
invoice.getDateInvoiced(),
invoice.getGrandTotal(),
invoice.getDocumentNo());
} // completeInvoice
/**
* Perform process.
*
* @return Message (clear text)
* @throws Exception if not successful
*/
protected String doIt() throws Exception {
log.info("C_InvoicePaySchedule_ID=" + getRecord_ID());
MInvoicePaySchedule[] schedule =
MInvoicePaySchedule.getInvoicePaySchedule(getCtx(), 0, getRecord_ID(), null);
if (schedule.length == 0)
throw new IllegalArgumentException("InvoicePayScheduleValidate - No Schedule");
// Get Invoice
MInvoice invoice = new MInvoice(getCtx(), schedule[0].getC_Invoice_ID(), null);
if (invoice.get_ID() == 0)
throw new IllegalArgumentException("InvoicePayScheduleValidate - No Invoice");
//
BigDecimal total = Env.ZERO;
for (int i = 0; i < schedule.length; i++) {
BigDecimal due = schedule[i].getDueAmt();
if (due != null) total = total.add(due);
}
boolean valid = invoice.getGrandTotal().compareTo(total) == 0;
invoice.setIsPayScheduleValid(valid);
invoice.save();
// Schedule
for (int i = 0; i < schedule.length; i++) {
if (schedule[i].isValid() != valid) {
schedule[i].setIsValid(valid);
schedule[i].save();
}
}
String msg = "@OK@";
if (!valid)
msg =
"@GrandTotal@ = "
+ invoice.getGrandTotal()
+ " <> @Total@ = "
+ total
+ " - @Difference@ = "
+ invoice.getGrandTotal().subtract(total);
return Msg.parseTranslation(getCtx(), msg);
} // doIt
/**
* Process
*
* @return message
* @throws Exception
*/
protected String doIt() throws Exception {
Calendar dateFrom = Calendar.getInstance();
dateFrom.setTimeInMillis(System.currentTimeMillis());
dateFrom.set(Calendar.HOUR_OF_DAY, 0);
dateFrom.set(Calendar.MINUTE, 0);
dateFrom.set(Calendar.SECOND, 0);
dateFrom.set(Calendar.MILLISECOND, 0);
p_dateFrom = new Timestamp(dateFrom.getTimeInMillis());
p_dateFrom.setNanos(0);
log.info("Calculating initial balance");
/* initial balance */
StringBuilder sqlIni =
new StringBuilder(
"SELECT SUM(acctBalance(Account_ID,AmtAcctDr,AmtAcctCr)) FROM Fact_Acct WHERE DateAcct<=");
sqlIni
.append(DB.TO_DATE(p_dateFrom))
.append(" AND PostingType='")
.append(MFactAcct.POSTINGTYPE_Actual)
.append("' AND ");
// initial balance - the balance of selected account on selected schema on default hierarchy
String whereClause =
MReportTree.getWhereClause(
getCtx(), 0, MAcctSchemaElement.ELEMENTTYPE_Account, p_C_ElementValue_ID);
sqlIni.append(whereClause);
BigDecimal initialBalance = DB.getSQLValueBD(get_TrxName(), sqlIni.toString(), new Object[] {});
X_T_CashFlow cfini = new X_T_CashFlow(getCtx(), 0, get_TrxName());
cfini.setAD_Org_ID(0);
cfini.setAD_PInstance_ID(getAD_PInstance_ID());
cfini.setCashFlowSource(X_T_CashFlow.CASHFLOWSOURCE_1_InitialBalance);
cfini.setCashFlowType(X_T_CashFlow.CASHFLOWTYPE_Operational);
cfini.setDateTrx(p_dateFrom);
cfini.setIsActive(true);
cfini.setIsSOTrx(true);
cfini.setLineTotalAmt(initialBalance);
cfini.setProbability(Env.ONEHUNDRED);
cfini.setDateTo(p_dateTo);
cfini.setC_AcctSchema_ID(p_C_AcctSchema_ID);
cfini.setC_ElementValue_ID(p_C_ElementValue_ID);
if (!cfini.save()) throw new AdempiereSystemError("Error saving cash flow ini");
if (log.isLoggable(Level.INFO)) log.info("Initial balance calculated = " + initialBalance);
/* plan records */
String sqlPlan =
"SELECT cpl.AD_Org_ID, "
+ "COALESCE(cpl.C_Activity_ID, cp.C_Activity_ID) AS C_Activity_ID, "
+ "cp.CashFlowType, "
+ "COALESCE(cpl.C_BPartner_ID, cp.C_BPartner_ID) AS C_BPartner_ID, "
+ "COALESCE(cpl.C_Campaign_ID, cp.C_Campaign_ID) AS C_Campaign_ID, "
+ "cpl.C_Charge_ID, "
+ "COALESCE(cpl.C_Project_ID, cp.C_Project_ID) AS C_Project_ID, "
+ "cpl.DateTrx, "
+ "cpl.Description, "
+ "cp.IsSOTrx, "
+ "cpl.LineTotalAmt, "
+ "cpl.M_Product_ID, "
+ "cpl.Name, "
+ "cpl.Probability, "
+ "cpl.C_CashPlanLine_ID, "
+ "cp.C_CashPlan_ID "
+ "FROM C_CashPlanLine cpl "
+ "JOIN C_CashPlan cp ON (cp.C_CashPlan_ID=cpl.C_CashPlan_ID) "
+ "WHERE cp.AD_Client_ID=? AND cp.IsActive='Y' AND cpl.IsActive='Y' AND cpl.DateTrx BETWEEN ? AND ?";
PreparedStatement pstmtPlan = null;
ResultSet rsPlan = null;
try {
pstmtPlan = DB.prepareStatement(sqlPlan, get_TrxName());
pstmtPlan.setInt(1, getAD_Client_ID());
pstmtPlan.setTimestamp(2, p_dateFrom);
pstmtPlan.setTimestamp(3, p_dateTo);
rsPlan = pstmtPlan.executeQuery();
int noPlan = 0;
while (rsPlan.next()) {
noPlan++;
boolean issotrx = "Y".equals(rsPlan.getString("IsSOTrx"));
BigDecimal total = rsPlan.getBigDecimal("LineTotalAmt");
if (!issotrx) total = total.negate();
X_T_CashFlow cfplan = new X_T_CashFlow(getCtx(), 0, get_TrxName());
cfplan.setAD_Org_ID(rsPlan.getInt("AD_Org_ID"));
cfplan.setAD_PInstance_ID(getAD_PInstance_ID());
cfplan.setC_Activity_ID(rsPlan.getInt("C_Activity_ID"));
cfplan.setCashFlowSource(X_T_CashFlow.CASHFLOWSOURCE_2_Plan);
cfplan.setCashFlowType(rsPlan.getString("CashFlowType"));
cfplan.setC_BPartner_ID(rsPlan.getInt("C_BPartner_ID"));
cfplan.setC_Campaign_ID(rsPlan.getInt("C_Campaign_ID"));
cfplan.setC_Charge_ID(rsPlan.getInt("C_Charge_ID"));
cfplan.setC_Project_ID(rsPlan.getInt("C_Project_ID"));
cfplan.setDateTrx(rsPlan.getTimestamp("DateTrx"));
cfplan.setDescription(rsPlan.getString("Description"));
cfplan.setIsActive(true);
cfplan.setIsSOTrx(issotrx);
cfplan.setLineTotalAmt(total);
cfplan.setM_Product_ID(rsPlan.getInt("M_Product_ID"));
cfplan.setName(rsPlan.getString("Name"));
cfplan.setProbability(rsPlan.getBigDecimal("Probability"));
cfplan.setDateTo(p_dateTo);
cfplan.setC_AcctSchema_ID(p_C_AcctSchema_ID);
cfplan.setC_ElementValue_ID(p_C_ElementValue_ID);
cfplan.setC_CashPlanLine_ID(rsPlan.getInt("C_CashPlanLine_ID"));
if (!cfplan.save()) throw new AdempiereSystemError("Error saving cash flow plan");
}
if (log.isLoggable(Level.INFO)) log.info(noPlan + " plan inserted");
} catch (Exception e) {
log.log(Level.SEVERE, sqlPlan, e);
} finally {
DB.close(rsPlan, pstmtPlan);
rsPlan = null;
pstmtPlan = null;
}
/* commitment records */
String sqlOpenOrders =
"SELECT o.C_Order_ID, o.IsPayScheduleValid, "
+ "SUM((ol.QtyOrdered-ol.QtyInvoiced)*ol.PriceActual)/o.TotalLines as Pending "
+ "FROM C_Order o JOIN C_OrderLine ol ON (o.C_Order_ID=ol.C_Order_ID) "
+ "WHERE o.AD_Client_ID=? AND o.TotalLines != 0 AND o.DocStatus IN ('CO') AND ol.QtyInvoiced<ol.QtyOrdered "
+ "GROUP BY o.C_Order_ID, o.IsPayScheduleValid, o.TotalLines";
PreparedStatement pstmtOpenOrders = null;
ResultSet rsOpenOrders = null;
try {
pstmtOpenOrders = DB.prepareStatement(sqlOpenOrders, get_TrxName());
pstmtOpenOrders.setInt(1, getAD_Client_ID());
rsOpenOrders = pstmtOpenOrders.executeQuery();
int noOrders = 0;
int noOrdIns = 0;
int noOrdSchIns = 0;
while (rsOpenOrders.next()) {
noOrders++;
if ((noOrders % 100) == 0)
if (log.isLoggable(Level.INFO)) log.info(noOrders + " orders processed");
int order_id = rsOpenOrders.getInt("C_Order_ID");
boolean isPaySchedule = ("Y".equals(rsOpenOrders.getString("IsPayScheduleValid")));
BigDecimal pending = rsOpenOrders.getBigDecimal("Pending");
MOrder order = new MOrder(getCtx(), order_id, get_TrxName());
MCurrency curr = MCurrency.get(getCtx(), order.getC_Currency_ID());
BigDecimal open = order.getGrandTotal().multiply(pending);
// subtract payments done directly to order still not allocated to an invoice
// TODO: review if is possible to check just the balance for partially allocated payments
// TODO: currency convert of payment if different currency from order
BigDecimal paid =
DB.getSQLValueBD(
get_TrxName(),
"SELECT SUM(CASE WHEN IsReceipt='Y' THEN PayAmt ELSE -PayAmt END) FROM C_Payment WHERE DocStatus IN ('CO','CL') AND C_Order_ID=? AND C_Invoice_ID IS NULL AND IsAllocated='N'",
order_id);
if (paid != null) {
if (!order.isSOTrx()) paid = paid.negate();
open = open.subtract(paid);
}
if (open.scale() > curr.getStdPrecision())
open = open.setScale(curr.getStdPrecision(), BigDecimal.ROUND_HALF_UP);
BigDecimal invoiced = order.getGrandTotal().subtract(open);
if (isPaySchedule) {
MOrderPaySchedule[] schedule =
MOrderPaySchedule.getOrderPaySchedule(getCtx(), order_id, 0, get_TrxName());
BigDecimal accum = Env.ZERO;
for (MOrderPaySchedule ops : schedule) {
accum = accum.add(ops.getDueAmt());
if (invoiced.compareTo(accum) > 0) continue;
if (ops.getDueDate().compareTo(p_dateTo) <= 0) {
BigDecimal opensch;
if (accum.subtract(invoiced).compareTo(ops.getDueAmt()) > 0)
opensch = ops.getDueAmt();
else opensch = accum.subtract(invoiced);
if (!order.isSOTrx()) opensch = opensch.negate();
noOrdSchIns++;
X_T_CashFlow cforderps = new X_T_CashFlow(getCtx(), 0, get_TrxName());
cforderps.setAD_Org_ID(order.getAD_Org_ID());
cforderps.setAD_PInstance_ID(getAD_PInstance_ID());
cforderps.setC_Activity_ID(order.getC_Activity_ID());
cforderps.setCashFlowSource(X_T_CashFlow.CASHFLOWSOURCE_3_CommitmentsOrders);
// cforder.setCashFlowType(X_T_CashFlow.CASHFLOWTYPE_Operational);
cforderps.setC_BPartner_ID(order.getC_BPartner_ID());
cforderps.setC_Campaign_ID(order.getC_Campaign_ID());
cforderps.setC_Project_ID(order.getC_Project_ID());
cforderps.setDateTrx(ops.getDueDate());
cforderps.setDescription(order.getDescription());
cforderps.setIsActive(true);
cforderps.setIsSOTrx(order.isSOTrx());
cforderps.setLineTotalAmt(opensch);
cforderps.setProbability(Env.ONEHUNDRED);
cforderps.setDateTo(p_dateTo);
cforderps.setC_AcctSchema_ID(p_C_AcctSchema_ID);
cforderps.setC_ElementValue_ID(p_C_ElementValue_ID);
cforderps.setC_CashPlanLine_ID(order.getC_CashPlanLine_ID());
cforderps.setC_Order_ID(order_id);
if (!cforderps.save())
throw new AdempiereSystemError("Error saving cash flow order pay schedule");
}
}
} else {
Timestamp dueDate =
DB.getSQLValueTS(
get_TrxName(),
"SELECT paymentTermDueDate(?, ?) FROM Dual",
new Object[] {order.getC_PaymentTerm_ID(), order.getDateOrdered()});
if (dueDate.compareTo(p_dateTo) <= 0) {
if (!order.isSOTrx()) open = open.negate();
noOrdIns++;
X_T_CashFlow cforder = new X_T_CashFlow(getCtx(), 0, get_TrxName());
cforder.setAD_Org_ID(order.getAD_Org_ID());
cforder.setAD_PInstance_ID(getAD_PInstance_ID());
cforder.setC_Activity_ID(order.getC_Activity_ID());
cforder.setCashFlowSource(X_T_CashFlow.CASHFLOWSOURCE_3_CommitmentsOrders);
// cforder.setCashFlowType(X_T_CashFlow.CASHFLOWTYPE_Operational);
cforder.setC_BPartner_ID(order.getC_BPartner_ID());
cforder.setC_Campaign_ID(order.getC_Campaign_ID());
cforder.setC_Project_ID(order.getC_Project_ID());
cforder.setDateTrx(dueDate);
cforder.setDescription(order.getDescription());
cforder.setIsActive(true);
cforder.setIsSOTrx(order.isSOTrx());
cforder.setLineTotalAmt(open);
cforder.setProbability(Env.ONEHUNDRED);
cforder.setDateTo(p_dateTo);
cforder.setC_AcctSchema_ID(p_C_AcctSchema_ID);
cforder.setC_ElementValue_ID(p_C_ElementValue_ID);
cforder.setC_CashPlanLine_ID(order.getC_CashPlanLine_ID());
cforder.setC_Order_ID(order_id);
if (!cforder.save()) throw new AdempiereSystemError("Error saving cash flow order");
}
}
}
if (log.isLoggable(Level.INFO))
log.info(
noOrders
+ " orders processed, "
+ noOrdIns
+ " orders inserted, "
+ noOrdSchIns
+ " schedule inserted");
} catch (Exception e) {
log.log(Level.SEVERE, sqlOpenOrders, e);
} finally {
DB.close(rsOpenOrders, pstmtOpenOrders);
rsOpenOrders = null;
pstmtOpenOrders = null;
}
/* actual records */
String sqlActual =
"SELECT oi.AD_Org_ID, oi.C_Invoice_ID, oi.C_BPartner_ID, oi.IsSOTrx, oi.DueDate, oi.OpenAmt, oi.C_Campaign_ID, oi.C_Project_ID, oi.C_Activity_ID "
+ "FROM RV_OpenItem oi "
+ "WHERE oi.AD_Client_ID=? AND oi.DueDate <= ?";
PreparedStatement pstmtActual = null;
ResultSet rsActual = null;
try {
pstmtActual = DB.prepareStatement(sqlActual, get_TrxName());
pstmtActual.setInt(1, getAD_Client_ID());
pstmtActual.setTimestamp(2, p_dateTo);
rsActual = pstmtActual.executeQuery();
int noInv = 0;
while (rsActual.next()) {
boolean issotrx = "Y".equals(rsActual.getString("IsSOTrx"));
BigDecimal openamt = rsActual.getBigDecimal("OpenAmt");
if (!issotrx) openamt = openamt.negate();
MInvoice invoice = new MInvoice(getCtx(), rsActual.getInt("C_Invoice_ID"), get_TrxName());
noInv++;
X_T_CashFlow cfactual = new X_T_CashFlow(getCtx(), 0, get_TrxName());
cfactual.setAD_Org_ID(rsActual.getInt("AD_Org_ID"));
cfactual.setAD_PInstance_ID(getAD_PInstance_ID());
cfactual.setC_Activity_ID(rsActual.getInt("C_Activity_ID"));
cfactual.setCashFlowSource(X_T_CashFlow.CASHFLOWSOURCE_4_ActualDebtInvoices);
// cfactual.setCashFlowType(X_T_CashFlow.CASHFLOWTYPE_Operational);
cfactual.setC_BPartner_ID(rsActual.getInt("C_BPartner_ID"));
cfactual.setC_Campaign_ID(rsActual.getInt("C_Campaign_ID"));
cfactual.setC_Project_ID(rsActual.getInt("C_Project_ID"));
cfactual.setDateTrx(rsActual.getTimestamp("DueDate"));
cfactual.setDescription(invoice.getDescription());
cfactual.setIsActive(true);
cfactual.setIsSOTrx(issotrx);
cfactual.setLineTotalAmt(openamt);
cfactual.setProbability(Env.ONEHUNDRED);
cfactual.setDateTo(p_dateTo);
cfactual.setC_AcctSchema_ID(p_C_AcctSchema_ID);
cfactual.setC_ElementValue_ID(p_C_ElementValue_ID);
cfactual.setC_CashPlanLine_ID(invoice.getC_CashPlanLine_ID());
cfactual.setC_Invoice_ID(rsActual.getInt("C_Invoice_ID"));
if (!cfactual.save()) throw new AdempiereSystemError("Error saving cash flow actual");
}
if (log.isLoggable(Level.INFO)) log.info(noInv + " invoices inserted");
} catch (Exception e) {
log.log(Level.SEVERE, sqlActual, e);
} finally {
DB.close(rsActual, pstmtActual);
rsActual = null;
pstmtActual = null;
}
/* subtract from plan lines the related orders */
String sqlupdord =
"UPDATE T_CashFlow "
+ "SET LineTotalAmt = LineTotalAmt - "
+ "(SELECT COALESCE(SUM(LineTotalAmt),0) "
+ "FROM T_CashFlow cf "
+ "WHERE cf.AD_PInstance_ID = T_CashFlow.AD_Pinstance_ID "
+ "AND cf.C_CashPlanLine_ID=T_CashFlow.C_CashPlanLine_ID AND "
+ "CashFlowSource = ? /* Orders */) "
+ "WHERE AD_PInstance_ID = ? "
+ "AND CashFlowSource = ? /* Plan */ "
+ "AND EXISTS (SELECT 1 FROM T_CashFlow cf "
+ "WHERE cf.AD_PInstance_ID = T_CashFlow.AD_PInstance_ID "
+ "AND cf.C_CashPlanLine_ID=T_CashFlow.C_CashPlanLine_ID "
+ "AND CashFlowSource = ? /* Orders */)";
int noupdord =
DB.executeUpdate(
sqlupdord,
new Object[] {
X_T_CashFlow.CASHFLOWSOURCE_3_CommitmentsOrders,
getAD_PInstance_ID(),
X_T_CashFlow.CASHFLOWSOURCE_2_Plan,
X_T_CashFlow.CASHFLOWSOURCE_3_CommitmentsOrders
},
false,
get_TrxName());
if (log.isLoggable(Level.INFO)) log.info(noupdord + " plans subtracted from orders");
/* subtract from plan lines the related invoices */
String sqlupdinv =
"UPDATE T_CashFlow "
+ "SET LineTotalAmt = LineTotalAmt - "
+ "(SELECT COALESCE(SUM(LineTotalAmt),0) "
+ "FROM T_CashFlow cf "
+ "WHERE cf.AD_PInstance_ID = T_CashFlow.AD_Pinstance_ID "
+ "AND cf.C_CashPlanLine_ID=T_CashFlow.C_CashPlanLine_ID AND "
+ "CashFlowSource = ? /* Invoices */) "
+ "WHERE AD_PInstance_ID = ? "
+ "AND CashFlowSource = ? /* Plan */ "
+ "AND EXISTS (SELECT 1 FROM T_CashFlow cf "
+ "WHERE cf.AD_PInstance_ID = T_CashFlow.AD_PInstance_ID "
+ "AND cf.C_CashPlanLine_ID=T_CashFlow.C_CashPlanLine_ID "
+ "AND CashFlowSource = ? /* Invoices */)";
int noupdinv =
DB.executeUpdate(
sqlupdinv,
new Object[] {
X_T_CashFlow.CASHFLOWSOURCE_4_ActualDebtInvoices,
getAD_PInstance_ID(),
X_T_CashFlow.CASHFLOWSOURCE_2_Plan,
X_T_CashFlow.CASHFLOWSOURCE_4_ActualDebtInvoices
},
false,
get_TrxName());
if (log.isLoggable(Level.INFO)) log.info(noupdinv + " plans subtracted from invoices");
/* delete overplanned records */
String sqldeloverplanned =
"DELETE FROM T_CashFlow "
+ "WHERE AD_PInstance_ID = ? "
+ "AND CashFlowSource = ? /* Plan */ "
+ "AND ((IsSOTrx='Y' AND LineTotalAmt<=0) OR (IsSOTrx='N' AND LineTotalAmt>=0))";
int nodelplan =
DB.executeUpdate(
sqldeloverplanned,
new Object[] {getAD_PInstance_ID(), X_T_CashFlow.CASHFLOWSOURCE_2_Plan},
false,
get_TrxName());
if (log.isLoggable(Level.INFO)) log.info(nodelplan + " overplanned plans deleted");
return "OK";
} // doIt
/**
* Create GL Journal
*
* @return document info
*/
private String createGLJournal() {
// FR: [ 2214883 ] Remove SQL code and Replace for Query
String whereClause = "AD_PInstance_ID=?";
List<X_T_InvoiceGL> list =
new Query(getCtx(), X_T_InvoiceGL.Table_Name, whereClause, get_TrxName())
.setParameters(new Object[] {getAD_PInstance_ID()})
.setOrderBy("AD_Org_ID")
.list();
// FR: [ 2214883 ] Remove SQL code and Replace for Query
if (list.size() == 0) return " - No Records found";
//
MAcctSchema as = MAcctSchema.get(getCtx(), p_C_AcctSchema_ID);
MAcctSchemaDefault asDefaultAccts = MAcctSchemaDefault.get(getCtx(), p_C_AcctSchema_ID);
MGLCategory cat = MGLCategory.getDefaultSystem(getCtx());
if (cat == null) {
MDocType docType = MDocType.get(getCtx(), p_C_DocTypeReval_ID);
cat = MGLCategory.get(getCtx(), docType.getGL_Category_ID());
}
//
MJournalBatch batch = new MJournalBatch(getCtx(), 0, get_TrxName());
batch.setDescription(getName());
batch.setC_DocType_ID(p_C_DocTypeReval_ID);
batch.setDateDoc(new Timestamp(System.currentTimeMillis()));
batch.setDateAcct(p_DateReval);
batch.setC_Currency_ID(as.getC_Currency_ID());
if (!batch.save()) return " - Could not create Batch";
//
MJournal journal = null;
BigDecimal drTotal = Env.ZERO;
BigDecimal crTotal = Env.ZERO;
int AD_Org_ID = 0;
for (int i = 0; i < list.size(); i++) {
X_T_InvoiceGL gl = list.get(i);
if (gl.getAmtRevalDrDiff().signum() == 0 && gl.getAmtRevalCrDiff().signum() == 0) continue;
MInvoice invoice = new MInvoice(getCtx(), gl.getC_Invoice_ID(), null);
if (invoice.getC_Currency_ID() == as.getC_Currency_ID()) continue;
//
if (journal == null) {
journal = new MJournal(batch);
journal.setC_AcctSchema_ID(as.getC_AcctSchema_ID());
journal.setC_Currency_ID(as.getC_Currency_ID());
journal.setC_ConversionType_ID(p_C_ConversionTypeReval_ID);
MOrg org = MOrg.get(getCtx(), gl.getAD_Org_ID());
journal.setDescription(getName() + " - " + org.getName());
journal.setGL_Category_ID(cat.getGL_Category_ID());
if (!journal.save()) return " - Could not create Journal";
}
//
MJournalLine line = new MJournalLine(journal);
line.setLine((i + 1) * 10);
line.setDescription(invoice.getSummary());
//
MFactAcct fa = new MFactAcct(getCtx(), gl.getFact_Acct_ID(), null);
// TODO: C_ValidCombination_ID is no longer a column because we have DR/CR accounts
// line.setC_ValidCombination(MAccount.get(fa));
BigDecimal dr = gl.getAmtRevalDrDiff();
BigDecimal cr = gl.getAmtRevalCrDiff();
drTotal = drTotal.add(dr);
crTotal = crTotal.add(cr);
line.setAmtSourceDr(dr);
line.setAmtAcctDr(dr);
line.setAmtSourceCr(cr);
line.setAmtAcctCr(cr);
line.save();
//
if (AD_Org_ID == 0) // invoice org id
AD_Org_ID = gl.getAD_Org_ID();
// Change in Org
if (AD_Org_ID != gl.getAD_Org_ID()) {
createBalancing(asDefaultAccts, journal, drTotal, crTotal, AD_Org_ID, (i + 1) * 10);
//
AD_Org_ID = gl.getAD_Org_ID();
drTotal = Env.ZERO;
crTotal = Env.ZERO;
journal = null;
}
}
createBalancing(asDefaultAccts, journal, drTotal, crTotal, AD_Org_ID, (list.size() + 1) * 10);
return " - " + batch.getDocumentNo() + " #" + list.size();
} // createGLJournal
/**
* Perform process.
*
* @return Message (clear text)
* @throws Exception if not successful
*/
protected String doIt() throws java.lang.Exception {
StringBuffer sql =
new StringBuffer(
"SELECT * "
+ "FROM S_TimeExpense e "
+ "WHERE e.Processed='Y'"
+ " AND e.AD_Client_ID=?"); // #1
if (m_C_BPartner_ID != 0) sql.append(" AND e.C_BPartner_ID=?"); // #2
if (m_DateFrom != null) sql.append(" AND e.DateReport >= ?"); // #3
if (m_DateTo != null) sql.append(" AND e.DateReport <= ?"); // #4
sql.append(
" AND EXISTS (SELECT * FROM S_TimeExpenseLine el "
+ "WHERE e.S_TimeExpense_ID=el.S_TimeExpense_ID"
+ " AND el.C_InvoiceLine_ID IS NULL"
+ " AND el.ConvertedAmt<>0) "
+ "ORDER BY e.C_BPartner_ID, e.S_TimeExpense_ID");
//
int old_BPartner_ID = -1;
MInvoice invoice = null;
//
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
int par = 1;
pstmt.setInt(par++, getAD_Client_ID());
if (m_C_BPartner_ID != 0) pstmt.setInt(par++, m_C_BPartner_ID);
if (m_DateFrom != null) pstmt.setTimestamp(par++, m_DateFrom);
if (m_DateTo != null) pstmt.setTimestamp(par++, m_DateTo);
rs = pstmt.executeQuery();
while (rs.next()) // ********* Expense Line Loop
{
MTimeExpense te = new MTimeExpense(getCtx(), rs, get_TrxName());
// New BPartner - New Order
if (te.getC_BPartner_ID() != old_BPartner_ID) {
completeInvoice(invoice);
MBPartner bp = new MBPartner(getCtx(), te.getC_BPartner_ID(), get_TrxName());
//
log.info("New Invoice for " + bp);
invoice = new MInvoice(getCtx(), 0, null);
invoice.setClientOrg(te.getAD_Client_ID(), te.getAD_Org_ID());
invoice.setC_DocTypeTarget_ID(MDocType.DOCBASETYPE_APInvoice); // API
invoice.setDocumentNo(te.getDocumentNo());
//
invoice.setBPartner(bp);
if (invoice.getC_BPartner_Location_ID() == 0) {
log.log(Level.SEVERE, "No BP Location: " + bp);
addLog(
0,
te.getDateReport(),
null,
"No Location: " + te.getDocumentNo() + " " + bp.getName());
invoice = null;
break;
}
invoice.setM_PriceList_ID(te.getM_PriceList_ID());
invoice.setSalesRep_ID(te.getDoc_User_ID());
String descr =
Msg.translate(getCtx(), "S_TimeExpense_ID")
+ ": "
+ te.getDocumentNo()
+ " "
+ DisplayType.getDateFormat(DisplayType.Date).format(te.getDateReport());
invoice.setDescription(descr);
if (!invoice.save()) new IllegalStateException("Cannot save Invoice");
old_BPartner_ID = bp.getC_BPartner_ID();
}
MTimeExpenseLine[] tel = te.getLines(false);
for (int i = 0; i < tel.length; i++) {
MTimeExpenseLine line = tel[i];
// Already Invoiced or nothing to be reimbursed
if (line.getC_InvoiceLine_ID() != 0
|| Env.ZERO.compareTo(line.getQtyReimbursed()) == 0
|| Env.ZERO.compareTo(line.getPriceReimbursed()) == 0) continue;
// Update Header info
if (line.getC_Activity_ID() != 0 && line.getC_Activity_ID() != invoice.getC_Activity_ID())
invoice.setC_Activity_ID(line.getC_Activity_ID());
if (line.getC_Campaign_ID() != 0 && line.getC_Campaign_ID() != invoice.getC_Campaign_ID())
invoice.setC_Campaign_ID(line.getC_Campaign_ID());
if (line.getC_Project_ID() != 0 && line.getC_Project_ID() != invoice.getC_Project_ID())
invoice.setC_Project_ID(line.getC_Project_ID());
if (!invoice.save()) new IllegalStateException("Cannot save Invoice");
// Create OrderLine
MInvoiceLine il = new MInvoiceLine(invoice);
//
if (line.getM_Product_ID() != 0) il.setM_Product_ID(line.getM_Product_ID(), true);
il.setQty(line.getQtyReimbursed()); // Entered/Invoiced
il.setDescription(line.getDescription());
//
il.setC_Project_ID(line.getC_Project_ID());
il.setC_ProjectPhase_ID(line.getC_ProjectPhase_ID());
il.setC_ProjectTask_ID(line.getC_ProjectTask_ID());
il.setC_Activity_ID(line.getC_Activity_ID());
il.setC_Campaign_ID(line.getC_Campaign_ID());
//
// il.setPrice(); // not really a list/limit price for reimbursements
il.setPrice(line.getPriceReimbursed()); //
il.setTax();
if (!il.save()) new IllegalStateException("Cannot save Invoice Line");
// Update TEL
line.setC_InvoiceLine_ID(il.getC_InvoiceLine_ID());
line.saveEx();
} // for all expense lines
} // ********* Expense Line Loop
} catch (Exception e) {
log.log(Level.SEVERE, sql.toString(), e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
completeInvoice(invoice);
return "@Created@=" + m_noInvoices;
} // doIt
/**
* Create actual Payment
*
* @param C_Invoice_ID invoice
* @param C_BPartner_ID partner ignored when invoice exists
* @param C_Currency_ID currency
* @param StmtAmt statement amount
* @param TrxAmt transaction amt
* @param C_BP_BankAccount_ID bank account
* @param DateTrx transaction date
* @param DateAcct accounting date
* @param Description description
* @param AD_Org_ID org
* @return payment
*/
private MPayment createPayment(
int C_Invoice_ID,
int C_BPartner_ID,
int C_Currency_ID,
BigDecimal StmtAmt,
BigDecimal TrxAmt,
int C_BP_BankAccount_ID,
Timestamp DateTrx,
Timestamp DateAcct,
String Description,
int AD_Org_ID) {
// Trx Amount = Payment overwrites Statement Amount if defined
BigDecimal PayAmt = TrxAmt;
if (PayAmt == null || Env.ZERO.compareTo(PayAmt) == 0) PayAmt = StmtAmt;
if (C_Invoice_ID == 0 && (PayAmt == null || Env.ZERO.compareTo(PayAmt) == 0))
throw new IllegalStateException("@PayAmt@ = 0");
if (PayAmt == null) PayAmt = Env.ZERO;
//
MPayment payment = new MPayment(getCtx(), 0, get_TrxName());
payment.setAD_Org_ID(AD_Org_ID);
payment.setC_BP_BankAccount_ID(C_BP_BankAccount_ID);
payment.setTenderType(MPayment.TENDERTYPE_Check);
if (DateTrx != null) payment.setDateTrx(DateTrx);
else if (DateAcct != null) payment.setDateTrx(DateAcct);
if (DateAcct != null) payment.setDateAcct(DateAcct);
else payment.setDateAcct(payment.getDateTrx());
payment.setDescription(Description);
//
if (C_Invoice_ID != 0) {
MInvoice invoice = new MInvoice(getCtx(), C_Invoice_ID, null);
payment.setC_DocType_ID(invoice.isSOTrx()); // Receipt
payment.setC_Invoice_ID(invoice.getC_Invoice_ID());
payment.setC_BPartner_ID(invoice.getC_BPartner_ID());
if (PayAmt.signum() != 0) // explicit Amount
{
payment.setC_Currency_ID(C_Currency_ID);
if (invoice.isSOTrx()) payment.setPayAmt(PayAmt);
else // payment is likely to be negative
payment.setPayAmt(PayAmt.negate());
payment.setOverUnderAmt(invoice.getGrandTotal(true).subtract(payment.getPayAmt()));
} else // set Pay Amout from Invoice
{
payment.setC_Currency_ID(invoice.getC_Currency_ID());
payment.setPayAmt(invoice.getGrandTotal(true));
}
} else if (C_BPartner_ID != 0) {
payment.setC_BPartner_ID(C_BPartner_ID);
payment.setC_Currency_ID(C_Currency_ID);
if (PayAmt.signum() < 0) // Payment
{
payment.setPayAmt(PayAmt.abs());
payment.setC_DocType_ID(false);
} else // Receipt
{
payment.setPayAmt(PayAmt);
payment.setC_DocType_ID(true);
}
} else return null;
payment.save();
//
payment.processIt(MPayment.DOCACTION_Complete);
payment.save();
return payment;
} // createPayment
/**
* Stream invoice
*
* @param request request
* @param response response
* @return "" or error message
*/
private String streamInvoice(HttpServletRequest request, HttpServletResponse response) {
int MIN_SIZE = 2000; // if not created size is 1015
// Get Invoice ID
int C_Invoice_ID = WebUtil.getParameterAsInt(request, "Invoice_ID");
if (C_Invoice_ID == 0) {
log.fine("No ID)");
return "No Invoice ID";
}
// Get Invoice
Properties ctx = JSPEnv.getCtx(request);
MInvoice invoice = new MInvoice(ctx, C_Invoice_ID, null);
if (invoice.getC_Invoice_ID() != C_Invoice_ID) {
if (log.isLoggable(Level.FINE)) log.fine("Invoice not found - ID=" + C_Invoice_ID);
return "Invoice not found";
}
// Get WebUser & Compare with invoice
HttpSession session = request.getSession(true);
WebUser wu = (WebUser) session.getAttribute(WebUser.NAME);
if (wu.getC_BPartner_ID() != invoice.getC_BPartner_ID()) {
log.warning(
"Invoice from BPartner - C_Invoice_ID="
+ C_Invoice_ID
+ " - BP_Invoice="
+ invoice.getC_BPartner_ID()
+ " = BP_Web="
+ wu.getC_BPartner_ID());
return "Your invoice not found";
}
// Check Directory
String dirName = ctx.getProperty("documentDir", ".");
try {
File dir = new File(dirName);
if (!dir.exists()) dir.mkdir();
} catch (Exception ex) {
log.log(Level.SEVERE, "Could not create directory " + dirName, ex);
return "Streaming error - directory";
}
// Check if Invoice already created
String fileName = invoice.getPDFFileName(dirName);
File file = new File(fileName);
if (file.exists() && file.isFile() && file.length() > MIN_SIZE) {
if (log.isLoggable(Level.INFO))
log.info("Existing: " + file + " - " + new Timestamp(file.lastModified()));
} else {
if (log.isLoggable(Level.INFO)) log.info("New: " + fileName);
file = invoice.createPDF(file);
if (file != null) {
invoice.setDatePrinted(new Timestamp(System.currentTimeMillis()));
invoice.saveEx();
}
}
// Issue Error
if (file == null || !file.exists() || file.length() < MIN_SIZE) {
log.warning("File does not exist - " + file);
return "Streaming error - file";
}
// Send PDF
try {
int bufferSize = 2048; // 2k Buffer
int fileLength = (int) file.length();
//
response.setContentType("application/pdf");
response.setBufferSize(bufferSize);
response.setContentLength(fileLength);
//
if (log.isLoggable(Level.FINE)) log.fine(file.getAbsolutePath() + ", length=" + fileLength);
long time = System.currentTimeMillis(); // timer start
//
FileInputStream in = new FileInputStream(file);
ServletOutputStream out = response.getOutputStream();
byte[] buffer = new byte[bufferSize];
double totalSize = 0;
int count = 0;
do {
count = in.read(buffer, 0, bufferSize);
if (count > 0) {
totalSize += count;
out.write(buffer, 0, count);
}
} while (count != -1);
out.flush();
out.close();
//
in.close();
time = System.currentTimeMillis() - time;
double speed = (totalSize / 1024) / ((double) time / 1000);
if (log.isLoggable(Level.FINE))
log.fine("Length=" + totalSize + " - " + time + " ms - " + speed + " kB/sec");
} catch (IOException ex) {
log.log(Level.SEVERE, ex.toString());
return "Streaming error";
}
return null;
} // streamInvoice
