private void GetWhereClause(VAdvantage.Model.MOrder ord)
{
// StringBuilder where = new StringBuilder();
//if (ord.GetC_BPartner_ID() != 0)
//{
// whereClause.Append(" AND fa.C_BPartner_ID = " + ord.GetC_BPartner_ID());
//}
//else
//{
// whereClause.Append(" AND fa.C_BPartner_ID is null ");
//}
if (ord.GetC_Campaign_ID() != 0)
{
whereClause.Append(" AND fa.C_Campaign_ID = " + ord.GetC_Campaign_ID());
}
else
{
whereClause.Append(" AND fa.C_Campaign_ID is null ");
}
if (ord.GetC_Activity_ID() != 0)
{
whereClause.Append(" AND fa.C_Activity_ID = " + ord.GetC_Activity_ID());
}
else
{
whereClause.Append(" AND fa.C_Activity_ID is null ");
}
if (ord.GetC_Project_ID() != 0)
{
whereClause.Append(" AND fa.C_Project_ID = " + ord.GetC_Project_ID());
}
else
{
whereClause.Append(" AND fa.C_Project_ID is null ");
}
//if (ord.GetUser1_ID() != 0)
//{
// whereClause.Append(" AND fa.User1_ID = " + ord.GetUser1_ID());
//}
//else
//{
// whereClause.Append(" AND fa.User1_ID is null ");
//}
//if (ord.GetUser2_ID() != 0)
//{
// whereClause.Append(" AND fa.User2_ID = " + ord.GetUser2_ID());
//}
//else
//{
// whereClause.Append(" AND fa.User2_ID is null ");
//}
//if (ord.GetAD_OrgTrx_ID() != 0)
//{
// whereClause.Append(" AND fa.AD_OrgTrx_ID = " + ord.GetAD_OrgTrx_ID());
//}
//else
//{
// whereClause.Append(" AND fa.AD_OrgTrx_ID is null ");
//}
//if (ord.GetC_BPartner_ID() != 0)
//{
// where.Append("C_BPartner_ID = " + ord.GetC_BPartner_ID());
//}
}
//
private int GetBudgetAmount(int AccID, DateTime?startDate, DateTime?endDate, VAdvantage.Model.MOrder ord)
{
int B_ID = 0;
int budget_ID = 0;
bgtAmount = 0;
sql.Clear();
int C_Project_ID = Util.GetValueOfInt(ord.GetC_Project_ID());
int C_Campaign_ID = Util.GetValueOfInt(ord.GetC_Campaign_ID());
int C_Activity_ID = Util.GetValueOfInt(ord.GetC_Activity_ID());
IDataReader idr = null;
if (C_Project_ID != 0 && C_Campaign_ID != 0 && C_Activity_ID != 0)
{
// find Budget against all three Parameters
budget_ID = GetBudgetAgainstProjectCampaignActivity(AccID, startDate, endDate, C_Project_ID, C_Campaign_ID, C_Activity_ID);
// if not found then find against Project and Campaign
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstProjectCampaign(AccID, startDate, endDate, C_Project_ID, C_Campaign_ID);
}
// Then Against Project and Activity
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstProjectActivity(AccID, startDate, endDate, C_Project_ID, C_Activity_ID);
}
// Then Against Campaign and Activity
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstCampaignActivity(AccID, startDate, endDate, C_Activity_ID, C_Campaign_ID);
}
// Against Project
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstProject(AccID, startDate, endDate, C_Project_ID);
}
// Against Project
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstCampaign(AccID, startDate, endDate, C_Campaign_ID);
}
// Against Activity
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstActivity(AccID, startDate, endDate, C_Activity_ID);
}
}
else if (C_Project_ID != 0 && C_Campaign_ID != 0)
{
budget_ID = GetBudgetAgainstProjectCampaign(AccID, startDate, endDate, C_Project_ID, C_Campaign_ID);
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstProject(AccID, startDate, endDate, C_Project_ID);
}
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstCampaign(AccID, startDate, endDate, C_Campaign_ID);
}
}
else if (C_Campaign_ID != 0 && C_Activity_ID != 0)
{
budget_ID = GetBudgetAgainstCampaignActivity(AccID, startDate, endDate, C_Campaign_ID, C_Activity_ID);
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstCampaign(AccID, startDate, endDate, C_Campaign_ID);
}
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstActivity(AccID, startDate, endDate, C_Activity_ID);
}
}
else if (C_Project_ID != 0 && C_Activity_ID != 0)
{
budget_ID = GetBudgetAgainstProjectActivity(AccID, startDate, endDate, C_Project_ID, C_Activity_ID);
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstProject(AccID, startDate, endDate, C_Project_ID);
}
if (budget_ID == 0)
{
budget_ID = GetBudgetAgainstActivity(AccID, startDate, endDate, C_Activity_ID);
}
}
else if (C_Project_ID != 0)
{
budget_ID = GetBudgetAgainstProject(AccID, startDate, endDate, C_Project_ID);
}
else if (C_Campaign_ID != 0)
{
budget_ID = GetBudgetAgainstCampaign(AccID, startDate, endDate, C_Campaign_ID);
}
else if (C_Activity_ID != 0)
{
budget_ID = GetBudgetAgainstActivity(AccID, startDate, endDate, C_Activity_ID);
}
// If Budget Not Found For Any Dimension Then look for Account + Organization
if (budget_ID == 0)
{
try
{
sql.Clear();
sql.Append("SELECT fa.AmtAcctDr, b.GL_Budget_ID FROM Fact_Acct fa inner join GL_Budget b ON (b.GL_Budget_ID = fa.GL_Budget_ID) WHERE fa.Account_ID = " + AccID + " AND fa.DateAcct >= " + GlobalVariable.TO_DATE(startDate, false) + " AND fa.DateAcct <= " + GlobalVariable.TO_DATE(endDate, false) + " AND fa.AD_Org_ID = " + _AD_Org_ID + " AND fa.AD_Client_ID = " + _AD_Client_ID + " AND fa.GL_Budget_ID IS Not null AND b.BudgetControlBasis = '" + controlBasis + "' ORDER BY fa.dateacct DESC");
idr = DB.ExecuteReader(sql.ToString(), null, null);
while (idr.Read())
{
if (B_ID == 0)
{
B_ID = Util.GetValueOfInt(idr[1]);
}
if (B_ID.Equals(Util.GetValueOfInt(idr[1])))
{
budget_ID = Util.GetValueOfInt(idr[1]);
bgtAmount = Util.GetValueOfDecimal(idr[0]);
}
}
idr.Close();
idr = null;
}
catch
{
idr.Close();
idr = null;
}
}
#region
//while (!gotID)
//{
// finalWhereClause.Clear();
// if (ord.GetC_Campaign_ID() != 0)
// {
// finalWhereClause.Append(" AND fa.C_Campaign_ID = " + ord.GetC_Campaign_ID());
// }
// else
// {
// finalWhereClause.Append(" AND fa.C_Campaign_ID is null ");
// }
// if (ord.GetC_Activity_ID() != 0)
// {
// finalWhereClause.Append(" AND fa.C_Activity_ID = " + ord.GetC_Activity_ID());
// }
// else
// {
// finalWhereClause.Append(" AND fa.C_Activity_ID is null ");
// }
// if (ord.GetC_Project_ID() != 0)
// {
// finalWhereClause.Append(" AND fa.C_Project_ID = " + ord.GetC_Project_ID());
// }
// else
// {
// finalWhereClause.Append(" AND fa.C_Project_ID is null ");
// }
// sql.Append("SELECT fa.AmtAcctDr, b.GL_Budget_ID FROM Fact_Acct fa inner join GL_Budget b ON (b.GL_Budget_ID = fa.GL_Budget_ID) " + whereClause + " AND fa.GL_Budget_ID IS Not null AND b.BudgetControlBasis = '" + controlBasis + "' ORDER BY fa.DateAcct DESC");
// // IDataReader idr = null;
// try
// {
// // Amount = Util.GetValueOfDecimal(DB.ExecuteScalar(sql.ToString(), null, null));
// idr = DB.ExecuteReader(sql.ToString(), null, null);
// while (idr.Read())
// {
// gotID = true;
// if (B_ID == 0)
// {
// B_ID = Util.GetValueOfInt(idr[1]);
// }
// if (B_ID.Equals(Util.GetValueOfInt(idr[1])))
// {
// budget_ID = Util.GetValueOfInt(idr[1]);
// bgtAmount = Decimal.Add(bgtAmount.Value, Util.GetValueOfDecimal(idr[0]));
// }
// }
// idr.Close();
// idr = null;
// //if (budget_ID.Equals(0))
// //{
// // sql.Clear();
// // sql.Append("SELECT fa.AmtAcctDr, b.GL_Budget_ID FROM Fact_Acct fa inner join GL_Budget b ON (b.GL_Budget_ID = fa.GL_Budget_ID) WHERE fa.Account_ID = " + AccID + " AND fa.DateAcct >= " + GlobalVariable.TO_DATE(startDate, false) + " AND fa.DateAcct <= " + GlobalVariable.TO_DATE(endDate, false) + " AND fa.AD_Org_ID = " + _AD_Org_ID + " AND fa.AD_Client_ID = " + _AD_Client_ID + " AND fa.GL_Budget_ID IS Not null AND b.BudgetControlBasis = '" + controlBasis + "' AND RowNum = 1 ORDER BY fa.dateacct DESC");
// // idr = DB.ExecuteReader(sql.ToString(), null, null);
// // while (idr.Read())
// // {
// // budget_ID = Util.GetValueOfInt(idr[1]);
// // bgtAmount = Util.GetValueOfDecimal(idr[0]);
// // break;
// // }
// // idr.Close();
// // idr = null;
// //}
// }
// catch
// {
// idr.Close();
// idr = null;
// }
//}
//sql.Append("SELECT GL_Budget_ID FROM GL_Budget WHERE " + GlobalVariable.TO_DATE(dateAcct, false) + " >= FromDate AND " + GlobalVariable.TO_DATE(dateAcct, false) + " <= ToDate AND IsActive = 'Y' AND AD_Client_ID = " + _AD_Client_ID);
// sql.Append("SELECT GL_Budget_ID FROM GL_Budget WHERE AD_Client_ID = " + _AD_Client_ID + " AND BudgetControlBasis = '" + controlBasis + "'");
// int GL_Budget_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql.ToString(), null, null));
// sql.Clear();
//sql.Append("SELECT GL_Budget_ID FROM GL_Budget WHERE " + GlobalVariable.TO_DATE(dateAcct, false) + " >= FromDate AND " + GlobalVariable.TO_DATE(dateAcct, false) + " <= ToDate AND IsActive = 'Y' AND AD_Client_ID = " + _AD_Client_ID);
#endregion
return(budget_ID);
}
