<?php

function get_custom_fields($module){

	$result =array();
	$sql = "SELECT * FROM ".TB_PREF."custom_fields WHERE module =".db_escape($module);
	$query =db_query($sql, "could not get custom fields");	
	while ( $myrow = db_fetch($query)) {
			$result[] =$myrow;
		}	
	return $result;
}
function get_custom_fields_with_header($module){

	$result =array();
	$sql ="SELECT  fields.* ,headers.header_label, headers.position FROM ".TB_PREF."custom_fields AS fields INNER JOIN ".TB_PREF."custom_headers AS headers ON fields.header_id = headers.id WHERE fields.module =".db_escape($module);

	// $sql = "SELECT * FROM ".TB_PREF."custom_fields WHERE module =".db_escape($module);
	$query =db_query($sql, "could not get custom fields");	
	while ( $myrow = db_fetch($query)) {
			$result[] =$myrow;
		}

	$sql ="SELECT  * FROM ".TB_PREF."custom_fields WHERE header_id =0 AND module =".db_escape($module);

	$query =db_query($sql, "could not get custom field");		
		while($row = db_fetch_assoc($query)) {
			$default_header[] = $row;
		}
	if(!empty($default_header)){	
		foreach ($default_header as $d_header_row) {
			$d_header_row['header_label']='Custom Fields (default)';
		    $result[]=$d_header_row;
		}
	}	

	return $result;
}
function get_custom_headers($module){

	$result =array();
	$sql = "SELECT * FROM ".TB_PREF."custom_headers WHERE module =".db_escape($module);
	$query =db_query($sql, "could not get custom fields");	
	while ( $myrow = db_fetch($query)) {
			$result[] =$myrow;
		}	
	return $result;
}
function get_custom_field($id){
	$sql = "SELECT * FROM ".TB_PREF."custom_fields WHERE id=".$id;
	$result =db_query($sql, "could not get custom field");		
	return db_fetch($result);
}
function get_custom_header($id){
	$sql = "SELECT * FROM ".TB_PREF."custom_headers WHERE id=".$id;
	$result =db_query($sql, "could not get custom field");		
	return db_fetch($result);
}
function add_custom_field($field,$label,$module,$field_name,$pick_type ='',$header_id=0){
	$sql = "INSERT INTO ".TB_PREF."custom_fields (field,label,module,field_name,pick_type,header_id) VALUES (".db_escape($field).",".db_escape($label).",".db_escape($module).",".db_escape($field_name).",".db_escape($pick_type).",".$header_id.")";
	db_query($sql, "could not custom fields");	

	$field_types =array('text' => _("VARCHAR(255)"),'amount' => _("INT"), 'dropdown' => _("VARCHAR(10)"),'checkbox'=>_("TINYINT(1)"),'radiobutton'=>_('VARCHAR(10)'),'function'=>_('VARCHAR(255)'));

	switch ($module) {
		case 'customer':
			$sql = "ALTER TABLE ".TB_PREF."debtors_master ADD ".$field_name." ".$field_types[$field];
			db_query($sql, "could not custom fields");	
			break;
		case 'supplier':
			$sql = "ALTER TABLE ".TB_PREF."suppliers ADD ".$field_name." ".$field_types[$field];
			db_query($sql, "could not custom fields");	
			break;
		case 'opportunities':
			$sql = "ALTER TABLE ".TB_PREF."kv_crm_opportunities ADD ".$field_name." ".$field_types[$field];
			db_query($sql, "could not custom fields");	
			break;
		case 'leads':
			$sql = "ALTER TABLE ".TB_PREF."kv_crm_leads ADD ".$field_name." ".$field_types[$field];
			db_query($sql, "could not custom fields");	
			break;
		case 'task':
			$sql = "ALTER TABLE ".TB_PREF."kv_crm_tasks ADD ".$field_name." ".$field_types[$field];
			db_query($sql, "could not custom fields");	
			break;
		case 'project':
			$sql = "ALTER TABLE ".TB_PREF."kv_crm_projects ADD ".$field_name." ".$field_types[$field];
			db_query($sql, "could not custom fields");	
			break;
		case 'branch':
			$sql = "ALTER TABLE ".TB_PREF."cust_branch ADD ".$field_name." ".$field_types[$field];
			db_query($sql, "could not custom fields");	
			break;
		default:
			# code...
			break;
	}
}
function add_custom_header($header_label,$module,$position)
{
	$sql = "INSERT INTO ".TB_PREF."custom_headers (header_label,module,position) VALUES (".db_escape($header_label).",".db_escape($module).",".db_escape($position).")";
	db_query($sql, "could not custom fields");	
	
}
function update_custom_field($id,$field,$label,$pick_type ='',$header_id=0)
{
	$sql = "UPDATE ".TB_PREF."custom_fields SET field = ".db_escape($field).",
	label =".db_escape($label).",pick_type =".db_escape($pick_type).",header_id =".$header_id." WHERE id = ".db_escape($id);
	db_query($sql, "could not update custom field");	
}
function update_custom_header($id,$header_label,$position)
{
	$sql = "UPDATE ".TB_PREF."custom_headers SET header_label = ".db_escape($header_label).",
	position =".db_escape($position)." WHERE id = ".db_escape($id);
	db_query($sql, "could not update custom field");	
}
function delete_custom_field($id,$module)
{
	$sql="SELECT field_name,module FROM ".TB_PREF."custom_fields WHERE id=".db_escape($id);
	$result =db_query($sql,"The custom field record could not be deleted");
	$row=db_fetch($result);
	$module =$row['module'];
	$field_name =$row['field_name'];
	$sql="DELETE FROM ".TB_PREF."custom_fields WHERE id=".db_escape($id);
	db_query($sql,"The custom field record could not be deleted");
	
	// $field_name =GetSingleValue('custom_fields','field_name',array('id'=>db_escape($id)));
	switch ($module) {
		case 'customer':
			$sql = "ALTER TABLE ".TB_PREF."debtors_master DROP COLUMN ".$field_name;	
			db_query($sql, "could not custom fields");	
			break;
		case 'supplier':
			$sql = "ALTER TABLE ".TB_PREF."suppliers DROP COLUMN ".$field_name;
			db_query($sql, "could not custom fields");	
			break;
		case 'opportunities':
			$sql = "ALTER TABLE ".TB_PREF."kv_crm_opportunities DROP COLUMN ".$field_name;
			db_query($sql, "could not custom fields");	
			break;
		case 'leads':
			$sql = "ALTER TABLE ".TB_PREF."kv_crm_leads DROP COLUMN ".$field_name;
			db_query($sql, "could not custom fields");	
			break;
		case 'task':
			$sql = "ALTER TABLE ".TB_PREF."kv_crm_tasks DROP COLUMN ".$field_name;
			db_query($sql, "could not custom fields");	
			break;
		case 'project':
			$sql = "ALTER TABLE ".TB_PREF."kv_crm_projects DROP COLUMN ".$field_name;
			db_query($sql, "could not custom fields");	
			break;
		case 'branch':
			$sql = "ALTER TABLE ".TB_PREF."cust_branch DROP COLUMN ".$field_name;
			db_query($sql, "could not custom fields");	
			break;
		default:
			# code...
			break;
	}

}

