/**
* TrackWarnExportModel::getXlsZip()
* @param string $filePath 文件路径
* @param string $fileName 文件名
* 打包文件
* @return json string
*/
public static function getXlsZip($filePath, $fileName)
{
$zipfile = str_replace(".xls", ".zip", $filePath);
require_once WEB_PATH . 'lib/pclzip.lib.php';
$obj = new PclZip($zipfile);
$files = array($filePath);
$curtime = date('Y-m-d H:i:s', time());
//创建压缩文件
if ($obj->create($files, PCLZIP_OPT_REMOVE_PATH, WEB_PATH . 'html/temp/' . date('Ymd') . '/', PCLZIP_OPT_COMMENT, "Today's tran.valsun.cn trackNumber info packaged!\n\npackaged time:{$curtime}")) {
@unlink($filePath);
return 'temp/' . date('Ymd') . '/' . $fileName . '.zip';
} else {
self::$errCode = 10000;
self::$errMsg = "打包失败！请检查相关权限";
return false;
}
}
/**
* TrackWarnExportModel::exportTrackNumberInfo()
* 导出跟踪号信息
* @param string $condition 导出条件
* @param integer $pagesize 每页数量
* @return string
*/
public static function exportTrackNumberInfo($condition, $pagesize = 10000)
{
self::initDB();
set_time_limit(600);
ignore_user_abort(false);
$totalnum = 0;
$data = "";
$sql = "SELECT count(*) AS totalnum FROM\n\t\t\t\t\t\t" . self::$prefix . self::$table . " AS a\n\t\t\t\t\t\tLEFT JOIN " . self::$prefix . self::$tab_track_channel . " AS b ON a.channelId = b.id\n\t\t\t\t\t\tLEFT JOIN " . self::$prefix . self::$tab_track_carrier . " AS c ON a.carrierId = c.id\n\t\t\t\t\t\tWHERE {$condition}";
$query = self::$dbConn->query($sql);
if ($query) {
$res = self::$dbConn->fetch_array($query);
$totalnum = intval($res['totalnum']);
} else {
self::$errCode = 10000;
self::$errMsg = "获取数据失败";
return false;
}
$pages = ceil($totalnum / $pagesize);
$filename = 'track_number_info_' . $_SESSION[C('USER_AUTH_SYS_ID')];
$statusList = C('TRACK_STATUS_DETAIL');
$fileurl = WEB_URL . "temp/" . $filename . ".xls";
$filepath = WEB_PATH . "html/temp/" . $filename . ".xls";
for ($i = 0; $i < $pages; $i++) {
$offset = $i * $pagesize;
$idArr = array();
$ids = '';
$sql = "SELECT\n\t\t\t\t\t\ta.id\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t" . self::$prefix . self::$table . " AS a\n\t\t\t\t\t\tLEFT JOIN " . self::$prefix . self::$tab_track_channel . " AS b ON a.channelId = b.id\n\t\t\t\t\t\tLEFT JOIN " . self::$prefix . self::$tab_track_carrier . " AS c ON a.carrierId = c.id\n\t\t\t\t\t\tWHERE {$condition} LIMIT {$offset},{$pagesize}";
$query = self::$dbConn->query($sql);
$res = self::$dbConn->fetch_array_all($query);
foreach ($res as $v) {
array_push($idArr, $v['id']);
}
if (empty($idArr)) {
self::$errCode = 10001;
self::$errMsg = "获取数据失败";
return false;
} else {
$ids = implode(",", $idArr);
}
$sql = "SELECT\n\t\t\t\t\t\tc.carrierNameCn,\n\t\t\t\t\t\tb.channelName,\n\t\t\t\t\t\ta.orderSn,\n\t\t\t\t\t\ta.recordId,\n\t\t\t\t\t\ta.carrierId,\n\t\t\t\t\t\ta.channelId,\n\t\t\t\t\t\ta.platAccount,\n\t\t\t\t\t\ta.platForm,\n\t\t\t\t\t\ta.trackNumber,\n\t\t\t\t\t\ta.weight,\n\t\t\t\t\t\ta.cost,\n\t\t\t\t\t\ta.scanTime,\n\t\t\t\t\t\ta.toCountry,\n\t\t\t\t\t\ta.lastEvent,\n\t\t\t\t\t\ta.lastPostion,\n\t\t\t\t\t\ta.lastTime,\n\t\t\t\t\t\ta.status\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t" . self::$prefix . self::$table . " AS a\n\t\t\t\t\t\tLEFT JOIN " . self::$prefix . self::$tab_track_channel . " AS b ON a.channelId = b.id\n\t\t\t\t\t\tLEFT JOIN " . self::$prefix . self::$tab_track_carrier . " AS c ON a.carrierId = c.id\n\t\t\t\t\t\tWHERE a.id IN({$ids})\n\t\t\t\t\t\tORDER BY scanTime DESC";
$query = self::$dbConn->query($sql);
$res = self::$dbConn->fetch_array_all($query);
if ($i == 0) {
$data = '<?xml version="1.0" encoding="UTF-8"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"><Styles><Style ss:ID="sDT"><NumberFormat ss:Format="Short Date"/></Style></Styles><Worksheet ss:Name="Sheet1"><Table><Row><Cell><Data ss:Type="String">运输方式</Data></Cell><Cell><Data ss:Type="String">渠道</Data></Cell><Cell><Data ss:Type="String">跟踪号</Data></Cell><Cell><Data ss:Type="String">发货时间</Data></Cell><Cell><Data ss:Type="String">发往国家</Data></Cell><Cell><Data ss:Type="String">跟踪事件</Data></Cell><Cell><Data ss:Type="String">跟踪位置</Data></Cell><Cell><Data ss:Type="String">跟踪时间</Data></Cell><Cell><Data ss:Type="String">跟踪状态</Data></Cell><Cell><Data ss:Type="String">收寄日期</Data></Cell><Cell><Data ss:Type="String">收寄地点</Data></Cell><Cell><Data ss:Type="String">互封日期</Data></Cell><Cell><Data ss:Type="String">互封地点</Data></Cell><Cell><Data ss:Type="String">直封日期</Data></Cell><Cell><Data ss:Type="String">直封地点</Data></Cell></Row>' . "\n";
}
foreach ($res as $v) {
$nodeList = TransOpenApiModel::getTrackNodeList($v['carrierId'], $v['channelId']);
$detail = TransOpenApiModel::getTrackInfoLocal($v['trackNumber'], $v['carrierId']);
$j = 0;
$dates = array();
$pos = array();
foreach ($nodeList as $n) {
foreach ($detail as $val) {
$keys = explode(" ", $n['nodeKey']);
foreach ($keys as $key) {
if (strpos($val['event'], $key) !== false && !in_array($val['event'], array('未妥投'))) {
$dates[$j] = !empty($val['trackTime']) ? strftime("%Y-%m-%dT%H:%M:%S", $val['trackTime']) : '';
$pos[$j] = $val['postion'];
break;
}
}
}
$j++;
}
$data .= '<Row><Cell><Data ss:Type="String">' . $v['carrierNameCn'] . '</Data></Cell><Cell><Data ss:Type="String">' . $v['channelName'] . '</Data></Cell><Cell><Data ss:Type="String">' . $v['trackNumber'] . '</Data></Cell><Cell ss:StyleID="sDT"><Data ss:Type="DateTime">' . strftime("%Y-%m-%dT%H:%M:%S", $v['scanTime']) . '</Data></Cell><Cell><Data ss:Type="String">' . $v['toCountry'] . '</Data></Cell><Cell><Data ss:Type="String">' . $v['lastEvent'] . '</Data></Cell><Cell><Data ss:Type="String">' . $v['lastPostion'] . '</Data></Cell><Cell ss:StyleID="sDT"><Data ss:Type="DateTime">' . strftime("%Y-%m-%dT%H:%M:%S", $v['lastTime']) . '</Data></Cell><Cell><Data ss:Type="String">' . $statusList[$v['status']] . '</Data></Cell><Cell ss:StyleID="sDT"><Data ss:Type="DateTime">' . $dates[0] . '</Data></Cell><Cell><Data ss:Type="String">' . $pos[0] . '</Data></Cell><Cell ss:StyleID="sDT"><Data ss:Type="DateTime">' . $dates[1] . '</Data></Cell><Cell><Data ss:Type="String">' . $pos[1] . '</Data></Cell><Cell ss:StyleID="sDT"><Data ss:Type="DateTime">' . $dates[2] . '</Data></Cell><Cell><Data ss:Type="String">' . $pos[2] . '</Data></Cell></Row>' . "\n";
}
if ($i == 0) {
write_w_file($filepath, $data);
} else {
write_a_file($filepath, $data);
}
$data = "";
}
$data = "</Table></Worksheet></Workbook>";
write_a_file($filepath, $data);
if (file_exists($filepath)) {
return $fileurl;
} else {
return "fail";
}
}