package com.dataparsing.count_table_records;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.configuration.ConfigurationException;
import org.apache.log4j.Logger;

import com.common.utils.DbConnectionManager;
import com.dataparsing.bean.DevelopmentCountBean;

public class CountDevelopmentTableRecords
{
 private static Logger logger = Logger.getLogger(CountDevelopmentTableRecords.class);
 
 private Connection conn = null;
 private Statement stmt = null;
 private ResultSet rs = null;

 private DevelopmentCountBean developmentCountBean = null;
 private List<DevelopmentCountBean> developmentCountBeanList = new ArrayList<DevelopmentCountBean>();

 public synchronized List<DevelopmentCountBean> loadDataToBean()
 {
  try
  {
   conn = DbConnectionManager.get();
   stmt = conn.createStatement();

   processConcreteSchemaData("mkt_price");
   processConcreteSchemaData("public");
   processConcreteSchemaData("test");
   
   stmt.close();
   conn.close();
   logger.debug("conn closed ? " +conn.isClosed());
  }
  catch (ConfigurationException e)
  {
   logger.error("configuration error. " + e.getMessage());
  } catch (ClassNotFoundException e)
  {
   logger.error("class not found error. " + e.getMessage());
  } catch (SQLException e)
  {
   logger.error("sql error. " + e.getMessage());
  }
  finally
  {
   logger.info("Generate DevelopmentCount Bean sucessfully! The total number: " + developmentCountBeanList.size());
  }
  
  return developmentCountBeanList;

 }

 private void processConcreteSchemaData(String schema)
 {
  try
  {
   rs = stmt.executeQuery("select * from " + schema
     + ".count_table_records");
   
   while (rs.next())
   {
    String tableName = rs.getString("table_name");
    int count = rs.getInt("count");
    
    developmentCountBean = new DevelopmentCountBean(schema);
    developmentCountBean.setTableName(tableName);
    developmentCountBean.setDevelopDbCount(count);
    developmentCountBeanList.add(developmentCountBean);
   }
   rs.close();
  }
  catch (SQLException e)
  {
   logger.error("sql error during processConcreteSchemaData. " + e.getMessage());
  }
  finally
  {
   logger.info("sucessfully processConcreteSchemaData. The schema is:\t" + schema +
     ", the developmentCountBeanList's size:\t" + developmentCountBeanList.size() + ".");
  }
  

 }
 
 public static void main(String[] args)
 {
  DevelopmentCountBean bean = null;
  CountDevelopmentTableRecords cdtr = new CountDevelopmentTableRecords();
  List<DevelopmentCountBean> developmentCountBeanList = cdtr.loadDataToBean();
  
  for(Iterator<DevelopmentCountBean> iter = developmentCountBeanList.iterator(); iter.hasNext();)
  {
   bean = iter.next();
   logger.debug("Schema: " +bean.getSchema() +"\tTableName: " + bean.getTableName() + "\tNumber: " + bean.getDevelopDbCount());
  }
 }
}

 

 

public class CountDatabaseRecordsNumber
{
 public static void main(String args[])
 {
  try
  {
   Workbook wb = Workbook.getWorkbook(new File("D:/test.xls"));
   
   WritableWorkbook book = Workbook.createWorkbook(new File(
     "D:/test.xls"), wb);
//   WritableWorkbook book = Workbook.createWorkbook(new File(
//     "D:/test.xls"));
   WritableSheet sheet = book.createSheet("Schema of test", 0);

   Label label = new Label(0, 0, "Development Database");
   Label tableName = new Label(0, 1, "Table Name");
   Label recordNumber = new Label(1, 1, "Records Number");

   sheet.addCell(label);
   sheet.addCell(tableName);
   sheet.addCell(recordNumber);
   sheet.mergeCells(0, 0, 1, 0);

   Label label2 = new Label(3, 0, "Production Database");
   Label tableName2 = new Label(3, 1, "Table Name");
   Label recordNumber2 = new Label(4, 1, "Records Number");

   sheet.addCell(label2);
   sheet.addCell(tableName2);
   sheet.addCell(recordNumber2);
   sheet.mergeCells(3, 0, 4, 0);// column row column1 row1

   int i = 2;

   CountDevelopmentTableRecords cdtr = new CountDevelopmentTableRecords();
   List<DevelopmentCountBean> developmentCountBeanList = cdtr
     .loadDataToBean();

   for (Iterator<DevelopmentCountBean> iter = developmentCountBeanList
     .iterator(); iter.hasNext();)
   {
    DevelopmentCountBean bean = iter.next();
    if (bean.getSchema().equals("test"))
    {
     Label publicDevelopmentTableName = new Label(0, i,
       bean.getTableName());
     Number publicDevelopmentRecordsNumber = new Number(1, i,
       bean.getDevelopDbCount());
     sheet.addCell(publicDevelopmentTableName);
     sheet.addCell(publicDevelopmentRecordsNumber);
     i++;
    }
   }

   book.write();
   book.close();

  } catch (Exception e)
  {
   System.out.println(e);
  }

  try
  {
   Workbook wb = Workbook.getWorkbook(new File("D:/test.xls"));
   WritableWorkbook book = Workbook.createWorkbook(new File(
     "D:/test.xls"), wb);

   WritableSheet sheet = book.getSheet("Schema of mkt_price");

   int i = 2;

   CountProductionTableRecords cptr = new CountProductionTableRecords();
   List<ProductionCountBean> productionCountBeanList = cptr
     .loadDataToBean();

   for (Iterator<ProductionCountBean> iter = productionCountBeanList
     .iterator(); iter.hasNext();)
   {
    ProductionCountBean bean = iter.next();
    if (bean.getSchema().equals("mkt_price"))
    {
     Label publicProductionTableName = new Label(3, i,
       bean.getTableName());
     Number publicProductionRecordsNumber = new Number(4, i,
       bean.getProductionCount());
     sheet.addCell(publicProductionTableName);
     sheet.addCell(publicProductionRecordsNumber);
     i++;
    }
   }

   book.write();
   book.close();

  } catch (Exception e)
  {
   System.out.println(e);
  }
 }
}