package zr.zrpower.ejb.sys;

import zr.zrpower.common.util.*;
import zr.zrpower.common.db.*;
import zr.zrpower.entity.sys.BPIP_OPERATE_TABLE;

/**
 * <p>Title: ZRPower</p>
 * <p>Description: 表结构维护管理EJB服务</p>
 * <p>Copyright: Copyright (c) 2015</p>
 */
public class OperateTableService
  {
  static private Log log;
  dbEngine dbengine;
  static private int clients=0;
  public zr.zrpower.common.util.SysPreperty ObjSysPreperty = new zr.zrpower.common.util.SysPreperty();
  String DataBaseType = ObjSysPreperty.getProperty().DataBaseType;//数据库类型

  public OperateTableService()
  {
    dbengine = new dbEngine(SysPreperty.getProperty().MainDataSource,
                              SysPreperty.getProperty().IsConvert);
    dbengine.initialize();


    if (clients<1)
    {
      log = new Log();
      log.SetLogForClass("OperateTableService");
      log.SetLogFile("OperateTableService.log");
    }
    clients++;
  }

  /**
   * 功能：判断数据库中指定表中的字段数量
   * @param tableName 表名

   * @return 返回int值
   **/
  private int GetColumnCount(String tableName) {
    String  strSql = "";
    strSql = "select FIELDID from BPIP_FIELD  where TABLEID in (select TABLEID from BPIP_TABLE where  TABLENAME=upper('"+
          tableName+"') or TABLENAME='"+ tableName+"')";
    try {
      return dbengine.QuerySQL(strSql).RowCount();
    }
    catch (Exception e) {
      log.WriteLog("出现异常", e.getMessage());
    }
    return 0;

  }

  /**
   * 功能：增加字段
   * @param BPIP_OPERATE_TABLE 字段信息
   * @return 返回boolearn值
   **/
  public boolean AddField(BPIP_OPERATE_TABLE field) {
    boolean result = false;
    String endStr = "";
    String strBuf = "";

    if (DataBaseType.equals("2"))//mssql数据库
    {
       //处理mssql字段的类型，oracle的VARCHAR2变成VARCHAR
       if (field.FieldType.equals("VARCHAR2"))
       {
          field.FieldType = "VARCHAR";
       }
       if (field.FieldType.equals("NUMBER"))
       {
          field.FieldType = "NUMERIC";
       }
       if (field.FieldType.equals("BLOB"))
       {
          field.FieldType = "IMAGE";
       }
       if (field.FieldType.equals("CLOB"))
       {
          field.FieldType = "TEXT";
       }
       if (field.FieldType.equals("DATE"))
       {
          field.FieldType = "DATETIME";
       }

    }
    if (DataBaseType.equals("3"))//mysql数据库
    {
       //处理mssql字段的类型，oracle的VARCHAR2变成VARCHAR
       if (field.FieldType.equals("VARCHAR2"))
       {
          field.FieldType = "VARCHAR";
       }
       if (field.FieldType.equals("NUMBER"))
       {
          field.FieldType = "NUMERIC";
       }
       if (field.FieldType.equals("BLOB"))
       {
          field.FieldType = "LONGBLOB";
       }
       if (field.FieldType.equals("CLOB"))
       {
          field.FieldType = "LONGTEXT";
       }
       if (field.FieldType.equals("DATE"))
       {
          field.FieldType = "DATETIME";
       }

    }


    if (IsExistTable(field.TableName)) {
        strBuf = "ALTER TABLE "+field.TableName+" ADD "+ field.FieldName;
    }
    else { //不存在表
        strBuf ="CREATE TABLE "+field.TableName+" ( "+ field.FieldName;
        endStr =" ) ";
    }

    strBuf = strBuf + "  "+field.FieldType+"  ";

    if (field.Length > 0 && field.Precision > 0) {
      strBuf = strBuf + "("+String.valueOf(field.Length)+","+
                    String.valueOf(field.Precision)+")";
    }
    else
    if (field.Length > 0) {
      strBuf = strBuf + "("+String.valueOf(field.Length)+")";
    }

    if (field.IsNull) {
      strBuf = strBuf + " NULL";
    }
    else {
      strBuf = strBuf + " NOT NULL";
    }
    if (field.IsPrimaryKey) {
      strBuf = strBuf + " PRIMARY KEY";
    }

    if (field.DefaultValue.length() > 0) {
      strBuf = strBuf + " DEFAULT "+field.DefaultValue;
    }

    strBuf = strBuf + endStr;
    try {
      result = dbengine.ExecuteSQL(strBuf);
    }
    catch (Exception e) {
      result = false;
      log.WriteLog("出现异常", e.getMessage());
    }
    return result;
  }

  /**
   * 功能：删除字段
   * @param BPIP_OPERATE_TABLE 字段信息
   * @return 返回boolearn值
   **/
  public boolean DeleteField(BPIP_OPERATE_TABLE field) {
    String strSql = "";
    if (GetColumnCount(field.TableName) == 1) {
      //表中只一个字段，删除此字段后表即不存在，所以要执行删除表的操作；
      if (DataBaseType.equals("2") || DataBaseType.equals("3"))//mssql或mysql
      {
        strSql = "DROP TABLE "+field.TableName;
      }else
      {
        strSql = "DROP TABLE "+field.TableName+" CASCADE CONSTRAINT";
      }

    }
    else {
      strSql = "ALTER TABLE "+field.TableName+" DROP COLUMN "+ field.FieldName;
    }
    try {
      return dbengine.ExecuteSQL(strSql);
    }
    catch (Exception e) {
      log.WriteLog("DeleteField-出现异常", e.getMessage());
    }
    return false;
  }

  /**
   * 功能：删除指定的表
   * @param TableName 表名
   * @return 返回boolearn值
   **/
  public boolean DeleteTable(String TableName) {
    String strSql = "";
    boolean result = false;
    if (DataBaseType.equals("2") || DataBaseType.equals("3"))//mssql或mysql
    {
      strSql = "DROP TABLE "+TableName;
    }
    else
    {
      strSql = "DROP TABLE "+TableName+" CASCADE CONSTRAINT";
    }

      try {
        result = dbengine.ExecuteSQL(strSql);
      }
      catch (Exception e) {
        log.WriteLog("DeleteTable-出现异常", e.getMessage());
      }
    return result;
  }

  /**
   * 功能：修改字段
   * @param field 字段信息
   * @return 返回boolearn值
   **/
  public boolean ModifyField(BPIP_OPERATE_TABLE field) {
    log.WriteLog("ModifyField", "开始调用");
    boolean result = false;
    StringBuffer strBuf = new StringBuffer();

    if (DataBaseType.equals("2"))//mssql数据库
    {
       //处理mssql字段的类型，oracle的VARCHAR2变成VARCHAR
       if (field.FieldType.equals("VARCHAR2"))
       {
          field.FieldType = "VARCHAR";
       }
       if (field.FieldType.equals("NUMBER"))
       {
          field.FieldType = "NUMERIC";
       }
       if (field.FieldType.equals("BLOB"))
       {
          field.FieldType = "IMAGE";
       }
       if (field.FieldType.equals("CLOB"))
       {
          field.FieldType = "TEXT";
       }
       if (field.FieldType.equals("DATE"))
       {
          field.FieldType = "DATETIME";
       }

    }

    if (DataBaseType.equals("3"))//mysql数据库
    {
       //处理mssql字段的类型，oracle的VARCHAR2变成VARCHAR
       if (field.FieldType.equals("VARCHAR2"))
       {
          field.FieldType = "VARCHAR";
       }
       if (field.FieldType.equals("NUMBER"))
       {
          field.FieldType = "NUMERIC";
       }
       if (field.FieldType.equals("BLOB"))
       {
          field.FieldType = "LONGBLOB";
       }
       if (field.FieldType.equals("CLOB"))
       {
          field.FieldType = "LONGTEXT";
       }
       if (field.FieldType.equals("DATE"))
       {
          field.FieldType = "DATETIME";
       }

    }


      if (!field.FieldName.equals(field.OldFieldName)) {

        if (DataBaseType.equals("1"))//oracle数据库
        {
          strBuf.append("ALTER TABLE "+field.TableName+"  RENAME COLUMN "+
                      field.OldFieldName+" TO "+field.FieldName+";");
        }
        if (DataBaseType.equals("2"))//mssql数据库
        {
          strBuf.append("exec sp_rename '"+field.TableName+"."+
                      field.OldFieldName+"' , '"+field.FieldName+"';");
        }
        if (DataBaseType.equals("3"))//mysql数据库
        {
          strBuf.append("ALTER TABLE "+field.TableName+"  change "+
                      field.OldFieldName+" "+field.FieldName+";");
        }


      }

       if (DataBaseType.equals("1"))//oracle数据库
       {
         strBuf.append("ALTER TABLE "+field.TableName+" MODIFY "+
                      field.FieldName);
       }
       if (DataBaseType.equals("2"))//mssql数据库
       {
         strBuf.append("ALTER TABLE "+field.TableName+" ALTER COLUMN "+
                      field.FieldName);
       }

       if (DataBaseType.equals("3"))//mysql数据库
       {
         strBuf.append("ALTER TABLE "+field.TableName+"  change  "+
                      field.FieldName);
       }

      strBuf.append(" "+field.FieldType+"");

      if (field.Length > 0 && field.Precision > 0) {
        strBuf.append("("+String.valueOf(field.Length)+","+
                      String.valueOf(field.Precision)+")");
      }
      else
      if (field.Length > 0) {
        strBuf.append("("+String.valueOf(field.Length)+")");
      }

     if (!field.IsNull)
     {
           strBuf.append(" NOT NULL");
     }

     if (field.IsNull)
     {
          strBuf.append(" NULL");
     }

    if (field.IsPrimaryKey) {
        strBuf.append(" PRIMARY KEY");
      }

      if (field.DefaultValue.length() > 0) {
        strBuf.append(" DEFAULT "+field.DefaultValue);
      }
      strBuf.append(";");
      try {
        log.WriteLog("执行的SQL语句", strBuf.toString());
        result = dbengine.ExecuteSQLs(strBuf.toString().split(";"));

      }
      catch (Exception e) {
        log.WriteLog("出现异常", e.getMessage());
      }

    return result;
  }

  /**
   * 功能：更改表名
   * @param OldName 旧表名
   * @param NewName 新表名
   * @return 返回boolearn值
   **/
  public boolean ChangeTableName(String OldName, String NewName) {
    boolean result = false;
    String strSql = "";
    strSql = "ALTER TABLE "+OldName+" rename to "+NewName;
      try {
        result = dbengine.ExecuteSQL(strSql);
      }
      catch (Exception e) {
        result = false;
        log.WriteLog("ChangeTableName-出现异常", e.getMessage());
      }
    return result;
  }
  /**
     * 功能：判断数据库中是否有指定的表存在
     * @param tableName 表名字串
     * @return 返回boolearn值
     **/
    private boolean IsExistTable(String tableName) {
      log.WriteLog("IsExistTable", "开始调用");
      boolean result = false;
      String  strSql = "";
      strSql = "select count(TABLEID) as num from BPIP_FIELD where TABLEID in (select TABLEID from BPIP_TABLE where TABLENAME=upper('"+
          tableName+"') or TABLENAME='"+ tableName+"')";

      int rowCount  = 0;
      try {
        dbSet dbset = dbengine.QuerySQL(strSql);
        if (dbset != null) {
          if (dbset.RowCount() > 0) {
             rowCount = dbset.Row(0).Column("num").getInteger();
          }
        }
      }
      catch (Exception e) {
        rowCount = 0;
        log.WriteLog("出现异常", e.getMessage());
      }

      if (rowCount > 1) {
        result = true;
      }
      else {
        result = false;
      }
      log.WriteLog("IsExistTable", "结束调用");
      return result;
  }
}
