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