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 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 deleteAction()
{
$this->accessRights(14);
$selectedArray = $_POST['cbox'];
$str_id = "";
$adapter = new NewsListAdapter($this->serviceLocator());
foreach ($selectedArray as $key => $value) {
$news_get_data = $adapter->getNews($value);
foreach ($news_get_data as $news_data) {
$str_id .= $news_data['title'] . ", ";
}
$adapter->delete($value);
}
$this->save_to_audit_trail($str_id, '--', '--', 'delete', 14);
$this->flashMessenger()->addMessage(['content' => 'News has been deleted', 'type' => 'warning']);
$this->redirect()->toRoute('news_list');
}
