} // prepare
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message (clear text)</returns>
protected override String DoIt()
{
int index = 1;
StringBuilder sql = new StringBuilder("SELECT * "
+ "FROM S_TimeExpense e "
+ "WHERE e.Processed='Y'"
+ " AND e.AD_Client_ID=@param1"); // #1
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
index++;
sql.Append(" AND e.C_BPartner_ID=@param2"); // #2
}
if (_DateFrom != null)
{
index++;
sql.Append(" AND e.DateReport >=@param3"); // #3
}
if (_DateTo != null)
{
index++;
sql.Append(" AND e.DateReport <=@param4"); // #4
}
// JID_0868
// chanegs done by Bharat on 12 September 2018 to handle the case if invoice is created with an expense for the selected Business Partner
sql.Append(" AND EXISTS (SELECT * FROM S_TimeExpenseLine el "
+ "WHERE e.S_TimeExpense_ID=el.S_TimeExpense_ID"
+ " AND el.C_InvoiceLine_ID IS NULL"
+ " AND el.ConvertedAmt<>0) "
+ "ORDER BY e.C_BPartner_ID, e.S_TimeExpense_ID");
//
int old_BPartner_ID = -1;
MInvoice invoice = null;
MTimeExpense te = null;
//
//PreparedStatement pstmt = null;
SqlParameter[] param = new SqlParameter[index];
IDataReader idr = null;
DataTable dt = null;
try
{
//pstmt = DataBase.prepareStatement (sql.toString (), get_TrxName());
int par = 0;
//pstmt.setInt(par++, getAD_Client_ID());
param[0] = new SqlParameter("@param1", GetAD_Client_ID());
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
par++;
//pstmt.setInt (par++, _C_BPartner_ID);
param[par] = new SqlParameter("@param2", _C_BPartner_ID);
}
if (_DateFrom != null)
{
par++;
//pstmt.setTimestamp (par++, _DateFrom);
param[par] = new SqlParameter("@param3", _DateFrom);
}
if (_DateTo != null)
{
par++;
//pstmt.setTimestamp (par++, _DateTo);
param[par] = new SqlParameter("@param4", _DateTo);
}
//ResultSet rs = pstmt.executeQuery ();
idr = DataBase.DB.ExecuteReader(sql.ToString(), param, Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
// ********* Expense Line Loop
{
te = new MTimeExpense(GetCtx(), dr, Get_TrxName());
// New BPartner - New Order
//
if (te.GetC_BPartner_ID() != old_BPartner_ID)
{
CompleteInvoice(invoice, te);
MBPartner bp = new MBPartner(GetCtx(), te.GetC_BPartner_ID(), Get_TrxName());
log.Info("New Invoice for " + bp);
invoice = new MInvoice(GetCtx(), 0, Get_TrxName());
invoice.SetBPartner(bp);
if (invoice.GetC_BPartner_Location_ID() == 0)
{
log.Log(Level.SEVERE, "No BP Location: " + bp);
AddLog(0, te.GetDateReport(),
null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName());
invoice = null;
break;
}
// Siddheshwar: added a code to check for payment method if null
if (bp.GetVA009_PO_PaymentMethod_ID() <= 0)
{
paymethod = GetPaymentMethod(te);
if (paymethod <= 0)
{
if (!noPayMethEmp.Contains(bp.GetC_BPartner_ID()))
{
noPayMethEmp.Add(bp.GetC_BPartner_ID());
if (string.IsNullOrEmpty(bpNameNoPM))
{
bpNameNoPM = bp.GetName();
}
else
{
bpNameNoPM += bp.GetName() + ", ";
}
return(Msg.GetMsg(GetCtx(), "NoPayMethEmp") + bpNameNoPM);
}
}
else
{
invoice.SetVA009_PaymentMethod_ID(paymethod);
}
}
else
{
//JID_1783_1 if active paymentMethod not found ,then dont create the invoice.
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM VA009_PaymentMethod WHERE VA009_PaymentMethod_ID=" + bp.GetVA009_PO_PaymentMethod_ID(), null, Get_Trx())).Equals("Y"))
{
invoice.SetVA009_PaymentMethod_ID(bp.GetVA009_PO_PaymentMethod_ID());
}
else
{
return(Msg.GetMsg(GetCtx(), "IsActivePaymentMethodInv"));;
}
}
// Checking payment term
if (bp.GetPO_PaymentTerm_ID() <= 0)
{
payterm = GetPaymentTerm(te);
if (payterm <= 0)
{
if (!PayTermEmp.Contains(bp.GetC_BPartner_ID()))
{
PayTermEmp.Add(bp.GetC_BPartner_ID());
if (string.IsNullOrEmpty(bpNamePT))
{
bpNamePT = bp.GetName();
}
else
{
bpNamePT += bp.GetName() + ", ";
}
return(Msg.GetMsg(GetCtx(), "NoPayTerm") + bpNamePT);
}
}
else
{
invoice.SetC_PaymentTerm_ID(payterm);
}
}
else
{
//JID_1783_1 if active paymentTerm not found ,then dont create the invoice.
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM C_PaymentTerm WHERE C_PaymentTerm_ID=" + bp.GetPO_PaymentTerm_ID(), null, Get_Trx())).Equals("Y"))
{
invoice.SetC_PaymentTerm_ID(bp.GetPO_PaymentTerm_ID());
}
else
{
return(Msg.GetMsg(GetCtx(), "IsActivePaymentTermInv"));
}
}
invoice.SetIsExpenseInvoice(true); //added by arpit asked by Surya Sir on DEC 28,2015
invoice.SetClientOrg(te.GetAD_Client_ID(), te.GetAD_Org_ID());
//invoice.SetVA009_PaymentMethod_ID(bp.GetVA009_PO_PaymentMethod_ID());
// JID_0868
// chanegs done by Bharat on 12 September 2018 to set the document type where Expense Invoice checkbox is true.
// String qry = "SELECT C_DocType_ID FROM C_DocType "
//+ "WHERE AD_Client_ID=@param1 AND DocBaseType=@param2"
//+ " AND IsActive='Y' AND IsExpenseInvoice = 'Y' "
//+ "ORDER BY C_DocType_ID DESC , IsDefault DESC";
String qry = "SELECT C_DocType_ID FROM C_DocType "
+ "WHERE AD_Client_ID=" + GetAD_Client_ID() + @" AND DocBaseType='" + MDocBaseType.DOCBASETYPE_APINVOICE + @"'"
+ " AND IsActive='Y' AND IsExpenseInvoice = 'Y' AND AD_Org_ID IN(0," + te.GetAD_Org_ID() + ") "
+ " ORDER BY AD_Org_ID Desc, C_DocType_ID DESC , IsDefault DESC";
//int C_DocType_ID = DB.GetSQLValue(null, qry, GetAD_Client_ID(), MDocBaseType.DOCBASETYPE_APINVOICE);
int C_DocType_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry));
if (C_DocType_ID <= 0)
{
log.Log(Level.SEVERE, "Not found for AC_Client_ID="
+ GetAD_Client_ID() + " - " + MDocBaseType.DOCBASETYPE_APINVOICE);
return(Msg.GetMsg(GetCtx(), "NoDocTypeExpInvoice"));
}
else
{
log.Fine(MDocBaseType.DOCBASETYPE_APINVOICE);
}
invoice.SetC_DocTypeTarget_ID(C_DocType_ID);
//invoice.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APINVOICE); // API
//commented by Arpit on Jan 4,2015 Mentis issue no. 0000310
//invoice.SetDocumentNo(te.GetDocumentNo());
//
invoice.SetM_PriceList_ID(te.GetM_PriceList_ID());
invoice.SetSalesRep_ID(te.GetDoc_User_ID());
String descr = Msg.Translate(GetCtx(), "S_TimeExpense_ID")
+ ": " + te.GetDocumentNo() + " "
+ DisplayType.GetDateFormat(DisplayType.Date).Format(te.GetDateReport());
invoice.SetDescription(descr);
if (!invoice.Save())
{
return(GetRetrievedError(invoice, "Cannot save Invoice"));
// new Exception("Cannot save Invoice");
}
//added by arpit asked by Surya Sir on 29/12/2015*******
else
{
old_BPartner_ID = bp.GetC_BPartner_ID();
}
//end***************
}
// JID_0868
//Description include all document numbers which is come from Time And Expense Recording window to expense invoice in case of multiple records
else if (old_BPartner_ID > 0)
{
String descr = invoice.GetDescription() + "\n" + Msg.Translate(GetCtx(), "S_TimeExpense_ID")
+ ": " + te.GetDocumentNo() + " "
+ DisplayType.GetDateFormat(DisplayType.Date).Format(te.GetDateReport());
invoice.SetDescription(descr);
}
MTimeExpenseLine[] tel = te.GetLines(false);
for (int i = 0; i < tel.Length; i++)
{
MTimeExpenseLine line = tel[i];
// Already Invoiced or nothing to be reimbursed
if (line.GetC_InvoiceLine_ID() != 0 ||
Env.ZERO.CompareTo(line.GetQtyReimbursed()) == 0 ||
Env.ZERO.CompareTo(line.GetPriceReimbursed()) == 0)
{
continue;
}
// Update Header info
if (line.GetC_Activity_ID() != 0 && line.GetC_Activity_ID() != invoice.GetC_Activity_ID())
{
invoice.SetC_Activity_ID(line.GetC_Activity_ID());
}
if (line.GetC_Campaign_ID() != 0 && line.GetC_Campaign_ID() != invoice.GetC_Campaign_ID())
{
invoice.SetC_Campaign_ID(line.GetC_Campaign_ID());
}
if (line.GetC_Project_ID() != 0 && line.GetC_Project_ID() != invoice.GetC_Project_ID())
{
invoice.SetC_Project_ID(line.GetC_Project_ID());
}
if (!invoice.Save())
{
return(GetRetrievedError(invoice, "Cannot save Invoice"));
//new Exception("Cannot save Invoice");
}
// Create OrderLine
MInvoiceLine il = new MInvoiceLine(invoice);
//
if (line.GetM_Product_ID() != 0)
{
il.SetM_Product_ID(line.GetM_Product_ID(), true);
}
//added by arpit asked by Surya Sir on 28/12/2015_____***************************
if (line.GetC_Charge_ID() != 0)
{
il.SetC_Charge_ID(line.GetC_Charge_ID());
}
//end here *****************************
il.SetQty(line.GetQtyReimbursed()); // Entered/Invoiced
il.SetDescription(line.GetDescription());
//
il.SetC_Project_ID(line.GetC_Project_ID());
il.SetC_ProjectPhase_ID(line.GetC_ProjectPhase_ID());
il.SetC_ProjectTask_ID(line.GetC_ProjectTask_ID());
il.SetC_Activity_ID(line.GetC_Activity_ID());
il.SetC_Campaign_ID(line.GetC_Campaign_ID());
//
// il.setPrice(); // not really a list/limit price for reimbursements
il.SetPrice(line.GetPriceReimbursed()); //
il.SetC_UOM_ID(line.GetC_UOM_ID());
// JID_0868
// chanegs done by Bharat on 12 September 2018 to set the Amount in List price column.
il.SetPriceList(line.GetPriceReimbursed());
il.SetTax();
if (!il.Save())
{
return(GetRetrievedError(il, "Cannot save Invoice"));
//new Exception("Cannot save Invoice Line");
}
// Update TEL
line.SetC_InvoiceLine_ID(il.GetC_InvoiceLine_ID());
line.SetIsInvoiced(true);
line.Save();
} // for all expense lines
}
}
else
{
message = Msg.GetMsg(GetCtx(), "NoRecForInv");
}
// ********* Expense Line Loop
dt = null;
//dt.Clear();
}
catch (Exception e)
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
finally
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
}
CompleteInvoice(invoice, te);
//if (_noInvoices == 0)
//{
// message = " @No Record Found for Invoice Creation@";
//}
//Code for Showing Message when PaymentTerm is null
if (!string.IsNullOrEmpty(bpNamePT))
{
message += Msg.GetMsg(GetCtx(), "NoPayTerm") + bpNamePT + "\n";
}
//code for showing Error Message when Invoice is not completed
if (!string.IsNullOrEmpty(bpNameInvoice))
{
message += Msg.GetMsg(GetCtx(), "NoInvoiceCreated") + bpNameInvoice + "\n";
}
if (!string.IsNullOrEmpty(bpNameNoPM))
{
message = Msg.GetMsg(GetCtx(), "NoPayMethEmp") + bpNameNoPM + "\n";
}
if (_noInvoices > 0)
{
message += "" + _noInvoices + " " + Msg.GetMsg(GetCtx(), "VIS_InvGenerated");
}
//return "" + _noInvoices + " @Invoices Generated Successfully@";
return(message);
} // doIt
} // prepare
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message to be translated</returns>
protected override String DoIt()
{
int index = 1;
StringBuilder sql = new StringBuilder("SELECT * FROM S_TimeExpenseLine el "
+ "WHERE el.AD_Client_ID=@param1" // #1
+ " AND el.C_BPartner_ID>0 AND el.IsInvoiced='Y'" // Business Partner && to be invoiced
+ " AND el.C_OrderLine_ID IS NULL" // not invoiced yet
+ " AND EXISTS (SELECT * FROM S_TimeExpense e " // processed only
+ "WHERE el.S_TimeExpense_ID=e.S_TimeExpense_ID AND e.Processed='Y')");
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
index++;
sql.Append(" AND el.C_BPartner_ID=@param2"); // #2
}
if (_DateFrom != null || _DateTo != null)
{
sql.Append(" AND EXISTS (SELECT * FROM S_TimeExpense e "
+ "WHERE el.S_TimeExpense_ID=e.S_TimeExpense_ID");
if (_DateFrom != null)
{
index++;
sql.Append(" AND e.DateReport >= @param3"); // #3
}
if (_DateTo != null)
{
index++;
sql.Append(" AND e.DateReport <= @param4"); // #4
}
sql.Append(")");
}
sql.Append(" ORDER BY el.C_BPartner_ID, el.C_Project_ID, el.S_TimeExpense_ID, el.Line");
//
MBPartner oldBPartner = null;
int old_Project_ID = -1;
MTimeExpense te = null;
//
SqlParameter[] param = new SqlParameter[index];
IDataReader idr = null;
DataTable dt = null;
try
{
//pstmt = DataBase.prepareStatement(sql.toString(), get_TrxName());
int par = 0;
//pstmt.setInt(par++, getAD_Client_ID());
param[par] = new SqlParameter("@param1", GetAD_Client_ID());
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
//pstmt.setInt(par++, _C_BPartner_ID);
par++;
param[par] = new SqlParameter("@param2", _C_BPartner_ID);
}
if (_DateFrom != null)
{
par++;
//pstmt.setTimestamp(par++, _DateFrom);
param[par] = new SqlParameter("@param3", _DateFrom);
}
if (_DateTo != null)
{
//pstmt.setTimestamp(par++, _DateTo);
par++;
param[par] = new SqlParameter("@param4", _DateTo);
}
//ResultSet rs = pstmt.executeQuery();
idr = DataBase.DB.ExecuteReader(sql.ToString(), param, Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows) // ********* Expense Line Loop
{
MTimeExpenseLine tel = new MTimeExpenseLine(GetCtx(), dr, Get_TrxName());
if (!tel.IsInvoiced())
{
continue;
}
// New BPartner - New Order
if (oldBPartner == null ||
oldBPartner.GetC_BPartner_ID() != tel.GetC_BPartner_ID())
{
CompleteOrder();
oldBPartner = new MBPartner(GetCtx(), tel.GetC_BPartner_ID(), Get_TrxName());
}
// New Project - New Order
if (old_Project_ID != tel.GetC_Project_ID())
{
CompleteOrder();
old_Project_ID = tel.GetC_Project_ID();
}
if (te == null || te.GetS_TimeExpense_ID() != tel.GetS_TimeExpense_ID())
{
te = new MTimeExpense(GetCtx(), tel.GetS_TimeExpense_ID(), Get_TrxName());
}
//
ProcessLine(te, tel, oldBPartner);
}
// ********* Expense Line Loop
dt = null;
// dt.Clear();
}
catch (Exception e)
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
finally
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
}
CompleteOrder();
return("" + _noOrders + "@Invoices Generated Successfully@");
} // doIt
} // doIt
/// <summary>
/// Process Expense Line
/// </summary>
/// <param name="te">header</param>
/// <param name="tel">line</param>
/// <param name="bp">bp</param>
private void ProcessLine(MTimeExpense te, MTimeExpenseLine tel, MBPartner bp)
{
if (_order == null)
{
log.Info("New Order for " + bp + ", Project=" + tel.GetC_Project_ID());
_order = new MOrder(GetCtx(), 0, Get_TrxName());
_order.SetAD_Org_ID(tel.GetAD_Org_ID());
_order.SetC_DocTypeTarget_ID(MOrder.DocSubTypeSO_Standard);
//
_order.SetBPartner(bp);
if (_order.GetC_BPartner_Location_ID() == 0)
{
log.Log(Level.SEVERE, "No BP Location: " + bp);
AddLog(0, te.GetDateReport(),
null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName());
_order = null;
return;
}
_order.SetM_Warehouse_ID(te.GetM_Warehouse_ID());
////Added By Arpit asked by Surya Sir..................29-12-2015
//_order.SetSalesRep_ID(GetCtx().GetAD_User_ID());
//End
if (tel.GetC_Activity_ID() != 0)
{
_order.SetC_Activity_ID(tel.GetC_Activity_ID());
}
if (tel.GetC_Campaign_ID() != 0)
{
_order.SetC_Campaign_ID(tel.GetC_Campaign_ID());
}
if (tel.GetC_Project_ID() != 0)
{
_order.SetC_Project_ID(tel.GetC_Project_ID());
// Optionally Overwrite BP Price list from Project
MProject project = new MProject(GetCtx(), tel.GetC_Project_ID(), Get_TrxName());
if (project.GetM_PriceList_ID() != 0)
{
_order.SetM_PriceList_ID(project.GetM_PriceList_ID());
}
}
_order.SetSalesRep_ID(te.GetDoc_User_ID());
//
if (!_order.Save())
{
throw new Exception("Cannot save Order");
}
}
else
{
// Update Header info
if (tel.GetC_Activity_ID() != 0 && tel.GetC_Activity_ID() != _order.GetC_Activity_ID())
{
_order.SetC_Activity_ID(tel.GetC_Activity_ID());
}
if (tel.GetC_Campaign_ID() != 0 && tel.GetC_Campaign_ID() != _order.GetC_Campaign_ID())
{
_order.SetC_Campaign_ID(tel.GetC_Campaign_ID());
}
if (!_order.Save())
{
new Exception("Cannot save Order");
}
}
// OrderLine
MOrderLine ol = new MOrderLine(_order);
//
if (tel.GetM_Product_ID() != 0)
{
ol.SetM_Product_ID(tel.GetM_Product_ID(),
tel.GetC_UOM_ID());
}
if (tel.GetS_ResourceAssignment_ID() != 0)
{
ol.SetS_ResourceAssignment_ID(tel.GetS_ResourceAssignment_ID());
}
// Set charge ID
if (tel.GetC_Charge_ID() != 0)
{
ol.SetC_Charge_ID(tel.GetC_Charge_ID());
ol.SetPriceActual(tel.GetExpenseAmt());
ol.SetQty(tel.GetQty());
}
ol.SetQty(tel.GetQtyInvoiced()); //
ol.SetDescription(tel.GetDescription());
//
ol.SetC_Project_ID(tel.GetC_Project_ID());
ol.SetC_ProjectPhase_ID(tel.GetC_ProjectPhase_ID());
ol.SetC_ProjectTask_ID(tel.GetC_ProjectTask_ID());
ol.SetC_Activity_ID(tel.GetC_Activity_ID());
ol.SetC_Campaign_ID(tel.GetC_Campaign_ID());
//
Decimal price = tel.GetPriceInvoiced(); //
if (price.CompareTo(Env.ZERO) != 0)
{
if (tel.GetC_Currency_ID() != _order.GetC_Currency_ID())
{
price = MConversionRate.Convert(GetCtx(), price,
tel.GetC_Currency_ID(), _order.GetC_Currency_ID(),
_order.GetAD_Client_ID(), _order.GetAD_Org_ID());
}
ol.SetPrice(price);
}
else
{
ol.SetPrice();
}
if (tel.GetC_UOM_ID() != 0 && ol.GetC_UOM_ID() == 0)
{
ol.SetC_UOM_ID(tel.GetC_UOM_ID());
}
ol.SetTax();
if (!ol.Save())
{
throw new Exception("Cannot save Order Line");
}
// Update TimeExpense Line
tel.SetC_OrderLine_ID(ol.GetC_OrderLine_ID());
if (tel.Save())
{
log.Fine("Updated " + tel + " with C_OrderLine_ID");
}
else
{
log.Log(Level.SEVERE, "Not Updated " + tel + " with C_OrderLine_ID");
}
} // processLine
} // doIt
/// <summary>
/// Process Expense Line
/// </summary>
/// <param name="te">header</param>
/// <param name="tel">line</param>
/// <param name="bp">bp</param>
private void ProcessLine(MTimeExpense te, MTimeExpenseLine tel, MBPartner bp)
{
if (_order == null)
{
log.Info("New Order for " + bp + ", Project=" + tel.GetC_Project_ID());
_order = new MOrder(GetCtx(), 0, Get_TrxName());
_order.SetAD_Org_ID(tel.GetAD_Org_ID());
_order.SetC_DocTypeTarget_ID(MOrder.DocSubTypeSO_Standard);
//
_order.SetBPartner(bp);
if (_order.GetC_BPartner_Location_ID() == 0)
{
log.Log(Level.SEVERE, "No BP Location: " + bp);
AddLog(0, te.GetDateReport(),
null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName());
_order = null;
return;
}
_order.SetM_Warehouse_ID(te.GetM_Warehouse_ID());
//Bhupendra: Add payment term
// to check for if payment term is null
if (bp.GetC_PaymentTerm_ID() == 0)
{
// set the default payment method as check
int payTerm = GetPaymentTerm();
if (payTerm <= 0)
{
message = Msg.GetMsg(GetCtx(), "IsActivePaymentTerm");
return;
}
else
{
_order.SetC_PaymentTerm_ID(payTerm);
}
}
else
{
//check weather paymentterm is active or not
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM C_PaymentTerm WHERE C_PaymentTerm_ID=" + bp.GetC_PaymentTerm_ID(), null, Get_Trx())).Equals("Y"))
{
_order.SetC_PaymentTerm_ID(bp.GetC_PaymentTerm_ID());
}
else
{
message = Msg.GetMsg(GetCtx(), "IsActivePaymentTerm");
return;
}
}
// Bhupendra: added a cond to check for payment method if null
// Added by mohit - to set payment method and sales rep id.
if (bp.GetVA009_PaymentMethod_ID() == 0)
{
// set the default payment method as check
int paymethod = GetPaymentMethod();
if (paymethod <= 0)
{
message = Msg.GetMsg(GetCtx(), "IsActivePaymentMethod");
return;
}
else
{
_order.SetVA009_PaymentMethod_ID(paymethod);
}
}
else
{
//check weather the PaymentMethod is active or not
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM VA009_PaymentMethod WHERE VA009_PaymentMethod_ID=" + bp.GetVA009_PaymentMethod_ID(), null, Get_Trx())).Equals("Y"))
{
_order.SetVA009_PaymentMethod_ID(bp.GetVA009_PaymentMethod_ID());
}
else
{
message = Msg.GetMsg(GetCtx(), "IsActivePaymentMethod");
return;
}
}
_order.SetSalesRep_ID(te.GetDoc_User_ID());
////Added By Arpit asked by Surya Sir..................29-12-2015
//_order.SetSalesRep_ID(GetCtx().GetAD_User_ID());
//End
if (tel.GetC_Activity_ID() != 0)
{
_order.SetC_Activity_ID(tel.GetC_Activity_ID());
}
if (tel.GetC_Campaign_ID() != 0)
{
_order.SetC_Campaign_ID(tel.GetC_Campaign_ID());
}
if (tel.GetC_Project_ID() != 0)
{
_order.SetC_Project_ID(tel.GetC_Project_ID());
// Optionally Overwrite BP Price list from Project
MProject project = new MProject(GetCtx(), tel.GetC_Project_ID(), Get_TrxName());
if (project.GetM_PriceList_ID() != 0)
{
//check weather the PriceList is active or not
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM M_PriceList WHERE M_PriceList_ID=" + project.GetM_PriceList_ID(), null, Get_Trx())).Equals("Y"))
{
_order.SetM_PriceList_ID(project.GetM_PriceList_ID());
}
else
{
message = Msg.GetMsg(GetCtx(), "IsActivePriceList");
return;
}
}
}
else
{
if (bp.GetM_PriceList_ID() != 0)
{
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM M_PriceList WHERE M_PriceList_ID=" + bp.GetM_PriceList_ID(), null, Get_Trx())).Equals("Y"))
{
_order.SetM_PriceList_ID(bp.GetM_PriceList_ID());
}
else
{
message = Msg.GetMsg(GetCtx(), "IsActivePriceList");
return;
}
}
}
_order.SetSalesRep_ID(te.GetDoc_User_ID());
//
if (!_order.Save())
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
if (pp != null)
{
message = pp.GetName();
//if GetName is Empty then it will check GetValue
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg("", pp.GetValue());
}
}
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg(GetCtx(), "CantSaveOrder");
}
return;
//throw new Exception("Cannot save Order");
}
}
else
{
// Update Header info
if (tel.GetC_Activity_ID() != 0 && tel.GetC_Activity_ID() != _order.GetC_Activity_ID())
{
_order.SetC_Activity_ID(tel.GetC_Activity_ID());
}
if (tel.GetC_Campaign_ID() != 0 && tel.GetC_Campaign_ID() != _order.GetC_Campaign_ID())
{
_order.SetC_Campaign_ID(tel.GetC_Campaign_ID());
}
if (!_order.Save())
{
Rollback();
//get error message from ValueNamePair
ValueNamePair pp = VLogger.RetrieveError();
if (pp != null)
{
message = pp.GetName();
//if GetName is Empty then it will check GetValue
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg("", pp.GetValue());
}
}
//it will check message is null or not
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg(GetCtx(), "CantSaveOrder");
}
return;
//new Exception("Cannot save Order");
}
}
// OrderLine
MOrderLine ol = new MOrderLine(_order);
//
if (tel.GetM_Product_ID() != 0)
{
ol.SetM_Product_ID(tel.GetM_Product_ID(),
tel.GetC_UOM_ID());
}
if (tel.GetS_ResourceAssignment_ID() != 0)
{
ol.SetS_ResourceAssignment_ID(tel.GetS_ResourceAssignment_ID());
}
// Set charge ID
if (tel.GetC_Charge_ID() != 0)
{
ol.SetC_Charge_ID(tel.GetC_Charge_ID());
ol.SetPriceActual(tel.GetExpenseAmt());
ol.SetQty(tel.GetQty());
}
ol.SetQty(tel.GetQtyInvoiced()); //
ol.SetDescription(tel.GetDescription());
//
ol.SetC_Project_ID(tel.GetC_Project_ID());
ol.SetC_ProjectPhase_ID(tel.GetC_ProjectPhase_ID());
ol.SetC_ProjectTask_ID(tel.GetC_ProjectTask_ID());
ol.SetC_Activity_ID(tel.GetC_Activity_ID());
ol.SetC_Campaign_ID(tel.GetC_Campaign_ID());
//
Decimal price = tel.GetPriceInvoiced(); //
if (price.CompareTo(Env.ZERO) != 0)
{
if (tel.GetC_Currency_ID() != _order.GetC_Currency_ID())
{
price = MConversionRate.Convert(GetCtx(), price,
tel.GetC_Currency_ID(), _order.GetC_Currency_ID(),
_order.GetAD_Client_ID(), _order.GetAD_Org_ID());
}
ol.SetPrice(price);
// added by Bhupendra to set the entered price
ol.SetPriceEntered(price);
}
else
{
ol.SetPrice();
}
if (tel.GetC_UOM_ID() != 0 && ol.GetC_UOM_ID() == 0)
{
ol.SetC_UOM_ID(tel.GetC_UOM_ID());
}
ol.SetTax();
if (!ol.Save())
{
Rollback();
//get error message from ValueNamePair
ValueNamePair pp = VLogger.RetrieveError();
if (pp != null)
{
message = pp.GetName();
//if GetName is Empty then it will check GetValue
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg("", pp.GetValue());
}
}
//it will check message is null or not
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg(GetCtx(), "CantSaveOrderLine");
}
return;
//throw new Exception("Cannot save Order Line");
}
// Update TimeExpense Line
tel.SetC_OrderLine_ID(ol.GetC_OrderLine_ID());
if (tel.Save())
{
log.Fine("Updated " + tel + " with C_OrderLine_ID");
}
else
{
log.Log(Level.SEVERE, "Not Updated " + tel + " with C_OrderLine_ID");
}
} // processLine
} // prepare
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message (clear text)</returns>
protected override String DoIt()
{
int index = 1;
StringBuilder sql = new StringBuilder("SELECT * "
+ "FROM S_TimeExpense e "
+ "WHERE e.Processed='Y'"
+ " AND e.AD_Client_ID=@param1"); // #1
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
index++;
sql.Append(" AND e.C_BPartner_ID=@param2"); // #2
}
if (_DateFrom != null)
{
index++;
sql.Append(" AND e.DateReport >=@param3"); // #3
}
if (_DateTo != null)
{
index++;
sql.Append(" AND e.DateReport <=@param4"); // #4
}
// JID_0868
// chanegs done by Bharat on 12 September 2018 to handle the case if invoice is created with an expense for the selected Business Partner
sql.Append(" AND EXISTS (SELECT * FROM S_TimeExpenseLine el "
+ "WHERE e.S_TimeExpense_ID=el.S_TimeExpense_ID"
+ " AND el.C_InvoiceLine_ID IS NULL"
+ " AND el.ConvertedAmt<>0) "
+ "ORDER BY e.C_BPartner_ID, e.S_TimeExpense_ID");
//
int old_BPartner_ID = -1;
MInvoice invoice = null;
//
//PreparedStatement pstmt = null;
SqlParameter[] param = new SqlParameter[index];
IDataReader idr = null;
DataTable dt = null;
try
{
//pstmt = DataBase.prepareStatement (sql.toString (), get_TrxName());
int par = 0;
//pstmt.setInt(par++, getAD_Client_ID());
param[0] = new SqlParameter("@param1", GetAD_Client_ID());
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
par++;
//pstmt.setInt (par++, _C_BPartner_ID);
param[par] = new SqlParameter("@param2", _C_BPartner_ID);
}
if (_DateFrom != null)
{
par++;
//pstmt.setTimestamp (par++, _DateFrom);
param[par] = new SqlParameter("@param3", _DateFrom);
}
if (_DateTo != null)
{
par++;
//pstmt.setTimestamp (par++, _DateTo);
param[par] = new SqlParameter("@param4", _DateTo);
}
//ResultSet rs = pstmt.executeQuery ();
idr = DataBase.DB.ExecuteReader(sql.ToString(), param, Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
// ********* Expense Line Loop
{
MTimeExpense te = new MTimeExpense(GetCtx(), dr, Get_TrxName());
// New BPartner - New Order
if (te.GetC_BPartner_ID() != old_BPartner_ID)
{
CompleteInvoice(invoice);
MBPartner bp = new MBPartner(GetCtx(), te.GetC_BPartner_ID(), Get_TrxName());
//
log.Info("New Invoice for " + bp);
invoice = new MInvoice(GetCtx(), 0, null);
invoice.SetIsExpenseInvoice(true); //added by arpit asked by Surya Sir on DEC 28,2015
invoice.SetClientOrg(te.GetAD_Client_ID(), te.GetAD_Org_ID());
// JID_0868
// chanegs done by Bharat on 12 September 2018 to set the document type where Expense Invoice checkbox is true.
String qry = "SELECT C_DocType_ID FROM C_DocType "
+ "WHERE AD_Client_ID=@param1 AND DocBaseType=@param2"
+ " AND IsActive='Y' AND IsExpenseInvoice = 'Y' "
+ "ORDER BY C_DocType_ID DESC , IsDefault DESC";
int C_DocType_ID = DB.GetSQLValue(null, qry, GetAD_Client_ID(), MDocBaseType.DOCBASETYPE_APINVOICE);
if (C_DocType_ID <= 0)
{
log.Log(Level.SEVERE, "Not found for AC_Client_ID="
+ GetAD_Client_ID() + " - " + MDocBaseType.DOCBASETYPE_APINVOICE);
}
else
{
log.Fine(MDocBaseType.DOCBASETYPE_APINVOICE);
}
invoice.SetC_DocTypeTarget_ID(C_DocType_ID);
//invoice.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APINVOICE); // API
//commented by Arpit on Jan 4,2015 Mentis issue no. 0000310
//invoice.SetDocumentNo(te.GetDocumentNo());
//
invoice.SetBPartner(bp);
if (invoice.GetC_BPartner_Location_ID() == 0)
{
log.Log(Level.SEVERE, "No BP Location: " + bp);
AddLog(0, te.GetDateReport(),
null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName());
invoice = null;
break;
}
invoice.SetM_PriceList_ID(te.GetM_PriceList_ID());
invoice.SetSalesRep_ID(te.GetDoc_User_ID());
String descr = Msg.Translate(GetCtx(), "S_TimeExpense_ID")
+ ": " + te.GetDocumentNo() + " "
+ DisplayType.GetDateFormat(DisplayType.Date).Format(te.GetDateReport());
invoice.SetDescription(descr);
if (!invoice.Save())
{
//return GetReterivedError(invoice, "Cannot save Invoice");
new Exception("Cannot save Invoice");
}
//added by arpit asked by Surya Sir on 29/12/2015*******
else
{
old_BPartner_ID = bp.GetC_BPartner_ID();
}
//end***************
}
// JID_0868
//Description include all document numbers which is come from Time And Expense Recording window to expense invoice in case of multiple records
else if (old_BPartner_ID > 0)
{
String descr = invoice.GetDescription() + "\n" + Msg.Translate(GetCtx(), "S_TimeExpense_ID")
+ ": " + te.GetDocumentNo() + " "
+ DisplayType.GetDateFormat(DisplayType.Date).Format(te.GetDateReport());
invoice.SetDescription(descr);
}
MTimeExpenseLine[] tel = te.GetLines(false);
for (int i = 0; i < tel.Length; i++)
{
MTimeExpenseLine line = tel[i];
// Already Invoiced or nothing to be reimbursed
if (line.GetC_InvoiceLine_ID() != 0 ||
Env.ZERO.CompareTo(line.GetQtyReimbursed()) == 0 ||
Env.ZERO.CompareTo(line.GetPriceReimbursed()) == 0)
{
continue;
}
// Update Header info
if (line.GetC_Activity_ID() != 0 && line.GetC_Activity_ID() != invoice.GetC_Activity_ID())
{
invoice.SetC_Activity_ID(line.GetC_Activity_ID());
}
if (line.GetC_Campaign_ID() != 0 && line.GetC_Campaign_ID() != invoice.GetC_Campaign_ID())
{
invoice.SetC_Campaign_ID(line.GetC_Campaign_ID());
}
if (line.GetC_Project_ID() != 0 && line.GetC_Project_ID() != invoice.GetC_Project_ID())
{
invoice.SetC_Project_ID(line.GetC_Project_ID());
}
if (!invoice.Save())
{
//return GetReterivedError(invoice, "Cannot save Invoice");
new Exception("Cannot save Invoice");
}
// Create OrderLine
MInvoiceLine il = new MInvoiceLine(invoice);
//
if (line.GetM_Product_ID() != 0)
{
il.SetM_Product_ID(line.GetM_Product_ID(), true);
}
//added by arpit asked by Surya Sir on 28/12/2015_____***************************
if (line.GetC_Charge_ID() != 0)
{
il.SetC_Charge_ID(line.GetC_Charge_ID());
}
//end here *****************************
il.SetQty(line.GetQtyReimbursed()); // Entered/Invoiced
il.SetDescription(line.GetDescription());
//
il.SetC_Project_ID(line.GetC_Project_ID());
il.SetC_ProjectPhase_ID(line.GetC_ProjectPhase_ID());
il.SetC_ProjectTask_ID(line.GetC_ProjectTask_ID());
il.SetC_Activity_ID(line.GetC_Activity_ID());
il.SetC_Campaign_ID(line.GetC_Campaign_ID());
//
// il.setPrice(); // not really a list/limit price for reimbursements
il.SetPrice(line.GetPriceReimbursed()); //
// JID_0868
// chanegs done by Bharat on 12 September 2018 to set the Amount in List price column.
il.SetPriceList(line.GetPriceReimbursed());
il.SetTax();
if (!il.Save())
{
//return GetReterivedError(il, "Cannot save Invoice");
new Exception("Cannot save Invoice Line");
}
// Update TEL
line.SetC_InvoiceLine_ID(il.GetC_InvoiceLine_ID());
line.SetIsInvoiced(true);
line.Save();
} // for all expense lines
} // ********* Expense Line Loop
dt = null;
//dt.Clear();
}
catch (Exception e)
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
finally
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
}
CompleteInvoice(invoice);
if (_noInvoices == 0)
{
return(" @No Record Found for Invoice Creation@");
}
return("" + _noInvoices + " @Invoices Generated Successfully@");
} // doIt
} // prepare
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message (clear text)</returns>
protected override String DoIt()
{
int index = 1;
StringBuilder sql = new StringBuilder("SELECT * "
+ "FROM S_TimeExpense e "
+ "WHERE e.Processed='Y'"
+ " AND e.AD_Client_ID=@param1"); // #1
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
index++;
sql.Append(" AND e.C_BPartner_ID=@param2"); // #2
}
if (_DateFrom != null)
{
index++;
sql.Append(" AND e.DateReport >=@param3"); // #3
}
if (_DateTo != null)
{
index++;
sql.Append(" AND e.DateReport <=@param4"); // #4
sql.Append(" AND EXISTS (SELECT * FROM S_TimeExpenseLine el "
+ "WHERE e.S_TimeExpense_ID=el.S_TimeExpense_ID"
+ " AND el.C_InvoiceLine_ID IS NULL"
+ " AND el.ConvertedAmt<>0) "
+ "ORDER BY e.C_BPartner_ID, e.S_TimeExpense_ID");
}
//
int old_BPartner_ID = -1;
MInvoice invoice = null;
//
//PreparedStatement pstmt = null;
SqlParameter[] param = new SqlParameter[index];
IDataReader idr = null;
DataTable dt = null;
try
{
//pstmt = DataBase.prepareStatement (sql.toString (), get_TrxName());
int par = 0;
//pstmt.setInt(par++, getAD_Client_ID());
param[0] = new SqlParameter("@param1", GetAD_Client_ID());
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
par++;
//pstmt.setInt (par++, _C_BPartner_ID);
param[par] = new SqlParameter("@param2", _C_BPartner_ID);
}
if (_DateFrom != null)
{
par++;
//pstmt.setTimestamp (par++, _DateFrom);
param[par] = new SqlParameter("@param3", _DateFrom);
}
if (_DateTo != null)
{
par++;
//pstmt.setTimestamp (par++, _DateTo);
param[par] = new SqlParameter("@param4", _DateTo);
}
//ResultSet rs = pstmt.executeQuery ();
idr = DataBase.DB.ExecuteReader(sql.ToString(), param, Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
// ********* Expense Line Loop
{
MTimeExpense te = new MTimeExpense(GetCtx(), dr, Get_TrxName());
// New BPartner - New Order
if (te.GetC_BPartner_ID() != old_BPartner_ID)
{
CompleteInvoice(invoice);
MBPartner bp = new MBPartner(GetCtx(), te.GetC_BPartner_ID(), Get_TrxName());
//
log.Info("New Invoice for " + bp);
invoice = new MInvoice(GetCtx(), 0, null);
invoice.SetClientOrg(te.GetAD_Client_ID(), te.GetAD_Org_ID());
invoice.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APINVOICE); // API
invoice.SetDocumentNo(te.GetDocumentNo());
//
invoice.SetBPartner(bp);
if (invoice.GetC_BPartner_Location_ID() == 0)
{
log.Log(Level.SEVERE, "No BP Location: " + bp);
AddLog(0, te.GetDateReport(),
null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName());
invoice = null;
break;
}
invoice.SetM_PriceList_ID(te.GetM_PriceList_ID());
invoice.SetSalesRep_ID(te.GetDoc_User_ID());
String descr = Msg.Translate(GetCtx(), "S_TimeExpense_ID")
+ ": " + te.GetDocumentNo() + " "
+ DisplayType.GetDateFormat(DisplayType.Date).Format(te.GetDateReport());
invoice.SetDescription(descr);
if (!invoice.Save())
{
new Exception("Cannot save Invoice");
}
old_BPartner_ID = bp.GetC_BPartner_ID();
}
MTimeExpenseLine[] tel = te.GetLines(false);
for (int i = 0; i < tel.Length; i++)
{
MTimeExpenseLine line = tel[i];
// Already Invoiced or nothing to be reimbursed
if (line.GetC_InvoiceLine_ID() != 0 ||
Env.ZERO.CompareTo(line.GetQtyReimbursed()) == 0 ||
Env.ZERO.CompareTo(line.GetPriceReimbursed()) == 0)
{
continue;
}
// Update Header info
if (line.GetC_Activity_ID() != 0 && line.GetC_Activity_ID() != invoice.GetC_Activity_ID())
{
invoice.SetC_Activity_ID(line.GetC_Activity_ID());
}
if (line.GetC_Campaign_ID() != 0 && line.GetC_Campaign_ID() != invoice.GetC_Campaign_ID())
{
invoice.SetC_Campaign_ID(line.GetC_Campaign_ID());
}
if (line.GetC_Project_ID() != 0 && line.GetC_Project_ID() != invoice.GetC_Project_ID())
{
invoice.SetC_Project_ID(line.GetC_Project_ID());
}
if (!invoice.Save())
{
new Exception("Cannot save Invoice");
}
// Create OrderLine
MInvoiceLine il = new MInvoiceLine(invoice);
//
if (line.GetM_Product_ID() != 0)
{
il.SetM_Product_ID(line.GetM_Product_ID(), true);
}
il.SetQty(line.GetQtyReimbursed()); // Entered/Invoiced
il.SetDescription(line.GetDescription());
//
il.SetC_Project_ID(line.GetC_Project_ID());
il.SetC_ProjectPhase_ID(line.GetC_ProjectPhase_ID());
il.SetC_ProjectTask_ID(line.GetC_ProjectTask_ID());
il.SetC_Activity_ID(line.GetC_Activity_ID());
il.SetC_Campaign_ID(line.GetC_Campaign_ID());
//
// il.setPrice(); // not really a list/limit price for reimbursements
il.SetPrice(line.GetPriceReimbursed()); //
il.SetTax();
if (!il.Save())
{
new Exception("Cannot save Invoice Line");
}
// Update TEL
line.SetC_InvoiceLine_ID(il.GetC_InvoiceLine_ID());
line.SetIsInvoiced(true);
line.Save();
} // for all expense lines
} // ********* Expense Line Loop
dt = null;
//dt.Clear();
}
catch (Exception e)
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
finally
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
}
CompleteInvoice(invoice);
if (_noInvoices == 0)
{
return(" @No Record Found for Invoice Creation@");
}
return("" + _noInvoices + " @Invoices Generated Successfully@");
} // doIt