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


function add_material_group($code, $description, $long_description, 
                                $unit, $cost)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."material_groups
                (code, description, long_description,
                unit, cost, 
                created_by, created_on)
        
            VALUES
                (".db_escape($code).", ".db_escape($description).", 
                    ".db_escape($long_description).", ".db_escape($unit).", 
                    ".db_escape($cost).", 
                    ".db_escape($created_by).", ".db_escape($created_on).")";
           
    db_query($sql);
}


function update_material_group($selected_id, $code, $description, $long_description, $unit, $cost)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."material_groups
            SET code=".db_escape($code).",
                description=".db_escape($description).",
                long_description=".db_escape($long_description).",
                unit=".db_escape($unit).",
                cost=".db_escape($cost).",
                updated_by=".db_escape($updated_by).",
                updated_on=".db_escape($updated_on)." 
            WHERE id=".db_escape($selected_id);
                    
    db_query($sql);
    
}

function get_all_material_groups()
{
    $sql = "SELECT img.*,
                    iu.name unit_name

            FROM ".TB_PREF."material_groups img                
                LEFT JOIN ".TB_PREF."item_units iu ON
                    img.unit = iu.abbr
                        
            WHERE 1=1";
    
    return db_query($sql);

}

function get_material_group_item($selected_id)
{
    $sql = "SELECT mg.*,
                    mgun.name material_group_unit_name
        
            FROM ".TB_PREF."material_groups mg
                LEFT JOIN ".TB_PREF."item_units mgun ON
                        mg.unit = mgun.abbr 
                
            WHERE mg.id=".db_escape($selected_id);
    
    $result = db_query($sql);
    return db_fetch($result);
}


function delete_material_group_item($selected_id)
{
    $sql = "DELETE FROM ".TB_PREF."material_groups WHERE id=".db_escape($selected_id);
    
    db_query($sql);    
}

function get_material_group_average_cost($material_group_id)
{
    $sql = "SELECT SUM(img.conversion_factor * (sm.material_cost + labour_cost + overhead_cost))/COUNT(id) as_material_group_cost 
            FROM 0_item_material_groups img 
                JOIN 0_stock_master sm ON 
                    img.stock_id = sm.stock_id 
            WHERE img.material_group_id = $material_group_id 
            GROUP BY img.material_group_id ";												

    $result = db_query($sql);
    return db_fetch($result);

}

function update_material_group_cost($selected_id, $material_group_cost)
{
    $last_calculated_by = $_SESSION['wa_current_user']->user;
    $last_calculated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."material_groups
            SET cost=".db_escape($material_group_cost).",
                last_calculated_by=".db_escape($last_calculated_by).",
                last_calculated_on=".db_escape($last_calculated_on)." 
            WHERE id=".db_escape($selected_id);
    
    db_query($sql);
}


function get_material_group_record($id)
{
    $sql = "SELECT *
            FROM ".TB_PREF."material_groups
            WHERE id=".db_escape($id);
    
    $result = db_query($sql);
    return db_fetch($result);
}


function get_material_group_record_by_code($material_group_code)
{
    $sql = "SELECT *
            FROM ".TB_PREF."material_groups
            WHERE code=".db_escape($material_group_code);
    
    $result = db_query($sql);
    return db_fetch($result);
}
 

function check_material_group_code_exists($code, $selected_id)
{
    $sql = "SELECT id 
            FROM ".TB_PREF."material_groups
            WHERE code = ".db_escape($code)."  AND id <> ".db_escape($selected_id);
    
    $result = db_query($sql);
    $row = db_fetch($result);
    if(isset($row['id']) && $row['id'] > 0){
        return true;
    }
    else{
        return false;
    }
 
}


function check_material_group_description_exists($description, $selected_id)
{
    $sql = "SELECT id
            FROM ".TB_PREF."material_groups
            WHERE description = ".db_escape($description)." AND id <> ".db_escape($selected_id);
    
    $result = db_query($sql);
    $row = db_fetch($result);
    if(isset($row['id']) && $row['id'] > 0){
        return true;
    }
    else{
        return false;
    }

}



?>