<?php

class Statistics {
    public static function business(){
        global $wpdb;
        $today = date("Y-m-d");
        $year = date("Y");
        $month = date("m");
        $consultant = array();
        $sql = "select count(name) as num,house_type ,user_id as consultantId from tospur_house
        where house_type >0 and creattime >= '".$today."' and
        creattime<DATE_SUB('".$today."', INTERVAL -1 DAY)
        group by user_id,house_type;";
        $result = $wpdb->get_results($sql);
        if(!$wpdb->last_error){
            foreach($result as $item){
                if(!$consultant[$item->consultantId]){
                    $bItem = array();
                }else{
                    $bItem = $consultant[$item->consultantId];
                }
                $where = array(
                    "consultantId" => $item->consultantId,
                    "date" => $today
                );
                $params = array();
                $house_type = $item->house_type;
                if($house_type == 1){
                    $params["newSell"] = $item->num;
                    $bItem["newSell"] = $item->num;
                }
                if($house_type == 2){
                    $params["newLease"] = $item->num;
                    $bItem["newLease"] = $item->num;
                }
                $consultant[$item->consultantId] = $bItem;
            }
        }

        $sql = "select consultant_id as consultantId,sum(if(new_house+secondHand_house>0,1,0)) as newBuy,sum(rent_house) as newTenant from tospur_customer
        where time >= '".$today."'
        and time<DATE_SUB('".$today."', INTERVAL -1 DAY)
        group by consultant_id;";
        $result = $wpdb->get_results($sql);
        if(!$wpdb->last_error){
            foreach($result as $item){
                if(!$consultant[$item->consultantId]){
                    $bItem = array();
                }else{
                    $bItem = $consultant[$item->consultantId];
                }
                $where = array(
                    "consultantId" => $item->consultantId,
                    "date" => $today
                );
                $params = array(
                    "newBuy" => $item->newBuy,
                    "newTenant" => $item->newTenant
                );
                $bItem["newBuy"] = $item->newBuy;
                $bItem["newTenant"] = $item->newTenant;
                $consultant[$item->consultantId] = $bItem;
            }
        }

        /* 效率高点？ 由于数据少 待确认 少了sql的if判断，
         * select consultant_id,sum(business) as business,sum(lease) as lease from (select consultant_id,1 as business,0 as lease,tct.time from tospur_customer_tracking tct
        left join tospur_house th on th.id =tct.house_id
        where th.house_type in(0,1)
        union
        select consultant_id,0 as business,1 as lease,tct.time from tospur_customer_tracking tct
        left join tospur_house th on th.id =tct.house_id
        where th.house_type = 2) t1
        where time >= '2015-09-24'
        and time < DATE_SUB('2015-09-24', INTERVAL -1 DAY)
        group by consultant_id;
         * */
        $sql = "select tct.consultant_id as consultantId,
        sum(if(th.house_type<2,1,0)) as business,sum(if(th.house_type = 2 ,1,0)) as lease
        from tospur_customer_tracking tct
        left join tospur_house th on th.id =tct.house_id
        where tct.time >= '".$today."'
        and tct.time < DATE_SUB('".$today."', INTERVAL -1 DAY)
        group by consultant_id;";
        $result = $wpdb->get_results($sql);
        if(!$wpdb->last_error){
            foreach($result as $item){
                if(!$consultant[$item->consultantId]){
                    $bItem = array();
                }else{
                    $bItem = $consultant[$item->consultantId];
                }
                $where = array(
                    "consultantId" => $item->consultantId,
                    "date" => $today
                );
                $params = array(
                    "business" => $item->business,
                    "lease" => $item->lease
                );
                $bItem["business"] = $item->business;
                $bItem["lease"] = $item->lease;
                $consultant[$item->consultantId] = $bItem;
            }
        }

        $sql = "select consultantId,sum(if(type = 0,1,0)) as dealBusiness,sum(if(type = 1,1,0)) as dealLease from tospur_commission tc
        where contractId in(select id from tospur_contract where status = 3
        and dealTime >= '".$today."'
        and dealTime < DATE_SUB('".$today."', INTERVAL -1 DAY))
        group by consultantId;";
        $result = $wpdb->get_results($sql);
        //print_r($wpdb->last_query);
        if(!$wpdb->last_error){
            foreach($result as $item){
                if(!$consultant[$item->consultantId]){
                    $bItem = array();
                }else{
                    $bItem = $consultant[$item->consultantId];
                }
                $where = array(
                    "consultantId" => $item->consultantId,
                    "date" => $today
                );
                $params = array(
                    "dealBusiness" => $item->dealBusiness,
                    "dealLease" => $item->dealLease
                );
                $bItem["dealBusiness"] = $item->dealBusiness;
                $bItem["dealLease"] = $item->dealLease;
                $consultant[$item->consultantId] = $bItem;
            }
        }

        $sql = "select consultantId,quota from tospur_quota where year = ".$year." and month = ".$month.";";
        $result = $wpdb->get_results($sql);
        if(!$wpdb->last_error){
            foreach($result as $item){
                if(!$consultant[$item->consultantId]){
                    $bItem = array();
                }else{
                    $bItem = $consultant[$item->consultantId];
                }
                $where = array(
                    "consultantId" => $item->consultantId,
                    "date" => $today
                );
                $params = array(
                    "quota" => $item->quota
                );
                $bItem["quota"] = $item->quota;
                $consultant[$item->consultantId] = $bItem;
            }
        }

        $sql = "select consultantId,sum(accounts) as accounts from
        tospur_commission where mtime >= '".$today."'
        and mtime < DATE_SUB('".$today."', INTERVAL -1 DAY)
        group by consultantId;";
        $result = $wpdb->get_results($sql);
        if(!$wpdb->last_error){
            foreach($result as $item){
                if(!$consultant[$item->consultantId]){
                    $bItem = array();
                }else{
                    $bItem = $consultant[$item->consultantId];
                }
                $where = array(
                    "consultantId" => $item->consultantId,
                    "date" => $today
                );
                $params = array(
                    "accounts" => $item->accounts
                );
                $bItem["accounts"] = $item->accounts;
                $consultant[$item->consultantId] = $bItem;
            }
        }

        $sql = "select tc.consultantId,sum(tcl.paid) as paid from tospur_commission tc
        left join tospur_commission_log tcl on tc.id = tcl.commissionId
        where time >= '".$today."'
        and time < DATE_SUB('".$today."', INTERVAL -1 DAY)
        group by tc.consultantId;";
        $result = $wpdb->get_results($sql);
        if(!$wpdb->last_error){
            foreach($result as $item){
                if(!$consultant[$item->consultantId]){
                    $bItem = array();
                }else{
                    $bItem = $consultant[$item->consultantId];
                }
                $where = array(
                    "consultantId" => $item->consultantId,
                    "date" => $today
                );
                $params = array(
                    "paid" => $item->paid
                );
                $bItem["paid"] = $item->paid;
                $consultant[$item->consultantId] = $bItem;
            }
        }

        $sql = "select sum(if(house_type = 1,1,0)) sell,sum(if(house_type = 2,1,0)) lease,ahu.user_id as consultantId from a_house_user ahu
        left join tospur_house th on th.id = ahu.house_id
        where th.house_type > 0 and user_type = 1 group by ahu.user_id;";
        $result = $wpdb->get_results($sql);
        if(!$wpdb->last_error){
            foreach($result as $item){
                if(!$consultant[$item->consultantId]){
                    $bItem = array();
                }else{
                    $bItem = $consultant[$item->consultantId];
                }
                $where = array(
                    "consultantId" => $item->consultantId,
                    "date" => $today
                );
                $params = array(
                    "stockSellH" => $item->sell,
                    "stockLeaseH" => $item->lease
                );
                $bItem["stockSellH"] = $item->sell;
                $bItem["stockLeaseH"] = $item->lease;
                $consultant[$item->consultantId] = $bItem;
            }
        }

        $sql = "select consultant_id as consultantId,sum(secondHand_house) as stockSellC,sum(rent_house) as stockLeaseC from tospur_customer where status = 0 group by consultant_id;";
        $result = $wpdb->get_results($sql);
        if(!$wpdb->last_error){
            foreach($result as $item){
                if(!$consultant[$item->consultantId]){
                    $bItem = array();
                }else{
                    $bItem = $consultant[$item->consultantId];
                }
                $where = array(
                    "consultantId" => $item->consultantId,
                    "date" => $today
                );
                $params = array(
                    "stockSellC" => $item->stockSellC,
                    "stockLeaseC" => $item->stockLeaseC
                );
                $bItem["stockSellC"] = $item->stockSellC;
                $bItem["stockLeaseC"] = $item->stockLeaseC;
                $consultant[$item->consultantId] = $bItem;
            }
        }

        foreach($consultant as $key => $item){
            if($key>0){
                $where = array(
                    "consultantId" => $key,
                    "date" => $today
                );
                Statistics::updateBusiness($item,$where);
            }
        }
    }

    public static function updateBusiness($params,$where){
        global $wpdb;
        $result = $wpdb->update(Config::STATISTICS_BUSINESS,$params,$where);
        if($result === 0){
            $result = $wpdb->get_row($wpdb->prepare("select * from ".Config::STATISTICS_BUSINESS." where consultantId=%d and date = %s",$where["consultantId"],$where["date"]));
            if(!$result)
                $wpdb->insert(Config::STATISTICS_BUSINESS,array_merge($params,$where));
        }
    }
} 