<?php
/**********************************************************************
***********************************************************************/
function add_work_centre($name, $description)
{
	$sql = "INSERT INTO ".TB_PREF."workcentres (name, description)
		VALUES (".db_escape($name).",".db_escape($description).")";

	db_query($sql, "could not add work centre");
}

function update_work_centre($type_id, $name, $description)
{
	$sql = "UPDATE ".TB_PREF."workcentres SET name=".db_escape($name).", description=".db_escape($description)."
		WHERE id=".db_escape($type_id);

	db_query($sql, "could not update work centre");
}

function get_all_work_centres($all=false)
{
	$sql = "SELECT * FROM ".TB_PREF."workcentres";
	if (!$all) $sql .= " WHERE !inactive";

	return db_query($sql, "could not get all work centres");
}

function get_work_centre($id)
{
	$sql = "SELECT * FROM ".TB_PREF."workcentres WHERE id=".db_escape($id);

	$result = db_query($sql, "could not get work centre");

	return db_fetch($result);
}

function delete_work_centre($id)
{
	$sql="DELETE FROM ".TB_PREF."workcentres WHERE id=".db_escape($id);

	db_query($sql, "could not delete work centre");
}

function get_workcentre_total_outstanding_work($workcentre_id, $date = null)
{
    $sql = "SELECT SUM(remaining_duration) remaining_duration  
            FROM ".TB_PREF."workcentre_schedule 
            WHERE workcentre_id =".db_escape($workcentre_id);
            
            if($date){
                $sql .= " AND work_date =  ".db_escape($date);
            }
    $sql .= " AND completed <> 1";
    
    $result = db_query($sql, "could not get workcentre outstanding work");
    
    $row = db_fetch($result);
    if(isset($row['remaining_duration'])){
        return $row['remaining_duration'];
    }
    else{
        return 0;
    }
    
}

function get_workcentre_workorder_outstanding_work($workcentre_id, $workorder_id, $sequence)
{
    $sql = "SELECT SUM(ws.remaining_duration) 
            FROM ".TB_PREF."workcentre_schedule ws, ".TB_PREF."wo_processes wp  
            WHERE ws.wo_process_id = wp.id 
                    AND ws.workcentre_id = ".db_escape($workcentre_id)." 
                    AND wp.workorder_id =  ".db_escape($workorder_id)." 
                    AND wp.sequence <=  ".db_escape($sequence)." 
                    AND ws.completed <> 1 ";
    
    $result = db_query($sql, "could not get_workcentre_workorder_outstanding_work");
    
    $row = db_fetch($result);
    if(isset($row['remaining_duration'])){
        return $row['remaining_duration'];
    }
    else{
        return 0;
    }
}


function get_applicable_workcentre_shift($workcentre_id, $date)
{
	$sql = "SELECT * FROM ".TB_PREF."workcentre_shifts 
            WHERE workcentre_id = ".db_escape($workcentre_id)." AND start_date <=".db_escape($date)." 
            ORDER BY start_date DESC LIMIT 1";

	$result = db_query($sql, "Could not get_applicable_workcentre_shift");

	return db_fetch($result);
}

function add_workcentre_schedule_time($wo_process_id, $workcentre_id, $work_type_id, $wc_from_ts, $wc_duration_s, $user_id)
{
    $work_date = date('Y-m-d', $wc_from_ts);
    $work_start_date_time = date('Y-m-d H:i:s', $wc_from_ts);
    $created_on = date('Y-m-d H:i:s');
    
	$sql = "INSERT INTO ".TB_PREF."workcentre_schedule(
                        wo_process_id, workcentre_id, 
                        work_type, work_date, 
                        work_start_date_time, duration, 
                        remaining_duration, completed, 
                        created_by, created_on)
            VALUES (".db_escape($wo_process_id).",".db_escape($workcentre_id).", "
                        .db_escape($work_type_id).",".db_escape($work_date).", "
                        .db_escape($work_start_date_time).",".db_escape($wc_duration_s).", "
                        .db_escape($wc_duration_s).",".db_escape(0).", "
                        .db_escape($user_id).",".db_escape($created_on).") ";
            
	db_query($sql, "could not add work centre");
}

