<?php

class functions
{
	static function get_cache()
	{
		$cache = array();
		$functions_query = db_query("select f.* from app_ext_functions f");
		while($functions = db_fetch_array($functions_query))
		{
			$cache[$functions['id']] = $functions;
		}
		
		return $cache;
	}
		
  static public function get_choices()
  {
    $choices = array();
    $choices['SUM'] = TEXT_EXT_FUNCTION_SUM;
    $choices['COUNT'] = TEXT_EXT_FUNCTION_COUNT;
    $choices['MIN'] = TEXT_EXT_FUNCTION_MIN;
    $choices['MAX'] = TEXT_EXT_FUNCTION_MAX;
    $choices['SELECT'] = TEXT_EXT_FUNCTION_SELECT;
    
    return $choices;
  } 
  
  static public function prepare_formula_query($entities_id,  $formula, $table_prefix = 100)
  {
  	global $app_functions_cache;
    
    foreach($app_functions_cache as $functions)
    {
      if(strstr($formula,'{' . $functions['id'] . '}'))
      {
        $formula = str_replace('{' . $functions['id'] . '}',self::prepare_function_query($entities_id, $functions['id'],0, $table_prefix),$formula);
      }
      
      if(preg_match_all('/{([^:]*):([^}]*)}/',$formula, $matches))
      { 
      	foreach($matches[1] as $matches_key=>$functions_id)
      	{	
      		$perform_field_id = $matches[2][$matches_key];
        	$formula = str_replace('{' . $functions_id . ':'  . $perform_field_id. '}',self::prepare_function_query($entities_id, $functions_id,$perform_field_id,$table_prefix),$formula);
      	}
      }
    }
    
    return $formula;
  }   
  
  static public function prepare_function_query($entities_id, $functions_id, $perform_field_id=0, $table_prefix)
  {
  	global $app_functions_cache, $reports_filters_query_holder;
  	        
    if(isset($app_functions_cache[$functions_id]))
    {    
    	$function_info = $app_functions_cache[$functions_id]; 
    	
      switch($function_info['functions_name'])
      {
        case 'COUNT':
            $sql = "select count(*) from app_entity_" . $function_info['entities_id'] . " func{$table_prefix} where func{$table_prefix}.id>0 ";
          break;
        case 'SUM':
        case 'MIN':  
        case 'MAX':                
            $sql = "select " . $function_info['functions_name'] . "(" . self::prepare_formula_in_function_query($function_info['entities_id'],$function_info['functions_formula'],$table_prefix) . ") from app_entity_" . $function_info['entities_id'] . " func{$table_prefix} where func{$table_prefix}.id>0 ";
          break;
        case 'SELECT':
        		$sql = "select (" . self::prepare_formula_in_function_query($function_info['entities_id'],$function_info['functions_formula'],$table_prefix) . ") from app_entity_" . $function_info['entities_id'] . " func{$table_prefix} where func{$table_prefix}.id>0 ";
        	break;
      }
      
      //
      $sql = self::add_field_query($entities_id, $function_info['entities_id'], $perform_field_id,$sql, $table_prefix); 
      
      //add filters query
      
      if(!isset($reports_filters_query_holder[$function_info['reports_id']. 'func' . $table_prefix]))
      {	
      	$sql = $reports_filters_query_holder[$function_info['reports_id'] . 'func' . $table_prefix] = reports::add_filters_query($function_info['reports_id'],$sql,'func' . $table_prefix);
      }
      else
      {
      	$sql = $reports_filters_query_holder[$function_info['reports_id']. 'func' . $table_prefix];
      }
                        
      //get paret entities
      $parent_entities = entities::get_parents($function_info['entities_id']);
      
      //check if current entity in parent entities and prepare parent entities query
      if(in_array($entities_id,$parent_entities))
      {
        $sql = self::prepare_parent_entities_query($parent_entities,$entities_id, $sql, $table_prefix);
      }
      
      if($function_info['functions_name']=='SELECT')
      {
      	$entities_info = db_find('app_entities',$entities_id);
      	
      	//select value from paretn entity
      	if($entities_info['parent_id']==$function_info['entities_id'] and $entities_info['parent_id']>0)
      	{      
      		$e_prefix =($table_prefix==100 ? 'e' : 'func' . ($table_prefix+1));
      		
      		$sql .= " and func{$table_prefix}.id={$e_prefix}.parent_item_id limit 1";
      	}
      	else
      	{      	
      		$sql .= " order by func{$table_prefix}.id desc limit 1";
      	}
      }
                  
      //echo $sql . '<br>';
            
      return ' (' .$sql . ') ';
    }
    else
    {
      return '{' . $functions_id . '}';
    }      
  }
  
