<?php

/***************************************************************\
|                                                               |
|   开单大师（专业的房产ERP管理系统）                           |
|                                                               |
|   © 2017-2022 淮南市银泰软件科技有限公司 All rights reserved  |
|                                                               |
|   http://www.kaidandashi.com                                  |
|                                                               |
|   QQ号：984784483                                             |
|                                                               |
\***************************************************************/

namespace Admin\Controller;
use Think\Controller;
class IndexController extends CommonController {
    public function index(){
        $role_id=M('role_user')->where(array('user_id'=>session('uid')))->getField('role_id');
        $sql = "select a.* from  
            (select * from __MENU__ where status=1 ) as a , 
            (select * from __ACCESS__ where role_id = '".$role_id."') as b where a.node_id=b.node_id  ORDER BY a.listorder ASC";
        $Model = new \Think\Model();
        $smenu=$Model->query($sql);
        $pmenu=M('Menu')->where(array('type'=>array('neq',2),'status'=>1))->select();
        $menu=array_merge_recursive($smenu,$pmenu);
        $menu=array_sort($menu,'listorder','asc');
        if(session('username') == C('RBAC_SUPERADMIN')){
            $result=M('menu')->where(array('status'=>1))->order('listorder asc')->select();
        }else{
            $result=$menu;
        }
        foreach ($result as $n=> $r) {
            if ($r['type']==2) {
                $name = ucwords($r['app']);
                $model = ucwords($r['model']);
                $action = $r['action'];
                //附带参数
                $params = "";
                if ($r['data']) {
                    $params = "?" . htmlspecialchars_decode($r['data']);
                }
                $result[$n]['url'] = U("{$name}/{$model}/{$action}{$params}");
            }elseif ($r['type']==3) {
                if ($r['blank']) {
                    $params =  htmlspecialchars_decode($r['blank']);
                }
                $result[$n]['url'] = $params;
            }
        }
        $tree = new \Org\Util\Tree();
        $tree->init($result);
        $categorys = $tree->get_tree_array(0);
        foreach ($categorys as $key => $value) {
            foreach ($value['child'] as $key1 => $value1) {
                if ($value1['child']==null) {
                    unset($categorys[$key]['child'][$key1]);
                }
            }
        }
        foreach ($categorys as $key => $value) {
            if ($value['child']==null) {
                unset($categorys[$key]);
            }
        }
        $this->categorys = $categorys;
        $this->user = M('yonghu')->where('id='.session('uid'))->find();
        $this->zdkp();
        $this->fkypz();
        $this->display('index1');
    }
    public function zdkp(){
        $jintian=date("Y-m-d",time());
        $jinri=strtotime($jintian);
        $zhkpsj=M("fkypz")->where(array('id'=>1))->getField('zhkpsj');
        if ($jinri != $zhkpsj) {
            $fpkp=M("fpkp")->where(array('gongsiid'=>session('gongsiid'),'endunix'=>array('lt',$jinri),'czlx'=>1,'type'=>array('neq',2)))->select();
            foreach ($fpkp as $key => $value) {
                if ($value['zylx'] == 1) {
                    $zyb=M('fangyuan');
                } else if ($value['zylx'] == 2) {
                    $zyb=M('keyuan');
                }
                if ($value['fkplx'] == 1) {
                    $fpzd="fpdh";
                } else if ($value['fkplx'] == 2) {
                    $fpzd="fpdz";
                } else if ($value['fkplx'] == 3) {
                    $fpzd="fpdj";
                } else if ($value['fkplx'] == 4) {
                    $fpzd="fpdq";
                } else if ($value['fkplx'] == 5) {
                    $fpzd="fpck";
                }
                $zyb->where(array('id'=>$value['zyid']))->setField($fpzd,2);
                $zyb->where(array('id'=>$value['zyid']))->setField('fengpanrenid','');
            }
        }
        M("fkypz")->where(array('id'=>1))->setField('zhkpsj',$jinri);
    }
    public function fkypz(){
        $fkypz=M("fkypz")->where(array('id'=>1))->find();
        $xzshijian=time();
        $jintian=date("Y-m-d",$xzshijian);
        $jinri=strtotime($jintian);
        $chengjiao_options=M('options')->where(array('option_name'=>'chengjiao_options'))->find();
        $options_arr = json_decode($chengjiao_options['option_value'],true);
        if ($jinri != $fkypz['zhzxsj']) {
            if($fkypz['csgj'] == 1){
                $csgjsj=$xzshijian-$fkypz['csgjsj'];
                $csgjwcj=M("fangyuan")->where("gongsiid = ".session('gongsiid')." and leixing = 1 and zhuangtai > 0 and zhuangtai not in (".$options_arr['fyfengpan_zhuangtai'].") and zhgjsj <".$csgjsj." and isdel = 2")->setField('zhuangtai',$options_arr['fygongpan_zhuangtai']);
            }
            if(($fkypz['csgj'] == 0) && ($fkypz['csxz'] == 1)){
                $csxzsj=$xzshijian-$fkypz['csxzsj'];
                $csxzwcj=M("fangyuan")->where("gongsiid = ".session('gongsiid')." and leixing = 1 and zhuangtai > 0 and zhuangtai not in (".$options_arr['fyfengpan_zhuangtai'].") and xiugaisj <".$csxzsj." and isdel = 2")->setField('zhuangtai',$options_arr['fygongpan_zhuangtai']);
            }
            if(($fkypz['csgj'] == 0) && ($fkypz['csxz'] == 0) && ($fkypz['cslr'] == 1)){
                $cslrsj=$xzshijian-$fkypz['cslrsj'];
                $cslrwcj=M("fangyuan")->where("gongsiid = ".session('gongsiid')." and leixing = 1 and zhuangtai > 0 and zhuangtai not in (".$options_arr['fyfengpan_zhuangtai'].") and lurusj <".$cslrsj." and isdel = 2")->setField('zhuangtai',$options_arr['fygongpan_zhuangtai']);
            }
            if($fkypz['czgj'] == 1){
                $czgjsj=$xzshijian-$fkypz['czgjsj'];
                $czgjwcj=M("fangyuan")->where("gongsiid = ".session('gongsiid')." and leixing = 2 and zhuangtai > 0 and zhuangtai not in (".$options_arr['fyfengpan_zhuangtai'].") and zhgjsj <".$czgjsj." and isdel = 2")->setField('zhuangtai',$options_arr['fygongpan_zhuangtai']);
            }
            if(($fkypz['czgj'] == 0) && ($fkypz['czxz'] == 1)){
                $czxzsj=$xzshijian-$fkypz['czxzsj'];
                $czxzwcj=M("fangyuan")->where("gongsiid = ".session('gongsiid')." and leixing = 2 and zhuangtai > 0 and zhuangtai not in (".$options_arr['fyfengpan_zhuangtai'].") and xiugaisj <".$czxzsj." and isdel = 2")->setField('zhuangtai',$options_arr['fygongpan_zhuangtai']);
            }
            if(($fkypz['czgj'] == 0) && ($fkypz['czxz'] == 0) && ($fkypz['czlr'] == 1)){
                $czlrsj=$xzshijian-$fkypz['czlrsj'];
                $czlrwcj=M("fangyuan")->where("gongsiid = ".session('gongsiid')." and leixing = 2 and zhuangtai > 0 and zhuangtai not in (".$options_arr['fyfengpan_zhuangtai'].") and lurusj <".$czlrsj." and isdel = 2")->setField('zhuangtai',$options_arr['fygongpan_zhuangtai']);
            }
            if($fkypz['qggj'] == 1){
                $qggjsj=$xzshijian-$fkypz['qggjsj'];
                $qggjwcj=M("keyuan")->where("gongsiid = ".session('gongsiid')." and mmzl = 1 and zhuangtai > 0 and zhuangtai not in (".$options_arr['kyfengpan_zhuangtai'].") and zhgjsj <".$qggjsj." and isdel = 2")->setField('zhuangtai',$options_arr['kygongpan_zhuangtai']);
            }
            if(($fkypz['qggj'] == 0) && ($fkypz['qgxz'] == 1)){
                $qgxzsj=$xzshijian-$fkypz['qgxzsj'];
                $qgxzwcj=M("keyuan")->where("gongsiid = ".session('gongsiid')." and mmzl = 1 and zhuangtai > 0 and zhuangtai not in (".$options_arr['kyfengpan_zhuangtai'].") and xiugaisj <".$qgxzsj." and isdel = 2")->setField('zhuangtai',$options_arr['kygongpan_zhuangtai']);
            }
            if(($fkypz['qggj'] == 0) && ($fkypz['qgxz'] == 0) && ($fkypz['qglr'] == 1)){
                $qglrsj=$xzshijian-$fkypz['qglrsj'];
                $qglrwcj=M("keyuan")->where("gongsiid = ".session('gongsiid')." and mmzl = 1 and zhuangtai > 0 and zhuangtai not in (".$options_arr['kyfengpan_zhuangtai'].") and lurusj <".$qglrsj." and isdel = 2")->setField('zhuangtai',$options_arr['kygongpan_zhuangtai']);
            }
            if($fkypz['qzgj'] == 1){
                $qzgjsj=$xzshijian-$fkypz['qzgjsj'];
                $qzgjwcj=M("keyuan")->where("gongsiid = ".session('gongsiid')." and mmzl = 2 and zhuangtai > 0 and zhuangtai not in (".$options_arr['kyfengpan_zhuangtai'].") and zhgjsj <".$qzgjsj." and isdel = 2")->setField('zhuangtai',$options_arr['kygongpan_zhuangtai']);
            }
            if(($fkypz['qzgj'] == 0) && ($fkypz['qzxz'] == 1)){
                $qzxzsj=$xzshijian-$fkypz['qzxzsj'];
                $qzxzwcj=M("keyuan")->where("gongsiid = ".session('gongsiid')." and mmzl = 2 and zhuangtai > 0 and zhuangtai not in (".$options_arr['kyfengpan_zhuangtai'].") and xiugaisj <".$qzxzsj." and isdel = 2")->setField('zhuangtai',$options_arr['kygongpan_zhuangtai']);
            }
            if(($fkypz['qzgj'] == 0) && ($fkypz['qzxz'] == 0) && ($fkypz['qzlr'] == 1)){
                $qzlrsj=$xzshijian-$fkypz['qzlrsj'];
                $qzlrwcj=M("keyuan")->where("gongsiid = ".session('gongsiid')." and mmzl = 2 and zhuangtai > 0 and zhuangtai not in (".$options_arr['kyfengpan_zhuangtai'].") and lurusj <".$qzlrsj." and isdel = 2")->setField('zhuangtai',$options_arr['kygongpan_zhuangtai']);
            }
            M("fkypz")->where(array('id'=>1))->setField('zhzxsj',$jinri);
        }
    }
    public function xiaoxi(){
        $Model = new \Think\Model();
        $map10 = "gongsiid=".session('gongsiid');
        $map10 .= " and txuid=".session('uid');
        $map10 .= " and txshijian<".time();
        $map10 .= " and isyidu=0";
        $sqlcount10 = "select count(*) FROM __KYTIXING__ where {$map10}";
        $countb10=$Model->query($sqlcount10);
        $sh_count10=$countb10[0]['count(*)'];
        $arr['sh_count10']=$sh_count10;
        $is_rbac12 = _rbac_node_show(841);
        if ($is_rbac12) {
            $map12 = "gongsiid=".session('gongsiid');
            $x12 = _rbac_lists_where(841,0);
            if ($x12 && $x12 != "all" && $x12 != "me") {
                $map12 .= " and bumen in ({$x12})";
            }elseif ($x12 == "me") {
                $map12 .= " and id=".session('uid');
            }elseif ($x12 == false) {
                $map12 .= " and id=0";
            }
            $sqlcount12 = "select count(*) FROM __FPKP__ as a join (select id from __YONGHU__ where {$map12}) as b on a.uid=b.id where type=3";
            $countb12=$Model->query($sqlcount12);
            $sh_count12=$countb12[0]['count(*)'];
            $arr['sh_count12']=$sh_count12;
        }
        $is_rbac13 = _rbac_node_show(844);
        if ($is_rbac13) {
            $map13 = "gongsiid=".session('gongsiid');
            $x13 = _rbac_lists_where(844,0);
            if ($x13 && $x13 != "all" && $x13 != "me") {
                $map13 .= " and bumen in ({$x13})";
            }elseif ($x13 == "me") {
                $map13 .= " and id=".session('uid');
            }elseif ($x13 == false) {
                $map13 .= " and id=0";
            }
            $sqlcount13 = "select count(*) FROM __FPKP__ as a join (select id from __YONGHU__ where {$map13}) as b on a.uid=b.id where zylx=1 and czlx=1 and type=3";
            $countb13=$Model->query($sqlcount13);
            $sh_count13=$countb13[0]['count(*)'];
            $arr['sh_count13']=$sh_count13;
        }
        $is_rbac14 = _rbac_node_show(847);
        if ($is_rbac14) {
            $map14 = "gongsiid=".session('gongsiid');
            $x14 = _rbac_lists_where(847,0);
            if ($x14 && $x14 != "all" && $x14 != "me") {
                $map14 .= " and bumen in ({$x14})";
            }elseif ($x14 == "me") {
                $map14 .= " and id=".session('uid');
            }elseif ($x14 == false) {
                $map14 .= " and id=0";
            }
            $sqlcount14 = "select count(*) FROM __FPKP__ as a join (select id from __YONGHU__ where {$map14}) as b on a.uid=b.id where zylx=1 and czlx=2 and type=3";
            $countb14=$Model->query($sqlcount14);
            $sh_count14=$countb14[0]['count(*)'];
            $arr['sh_count14']=$sh_count14;
        }
        $is_rbac15 = _rbac_node_show(850);
        if ($is_rbac15) {
            $map15 = "gongsiid=".session('gongsiid');
            $x15 = _rbac_lists_where(850,0);
            if ($x15 && $x15 != "all" && $x15 != "me") {
                $map15 .= " and bumen in ({$x15})";
            }elseif ($x15 == "me") {
                $map15 .= " and id=".session('uid');
            }elseif ($x15 == false) {
                $map15 .= " and id=0";
            }
            $sqlcount15 = "select count(*) FROM __FPKP__ as a join (select id from __YONGHU__ where {$map15}) as b on a.uid=b.id where zylx=2 and czlx=1 and type=3";
            $countb15=$Model->query($sqlcount15);
            $sh_count15=$countb15[0]['count(*)'];
            $arr['sh_count15']=$sh_count15;
        }
        $is_rbac16 = _rbac_node_show(853);
        if ($is_rbac16) {
            $map16 = "gongsiid=".session('gongsiid');
            $x16 = _rbac_lists_where(853,0);
            if ($x16 && $x16 != "all" && $x16 != "me") {
                $map16 .= " and bumen in ({$x16})";
            }elseif ($x16 == "me") {
                $map16 .= " and id=".session('uid');
            }elseif ($x16 == false) {
                $map16 .= " and id=0";
            }
            $sqlcount16 = "select count(*) FROM __FPKP__ as a join (select id from __YONGHU__ where {$map16}) as b on a.uid=b.id where zylx=2 and czlx=2 and type=3";
            $countb16=$Model->query($sqlcount16);
            $sh_count16=$countb16[0]['count(*)'];
            $arr['sh_count16']=$sh_count16;
        }
        if ($sh_count12) {
            $fkpshzs=$sh_count12;
        } else {
            $fkpshzs=$sh_count13+$sh_count14+$sh_count15+$sh_count16;
        }
        $arr['sh_countz']=$sh_count10+$sh_count11+$fkpshzs;
        $data=array('data'=>$arr);
        header("Content-type: application/json");
        exit(json_encode($data));
    }
    public function validatepass(){
        if (trim(I('passwd'))=='') {
            $this->error('密码不能为空');
        }
        $passwd = md5(trim(I('passwd')));
        $uid = session('uid');
        $r = M('Yonghu')->where(array('id'=>$uid,'mima'=>$passwd))->getField('id');
        //返回查询结果到异步json
        $data=array('r'=>$r);
        header("Content-type: application/json");
        exit(json_encode($data)); 
    }
    public function updatepass(){
        if (IS_POST) {
            $ymima=I('ymima');
            $xmima=I('xmima');
            $qrmima=I('qrmima');
            $yymima=M('Yonghu')->where(array('id'=>session('uid')))->getField('mima');
            if ($yymima!=md5($ymima)) {
                $this->error('原密码错误!');
            }
            if ($xmima!=$qrmima) {
                $this->error('两次密码不匹配!');
            }
            $passwd = md5($xmima);
            $uid = session('uid');
            $r = M('Yonghu')->where(array('id'=>$uid))->setField(array('mima'=>$passwd));
            if ($r) {
                $data_rizhi['zy_type']=8;
                $data_rizhi['uid']=session("uid");
                $data_rizhi['addtime']=time();
                $data_rizhi['cz_type']=2;
                $neirong="修改密码";
                $data_rizhi['neirong']=$neirong;
                M('rizhi')->add($data_rizhi);
                $this->success('成功');
            }else{
                $this->error('失败');
            }
        }else{
            $this->display();
        }
    }
    public function welcome(){
        //用户信息显示
        $this->useri=M('Yonghu')->where(array('id'=>session('uid')))->field('ygmingcheng,dianhua,touxiang,ygbianhao,bumen')->find();
        $this->bmming=M('bumen')->where(array('id'=>session('bumen')))->getField('bmming');
        $this->remark=M('role')->where(array('id'=>session('role')))->getField('remark');
        //准意向客户
        $tmb=time()-7*24*3600;
        $tme=time();
        $yixiang="select id,khxingming,xqbianhao,a.count from __KEYUAN__ b join (select count(*) as count,kyid,dkuid,dkshijian from __KYDAIKAN__ group by kyid) a on a.kyid=b.id where a.dkuid=".session('uid')." and a.dkshijian>{$tmb} and a.dkshijian<{$tme} order by a.count DESC limit 6 ";
        $yixiang=M('kydaikan')->query($yixiang);
        $this->assign('yixiang',$yixiang);
        //本月数据-客源（按分店统计）
        $kybmtj_sql="SELECT distinct syky.lry AS llry,byky.lry AS nlry,
        syky.bm, syky.kysl AS lky
        , ifnull(byky.kysl, 0) AS nky
        , round((ifnull(byky.kysl, 0) - ifnull(syky.kysl,0)) / ifnull(syky.kysl,1) * 100, 2) AS tbzz
        FROM (
            SELECT month(FROM_UNIXTIME(ky.lurusj)) AS lry, yh.bumen AS bm
            , COUNT(*) AS kysl
            FROM __KEYUAN__ ky, __YONGHU__ yh
            WHERE ky.weihurenid = yh.id
            AND FROM_UNIXTIME(ky.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
            AND FROM_UNIXTIME(ky.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
            GROUP BY lry, yh.bumen
        ) syky
        LEFT JOIN (
            SELECT month(FROM_UNIXTIME(ky.lurusj)) AS lry, yh.bumen AS bm
            , COUNT(*) AS kysl
            FROM __KEYUAN__ ky, __YONGHU__ yh
            WHERE ky.weihurenid = yh.id
            AND FROM_UNIXTIME(ky.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
            GROUP BY lry, yh.bumen
        ) byky
        ON syky.bm = byky.bm
        union
        SELECT distinct syky.lry AS llry,byky.lry AS nlry,
        byky.bm, syky.kysl AS lky
        , ifnull(byky.kysl, 0) AS nky
        , round((ifnull(byky.kysl, 0) - ifnull(syky.kysl,0)) / ifnull(syky.kysl,1) * 100, 2) AS tbzz
        FROM (
            SELECT month(FROM_UNIXTIME(ky.lurusj)) AS lry, yh.bumen AS bm
            , COUNT(*) AS kysl
            FROM __KEYUAN__ ky, __YONGHU__ yh
            WHERE ky.weihurenid = yh.id
            AND FROM_UNIXTIME(ky.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
            AND FROM_UNIXTIME(ky.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
            GROUP BY lry, yh.bumen
        ) syky
        RIGHT JOIN (
            SELECT month(FROM_UNIXTIME(ky.lurusj)) AS lry, yh.bumen AS bm
            , COUNT(*) AS kysl
            FROM __KEYUAN__ ky, __YONGHU__ yh
            WHERE ky.weihurenid = yh.id
            AND FROM_UNIXTIME(ky.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
            GROUP BY lry, yh.bumen
        ) byky
        ON syky.bm = byky.bm
        ORDER BY nky DESC";
        $kybmtj_result = new \Think\Model();
        $kybmtj_result =$kybmtj_result->query($kybmtj_sql);
        $kybmtj['ky']=$kybmtj_result;
        $kybmtj['bm']=session('bumen');
        $this->assign('kybmtj',$kybmtj);
        //本月数据-房源（按分店统计）
        $fybmtj_sql="SELECT syfy.lry, syfy.bm, syfy.fysl AS lfy
        , ifnull(byfy.fysl, 0) AS nfy
        , round((ifnull(byfy.fysl, 0) - ifnull(syfy.fysl,0)) / ifnull(syfy.fysl,1) * 100, 2) AS tbzz
        FROM (
            SELECT month(FROM_UNIXTIME(fy.lurusj)) AS lry, yh.bumen AS bm
            , COUNT(*) AS fysl
            FROM __FANGYUAN__ fy, __YONGHU__ yh
            WHERE fy.weihurenid = yh.id
            AND FROM_UNIXTIME(fy.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
            AND FROM_UNIXTIME(fy.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
            GROUP BY lry, yh.bumen
        ) syfy
        LEFT JOIN (
            SELECT month(FROM_UNIXTIME(fy.lurusj)) AS lry, yh.bumen AS bm
            , COUNT(*) AS fysl
            FROM __FANGYUAN__ fy, __YONGHU__ yh
            WHERE fy.weihurenid = yh.id
            AND FROM_UNIXTIME(fy.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
            GROUP BY lry, yh.bumen
        ) byfy
        ON syfy.bm = byfy.bm
        union
        SELECT byfy.lry, byfy.bm, syfy.fysl AS lfy
        , ifnull(byfy.fysl, 0) AS nfy
        , round((ifnull(byfy.fysl, 0) - ifnull(syfy.fysl,0)) / ifnull(syfy.fysl,1) * 100, 2) AS tbzz
        FROM (
            SELECT month(FROM_UNIXTIME(fy.lurusj)) AS lry, yh.bumen AS bm
            , COUNT(*) AS fysl
            FROM __FANGYUAN__ fy, __YONGHU__ yh
            WHERE fy.weihurenid = yh.id
            AND FROM_UNIXTIME(fy.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
            AND FROM_UNIXTIME(fy.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
            GROUP BY lry, yh.bumen
        ) syfy
        RIGHT JOIN (
            SELECT month(FROM_UNIXTIME(fy.lurusj)) AS lry, yh.bumen AS bm
            , COUNT(*) AS fysl
            FROM __FANGYUAN__ fy, __YONGHU__ yh
            WHERE fy.weihurenid = yh.id
            AND FROM_UNIXTIME(fy.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
            GROUP BY lry, yh.bumen
        ) byfy
        ON syfy.bm = byfy.bm
        ORDER BY nfy DESC";
        $fybmtj_result = new \Think\Model();
        $fybmtj_result =$fybmtj_result->query($fybmtj_sql);
        $fybmtj['fy']=$fybmtj_result;
        $fybmtj['bm']=session('bumen');
        $this->assign('fybmtj',$fybmtj);
        //本月数据-客源带看（按分店统计）
        $dkbmtj_sql="select sydk.bm,sydk.dkcs ldk,ifnull(bydk.dkcs,0) ndk, round((ifnull(bydk.dkcs,0)-sydk.dkcs)/sydk.dkcs*100,2) tbzz  from (select month(FROM_UNIXTIME(kydk.dkshijian)) dksj, yh.bumen bm,count(*) dkcs from __KYDAIKAN__ as kydk,__YONGHU__ as yh where kydk.dkuid=yh.id and FROM_UNIXTIME(kydk.dkshijian)>=date_add(date_sub(curdate(),interval 1 month),interval - day(curdate()) + 1 day) and FROM_UNIXTIME(kydk.dkshijian)<=date_sub(curdate(),interval 1 month) group by dksj,yh.bumen) as sydk left join (select month(FROM_UNIXTIME(kydk.dkshijian)) dksj, yh.bumen bm,count(*) dkcs from __KYDAIKAN__ as kydk,__YONGHU__ as yh where kydk.dkuid=yh.id and FROM_UNIXTIME(kydk.dkshijian)>=date_add(curdate(), interval - day(curdate()) + 1 day) group by dksj,yh.bumen) as bydk on sydk.bm=bydk.bm order by ndk desc";
        $dkbmtj_sql = "SELECT sydk.bm,sydk.dkcs ldk,ifnull(bydk.dkcs,0) ndk, round((ifnull(bydk.dkcs,0)-ifnull(sydk.dkcs,0))/ifnull(sydk.dkcs,1)*100,2) tbzz
        FROM (
            SELECT month(FROM_UNIXTIME(kydk.dkshijian)) AS dksj, yh.bumen AS bm
            , COUNT(*) AS dkcs
            FROM __KYDAIKAN__ kydk, __YONGHU__ yh
            WHERE kydk.dkuid = yh.id
            AND FROM_UNIXTIME(kydk.dkshijian) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
            AND FROM_UNIXTIME(kydk.dkshijian) <= date_sub(curdate(), INTERVAL 1 MONTH)
            GROUP BY dksj, yh.bumen
        ) sydk
        LEFT JOIN (
            SELECT month(FROM_UNIXTIME(kydk.dkshijian)) AS dksj, yh.bumen AS bm
            , COUNT(*) AS dkcs
            FROM __KYDAIKAN__ kydk, __YONGHU__ yh
            WHERE kydk.dkuid = yh.id
            AND FROM_UNIXTIME(kydk.dkshijian) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
            GROUP BY dksj, yh.bumen
        ) bydk
        ON sydk.bm=bydk.bm 
        union
        SELECT sydk.bm,sydk.dkcs ldk,ifnull(bydk.dkcs,0) ndk, round((ifnull(bydk.dkcs,0)-ifnull(sydk.dkcs,0))/ifnull(sydk.dkcs,1)*100,2) tbzz
        FROM (
            SELECT month(FROM_UNIXTIME(kydk.dkshijian)) AS dksj, yh.bumen AS bm
            , COUNT(*) AS dkcs
            FROM __KYDAIKAN__ kydk, __YONGHU__ yh
            WHERE kydk.dkuid = yh.id
            AND FROM_UNIXTIME(kydk.dkshijian) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
            AND FROM_UNIXTIME(kydk.dkshijian) <= date_sub(curdate(), INTERVAL 1 MONTH)
            GROUP BY dksj, yh.bumen
        ) sydk
        RIGHT JOIN (
            SELECT month(FROM_UNIXTIME(kydk.dkshijian)) AS dksj, yh.bumen AS bm
            , COUNT(*) AS dkcs
            FROM __KYDAIKAN__ kydk, __YONGHU__ yh
            WHERE kydk.dkuid = yh.id
            AND FROM_UNIXTIME(kydk.dkshijian) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
            GROUP BY dksj, yh.bumen
        ) bydk
        ON sydk.bm=bydk.bm 
        ORDER BY ndk DESC";
        $dkbmtj_result = new \Think\Model();
        $dkbmtj_result =$dkbmtj_result->query($dkbmtj_sql);
        $dkbmtj['dk']=$dkbmtj_result;
        $dkbmtj['bm']=session('bumen');
        $this->assign('dkbmtj',$dkbmtj);
        //本月数据-客源、房源（按分店用户统计）
        $kyfytj_sql="SELECT kyyhtj.bm, kyyhtj.uid, kyyhtj.ygmingcheng
        , ifnull(kyyhtj.lky, 0) AS lky
        , ifnull(kyyhtj.nky, 0) AS nky
        , round((ifnull(kyyhtj.nky, 0) - ifnull(kyyhtj.lky, 0)) / ifnull(kyyhtj.lky, 1) * 100, 2) AS kytbzj
        , ifnull(fyyhtj.lfy, 0) AS lfy
        , ifnull(fyyhtj.nfy, 0) AS nfy
        , round((ifnull(fyyhtj.nfy, 0) - ifnull(fyyhtj.lfy, 0)) / ifnull(fyyhtj.lfy, 1) * 100, 2) AS fytbzj
        FROM (
            SELECT byky.bm, byky.uid, byky.ygmingcheng, syky.kysl AS lky, byky.kysl AS nky
            FROM (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS kysl
                FROM __KEYUAN__ ky, __YONGHU__ yh
                WHERE ky.weihurenid = yh.id  AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(ky.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
                GROUP BY yh.id, yh.bumen
            ) byky
            LEFT JOIN (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS kysl
                FROM __KEYUAN__ ky, __YONGHU__ yh
                WHERE ky.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(ky.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
                AND FROM_UNIXTIME(ky.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
                GROUP BY yh.id, yh.bumen
            ) syky
            ON byky.uid = syky.uid
            AND byky.bm = syky.bm
            UNION
            SELECT syky.bm, syky.uid, syky.ygmingcheng, syky.kysl AS lky, byky.kysl AS nky
            FROM (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS kysl
                FROM __KEYUAN__ ky, __YONGHU__ yh
                WHERE ky.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(ky.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
                GROUP BY yh.id, yh.bumen
            ) byky
            RIGHT JOIN (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS kysl
                FROM __KEYUAN__ ky, __YONGHU__ yh
                WHERE ky.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(ky.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
                AND FROM_UNIXTIME(ky.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
                GROUP BY yh.id, yh.bumen
            ) syky
            ON byky.uid = syky.uid
            AND byky.bm = syky.bm
        ) kyyhtj
        LEFT JOIN (
        SELECT syfy.bm, syfy.uid, syfy.ygmingcheng, syfy.fysl AS lfy, byfy.fysl AS nfy
            FROM (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS fysl
                FROM __FANGYUAN__ fy, __YONGHU__ yh
                WHERE fy.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(fy.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
                AND FROM_UNIXTIME(fy.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
                GROUP BY yh.id, yh.bumen
            ) syfy
            LEFT JOIN (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS fysl
                FROM __FANGYUAN__ fy, __YONGHU__ yh
                WHERE fy.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(fy.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
                GROUP BY yh.id, yh.bumen
            ) byfy
            ON syfy.uid = byfy.uid
            AND syfy.bm = byfy.bm
            UNION
            SELECT byfy.bm, byfy.uid, byfy.ygmingcheng, syfy.fysl AS lfy, byfy.fysl AS nfy
            FROM (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS fysl
                FROM __FANGYUAN__ fy, __YONGHU__ yh
                WHERE fy.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(fy.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
                AND FROM_UNIXTIME(fy.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
                GROUP BY yh.id, yh.bumen
            ) syfy
            RIGHT JOIN (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS fysl
                FROM __FANGYUAN__ fy, __YONGHU__ yh
                WHERE fy.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(fy.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
                GROUP BY yh.id, yh.bumen
            ) byfy
            ON syfy.uid = byfy.uid
            AND syfy.bm = byfy.bm
        ) fyyhtj
        ON kyyhtj.bm = fyyhtj.bm
        AND kyyhtj.uid = fyyhtj.uid        
        UNION
        SELECT fyyhtj.bm, fyyhtj.uid, fyyhtj.ygmingcheng
        , ifnull(kyyhtj.lky, 0) AS lky
        , ifnull(kyyhtj.nky, 0) AS nky
        , round((ifnull(kyyhtj.nky, 0) - ifnull(kyyhtj.lky, 0)) / ifnull(kyyhtj.lky, 1) * 100, 2) AS kytbzj
        , ifnull(fyyhtj.lfy, 0) AS lfy
        , ifnull(fyyhtj.nfy, 0) AS nfy
        , round((ifnull(fyyhtj.nfy, 0) - ifnull(fyyhtj.lfy, 0)) / ifnull(fyyhtj.lfy, 1) * 100, 2) AS fytbzj
        FROM (
            SELECT byky.bm, byky.uid, byky.ygmingcheng, syky.kysl AS lky, byky.kysl AS nky
            FROM (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS kysl
                FROM __KEYUAN__ ky, __YONGHU__ yh
                WHERE ky.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(ky.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
                GROUP BY yh.id, yh.bumen
            ) byky
            LEFT JOIN (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS kysl
                FROM __KEYUAN__ ky, __YONGHU__ yh
                WHERE ky.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(ky.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
                AND FROM_UNIXTIME(ky.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
                GROUP BY yh.id, yh.bumen
            ) syky
            ON byky.uid = syky.uid
            AND byky.bm = syky.bm
            UNION
            SELECT syky.bm, syky.uid, syky.ygmingcheng, syky.kysl AS lky, byky.kysl AS nky
            FROM (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS kysl
                FROM __KEYUAN__ ky, __YONGHU__ yh
                WHERE ky.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(ky.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
                GROUP BY yh.id, yh.bumen
            ) byky
            RIGHT JOIN (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS kysl
                FROM __KEYUAN__ ky, __YONGHU__ yh
                WHERE ky.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(ky.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
                AND FROM_UNIXTIME(ky.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
                GROUP BY yh.id, yh.bumen
            ) syky
            ON byky.uid = syky.uid
            AND byky.bm = syky.bm
        ) kyyhtj
        RIGHT JOIN (
            SELECT syfy.bm, syfy.uid, syfy.ygmingcheng, syfy.fysl AS lfy, byfy.fysl AS nfy
            FROM (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS fysl
                FROM __FANGYUAN__ fy, __YONGHU__ yh
                WHERE fy.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(fy.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
                AND FROM_UNIXTIME(fy.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
                GROUP BY yh.id, yh.bumen
            ) syfy
            LEFT JOIN (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS fysl
                FROM __FANGYUAN__ fy, __YONGHU__ yh
                WHERE fy.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(fy.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
                GROUP BY yh.id, yh.bumen
            ) byfy
            ON syfy.uid = byfy.uid
            AND syfy.bm = byfy.bm
            UNION
            SELECT byfy.bm, byfy.uid, byfy.ygmingcheng, syfy.fysl AS lfy, byfy.fysl AS nfy
            FROM (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS fysl
                FROM __FANGYUAN__ fy, __YONGHU__ yh
                WHERE fy.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(fy.lurusj) >= date_add(date_sub(curdate(), INTERVAL 1 MONTH), INTERVAL -day(curdate()) + 1 DAY)
                AND FROM_UNIXTIME(fy.lurusj) <= date_sub(curdate(), INTERVAL 1 MONTH)
                GROUP BY yh.id, yh.bumen
            ) syfy
            RIGHT JOIN (
                SELECT yh.bumen AS bm, yh.id AS uid, yh.ygmingcheng, COUNT(*) AS fysl
                FROM __FANGYUAN__ fy, __YONGHU__ yh
                WHERE fy.weihurenid = yh.id AND yh.bumen=".session('bumen')."
                AND FROM_UNIXTIME(fy.lurusj) >= date_add(curdate(), INTERVAL -day(curdate()) + 1 DAY)
                GROUP BY yh.id, yh.bumen
            ) byfy
            ON syfy.uid = byfy.uid
            AND syfy.bm = byfy.bm
        ) fyyhtj
        ON kyyhtj.bm = fyyhtj.bm 
        AND kyyhtj.uid = fyyhtj.uid order by fytbzj desc limit 6"; 
        $kyfytj_result = new \Think\Model();
        $kyfytj_result =$kyfytj_result->query($kyfytj_sql);
        $this->assign('fykytj',$kyfytj_result);
        $this->display();
    }
    //显示头像页面
    public function avatar(){
        $yhtx = '/Upload/Avatar/'.session("gongsiid").'/'.session('username').'_200.jpg';
        if (file_exists('.'.$yhtx)){
            $this->touxiang = $yhtx;
        }
        $this->display();
    }
}