<?php
/**********************************************************************
***********************************************************************/

function add_item_purchasing_data($supplier_id, $stock_id, 
        $price, $discount,
        $project_id, $suppliers_uom, 
        $effective_date, $expiry_date, 
        $supplier_description ="",$supplier_itemcode = "")
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
        
	$sql = "INSERT INTO ".TB_PREF."purch_data (
                supplier_id, stock_id, 
                price, discount, 
                project_id, suppliers_uom,
                effective_date, expiry_date, 
                supplier_description, supplier_itemcode, 
                created_by, created_on) 
            VALUES (";
	$sql .= db_escape($supplier_id).", ".db_escape($stock_id). ", "
            .$price . ", ".db_escape($discount ). ", "
            .db_escape($project_id). ", ".db_escape($suppliers_uom ). ", "
            .db_escape(date2sql($effective_date )). ", ".db_escape(date2sql($expiry_date )). ", "
            .db_escape($supplier_description) . ", ".db_escape($supplier_itemcode). ", "
            .db_escape($created_by) . ", ".db_escape($created_on) . ")";

	db_query($sql,"The supplier purchasing details could not be added");
}

function update_item_purchasing_data($selected_id, $price, $discount, 
                    $project_id, $suppliers_uom, 
                    $effective_date, $expiry_date, 
                    $supplier_description ="", $supplier_itemcode = "")
{
	$sql = "UPDATE ".TB_PREF."purch_data SET 
                        price=" . $price . ",
                        discount=".db_escape($discount) . ",
                        project_id=".db_escape($project_id) . ",
                        suppliers_uom=".db_escape($suppliers_uom) . ",
                        effective_date=".db_escape(date2sql($effective_date)) . ",
                        expiry_date=".db_escape(date2sql($expiry_date)) . ",
                        supplier_description=" . db_escape($supplier_description) . ",
                        supplier_itemcode=" . db_escape($supplier_itemcode) . " 
            WHERE id=".db_escape($selected_id);
    
	db_query($sql,"The supplier purchasing details could not be updated");
}

function delete_item_purchasing_data($selected_id)
{
	$sql = "DELETE FROM ".TB_PREF."purch_data WHERE id=".db_escape($selected_id);
    
	db_query($sql,"could not delete purchasing data");
}

function get_items_purchasing_data($stock_id)
{
    $sql = "SELECT pd.*, 
                    s.supp_name, s.curr_code,
                    pm.project_name,
                    iu.name unit_name 
            FROM ".TB_PREF."purch_data pd 
                    INNER JOIN ".TB_PREF."suppliers s ON 
                            pd.supplier_id= s.supplier_id 
                    LEFT JOIN ".TB_PREF."item_units iu ON 
                            pd.suppliers_uom = iu.abbr 
                    LEFT JOIN ".TB_PREF."projects_master pm ON 
                            pd.project_id= pm.project_id
            WHERE stock_id = ".db_escape($stock_id);

    return db_query($sql, "The supplier purchasing details for the selected part could not be retrieved");
}

function get_item_purchasing_data($selected_id)
{
	$sql = "SELECT pd.*, s.supp_name 
            FROM ".TB_PREF."purch_data pd
                    INNER JOIN ".TB_PREF."suppliers s ON 
                            pd.supplier_id=s.supplier_id
            WHERE pd.id=".db_escape($selected_id);

	$result = db_query($sql, "The supplier purchasing details for the selected supplier and item could not be retrieved");

	return db_fetch($result);
}

function get_supplier_price($stock_id, $supplier_id, $project_id, $unit, $date)
{
    if($project_id < 0){
        $project_id = 0;
    }
    
    $sql = "SELECT price, discount    
            FROM ".TB_PREF."purch_data
            WHERE stock_id = ".db_escape($stock_id)." 
                    AND supplier_id = ".db_escape($supplier_id)." 
                    AND (project_id = ".db_escape($project_id)." OR project_id = 0) 
                    AND suppliers_uom = ".db_escape($unit)." 
                    AND effective_date <= ".db_escape(date2sql($date))." 
                    AND expiry_date >= ".db_escape(date2sql($date))." 
                    
            ORDER BY effective_date DESC, price LIMIT 1";
    
	$result = db_query($sql, "Could not get_supplier_price");
    return db_fetch($result);
}

function get_purchase_minimum_price($stock_id, $project_id, $unit, $date)
{
    if($project_id < 0){
        $project_id = 0;
    }
    
    $p[] = array();
    $result = get_item_supplier_price_supplers($stock_id, $project_id, $unit, $date);
    while ($myrow = db_fetch($result)){
        $sp = get_supplier_price($stock_id, $myrow['supplier_id'], $project_id, $unit, $date);
        $p[] = array("discounted_price" => $sp['price'] * (1 - $sp['discount']/100), 
                    "price" => $sp['price'], 
                    "discount" => $sp['discount'], 
                    "supplier_id" => $myrow['supplier_id']);
    }
    
    $min_price = 9999999999;
    $m = array("price" => 0, "discount" => 0, "supplier_id" => 0);
    
    foreach ($p as $price) {
        if(isset($price['discounted_price'])){
            $temp_price = $price['discounted_price'];
            if($temp_price < $min_price){
                $min_price = $temp_price;
                $m = array("price" => $price['price'], "discount" => $price['discount'], "supplier_id" => $price['supplier_id']);
            }
        }
    }
    
    return $m;
}

function get_item_supplier_price_supplers($stock_id, $project_id, $unit, $date)
{
    $sql = "SELECT DISTINCT supplier_id  
            FROM ".TB_PREF."purch_data
            WHERE stock_id = ".db_escape($stock_id)." 
                    AND suppliers_uom = ".db_escape($unit)." 
                    AND effective_date <= ".db_escape(date2sql($date))." 
                    AND expiry_date >= ".db_escape(date2sql($date))." 
                    AND project_id = ".db_escape($project_id);
    
	return db_query($sql, "Could not get_purchase_minimum_price");
}

function get_suppliers_purchasing_data($stock_id, $supplier_id)
{
    $sql = "SELECT supplier_id 
                FROM ".TB_PREF."purch_data
                    WHERE stock_id = ".db_escape($stock_id)." AND 
                            supplier_id = ".db_escape($supplier_id)."
        ";
    $result = db_query($sql, "could not retrieve minimum price");
    return db_fetch($result);
}

?>