function delete_custom_header($id,$module)
{
	$sql="DELETE FROM ".TB_PREF."custom_headers WHERE id=".db_escape($id);
	db_query($sql,"The custom field record could not be deleted");
}
function add_custom_values($module,$ref_id,$value)
{
	switch ($module) {
		case 'customer':
			Update('debtors_master',array("debtor_no "=>$ref_id	),$value);
			// // $sql = "UPDATE ".TB_PREF."debtors_master SET custom_fields_values = ".db_escape($value)." WHERE debtor_no = ".db_escape($ref_id);
			// db_query($sql, "could not custom fields");	
			break;
		case 'supplier':
			Update('suppliers',array("supplier_id "=>$ref_id),$value);
			break;
		case 'opportunities':
			Update('kv_crm_opportunities',array("opportunity_id "=>$ref_id),$value);
			break;
		case 'leads':
			Update('kv_crm_leads',array("lead_id "=>$ref_id),$value);
			db_query($sql, "could not custom fields");	
			break;
		case 'task':
			Update('kv_crm_tasks',array("id "=>$ref_id),$value);
			db_query($sql, "could not custom fields");	
			break;
		case 'project':
			Update('kv_crm_projects',array("id "=>$ref_id),$value);
			break;
		case 'branch':
			Update('cust_branch',array("branch_code "=>$ref_id),$value);
			break;
		default:
			# code...
			break;
	}
	// $sql = "INSERT INTO ".TB_PREF."custom_fileds_data (module,ref_id,value) VALUES (".db_escape($module).",".$ref_id.",".db_escape($value).")";
	// db_query($sql, "could not custom fields");	
}
function get_custom_values($module ,$ref_id)
{
	switch ($module) {
		case 'customer':
			$result=GetRow('debtors_master',array("debtor_no"=>$ref_id));
			// $sql = "SELECT custom_fields_values	 FROM ".TB_PREF."debtors_master WHERE debtor_no=".$ref_id;
			// $result =db_query($sql, "could not get custom field");		
			return $result;
			break;
		case 'supplier':
			$result=GetRow('suppliers',array("supplier_id"=>$ref_id));
			return $result;	
			break;
		case 'opportunities':
			$result=GetRow('kv_crm_opportunities',array("opportunity_id"=>$ref_id));
			return $result;	
			break;
		case 'leads':
			$result=GetRow('kv_crm_leads',array("lead_id"=>$ref_id));
			return $result;	
			break;
		case 'task':
			$result=GetRow('kv_crm_tasks',array("id"=>$ref_id));
			return $result;	
			break;
		case 'project':
			$result=GetRow('kv_crm_projects',array("id"=>$ref_id));
			return $result;	
			break;
		case 'branch':
			$result=GetRow('cust_branch',array("branch_code"=>$ref_id));
			return $result;	
			break;
		default:
			# code...
			break;
	}
	// $sql = "SELECT * FROM ".TB_PREF."custom_fileds_data WHERE ref_id=".$ref_id." AND module =".db_escape($module);
	// $result =db_query($sql, "could not get custom field");		
	// return db_fetch($result);
}
function update_custom_values($module,$ref_id,$value)
{	
	$sql = "UPDATE ".TB_PREF."custom_fileds_data SET value = ".db_escape($value)." WHERE module = ".db_escape($module)." AND ref_id =".db_escape($ref_id);
	
	db_query($sql, "could not update custom value");	
}
function delete_custom_values($module,$ref_id)
{
	$sql="DELETE FROM ".TB_PREF."custom_fields WHERE module = ".db_escape($module)." AND ref_id =".db_escape($ref_id);
	db_query($sql,"The custom value record could not be deleted");
}

