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


function get_vehicle_running_chart($from_date, $to_date, $search_supplier_id = 0, $vehicle_id = 0, $plate_no = '', $batch_no = 0)
{
    $sql = "SELECT vr.*,
                    pm.description project_description,
                    vp.description package_description,
                    v.description vehicle_description,
                    d.name driver_name,
                    s.supp_name 
        
            FROM ".TB_PREF."vehicle_running_chart vr
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    vr.project_id = pm.project_id

                LEFT JOIN ".TB_PREF."vehicle_packages vp ON
                    vp.id = vr.package_id
                    
                LEFT JOIN ".TB_PREF."vehicles v ON
                    v.id = vr.vehicle_id
                    
                LEFT JOIN ".TB_PREF."drivers d ON
                    d.id = vr.driver_id	
                    
                LEFT JOIN ".TB_PREF."suppliers s ON
                    s.supplier_id = vr.supplier_id	

            WHERE vr.start_date_time >=".db_escape(date2sql($from_date))." AND vr.end_date_time <=".db_escape(date2sql($to_date))." AND
                    batch_no > 0 AND inv_no = 0 AND payment_source_no = 0";
                   
            if($vehicle_id > 0){
                $sql .= " AND vr.vehicle_id = ".db_escape($vehicle_id);
            } 
            
            if(strlen($plate_no) > 0){
                $sql .= " AND vr.plate_no = ".db_escape($plate_no);
            }  
            
            if($search_supplier_id > 0){
                $sql .= " AND vr.supplier_id = ".db_escape($search_supplier_id);
            }
            
            if($batch_no > 0){
                $sql .= " AND vr.batch_no = ".db_escape($batch_no);
            }

   return db_query($sql);    
    
}


function get_supplier_transport_invoice_vehicle_running_chart($sti_no)
{
    $sql = "SELECT vr.*,
                    pm.description project_description,
                    vp.description package_description,
                    v.description vehicle_description,
                    d.name driver_name,
                    s.supp_name 
        
            FROM ".TB_PREF."vehicle_running_chart vr
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    vr.project_id = pm.project_id

                LEFT JOIN ".TB_PREF."vehicle_packages vp ON
                    vp.id = vr.package_id
                    
                LEFT JOIN ".TB_PREF."vehicles v ON
                    v.id = vr.vehicle_id
                    
                LEFT JOIN ".TB_PREF."drivers d ON
                    d.id = vr.driver_id	
                    
                LEFT JOIN ".TB_PREF."suppliers s ON
                    s.supplier_id = vr.supplier_id	

            WHERE vr.inv_no = ".db_escape($sti_no);

   return db_query($sql);    
    
}


function get_supplier_transport_invoice_reference($supplier_id, $supp_inv_ref, $sti_no)
{
    $sql = "SELECT supp_inv_ref, id          
            FROM ".TB_PREF."supplier_transport_invoices 
            WHERE supplier_id=".db_escape($supplier_id)." AND 
                supp_inv_ref = ".db_escape($supp_inv_ref)." AND 
                id <> ".db_escape($sti_no);
    
    $result = db_query($sql);    
    $inv_ref = db_fetch($result);
    if($inv_ref){
        return true;
    }
    else{
        return false;
    }
}

function get_supplier_transport_invoices($supplier_id)
{
    $sql = "SELECT *     
            FROM ".TB_PREF."supplier_transport_invoices 
            WHERE supplier_id = ".db_escape($supplier_id);
    
    return db_query($sql);    

}

function get_open_supplier_transport_invoices($supplier_id)
{
    $sql = "SELECT *     
            FROM ".TB_PREF."supplier_transport_invoices 
            WHERE supplier_id = ".db_escape($supplier_id)." AND 
                    payment_source_no = 0 ";
    
    return db_query($sql);    

}

function get_supplier_transport_invoice_record($id)
{
    $sql = "SELECT *     
            FROM ".TB_PREF."supplier_transport_invoices 
            WHERE id = ".db_escape($id);
    
    $result = db_query($sql);    
    return db_fetch($result);

}

function get_supplier_transport_invoice_record_by_payment_source($payment_source_type, $payment_source_no)
{
    $sql = "SELECT *     
            FROM ".TB_PREF."supplier_transport_invoices 
            WHERE payment_source_type = ".db_escape($payment_source_type)." AND 
                payment_source_no = ".db_escape($payment_source_no);
    
    $result = db_query($sql);    
    return db_fetch($result);

}


