/**
* TrackWarnExportAct::act_exportTrackInfo()
* 导出跟踪信息
* @param integer $carrierId 运输方式ID
* @param string $status 跟踪号状态
* @return json string
*/
public function act_exportTrackInfo()
{
$countryId = isset($_GET['countryId']) ? intval($_GET['countryId']) : 0;
$carrierId = isset($_GET['carrierId']) ? intval($_GET['carrierId']) : 0;
$channelId = isset($_GET['channelId']) ? intval($_GET['channelId']) : 0;
$type = isset($_GET['type']) ? trim($_GET['type']) : '';
$key = isset($_GET['key']) ? post_check(trim($_GET['key'])) : '';
$timeNode = isset($_GET['timeNode']) ? post_check(trim($_GET['timeNode'])) : '';
$warnLevel = isset($_GET['warnLevel']) ? intval($_GET['warnLevel']) : '';
$is_warn = isset($_GET['is_warn']) ? intval($_GET['is_warn']) : 1;
$status = isset($_GET['status']) ? intval($_GET['status']) : -1;
$condition = "1";
if (!empty($countryId)) {
$condition .= " AND a.countryId = '{$countryId}'";
}
if ($status >= 0) {
$condition .= " AND a.status = '{$status}'";
}
if (!empty($carrierId)) {
$condition .= " AND a.carrierId = '{$carrierId}'";
}
if (!empty($channelId)) {
$condition .= " AND a.channelId = '{$channelId}'";
}
if (!empty($timeNode)) {
if (!in_array($timeNode, array('scanTime', 'lastTime', 'trackTime'))) {
redirect_to("index.php?mod=trackWarnInfo&act=index");
}
$startTime = isset($_GET['startTime']) ? strtotime(trim($_GET['startTime']) . " 00:00:00") : strtotime(date("Y-m-d", time()) . " 00:00:00");
$endTime = isset($_GET['endTime']) ? strtotime(trim($_GET['endTime']) . " 23:59:59") : strtotime(date("Y-m-d", time()) . " 23:59:59");
if ($startTime && $endTime) {
$condition .= ' AND a.' . $timeNode . " BETWEEN '" . $startTime . "' AND " . "'" . $endTime . "'";
}
}
if ($type && $key) {
if (!in_array($type, array('orderSn', 'trackNumber', 'recordId'))) {
redirect_to("index.php?mod=trackWarnInfo&act=index");
}
$condition .= ' AND a.' . $type . " = '" . $key . "'";
}
if ($warnLevel === 0) {
//全部节点预警
$condition .= " AND a.warnLevel > 0";
} elseif ($warnLevel === -1) {
//没预警节点
$condition .= " AND a.warnLevel = 0";
} elseif (!empty($warnLevel)) {
//某个预警节点
$warnStr = str_pad($warnStr, $warnLevel - 1, "_", STR_PAD_LEFT);
switch ($is_warn) {
case 1:
$condition .= " AND a.warnLevel like '{$warnStr}1%'";
break;
case 2:
$condition .= " AND a.warnLevel like '{$warnStr}0%' AND a.nodeEff like '{$warnStr}1%'";
break;
case 3:
$condition .= " AND a.nodeEff like '{$warnStr}1%'";
break;
default:
$condition .= " AND a.warnLevel like '{$warnStr}1%'";
}
}
$act = isset($_REQUEST["act"]) ? post_check($_REQUEST["act"]) : "";
$mod = isset($_REQUEST["mod"]) ? post_check($_REQUEST["mod"]) : "";
if (!AuthUser::checkLogin($mod, $act)) {
self::$errCode = 10000;
self::$errMsg = "对不起,您无跟踪号数据导出权限！";
return "fail";
}
$res = TrackWarnExportModel::exportTrackNumberInfo($condition);
self::$errCode = TrackWarnExportModel::$errCode;
self::$errMsg = TrackWarnExportModel::$errMsg;
return $res;
}
/**
* 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";
}
}
