/// <summary>
/// Create Commission Detail
/// </summary>
/// <param name="sql">sql statement</param>
/// <param name="comAmt">parent</param>
private void CreateDetail(String sql, MCommissionAmt comAmt)
{
IDataReader idr = null;
try
{
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@clientid", m_com.GetAD_Client_ID());
param[1] = new SqlParameter("@sdate", p_StartDate.Date);
param[2] = new SqlParameter("@edate", m_EndDate.Date);
idr = DataBase.DB.ExecuteReader(sql, param, Get_Trx());
while (idr.Read())
{
// CommissionAmount, C_Currency_ID, Amt, Qty,
MCommissionDetail cd = new MCommissionDetail(comAmt,
Utility.Util.GetValueOfInt(idr[0]), Utility.Util.GetValueOfDecimal(idr[1]), Utility.Util.GetValueOfDecimal(idr[2]));
// C_OrderLine_ID, C_InvoiceLine_ID,
cd.SetLineIDs(Utility.Util.GetValueOfInt(idr[3]), Utility.Util.GetValueOfInt(idr[4]));
// Reference, Info,
String s = idr[5].ToString();
if (s != null)
{
cd.SetReference(s);
}
s = idr[6].ToString();
if (s != null)
{
cd.SetInfo(s);
}
// Date
DateTime?date = Utility.Util.GetValueOfDateTime(idr[7]);
cd.SetConvertedAmt(date);
//
if (!cd.Save()) // creates memory leak
{
idr.Close();
throw new ArgumentException("CommissionCalc - Detail Not saved");
}
}
idr.Close();
}
catch (Exception e)
{
idr.Close();
log.Log(Level.SEVERE, "CreateDetail", e);
}
}
/// <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());
}
