} // 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
