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();
 }
Example #2
0
 static function export($file_export, $content_theme)
 {
     if (!self::$xls_exporter) {
         require_once ROOT_PATH . 'includes/html_to_xls.inc.php';
         self::$xls_exporter = new HTML_TO_XLS();
         $excel_css = file_get_contents(ROOT_PATH . 'style/excel.css');
         self::$xls_exporter->setCSS(str_replace("\r\n", '', $excel_css));
     }
     self::$xls_exporter->createXls($content_theme, $file_export, true);
 }
Example #3
0
 public function leaderexportAction()
 {
     $result = $this->roleInfo('L');
     $excelExport = new ExcelExport();
     $excelExport->LeaderExport($result);
 }
 function exportXLS($data)
 {
     require_once 'ExcelExport.php';
     $this->exportFileName = $this->getExportFileName();
     $cols = $this->getDisplayColumnInfo();
     $xls = new ExcelExport();
     $colHeader = array();
     foreach ($cols as $idx => $col) {
         array_push($colHeader, $col['header']);
     }
     $xls->addRow($colHeader);
     foreach ($data as $idx => $record) {
         $record = $this->object2array($record);
         $recordA = array();
         foreach ($cols as $idx => $col) {
             $f = $record[$col['fieldIndex']];
             array_push($recordA, empty($f) ? '' : $f);
         }
         $xls->addRow($recordA);
     }
     $xls->download($this->exportFileName . '.xls');
 }
 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));
 }
Example #6
0
<?php

# Export all transactions from database into an excel file
# Open database connection
include 'database.php';
# Excel export class
require_once "xls.php";
$xls = new ExcelExport();
# Create headings
$xls->addRow(array("Name", "Phone", "Email", "Address", "City", "State", "Zip", "Country", "School Name", "District", "Title I", "Percent Free Lunch", "Percent Eng Learners", "Percent Non White", "Years Taught", "Grades Taught", "Quantity", "Request Date", "Start Date", "End Date", "Approved", "Shipped"));
# Read from transactions table
$result = mysql_query("SELECT * FROM rentals_tbl Order By requestdate DESC");
while ($row = mysql_fetch_array($result)) {
    # Populate spreadsheet cells with database row data
    $xls->addRow(array($row['name'], $row['phone'], $row['email'], $row['address'], $row['city'], $row['state'], $row['zip'], $row['country'], $row['schoolname'], $row['district'], $row['titlei'], $row['freelunch'], $row['englearners'], $row['nonwhite'], $row['yearstaught'], $row['gradestaught'], $row['quantity'], $row['requestdate'], $row['startdate'], $row['enddate'], $row['rental_approved'], $row['shipstatus']));
}
# Create excel file for download
$xls->download("qcn-BorrowedSensors.xls");
# Close database connection
mysql_close($link);
Example #7
0
<?php

