<?php
session_start();
$companyname=$_SESSION['companyname'];//;
$adminemailid=$_SESSION['emailid'];//;
$companytype=$_SESSION['companytype'];

$employeetype=$_SESSION['employeetype'];
include('../Model/configuration.php');
require_once('../Control/sales.php');
$objsales=new salesctrl();
require_once('../Control/pipeline.php');
$objpipeline=new pipelinectrl();
$pipelinetype_res=$objsales->salesselectall($companyname, $adminemailid, "sales_pipe_type", "no");
$i=0;
$pipe_name=array();
$pipe_percentage=array();
foreach($pipelinetype_res as $pipelinetype_row){
    $pipe_id[$i]=$pipelinetype_row['id'];
    $pipe_name[$i]=$pipelinetype_row['pipe_name'];
    $pipe_percentage[$i]=$pipelinetype_row['pipe_percentage'];
    //echo $pipelinetype_row['pipe_percentage'];
    $i++;
}
$emp_res=$objsales->salesselectall($companyname, $adminemailid, "signupdetails", "no");
$email_array=[];
$employeename_array=[];

$j=0;
foreach($emp_res as $emp_row){
$emp_emailid_a[$j++]=$emp_row['emailid'];  
$emp_employeename[$j++]=$emp_row['employeename'];
array_push($email_array,$emp_row['emailid']);
array_push($employeename_array,$emp_row['employeename']);
}

if(isset($_POST['expected_won'])){
    $startmonth=$_POST['expected_won'];
                            
    $year=date('Y', strtotime($startmonth));
    $month=date('Y-m-01', strtotime($startmonth));
    $qu=["q1","q2","q3","q4"];
    $mno=date('m', strtotime($startmonth));
    if($mno<4){
        $va=0;
    }elseif($mno<7){
        $va=1;
    }elseif($mno<10){
        $va=2;
    }else{
        $va=3;
    }
    
    for($iiiii=0,$v1=intval($va);$iiiii<6;$iiiii++){
        if($qu[$v1]=="q1"){
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="01" or  Month(d_date)="02" or  Month(d_date)="03")and YEAR(`d_date`)="'.$year.'" and `d_stage`="won"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }else if($qu[$v1]=="q2"){
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="04" or  Month(d_date)="05" or  Month(d_date)="06")and YEAR(`d_date`)="'.$year.'" and `d_stage`="won"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }else if($qu[$v1]=="q3"){
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="07" or  Month(d_date)="08" or  Month(d_date)="09")and YEAR(`d_date`)="'.$year.'" and `d_stage`="won"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }else{
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="10" or  Month(d_date)="11" or  Month(d_date)="12")and YEAR(`d_date`)="'.$year.'" and `d_stage`="won"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }
        $v1++;
        if($v1>3){
            $v1=0;
            $year=intval($year)+1;
        }
    ?>
    
    <div class="col-md-2" style="color:black;cursor:pointer;padding-top:15px;height:50px;background-color:#fff;border-right:1px solid #ddd;">
                 <?php if($total_amount>0){ ?>
                <a style="color:black;font-size:14px;" data-toggle="modal" data-target="#myModal_view_pipeline_deal" onclick="show_pipeline_total_deal_month('<?php echo $companyname;?>','<?php echo $months;?>','<?php echo $year;?>')"><center>&#8360&nbsp;<b><?php echo $total_amount; ?></b></center></a>
                <?php  } else { ?>
                <center><span style="color: #ccc;text-decoration: none;">&#8360&nbsp;0</span></center>
                <?php } ?>
    </div> 
    
    <?php
    }
}



if(isset($_POST['expected_won_pending'])){
    $startmonth=$_POST['expected_won_pending'];
                            
    $year=date('Y', strtotime($startmonth));
    $month=date('Y-m-01', strtotime($startmonth));
    $qu=["q1","q2","q3","q4"];
    $mno=date('m', strtotime($startmonth));
    if($mno<4){
        $va=0;
    }elseif($mno<7){
        $va=1;
    }elseif($mno<10){
        $va=2;
    }else{
        $va=3;
    }
    
    for($iiiii=0,$v1=intval($va);$iiiii<6;$iiiii++){
        if($qu[$v1]=="q1"){
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="01" or  Month(d_date)="02" or  Month(d_date)="03")and YEAR(`d_date`)="'.$year.'" and `d_stage`!="loss"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }else if($qu[$v1]=="q2"){
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="04" or  Month(d_date)="05" or  Month(d_date)="06")and YEAR(`d_date`)="'.$year.'" and `d_stage`!="loss"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }else if($qu[$v1]=="q3"){
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="07" or  Month(d_date)="08" or  Month(d_date)="09")and YEAR(`d_date`)="'.$year.'" and `d_stage`!="loss"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }else{
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="10" or  Month(d_date)="11" or  Month(d_date)="12")and YEAR(`d_date`)="'.$year.'" and `d_stage`!="loss"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }
        $v1++;
        if($v1>3){
            $v1=0;
            $year=intval($year)+1;
        }
    ?>
    
    <div class="col-md-2" style="color:black;cursor:pointer;padding-top:15px;height:50px;background-color:#fff;border-right:1px solid #ddd;">
                 <?php if($total_amount>0){ ?>
                <a style="color:black;font-size:14px;" data-toggle="modal" data-target="#myModal_view_pipeline_deal" onclick="show_pipeline_total_deal_month('<?php echo $companyname;?>','<?php echo $months;?>','<?php echo $year;?>')"><center>&#8360&nbsp;<b><?php echo $total_amount; ?></b></center></a>
                <?php  } else { ?>
                <center><span style="color: #ccc;text-decoration: none;">&#8360&nbsp;0</span></center>
                <?php } ?>
    </div> 
    
    <?php
    }
}


