public function actionFullupload()
{
/*
$price_update = Prices::model()->findByAttributes(['trade_date'=>'0000-00-00', 'instrument_id' =>43],
[
'condition'=>'price!=:price',
'params'=>array('price'=>17.420),
]
);
var_dump($price_update);
exit;
*/
$model = new Uploads();
//$path = Yii::app()->basePath.'../../uploads/';
$path = Yii::getPathOfAlias('webroot') . '/uploads/';
if (isset($_POST['Uploads'])) {
Yii::import('ext.phpexcel.XPHPExcel');
XPHPExcel::init();
ini_set('max_execution_time', 150000);
ini_set("memory_limit", "128M");
require_once Yii::app()->basePath . '/extensions/XLSXReader/XLSXReader.php';
//OKarray(2) { ["Uploads"]=> array(2) { ["instrument_id"]=> string(2) "12" ["upload_description"]=> string(5) "sfggs" } ["yt0"]=> string(6) "Upload" }
$model->attributes = $_POST['Uploads'];
if ($upload_file = self::uploadMultifile($model, 'upload_file', $path)) {
$model->upload_file = implode(",", $upload_file);
}
$model->user_id = Yii::app()->user->id;
//$instrument_id = $model->instrument_id;
//////////////////////////////////////////
if ($model->validate()) {
//Upload File //
if ($model->save()) {
$upload_file_id = Yii::app()->db->getLastInsertID();
$csvFile = CUploadedFile::getInstance($model, 'upload_file', '../../uploads/');
$tempLoc = Yii::getPathOfAlias('webroot') . '/uploads/' . $model->upload_file;
$xlsx = new XLSXReader($tempLoc);
$data = $xlsx->getSheetData('Sheet1');
$instruments = Instruments::model()->findAll(array('select' => 'id, instrument'));
$instruments_for_returns_update = [];
foreach ($data as $dat) {
$trade_date = gmdate('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($dat['0']));
$instrument_name = trim($dat['1']);
$price = $dat['2'];
$currency = $dat['3'];
$instrument = Instruments::model()->findByAttributes(['instrument' => $instrument_name, 'is_current' => 1]);
if ($instrument) {
$instrument_id = $instrument->id;
$instruments_for_returns_update[] = $instrument_id;
} else {
$new_instrument = new Instruments();
$new_instrument->instrument = $instrument_name;
$new_instrument->price_uploaded = 1;
$new_instrument->currency = $currency;
$new_instrument->save();
$instrument_id = $new_instrument->id;
$instruments_for_returns_update[] = $instrument_id;
}
$existing_record = Prices::model()->findByAttributes(['trade_date' => $trade_date, 'instrument_id' => $instrument_id]);
if ($existing_record) {
if ($existing_record->price !== $price) {
$existing_record->price = $price;
$existing_record->upload_file_id = $upload_file_id;
$existing_record->save();
}
} else {
$new_price = new Prices();
$new_price->instrument_id = $instrument_id;
$new_price->trade_date = $trade_date;
$new_price->price = $price;
$new_price->upload_file_id = $upload_file_id;
//$new_price->name = $instrument_name;
$new_price->save();
}
}
$unique_instruments_for_returns_update = array_unique($instruments_for_returns_update);
Returns::model()->instrumnetReturnsUpdate($unique_instruments_for_returns_update);
Yii::app()->user->setFlash('success', "Prices Uploaded!");
@chmod($tempLoc, 0777);
@unlink($tempLoc);
//unlink(Yii::getPathOfAlias('webroot').'/uploads/'.$model->upload_file);
//$this->redirect(array('view','id'=>$model->id));
$user_data = Users::model()->findByPk(Yii::app()->user->id);
$step_completed = $user_data->step_completed;
if ($user_data->user_role == 2 && $step_completed < 2) {
$user_data->step_completed = 1;
$user_data->save();
$this->redirect(Yii::app()->baseUrl . '/site/admin');
}
//else{
// $this->redirect(Yii::app()->baseUrl.'/site/admin');
// $this->render('overview', ['user_data' => $user_data]); }
}
}
///////////////////////////////////////////
}
$this->render('upload_form', array('model' => $model));
}
private function exportEXCEL($excelname, $col, $title, $colAry)
{
// PHP EXCEL 初始化
XPHPExcel::init();
$fileTitle = "JIT Excel File";
$objPHPExcel = XPHPExcel::createPHPExcel();
$objPHPExcel->getProperties()->setCreator("JIT")->setLastModifiedBy("JIT")->setTitle($fileTitle)->setSubject("")->setDescription($fileTitle)->setKeywords("office 2007 openxml php")->setCategory("Excel File");
// 第一列 填入標題
$column = 0;
// 第幾欄. 由第0欄開始
for ($i = 0; $i < count($col); $i++) {
if (isset($title[$col[$i]])) {
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($column, 1, isset($title[$col[$i]]) ? $title[$col[$i]] : '', PHPExcel_Cell_DataType::TYPE_STRING);
$column++;
}
}
// 後續 填入內容
// 第幾列, 由第2列開始
$row = 2;
for ($j = 0; $j < count($colAry); $j++) {
if (isset($colAry[$j][$col[0]])) {
$column = 0;
// 第幾欄. 由第0欄開始
for ($i = 0; $i < count($col); $i++) {
// 若符合篩選欄位. 才進行
if (isset($title[$col[$i]])) {
/*$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $row, (isset($colAry[$j][$col[$i]]))?
$colAry[$j][$col[$i]]:'');*/
//避免(007002變7002) 0被吃掉，所以關鍵 setValueExplicit所有都轉字串
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setValueExplicit(isset($colAry[$j][$col[$i]]) ? $colAry[$j][$col[$i]] : '', PHPExcel_Cell_DataType::TYPE_STRING);
$column++;
}
}
$row++;
}
}
//CVarDumper::dump($colAry,10,true);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($excelname . '-正航匯入表');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a web browser (Excel5)
$webroot = Yii::getPathOfAlias('webroot');
//$fileName =$excelname.'-'.time().'.xls';
$fileName = 'PerformOut01' . '.xls';
$filePath = $webroot . '/' . "protected" . '/' . "tmp" . '/';
$fileUrl = $filePath . $fileName;
// If you're serving to IE over SSL, then the following may be needed
// header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
// header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
// header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
// header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// $fileName = Yii::app()->request->baseUrl."/protected/tmp/".$fileName;
// CVarDumper::dump($fileName);
// $objWriter->save($fileName);
$objWriter->save($fileUrl);
//
// Yii::app()->end();
// $objWriter->save(str_replace('.php', '.xls', __FILE__));
// $objWriter->save(str_replace(__FILE__,'/protected/tmp/'.$fileName,__FILE__));
// $objWriter->save('/protected/tmp/'.$fileName);
// @readfile('/protected/tmp/'.$fileName);
// spl_autoload_register(array('YiiBase','autoload'),true,true);
return $fileName;
}
public function actionExportResponseStatistics($id, $location)
{
$survey = $this->loadModel($id, $location);
$allowedForUsers = array_map(function ($x) {
return $x->userId;
}, $survey->surveyUsers);
if (!Yii::app()->user->checkAccess('survey.view') && !Yii::app()->user->checkAccess('survey.viewAndDownloadAllowed', array('user_ids' => $allowedForUsers))) {
throw new Http403Exception();
}
if ($survey->surveyInfo && $survey->surveyInfo->vicidialCampaign) {
$statistics = $survey->surveyInfo->vicidialCampaign->getCallsStatistics();
Yii::import('ext.PHPExcel.XPHPExcel');
XPHPExcel::init();
$phpExcel = XPHPExcel::createPHPExcel();
$phpExcel->getProperties()->setCreator("BCGroup");
$phpExcel->getProperties()->setLastModifiedBy("BCGroup");
$phpExcel->getProperties()->setTitle("Статистика отвечаемости по проекту {$survey->title}");
$phpExcel->getProperties()->setSubject("Статистика отвечаемости");
$phpExcel->setActiveSheetIndex(0);
$phpExcel->getActiveSheet()->SetCellValue('A1', 'Код статуса');
$phpExcel->getActiveSheet()->SetCellValue('B1', 'Название статуса');
$phpExcel->getActiveSheet()->SetCellValue('C1', 'Количество');
foreach ($statistics as $idx => $statisticsRow) {
$phpExcel->getActiveSheet()->SetCellValue('A' . ($idx + 2), $statisticsRow['status']);
$phpExcel->getActiveSheet()->SetCellValue('B' . ($idx + 2), $statisticsRow['status_name']);
$phpExcel->getActiveSheet()->SetCellValue('C' . ($idx + 2), $statisticsRow['count']);
}
$phpExcel->getActiveSheet()->setTitle('Статистика отвечаемости');
$phpExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$phpExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$phpExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$phpExcel->getActiveSheet()->getStyle("A1:C1")->getFont()->setBold(true);
$objWriter = new PHPExcel_Writer_Excel2007($phpExcel);
$filePath = $survey->getResponseStatisticsSrc();
$objWriter->save($filePath);
Yii::app()->request->sendFile(basename($filePath), file_get_contents($filePath));
} else {
throw new CHttpException(404, 'The requested page does not exist.');
}
Yii::app()->end();
}
echo "</div>";
break;
case 13:
echo "<table class='table'><tr><td>";
$i = 0;
$id = $element->id;
//echo "<a id=traceability style='color:black; background-color:rgb(200,200,200); border-radius:4px; ' onclick=toggle_visibility('info')> Click for Info </a></td>";
foreach ($element->columns as $column) {
echo '<th>' . $column->value . '</th>';
$i++;
}
echo "</tr>";
$r = 0;
echo "<h1>PHPExcel Reader</h1>";
Yii::import('ext.phpexcel.XPHPExcel');
XPHPExcel::init();
//$objPHPExcel = new PHPExcel();
//set_include_path(get_include_path() . PATH_SEPARATOR . 'c:\inetpub\wwwroot\vector2014\protected\vendors\\');
//include 'PHPExcel\IOFactory.php';
$inputFileName = 'protected/extensions/examples/reader/sampledata/example1.xls';
echo 'Loading file ', pathinfo($inputFileName, PATHINFO_BASENAME), ' using IOFactory to identify the format<br />';
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
echo '<br />';
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
var_dump($sheetData);
foreach ($element->rows as $row) {
echo '<tr><th>' . $row->value . '</th>';
for ($index = 0; $index < $i; $index++) {
$res = $element->getResultTable($issueId, $index, $r);
if ($res != null) {
echo "<td title='" . $element->getUserDateForTables2($issueId, $r, $index) . "'>\n\t\t\t\t\t\t\t\t<input style='margin:2px;' name='Table[{$id}][{$r}][{$index}]' type='text' value ='{$res}'><br>\n\t\t\t\t\t\t\t\t<div class='info' style='display: none; font-size:small; padding:3px; background-color:rgb(220,220,220); border-radius:4px'>" . $element->getUserDateForTables($issueId, $r, $index) . "</div></td>";
