|
1
32
33 package com.knowgate.hipergate;
34
35 import java.sql.Statement  ;
36 import java.sql.PreparedStatement ;
37 import java.sql.ResultSet ;
38 import java.sql.SQLException ;
39 import java.sql.Types ;
40
41 import java.util.Date ;
42 import java.util.HashMap ;
43
44 import java.math.BigDecimal ;
45
46 import com.knowgate.dataobjs.*;
47 import com.knowgate.jdc.JDCConnection;
48 import com.knowgate.misc.Gadgets;
49 import com.knowgate.debug.DebugFile;
50
51
55 public class Invoice extends AbstractOrder {
56
57 private DBSubset oOrders;
58 private Address oShipAddr;
59 private Address oBillAddr;
60
61
63
66 public Invoice() {
67 super(DB.k_invoices, DB.k_invoice_lines, DB.gu_invoice, "Invoice");
68 oOrders=null;
69 oBillAddr=oShipAddr=null;
70 }
71
72
75 public Invoice(String sInvoiceId) {
76 super(DB.k_invoices, DB.k_invoice_lines, DB.gu_invoice, "Invoice");
77 put(DB.gu_invoice, sInvoiceId);
78 oOrders=null;
79 oBillAddr=oShipAddr=null;
80 }
81
82
88 public Invoice(JDCConnection oConn, String sInvoiceId)
89 throws SQLException {
90 super(DB.k_invoices, DB.k_invoice_lines, DB.gu_invoice, "Invoice");
91 load(oConn, new Object []{sInvoiceId});
92 }
93
94
96
103 public Invoice(JDCConnection oConn, int iInvoicePg, String sWorkArea)
104 throws SQLException {
105 super(DB.k_invoices, DB.k_invoice_lines, DB.gu_invoice, "Invoice");
106 String sGuInvoice = getIdFromNumber(oConn, iInvoicePg, sWorkArea);
107 if (null!=sGuInvoice) load(oConn, new Object []{sGuInvoice});
108 }
109
110
112
118 public String [] getOrders() {
119 if (oOrders==null)
120 return null;
121 else {
122 String [] aRetVal = new String [oOrders.getRowCount()];
123 for (int o=0; o<oOrders.getRowCount(); o++)
124 aRetVal[o] = oOrders.getString(0,o);
125 return aRetVal;
126 }
127 }
128
129
131
138 public boolean load(JDCConnection oConn, Object [] PKVals) throws SQLException {
139 boolean bRetVal = super.load(oConn, PKVals);
140 if (bRetVal) {
141 oOrders = new DBSubset(DB.k_x_orders_invoices, DB.gu_order, DB.gu_invoice + "=?", 1);
142 if (DBBind.exists(oConn, DB.k_x_orders_invoices, "U")) {
143 oOrders.load(oConn, PKVals);
144 }
145 if (!isNull(DB.gu_ship_addr))
146 oShipAddr = new Address(oConn, getString(DB.gu_ship_addr));
147 if (!isNull(DB.gu_bill_addr))
148 oShipAddr = new Address(oConn, getString(DB.gu_bill_addr));
149 else
150 oShipAddr = null;
151 if (!isNull(DB.gu_bill_addr))
152 oBillAddr = new Address(oConn, getString(DB.gu_bill_addr));
153 if (!isNull(DB.gu_ship_addr))
154 oShipAddr = new Address(oConn, getString(DB.gu_ship_addr));
155 else
156 oBillAddr = null;
157 }
158 return bRetVal;
159 }
161
163
172 public boolean store(JDCConnection oConn) throws SQLException {
173 java.sql.Timestamp dtNow = new java.sql.Timestamp (DBBind.getTime());
174
175 if (DebugFile.trace) {
176 DebugFile.writeln("Begin Invoice.store([JDCConnection])");
177 DebugFile.incIdent();
178 }
179
180 if (!AllVals.containsKey(DB.gu_invoice))
181 AllVals.put(DB.gu_invoice,Gadgets.generateUUID());
182 else
183 replace(DB.dt_modified, dtNow);
184
185 if (!AllVals.containsKey(DB.pg_invoice) && AllVals.containsKey(DB.gu_workarea)) {
186 AllVals.put(DB.pg_invoice, new Integer (nextVal(oConn, (String ) AllVals.get(DB.gu_workarea))));
187 }
189 if (!AllVals.containsKey(DB.id_legal)) {
190 PreparedStatement oStmt;
191 ResultSet oRSet;
192 String sLegalId = null;
193 if (AllVals.containsKey(DB.gu_contact)) {
194 oStmt = oConn.prepareStatement("SELECT "+DB.sn_passport+" FROM "+DB.k_contacts+ " WHERE "+DB.gu_contact+"=?",
195 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
196 oStmt.setString(1, getString(DB.gu_contact));
197 oRSet = oStmt.executeQuery();
198 if (oRSet.next())
199 sLegalId = oRSet.getString(1);
200 oRSet.close();
201 oStmt.close();
202 } if ((sLegalId==null) && AllVals.containsKey(DB.gu_company)) {
204 oStmt = oConn.prepareStatement("SELECT "+DB.id_legal+" FROM "+DB.k_companies+ " WHERE "+DB.gu_company+"=?",
205 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
206 oStmt.setString(1, getString(DB.gu_company));
207 oRSet = oStmt.executeQuery();
208 if (oRSet.next())
209 sLegalId = oRSet.getString(1);
210 oRSet.close();
211 oStmt.close();
212 }
213 if (sLegalId!=null) put(DB.id_legal, sLegalId);
214 }
216 boolean bRetVal = super.store(oConn);
217
218 if (DebugFile.trace) {
219 DebugFile.decIdent();
220 DebugFile.writeln("End Invoice.store() : " + String.valueOf(bRetVal));
221 }
222 return bRetVal;
223 }
225
227
246 public boolean storeLine(JDCConnection oConn, int iLine, BigDecimal oPrSale,
247 float fQuantity, String sIdUnit, BigDecimal oPrTotal,
248 float fTaxRate, short bTaxIncluded, String sNmProduct,
249 String sGuProduct, String sGuItem, String sTxPromo,
250 String sTxOptions)
251 throws SQLException {
252
253 PreparedStatement oStmt = oConn.prepareStatement("UPDATE "+DB.k_invoices+" SET "+DB.dt_modified+"="+DBBind.Functions.GETDATE+" WHERE "+DB.gu_invoice+"=?");
254 oStmt.setObject(1, get(DB.gu_invoice), Types.CHAR);
255 oStmt.executeUpdate();
256 oStmt.close();
257
258 InvoiceLine oLine = new InvoiceLine();
259 oLine.put(DB.gu_invoice, getString(DB.gu_invoice));
260 oLine.put(DB.pg_line, iLine);
261 oLine.put(DB.pr_sale, oPrSale);
262 oLine.put(DB.nu_quantity, fQuantity);
263 oLine.put(DB.id_unit, (sIdUnit==null) ? "UNIT" : sIdUnit);
264 oLine.put(DB.pr_total, oPrTotal);
265 oLine.put(DB.pct_tax_rate, fTaxRate);
266 oLine.put(DB.is_tax_included, bTaxIncluded);
267 oLine.put(DB.nm_product, sNmProduct);
268 oLine.put(DB.gu_product, sGuProduct);
269 oLine.put(DB.gu_item, sGuItem);
270 oLine.put(DB.tx_promotion, sTxPromo);
271 oLine.put(DB.tx_options, sTxOptions);
272 return oLine.store(oConn);
273 }
275
277
295 public int addLine (JDCConnection oConn, BigDecimal oPrSale,
296 float fQuantity, String sIdUnit, BigDecimal oPrTotal,
297 float fTaxRate, short bTaxIncluded, String sNmProduct,
298 String sGuProduct, String sGuItem, String sTxPromo,
299 String sTxOptions)
300 throws SQLException {
301 int iLine;
302 PreparedStatement oStmt = oConn.prepareStatement("SELECT MAX("+DB.pg_line+") FROM "+DB.k_invoice_lines+" WHERE "+DB.gu_invoice+"=?",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
303 oStmt.setString(1, getStringNull(DB.gu_invoice,null));
304 ResultSet oRSet = oStmt.executeQuery();
305 if (oRSet.next()) {
306 Object oPgLine = oRSet.getObject(1);
307 if (oRSet.wasNull()) {
308 iLine = 1;
309 } else {
310 iLine = Integer.parseInt(oPgLine.toString());
311 }
312 } else {
313 iLine = 1;
314 }
315 oRSet.close();
316 oStmt.close();
317 storeLine(oConn, iLine, oPrSale, fQuantity, sIdUnit, oPrTotal, fTaxRate, bTaxIncluded, sNmProduct, sGuProduct, sGuItem, sTxPromo, sTxOptions);
318 return iLine;
319 }
321
323
328 public boolean delete (JDCConnection oConn) throws SQLException {
329 boolean bRetVal;
330 Statement oStmt;
331
332 if (DebugFile.trace) {
333 DebugFile.writeln("Begin Invoice.delete([Connection])");
334 DebugFile.incIdent();
335 }
336
337 oStmt = oConn.createStatement();
338
339 oStmt.executeUpdate("DELETE FROM " + DB.k_invoice_lines + " WHERE " + DB.gu_invoice + "='" + getString(DB.gu_invoice) + "' OR "+DB.gu_invoice+" IN (SELECT "+DB.gu_returned+" FROM "+DB.k_returned_invoices+" WHERE "+DB.gu_invoice+"='"+getString(DB.gu_invoice)+"')");
340
341 oStmt.executeUpdate("DELETE FROM " + DB.k_returned_invoices + " WHERE " + DB.gu_invoice + "='" + getString(DB.gu_invoice) + "'");
342
343 oStmt.executeUpdate("DELETE FROM " + DB.k_x_orders_invoices + " WHERE " + DB.gu_invoice + "='" + getString(DB.gu_invoice) + "'");
344
345 oStmt.close();
346
347 bRetVal = super.delete(oConn);
348
349 if (DebugFile.trace) {
350 DebugFile.decIdent();
351 DebugFile.writeln("End Invoice.delete() : " + String.valueOf(bRetVal));
352 }
353
354 return bRetVal;
355 }
357
359
366 public InvoiceLine getLine(JDCConnection oConn, int iPgLine) throws SQLException {
367 InvoiceLine oRetVal = new InvoiceLine();
368 if (oRetVal.load(oConn, new Object []{getStringNull(DB.gu_invoice,null),new Integer (iPgLine)}))
369 return oRetVal;
370 else
371 return null;
372 }
374
376
382 public DBSubset getLines(JDCConnection oConn) throws SQLException {
383
384 oLines = new DBSubset(DB.k_invoice_lines,
385 DB.gu_invoice+","+DB.pg_line+","+DB.pr_sale+","+
386 DB.nu_quantity+","+DB.id_unit+","+DB.pr_total+","+
387 DB.pct_tax_rate+","+DB.is_tax_included+","+
388 DB.nm_product+","+ DB.gu_product+","+
389 DB.gu_item+","+DB.tx_promotion+","+DB.tx_options,
390 DB.gu_invoice + "=? ORDER BY 2", 10);
391
392 oLines.load(oConn, new Object []{getString(DB.gu_invoice)});
393
394 return oLines;
395 }
397
399
523 public String toXML(JDCConnection oConn, String sLocale) throws IllegalStateException {
524
525 if (oLines==null) throw new IllegalStateException ("Invoice.toXML() Invoice lines not loaded");
526 if (oBuyer==null) throw new IllegalStateException ("Invoice.toXML() Buyer party not set");
527 if (oSeller==null) throw new IllegalStateException ("Invoice.toXML() Seller party not set");
528
529 if (DebugFile.trace) {
530 DebugFile.writeln("Begin Invoice.toXML([Connection],"+sLocale+")");
531 DebugFile.incIdent();
532 }
533
534 final int iLineCount = oLines.getRowCount();
535 StringBuffer oBfr = new StringBuffer ();
536
537 oBfr.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
538 oBfr.append("<Invoice xmlns:cbc=\"urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-1.0\" xmlns:cac=\"urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-1.0\" xmlns:cur=\"urn:oasis:names:specification:ubl:schema:xsd:CurrencyCode-1.0\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">\n");
539 oBfr.append(" <ID>"+String.valueOf(getInt(DB.pg_invoice))+"</ID>\n");
541 oBfr.append(" <GUID>"+getString(DB.gu_invoice)+"</GUID>\n");
542 if (isNull(DB.dt_invoiced))
543 oBfr.append(" <cbc:IssueDate/>\n");
544 else
545 oBfr.append(" <cbc:IssueDate>"+getDateShort(DB.dt_invoiced)+"</cbc:IssueDate>\n");
546 oBfr.append(" <InvoiceCurrencyCode>"+getString(DB.id_currency)+"</InvoiceCurrencyCode>\n");
547 oBfr.append(" <LineItemCountNumeric>"+String.valueOf(iLineCount)+"</LineItemCountNumeric>\n");
548 oBfr.append(" <cac:OrderReference>\n");
549 oBfr.append(" <cac:BuyersID><![CDATA["+getStringNull(DB.id_legal,oBuyer.getStringNull(DB.id_legal,oBuyer.getStringNull(DB.sn_passport,"")))+"]]></cac:BuyersID>\n");
550 oBfr.append(" <cac:SellerID><![CDATA["+oSeller.getStringNull(DB.id_legal,oSeller.getStringNull(DB.sn_passport,""))+"]]></cac:SellerID>\n");
551 oBfr.append(" <DocumentStatusCode><![CDATA["+getStringNull(DB.id_status,"")+"]]></DocumentStatusCode>\n");
552 String [] aOrders = getOrders();
553 Order oOrdr;
554 if (aOrders!=null) {
555 try {
556 oOrdr = new Order(oConn, aOrders[0]);
557 if (oOrdr.isNull(DB.dt_invoiced))
558 oBfr.append(" <cbc:IssueDate/>\n");
559 else
560 oBfr.append(" <cbc:IssueDate>"+oOrdr.getDateShort(DB.dt_invoiced)+"</cbc:IssueDate>\n");
561 oBfr.append(" <GUID>"+oOrdr.getString(DB.gu_order)+"</GUID>\n");
562 } catch (Exception ignore) {}
563 } oBfr.append(" </cac:OrderReference>\n");
565 if (DebugFile.trace) DebugFile.writeln("Writting BuyerParty...");
566 oBfr.append(" <cac:BuyerParty>\n");
567 oBfr.append(" <cac:Party>\n");
568 if (oBuyer.isNull(DB.gu_contact))
569 oBfr.append(" <cac:PartyName><cbc:Name><![CDATA["+oBuyer.getString(DB.nm_legal)+"]]></cbc:Name></cac:PartyName>\n");
570 else
571 oBfr.append(" <cac:PartyName><cbc:Name><![CDATA["+oBuyer.getStringNull(DB.tx_name,"")+" "+oBuyer.getStringNull(DB.tx_surname,"")+"]]></cbc:Name></cac:PartyName>\n");
572 oBfr.append(" <cac:Address>\n");
573 if (oBillAddr==null) {
574 oBfr.append(" <ID/>\n");
575 oBfr.append(" <cbc:PostBox/>\n");
576 oBfr.append(" <cbc:StreetName/>\n");
577 oBfr.append(" <cbc:AdditionalStreetName/>\n");
578 oBfr.append(" <cbc:BuildingName/>\n");
579 oBfr.append(" <cbc:BuildingNumber/>\n");
580 oBfr.append(" <cbc:CityName/>\n");
581 oBfr.append(" <cbc:PostalZone/>\n");
582 oBfr.append(" <cbc:CountrySubentity/>\n");
583 oBfr.append(" <cbc:CountrySubentityCode/>\n");
584 oBfr.append(" <cbc:AddressLine/>\n");
585 oBfr.append(" <Country/>\n");
586 } else {
587 oBfr.append(" <ID>"+oBillAddr.getString(DB.gu_address)+"</ID>\n");
588 oBfr.append(" <cbc:PostBox>"+oBillAddr.getStringNull(DB.po_box,"")+"</cbc:PostBox>\n");
589 oBfr.append(" <cbc:StreetName><![CDATA["+oBillAddr.getStringNull(DB.nm_street,"")+"]]></cbc:StreetName>\n");
590 oBfr.append(" <cbc:AdditionalStreetName><![CDATA["+oBillAddr.getStringNull(DB.tp_street,"")+"]]></cbc:AdditionalStreetName>\n");
591 oBfr.append(" <cbc:BuildingName><![CDATA["+oBillAddr.getStringNull(DB.tx_addr2,"")+"]]></cbc:BuildingName>\n");
592 oBfr.append(" <cbc:BuildingNumber><![CDATA["+oBillAddr.getStringNull(DB.nu_street,"")+"]]></cbc:BuildingNumber>\n");
593 oBfr.append(" <cbc:CityName><![CDATA["+oBillAddr.getStringNull(DB.mn_city,"")+"]]></cbc:CityName>\n");
594 oBfr.append(" <cbc:PostalZone>"+oBillAddr.getStringNull(DB.zipcode,"")+"</cbc:PostalZone>\n");
595 oBfr.append(" <cbc:CountrySubentity><![CDATA["+oBillAddr.getStringNull(DB.nm_state,"")+"]]></cbc:CountrySubentity>\n");
596 oBfr.append(" <cbc:CountrySubentityCode>"+oBillAddr.getStringNull(DB.id_state,"")+"</cbc:CountrySubentityCode>\n");
597 oBfr.append(" <cbc:AddressLine><![CDATA["+oBillAddr.getStringNull(DB.tx_addr1,"")+"]]></cbc:AddressLine>\n");
598 oBfr.append(" <Country><![CDATA["+oBillAddr.getStringNull(DB.nm_country,"").trim()+"]]></Country>\n");
599 }
600 oBfr.append(" </cac:Address>\n");
601 oBfr.append(" </cac:Party>\n");
602 oBfr.append(" <cac:AccountsContact>\n");
603 if (oBillAddr==null) {
604 oBfr.append(" <cbc:Name/>\n");
605 oBfr.append(" <cbc:Telephone/>\n");
606 } else {
607 oBfr.append(" <cbc:Name><![CDATA["+oBuyer.getStringNull(DB.contact_person,"")+"]]></cbc:Name>\n");
608 oBfr.append(" <cbc:Telephone><![CDATA["+oBuyer.getStringNull(DB.direct_phone,oBuyer.getStringNull(DB.work_phone,""))+"]]></cbc:Telephone>\n");
609 }
610 oBfr.append(" </cac:AccountsContact>\n");
611 oBfr.append(" </cac:BuyerParty>\n");
612 if (DebugFile.trace) DebugFile.writeln("Writting SellerParty...");
613 oBfr.append(" <cac:SellerParty>\n");
614 oBfr.append(" <cac:Party>\n");
615 oBfr.append(" <cac:PartyName><cbc:Name><![CDATA["+oSeller.getStringNull(DB.nm_company,oSeller.getStringNull(DB.nm_shop,""))+"]]></cbc:Name></cac:PartyName>\n");
616 oBfr.append(" <cac:Address>\n");
617 if (oBillAddr==null) {
618 oBfr.append(" <ID/>\n");
619 oBfr.append(" <cbc:PostBox/>\n");
620 oBfr.append(" <cbc:StreetName/>\n");
621 oBfr.append(" <cbc:AdditionalStreetName/>\n");
622 oBfr.append(" <cbc:BuildingName/>\n");
623 oBfr.append(" <cbc:BuildingNumber/>\n");
624 oBfr.append(" <cbc:CityName/>\n");
625 oBfr.append(" <cbc:PostalZone/>\n");
626 oBfr.append(" <cbc:CountrySubentity/>\n");
627 oBfr.append(" <cbc:CountrySubentityCode/>\n");
628 oBfr.append(" <cbc:AddressLine/>\n");
629 oBfr.append(" <Country/>\n");
630 } else {
631 oBfr.append(" <ID>"+oSeller.getStringNull(DB.gu_shop, oSeller.getStringNull(DB.gu_address, ""))+"</ID>\n");
632 oBfr.append(" <cbc:StreetName><![CDATA["+oSeller.getStringNull(DB.nm_street,"")+"]]></cbc:StreetName>\n");
633 oBfr.append(" <cbc:AdditionalStreetName><![CDATA["+oSeller.getStringNull(DB.tp_street,"")+"]]></cbc:AdditionalStreetName>\n");
634 oBfr.append(" <cbc:BuildingName><![CDATA["+oSeller.getStringNull(DB.tx_addr2,"")+"]]></cbc:BuildingName>\n");
635 oBfr.append(" <cbc:BuildingNumber><![CDATA["+oSeller.getStringNull(DB.nu_street,"")+"]]></cbc:BuildingNumber>\n");
636 oBfr.append(" <cbc:CityName><![CDATA["+oSeller.getStringNull(DB.mn_city,"")+"]]></cbc:CityName>\n");
637 oBfr.append(" <cbc:PostalZone>"+oSeller.getStringNull(DB.zipcode,"")+"</cbc:PostalZone>\n");
638 oBfr.append(" <cbc:CountrySubentity><![CDATA["+oSeller.getStringNull(DB.nm_state,"")+"]]></cbc:CountrySubentity>\n");
639 oBfr.append(" <cbc:CountrySubentityCode>"+oSeller.getStringNull(DB.id_state,"")+"</cbc:CountrySubentityCode>\n");
640 oBfr.append(" <cbc:AddressLine><![CDATA["+oSeller.getStringNull(DB.tx_addr1,"")+"]]></cbc:AddressLine>\n");
641 oBfr.append(" <Country><![CDATA["+oSeller.getStringNull(DB.nm_country,"").trim()+"]]></Country>\n");
642 }
643 oBfr.append(" </cac:Address>\n");
644 oBfr.append(" </cac:Party>\n");
645 oBfr.append(" <cac:AccountsContact>\n");
646 oBfr.append(" <cbc:Name><![CDATA["+oSeller.getStringNull(DB.contact_person,"")+"]]></cbc:Name>\n");
647 oBfr.append(" <cbc:Telephone><![CDATA["+oSeller.getStringNull(DB.direct_phone,oSeller.getStringNull(DB.work_phone,""))+"]]></cbc:Telephone>\n");
648 oBfr.append(" </cac:AccountsContact>\n");
649 oBfr.append(" </cac:SellerParty>\n");
650 oBfr.append(" <cac:PaymentMeans>\n");
651 oBfr.append(" <cbc:DuePaymentDate>"+(isNull(DB.dt_payment) ? "" : getDateShort(DB.dt_payment))+"</cbc:DuePaymentDate>\n");
652 oBfr.append(" <PayeeFinancialAccount>\n");
653 oBfr.append(" <ID>"+oSeller.getStringNull(DB.nu_bank_acc,"")+"</ID>\n");
654 oBfr.append(" <FinancialInstitutionBranch><![CDATA["+oSeller.getStringNull(DB.nm_bank,"")+"]]></FinancialInstitutionBranch>\n");
655 oBfr.append(" </PayeeFinancialAccount>\n");
656 oBfr.append(" <PayerFinancialAccount>\n");
657 oBfr.append(" <ID>"+oBuyer.getStringNull(DB.nu_bank_acc,"")+"</ID>\n");
658 oBfr.append(" <FinancialInstitutionBranch><![CDATA["+oBuyer.getStringNull(DB.nm_bank,"")+"]]></FinancialInstitutionBranch>\n");
659 oBfr.append(" </PayerFinancialAccount>\n");
660 oBfr.append(" <Payment>\n");
661 oBfr.append(" <ID/>\n");
662 if (isNull(DB.im_paid) && isNull(DB.im_total))
663 oBfr.append(" <PaidAmount>/n");
664 else if (isNull(DB.im_paid))
665 oBfr.append(" <PaidAmount>"+getDecimal(DB.im_total).toString()+"</PaidAmount>\n");
666 else
667 oBfr.append(" <PaidAmount>"+getDecimal(DB.im_paid).toString()+"</PaidAmount>\n");
668 oBfr.append(" <cbc:ReceivedDate>"+(isNull(DB.dt_paid) ? "" : getDateShort(DB.dt_paid))+"</cbc:ReceivedDate>\n");
669 oBfr.append(" </Payment>\n");
670 oBfr.append(" </cac:PaymentMeans>\n");
671 if (DebugFile.trace) DebugFile.writeln("Writting Delivery...");
672 oBfr.append(" <cac:Delivery>\n");
673 if (oShipAddr!=null) {
674 oBfr.append(" <cac:DeliveryAddress>\n");
675 oBfr.append(" <ID>"+oShipAddr.getString(DB.gu_address)+"</ID>\n");
676 oBfr.append(" <cbc:StreetName><![CDATA["+oShipAddr.getStringNull(DB.nm_street,"")+"]]></cbc:StreetName>\n");
677 oBfr.append(" <cbc:AdditionalStreetName><![CDATA["+oShipAddr.getStringNull(DB.tp_street,"")+"]]></cbc:AdditionalStreetName>\n");
678 oBfr.append(" <cbc:BuildingName><![CDATA["+oShipAddr.getStringNull(DB.tx_addr2,"")+"]]></cbc:BuildingName>\n");
679 oBfr.append(" <cbc:BuildingNumber><![CDATA["+oShipAddr.getStringNull(DB.nu_street,"")+"]]></cbc:BuildingNumber>\n");
680 oBfr.append(" <cbc:CityName><![CDATA["+oShipAddr.getStringNull(DB.mn_city,"")+"]]></cbc:CityName>\n");
681 oBfr.append(" <cbc:PostalZone>"+oShipAddr.getStringNull(DB.zipcode,"")+"</cbc:PostalZone>\n");
682 oBfr.append(" <cbc:CountrySubentity><![CDATA["+oShipAddr.getStringNull(DB.nm_state,"")+"]]></cbc:CountrySubentity>\n");
683 oBfr.append(" <cbc:CountrySubentityCode>"+oShipAddr.getStringNull(DB.id_state,"")+"</cbc:CountrySubentityCode>\n");
684 oBfr.append(" <cbc:AddressLine><![CDATA["+oShipAddr.getStringNull(DB.tx_addr1,"")+"]]></cbc:AddressLine>\n");
685 oBfr.append(" <Country><![CDATA["+oShipAddr.getStringNull(DB.id_country,"").trim()+"]]></Country>\n");
686 oBfr.append(" </cac:DeliveryAddress>\n");
687 }
688 oBfr.append(" </cac:Delivery>\n");
689 if (DebugFile.trace) DebugFile.writeln("Writting TaxTotal...");
690 oBfr.append(" <cac:TaxTotal>\n");
691 oBfr.append(" <cbc:TotalTaxAmount amountCurrencyCodeListVersionID=\"0.3\" amountCurrencyID=\""+getString(DB.id_currency)+"\">");
692 oBfr.append(getDecimalFormated(DB.im_taxes));
693 oBfr.append("</cbc:TotalTaxAmount>\n");
694 oBfr.append(" </cac:TaxTotal>\n");
695 if (DebugFile.trace) DebugFile.writeln("Writting LegalTotal...");
696 oBfr.append(" <cac:LegalTotal>\n");
697 oBfr.append(" <cbc:LineExtensionTotalAmount amountCurrencyCodeListVersionID=\"0.3\" amountCurrencyID=\""+getString(DB.id_currency)+"\">");
698 oBfr.append(getDecimalFormated(DB.im_subtotal));
699 oBfr.append("</cbc:LineExtensionTotalAmount>\n");
700 oBfr.append(" <cbc:TaxInclusiveTotalAmount amountCurrencyCodeListVersionID=\"0.3\" amountCurrencyID=\""+getString(DB.id_currency)+"\">");
701 oBfr.append(getDecimalFormated(DB.im_total));
702 oBfr.append("</cbc:TaxInclusiveTotalAmount>\n");
703 oBfr.append(" </cac:LegalTotal>\n");
704 for (int l=0; l<iLineCount; l++) {
705 if (DebugFile.trace) DebugFile.writeln("Writting InvoiceLine "+String.valueOf(l+1)+"...");
706 oBfr.append(" <cac:InvoiceLine>\n");
707 oBfr.append(" <ID>"+String.valueOf(l+1)+"</ID>\n");
708 float fQuantity = oLines.getFloat(DB.nu_quantity,l);
709 if (fQuantity == (long) fQuantity)
710 oBfr.append(" <cbc:InvoicedQuantity>"+String.valueOf((long)fQuantity)+"</cbc:InvoicedQuantity>\n");
711 else
712 oBfr.append(" <cbc:InvoicedQuantity>"+String.valueOf(fQuantity)+"</cbc:InvoicedQuantity>\n");
713 oBfr.append(" <cbc:LineExtensionAmount amountCurrencyCodeListVersionID=\"0.3\" amountCurrencyID=\""+getString(DB.id_currency)+"\">"+oLines.getDecimalFormated(DB.pr_total,l,getCurrencyFormat())+"</cbc:LineExtensionAmount>\n");
714 oBfr.append(" <cac:Item>\n");
715 oBfr.append(" <cbc:Description><![CDATA["+oLines.getString(DB.nm_product,l)+"]]></cbc:Description>\n");
716 oBfr.append(" </cac:Item>\n");
717 if (oLines.isNull(DB.pr_sale,l))
718 oBfr.append(" <BasePrice/>\n");
719 else
720 oBfr.append(" <BasePrice>"+oLines.getDecimalFormated(DB.pr_sale,l,getCurrencyFormat())+"</BasePrice>\n");
721 oBfr.append(" </cac:InvoiceLine>\n");
722 }
723 oBfr.append(" </Invoice>\n");
724
725 if (DebugFile.trace) {
726 DebugFile.decIdent();
727 DebugFile.writeln("End Invoice.toXML()");
728 }
729
730 return oBfr.toString();
731 }
733
735
742
743 public String toXML() throws IllegalStateException {
744 JDCConnection oConn = null;
745 return toXML (oConn, null);
746 }
747
748
750
757
758 public String toXML(String sIdent, String sDelim) throws IllegalStateException {
759 JDCConnection oConn = null;
760 return toXML (oConn, null);
761 }
762
763
765
772
773 public String toXML(String sIdent) throws IllegalStateException {
774 JDCConnection oConn = null;
775 return toXML (oConn, null);
776 }
777
778
780
788 public static String getIdFromNumber(JDCConnection oConn, int iInvoicePg, String sWorkArea)
789 throws SQLException {
790 String sInvoiceId;
791 PreparedStatement oStmt = oConn.prepareStatement("SELECT "+DB.gu_invoice+" FROM "+DB.k_invoices+" WHERE "+DB.pg_invoice+"=? AND "+DB.gu_workarea+"=?",
792 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
793 oStmt.setInt(1, iInvoicePg);
794 oStmt.setString(2, sWorkArea);
795 ResultSet oRSet = oStmt.executeQuery();
796 if (oRSet.next())
797 sInvoiceId = oRSet.getString(1);
798 else
799 sInvoiceId = null;
800 oRSet.close();
801 oStmt.close();
802 return sInvoiceId;
803 }
804
805
807
814 public static int nextVal(JDCConnection oConn, String sGuWorkArea)
815 throws SQLException {
816 boolean bNext;
817 int iNextInvoic = 1;
818 String sSQL = "";
819 PreparedStatement oSnxt = null;
820 ResultSet oRnxt = null;
821
822 sSQL = "SELECT "+DB.pg_invoice+","+DB.gu_workarea+" FROM "+DB.k_invoices_next+ " WHERE "+DB.gu_workarea+"=? ";
823 if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
824 sSQL += "FOR UPDATE OF "+DB.k_invoices_next;
825 } if (oConn.getDataBaseProduct()==JDCConnection.DBMS_ORACLE) {
826 sSQL += "FOR UPDATE OF "+DB.pg_invoice;
827 }
828
829 try {
830 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement("+sSQL+",ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)");
831 oSnxt = oConn.prepareStatement(sSQL,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
832 oSnxt.setObject(1, sGuWorkArea, Types.CHAR);
833 oRnxt = oSnxt.executeQuery();
834 bNext = oRnxt.next();
835 if (bNext) {
836 iNextInvoic = oRnxt.getInt(1);
837 oRnxt.updateInt(DB.pg_invoice, ++iNextInvoic);
838 oRnxt.updateRow();
839 }
840 oRnxt.close();
841 oRnxt=null;
842 oSnxt.close();
843 oSnxt=null;
844 if (!bNext) {
845 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement("+sSQL+",ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)");
846 sSQL = "INSERT INTO "+DB.k_invoices_next+" ("+DB.gu_workarea+","+DB.pg_invoice+") VALUES (?,?)";
847 oSnxt = oConn.prepareStatement(sSQL);
848 oSnxt.setObject(1, sGuWorkArea, Types.CHAR);
849 oSnxt.setInt(2, iNextInvoic);
850 oSnxt.executeUpdate();
851 oSnxt.close();
852 }
853 } catch (SQLException sqle) {
854 if (oRnxt!=null) oRnxt.close();
855 if (oSnxt!=null) oSnxt.close();
856 throw new SQLException (sqle.getMessage()+" "+sSQL,sqle.getSQLState(),sqle.getErrorCode());
857 }
858 return iNextInvoic;
859 }
861
864 public static final short ClassId = 47;
865
866 }
868 |
Popular Tags |
Java API By Example, From Geeks To Geeks.