<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
/*********************************************************************************
 * SugarCRM is a customer relationship management program developed by
 * SugarCRM, Inc. Copyright (C) 2004 - 2007 SugarCRM Inc.
 * 
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License version 3 as published by the
 * Free Software Foundation with the addition of the following permission added
 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, see http://www.gnu.org/licenses or write to the Free
 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 * 
 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
 * 
 * The interactive user interfaces in modified source and object code versions
 * of this program must display Appropriate Legal Notices, as required under
 * Section 5 of the GNU General Public License version 3.
 * 
 * In accordance with Section 7(b) of the GNU General Public License version 3,
 * these Appropriate Legal Notices must retain the display of the "Powered by
 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
 * technical reasons, the Appropriate Legal Notices must display the words
 * "Powered by SugarCRM".
 ********************************************************************************/
/*********************************************************************************

* Description: This file handles the Data base functionality for the application specific
* to Mssql database. It is called by the DBManager class to generate various sql statements.
*
* All the functions in this class will work with any bean which implements the meta interface.
* Please refer the DBManager documentation for the details.
*
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
* All Rights Reserved.
* Contributor(s): ___RPS___________________________________..
********************************************************************************/

include_once('sugar_version.php');
require_once('log4php/LoggerManager.php');
include_once('include/database/DBHelper.php');

class MssqlHelper extends DBHelper
{

	function MssqlHelper()
	{
        parent::DBHelper();
	}

	/**
	* This is a private (php does not support it as of 4.x) method.
	* It outputs a correct string for the sql statement according to value
	* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
	* All Rights Reserved..
	* Contributor(s): ______________________________________..
	*/
	function massageValue($val, $fieldDef){
        if (!$val) return "''";
        $type = $this->getFieldType($fieldDef);

		switch ($type){
		  case 'int':
		  case 'double':
		  case 'float':
		  case 'uint':
		  case 'ulong':
		  case 'long':
		  case 'short':
          case 'tinyint':
		    return $val;
		    break;
		}

		$qval = $this->quote($val);

		switch ($type){
		  case 'varchar':
          case 'char':
          case 'longtext':
		  case 'text':
		  case 'enum':
		  case 'multienum':
          case 'blob':
          case 'longblob':
          case 'clob':
          case 'id':
		  	return $qval;
		  	break;
		  case 'date':
		    return "$qval";
		    break;
		  case 'datetime':
		    return $qval;
		    break;
		  case 'time':
		    return "$qval";
		    break;
		}
		return $val;
	}

    /** returns the valid type for a column given the type in fieldDef
    */

    function getColumnType($type){
        $map = array( 'int'      => 'int'
                    , 'double'   => 'float'
                    , 'float'    => 'float'
                    , 'uint'     => 'int'
                    , 'ulong'    => 'int'
                    , 'long'     => 'bigint'
                    , 'short'    => 'smallint'
                    , 'varchar'  => 'varchar'
                    , 'text'     => 'text'
                    , 'longtext' => 'text'
                    , 'date'     => 'datetime'
                    , 'enum'     => 'varchar'
                    , 'multienum'=> 'text'
                    , 'datetime' => 'datetime'
                    , 'time'     => 'datetime'
                    , 'bool'     => 'bit'
                    , 'tinyint'  => 'tinyint'
                    , 'char'  => 'char'
                    , 'blob'  => 'image'
                    , 'longblob' => 'image'
                    , 'currency'=>'decimal'
                    , 'decimal' => 'decimal'
                    , 'decimal2' => 'decimal'
                    , 'id' => 'varchar(36)'

                    );
        return $map[$type];
    }

    function dropTableNameSQL($name){
		return "DROP TABLE ".$name;
	}

    /** private function to get sql for a column
    */
    function oneColumnSQLRep($fieldDef, $ignoreRequired = false, $return_as_array=false){

    	//the last parameter request the return value as an array.
    	//the components of field definition as returned in the following format.
    	// array('name','colType','default','required','auto_increment','full')
    	//full represents the complete string returned by default.
        $rep = parent::oneColumnSQLRep($fieldDef, $ignoreRequired,'', $return_as_array);
        return $rep;
    }