function update_supplier_transport_invoice_vrc_detail($id, $supplier_charge, $inv_no, $bill_reference)
{
    
    $sql = "UPDATE ".TB_PREF."vehicle_running_chart
            SET supplier_charge = ".db_escape($supplier_charge).",
                inv_no = ".db_escape($inv_no).",
                bill_reference = ".db_escape($bill_reference)."    
            WHERE id=".db_escape($id)." AND 
                    batch_no > 0 AND 
                    (inv_no = 0 OR inv_no = ".db_escape($inv_no).") AND 
                    payment_source_no = 0 ";
    
    db_query($sql);
    
    return db_num_affected_rows();
      
}

function add_supplier_transport_invoice_header($inv_supplier_id, $reference, $supp_inv_ref, $tran_date, 
                                                    $total, $payment_terms, $due_date, $comments,
                                                    $other_charges_text, $other_charges,
                                                    $project_id, $task_id)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
        
    $sql = "INSERT INTO ".TB_PREF."supplier_transport_invoices (
                        supplier_id, reference, supp_inv_ref, 
                        tran_date, total, 
                        payment_terms, due_date,
                        other_charges_text, other_charges, comments,
                        project_id, task_id,
                        created_by, created_on) ";
	$sql .= "VALUES (".db_escape($inv_supplier_id).", ".db_escape($reference).", ".db_escape($supp_inv_ref).", "
                        .db_escape(date2sql($tran_date)).", ".db_escape($total).", "
                        .db_escape($payment_terms).", ".db_escape(date2sql($due_date)).", "
                        .db_escape($other_charges_text).", ".db_escape($other_charges).", ".db_escape($comments).", "
                        .db_escape($project_id).", ".db_escape($task_id).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";

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


function update_supplier_transport_invoice_header($sti_no, $reference, $supp_inv_ref, $tran_date, 
                                                    $total, $payment_terms, $due_date, $comments,
                                                    $other_charges_text, $other_charges,
                                                    $project_id, $task_id)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
        
    $sql = "UPDATE ".TB_PREF."supplier_transport_invoices 
            SET reference = ".db_escape($reference).", 
                supp_inv_ref = ".db_escape($supp_inv_ref).", 
                tran_date = ".db_escape(date2sql($tran_date)).", 
                total = ".db_escape($total).",
                payment_terms = ".db_escape($payment_terms).",
                due_date = ".db_escape(date2sql($due_date)).", 
                other_charges_text = ".db_escape($other_charges_text).", 
                other_charges = ".db_escape($other_charges).",
                comments = ".db_escape($comments).", 
                project_id = ".db_escape($project_id).", 
                task_id = ".db_escape($task_id).", 
                updated_by = ".db_escape($updated_by).",
                updated_on = ".db_escape($updated_on)." 
            WHERE id=".db_escape($sti_no);                         

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

function update_supplier_transport_invoice_payment_source($transport_inv_id, $payment_source_type, $payment_source_no)
{
    $sql = "UPDATE ".TB_PREF."supplier_transport_invoices
            SET payment_source_type = ".db_escape($payment_source_type).", 
                payment_source_no = ".db_escape($payment_source_no)." 
            WHERE id=".db_escape($transport_inv_id)." AND 
                    payment_source_no=0";
    
    db_query($sql);
    
    return db_num_affected_rows();
    
}


function delete_supplier_transport_invoice_taxes($inv_no)
{     
     $sql = "DELETE FROM ".TB_PREF."supplier_transport_invoice_taxes   
             WHERE order_no = ".db_escape($inv_no);     
     
    db_query($sql, "Could not deletesupplier_transport_invoice_taxes.");
 
}


