<?php

header("Content-Type:   application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=Expense-Report-USD-" . date('d-m-Y') . ".xls");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);


$db = new Conexion;
$user = new User;
$core = new Core;
$userData = $user->cdp_getUserData();


$range = $_REQUEST['range'];

$sWhere = ""; 
$name_off=$userData->name_off;


if ($agency > 0) {

	$db->cdp_query("SELECT * FROM cdb_branchoffices where id= '" . $agency . "'");
	$agencys = $db->cdp_registro();

	$agencyFilter = $agencys->name_branch;

	$sWhere .= " and a.agency = '" . $agency . "'";
} else {

	$agencyFilter = 'All';
}


if (!empty($range)) {

	$fecha =  explode(" - ", $range);
	$fecha = str_replace('/', '-', $fecha);

	$fecha_inicio = date('Y-m-d', strtotime($fecha[0]));
	$fecha_fin = date('Y-m-d', strtotime($fecha[1]));


	$sWhere .= " and  created_date between '" . $fecha_inicio . "'  and '" . $fecha_fin . "'";
}

if($name_off == 'undefined'){
    
  $sql = "SELECT * FROM expenses_master 
        Where created_date between '" . $fecha_inicio . "'  and '" . $fecha_fin . "'
        and id > 1 
        and currency = '$'
        order by created_date desc";  
    
} else{
    
   $sql = "SELECT * FROM expenses_master
        Where created_date between '" . $fecha_inicio . "'  and '" . $fecha_fin . "'
        and currency = '$'
        and name_off='$name_off'
        order by created_date desc"; 
}

if($name_off == 'undefined'){

$db->cdp_query("SELECT IFNULL(SUM(expense_amount),0) as total FROM expenses_master where created_date between '" . $fecha_inicio . "'  and '" . $fecha_fin . "' and currency='$'");
$db->cdp_execute();
$row = $db->cdp_registro();
$dexp = $row->total;

$db->cdp_query("SELECT IFNULL(SUM(expense_amount),0) as total FROM expenses_master where created_date between '" . $fecha_inicio . "'  and '" . $fecha_fin . "' and currency='Ksh.'");
$db->cdp_execute();
$row = $db->cdp_registro();
$kexp = $row->total;    
    
}else{
    
$db->cdp_query("SELECT IFNULL(SUM(expense_amount),0) as total FROM expenses_master where created_date between '" . $fecha_inicio . "'  and '" . $fecha_fin . "' and currency='$' and name_off='$name_off' ");
$db->cdp_execute();
$row = $db->cdp_registro();
$dexp = $row->total;

$db->cdp_query("SELECT IFNULL(SUM(expense_amount),0) as total FROM expenses_master where created_date between '" . $fecha_inicio . "'  and '" . $fecha_fin . "' and currency='Ksh.' and name_off='$name_off' ");
$db->cdp_execute();
$row = $db->cdp_registro();
$kexp = $row->total;

}

$query_count = $db->cdp_query($sql);
$db->cdp_execute();
$numrows = $db->cdp_rowCount();


$db->cdp_query($sql);
$data = $db->cdp_registros();

$fecha = str_replace('-', '/', $fecha);

$html = '
	<html>
		<body>
		
		<h2>' . $core->site_name . '<br>
		Expense Report - USD<br>

		[' . $fecha[0] . ' - ' . $fecha[1] . ']
		<br>
		agency: ' . $name_off . '

		</h2>


		<table border=1>
		<tbody>
			<tr style="background-color: #3e5569; color: white">				
				<th><b></b></th>
				<th><b>Expense Title</b></th>
				<th><b>Expense Type</b></th>
				<th><b>User</b></th>
				<th><b>Expense Amount</b></th>
				<th><b>Expense Agency</b></th>
				<th><b>Created Date</b></th>
				<th><b>Currency</b></th>
			</tr>';

if ($numrows > 0) {

	$count = 0;
	$sumador_weight = 0;
	$sumador_subtotal = 0;
	$sumador_discount = 0;
	$sumador_insurance = 0;
	$sumador_c_tariff = 0;
	$sumador_tax = 0;
	$sumador_total = 0;
	$sumador_declared_tax = 0;
	$sumador_fixed_charge = 0;

	foreach ($data as $row) {

		$db->cdp_query("SELECT * FROM cdb_users where id= '" . $row->sender_id . "'");
		$sender_data = $db->cdp_registro();

        $db->cdp_query("SELECT * FROM cdb_users where id= '" . $row->receiver_id . "'");
		$receiver_data = $db->cdp_registro();

		$db->cdp_query("SELECT * FROM cdb_courier_com where id= '" . $row->order_courier . "'");
		$courier_com = $db->cdp_registro();

        $db->cdp_query("SELECT currency FROM cdb_add_order WHERE order_id='" . $row->order_id . "'");
        $curr = $db->cdp_registro();

		$db->cdp_query("SELECT * FROM cdb_styles where id= '14'");
		$status_style_pickup = $db->cdp_registro();

		$db->cdp_query("SELECT * FROM cdb_styles where id= '13'");
		$status_style_consolidate = $db->cdp_registro();

		$db->cdp_query("SELECT * FROM cdb_address_shipments where order_track='" . $row->order_prefix . $row->order_no . "'");
		$address_order = $db->cdp_registro();
		
		
		if ($row->status_invoice == 1) {
			$text_status = $lang['invoice_paid'];
			$label_class = "label-success";
		} else if ($row->status_invoice == 2) {
			$text_status = $lang['invoice_pending'];
			$label_class = "label-warning";
		} else if ($row->status_invoice == 3) {
			$text_status = $lang['invoice_due'];
			$label_class = "label-danger";
		}



		$weight = $row->total_weight;
		$sub_total = $row->sub_total;
		$discount = $row->total_tax_discount;
		$insurance = $row->total_tax_insurance;
		$custom_c = $row->total_tax_custom_tariffis;
		$tax = $row->total_tax;
		$total_declared_tax = $row->total_declared_value;
		$total_fixed_charge = $row->total_fixed_value;

		$total = $row->expense_amount;

		$sumador_total += $total;
		
		$db->cdp_query('SELECT IFNULL(SUM(expense_amount),0) as total FROM expenses_master where currency="Ksh."');
        $db->cdp_execute();
        $ksum = $db->cdp_registro();
        $sum1 = $ksum->total;
        
        $db->cdp_query('SELECT IFNULL(SUM(expense_amount),0) as total FROM expenses_master where currency="$"');
        $db->cdp_execute();
        $usum = $db->cdp_registro();
        $sum2 = $usum->total;


		$count++;

		$html .= '<tr>';
		$html .= '<td ><b>' . $count . '</b></td>';
		$html .= '<td>' . $row->expense_title . '</td>';
		$html .= '<td>' . $row->expense_type . '</td>';
		$html .= '<td>' . $row->pre_user . '</td>';
		$html .= '<td>' . $row->currency . ' ' . $row->expense_amount . '</td>';
		$html .= '<td>' . $row->name_off . '</td>';
		$html .= '<td>' . $row->created_date . '</td>';
		$html .= '<td>' . $row->currency . '</td>';
		$html .= '</tr>';
	}

	$html .= '<tr>';
	$html .= '<td><b>TOTAL</td> </b>';
	$html .= '<td  colspan="2"></td>';
	$html .= '<td><b></b></td>';
	$html .= '<td><b>$ ' . number_format($dexp) . ' </b></td>';
	$html .= '<td><b></b></td>';
	$html .= '</tr>';
}

$html .= '</table></html>';
echo utf8_decode($html);
