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; }