统计报表SQL语句整合

<?php
class ModelChartChart extends Model {



    public function getMonth($year,$month){
        $sqlY="select month(regdate) as `mon`,count(*) as num from company where year ( regdate) = $year group by month ( regdate)";
        $queryY=$this->db->query($sqlY);
        $dataY= $queryY->rows;
        $sqlM="select day(regdate) as `day`,count(*) as num from company where year ( regdate) = $year and month ( regdate) = $month group by day ( regdate)";
        $queryM=$this->db->query($sqlM);
        $dataM= $queryM->rows;
        $newData['date_m']=$dataY;
        $newData['date_d']=$dataM;
        return $newData;
    }
    public function getActivity($year,$month){
        $sqlY="select month(last_login) as `mon`,count(*) as num from user where year ( last_login) = $year group by month ( last_login)";
        $queryY=$this->db->query($sqlY);
        $dataY= $queryY->rows;
        $sqlM="select day(last_login) as `day`,count(*) as num from user where year ( last_login) = $year and month ( last_login) = $month group by day ( last_login)";
        $queryM=$this->db->query($sqlM);
        $dataM= $queryM->rows;
        $newData['date_m']=$dataY;
        $newData['date_d']=$dataM;
        return $newData;
    }
    public function getTurnover($year,$month){
        $sqlY="select month(a.addeddate) as `mon`,sum((ceil((a.min_salary+a.max_salary)/2))*a.total_person) as num from(select p.id as p_id,p.addeddate,pj.* from project p left join projectjob pj on p.id=pj.projectid where pj.a_status='completed' and  year ( p.addeddate) = $year)a group by month ( a.addeddate)";
        $queryY=$this->db->query($sqlY);
        $dataY= $queryY->rows;
        $sqlM="select day(a.addeddate) as `day`,sum((ceil((a.min_salary+a.max_salary)/2))*a.total_person) as num from(select p.id as p_id,p.addeddate,pj.* from project p left join projectjob pj on p.id=pj.projectid where pj.a_status='completed' and  year ( p.addeddate) = $year and month ( p.addeddate) = $month)a group by day( a.addeddate)";
        $queryM=$this->db->query($sqlM);
        $dataM= $queryM->rows;
        $newData['date_m']=$dataY;
        $newData['date_d']=$dataM;
        return $newData;
        /*
        //$sql="select month(regdate) as `mon`,count(*) as num from company where year ( regdate) = $year group by month ( regdate)";
        $sql="select month(p.addeddate) as `mon`,sum(ceil((pj.min_salary+pj.max_salary)/2)) as num from project p left join projectjob pj on p.id=pj.projectid where pj.a_status='completed' and  year ( p.addeddate) = $year group by month ( p.addeddate)";
        $query=$this->db->query($sql);
        return $query->rows;
        */
    }
    public function getMaster($year,$month){
        $sqlY="select month(datanow) as `mon`,count(*) as num from user where year ( datanow) = $year group by month ( datanow)";
        $queryY=$this->db->query($sqlY);
        $dataY= $queryY->rows;
        $sqlM="select day(datanow) as `day`,count(*) as num from user where year ( datanow) = $year and month ( datanow) = $month group by day ( datanow)";
        $queryM=$this->db->query($sqlM);
        $dataM= $queryM->rows;
        $newData['date_m']=$dataY;
        $newData['date_d']=$dataM;
        return $newData;
        /*
        $sql="select month(datanow) as `mon`,count(*) as num from user where year (datanow) = $year group by month (datanow)";
        $query=$this->db->query($sql);
        return $query->rows;
        */
    }
    public function getProject($year,$month){
        $sqlSM="select month(p.addeddate) as `mon`,count(*) as num from project p left join projectjob pj on p.id=pj.projectid where year(p.addeddate) = $year and (pj.a_status='completed' or pj.a_status='progress' or pj.a_status='employed' ) group by month (p.addeddate)";
        $querySM=$this->db->query($sqlSM);
        $dataSM= $querySM->rows;
        $sqlSD="select day(p.addeddate) as `day`,count(*) as num from project p left join projectjob pj on p.id=pj.projectid where year(p.addeddate) = $year and month ( p.addeddate) = $month and (pj.a_status='completed' or pj.a_status='progress' or pj.a_status='employed' ) group by month (p.addeddate)";
        $querySD=$this->db->query($sqlSD);
        $dataSD= $querySD->rows;

        $sqlTM="select month(p.addeddate) as `mon`,count(*) as num from project p left join projectjob pj on p.id=pj.projectid where year(p.addeddate) = $year  group by month (p.addeddate)";
        $queryTM=$this->db->query($sqlTM);
        $dataTM= $queryTM->rows;
        $sqlTD="select day(p.addeddate) as `day`,count(*) as num from project p left join projectjob pj on p.id=pj.projectid where year(p.addeddate) = $year and month ( p.addeddate) = $month   group by month (p.addeddate)";
        $queryTD=$this->db->query($sqlTD);
        $dataTD= $queryTD->rows;

        $data['date_sm']=$dataSM;
        $data['date_sd']=$dataSD;
        $data['date_tm']=$dataTM;
        $data['date_td']=$dataTD;
        return $data;

    }
    public function getProjectSucceed($year=null){
        if($year==null){
            $year=date('Y',time());//获取当前年份
        }
        $sql="select month(p.addeddate) as `mon`,count(*) as num from project p left join projectjob pj on p.id=pj.projectid where year(p.addeddate) = $year and (pj.a_status='completed' or pj.a_status='progress' or pj.a_status='employed' ) group by month (p.addeddate)";
        $query=$this->db->query($sql);
        return $query->rows;
    }
    public function getProjectTotal($year=null){
        if($year==null){
            $year=date('Y',time());//获取当前年份
        }
        $sql="select month(p.addeddate) as `mon`,count(*) as num from project p left join projectjob pj on p.id=pj.projectid where year(p.addeddate) = $year  group by month (p.addeddate)";
        $query=$this->db->query($sql);
        return $query->rows;
    }


}
?>