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); }