/******************************************************************************
 * The contents of this file are subject to the   Compiere License  Version 1.1
 * ("License"); You may not use this file except in compliance with the License
 * You may obtain a copy of the License at http://www.compiere.org/license.html
 * Software distributed under the License is distributed on an  "AS IS"  basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 * The Original Code is Compiere ERP & CRM Smart Business Solution. The Initial
 * Developer of the Original Code is Jorg Janke. Portions created by Jorg Janke
 * are Copyright (C) 1999-2005 Jorg Janke.
 * All parts are Copyright (C) 1999-2005 ComPiere, Inc.  All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/

package org.compiere.install;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.logging.Level;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Ini;

/**
 *	DB Spec Export.
 *
 * 	@author 	Hans Zou
 * 	@version 	$Id: DBExport.java,v 1.1 2008/02/18 09:01:11 jrmt Exp $
 */
public class DBExport {
	private CLogger	 log = CLogger.getCLogger(getClass());
	
  public void export(String directory, int AD_Client_ID, String Table) {
	String FilePath=directory;
	int Data_Precision=0;
	int Data_Length=0;
	String OldTable="";
	String Help="";
	String Name="";
	String Description="";
	String Data_Type="";
	String ColumnName="";
	String NullAble="";
	String Key="";
	String TableName="";
	
	StringBuffer sql = new StringBuffer("SELECT ");
	sql.append("t.TABLENAME, ");
	sql.append("c.COLUMNNAME, ");
	sql.append("atc.DATA_TYPE, ");
	sql.append("c.NAME, ");
	sql.append("atc.DATA_LENGTH, ");
	sql.append("atc.DATA_PRECISION,  ");
	sql.append("atc.NULLABLE, ");
	sql.append("c.ISKEY, ");
	sql.append("aet.DESCRIPTION, ");
	sql.append("aet.HELP ");
	sql.append("from COMPIERE.AD_COLUMN c ");
	sql.append("inner join COMPIERE.AD_COLUMN ct on ( c.AD_COLUMN_ID = ct.AD_COLUMN_ID) ");
	if ( Table!=null && !"".equals(Table) ){
	sql.append("inner join COMPIERE.AD_TABLE t on ( c.AD_TABLE_ID = t.AD_TABLE_ID and t.TABLENAME= '");
	sql.append(Table);
	sql.append( "') ");
	}else sql.append("inner join COMPIERE.AD_TABLE t on ( c.AD_TABLE_ID = t.AD_TABLE_ID) ");
	sql.append("inner join COMPIERE.AD_ELEMENT aet on ( c.AD_ELEMENT_ID = aet.AD_ELEMENT_ID) ");
	sql.append("left join SYS.ALL_TAB_COLUMNS atc on ( atc.OWNER = 'COMPIERE' ");
	sql.append("and UPPER(c.COLUMNNAME) = atc.COLUMN_NAME and atc.TABLE_NAME=UPPER(t.TABLENAME)) ");
	if ( AD_Client_ID !=-1) {
		sql.append("where c.AD_Client_ID= ");
		sql.append(AD_Client_ID);
		sql.append( " ");
	}
	sql.append("order by t.TABLENAME, c.AD_COLUMN_ID ");	
    PreparedStatement pstmt = null;
    try
    {
        pstmt = DB.prepareStatement (sql.toString(), null);
        ResultSet rs = pstmt.executeQuery ();
        
        while (rs.next ()) {
            TableName = rs.getString(1);
            ColumnName = rs.getString(2);
            Data_Type = rs.getString(3);
            Name = rs.getString(4);
            Data_Length = rs.getInt(5);
            Data_Precision = rs.getInt(6);
            NullAble = rs.getString(7);
            Key = rs.getString(8);
            Description = rs.getString(9);
            Help = rs.getString(10);

        	if ( !TableName.equals(OldTable)) {
        		System.out.println("TableName: "+TableName);
        		title(FilePath, TableName);
        		System.out.println("ColumnName: "+ColumnName);
            	insert(FilePath, TableName, ColumnName, Data_Type, Name, Data_Length, Data_Precision, NullAble, Key, Description, Help);
        	}else {
        		System.out.println("ColumnName: "+ColumnName);
        		insert(FilePath, TableName, ColumnName, Data_Type, Name, Data_Length, Data_Precision, NullAble, Key, Description, Help);
        	}
        	 OldTable=TableName;
          }
         }
	catch (SQLException e)
	{
		log.log(Level.SEVERE, sql.toString(), e);
	}
	catch (Exception e)
	{
		log.log(Level.SEVERE, "", e);
	}
    }
  
