1 14 package org.compiere.process; 15 16 import java.sql.*; 17 import java.math.*; 18 import java.net.*; 19 20 import org.compiere.model.*; 21 import org.compiere.util.DB; 22 import org.compiere.util.Env; 23 24 30 public class ExpenseAPInvoice extends SvrProcess 31 { 32 35 public ExpenseAPInvoice() 36 { 37 super(); 38 log.info("ExpenseAPInvoice-Start"); 39 } 41 private int m_C_BPartner_ID = 0; 42 private Timestamp m_DateFrom = null; 43 private Timestamp m_DateTo = null; 44 private int m_noInvoices = 0; 45 46 49 protected void prepare() 50 { 51 ProcessInfoParameter[] para = getParameter(); 52 for (int i = 0; i < para.length; i++) 53 { 54 String name = para[i].getParameterName(); 55 if (para[i].getParameter() == null) 56 ; 57 else if (name.equals("C_BPartner_ID")) 58 m_C_BPartner_ID = ((BigDecimal)para[i].getParameter()).intValue(); 59 else if (name.equals("DateReport")) 60 { 61 m_DateFrom = (Timestamp)para[i].getParameter(); 62 m_DateTo = (Timestamp)para[i].getParameter_To(); 63 } 64 else 65 log.error("prepare - Unknown Parameter: " + name); 66 } 67 } 69 70 75 protected String doIt() throws java.lang.Exception 76 { 77 StringBuffer sql = new StringBuffer ("SELECT e.AD_Client_ID, e.AD_Org_ID," 78 + "e.C_BPartner_ID, e.M_Warehouse_ID," 79 + "el.C_Project_ID, el.C_Activity_ID, el.C_Campaign_ID," 80 + "el.M_Product_ID, el.S_ResourceAssignment_ID, el.C_UOM_ID," 81 + "el.Qty, el.Description, el.Note," 82 + "el.ExpenseAmt, el.C_Currency_ID, el.ConvertedAmt, " 83 + "e.DocumentNo, el.S_TimeExpenseLine_ID " 84 + "FROM S_TimeExpense e" 85 + " INNER JOIN S_TimeExpenseLine el ON (e.S_TimeExpense_ID=el.S_TimeExpense_ID) " 86 + "WHERE el.C_InvoiceLine_ID IS NULL" 87 + " AND el.ConvertedAmt<>0 AND e.Processed='Y'" 88 + " AND e.AD_Client_ID=?"); if (m_C_BPartner_ID != 0) 90 sql.append(" AND e.C_BPartner_ID=?"); if (m_DateFrom != null) 92 sql.append(" AND e.DateReport >= ?"); if (m_DateTo != null) 94 sql.append(" AND e.DateReport <= ?"); sql.append(" ORDER BY e.C_BPartner_ID, e.S_TimeExpense_ID, el.Line"); 96 int old_BPartner_ID = -1; 98 MInvoice invoice = null; 99 PreparedStatement pstmt = null; 101 try 102 { 103 pstmt = DB.prepareStatement (sql.toString ()); 104 int par = 1; 105 pstmt.setInt(par++, getAD_Client_ID()); 106 if (m_C_BPartner_ID != 0) 107 pstmt.setInt (par++, m_C_BPartner_ID); 108 if (m_DateFrom != null) 109 pstmt.setTimestamp (par++, m_DateFrom); 110 if (m_DateTo != null) 111 pstmt.setTimestamp (par++, m_DateTo); 112 ResultSet rs = pstmt.executeQuery (); 113 while (rs.next()) { 115 int AD_Client_ID = rs.getInt(1); 117 int AD_Org_ID = rs.getInt(2); 118 int C_BPartner_ID = rs.getInt(3); 120 int M_Warehouse_ID = rs.getInt(4); 121 int C_Project_ID = rs.getInt(5); 123 int C_Activity_ID = rs.getInt(6); 124 int C_Campaign_ID = rs.getInt(7); 125 int M_Product_ID = rs.getInt(8); 127 int S_ResourceAssignment_ID = rs.getInt(9); 128 int C_UOM_ID = rs.getInt(10); 129 BigDecimal Qty = rs.getBigDecimal(11); 131 String Description = rs.getString(12); 132 String Note = rs.getString(13); 133 BigDecimal Cost = rs.getBigDecimal(16); 135 if (Cost == null || Cost.compareTo(Env.ZERO) == 0) 136 continue; 137 String DocumentNo = rs.getString(17); 139 int S_TimeExpenseLine_ID = rs.getInt(18); 140 141 if (C_BPartner_ID != old_BPartner_ID) 143 { 144 completeInvoice (invoice); 145 MBPartner bp = new MBPartner (getCtx(), C_BPartner_ID); 146 log.info("doIt - New Invoice for " + bp); 148 invoice = new MInvoice (getCtx(), 0); 149 invoice.setClientOrg(AD_Client_ID, AD_Org_ID); 150 invoice.setC_DocTypeTarget_ID(MInvoice.DocBaseType_API); 151 invoice.setDocumentNo (DocumentNo); 152 invoice.setBPartner(bp); 154 MUser[] users = bp.getContacts(); 155 if (users != null && users.length > 0) 156 invoice.setSalesRep_ID(users[0].getAD_User_ID()); 157 if (C_Activity_ID != 0) 159 invoice.setC_Activity_ID(C_Activity_ID); 160 if (C_Campaign_ID != 0) 161 invoice.setC_Campaign_ID(C_Campaign_ID); 162 if (C_Project_ID != 0) 163 invoice.setC_Project_ID(C_Project_ID); 164 if (!invoice.save()) 166 { 167 log.error("doIt - cannot save Invoice"); 168 invoice = null; 169 break; 170 } 171 old_BPartner_ID = C_BPartner_ID; 172 } 173 MInvoiceLine il = new MInvoiceLine (invoice); 175 if (M_Product_ID != 0) 177 il.setM_Product_ID(M_Product_ID); 178 il.setQtyInvoiced(Qty); 179 il.setDescription(Description); 180 if (Cost != null && Cost.compareTo(Env.ZERO) != 0) 182 il.setPriceActual(Cost); 183 else 184 il.setPrice(); 185 if (C_UOM_ID != 0 && il.getC_UOM_ID() == 0) 186 il.setC_UOM_ID(C_UOM_ID); 187 il.setTax(); 188 if (!il.save()) 189 { 190 log.error ("doIt - cannot save InvoiceLine"); 191 invoice = null; 192 break; 193 } 194 sql = new StringBuffer ("UPDATE S_TimeExpenseLine SET C_InvoiceLine_ID=") 196 .append(il.getID()) 197 .append(" WHERE S_TimeExpenseLine_ID=").append(S_TimeExpenseLine_ID); 198 int no = DB.executeUpdate(sql.toString()); 199 if (no == 1) 200 log.debug("doIt Updated - S_TimeExpenseLine_ID=" + S_TimeExpenseLine_ID 201 + " with C_InvoiceLine_ID=" + il.getID()); 202 else 203 log.error("doIt Not Updated #" + no 204 + " - S_TimeExpenseLine_ID=" + S_TimeExpenseLine_ID 205 + " with C_InvoiceLine_ID=" + il.getID()); 206 } rs.close (); 208 pstmt.close (); 209 pstmt = null; 210 } 211 catch (Exception e) 212 { 213 log.error ("doIt", e); 214 } 215 finally 216 { 217 try 218 { 219 if (pstmt != null) 220 pstmt.close (); 221 } 222 catch (Exception e) 223 {} 224 pstmt = null; 225 } 226 completeInvoice (invoice); 227 return "@Created@=" + m_noInvoices; 228 } 230 234 private void completeInvoice (MInvoice invoice) 235 { 236 if (invoice == null) 237 return; 238 m_noInvoices++; 239 addLog(invoice.getID(), invoice.getDateInvoiced(), invoice.getGrandTotal(), invoice.getDocumentNo()); 240 } 242 } | Popular Tags |