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

function get_approval_path($project_id, $approval_path_type)
{
        $sql = "SELECT  ap.* 
                FROM ".TB_PREF."project_approval_paths pap, 
                        ".TB_PREF."approval_path ap 
                WHERE pap.approval_path_id = ap.approval_path_id AND 
                        pap.project_id = ".db_escape($project_id)." AND 
                        pap.approval_path_type = ".db_escape($approval_path_type);
                            
        $result = db_query($sql,"approval group of current user could not be retreived");
	
	return db_fetch($result);
        
}

//--------------------------------------------------------------------------------------------------

function get_next_approval_group($approval_path_id, $position_seq_no)
{
	$sql = "SELECT * FROM ".TB_PREF."approval_path_configuration 
                WHERE approval_path_id = ".db_escape($approval_path_id)." AND 
                        position_seq_no > ".db_escape($position_seq_no)." 
                ORDER BY position_seq_no LIMIT 1";
	
	$result = db_query($sql,"Next approval group could not be retreived");
	
	return db_fetch($result);
}


//--------------------------------------------------------------------------------------------------

function get_user_approval_group($approval_path_id, $current_user_id)
{
        $sql = "SELECT apc.* 
                FROM ".TB_PREF."approval_path_configuration apc,
                        ".TB_PREF."approvers a 
                WHERE apc.approval_group_id = a.approval_group_id  AND 
                        apc.approval_path_id = ".  db_escape($approval_path_id)." AND 
                        a.user_id = ". db_escape($current_user_id)." 
                ORDER BY apc.position_seq_no LIMIT 1";
                            
        $result = db_query($sql,"approval group of current user could not be retreived");
	
	return db_fetch($result);
        
}

//--------------------------------------------------------------------------------------------------


function get_approval_group_in_approval_path($approval_path_id, $approval_group_id)
{    
    $sql = "SELECT apc.approval_path_id,
                    apc.approval_group_id,
                    apc.position_seq_no,
                    apc.position_description,
                    apc.display_status,
                    apc.position_code,
                    apc.next_position_message,
                    ag.approval_group_description   
            FROM ".TB_PREF."approval_group ag,
                    ".TB_PREF."approval_path_configuration apc 
            WHERE ag.approval_group_id = apc.approval_group_id AND 
                    apc.approval_path_id = ".db_escape($approval_path_id)." AND 
                    ag.approval_group_id = ".db_escape($approval_group_id)." 
            ORDER BY apc.position_seq_no";
    
    $result = db_query($sql, "could not retrieve approver in approval group");
    
    return db_fetch($result);
    

}


//--------------------------------------------------------------------------------------------------


function get_approver_in_approval_group($approval_group_id, $current_user_id)
{
    $sql = "SELECT * FROM ".TB_PREF."approvers 
            WHERE approval_group_id = ".db_escape($approval_group_id)." AND "
                    . "user_id = ".db_escape($current_user_id);
    
    $result = db_query($sql, "could not retrieve approver in approval group");
    
    return db_fetch($result);
    
}

//--------------------------------------------------------------------------------------------------


function get_approval_group_name($approval_group_id)
{
    $sql = "SELECT approval_group_description FROM ".TB_PREF."approval_group 
            WHERE approval_group_id = ".db_escape($approval_group_id);
    
    $result = db_query($sql, "Could not get_approval_group_name");
    
    $ag = db_fetch($result);
    if(isset($ag['approval_group_description'])){
        return $ag['approval_group_description'];
    }
    else{
        return "Unknown approval group";
    }
    
}

//--------------------------------------------------------------------------------------------------


function get_approval_path_name($approval_path_id)
{
    $sql = "SELECT approval_path_name FROM ".TB_PREF."approval_path 
            WHERE approval_path_id = ".db_escape($approval_path_id);
    
    $result = db_query($sql, "Could not get_approval_path_name");
    
    $ag = db_fetch($result);
    if(isset($ag['approval_path_name'])){
        return $ag['approval_path_name'];
    }
    else{
        return "Unknown approval path";
    }
    
}
//--------------------------------------------------------------------------------------------------

