<?php
include('../headertop.php');
include('../db.php');
//include('../datepicker.php');

$disp = '';
$pagetitle = 'View/Search Expenses';

if(!isset($_POST['dailyexpensesubmit']))
{
echo '<meta http-equiv="refresh" content="0;URL=dailyexpense.php">';
exit;
}

$date = $_POST['date'];
$date2 = $_POST['date2'];
$project = $_POST['project'];

if(empty($project))
{
echo '<meta http-equiv="refresh" content="0;URL=dailyexpense.php?sm=Please select dates">';
exit;
}

$qtemp = "create temporary table expensestemp (date date,amount float,work_type  varchar(40))";
$qest = "create temporary table estimatestemp(project_id int(3), project_name varchar(40),work_type int(3),estimate float)";
mysqli_query($dbcon,$qtemp) or die(mysqli_error($dbcon));
mysqli_query($dbcon,$qest) or die(mysqli_error($dbcon));

if($project=='all')
{
$qest2 = "insert into estimatestemp(project_id,project_name,work_type,estimate) select project_id,project_name,work_type_id,estimate from estimates";
$rest2 = mysqli_query($dbcon,$qest2);
@$rwest2 = mysqli_fetch_array($rest2);

if(empty($date) and empty($date2))
{
$q = "select * from expenses";
$qins = "insert into expensestemp(date,amount,work_type) select date,amount,work_type from expenses";
$qsum = "select sum(amount) as total_sum from expenses";
}
else
{
$q = "select * from expenses where date between '$date' and '$date2'";
$qins = "insert into expensestemp(date,amount,work_type) select date,amount,work_type from expenses where date between '$date' and '$date2'";
$qsum = "select sum(amount) as total_sum from expenses where date between '$date' and '$date2'";
}
}


else
{
$qest2 = "insert into estimatestemp(project_id,project_name,work_type,estimate) select project_id,project_name,work_type_id,estimate from estimates where project_id='$project'";
$rest2 = mysqli_query($dbcon,$qest2);
@$rwest2 = mysqli_fetch_array($rest2);

if(empty($date) and empty($date2))
{
$q = "select * from expenses where project='$project'";
$qins = "insert into expensestemp(date,amount,work_type) select date,amount,work_type from expenses where project='$project'";
$qsum = "select sum(amount) as total_sum from expenses where project='$project'";
}
else
{
$q = "select * from expenses where project='$project' and date between '$date' and '$date2'";
$qins = "insert into expensestemp(date,amount,work_type) select date,amount,work_type from expenses where project='$project' and date between '$date' and '$date2'";
$qsum = "select sum(amount) as total_sum from expenses where project='$project' and date between '$date' and '$date2'";
}
}

mysqli_query($dbcon,$qins) or die(mysqli_error($dbcon));
$rsum = mysqli_query($dbcon,$qsum) or die(mysqli_error($dbcon));
$rwsum = mysqli_fetch_array($rsum);

//$disp .= '<h3>Viewing Expenses  Between '.$date.' to '.$date2.' :</h3> <br>';
$disp .= '<button class="btn btn-warning" style="margin-bottom:15px">Total Expenses: <a href="viewexpenses.php?date='.$date.'&date2='.$date2.'&project='.$project.'&work_type=all">'.$rwsum['total_sum'].'</a></button><table class="table table-striped"><thead><th>Work</th><th>Total Expense Amount</th><th>Total Estimated Cost</th><th>Difference</th></thead><tbody>';
$qwork = "select * from work_type";
$rwork = mysqli_query($dbcon,$qwork);
while($rwwork = mysqli_fetch_array($rwork))
{
$work_type = $rwwork['id'];

$qtotal = "select sum(amount) as total_exp from expensestemp where work_type='$work_type'";
$rtotal = mysqli_query($dbcon,$qtotal);
$rwtotal = mysqli_fetch_array($rtotal);

$qest3 = "select sum(estimate) as total_est from estimatestemp where work_type='$work_type'";
$rest3 = mysqli_query($dbcon,$qest3);
$rwest3 = mysqli_fetch_array($rest3);

$diff =   $rwest3['total_est'] - $rwtotal['total_exp'];

$disp .='<tr><td>'.$rwwork['work_type'].'</td><td> <a href="viewexpenses.php?date='.$date.'&date2='.$date2.'&project='.$project.'&work_type='.$work_type.'" target=_blank>'.$rwtotal['total_exp'].'</a></td><td>'.$rwest3['total_est'].'</td>';

if($diff<1)
$disp .= '<td style="background-color:red;color:white">'.$diff.'</td></tr>';
else
$disp .= '<td style="background-color:green;color:white">'.$diff.'</td></tr>';
}
$disp .= '</tbody></table>';

$qdroptemp = "DROP temporary TABLE expensestemp";
$qdropest = "DROP temporary TABLE estimatestemp";							
mysqli_query($dbcon,$qdroptemp) or die(mysqli_error($dbcon));
mysqli_query($dbcon,$qdropest) or die(mysqli_error($dbcon));



include('../header.php');
include('../template.php');