function has_custom_values($module,$ref_id){
	$field_name =GetSingleValue('custom_fields','field_name',array('id'=>$ref_id,'module'=>$module));
	// $sql="SELECT ".$field_name." FROM ".TB_PREF."debtors_master WHERE ".$field_name." IS NOT NULL ";
	// $query =db_query($sql, "could not get custom field");		
	// 	while($row = db_fetch_assoc($query)) {
	// 		if($row[$field_name] !='' || $row[$field_name] >0 ){
	// 			$result[]= $row[$field_name];
	// 		}
	// 	}
	$result =array();
	switch ($module) {
		case 'customer':
			$sql="SELECT ".$field_name." FROM ".TB_PREF."debtors_master WHERE ".$field_name." IS NOT NULL ";
			$query =db_query($sql, "could not get custom field");		
			while($row = db_fetch_assoc($query)) {
				if($row[$field_name] !='' || $row[$field_name] >0 ){
					$result[]= $row[$field_name];
				}
			}
			// $sql = "SELECT custom_fields_values	 FROM ".TB_PREF."debtors_master WHERE debtor_no=".$ref_id;
			// $result =db_query($sql, "could not get custom field");		
			return count($result);
			break;
		case 'supplier':
			$sql="SELECT ".$field_name." FROM ".TB_PREF."suppliers WHERE ".$field_name." IS NOT NULL ";
			$query =db_query($sql, "could not get custom field");		
			while($row = db_fetch_assoc($query)) {
				if($row[$field_name] !='' || $row[$field_name] >0 ){
					$result[]= $row[$field_name];
				}
			}
			return count($result);	
			break;
		case 'opportunities':
			$sql="SELECT ".$field_name." FROM ".TB_PREF."kv_crm_opportunities WHERE ".$field_name." IS NOT NULL ";
			$query =db_query($sql, "could not get custom field");		
			while($row = db_fetch_assoc($query)) {
				if($row[$field_name] !='' || $row[$field_name] >0 ){
					$result[]= $row[$field_name];
				}
			}
			return count($result);	
			break;
		case 'leads':
			$sql="SELECT ".$field_name." FROM ".TB_PREF."kv_crm_leads WHERE ".$field_name." IS NOT NULL ";
			$query =db_query($sql, "could not get custom field");		
			while($row = db_fetch_assoc($query)) {
				if($row[$field_name] !='' || $row[$field_name] >0 ){
					$result[]= $row[$field_name];
				}
			}
			return count($result);	
			break;
		case 'task':
			$sql="SELECT ".$field_name." FROM ".TB_PREF."kv_crm_tasks WHERE ".$field_name." IS NOT NULL ";
			$query =db_query($sql, "could not get custom field");		
			while($row = db_fetch_assoc($query)) {
				if($row[$field_name] !='' || $row[$field_name] >0 ){
					$result[]= $row[$field_name];
				}
			}
			return count($result);
			break;
		case 'project':
			$sql="SELECT ".$field_name." FROM ".TB_PREF."kv_crm_projects WHERE ".$field_name." IS NOT NULL ";
			$query =db_query($sql, "could not get custom field");		
			while($row = db_fetch_assoc($query)) {
				if($row[$field_name] !='' || $row[$field_name] >0 ){
					$result[]= $row[$field_name];
				}
			}
			return count($result);	
			break;
		case 'branch':
			$sql="SELECT ".$field_name." FROM ".TB_PREF."cust_branch WHERE ".$field_name." IS NOT NULL ";
			$query =db_query($sql, "could not get custom field");		
			while($row = db_fetch_assoc($query)) {
				if($row[$field_name] !='' || $row[$field_name] >0 ){
					$result[]= $row[$field_name];
				}
			}
			return count($result);	
			break;
		default:
			# code...
			break;
	}	
	return count($result);
}	