function update_document_approval_status($info)
{
        $approved_on = date('Y-m-d H:i:s');

        get_table_info($info, $table, $num_field, $type_field);
        
    	$sql = "UPDATE ".TB_PREF.$table."
                SET next_approval_group_id = ".db_escape($info['next_approval_group_id']).",  
                        approval_status = ".db_escape($info['approval_status']).",  
                        display_status = ".db_escape($info['display_status']).", 
                        approved_by = ".db_escape($info['current_user_id']).",
                        approved_on = ".db_escape($approved_on).", ";
                        if($info['change_type'] == "REOPEN"){
                            $sql .= "amends = amends + 1, ";
                        }
                        $sql .= "row_state = ".db_escape(db_new_row_state($info['row_state']))." 
                WHERE $num_field = ".db_escape($info['tran_no']);
                        if(isset($type_field) && strlen($type_field) >  0){
                            $sql .= " AND $type_field = ".db_escape($info['tran_type']);
                                }
                        $sql .= " AND row_state = ".db_escape($info['row_state']);
        
        if($info['change_type'] == "REOPEN"){
            $sql .= " AND approval_status =".db_escape(AS_APPROVED); 
        }
        else{
            $sql .= " AND approval_status NOT IN (".db_escape(AS_APPROVED).", ".db_escape(AS_REJECTED).")"; 
        }

    	db_query($sql, "could not update document approval status");
        
        $num_recs = db_num_affected_rows();
        if($num_recs == 1){
            write_to_approval_log($info);
            return array("success" => true, "message" => $info['message']);
        }
        else{
            switch ($info['change_type']) {
                case "REJECT":
                    $info['message'] = "Document has been updated by another user. 
                                        Not possible to update this document.";  
                    break;
                case "REOPEN":
                    $info['message'] = "Trying to reopen an unapproved document or the document has been updated by another user. 
                                        Not possible to update this document.";  
                    break;
                default:
                    $info['message'] = "Document has been updated by another user. 
                                        Not possible to update this document.";  
                    break;
            }
            
            write_to_approval_log($info);
            return array("success" => false, "message" => $info['message']);
        }

}

//--------------------------------------------------------------------------------------------------

function get_table_info($info, &$table, &$num_field, &$type_field = null)
{
        switch ($info['tran_type']) {
            case ST_MATERIAL_REQ:
                $table = "material_requisitions";
                $num_field = "mr_no";
                $type_field = null;
                break;
            case ST_TRANSFERREQ:
                $table = "material_transfer_requisitions";
                $num_field = "mtr_no";
                $type_field = null;
                break;
            case ST_PURCH_REQ:
                $table = "purchase_requisitions";
                $num_field = "pr_no";
                $type_field = null;
                break;
            case ST_PURCHORDER:
                $table = "purch_orders";
                $num_field = "order_no";
                $type_field = null;
                break;
            case ST_SALESORDER:
                $table = "sales_orders";
                $num_field = "order_no";
                $type_field = "trans_type";
                break;
            case ST_SALESQUOTE:
                $table = "sales_orders";
                $num_field = "order_no";
                $type_field = "trans_type";
                break;
            case ST_WORKORDER:
                $table = "workorders";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_I_OWE_YOU:
                $table = "i_owe_you";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_I_OWE_YOU_BATCH:
                $table = "i_owe_you_batches";
                $num_field = "batch_no";
                $type_field = null;
                break;
            case ST_VEHICLE_REQUEST:
                $table = "vehicle_requests";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_PROJECT_INVOICE:
                $table = "project_invoices";
                $num_field = "order_no";
                $type_field = null;
                break;
            case ST_PROJECT_INVOICE:
                $table = "project_credit_notes";
                $num_field = "order_no";
                $type_field = null;
                break;
            case ST_PROJECT_CREDIT_NOTE:
                $table = "project_credit_notes";
                $num_field = "order_no";
                $type_field = null;
                break;
            case ST_SUPPLIER_TRANSPORT_INVOICES:
                $table = "supplier_transport_invoices";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_SUBCONTRACTOR_INVOICE:
                $table = "subcontractor_invoices";
                $num_field = "order_no";
                $type_field = null;
                break;
            case ST_UTILITY_BILLS:
                $table = "utility_bills";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_UTILITY_BILL_CREDIT_NOTE:
                $table = "utility_bill_credit_notes";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_SUBCONTRACTOR_PURCH_ORDER:
                $table = "subcontractor_purchase_orders";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_SUBCONTRACTOR_CREDIT_NOTE:
                $table = "subcontractor_credit_notes";
                $num_field = "order_no";
                $type_field = null;
                break;
            case ST_MISCELLANEOUS_PROJECT_COST:
                $table = "miscellaneous_project_costs";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_TOOL_REQUEST:
                $table = "tool_requests";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_APPROVE_PROJECT:
                $table = "projects_master";
                $num_field = "project_id";
                $type_field = null;
                break;
            case ST_APPROVE_EST_PROJECT:
                $table = "est_projects_master";
                $num_field = "est_project_id";
                $type_field = null;
                break;
            case ST_MONTHLY_PROJECT_COSTS:
                $table = "monthly_project_costs";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_INVENTORY_ISSUE_REQUEST:
            case ST_INVENTORY_RECEIPT_REQUEST:
                $table = "stock_adjustment_requests";
                $num_field = "id";
                $type_field = "type";
                break;
            case ST_ASSET_TRANSFER_REQUEST: 
                $table = "asset_transfer_requests";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_ASSET_TRANSFER: 
                $table = "asset_transfers";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_ASSET_QUANTITY_ADJUSTMENT_REQUEST: 
                $table = "asset_quantity_adjustment_requests";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_ASSET_QUANTITY_ADJUSTMENT: 
                $table = "asset_quantity_adjustments";
                $num_field = "id";
                $type_field = null;
                break;
            case ST_REQTRANSFER:
                $table = "material_requisition_transfers";
                $num_field = "mrt_no";
                $type_field = "type";
                break;
            case ST_JOB_COST_ADDITION:
            case ST_JOB_COST_DEDUCTION:
                $table = "job_cost_additions";
                $num_field = "trans_no";
                $type_field = "type";
                break;
            case ST_POS_INVOICE:
            case ST_POS_CREDIT_NOTE:
                $table = "pos_invoices";
                $num_field = "trans_no";
                $type_field = "type";
                break;
            case ST_POS_CUSTOMER_PAYMENT:
            case ST_POS_CUSTOMER_RETURN:
                $table = "pos_customer_payments";
                $num_field = "trans_no";
                $type_field = "type";
                break;
            default:
                break;
        }
}

