<?php

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

function get_sql_for_stock_count_search()
{
	global $all_items;
        
	$sql = "SELECT 
                        sc.stock_count_no,
                        sc.reference,
                        sc.loc_code,
                        sc.tran_date,
                        sc.status_id,
                        loc.location_name 
		FROM ".TB_PREF."stock_counts as sc,
                        ".TB_PREF."locations as loc            
		WHERE sc.loc_code = loc.loc_code";
        
                if(isset($_POST['OrdersAfterDate']) && $_POST['OrdersToDate'])
                {    
                    $data_after = date2sql($_POST['OrdersAfterDate']);
                    $date_before = date2sql($_POST['OrdersToDate']);

                    $sql .= " AND sc.tran_date >= '$data_after'";
                    $sql .= " AND sc.tran_date <= '$date_before'";
                }
                
                if (isset($_POST['Location']) && $_POST['Location'] != $all_items)
		{
                    $sql .= " AND sc.loc_code = ".db_escape($_POST['Location']);
		}

	return $sql;
        
}       
//    
//function get_stock_count_lines($order_no, &$order)
//{
//            
//    $sql = "SELECT 
//                scd.id,
//                scd.stock_id,
//                scd.status_id,
//                scd.standard_cost,
//                scd.created_by,
//                scd.created_on,
//                sm.description,
//                scd.counted_qty
//                
//                FROM ".TB_PREF."stock_master as sm,
//                       ".TB_PREF."stock_count_details as scd
//
//                WHERE scd.stock_count_no = $order_no AND sm.stock_id = scd.stock_id ";  
//                
//    return db_query($sql, "Could not retreive stock line details");
//
//   // $result = db_query($sql, "The lines on the job order cannot be retrieved");
//} 

function get_stock_count_details($stock_count_id)
{
    $sql = "SELECT
                    scd.*,
                    sm.description 
            FROM ".TB_PREF."stock_count_details as scd,
                    ".TB_PREF."stock_master as sm

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

           return db_query($sql, "Could not retreive stock count details"); 
}
   
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.reference,
                    sc.loc_code,
                    loc.location_name,
                    sc.created_on,
                    sc.status_id,
                    sc.comments,
                    scd.updated_on,
                    sc.tran_date,
                    sc.project_id,
                    sc.task_id,
                    scd.system_qty,
                    scd.standard_cost,
                    pm.description project_description,
                    pt.description task_description,
                    ap.approval_path_name approval_path_description
               FROM "
                    .TB_PREF."stock_counts as sc ,"
                    .TB_PREF."locations as loc ,"
                    .TB_PREF."projects_master pm,"
                    .TB_PREF."project_tasks pt,"
                    .TB_PREF."approval_path ap,"
                    .TB_PREF."stock_count_details as scd
                WHERE 
                     sc.stock_count_no = $stock_count_id 
                            AND sc.stock_count_no = scd.stock_count_no
                            AND sc.project_id = pm.project_id 
                            AND sc.task_id = pt.task_id
                            AND pt.approval_path_id = ap.approval_path_id
                            AND sc.loc_code = loc.loc_code ";
                   
       $result = db_query($sql, "Could not retreive stock count header details");
       
       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 get_stock_count_view_details($stock_count_id)
{
    
        $sql = "SELECT
                    scd.stock_id,
                    scd.counted_qty,
                    scd.system_qty,
                    scd.standard_cost,
                    scd.status_id,
                    sm.description,
                    sm.units 
                FROM ".TB_PREF."stock_count_details as scd,
                    ".TB_PREF."stock_master as sm 
                WHERE scd.stock_id = sm.stock_id AND 
                    scd.stock_count_no = ".db_escape($stock_count_id);
               
        return db_query($sql, "Could not retreive stock count view details"); 
}

function get_stock_count_view_header($stock_count_id)
{
    
        $sql = "SELECT
                    sc.stock_count_no,
                    sc.reference,
                    sc.loc_code,
                    sc.tran_date,
                    sc.status_id,
                    sc.comments,
                    l.location_name  
                FROM ".TB_PREF."stock_counts as sc,
                    ".TB_PREF."locations l  
                WHERE sc.loc_code = l.loc_code AND 
                    sc.stock_count_no = ".db_escape($stock_count_id);
               
        $result = db_query($sql, "Could not retreive stock count view header"); 
        
        return db_fetch($result);
}

function add_stock_count(&$order, $loc_code, $reference, 
                        $status_id, $tran_date, $comments, $project_id, $task_id)
        
