<?php
/**********************************************************************
***********************************************************************/
function add_movement_type($type_id, $name, $linked_field, $group_id)
{
	$sql = "INSERT INTO ".TB_PREF."movement_types (
                        type, name, 
                        linked_field, group_id)
		VALUES (".db_escape($type_id).",".db_escape($name).","
                        .db_escape($linked_field).",".db_escape($group_id).")";

	db_query($sql, "could not add item movement type");
}

function update_movement_type($type_id, $name, $linked_field, $group_id)
{
	$sql = "UPDATE ".TB_PREF."movement_types SET 
                    name =".db_escape($name).", 
                    linked_field = ".db_escape($linked_field).", 
                    group_id = ".db_escape($group_id)." 
			WHERE id=".db_escape($type_id);

	db_query($sql, "could not update item movement type");
}

function get_all_movement_types($all=false)
{
	$sql = "SELECT mt.*,
                    ln.value_name linked_name 
                FROM ".TB_PREF."movement_types mt 
                    LEFT JOIN ".TB_PREF."list_values ln 
                        ON ln.id = mt.group_id ";
        
	if (!$all) $sql .= " WHERE !mt.inactive ";
    
    $sql .= " ORDER BY mt.type ";

	return db_query($sql, "could not get all item movement types");
}

function get_movement_type($type_id)
{
	$sql = "SELECT * FROM ".TB_PREF."movement_types WHERE id=".db_escape($type_id);

	$result = db_query($sql, "could not get item movement type");

	return db_fetch($result);
}

function delete_movement_type($type_id)
{
	$sql="DELETE FROM ".TB_PREF."movement_types WHERE id=".db_escape($type_id);

	db_query($sql, "could not delete item movement type");
}

function get_stock_movements($stock_id, $StockLocation,	$BeforeDate, $AfterDate, $project_id, $order_by=0){
    $before_date = date2sql($BeforeDate);
    $after_date = date2sql($AfterDate);
    
    $sql = "SELECT sm.trans_id, sm.type, sm.trans_no, sm.tran_date, sm.person_id, sm.qty, sm.reference, pm.description project_name,
                    sm.standard_cost, u.name tran_unit_name, sm.tran_qty, sm.convertion_factor, sm.loc_code";

//	if(!$StockLocation){
//            $sql .= ", sm.loc_code";
//	}

        $sql.= " FROM ".TB_PREF."stock_moves sm 
                    LEFT JOIN ".TB_PREF."project_tasks pt 
                        ON sm.task_id = pt.task_id 
                    LEFT JOIN ".TB_PREF."projects_master pm  
                        ON pm.project_id = pt.project_id 
                    LEFT JOIN ".TB_PREF."item_units u   
                        ON sm.tran_unit = u.abbr 
                 WHERE";

        if($StockLocation){
            $sql.= " sm.loc_code = ".db_escape($StockLocation)." AND";
        }

        $sql .= " sm.tran_date >= '". $after_date . "'
                  AND sm.tran_date <= '" . $before_date . "'
                  AND sm.stock_id = ".db_escape($stock_id);

        if($project_id > 0){
            $sql .= " AND pt.project_id = ".db_escape($project_id);
        }

        if($order_by > 0){
            $sql .= " ORDER BY sm.trans_id";
        }
        else{
            $sql .= " ORDER BY sm.tran_date, sm.trans_id";
        }

  return db_query($sql, "could not query stock moves");
}

function get_stock_movements_before($stock_id, $StockLocation, $AfterDate)
{
	$after_date = date2sql($AfterDate);
	$sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id);
	if ($StockLocation)
		$sql .= " AND loc_code=".db_escape( $StockLocation);
	$sql .= " AND tran_date < '" . $after_date . "'";
	$before_qty = db_query($sql, "The starting quantity on hand could not be calculated");

	$before_qty_row = db_fetch_row($before_qty);
	return $before_qty_row[0];
}

function movement_types_in_stock_moves($selected_id)
{
	$sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_moves 
		WHERE type=" . ST_INVADJUST. " AND person_id=".db_escape($selected_id);

	$result = db_query($sql, "could not query stock moves");
	$myrow = db_fetch_row($result);
	return ($myrow[0] > 0); 
}

function get_movement_type_group_duplicates($type, $group_id, $selected_id=0){
    
    $sql = "SELECT * 
            FROM ".TB_PREF."movement_types 
            WHERE type =".db_escape($type)." 
                AND group_id = ".db_escape($group_id);
    
    if($selected_id > 0){
        $sql .= " AND id <> ".db_escape($selected_id);
    }

    $result = db_query($sql, "get_movement_type_group_duplicates");

    return db_fetch($result);
    
}

function get_movement_type_group_mismatching_linked_fields($group_id, $linked_field_type){
    
    $sql = "SELECT * 
            FROM ".TB_PREF."movement_types 
            WHERE group_id = ".db_escape($group_id)." 
                AND linked_field <> ".db_escape($linked_field_type)." 
            ORDER BY type ";

    $result = db_query($sql, "get_movement_type_group_mismatching_linked_fields");

    return db_fetch($result);
    
}

function get_matching_movement_type($source_trans_type, $source_movement_type, $source_linked_field_type, $target_trans_type){
    
    $sql = "SELECT tmt.* 
            FROM ".TB_PREF."movement_types smt 
                JOIN ".TB_PREF."movement_types tmt 
            WHERE smt.id = ".db_escape($source_movement_type)." 
                AND smt.type = ".db_escape($source_trans_type)." 
                AND smt.linked_field = ".db_escape($source_linked_field_type)." 
                AND smt.group_id = tmt.group_id 
                AND smt.linked_field = tmt.linked_field 
                AND tmt.type = ".db_escape($target_trans_type);

    $result = db_query($sql, "get_movement_type_group_mismatching_linked_fields");

    return db_fetch($result);
}