<?php

include_once($path_to_root . "/includes/ui/items_cart.inc");
    

function get_stock_count_details($stock_count_id, $sheet_no)
{
    $sql = "SELECT scd.*,
                    sm.description,
                    bu.name base_unit_name,
                    tu.name tran_unit_name                     
            FROM ".TB_PREF."stock_count_details as scd 
                    INNER JOIN ".TB_PREF."stock_master as sm ON 
                            sm.stock_id = scd.stock_id 
                    JOIN ".TB_PREF."item_units bu ON 
                            sm.units =  bu.abbr 
                    LEFT JOIN ".TB_PREF."item_units tu ON 
                            scd.tran_unit =  tu.abbr 
            WHERE scd.stock_count_no = ".db_escape($stock_count_id)." AND 
                    scd.sheet_no = ".db_escape($sheet_no)." 
            ORDER BY scd.stock_id ";
                    
                    

           return db_query($sql, "Could not retreive stock count details"); 
}
   

function get_stock_count_counted_details($stock_count_id, $sheet_no)
{

    $sql = "SELECT scd.*,
                    sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost
            FROM ".TB_PREF."stock_count_details as scd 
                    INNER JOIN ".TB_PREF."stock_master as sm ON 
                            sm.stock_id = scd.stock_id 

            WHERE scd.stock_count_no = ".db_escape($stock_count_id)." AND 
                    scd.sheet_no = ".db_escape($sheet_no);               
                    

           return db_query($sql, "Could not get_stock_count_details_all"); 
}

function get_stock_count_detail($id)
{
    $sql = "SELECT scd.*,
                    sm.description item_description,
                    bu.name base_unit_name,
                    bu.name tran_unit_name                     
            FROM ".TB_PREF."stock_count_details as scd 
                    INNER JOIN ".TB_PREF."stock_master as sm ON 
                            sm.stock_id = scd.stock_id 
                    JOIN ".TB_PREF."item_units bu ON 
                            sm.units =  bu.abbr 
                    LEFT JOIN ".TB_PREF."item_units tu ON 
                            scd.unit =  tu.abbr  
            WHERE scd.id = ".db_escape($id);  
                    
                    

    $result = db_query($sql, "Could not retreive stock count details"); 
    return db_fetch($result);
}

function update_stock_count_details($stock_count_id, $stock_id, $counted_qty, $update_date, $id_user)
{
       begin_transaction();
	hook_db_prewrite($order, 17);
       
       $sql = "UPDATE ".TB_PREF."stock_count_details SET counted_qty = $counted_qty , updated_on = '$update_date', updated_by = '$id_user'
                            , status_id = 15
		WHERE stock_id = ".db_escape($stock_id)." AND stock_count_no = ".db_escape($stock_count_id);

	db_query($sql,"Stock count details cannot be updated");
        $no_of_rows_updated = db_num_affected_rows();
//        if($no_of_rows_updated == 0){
//            cancel_transaction();
//            return 0;
            
             $sql = "UPDATE ".TB_PREF."stock_counts SET status_id = 15
		WHERE stock_count_no = ".db_escape($stock_count_id);

	db_query($sql,"Stock count cannot be updated");
        
        hook_db_postwrite($order, 17);
	commit_transaction();
}

function get_stock_count_header($stock_count_id)
{
        
       $sql = "SELECT 
                    sc.*,
                    loc.location_name,
                    pm.description project_description,
                    pt.description task_description 
               FROM ".TB_PREF."stock_counts as sc 
                        INNER JOIN ".TB_PREF."locations as loc ON 
                            sc.loc_code = loc.loc_code 
                        LEFT JOIN ".TB_PREF."projects_master pm ON 
                            sc.project_id = pm.project_id 
                        LEFT JOIN ".TB_PREF."project_tasks pt ON 
                            sc.task_id = pt.task_id 
                WHERE sc.stock_count_no = ".db_escape($stock_count_id);	

                   
       $result = db_query($sql, "Could not retreive stock count header");
       
       return db_fetch($result);
}


function get_stock_count_header_record($stock_count_id)
{
        
        $sql = "SELECT  sc.*
                FROM ".TB_PREF."stock_counts as sc                         
                WHERE sc.stock_count_no = ".db_escape($stock_count_id);	

                   
       $result = db_query($sql, "Could not retreive stock count header");
       
       return db_fetch($result);
}
       
function get_stock_count_complete_details($stock_count_id, $stock_id)
{
        $sql = "SELECT

                 scd.system_qty,
                 scd.standard_cost

                 FROM ".TB_PREF."stock_count_details as scd


                 WHERE scd.stock_count_no = $stock_count_id 
                     AND scd.stock_id = '$stock_id' ";

        return db_query($sql, "Could not retreive stock line details"); 
}
   
