Examples with MCommissionLine org.compiere.model.MCommissionLine used on opensource projects

Example 1 with MCommissionLine

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

the class CommissionCalc method doIt.

//	prepare
/**
	 *  Perform process.
	 *  @return Message (text with variables)
	 *  @throws Exception if not successful
	 */
protected String doIt() throws Exception {
    log.info("C_Commission_ID=" + getRecord_ID() + ", StartDate=" + p_StartDate);
    if (p_StartDate == null)
        p_StartDate = new Timestamp(System.currentTimeMillis());
    m_com = new MCommission(getCtx(), getRecord_ID(), get_TrxName());
    if (m_com.get_ID() == 0)
        throw new AdempiereUserError("No Commission");
    //	Create Commission	
    MCommissionRun comRun = new MCommissionRun(m_com);
    setStartEndDate();
    comRun.setStartDate(p_StartDate);
    //	01-Jan-2000 - 31-Jan-2001 - USD
    SimpleDateFormat format = DisplayType.getDateFormat(DisplayType.Date);
    String description = format.format(p_StartDate) + " - " + format.format(m_EndDate) + " - " + MCurrency.getISO_Code(getCtx(), m_com.getC_Currency_ID());
    comRun.setDescription(description);
    if (!comRun.save())
        throw new AdempiereSystemError("Could not save Commission Run");
    MCommissionLine[] lines = m_com.getLines();
    for (int i = 0; i < lines.length; i++) {
        //	Amt for Line - Updated By Trigger
        MCommissionAmt comAmt = new MCommissionAmt(comRun, lines[i].getC_CommissionLine_ID());
        if (!comAmt.save())
            throw new AdempiereSystemError("Could not save Commission Amt");
        //
        StringBuffer sql = new StringBuffer();
        if (MCommission.DOCBASISTYPE_Receipt.equals(m_com.getDocBasisType())) {
            if (m_com.isListDetails()) {
                sql.append("SELECT h.C_Currency_ID, (l.LineNetAmt*al.Amount/h.GrandTotal) AS Amt," + " (l.QtyInvoiced*al.Amount/h.GrandTotal) AS Qty," + " NULL, l.C_InvoiceLine_ID, p.DocumentNo||'_'||h.DocumentNo," + " COALESCE(prd.Value,l.Description), h.DateInvoiced " + "FROM C_Payment p" + " INNER JOIN C_AllocationLine al ON (p.C_Payment_ID=al.C_Payment_ID)" + " INNER JOIN C_Invoice h ON (al.C_Invoice_ID = h.C_Invoice_ID)" + " INNER JOIN C_InvoiceLine l ON (h.C_Invoice_ID = l.C_Invoice_ID) " + " LEFT OUTER JOIN M_Product prd ON (l.M_Product_ID = prd.M_Product_ID) " + "WHERE p.DocStatus IN ('CL','CO','RE')" + " AND h.IsSOTrx='Y'" + " AND p.AD_Client_ID = ?" + " AND p.DateTrx BETWEEN ? AND ?");
            } else {
                sql.append("SELECT h.C_Currency_ID, SUM(l.LineNetAmt*al.Amount/h.GrandTotal) AS Amt," + " SUM(l.QtyInvoiced*al.Amount/h.GrandTotal) AS Qty," + " NULL, NULL, NULL, NULL, MAX(h.DateInvoiced) " + "FROM C_Payment p" + " INNER JOIN C_AllocationLine al ON (p.C_Payment_ID=al.C_Payment_ID)" + " INNER JOIN C_Invoice h ON (al.C_Invoice_ID = h.C_Invoice_ID)" + " INNER JOIN C_InvoiceLine l ON (h.C_Invoice_ID = l.C_Invoice_ID) " + "WHERE p.DocStatus IN ('CL','CO','RE')" + " AND h.IsSOTrx='Y'" + " AND p.AD_Client_ID = ?" + " AND p.DateTrx BETWEEN ? AND ?");
            }
        } else if (MCommission.DOCBASISTYPE_Order.equals(m_com.getDocBasisType())) {
            if (m_com.isListDetails()) {
                sql.append("SELECT h.C_Currency_ID, l.LineNetAmt, l.QtyOrdered, " + "l.C_OrderLine_ID, NULL, h.DocumentNo," + " COALESCE(prd.Value,l.Description),h.DateOrdered " + "FROM C_Order h" + " INNER JOIN C_OrderLine l ON (h.C_Order_ID = l.C_Order_ID)" + " LEFT OUTER JOIN M_Product prd ON (l.M_Product_ID = prd.M_Product_ID) " + "WHERE h.DocStatus IN ('CL','CO')" + " AND h.IsSOTrx='Y'" + " AND h.AD_Client_ID = ?" + " AND h.DateOrdered BETWEEN ? AND ?");
            } else {
                sql.append("SELECT h.C_Currency_ID, SUM(l.LineNetAmt) AS Amt," + " SUM(l.QtyOrdered) AS Qty, " + "NULL, NULL, NULL, NULL, MAX(h.DateOrdered) " + "FROM C_Order h" + " INNER JOIN C_OrderLine l ON (h.C_Order_ID = l.C_Order_ID) " + "WHERE h.DocStatus IN ('CL','CO')" + " AND h.IsSOTrx='Y'" + " AND h.AD_Client_ID = ?" + " AND h.DateOrdered BETWEEN ? AND ?");
            }
        } else //	Invoice Basis
        {
            if (m_com.isListDetails()) {
                sql.append("SELECT h.C_Currency_ID, l.LineNetAmt, l.QtyInvoiced, " + "NULL, l.C_InvoiceLine_ID, h.DocumentNo," + " COALESCE(prd.Value,l.Description),h.DateInvoiced " + "FROM C_Invoice h" + " INNER JOIN C_InvoiceLine l ON (h.C_Invoice_ID = l.C_Invoice_ID)" + " LEFT OUTER JOIN M_Product prd ON (l.M_Product_ID = prd.M_Product_ID) " + "WHERE h.DocStatus IN ('CL','CO','RE')" + " AND h.IsSOTrx='Y'" + " AND h.AD_Client_ID = ?" + " AND h.DateInvoiced BETWEEN ? AND ?");
            } else {
                sql.append("SELECT h.C_Currency_ID, SUM(l.LineNetAmt) AS Amt," + " SUM(l.QtyInvoiced) AS Qty, " + "NULL, NULL, NULL, NULL, MAX(h.DateInvoiced) " + "FROM C_Invoice h" + " INNER JOIN C_InvoiceLine l ON (h.C_Invoice_ID = l.C_Invoice_ID) " + "WHERE h.DocStatus IN ('CL','CO','RE')" + " AND h.IsSOTrx='Y'" + " AND h.AD_Client_ID = ?" + " AND h.DateInvoiced BETWEEN ? AND ?");
            }
        }
        //	CommissionOrders/Invoices
        if (lines[i].isCommissionOrders()) {
            MUser[] users = MUser.getOfBPartner(getCtx(), m_com.getC_BPartner_ID(), get_TrxName());
            if (users == null || users.length == 0)
                throw new AdempiereUserError("Commission Business Partner has no Users/Contact");
            if (users.length == 1) {
                int SalesRep_ID = users[0].getAD_User_ID();
                sql.append(" AND h.SalesRep_ID=").append(SalesRep_ID);
            } else {
                log.warning("Not 1 User/Contact for C_BPartner_ID=" + m_com.getC_BPartner_ID() + " but " + users.length);
                sql.append(" AND h.SalesRep_ID IN (SELECT AD_User_ID FROM AD_User WHERE C_BPartner_ID=").append(m_com.getC_BPartner_ID()).append(")");
            }
        }
        //	Organization
        if (lines[i].getOrg_ID() != 0)
            sql.append(" AND h.AD_Org_ID=").append(lines[i].getOrg_ID());
        //	BPartner
        if (lines[i].getC_BPartner_ID() != 0)
            sql.append(" AND h.C_BPartner_ID=").append(lines[i].getC_BPartner_ID());
        //	BPartner Group
        if (lines[i].getC_BP_Group_ID() != 0)
            sql.append(" AND h.C_BPartner_ID IN " + "(SELECT C_BPartner_ID FROM C_BPartner WHERE C_BP_Group_ID=").append(lines[i].getC_BP_Group_ID()).append(")");
        //	Sales Region
        if (lines[i].getC_SalesRegion_ID() != 0)
            sql.append(" AND h.C_BPartner_Location_ID IN " + "(SELECT C_BPartner_Location_ID FROM C_BPartner_Location WHERE C_SalesRegion_ID=").append(lines[i].getC_SalesRegion_ID()).append(")");
        //	Product
        if (lines[i].getM_Product_ID() != 0)
            sql.append(" AND l.M_Product_ID=").append(lines[i].getM_Product_ID());
        //	Product Category
        if (lines[i].getM_Product_Category_ID() != 0)
            sql.append(" AND l.M_Product_ID IN " + "(SELECT M_Product_ID FROM M_Product WHERE M_Product_Category_ID=").append(lines[i].getM_Product_Category_ID()).append(")");
        //	Payment Rule
        if (lines[i].getPaymentRule() != null)
            sql.append(" AND h.PaymentRule='").append(lines[i].getPaymentRule()).append("'");
        //	Grouping
        if (!m_com.isListDetails())
            sql.append(" GROUP BY h.C_Currency_ID");
        //
        log.fine("Line=" + lines[i].getLine() + " - " + sql);
        //
        createDetail(sql.toString(), comAmt);
        comAmt.calculateCommission();
        comAmt.saveEx();
    }
    //	for all commission lines
    //	comRun.updateFromAmt();
    //	comRun.saveEx();
    //	Save Last Run
    m_com.setDateLastRun(p_StartDate);
    m_com.saveEx();
    return "@C_CommissionRun_ID@ = " + comRun.getDocumentNo() + " - " + comRun.getDescription();
}