Thank you in advance.
I'm going around trying to subtract PM from AM
The difference of format AM to PM the result is correct; however when rest of PM to AM the result is not the expected one
The insertion in the BD I do it this way:
public function insertarTiempo($usuario,$depto,$turno,$super,$jefe,$gerente,$area,$costo,$cantidad,$motivo,$status,$fecha,$fechaInicial,$fechaFinal)
{
$idinserted = 0;
$week= "" . date("W", strtotime($fecha));
$sql = "INSERT into tblHorasExtra VALUES('".$usuario."','".$depto."','".$turno."','".$super."','".$jefe."','".$gerente."','".$area."',".$costo.",'".$cantidad."','".$motivo."','".$status."',CONVERT(DATETIME, '".$fecha." 00:00:00', 103),0,0,0,'".$week."',CONVERT(DATETIME, '".$fechaInicial." 00:00:00', 103),CONVERT(DATETIME, '".$fechaFinal." 00:00:00', 103)); SELECT SCOPE_IDENTITY()";
//echo $sql;
$stmt = sqlsrv_query($this->conn,$sql);
if( $stmt === false) {
die( print_r( sqlsrv_errors(),true));
echo " <br/>" . $sql;
$done = false;
}else
{
$done = true;
}
//Liberar memoria
sqlsrv_next_result($stmt);
sqlsrv_fetch($stmt);
$idinserted = sqlsrv_get_field($stmt, 0);
sqlsrv_free_stmt($stmt);
return $idinserted;
}
Y la consulta de esta manera:
public function mostrarDetalles($folio)
{
$res = array();
$i = 0;
$stmt = sqlsrv_query( $this->conn,"SELECT t.idTiempo as folio,c.nombre + ' ' + c.apellidos as Supervisor,d.nombre + ' ' + d.apellidos as jefe ,f.nombre + ' ' + f.apellidos as gerente,ar.Descripcion as area,dp.Descripcion as depto,t.costo,t.cantidad,t.motivo,convert(nvarchar(30), t.fecha, 103) as fecha ,convert(nvarchar(30), t.fechaInicial, 103) as fechaInicial,convert(nvarchar(30), t.fechaFinal, 103) as fechaFinal,t.status,t.AprobSuper,t.AprobJefe,t.AprobGerente,t.idSuper,t.idJefe,t.idGerente,tu.Descripcion as turno,t.semana from tblHorasExtra t JOIN tblpersonal c on t.idsuper = c.idclave JOIN tblpersonal d on t.idJefe = d.idclave JOIN tblpersonal f ON t.idgerente = f.idclave JOIN tblArea ar ON t.idArea = ar.idArea JOIN tblDepto dp ON dp.idDepto = t.idDepto JOIN tblturno tu ON t.idTurno = tu.idTurno where idTiempo = ".$folio." order by folio desc");
//select CONVERT(varchar(15),CAST('17:30:00.0000000' AS TIME),100)
if( $stmt === false) {
echo $qry;
die( print_r( sqlsrv_errors(), true) );
}
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
$res["folio"] = $row['folio'];
$res["Supervisor"] = $row['Supervisor'];
$res["jefe"] = $row['jefe'];
$res["Gerente"] = $row['gerente'];
$res["jefe"] = $row['jefe'];
$res["gerente"] = $row['gerente'];
$res["area"] = $row['area'];
$res["turno"] = $row['turno'];
$res["depto"] = $row['depto'];
$res["costo"] = $row['costo'];
$res["semana"] = $row['semana'];
$res["fechaInicial"] = $row['fechaInicial'];
$res["fechaFinal"] = $row['fechaFinal'];
$res["cantidad"] = $row['cantidad'];
$res["motivo"] = $row['motivo'];
$res["status"] = $row['status'];
$res["idSuper"] = $row['idSuper'];
$res["idJefe"] = $row['idJefe'];
$res["idGerente"] = $row['idGerente'];
$res["fecha"] = $row['fecha'];
$res["AprobSuper"] = $row['AprobSuper'];
$res["AprobJefe"] = $row['AprobJefe'];
$res["AprobGerente"] = $row['AprobGerente'];
}
sqlsrv_free_stmt($stmt);
return $res;
}
And finally I do the subtraction to be able to show it in EXCEL
foreach ($empleados as $row) {
$datos = $db->consultarFecha($row['idemp'],$row['fecha']);
$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']));
//Resta de Horas con valor absoluto, para no mostrar horas en negativo: ->setCellValue('I'. $i, abs($datos['fechaEntrada']- $datos['fechaSalida']));
$i++;
}