<?php
include "../db/dBase.php";
$dbase = new dBase();
$tcon = $dbase->con;
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: GET");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

$crrentDate = date("Y-m-d H:i");
$split = array('result' => 'FAIL');
if (isset($_GET['apikey'])) {
    $key_ = mysqli_real_escape_string($tcon, $_GET['apikey']);
    $key = md5($key_);

    $cur_m=date('m');
    $cur_y=date('Y');
    $cur_d=date('d');


    $overall_time_factor_closed=" AND ( DATE(lead.dated) >= now()-interval 1 MONTH OR DATE(lead.last_updated_date) >= now()-interval 1 MONTH )  ";
    $overall_time_factor=" AND MONTH(lead.added) = '".$cur_m."' AND  YEAR(lead.added)='".$cur_y."' ";
    $overall_time_factor_full=" AND DAY(lead.added) = '".$cur_d."' AND MONTH(lead.added) = '".$cur_m."' AND  YEAR(lead.added)='".$cur_y."' ";

    // check API Validity
    $api_n = $dbase->countQuery("SELECT `id` FROM `access_log` WHERE `token`='$key' ");
    if ($api_n == 1) {


        if (isset($_GET['filter'])) {
            $filter = json_decode($_GET['filter']);
        }

        $while_monthly = $dbase->querytoWhile("SELECT COUNT(*) AS 'total_leads', YEAR(lead.dated) AS `selDay`,MONTH(lead.dated) AS `selMon` FROM lead WHERE DATE(lead.dated) >= now()-interval 11 MONTH GROUP BY MONTH(lead.dated) ORDER BY DATE(lead.dated) ASC  ");



        $last_month_closed = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` LEFT JOIN `user` ON lead.agent = user.userID WHERE lead.id<>'' $overall_time_factor_closed AND lead.status='C' AND user.type='AG' ");
        $last_month_total = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE lead.id<>'' $overall_time_factor ");
        $last_month_rent = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE lead.id<>'' $overall_time_factor AND `rent_sale`='rent' ");
        $last_month_sale = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE lead.id<>'' $overall_time_factor AND ( `rent_sale`='sale' OR `rent_sale`='offplan')  ");
        $last_month_owner = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE DATE(lead.dated) >= now()-interval 40 DAY AND `rent_sale`='owner' ");

        $last_week_closed = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` LEFT JOIN `user` ON lead.agent = user.userID WHERE DATE(lead.dated) >= now()-interval 8 DAY AND lead.status ='C' ");
        $last_week_total = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE DATE(lead.dated) >= now()-interval 8 DAY ");
        $last_week_rent = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE DATE(lead.dated) >= now()-interval 8 DAY AND `rent_sale`='rent' ");
        $last_week_sale = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE DATE(lead.dated) >= now()-interval 8 DAY AND ( `rent_sale`='sale' OR `rent_sale`='offplan')  ");
        $last_week_owner = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE DATE(lead.dated) >= now()-interval 8 DAY AND `rent_sale`='owner' ");

        $last_day_closed = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` LEFT JOIN `user` ON lead.agent = user.userID WHERE lead.id<>'' $overall_time_factor_full AND lead.status='C' ");
        $last_day_total = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE lead.id<>'' $overall_time_factor_full ");
        $last_day_rent = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE lead.id<>'' $overall_time_factor_full AND `rent_sale`='rent' ");
        $last_day_sale = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE lead.id<>'' $overall_time_factor_full AND ( `rent_sale`='sale' OR `rent_sale`='offplan')  ");
        $last_day_owner = $dbase->queryto_fetch_api("SELECT COUNT(*) AS 'count' FROM `lead` WHERE lead.id<>'' $overall_time_factor_full AND `rent_sale`='owner' ");


        $last_month = array('closed' => $last_month_closed['count'], 'total' => $last_month_total['count'], 'rent' => $last_month_rent['count'], 'sale' => $last_month_sale['count'], 'owner' => $last_month_owner['count']);

        $last_week = array('closed' => $last_week_closed['count'], 'total' => $last_week_total['count'], 'rent' => $last_week_rent['count'], 'sale' => $last_week_sale['count'], 'owner' => $last_week_owner['count']);

        $last_day = array('closed' => $last_day_closed['count'], 'total' => $last_day_total['count'], 'rent' => $last_day_rent['count'], 'sale' => $last_day_sale['count'], 'owner' => $last_day_owner['count']);


        $rent_closures = $dbase->queryToWhile_2("SELECT COUNT(*) AS `count`, lead.id,user.name,user.dp,agent.short FROM `lead` LEFT JOIN `user` ON lead.agent=user.userID 
         LEFT JOIN `agent` ON agent.userID=user.userID LEFT JOIN `response` ON response.leadID = lead.id WHERE response.status='N' AND user.status=0 AND user.type='AG' AND lead.agent<>'63bf9d9068ff2' AND lead.status='C' ".$overall_time_factor_closed." GROUP BY lead.agent ORDER BY DATE(response.dated) DESC, TIME(response.dated) DESC, `count` DESC ");

        $sale_closures = $dbase->queryToWhile_2("SELECT COUNT(*) AS `count`, lead.id,user.name FROM `lead` LEFT JOIN `user` ON lead.agent=user.userID WHERE user.status=0 AND lead.status='C' AND (lead.rent_sale= 'sale' OR lead.rent_sale= 'offplan' OR lead.rent_sale= '' OR lead.rent_sale= 'resale' ) ".$overall_time_factor_closed."  GROUP BY lead.agent ORDER BY `count` DESC ");

        $campaign_while = $dbase->queryToWhile_2("SELECT campaign.title AS `name`, COUNT(*) AS  `count` FROM `lead` LEFT JOIN `campaign` ON campaign.campaignID = lead.campaign WHERE lead.id <> '' $overall_time_factor  AND lead.campaign <> '5e6c7bcfd98fd'  AND lead.campaign<>'5e3e4eaf80e9e'  GROUP BY lead.campaign ORDER BY count DESC LIMIT 5  ");

        $source_while= $dbase->queryToWhile_2("SELECT _source.name AS `name`,_source.img, COUNT(*) AS  `count` FROM `lead` LEFT JOIN `_source` ON _source.short = lead.source WHERE lead.id <> '' $overall_time_factor GROUP BY lead.source ORDER BY count DESC LIMIT 8  ");

        $agent_un_while = $dbase->queryToWhile_2("SELECT user.social_status,user.name,user.userID,user.dp,agent.short FROM `user` LEFT JOIN `agent` ON agent.userID = user.userID WHERE user.status=0 AND user.type='AG' AND user.userID<>'AG5ab9009ec4ebf' AND agent.screen='ON' ");

        $result=[];
        
        foreach (array_filter($agent_un_while) as $agent_un_while_key => $agent_un_while_val) {
            
            $cUser = $agent_un_while_val['userID'];
            

            $total_count = $dbase->queryto_fetch_api("SELECT COUNT(*) AS `count` FROM `lead` WHERE agent='" . $cUser . "'  ");
            $closeCount = $dbase->queryto_fetch_api("SELECT COUNT(*) AS `count` FROM `lead` WHERE `agent`='$cUser' AND `status`='C' $overall_time_factor ");
            $unattandedCount = $dbase->queryto_fetch_api("SELECT COUNT(*) AS `count` FROM `lead` WHERE `agent`='$cUser' AND `status`='U' $overall_time_factor ");
            $positiveCount = $dbase->queryto_fetch_api("SELECT COUNT(*) AS `count` FROM `lead` LEFT JOIN `_response_list` ON _response_list.code = lead.response_code WHERE lead.agent='$cUser' AND _response_list.category='POSITIVE' ");

            if ($total_count['count'] > 5) {
               
                
            
                $expireCount = $dbase->queryto_fetch_api("SELECT COUNT(*) AS `count` FROM `lead` WHERE `agent`='$cUser' AND `status`='E' ");
                
                
                $total_official = abs($total_count['count'] - $expireCount['count']);
                
                // count sections
                $response_count = $dbase->queryto_fetch_api("SELECT COUNT(*) AS `count` FROM `lead` WHERE lead.response_code<>'' AND lead.status<>'E' AND lead.agent='" . $cUser . "' ");
                // calculations
                if($total_official>0){
                    $response_score = round(($response_count['count'] / ($total_official)) *100);
                }else{ $response_score=0; }
                

                $result_new = array('social_status'=>$agent_un_while_val['social_status'],'short' => $agent_un_while_val['short'],'name' => $agent_un_while_val['name'], 'userID' => $cUser, 'dp' => $agent_un_while_val['dp'], 'score' => $response_score,'closed'=>$closeCount['count'],'una'=>$unattandedCount['count'],'pos'=>$positiveCount['count']);
                array_push($result,$result_new);

        
            
            }
            
        }



        $split = array('last_month' => $last_month, 'last_week' => $last_week, 'last_day' => $last_day, 'RC' => array_filter($rent_closures), 'SC' => array_filter($sale_closures), 'rank_while' => array_filter($result),'camps'=>array_filter($campaign_while),'src_while'=>array_filter($source_while),'month_while'=>array_filter($while_monthly));
    }
}
echo json_encode(array_filter($split));
