public function actionExport() { $id = Yii::app()->request->getParam('id'); $page = Yii::app()->request->getParam('page', 1); $limit = 50000; $offset = ($page - 1) * $limit; $sql = "SELECT msisdn from tools_msisdn \n\t\t\t\tWHERE setting_id=:sid \n\t\t\t\tLIMIT :limit\n\t\t\t\tOFFSET :offset\n\t\t\t\t"; $command = Yii::app()->db->createCommand($sql); $command->bindParam(':sid', $id, PDO::PARAM_STR); $command->bindParam(':offset', $offset, PDO::PARAM_INT); $command->bindParam(':limit', $limit, PDO::PARAM_INT); $data = $command->queryAll(); $label = array('msisdn' => 'Thuê bao'); $title = Yii::t('admin', 'Export_' . $page); $excelObj = new ExcelExport($data, $label, $title); $excelObj->export(); }
public function actionIndex() { $pageSize = Yii::app()->request->getParam('pageSize', Yii::app()->params['pageSize']); Yii::app()->user->setState('pageSize', $pageSize); $model = new AdminVideoModel('search'); $copyrightType = Yii::app()->request->getParam('ccp_type', null); if (isset($_GET['AdminVideoModel'])) { if (isset($_GET['AdminVideoModel']['created_time']) && $_GET['AdminVideoModel']['created_time'] != "") { // Re-setAttribute create datetime $createdTime = $_GET['AdminVideoModel']['created_time']; if (strrpos($createdTime, "-")) { $createdTime = explode("-", $createdTime); $fromDate = explode("/", trim($createdTime[0])); $fromDate = $fromDate[2] . "-" . str_pad($fromDate[0], 2, '0', STR_PAD_LEFT) . "-" . str_pad($fromDate[1], 2, '0', STR_PAD_LEFT); $fromDate .= " 00:00:00"; $toDate = explode("/", trim($createdTime[1])); $toDate = $toDate[2] . "-" . str_pad($toDate[0], 2, '0', STR_PAD_LEFT) . "-" . str_pad($toDate[1], 2, '0', STR_PAD_LEFT); $toDate .= " 23:59:59"; } else { $fromDate = date("Y-m-d", strtotime($_GET['AdminVideoModel']['created_time'])) . " 00:00:00"; $toDate = date("Y-m-d", strtotime($_GET['AdminVideoModel']['created_time'])) . " 23:59:59"; } } } $is_composer = ""; if (isset($_GET['is_composer']) && $_GET['is_composer'] > 0) { $is_composer = $_GET['is_composer']; } $is_lyric = ""; if (isset($_GET['is_lyric']) && $_GET['is_lyric'] > 0) { $is_lyric = $_GET['is_lyric']; } $categoryList = AdminGenreModel::model()->gettreelist(2); $cpList = AdminCpModel::model()->findAll(); $numPage = 1; $count = 0; if (isset($_GET['AdminVideoModel'])) { $limit = 10; $offset = 0; $where = ""; if ($_GET['AdminVideoModel']['genre_id'] != '') { $genreId = (int) $_GET['AdminVideoModel']['genre_id']; $where .= " and (g.id = '{$genreId}' OR g.parent_id= '{$genreId}')"; } if ($_GET['AdminVideoModel']['name'] != '') { $where .= " and t.name LIKE '%" . $_GET['AdminVideoModel']['name'] . "%' "; } if ($_GET['AdminVideoModel']['artist_name'] != '') { $where .= " and a.name LIKE '%" . $_GET['AdminVideoModel']['artist_name'] . "%' "; } if ($_GET['is_composer'] == 1 || $_GET['is_composer'] == 2) { if ($_GET['is_composer'] == 1) { $where .= " and t.composer_id > 0 "; } else { $where .= " and t.composer_id = 0 "; } } if ($_GET['is_lyric'] == 1 || $_GET['is_lyric'] == 2) { if ($_GET['is_lyric'] == 1) { $where .= " and ve.description <> '' "; } else { $where .= " and (ve.description = '' OR ve.description is null)"; } } if ($_GET['AdminVideoModel']['max_bitrate'] != '') { $bitRate = $_GET['AdminVideoModel']['max_bitrate']; if ($bitRate == "720") { $where .= " and t.profile_ids LIKE '%9%'"; } } if ($_GET['AdminVideoModel']['created_time'] != '') { $where .= " and t.created_time BETWEEN '{$fromDate}' AND '{$toDate}' "; } $sql = "SELECT count(DISTINCT `t`.`id`)\n\t\t\t\t\tFROM video t \n\t\t\t\t\tINNER JOIN video_status st ON t.id = st.video_id\n\t\t\t\t\tLEFT JOIN video_artist va ON t.id =va.video_id\n\t\t\t\t\tLEFT JOIN artist a ON a.id=va.artist_id\n\t\t\t\t\tLEFT JOIN genre g ON g.id = t.genre_id\n\t\t\t\t\tLEFT JOIN video_extra ve ON t.id = ve.video_id\n\t\t\t\t\tWHERE true {$where}\n\t\t\t\t\t"; $count = Yii::app()->db->createCommand($sql)->queryScalar(); $perPage = 5000; if ($count <= $perPage) { $numPage = 1; } elseif ($count % $perPage == 0) { $numPage = $count / $perPage; } else { $numPage = $count / $perPage + 1; $numPage = (int) $numPage; } if (isset($_GET['export'])) { $page = Yii::app()->request->getParam('page', 1); $limit = $perPage; $offset = ($page - 1) * $limit; $sql = "SELECT t.id, t.name as video_name, a.name as composer_name, \n\t\t\t\tt.composer_id, t.genre_id, g.name, t.artist_name,ve.description as lyric,\n\t\t\t\tsc.copryright_id as copyright_id,c.appendix_no,c.contract_no \n\t\t\t\tFROM video t\n\t\t\t\tINNER JOIN video_status st ON t.id = st.video_id\n\t\t\t\tLEFT JOIN video_artist va ON va.video_id = t.id\n\t\t\t\tLEFT JOIN artist a ON a.id=t.composer_id\n\t\t\t\tLEFT JOIN genre g ON g.id=t.genre_id\n\t\t\t\tLEFT JOIN video_extra ve ON t.id = ve.video_id\n\t\t\t\tLEFT JOIN video_copyright sc ON sc.video_id=t.id\n\t\t\t\tLEFT JOIN copyright c ON c.id=sc.copryright_id \t\t\t\t\n\t\t\t\tWHERE true {$where}\n\t\t\t\tGROUP BY t.id\n\t\t\t\tORDER BY t.id ASC\n\t\t\t\tLIMIT {$limit}\n\t\t\t\tOFFSET {$offset}\n\t\t\t\t"; $data = Yii::app()->db->createCommand($sql)->queryAll(); $label = array('id' => 'ID', 'video_name' => 'Video', 'artist_name' => 'Ca sỹ', 'composer_name' => 'Nhạc sỹ', 'composer_id' => 'composer_id', 'name' => 'Thể loại', 'lyric' => 'Lyric', 'contract_no' => 'Số hợp đồng', 'cc' => 'Tên CP, Ca sĩ, đại diện HĐ', 'appendix_no' => 'Số phụ lục', 'copyright_id' => 'ID Hợp đồng'); $title = Yii::t('admin', 'Export_' . $page); $excelObj = new ExcelExport($data, $label, $title); $excelObj->export(); exit; } } $this->render('index', array('model' => $model, 'categoryList' => $categoryList, 'cpList' => $cpList, 'pageSize' => $pageSize, 'is_composer' => $is_composer, 'copyrightType' => $copyrightType, 'numPage' => $numPage, 'count' => $count, 'is_lyric' => $is_lyric)); }
$res[$i][9] = "E级 120小时内完成"; break; case 240: $res[$i][9] = "F级 240小时内完成"; break; case 480: $res[$i][9] = "G级 480小时内完成"; break; case 720: $res[$i][9] = "H级 720小时内完成"; break; } switch ($res[$i][19]) { case 1: $res[$i][19] = "未处理"; break; case 2: $res[$i][19] = "已指派"; break; case 3: $res[$i][19] = "已取消"; break; case 4: $res[$i][19] = "已解决"; break; } } $excel->setCellTitle($cell, $title); $excel->setCellValue($res); $excel->export();
public function actionIndex() { $pageSize = Yii::app()->request->getParam('pageSize', Yii::app()->params['pageSize']); Yii::app()->user->setState('pageSize', $pageSize); $model = new AdminSongModel('search'); $copyrightType = Yii::app()->request->getParam('ccp_type', null); if (isset($_GET['AdminSongModel'])) { if (isset($_GET['AdminSongModel']['created_time']) && $_GET['AdminSongModel']['created_time'] != "") { // Re-setAttribute create datetime $createdTime = $_GET['AdminSongModel']['created_time']; if (strrpos($createdTime, "-")) { $createdTime = explode("-", $createdTime); $fromDate = explode("/", trim($createdTime[0])); $fromDate = $fromDate[2] . "-" . str_pad($fromDate[0], 2, '0', STR_PAD_LEFT) . "-" . str_pad($fromDate[1], 2, '0', STR_PAD_LEFT); $fromDate .= " 00:00:00"; $toDate = explode("/", trim($createdTime[1])); $toDate = $toDate[2] . "-" . str_pad($toDate[0], 2, '0', STR_PAD_LEFT) . "-" . str_pad($toDate[1], 2, '0', STR_PAD_LEFT); $toDate .= " 23:59:59"; } else { $fromDate = date("Y-m-d", strtotime($_GET['AdminSongModel']['created_time'])) . " 00:00:00"; $toDate = date("Y-m-d", strtotime($_GET['AdminSongModel']['created_time'])) . " 23:59:59"; } } } $is_composer = ""; if (isset($_GET['is_composer']) && $_GET['is_composer'] > 0) { $is_composer = $_GET['is_composer']; } $categoryList = AdminGenreModel::model()->gettreelist(2); $cpList = AdminCpModel::model()->findAll(); if (isset($_GET['AdminSongModel'])) { $limit = 10; $offset = 0; $where = ""; if ($_GET['AdminSongModel']['cp_id'] != '') { $where .= " and t.cp_id = " . $_GET['AdminSongModel']['cp_id'] . " "; } if ($_GET['AdminSongModel']['genre_id'] != '') { $where .= " and sg.genre_id = " . $_GET['AdminSongModel']['genre_id'] . " "; } if ($_GET['AdminSongModel']['name'] != '') { $where .= " and t.name LIKE '%" . $_GET['AdminSongModel']['name'] . "%' "; } if ($_GET['AdminSongModel']['artist_name'] != '') { $where .= " and t.artist_name LIKE '%" . $_GET['AdminSongModel']['artist_name'] . "%' "; } if ($_GET['is_composer'] == 1 || $_GET['is_composer'] == 2) { if ($_GET['is_composer'] == 1) { $where .= " and t.composer_id > 0 "; } else { $where .= " and t.composer_id = 0 "; } } if ($_GET['AdminSongModel']['max_bitrate'] != '') { $where .= " and t.max_bitrate = " . $_GET['AdminSongModel']['max_bitrate']; } if ($_GET['ccp_type'] != '') { $where .= " and sc.type = " . $_GET['ccp_type']; } if ($_GET['AdminSongModel']['created_time'] != '') { $where .= " and t.created_time BETWEEN '{$fromDate}' AND '{$toDate}' "; } $sql = "SELECT count(DISTINCT `t`.`id`)\n\t\t\t\t\tFROM song t \n\t\t\t\t\tINNER JOIN song_status st ON t.id = st.song_id\n\t\t\t\t\tLEFT JOIN artist a ON a.id=t.composer_id\n\t\t\t\t\tLEFT JOIN song_genre sg ON sg.song_id=t.id\n\t\t\t\t\tLEFT JOIN song_copyright sc ON sc.song_id=t.id\n\t\t\t\t\tLEFT JOIN copyright c ON c.id=sc.copryright_id\n\t\t\t\t\tWHERE t.status=1 {$where}\n\t\t\t\t\t"; $count = Yii::app()->db->createCommand($sql)->queryScalar(); $perPage = 10000; if ($count <= $perPage) { $numPage = 1; } elseif ($count % $perPage == 0) { $numPage = $count / $perPage; } else { $numPage = $count / $perPage + 1; $numPage = (int) $numPage; } if (isset($_GET['export'])) { $page = Yii::app()->request->getParam('page', 1); $limit = $perPage; $offset = ($page - 1) * $limit; $sql = "SELECT t.id, t.name, t.artist_name, a.name as composer_name, t.composer_id, sg.genre_name, sc.copryright_id as copyright_id,c.appendix_no,c.contract_no, cp.name as cc, '' as ccc \n\t\t\t\tFROM song t\n\t\t\t\tINNER JOIN song_status st ON t.id = st.song_id\n\t\t\t\tLEFT JOIN cp ON t.cp_id = cp.id\n\t\t\t\tLEFT JOIN artist a ON a.id=t.composer_id\n\t\t\t\tLEFT JOIN song_genre sg ON sg.song_id=t.id\n\t\t\t\tLEFT JOIN song_copyright sc ON sc.song_id=t.id\n\t\t\t\tLEFT JOIN copyright c ON c.id=sc.copryright_id\n\t\t\t\tWHERE t.status=1 {$where}\n\t\t\t\tGROUP BY t.id\n\t\t\t\tORDER BY t.id ASC\n\t\t\t\tLIMIT {$limit}\n\t\t\t\tOFFSET {$offset}\n\t\t\t\t"; $data = Yii::app()->db->createCommand($sql)->queryAll(); $label = array('id' => 'ID', 'name' => 'Bài Hát', 'artist_name' => 'Ca sỹ', 'composer_name' => 'Nhạc sỹ', 'composer_id' => 'composer_id', 'contract_no' => 'Số hợp đồng', 'cc' => 'Tên CP, Ca sĩ, đại diện HĐ', 'appendix_no' => 'Số phụ lục', 'ccc' => 'STTPL', 'copyright_id' => 'ID Hợp đồng'); $title = Yii::t('admin', 'Export_' . $page); $excelObj = new ExcelExport($data, $label, $title); $excelObj->export(); exit; } } $this->render('index', array('model' => $model, 'categoryList' => $categoryList, 'cpList' => $cpList, 'pageSize' => $pageSize, 'is_composer' => $is_composer, 'copyrightType' => $copyrightType, 'numPage' => $numPage, 'count' => $count)); }