<?php !defined('APP_DIR') ? die('acceess not defined') : '';
class actions_jobs_sell {
    
    /**
     * 计算小区不同面积范围的均价
     *
     */
    public function block_area_price(){
        //die('aaa');
        $last_id = 0;
        $run = 1;
        $t_s = time();
        
        //获取所有小区的数据
        $blocks = array();
        while ($run){
            $query_struct = array(
                'fields' => 'id,blockname',
                'where' => array(
                    'id >' => $last_id,
                    'esta' => 2,
                ),
                'orderby' => array(
                    'id' => 'ASC',
                ),
                'limit' => 2000,
            );
            
            $data = bm('block')->get_all($query_struct,'id');
            
            if ($data){
                $blocks = array_merge($blocks,$data);
                foreach ($data as $_data){
                    $last_id = $_data['id'];
                }
            }else {
                $run = 0;
            }
        }
        
        //根据小区获取房源并处理
        $array = array();
        $sell_count = 0;
        $block_ids = array();
        $i=0;
        foreach ($blocks as $_block){
            $query_struct = array(
                'fields' => 'id,blockid,buildarea,floor,totalfloor,averprice',
                'where' => array(
                    'blockid' => $_block['id'],
                    'esta' => 1,
                ),
            );
            $sells = bm('sell')->get_all($query_struct,'id');
            
            foreach ($sells as $_sell){
                $l_id = $_sell['id'];
                if ( $_sell['floor']==1 || $_sell['floor']==$_sell['totalfloor'] ){
                    continue;
                }
                //判断落在哪个区间范围
                if ($_sell['buildarea']<60){
                    $area_extent = 10;
                }elseif ($_sell['buildarea']>=60 && $_sell['buildarea']<90){
                    $area_extent = 20;
                }elseif ($_sell['buildarea']>=90 && $_sell['buildarea']<144){
                    $area_extent = 30;
                }elseif ($_sell['buildarea']>=144){
                    $area_extent = 40;
                }
                
                //数组初始化
                $arr_key = $_sell['blockid'].'_'.$area_extent;
                if ( !$array[$arr_key] ){
                    $array[$arr_key] = array(
                        'block_id'      => $_sell['blockid'],
                        'area_extent'   => $area_extent,
                        'total_price'   => 0,
                        'total_num'     => 0,
                        'average_price' => 0,
                    );
                }
                
                $array[$arr_key]['total_price'] += $_sell['averprice'];
                $array[$arr_key]['total_num'] += 1;
                $array[$arr_key]['average_price'] = $array[$arr_key]['total_price']/$array[$arr_key]['total_num'];
            }
            
            $sell_count += count($data);
        }
        
        $sqls = "REPLACE INTO esfpublic.block_area_price (city, block_id, area_extent, total_price, total_num, average_price) VALUES ";
        $_sqls = '';
        $i = 0;
        foreach ($array as $_arr){
            $i += 1;
            $_sqls .= 
                ($_sqls ? 
                ",('nj', {$_arr['block_id']},{$_arr['area_extent']},{$_arr['total_price']},{$_arr['total_num']},{$_arr['average_price']})":
                "('nj', {$_arr['block_id']},{$_arr['area_extent']},{$_arr['total_price']},{$_arr['total_num']},{$_arr['average_price']})");
        }
        $sql = $sqls.$_sqls;
        DAO::instance('block_area_price')->query($sql);
        
        $t_e = time();
        echo '完成区间均价的更新，共耗时'.($t_e - $t_s).'秒';die();
    }
    
    
    /**
     * 二手房价格预警
     *
     */
    public function sell_price_warning(){
        $last_id = 0;
        $run = 1;
        $t_s = time();
        
        //获取所有小区的数据
        $blocks = array();
        while ($run){
            $query_struct = array(
                'fields' => 'id,blockname',
                'where' => array(
                    'id >' => $last_id,
                    'esta' => 2,
                ),
                'orderby' => array(
                    'id' => 'ASC',
                ),
                'limit' => 2000,
            );
            
            $data = bm('block')->get_all($query_struct,'id');
            
            if ($data){
                $blocks = array_merge($blocks,$data);
                foreach ($data as $_data){
                    $last_id = $_data['id'];
                }
            }else {
                $run = 0;
            }
        }
        
        $array = array();
        $query_struct = array(
            'fields' => 'block_id,area_extent,average_price',
            'where' => array(
                'city' => 'nj',
            ),
        );
        $data = DAO::instance('block_area_price')->get_all($query_struct);
        foreach ($data as $_data){
            $array["{$_data['block_id']}_{$_data['area_extent']}"] = $_data;
        }
        
        //根据小区获取房源并处理
        $sell_count = 0;
        $block_ids = array();
        $i=0;
        foreach ($blocks as $_block){
            $query_struct = array(
                'fields' => 'id,ownid,infotype,infofrom,agentcode,uid,esta,district,blockid,blockshowname,mright,buildarea,floor,totalfloor,room,hall,toilet,kitchen,price,averprice,contactor,telno,mobile,creattime,updatetime,expiretime',
                'where' => array(
                    'blockid' => $_block['id'],
                    'infotype' => 1,
                    'infofrom' =>5,
                    'esta' => 1,
                ),
            );
            $sells = bm('sell')->get_all($query_struct,'id');
            //print_r($sells);die();
            
            $sqls = "REPLACE INTO esfpublic.sell_price_warning (city,id,ownid,infotype,infofrom,agentcode,uid,esta,district,blockid,blockshowname,mright,buildarea,floor,totalfloor,room,hall,toilet,kitchen,price,averprice,contactor,telno,mobile,creattime,updatetime,expiretime) VALUES ";
            $_sqls = '';
            foreach ($sells as $_sell){
                $l_id = $_sell['id'];
                
                //计算下浮比例
                $rate = 0.1;
                if ( $_sell['floor']==1 || $_sell['floor']==$_sell['totalfloor'] ){
                    $rate = 0.15;
                }
                
                //判断落在哪个区间范围
                if ($_sell['buildarea']<60){
                    $area_extent = 10;
                }elseif ($_sell['buildarea']>=60 && $_sell['buildarea']<90){
                    $area_extent = 20;
                }elseif ($_sell['buildarea']>=90 && $_sell['buildarea']<144){
                    $area_extent = 30;
                }elseif ($_sell['buildarea']>=144){
                    $area_extent = 40;
                }
                
                //键值
                $arr_key = $_sell['blockid'].'_'.$area_extent;
                if ( $array[$arr_key] ){
                    if ( $_sell['averprice'] < ($array[$arr_key]['average_price']*(1-$rate)) ){
                        if ($_sell['infofrom']==5){
                            $_sell['city'] = 'nj';
                            $_sql = "('nj','{$_sell['id']}','{$_sell['ownid']}','{$_sell['infotype']}','{$_sell['infofrom']}','{$_sell['agentcode']}','{$_sell['uid']}','{$_sell['esta']}','{$_sell['district']}','{$_sell['blockid']}','{$_sell['blockshowname']}','{$_sell['mright']}','{$_sell['buildarea']}','{$_sell['floor']}','{$_sell['totalfloor']}','{$_sell['room']}','{$_sell['hall']}','{$_sell['toilet']}','{$_sell['kitchen']}','{$_sell['price']}','{$_sell['averprice']}','{$_sell['contactor']}','{$_sell['telno']}','{$_sell['mobile']}','{$_sell['creattime']}','{$_sell['updatetime']}','{$_sell['expiretime']}')";
                            $_sqls .= ($_sqls ? ",$_sql" : $_sql);
                        }
                    }
                }
            }
            $sql = $sqls.$_sqls;
            //echo $sql;die();
            DAO::instance('sell_price_warning')->query($sql);
            $sell_count += count($data);
        }
        
        $t_e = time();
        echo $t_e - $t_s;die();
    }
    
    
    /**
     * 24
     *
     */
    public function warning_24h_num(){
        $last_id = 0;
        $run = 1;
        $t_s = time();
        
        $warning_num = 0;
        
        //获取所有小区的数据
        $blocks = array();
        while ($run){
            $query_struct = array(
                'fields' => 'id,blockname',
                'where' => array(
                    'id >' => $last_id,
                    'esta' => 2,
                ),
                'orderby' => array(
                    'id' => 'ASC',
                ),
                'limit' => 2000,
            );
            
            $data = bm('block')->get_all($query_struct,'id');
            
            if ($data){
                $blocks = array_merge($blocks,$data);
                foreach ($data as $_data){
                    $last_id = $_data['id'];
                }
            }else {
                $run = 0;
            }
        }
        
        $array = array();
        $query_struct = array(
            'fields' => 'block_id,area_extent,average_price',
            'where' => array(
                'city' => 'nj',
            ),
        );
        $data = DAO::instance('block_area_price')->get_all($query_struct);
        foreach ($data as $_data){
            $array["{$_data['block_id']}_{$_data['area_extent']}"] = $_data;
        }
        
        //根据小区获取房源并处理
        $sell_count = 0;
        $block_ids = array();
        $i=0;
        foreach ($blocks as $_block){
            $query_struct = array(
                'fields' => 'id,ownid,infotype,infofrom,agentcode,uid,esta,district,blockid,blockshowname,mright,buildarea,floor,totalfloor,room,hall,toilet,kitchen,price,averprice,contactor,telno,mobile,creattime,updatetime,expiretime',
                'where' => array(
                    'blockid' => $_block['id'],
                    'infotype' => 1,
                    'infofrom' =>5,
                    'esta' => 1,
                    'creattime >' => ($t_s-86400),
                ),
            );
            $sells = bm('sell')->get_all($query_struct,'id');
            //print_r(db::$sqls);die();
            //print_r($sells);die();
            
            //$sqls = "REPLACE INTO esfpublic.sell_price_warning (city,id,ownid,infotype,infofrom,agentcode,uid,esta,district,blockid,blockshowname,mright,buildarea,floor,totalfloor,room,hall,toilet,kitchen,price,averprice,contactor,telno,mobile,creattime,updatetime,expiretime) VALUES ";
            //$_sqls = '';
            foreach ($sells as $_sell){
                $l_id = $_sell['id'];
                
                //计算下浮比例
                $rate = 0.2;
                if ( $_sell['floor']==1 || $_sell['floor']==$_sell['totalfloor'] ){
                    $rate = 0.2;
                }
                
                //判断落在哪个区间范围
                if ($_sell['buildarea']<60){
                    $area_extent = 10;
                }elseif ($_sell['buildarea']>=60 && $_sell['buildarea']<90){
                    $area_extent = 20;
                }elseif ($_sell['buildarea']>=90 && $_sell['buildarea']<144){
                    $area_extent = 30;
                }elseif ($_sell['buildarea']>=144){
                    $area_extent = 40;
                }
                
                //键值
                $arr_key = $_sell['blockid'].'_'.$area_extent;
                if ( $array[$arr_key] ){
                    if ( $_sell['averprice'] < ($array[$arr_key]['average_price']*(1-$rate)) ){
                        if ($_sell['infofrom']==5){
                            $warning_num += 1;
                            //$_sell['city'] = 'nj';
                            //$_sql = "('nj','{$_sell['id']}','{$_sell['ownid']}','{$_sell['infotype']}','{$_sell['infofrom']}','{$_sell['agentcode']}','{$_sell['uid']}','{$_sell['esta']}','{$_sell['district']}','{$_sell['blockid']}','{$_sell['blockshowname']}','{$_sell['mright']}','{$_sell['buildarea']}','{$_sell['floor']}','{$_sell['totalfloor']}','{$_sell['room']}','{$_sell['hall']}','{$_sell['toilet']}','{$_sell['kitchen']}','{$_sell['price']}','{$_sell['averprice']}','{$_sell['contactor']}','{$_sell['telno']}','{$_sell['mobile']}','{$_sell['creattime']}','{$_sell['updatetime']}','{$_sell['expiretime']}')";
                            //$_sqls .= ($_sqls ? ",$_sql" : $_sql);
                        }
                    }
                }
            }
            //$sql = $sqls.$_sqls;
            //echo $sql;die();
            //DAO::instance('sell_price_warning')->query($sql);
            //$sell_count += count($data);
        }
        
        $t_e = time();
        echo '扫描耗时'.($t_e - $t_s).'秒，发现'.$warning_num.'条问题房源<br>';
        die();
    }
}