<?    
function get_report($id)
{
	$sql = "select * from tblreport where 0=0 ";
	if($_SESSION[login][companyid])
		$sql .= " and reportcompany = 1 ";
	if($_SESSION[login][vendorid])
		$sql.= " and reportvendor = 1 ";
	if (clientcompanyclientid())
		$sql.= " and reportclientcompanyclient = 1 ";
	if (is_admin())
		$sql.= " and reportadmin = 1 ";
	if(is_numeric($id))
		$sql.=" and reportid = $id";
	else
		$sql.=" and reportshortname = '$id'";
	$sql.=" order by reportname"; 
	$arr=db_query($sql);
	return $arr[0];	
}  

function get_memorized_report($reportid, $memorizedreportid)
{
	$sql = "select * from tblreport join tblreportmemorized using(reportid) where 0=0 ";
	if($_SESSION[login][companyid])
		$sql.= " and companyid = {$_SESSION[login][companyid]} ";
	if($_SESSION[login][vendorid])
		$sql.= " and vendorid = {$_SESSION[login][vendorid]} ";  
	if(is_numeric($reportid))
		$sql.=" and reportid = $reportid";
	if(clientcompanyclientid())
		$sql.="  and reportclientcompanyclient = 1
		         and reportmemorizedcreatedby =  ".loginid(); //  ; reportclientcompanyclient = 1";
	if(is_numeric($memorizedreportid))
		$sql.=" and reportmemorizedid = $memorizedreportid";
	else
		$sql.=" and reportshortname = '$reportid'";
	$sql.="order by reportname"; 
	$arr=db_query($sql);
	return $arr[0];	
}  
function get_memorized_reports( )
{
	
	$sql = "select * from tblreport join tblreportmemorized using(reportid) where 0=0 ";
		$sql.= " and companyid= ".companyid();
		$sql.= " and clientcompanyid= ".clientcompanyid();
		$sql.= " and vendorid= {$_SESSION[login][vendorid]} ";   
	if(clientcompanyclientid())
		$sql.=" and reportclientcompanyclient = 1
				and reportmemorizedcreatedby =  ".loginid(); //  ; reportclientcompanyclient = 1";
	$sql.="order by reportname"; 
	return db_query($sql); 
}  


function get_reports()
{
	
	$sql = "select * from tblreport where 0=0 ";
	if(companyid())
		$sql .= " and reportcompany = 1 ";
	if (clientcompanyclientid())
		$sql .= " and reportclientcompanyclient=1 ";
	else if(clientcompanyid())
	{
		$sql.= " and (reportclientcompany=1 ";
		if(is_developer()) $sql.= " or reportclientcompany=-1 ";
		$sql.=")";
	}
	if (is_admin() && !is_admin_pseudo())
		$sql .= " and reportadmin = 1 ";
	if($_SESSION[login][vendorid])
		$sql.= " and reportvendor=1 "; 
	$sql.=" order by reportname"; 
	$arr=db_query($sql);
	d_dev('get_reports', array('sql' => $sql, 'arr' => $arr));
	return $arr;
}  