# Export all transactions from database into an excel file
# Open database connection
include 'database.php';
# Excel export class
require_once "xls.php";
$xls = new ExcelExport();
# Create headings
$xls->addRow(array("Name", "Phone", "Email", "Address", "City", "State", "Zip", "Country", "Quantity. @ \$5.00", "Quantity @ \$49.00", "Date of Purchase", "Transaction Id", "Amount Billed", "Tax (Percent)", "Tax Amount", "Grand Total", "\$5 Sensor Approved", "Ship Status", "Email Confirmation Sent"));
# Read from transactions table
$result = mysql_query("SELECT * FROM transactions_tbl Order By purchasedate DESC");
while ($row = mysql_fetch_array($result)) {
    # Calculate what was charged to the buyer
    if ($row['qtySensor5'] != 0 && $row['qtySensor49'] != 0) {
        $billedAmount = $row['qtySensor5'] * 5.0 + $row['qtySensor49'] * 49.0 . '.00';
    }
    if ($row['qtySensor5'] != 0 && $row['qtySensor49'] == 0) {
        $billedAmount = $row['qtySensor5'] * 5.0 . '.00';
    }
    if ($row['qtySensor5'] == 0 && $row['qtySensor49'] != 0) {
        $billedAmount = $row['qtySensor49'] * 49.0 . '.00';
    }
    # Set tax
    $taxregion = $row['region_id'];
    if ($taxregion != '4') {
        $taxresult = mysql_query("SELECT * FROM taxregions_tbl WHERE id = '{$taxregion}'");
        $taxrow = mysql_fetch_array($taxresult);
        $tax = $taxrow['tax'];
        if (strtotime($row['purchasedate']) < strtotime('07/01/2011')) {
            $tax += 1;
 public function exportroleAction($type)
 {
     $manager = $this->session->get('Manager');
     if (empty($manager)) {
         $this->dataReturn(array('error' => '用户信息失效,请重新登录!'));
         return;
     }
     try {
         $excelExport = new ExcelExport();
         $file_name = '';
         switch ($type) {
             case 1:
                 $result = Examinee::find(array('project_id = ?1 AND type = 0 ', 'bind' => array(1 => $manager->project_id)));
                 $file_name = $excelExport->ExamineeExport($result, $manager->project_id);
                 $this->dataReturn(array('success' => $file_name));
                 return;
                 break;
             case 2:
                 $result = Manager::find(array('project_id = ?1 AND role = \'I\'', 'bind' => array(1 => $manager->project_id)));
                 $file_name = $excelExport->InterviewerExport($result, $manager->project_id);
                 $this->dataReturn(array('success' => $file_name));
                 return;
                 break;
             case 3:
                 $result = Manager::find(array('project_id = ?1 AND role = \'L\'', 'bind' => array(1 => $manager->project_id)));
                 $file_name = $excelExport->LeaderExport($result, $manager->project_id);
                 $this->dataReturn(array('success' => $file_name));
                 return;
                 break;
             default:
                 $this->dataReturn(array('error' => '参数错误-' . $type));
                 return;
         }
     } catch (Exception $e) {
         $this->dataReturn(array('error' => '列表生成失败'));
         return;
     }
 }
Example #9
0
/**
 * Created by PhpStorm.
 * User: huzhiming
 * Date: 2015/10/9
 * Time: 15:44
 */
require_once "../model/ExcelExport.php";
require_once "../tools/SQLTool.class.php";
$list = $_GET['list'];
if (isset($_GET['title'])) {
    $title = $_GET['title'];
} else {
    $title = "用户请求列表";
}
$sqltool = new SQLTool();
$excel = new ExcelExport();
$cell = array("请求编号", "请求标题", "请求分类", "请求方式", "请求时间", "请求描述", "优先级", "影响范围", "复杂程度", "服务协议", "请求人姓名", "请求人帐号", "请求人单位", "请求人职工号", "请求人职务", "请求人电话", "请求人手机", "请求人邮箱", "工程师", "请求状态", "解决方案");
$sql = "select t_req.req_num, t_req.req_title, t_req.req_sort,t_req.req_source, t_req.req_time, t_req.req_content, req_priority,req_effect,\r\n        t_req_assign.req_complex,req_time_limit,t_user.name,t_req.req_author,(select depart_name from t_depart where t_depart.depart_id=t_user.depart_id),t_user.staff_id,t_user.position,t_user.phone,\r\n        t_user.telephone,t_user.email,t_req_assign.req_engineer,t_req.req_state,t_req.req_solution\r\n        from t_req left outer join t_req_assign on t_req.req_num=t_req_assign.req_num and (finish_flag=1 or finish_flag=2),t_user\r\n\t\twhere t_req.req_author = t_user.user_name  and t_req.req_num in {$list} order by req_time desc";
$res = $sqltool->dbQuery($sql);
for ($i = 0; $i < count($res); $i++) {
    switch ($res[$i][3]) {
        case 1:
            $res[$i][3] = "网页请求";
            break;
        case 2:
            $res[$i][3] = "电话请求";
            break;
        case 3:
            $res[$i][3] = "邮件请求";
            break;
    }
 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));
 }
Example #11
0
<?php

/**
 * Created by PhpStorm.
 * User: huzhiming
 * Date: 2015/10/8
 * Time: 22:14
 */
require_once "ExcelExport.php";
$excel = new ExcelExport();
$cell = array("姓名", "电话", "手机号");
$value = array(array("张三", "122334<a></a>", NULL), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"), array("李四", "23908u0", "3345354343345"));
$excel->setCellTitle($cell, "学生通信录");
$excel->setCellValue($value);
$excel->export();