public function indexAction()
{
$this->accessRights(43);
$this->childModuleAccessRights(42, 'view');
//Accept Child Module ID & it's Actions: add, edit, view, disable
$form = new NewsListForm($this->serviceLocator());
$form->get('submit')->setValue('Go');
$adapter = new ArchiveTable($this->serviceLocator());
$news = new NewsListAdapter($this->adapter);
$result = array();
$user = $this->getUser();
$user_id = $user['user_id'];
$request = $this->getRequest();
$sub_kind = "";
$result = $adapter->getAllDraft();
if ($request->isPost()) {
$s_id = $_POST['search_by'];
$input = $_POST['input'];
$id = $_POST['media_outfit_id'];
$sub_kind = $_POST['sub_kind'];
$article_type_id = $_POST['article_type_id'];
$from = $_POST['from'];
$to = $_POST['to'];
$bu_csr = $_POST['bu_csr'];
$form->get('search_by')->setValue($s_id);
$form->get('input')->setValue($input);
$form->get('media_outfit_id')->setValue($id);
$form->get('bu_csr')->setValue($bu_csr);
if ($id == 1) {
$form->get('sub_kind')->setValueOptions(array('8' => 'All', '1' => 'Online', '2' => 'Blog', '3' => 'International'));
} elseif ($id == 2) {
$form->get('sub_kind')->setValueOptions(array('9' => 'All', '4' => 'Local', '5' => 'National'));
} elseif ($id == 3) {
$form->get('sub_kind')->setValueOptions(array('6' => 'Radio'));
} elseif ($id == 4) {
$form->get('sub_kind')->setValueOptions(array('7' => 'TV'));
}
$form->get('sub_kind')->setValue($sub_kind);
$form->get('article_type_id')->setValue($article_type_id);
$form->get('from')->setValue($from);
$form->get('to')->setValue($to);
$news->createTmpTable();
$result = $adapter->getOnlyDraft($s_id, $input, $sub_kind, $article_type_id, $from, $to, $bu_csr, $user_id);
//
}
return new ViewModel(array('draft_list' => $result, 'form' => $form, 'user_id' => $user_id, 'sub_kind' => $sub_kind, 'access_rights' => $this->getSubModuleAccessRights(42), 'tab_menus' => $this->getTabMenu('Draft / Archive')));
}
public function getAllDraft()
{
$news = new NewsListAdapter($this->adapter);
$dbAdapter = $this->adapter;
$sql = "SELECT news_header_id, created_at\n FROM news_header\n WHERE `status`=2\n AND\n DATE(`created_at`) <= DATE(DATE_SUB(NOW(),INTERVAL 1 WEEK));";
$statement = $dbAdapter->createStatement($sql);
$result = $statement->execute();
foreach ($result as $data) {
$news->delete($data['news_header_id']);
}
$user = $this->getUser();
$user_id = $user['user_id'];
$news->createTmpTable();
$sql = "SELECT * FROM news_list_header WHERE status = 2 AND created_by =?";
$this->adapter->query($sql);
$statement = $this->adapter->createStatement($sql, array($user_id));
$result = $statement->execute();
return $result;
}
public function getMediaProfileEvents(AdapterInterface $adapter, $id)
{
$query_journal = "\n SELECT\n mp.first_name, mp.last_name,\n ev.event_name, ev.event_date,\n emp.invited, emp.confirmed\n FROM media_profiles AS mp\n LEFT JOIN events_media_profiles AS emp\n ON mp.media_profiles_id = emp.media_profile_id\n LEFT JOIN `events` AS ev\n ON emp.event_id = ev.events_id\n WHERE mp.media_profiles_id = {$id} AND emp.attended = 1\n ";
if (isset($_POST['buttonSearchEvent'])) {
$date_from = $_POST['date_from_event'];
$date_to = $_POST['date_to_event'];
$query_journal = "\n SELECT\n mp.first_name, mp.last_name,\n ev.event_name, ev.event_date,\n emp.invited, emp.confirmed\n FROM media_profiles AS mp\n LEFT JOIN events_media_profiles AS emp\n ON mp.media_profiles_id = emp.media_profile_id\n LEFT JOIN `events` AS ev\n ON emp.event_id = ev.events_id\n WHERE mp.media_profiles_id = {$id} AND emp.attended = 1 AND ev.event_date BETWEEN '{$date_from}' AND '{$date_to}'\n ";
}
$newsList = new NewsListAdapter($adapter);
$newsList->createTmpTable();
$statement = $this->adapter->query("{$query_journal}");
$list = $statement->execute();
return $list;
}
public function getAllSSP($role, $user_id, $access_rights)
{
$base_url = "http://" . $_SERVER['SERVER_NAME'] . dirname($_SERVER["REQUEST_URI"] . '?') . '/';
extract($access_rights);
$requestData = $_REQUEST;
// storing request (ie, get/post) global array to a variable
// echo'<pre>';print_r($requestData);die;
$columns = array(0 => '', 1 => '', 2 => '', 3 => 'source_kind', 4 => 'title', 5 => 'article_date', 6 => 'company_name', 7 => 'source', 8 => 'author', 9 => 'article_type', 10 => 'uploader_name', 11 => '');
if ($requestData['search_by'] != -1 && !empty($requestData['from']) || (!empty($requestData['search_by']) || !empty($requestData['input']) || !empty($requestData['media_outfit_id']) || !empty($requestData['sub_kind']) || !empty($requestData['article_type_id']) || !empty($requestData['to']))) {
$s_id = $requestData['search_by'];
$input = $requestData['input'];
$id = $requestData['media_outfit_id'];
$media_id = $requestData['sub_kind'];
$article_type_id = $requestData['article_type_id'];
$from = !empty($requestData['from']) ? $requestData['from'] : NULL;
$to = !empty($requestData['to']) ? $requestData['to'] : NULL;
$bu_csr = $requestData['bu_csr'];
$news = new NewsListAdapter($this->adapter);
$news->createTmpTable();
//$result = $this->getOnlyArchive($s_id, $input, $sub_kind, $article_type_id, $from, $to, $bu_csr);
$dbAdapter = $this->adapter;
$minDate = Date('Y-m-d', strtotime("-90 days"));
$current_date = Date('Y-m-d');
if ($s_id == 0 && $from == null && $to == null) {
$where = "news_list_header . status = 1 and article_date >= ?";
$arr = array($minDate);
} elseif ($s_id == 0 && $from != null && $to != null) {
$where = "article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($from, $to);
} elseif ($s_id == 0 && $from != null && $to == null) {
$where = "article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($from, $current_date);
} elseif ($s_id == 0 && $from == null && $to != null) {
$where = "article_date >=? and article_date <= ? AND news_list_header . status = 1";
$arr = array($minDate, $to);
} elseif ($s_id == 1 && $media_id != null && $article_type_id == 0 && $from == null && $to == null) {
if ($media_id == 8) {
$where = "media_outfit_id in(?,?,?) AND news_list_header . status = 1 and article_date >= ?";
$arr = array(1, 2, 3, $minDate);
} elseif ($media_id == 9) {
$where = "media_outfit_id in(?,?) AND news_list_header . status = 1 and article_date >= ?";
$arr = array(4, 5, $minDate);
} else {
$where = "media_outfit_id =? AND news_list_header . status = 1 and article_date >= ?";
$arr = array($media_id, $minDate);
}
} elseif ($s_id == 1 && $media_id != null && $article_type_id == 0 && $from != null && $to != null) {
if ($media_id == 8) {
$where = "media_outfit_id in(?,?,?) and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array(1, 2, 3, $from, $to);
} elseif ($media_id == 9) {
$where = "media_outfit_id in(?,?) and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array(4, 5, $from, $to);
} else {
$where = "media_outfit_id =? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($media_id, $from, $to);
}
} elseif ($s_id == 1 && $media_id != null && $article_type_id == 0 && $from != null && $to == null) {
if ($media_id == 8) {
$where = "media_outfit_id in(?,?,?) and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array(1, 2, 3, $from, $current_date);
} elseif ($media_id == 9) {
$where = "media_outfit_id in(?,?) and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array(4, 5, $from, $current_date);
} else {
$where = "media_outfit_id =? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($media_id, $from, $current_date);
}
} elseif ($s_id == 1 && $media_id != null && $article_type_id == 0 && $from == null && $to != null) {
if ($media_id == 8) {
$where = "media_outfit_id in(?,?,?) AND article_date>=? and article_date <= ? AND news_list_header . status = 1";
$arr = array(1, 2, 3, $minDate, $to);
} elseif ($media_id == 9) {
$where = "media_outfit_id in(?,?) AND article_date>=? AND article_date <= ? AND news_list_header . status = 1";
$arr = array(4, 5, $minDate, $to);
} else {
$where = "media_outfit_id =? AND article_date>=? and article_date <= ? AND news_list_header . status = 1";
$arr = array($media_id, $minDate, $to);
}
} elseif ($s_id == 1 && $media_id != null && $article_type_id > 0 && $from != null && $to != null) {
if ($media_id == 8) {
$where = "article_type_id =? and media_outfit_id in(?,?,?) and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, 1, 2, 3, $from, $to);
} elseif ($media_id == 9) {
$where = "article_type_id =? and media_outfit_id in(?,?) and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, 4, 5, $from, $to);
} else {
$where = "article_type_id =? and media_outfit_id =? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, $media_id, $from, $to);
}
} elseif ($s_id == 1 && $media_id != null && $article_type_id > 0 && $from != null && $to == null) {
if ($media_id == 8) {
$where = "article_type_id =? and media_outfit_id in(?,?,?) and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, 1, 2, 3, $from, $current_date);
} elseif ($media_id == 9) {
$where = "article_type_id =? and media_outfit_id in(?,?) and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, 4, 5, $from, $current_date);
} else {
$where = "article_type_id =? and media_outfit_id =? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, $media_id, $from, $current_date);
}
} elseif ($s_id == 1 && $media_id != null && $article_type_id > 0 && $from == null && $to != null) {
if ($media_id == 8) {
$where = "article_type_id =? and media_outfit_id in(?,?,?) AND article_date>=? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, 1, 2, 3, $minDate, $current_date);
} elseif ($media_id == 9) {
$where = "article_type_id =? and media_outfit_id in(?,?) AND article_date>=? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, 4, 5, $minDate, $to);
} else {
$where = "article_type_id =? and media_outfit_id =? AND article_date>=? and article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, $media_id, $minDate, $to);
}
} elseif ($s_id == 1 && $media_id == null && $article_type_id > 0 && $from == null && $to == null) {
$where = "article_type_id =? AND news_list_header . status = 1 and article_date >= ?";
$arr = array($article_type_id, $minDate);
} elseif ($s_id == 1 && $media_id == null && $article_type_id > 0 && $from != null && $to != null) {
$where = "article_type_id =? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, $from, $to);
} elseif ($s_id == 1 && $media_id == null && $article_type_id > 0 && $from != null && $to == null) {
$where = "article_type_id =? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, $from, $current_date);
} elseif ($s_id == 1 && $media_id == null && $article_type_id > 0 && $from == null && $to != null) {
$where = "article_type_id =? and article_date >= ? and article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, $minDate, $to);
} elseif ($s_id == 2 && $from == null && $to == null) {
$where = "source like ? AND news_list_header.status = 1 and article_date >= ?";
$arr = array("%{$input}%", $minDate);
} elseif ($s_id == 2 && $from != null && $to != null) {
$where = "source like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $to);
} elseif ($s_id == 2 && $from != null && $to == null) {
$where = "source like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $current_date);
} elseif ($s_id == 2 && $from == null && $to != null) {
$where = "source like ? and article_date>=? and article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $minDate, $to);
} elseif ($s_id == 3 && $from == null && $to == null) {
$where = "title like ? AND news_list_header . status = 1 and article_date >= ?";
$arr = array("%{$input}%", $minDate);
} elseif ($s_id == 3 && $from != null && $to != null) {
$where = "title like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $to);
} elseif ($s_id == 3 && $from != null && $to == null) {
$where = "title like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $current_date);
} elseif ($s_id == 3 && $from == null && $to != null) {
$where = "title like ? and article_date>=? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $minDate, $to);
} elseif ($s_id == 4 && $from == null && $to == null && $bu_csr == 0) {
if ($input == null) {
$where = "news_list_header . status = 1 and article_date >= ?";
$arr = array($minDate);
} else {
$where = "company_name like ? AND news_list_header . status = 1 and article_date >= ?";
$arr = array("%{$input}%", $minDate);
}
} elseif ($s_id == 4 && $from != null && $to != null && $bu_csr == 0) {
$where = "company_name like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $to);
} elseif ($s_id == 4 && $from != null && $to == null && $bu_csr == 0) {
$where = "company_name like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $minDate, $current_date);
} elseif ($s_id == 4 && $from == null && $to != null && $bu_csr == 0) {
$where = "company_name like ? and article_date >= ? and article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $minDate, $to);
} elseif ($s_id == 4 && $from == null && $to == null && $bu_csr == 1) {
if ($input == null) {
$where = "`bu_csr` = 1 AND news_list_header . status = 1 and article_date >= ?";
$arr = array($minDate);
} else {
$where = "company_name like ? and `bu_csr` = 1 AND news_list_header . status = 1 and article_date >= ?";
$arr = array("%{$input}%", $minDate);
}
} elseif ($s_id == 4 && $from != null && $to != null && $bu_csr == 1) {
$where = "company_name like ? and article_date >= ? AND article_date <= ? and `bu_csr` = 1 AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $to);
} elseif ($s_id == 4 && $from != null && $to == null && $bu_csr == 1) {
$where = "company_name like ? and article_date >= ? AND article_date <= ? and `bu_csr` = 1 AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $current_date);
} elseif ($s_id == 4 && $from == null && $to != null && $bu_csr == 1) {
$where = "company_name like ? and article_date >= ? and article_date <= ? and `bu_csr` = 1 AND news_list_header . status = 1";
$arr = array("%{$input}%", $minDate, $to);
} elseif ($s_id == 5 && $from == null && $to == null) {
$where = "author like ? AND news_list_header.status = 1 and article_date >= ? and article_date <= ?";
$arr = array("%{$input}%", $minDate, $current_date);
} elseif ($s_id == 5 && $from != null && $to != null) {
$where = "author like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $to);
} elseif ($s_id == 5 && $from != null && $to == null) {
$where = "author like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $current_date);
} elseif ($s_id == 5 && $from == null && $to != null) {
$where = "author like ? and article_date >= ? and article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $minDate, $to);
} elseif ($s_id == 6 && $from == null && $to == null) {
$where = "uploader_name like ? AND news_list_header . status = 1 and article_date >= ?";
$arr = array("%{$input}%", $minDate);
} elseif ($s_id == 6 && $from != null && $to != null) {
$where = "uploader_name like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $to);
} elseif ($s_id == 6 && $from != null && $to == null) {
$where = "uploader_name like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $current_date);
} elseif ($s_id == 6 && $from == null && $to != null) {
$where = "uploader_name like ? and article_date >= ? and article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $minDate, $to);
} elseif ($s_id == 7 && $from == null && $to == null) {
$where = "story_angle like ? AND news_list_header . status = 1 and article_date >= ?";
$arr = array("%{$input}%", $minDate);
} elseif ($s_id == 7 && $from != null && $to != null) {
$where = "story_angle like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $to);
} elseif ($s_id == 7 && $from != null && $to == null) {
$where = "story_angle like ? and article_date >= ? AND article_date <= ? AND news_list_header . status = 1";
$arr = array("%{$input}%", $from, $current_date);
} elseif ($s_id == 7 && $from == null && $to != null) {
$where = "story_angle like ? and article_date >= ? AND article_date <= ? AND news_list_header.status = 1";
$arr = array("%{$input}%", $minDate, $to);
} elseif ($s_id == 8 && $from == null && $to == null) {
$where = "news_article like ? AND news_list_header.status = 1 and article_date >= ?";
$arr = array("%{$input}%", $minDate);
} elseif ($s_id == 8 && $from != null && $to != null) {
$where = "news_article like ? and article_date >= ? AND article_date <= ? AND news_list_header.status = 1";
$arr = array("%{$input}%", $from, $to);
} elseif ($s_id == 8 && $from != null && $to == null) {
$where = "news_article like ? and article_date >= ? AND article_date <= ? AND news_list_header.status = 1";
$arr = array("%{$input}%", $from, $current_date);
} elseif ($s_id == 8 && $from == null && $to != null) {
$where = "news_article like ? and article_date >= ? and article_date <= ? AND news_list_header.status = 1";
$arr = array("%{$input}%", $minDate, $to);
} elseif ($s_id == 9 && $from == null && $to == null) {
$where = "article_type like ? AND news_list_header.status = 1 and article_date >= ?";
$arr = array("%{$input}%", $minDate);
} elseif ($s_id == 9 && $from != null && $to != null) {
$where = "article_type like ? and article_date >= ? AND article_date <= ? AND news_list_header.status = 1";
$arr = array("%{$input}%", $from, $to);
} elseif ($s_id == 9 && $from != null && $to == null) {
$where = "article_type like ? and article_date >= ? AND article_date <= ? AND news_list_header.status = 1";
$arr = array("%{$input}%", $from, $current_date);
} elseif ($s_id == 9 && $from == null && $to != null) {
$where = "article_type like ? and article_date >= ? and article_date <= ? AND news_list_header.status = 1";
$arr = array("%{$input}%", $minDate, $to);
} elseif ($s_id == 1 && $media_id != null && $article_type_id > 0 && $from == null && $to == null) {
if ($media_id == 8) {
$where = "article_type_id =? and media_outfit_id in(?,?,?) and article_date >= ? and article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, 1, 2, 3, $minDate, $current_date);
} elseif ($media_id == 9) {
$where = "article_type_id =? and media_outfit_id in(?,?) and article_date >= ? and article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, 4, 5, $minDate, $current_date);
} else {
$where = "article_type_id =? and media_outfit_id =? and article_date >= ? and article_date <= ? AND news_list_header . status = 1";
$arr = array($article_type_id, $media_id, $minDate, $current_date);
}
}
$sql = "SELECT * FROM news_list_header where " . $where . " and (source_status=1 or source_status is null) and (company_status=1 or company_status is null) and (media_profile_status=1 or media_profile_status is null) and status=1";
$sql_counter = "SELECT news_header_id FROM news_list_header where " . $where . " and (source_status=1 or source_status is null) and (company_status=1 or company_status is null) and (media_profile_status=1 or media_profile_status is null) and status=1";
$dbAdapter->query($sql);
$statement = $dbAdapter->createStatement($sql_counter, $arr);
$result = $statement->execute();
$totalFiltered = count($result);
// when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";
$dbAdapter->query($sql);
$statement = $dbAdapter->createStatement($sql, $arr);
$result = $statement->execute();
} else {
$this->createTmpTable();
$dbAdapter = $this->adapter;
$from = Date('Y-m-d', strtotime("-90 days"));
// if($role == 1)
// {
$sql2 = "SELECT * FROM news_list_header where article_date >= ? and (source_status=1 or source_status is null) and (company_status=1 or company_status is null) and (media_profile_status=1 or media_profile_status is null) and status=1";
$sql2_counter = "SELECT news_header_id FROM news_list_header where article_date >= ? and (source_status=1 or source_status is null) and (company_status=1 or company_status is null) and (media_profile_status=1 or media_profile_status is null) and status=1";
$statement = $dbAdapter->createStatement($sql2_counter, array($from));
/* }
else
{
$sql = "SELECT * FROM news_list_header where article_date >= ? and source_status=1 and company_status=1 and media_profile_status=1 and status= 1 AND created_by = ?;";
$dbAdapter->query($sql);
$statement = $dbAdapter->createStatement($sql,array($from,$user_id));
}*/
$result = $statement->execute();
$totalFiltered = count($result);
$sql2 .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";
$statement = $dbAdapter->createStatement($sql2, array($from));
$result = $statement->execute();
}
$json_data = array("draw" => intval($requestData['draw']), "recordsFiltered" => intval($totalFiltered), "data" => array());
foreach ($result as $aRow) {
$row = array();
$row[] = NULL;
$row[] = '
<input ' . ($aRow['marked'] == 1 ? 'checked' : '') . ' type="checkbox" class="marked glyphicon glyphicon-star-empty" id="' . $aRow['news_header_id'] . '">
';
$delete_news = '
<input type="checkbox" class="cbox" name="cbox[]" value="' . $aRow['news_header_id'] . '" id="' . $aRow['news_header_id'] . '">
';
$row[] = $user_id == $aRow['created_by'] || isset($delete_all_action) ? $delete_news : '';
$row[] = $aRow[$columns[3]];
//source kind
$row[] = '
<a href="' . $base_url . '../news/show/' . $aRow['news_header_id'] . '" target="_blank">' . $aRow['title'] . '</a>
<i class="fa fa-chevron-right"></i>
';
$row[] = $aRow[$columns[5]];
//article date
$row[] = $aRow[$columns[6]];
//company name
$row[] = $aRow[$columns[7]];
//source
$row[] = $aRow[$columns[8]];
//author
$row[] = $aRow[$columns[9]];
//article type
$row[] = $aRow[$columns[10]];
//uploader_name
$edit_news = '
<a href="' . $base_url . '../news_management/edit/' . $aRow['news_header_id'] . '"
class="edit-btn adj" data-toggle="tooltip" data-id="16"
data-placement="right" title="Edit">
<i class="fa fa-pencil-square-o fa-lg"></i>
</a>
';
$row[] = $user_id == $aRow['created_by'] || isset($edit_all_action) ? $edit_news : '';
$json_data['data'][] = $row;
}
return $json_data;
// send data as json format
}
public function fetchAllNotif()
{
$dbAdapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter');
$news = new NewsListAdapter($dbAdapter);
$sql = "SELECT news_header_id, created_at\n FROM news_header\n WHERE `status`=2\n AND\n DATE(`created_at`) <= DATE(DATE_SUB(NOW(),INTERVAL 1 WEEK));";
$statement = $dbAdapter->createStatement($sql);
$result = $statement->execute();
foreach ($result as $data) {
$news->delete($data['news_header_id']);
}
$sql = "SELECT tbl_name,created_at\n FROM for_approval\n WHERE\n DATE(created_at) < DATE(DATE_SUB(NOW(),INTERVAL 1 WEEK))";
$statement = $dbAdapter->createStatement($sql);
$result = $statement->execute();
foreach ($result as $data) {
$sql = "DELETE\n FROM " . $data['tbl_name'] . "\n WHERE DATE(created_at) < DATE(DATE_SUB(NOW(),INTERVAL 1 WEEK))\n AND `status`=2";
$statement = $dbAdapter->createStatement($sql);
$statement->execute();
}
$sql = "DELETE\n FROM for_approval\n WHERE\n DATE(created_at) < DATE(DATE_SUB(NOW(),INTERVAL 1 WEEK))";
$statement = $dbAdapter->createStatement($sql);
if ($statement->execute()) {
$sql = "SELECT\n for_approval_id,record_id,route_name,created_at,\n CONCAT(first_name,' ', last_name) AS request_by,field_name,`data`\n FROM\n (SELECT *\n FROM for_approval\n LEFT JOIN\n (SELECT app_user_credentials_id,user_detail_id FROM app_user_credentials)app_user_credentials\n ON\n app_user_credentials_id = for_approval.created_by\n LEFT JOIN\n (SELECT first_name,last_name,app_user_details_id FROM app_user_details)app_user_details\n ON app_user_details_id = user_detail_id)a\n ";
$statement = $dbAdapter->createStatement($sql);
$result = $statement->execute();
return $result;
}
}
public function serverSideProcessingAction()
{
//extract access rights
$this->accessRights(14);
$this->childModuleAccessRights(14, 'view');
$access_rights = $this->getSubModuleAccessRights(14);
$adapter = new NewsListAdapter($this->serviceLocator());
$user = $this->getUser();
$user_role = $user['role_id'];
$user_id = $user['user_id'];
$result = $adapter->getAllSSP($user_role, $user_id, $access_rights);
echo json_encode($result);
}
public function sendNews($sendEmail = array(), $news_data = array(), $head_news_id = null, $head_news_id_1st = null, $head_news_id_2nd = null, $head_news_id_3rd = null, $tag_edition = null)
{
$this->accessRights(41);
$this->childModuleAccessRights(41, 'send');
//Accept Child Module ID & it's Actions: add, edit, view, disable
$this->adapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter');
$newslett = new NewsListAdapter($this->adapter);
// get current market highlight date
$market_highlight = $this->getMarketHighlightTable()->fetchMarketHighlightForNewsLetter($this->adapter);
// prepare article list data
$email_list_content = $newslett->getContentToSend($news_data, $head_news_id, $head_news_id_1st, $head_news_id_2nd, $head_news_id_3rd);
switch ($tag_edition) {
case 1:
$tag_edition_text = 'Morning Edition';
break;
case 2:
$tag_edition_text = 'Afternoon Edition';
break;
case 3:
$tag_edition_text = 'Breaking News Edition';
break;
}
// key > 3 and cs bur = 1 output
$least_bu_csr = $this->bondBuCsr($newslett->getContentToSend($news_data, $head_news_id, $head_news_id_1st, $head_news_id_2nd, $head_news_id_3rd));
$industries = $this->bondIndustries();
print_r($industries);
/*
foreach($industries as $key=>$value)
{
if(count($value) ==0)
{
unset($industries[$key]);
}
}
*/
// template with article list data
$view = new ViewModel(array('email_list_content' => $email_list_content, 'market_highlight' => $market_highlight, 'news_edition' => $tag_edition_text, 'bonded_industries' => $industries, 'bonded_bu_csr' => $least_bu_csr));
// selects tamplate from view folder
$view->setTemplate('send-email/template/mail');
$view->setTerminal(true);
$serviceManager = $this->getServiceLocator();
$emailHtml = $serviceManager->get('ViewRenderer')->render($view);
$getDataEmail = iterator_to_array($this->getSetUpTable()->fetchSetUp($this->adapter));
foreach ($getDataEmail as $val_setup_data) {
$application_email = $val_setup_data['app_email'];
$application_name = $val_setup_data['app_email_name'];
$application_mandrill_api = $val_setup_data['mandrill_api_key'];
}
try {
// initiates mandrill api
$mandrill = new Mandrill($application_mandrill_api);
// original subject format
// $tagdate = $tag_edition_text." ".Date('F d, Y h:i a');
$tagdate = $tag_edition_text . " " . Date('d F Y');
// message content
$message = array('subject' => $tagdate, 'to' => [], 'html' => $emailHtml, 'from_email' => $application_email, 'from_name' => $application_name, 'inline_css' => TRUE, 'track_opens => TRUE', 'track_clicks' => TRUE, 'tags' => array($tagdate));
// loop emails from db to supply "to" array object
foreach ($sendEmail as $emailObj) {
$message['to'][] = ['email' => $emailObj, 'name' => $emailObj, 'type' => 'to'];
}
$async = true;
// triggers the sending email
if ($mandrill->messages->send($message, $async)) {
return true;
}
} catch (Mandrill_Error $e) {
// Mandrill errors are thrown as except;ions
echo 'A mandrill error occurred: ' . get_class($e) . ' - ' . $e->getMessage();
// A mandrill error occurred: Mandrill_Invalid_Key - Invalid API key
throw $e;
}
}
public function returnMediaValue($field, $input, $from, $to, $score, $csr = null)
{
$dbAdapter = $this->adapter;
$newsList = new NewsListAdapter($dbAdapter);
$newsList->createTmpTable();
$score = $score == 1 ? "score IN (0,1)" : "score IN (2)";
switch ($field) {
case "source":
$where = $score . " AND source = ? AND `status` = 1";
break;
case "author":
$where = $score . " AND author = ? AND `status` = 1";
break;
case "company":
if ($csr != null) {
$and = " AND bu_csr=1";
} else {
$and = "";
}
$where = $score . " AND company_name = ? AND `status` = 1" . $and;
break;
case "source_kind":
$where = $score . " AND source_kind = ? AND `status` = 1";
break;
default:
$where = "status = 1";
}
if ($from == null and $to == null) {
$sql = "SELECT sum(media_value) AS `media_value`,source_kind FROM news_list_header where " . $where;
$ar = array($input);
} else {
$sql = "SELECT sum(media_value) AS `media_value`,source_kind FROM news_list_header where " . $where . " AND article_date BETWEEN ? AND ?";
$ar = array($input, $from, $to);
}
$dbAdapter->query($sql);
$statement = $dbAdapter->createStatement($sql, $ar);
$result_media_value = $statement->execute();
return $result_media_value;
}
public function searchNews($input)
{
$dbAdapter = $this->adapter;
$newsList = new NewsListAdapter($dbAdapter);
$newsList->createTmpTable();
$sql = "SELECT * FROM news_list_header WHERE title LIKE ?\n OR news_article LIKE ?\n OR news_highlight LIKE ?\n OR uploader_name LIKE ?\n OR article_type LIKE ?\n OR author LIKE ?\n OR company_name LIKE ?\n OR source LIKE ?\n OR source_kind LIKE ?\n OR story_angle LIKE ?\n and `status` = 1\n and media_profile_status=1\n and company_status =1\n and source_status =1;\n ";
$dbAdapter->query($sql);
$statement = $dbAdapter->createStatement($sql, array("%{$input}%", "%{$input}%", "%{$input}%", "%{$input}%", "%{$input}%", "%{$input}%", "%{$input}%", "%{$input}%", "%{$input}%", "%{$input}%"));
$result = $statement->execute();
return $result;
}