function get_report_sql($report)
{
	global $selected_columns, $available_columns, $option_group, $default_columns, $group_selected, $default_orderby_columns, $columnconfig, $selected_clientcompanyid;
	if(!$report)
		ddie("No Report Name Specified");
	$addpath='';		
	if($selected_clientcompanyid)
		$addpath="../";
	if(!file_exists($addpath."reports/$report.inc.php"))
		ddie("No report definition file exists in ".$addpath."reports/$report.inc.php","You must define this report file in order to proceed");

	include($addpath."reports/$report.inc.php");
	include($addpath."reports/columns.inc.php"); //contains array of all column names
	if(!$available_columns)
		ddie("available_columns must be defined by reports/$report.inc.php",  "can not continue until developer creates this variable");
	
	foreach($available_columns as $k =>$v)
	{ 
			if(!$columnnames[$k])
				ddie("Column Name: '$k',  is not defined within reports/columns.inc.php","You must define '$k' as a column before continuing");
			else
				$available_columns[$k]=$columnnames[$k];
	}

	if(!is_array($default_columns))
		ddie("default_columns variable must be defined by reports/$report.inc.php",  "can not continue until developer creates this variable");
	if($_POST[group_selected])
		$group_selected = $_POST[group_selected];	
	if($_POST[selected_columns])
	{	 
		$s=explode(",",$_POST[selected_columns]);
		foreach($s as $k=>$col)
			if($available_columns[$col])
				$selected_columns[$col]=1;
	}
	else
	{ 
		$selected_columns=$default_columns;
		if($_POST)
			$_POST[selected_columns]=implode(",",array_keys($default_columns));
			
	}

	$fnname="get_report_sql_{$report}";
	if(!$sql)
		ddie("function: Variable 'sql' does not exists and must be defined by reports/$report.inc.php",  "can not continue until developer creates this function");  

	$sql = get_selected_columns_sql($selected_columns, $sql);

	return $sql;
}
function get_selected_columns_grouped_sql($selected_columns, $start_sql,$group_selected)
{ // no grouping options ,  this is basically defunct and is never called
	global $summablecolumns;
	$start_sql = get_selected_columns_sql($selected_columns,$start_sql,0);
	$sql = "select sum(cnt) as cnt";
	foreach($selected_columns as $col=>$v)
	{
		$sql.=",";
		if($summablecolumns[$col])
			$sql.="sum($col) as $col ";
		else
			$sql.="$col ";
	}
	$sql.=" from (\n 	$start_sql\n)tbl where 0=0 ";
	if(is_array($_POST[report_filter]))
	{
		foreach ($_POST[report_filter] as $filter=>$fv)
		{
			if(!$summablecolumns[$filter] && !$filter_summable) //if this is a summable column and $filter_summable = false then dont put the criteria here,  we'll add it after the column has been summed
				continue;
			$fv=trim($fv);
			
			$op="";
			$tempop = substr($fv,0,1); 
			if($tempop=="!") 
				$op="!="; 
			if($tempop==">") 
				$op=">"; 
			if($tempop=="<") 
				$op="<"; 
			if($op)
				$fv=substr($fv,-(strlen($fv)-1));
			else
				$op="=";
			if(is_numeric($fv)) 
				$sql .= " --asfdsdaf \nand $filter $op $fv \n";		 
		}
	}
	$sql.=" group by ";
	foreach($selected_columns as $col=>$v)
	{
		if($groupby_count++)
			$sql.=",";
		if(!$summablecolumns[$col]) 
			$sql.="$col ";
	}
	if($_POST[display_row_count] && is_numeric($_POST[display_row_count]))
		$sql.= " limit $_POST[display_row_count] ";
	return $sql;
}

function get_selected_columns_sql($selected_columns,$start_sql,$filter_summable=1)
{
	global $summablecolumns,$default_orderby_columns,$columnconfig,$always_selected, $sql_select_inc;
	$sql = "select * from ( select 1 as cnt";
	
	if($always_selected) //forceselection of these columsn
		foreach($always_selected as $col=>$v)
			if(!$selected_columns[$col])
				$selected_columns[$col] = $v;
	foreach($selected_columns as $col=>$v)
	{ 
			$col = report_column_name($col);
			if ($col =="propertyextensionutility_landscape")
				continue;
			$sql.=",";
			if($col == "vendorrating")
				$sql .= "vendor_rating_average(tbl.vendorid) as vendorrating\n";
			else
				$sql .= "$col\n"; 
	}
	if ($sql_select_inc)
	{
		$sql .= ", $sql_select_inc ";
	}
	$sql .= "from (\n			$start_sql\n) tbl  where 0=0 ";
	$sql .= get_sqlfilterstring($_POST[selected_columns], $_POST[report_filter]);
	
	if($_POST[display_row_count] && is_numeric($_POST[display_row_count]))
		$sql.= " limit $_POST[display_row_count] ";
	$sql .= ") tbl ";
	return $sql;
}
function report_column_name($col)
{
	$col = preg_replace("/[ .\\/\\\\#@%&*]/", "_", strtolower($col));

	//string length of 63 or greater gets truncated in postgres
	//this has led to some ambigious names and sql errors
	$maxlen = 60;
	if (strlen($col) >= $maxlen)
	{
		global $keyedcolumnnames;
		if (!$keyedcolumnnames[$col])
		{
			$keycol = substr($col, 0, $maxlen-6)."_".rand(10000, 99999);
			$keyedcolumnnames[$col] = $keycol;
		}
		$col = $keyedcolumnnames[$col];
	}
	return $col;
}
function report_display_column($col,$val)
{
	global $columnconfig;
	$config = $columnconfig[$col]; 
	if($config[striptags]) 
		$val = strip_tags($val); 
	$nm=get_columnname($col);
	if($config["function_format"])
	{
		$fn = $config["function_format"];
		return $fn($val);
	}
	elseif( (!strstr($col,'update') && !strstr($nm,'update') &&(stristr($col,date)||stristr($nm,date))) || ((strstr(str_replace('update','',$col),'date'))) || ((strstr(str_replace('update','',$nm),'date'))))
	{
	    if($config[dt_format])
			return dt_format($val);
		else
			return dttime_format($val);
	}
	if($val&&is_numeric($val)&&substr($col,-4)=="_doc")
		return customfield_display_documentupload($val);
	return $val;
}
function get_columnname($col)
{
	global $columnnames;
	if($columnnames[$col])
		return $columnnames[$col];
	ddie("You requested columnname '$col',  this is not setup in \$columnnames array", "Developer must correct this before continueing");
}

