我有一个脚本,可以从员工在holiday_entitlement表中的当前记录中获取他们的假期权利。
然后,此权利数字将用于数据库中要从中减去的每个假日请求。
例如:权利天数 = 27
假日请求天数 = 剩余 2>>>> 个权利天数 = 25
假日请求天数 = 剩余 4>>>> 个权利天数 = 21
假日请求天数 = 剩余 1>>>> 个权利天数 = 20等等....
在我的脚本中,剩余的权利天数是$remainder
我正在努力解决的一点是能够在每次批准请求时将此$remainder保存到holiday_entitlement表中,或者在SQL中计算出原始权利数字减去该员工的所有请求,该学年以及是否获得批准。
<?php
$is_business_result = mysql_query('SELECT * FROM holiday_entitlement WHERE employee = ''' . $username . ''' AND academic_year = ''' . $acyear . ''' ');
if($is_business = mysql_fetch_array($is_business_result)) {
echo'<div style="float:left; width:400px;">';
echo'<table width="100%">
<tr>
<td><strong>Name:</strong></td>
<td>'.$is_business['employee'].'</td>
</tr>
<tr>
<td><strong>Entitlement:</strong></td>
<td>'.$is_business['new_entitlement'].' '.$is_business['units'].'</td>
</tr>
<tr>
<td><strong>Department / Division:</strong></td>
<td>'.$is_business['division'].'</td>
</tr>
<tr>
<td><strong>Line Manager:</strong></td>
<td>'.$is_business['line_manager'].'</td>
</tr>
</table>';
echo'</div>';
echo'<table class="dataTable" id="business_days_table" cellpadding="2" cellspacing="2" width="100%">
<thead>
<th>Start Date</th>
<th>End Date</th>
<th>Days to be taken</th>
<th>Days remaining</th>
</thead>';
echo '<tr>';
echo '<td>-</td>';
echo '<td>-</td>';
echo '<td>-</td>';
echo '<td>'.$is_business['new_entitlement'].'</td>';
echo '</tr>';
$input = $is_business['new_entitlement'];
}
else {echo 'You currently dont have a record for this academic year. ';}
$requests_result = mysql_query('SELECT * FROM requests WHERE employee = ''' . $username . ''' AND approved = 1 AND academic_year = ''' . $acyear . ''' ORDER BY start_date ASC');
$remainder = 0;
while($requests = mysql_fetch_array($requests_result)) {
$start_date = new DateTime($requests['start_date']);
$end_date = new DateTime($requests['end_date']);
$timestamp_start_date = $start_date->getTimestamp();
$timestamp_end_date = $end_date->getTimestamp();
$formatted_start_date = date("d M Y", $timestamp_start_date);
$formatted_end_date = date("d M Y", $timestamp_end_date);
$remainder = ($remainder == 0) ? $input : $remainder;
$out = $remainder - $requests['days'];
if($out < 0){
break;
}
$remainder = $out;
echo'<tr>';
echo'<td>'.$formatted_start_date.'</td>';
echo'<td>'.$formatted_end_date.'</td>';
echo'<td>'.$requests['days'].'</td>';
echo'<td>'.$remainder.'</td>';
echo'</tr>';
}
echo'</table>';
?>
在SQL中计算出原始权利数字减去该员工的所有请求,该学年以及是否获得批准
应该是这样的:
select new_entitlement-(select sum(days) from requests where employee="sashn" and academic_year=2012 and approved=1) from holiday_entitlement where employee="sashn";
或作为更新:
update holiday_entitlement set new_entitlement=27-(select sum(days) from requests where employee="sashn" and academic_year=2012 and approved=1) where employee="sashn" and academic_year=2012;