/** * @author Thuanth6589 <*****@*****.**> * @param array $filters * @return $this */ private function _get_where($filters = array()) { $query = \Fuel\Core\DB::select('m_ss.*', 'm_partner.branch_name', 'm_group.name')->from('m_ss'); $query->join('m_partner', 'left')->on('m_ss.partner_code', '=', 'm_partner.partner_code'); $query->join('m_group', 'left')->on('m_group.m_group_id', '=', 'm_partner.m_group_id'); if (isset($filters['department_id']) && $filters['department_id']) { $query->where('m_partner.department_id', '=', $filters['department_id']); } if (isset($filters['addr1']) && $filters['addr1']) { $query->where('m_ss.addr1', '=', $filters['addr1']); } if (isset($filters['addr2']) && $filters['addr2']) { $query->where('m_ss.addr2', '=', $filters['addr2']); } if (isset($filters['group_id']) && $filters['group_id']) { $query->where('m_partner.m_group_id', '=', $filters['group_id']); } if (isset($filters['partner_code']) && $filters['partner_code']) { $query->where('m_ss.partner_code', '=', $filters['partner_code']); } if (isset($filters['base_code']) && $filters['base_code'] !== '') { $query->where('m_ss.base_code', '=', $filters['base_code']); } if (isset($filters['status']) && $filters['status'] !== '') { $query->where('m_ss.status', '=', $filters['status']); } if (isset($filters['is_available']) && $filters['is_available'] !== '') { $query->where('m_ss.is_available', '=', $filters['is_available']); } if (isset($filters['keyword']) && $filters['keyword']) { $arr_keyword = array_filter(preg_split('/\\s|\\s+| /', trim($filters['keyword']))); $query->and_where_open(); $query->and_where_open(); foreach ($arr_keyword as $k => $v) { $query->where(\Fuel\Core\DB::expr('CONCAT(m_group.name, m_partner.branch_name)'), 'like', '%' . $v . '%'); } $query->and_where_close(); $query->and_where_close(); } if (isset($filters['department_id']) && $filters['department_id']) { $partner_code = Model_Mpartner::get_partnercode_department($filters['department_id']); if (empty($partner_code)) { $partner_code = array(''); } $query->where('m_ss.partner_code', 'IN', $partner_code); } if (isset($filters['limit']) && $filters['limit']) { $query->limit($filters['limit']); } if (isset($filters['offset']) && $filters['offset']) { $query->offset($filters['offset']); } $query->order_by('m_ss.ss_id', 'desc'); return $query; }
public static function get_all_order_list($limit = null, $offset = null, $search) { $model_ss = new \Model_Mss(); $model_partner = new Model_Mpartner(); $model_user = new \Model_Muser(); $query = DB::select(DB::expr('*,(SELECT name FROM m_user WHERE m_user.user_id = orders.author_user_id) as user_name'))->from(self::$_table_name); if (isset($search['order_user_id']) && $search['order_user_id'] != '') { $query->and_where('order_user_id', '=', $search['order_user_id']); } else { if (isset($search['order_department']) && $search['order_department'] != null) { $list_order_user_id = array(-1); $list_users = $model_user->get_list_user_by_departmentid($search['order_department']); foreach ($list_users as $key => $val) { $list_order_user_id[] = $val['user_id']; } $query->and_where('order_user_id', 'IN', $list_order_user_id); } } if (isset($search['ssid']) && $search['ssid'] != null) { $query->and_where('ss_id', $search['ssid']); } if (isset($search['group']) && $search['group'] != null) { $sql = 'select m_ss.ss_id from m_ss inner join m_partner on (m_ss.partner_code = m_partner.partner_code) where m_partner.m_group_id = :group_id'; $group_id = Fuel\Core\Input::get('group_search'); $_array = Fuel\Core\DB::query($sql)->bind('group_id', $search['group'])->execute()->as_array(); if (count($_array) == 0) { return array(); } $query->and_where('ss_id', 'in', array_column($_array, 'ss_id')); } elseif (isset($search['partner']) && $search['partner'] != null) { $list_ss_id_partner = array(); $list_ss = $model_ss->get_data(array('partner_code' => $search['partner'])); foreach ($list_ss as $key => $val) { $list_ss_id_partner[] = $val->ss_id; } if (count($list_ss_id_partner) <= 0) { return array(); } else { $query->and_where('ss_id', 'in', $list_ss_id_partner); } } elseif (isset($search['addr1']) && $search['addr1'] != null) { $list_ss_id_addr1 = array(); $list_partner_code = array(); $list_partner = $model_partner->get_filter_partner(array('addr1' => $search['addr1'])); foreach ($list_partner as $key => $value) { $list_partner_code[] = $value['partner_code']; } $config['where'][] = array('partner_code', 'IN', $list_partner_code); if (count($list_partner_code) <= 0) { return array(); } else { $list_ss = \Model_Mss::forge()->find($config); if ($list_ss) { foreach ($list_ss as $key => $val) { $list_ss_id_addr1[] = $val->ss_id; } } if (count($list_ss_id_addr1) <= 0) { return array(); } else { $query->and_where('ss_id', 'in', $list_ss_id_addr1); } } } if (isset($search['apply_date']) && $search['apply_date'] != null) { $query->where(DB::expr("DATE_FORMAT(apply_date,'%Y-%m-%d') >= '" . $search['apply_date'] . "'")); } if (isset($search['post_date']) && $search['post_date'] != null) { $query->where(DB::expr("DATE_FORMAT(apply_date,'%Y-%m-%d') <= '" . $search['post_date'] . "'")); } if (isset($search['media_id']) && $search['media_id'] != null) { $model_post = new Model_Mpost(); $list_post = $model_post->get_list_by_media($search['media_id']); if ($list_post) { $list_post_id = array_column($list_post, 'post_id'); $query->and_where('post_id', 'in', $list_post_id); } } if (isset($search['maddr2']) && $search['maddr2'] != null) { $list_ss_id_add2 = array(); $listss = $model_ss->get_list_all_ss(array('addr2' => $search['maddr2'])); foreach ($listss as $key => $val) { $list_ss_id_add2[] = $val['ss_id']; } if (count($list_ss_id_add2)) { $query->and_where('ss_id', 'in', $list_ss_id_add2); } } //get follow author_user_id if (isset($search['user_id']) && $search['user_id'] != null) { $list_ss_id_user_temp[] = -1; $list_ss_id_user = self::find_ss_list($search['user_id']); if ($list_ss_id_user) { $query->and_where('ss_id', 'IN', $list_ss_id_user); } else { $query->and_where('ss_id', 'IN', $list_ss_id_user_temp); } } elseif (isset($search['department']) && $search['department'] != null) { $list_user_id = array(); $list_users = $model_user->get_list_user_by_departmentid($search['department']); foreach ($list_users as $key => $val) { $list_user_id[] = $val['user_id']; } $list_partner_code = array(); if ($list_user_id) { $config_partner['where'][] = array('user_id', 'IN', $list_user_id); $list_user_partner = \Model_Mpartner::forge()->find($config_partner); if ($list_user_partner) { foreach ($list_user_partner as $partner) { $list_partner_code[] = $partner['partner_code']; } } } $list_ss_id_partner[] = -1; if ($list_partner_code) { $config_partner_ss['where'][] = array('partner_code', 'IN', $list_partner_code); $list_ss_partner = \Model_Mss::forge()->find($config_partner_ss); if ($list_ss_partner) { foreach ($list_ss_partner as $ss_id) { $list_ss_id_partner[] = $ss_id['ss_id']; } } } $query->and_where('ss_id', 'IN', $list_ss_id_partner); } if (isset($search['department_id']) && $search['department_id'] != null) { $list_partnercode_department = array(); $list_partner_department = $model_partner->get_partnercode_department($search['department_id']); foreach ($list_partner_department as $key => $val) { $list_partnercode_department[] = $val['partner_code']; } $list_ss_id_deparment[] = -1; if ($list_partnercode_department) { $config_partner_department['where'][] = array('partner_code', 'IN', $list_partnercode_department); $list_ss_deparment = \Model_Mss::forge()->find($config_partner_department); if ($list_ss_deparment) { foreach ($list_ss_deparment as $ss_id) { $list_ss_id_deparment[] = $ss_id['ss_id']; } } } $query->and_where('ss_id', 'IN', $list_ss_id_deparment); } $or_where = null; $flag = 0; if (isset($search['unapproved']) && $search['unapproved'] == 0) { $or_where .= 'status = 0'; $flag += 1; } if (isset($search['approved']) && $search['approved'] == 1) { $or_where .= $flag > 0 ? ' OR status = 1' : 'status = 1'; $flag += 1; } if (isset($search['confirmed']) && $search['confirmed'] == 2) { $or_where .= $flag > 0 ? ' OR status = 2' : 'status = 2'; $flag += 1; } if (isset($search['nonapproved']) && $search['nonapproved'] == -1) { $or_where .= $flag > 0 ? ' OR status = -1' : 'status = -1'; $flag += 1; } if (isset($search['stop']) && $search['stop'] == 3) { $or_where .= $flag > 0 ? ' OR status = 3' : 'status = 3'; $flag += 1; } if ($or_where) { $query->and_where(DB::expr('(' . $or_where . ')')); } if (isset($search['order_id']) && $search['order_id']) { $query->and_where('order_id', '=', $search['order_id']); } if (isset($search['start_date']) && $search['start_date']) { $query->where('post_date', '>=', $search['start_date']); } if (isset($search['end_date']) && $search['end_date']) { $query->where('post_date', '<=', $search['end_date']); } if (isset($search['keyword']) && $search['keyword']) { $arr_keyword = array_filter(preg_split('/\\s|\\s+| /', trim($search['keyword']))); $sql = 'select orders.order_id from orders inner join m_ss on (orders.ss_id = m_ss.ss_id) inner join m_partner on (m_ss.partner_code = m_partner.partner_code) inner join sssale on (orders.agreement_type = sssale.sssale_id) left join m_post on (m_post.post_id = orders.post_id) left join m_media on (m_media.m_media_id = m_post.m_media_id)'; $where = ' WHERE '; foreach ($arr_keyword as $k => $v) { $where .= " CONCAT(m_ss.ss_name, m_partner.branch_name, IF(sssale.sale_name IS NULL,'' ,sssale.sale_name), IF(m_media.media_name IS NULL,'' ,m_media.media_name)) like '%{$v}%' AND"; } $where = trim($where, 'AND'); $sql = $sql . $where; $rs = \Fuel\Core\DB::query($sql)->execute(); $order_id = [-1]; foreach ($rs as $item) { $order_id[] = $item['order_id']; } $query->where('order_id', 'in', $order_id); } if (isset($search['sale_type']) && $search['sale_type']) { $sql = 'select sssale_id from sssale where sale_type = ' . $search['sale_type']; $rs = \Fuel\Core\DB::query($sql)->execute(); $sssale_id = [-1]; foreach ($rs as $item) { $sssale_id[] = $item['sssale_id']; } $query->where('agreement_type', 'in', $sssale_id); } if ($limit) { $query->limit($limit); } if ($offset) { $query->offset($offset); } $query->order_by('order_id', 'desc'); return $query->as_object('Model_Orders')->execute(); }