function saveAveragePrices($start_date, $end_date)
{
global $db;
global $db2;
$prev_end_date = getNextDate($end_date, -1);
$sql = "SELECT rooms, ROUND(AVG(price)) price_avr, ROUND(AVG(total_area),1) area_avr, \n\t\tROUND(AVG(price_m),1) price_m_avr \n\t\tFROM flat f, tenement t WHERE f.updated_on>='{$start_date}' \n\t\t\tAND f.updated_on<'{$end_date}' AND total_area > 29 AND total_area < 400\n\t\t\tAND f.price>1000000 AND f.price < 6000000 AND f.tenement_id=t.id\n\t\t\tAND f.status IN (2,6)\n\t\t\tAND t.city_id=0 \n\t\tGROUP BY rooms";
$db->query($sql);
while ($row = $db->fetchRow()) {
$sql2 = "INSERT INTO average_flat_price VALUES \n\t\t('{$prev_end_date}','{$row['rooms']}','{$row['price_avr']}','{$row['area_avr']}','{$row['price_m_avr']}')";
$db2->query($sql2);
}
}
function map($s) {
$s->assign("lat",LAT_YOLA);
$s->assign("lon",LON_YOLA);
$s->assign("YANDEX_KEY",YANDEX_KEY);
$ids = '';
$rooms = '';
$prices = '';
$prices_m = '';
$areas = '';
$addresses = '';
$storeys = '';
$dates = '';
$types = '';
$lons = '';
$lats = '';
$photos = '';
$icons = '';
if (!isset($_REQUEST['act']) || $_REQUEST['act']=='sales') {
$status=REALTY_STATUS_SALE;
}
else {
$status=REALTY_STATUS_RENT;
}
$res = _filter($status);
$add_sql = "f.updated_on>'".getNextDate(date('Y-m-d'),-30)."' AND t.status=".REALTY_STATUS_ACTIVE.' AND '.$res['sql'];
$db_res = Land::getFullListLink($add_sql);
while ($row = $db_res->fetchRow()) {
$ids .= $row['id'].',';
$rooms .= $row['rooms'].',';
$prices .= "'".number_format($row['price'],0)."',";
$prices_m .= "'".number_format($row['price_m'],0)."',";
$areas .= "'".$row['total_area']."',";
$addr = "{$row['street']}, {$row['tnum']}";
$addresses .= "'".$addr."',";
$date = explode(' ',$row['updated_on']);
$ds = explode('-',$date[0]);
$date = $ds[2].'.'.$ds[1].'.'.$ds[0];
$dates .= "'".$date."',";
$storeys .= "'".$row['storey']."/".$row['storeys']."',";
$types .= "'".Tenement::$TYPE[$row['ttype']]."',";
$lons .= "'".$row['lon']."',";
$lats .= "'".$row['lat']."',";
$photo = ($row['photo_tenement']!='') ? $row['tenement_id']."/".$row['photo_tenement']."_prev" : '';
$photos .= "'".$photo."',";
if ($row['price_m']<30000) {
$color = 'a';
}
else if ($row['price_m']<40000) {
$color = 'b';
}
else {
$color = 'c';
}
$ri = ($row['rooms']<4) ? $row['rooms'] : 3;
$icons .= "'".$ri."k".$color."',";
}
$s->assign("ids",$ids);
$s->assign("rooms",$rooms);
$s->assign("prices",$prices);
$s->assign("prices_m",$prices_m);
$s->assign("areas",$areas);
$s->assign("addresses",$addresses);
$s->assign("storeys",$storeys);
$s->assign("dates",$dates);
$s->assign("types",$types);
$s->assign("lons",$lons);
$s->assign("lats",$lats);
$s->assign("photos",$photos);
$s->assign("icons",$icons);
$block_html = Html::getBlock('Квартиры на продажу на карте Йошкар-Олы',Html::pagelandMap());
$s->assign("block_html",$block_html);
$s->display("land_map.tpl");
}
insertQVar(2, 2, 1, 1);
insertQVar(3, 2, 2, 2);
insertStatistic(3, 'Not so interesting (1D)...', 1, 1, $dates[3]);
insertUVar(3, 3, 'AGE', 0);
insertTracking(1, 2, true, $dates[1]);
insertTracking(1, 3, false, getNextDate($phpdate));
insertTracking(2, 1, true, getNextDate($phpdate));
insertTracking(2, 3, false, getNextDate($phpdate));
insertTracking(3, 4, true, getNextDate($phpdate));
insertTracking(4, 5, true, getNextDate($phpdate));
insertTracking(5, 4, true, getNextDate($phpdate));
insertTracking(6, 5, true, getNextDate($phpdate));
insertTracking(5, 6, true, getNextDate($phpdate));
insertTracking(8, 9, true, getNextDate($phpdate));
insertTracking(7, 9, true, getNextDate($phpdate));
insertTracking(10, 9, true, getNextDate($phpdate));
function insertStatistic($id, $title, $uid, $qid, $date)
{
$queries = "INSERT INTO statistics (id,title, FK_users, FK_queries, date) VALUES ({$id}, '{$title}', {$uid}, {$qid}, '{$date}');";
mysql_query($queries) or die("Error in query: {$queries}. " . mysql_error());
}
function insertUVar($id, $sid, $property, $var)
{
$queries = "INSERT INTO user_variable (id,FK_statistics, property, var) VALUES ({$id}, {$sid}, '{$property}', {$var});";
mysql_query($queries) or die("Error in query: {$queries}. " . mysql_error());
}
function insertQVar($id, $sid, $qid, $var)
{
$queries = "INSERT INTO question_variable (id,FK_statistics, FK_question, var) VALUES ({$id}, {$sid},{$qid}, {$var});";
mysql_query($queries) or die("Error in query: {$queries}. " . mysql_error());
}
$end_date = '2011-10-30';
//Вс
$start_date = getNextDate($end_date, -6);
//Пн
//$prev_end_date = getNextDate($end_date,-1);
$i = 0;
while ($end_date < date("Y-m-d") && $i < 1000) {
saveAveragePrices($start_date, $end_date);
$start_date = getNextDate($start_date, 7);
//Пн
$end_date = getNextDate($start_date, 6);
//Вс
$i++;
}
if (dateDiff('now', $start_date) > 0) {
saveAveragePrices(getNextDate($start_date, 1), date("Y-m-d"));
}
//ob_start();
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=download.csv");
header("Pragma: no-cache");
header("Expires: 0");
echo $query = "SELECT * FROM _tmp_prices2";
$export = $db->query($query) or die("Sql error : " . mysql_error());
$fields = mysql_num_fields($export);
$fp = fopen('php://output', 'w');
$header = [];
for ($i = 0; $i < $fields; $i++) {
$header[] = mysql_field_name($export, $i);
}
fputcsv($fp, $header);
exit;
} else {
$DOMAIN_ARR = explode('.', $_SERVER["HTTP_HOST"]);
$domain = $DOMAIN_ARR[0];
$company = Company::getInfoByDomain($domain);
if ($_SERVER['PHP_SELF'] == '/index.php') {
$photos = Company::getPhotosStatic($company['id']);
$photo_path = Company::getPhotoWebPathStatic($company['id']);
$gallery = Html::getPhotosGallery($photos, $photo_path);
$logo = Company::getLogoURL($company['id']);
$sort_by = 'updated_on';
$direction = 'DESC';
$sql_flats = "t.status='" . REALTY_STATUS_ACTIVE . "' AND f.status='" . REALTY_STATUS_SALE . "'";
$sql_company = " AND f.user_id IN (SELECT id FROM user WHERE company_id={$company['id']})";
//Объявления не старше месяца
$sql_date = " AND f.updated_on>'" . getNextDate(date('Y-m-d'), -61) . "'";
$flats = Html::getFlatList($sql_flats . $sql_company . $sql_date, $sort_by, $direction, 100, 1, 'sales');
$sql_flats = "t.status='" . REALTY_STATUS_ACTIVE . "' AND f.status='" . REALTY_STATUS_RENT . "'";
$flats_rent = Html::getFlatList($sql_flats . $sql_company . $sql_date, $sort_by, $direction, 100, 1, 'rent');
$sql_houses = "f.status='" . REALTY_STATUS_SALE . "'";
$houses = Html::getHouseList($sql_houses . $sql_company . $sql_date, $sort_by, $direction, 100, 1, 'sales');
$sql_lands = "f.status='" . REALTY_STATUS_SALE . "'";
$lands = Html::getLandList($sql_lands . $sql_company . $sql_date, $sort_by, $direction, 100, 1, 'sales');
$sql_commercial_sale = "f.status='" . REALTY_STATUS_SALE . "'";
$commercial = Html::getCommercialList($sql_commercial_sale . $sql_company . $sql_date, $sort_by, $direction, 100, 1, 'sales');
$sql_commercial_rent = "f.status='" . REALTY_STATUS_RENT . "'";
$commercial_rent = Html::getCommercialList($sql_commercial_rent . $sql_company . $sql_date, $sort_by, $direction, 100, 1, 'rent');
$s->assign("title", $company['name']);
$s->assign("logo", $logo);
$s->assign("subdomain", $domain);
$s->assign("HOST", $_SERVER["HTTP_HOST"]);
AND price_m <90000
AND price_m >20000
AND total_area>30
";
*/
//трешки кирпич
$sql = "SELECT DATE_FORMAT( f.created_on, '%Y-%m-%d' ) date, f.rooms, \n\t\tROUND( AVG( price ) ) price_avr, ROUND( AVG( price_m ) ) price_m_avr, \n\t\tROUND( AVG( total_area ) ) area_avr\n\t\tFROM flat f, tenement t \n\t\tWHERE \n\t\tf.tenement_id=t.id AND \n\t\tt.city_id=0 AND \n\t\t((f.created_on>='{$start_date}' AND f.created_on<='{$end_date}') \n\t\tOR (f.created_on<='{$end_date}' AND f.updated_on>='{$end_date}'))\n\t\tAND t.type_id=0\n\t\tAND rooms =3 \n\t\tAND is_new =0\n\t\tAND price >1400000\n\t\tAND price_m <90000\n\t\tAND price_m >20000 \n\t\tAND total_area>40 \n\t\t";
//кв. м. 3-шки новостройки
$sql = "SELECT DATE_FORMAT( f.created_on, '%Y-%m-%d' ) date, f.rooms, \n\t\tROUND( AVG( price ) ) price_avr, ROUND( AVG( price_m ) ) price_m_avr, \n\t\tROUND( AVG( total_area ) ) area_avr\n\t\tFROM flat f, tenement t \n\t\tWHERE \n\t\tf.tenement_id=t.id AND \n\t\tt.city_id=0 AND \n\t\t((f.created_on>='{$start_date}' AND f.created_on<='{$end_date}') \n\t\tOR (f.created_on<='{$end_date}' AND f.updated_on>='{$end_date}'))\n\t\tAND ((t.birthday>= IF (YEAR(f.created_on)=2012,2012,2011)) OR is_new=1)\n\t\tAND rooms = 3\t\t\n\t\tAND price > 1500000\n\t\tAND price < 8000000\n\t\tAND price_m <90000\n\t\tAND price_m >20000 \n\t\tAND total_area>40 \n\t\t";
$db->query($sql);
while ($row = $db->fetchRow()) {
$sql2 = "INSERT INTO _tmp_prices VALUES \n\t\t('{$end_date}','{$row['rooms']}','{$row['price_avr']}','{$row['area_avr']}','{$row['price_m_avr']}','{$row['count_flats']}')";
$db2->query($sql2);
}
}
$db->query("TRUNCATE TABLE _tmp_prices");
//$end_date = date('Y-m-d');
$end_date = '2011-10-30';
//Вс
$start_date = getNextDate($end_date, -6);
//Пн
//$prev_end_date = getNextDate($end_date,-1);
$i = 0;
while ($end_date < date("Y-m-d") && $i < 1000) {
saveAveragePrices($start_date, $end_date);
$start_date = getNextDate($start_date, 7);
//Пн
$end_date = getNextDate($start_date, 6);
//Вс
$i++;
}