	//MSSQL has a quirky T-SQL alter table syntax. Pay special attention to the
	//modify operation
    function alterSQLRep($action, $def, $ignoreRequired, $tablename='') {
    	switch($action){
    		case 'add':
    			 $f_def=$this->oneColumnSQLRep($def, $ignoreRequired,false);
    			return "ADD " . $f_def;
    			break;

    		case 'drop':
    			$f_def=$this->oneColumnSQLRep($def, $ignoreRequired,false);
    			return "DROP COLUMN " . $f_def;
    			break;

    		case 'modify':
    			//You cannot specify a default value for a column for MSSQL
    			$f_def = $this->oneColumnSQLRep($def, $ignoreRequired, true);
    			$f_stmt= "ALTER COLUMN " . $f_def['name'] .' ' .$f_def['colType'] . ' ' .$f_def['required'] . ' ' .$f_def['auto_increment']. "\n" ;
    			if (!empty( $f_def['default'])) {
    					$f_stmt.= " ALTER TABLE " . $tablename .  " ADD  ". $f_def['default'] . " FOR " . $def['name'];
    			}
    			return $f_stmt;
    			break;

    		default:
    			return '';
    	}
    }

    /**
    * A private function which generates the SQL for changing columns
    *
    * MSSQL uses a different syntax than MySQL for table altering that is
    * not quite as simplistic to implement...
    */
    function changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired = false){

		$sql='';
        if ($this->isFieldArray($fieldDefs)){
      		foreach ($fieldDefs as $def)
      		{
          		//if the column is being modified drop the default value
          		//constraint if it exists. alterSQLRep will add the constraint back
          		$def_ctrt_name=MssqlHelper::get_field_default_constraint_name($tablename, $def['name']);
          		if (!empty($def_ctrt_name)) {
          			$sql.=" ALTER TABLE " . $tablename . " DROP CONSTRAINT " . $def_ctrt_name;
          		}

          		$columns[] = $this->alterSQLRep($action, $def, $ignoreRequired,$tablename);
      		}
        }
        else {

      		//if the column is being modified drop the default value
      		//constraint if it exists. alterSQLRep will add the constraint back
      		$def_ctrt_name=MssqlHelper::get_field_default_constraint_name($tablename, $fieldDefs['name']);
      		if (!empty($def_ctrt_name)) {
      			$sql.=" ALTER TABLE " . $tablename . " DROP CONSTRAINT " . $def_ctrt_name;
      		}

          	$columns[] = $this->alterSQLRep($action, $fieldDefs, $ignoreRequired,$tablename);
        }


        $columns = implode(", ", $columns);
        $sql .= " ALTER TABLE $tablename $columns";
        return $sql;
    }

    /**
    * This method generates sql that deletes a column identified by fieldDef.
    * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
    * All Rights Reserved.
    * Contributor(s): ______________________________________..
    */
    function deleteColumnSQL($bean, $fieldDefs){
        if ($this->isFieldArray($fieldDefs)) foreach ($fieldDefs as $fieldDef) $columns[] = $fieldDef['name'];
        else $columns[] = $fieldDefs['name'];
        $columns = implode(", DROP COLUMN ", $columns);
        $sql = "ALTER TABLE ".$bean->getTableName()." DROP COLUMN $columns";
        return $sql;
    }

    /**
    * This method genrates sql for key statement for any bean identified by id.
    * The passes array is an array of field definitions or a field definition
    * itself. The keys generated will be either primary, foreign, unique, index
    * or none at all depending on the setting of the "key" parameter of a field definition
    * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
    * All Rights Reserved.
    * Contributor(s): ______________________________________..
    */
    function keysSQL( $indices, $alter_table = false, $alter_action = '')
	{

		/* function has been deprecated
		use indexSQL instead
		*/

    }

    function indexSQL( $tableName, $fieldDefs, $indices) {
       // check if the passed value is an array of fields.
       // if not, convert it into an array
       if (!$this->isFieldArray($indices)) $indices[] = $indices;

        $columns = array();
		foreach ($indices as $index){

		  if(!empty($index['db']) && $index['db'] != 'mssql')continue;

          $type = $index['type'];

          $name = $index['name'];

          if(is_array($index['fields'])) {
          	$fields = implode(", ", $index['fields']);
          }else{
          	$fields = $index['fields'];
          }

          switch ($type){

            case 'primary':
                // SQL server requires primary key constraints to be created with
                //key word "PRIMARY KEY".  Cannot default to index as synonym
           	   $columns[] = "ALTER TABLE $tableName ADD CONSTRAINT pk_$tableName PRIMARY KEY ($fields)";
                break;

            case 'unique':
                $columns[] = "ALTER TABLE $tableName ADD CONSTRAINT " . $index['name'] . " UNIQUE ($fields)";
                break;

            case 'index':
            case 'alternate_key':
            case 'foreign':
                $columns[] = "create index $name on $tableName ( $fields )";
                break;

            case 'fulltext':

            	if ($this->full_text_indexing_enabled()) {
            		$catalog_name="sugar_fts_catalog";
            		if (isset ($index['catalog_name']) and $index['catalog_name'] != 'default') {
            			$catalog_name=$index['catalog_name'];
            		}

            		$language="Language 1033";
            		if (isset($index['language']) and !empty($index['language'])) {
            			$language="Language " . $index['language'];
            		}

            		$key_index=$index['key_index'];;

            		$change_tracking="auto";
            		if (isset($index['change_tracking']) and !empty($index['change_tracking'])) {
            			$change_tracking=$index['change_tracking'];
            		}
            		$columns[] = " CREATE FULLTEXT INDEX ON $tableName($fields $language) KEY INDEX $key_index ON $catalog_name WITH CHANGE_TRACKING $change_tracking" ;
            	}
                break;
          }
       }

       $columns = implode(" ", $columns);
       return $columns;
    }

    function quote($string){
        $string = from_html($string);
        $string =  str_replace("'","''",$string);
        return "'$string'";
    }

    function escape_quote($string){
        $string = from_html($string);
        $string =  str_replace("'","''",$string);
        return $string;
    }


 	function setAutoIncrement($table, $field_name){
		return "identity(1,1)";
	}


	/** private function to get sql for a column
	*/
	function columnSQLRep($fieldDefs, $ignoreRequired = false, $tablename){
		$columns = array();
		if ($this->isFieldArray($fieldDefs)) {
		  foreach ($fieldDefs as $fieldDef){
		  	if(!isset($fieldDef['source']) || $fieldDef['source'] == 'db'){
		  	 $columns[] = $this->oneColumnSQLRep($fieldDef,false);
		  	}
		  }
		 	 $columns = implode(",", $columns);
		}
		else $columns = $this->oneColumnSQLRep($fieldDefs,false); //jc: for consistency


		return $columns;
	}


	function createTableSQLParams($tablename, $fieldDefs, $indices)
	{
		if(empty($tablename) || empty($fieldDefs)) return '';

		$columns = $this->columnSQLRep($fieldDefs, false, $tablename);

        $sql ='';
        if(!empty($columns)){
            $sql = "CREATE TABLE $tablename ($columns ) ";
        }

        $sql .= $this->indexSQL($tablename, $fieldDefs, $indices);

        return $sql;
    }

   	/**
   	 * Returns definitions of all indies for passed table. return will is a multi-dimensional array that
     * categorizes the index definition by types, unique, primary and index.
     * return format $indices = array ('index1'=>('name'=>'index1','type'=>'primary','fields'=>array('field1','field2'))
     * This format is similar to how indicies are defined in vardef file.
     */


    function get_indices($tablename) {
        $tablename=$tablename;
        $indices=array();
        //find all unique indexes and primary keys.
        //
        $query="SELECT LEFT(so.[name], 30) TableName, LEFT(si.[name], 50) 'Key_name',
                LEFT(sik.[keyno], 30) Sequence, LEFT(sc.[name], 30) Column_name
                FROM sysindexes si
                INNER JOIN sysindexkeys sik ON (si.[id] = sik.[id] AND si.indid = sik.indid)
                INNER JOIN sysobjects so ON si.[id] = so.[id]
                INNER JOIN syscolumns sc ON (so.[id] = sc.[id] AND sik.colid = sc.colid)
                INNER JOIN sysfilegroups sfg ON si.groupid = sfg.groupid";
                /*WHERE si.[name] NOT LIKE 'sys%' --filter out tables that start with sys
                AND si.[name] NOT LIKE '[u,n]c%sys%' --filter out tables that start with ncsys, nc1, nc2, ucsys
                AND si.[name] NOT LIKE '_WA_%' --filter out tables that start with _WA_
                AND si.[name] NOT LIKE 'hind_%' --filter out tables that start with hind_
                AND so.[name] <> 'dtproperties' --filter out the dtproperties table*/
        $query .=" Where so.[name] = '$tablename'
                Order by Key_name, Sequence, Column_name";
        $result=$this->db->query($query);
        while (($row=$this->db->fetchByAssoc($result)) !=null) {
            $index_type='index';
            if ($row['Key_name'] =='PRIMARY') {
                $index_type='primary';
            }
            $indices[strtolower($row['Key_name'])]['name']=strtolower($row['Key_name']);
            $indices[strtolower($row['Key_name'])]['type']=$index_type;
            $indices[strtolower($row['Key_name'])]['fields'][]=strtolower($row['Column_name']);
        }
        return $indices;
    }

    /**
     * function generate alter constraint statement given a tabe name and vardef definition.
     * supports both adding and droping a constraint.
     */
    function add_drop_constraint($table,$definition, $drop=false) {
        $type=$definition['type'];
        $fields=implode(',',$definition['fields']);
        $name=$definition['name'];
        $foreignTable=isset($definition['foreignTable']) ? $definition['foreignTable'] : array();
        //_pp("Type is $type, drop is $drop, table is $table");
        switch ($type){
            // generic indices
            case 'index':
            case 'alternate_key':
                    if ($drop) {
                        $sql = "DROP INDEX {$name} ";
                    } else {
                        $sql = "CREATE INDEX {$name} ON {$table} ({$fields})";
                    }
                    break;
            // constraints as indices
            case 'unique':
                    if ($drop){
                        $sql = "ALTER TABLE {$table} DROP INDEX $name";
                    } else {
                        //$sql = "ALTER TABLE {$table} ADD CONSTRAINT {$name}  UNIQUE ({$fields})";
                        $sql = "ALTER TABLE {$table} ADD CONSTRAINT {$name} UNIQUE ({$fields})";
                    }
                    break;
            case 'primary':
                    if ($drop) {
                        $sql = "ALTER TABLE {$table} DROP PRIMARY KEY";
                    } else {
                        $sql = "ALTER TABLE {$table} ADD CONSTRAINT {$name} PRIMARY KEY ({$fields})";
                    }
                    break;
            case 'foreign':
                    if ($drop) {
                        $sql = "ALTER TABLE {$table} DROP FOREIGN KEY ({$fields})";
                    } else {
                        $sql = "ALTER TABLE {$table} ADD CONSTRAINT {$name}  FOREIGN KEY ({$fields}) REFERENCES {$foreignTable}({$foreignfields})";
                    }
                    break;
        }
        return $sql;
    }

    function rename_index($old_definition,$new_definition,$table_name) {
        $ret_commands=array();
        $ret_commands[]=$this->add_drop_constraint($table_name,$old_definition,true);
        $ret_commands[]=$this->add_drop_constraint($table_name,$new_definition);
        return $ret_commands;
    }

    	function createTableSQL($bean){

		$tablename = $bean->getTableName();
		$fieldDefs = $bean->getFieldDefinitions();
		$indices = $bean->getIndices();
		return $this->createTableSQLParams($tablename, $fieldDefs, $indices);

	}

    /* Function returns a count coulmns in the supplied table name, function is used primarily by
     * custom module code.
     */
    function number_of_columns($table_name) {

        $def_query="SELECT count(*) as cols ";
        $def_query.=" FROM sys.columns col";
        $def_query.=" join sys.types col_type on col.user_type_id=col_type.user_type_id ";
        $def_query.=" where col.object_id = (select object_id(sys.schemas.name + '.' + sys.tables.name)";
        $def_query.=" from sys.tables  join sys.schemas on sys.schemas.schema_id = sys.tables.schema_id";
        $def_query.=" where sys.tables.name='$table_name')";


		//TODO test the similarities of the above the query against all system tables vs the query below against
		//the information_schema view in terms of results and efficiency. suspician is provided the two produce
		//the same information the latter will be slightly faster.
        //$def_query = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$table_name'";

        $result=$GLOBALS['db']->query($def_query);
        $row=$GLOBALS['db']->fetchByAssoc($result);
        if (!empty($row)) {
            return $row['cols'];
        }
        return 0;
    }

	/* This method genrates sql for insert statement.
	* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
	* All Rights Reserved..
	* Contributor(s): ______________________________________..
	*/
	function insertSQL($bean){
		// get basic insert
		$colums = array();
		$values = array();
		// get field definitions
		$fields = $bean->getFieldDefinitions();

		foreach($fields as $field=>$value)
		{

			if(!isset($value['source']) || $value['source'] == 'db')
			{
				// Do not write out the id field on the update statement.
				// We are not allowed to change ids.
				//if($isUpdate && ('id' == $field)) continue;
				//custom fields handle there save seperatley

				if(isset($bean->field_name_map) && !empty($bean->field_name_map[$field]['custom_type']))
				continue;

				// Only assign variables that have been set.
				if(isset($bean->$field))
				{
					//trim the value in case empty space is passed in.
					//this will allow default values set in db to take effect, otherwise
					//will insert blanks into db
					$trimmed_field = trim($bean->$field);
					//if this value is empty, do not include the field value in statement
					if($trimmed_field =='')
					{
						continue;
					}
                    //added check for ints because sql-server does not like casting varchar with a decimal value
                    //into an int.
                    if(isset($value['type']) and $value['type']=='int') {
                        $values[] = PearDatabase::quote(from_html($bean->$field));
                    } else {
                        $values[] = "'".PearDatabase::quote(from_html($bean->$field))."'";

                    }
					$columns[] = $field;
				}
			}
		}

		// build out the SQL INSERT statement.
		$query = "INSERT INTO $bean->table_name (" .implode("," , $columns). " ) VALUES ( ". implode("," , $values). ')';
		$GLOBALS['log']->info("Save.Insert: ".$query);

		$bean->db->query($query, true);
	}

	/**
	* This method genrates sql for update statement.
	* Updates are based for the row identified by primary key only.
	* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
	* All Rights Reserved.
	* Contributor(s): ______________________________________..
	*/
	function updateSQL($bean, $where=array()){

		// get field definitions
		$query = "UPDATE " . $bean->table_name." SET ";
		$firstPass = 0;
		foreach($bean->field_defs as $field=>$value)
		{
			if(!isset($value['source']) || $value['source'] == 'db')
			{
				// Do not write out the id field on the update statement.
				// We are not allowed to change ids.
				if('id' == $field)
					continue;

				// If the field is an auto_increment field, then we shouldn't be setting it.  This was added
				// specially for Bugs and Cases which have a number associated with them.
				if (isset($bean->field_name_map[$field]['auto_increment']) &&
				    $bean->field_name_map[$field]['auto_increment'] == true)
					continue;

				//custom fields handle their save seperatley
				if(isset($bean->field_name_map) && !empty($bean->field_name_map[$field]['custom_type']))
					continue;

				// Only assign variables that have been set.
				if(isset($bean->$field))
				{
					if(strlen($bean->$field) <= 0)
					{
						$bean->$field = null;
					}
					// Try comparing this element with the head element.
					if(0 == $firstPass)
						$firstPass = 1;
					else
						$query .= ", ";

					if(is_null($bean->$field))
					{
						$query .= $field."=null";
					}
					else
					{
						$query .= $field."='".PearDatabase::quote(from_html($bean->$field))."'";
					}
				}
			}
		}
		$query = $query." WHERE ID = '$bean->id'";
		$GLOBALS['log']->info("Save.Update $bean->object_name: ".$query);

		$bean->db->query($query, true);
	}

    function full_text_indexing_installed() {
        //create query that will check if fulltext service is installed on mssql
        $ftsChckQry = "SELECT FULLTEXTSERVICEPROPERTY('IsFulltextInstalled') as fts";
        $ftsChckRes = $GLOBALS['db']->query($ftsChckQry);
        $row = $GLOBALS['db']->fetchByAssoc($ftsChckRes);

        if(isset($row) && isset($row['fts']) && ($row['fts'] == 1 || $row['fts'] == '1')){
            //fts is installed
            return true;
        }else{
            //Full Text Search is Not installed, make a note of it to display on summary page
            return false;
        }
    }



	function full_text_indexing_enabled($dbname=null) {
        //check to see if we already have install setting in session
    	if(!isset($_SESSION['IsFulltextInstalled'])){
            $_SESSION['IsFulltextInstalled'] = $this->full_text_indexing_installed();
        }
        //check to see if FTS Indexing service is installed
        if(empty($_SESSION['IsFulltextInstalled']) ||  $_SESSION['IsFulltextInstalled'] === false){
            //Indexing service is not installed, no need to check further
            return false;
        }
        //grab the dbname if it was not passed through
		if (empty($dbname)) {
			global $sugar_config;
			$dbname=$sugar_config['dbconfig']['db_name'];
		}
        //we already know that Indexing service is installed, now check
        //to see if it is enabled
		$query="SELECT DATABASEPROPERTY('$dbname', 'IsFulltextEnabled') ftext";
		$status=$GLOBALS['db']->query($query);
		if (!empty($status)) {
			$row=$GLOBALS['db']->fetchByAssoc($status);
			if (isset($row['ftext']) and $row['ftext']==1) {
                //Indexing service is enabled, return true
				return true;
			}
		}
        //Indexing service is NOT enabled, return true
		return false;
	}

	function create_default_full_text_catalog() {
		if ($status=$GLOBALS['db']->helper->full_text_indexing_enabled()) {

			$GLOBALS['log']->debug('Creating the default catalog for full-text indexing, sugar_fts_catalog');
			//drop catalog if exists.
			$query="if not exists(select * from sys.fulltext_catalogs where name ='sugar_fts_catalog') CREATE FULLTEXT CATALOG sugar_fts_catalog ";
			$ret=$GLOBALS['db']->query($query);

			if (empty($ret)) {
				$GLOBALS['log']->error('Error creating default full-text catalog, sugar_fts_catalog');
			}
		}
	}

	/*
	 * Function returns name of the constraint automatically generated by sql-server.
	 * We request this for default, primary key, required
	 */
	function get_field_default_constraint_name($table, $column) {

		$query=<<<EOQ

			select s.name, o.name, c.name, d.name ctrt
			  from sys.default_constraints as d
			  join sys.objects as o
				on o.object_id = d.parent_object_id
			  join sys.columns as c
				on c.object_id = o.object_id and c.column_id = d.parent_column_id
			  join sys.schemas as s
				on s.schema_id = o.schema_id
			  where o.name = '$table' and c.name = '$column'
EOQ;

		$res=$GLOBALS['db']->query($query);
		$row=$GLOBALS['db']->fetchByAssoc($res);
		if (!empty($row)) {
			return $row['ctrt'];
		} else {
			return null;
		}
	}
}
?>
