/// <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> /// /// </summary> /// <param name="contSchedule"></param> private void GenerateInvoice(VAdvantage.Model.X_C_ContractSchedule contSchedule) { if (contSchedule.IsActive()) { int res = 0; sql = "select c_doctype_id from c_doctype where name = 'AR Invoice' and ad_client_id = " + GetCtx().GetAD_Client_ID(); int C_DocType_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); // sql = "select noofdays from c_frequency where c_frequency_id = " + cont.GetC_Frequency_ID(); // Decimal? days = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null)); Decimal?price = null; if (!cont.IsCancel()) { price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered()); } else { sql = "update c_contract set renewaltype = null where c_contract_id = " + cont.GetC_Contract_ID(); int res2 = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null)); if (contSchedule.GetEndDate() <= cont.GetCancellationDate()) { price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered()); } else { sql = "select max(c_contractschedule_id) from c_contractschedule where c_invoice_id is not null and c_contract_id = " + cont.GetC_Contract_ID(); int c_contractschedule_id = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); if (c_contractschedule_id != 0) { string date = cont.GetCancellationDate().Value.ToString("dd-MMM-yyyy"); // int contsch = Util.GetValueOfInt(contSchedule.GetC_ContractSchedule_ID()) - 1; sql = "select daysbetween('" + date + "', enddate) from c_contractschedule where c_contractschedule_id = " + c_contractschedule_id; Decimal?diffDays = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null)); // price = Decimal.Multiply(cont.GetPriceEntered(), diffDays.Value); price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered()); sql = "update c_contractschedule set isactive = 'N' where enddate > '" + date + "' and c_contract_id = " + cont.GetC_Contract_ID(); res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null)); } else { sql = "select daysbetween(cancellationdate, startdate) from c_contract where c_contract_id = " + cont.GetC_Contract_ID(); Decimal?diffDays = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null)); //price = Decimal.Multiply(Decimal.Divide(cont.GetPriceEntered(), days.Value), diffDays.Value); price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered()); if (diffDays > 0) { sql = "update c_contractschedule set isactive = 'N' where c_contract_id = " + cont.GetC_Contract_ID(); res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null)); } else { sql = "update c_contractschedule set isactive = 'N' where c_contract_id = " + cont.GetC_Contract_ID(); res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null)); } // sql = "update c_contractschedule set isactive = 'N' where enddate > '" + System.DateTime.Now + "' and c_contract_id = " + cont.GetC_Contract_ID(); } } } price = Decimal.Round(price.Value, 2, MidpointRounding.AwayFromZero); VAdvantage.Model.MInvoice inv = new VAdvantage.Model.MInvoice(GetCtx(), 0, null); inv.SetAD_Client_ID(GetCtx().GetAD_Client_ID()); inv.SetAD_Org_ID(GetCtx().GetAD_Org_ID()); inv.SetC_BPartner_ID(cont.GetC_BPartner_ID()); if (Util.GetValueOfInt(cont.GetC_Order_ID()) != 0) { inv.SetC_Order_ID(cont.GetC_Order_ID()); } inv.SetC_DocType_ID(C_DocType_ID); inv.SetC_DocTypeTarget_ID(C_DocType_ID); inv.SetC_BPartner_Location_ID(cont.GetBill_Location_ID()); inv.SetC_Currency_ID(cont.GetC_Currency_ID()); inv.SetC_PaymentTerm_ID(cont.GetC_PaymentTerm_ID()); inv.SetC_Campaign_ID(cont.GetC_Campaign_ID()); inv.SetIsSOTrx(true); inv.SetM_PriceList_ID(cont.GetM_PriceList_ID()); inv.SetSalesRep_ID(cont.GetSalesRep_ID()); inv.SetC_Contract_ID(cont.GetC_Contract_ID()); if (!inv.Save()) { } if (inv.GetC_Invoice_ID() != 0) { VAdvantage.Model.MInvoiceLine invLine = new VAdvantage.Model.MInvoiceLine(GetCtx(), 0, null); invLine.SetAD_Client_ID(inv.GetAD_Client_ID()); invLine.SetAD_Org_ID(inv.GetAD_Org_ID()); invLine.SetC_Campaign_ID(inv.GetC_Campaign_ID()); invLine.SetC_Invoice_ID(inv.GetC_Invoice_ID()); invLine.SetC_UOM_ID(cont.GetC_UOM_ID()); invLine.SetM_Product_ID(cont.GetM_Product_ID()); if (Util.GetValueOfInt(cont.GetC_OrderLine_ID()) != 0) { invLine.SetC_OrderLine_ID(cont.GetC_OrderLine_ID()); } invLine.SetC_Tax_ID(cont.GetC_Tax_ID()); invLine.SetQty(cont.GetQtyEntered()); invLine.SetQtyEntered(cont.GetQtyEntered()); // invLine.SetQtyInvoiced(1); // invLine.SetPrice(price.Value); invLine.SetPriceActual(cont.GetPriceEntered()); invLine.SetPriceEntered(cont.GetPriceEntered()); // invLine.SetPriceLimit(price); invLine.SetPriceList(cont.GetPriceEntered()); if (!invLine.Save()) { } } string comp = inv.CompleteIt(); inv.SetDocAction("CL"); inv.SetDocStatus("CO"); if (!inv.Save()) { } sql = "update c_invoice set c_contract_id = " + cont.GetC_Contract_ID() + " where c_invoice_id = " + inv.GetC_Invoice_ID(); res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null)); sql = "select sum(taxamt) from c_invoicetax where c_invoice_id = " + inv.GetC_Invoice_ID(); Decimal?taxAmt = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null)); sql = "update c_contractschedule set c_invoice_id = " + inv.GetC_Invoice_ID() + ", processed = 'Y' where c_contractschedule_id = " + contSchedule.GetC_ContractSchedule_ID(); // sql = "update c_contractschedule set c_invoice_id = " + inv.GetC_Invoice_ID() + ", processed = 'Y', TotalAmt = " + inv.GetTotalLines() + ", taxamt = " + taxAmt + ", grandtotal = " + inv.GetGrandTotal() + " where c_contractschedule_id = " + contSchedule.GetC_ContractSchedule_ID(); res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null)); } }
/// <summary> /// /// </summary> /// <param name="tLine"></param> private int GenerateInvoice(VAdvantage.Model.X_S_TimeExpenseLine tLine, VAdvantage.Model.X_S_TimeExpense tExp, bool IsExpense) { int C_PaymentTerm_ID = 0; VAdvantage.Model.X_C_Order ord = null; if (tLine.GetC_Order_ID() != 0) { ord = new VAdvantage.Model.X_C_Order(GetCtx(), tLine.GetC_Order_ID(), null); } sql = "select C_BPartner_Location_ID from c_Bpartner_Location where c_bpartner_ID = " + tLine.GetC_BPartner_ID(); int C_BPartner_Location_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); if (_DateInvoiced == null) { _DateInvoiced = System.DateTime.Now; } // X_C_Invoice inv = new X_C_Invoice(GetCtx(), 0, null); VAdvantage.Model.MInvoice inv = new VAdvantage.Model.MInvoice(GetCtx(), 0, null); inv.SetAD_Client_ID(GetCtx().GetAD_Client_ID()); inv.SetAD_Org_ID(GetCtx().GetAD_Org_ID()); inv.SetAD_User_ID(tExp.GetAD_User_ID()); inv.SetC_BPartner_ID(tLine.GetC_BPartner_ID()); inv.SetC_BPartner_Location_ID(C_BPartner_Location_ID); inv.SetC_Currency_ID(tLine.GetC_Currency_ID()); inv.SetDateInvoiced(_DateInvoiced); inv.SetM_PriceList_ID(tExp.GetM_PriceList_ID()); inv.SetDateAcct(_DateInvoiced); inv.SetIsApproved(true); //inv.SetPaymentRule(); if (!IsExpense) { inv.SetIsSOTrx(true); sql = "select C_DocType_ID from c_doctype where name = 'AR Invoice' and ad_client_id = " + GetCtx().GetAD_Client_ID(); int C_DocType_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); inv.SetC_DocType_ID(C_DocType_ID); inv.SetC_DocTypeTarget_ID(C_DocType_ID); } else { inv.SetIsSOTrx(false); sql = "select C_DocType_ID from c_doctype where docbasetype = 'API' and ad_client_id = " + GetCtx().GetAD_Client_ID(); int C_DocType_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); inv.SetC_DocType_ID(C_DocType_ID); inv.SetC_DocTypeTarget_ID(C_DocType_ID); } //inv.SetSalesRep_ID(); inv.SetDocAction("CO"); inv.SetDocStatus("DR"); if (ord != null) { inv.SetC_PaymentTerm_ID(ord.GetC_PaymentTerm_ID()); inv.SetSalesRep_ID(ord.GetSalesRep_ID()); inv.SetC_Order_ID(ord.GetC_Order_ID()); inv.SetM_PriceList_ID(ord.GetM_PriceList_ID()); } else { sql = " select c_paymentterm_id from c_bpartner where c_bpartner_id = " + tLine.GetC_BPartner_ID(); C_PaymentTerm_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); if (C_PaymentTerm_ID == 0) { sql = "select c_paymentterm_id from c_paymentterm where isdefault = 'Y' and ad_client_id= " + GetCtx().GetAD_Client_ID(); C_PaymentTerm_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null)); } sql = "select M_Pricelist_ID from m_Pricelist where issopricelist = 'Y' and isactive = 'Y'"; inv.SetM_PriceList_ID(Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null))); inv.SetC_PaymentTerm_ID(C_PaymentTerm_ID); inv.SetSalesRep_ID(tExp.GetSalesRep_ID()); } if (!inv.Save()) { log.SaveError("InvoiceNotSaved", "InvoiceNotSaved"); return(0); } return(inv.GetC_Invoice_ID()); }