function add_supplier_transport_invoice_tax_detail($inv_no, $tax_id, $tax_value, $tax_type_name , $rate)
{     
    $sql = "INSERT INTO ".TB_PREF."supplier_transport_invoice_taxes 
                   (order_no, tax_type_id, amount, tax_type_name , rate)
             VALUES (".db_escape($inv_no).","
                    .db_escape($tax_id).","
                    .db_escape($tax_value).","
                    .db_escape($tax_type_name ).","
                    .db_escape($rate).")";              
     
    db_query($sql, "Could not add_supplier_transport_invoice_tax_detail.");
 
}


function get_supplier_transport_invoice_advances_total($inv_no)
{
    $sql = "SELECT SUM(va.paid_amount) advances_total 
            FROM ".TB_PREF."vehicle_allocations va  
            WHERE va.transport_inv_no = ".db_escape($inv_no);
    
    $result = db_query($sql, "Could not get_supplier_transport_invoice_advances_total");  
    
    if($result){
        $t = db_fetch($result);
        return $t['advances_total'];
    }
    else{
        return 0;
    }

}

function get_supplier_transport_invoice_vrc_total($inv_no)
{
    $sql = "SELECT SUM(vrc.supplier_charge) supplier_charges_total 
            FROM ".TB_PREF."vehicle_running_chart vrc   
            WHERE vrc.inv_no = ".db_escape($inv_no);
    
    $result = db_query($sql, "Could not get_supplier_transport_invoice_vrc_total");  
    
    if($result){
        $t = db_fetch($result);
        return $t['supplier_charges_total'];
    }
    else{
        return 0;
    }

}

function calculate_supplier_transport_invoice_details_total($inv_no)
{
    $advances_total = get_supplier_transport_invoice_advances_total($inv_no);
    
    $vrc_total = get_supplier_transport_invoice_vrc_total($inv_no);
    
    return $vrc_total - $advances_total;
}


function update_supplier_transport_invoice_header_totals($inv_no, $sti_total, $taxes_total, $tax_included)
{
    $sql = "UPDATE ".TB_PREF."supplier_transport_invoices
            SET total = ".db_escape($sti_total).", 
                taxes = ".db_escape($taxes_total).", 
                tax_included = ".db_escape($tax_included)." 
            WHERE id=".db_escape($inv_no);
    
    db_query($sql, "Could not update_supplier_transport_invoice_header_totals");

}


function delete_supplier_transport_invoice_vrc_entry($inv_no, $vrc_id)
{
    $sql = "UPDATE ".TB_PREF."vehicle_running_chart 
            SET inv_no = 0  
            WHERE id=".db_escape($vrc_id)." AND 
                    inv_no=".db_escape($inv_no);
    
    db_query($sql, "Could not delete_supplier_transport_invoice_vrc_entry");
}

function delete_supplier_transport_invoice_va_detail($inv_no, $va_id)
{
    $sql = "UPDATE ".TB_PREF."vehicle_allocations 
            SET transport_inv_no = 0  
            WHERE id=".db_escape($va_id)." AND 
                    transport_inv_no=".db_escape($inv_no);
    
    db_query($sql, "Could not delete_supplier_transport_invoice_va_detail");
}

function get_supplier_transport_invoice_tax_details($inv_no)
{     
    $sql = "SELECT sti_tax.*, tax_types.purchasing_gl_code   
            FROM ".TB_PREF."supplier_transport_invoice_taxes sti_tax 
                    JOIN ".TB_PREF."tax_types tax_types 
            WHERE order_no = ".db_escape($inv_no)." AND 
                    sti_tax.tax_type_id = tax_types.id ";
     
    $result = db_query($sql, "Could not get_supplier_transport_invoice_tax_details.");
    
//    	$active_taxes = $ret_tax_array = array();
//
//	$tax_group_items = get_tax_group_rates($id);

	while ($tax_group_item = db_fetch_assoc($result)) 
	{
		$tax_group_item['Value'] = $tax_group_item['amount'];
		$ret_tax_array[$tax_group_item['tax_type_id']] = $tax_group_item;
	}

	return $ret_tax_array;
    
 
}


function release_supplier_transport_invoice($sti_no)
{
    $task_costs = array();
    $current_user_id = $_SESSION['wa_current_user']->user;
    $trans_type = ST_SUPPLIER_TRANSPORT_INVOICES;
    
    $sti = get_supplier_transport_invoice_record($sti_no);
    $date_ = sql2date($sti['tran_date']);
    
    $sti_total_excl = $sti['total'];
    $sti_total_incl = $sti['total'] + $sti['taxes'];
    
//    if($sti['tax_included']){
//        $sti_total_excl = $sti['total'] - $sti['taxes'];
//        $sti_total_incl = $sti['total'];
//    }
//    else{
//        $sti_total_excl = $sti['total'];
//        $sti_total_incl = $sti['total'] + $sti['taxes'];
//    }
    
    add_to_task_cost($task_costs, $sti['task_id'], $sti_total_excl, $sti_total_excl, $sti_total_excl);
    
    $info = approve_document($trans_type, $sti_no, $sti['project_id'], $task_costs, 
                                    $sti['next_approval_group_id'], $current_user_id, $sti['row_state'], $sti['created_by']);
        
    if($info['success'] !== true){
        return array("success" => $info['success'],"trans_no" => $sti_no, "message" => $info['message']);
    }
    
    if($info['approval_status'] != AS_APPROVED){
        return array("success" => $info['success'], "trans_no" => $sti_no, "message" => $info['message']);
    }
    
    $cct_msg = charge_cost_to_tasks($trans_type, $sti_no, 
                                $task_costs, $current_user_id);
    
    if($cct_msg['success'] !== true)
    {
        return array("success" => $cct_msg['success'], "tran_no" => $sti_no, "message" => $cct_msg['message']);
    }
    
    
    /* Financial entries */
    
    if(exists_supp_trans($trans_type, $sti_no)){
        return array("success" => false, "message" => "Supplier Transport Invoice $sti_no is already in the Supplier's transactions list.");
    }
    
    $total = 0;
    $supplier = get_supplier($sti['supplier_id']);
    $ex_rate = get_exchange_rate_from_home_currency($supplier['curr_code'], $date_);
    
    write_supp_trans($trans_type, $sti_no, $sti['supplier_id'], $date_, sql2date($sti['due_date']),
                                    $sti['reference'], $sti['supp_inv_ref'],
                                    $sti_total_excl, $sti['taxes'], 0, "", 0, $sti['tax_included']);
    
    $taxes = get_supplier_transport_invoice_tax_details($sti_no);
    foreach ($taxes as $taxitem)
    {
		if ($taxitem['amount'] != 0)
		{
			add_trans_tax_details($trans_type, $sti_no, $taxitem['tax_type_id'], $taxitem['rate'], $sti['tax_included'], 
                                    $taxitem['amount'], $sti_total_excl, $ex_rate, $date_, $sti['supp_inv_ref']);

//			$total += add_gl_trans_supplier($trans_type, $sti_no, $date_,
//                                            $taxitem['purchasing_gl_code'], 0, 0, $taxitem['amount'],
//                                            $sti['supplier_id'],
//                                            "A general ledger transaction for the tax amount could not be added");
		}
    }
    
//    add_gl_trans_supplier($trans_type, $sti_no, $date_, $supplier["payable_account"], 0, 0,
//                                    -($sti_total_incl), $sti['supplier_id'],
//                                    "The general ledger transaction for the control total could not be added");
    
    $transport_exp_act = get_sys_prefs_parameter_value('transport_exp_act');
    if(!(isset($transport_exp_act) && strlen($transport_exp_act) > 0)){
        $message = "Transport Expenses Account not entered in the system.";
        return array("success" => false, "tran_no" => $sti_no, "message" => $message);
    }
                
//    $memo_ = "";
//    $total += add_gl_trans_supplier($trans_type, $sti_no, $date_, $transport_exp_act,
//        0, 0, $sti_total_excl , $sti['supplier_id'], "", 0, $memo_);
        
//    add_gl_balance($trans_type, $sti_no, $date_, -$sti_total_excl, PT_SUPPLIER, $sti['supplier_id']); // ??

//	add_comments(ST_SUPPLIER_TRANSPORT_INVOICES, $invoice_id, $date_, $sti['comments']);
//
//	$Refs->save(ST_SUPPLIER_TRANSPORT_INVOICES, $invoice_id, $sti['supp_inv_ref']);
    
    return array("success" => true, "tran_no" => $sti_no, "message" => $info['message']);
}


function line_bill_reference_exists($supplier_id, $line_bill_reference, $vrc_id)
{
    $sql = "SELECT vrc.id 
            FROM ".TB_PREF."supplier_transport_invoices sti 
                    JOIN ".TB_PREF."vehicle_running_chart vrc ON 
                            sti.id = vrc.inv_no 
            WHERE sti.supplier_id = ".db_escape($supplier_id)." 
                    AND vrc.bill_reference = ".db_escape($line_bill_reference)." 
                    AND vrc.id <> ".db_escape($vrc_id);
    
    $result = db_query($sql, "Could not check line_bill_reference_exists");  
    
    if(db_num_rows($result) > 0){
        return true;
    }
    else{
        return false;
    }
}

?>
