Search in sources :

Example 1 with MAging

use of org.compiere.model.MAging in project adempiere by adempiere.

the class Aging method doIt.

//	prepare
/**
	 * 	DoIt
	 *	@return Message
	 *	@throws Exception
	 */
protected String doIt() throws Exception {
    log.info("StatementDate=" + p_StatementDate + ", IsSOTrx=" + p_IsSOTrx + ", C_Currency_ID=" + p_C_Currency_ID + ", AD_Org_ID=" + p_AD_Org_ID + ", C_BP_Group_ID=" + p_C_BP_Group_ID + ", C_BPartner_ID=" + p_C_BPartner_ID + ", IsListInvoices=" + p_IsListInvoices);
    //FR 1933937
    String dateacct = DB.TO_DATE(p_StatementDate);
    StringBuffer sql = new StringBuffer();
    sql.append(// 1..4 
    "SELECT bp.C_BP_Group_ID, oi.C_BPartner_ID,oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID, " + //	5..6
    "oi.C_Currency_ID, oi.IsSOTrx, " + //	7..10
    "oi.DateInvoiced, oi.NetDays,oi.DueDate,oi.DaysDue, ");
    if (p_C_Currency_ID == 0) {
        if (//FR 1933937
        !p_DateAcct) {
            //	11..13
            sql.append(" oi.GrandTotal, oi.PaidAmt, oi.OpenAmt ");
        } else {
            //	11..13
            sql.append(" oi.GrandTotal, invoicePaidToDate(oi.C_Invoice_ID, oi.C_Currency_ID, 1," + dateacct + ") AS PaidAmt, invoiceOpenToDate(oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID," + dateacct + ") AS OpenAmt ");
        }
    } else {
        String s = ",oi.C_Currency_ID," + p_C_Currency_ID + ",oi.DateAcct,oi.C_ConversionType_ID,oi.AD_Client_ID,oi.AD_Org_ID)";
        //	11
        sql.append("currencyConvert(oi.GrandTotal").append(s);
        if (!p_DateAcct) {
            // 12
            sql.append(", currencyConvert(oi.PaidAmt").append(s).append(", currencyConvert(oi.OpenAmt").append(// 13
            s);
        } else {
            // 12
            sql.append(", currencyConvert(invoicePaidToDate(oi.C_Invoice_ID, oi.C_Currency_ID, 1," + dateacct + ")").append(s).append(", currencyConvert(invoiceOpenToDate(oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID," + dateacct + ")").append(// 13
            s);
        }
    }
    //	14..17
    sql.append(",oi.C_Activity_ID,oi.C_Campaign_ID,oi.C_Project_ID,oi.AD_Org_ID ");
    if (//FR 1933937
    !p_DateAcct) {
        sql.append(" FROM RV_OpenItem oi");
    } else {
        sql.append(" FROM RV_OpenItemToDate oi");
    }
    sql.append(" INNER JOIN C_BPartner bp ON (oi.C_BPartner_ID=bp.C_BPartner_ID) " + "WHERE oi.ISSoTrx=").append(p_IsSOTrx ? "'Y'" : "'N'");
    if (p_C_BPartner_ID > 0) {
        sql.append(" AND oi.C_BPartner_ID=").append(p_C_BPartner_ID);
    } else if (p_C_BP_Group_ID > 0) {
        sql.append(" AND bp.C_BP_Group_ID=").append(p_C_BP_Group_ID);
    }
    if (// BF 2655587
    p_AD_Org_ID > 0) {
        sql.append(" AND oi.AD_Org_ID=").append(p_AD_Org_ID);
    }
    if (//FR 1933937
    p_DateAcct) {
        sql.append(" AND invoiceOpenToDate(oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID," + dateacct + ") <> 0 ");
    }
    sql.append(" ORDER BY oi.C_BPartner_ID, oi.C_Currency_ID, oi.C_Invoice_ID");
    log.finest(sql.toString());
    String finalSql = MRole.getDefault(getCtx(), false).addAccessSQL(sql.toString(), "oi", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);
    log.finer(finalSql);
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    //
    MAging aging = null;
    int counter = 0;
    int rows = 0;
    int AD_PInstance_ID = getAD_PInstance_ID();
    //
    try {
        pstmt = DB.prepareStatement(finalSql, get_TrxName());
        rs = pstmt.executeQuery();
        while (rs.next()) {
            int C_BP_Group_ID = rs.getInt(1);
            int C_BPartner_ID = rs.getInt(2);
            int C_Invoice_ID = p_IsListInvoices ? rs.getInt(3) : 0;
            int C_InvoicePaySchedule_ID = p_IsListInvoices ? rs.getInt(4) : 0;
            int C_Currency_ID = rs.getInt(5);
            boolean IsSOTrx = "Y".equals(rs.getString(6));
            //
            Timestamp DateInvoiced = rs.getTimestamp(7);
            int NetDays = rs.getInt(8);
            Timestamp DueDate = rs.getTimestamp(9);
            //	Days Due
            int DaysDue = //	based on today
            rs.getInt(10) + m_statementOffset;
            //
            BigDecimal GrandTotal = rs.getBigDecimal(11);
            BigDecimal PaidAmt = rs.getBigDecimal(12);
            BigDecimal OpenAmt = rs.getBigDecimal(13);
            //
            int C_Activity_ID = p_IsListInvoices ? rs.getInt(14) : 0;
            int C_Campaign_ID = p_IsListInvoices ? rs.getInt(15) : 0;
            int C_Project_ID = p_IsListInvoices ? rs.getInt(16) : 0;
            int AD_Org_ID = rs.getInt(17);
            rows++;
            //	New Aging Row
            if (//	Key
            aging == null || AD_PInstance_ID != aging.getAD_PInstance_ID() || C_BPartner_ID != aging.getC_BPartner_ID() || C_Currency_ID != aging.getC_Currency_ID() || C_Invoice_ID != aging.getC_Invoice_ID() || C_InvoicePaySchedule_ID != aging.getC_InvoicePaySchedule_ID()) {
                if (aging != null) {
                    aging.saveEx();
                    log.fine("#" + ++counter + " - " + aging);
                }
                aging = new MAging(getCtx(), AD_PInstance_ID, p_StatementDate, C_BPartner_ID, C_Currency_ID, C_Invoice_ID, C_InvoicePaySchedule_ID, C_BP_Group_ID, AD_Org_ID, DueDate, IsSOTrx, get_TrxName());
                aging.setC_Activity_ID(C_Activity_ID);
                aging.setC_Campaign_ID(C_Campaign_ID);
                aging.setC_Project_ID(C_Project_ID);
                aging.set_ValueOfColumn("C_BankAccount_ID", 0);
                aging.set_ValueOfColumn("C_CashFlow_ID", 0);
                aging.setDateAcct(p_DateAcct);
            }
            //	Fill Buckets
            aging.add(DueDate, DaysDue, GrandTotal, OpenAmt);
        }
        if (aging != null) {
            aging.saveEx();
            counter++;
            log.fine("#" + counter + " - " + aging);
        }
    } catch (SQLException e) {
        throw new DBException(e, finalSql);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    //	
    log.info("#" + counter + " - rows=" + rows);
    return "";
}
Also used : DBException(org.adempiere.exceptions.DBException) SQLException(java.sql.SQLException) MAging(org.compiere.model.MAging) ResultSet(java.sql.ResultSet) PreparedStatement(java.sql.PreparedStatement) Timestamp(java.sql.Timestamp) BigDecimal(java.math.BigDecimal)

Example 2 with MAging

use of org.compiere.model.MAging in project adempiere by adempiere.

the class CashFlow method createAgingRecord.

protected MAging createAgingRecord(Timestamp StatementDate, int C_BankAccount_ID, int C_BPartner_ID, int C_Currency_ID, String cashFlowSource, int record_id, int C_InvoicePaySchedule_ID, int C_BP_Group_ID, int AD_Org_ID, Timestamp DueDate, boolean IsSOTrx) {
    MAging aging = new MAging(getCtx(), getAD_PInstance_ID(), StatementDate, C_BankAccount_ID, C_BPartner_ID, C_Currency_ID, 0, C_InvoicePaySchedule_ID, C_BP_Group_ID, AD_Org_ID, DueDate, IsSOTrx, get_TrxName());
    aging.setIsListInvoices(false);
    setRecord_ID(aging, cashFlowSource, record_id);
    return aging;
}
Also used : MAging(org.compiere.model.MAging)

Aggregations

MAging (org.compiere.model.MAging)2 BigDecimal (java.math.BigDecimal)1 PreparedStatement (java.sql.PreparedStatement)1 ResultSet (java.sql.ResultSet)1 SQLException (java.sql.SQLException)1 Timestamp (java.sql.Timestamp)1 DBException (org.adempiere.exceptions.DBException)1