//--------------------------------------------------------------------------------------------------

function direct_update_document_status($tran_type, $tran_no, $approval_status, $row_state, 
                                $message='', $display_status="", $project_id=0, $position_code="", $next_approval_group_id=0)
{
        
        $info['tran_type'] = $tran_type;
        $info['tran_no'] = $tran_no;
        $info['approval_status'] = $approval_status;
        $info['next_approval_group_id'] = $next_approval_group_id;
        $info['display_status'] = $display_status;
        $info['position_code'] = $position_code;
        $info['project_id'] = $project_id;
        $info['message'] = $message;
        $info['current_user_id'] = $_SESSION['wa_current_user']->user;
        $info['row_state'] = $row_state;

        get_table_info($info, $table, $num_field, $type_field);
        
    	$sql = "UPDATE ".TB_PREF.$table."
                SET approval_status = ".db_escape($info['approval_status']);
        
                if(strlen($info['display_status']) > 0){
                    $sql = ", display_status = ".db_escape($info['display_status']);
                }
                        
                $sql .= ", row_state = ".db_escape(db_new_row_state($info['row_state']))." 
                    
                WHERE $num_field = ".db_escape($info['tran_no']);
                
                if(isset($type_field) && strlen($type_field) >  0){
                    $sql .= " AND $type_field = ".db_escape($info['tran_type']);
                }
                        
                $sql .= " AND row_state = ".db_escape($info['row_state']);

    	db_query($sql, "could not update document approval status");
        
        $num_recs = db_num_affected_rows();
        if($num_recs == 1){
            write_to_approval_log($info);
            return array("success" => true, "message" => $info['message']);
        }
        else{
            $info['message'] = "Document has been updated by another user. 
                                        Not possible to update this document.";  
            write_to_approval_log($info);
            return array("success" => false, "message" => $info['message']);
        }

}


function write_to_approval_log($info)
{
        $approved_on = date('Y-m-d H:i:s');
        
        $display_status = "";
        if(isset($info['display_status'])){
            $display_status = $info['display_status'];
        }
        
        $position_code = "";
        if(isset($info['position_code'])){
            $position_code = $info['position_code'];
        }
        
        $next_approval_group_id = 0;
        if(isset($info['next_approval_group_id'])){
            $next_approval_group_id = $info['next_approval_group_id'];
        }
        
    	$sql = "INSERT INTO ".TB_PREF."approval_log(
                        project_id, tran_type, 
                        tran_no, approval_status, next_approval_group_id,
                        display_status, position_code,
                        comment, 
                        approved_by, approved_on)
    		VALUES (".db_escape($info['project_id']).", ".db_escape($info['tran_type']).", "
                        .db_escape($info['tran_no']).", ".db_escape($info['approval_status']).", ".db_escape($info['next_approval_group_id']).", "
                        .db_escape($display_status).", ".db_escape($position_code).", "
                        .db_escape($info['message']).", "
                        .db_escape($info['current_user_id']).", ".db_escape($approved_on).")";

    	db_query($sql, "could not write to approval log");

}

//--------------------------------------------------------------------------------------------------

