function getRowCount($filename, $attributes)
{
App::import('Vendor', 'import.excelreader/excelreader');
if (is_file($filename)) {
if (isset($attributes['delimiter']) && (isset($attributes['excel_reader']) && $attributes['excel_reader'])) {
$xl = new ExcelReader();
$xl->read($filename);
$tmp_array = $xl->toArray();
return count($tmp_array);
} else {
$row_count = count(file($filename));
}
}
return false;
}
function getRowCount()
{
App::import('Vendor', 'import.excelreader/excelreader');
if (is_file($this->filename)) {
if (isset($this->options['delimiter']) && (isset($this->options['excel_reader']) && $this->options['excel_reader'])) {
$xl = new ExcelReader();
$xl->read($this->filename);
$tmp_array = $xl->toArray();
return count($tmp_array);
} else {
return count(file($this->filename));
}
}
return false;
}
<?php
require '../include/init.inc.php';
if (Common::isPost()) {
if (empty($_FILES['excel'])) {
OSAdmin::alert("error", "empty file");
} else {
if ($_FILES['excel']['error'] != 0) {
$message = 'ио╢╚нд╪Чй╖╟э,error number(' . $_FILES['excel']['error'] . ')';
OSAdmin::alert("error", $message);
}
$file = $_FILES['excel']['tmp_name'];
$excel_array = ExcelReader::readXLS($file);
$output = print_r($excel_array, true);
}
}
Template::assign("_POST", $_POST);
Template::assign("output", $output);
Template::display('sample/read_excel.tpl');
static function openExcel($file_name)
{
require_once ROOT_PATH . 'includes/excel_reader.php';
self::$xls_reader = new Spreadsheet_Excel_Reader($file_name);
// Đọc file excel, hỗ trợ Unicode UTF-8
}
public function actionNewimport()
{
//$path = _APP_PATH_ . DS . "data";
//$pathSource = 'E:\phuongnv\Vega\chacha_cloud\src\trunk\chacha\data';
$pathSource = _APP_PATH_ . DS . "data";
try {
$log = new KLogger('LOG_IMPORT_FILE_SONG_PNV', KLogger::INFO);
$log->LogInfo("Start New Import", false);
$model = new AdminImportSongModel();
$importer = self::IMPORT_SONG_CACHE . Yii::app()->user->id;
$result = array();
if (isset($_POST['AdminImportSongModel'])) {
$this->layout = false;
$autoconfirm = Yii::app()->request->getParam('autoconfirm');
$autoconfirm = isset($autoconfirm) ? 1 : 0;
$created_time = $_POST['AdminSongModel']['created_time'];
$updated_time = $_POST['AdminSongModel']['updated_time'];
$path = Yii::app()->params['importsong']['store_path'];
$file_path = $pathSource . DS . "tmp" . DS . $_POST['AdminImportSongModel']['source_path'];
$fileName = explode(DS, $file_path);
$fileName = $fileName[count($fileName) - 1];
if (file_exists($file_path)) {
$count = 0;
$start_row = $_POST['AdminImportSongModel']['start_row'] > 0 ? $_POST['AdminImportSongModel']['start_row'] : 0;
$start_row += self::START_ROW;
$limit_row = 65000;
$limit_row += $start_row;
$log->LogInfo("Start Read File and put Memcache | " . $file_path, false);
$data = new ExcelReader($file_path);
$resultSql = array();
//insert file
$sql = "INSERT INTO import_song_file(file_name,importer,status,created_time)\n \t\t\tVALUE('" . $fileName . "', '{$importer}',0,NOW())\n\t \t\t\t";
$insertFileRess = Yii::app()->db->createCommand($sql)->execute();
$fileImportId = Yii::app()->db->getLastInsertID();
for ($i = $start_row; $i < $limit_row; $i++) {
if ($data->val($i, 'B') != "" && $data->val($i, 'G') != "" && $data->val($i, 'C') != "") {
$stt = $data->val($i, Yii::app()->params['importsong']['excelcolumns']['stt']);
$name = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['name']));
$category = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['category']));
$sub_category = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['sub_category']));
$composer = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['composer']));
$artist = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['artist']));
$album = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['album']));
$path = str_replace('\\', DS, $data->val($i, Yii::app()->params['importsong']['excelcolumns']['path']));
$file = $data->val($i, Yii::app()->params['importsong']['excelcolumns']['file']);
$sql = "(";
$sql .= "'" . $autoconfirm . "'";
$sql .= ",'" . $created_time . "'";
$sql .= ",'" . $updated_time . "'";
$sql .= ",'" . $stt . "'";
$sql .= ",'" . addslashes($name) . "'";
$sql .= ",'" . $category . "'";
$sql .= ",'" . $sub_category . "'";
$sql .= ",'" . addslashes($composer) . "'";
$sql .= ",'" . addslashes($artist) . "'";
$sql .= ",'" . addslashes($album) . "'";
//$sql .= ",'".str_replace('\\', '\\\\', $path)."'";
$sql .= ",'" . addslashes($path) . "'";
//$sql .= ",'".str_replace('\\', '\\\\', $file)."'";
$sql .= ",'" . addslashes($file) . "'";
$sql .= ",'" . $importer . "'";
//$sql .= ",'".str_replace('\\', '\\\\', $file_path)."'";
$sql .= ",'" . addslashes($file_path) . "'";
$sql .= ",'" . $fileImportId . "'";
$sql .= ")";
$resultSql[] = $sql;
$count++;
}
/* if($count==10)
echo '<pre>';print_r($result);die(); */
}
//insert data to db
if ($insertFileRess) {
$sql = "INSERT INTO import_song(autoconfirm,created_time,updated_time,stt,name,category,sub_category,composer,artist,album,path,file,importer,file_name,file_id) VALUES";
$sql .= implode(',', $resultSql);
if (Yii::app()->db->createCommand($sql)->execute()) {
$sql = "UPDATE import_song_file set total_song={$count} WHERE id={$fileImportId}";
Yii::app()->db->createCommand($sql)->execute();
}
//insert false
}
//remove file source after insert
$fileSystem = new Filesystem();
$fileSystem->remove($file_path);
echo CJSON::encode(array('errorCode' => 0, 'errorDesc' => 'Success imported Total Record: ' . count($resultSql)));
} else {
//if ($_POST['AdminImportSongModel']['ajax'])
echo CJSON::encode(array('errorCode' => 1, 'errorDesc' => 'Chưa upload file excel'));
}
Yii::app()->end();
}
} catch (Exception $e) {
$log->LogError("actionAjaxImport | Exception Error: " . $e->getMessage(), false);
echo CJSON::encode(array('errorCode' => 1, 'errorDesc' => 'Chưa upload file excel'));
Yii::app()->end();
}
$uploadModel = new XUploadForm();
$this->render('newimport', array('model' => $model, 'listSong' => $result, 'uploadModel' => $uploadModel));
}
/**
* Creates a new model.
* If creation is successful, the browser will be redirected to the 'view' page.
*/
public function actionCreate()
{
$model = new ImportUserFileModel();
$content_type = Yii::app()->request->getParam('content_type', 'subscribe');
Yii::app()->session['content_type'] = $content_type;
if (Yii::app()->request->isPostRequest) {
$content_type = Yii::app()->session['content_type'];
$package_id = Yii::app()->request->getParam('package_id', null);
if (!isset($package_id)) {
$model->addError("name", "Chưa chọn gói cước!");
}
if (!$_FILES['file']['error']) {
$ext = Utils::getExtension($_FILES['file']['name']);
$type = $_FILES['file']['type'];
if ($ext == 'xls' && ($type == 'application/xls' || $type == 'application/vnd.ms-excel')) {
$storage = Yii::app()->params['storage']['baseStorage'] . "uploads" . DS . "user" . DS;
Utils::makeDir($storage);
$fileName = $_FILES['file']['name'];
if (move_uploaded_file($_FILES['file']['tmp_name'], $storage . DS . $fileName)) {
$transaction = Yii::app()->db->beginTransaction();
try {
$file_path = $storage . DS . $fileName;
$model->file_name = $fileName;
$model->created_by = $this->userId;
$model->file_path = $file_path;
$model->package_id = $package_id;
$model->created_time = new CDbExpression("NOW()");
$model->content_type = $content_type;
if ($model->save(false)) {
$data = new ExcelReader($file_path);
$start_row = 2;
$limit_row = $data->rowcount();
$cell_name = "A";
$arrayVal = array();
for ($i = $start_row; $i <= $limit_row; $i++) {
if ($data->val($i, $cell_name) == "") {
continue;
}
$name = $model->my_encoding($data->val($i, $cell_name));
$arrayVal[] = "('{$name}','{$model->id}','{$package_id}')";
}
/**
* Start insert here: split 200 line per command
*/
$arrs = array_chunk($arrayVal, 200);
foreach ($arrs as $arr) {
$vals = implode(",", $arr);
$sql = "INSERT INTO import_user_content (`msisdn`,`file_id`,`package_id`) VALUES {$vals}";
$command = Yii::app()->db->createCommand($sql);
$command->execute();
}
$transaction->commit();
$this->redirect(array('view', 'id' => $model->id, 'content_type' => $content_type));
} else {
$transaction->rollback();
}
} catch (Exception $e) {
$transaction->rollback();
$model->addError("exception", $e->getMessage());
}
} else {
$model->addError("name", "Không upload được file vào thư mục:" . $storage . DS . $fileName);
}
} else {
$model->addError("name", "Chỉ upload file xls");
}
} else {
$model->addError("name", "Chưa upload file");
// $model->content_type = $content_type;
}
}
$this->render('create', array('model' => $model, 'content_type' => $content_type));
}
/**
* 初期化
*
*/
public function __construct()
{
parent::__construct();
ini_set('memory_limit', '256M');
}
/**
* Creates a new model.
* If creation is successful, the browser will be redirected to the 'view' page.
*/
public function actionCreate()
{
$model = new CopyrightInputFileModel();
if (Yii::app()->request->isPostRequest) {
$content_type = Yii::app()->request->getParam('content_type', null);
if (!isset($content_type)) {
$model->addError("name", "Chưa chọn loại nội dung content_type!");
} else {
if (!$_FILES['file']['error']) {
$ext = Utils::getExtension($_FILES['file']['name']);
$type = $_FILES['file']['type'];
if ($ext == 'xls' && ($type == 'application/xls' || $type == 'application/vnd.ms-excel')) {
$storage = Yii::app()->params['storage']['baseStorage'] . "uploads" . DS . "copyright";
Utils::makeDir($storage);
$fileName = $_FILES['file']['name'];
if (move_uploaded_file($_FILES['file']['tmp_name'], $storage . DS . $fileName)) {
$transaction = Yii::app()->db->beginTransaction();
try {
$model->file_name = $fileName;
$model->content_type = $content_type;
$model->created_by = $this->userId;
$model->created_time = new CDbExpression("NOW()");
if ($model->save()) {
$file_path = $storage . DS . $fileName;
$data = new ExcelReader($file_path);
$start_row = 2;
$limit_row = $data->rowcount();
$cell_content_id = "A";
$cell_name = "B";
$cell_artist = "C";
$cell_copyright_code = "F";
$copyright_id = "J";
$arrayVal = array();
for ($i = $start_row; $i < $limit_row; $i++) {
if ($data->val($i, $cell_name) == "" || $data->val($i, $cell_artist) == "" || $data->val($i, $copyright_id) == "") {
continue;
}
$contentId = $model->my_encoding($data->val($i, $cell_content_id));
$name = $model->my_encoding($data->val($i, $cell_name));
$name = '"' . $name . '"';
$artist = $model->my_encoding($data->val($i, $cell_artist));
$ccode = $model->my_encoding($data->val($i, $cell_copyright_code));
$ccid = $model->my_encoding($data->val($i, $copyright_id));
$arrayVal[] = "('" . $contentId . " ', " . $name . " ,'" . $artist . " ','" . $ccode . " '," . $ccid . " ,'{$model->id}')";
}
/**
* Start insert here: split 200 line per command
*/
$arrs = array_chunk($arrayVal, 200);
foreach ($arrs as $arr) {
$vals = implode(",", $arr);
$sql = "INSERT INTO copyright_input_content (`content_id`,`name`,`artist`,`copyright_code`,`copyright_id`,`input_file`) VALUES {$vals}";
$command = Yii::app()->db->createCommand($sql);
$command->execute();
}
$transaction->commit();
$this->redirect(array('view', 'id' => $model->id));
} else {
$transaction->rollback();
}
} catch (Exception $e) {
$transaction->rollback();
$model->addError("exception", $e->getMessage());
}
} else {
$model->addError("name", "Không upload được file vào thư mục:" . $storage . DS . $fileName);
}
} else {
$model->addError("name", "Chỉ upload file xls");
}
} else {
$model->addError("name", "Chưa upload file");
$model->content_type = $content_type;
}
}
}
$this->render('create', array('model' => $model));
}
public static function save($param, $table, $month, $province_id, $record_id)
{
foreach ($param as $key => $value) {
$param[$key] = addslashes($value);
}
$param[] = $record_id;
$columns['base'] = self::$base;
$columns['custom'] = self::$custom;
$columns['complaints'] = self::$complaints;
$columns['income'] = self::$income;
$columns['value_income'] = self::$value_income;
$columns['black_list'] = self::$black_list;
$columns['complaint_province'] = self::$complaint_province;
$columns['complaint_class'] = self::$complaint_class;
// unset($param[0]);
// var_dump(count(explode(',', $columns[$table])),count($param));exit;
$bussLine = array('联通在信' => 1, '彩信' => 2);
$db = self::__instance();
if ($table == 'complaint_province') {
$param[0] = Info::getProvinceByName($param[0]);
$param[2] = strtotime($param[2] . '01');
if (strtotime($month . '-01') != $param[2]) {
return false;
}
}
if ($table == 'complaint_class') {
$param[2] = strtotime($param[2] . '01');
if (strtotime($month . '-01') != $param[2]) {
return false;
}
}
if ($table == 'base') {
$param[0] = Info::getProvinceByName($param[0]);
// $param[6] = ExcelReader::xlsTime($param[6]);
$param[25] = strtotime($param[25] . '01');
if (strtotime($month . '-01') != $param[25]) {
return false;
}
if ($province_id != $param[0]) {
return false;
}
$tmp = array($param[3], $param[0], $param[6], $param[25], $param[5], '', 1, '一年');
$num = $db->count('co_base', array('complaint_phone' => $param[3]));
if ($num > 0) {
$db->delete('co_black_list', array('complaint_phone' => $param[3]));
$tmp = array($param[3], $param[0], $param[6], $param[25], $param[5], '', 2, '五年');
}
$sql = 'insert into co_black_list (' . $columns['black_list'] . ') values ("' . implode('","', $tmp) . '")';
if ($param[3]) {
$r = $db->query($sql);
}
}
if ($table == 'custom') {
$param[29] = strtotime($param[29] . '01');
if (strtotime($month . '-01') != $param[29]) {
return false;
}
$param[22] = Info::getProvinceByName($param[22]);
// if($province_id != $param[22])
// return true;
$param[25] = ExcelReader::xlsTime($param[25]);
//$param[6] = ExcelReader::xlsTime($param[6]);
$param[6] = strtotime($param[6]);
$param[7] = $bussLine[$param[7]];
$tmp = array($param[16], $param[22], $param[6], $param[29], $param[2], '', 1, '一年');
$num = $db->count('co_custom', array('complaint_phone' => $param[16]));
if ($num > 0) {
$db->delete('co_black_list', array('complaint_phone' => $param[16]));
$tmp = array($param[16], $param[22], $param[6], $param[29], $param[2], '', 2, '五年');
}
$sql = 'insert into co_black_list (' . $columns['black_list'] . ') values ("' . implode('","', $tmp) . '")';
if ($param[16]) {
$db->query($sql);
}
// var_dump(Info::getProvinceByName($param[22]));exit;
}
if ($table == 'complaints') {
// $param[17] = ExcelReader::xlsTime($param[17]);
// $param[18] = ExcelReader::xlsTime($param[18]);
// $param[19] = ExcelReader::xlsTime($param[19]);
$param[7] = Info::getProvinceByName($param[7]);
// if($province_id != $param[7])
// return true;
$param[47] = strtotime($param[47] . '01');
if (strtotime($month . '-01') != $param[47]) {
return false;
}
$tmp = array($param[4], $param[7], $param[42], $param[47], $param[41], '', 3, '永久屏蔽');
$sql = 'insert into co_black_list (' . $columns['black_list'] . ') values ("' . implode('","', $tmp) . '")';
$db->query($sql);
}
if ($table == 'income') {
$param[0] = Info::getProvinceByName($param[0]);
// if($province_id != $param[0])
// return true;
$param[13] = strtotime($param[13] . '01');
if (strtotime($month . '-01') != $param[13]) {
return false;
}
}
if ($table == 'value_income') {
$param[0] = strtotime($param[0] . '01');
if (strtotime($month . '-01') != $param[0]) {
return false;
}
}
// var_dump($param);exit;
$sql = "insert into co_" . $table . " (" . $columns[$table] . ") values ('" . implode("','", $param) . "')";
// echo $sql.'<br>';exit;
$r = $db->query($sql);
// if(!$r)
// echo $sql;
return $r;
}
public function actionNewimport()
{
//$path = _APP_PATH_ . DS . "data";
//$pathSource = 'E:\phuongnv\Vega\chacha_cloud\src\trunk\chacha\data';
$pathSource = _APP_PATH_ . DS . "data";
$log = new KLogger('LOG_CHECK_SONG_IMPORT', KLogger::INFO);
$log->LogInfo("Start New Import", false);
try {
$model = new AdminImportSongModel();
$importer = self::IMPORT_SONG_CACHE . Yii::app()->user->id;
$result = array();
if (isset($_POST['AdminImportSongModel'])) {
$this->layout = false;
$file_path = $pathSource . DS . "tmp" . DS . $_POST['AdminImportSongModel']['source_path'];
$fileName = explode(DS, $file_path);
$fileName = $fileName[count($fileName) - 1];
if (file_exists($file_path)) {
$log->LogInfo("Start Read File and put Memcache | " . $file_path, false);
$data = new ExcelReader($file_path);
//insert file
$sql = "INSERT INTO import_song_file(file_name,importer,status,created_time,type)\n \t\t\tVALUE('" . $fileName . "', '{$importer}',0,NOW(),'CHECK')\n\t \t\t\t";
$log->LogInfo("SQL: " . $sql, false);
$insertFileRess = Yii::app()->db->createCommand($sql)->execute();
$fileImportId = Yii::app()->db->getLastInsertID();
$i = 1;
$err = 0;
$count = 0;
$total = 0;
$sqlAr = array();
$flag = true;
while ($flag) {
if ($data->val($i, 'A') != "" && $data->val($i, 'B') != "" && $data->val($i, 'C') != "") {
$stt = $data->val($i, 'A');
$songName = $model->my_encoding($data->val($i, 'B'));
$artistName = $model->my_encoding($data->val($i, 'C'));
$artistId = $this->getArtistId($artistName);
$artistId = implode(',', $artistId);
$sqlAr[] = "('{$stt}','" . addslashes($songName) . "', '" . addslashes($artistName) . "', {$fileImportId}, '{$importer}','{$artistId}')";
$count++;
$total++;
} else {
$err++;
}
$i++;
if ($err == 3) {
$flag = false;
}
if ($count == 200 || !$flag) {
$sql = "INSERT INTO import_song(stt,name,artist,file_id,importer,album) VALUES";
$sql .= implode(',', $sqlAr);
$log->LogInfo("SQL: " . $sql, false);
if (Yii::app()->db->createCommand($sql)->execute()) {
//reset
$count = 0;
$sqlAr = array();
}
}
}
$sql = "UPDATE import_song_file set total_song={$total} WHERE id={$fileImportId}";
Yii::app()->db->createCommand($sql)->execute();
//remove file source after insert
/* $fileSystem = new Filesystem();
$fileSystem->remove($file_path); */
echo CJSON::encode(array('errorCode' => 0, 'errorDesc' => 'Success imported Total Record: ' . $total . ' Go to <a href="' . Yii::app()->createUrl('/tools/importSong/index&fileId=' . $fileImportId) . '">Scan</a>'));
} else {
echo CJSON::encode(array('errorCode' => 1, 'errorDesc' => 'File không tồn tại:' . $file_path));
$log->LogInfo("File không tồn tại: " . $file_path, false);
}
Yii::app()->end();
}
} catch (Exception $e) {
echo CJSON::encode(array('errorCode' => 1, 'errorDesc' => 'Exception:' . $e->getMessage()));
$log->LogError("actionAjaxImport | Exception Error: " . $e->getMessage(), false);
Yii::app()->end();
}
$uploadModel = new XUploadForm();
$this->render('newimport', array('model' => $model, 'listSong' => $result, 'uploadModel' => $uploadModel));
}
