public static function run() { $data['feedurl'][] = file_get_contents('http://headlines.yahoo.co.jp/rss/goal-c_spo.xml'); $data['feedurl'][] = file_get_contents('http://headlines.yahoo.co.jp/rss/gekisaka-c_spo.xml'); $data['feedurl'][] = file_get_contents('http://zasshi.news.yahoo.co.jp/rss/soccermzw-all.xml'); $data['feedurl'][] = file_get_contents('http://zasshi.news.yahoo.co.jp/rss/footballc-all.xml'); $data['feedurl'][] = file_get_contents('http://zasshi.news.yahoo.co.jp/rss/fballista-all.xml'); $data['feedurl'][] = file_get_contents('http://www.nikkansports.com/soccer/world/atom.xml'); $data['feedurl'][] = file_get_contents('http://rss.rssad.jp/rss/number/jfootball'); $data['feedurl'][] = file_get_contents('http://rss.rssad.jp/rss/number/wfootball'); $data['feedurl'][] = file_get_contents('http://www.soccer-king.jp/RSS.rdf'); $data['feedurl'][] = file_get_contents('http://murayaman.blog72.fc2.com/?xml'); $kaisya = array('GOAL - スポーツ - Yahoo!ニュース', 'ゲキサカ - スポーツ - Yahoo!ニュース', 'Soccer Magazine ZONE web - 雑誌 - Yahoo!ニュース', 'フットボールチャンネル - 雑誌 - Yahoo!ニュース', 'footballista - 雑誌 - Yahoo!ニュース', '海外サッカー - nikkansports.com', 'サッカー日本代表 - Number Web', '海外サッカー - Number Web', 'サッカーキング - ニュース', '村山義光ライブスケジュール・ブログ'); $count = count($data['feedurl']); for ($i = 0; $i < $count; $i++) { $rss['feed'][] = Format::forge($data['feedurl'][$i], 'xml')->to_array(); } for ($i = 0; $i < count($rss['feed']); $i++) { if (isset($rss['feed'][$i]['channel']['item'])) { $data['company'] = $rss['feed'][$i]['channel']['title']; for ($y = 0; $y < 5; $y++) { $data['title'][] = $rss['feed'][$i]['channel']['item'][$y]['title']; $data['date'][] = date("Y年 n月 j日", strtotime($rss['feed'][$i]['channel']['item'][$y]['pubDate'])); $data['link'][] = $rss['feed'][$i]['channel']['item'][$y]['link']; if (strstr($kaisya[$i], $data['company'])) { $data['company_id'][] = $i; } } } elseif (isset($rss['feed'][$i]['entry'])) { $data['company'] = $rss['feed'][$i]['title']; for ($y = 0; $y < 5; $y++) { $data['title'][] = $rss['feed'][$i]['entry'][$y]['title']; $data['date'][] = date("Y年 n月 j日", strtotime($rss['feed'][$i]['entry'][$y]['published'])); $data['link'][] = $rss['feed'][$i]['entry'][$y]['id']; if (strstr($kaisya[$i], $data['company'])) { $data['company_id'][] = $i; } } } else { $data['company'] = $rss['feed'][$i]['channel']['title']; for ($y = 0; $y < 5; $y++) { $data['title'][] = $rss['feed'][$i]['item'][$y]['title']; $data['date'][] = date("Y年 n月 j日"); $data['link'][] = $rss['feed'][$i]['item'][$y]['link']; if (strstr($kaisya[$i], $data['company'])) { $data['company_id'][] = $i; } } } } for ($i = 0; $i < count($data['title']); $i += 5) { for ($y = 0; $y < 5; $y++) { $dbdata = array('title' => $data['title'][$y + $i], 'company_id' => $data['company_id'][$i + $y], 'link' => $data['link'][$y + $i]); $dbdata['title'] = str_replace("'", "\\'", $dbdata['title']); $sql = "INSERT IGNORE INTO soccer (title,company_id,link) values('" . $dbdata['title'] . "','" . $dbdata['company_id'] . "','" . $dbdata['link'] . "')"; $query = DB::query($sql); $result = $query->execute(); } } }
public static function query($sql, $type = NULL) { //TODO: unsatisfactory implementation now. // $query = new Query(); // return $query->parse_sql($sql); return parent::query($sql, $type); }
public static function get_content($data) { $type = $data['type']; $query = "\n SELECT content FROM recruitment\n WHERE type = '{$type}'\n "; $dbResult = DB::query($query)->execute(); $data = $dbResult->as_array(); if (empty($data)) { $data['content'] = ''; return $data; } else { return $data[0]; } }
public static function get_content($data) { $faculty = $data['faculty']; $type = $data['type']; $query = "\n SELECT content FROM admissions_faculty\n WHERE faculty_id = '{$faculty}' AND\n name = '{$type}'\n\n "; $dbResult = DB::query($query)->execute(); $data = $dbResult->as_array(); if (empty($data)) { $data['content'] = ''; return $data; } else { return $data[0]; } }
public static function get_auto_complete_list() { $table_name = Model_Project::table(); $sql = <<<SQL SELECT name FROM {$table_name} ORDER BY name ASC SQL; $result = \Fuel\Core\DB::query($sql)->execute(); $auto_complete_list = array(); foreach ($result->as_array() as $index => $array) { foreach ($array as $project_name) { $auto_complete_list[] = $project_name; } } return $auto_complete_list; }
/** * @author dangbc <*****@*****.**> * save data plan */ public function save_plan($current_date, $data_post = array()) { try { \Fuel\Core\DB::start_transaction(); $delete = \Fuel\Core\DB::query('DELETE FROM plan where start_date = :start_date')->bind('start_date', $current_date)->execute(); $plan = \Fuel\Core\DB::insert(self::$_table_name)->columns(array('start_date', 'area_id', 'job_cost', 'expenses', 'created_at', 'updated_at')); foreach ($data_post['area_id'] as $k => $v) { $plan->values(array($current_date, $k, $data_post['job_cost'][$k] == '' ? 0 : $data_post['job_cost'][$k], $data_post['expenses'][$k] == '' ? 0 : $data_post['expenses'][$k], date('Y-m-d h:s'), date('Y-m-d h:s'))); } if (!$plan->execute()) { \Fuel\Core\DB::rollback_transaction(); return false; } \Fuel\Core\DB::commit_transaction(); return true; } catch (Exception $ex) { \Fuel\Core\DB::rollback_transaction(); return false; } }
/** * @author NamDD <*****@*****.**> * @param type $id * @return boolean */ public function delete_data($job_id) { $job_id = (int) $job_id; return \Fuel\Core\DB::query('DELETE FROM ' . self::$_table_name . ' WHERE job_id = ' . $job_id)->execute(); }
/** * @author Thuanth6589 <*****@*****.**> * delete post by media id * @param $media_id * @return object */ public function delete_by_media($media_id, $post_id) { $post_id = empty($post_id) ? array(0) : $post_id; $delete = \Fuel\Core\DB::query('DELETE FROM m_post where m_media_id = :media_id AND post_id NOT IN :post_id')->bind('media_id', $media_id)->bind('post_id', $post_id)->execute(); return $delete; }
public function _where($filter = array()) { $is_where = DB::select('person.*', DB::expr('person.name as p_name'), 'employment.contact_result', 'employment.review_date', 'employment.classification', 'employment.adoption_result', 'employment.registration_expiration', 'employment.rank', 'employment.register_date', 'employment.contract_date', 'employment.contract_result', 'employment.hire_date', 'employment.work_confirmation', 'employment.employee_code', 'employment.code_registration_date', 'm_ss.ss_name', 'm_partner.branch_name', 'job.job_id', DB::expr('m_group.name as g_name'), 'm_partner.department_id', 'm_user.name', 'sssale.sale_name', 'sssale.sale_type', array('m_group.name', 'm_group_name'), array('sssale2.sale_name', 'job_sale_name'), array('m_ss2.ss_name', 'job_ss_name'))->from('person')->join('job', 'LEFT')->on('job.job_id', '=', 'person.job_id'); $is_where->join('sssale', 'LEFT')->on('person.sssale_id', '=', 'sssale.sssale_id'); $is_where->join('m_ss', 'LEFT')->on('m_ss.ss_id', '=', 'sssale.ss_id'); $is_where->join('m_partner', 'LEFT')->on('m_partner.partner_code', '=', 'm_ss.partner_code'); $is_where->join('m_group', 'LEFT')->on('m_partner.m_group_id', '=', 'm_group.m_group_id'); $is_where->join('employment', 'LEFT')->on('employment.person_id', '=', 'person.person_id'); $is_where->join('m_user', 'LEFT')->on('m_partner.user_id', '=', 'm_user.user_id'); $is_where->join(array('sssale', 'sssale2'), 'LEFT')->on('job.sssale_id', '=', 'sssale2.sssale_id'); $is_where->join(array('m_ss', 'm_ss2'), 'LEFT')->on('sssale2.ss_id', '=', 'm_ss2.ss_id'); $is_where->order_by('person_id', 'desc'); $is_where->group_by('person_id'); if (isset($filter['addr1']) && $filter['addr1'] != '') { $is_where->and_where('person.addr1', 'like', '%' . $filter['addr1'] . '%'); } if (isset($filter['addr2']) && $filter['addr2'] != '') { $arr_addr = array_filter(preg_split('/\\s|\\s+| /', trim($filter['addr2']))); $is_where->and_where_open(); $is_where->and_where_open(); foreach ($arr_addr as $k => $v) { $is_where->where(\Fuel\Core\DB::expr('CONCAT(person.addr2, person.addr3)'), 'like', '%' . $v . '%'); } $is_where->and_where_close(); $is_where->and_where_close(); } if (isset($filter['status']) && $filter['status'] != '') { $is_where->where('person.status', '=', $filter['status']); } if (isset($filter['email']) && $filter['email'] != '') { $is_where->and_where_open(); $is_where->where('mail_addr1', 'LIKE', '%' . $filter['email'] . '%')->or_where('mail_addr2', 'LIKE', '%' . $filter['email'] . '%'); $is_where->and_where_close(); } if (isset($filter['name']) && $filter['name'] != '') { $is_where->and_where_open(); $is_where->where('person.name', 'LIKE', '%' . $filter['name'] . '%'); $is_where->or_where('person.name_kana', 'LIKE', '%' . $filter['name'] . '%'); $is_where->and_where_close(); } if (isset($filter['phone']) && $filter['phone'] != '') { $is_where->and_where_open(); $is_where->where('person.tel', 'LIKE', '%' . $filter['phone'] . '%')->or_where('person.mobile', 'LIKE', '%' . $filter['phone'] . '%'); $is_where->and_where_close(); } if (isset($filter['group_id']) && $filter['group_id'] != '') { $is_where->and_where_open(); $is_where->where('m_partner.m_group_id', '=', $filter['group_id']); $is_where->and_where_close(); } if (isset($filter['partner_code']) && $filter['partner_code'] != '') { $is_where->and_where_open(); $is_where->where('m_ss.partner_code', '=', $filter['partner_code']); $is_where->and_where_close(); } if (isset($filter['ss_id']) && $filter['ss_id'] != '') { $is_where->and_where_open(); $is_where->where('sssale.ss_id', '=', $filter['ss_id']); $is_where->and_where_close(); } if (isset($filter['ss_name']) && $filter['ss_name'] != '') { $is_where->and_where_open(); $is_where->where('ss_name', 'LIKE', '%' . $filter['ss_name'] . '%'); $is_where->and_where_close(); } if (isset($filter['branch_name']) && $filter['branch_name'] != '') { $is_where->and_where_open(); $is_where->where('branch_name', 'LIKE', '%' . $filter['branch_name'] . '%'); $is_where->and_where_close(); } if (isset($filter['to_date']) && $filter['to_date'] != '') { $filter_date = strtotime($filter['to_date']) + 86399; $date_to = date('Y-m-d H:i:s', $filter_date); } if (isset($filter['from_date']) && isset($filter['to_date']) && $filter['from_date'] != '' && $filter['to_date'] == '') { $is_where->and_where_open(); $is_where->where('application_date', '>=', $filter['from_date']); $is_where->and_where_close(); } if (isset($filter['to_date']) && $filter['from_date'] == '' && $filter['to_date'] != '') { $is_where->and_where_open(); $is_where->and_where('application_date', '<=', $date_to); $is_where->and_where_close(); } if (isset($filter['from_date']) && $filter['from_date'] != '' && $filter['to_date'] != '') { $is_where->and_where_open(); $is_where->where('application_date', '>=', $filter['from_date']); $is_where->and_where('application_date', '<=', $date_to); $is_where->and_where_close(); } if (isset($filter['gender']) && $filter['gender'] != '') { $is_where->and_where_open(); $is_where->where('gender', 'IN', $filter['gender']); $is_where->and_where_close(); } if (isset($filter['age_from']) && $filter['age_from'] != '' && $filter['age_to'] != '' && isset($filter['age_to'])) { $is_where->and_where_open(); $is_where->where(DB::expr("DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d'))"), '>=', $filter['age_from']); $is_where->where(DB::expr("DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d'))"), '<=', $filter['age_to']); $is_where->and_where_close(); } elseif (isset($filter['age_from']) && $filter['age_from'] != '' && $filter['age_to'] == '') { $is_where->and_where_open(); $is_where->where(DB::expr("DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d'))"), '>=', $filter['age_from']); $is_where->and_where_close(); } elseif (isset($filter['age_to']) && $filter['age_to'] != '' && $filter['age_from'] == '') { $is_where->and_where_open(); $is_where->where(DB::expr("DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d'))"), '<=', $filter['age_to']); $is_where->and_where_close(); } $datas = array(); for ($i = 1; $i <= 3; $i++) { $datas['license' . $i] = ''; if (isset($filter['license' . $i])) { $is_where->and_where_open(); foreach ($filter['license' . $i] as $key => $value) { $datas['license' . $i] .= ',' . $value; } if (isset($datas['license' . $i]) && $datas['license' . $i] != '') { $x = explode(',', $datas['license' . $i]); unset($x[0]); foreach ($x as $key => $value) { $is_where->or_where(DB::expr('FIND_IN_SET("' . $value . '",license' . $i . ')')); } } $is_where->and_where_close(); } } if (isset($filter['review_result']) && $filter['review_result'] != '') { $is_where->and_where_open(); if (isset($filter['review_result'][0]) && !isset($filter['review_result'][1])) { $is_where->where('review_result', '=', 1); } elseif (!isset($filter['review_result'][0]) && isset($filter['review_result'][1])) { $is_where->where('review_result', '<>', 1); $is_where->or_where('review_result', 'is', null); } else { $is_where->where(DB::expr(1, '=', 1)); } $is_where->and_where_close(); } if (isset($filter['rank']) && $filter['rank'] != '') { $is_where->and_where_open(); $is_where->where('rank', 'IN', $filter['rank']); $is_where->and_where_close(); } if (isset($filter['contract_result']) && $filter['contract_result'] != '') { $is_where->and_where_open(); $is_where->where('contract_result', 'IN', $filter['contract_result']); $is_where->and_where_close(); } if (isset($filter['adoption_result']) && $filter['adoption_result'] != '') { $is_where->and_where_open(); $is_where->where('adoption_result', 'IN', $filter['adoption_result']); $is_where->and_where_close(); } if (isset($filter['user_id']) && $filter['user_id'] != '') { $is_where->and_where_open(); $is_where->where('m_partner.user_id', '=', $filter['user_id']); $is_where->and_where_close(); } if (isset($filter['department']) && $filter['department'] != '' && $filter['user_id'] == '') { $list_user_id = array(); $model_user = new \Model_Muser(); $list_users = $model_user->get_list_user_by_departmentid($filter['department']); foreach ($list_users as $key => $val) { $list_user_id[] = $val['user_id']; } if (count($list_user_id)) { $is_where->and_where_open(); $is_where->where('m_partner.user_id', 'in', $list_user_id); $is_where->and_where_close(); } } if (isset($filter['media_name']) && $filter['media_name'] != '') { $model_m_media = new \Model_Mmedia(); $model_m_post = new \Model_Mpost(); $model_order = new \Model_Orders(); $media_id_list = $model_m_media->get_media_id_list_by_name($filter['media_name']); if (count($media_id_list) == 0) { $is_where->and_where_open(); $is_where->where('person.order_id', '=', '-1'); $is_where->and_where_close(); return $is_where; } foreach ($media_id_list as $key => $value) { $media_id_list[$key] = $value['m_media_id']; } $post_id_list = $model_m_post->get_list_post_id($media_id_list); if (count($post_id_list) == 0) { $is_where->and_where_open(); $is_where->where('person.order_id', '=', '-1'); $is_where->and_where_close(); return $is_where; } foreach ($post_id_list as $key => $value) { $post_id_list[$key] = $value['post_id']; } $order_id_list = $model_order->get_order_id_list($post_id_list); if (count($order_id_list) == 0) { $is_where->and_where_open(); $is_where->where('person.order_id', '=', '-1'); $is_where->and_where_close(); return $is_where; } foreach ($order_id_list as $key => $value) { $order_id_list[$key] = $value['order_id']; } $is_where->and_where_open(); $is_where->where('person.order_id', 'in', $order_id_list); $is_where->and_where_close(); } if (isset($filter['job_id']) && !empty($filter['job_id'])) { $is_where->where('person.job_id', '=', $filter['job_id']); } if (isset($filter['order_id']) && $filter['order_id']) { $is_where->where('person.order_id', '=', $filter['order_id']); } if (isset($filter['array_person'])) { $filter['array_person'] = empty($filter['array_person']) ? array(0) : $filter['array_person']; $is_where->where('person.person_id', 'in', $filter['array_person']); } if (isset($filter['reprinted_via']) && $filter['reprinted_via']) { $is_where->where('person.reprinted_via', '=', $filter['reprinted_via']); } if (isset($filter['sale_type']) && $filter['sale_type']) { $sql = 'select sssale_id from sssale where sale_type = ' . $filter['sale_type']; $rs = \Fuel\Core\DB::query($sql)->execute(); $sssale_id = [-1]; foreach ($rs as $item) { $sssale_id[] = $item['sssale_id']; } $is_where->where('person.sssale_id', 'in', $sssale_id); } if (isset($filter['limit'])) { $is_where->limit($filter['limit']); } if (isset($filter['offset'])) { $is_where->offset($filter['offset']); } return $is_where; }
public function get_all_ss() { $query = \Fuel\Core\DB::query('SELECT ss_id,ss_name FROM m_ss'); return $query->execute()->as_array(); }
private static function DB_Query() { //for test , to make sure that my DB is connected $_sql = 'SELECT * FROM Guest'; $_query = DB::query($_sql); $_guestArray = $_query->execute()->as_array(); //var_dump($_guestArray); return $_guestArray; }
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(); }