function report_display_contact($contactid)
{
	$c = db_first("select * from tblcontact where contactid = ".db_number($contactid));
	return $c[contactcname];
}	

function get_activeassignmentcount($clientcompanypropertyid)
{
	$sql = "select count(*) as count from tblclientcompanypropertyassignment
	join tbllogin using(staffid)
		where clientcompanypropertyid = '".$clientcompanypropertyid."'
		and clientcompanypropertyassignmentaccepteddate is not null
		and (clientcompanypropertyassignmentrevokeddate > now() or clientcompanypropertyassignmentrevokeddate is null)";
	$active_assignments = db_query($sql);
	return $active_assignments[0]['count'];
}

function get_clientcompanypropertynotes($clientcompanypropertyid)
{
	$sql = "select notesubject, notetext, notecreatedby, notecreateddate from tblnote where clientcompanypropertyid = ".db_number($clientcompanypropertyid);
	$results = db_query($sql);
	$rval = "";
	
	$count = 0;
	if ($results)
	{
		$rval .= "<table>";
		foreach ($results as $val => $text)
		{
			$style = ($count)?"#57708D":"#243D5A";
			$rval .= "<tr style=\"color:$style;\">";
			$rval .= "<td>".dttime_format($text['notecreateddate'])."</td>";
			$rval .= "<td>".get_login_info($text['notecreatedby'])."</td>";
			$rval .= "<td>".trim(strip_tags($text[notesubject]))."</td>";
			$rval .= "<td>".trim(strip_tags($text[notetext]))."</td>";
			$rval .= "</tr>";
			$count = ($count)?0:1;
		}
		$rval .= "</table>";
	}
	return $rval;
}

function get_sqlfilterstring($selected_columns, $filters, $filter_summable=true)
{
	$sql = '';
	if(is_array($filters))
	{
		foreach ($filters as $filter=>$fvs)
		{
			$filter = report_column_name($filter);
            if(strstr($filter,"propertycountfilter"))
                continue;
			if($filter == "propertylistingagent")
				continue;
			if (is_array($columnconfig[$filter]))
				continue;
			if($summablecolumns[$filter] && !$filter_summable)
				continue;
			if (strstr("utility",$filter)) // This is exception for utilities.
			{
				$colname = db_col(trim ($filter));
				continue;
			}
			if (strstr($filter,"financialtabcda_")) // This is exception for financialtabcda.
			{
				$colname = db_col(trim ($filter));
				continue;
			}			
			$colname = list_first($filter, "_");
			if(!strstr($selected_columns, $colname))
				continue;
			if($fvs)
			{
				$fvsouterarr = explode("&",$fvs);
				foreach($fvsouterarr as $fvs)
				{
					$fvs = str_replace("|",",",$fvs);
					$fvarr = explode(",",$fvs) ;
					$sqlstart= " and (0=1 ";
					foreach($fvarr as $fv)
					{
						$sqlt = get_sqlfilter($filter, trim($fv));
						if($sqlt)
							$sqlmiddle .= " or (0=0  $sqlt)";
					} 
					$sqlend= " ) ";

					if($sqlmiddle)
					{
						$sql.=$sqlstart.$sqlmiddle.$sqlend;
						unset($sqlmiddle);
					}
					
				}
			}
		}
	} 
	return $sql;
}