function update_document_status(&$info)
{
        $approved_on = date('Y-m-d H:i:s');
        
        get_table_info($info, $table, $key_field);
        
        $new_row_state = db_new_row_state($info['row_state']);
        
    	$sql = "UPDATE ".TB_PREF.$table."
                SET approval_status = ".db_escape($info['approval_status']).", ";
                        if($info['change_type'] == "REOPEN"){
                            $sql .= "amends = amends + 1, ";
                        }
                        $sql .= "row_state = ".db_escape($new_row_state)." 
                WHERE $key_field = ".db_escape($info['tran_no'])." AND 
                        row_state = ".db_escape($info['row_state']);
                        
                if($info['change_type'] == "REOPEN"){
                    $sql .= " AND approval_status =".db_escape(AS_APPROVED); 
                }
                        
        db_query($sql, "could not update document status");
        
        $info["row_state"] = $new_row_state;
        
        if(db_num_affected_rows() == 1){
            $info["success"] = true;
            $info["message"] = "Document status has been updated.";
        }
        else{
            $info["success"] = true;
            $info["message"] = "Document status has been updated by another user";
        }

}


//--------------------------------------------------------------------------------------------------


function get_new_position_user_name($user_id)
{
    $sql = "SELECT real_name FROM ".TB_PREF."users  
            WHERE id = ".db_escape($user_id);
    
    $result = db_query($sql, "Could not get_new_position_user_name");
    
    $ag = db_fetch($result);
    if(isset($ag['real_name'])){
        return $ag['real_name'];
    }
    else{
        return "Unknown user";
    }
    
}


//--------------------------------------------------------------------------------------------------

function get_originator_approval_group($info, $approval_path_id, $originator_id = 0)
{
    if($originator_id > 0){
        
    }
    else{
        get_table_info($info, $table, $num_field, $type_field);
        $originator_id = get_document_originator($info, $table, $num_field, $type_field);
    }
            
    $sql = "SELECT apc.*, ag.approval_group_description  
            FROM ".TB_PREF."approval_path_configuration apc,
                    ".TB_PREF."approval_group ag,
                    ".TB_PREF."approvers a 
            WHERE apc.approval_group_id = a.approval_group_id  AND 
                    ag.approval_group_id = apc.approval_group_id AND 
                    apc.approval_path_id = ".  db_escape($approval_path_id)." AND 
                    a.user_id = ". db_escape($originator_id)." 
            ORDER BY apc.position_seq_no LIMIT 1";
                            
    $result = db_query($sql,"Could not get_originator_approval_group");
	
	return db_fetch($result);

}

//--------------------------------------------------------------------------------------------------


function get_document_originator($info, $table_name, $num_field, $type_field)
{
    $sql = " SELECT created_by 
                FROM ".TB_PREF.$table_name;
                
    $sql .= " WHERE ";     
            if(isset($type_field) && strlen($type_field) >  0){
                $sql .= " $type_field = ".db_escape($info['tran_type'])." AND ";
            }
    $sql .= " $num_field = ".db_escape($info['tran_no']);
    
    $result = db_query($sql, "could not write to approval log");
    $originator = db_fetch($result);
    if(isset($originator['created_by'])){
        return $originator['created_by'];
    }
    else{
        return 0;
    }
        
}
//--------------------------------------------------------------------------------------------------
function get_approval_logs($tran_type, $tran_no)
{
    $sql = "SELECT al.approval_status,
                    u.real_name approved_by,
                    al.approved_on,
                    al.next_approval_group_id,
                    ap.approval_group_description,
                    al.display_status,
                    al.comment     
            FROM ".TB_PREF."approval_log al
            LEFT JOIN ".TB_PREF."users u ON
                al.approved_by = u.id
            LEFT JOIN ".TB_PREF."approval_group ap ON
                ap.approval_group_id = al.next_approval_group_id
            WHERE tran_type =".db_escape($tran_type).
                    "AND tran_no =".db_escape($tran_no)." 
            ORDER BY
                al.id";
    return db_query($sql,"User could not be retreived");
}

function get_approval_group($trans_no)
{
        $sql = "SELECT *
            FROM ".TB_PREF."approval_group ag
            WHERE approval_group_id =".db_escape($trans_no);
        
   $result = db_query($sql,"Approval Group could not be retreived");
   
   return db_fetch($result);
    
}

function get_approvers($trans_no)
{
    $sql = "SELECT u.real_name,
                   ap.*
            FROM ".TB_PREF."approvers ap
            LEFT JOIN ".TB_PREF."users u ON
                u.id=ap.user_id
            WHERE ap.approval_group_id =".db_escape($trans_no);
        
    return db_query($sql,"Approval Group could not be retreived");
    
}

?>