if(isset($_POST['quarterly_target'])){
    $startmonth=$_POST['quarterly_target'];
                            
    $year=date('Y', strtotime($startmonth));
    $month=date('Y-m-01', strtotime($startmonth));
    $qu=["q1","q2","q3","q4"];
    $mno=date('m', strtotime($startmonth));
    if($mno<4){
        $va=0;
    }elseif($mno<7){
        $va=1;
    }elseif($mno<10){
        $va=2;
    }else{
        $va=3;
    }
    
    for($iiiii=0,$v1=intval($va);$iiiii<6;$iiiii++){
        if($qu[$v1]=="q1"){
        $sqlans1='SELECT sum(t_1+t_2+t_3) as amount FROM `sales_employee_target1` WHERE `t_company`="'.$companyname.'" and `t_year`="'.$year.'"';
        }else if($qu[$v1]=="q2"){
        $sqlans1='SELECT sum(t_4+t_5+t_6) as amount FROM `sales_employee_target1` WHERE `t_company`="'.$companyname.'" and `t_year`="'.$year.'"';
        }else if($qu[$v1]=="q3"){
        $sqlans1='SELECT sum(t_7+t_8+t_9) as amount FROM `sales_employee_target1` WHERE `t_company`="'.$companyname.'" and `t_year`="'.$year.'"';
        }else{
        $sqlans1='SELECT sum(t_10+t_11+t_12) as amount FROM `sales_employee_target1` WHERE `t_company`="'.$companyname.'" and `t_year`="'.$year.'"';
        }
        $resultans1 = $pdo->query($sqlans1);
        $total_amount=0;
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['amount'];
        }
        $traget_amount=round($total_amount);
        $v1++;
        if($v1>3){
            $v1=0;
            $year=intval($year)+1;
        }
    ?>
    
        <div class="col-md-2" style="color:black;cursor:pointer;padding-top:10px;height:40px;background-color:#fff;border-right:1px solid #ddd;">
            <span style="color:grey;font-size:14px;"><center>&#8360&nbsp;<b><?php echo $traget_amount;?></b></center></span>
        </div>
    
    <?php
    }
}


