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