function get_workcentre_schedule_process_start_details($wo_process_id)
{
    $sql = "SELECT * FROM ".TB_PREF."workcentre_schedule  
            WHERE wo_process_id = ".db_escape($wo_process_id)." 
            ORDER BY work_start_date_time LIMIT 1";

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

function get_workcentre_schedule_process_end_details($wo_process_id)
{
    $sql = "SELECT * FROM ".TB_PREF."workcentre_schedule  
            WHERE wo_process_id = ".db_escape($wo_process_id)." 
            ORDER BY work_start_date_time DESC, duration DESC LIMIT 1";

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


function get_workcentre_schedule_wo_start_details($wo_id)
{
    $sql = "SELECT wcs.* 
            FROM ".TB_PREF."workcentre_schedule wcs, 
                ".TB_PREF."wo_processes wop 
            WHERE wcs.wo_process_id = wop.id 
            	AND wop.workorder_id = ".db_escape($wo_id)."  
            ORDER BY wcs.work_start_date_time LIMIT 1";
    
	$result = db_query($sql, "Could not get_workcentre_schedule_wo_start_details");
    
    return db_fetch($result);
    
}

function get_workcentre_schedule_wo_end_details($wo_id)
{
    $sql = "SELECT wcs.* 
            FROM ".TB_PREF."workcentre_schedule wcs, 
                ".TB_PREF."wo_processes wop 
            WHERE wcs.wo_process_id = wop.id 
            	AND wop.workorder_id = ".db_escape($wo_id)."  
            ORDER BY wcs.work_start_date_time DESC, wcs.duration DESC LIMIT 1";

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

function delete_workcentre_process_schedule($wo_process_id)
{
	$sql="DELETE FROM ".TB_PREF."workcentre_schedule WHERE wo_process_id=".db_escape($wo_process_id);

	db_query($sql, "could not delete_workcentre_process_schedule");
}

function get_workorder_schedule_end_time_details($workorder_id, $sequence)
{
    $sql = "SELECT ws.* 
            FROM ".TB_PREF."workorders wo, ".TB_PREF."workcentre_schedule ws, ".TB_PREF."wo_processes wp  
            WHERE wo.id = wp.workorder_id 
                    AND ws.wo_process_id = wp.id 
                    AND wo.id =  ".db_escape($workorder_id)." 
                    AND wp.sequence <=  ".db_escape($sequence)." 
                    AND ws.completed <> 1 
            ORDER BY wp.sequence DESC, work_start_date_time DESC  LIMIT 1";
    
    $sql = "SELECT ws.* 
            FROM ".TB_PREF."workorders wo, ".TB_PREF."workcentre_schedule ws, ".TB_PREF."wo_processes wp  
            WHERE wo.id = wp.workorder_id 
                    AND ws.wo_process_id = wp.id 
                    AND wo.id =  ".db_escape($workorder_id)." 
                    AND wp.sequence <=  ".db_escape($sequence)." 
                    AND ws.completed <> 1 
            ORDER BY wp.sequence DESC, DATE_ADD(work_start_date_time, INTERVAL duration SECOND) DESC  LIMIT 1";

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


function close_workcentre_process_schedules($process_id, $worktype = 0)
{
    $sql = "UPDATE ".TB_PREF."workcentre_schedule 
            SET completed = 1 
            WHERE wo_process_id = ".db_escape($process_id);
    
    if($worktype > 0){
        $sql .= " AND work_type = ".db_escape($worktype);
    }

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

function update_workcentre_schedule_completed_duration($id, $duration, $complete)
{
    $sql = "UPDATE ".TB_PREF."workcentre_schedule SET 
                    remaining_duration = remaining_duration - ". db_escape($duration).",
                    completed = ". db_escape($complete)." 
            WHERE id = ".db_escape($id);

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

function get_workcentre_process_open_schedule($process_id)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."workcentre_schedule 
            WHERE wo_process_id = ".db_escape($process_id)." 
                    AND work_type = ".db_escape(WOPWT_PROCESSING)." 
                    AND completed = 0 
            ORDER BY work_start_date_time";

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

function get_sql_for_workcentre_schedules($w_no,$wo,$workcentre,$from_date,$to_date)
{
	$sql = "SELECT
		ws.id,
                wc.name wc_name,		
                wo.id work_order_id,
		CONCAT(wp.sequence,' - ',wp.description) process,
		ws.work_type work_type,
                ws.work_start_date_time,
                ws.duration,
                ws.remaining_duration
		FROM    ".TB_PREF."workcentre_schedule as ws,"                        	
			.TB_PREF."workcentres as wc,"
                        .TB_PREF."workorders as wo,"
			.TB_PREF."wo_processes as wp
		WHERE ws.workcentre_id=wc.id AND ws.wo_process_id=wp.id 
                      AND wp.workorder_id=wo.id ";

	if ($wo !='')
	{
		$sql .= " AND wo.id=". db_escape($wo);
	}

	if ($workcentre != 0 )
	{
		$sql .= " AND wc.id=".db_escape($workcentre);
	}
        if ($from_date != null )
	{
		$sql .= " AND ws.work_date >=". db_escape($from_date) ;
	}
        if ($to_date != null )
	{
		$sql .= " AND ws.work_date <=". db_escape($to_date) ;
	}
       if ($w_no == '1')
	{
		$sql .= " AND wo.id > 0 ";
	}
        if ($w_no == '2')
	{
		$sql .= " AND wo.id < 0 ";
	}

	return $sql;
}
?>