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