if(isset($_POST['quarterly_body'])){
    $startdate=$_POST['quarterly_body'];
    for($i1=0;$i1<count($pipe_percentage);$i1++){
            $j=100-($i1*10)."%";
            $stage=$pipe_id[$i1];
            $percentage=$pipe_percentage[$i1];
            $p_name=$pipe_name[$i1];
    ?>
                
        <div class="row" style="height:40px;">
            <div class="col-md-3" style="background-color:#f6f6f6;">
                <center><span id="pipeline1" style="width:<?php echo $j;?>"><span ><?php echo $percentage;?><br><span style="font-size:12px;"><?php echo $p_name;?></span></span></span></center>
            </div>
            <div class="col-md-9"  style="height:40px;">
                
                
                <?php
                            
                $year=date('Y', strtotime($startdate));
                $month=date('Y-m-01', strtotime($startdate));
                $qu=["q1","q2","q3","q4"];
                $mno=date('m', strtotime($startdate));
                if($mno<4){
                    $va=0;
                }elseif($mno<7){
                    $va=1;
                }elseif($mno<10){
                    $va=2;
                }else{
                    $va=3;
                }
                
                for($iiiii=0,$v1=intval($va);$iiiii<6;$iiiii++){
                    if($qu[$v1]=="q1"){
                    $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="01" or  Month(d_date)="02" or  Month(d_date)="03")and YEAR(`d_date`)="'.$year.'" and `d_stage`="'.$stage.'"';
                    $resultans1 = $pdo->query($sqlans1);
                    $total_amount="";
                    foreach($resultans1 as $rowans1){
                        $total_amount=$rowans1['total_amount'];
                    }
                    $total_amount=round($total_amount);
                    }else if($qu[$v1]=="q2"){
                    $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="04" or  Month(d_date)="05" or  Month(d_date)="06")and YEAR(`d_date`)="'.$year.'" and `d_stage`="'.$stage.'"';
                    $resultans1 = $pdo->query($sqlans1);
                    $total_amount="";
                    foreach($resultans1 as $rowans1){
                        $total_amount=$rowans1['total_amount'];
                    }
                    $total_amount=round($total_amount);
                    }else if($qu[$v1]=="q3"){
                    $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="07" or  Month(d_date)="08" or  Month(d_date)="09")and YEAR(`d_date`)="'.$year.'" and `d_stage`="'.$stage.'"';
                    $resultans1 = $pdo->query($sqlans1);
                    $total_amount="";
                    foreach($resultans1 as $rowans1){
                        $total_amount=$rowans1['total_amount'];
                    }
                    $total_amount=round($total_amount);
                    }else{
                    $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="10" or  Month(d_date)="11" or  Month(d_date)="12")and YEAR(`d_date`)="'.$year.'" and `d_stage`="'.$stage.'"';
                    $resultans1 = $pdo->query($sqlans1);
                    $total_amount="";
                    foreach($resultans1 as $rowans1){
                        $total_amount=$rowans1['total_amount'];
                    }
                    $total_amount=round($total_amount);
                    }
                    $v1++;
                    if($v1>3){
                        $v1=0;
                        $year=intval($year)+1;
                    }
                ?>
                
                <div class="col-md-2" style="color:black;cursor:pointer;padding-top:15px;height:50px;background-color:#fff;border-right:1px solid #ddd;">
                             <?php if($total_amount>0){ ?>
                            <a style="color:black;font-size:14px;" data-toggle="modal" data-target="#myModal_view_pipeline_deal" onclick="show_pipeline_total_deal_month('<?php echo $companyname;?>','<?php echo $months;?>','<?php echo $year;?>')"><center>&#8360&nbsp;<b><?php echo $total_amount; ?></b></center></a>
                            <?php  } else { ?>
                            <center><span style="color: #ccc;text-decoration: none;">&#8360&nbsp;0</span></center>
                            <?php } ?>
                </div> 
                
                <?php
                }
                ?>
            </div>
        </div>
        <br>
                
<?php
    }
}


if(isset($_POST['quarterly_tot_pending'])){
    $startdate=$_POST['quarterly_tot_pending'];
                            
    $year=date('Y', strtotime($startdate));
    $month=date('Y-m-01', strtotime($startdate));
    $qu=["q1","q2","q3","q4"];
    $mno=date('m', strtotime($startdate));
    if($mno<4){
        $va=0;
    }elseif($mno<7){
        $va=1;
    }elseif($mno<10){
        $va=2;
    }else{
        $va=3;
    }
    
    for($iiiii=0,$v1=intval($va);$iiiii<6;$iiiii++){
        if($qu[$v1]=="q1"){
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="01" or  Month(d_date)="02" or  Month(d_date)="03")and YEAR(`d_date`)="'.$year.'" and `d_stage`!="loss" and `d_stage`!="won"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }else if($qu[$v1]=="q2"){
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="04" or  Month(d_date)="05" or  Month(d_date)="06")and YEAR(`d_date`)="'.$year.'" and `d_stage`!="loss" and `d_stage`!="won"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }else if($qu[$v1]=="q3"){
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="07" or  Month(d_date)="08" or  Month(d_date)="09")and YEAR(`d_date`)="'.$year.'" and `d_stage`!="loss" and `d_stage`!="won"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }else{
        $sqlans1='SELECT sum( case when `d_stage`="Won" then `d_amount` else (d_amount * cast(REPLACE(sales_pipeline_type.pipe_percentage, "%", "") as UNSIGNED INTEGER) / 100) end) as total_amount FROM `sales_deal_pipeline` left join sales_pipeline_type on sales_pipeline_type.id=sales_deal_pipeline.d_stage WHERE sales_deal_pipeline.`companyname`="'.$companyname.'" and ( Month(d_date)="10" or  Month(d_date)="11" or  Month(d_date)="12")and YEAR(`d_date`)="'.$year.'" and `d_stage`!="loss" and `d_stage`!="won"';
        $resultans1 = $pdo->query($sqlans1);
        $total_amount="";
        foreach($resultans1 as $rowans1){
            $total_amount=$rowans1['total_amount'];
        }
        $total_amount=round($total_amount);
        }
        $v1++;
        if($v1>3){
            $v1=0;
            $year=intval($year)+1;
        }
    ?>
    
    <div class="col-md-2" style="color:black;cursor:pointer;padding-top:15px;height:50px;background-color:#fff;border-right:1px solid #ddd;">
        <?php if($total_amount>0){ ?>
        <a style="color:#db1b56;font-size:14px;"><center>&#8360&nbsp;<b><?php echo $total_amount; ?></b></center></a>
        <?php  } else { ?>
        <center><span style="color: #db1b56;text-decoration: none;">&#8360&nbsp;0</span></center>
        <?php } ?>
    </div> 
    
    <?php
    }
    ?>
                
<?php
}



?>