  static public function  add_field_query($entities_id, $function_entities_id, $perform_field_id, $sql, $table_prefix)
  {
    if($perform_field_id>0)
    {                    
      $field_query = db_query("select * from app_fields where entities_id='" . $entities_id . "' and id='" .$perform_field_id  . "'");
      if($field = db_fetch_array($field_query))
      {
        $e_prefix =($table_prefix==100 ? 'e' : 'func' . ($table_prefix+1));
        
        switch($field['type'])
        {
          case 'fieldtype_related_records':
                        	          
          	$cfg = new fields_types_cfg($field['configuration']);
          	$related_entities_id = $cfg->get('entity_id');
          	
          	$table_info = related_records::get_related_items_table_name($entities_id,$cfg->get('entity_id'));
          	
          	$related_items_sql = "select ri.entity_" . $cfg->get('entity_id') . $table_info['sufix']  . "_items_id from " . $table_info['table_name'] . " ri where ri.entity_" . $entities_id . "_items_id={$e_prefix}.id";
          	$sql .= " and (func{$table_prefix}.id in (" . $related_items_sql . ")";
          	
          	if(strlen($table_info['sufix'])>0)
          	{
          		$related_items_sql = "select ri.entity_" . $cfg->get('entity_id') . "_items_id from " . $table_info['table_name'] . " ri where ri.entity_" . $entities_id . $table_info['sufix'] . "_items_id={$e_prefix}.id";
          		$sql .= " or func{$table_prefix}.id in (" . $related_items_sql . ")";
          	}
          	
          	$sql .= ")";
          	
          	//echo $sql;
          	
            break;          
          case 'fieldtype_entity':
              $related_items_sql = "select cv.value from app_entity_" . $entities_id . "_values cv where cv.fields_id='" . db_input($field['id']) . "' and cv.items_id={$e_prefix}.id";
              $sql .= " and func{$table_prefix}.id in (" . $related_items_sql . ")";  
            break;
        }
      }        
    } 
    
    return $sql;        
  }
  
  static public function prepare_formula_in_function_query($entities_id,$formula,$table_prefix)
  {
  	global $app_not_formula_fields_cache, $app_formula_fields_cache;
  	
    if(strlen($formula)==0) return '*';
    
    
    $available_fields = array();        
    if(isset($app_not_formula_fields_cache[$entities_id]))
    {
    	$available_fields = $app_not_formula_fields_cache[$entities_id]; 
    }
    
    //get formulas
    $formulas_fields = array();    
    if(isset($app_formula_fields_cache[$entities_id]))
    {	
	    foreach($app_formula_fields_cache[$entities_id] as $fields)
	    {
	    	$cfg = fields_types::parse_configuration($fields['configuration']);
	    
	    	if(strlen($cfg['formula']))
	    	{
	    		$formulas_fields[$fields['id']] = '(' . $cfg['formula'] . ')';
	    	}
	    }
    }
    
    //prepare formula fields
    $formula = fieldtype_formula::prepare_formula_fields($formulas_fields, $formula);
    
    foreach($available_fields as $fields_id)
    {
      $formula = str_replace('[' . $fields_id . ']','func' . $table_prefix . '.field_' . $fields_id,$formula);
    }  
    
    //prepare [TODAY]
    $formula = str_replace('[TODAY]',get_date_timestamp(date('Y-m-d')),$formula);
    
    if(strstr($formula,'{'))
    {
      $table_prefix  = $table_prefix-1;
      $formula = functions::prepare_formula_query($entities_id, $formula,$table_prefix);
    }
    
    return $formula;    
  }
  
  static public function prepare_parent_entities_query($parent_entities,$entities_id,$sql, $table_prefix)
  {
    $e_prefix =($table_prefix==100 ? 'e' : 'func' . ($table_prefix+1)); 
    
    if(($parent_lavel = array_search($entities_id,$parent_entities))>0)
    { 
      $sql .=  " and func{$table_prefix}.parent_item_id in ";
      foreach($parent_entities as $key=>$pid)
      {
        if($pid!=$entities_id )
        {
          $sql .= "(select func_" . $pid . ".id from app_entity_" . $pid . " func_" . $pid . " where func_" . $pid . ".parent_item_id in ";
        }
        else
        {          
          $sql .= "({$e_prefix}.id)" . str_repeat(')',$parent_lavel);
        }
      }
    }
    else
    {
      $sql .= " and func{$table_prefix}.parent_item_id={$e_prefix}.id ";
    }
            
    return $sql;
  }   
}