private Decimal?CompareBudget(Decimal?lineAmt, VAdvantage.Model.MOrder order) { Decimal?amt = 0; sql.Clear(); sql.Append(" SELECT SUM(fa.AmtAcctDr) FROM Fact_Acct fa " + whereClause); Decimal?accBudget = Util.GetValueOfDecimal(DB.ExecuteScalar(sql.ToString(), null, null)); if (bgtAmount < Decimal.Add(accBudget.Value, lineAmt.Value)) { amt = Decimal.Subtract(Decimal.Add(accBudget.Value, lineAmt.Value), bgtAmount.Value); if (maxAmount < amt) { //sql.Clear(); order.SetIsBudgetViolated(true); order.SetMaxBudgetViolationAmount(amt); if (!order.Save()) { } //sql.Append("UPDATE " + tableName + " SET IsBudgetViolated = 'Y', MaxBudgetViolationAmount = " + amt + " WHERE " + tableName + "_ID = " + _Record_ID); //int res = Util.GetValueOfInt(DB.ExecuteQuery(sql.ToString(), null, null)); } } return(amt); }
protected override string DoIt() { int c_order_id = 0; int pid = 0; VAdvantage.Model.MOrder ord = new VAdvantage.Model.MOrder(GetCtx(), c_order_id, null); VAdvantage.Model.MOrderLine ol = new VAdvantage.Model.MOrderLine(GetCtx(), 0, null); ol.SetC_Order_ID(c_order_id); ol.SetM_Product_ID(pid); ol.SetAD_Client_ID(ord.GetAD_Client_ID()); ol.SetAD_Org_ID(ord.GetAD_Org_ID()); ol.SetQty(1); if (!ol.Save()) { } return(""); }
protected override string DoIt() { // Int32 value = 0; string msg = ""; log.Info("C_Project_ID=" + _C_Project_ID); if (_C_Project_ID == 0) { throw new ArgumentException("C_Project_ID == 0"); } VAdvantage.Model.MProject fromProject = new VAdvantage.Model.MProject(GetCtx(), _C_Project_ID, null); if (fromProject.GetGenerate_Quotation() == null) { throw new ArgumentException("No Generate Quotation found on Project."); } if (fromProject.GetGenerate_Quotation().Trim() == "Y") { throw new ArgumentException("Sales Quotation already generated"); } VAdvantage.Model.MOrder order = new VAdvantage.Model.MOrder(GetCtx(), 0, null); C_Bpartner_id = fromProject.GetC_BPartner_ID(); C_Bpartner_Location_id = fromProject.GetC_BPartner_Location_ID(); C_BPartnerSR_ID = fromProject.GetC_BPartnerSR_ID(); //MBPartner bp = new MBPartner(GetCtx(), C_Bpartner_id, null); VAdvantage.Model.MBPartnerLocation bpartnerloc = new VAdvantage.Model.MBPartnerLocation(GetCtx(), C_Bpartner_Location_id, null); String currentdate = DateTime.Now.ToString(); String sqlprjln = " select c_projectline_id from c_projectline where c_project_id=" + _C_Project_ID + ""; C_ProjectLine_ID = VAdvantage.Utility.Util.GetValueOfInt(DB.ExecuteScalar(sqlprjln)); if (C_ProjectLine_ID != 0) { order.SetDateOrdered(Convert.ToDateTime(currentdate)); order.SetDatePromised(Convert.ToDateTime(currentdate)); if (C_Bpartner_id != 0) { order.SetC_BPartner_ID(fromProject.GetC_BPartner_ID()); if (bpartnerloc.IsShipTo() == true) { order.SetC_BPartner_Location_ID(fromProject.GetC_BPartner_Location_ID()); order.SetAD_User_ID(fromProject.GetAD_User_ID()); } if (bpartnerloc.IsBillTo() == true) { order.SetBill_Location_ID(fromProject.GetC_BPartner_Location_ID()); order.SetBill_User_ID(fromProject.GetAD_User_ID()); } } if (C_BPartnerSR_ID != 0) { //String sqlcust = "update c_bpartner set iscustomer='Y', isprospect='N' where c_bpartner_id=" + C_BPartnerSR_ID + ""; //value = DB.ExecuteQuery(sqlcust, null, null); //if (value == -1) //{ //} //bp.SetIsCustomer(true); //bp.SetIsProspect(false); order.SetC_BPartner_ID(fromProject.GetC_BPartnerSR_ID()); if (bpartnerloc.IsShipTo() == true) { order.SetC_BPartner_Location_ID(fromProject.GetC_BPartner_Location_ID()); order.SetAD_User_ID(fromProject.GetAD_User_ID()); } if (bpartnerloc.IsBillTo() == true) { order.SetBill_Location_ID(fromProject.GetC_BPartner_Location_ID()); order.SetBill_User_ID(fromProject.GetAD_User_ID()); } } // String sql = "select c_doctype_id from c_doctype where docbasetype= 'SOO' and = 'Sales Quotation'"; String sql = "select c_doctype_id from c_doctype where docbasetype = 'SOO' and docsubtypeso = 'ON' and isreturntrx = 'N' and ad_client_id = " + GetCtx().GetAD_Client_ID(); int Doctype_id = VAdvantage.Utility.Util.GetValueOfInt(DB.ExecuteScalar(sql)); int MPriceList_id = Util.GetValueOfInt(fromProject.GetM_PriceList_ID()); order.SetM_PriceList_ID(MPriceList_id); ////String sqlmpricelist = "select m_pricelist_id from m_pricelist where name='Export'"; ////int MPriceList_id = VAdvantage.Utility.Util.GetValueOfInt(DB.ExecuteScalar(sqlmpricelist)); //if (MPriceList_id == order.GetM_PriceList_ID()) //{ // String sqlconversiontype = "select c_conversiontype_id from c_conversiontype where value = 'C'"; // int C_ConversionType_id = VAdvantage.Utility.Util.GetValueOfInt(DB.ExecuteScalar(sqlconversiontype)); // order.SetC_ConversionType_ID(C_ConversionType_id); //} order.SetC_Project_ID(GetRecord_ID()); if (fromProject.GetSalesRep_ID() > 0) { order.SetSalesRep_ID(fromProject.GetSalesRep_ID()); } order.SetC_Currency_ID(fromProject.GetC_Currency_ID()); if (C_Bpartner_id != 0) { VAdvantage.Model.MBPartner bp = new VAdvantage.Model.MBPartner(GetCtx(), C_Bpartner_id, null); if (bp.GetC_Campaign_ID() == 0 && fromProject.GetC_Campaign_ID() > 0) { bp.SetC_Campaign_ID(fromProject.GetC_Campaign_ID()); } bp.SetAD_Client_ID(fromProject.GetAD_Client_ID()); bp.SetAD_Org_ID(fromProject.GetAD_Org_ID()); if (bp.GetC_PaymentTerm_ID() != 0) { order.SetPaymentMethod(bp.GetPaymentRule()); order.SetC_PaymentTerm_ID(bp.GetC_PaymentTerm_ID()); } if (!bp.Save()) { log.SaveError("CampaignIDNotSaved", ""); return(Msg.GetMsg(GetCtx(), "CampaignIDtNotSaved")); } } else { VAdvantage.Model.MBPartner bp = new VAdvantage.Model.MBPartner(GetCtx(), C_BPartnerSR_ID, null); if (bp.GetC_Campaign_ID() == 0 && fromProject.GetC_Campaign_ID() > 0) { bp.SetC_Campaign_ID(fromProject.GetC_Campaign_ID()); } bp.SetAD_Client_ID(fromProject.GetAD_Client_ID()); bp.SetAD_Org_ID(fromProject.GetAD_Org_ID()); if (bp.GetC_PaymentTerm_ID() != 0) { order.SetPaymentMethod(bp.GetPaymentRule()); order.SetC_PaymentTerm_ID(bp.GetC_PaymentTerm_ID()); } if (!bp.Save()) { log.SaveError("CampaignIDtNotSaved", ""); return(Msg.GetMsg(GetCtx(), "CampaignIDtNotSaved")); } } //if (bp.GetC_PaymentTerm_ID() != 0) //{ // order.SetPaymentMethod(bp.GetPaymentRule()); // order.SetC_PaymentTerm_ID(bp.GetC_PaymentTerm_ID()); //} order.SetFreightCostRule("I"); if (order.GetC_Campaign_ID() == 0 && fromProject.GetC_Campaign_ID() > 0) { order.SetC_Campaign_ID(fromProject.GetC_Campaign_ID()); } order.SetDocStatus("IP"); order.SetC_DocType_ID(Doctype_id); order.SetC_DocTypeTarget_ID(Doctype_id); order.SetIsSOTrx(true); if (!order.Save()) { log.SaveError("SaleOrdertNotSaved", ""); return(Msg.GetMsg(GetCtx(), "SaleOrdertNotSaved")); } //Order Lines int count = 0; VAdvantage.Model.MProjectLine[] lines = fromProject.GetLines(); for (int i = 0; i < lines.Length; i++) { VAdvantage.Model.MOrderLine ol = new VAdvantage.Model.MOrderLine(order); ol.SetLine(lines[i].GetLine()); ol.SetDescription(lines[i].GetDescription()); ol.SetM_Product_ID(lines[i].GetM_Product_ID(), true); ol.SetQtyEntered(lines[i].GetPlannedQty()); ol.SetQtyOrdered(lines[i].GetPlannedQty()); ol.SetPriceEntered(lines[i].GetPlannedPrice()); ol.SetPriceActual(lines[i].GetPlannedPrice()); ol.SetPriceList(lines[i].GetPriceList()); if (ol.Save()) { count++; } } fromProject.SetRef_Order_ID(order.GetC_Order_ID()); fromProject.SetGenerate_Quotation("Y"); if (!fromProject.Save()) { log.SaveError("ProjectNotSaved", ""); return(Msg.GetMsg(GetCtx(), "ProjectNotSaved"));; } msg = Msg.GetMsg(GetCtx(), "QuotationGenerated"); } else { msg = Msg.GetMsg(GetCtx(), "No Lines"); } return(msg); }
/// <summary> /// Generate Shipments /// </summary> /// <returns>info</returns> protected override String DoIt() { bool isExpense = false; if (ConsolidateDocument == "Y") { string sqlSelect = "select * from s_timeexpenseline where processed = 'Y' and (ARInvoice = 'Y' or billtocustomer = 'Y') and Ref_C_Invoice_ID is null"; StringBuilder sqlWhere = new StringBuilder(); if (C_Order_ID != 0) { sqlWhere.Append(" AND C_Order_ID = " + C_Order_ID); } else { if (_C_BPartner_ID != 0) { sqlWhere.Append(" AND C_BPartner_ID = " + _C_BPartner_ID); } if (AD_Org_ID != 0) { sqlWhere.Append(" AND AD_Org_ID = " + AD_Org_ID); } } if (sqlWhere.Length > 0) { sql = sqlSelect + sqlWhere.ToString(); } else { sql = sqlSelect; } sql = sql + " order by C_order_ID, c_bpartner_id"; IDataReader idr = null; try { idr = DB.ExecuteReader(sql, null, null); while (idr.Read()) { if (C_BPartner_ID.Contains(Util.GetValueOfInt(idr["C_BPartner_ID"]))) { VAdvantage.Model.MOrder ord = new VAdvantage.Model.MOrder(GetCtx(), Util.GetValueOfInt(idr["C_Order_ID"]), null); bool chk = false; for (int i = 0; i < invoices.Count; i++) { VAdvantage.Model.MInvoice inv = new VAdvantage.Model.MInvoice(GetCtx(), Util.GetValueOfInt(invoices[i]), null); if ((inv.GetC_PaymentTerm_ID() == ord.GetC_PaymentTerm_ID()) && (inv.GetM_PriceList_ID() == ord.GetM_PriceList_ID())) { chk = true; break; } } if (!chk) { VAdvantage.Model.X_S_TimeExpenseLine tLine = new VAdvantage.Model.X_S_TimeExpenseLine(GetCtx(), Util.GetValueOfInt(idr["s_timeexpenseline_id"]), null); VAdvantage.Model.X_S_TimeExpense tExp = new VAdvantage.Model.X_S_TimeExpense(GetCtx(), Util.GetValueOfInt(tLine.GetS_TimeExpense_ID()), null); int C_Invoice_ID = GenerateInvoice(tLine, tExp, isExpense); invoices.Add(C_Invoice_ID); } } else { C_BPartner_ID.Add(Util.GetValueOfInt(idr["C_BPartner_ID"])); VAdvantage.Model.X_S_TimeExpenseLine tLine = new VAdvantage.Model.X_S_TimeExpenseLine(GetCtx(), Util.GetValueOfInt(idr["s_timeexpenseline_id"]), null); VAdvantage.Model.X_S_TimeExpense tExp = new VAdvantage.Model.X_S_TimeExpense(GetCtx(), Util.GetValueOfInt(tLine.GetS_TimeExpense_ID()), null); int C_Invoice_ID = GenerateInvoice(tLine, tExp, isExpense); invoices.Add(C_Invoice_ID); } } if (idr != null) { idr.Close(); idr = null; } // isExpense = false; if (invoices.Count > 0) { for (int k = 0; k < invoices.Count; k++) // foreach (KeyValuePair<int, int> pair in BPInvoice) { sqlWhere = new StringBuilder(); if (C_Order_ID != 0) { sqlWhere.Append(" AND C_Order_ID = " + C_Order_ID); } else { if (_C_BPartner_ID != 0) { sqlWhere.Append(" AND C_BPartner_ID = " + _C_BPartner_ID); } if (AD_Org_ID != 0) { sqlWhere.Append(" AND AD_Org_ID = " + AD_Org_ID); } } if (sqlWhere.Length > 0) { sql = sqlSelect + sqlWhere.ToString(); } else { sql = sqlSelect; } sql = sql + " order by C_order_ID, c_bpartner_id"; DataSet ds = DB.ExecuteDataset(sql, null, null); if (ds != null) { if (ds.Tables[0].Rows.Count > 0) { for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { int invID = 0; bool chk1 = false; for (int l = 0; l < invoices.Count; l++) { VAdvantage.Model.MOrder ord = new VAdvantage.Model.MOrder(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[j]["C_Order_ID"]), null); VAdvantage.Model.MInvoice inv1 = new VAdvantage.Model.MInvoice(GetCtx(), Util.GetValueOfInt(invoices[l]), null); if ((inv1.GetC_PaymentTerm_ID() == ord.GetC_PaymentTerm_ID()) && (inv1.GetC_BPartner_ID() == ord.GetC_BPartner_ID()) && (inv1.GetM_PriceList_ID() == ord.GetM_PriceList_ID())) { chk1 = true; invID = inv1.GetC_Invoice_ID(); break; } } if (chk1) { //CreateLine(Util.GetValueOfInt(ds.Tables[0].Rows[j]["S_TimeExpenseLine_ID"]), Util.GetValueOfInt(pair.Value)); CreateLine(Util.GetValueOfInt(ds.Tables[0].Rows[j]["S_TimeExpenseLine_ID"]), invID); } } for (int m = 0; m < invoices.Count; m++) { //MInvoice inv = new MInvoice(GetCtx(), Util.GetValueOfInt(pair.Value), null); VAdvantage.Model.MInvoice inv = new VAdvantage.Model.MInvoice(GetCtx(), Util.GetValueOfInt(invoices[m]), null); if (docAction == "CO") { string comp = inv.CompleteIt(); if (comp == "CO") { inv.SetDocAction("CL"); inv.SetDocStatus("CO"); inv.Save(); } } else if (docAction == "PR") { string prp = inv.PrepareIt(); if (prp == "IP") { inv.SetDocAction("PR"); inv.SetDocStatus("IP"); inv.Save(); } } } } } } } } catch { if (idr != null) { idr.Close(); idr = null; } return(Msg.GetMsg(GetCtx(), "ProcessTerminated")); } } // If Consolidate Document is Not Checked else { string sqlSelect = "select * from s_timeexpenseline where processed = 'Y' and (ARInvoice = 'Y' or billtocustomer = 'Y') and Ref_C_Invoice_ID is null"; StringBuilder sqlWhere = new StringBuilder(); if (C_Order_ID != 0) { sqlWhere.Append(" AND C_Order_ID = " + C_Order_ID); } else { if (_C_BPartner_ID != 0) { sqlWhere.Append(" AND C_BPartner_ID = " + _C_BPartner_ID); } if (AD_Org_ID != 0) { sqlWhere.Append(" AND AD_Org_ID = " + AD_Org_ID); } } if (sqlWhere.Length > 0) { sql = sqlSelect + sqlWhere.ToString(); } else { sql = sqlSelect; } sql = sql + " order by C_order_ID, c_bpartner_id"; IDataReader idr = null; try { idr = DB.ExecuteReader(sql, null, null); int C_Invoice_ID = 0; while (idr.Read()) { if (orders.Contains(Util.GetValueOfInt(idr["C_Order_ID"]))) { CreateLine(Util.GetValueOfInt(idr["s_timeexpenseline_id"]), C_Invoice_ID); } else { orders.Add(Util.GetValueOfInt(idr["C_Order_ID"])); VAdvantage.Model.X_S_TimeExpenseLine tLine = new VAdvantage.Model.X_S_TimeExpenseLine(GetCtx(), Util.GetValueOfInt(idr["s_timeexpenseline_id"]), null); VAdvantage.Model.X_S_TimeExpense tExp = new VAdvantage.Model.X_S_TimeExpense(GetCtx(), Util.GetValueOfInt(tLine.GetS_TimeExpense_ID()), null); C_Invoice_ID = GenerateInvoice(tLine, tExp, isExpense); invoices.Add(C_Invoice_ID); CreateLine(Util.GetValueOfInt(idr["s_timeexpenseline_id"]), C_Invoice_ID); } } for (int m = 0; m < invoices.Count; m++) { //MInvoice inv = new MInvoice(GetCtx(), Util.GetValueOfInt(pair.Value), null); VAdvantage.Model.MInvoice inv = new VAdvantage.Model.MInvoice(GetCtx(), Util.GetValueOfInt(invoices[m]), null); if (docAction == "CO") { string comp = inv.CompleteIt(); if (comp == "CO") { inv.SetDocAction("CL"); inv.SetDocStatus("CO"); inv.Save(); } } else if (docAction == "PR") { string prp = inv.PrepareIt(); if (prp == "IP") { inv.SetDocAction("PR"); inv.SetDocStatus("IP"); inv.Save(); } } } if (idr != null) { idr.Close(); idr = null; } } catch { if (idr != null) { idr.Close(); idr = null; } } } string docNo = ""; for (int i = 0; i < invoices.Count; i++) { VAdvantage.Model.MInvoice inv = new VAdvantage.Model.MInvoice(GetCtx(), Util.GetValueOfInt(invoices[i]), null); docNo = docNo + ", " + inv.GetDocumentNo(); } if (docNo != "") { docNo = docNo.Remove(0, 2); } return(Msg.GetMsg(GetCtx(), "InvoicesCreated" + " : " + docNo)); // return Msg.GetMsg(GetCtx(), "ProcessCompleted"); }
/// <summary> /// Perform Process. /// </summary> /// <returns>Message (clear text)</returns> protected override String DoIt() { log.Info("C_Order_ID=" + _C_Order_ID + ", C_DocType_ID=" + _C_DocType_ID + ", CloseDocument=" + _IsCloseDocument); if (_C_Order_ID == 0) { throw new ArgumentException("No Order"); } VAdvantage.Model.MDocType dt = VAdvantage.Model.MDocType.Get(GetCtx(), _C_DocType_ID); if (dt.Get_ID() == 0) { throw new ArgumentException("No DocType"); } if (_DateDoc == null) { _DateDoc = Util.GetValueOfDateTime(DateTime.Now); } // VAdvantage.Model.MOrder from = new VAdvantage.Model.MOrder(GetCtx(), _C_Order_ID, Get_Trx()); VAdvantage.Model.MOrder newOrder = VAdvantage.Model.MOrder.CopyFrom(from, _DateDoc, dt.GetC_DocType_ID(), false, true, null); // copy ASI newOrder.SetC_DocTypeTarget_ID(_C_DocType_ID); int C_Bpartner_ID = newOrder.GetC_BPartner_ID(); //Update New Order Refrence From Sales Qutation in Sales order newOrder.SetPOReference(Util.GetValueOfString(from.GetDocumentNo())); String sqlbp = "update c_project set c_bpartner_id=" + C_Bpartner_ID + " where ref_order_id=" + _C_Order_ID + ""; int value = DB.ExecuteQuery(sqlbp, null, Get_Trx()); bool OK = newOrder.Save(); if (!OK) { throw new Exception("Could not create new Order"); } if (OK) { string sql = "select C_Project_id from c_project where c_order_id = " + from.GetC_Order_ID(); int C_Project_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx())); if (C_Project_ID != 0) { VAdvantage.Model.X_C_Project project = new VAdvantage.Model.X_C_Project(GetCtx(), C_Project_ID, Get_Trx()); project.SetC_BPartner_ID(project.GetC_BPartnerSR_ID()); project.SetC_BPartnerSR_ID(0); if (!project.Save()) { } } from.SetRef_Order_ID(newOrder.GetC_Order_ID()); from.Save(); int bp = newOrder.GetC_BPartner_ID(); VAdvantage.Model.X_C_BPartner prosp = new VAdvantage.Model.X_C_BPartner(GetCtx(), bp, Get_Trx()); prosp.SetIsCustomer(true); prosp.SetIsProspect(false); prosp.Save(); } // if (_IsCloseDocument) { VAdvantage.Model.MOrder original = new VAdvantage.Model.MOrder(GetCtx(), _C_Order_ID, Get_Trx()); original.SetDocAction(VAdvantage.Model.MOrder.DOCACTION_Complete); original.ProcessIt(VAdvantage.Model.MOrder.DOCACTION_Complete); original.Save(); original.SetDocAction(VAdvantage.Model.MOrder.DOCACTION_Close); original.ProcessIt(VAdvantage.Model.MOrder.DOCACTION_Close); original.Save(); } // return(Msg.GetMsg(GetCtx(), "OrderCreatedSuuccessfully")); //return dt.GetName() + ": " + newOrder.GetDocumentNo(); }
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); }
/// <summary> /// Document Base type for Process e.g. POO for Purchase Order /// </summary> /// <param name="docBaseType"></param> private void ProcessDocBaseType(string docBaseType) { int M_Product_ID = 0; DateTime?sDate = null; DateTime?eDate = null; if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_PURCHASEORDER.Equals(docBaseType)) { VAdvantage.Model.MOrder ord = new VAdvantage.Model.MOrder(GetCtx(), _Record_ID, _trx); if (controlBasis.Equals("A")) { sql.Clear(); sql.Append(@" SELECT PeriodNo, StartDate , EndDate FROM C_Period WHERE C_Year_ID = (SELECT cy.C_Year_ID FROM C_Period cp inner join C_Year cy ON (cy.c_year_id = cp.c_Year_id) WHERE cy.IsActive = 'Y' AND cy.AD_Client_ID = " + _AD_Client_ID + @" AND StartDate <= " + GlobalVariable.TO_DATE(ord.GetDateOrdered(), true) + @" AND EndDate >= " + GlobalVariable.TO_DATE(ord.GetDateOrdered(), true) + @" AND cy.c_calendar_id = (SELECT C_Calendar_ID FROM AD_ClientInfo WHERE ad_clientinfo.ad_client_id = " + _AD_Client_ID + @")) AND PeriodNo IN (1,12)"); // sql.Append("SELECT PeriodNo, StartDate, EndDate FROM C_Period WHERE C_Year_ID = (SELECT C_Year_ID FROM C_Period WHERE IsActive = 'Y' AND AD_Client_ID = " + _AD_Client_ID + " AND StartDate <= " + GlobalVariable.TO_DATE(System.DateTime.Now, false) + " AND EndDate >= " + GlobalVariable.TO_DATE(System.DateTime.Now, false) + ") AND PeriodNo IN (1,12)"); DataSet dsPer = DB.ExecuteDataset(sql.ToString(), null, null); if (dsPer != null) { if (dsPer.Tables[0].Rows.Count > 0) { for (int k = 0; k < dsPer.Tables[0].Rows.Count; k++) { if (Util.GetValueOfInt(dsPer.Tables[0].Rows[k]["PeriodNo"]).Equals(1)) { sDate = Util.GetValueOfDateTime(dsPer.Tables[0].Rows[k]["StartDate"]); } if (Util.GetValueOfInt(dsPer.Tables[0].Rows[k]["PeriodNo"]).Equals(12)) { eDate = Util.GetValueOfDateTime(dsPer.Tables[0].Rows[k]["EndDate"]); } } } } else { log.SaveError("Period Not Found", "Period Not Found"); } dsPer.Dispose(); //whereClause.Append(" AND DateAcct >= " + GlobalVariable.TO_DATE(sDate, false) + " AND DateAcct <= " + GlobalVariable.TO_DATE(eDate, false)); } else if (controlBasis.Equals("P")) { sql.Clear(); sql.Append(@" SELECT PeriodNo, StartDate , EndDate FROM C_Period cp INNER JOIN C_Year cy ON (cy.c_year_id = cp.c_Year_id) WHERE cy.IsActive = 'Y' AND cy.AD_Client_ID = " + _AD_Client_ID + @" AND StartDate <= " + GlobalVariable.TO_DATE(ord.GetDateOrdered(), true) + @" AND EndDate >= " + GlobalVariable.TO_DATE(ord.GetDateOrdered(), true) + @" AND cy.c_calendar_id = (SELECT C_Calendar_ID FROM AD_ClientInfo WHERE ad_clientinfo.ad_client_id = " + _AD_Client_ID + @")"); //sql.Append("SELECT PeriodNo, StartDate, EndDate FROM C_Period WHERE IsActive = 'Y' AND AD_Client_ID = " + _AD_Client_ID + " AND StartDate <= " + GlobalVariable.TO_DATE(System.DateTime.Now, false) + " AND EndDate >= " + GlobalVariable.TO_DATE(System.DateTime.Now, false)); DataSet dsPer = DB.ExecuteDataset(sql.ToString(), null, null); if (dsPer != null) { if (dsPer.Tables[0].Rows.Count > 0) { sDate = Util.GetValueOfDateTime(dsPer.Tables[0].Rows[0]["StartDate"]); eDate = Util.GetValueOfDateTime(dsPer.Tables[0].Rows[0]["EndDate"]); } } else { log.SaveError("Period Not Found", "Period Not Found"); } dsPer.Dispose(); } sql.Clear(); sql.Append("SELECT * FROM C_OrderLine WHERE C_Order_ID = " + _Record_ID + " AND IsActive ='Y'"); DataSet ds = DB.ExecuteDataset(sql.ToString(), null, null); if (ds != null) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { whereClause.Clear(); // Amount = 0; VAdvantage.Model.MOrderLine ol = new VAdvantage.Model.MOrderLine(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_OrderLine_ID"]), _trx); M_Product_ID = ol.GetM_Product_ID(); int Account_ID = 0; if (M_Product_ID > 0) { Account_ID = GetAccount(M_Product_ID, 0); whereClause.Append(" WHERE fa.AD_Client_ID = " + _AD_Client_ID + " AND fa.Account_ID = " + Account_ID + " AND fa.AD_Org_ID = " + _AD_Org_ID); } else if (ol.GetC_Charge_ID() > 0) { Account_ID = GetAccount(0, ol.GetC_Charge_ID()); whereClause.Append(" WHERE fa.AD_Client_ID = " + _AD_Client_ID + " AND fa.Account_ID = " + Account_ID + " AND fa.AD_Org_ID = " + _AD_Org_ID); } whereClause.Append(" AND fa.DateAcct >= " + GlobalVariable.TO_DATE(sDate, false) + " AND fa.DateAcct <= " + GlobalVariable.TO_DATE(eDate, false)); // GetBudgetAgainstDimension(ord); if (Account_ID > 0) { Decimal?lineAmount = GetLineAmount(ord.GetC_Currency_ID(), ol.GetLineNetAmt()); int BudgetID = 0; //if (ord.GetC_Project_ID() > 0) //{ // BudgetID = GetProjectBudget(ord.GetC_Project_ID()); //} //if (BudgetID <= 0) //{ // BudgetID = GetOrgBudget(); //} BudgetID = GetBudgetAmount(Account_ID, sDate, eDate, ord); if (BudgetID > 0) { GetBudgetControlComparison(BudgetID); Decimal?amt = CompareBudget(lineAmount, ord); sql.Clear(); if (amt > 0) { ol.SetBudgetViolationAmount(amt); if (!ol.Save()) { } //sql.Append("UPDATE C_OrderLine SET BudgetViolationAmount = " + amt + " WHERE C_OrderLine_ID = " + Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_OrderLine_ID"])); //int res = Util.GetValueOfInt(DB.ExecuteQuery(sql.ToString(), null, null)); } } else { log.SaveError("Budget Not Found", "Budget Not Found"); } } else { log.SaveError("No Account Found", "No Account Found"); } // bool chk = ol.Save(_trxName); } } else { log.SaveError("No OrderLines Found", "No OrderLines Found"); } } #region else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_SALESORDER.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_APCREDITMEMO.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_APINVOICE.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_APPAYMENT.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_ARCREDITMEMO.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_ARINVOICE.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_ARPROFORMAINVOICE.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_ARRECEIPT.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_BANKSTATEMENT.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_CASHJOURNAL.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_FIXASSET.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_GLDOCUMENT.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_GLJOURNAL.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_MATCHINVOICE.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_MATCHPO.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_MATERIALDELIVERY.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_MATERIALMOVEMENT.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_MATERIALPHYSICALINVENTORY.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_MATERIALPICK.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_MATERIALPRODUCTION.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_MATERIALPUTAWAY.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_MATERIALRECEIPT.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_MATERIALREPLENISHMENT.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_PAYMENTALLOCATION.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_PROJECTISSUE.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_PURCHASEREQUISITION.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_STANDARDCOSTUPDATE.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_WORKORDER.Equals(docBaseType)) { } else if (VAdvantage.Model.MDocBaseType.DOCBASETYPE_WORKORDERTRANSACTION.Equals(docBaseType)) { } #endregion }
/// <summary> /// Perform Process. /// </summary> /// <returns>Message (clear text)</returns> protected override String DoIt() { log.Info("C_Order_ID=" + _C_Order_ID + ", C_DocType_ID=" + _C_DocType_ID + ", CloseDocument=" + _IsCloseDocument); if (_C_Order_ID == 0) { throw new ArgumentException("No Order"); } VAdvantage.Model.MDocType dt = VAdvantage.Model.MDocType.Get(GetCtx(), _C_DocType_ID); if (dt.Get_ID() == 0) { throw new ArgumentException("No DocType"); } if (_DateDoc == null) { _DateDoc = Util.GetValueOfDateTime(DateTime.Now); } // VAdvantage.Model.MOrder from = new VAdvantage.Model.MOrder(GetCtx(), _C_Order_ID, Get_Trx()); if (from.GetDocStatus() != "DR" && from.GetDocStatus() != "IP" && from.GetDocStatus() != "CO") { throw new Exception("Order Closed"); } VAdvantage.Model.MOrder newOrder = VAdvantage.Model.MOrder.CopyFrom(from, _DateDoc, dt.GetC_DocType_ID(), false, true, null); // copy ASI newOrder.SetC_DocTypeTarget_ID(_C_DocType_ID); int C_Bpartner_ID = newOrder.GetC_BPartner_ID(); newOrder.Set_Value("IsSalesQuotation", false); // Added by Bharat on 05 Jan 2018 to set Values on Blanket Sales Order from Sales Quotation. if (dt.GetDocBaseType() == "BOO") { newOrder.Set_Value("IsBlanketTrx", true); } else // Added by Bharat on 29 March 2018 to set Blanket Order zero in case of Sales order Creation. { newOrder.SetC_Order_Blanket(0); } if (newOrder.Get_ColumnIndex("C_Order_Quotation") > 0) { newOrder.SetC_Order_Quotation(_C_Order_ID); } //Update New Order Refrence From Sales Qutation in Sales order newOrder.SetPOReference(Util.GetValueOfString(from.GetDocumentNo())); // Added by Bharat on 31 Jan 2018 to set Inco Term from Quotation if (newOrder.Get_ColumnIndex("C_IncoTerm_ID") > 0) { newOrder.SetC_IncoTerm_ID(from.GetC_IncoTerm_ID()); } String sqlbp = "update c_project set c_bpartner_id=" + C_Bpartner_ID + " where ref_order_id=" + _C_Order_ID + ""; int value = DB.ExecuteQuery(sqlbp, null, Get_Trx()); bool OK = newOrder.Save(); if (!OK) { //return GetReterivedError( newOrder, "Could not create new Order"); throw new Exception("Could not create new Order"); } if (OK) { string sql = "select C_Project_id from c_project where c_order_id = " + from.GetC_Order_ID(); int C_Project_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx())); if (C_Project_ID != 0) { VAdvantage.Model.X_C_Project project = new VAdvantage.Model.X_C_Project(GetCtx(), C_Project_ID, Get_Trx()); project.SetC_BPartner_ID(project.GetC_BPartnerSR_ID()); project.SetC_BPartnerSR_ID(0); if (!project.Save()) { log.SaveError("Error on " + project.Get_TableName(), ""); } } if (dt.GetDocBaseType() == "BOO") { from.SetC_Order_Blanket(newOrder.GetC_Order_ID()); } else { from.SetRef_Order_ID(newOrder.GetC_Order_ID()); } from.Save(); int bp = newOrder.GetC_BPartner_ID(); VAdvantage.Model.X_C_BPartner prosp = new VAdvantage.Model.X_C_BPartner(GetCtx(), bp, Get_Trx()); prosp.SetIsCustomer(true); prosp.SetIsProspect(false); if (!prosp.Save()) { log.SaveError("Error on " + prosp.Get_TableName(), ""); } } // if (_IsCloseDocument) { VAdvantage.Model.MOrder original = new VAdvantage.Model.MOrder(GetCtx(), _C_Order_ID, Get_Trx()); //Edited by Arpit Rai on 8th of Nov,2017 if (original.GetDocStatus() != "CO") //to check if document is already completed { original.SetDocAction(VAdvantage.Model.MOrder.DOCACTION_Complete); original.ProcessIt(VAdvantage.Model.MOrder.DOCACTION_Complete); original.Save(); } //Arpit original.SetDocAction(VAdvantage.Model.MOrder.DOCACTION_Close); original.ProcessIt(VAdvantage.Model.MOrder.DOCACTION_Close); original.Save(); } // return(Msg.GetMsg(GetCtx(), "OrderCreatedSuccessfully") + " - " + dt.GetName() + ": " + newOrder.GetDocumentNo()); }