/// <summary>
/// Perform Process
/// </summary>
/// <returns>Message (text with variables)</returns>
protected override String DoIt()
{
log.Info("C_Commission_ID=" + GetRecord_ID() + ", StartDate=" + p_StartDate);
if (p_StartDate == null)
{
p_StartDate = DateTime.Now;
}
m_com = new MCommission(GetCtx(), GetRecord_ID(), Get_Trx());
if (m_com.Get_ID() == 0)
{
throw new Exception("No Commission");
}
// Create Commission
MCommissionRun comRun = new MCommissionRun(m_com);
SetStartEndDate();
comRun.SetStartDate(p_StartDate);
System.Threading.Thread.CurrentThread.CurrentCulture = Utility.Env.GetLanguage(GetCtx()).GetCulture(Utility.Env.GetLoginLanguage(GetCtx()).GetAD_Language());
System.Threading.Thread.CurrentThread.CurrentUICulture = Utility.Env.GetLanguage(GetCtx()).GetCulture(Utility.Env.GetLoginLanguage(GetCtx()).GetAD_Language());
// 01-Jan-2000 - 31-Jan-2001 - USD
Classes.SimpleDateFormat format = Classes.DisplayType.GetDateFormat(Classes.DisplayType.Date);
String description = format.Format(p_StartDate)
+ " - " + format.Format(m_EndDate)
+ " - " + MCurrency.GetISO_Code(GetCtx(), m_com.GetC_Currency_ID());
//string description = p_StartDate
// + " - " + m_EndDate
// + " - " + MCurrency.GetISO_Code(GetCtx(), m_com.GetC_Currency_ID());
comRun.SetDescription(description);
System.Threading.Thread.CurrentThread.CurrentCulture = Utility.Env.GetLanguage(GetCtx()).GetCulture(Utility.Env.GetBaseAD_Language());
System.Threading.Thread.CurrentThread.CurrentUICulture = Utility.Env.GetLanguage(GetCtx()).GetCulture(Utility.Env.GetBaseAD_Language());
if (!comRun.Save())
{
throw new Exception("Could not save Commission Run please check Organization");
}
MCommissionLine[] lines = m_com.GetLines();
for (int i = 0; i < lines.Length; i++)
{
#region
#endregion
// Amt for Line - Updated By Trigger
MCommissionAmt comAmt = new MCommissionAmt(comRun, lines[i].GetC_CommissionLine_ID());
if (!comAmt.Save())
{
throw new SystemException("Could not save Commission Amt");
}
//
StringBuilder sql = new StringBuilder();
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 = @clientid"
+ " AND l.IsCommissionCalculated = 'N' "
+ " AND p.DateTrx BETWEEN @sdate AND @edate");
}
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 = @clientid"
+ " AND l.IsCommissionCalculated = 'N' "
+ " AND p.DateTrx BETWEEN @sdate AND @edate");
}
}
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 = @clientid"
+ " AND l.IsCommissionCalculated = 'N' "
+ " AND h.DateOrdered BETWEEN @sdate AND @edate");
}
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 l.IsCommissionCalculated = 'N' "
+ " AND h.AD_Client_ID = @clientid"
+ " AND h.DateOrdered BETWEEN @sdate AND @edate");
}
}
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 l.IsCommissionCalculated = 'N' "
+ " AND h.AD_Client_ID = @clientid"
+ " AND h.DateInvoiced BETWEEN @sdate AND @edate");
}
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 l.IsCommissionCalculated = 'N' "
+ " AND h.AD_Client_ID = @clientid"
+ " AND h.DateInvoiced BETWEEN @sdate AND @edate");
}
}
// CommissionOrders/Invoices
if (lines[i].IsCommissionOrders())
{
MUser[] users = MUser.GetOfBPartner(GetCtx(), m_com.GetC_BPartner_ID());
if (users == null || users.Length == 0)
{
throw new Exception("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(")");
}
}
//added by Arpit Rai on 7-May-2016 asked & Tested by Ravikant Sir
//To calculate Commission Amount For the Particular Agent If Not Selected in Line Tab Of Commission
else
{
MUser[] users = MUser.GetOfBPartner(GetCtx(), m_com.GetC_BPartner_ID());
if (users == null || users.Length == 0)
{
throw new Exception("Commission Business Partner has no Users/Contact");
}
if (users.Length == 1)
{
int SaleRepID = users[0].GetAD_User_ID();
sql.Append(" AND h.SalesRep_ID = ");
sql.Append(SaleRepID);
}
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(")");
}
// 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.CalculatecommissionwithNewLogic();
comAmt.Save();
int countDetails = Util.GetValueOfInt(DataBase.DB.ExecuteScalar("SELECT COUNT(*) FROM C_CommissionDetail WHERE C_CommissionAmt_ID=" + comAmt.GetC_CommissionAmt_ID(), null, Get_TrxName()));
if (countDetails == 0)
{
comAmt.Delete(true, Get_Trx());
}
} // for all commission lines
// comRun.updateFromAmt();
// comRun.save();
// Save Last Run
m_com.SetDateLastRun(p_StartDate);
m_com.Save();
return("@C_CommissionRun_ID@ = " + comRun.GetDocumentNo()
+ " - " + comRun.GetDescription());
}
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message (variables are parsed)</returns>
protected override String DoIt()
{
log.Info("doIt - C_CommissionRun_ID=" + GetRecord_ID());
// Load Data
MCommissionRun comRun = new MCommissionRun(GetCtx(), GetRecord_ID(), Get_Trx());
if (comRun.Get_ID() == 0)
{
throw new ArgumentException("CommissionAPInvoice - No Commission Run");
}
if (Env.ZERO.CompareTo(comRun.GetGrandTotal()) == 0)
{
throw new ArgumentException("@GrandTotal@ = 0");
}
MCommission com = new MCommission(GetCtx(), comRun.GetC_Commission_ID(), Get_Trx());
if (com.Get_ID() == 0)
{
throw new ArgumentException("CommissionAPInvoice - No Commission");
}
if (com.GetC_Charge_ID() == 0)
{
throw new ArgumentException("CommissionAPInvoice - No Charge on Commission");
}
MBPartner bp = new MBPartner(GetCtx(), com.GetC_BPartner_ID(), Get_Trx());
if (bp.Get_ID() == 0)
{
throw new ArgumentException("CommissionAPInvoice - No BPartner");
}
// Create Invoice
MInvoice invoice = new MInvoice(GetCtx(), 0, null);
invoice.SetClientOrg(com.GetAD_Client_ID(), com.GetAD_Org_ID());
invoice.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APINVOICE); // API
invoice.SetBPartner(bp);
// invoice.setDocumentNo (comRun.getDocumentNo()); // may cause unique constraint
invoice.SetSalesRep_ID(GetAD_User_ID()); // caller
//
if (com.GetC_Currency_ID() != invoice.GetC_Currency_ID())
{
throw new ArgumentException("CommissionAPInvoice - Currency of PO Price List not Commission Currency");
}
//
if (!invoice.Save())
{
throw new Exception("CommissionAPInvoice - cannot save Invoice");
}
// Create Invoice Line
MInvoiceLine iLine = new MInvoiceLine(invoice);
iLine.SetC_Charge_ID(com.GetC_Charge_ID());
iLine.SetQty(1);
iLine.SetPrice(comRun.GetGrandTotal());
iLine.SetTax();
if (!iLine.Save())
{
throw new Exception("CommissionAPInvoice - cannot save Invoice Line");
}
//
return("@C_Invoice_ID@ = " + invoice.GetDocumentNo());
}
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message (variables are parsed)</returns>
protected override String DoIt()
{
log.Info("doIt - C_CommissionRun_ID=" + GetRecord_ID());
// Load Data
MCommissionRun comRun = new MCommissionRun(GetCtx(), GetRecord_ID(), Get_Trx());
if (comRun.Get_ID() == 0)
{
throw new ArgumentException("CommissionAPInvoice - No Commission Run");
}
if (Env.ZERO.CompareTo(comRun.GetGrandTotal()) == 0)
{
throw new ArgumentException("@GrandTotal@ = 0");
}
MCommission com = new MCommission(GetCtx(), comRun.GetC_Commission_ID(), Get_Trx());
if (com.Get_ID() == 0)
{
throw new ArgumentException("CommissionAPInvoice - No Commission");
}
if (com.GetC_Charge_ID() == 0)
{
throw new ArgumentException("CommissionAPInvoice - No Charge on Commission");
}
MBPartner bp = new MBPartner(GetCtx(), com.GetC_BPartner_ID(), Get_Trx());
if (bp.Get_ID() == 0)
{
throw new ArgumentException("CommissionAPInvoice - No BPartner");
}
// Create Invoice
MInvoice invoice = new MInvoice(GetCtx(), 0, null);
invoice.SetClientOrg(com.GetAD_Client_ID(), com.GetAD_Org_ID());
invoice.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APINVOICE); // API
invoice.SetBPartner(bp);
// JID_0101: When we generate the AP invoice from Commission run window, its giving price list error.
if (invoice.GetM_PriceList_ID() == 0)
{
string sql = "SELECT M_PriceList_ID FROM M_PriceList WHERE IsActive = 'Y' AND AD_Client_ID = " + com.GetAD_Client_ID() + " AND AD_Org_ID = " + com.GetAD_Org_ID()
+ " AND IsDefault='Y' AND IsSOPriceList='N'";
int pricelist = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx()));
if (pricelist == 0)
{
pricelist = MPriceList.GetDefault(GetCtx(), false).Get_ID();
}
if (pricelist > 0)
{
invoice.SetM_PriceList_ID(pricelist);
}
}
// invoice.setDocumentNo (comRun.getDocumentNo()); // may cause unique constraint
invoice.SetSalesRep_ID(GetAD_User_ID()); // caller
//
if (com.GetC_Currency_ID() != invoice.GetC_Currency_ID())
{
throw new ArgumentException("CommissionAPInvoice - Currency of PO Price List not Commission Currency");
}
//
if (!invoice.Save())
{
//return GetReterivedError(invoice, "CommissionAPInvoice - cannot save Invoice");
throw new Exception("CommissionAPInvoice - cannot save Invoice");
}
// Create Invoice Line
MInvoiceLine iLine = new MInvoiceLine(invoice);
iLine.SetC_Charge_ID(com.GetC_Charge_ID());
iLine.SetQty(1);
iLine.SetPrice(comRun.GetGrandTotal());
iLine.SetTax();
if (!iLine.Save())
{
//return GetReterivedError(iLine, "CommissionAPInvoice - cannot save Invoice Line");
throw new Exception("CommissionAPInvoice - cannot save Invoice Line");
}
//
return("@C_Invoice_ID@ = " + invoice.GetDocumentNo());
}