function get_stock_count_item($id)
{
	$sql = "SELECT *     
		FROM ".TB_PREF."stock_count_details 
		WHERE id = ".db_escape($id);	
	
	$result = db_query($sql,"Stock count item could not be retreived");
        
    return db_fetch($result);
}
      




function add_stock_count_header($loc_code, $reference, $status_id,
                                $tran_date, $comments, $project_id, $task_id )                                
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');        

    $sql = "INSERT INTO ".TB_PREF."stock_counts 
                  ( loc_code, reference,  
                    status_id, tran_date,
                    comments, project_id, task_id,
                    created_by, created_on )

       VALUES (".db_escape($loc_code).", ".db_escape($reference)."," 
                    .db_escape($status_id).",".db_escape(date2sql($tran_date))."," 
                    .db_escape($comments).",".db_escape($project_id).",".db_escape($task_id).","
                    .db_escape($id_user)."," .db_escape($datetime).")";
       
    db_query($sql, "Stock count could not be added");        
    return db_insert_id();
}

function add_stock_count_detail($stock_count_no, $sheet_no, $stock_id, 
                                $base_unit, $base_unit_good_qty, $base_unit_damaged_qty,
                                $tran_unit, $conversion_factor, $tran_unit_good_qty, $tran_unit_damaged_qty,
                                $system_qty, $standard_cost, $status_id)
{

//    return 1;
        
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date("Y-m-d H:i:s");
        
        $sql = "INSERT INTO ".TB_PREF."stock_count_details (
                        stock_count_no, sheet_no, 
                        stock_id, unit, 
                        status_id, good_qty, damaged_qty,
                        tran_unit, conversion_factor, 
                        tran_unit_good_qty, tran_unit_damaged_qty,
                        system_qty, standard_cost,
                        created_by, created_on) 
                VALUES (".db_escape($stock_count_no).",".db_escape($sheet_no).","
                        .db_escape($stock_id).",".db_escape($base_unit).","
                        .db_escape($status_id).",".db_escape($base_unit_good_qty).",".db_escape($base_unit_damaged_qty).","
                        .db_escape($tran_unit).",".db_escape($conversion_factor).","
                        .db_escape($tran_unit_good_qty).",".db_escape($tran_unit_damaged_qty).","
                        .db_escape($system_qty).",".db_escape($standard_cost).","
                        .db_escape($created_by).",".db_escape($created_on).")";
        
        db_query($sql, "Stock count detail could not be added");

}


function update_stock_count_detail($id, $sheet_no, $base_unit, $base_unit_good_qty, $base_unit_damaged_qty,
                                $tran_unit, $conversion_factor, $tran_unit_good_qty, $tran_unit_damaged_qty,
                                $system_qty, $status_id)
{
//    return 1;
    
    $user_id = $_SESSION['wa_current_user']->user;
    $time = date("Y-m-d H:i:s");
        
        $sql = "UPDATE ".TB_PREF."stock_count_details SET 
                        sheet_no=".db_escape($sheet_no).",
                        unit= ".db_escape($base_unit).",
                        good_qty=".db_escape($base_unit_good_qty).",
                        damaged_qty=".db_escape($base_unit_damaged_qty).",
                        tran_unit=".db_escape($tran_unit).",
                        conversion_factor=".db_escape($conversion_factor).",
                        tran_unit_good_qty=".db_escape($tran_unit_good_qty).",
                        tran_unit_damaged_qty=".db_escape($tran_unit_damaged_qty).",
                        system_qty=".db_escape($system_qty).",
                        updated_by=".db_escape($user_id).",
                        updated_on=".db_escape($time)." 
                WHERE id = ".$id." AND status_id IN(".SC_NEW.",".SC_COUNTING.")"; 

        db_query($sql, "Stock count header could not be updated");
        return db_num_affected_rows();
}


function delete_stock_count_detail($id)
{
        $sql = "DELETE FROM ".TB_PREF."stock_count_details 
                WHERE id = ".$id." AND status_id IN(0, ".SC_NEW.",".SC_COUNTING.")"; 

        db_query($sql, "Could not delete_stock_count_detail");
        return db_num_affected_rows();
}



