$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');
