$z = ''; $z2 = ''; $z3 = ''; if ($where_user) { $z = " AND (" . str_replace('u.', 'z.', $where_user) . ")"; } if ($where_agency) { $where .= " AND (" . str_replace('ag.', 'z.', $where_agency) . ")"; } if ($where_corporate) { $where .= " AND (" . str_replace('corp.', 'z.', $where_corporate) . ")"; } if ($where_provider) { $where .= " AND ({$where_provider})"; } $sql_balance = str_replace("z.", "z2.", get_sql_balance()); if ($allagency) { $sql = mysql_query("\n\t\tSELECT \n\t\t\tcount(distinct ag.agency_id) as allcount, count(distinct z2.order_id) as allpaidcount,\n\t\t\tsum({$field_sum2}) as inall_sum, sum(z2.person_count) as allpcount, sum(z.suite_count*z.day_count) as allnomeronochi,\n\t\t\tcount(distinct z3.order_id) as provider_count, sum(if({$field_sum3}>0, ({$sql_balance})/{$field_sum3}, 0)) as allkomis\n\t\tFROM \n\t\t\t" . TABLE_AGENCY . " ag\n\t\t\tLEFT JOIN " . TABLE_ORDER . " z on (ag.agency_id=z.agency_id {$z}) \n\t\t\t{$city_table}\n\t\t\tLEFT JOIN " . TABLE_PROVIDER . " pr on (pr.provider_id=z.provider_id)\n\t\t\tLEFT JOIN " . TABLE_ORDER . " z2 on (z2.order_id=z.order_id AND z2.status>=1 AND (z2.paid OR z2.prepaid) ) \n\t\t\tLEFT JOIN " . TABLE_ORDER . " z3 on (z3.order_id=z2.order_id AND z3.provider_sum>0) \n\t\tWHERE\n\t\t\t1 {$where}") or Error(1, __FILE__, __LINE__); $arr = mysql_fetch_array($sql); $replace['all'] = $all = $arr['allcount']; $replace['allsumma'] = $arr['inall_sum']; $replace['allpcount'] = $arr['allpcount']; $replace['allpaidcount'] = $arr['allpaidcount']; $replace['allnomeronochi'] = $arr['allnomeronochi']; $replace['allkomis'] = $arr['provider_count'] ? number_format($arr['allkomis'] * 100 / $arr['provider_count'], 1) : 0; list($limit, $replace['pages']) = pages($all, ADMIN_URL . "?p={$part}&all=1&"); $replace['current_page'] = $current_page; $sql = mysql_query("\n\t\tSELECT \n\t\t\tag.name as agency_name, ag.agency_id,\n\t\t\tcount(distinct z.order_id) as ord_count,\n\t\t\tsum(z2.person_count) as p_count,\n\t\t\tcount(distinct z1.order_id) as annul_count,\n\t\t\tcount(distinct z2.order_id) as paid_count,\n\t\t\tcount(distinct z3.order_id) as provider_count,\n\t\t\tsum({$field_sum2}) as inall_sum,\n\t\t\tsum(z.suite_count*z.day_count) as nomeronochi,\n\t\t\tsum(if({$field_sum3}>0, ({$sql_balance})/{$field_sum3}, 0)) as komis\n\t\tFROM \n\t\t\t" . TABLE_AGENCY . " ag\n\t\t\tLEFT JOIN " . TABLE_ORDER . " z on (ag.agency_id=z.agency_id {$z})\n\t\t\t{$city_table}\n\t\t\tLEFT JOIN " . TABLE_PROVIDER . " pr on (pr.provider_id=z.provider_id)\n\t\t\tLEFT JOIN " . TABLE_ORDER . " z1 on (z1.order_id=z.order_id AND z1.status<1) \n\t\t\tLEFT JOIN " . TABLE_ORDER . " z2 on (z2.order_id=z.order_id AND z2.status>=1 AND (z2.paid OR z2.prepaid) ) \n\t\t\tLEFT JOIN " . TABLE_ORDER . " z3 on (z3.order_id=z2.order_id AND z3.provider_sum>0) \n\t\tWHERE\n\t\t\t1 {$where}\n\t\tGROUP BY\n\t\t\tag.agency_id\n\t\tORDER BY \n\t\t\t{$ord}\n\t\tLIMIT {$limit}") or Error(1, __FILE__, __LINE__); } elseif ($allcorporate) { $sql = mysql_query("\n\t\tSELECT \n\t\t\tcount(distinct corp.corporate_id) as allcount, count(distinct z2.order_id) as allpaidcount,\n\t\t\tsum({$field_sum2}) as inall_sum, sum(z2.person_count) as allpcount, sum(z.suite_count*z.day_count) as allnomeronochi,\n\t\t\tcount(distinct z3.order_id) as provider_count, sum(if({$field_sum3}>0, ({$sql_balance})/{$field_sum3}, 0)) as allkomis\n\t\tFROM \n\t\t\t" . TABLE_CORPORATE . " corp\n\t\t\tLEFT JOIN " . TABLE_ORDER . " z on (corp.corporate_id=z.corporate_id {$z}) \n\t\t\t{$city_table}\n\t\t\tLEFT JOIN " . TABLE_PROVIDER . " pr on (pr.provider_id=z.provider_id)\n\t\t\tLEFT JOIN " . TABLE_ORDER . " z2 on (z2.order_id=z.order_id AND z2.status>=1 AND (z2.paid OR z2.prepaid) ) \n\t\t\tLEFT JOIN " . TABLE_ORDER . " z3 on (z3.order_id=z2.order_id AND z3.provider_sum>0) \n\t\tWHERE\n\t\t\t1 {$where}") or Error(1, __FILE__, __LINE__); $arr = mysql_fetch_array($sql);
$d_field = $datesel == 1 ? 'date_from' : 'date'; if (!$mindate) { $arr = $maxdate ? $maxdate : date("Y-m-d"); list($y, $m, $d) = split("-", $arr); $mindate = date("Y-m-d", mktime(0, 0, 0, $m - 6, $d, $y)); } if ($mindate) { $where .= " AND z.{$d_field}>='{$mindate}'"; } if ($maxdate) { $where .= " AND z.{$d_field}<='{$maxdate}'"; } $replace['mindate'] = $mindate ? sql_to_text_date($mindate, 0, 1) : ''; $replace['maxdate'] = $maxdate ? sql_to_text_date($maxdate, 0, 1) : ''; $sql_provider_sum = get_sql_provider_sum(); $sql_balance = get_sql_balance(); $sql = mysql_query("\n\t\tSELECT \n\t\t\tcount(distinct order_id) as allcount\n\t\tFROM \n\t\t\t" . TABLE_ORDER . " z \n\t\t\tLEFT JOIN " . TABLE_OFFICE . " r on (r.office_id=z.office_id) \n\t\t\tLEFT JOIN " . TABLE_OBJECT . " ob on (ob.object_id=z.object_id) \n\t\t\tLEFT JOIN " . TABLE_CITY . " ct ON (ob.city_id=ct.city_id)\n\t\t\tLEFT JOIN " . TABLE_AGENCY . " ag ON (ag.agency_id=z.agency_id)\n\t\t\tLEFT JOIN " . TABLE_CORPORATE . " cr ON (cr.corporate_id=z.corporate_id)\n\t\t\tLEFT JOIN " . TABLE_USER . " u on (u.user_id=z.user_id) \n\t\t\tLEFT JOIN " . TABLE_CLIENT . " cl on (cl.client_id=SUBSTRING_INDEX( z.clients, ',', 1 )) \n\t\tWHERE\n\t\t\t1 {$where}") or Error(1, __FILE__, __LINE__); $arr = mysql_fetch_array($sql); $replace['all'] = $all = $arr['allcount']; list($limit, $replace['pages']) = pages($all, ADMIN_URL . "?p={$part}&all=1&"); $replace['current_page'] = $current_page; $sql = mysql_query("\n\t\tSELECT \n\t\t\tz.order_id, z.date, z.status as orderstatus, z.date_from, z.note, {$sql_balance} as balance,\n\t\t\tz.client,\n\t\t\tz.annul, z.tphone, z.fio, z.tphone, z.card_id, z.card_vip, \n\t\t\tcl.phone as client_phone, cl.name as client_name, cl.birthday_city,\n\t\t\tr.region,\n\t\t\tob.name as object_name, \n\t\t\tct.city_id, ct.name as city_name, c.name as country_name, u.name as user_name,\n\t\t\tag.name as agency_name, cr.name as corporate_name, z.agency_id, cr.corporate_id\n\t\tFROM \n\t\t\t" . TABLE_ORDER . " z \n\t\t\tLEFT JOIN " . TABLE_OFFICE . " r on (r.office_id=z.office_id) \n\t\t\tLEFT JOIN " . TABLE_OBJECT . " ob on (ob.object_id=z.object_id) \n\t\t\tLEFT JOIN " . TABLE_CITY . " ct ON (ob.city_id=ct.city_id)\n\t\t\tLEFT JOIN " . TABLE_COUNTRY . " c ON (ct.country_id=c.country_id)\n\t\t\tLEFT JOIN " . TABLE_AGENCY . " ag ON (ag.agency_id=z.agency_id)\n\t\t\tLEFT JOIN " . TABLE_CORPORATE . " cr ON (cr.corporate_id=z.corporate_id)\n\t\t\tLEFT JOIN " . TABLE_USER . " u on (u.user_id=z.user_id) \n\t\t\tLEFT JOIN " . TABLE_CLIENT . " cl on (cl.client_id=SUBSTRING_INDEX( z.clients, ',', 1 )) \n\t\tWHERE\n\t\t\t1 {$where}\n\t\tGROUP BY\n\t\t\tz.order_id\n\t\tORDER BY \n\t\t\t{$ord}\n\t\tLIMIT {$limit}") or Error(1, __FILE__, __LINE__); $list = array(); $i = ($current_page - 1) * $_SESSION['on_page']; $j = 0; while ($info = @mysql_fetch_array($sql)) { $i++; $j++; $info['i'] = $i; $info['j'] = $j; $info['object_name'] = htmlspecialchars($info['object_name'], ENT_COMPAT, 'cp1251');