public function test()
{
$fields = array('id' => 'S/N', 'surname' => 'SURNAME', 'firstname' => 'FIRSTNAME', 'othername' => 'OTHERNAME', 'dateofbirth' => 'DATE OF BIRTH', 'gender' => 'SEX', 'phone' => 'PHONE NUMBER', 'occupation' => 'UNIT', 'picture' => FALSE, 'email' => 'EMAIL ADDRESS', 'name' => 'ID NUMBER', 'last_login' => 'LAST LOGIN');
$query = array();
$query = json_decode(file_get_contents(base_url('api/accounts')));
// $query = $this->aauth->list_users($group_par = FALSE, $limit = FALSE, $offset = FALSE, $include_banneds = FALSE);
echo arrayToExcel($query, $fields, "Users");
// $data['title'] = "Annual Report"; // it can be any variable with content that the code will use
// $fileName = date('YmdHis') . "_report";
// $pdfView = $this->load->view('pdf_template/pdf_template', $data, TRUE); // we need to use a view as PDF content
// $cssView = $this->load->view('pdf_template/pdf_template_css', NULL, TRUE); // the use a css stylesheet is optional
// exportPDF($fileName, $pdfView, $cssView, 'P'); // then define the content and filename
// $this->load->library('test');
// echo $this->test->index();
}
/**
* 对集合数据进行统计
* 目前支持的统计类型：
* 计数、唯一数、求和、均值、中位数、方差、标准差、最大值、最小值
*
* @author young
* @name 对集合数据进行统计
* @version 2014.01.29 young
*/
public function statisticAction()
{
$action = $this->params()->fromQuery('action', null);
$export = filter_var($this->params()->fromQuery('export', false));
$statistic_id = $this->params()->fromQuery('__STATISTIC_ID__', null);
if ($action !== 'statistic') {
return $this->msg(false, '$action is not statistic');
}
if (empty($statistic_id)) {
throw new \Exception('请选择统计方法');
}
$statisticInfo = $this->_statistic->findOne(array('_id' => myMongoId($statistic_id)));
if ($statisticInfo == null) {
throw new \Exception('统计方法不存在');
}
try {
$query = array();
$query = $this->searchCondition();
$rst = mapReduce($this->_data, $statisticInfo, $query);
if (is_array($rst) && isset($rst['ok']) && $rst['ok'] === 0) {
switch ($rst['code']) {
case 500:
return $this->deny('根据查询条件，未检测到有效的统计数据');
break;
case 501:
return $this->deny('MapReduce执行失败，原因：' . $rst['msg']);
break;
case 502:
return $this->deny('程序正在执行中，请勿频繁尝试');
break;
case 503:
return $this->deny('程序异常：' . $rst['msg']);
break;
}
}
if (!$rst instanceof \MongoCollection) {
return $this->deny('$rst不是MongoCollection的子类实例');
throw new \Exception('$rst不是MongoCollection的子类实例');
}
$outCollectionName = $rst->getName();
// 输出集合名称
if ($export) {
$datas = $rst->findAll(array());
$excel = array();
$excel['title'] = array('键', '值');
$excel['result'] = $datas;
arrayToExcel($excel);
} else {
$limit = intval($statisticInfo['maxShowNumber']) > 0 ? intval($statisticInfo['maxShowNumber']) : 100;
$datas = $rst->findAll(array(), array('value' => -1), 0, $limit);
return $this->rst($datas, 0, true);
}
} catch (\Exception $e) {
return $this->deny('程序异常：' . $e->getLine() . $e->getMessage());
}
}
function saveWizardResultsToExcel($wizRsltsVar)
{
// Include needed libraries for writeing Excel files
if (!function_exists('arrayToExcel')) {
include_once 'sites/all/libraries/PHPExcelHelper/phpexcel-helper-functions.php';
}
// $rows shall be an array of rows to be set in the spreadsheet, each element within $rows, shall be an array of cell values [to be set in the spreadsheet]
$rows = array();
foreach ($wizRsltsVar['sections'] as $sectionMachineName => $wizardResultsArray) {
foreach ($wizardResultsArray as $result) {
// Prepare to add a new row into the Excel sheet
$newRowToAdd = array();
// Determin the value for the cell under the "Category" column
$resultType = '?';
if (!empty($result['ctype'])) {
$resultType = $result['ctype'];
}
if ($resultType === '?') {
$resultType = $sectionMachineName;
}
if (!empty($wizRsltsVar['legend'][$resultType]['title'])) {
$resultType = $wizRsltsVar['legend'][$resultType]['title'];
// This is more of a human-readable/friendly name
}
$newRowToAdd['Category'] = $resultType;
// Determin the value for the cell under the "Title" column
$newRowToAdd['Title'] = $result['title'];
// Determin the value for the cell under the "Link" column
$linkToContent = false;
if (!empty($result['link'])) {
$linkToContent = $result['link'];
}
if (!empty($result['url'])) {
$linkToContent = $result['link'];
}
if (!empty($result['nid']) && $linkToContent === false) {
$linkToContent = drupal_get_path_alias('node/' . $result['nid']);
// determin the Drupal [alias] URL-path to this node
}
if (!empty($newRowToAdd['Link']) && $newRowToAdd['Link'] !== false) {
$newRowToAdd['Link'] = $linkToContent;
}
// Determine the value for the cell under the "Details" column
if (!empty($result['snippet'])) {
$newRowToAdd['Details'] = $result['snippet'];
}
// Add this row onto the $rows array
$rows[] = $newRowToAdd;
}
}
// Determine save location
$xlsDir = 'sites/default/files/wizard-excel-exports';
if (!is_dir($xlsDir)) {
if (!mkdir($xlsDir)) {
$msg = "Error - Could not create directory " . $xlsDir;
error_log($msg);
print $msg;
return false;
}
}
//Added for BUSUSA-3339 issue
$wiz_uri = $_SERVER['REQUEST_URI'];
$wiz_uri_arr = explode("/", $wiz_uri);
$strrest = str_replace(" ", "-", ucwords(str_replace("-", " ", $wiz_uri_arr[1])));
if ($strrest == 'Browseregulations') {
$strrest = "Browse-Regulations";
} else {
if ($strrest == 'Jobcenter-Wizard') {
$strrest = "Job-Center";
}
}
//End
$xlsPath = $xlsDir . '/' . $strrest . '-BusinessUSA-Wizard-Results-' . time() . '.xls';
// Transform the $rows array into a PHPExcel spreadsheet (PHPExcel lib)
$objPHPExcel = arrayToExcel(null, $rows, true);
// Note: arrayToExcel() is defined in phpexcel-helper-functions.php - PHPExcel arrayToExcel($objPHPExcel = null, $rows, $writeArrayKeysAsHeader = false, $rowStartWrite = 1, $setActiveSheetTo = 0, $sheetName = null)
myExcel_SetRowBold($objPHPExcel, 1, 4);
myExcel_SetRowAlignment($objPHPExcel, 1, 4);
// Write the Excel file to disk
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($xlsPath);
if (is_file($xlsPath)) {
return $xlsPath;
} else {
return '';
}
}
/**
* 导出数据
*/
public function exportAction()
{
try {
$cache = $this->cache();
$this->_worker->addFunction("dataExport", function (\GearmanJob $job) use($cache) {
$job->handle();
$workload = $job->workload();
$params = unserialize($workload);
$scope = $params['scope'];
$collection_id = $params['collection_id'];
$query = $params['query'];
$fields = $params['fields'];
$exportKey = md5($workload);
$exportGearmanKey = md5($scope->_collection_id . serialize($query));
// 获取映射关系，初始化数据集合model
$mapCollection = $this->_mapping->findOne(array('project_id' => $scope->_project_id, 'collection_id' => $scope->_collection_id, 'active' => true));
if ($mapCollection != null) {
$this->_data->setCollection($mapCollection['collection'], $mapCollection['database'], $mapCollection['cluster']);
} else {
$this->_data->setCollection(iCollectionName($collection_id));
}
$this->_data->setReadPreference(\MongoClient::RP_SECONDARY_PREFERRED);
$cursor = $this->_data->find($query, $fields);
$excelDatas = array();
// 保持拥有全部的字段名，不存在错乱的想象
$fieldNames = array_keys($fields);
while ($cursor->hasNext()) {
$row = $cursor->getNext();
$tmp = array();
foreach ($fieldNames as $key) {
$tmp[$key] = isset($row[$key]) ? $row[$key] : '';
}
$excelDatas[] = $tmp;
unset($tmp);
}
// 在导出数据的情况下，将关联数据显示为关联集合的显示字段数据
$rshData = array();
foreach ($scope->_rshCollection as $_id => $detail) {
$_id = $this->getCollectionIdByAlias($scope->_project_id, $_id);
$model = $this->collection()->secondary(iCollectionName($_id));
$cursor = $model->find(array(), array($detail['rshCollectionKeyField'] => true, $detail['rshCollectionValueField'] => true));
$datas = array();
while ($cursor->hasNext()) {
$row = $cursor->getNext();
$key = $row[$detail['rshCollectionValueField']];
$value = isset($row[$detail['rshCollectionKeyField']]) ? $row[$detail['rshCollectionKeyField']] : '';
if ($key instanceof \MongoId) {
$key = $key->__toString();
}
if (!empty($key)) {
$datas[$key] = $value;
}
}
$rshData[$detail['collectionField']] = $datas;
}
// 结束
convertToPureArray($excelDatas);
array_walk($excelDatas, function (&$value, $key) use($rshData, $fields) {
$loop = function ($value, $tmp) {
$new = $value;
$len = count($tmp);
for ($i = 0; $i < $len; $i++) {
if (isset($new[$tmp[$i]])) {
$new = $new[$tmp[$i]];
} else {
return '';
}
}
return $new;
};
foreach ($fields as $k => $v) {
if (strpos($k, '.') !== false) {
$tmp = explode('.', $k);
$value[$k] = $loop($value, $tmp);
}
}
ksort($value);
array_walk($value, function (&$cell, $field) use($rshData) {
if (isset($rshData[$field])) {
$cell = isset($rshData[$field][$cell]) ? $rshData[$field][$cell] : '';
}
});
});
$title = array();
ksort($fields);
foreach (array_keys($fields) as $field) {
$title[] = isset($scope->_title[$field]) ? $scope->_title[$field] : $field;
}
$excel = array('title' => $title, 'result' => $excelDatas);
$temp = tempnam(sys_get_temp_dir(), 'gearman_export_');
arrayToExcel($excel, $exportKey, $temp);
$cache->save(file_get_contents($temp), $exportKey, 60);
unlink($temp);
$cache->remove($exportGearmanKey);
$job->sendComplete('complete');
});
while ($this->_worker->work()) {
if ($this->_worker->returnCode() != GEARMAN_SUCCESS) {
echo "return_code: " . $this->_worker->returnCode() . "\n";
}
}
return $this->response;
} catch (\Exception $e) {
var_dump(exceptionMsg($e));
$job->sendException(exceptionMsg($e));
return false;
}
}
/**
* 读取集合内的全部数据
*
* @author young
* @name 读取集合内的全部数据
* @version 2013.12.23 young
*/
public function indexAction()
{
$rst = array();
$query = array();
$sort = array();
$action = $this->params()->fromQuery('action', null);
$search = $this->params()->fromQuery('search', null);
$sort = $this->params()->fromQuery('sort', null);
$start = intval($this->params()->fromQuery('start', 0));
$limit = intval($this->params()->fromQuery('limit', 10));
$start = $start > 0 ? $start : 0;
if ($action == 'search' || $action == 'excel') {
$query = $this->searchCondition();
}
if ($search != null) {
if (!isset($this->_schema['combobox']['rshCollectionKeyField'])) {
return $this->msg(false, '关系集合的值');
}
$search = preg_replace("/\\s/", '', $search);
$explode = explode(',', $search);
$query['$and'][] = array($this->_schema['combobox']['rshCollectionKeyField'] => myMongoRegex(end($explode)));
}
$jsonSearch = $this->jsonSearch();
if ($jsonSearch) {
$query['$and'][] = $jsonSearch;
}
$linkageSearch = $this->linkageSearch();
if ($linkageSearch) {
$query['$and'][] = $linkageSearch;
}
if (empty($sort)) {
$sort = $this->defaultOrder();
}
$cursor = $this->_data->find($query);
$total = $cursor->count();
if ($total > 0) {
$cursor->sort($sort);
if ($action !== 'excel') {
$cursor->skip($start)->limit($limit);
}
$datas = iterator_to_array($cursor, false);
//$datas = $this->comboboxSelectedValues($datas);
if ($action == 'excel') {
// 在导出数据的情况下，将关联数据显示为关联集合的显示字段数据
$this->dealRshData();
// 结束
convertToPureArray($datas);
array_walk($datas, function (&$value, $key) {
ksort($value);
array_walk($value, function (&$cell, $field) {
if (isset($this->_rshData[$field])) {
$cell = $this->_rshData[$field][$cell];
}
});
});
$excel = array('title' => array_values($this->_title), 'result' => $datas);
arrayToExcel($excel);
}
return $this->rst($datas, $total, true);
} else {
return $this->rst(array(), 0, true);
}
}
/**
* 对集合数据进行统计
* 目前支持的统计类型：
* 计数、唯一数、求和、均值、中位数、方差、标准差、最大值、最小值
*
* @author young
* @name 对集合数据进行统计
* @version 2014.01.29 young
*/
public function statisticAction()
{
$action = $this->params()->fromQuery('action', null);
$wait = $this->params()->fromQuery('wait', null);
$export = filter_var($this->params()->fromQuery('export', false));
$statistic_id = $this->params()->fromQuery('__STATISTIC_ID__', null);
if ($action !== 'statistic') {
return $this->msg(false, '$action is not statistic');
}
if (empty($statistic_id)) {
throw new \Exception('请选择统计方法');
}
$statisticInfo = $this->_statistic->findOne(array('_id' => myMongoId($statistic_id)));
if ($statisticInfo == null) {
throw new \Exception('统计方法不存在');
}
$map = array('_id' => $statisticInfo['xAxisField']);
try {
$query = array();
$query = $this->searchCondition();
// 增加默认统计条件开始
if (!empty($statisticInfo['defaultQuery'])) {
if (isset($query['$and'])) {
$query['$and'][] = $statisticInfo['defaultQuery'];
} else {
$query = array_merge($query, $statisticInfo['defaultQuery']);
}
}
// 增加默认统计条件结束
// 采用数据导出结果
if ($export) {
if ($this->cache($statistic_id) !== null) {
return $this->msg(true, '重新统计中');
} else {
$rst = $this->collection()->secondary($statistic_id, DB_MAPREDUCE, DEFAULT_CLUSTER);
$rst->setNoAppendQuery(true);
}
} else {
if ($this->cache($statistic_id) !== null) {
return $this->msg(true, '统计进行中……');
} elseif ($wait) {
$rst = $this->collection()->secondary($statistic_id, DB_MAPREDUCE, DEFAULT_CLUSTER);
if ($rst instanceof MongoCollection) {
$rst->setNoAppendQuery(true);
}
} else {
// 任务交给后台worker执行
$params = array('out' => $statistic_id, 'dataCollection' => $this->_collection_name, 'statisticInfo' => $statisticInfo, 'query' => $query, 'method' => 'replace');
$jobHandle = $this->_gmClient->doBackground('mapreduce', serialize($params), $statistic_id);
$stat = $this->_gmClient->jobStatus($jobHandle);
if (isset($stat[0]) && $stat[0]) {
$this->cache()->save(true, $statistic_id, 60);
}
return $this->msg(true, '统计请求被受理');
}
}
// $rst = mapReduce($statistic_id, $this->_data, $statisticInfo, $query);
if (is_array($rst) && isset($rst['ok']) && $rst['ok'] === 0) {
switch ($rst['code']) {
case 500:
return $this->deny('根据查询条件，未检测到有效的统计数据');
break;
case 501:
return $this->deny('MapReduce执行失败，原因：' . $rst['msg']);
break;
case 502:
return $this->deny('程序正在执行中，请勿频繁尝试');
break;
case 503:
return $this->deny('程序异常：' . $rst['msg']);
break;
}
}
if (!$rst instanceof \MongoCollection) {
return $this->deny('$rst不是MongoCollection的子类实例');
throw new \Exception('$rst不是MongoCollection的子类实例');
}
$outCollectionName = $rst->getName();
// 输出集合名称
if ($export) {
$sort = array('_id' => 1);
if ($statisticInfo['seriesType'] != 'line') {
$sort = array('value' => -1);
}
$datas = $rst->findAll(array(), $sort);
$datas = $this->replaceRshData($datas, $map);
$excel = array();
$excel['title'] = array('键', '值');
$excel['result'] = $datas;
arrayToExcel($excel);
} else {
if ($statisticInfo['seriesType'] != 'line') {
$limit = intval($statisticInfo['maxShowNumber']) > 0 ? intval($statisticInfo['maxShowNumber']) : 20;
$datas = $rst->findAll(array(), array('value' => -1), 0, $limit);
} else {
$limit = intval($statisticInfo['maxShowNumber']) > 0 ? intval($statisticInfo['maxShowNumber']) : 20;
$datas = $rst->findAll(array(), array('_id' => 1), 0, $limit);
}
$datas = $this->replaceRshData($datas, $map);
return $this->rst($datas, 0, true);
}
} catch (\Exception $e) {
return $this->deny('程序异常：' . $e->getLine() . $e->getMessage());
}
}
