<?php
/**********************************************************************
***********************************************************************/
function clear_shipping_tax_group() {
	  $sql = "UPDATE ".TB_PREF."tax_groups SET tax_shipping=0 WHERE 1";
	  db_query($sql, "could not update tax_shipping fields");	  
}

function add_tax_group($name, $tax_shipping, $taxes, $rates, $display_sequence, $inactive, $tax_code){
    begin_transaction();

	if($tax_shipping) 	// only one tax group for shipping
	  clear_shipping_tax_group();
		
	$sql = "INSERT INTO ".TB_PREF."tax_groups (name, tax_shipping, display_sequence, 
                                                   inactive, tax_code) 
                    VALUES (".db_escape($name).", ".db_escape($tax_shipping).", ".db_escape($display_sequence).", 
                            ".db_escape($inactive).", ".db_escape($tax_code).")";
	db_query($sql, "could not add tax group");
	$id = db_insert_id();
	add_tax_group_items($id, $taxes, $rates);	
	
    commit_transaction();	
}

function update_tax_group($id, $name, $tax_shipping, $taxes, $rates, $display_sequence, $inactive){
    begin_transaction();	

	if($tax_shipping) 	// only one tax group for shipping
	  clear_shipping_tax_group();
	
        $sql = "UPDATE ".TB_PREF."tax_groups 
                    SET name = ".db_escape($name).",tax_shipping = ".db_escape($tax_shipping)." ,
                        display_sequence = ".db_escape($display_sequence)." , inactive = ".db_escape($inactive)." 
                WHERE id = ".db_escape($id);
	db_query($sql, "could not update tax group");
	
	delete_tax_group_items($id);
	add_tax_group_items($id, $taxes, $rates);	
	
    commit_transaction();			
}

function get_all_tax_groups($all=false){
    $sql = "SELECT * FROM ".TB_PREF."tax_groups";

    if(!$all) $sql .= " WHERE !inactive";

    $sql .= " ORDER BY display_sequence";

    return db_query($sql, "could not get all tax group");
} 

function get_tax_group($type_id)
{
	$sql = "SELECT * FROM ".TB_PREF."tax_groups WHERE id=".db_escape($type_id);
	
	$result = db_query($sql, "could not get tax group");
	
	return db_fetch($result);
}

function delete_tax_group($id)
{
	begin_transaction();
		
	$sql = "DELETE FROM ".TB_PREF."tax_groups WHERE id=".db_escape($id);
		
	db_query($sql, "could not delete tax group");
	
	delete_tax_group_items($id);	
	
	commit_transaction();
}

function add_tax_group_items($id, $items, $rates)
{
	for ($i=0; $i < count($items); $i++) 
	{
		$sql = "INSERT INTO ".TB_PREF."tax_group_items (tax_group_id, tax_type_id, rate)
			VALUES (".db_escape($id).",  ".db_escape($items[$i]).", " . $rates[$i] .")";
		db_query($sql, "could not add item tax group item");					
	}		
}

function delete_tax_group_items($id)
{
	$sql = "DELETE FROM ".TB_PREF."tax_group_items WHERE tax_group_id=".db_escape($id);
	
	db_query($sql, "could not delete item tax group items");					
}

//
//	Return all tax types with rate value updated according to tax group selected
//	Call the function without arg to find shipment group taxes.
//
function get_tax_group_rates($group_id=null)
{
	global $suppress_tax_rates;

	$sql = "SELECT t.sequence, t.add_to_price, t.name, t.id as tax_type_id, t.suspended, "
                .(isset($suppress_tax_rates) && $suppress_tax_rates == 1 ? "t.name as tax_type_name," : "CONCAT(t.name, ' (', t.rate, '%)') as tax_type_name,")
                ."t.sales_gl_code,
                t.purchasing_gl_code,
                IF(g.tax_type_id, t.rate, NULL) as rate                 
            FROM ".TB_PREF."tax_types t 
                    LEFT JOIN ".TB_PREF."tax_group_items g ON t.id=g.tax_type_id
                            AND g.tax_group_id=". ($group_id ? db_escape($group_id) : "(SELECT id FROM ".TB_PREF."tax_groups WHERE tax_shipping=1)")
		." WHERE !t.inactive "
        . " ORDER BY t.sequence";

	return db_query($sql, "cannot get tax types as array");
}

function get_tax_group_items_as_array($id)
{
	$active_taxes = $ret_tax_array = array();

	$tax_group_items = get_tax_group_rates($id);

	while ($tax_group_item = db_fetch_assoc($tax_group_items)) 
	{
		$tax_group_item['Value'] = 0;
		$ret_tax_array[$tax_group_item['tax_type_id']] = $tax_group_item;
	}

	return $ret_tax_array;
}


function get_shipping_tax_as_array()
{
	$active_taxes = $ret_tax_array = array();

	$tax_group_items = get_tax_group_rates();

	while ($tax_group_item = db_fetch_assoc($tax_group_items)) 
	{
		$tax_group_item['Value'] = 0;
		$ret_tax_array[$tax_group_item['tax_type_id']] = $tax_group_item;
	}

	return $ret_tax_array;
}

function get_tax_group_by_name($tax_group_name)
{
	$sql = "SELECT * FROM ".TB_PREF."tax_groups WHERE name=".db_escape($tax_group_name);
	
	$result = db_query($sql, "could not get_tax_group_by_name");
	
	return db_fetch($result);
}

function get_tax_groups_unique_tax_code($tax_code, $selected_id){
    $sql = "SELECT tax_code, id  
            FROM ".TB_PREF."tax_groups  
            WHERE tax_code = ".db_escape($tax_code);
    
    if($selected_id > 0){
        $sql .= " AND id <> ".db_escape($selected_id);
    }
    
    $result = db_query($sql,"Could not get_tax_type_unique_tax_code");
    return db_fetch($result);
}

function get_tax_groups_unique_tax_name($name, $selected_id){
    $sql = "SELECT name, id   
            FROM ".TB_PREF."tax_groups  
            WHERE name = ".db_escape($name);
    
    if($selected_id > 0){
        $sql .= " AND id <> ".db_escape($selected_id);
    }
    
    $result = db_query($sql,"Could not get_tax_groups_unique_tax_name");
    return db_fetch($result);
}