function has_custom_fields($header_id)		
{
	$sql = "SELECT * FROM ".TB_PREF."custom_fields WHERE header_id=".$header_id;
	$result =db_query($sql, "could not get custom field");		
	return db_fetch($result);
}
function get_fields_headers($module){
	$sql = "SELECT fields.*,headers.* FROM ".TB_PREF."custom_fields AS fields INNER JOIN ".TB_PREF."custom_headers AS headers ON fields.header_id = headers.id WHERE fields.module =".db_escape($module);
	$result =db_query($sql, "could not get custom field");		
	return db_fetch($result);
}
function get_headers_group_count($module){
	$sql ="SELECT COUNT(fields.id), fields.header_id ,headers.header_label, headers.position FROM ".TB_PREF."custom_fields AS fields INNER JOIN ".TB_PREF."custom_headers AS headers ON fields.header_id = headers.id WHERE fields.module =".db_escape($module)."GROUP BY fields.header_id";

	// $sql = "SELECT COUNT(id), header_id FROM ".TB_PREF."custom_fields WHERE module =".db_escape($module)." GROUP BY header_id";
	$result =db_query($sql, "could not get custom field");		
	$data = array();
		while($row = db_fetch_assoc($result)) {
			$data[] = $row;
		}

	$sql ="SELECT COUNT(id) ,header_id FROM ".TB_PREF."custom_fields WHERE module =".db_escape($module)." AND header_id=0 GROUP BY header_id";

	$result =db_query($sql, "could not get custom field");		
		while($row = db_fetch_assoc($result)) {
			$default_header[] = $row;
		}
	if(!empty($default_header)){	
		foreach ($default_header as $d_header_row) {
			$d_header_row['header_label']='Custom Fields';
			$d_header_row['position']='left';
		    $data[]=$d_header_row;
		}
	}
	return $data;
}