<?php

function add_attachment($type, $trans_no, $description, $filename, $unique_name, $filesize, $filetype, $line_id=0) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    $date = date2sql(Today());
    
	$sql = "INSERT INTO ".TB_PREF."attachments (type_no, trans_no, description, 
                                                    filename, unique_name, filesize, 
                                                    filetype, tran_date, line_id,
                                                    created_by, created_on) 
                VALUES (".db_escape($type).", ".db_escape($trans_no).", ".db_escape($description).", 
                        ".db_escape($filename).", ".db_escape($unique_name).", ".db_escape($filesize).", 
                        ".db_escape($filetype).", ".db_escape($date).", ".db_escape($line_id).", 
                        ".db_escape($created_by).", ".db_escape($created_on).")";
        
	db_query($sql, "add_attachment");
        return db_insert_id();
}

function update_attachment($selected_id, $description, $filename, $unique_name, $filesize, $filetype) {
    $date = date2sql(Today());
    
	$sql = "UPDATE ".TB_PREF."attachments 
                SET description=".db_escape($description).", ";
        
            if ($filename != "") {
		$sql .= "filename = ".db_escape($filename).",
                        unique_name = ".db_escape($unique_name).",
                        filesize = ".db_escape($filesize).",
                        filetype = ".db_escape($filetype).",";
            }	
            
	$sql .= "tran_date = ".db_escape($date)." 
                WHERE id = ".db_escape($selected_id);
        
	db_query($sql, "update_attachment");
        return db_num_affected_rows();
}

function delete_attachment($id) {
    $sql = "DELETE 
            FROM ".TB_PREF."attachments 
            WHERE id = ".db_escape($id);
    
	db_query($sql, "Could not delete attachment");
        return db_num_affected_rows();
}

function get_attachment_string($type, $id)
{
	global $path_to_root;
    $str_return = "";    
    $sql = "SELECT * FROM ".TB_PREF."attachments WHERE type_no=".db_escape($type)." AND trans_no=".db_escape($id)." ORDER BY trans_no";
    $return = db_query($sql, "Could not retrieve attachments");
    while ($attachment = db_fetch($return))
    {
        if (strlen($str_return))
            $str_return = $str_return . " \n";    
        $str_return .= _("Attached File:")." <a href='$path_to_root/admin/attachments.php?vw=".$attachment["id"]." ' target='blanc_'> ". 
        	$attachment["id"] . " " . $attachment["description"]. " - ". $attachment["filename"]."</a><br>";
    }
    return $str_return . "<br>";
}

function get_attached_documents($type, $trans_no=false)
{
	$sql = "SELECT * FROM ".TB_PREF."attachments WHERE type_no=".db_escape($type);
	if ($trans_no)
		$sql .= " AND trans_no=".db_escape($trans_no);
	$sql .= " ORDER BY trans_no";
	return db_query($sql, "Could not retrieve attachments");
}

function get_attachment($id)
{
	$sql = "SELECT * FROM ".TB_PREF."attachments WHERE id=".db_escape($id);
	$result = db_query($sql, "Could not retrieve attachments");
	return db_fetch($result);
}

function has_attachment($type, $id) {
    $sql = "SELECT DISTINCT * 
            FROM ".TB_PREF."attachments 
            WHERE type_no = ".db_escape($type)." 
                AND trans_no = ".db_escape($id);
    
	$result = db_query($sql, "has_attachment");
	$myrow = db_fetch($result);
        
            if(is_array($myrow)) {
		return $myrow['id'];
            } else {
		return 0;
            }
}

function get_sql_for_attached_documents($type, $trans_no=0, $line_id=0) {
    $sql = "SELECT id, trans_no, line_id, description, filename, filesize, filetype, tran_date, type_no, created_by 
            FROM ".TB_PREF."attachments 
            WHERE type_no = ".db_escape($type);

	if ($trans_no > -1) {
            $sql .= " AND trans_no = ".db_escape($trans_no);
        }
	if ($line_id > 0) {
            $sql .= " AND line_id = ".db_escape($line_id);
        }

	$sql .= " ORDER BY trans_no DESC";

	return $sql;
}

function move_trans_attachments($type, $trans_from, $trans_to)
{
	$sql = "UPDATE ".TB_PREF."attachments SET trans_no=".db_escape($trans_to)
		." WHERE type_no=".db_escape($type)." AND trans_no=".db_escape($trans_from);
	db_query($sql, 'cannot move atachments');
}

function get_sql_for_transaction_attached_documents($type, $trans_no)
{
	return "SELECT trans_no, description, filename, filesize, filetype, tran_date, id, type_no, unique_name  
                FROM ".TB_PREF."attachments 
                WHERE type_no=".db_escape($type)." 
                    AND trans_no=".db_escape($trans_no)." 
                ORDER BY trans_no DESC";
}

function get_transaction_attached_documents($type, $trans_no) {
    $sql = "SELECT trans_no, description, filename, filesize, filetype, tran_date, id, type_no, unique_name  
            FROM ".TB_PREF."attachments 
            WHERE type_no=".db_escape($type)." 
                AND trans_no=".db_escape($trans_no)." 
            ORDER BY trans_no DESC";

    return db_query($sql, "get_transaction_attached_documents");
}

function get_attachments_by_filters($id=0, $type=-1, $trans_no=0, $line_id=0, $fetch=false) {
    $sql = "SELECT * 
            FROM ".TB_PREF."attachments 
            WHERE 1 = 1 ";

	if ($id > 0) {
            $sql .= " AND id = ".db_escape($id);
        }
	if ($type > -1) {
            $sql .= " AND type_no = ".db_escape($type);
        }
	if ($trans_no > 0) {
            $sql .= " AND trans_no = ".db_escape($trans_no);
        }
	if ($line_id > 0) {
            $sql .= " AND line_id = ".db_escape($line_id);
        }

	$result = db_query($sql, "get_attachments_by_filters");
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}