<?php
/**
* Created by PhpStorm.
* User: Administrator
* Date: 25/12/2015
* Time: 2:22 CH
*/
if (!defined('BASEPATH')) exit('No direct script access allowed');
include FCPATH . "/application/third_party/PHPExcel.php";
class CmsRanking extends CI_Controller
{
protected $overlap_ranking_list = 'backend/cmsSetting/overlapRanking';
public function __construct()
{
parent::__construct();
date_default_timezone_set("Asia/Ho_Chi_Minh");
//
$this->load->model('common_model');
$this->load->model('track_model');
$this->load->model('user_model');
$this->load->model('report_views_daily_model');
$this->load->model('report_views_weekly_model');
$this->load->model('report_views_monthly_model');
$this->load->model('report_violation_model');
$this->load->model('crawler_listen_history_model');
$this->load->model('actionlog_model');
}
/**
* BXH theo ngày ---------------------------------------------------------------------------------------------------
*/
public function listByDaily()
{
// Ghi log
$this->actionlog_model->add('RANKING', 'BXH Ngày.', 'Report_views_daily_model', 'report_views_daily', null);
$this->_data['functionName'] = 'BXH Theo ngày';
$this->_data['action'] = 'listByDaily';
$this->_data['titlePage'] = 'BXH Theo ngày';
$this->_data['loadPage'] = 'backend/cms_ranking/list_by_daily_view';
$this->load->view('backend/admin_template_view', $this->_data);
}
public function listByDailyAjax()
{
$data = array();
$fByName = $this->input->post('fByName');
$fByName = str_replace('/', '-', $fByName);
$pageId = $this->input->post('pageId');
//
$pageId = ($pageId == 0) ? 1 : $pageId;
//
$limit = 100;
$offset = ($pageId - 1) * $limit;
$data['offset'] = ($pageId - 1) * $limit;
$totalRecord = $this->report_views_daily_model->cmsCountAll($fByName);
$data['pagination'] = MyHelper::genPaginationLink($totalRecord, $limit, $pageId);
$data['listData'] = $this->report_views_daily_model->cmsGetPagination($limit, $offset, $fByName);
$data['previousRanking'] = $this->report_views_daily_model->previousRanking($fByName);
//
$this->load->view('backend/ajax/cms_ranking/list_by_daily_view', $data);
}
/**
* BXH theo tuần ---------------------------------------------------------------------------------------------------
*/
public function listByWeekly()
{
// Ghi log
$this->actionlog_model->add('RANKING', 'BXH Tuần.', 'Report_views_weekly_model', 'report_views_weekly', null);
$week_list_db = $this->report_views_weekly_model->selectSearch();
$week_list = [];
foreach ($week_list_db as $val) {
$array_w = MyHelper::getWeekDateRange($val['week'], $val['year'],'d/m');
if (isset($array_w['start_week']) && isset($array_w['end_week'])) {
$prefix = 'Tuần ' . $val['week'] . '/' . $val['year'];
$suffixe = '(' . $array_w['start_week'] . '-' . $array_w['end_week'] . ')';
$week_list[$val['week'] . '-' . $val['year']] = $prefix . ' ' . $suffixe;
}
}
$this->_data['week_list'] = $week_list;
$this->_data['functionName'] = 'BXH Theo tuần';
$this->_data['action'] = 'listByDaily';
$this->_data['titlePage'] = 'BXH Theo tuần';
$this->_data['loadPage'] = 'backend/cms_ranking/list_by_weekly_view';
$this->load->view('backend/admin_template_view', $this->_data);
}
public function listByWeeklyAjax()
{
$data = array();
$fByName = $this->input->post('fByName');
$pageId = $this->input->post('pageId');
//
$pageId = ($pageId == 0) ? 1 : $pageId;
//
$limit = 100;
$offset = ($pageId - 1) * $limit;
$data['offset'] = ($pageId - 1) * $limit;
$totalRecord = $this->report_views_weekly_model->cmsCountAll($fByName);
$data['pagination'] = MyHelper::genPaginationLink($totalRecord, $limit, $pageId);
$data['listData'] = $this->report_views_weekly_model->cmsGetPagination($limit, $offset, $fByName);
$data['previousRanking'] = $this->report_views_weekly_model->previousRanking($fByName);
//
$this->load->view('backend/ajax/cms_ranking/list_by_weekly_view', $data);
}
/**
* BXH theo tháng --------------------------------------------------------------------------------------------------
*/
public function listByMonthly()
{
// Ghi log
$this->actionlog_model->add('RANKING', 'BXH Tháng.', 'Report_views_monthly_model', 'report_views_monthly', null);
$month_list_db = $this->report_views_monthly_model->selectSearch();
$month_list = [];
foreach ($month_list_db as $key => $val) {
$prefix = 'Tháng ' . $val['month'] . '/' . $val['year'];
$suffixe = '(21/' . ($val['month'] - 1) . '-' . '20/'.$val['month']. ')';
if ($val['month'] == 4 && $val['year'] == 2020) {
$suffixe = '(30/' . ($val['month'] - 1) . '-' . '20/'.$val['month']. ')';
}
$month_list[$val['month'] . '-' . $val['year']] = $prefix . ' ' . $suffixe;
}
$this->_data['month_list'] = $month_list;
$this->_data['functionName'] = 'BXH Theo tháng';
$this->_data['action'] = 'listByDaily';
$this->_data['titlePage'] = 'BXH Theo tháng';
$this->_data['loadPage'] = 'backend/cms_ranking/list_by_monthly_view';
$this->load->view('backend/admin_template_view', $this->_data);
}
public function listByMonthlyAjax()
{
$data = array();
$fByName = $this->input->post('fByName');
$pageId = $this->input->post('pageId');
//
$pageId = ($pageId == 0) ? 1 : $pageId;
//
$limit = 100;
$offset = ($pageId - 1) * $limit;
$data['offset'] = ($pageId - 1) * $limit;
$totalRecord = $this->report_views_monthly_model->cmsCountAll($fByName);
$data['pagination'] = MyHelper::genPaginationLink($totalRecord, $limit, $pageId);
$data['listData'] = $this->report_views_monthly_model->cmsGetPagination($limit, $offset, $fByName);
$data['previousRanking'] = $this->report_views_monthly_model->previousRanking($fByName);
//
$this->load->view('backend/ajax/cms_ranking/list_by_monthly_view', $data);
}
/**
* Export BXH ngày, tuần, tháng ra excel ---------------------------------------------------------------------------
* @param $type
* @param $time
*/
public function exportReport($type, $time)
{
ini_set('max_execution_time', -1);
// Ghi log
$this->actionlog_model->add('EXPORTED', 'Export BXH: ' . $type, null, null, null);
if ($type && $time) {
$array_time = explode('-', $time);
$week = date('W');
$month = date('n');
$year = date('Y');
switch ($type) {
case 'daily':
if ($time) {
$daily_at = date('Y-m-d', strtotime($time));
$filename = 'Daily_report_' . date('Y_m_d', strtotime($time));
$sheet_name = 'Daily-report-' . $daily_at;
$reports = $this->track_model->getReportDaily($daily_at);
}
break;
case 'weekly':
$week = trim($array_time[0]);
$year = trim($array_time[1]);
$date_range = MyHelper::getWeekDateRange($week, $year, 'd-m-Y');
if (isset($date_range['start_week']) && isset($date_range['end_week'])) {
$name = date('d_m', strtotime($date_range['start_week'])) . '__' . date('d_m', strtotime($date_range['end_week']));
$filename = 'Weekly_report_' . (($week < 10) ? '0'.$week : $week) . '_' . $year . ' (' . $name . ')';
} else {
$filename = 'Weekly_report_' . (($week < 10) ? '0'.$week : $week) . '_' . $year;
}
$sheet_name = 'Weekly-report-' . $time;
$reports = $this->track_model->getReportWeekly($week, $year);
break;
case 'monthly':
$month = trim($array_time[0]);
$year = trim($array_time[1]);
$filename = 'Monthly_report_' . (($month < 10) ? '0'.$time : $time);
$sheet_name = 'Monthly-report-' . $time;
$reports = $this->track_model->getReportMonthly($month, $year);
break;
}
$count_views = [];
foreach ($reports as $key => $result) {
$list_user = $this->track_model->getUserByTrack($result['track_id']);
$casi[$key] = [];
if ($list_user && count($list_user) > 0) {
foreach ($list_user as $k => $users){
$singer = $this->user_model->getUser($users['user_id']);
if ($singer) {
$casi[$key][] = $singer[0]['full_name'];
}else {
$casi[$key][] = $list_user[$k]['name'];
}
}
}
$list_artists = $this->track_model->getArtistsByTrack($result['track_id']);
$nhacsi[$key] = [];
if ($list_artists ) {
foreach ($list_artists as $k_art => $art){
$artist = $this->user_model->getUser($art['user_id']);
if ($artist) {
$nhacsi[$key][] = $artist[0]['full_name'];
}else {
$nhacsi[$key][] = $list_artists[$k_art]['name'];
}
}
}
$track_detail = $this->track_model->getTrackById($result['track_id']);
$track_detail = $track_detail[0];
$count_views[] = array(
'A' => isset($result['order']) ? $result['order'] : $result['ranking'],
//'B' => ($track_detail['title'] != '') ? $track_detail['title'] . ' (' . $track_detail['id'] . ')' : '',
'B' => ($track_detail['title'] != '') ? $track_detail['title'] : '',
'C' => ($casi[$key] && count($casi[$key]) > 0 ) ? implode(", ", $casi[$key]) : '',
'D' => ($nhacsi[$key] && count($nhacsi[$key]) > 0 ) ? implode(", ", $nhacsi[$key]) : '',
'E' => number_format($result['views_zing']),
'F' => number_format($result['views_nct']),
'G' => number_format($result['views_keeng']),
'H' => 0,
'I' => number_format($result['point_zing']),
'J' => number_format($result['point_nct']),
'K' => number_format($result['point_keeng']),
'L' => 0,
'M' => number_format($result['total_point'])
);
}
$objPHPExcel = new PHPExcel;
// set default font
$objPHPExcel->getDefaultStyle()->getFont()->setName('Tahoma');
// set default font size
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
// writer already created the first sheet for us, let's get it
$objSheet = $objPHPExcel->getActiveSheet();
// rename the sheet
$objSheet->setTitle($sheet_name);
$objSheet->getCell('A1')->setValue("Rank");
$objSheet->getCell('B1')->setValue("Song Title");
$objSheet->getCell('C1')->setValue("Artist");
$objSheet->getCell('D1')->setValue("Composer");
$objSheet->getCell('E1')->setValue("Zing Stream");
$objSheet->getCell('F1')->setValue("NCT Stream");
$objSheet->getCell('G1')->setValue("Keeng Stream");
$objSheet->getCell('H1')->setValue("Nhac.vn Stream");
$objSheet->getCell('I1')->setValue("Zing Point");
$objSheet->getCell('J1')->setValue("NCT Point");
$objSheet->getCell('K1')->setValue("Keeng Point");
$objSheet->getCell('L1')->setValue("Nhac.vn Point");
$objSheet->getCell('M1')->setValue("TOTAL POINT");
$border_style = array(
'borders' =>array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array('rgb' => '000000')
)
)
);
$style_header = array(
'font' => array(
'bold' => true,
),
'alignment' =>array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
),
'fill' =>array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => '8DB4E2'),
),
);
$objSheet->getStyle('A1:M1')->applyFromArray($style_header);
$objSheet->getStyle("A1:M1")->applyFromArray($border_style);
$style_top10 = array(
'font' => array(
'bold' => true,
),
);
$objSheet->getStyle('A2:D11')->applyFromArray($style_top10);
// In du lieu vao bang
foreach ($count_views as $hang => $hd) {
for ($i = 65; $i <= 77; $i++) {
if ($i == 66 || $i == 67 || $i == 68) {
$objSheet->getStyle(chr($i).($hang+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
}else {
$objSheet->getStyle(chr($i).($hang+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
// $hd[chr($i)] = (!empty($hd[chr($i)])) ? $hd[chr($i)] : '';
$objSheet->getCell(chr($i).($hang+2))->setValue($hd[chr($i)]);
$objSheet->getStyle(chr($i).($hang+2))->applyFromArray($border_style);
$objSheet->getStyle(chr($i).($hang+2))->getAlignment()->setWrapText(true);
$objSheet->getStyle(chr($i).($hang+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
// Set size column
// $objSheet->getColumnDimension(chr($i))->setAutoSize(false);
if ($i == 65) {
$objSheet->getColumnDimension(chr($i))->setWidth("8");
}elseif($i == 66 || $i == 67 || $i == 68) {
$objSheet->getColumnDimension(chr($i))->setWidth("26");
}else {
$objSheet->getColumnDimension(chr($i))->setWidth("12");
}
}
}
$objSheet->getStyle('A1:M1')->getAlignment()->setWrapText(true);
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '.xlsx"');
PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007')->save('php://output');;
die();
} else {
redirect(base_url('backend/home/index'));
}
}
/**
* Hàm xử lý lấy dữ liệu lịch sử lượt nghe của bài hát dựa vào ID
*/
public function getTrackHistoryAjax()
{
$track_id = $this->input->post('track_id');
$track_title = $this->input->post('track_title');
// Ghi log
$this->actionlog_model->add('SHOW_HISTORY', 'Xem lịch sử lượt nghe', null, null, $track_id);
$data = [];
$histories = $this->crawler_listen_history_model->getByTrackId($track_id);
if ($histories) {
foreach ($histories as $key => $track) {
$data[$key]['date'] = $track['date_log'];
$data[$key]['zing'] = intval($track['zing_count']);
$data[$key]['nct'] = intval($track['nct_count']);
$data[$key]['keeng'] = intval($track['keeng_count']);
}
}
/*$url = 'http://edupham.com/api/v1/vmusicchart/get-listen-history-by-track-id?track_id=1644&type=chart';
$data = file_get_contents($url);*/
$response['response'] = $data;
$response['track_title'] = $track_title;
$this->load->view('backend/ajax/cms_ranking/history_chart_view', $response);
}
/**
* Hàm lấy ds BXH để đổ vào select box
*/
public function getRankingByAjax()
{
$time = $this->input->post('time');
$scope = $this->input->post('scope');
//
$limit = 100;
$offset = 0;
switch ($scope) {
case 'DAILY':
$date = str_replace('/', '-', $time);
$data['ranking'] = $this->report_views_daily_model->cmsGetPagination($limit, $offset, $date);
break;
case 'WEEKLY':
$data['ranking'] = $this->report_views_weekly_model->cmsGetPagination($limit, $offset, $time);
break;
case 'MONTHLY':
$data['ranking'] = $this->report_views_monthly_model->cmsGetPagination($limit, $offset, $time);
break;
default:
$data['ranking'] = [];
}
$data['scope'] = $scope;
//
$this->load->view('backend/ajax/cms_ranking/ranking_selection_view', $data);
}
/**
* Hàm lưu báo cáo sai sót ở BXH
*/
public function reportOverlappingAjax()
{
$time = $this->input->post('time');
$scope = $this->input->post('scope');
//
$ranking_id = $this->input->post('ranking_id');
$keep_ranking = $this->input->post('keep_ranking');
$keep_track_id = $this->input->post('keep_track_id');
$keep_track_title = $this->input->post('keep_track_title');
//
$delete_ranking = $this->input->post('delete_ranking');
$delete_track_id = $this->input->post('delete_track_id');
$delete_track_title = $this->input->post('delete_track_title');
//
if ($ranking_id > 0 && $delete_track_id > 0 && in_array($scope, ['DAILY', 'WEEKLY', 'MONTHLY'])) {
$date = null;
$week = null;
$month = null;
$year = null;
switch ($scope) {
case 'DAILY':
$time = str_replace('/', '-', trim($time));
$date = date('Y-m-d', strtotime($time));
break;
case 'WEEKLY':
$time = explode('-', $time);
if (count($time) == 2) {
$week = trim($time[0]);
$year = trim($time[1]);
}
break;
case 'MONTHLY':
$time = explode('-', $time);
if (count($time) == 2) {
$month = trim($time[0]);
$year = trim($time[1]);
}
break;
default:
}
$data_insert = [
'scope' => $scope,
'ranking_id' => $ranking_id,
'date' => $date,
'week' => $week,
'month' => $month,
'year' => $year,
'keep_ranking' => $keep_ranking,
'keep_track_id' => $keep_track_id,
'keep_track_title' => trim($keep_track_title),
'delete_ranking' => $delete_ranking,
'delete_track_id' => $delete_track_id,
'delete_track_title' => trim($delete_track_title),
'status' => 0,
'report_by' => $this->session->userdata('id'),
'created_at' => date('Y-m-d H:i:s'),
'updated_at' => date('Y-m-d H:i:s')
];
$id = $this->report_violation_model->add($data_insert);
if ($id > 0) {
echo json_encode([
'code' => 200,
'message' => 'Thành công',
'data' => null
]);
} else {
echo json_encode([
'code' => 400,
'message' => 'Không thành công',
'data' => null
]);
}
} else {
echo json_encode([
'code' => 422,
'message' => 'Không thành công, lỗi tham số truyền lên',
'data' => null
]);
}
}
/**
* Hàm xử lý trùng BXH
* @param $id
*/
public function processOverlapRanking($id, $scope)
{
if (in_array($scope, ['DAILY', 'WEEKLY', 'MONTHLY'])) {
$overlap = $this->report_violation_model->getById($id, 0);
if (isset($overlap[0])) {
$limit = 105;
$offset = 0;
// Xử lý xóa và lấy ds sau khi xóa
switch ($scope) {
case 'DAILY':
$this->common_model->delete('report_views_daily', $overlap[0]['ranking_id']);
//
$date = $overlap[0]['date'];
$ranking = $this->report_views_daily_model->cmsGetPagination($limit, $offset, $date);
break;
case 'WEEKLY':
$this->common_model->delete('report_views_weekly_new', $overlap[0]['ranking_id']);
//
$time = $overlap[0]['week'] . '-' . $overlap[0]['year'];
$ranking = $this->report_views_weekly_model->cmsGetPagination($limit, $offset, $time);
break;
case 'MONTHLY':
$this->common_model->delete('report_views_monthly_new', $overlap[0]['ranking_id']);
//
$time = $overlap[0]['month'] . '-' . $overlap[0]['year'];
$ranking = $this->report_views_monthly_model->cmsGetPagination($limit, $offset, $time);
break;
default:
$ranking = [];
}
// Xử lý cập nhật lại BXH sau khi xóa
if (is_array($ranking) && count($ranking) > 0) {
$temp = [];
foreach ($ranking as $key => $item) {
if ($scope == 'DAILY') {
$temp[] = [ 'id' => $item['id'], 'ranking' => $key + 1 ];
} else {
$temp[] = [ 'id' => $item['id'], 'order' => $key + 1 ];
}
}
switch ($scope) {
case 'DAILY':
$this->common_model->update_batch('report_views_daily', $temp, 'id');
break;
case 'WEEKLY':
$this->common_model->update_batch('report_views_weekly_new', $temp, 'id');
break;
case 'MONTHLY':
$this->common_model->update_batch('report_views_monthly_new', $temp, 'id');
break;
default:
}
}
// Cập nhật trạng thái đã xử lý
$this->common_model->update(
'report_violations',
$id,
[
'status' => 1,
'action_by' => $this->session->userdata('id'),
'updated_at' => date('Y-m-d H:i:s')
]
);
//
$this->session->set_flashdata('success', 'Gỡ bỏ bài hát khỏi BXH '. $scope .' thành công!');
redirect(base_url($this->overlap_ranking_list));
} else {
redirect(base_url($this->overlap_ranking_list));
}
} else {
redirect(base_url($this->overlap_ranking_list));
}
}
}