  public static void title(String filePath, String tableName) throws Exception {
    	String startDir = Ini.getCompiereHome() + File.separator + "data/export";
	    POIFSFileSystem fs =
        new POIFSFileSystem(new FileInputStream(startDir+File.separator+"DBEXPORTTEMPLATE.xls"));
	    HSSFWorkbook wb = new HSSFWorkbook(fs);
	    HSSFSheet sheet = wb.getSheetAt(0);
	    HSSFCellStyle style = wb.createCellStyle();
	    // Create a row and put some cells in it. Rows are 0 based.
	    HSSFRow row = sheet.createRow((short) 2);
	    HSSFCell cell = row.createCell((short) 3);
	    cell.setCellValue(tableName);
	    cell.setCellStyle(style);
	    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
	    row = sheet.getRow((short) 0);
	    cell = row.createCell((short)9, HSSFCellStyle.ALIGN_LEFT);
	    cell.setCellValue(new Date());
	    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    style.setBorderTop(HSSFCellStyle.BORDER_THIN);

	    cell.setCellStyle(style);
	    
	    // Write the output to a file
	    FileOutputStream fileOut = new FileOutputStream(filePath+File.separator+tableName+".xls");
	    wb.write(fileOut);
	    fileOut.close();
	  }
  
  public static void insert(String filePath, String tableName, String ColumnName, String Dsta_Type, String Name, double Dsta_Length, double Data_Precision, String NullAble, String Key, String Description, String Help) throws Exception {
	    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath+File.separator+tableName+".xls"));
	    HSSFWorkbook wb = new HSSFWorkbook(fs);
	    HSSFSheet sheet = wb.getSheetAt(0);
	    HSSFCellStyle style = wb.createCellStyle();
	    // Create a row and put some cells in it. Rows are 0 based.
	    for(int j=6; j<200; j++){
	    HSSFRow row = sheet.getRow((short) j);
	    if (row==null){
	   	row = sheet.createRow( (short) j);	
	    HSSFCell cell = row.createCell((short) 0, HSSFCellStyle.VERTICAL_TOP);
	    cell.setCellValue(j-5);
	    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    cell.setCellStyle(style);
	    cell = row.createCell((short) 1);
	    cell.setCellValue(ColumnName);
	    cell.setCellStyle(style);
	    cell = row.createCell((short) 2);
	    cell.setCellValue(Dsta_Type);
	    cell.setCellStyle(style);
	    cell = row.createCell((short) 3);
	    cell.setCellValue(Name);
	    cell.setCellStyle(style);
	    cell = row.createCell((short) 4);
	    cell.setCellValue(Dsta_Length);
	    cell.setCellStyle(style);
	    cell = row.createCell((short) 5);
	    cell.setCellValue(Data_Precision);
	    cell.setCellStyle(style);
	    cell = row.createCell((short) 6);
	    cell.setCellValue(NullAble);
	    cell.setCellStyle(style);
	    cell = row.createCell((short) 7);
	    cell.setCellValue(Key);
	    cell.setCellStyle(style);
	    cell = row.createCell((short) 8);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	    cell.setCellValue(Description);
	    style.setWrapText( true );
	    cell.setCellStyle(style);
	    sheet.setColumnWidth( (short) 8, (short) 5000 );
	    cell = row.createCell((short) 9);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	    cell.setCellValue(Help);
	    style.setWrapText( true );
	    cell.setCellStyle(style);
	    sheet.setColumnWidth( (short) 9, (short) 10000 );
	    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	    // Write the output to a file
	    FileOutputStream fileOut = new FileOutputStream(filePath+File.separator +tableName+".xls");
	    wb.write(fileOut);
	    fileOut.close();
	    return;
	  }
    }
  }
}
/* end. */