{
	global $loc_notification, $path_to_root, $Refs;

	begin_transaction();
        
	hook_db_prewrite($order, $order->trans_type);
	$order_no = get_next_trans_no($order->trans_type);
	$created_on = date("Y-m-d H:i:s");
        $created_by = $_SESSION['wa_current_user']->user;
         
        $order->order_no = add_stock_count_header($reference, $loc_code, $status_id,
                                date2sql($tran_date), $comments, $project_id, $task_id, 
                                $created_by, $created_on);
        
	if($order->order_no > 0){
            
        }
        else{
            cancel_transaction();
            return $order->order_no;
        }

	foreach ($order->line_items as $line)
	{
            add_stock_count_detail($line->row_id, $order->order_no, $line->stock_id,
                    $status_id, $line->quantity, 
                    $created_by, $created_on);
	} 

	$Refs->save($order->trans_type, $order->order_no, $reference);

	hook_db_postwrite($order, $order->trans_type);
	commit_transaction();
        
	return $order->order_no;
}

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($id, $stock_count_no, $stock_id, 
                    $status_id, $counted_qty,
                    $created_by, $created_on, 
                    $updated_by = 0, $updated_on = '')
{
        if($updated_by == 0){
            $status_id =  SC_NEW;
        }
//        $created_by = $_SESSION['wa_current_user']->user;
//        $created_on = date2sql(date('Y/m/d'));
        
        $sql = "INSERT INTO ".TB_PREF."stock_count_details (
                        id, stock_count_no, stock_id, 
                        status_id, counted_qty,
                        created_by, created_on, 
                        updated_by, updated_on) 
                VALUES (".db_escape($id).",".db_escape($stock_count_no).",".db_escape($stock_id).","
                        .db_escape($status_id).",".db_escape($counted_qty).","
                        .db_escape($created_by).",".db_escape($created_on).","
                        .db_escape($updated_by).",";
        
        if($updated_by > 0){
            $sql .=  db_escape($updated_on);
        }
        else{
            $sql .=  db_escape($updated_on);
        }
        
        $sql .= ")";
        
        db_query($sql, "Stock count details could not be added");
        
        return db_insert_id();
}

function update_stock_count(&$order, $loc_code, $reference, 
                            $status_id, $tran_date, $comments, $project_id, $task_id)
{
        global $path_to_root;

	begin_transaction();
	hook_db_prewrite($order, $order->trans_type);

        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date("Y-m-d H:i:s");
        
        $num_affected_rows = update_stock_count_header($order->order_no,
                                                        $reference, $loc_code, $tran_date,
                                                        $comments, $updated_by, $updated_on,
                                                        $status_id, $project_id, $task_id);

        if($num_affected_rows == 1){
        }
        else{
            cancel_transaction();
            return $num_affected_rows;
        }
        
        $num_deleted_rows = delete_stock_count_details_all($order->order_no);
        if($num_deleted_rows > 0){
        }
        else{
            cancel_transaction();
            return $num_deleted_rows;
        }

	foreach ($order->line_items as $line)
	{
                $system_qty = 0;
                $standard_cost = 0;
                if($line->row_id == 0){
                    $updated_by = 0;
                    $updated_on = '';
                }
                if (!isset($line->created_on))
                {
                    $created_by = $_SESSION['wa_current_user']->user;
                    $created_on = date("Y-m-d H:i:s");
                    
                    $line->created_by = $created_by;
                    $line->created_on = $created_on;
                }
                add_stock_count_detail($line->row_id, $order->order_no, $line->stock_id,
                                    $status_id, $line->quantity,
                                    $line->created_by, $line->created_on, 
                                    $updated_by, $updated_on);
	}

	hook_db_postwrite($order, $order->trans_type);
	commit_transaction();

	return $order->order_no;      
}


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 delete_stock_count_details_all($stock_count_no)
{
        $sql = "DELETE FROM ".TB_PREF."stock_count_details 
                WHERE stock_count_no = ".$stock_count_no." AND 
                        status_id IN ('".SC_NEW."', '".SC_UPDATED."', '".SC_COUNTING."')"; 
        
        db_query($sql, "Stock count details could not be deleted");
        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 get_stock_count($stock_count_no)
{
    $sql = "SELECT *
            FROM ".TB_PREF."stock_counts
            WHERE stock_count_no=".db_escape($stock_count_no);
    
    $result = db_query($sql);
    return db_fetch($result);
    
}


?>


