package api.net.sparktell.bgbilling.service.impl;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.util.List;
import javax.jws.WebService;

import ru.bitel.common.Preferences;
import ru.bitel.bgbilling.common.BGException;
import ru.bitel.bgbilling.kernel.container.service.server.AbstractService;
import ru.bitel.bgbilling.server.util.Setup;
import bitel.billing.server.bill.bean.BillManager;
import bitel.billing.server.bill.bean.InvoiceManager;
import bitel.billing.server.bill.bean.Bill;
import bitel.billing.server.bill.bean.BillDoc;
import bitel.billing.server.bill.bean.PositionValue;


@WebService(targetNamespace="sparktell")
public class BillServiceImpl
extends AbstractService
{
   private static final int BUH_MID = 12;
   private static final Logger logger = Logger.getLogger( BillServiceImpl.class );
   private static final Setup setup = Setup.getSetup();
   private static final Connection conn = setup.getDBConnectionFromPool();
   private static final Preferences moduleSetup = setup.getModuleSetup( BUH_MID );


   private String[] getResult( BillDoc billDoc )
   throws BGException
   {
      if (billDoc == null) {
         logger.info( "billDoc is NULL!" );
         return null;
      }

      List<String> nameList = null;

      for ( PositionValue positionValue : billDoc.getPositionList() ) {
         nameList.add( positionValue.getName() );
      }

      String[] result = new String[nameList.size()];
      nameList.toArray(result);

      return result;
   }


   public String[] getBillDetails( int id )
   throws BGException
   {
      BillManager billManager = new BillManager( setup, conn, BUH_MID, moduleSetup);
      Bill bill = billManager.getBill( id );

      return getResult( bill );
   }


   public String[] getInvoiceDetails( int id )
   throws BGException
   {
      InvoiceManager invoiceManager = new InvoiceManager( setup, conn, BUH_MID, moduleSetup);
      BillDoc invoice = invoiceManager.getInvoice( id );

      return getResult( invoice );
   }
}

import java.io.InputStream;

import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.util.Iterator;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;

import ru.bitel.common.XMLUtils;

...

String query = "SELECT xml FROM bill_invoice_data_" + BILL_MID + " WHERE id=" + id;
PreparedStatement ps = con.prepareStatement();
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
   Blob xml = rs.getBlob( "xml" );
   InputStream is = xml.getBinaryStream();
   Document doc = XMLUtils.parseDocument( is );
   Node billNode = XMLUtils.selectElement( doc, "//bill" );
   Iterable elems = XMLUtils.selectElements( billNode, "pos" );
   //список позиций
   for ( Iterator iterator = elems.iterator(); iterator.hasNext(); )
   {
      Element posElem = (Element)iterator.next();
      ....
   }
   //список позиций субдоговоров
   Iterable subElems = XMLUtils.selectElements( billNode, "sub_bill" );
   for ( Iterator iterator = subElems.iterator(); iterator.hasNext(); )
   {
      Element subContractElement = (Element)iterator.next();
      ....
   }
}
rs.close();
ps.close();