function get_sqlfilter($filter, $fv, $outcolname='')
{
	if (!$fv)
		return "";
	$fv = trim($fv);
	$invalids = array("%","'");
	$fv = str_replace($invalids,"",$fv);
	global $columnconfig;	
	if($columnconfig[$filter]["function"])
		return ; // no filters on functions
	if($columnconfig[$filter]["function_array"])
		return ; // no filters on function array
	$tempop = substr($fv, 0, 1); 
	$tempval = trim(substr($fv, -(strlen($fv)-1)));
	
	if(($tempop == "<" || $tempop == ">") && is_numeric($tempval))
	{
		$useop = $tempop;
		$fv = $tempval;
	}
	
	if($tempop == "!"  && $fv == $tempop)
	{
		$useop = $tempop;
		$fv = "";
		$tempval = "";
	}
	else if($tempop == "!")
	{
		$fv = substr($fv, -(strlen($fv)-1));
		$not = not;
	}
	
	if(stristr($filter, amount))
	{
		if(!$useop)
			$exactmatch = 1;
		if($fv === "")
			return;
		if(!is_numeric($fv))
			return;
	}
	$origfilter = $filter;
	if($outcolname)
		$filter=$outcolname;

	$columntype = ($columnconfig[$filter][fieldtype] == 'integer' || substr($filter,-2) == "id" ? "integer" : "");
	if($columntype == "integer" )
		$tempval = db_number($tempval);
	if(stristr($origfilter, date) )
	{ 
		$filter = explode("_",$origfilter);
		if(is_date($fv))
		{	
			
			if (strstr($origfilter,"utility")) // This is exception for utilities.
				$outcolname = db_col(str_replace("_".end(explode("_",$origfilter)),"",$origfilter));
			$colname = $filter[0];
			if($outcolname)
				$colname=$outcolname;
			if(strstr($colname, propertyextension)) 
				$colname = "(case when coalesce($colname,'')='' then '1/1/1075'::date else $colname::date end	)";
			elseif(strstr($colname,propertysettlementdate)) // we have a text based 'date' field we need to convert
				$colname = "(case when coalesce($colname,'')='' then '1/1/1075'::date else $colname::date end	)";
				
			if($filter[1]==startdate)
				$sql .= " and (date_trunc('d',$colname::date) >= '$fv') \n";	
			if($filter[1]==enddate)
				$sql .= " and (date_trunc('d',$colname::date) <= '$fv') \n";	  
			$sql .= " and  $colname is not null \n";
		}
		if(is_date($fv2)) 
			$sql .= " and ($filter <= '$fv') \n";	 
	}
	elseif (strstr($filter, TABLECATETYPENAME))
	{
		$tablecatid = explode("_", $filter);
		$tablecatid = $tablecatid[count($tablecatid)-1];
		$sql .= " -- and propertytabcategoryid <> $tablecatid \n";
	}
	elseif(($tempop == "<"||$tempop == ">") && is_numeric($tempval))
	{ 
		if($fv) 
			$sql .= " and $filter $tempop '$tempval' \n";	 
	} 
	elseif(($tempop == "<" || $tempop == ">") && stristr($filter,interval))
	{ 
		if($fv) 
			$sql .= " and $filter $tempop interval '$tempval' \n";	 
	} 
	elseif($tempop == "!" && !$tempval)
		$sql .= " and coalesce($filter,'') = '' \n";
	elseif($columntype == 'integer' )
		$sql .= " and $not ($filter = ".db_number($fv).")  \n";
	elseif($exactmatch ||  $filter == "archivestoreinterval")
	{
		if($fv) 
			$sql .= " and $not ($filter = '$fv')  \n";		 
	}
	else
	{
		if($fv) 
			$sql .= " and $not ($filter ilike '$fv%' or $filter ilike '%$fv%')  \n";		 
	}
	return $sql;
}
function propertycountfilter($companyid)
{
    if(!$_POST[report_filter][propertycountfilter_startdate])
        $startdate = db_date('01/01/2004');
    else {
        $startdate = db_date($_POST[report_filter][propertycountfilter_startdate]);
    }
    if(!$_POST[report_filter][propertycountfilter_enddate])
        $enddate = db_date(time());
    else {
        $enddate = db_date($_POST[report_filter][propertycountfilter_enddate]);
    }
    
    return db_firstval("select count(1) from tblproperty where companyid = " . db_number($companyid) . " and propertycreated between ". $startdate . " and " . $enddate);
}
function get_listtestemails() 
{
	$arr=array();
	//RIO test emails
	$arr['tdottin@riogenesis.com']=1;
	$arr['jlhead2772@yahoo.com']=1;
	$arr['jlhead2798@gmail.com']=1;
	$arr['lwilliams@riogenesis.com']=1;
	$arr['riodev@riogenesis.com']=1;
	$arr['support@riogenesis.com']=1;
	$arr['contact@riogenesis.com']=1;
	$arr['ckrein@riogenesis.com']=1;
	$arr['eddiearmira@riogenesis.com']=1;
	$arr['eddiearmira@riogenesis.com']=1;
	$arr['lanitajwilliams21@gmail.com']=1;	
	
	//Matraex testemails
	$arr['riotestdevelopment@gmail.com']=1;
	$ret="";
	$ret=db_tick_list(array_keys($arr));
	if($ret)
		$ret=strtolower($ret);
	return $ret;
}
function get_emaillikefilters() 
{
	$arr=array();
	//RIO test emails
	$arr['@email']=1;
	$arr['@riogenesis']=1;
	$arr['matraex']=1;
	$arr['cavaleria']=1;	
	return $arr;
}
function get_testemailfiltersql($columnname='')
{
	$filtersql="";
	if(!trim($columnname))
		return $filtersql;
	$testemails=get_listtestemails();
	if($testemails)
		$filtersql.="	and lower($columnname) not in (".$testemails.") \n";
	
	$emaillikefilters=get_emaillikefilters();
	if($emaillikefilters && is_array($emaillikefilters))
		foreach($emaillikefilters as $key=>$val)
			$filtersql.="	and lower($columnname) not ilike '%".$key."%' \n";
	return $filtersql;
}