|
Directory : /var/www/vhosts/lautnusantara.com/httpdocs/mis/app/Models/ |
<?php
namespace App\Models;
use CodeIgniter\Model;
class Artikel_Model extends Model
{
protected $table = 'ssn_news_items';
protected $primaryKey = 'id';
public function get_artikel($id = false, $keyword = false, $limit = false)
{
$builder = $this->select('ssn_news_items.*,ssn_users.first_name
,ssn_users.last_name,ssn_news_categories.name as nama_kategori');
$builder->join('ssn_users', 'ssn_users.id=ssn_news_items.created_by', 'left');
$builder->join('ssn_news_categories', 'ssn_news_categories.id=ssn_news_items.catid', 'left');
$builder->where('ssn_news_items.trash', '0');
// $builder->where('ssn_news_categories.active', '1');
if ($id != false) {
$builder->where('ssn_news_items.id', $id);
}
if ($keyword != false) {
$builder->like('title', $keyword)
->orLike('ssn_news_categories.name', $keyword)
->orLike('ssn_users.first_name', $keyword)
->orLike('ssn_users.last_name', $keyword)
->groupBy('title')
->having('ssn_news_items.trash', '0');
}
if ($limit != false) {
$builder->where('ssn_news_items.active', '1');
$builder->limit($limit);
}
$builder->orderBy('ssn_news_items.publish_up', 'DESC');
return $builder;
}
public function save_artikel($data)
{
$query = $this->db->table('ssn_news_items')->insert($data);
return $query;
}
public function update_artikel($data, $id)
{
$query = $this->db->table('ssn_news_items')->update($data, array('id' => $id));
return $query;
}
public function delete_artikel($data, $id)
{
$query = $this->db->table('ssn_news_items')->update($data, array('id' => $id));
return $query;
}
// fungsi tags
public function get_tags($text)
{
$builder = $this->db->table('ssn_news_tags');
$builder->select('ssn_news_tags.*');
$builder->where('ssn_news_tags.name', $text);
return $builder->get();
}
public function get_tags_by_artikel($itemsid)
{
$builder = $this->db->table('ssn_news_tags_xref');
$builder->select('ssn_news_tags_xref.*,ssn_news_tags.name');
$builder->join('ssn_news_tags', 'ssn_news_tags.id=ssn_news_tags_xref.tag_id', 'left');
$builder->where('ssn_news_tags_xref.items_id', $itemsid);
$builder->where('ssn_news_tags_xref.active', '1');
$builder->where('ssn_news_tags_xref.trash', '0');
return $builder->get();
}
public function get_tags_xref($itemsid, $tagid)
{
$builder = $this->db->table('ssn_news_tags_xref');
$builder->select('ssn_news_tags_xref.*');
$builder->where('ssn_news_tags_xref.items_id', $itemsid);
$builder->where('ssn_news_tags_xref.tag_id', $tagid);
return $builder->get();
}
public function save_tags($data)
{
$query = $this->db->table('ssn_news_tags')->insert($data);
return $query;
}
public function save_tags_xref($data)
{
$query = $this->db->table('ssn_news_tags_xref')->insert($data);
return $query;
}
public function update_tags($data, $id)
{
$query = $this->db->table('ssn_news_tags_xref')->update($data, array('id' => $id));
return $query;
}
public function delete_tags($data, $id)
{
$query = $this->db->table('ssn_news_tags_xref')->update($data, array('items_id' => $id));
return $query;
}
////////////////////////////////////////////////////////////////////////////////////////
public function get_artikel_perbulan($thn, $bln)
{
$builder = $this->db->table('ssn_news_items');
$builder->where('ssn_news_items.active', '1');
$builder->where('ssn_news_items.trash', '0');
$builder->where('year(ssn_news_items.publish_up)', $thn);
$builder->where('month(ssn_news_items.publish_up)', $bln);
$builder->where('ssn_news_items.published', '1');
return $builder->countAllResults();
}
public function get_artikel_pertahun($thn)
{
$builder = $this->db->table('ssn_news_items');
$builder->where('ssn_news_items.active', '1');
$builder->where('ssn_news_items.trash', '0');
$builder->where('year(ssn_news_items.publish_up)', $thn);
$builder->where('ssn_news_items.published', '1');
return $builder->countAllResults();
}
public function get_data_perbulan($thn, $bln)
{
$builder = $this->db->table('ssn_news_items');
$builder->select('DAYOFWEEK(date_format(`publish_up`,"%Y%m%d")) as nhari
,date_format(`publish_up`,"%Y%m%d") as tgl
,date_format(`publish_up`,"%d") as ntgl
,date_format(`publish_up`,"%M") as bln
,WEEK(`publish_up`) as nweek
,sum(published) as jml ');
$builder->where('ssn_news_items.published', '1');
$builder->where('ssn_news_items.active', '1');
$builder->where('ssn_news_items.trash', '0');
$builder->where('year(ssn_news_items.publish_up)', $thn);
$builder->where('month(ssn_news_items.publish_up)', $bln);
$builder->where('ssn_news_items.published', '1');
$builder->GroupBy('date_format(ssn_news_items.publish_up,"%Y%m%d")');
$result = $builder->get();
return $result;
}
public function get_data_pertahun($thn)
{
$builder = $this->db->table('ssn_news_items');
$builder->select('DAYOFWEEK(date_format(`publish_up`,"%Y%m")) as nhari
,date_format(`publish_up`,"%Y%m") as tgl
,date_format(`publish_up`,"%m") as ntgl
,date_format(`publish_up`,"%M") as bln
,sum(published) as jml ');
$builder->where('ssn_news_items.published', '1');
$builder->where('ssn_news_items.active', '1');
$builder->where('ssn_news_items.trash', '0');
$builder->where('year(ssn_news_items.publish_up)', $thn);
$builder->GroupBy('date_format(ssn_news_items.publish_up,"%Y%m%")');
$result = $builder->get();
return $result;
}
public function get_view_perbulan($thn, $bln)
{
$builder = $this->db->table('ssn_news_hits');
$builder->select('DAYOFWEEK(date_format(`created`,"%Y%m%d")) as nhari
,date_format(`created`,"%Y%m%d") as tgl
,date_format(`created`,"%d") as ntgl
,date_format(`created`,"%M") as bln
,WEEK(`created`) as nweek
,sum(hits) as jml ');
$builder->where('ssn_news_hits.active', '1');
$builder->where('ssn_news_hits.trash', '0');
$builder->where('year(ssn_news_hits.created)', $thn);
$builder->where('month(ssn_news_hits.created)', $bln);
$builder->GroupBy('date_format(ssn_news_hits.created,"%Y%m%d")');
$result = $builder->get();
return $result;
}
public function get_view_pertahun($thn)
{
$builder = $this->db->table('ssn_news_hits');
$builder->select('DAYOFWEEK(date_format(`created`,"%Y%m")) as nhari
,date_format(`created`,"%Y%m") as tgl
,date_format(`created`,"%m") as ntgl
,date_format(`created`,"%M") as bln
,sum(hits) as jml ');
$builder->where('ssn_news_hits.active', '1');
$builder->where('ssn_news_hits.trash', '0');
$builder->where('year(ssn_news_hits.created)', $thn);
$builder->GroupBy('date_format(ssn_news_hits.created,"%Y%m")');
$result = $builder->get();
return $result;
}
public function get_hits_artikel($id = false, $keyword = false, $limit = false, $thn = false, $bln = false, $order = false)
{
$builder = $this->select('ssn_news_items.*,
ssn_news_items.hits as total_hits,
(SELECT SUM(hits)
FROM ssn_news_hits
WHERE ssn_news_hits.items_id = ssn_news_items.id
AND ssn_news_hits.active = 1
AND ssn_news_hits.trash = 0
AND YEAR(ssn_news_hits.created) = ' . $this->db->escape($thn) . '
AND MONTH(ssn_news_hits.created) = ' . $this->db->escape($bln) . ') as jml');
$builder->where('ssn_news_items.trash', '0');
if ($id != false) {
$builder->where('ssn_news_items.id', $id);
}
if ($keyword != false) {
$builder->like('title', $keyword)
->groupBy('title')
->having('ssn_news_items.trash', '0');
}
if ($limit != false) {
// $builder->where('ssn_news_items.active', '1');
$builder->limit($limit);
}
if ($order != false) {
$builder->orderBy($order, 'DESC');
} else {
$builder->orderBy('ssn_news_items.hits', 'DESC');
}
return $builder;
}
}
?>