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

function get_sql_for_work_orders($outstanding_only, $all_items)
{
	$sql = "SELECT
		workorder.id,
		workorder.wo_ref,
		pm.description project_description,
		item.description,
		workorder.units_reqd,
		workorder.units_issued,
		workorder.date_,
		workorder.required_by,
		workorder.released_date,
		workorder.closed,
		workorder.released,
		workorder.stock_id,
        workorder.approval_status,
		unit.decimals,
        workorder.closed_for_prs,
        workorder.display_status,
        workorder.closed_for_issues 
		FROM ".TB_PREF."workorders as workorder,"
			.TB_PREF."stock_master as item,"
			.TB_PREF."item_units as unit,"
			.TB_PREF."locations as location,"
			.TB_PREF."projects_master as pm 
		WHERE workorder.stock_id=item.stock_id 
			AND workorder.loc_code=location.loc_code
			AND item.units=unit.abbr 
            AND pm.project_id = workorder.project_id";

    if($outstanding_only == 1){
        $sql .= " AND workorder.closed = 0 AND workorder.approval_status = ".AS_APPROVED;
    }
    else{
        
    }
    
    if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "")
	{
		$sql .= " AND workorder.id = ".db_escape($_POST['OrderNumber']);
	}
    elseif (isset($_POST['OrderReference']) && $_POST['OrderReference'] != "")
	{
		$sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$_POST['OrderReference'].'%');
	}
    else{
        if (check_value('OpenOnly'))
        {
            $sql .= " AND workorder.closed = 0 AND workorder.released = 1";
        }

        if (isset($_POST['ProjectId']) && $_POST['ProjectId'] != $all_items)
        {
            $sql .= " AND workorder.project_id=".db_escape($_POST['ProjectId']);
        }

        if (isset($_POST['SelectedStockItem']) && $_POST['SelectedStockItem'] != $all_items)
        {
            $sql .= " AND workorder.stock_id=".db_escape($_POST['SelectedStockItem']);
        }

        if (check_value('OverdueOnly'))
        {
            $Today = date2sql(Today());

            $sql .= " AND workorder.required_by < '$Today' AND workorder.closed = 0 ";
        }

        switch ($_POST['WorkOrderType']) {
            case 1: //Confirmed
                $sql .= " AND workorder.id > 0 ";
                break;
            case 2: //Shedular
                $sql .= " AND workorder.id < 0 ";
                break;
            default: // 3 - All
                break;
        }
    }
	return $sql;
}


function get_sql_for_approve_work_orders($current_user_id)
{
	$sql = "SELECT
		workorder.id,
		workorder.wo_ref,
		pm.description project_description,
		item.description,
		workorder.units_reqd,
		workorder.units_issued,
		workorder.date_,
		workorder.required_by,
		workorder.released_date,
		workorder.closed,
		workorder.released,
		workorder.stock_id,
        workorder.approval_status,
		unit.decimals,
        workorder.closed_for_prs,
        workorder.display_status,
        workorder.closed_for_issues 
		FROM ".TB_PREF."workorders as workorder,"
			.TB_PREF."stock_master as item,"
			.TB_PREF."item_units as unit,"
			.TB_PREF."locations as location,"
			.TB_PREF."projects_master as pm,"
            .TB_PREF."approvers as apr, "
            .TB_PREF."user_data_access_groups as udag,"
            .TB_PREF."users creu   
		WHERE workorder.stock_id=item.stock_id AND 
            workorder.loc_code=location.loc_code AND 
            item.units=unit.abbr AND 
            pm.project_id = workorder.project_id AND 
            (workorder.approval_status = ".AS_AWAITING_APPROVAL." OR workorder.approval_status = ".AS_WITH_THE_APPROVER.") AND 
            workorder.next_approval_group_id = apr.approval_group_id AND 
            pm.dag_id = udag.dag_id AND 
            udag.user_id = apr.user_id AND  
            workorder.created_by = creu.id AND 
            apr.user_id = ".$current_user_id;
    
    if(isset($_POST['ProjectId']) && $_POST['ProjectId'] > 0){
        $sql .= " AND workorder.project_id = ".db_escape($_POST['ProjectId']);
    }
	return $sql;
}

?>