function update_stock_count_header($stock_count_no, $loc_code,
                                    $reference, $status_id, $tran_date,
                                    $comments, $project_id, $task_id)                                  
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');     
    
        $sql = "UPDATE ".TB_PREF."stock_counts 
                SET     reference=".db_escape($reference).",
                        loc_code= ".db_escape($loc_code).",
                        tran_date=".db_escape(date2sql($tran_date)).",
                        comments=".db_escape($comments).",
                        status_id=".db_escape($status_id).",                      
                        project_id=".db_escape($project_id).",
                        task_id=".db_escape($task_id).",
                        updated_by=".db_escape($id_user).",   
                        updated_on=".db_escape($datetime)."
                            
                WHERE stock_count_no = ".$stock_count_no." AND 
                        status_id = ".SC_NEW;

        db_query($sql, "Stock count could not be updated");
        return db_num_affected_rows();
}



function check_stock_count_reference($id, $reference)
{
        $sql = "SELECT reference  
                    FROM ".TB_PREF."stock_counts
                    WHERE  stock_count_no <> $id AND 
                            reference =".db_escape($reference);
    
        $result = db_query($sql, "Stock count reference could not be retrieved");
        
        return db_fetch($result);
        
}

function update_stock_count_header_status($stock_count_no, $new_status_id, $old_status_id)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date("Y-m-d H:i:s");
        
        $sql = "UPDATE ".TB_PREF."stock_counts SET 
                        status_id=".db_escape($new_status_id).",
                        updated_by=".db_escape($updated_by).",
                        updated_on=".db_escape($updated_on)." 
                WHERE stock_count_no = ".$stock_count_no." AND 
                        status_id = ".$old_status_id; 

        db_query($sql, "Could not update_stock_count_status");
        return db_num_affected_rows();
}


function update_stock_count_completed($stock_count_no, $new_status_id, $old_status_id, $total_adjustment_value)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date("Y-m-d H:i:s");
        
        $sql = "UPDATE ".TB_PREF."stock_counts SET 
                        status_id=".db_escape($new_status_id).",
                        total_adj_value=".db_escape($total_adjustment_value).",
                        updated_by=".db_escape($updated_by).",
                        updated_on=".db_escape($updated_on)." 
                WHERE stock_count_no = ".$stock_count_no." AND 
                        status_id = ".$old_status_id; 

        db_query($sql, "Could not update_stock_count_status");
        return db_num_affected_rows();
}

function update_stock_count_details_all_status($stock_count_no, $new_status_id, $old_status_id)
{
        
        $sql = "UPDATE ".TB_PREF."stock_count_details SET 
                        status_id=".db_escape($new_status_id)." 
                WHERE stock_count_no = ".$stock_count_no." AND 
                        status_id = ".$old_status_id; 

        db_query($sql, "Could not update_stock_count_details_all_status");
}



function get_stock_count_sheets($stock_count_id)
{
        
        $sql = "SELECT DISTINCT scd.sheet_no 
                FROM ".TB_PREF."stock_count_details as scd 
                WHERE scd.stock_count_no = ".db_escape($stock_count_id);
               
        return db_query($sql, "Could not get_stock_count_sheets"); 
}


function check_stock_count_item_exists($stock_count_id, $stock_id)
{
        $sql = "SELECT sheet_no 
                FROM ".TB_PREF."stock_count_details as scd
                WHERE scd.stock_count_no = $stock_count_id 
                        AND scd.stock_id = ".db_escape($stock_id);

        $result = db_query($sql, "Could not check_stock_count_item_exisits"); 
        
        return db_fetch($result);
}


function get_stock_count_duplicate_items($stock_count_id)
{
    $sql = "SELECT count(stock_id) cnt , stock_id 
            FROM ".TB_PREF."stock_count_details  
            WHERE stock_count_no = ".db_escape($stock_count_id)." 
            GROUP BY stock_id 
            HAVING cnt > 1 
            LIMIT 20 ";
    
    return db_query($sql, "Could not get_stock_count_duplicate_items"); 
}


function get_stock_count_duplicate_item_details($stock_count_id, $stock_id)
{
    $sql = "SELECT scd.stock_id, scd.sheet_no, sm.description item_description    
                FROM ".TB_PREF."stock_count_details as scd 
                        JOIN ".TB_PREF."stock_master sm ON 
                                scd.stock_id = sm.stock_id 
                WHERE scd.stock_count_no = $stock_count_id 
                        AND scd.stock_id = ".db_escape($stock_id);
    
    return db_query($sql, "Could not get_stock_count_duplicate_item_details"); 
}


function get_stock_count_sheet_item($stock_count_id, $sheet_no, $stock_id)
{
        $sql = "SELECT * 
                FROM ".TB_PREF."stock_count_details as scd
                WHERE scd.stock_count_no = ".db_escape($stock_count_id)."  
                        AND scd.sheet_no = ".db_escape($sheet_no)." 
                        AND scd.stock_id = ".db_escape($stock_id);

        $result = db_query($sql, "Could not check_stock_count_item_exisits"); 
        
        return db_fetch($result);
}
?>


