Exemple #1
0
 /**
  * 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;
 }
Exemple #2
0
 /**
  * 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
Exemple #5
0
  /**
   * 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
Exemple #8
0
 /**
  * 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