I am generating a report in Excel, however, when I try to add two columns and show them in: sumHoras = dateInput + dateExit
a) The result is successful as long as the operation is carried out in this way: -> setCellValue ('I'. $ i, abs ($ data ['dateInput'] - $ data ['dateExit'])); // cell I will be the result of sumHoras foreach ($ employees as $ row) {
$datos = $db-> consultarFecha($row['idemp'],$row['fecha']);
$suma = ($datos['fechaEntrada']- $datos['fechaSalida']);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A' .$i, $row['idemp'])
->setCellValue('B'. $i, $row['Nombre'])
->setCellValue('C'. $i, $row['fecha'])
->setCellValue('D'. $i, $row['horas'])
->setCellValue('E'. $i, $row['area'])
->setCellValue('F'. $i, $detalles['depto'])
->setCellValue('G'. $i, $datos['fechaEntrada'])
->setCellValue('H'. $i, $datos['fechaSalida'])
->setCellValue('I'. $i, abs($datos['fechaEntrada']- $datos['fechaSalida']));
//->setCellValue('I'. $i, $datos['sumaHoras']);
$i++;
}
1) The result will be as expected as long as (dateInput is AM) + (dateOutput is in PM)
2) The result will be successful no matter that DateEntry and DateExit do not contain data.
However, for option a) it does not correctly add the fields dateInput in PM + dateExit in AM.
b) The result is successful as long as the operation is carried out in this way and meets the numbers below: -> setCellValue ('I'. $ i, $ data ['sumHoras']);
foreach ($empleados as $row) {
$datos = $db-> consultarFecha($row['idemp'],$row['fecha']);
$suma = ($datos['fechaEntrada']- $datos['fechaSalida']);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A' .$i, $row['idemp'])
->setCellValue('B'. $i, $row['Nombre'])
->setCellValue('C'. $i, $row['fecha'])
->setCellValue('D'. $i, $row['horas'])
->setCellValue('E'. $i, $row['area'])
->setCellValue('F'. $i, $detalles['depto'])
->setCellValue('G'. $i, $datos['fechaEntrada'])
->setCellValue('H'. $i, $datos['fechaSalida'])
//->setCellValue('I'. $i, abs($datos['fechaEntrada']- $datos['fechaSalida']));
->setCellValue('I'. $i, $datos['sumaHoras']);
$i++;
}
1) The result will be successful as long as DateEntry and DateExit contain data.
Hourly Exit Entry
/ 22: 22: 46 06:10:51 8 / / 22: 09: 00 06:03:00 8 / / 21: 32: 02 06:05:36 9 / / 21: 30: 03 06:12:35 9 / / 21: 30: 03 06:12:35 9 /
2) Successful result if field dateEntry or dateExit are previous dates to the current one. Example Date Current 06/27/2017, date Entry and date Departure < to currentdate
3) If dateEntrance or dateExit does not contain data or dateEntry and dateExit are subsequent to the current (the reports will always handle a date after the current date) the report is not generated and shows the following error: PHP Notice: Undefined index: sumHoras in A: ...... \ excelrpt.php on line 235 PHP Notice: Undefined index: sumHoras in A: ...... \ excelrpt.php on line 235 PHP Notice: Undefined index: sumHoras in A: ...... \ excelrpt.php on line 235 PHP Notice: Undefined index: sumHoras in A: ...... \ excelrpt.php on line 235
"For what I notice is that the number of error lines (PHP Notice: Undefined index: sumHoras in A: ...... \ excelrpt.php on line 235) is equal to the number of records that do not contain a dateEntrance or dateExit "
Consultation to BDD -> DATEDIFF (HOUR, dateEntrance, dateExit)% 24 AS sumHours - > Makes the addition of PM to AM format possible
public function consultarFecha($empno,$fechainicio)
{
$res = Array("idclave"=>"","fechaEntrada"=>"","fechaSalida"=>"");
$qry = "select
CONVERT(VARCHAR(20),fechaSalida,108) AS fechaSalida,
CONVERT(VARCHAR(20),fechaEntrada,108) AS fechaEntrada,
DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS sumaHoras
from tblasistencia where idclave = '".$empno."' and
fechaEntrada BETWEEN CONVERT(DATETIME, '".$fechainicio." 00:00:00', 103) and
CONVERT(DATETIME, '".$fechainicio." 23:59:59',103)";
$stmt = sqlsrv_query( $this->conn,$qry);
if( $stmt === false) {
die( print_r( sqlsrv_errors() . " qry " .$qry, true) );
}
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
$res["fechaEntrada"] = $row['fechaEntrada'];
$res["fechaSalida"] = $row['fechaSalida'];
$res["sumaHoras"] = $row['sumaHoras'];
}
sqlsrv_free_stmt( $stmt);
return $res;
}
Attempts
1) - Try from the query to put a date by default, the date entered and even the departure date if they were set to '1900-01-01 00: 00: 00.000' however the result was the same: PHP Notice: Undefined index: sumHoras in A: ...... \ excelrpt.php on line 235 PHP Notice: Undefined index: sumHoras in A: ...... \ excelrpt.php on line 235 .................................................. ............
$qry = "select
CASE WHEN fechaEntrada IS NULL THEN '1900-01-01 00:00:00.000'
ELSE CONVERT(VARCHAR(20), fechaEntrada, 108) END AS
[fechaEntrada],
CONVERT(VARCHAR(20),fechaSalida,108) AS fechaSalida,
DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS sumaHoras
from tblasistencia where idclave = '".$empno."' and
fechaEntrada BETWEEN CONVERT(DATETIME, '".$fechainicio."
00:00:00', 103) and
CONVERT(DATETIME, '".$fechainicio." 23:59:59',103)";
2-Try validating from PHPexcel:
setCellValue ('I', $ i, '= IF (sumHoras == 15.6, dateInput + dateExit)');
3- Try starting from this operation: $ datetime1 = strtotime ($ data ['dateInput']); $ datetime2 = strtotime ($ data ['Exit date ']); $ interval = abs ($ datetime1 - $ datetime2); $ hours = round (($ interval / 60) / 60); and assign it to: -> setCellValue ('I'. $ i, $ hours);
In summary option B is the correct one but I can not correct when the conditions of subsection b) are met and the error comes out PHP Notice: Undefined index: sumHoras in A: ...... \ excelrpt.php on line 235 PHP Notice: Undefined index: sumHoras in A: ...... \ excelrpt.php on line 235 .................................................. .................................................. .................................................. ......... That is when 1 or many records have dates after the current one, that is, those dates are not yet fulfilled, but when they are fulfilled, the report will obviously be generated since the dates of entry and date of departure will be < To the current date; however, the reports must be generated no matter that those fields do not contain data, as in option a).