public function findMastersAdminFilter($category = 0, $city_id = 0, $country_id = 0, $sex = 0, $blocked = 0, $activated = 0, $checked = 0, $sort_field = "id", $sort_dir = "ASC", $find_string = "", $limit = 20, $page = 0)
{
$find_sql = " 1=1 ";
if ($city_id > 0) {
$find_sql .= " AND User.city_id = " . $city_id . "";
}
if ($country_id > 0) {
$find_sql .= " AND User.country_id = " . $country_id . "";
}
if ($sex == "1") {
$find_sql .= " AND User.sex = 1 ";
}
if ($sex == "2") {
$find_sql .= " AND User.sex = 2 ";
}
if ($blocked == 3) {
$find_sql .= " AND User.status = 3 ";
}
if ($checked == 1) {
$find_sql .= " AND User.data_status = 0 ";
}
if ($checked == 2) {
$find_sql .= " AND User.data_status = 1 ";
}
if ($activated == 1) {
$find_sql .= " AND User.status >= 1 ";
}
if ($activated == 2) {
$find_sql .= " AND User.status = 0 ";
}
$create_uid_sql = "";
if ($category > 0) {
App::import('model', 'Categories');
$cats = new Categories();
$cats->findRecursiveCategoriesByCategoryId($category);
$subcat = $cats->ids;
$subcat[] = $category;
if (count($subcat) > 0) {
App::import('model', 'ServiceModel');
$services_model = new ServiceModel();
$services_ids = array();
foreach ($subcat as $cat) {
$services_by_cats = $services_model->find('all', array('conditions' => array('category_id' => $cat)));
foreach ($services_by_cats as $service) {
$s_id = $service['services']['id'];
if (!in_array($s_id, $services_ids)) {
$services_ids[] = $s_id;
}
}
}
if (count($services_ids) > 0) {
$services_users_ids = array();
App::import('model', 'Servicetouser');
$servicetouser = new Servicetouser();
foreach ($services_ids as $s_id) {
$result = $servicetouser->find('all', array('conditions' => array('service_id' => $s_id)));
foreach ($result as $user) {
$master_id = $user['service_to_users']['user_id'];
if (!in_array($master_id, $services_users_ids)) {
$services_users_ids[] = $master_id;
}
}
}
$uid_count = count($services_users_ids);
if ($uid_count > 0) {
$create_uid_sql .= " AND (";
$c = 0;
foreach ($services_users_ids as $u_id) {
$c++;
if ($c == 1) {
$create_uid_sql .= "User.id = {$u_id} ";
} else {
$create_uid_sql .= " OR User.id = {$u_id} ";
}
}
$create_uid_sql .= ") ";
}
}
}
}
if (!empty($find_string)) {
$find_string = mb_strtolower($find_string);
$find_string_sql = " AND firstname LIKE '%{$find_string}%' OR " . " lastname LIKE '%{$find_string}%' OR " . " about_me LIKE '%{$find_string}%' OR " . " education LIKE '%{$find_string}%' OR " . " specialization LIKE '%{$find_string}%'";
} else {
$find_string_sql = "";
}
if (empty($create_uid_sql) and $category > 0) {
//если поиск мастера по категориям ничего не дал - возвращается пустой массив
return $result = array(0, 0);
} else {
if ($page <= 0 or !is_numeric($page)) {
$page = 1;
}
$limit_page = $limit * ($page - 1);
$order_sql = " ORDER BY {$sort_field} {$sort_dir} LIMIT {$limit_page}, {$limit}";
$query = "SELECT * FROM users AS User WHERE " . $find_sql . $find_string_sql . $create_uid_sql . $order_sql;
//echo $query;
$result_page = $this->query($query);
$query_total = "SELECT count(*) AS t_count FROM users AS User WHERE " . $find_sql . $find_string_sql . $create_uid_sql;
//echo $query_total;
$result_total = $this->query($query_total);
return array($result_total[0][0]['t_count'], $result_page);
}
}
public function findOrdersAdminFilter($category = 0, $city_id = 0, $country_id = 0, $master_status = "all", $admin_status = 'all', $payed = "all", $sort_field = "id", $sort_dir = "ASC", $find_string = "", $limit = 20, $page = 0)
{
$find_sql = " 1=1 ";
if ($city_id > 0) {
$find_sql .= " AND `Order`.city_id = " . $city_id . "";
}
if ($country_id > 0) {
$find_sql .= " AND `Order`.country_id = " . $country_id . "";
}
if ($payed !== "all") {
$find_sql .= " AND `Order`.`payed` = '{$payed}' ";
}
if ($admin_status !== "all") {
$find_sql .= " AND `Order`.status = '{$admin_status}' ";
}
if ($master_status !== "all") {
$find_sql .= " AND Order.id IN (SELECT order_id FROM user_orders AS MasterOrder WHERE `MasterOrder`.status = '{$master_status}') ";
}
//if ($activated == 2) $find_sql .= " AND User.status = 0 ";
$create_uid_sql = "";
if ($category > 0) {
App::import('model', 'Categories');
$cats = new Categories();
$cats->findRecursiveCategoriesByCategoryId($category);
$subcat = $cats->ids;
$subcat[] = $category;
if (count($subcat) > 0) {
App::import('model', 'ServiceModel');
$services_model = new ServiceModel();
$services_ids = array();
foreach ($subcat as $cat) {
$services_by_cats = $services_model->find('all', array('conditions' => array('category_id' => $cat)));
foreach ($services_by_cats as $service) {
$s_id = $service['services']['id'];
if (!in_array($s_id, $services_ids)) {
$services_ids[] = $s_id;
}
}
}
if (count($services_ids) > 0) {
$create_uid_sql .= " AND (";
$c = 0;
foreach ($services_ids as $s_id) {
$c++;
if ($c == 1) {
$create_uid_sql .= "`Order`.service_id = {$s_id} ";
} else {
$create_uid_sql .= " OR `Order`.service_id = {$s_id} ";
}
}
$create_uid_sql .= ") ";
}
}
}
if (!empty($find_string)) {
$find_string = mb_strtolower($find_string);
$find_string_sql = " AND\n\t\t\t\t\t`Order`.name LIKE '%{$find_string}%' OR " . " `Order`.phone LIKE '%{$find_string}%' OR " . " `Order`.mail LIKE '%{$find_string}%' OR " . " `Order`.text LIKE '%{$find_string}%'";
} else {
$find_string_sql = "";
}
if (empty($create_uid_sql) and $category > 0) {
//если поиск заявки по категориям ничего не дал - возвращается пустой массив
//die(1);
return $result = array(0, 0);
} else {
if ($page <= 0 or !is_numeric($page)) {
$page = 1;
}
$limit_page = $limit * ($page - 1);
if ($sort_field == "modified") {
$order_table = 'order_actions';
} else {
$order_table = 'Order';
}
$order_sql = " ORDER BY `{$order_table}`.{$sort_field} {$sort_dir} LIMIT {$limit_page}, {$limit}";
$query = "SELECT *, (SELECT max('created') FROM order_actions LIMIT 1) AS last_ops FROM `orders` AS `Order`\n\t\t\t WHERE " . $find_sql . $find_string_sql . $create_uid_sql . $order_sql;
//echo $query;
$result_page = $this->query($query);
$query_total = "SELECT count(*) AS t_count FROM `orders` AS `Order`\n\n\t\t\tWHERE " . $find_sql . $find_string_sql . $create_uid_sql;
//echo $query_total;
$result_total = $this->query($query_total);
$t_count = $result_total[0][0]['t_count'] ? $result_total[0][0]['t_count'] : 0;
return array($t_count, $result_page);
}
}
