#php #curl #phpexcel
#php #curl #phpexcel
Вопрос:
Я пытаюсь создать скрипт, который ищет в базе данных записи в очереди и для каждой из них генерирует и отправляет электронное письмо с прикрепленным отчетом Excel.
Сценарий следующий: cjob_send_async_reports.php и он выполняет задание cron каждую минуту.
<?php
error_log('-- START SEND ASYNC',0);
include realpath(dirname(__FILE__)) . '/../configuration.php';
$creds = array($rds_fm[0][0],$rds_fm[0][1]);
$db = db_on($creds);
$sql = " SELECT * FROM _itrack_business_async_reports_pro5 WHERE async_status = 1 ORDER BY async_reports_date ASC LIMIT 1";
try{
$stmt = $db->prepare($sql);
$stmt->execute();
}catch(PDOException $ex) {
itrack_error_log($dbg,$cjob_name,$ex->getMessage(),$sql);
}
if($stmt->rowCount() == 1){
error_log('-- ASYNC REPORT FOUND',0);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$curr_proc = date('Y-m-d H:i:s');
$id = $row['async_reports_id'];
$reg_id = $row['async_reports_register_id'];
$from = $row['async_reports_start_time'];
$to = $row['async_reports_stop_time'];
$email = $row['async_reports_email'];
$diary = ($row['async_reports_diary'] == 'yes') ? 'true' : 'false';
$report = $row['async_report'];
$vehIDs = $row['async_vehIDs'];
$poiIDs = $row['async_poiIDs'];
$xls = REPORTS_HOST . 'reports/daily_report.php';
$attrs = '';
$attrs .= '?reg_id='.$reg_id;
$attrs .= 'amp;dev_id='.$vehIDs;
$attrs .= 'amp;from='.$from;
$attrs .= 'amp;to='.$to;
error_log('-- LINK FOR REPORT: ' . $xls . $attrs,0);
$sql_upd = " UPDATE _itrack_business_async_reports_pro5
SET async_status = -1,
async_reports_date_proc = '".date('Y-m-d H:i:s')."'
WHERE async_reports_id = $id";
try{
$stmt_upd = $db->prepare($sql_upd);
$stmt_upd->execute();
}catch(PDOException $ex) {
itrack_error_log($dbg,$cjob_name,$ex->getMessage(),$sql_upd);
}
error_log('-- sql_upd: ' . $sql_upd,0);
error_log('-- PREPARE FOR CURL ',0);
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL,$xls);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_VERBOSE, true);
$data = array(
'reg_id' => $reg_id ,
'dev_id' => $vehIDs ,
'from' => $from ,
'to' => $to
);
curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
error_log('-- curl_exec ',0);
$out = curl_exec($ch);
if ($out === FALSE) {
error_log('-- curl_error: ' . curl_error($ch),0);
}
curl_close($ch);
error_log('-- curl_exec DONE ',0);
error_log('-- CONNECT AGAIN ON RDS1 ',0);
$db = db_on($creds);
$sql_reg = " SELECT register_owner,register_language,register_username
FROM _itrack_business_register
WHERE register_id = $reg_id";
try{
$stmt_reg = $db->prepare($sql_reg);
$stmt_reg->execute();
}catch(PDOException $ex) {
itrack_error_log($dbg,$cjob_name,$ex->getMessage(),$sql_reg);
}
$row_reg = $stmt_reg->fetch(PDO::FETCH_ASSOC);
$COMPANY = $row_reg['register_owner'];
$lan = $row_reg['register_language'];
$username = $row_reg['register_username'];
$enable_register = (substr($username,0,8) == "disable-") ? false : true;
$printingDay = date("d-m-Y H:i");
$nickname = '';
html_entity_decode(trim($row_dev['devices_nickname']), ENT_NOQUOTES, 'UTF-8');
$filename = "iTrack-Report_[".$nickname."]:(".$from.")-(".$to.").".date('d-m-Y').".xls";
error_log('-- filename ' . $filename,0);
$path = realpath(dirname(__FILE__)) . '/../async_reports/';
file_put_contents($path.$filename,$out);
error_log('-- SAVE ON: ' . $path,0);
$file = $path.$filename;
$file_size = filesize($file);
error_log('-- file_size: ' . $file_size,0);
error_log('-- PREPARE TO SEND REPORT ',0);
$handle = fopen($file, "r");
$content = fread($handle, $file_size);
fclose($handle);
$content = chunk_split(base64_encode($content));
$subject = "iTrack.gr - Automated Reporting System";
$lng_txt = main_lan($lan);
$message =
' <html>
<head><title>iTrack - Automated Reporting System</title></head>
<body>'.$lng_txt[595].$lng_txt[596].'</body>
</html>
';
// a random hash will be necessary to send mixed content
$separator = md5(time());
// carriage return type (we use a PHP end of line constant)
$eol = PHP_EOL;
// main header (multipart mandatory);
$headers = "From: iTrack Report Service <alert@itrack.gr>" . $eol;
$headers .= "MIME-Version: 1.0" . $eol;
$headers .= "Content-Type: multipart/mixed; boundary="" . $separator . """ . $eol . $eol;
$headers .= "Content-Transfer-Encoding: 7bit" . $eol;
$headers .= "This is a MIME encoded message." . $eol . $eol;
// message
$headers .= "--" . $separator . $eol;
$headers .= "Content-Type: text/html; charset="utf-8"" . $eol;
$headers .= "Content-Transfer-Encoding: 8bit" . $eol . $eol;
$headers .= $message . $eol . $eol;
// attachment
$headers .= "--" . $separator . $eol;
$headers .= "Content-Type: application/octet-stream; name="" . $filename . """ . $eol;
$headers .= "Content-Transfer-Encoding: base64" . $eol;
$headers .= "Content-Disposition: attachment" . $eol . $eol;
$headers .= $content . $eol . $eol;
$headers .= "--" . $separator . "--";
//SEND Mail
if($enable_register){
echo (mail($email, $subject, "", $headers,'-f alert@itrack.gr')) ? "done!" : "mail send ... ERROR!";
}
error_log('-- MAIL SENT ',0);
$sql_0 = " UPDATE _itrack_business_async_reports_pro5
SET async_status = 0,
async_reports_date_done = '".date('Y-m-d H:i:s')."'
WHERE async_reports_id = ".$id;
error_log('-- SQL UPDATE: ' . $sql_0,0);
try{
$stmt_0 = $db->prepare($sql_0);
$stmt_0->execute();
}catch(PDOException $ex) {
itrack_error_log($dbg,$cjob_name,$ex->getMessage(),$sql_0);
}
error_log('-- SQL DONE - REPORT IS OVER ',0);
}
?>
Через curl в php выполняется веб-вызов, который генерирует отчет. Сценарий следующий:
<?php
error_log("start",0);
include realpath(dirname(__FILE__)) . '/../configuration.php';
if(isset($_REQUEST['reg_id'],$_REQUEST['from'],$_REQUEST['to'])){
$from = $_REQUEST['from'];
$date_exp = explode(" ",$from);
$ymd = explode("-",$date_exp[0]);
if(!checkdate($ymd[1],$ymd[2],$ymd[0])) die('invalid date');
$to = $_REQUEST['to'];
$date_exp = explode(" ",$to);
$ymd = explode("-",$date_exp[0]);
if(!checkdate($ymd[1],$ymd[2],$ymd[0])) die('invalid date');
/* fix end date */
if(strtotime($to) - strtotime($from) > GO_TO_ARCHIVE){
$to = date("Y-m-d ",strtotime($from . " 10 days"))."23:59:59";
}
$reg_id = $_REQUEST['reg_id'];
$ii = look_up_rds_fm($reg_id);
$creds = array($rds_fm[$ii][0],$rds_fm[$ii][1]);
$db = db_on($creds);
error_log("from = " . $from ,0);
error_log("to = " . $to ,0);
error_log("reg_id = " . $reg_id ,0);
$sql = " SELECT register_owner,register_language,register_map,register_subaccounts
FROM _itrack_business_register
WHERE register_id = :reg_id";
try {
$stmt = $db->prepare($sql);
$stmt->bindValue(':reg_id', $reg_id, PDO::PARAM_INT);
$stmt->execute();
}catch(PDOException $ex){die("ERROR 1: ".$ex->getMessage());}
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$lan = $row['register_language'];
$owner = $row['register_owner'];
$map_url = $row['register_map'];
$regss = explode(",",$row['register_subaccounts']);
$lng_txt = main_lan($lan);
$dev_id = $_REQUEST['dev_id'];
$ii = look_up_rds_fm($dev_id,'dev');
$creds = array($rds_fm[$ii][0],$rds_fm[$ii][1]);
$db = db_on($creds);
$sql_dev = "SELECT devices_id,devices_model,devices_nickname,devices_digital_in,
devices_odom,devices_calibration,devices_digital_type,devices_driver,
devices_obd
FROM _itrack_business_devices
WHERE devices_id = :dev_id";
try {
$stmt_dev = $db->prepare($sql_dev);
$stmt_dev->bindValue(':dev_id', $dev_id, PDO::PARAM_INT);
$stmt_dev->execute();
}catch(PDOException $ex){die("error:1-> ".$ex->getMessage());}
$row_dev = $stmt_dev->fetch(PDO::FETCH_ASSOC);
$nickname = html_entity_decode(trim($row_dev['devices_nickname']), ENT_NOQUOTES, 'UTF-8');
$driver = html_entity_decode((trim($row_dev['devices_driver'])), ENT_NOQUOTES, 'UTF-8');
$stepsForPause = ($row_dev['devices_calibration'] != 1) ? $row_dev['devices_calibration'] : 6;
$dig_enable = 0;
$digital_type = $row_dev['devices_digital_type'];
$din2_name = $din3_name = '';
$iButtons = array();
$pois = array();
$db = select_db_on($dev_id,$from,'dev');
$KM_WALKED = 0;
$gmaps = array();
$gmaps_odom = ',gmaps_virtual_odometer';
$gmaps_iButton = ',gmaps_iButton';
$gmaps_now = ',gmaps_now';
$dig_query = " ";
$sql_gmaps = "
SELECT gmaps_lat,gmaps_lon,gmaps_engine,gmaps_time,gmaps_geoc,gmaps_temp,gmaps_ovrtemp,
gmaps_stop,gmaps_speed,gmaps_lock,gmaps_signal $gmaps_now $gmaps_odom $gmaps_iButton $dig_query
FROM _itrack_business_gmaps
WHERE devices_id = :dev_id AND
gmaps_time >= :from AND
gmaps_time <= :to
GROUP BY gmaps_time
ORDER BY gmaps_time DESC";
try {
$stmt = $db->prepare($sql_gmaps);
$stmt->bindValue(':dev_id' , $dev_id , PDO::PARAM_INT);
$stmt->bindValue(':from' , $from , PDO::PARAM_STR);
$stmt->bindValue(':to' , $to , PDO::PARAM_STR);
$stmt->execute();
}catch(PDOException $ex){die("error:5-> ".$ex->getMessage());}
error_log("done sql" ,0);
while($row_gmaps = $stmt->fetch(PDO::FETCH_ASSOC)){
$gmaps[] = array(
'gmaps_lat' => $row_gmaps['gmaps_lat'],
'gmaps_lon' => $row_gmaps['gmaps_lon'],
'gmaps_engine' => $row_gmaps['gmaps_engine'],
'gmaps_time' => $row_gmaps['gmaps_time'],
'gmaps_now' => $row_gmaps['gmaps_now'],
'gmaps_geoc' => $row_gmaps['gmaps_geoc'],
'gmaps_stop' => $row_gmaps['gmaps_stop'],
'gmaps_speed' => $row_gmaps['gmaps_speed'],
'gmaps_lock' => $row_gmaps['gmaps_lock'],
'gmaps_signal' => $row_gmaps['gmaps_signal'],
'gmaps_temp' => $row_gmaps['gmaps_temp'],
'gmaps_ovrtemp' => $row_gmaps['gmaps_ovrtemp'],
'gmaps_odom' => '',
'gmaps_iButton' => '',
'gmaps_digital_input2' => '',
'gmaps_digital_input3' => '');
}
error_log("array built (". sizeof($gmaps) .")" ,0);
$KM_WALKED = $KM_WALKED_LV_CAN;
include realpath(dirname(__FILE__)) . '/../assets/excel/PHPExcel.php';
$styleThickBrownBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THICK,'color' => array('argb' => '00000000'),),),);
$styleThinBorder = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN,'color' => array('argb' => '00000000'),),),);
$objPHPExcel = new PHPExcel();
// Set Orientation, size and scaling
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
$objDrawing = new PHPExcel_Worksheet_Drawing();
$ptr_s = 7;
$ptr = 8;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I1', $lng_txt[473].":");
$objPHPExcel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J1', $lng_txt[165]);
$objPHPExcel->getActiveSheet()->getStyle('J1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('J1')->getFill()->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$objPHPExcel->getActiveSheet()->getStyle('J1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('J1')->applyFromArray($styleThinBorder);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J2', $lng_txt[166]);
$objPHPExcel->getActiveSheet()->getStyle('J2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('J2')->getFill()->getStartColor()->setRGB('FF8C00');
$objPHPExcel->getActiveSheet()->getStyle('J2')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('J2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleThinBorder);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J3', $lng_txt[474]);
$objPHPExcel->getActiveSheet()->getStyle('J3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('J3')->getFill()->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKBLUE);
$objPHPExcel->getActiveSheet()->getStyle('J3')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('J3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('J3')->applyFromArray($styleThinBorder);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$ptr_s , $lng_txt[328])
->setCellValue('B'.$ptr_s , $lng_txt[376])
->setCellValue('C'.$ptr_s , $lng_txt[431])
->setCellValue('D'.$ptr_s , $lng_txt[172] . " (km/h)")
->setCellValue('E'.$ptr_s , $lng_txt[375])
->setCellValue('F'.$ptr_s , 'i-Button')
->setCellValue('G'.$ptr_s , $din2_name)
->setCellValue('H'.$ptr_s , $din3_name)
->setCellValue('I'.$ptr_s , $lng_txt[377])
->setCellValue('J'.$ptr_s , $lng_txt[372]."/".$lng_txt[373]);
$engineSTATUS = -1;
$earth_radius = 6371;
$adj = 0;
$stop_time = 0;
$pause_time = 0;
$total_stop_time = 0;
$total_pause_time = 0;
$all_data = false;
$pauses_counter = 0;
$objPHPExcel->getActiveSheet()->freezePane('A'.$ptr);
$sizeofgmaps = sizeof($gmaps);
if($sizeofgmaps > 3){
$g = 0;
$geoc = '';
addPoint('finish', $gmaps[$g]["gmaps_lat"], $gmaps[$g]["gmaps_lon"], $gmaps[$g]["gmaps_time"], $gmaps[$g]["gmaps_now"], $gmaps[$g]["gmaps_speed"], $gmaps[$g]["gmaps_lock"], $gmaps[$g]["gmaps_signal"], $gmaps[$g]["gmaps_temp"], $gmaps[$g]["gmaps_ovrtemp"], $gmaps[$g]["gmaps_iButton"], $gmaps[$g]["gmaps_digital_input2"],$gmaps[$g]["gmaps_digital_input3"],'',$geoc);
for($g = 1; $g < $sizeofgmaps - 1; $g ){
if($gmaps[$g]["gmaps_ovrtemp"] == 1 || $gmaps[$g]["gmaps_lock"] == 1){
if($stop_time != 0){
$stop_time = abs(strtotime($gmaps[$g]["gmaps_time"]) - strtotime($gmaps[$g-1]["gmaps_time"]));
$total_stop_time = $stop_time;
$duration = getDurationCurr($stop_time);
$stop_time = 0;
$geoc = ($gmaps[$g]["gmaps_geoc"] != 0 amp;amp; isset($pois[$gmaps[$g]["gmaps_geoc"]])) ? $pois[$gmaps[$g]["gmaps_geoc"]] : BING_REV_GEOC($gmaps[$g]['gmaps_lat'], $gmaps[$g]['gmaps_lon']);
addPoint('stop', $gmaps[$g-1]["gmaps_lat"], $gmaps[$g-1]["gmaps_lon"], $gmaps[$g-1]["gmaps_time"], $gmaps[$g-1]["gmaps_now"], $gmaps[$g-1]["gmaps_speed"], $gmaps[$g-1]["gmaps_lock"], $gmaps[$g-1]["gmaps_signal"], $gmaps[$g-1]["gmaps_temp"], $gmaps[$g-1]["gmaps_ovrtemp"], $gmaps[$g-1]["gmaps_iButton"], $gmaps[$g-1]["gmaps_digital_input2"],$gmaps[$g-1]["gmaps_digital_input3"],$duration,$geoc);
}
if($gmaps[$g]["gmaps_engine"] == 0) $mode = 'stop';
else $mode = 'move';
$geoc = ($gmaps[$g]["gmaps_geoc"] != 0 amp;amp; isset($pois[$gmaps[$g]["gmaps_geoc"]])) ? $pois[$gmaps[$g]["gmaps_geoc"]] : BING_REV_GEOC($gmaps[$g]['gmaps_lat'], $gmaps[$g]['gmaps_lon']);
addPoint($mode, $gmaps[$g]["gmaps_lat"], $gmaps[$g]["gmaps_lon"], $gmaps[$g]["gmaps_time"], $gmaps[$g]["gmaps_now"], $gmaps[$g]["gmaps_speed"], $gmaps[$g]["gmaps_lock"], $gmaps[$g]["gmaps_signal"], $gmaps[$g]["gmaps_temp"], $gmaps[$g]["gmaps_ovrtemp"], $gmaps[$g]["gmaps_iButton"], $gmaps[$g]["gmaps_digital_input2"],$gmaps[$g]["gmaps_digital_input3"],'',$geoc);
}
else if($gmaps[$g]["gmaps_engine"] == 0 amp;amp; $g < $sizeofgmaps - 2){
if($pause_time != 0){
$pause_time = abs(strtotime($gmaps[$g]["gmaps_time"]) - strtotime($gmaps[$g-1]["gmaps_time"]));
$total_pause_time = $pause_time;
$duration = getDurationCurr($pause_time);
$pause_time = 0;
$geoc = ($gmaps[$g]["gmaps_geoc"] != 0 amp;amp; isset($pois[$gmaps[$g]["gmaps_geoc"]])) ? $pois[$gmaps[$g]["gmaps_geoc"]] : BING_REV_GEOC($gmaps[$g]['gmaps_lat'], $gmaps[$g]['gmaps_lon']);
addPoint('pause', $gmaps[$g-1]["gmaps_lat"], $gmaps[$g-1]["gmaps_lon"], $gmaps[$g-1]["gmaps_time"], $gmaps[$g-1]["gmaps_now"], $gmaps[$g-1]["gmaps_speed"], $gmaps[$g-1]["gmaps_lock"], $gmaps[$g-1]["gmaps_signal"], $gmaps[$g-1]["gmaps_temp"], $gmaps[$g-1]["gmaps_ovrtemp"], $gmaps[$g-1]["gmaps_iButton"], $gmaps[$g-1]["gmaps_digital_input2"],$gmaps[$g-1]["gmaps_digital_input3"],$duration,$geoc);
}
$stop_time = abs(strtotime($gmaps[$g]["gmaps_time"]) - strtotime($gmaps[$g-1]["gmaps_time"]));
$engineSTATUS = $gmaps[$g]["gmaps_engine"];
continue;
}
else if($gmaps[$g]["gmaps_engine"] == 1 amp;amp; $engineSTATUS == 0 amp;amp; $g < $sizeofgmaps - 2){
$stop_time = abs(strtotime($gmaps[$g]["gmaps_time"]) - strtotime($gmaps[$g-1]["gmaps_time"]));
$total_stop_time = $stop_time;
$duration = getDurationCurr($stop_time);
$stop_time = 0;
$geoc = ($gmaps[$g]["gmaps_geoc"] != 0 amp;amp; isset($pois[$gmaps[$g]["gmaps_geoc"]])) ? $pois[$gmaps[$g]["gmaps_geoc"]] : BING_REV_GEOC($gmaps[$g]['gmaps_lat'], $gmaps[$g]['gmaps_lon']);
addPoint('stop', $gmaps[$g-1]["gmaps_lat"], $gmaps[$g-1]["gmaps_lon"], $gmaps[$g-1]["gmaps_time"], $gmaps[$g-1]["gmaps_now"], $gmaps[$g-1]["gmaps_speed"], $gmaps[$g-1]["gmaps_lock"], $gmaps[$g-1]["gmaps_signal"], $gmaps[$g-1]["gmaps_temp"], $gmaps[$g-1]["gmaps_ovrtemp"], $gmaps[$g-1]["gmaps_iButton"], $gmaps[$g-1]["gmaps_digital_input2"],$gmaps[$g-1]["gmaps_digital_input3"],$duration,$geoc);
addPoint('move', $gmaps[$g]["gmaps_lat"], $gmaps[$g]["gmaps_lon"], $gmaps[$g]["gmaps_time"], $gmaps[$g]["gmaps_now"], $gmaps[$g]["gmaps_speed"], $gmaps[$g]["gmaps_lock"], $gmaps[$g]["gmaps_signal"], $gmaps[$g]["gmaps_temp"], $gmaps[$g]["gmaps_ovrtemp"], $gmaps[$g]["gmaps_iButton"], $gmaps[$g]["gmaps_digital_input2"],$gmaps[$g]["gmaps_digital_input3"],'','');
}
else if($gmaps[$g]["gmaps_engine"] == 1 amp;amp; $g < $sizeofgmaps - 2){
$stop_time = 0;
if($pause_time == 0 amp;amp; !$all_data){
$latitude1 = $gmaps[$g]['gmaps_lat'];
$longitude1 = $gmaps[$g]['gmaps_lon'];
$stop = ($g $stepsForPause < $sizeofgmaps - 2) ? ($g $stepsForPause) : $sizeofgmaps - 2;
$pauses_counter = 0;
for($p = $g 1; $p < $stop; $p ){
$latitude2 = $gmaps[$p]['gmaps_lat'];
$longitude2 = $gmaps[$p]['gmaps_lon'];
$dLat = deg2rad($latitude2 - $latitude1);
$dLon = deg2rad($longitude2 - $longitude1);
$a = sin($dLat/2) * sin($dLat/2) cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * sin($dLon/2) * sin($dLon/2);
$c = 2 * asin(sqrt($a));
$d = $earth_radius * $c * 1000;
if( $gmaps[$p]["gmaps_engine"] == 1 amp;amp;
$gmaps[$p]['gmaps_signal'] == 'F' amp;amp;
$gmaps[$p]['gmaps_speed'] == 0 amp;amp; $d < 50 amp;amp; $p != $g 1
){
$pause_time = abs(strtotime($gmaps[$p]["gmaps_time"]) - strtotime($gmaps[$p-1]["gmaps_time"]));
$pauses_counter ;
}
}
if($pauses_counter != $stepsForPause-2){
$pause_time = 0;
}
else{
$g = $stop-1;
continue;
}
}
else if($pause_time != 0 amp;amp; !$all_data){
$latitude2 = $gmaps[$g]['gmaps_lat'];
$longitude2 = $gmaps[$g]['gmaps_lon'];
$dLat = deg2rad($latitude2 - $latitude1);
$dLon = deg2rad($longitude2 - $longitude1);
$a = sin($dLat/2) * sin($dLat/2) cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * sin($dLon/2) * sin($dLon/2);
$c = 2 * asin(sqrt($a));
$d = $earth_radius * $c * 1000;
if( $gmaps[$g]["gmaps_engine"] == 1 amp;amp;
$gmaps[$g]['gmaps_signal'] == 'F' amp;amp;
$gmaps[$g]['gmaps_speed'] == 0 amp;amp; $d < 50
amp;amp; !$all_data
){
$pause_time = abs(strtotime($gmaps[$g]["gmaps_time"]) - strtotime($gmaps[$g-1]["gmaps_time"]));
continue;
}
else{
$pause_time = abs(strtotime($gmaps[$g]["gmaps_time"]) - strtotime($gmaps[$g-1]["gmaps_time"]));
$total_pause_time = $pause_time;
$duration = getDurationCurr($pause_time);
$pause_time = 0;
$geoc = ($gmaps[$g]["gmaps_geoc"] != 0 amp;amp; isset($pois[$gmaps[$g]["gmaps_geoc"]])) ? $pois[$gmaps[$g]["gmaps_geoc"]] : BING_REV_GEOC($gmaps[$g]['gmaps_lat'], $gmaps[$g]['gmaps_lon']);
addPoint('pause', $gmaps[$g-1]["gmaps_lat"], $gmaps[$g-1]["gmaps_lon"], $gmaps[$g-1]["gmaps_time"], $gmaps[$g-1]["gmaps_now"], $gmaps[$g-1]["gmaps_speed"], $gmaps[$g-1]["gmaps_lock"], $gmaps[$g-1]["gmaps_signal"], $gmaps[$g-1]["gmaps_temp"], $gmaps[$g-1]["gmaps_ovrtemp"], $gmaps[$g-1]["gmaps_iButton"], $gmaps[$g-1]["gmaps_digital_input2"],$gmaps[$g-1]["gmaps_digital_input3"],$duration,$geoc);
}
}
$geoc = ($gmaps[$g]["gmaps_geoc"] != 0 amp;amp; isset($pois[$gmaps[$g]["gmaps_geoc"]])) ? $pois[$gmaps[$g]["gmaps_geoc"]] : '';
addPoint('move', $gmaps[$g]["gmaps_lat"], $gmaps[$g]["gmaps_lon"], $gmaps[$g]["gmaps_time"], $gmaps[$g]["gmaps_now"], $gmaps[$g]["gmaps_speed"], $gmaps[$g]["gmaps_lock"], $gmaps[$g]["gmaps_signal"], $gmaps[$g]["gmaps_temp"], $gmaps[$g]["gmaps_ovrtemp"], $gmaps[$g]["gmaps_iButton"], $gmaps[$g]["gmaps_digital_input2"],$gmaps[$g]["gmaps_digital_input3"],'',$geoc);
}
else if($g == $sizeofgmaps - 2){
if($stop_time != 0){
$stop_time = abs(strtotime($gmaps[$g]["gmaps_time"]) - strtotime($gmaps[$g-1]["gmaps_time"]));
$total_stop_time = $stop_time;
$duration = getDurationCurr($stop_time);
$stop_time = 0;
$geoc = ($gmaps[$g]["gmaps_geoc"] != 0 amp;amp; isset($pois[$gmaps[$g]["gmaps_geoc"]])) ? $pois[$gmaps[$g]["gmaps_geoc"]] : BING_REV_GEOC($gmaps[$g]['gmaps_lat'], $gmaps[$g]['gmaps_lon']);
addPoint('stop', $gmaps[$g-1]["gmaps_lat"], $gmaps[$g-1]["gmaps_lon"], $gmaps[$g-1]["gmaps_time"], $gmaps[$g-1]["gmaps_now"], $gmaps[$g-1]["gmaps_speed"], $gmaps[$g-1]["gmaps_lock"], $gmaps[$g-1]["gmaps_signal"], $gmaps[$g-1]["gmaps_temp"], $gmaps[$g-1]["gmaps_ovrtemp"], $gmaps[$g-1]["gmaps_iButton"], $gmaps[$g-1]["gmaps_digital_input2"],$gmaps[$g-1]["gmaps_digital_input3"],$duration,$geoc);
}
else if($pause_time != 0){
$pause_time = abs(strtotime($gmaps[$g]["gmaps_time"]) - strtotime($gmaps[$g-1]["gmaps_time"]));
$total_pause_time = $pause_time;
$duration = getDurationCurr($pause_time);
$pause_time = 0;
$geoc = ($gmaps[$g]["gmaps_geoc"] != 0 amp;amp; isset($pois[$gmaps[$g]["gmaps_geoc"]])) ? $pois[$gmaps[$g]["gmaps_geoc"]] : BING_REV_GEOC($gmaps[$g]['gmaps_lat'], $gmaps[$g]['gmaps_lon']);
addPoint('pause', $gmaps[$g-1]["gmaps_lat"], $gmaps[$g-1]["gmaps_lon"], $gmaps[$g-1]["gmaps_time"], $gmaps[$g-1]["gmaps_now"], $gmaps[$g-1]["gmaps_speed"], $gmaps[$g-1]["gmaps_lock"], $gmaps[$g-1]["gmaps_signal"], $gmaps[$g-1]["gmaps_temp"], $gmaps[$g-1]["gmaps_ovrtemp"], $gmaps[$g-1]["gmaps_iButton"], $gmaps[$g-1]["gmaps_digital_input2"],$gmaps[$g-1]["gmaps_digital_input3"],$duration,$geoc);
}
}
$engineSTATUS = $gmaps[$g]["gmaps_engine"];
}
$geoc = ($gmaps[$g]["gmaps_geoc"] != 0 amp;amp; isset($pois[$gmaps[$g]["gmaps_geoc"]])) ? $pois[$gmaps[$g]["gmaps_geoc"]] : BING_REV_GEOC($gmaps[$g]['gmaps_lat'], $gmaps[$g]['gmaps_lon']);
addPoint('start', $gmaps[$g]["gmaps_lat"], $gmaps[$g]["gmaps_lon"], $gmaps[$g]["gmaps_time"], $gmaps[$g]["gmaps_now"], $gmaps[$g]["gmaps_speed"], $gmaps[$g]["gmaps_lock"], $gmaps[$g]["gmaps_signal"], $gmaps[$g]["gmaps_temp"], $gmaps[$g]["gmaps_ovrtemp"], $gmaps[$g]["gmaps_iButton"], $gmaps[$g]["gmaps_digital_input2"],$gmaps[$g]["gmaps_digital_input3"],'',$geoc);
}
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$ptr, '');
$objPHPExcel->getActiveSheet()->getStyle('A'.$ptr)->getFont()->setBold(true);
$myName = "iTrack-Report_[".$nickname."]_(".$from.")-(".$to.").".date('d-m-Y').".xls";
error_log("excel is over" ,0);
header('Content-Type: application/vnd.ms-excel');
error_log("header 1",0);
header('Content-Disposition: attachment;filename='.$myName);
error_log("header 2",0);
header('Cache-Control: max-age=0');
error_log("header 3",0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
error_log("objWriter 1",0);
SaveViaTempFile($objWriter);
error_log("objWriter 2",0);
}
function SaveViaTempFile($objWriter){
$filePath = '/tmp/' . rand(0, getrandmax()) . rand(0, getrandmax()) . ".tmp";
$objWriter->save($filePath);
readfile($filePath);
unlink($filePath);
}
function addPoint($mode, $lat, $lon, $gtime, $gnow, $speed, $lock, $signal, $temp, $ovrtemp, $iButton, $dig2, $dig3, $duration, $geoc){
global $map_url,$ptr,$objPHPExcel,$styleThickBrownBorderOutline,$lng_txt,$din2_name,$din3_name,$styleThinBorder,$dig_enable,$digital_type;
if($mode == 'finish') $mode_txt = $lng_txt[514];
else if($mode == 'start') $mode_txt = $lng_txt[515];
else if($mode == 'stop') $mode_txt = $lng_txt[395];
else if($mode == 'move' amp;amp; $speed == 0) $mode_txt = $lng_txt[383];
else if($mode == 'move') $mode_txt = $lng_txt[380];
else if($mode == 'pause') $mode_txt = $lng_txt[393];
else $mode_txt = '';
$temp = round($temp,1);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$ptr , date("d/m/Y H:i", strtotime($gtime)))->setCellValue('B'.$ptr , $mode_txt)->setCellValue('C'.$ptr , $duration)->setCellValue('D'.$ptr , round($speed,0))->setCellValue('E'.$ptr , $temp)->setCellValue('F'.$ptr , $iButton)->setCellValue('G'.$ptr , $dig2)->setCellValue('H'.$ptr , $dig3)->setCellValue('I'.$ptr , $geoc);
if(($gnow != '') amp;amp; abs(strtotime($gnow) - strtotime($gtime)) > 300){
$objPHPExcel->getActiveSheet()->getStyle('A'.$ptr)->getFont()->setItalic(true);
}
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$ptr , round($lat,5)."/".round($lon,5)); // Γ.Πλάτος/Γ.Μήκος
$url = (isset($map_url) amp;amp; $map_url == 'bingv7')
? 'http://www.bing.com/maps/default.aspx?cp='.$lat.'~'.$lon.'amp;style=oamp;lvl=1amp;dir=0amp;scene=1140291'
: 'https://www.google.com/maps?f=qamp;q='.$lat.','.$lon ;
$objPHPExcel->getActiveSheet()->getCell('J'.$ptr)->getHyperlink()->setUrl($url);
$ptr ;
}
?>
Моя проблема в том, что команда $objWriter-> save($filePath); никогда не заканчивается. Кажется, что по причине, которую я не могу понять, он стекается сюда, и в результате процедура не может продолжаться. Кто-нибудь имеет представление о том, что происходит?
— дополнительная информация — Версия PHP 5.5.9-1ubuntu4.16 — Версия Apache Apache/2.4.7 (Ubuntu)
Комментарии:
1. сколько строк у вас есть? у меня были проблемы с Excel и большими таблицами, потому что он писал документ по ячейкам. мы заменили его на TBS (крошечный, но мощный), который решил проблему как по волшебству.
2. около 2000 строк. Оказывает ли это какое-либо влияние?
3. не уверен … для теста вы могли бы попробовать ограничить набор результатов 10 и проверить, помогает ли это.