<?php

$conf_basedir	= "../../";	//相对主目录的位置
$conf_where		= "admin";	//选择调用相应模块
$directory		= basename(dirname(__FILE__));	//定义当前所在的目录，引入模块公用函数
include_once($conf_basedir."base.inc.php");
include_once(CORE_LIB.'/CommonStatisticDownloader.php'); 
checkAuth('101');	//判断登录和权限
$js .= "<script src='".$conf_basedir."script/js/layer/layer.js'></script>";
$title = "数据报表";

//竞投类型:0,小区;1,小区词;2，热搜词
if($type=='z'){//总的数据
    $e_time = strtotime($e_time)?$e_time:date("Y-m").'-01';
    $s_time = strtotime($s_time)?$s_time:date("Y-m",  strtotime("-1 month")).'-01';
    $timeConf = "and gh.`utime`>='$s_time' and gh.`utime`<'$e_time'";
    //echo $e_time;exit;
    $sql = "select '$e_time' as e_time, '$s_time' as s_time, c.`username`,c.`truename`,c.`company`,c.dist,c.master,c.`agentname`,sum(c.`num`) as num,sum(c.`score`) as score from (
SELECT broker_id,'0' as 'type' ,br.username,br.truename ,br.dist,br.master,ag1.agentname as company, ag.agentname ,count(id) as num,SUM(score) as score
FROM broker as br
LEFT JOIN growth_bid_block_expert_record_history as gh ON gh.broker_id=br.uid
LEFT JOIN agency as ag ON br.agentcode=ag.agentcode
LEFT JOIN agency as ag1 on ag.parentcode=ag1.agentcode
WHERE gh.`status`=1 $timeConf
GROUP BY broker_id 
UNION
SELECT broker_id,type as 'type' ,br.username,br.truename ,br.dist,br.master,ag1.agentname as company, ag.agentname ,count(id) as num,SUM(score) as score
FROM broker as br
LEFT JOIN growth_bid_house_record_history as gh ON gh.broker_id=br.uid
LEFT JOIN agency as ag ON br.agentcode=ag.agentcode
LEFT JOIN agency as ag1 on ag.parentcode=ag1.agentcode
WHERE gh.`status`=1 $timeConf
GROUP BY broker_id,type) as c
GROUP BY c.broker_id
order by c.truename";
 $sql1 = "select c.* from (
SELECT '0' as 'type' ,br.username,br.truename ,br.dist,br.master,ag1.agentname as company, ag.agentname ,count(id) as num,SUM(score) as score
FROM broker as br
LEFT JOIN growth_bid_block_expert_record_history as gh ON gh.broker_id=br.uid
LEFT JOIN agency as ag ON br.agentcode=ag.agentcode
LEFT JOIN agency as ag1 on ag.parentcode=ag1.agentcode
WHERE gh.`status`=1 $timeConf
GROUP BY broker_id 
UNION
SELECT type ,br.username,br.truename ,br.dist,br.master,ag1.agentname as company, ag.agentname ,count(id) as num,SUM(score) as score
FROM broker as br
LEFT JOIN growth_bid_house_record_history as gh ON gh.broker_id=br.uid
LEFT JOIN agency as ag ON br.agentcode=ag.agentcode
LEFT JOIN agency as ag1 on ag.parentcode=ag1.agentcode
WHERE gh.`status`=1 $timeConf
GROUP BY broker_id,type) as c
order by c.truename";
 $res = $dbback->getAll($sql);
 $res1 = $dbback->getAll($sql1);
 $res1 = SqlCombinHelper::changeToDoubleKeyArray($res1, 'username','type');//print_r($res);exit;
foreach ($res as $key => &$value) {
    $v = $res1[$value['username']];
    $value['type0'] =  $v[0]?$v[0]['num']:0;
    $value['type1'] =  $v[1]?$v[1]['num']:0;
    $value['type2'] =  $v[2]?$v[2]['num']:0;
}
   $allColumns = array(
       '1'=>array('key'=>'开始时间','name'=>'开始时间','field'=>'s_time', 
       'value'=>array()
       ),
        '2'=>array('key'=>'结束时间','name'=>'结束时间','field'=>'e_time', 
       'value'=>array()
       ),
       '3'=>array('key'=>'经纪人账号','name'=>'经纪人账号','field'=>'username', 
       'value'=>array()
       ),
       '4'=>array('key'=>'经纪人姓名','name'=>'经纪人姓名','field'=>'truename', 
       'value'=>array()
       ),  
       '5'=>array('key'=>'区属','name'=>'区属','field'=>'dist', 
       'value'=>array()
       ),
        '6'=>array('key'=>'公司名称','name'=>'公司名称','field'=>'company', 
       'value'=>array()
       ),  
        '7'=>array('key'=>'门店名称','name'=>'门店名称','field'=>'agentname', 
       'value'=>array()
       ),  
         '8'=>array('key'=>'竞投成功次数','name'=>'竞投成功次数','field'=>'num', 
       'value'=>array()
       ),
        '9'=>array('key'=>'投入房豆','name'=>'投入房豆','field'=>'score', 
       'value'=>array()
       ),
       '10'=>array('key'=>'小区专家','name'=>'小区专家','field'=>'type0', 
       'value'=>array()
       ),
       '11'=>array('key'=>'小区词','name'=>'小区词','field'=>'type1', 
       'value'=>array()
       ),
       '12'=>array('key'=>'热搜词','name'=>'热搜词','field'=>'type2', 
       'value'=>array()
       ),
       '13'=>array('key'=>'客户经理','name'=>'客户经理','field'=>'master', 
       'value'=>array()
       ),
        
    );
    

    
    $data = $res;
    $dataDownloadColumn = new DataDownloadColumn();
    $dataDownloadColumn->setAllColumns($allColumns);

    $downloader = new AgencyStatisticDownloader();

    $downloader->setDataDownloadColumn($dataDownloadColumn);

    $downloader->setData($data);

    $downloader->createTitle();
    $downloader->writeData();

    //开始下载

    //下载操作日志
    $logMessage = "下载竞投统计数据";
    $operateRecord->insertData('85', 'download', 
                $logMessage, $_SESSION['esfadmin']['username']);
    $fileName = "竞投统计数据".$s_time."到".$e_time.'.xls';
    $downloader->setFileName($fileName);
    $downloader->output();
    

}



include_once(Template("header"));
?>
<script type="" src="http://esfadmin.house365.com/script/My97DatePicker/WdatePicker.js"></script>
<div>
    <form action="http://esfadmin.house365.com/module/growth/download_bid_data.php" method="get">
        <p>开始时间：<input type="text" autocomplete="off" name="s_time" onclick="WdatePicker({dateFmt:'yyyy-M-d',minDate:'%y-{%M-2}-%d',maxDate:'%y-%M-{%d-1}'})" value=""/></p>
        <p>结束时间：<input type="text" autocomplete="off" name="e_time" onclick="WdatePicker({dateFmt:'yyyy-M-d',minDate:'%y-{%M-2}-%d',maxDate:'%y-%M-{%d-1}'})" value=""/></p>
        <input type="hidden" name="type" value="z"/>
        <input type="submit" value="下载"/>
    </form>
</div>
<?php
include_once(Template("footer"));